Oracle-L: SORT

From: Remacle Jean <Jean.Remacle_at_winterthur.be>
Date: Thu, 23 May 2002 08:28:38 -0800
Message-ID: <F001.00469742.20020523082838@fatcity.com>

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: Jean.Remacle_at_winterthur.be

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: ListGuru_at_fatcity.com (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). Received on Thu May 23 2002 - 11:28:38 CDT