Hi all,
I did some testing on sorts. I sorted a table of 269 MB. I did set the
sort_area_size to a minimum value of 10k. I set event 10032 to level 1 and
looked after the initial runs to be able to calculate a good value for
sort_area_size. Once this done, I did run the statement with event 10053 set
to level 1 and discovered that initial runs did not match.
With 10032 I get 6 as initial runs and sort_area_size is 385024 where under
10053 I get 1069 initial runs and an area_size of 264192. So if you make the
maths you discover that the sorted size is about (6*385024) 2.2 MB for 10032
and is (1069*264192) 269 MB for 10053.
I guess that event 10053 reports just a simulation and it divides the table
size by its area size to get the number of initial runs. Event 10032 reports
the real numbers of what happened.
But then my question is how can it sort 269 MB of data in 2.2 MB of sort
space? My guess is that it only needs space for sorted rows and this is a
group by. So, the number of rows to sort is 3884794 times 73 bytes (avg row
length) is 269 MB. The number of output rows is 19839 times 73 is 1.4 MB it
will fit in 2.2 MB.
Another question is why that SORT_SIZE is reported instead of
SORT_AREA_SIZE?
Here is the statement and execution plan:
SELECT
trunc(collecttime, 'HH24'),
expname,
ipaddr,
hostname,
instance,
sum(floatvalue),
sum(stopticks - startticks) / 10 cnt
FROM
coldata
group by expname,
ipaddr,
hostname,
instance,
trunc(collecttime, 'HH24');
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=95599 Card=3866581
Bytes=220395117)
1 0 SORT (GROUP BY) (Cost=95599 Card=3866581 Bytes=220395117)
2 1 TABLE ACCESS (FULL) OF 'COLDATA' (Cost=2294 Card=3866581
Bytes=220395117)
Jean Remacle
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Remacle Jean
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
