| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: SORT
Sort area size is used to sort, but so is the temporary table space. Can
increase your sort area size? Both sort_area_size and
sort_area_retained_size need to be looked into.
sort_area_retained_size specifies in bytes the maximum amount of the user
global area (UGA) memory retained after a sort.
SORT_AREA_SIZE specifies in bytes the maximum amount of memory Oracle will
use for a sort.
So sort_area_retained_size is the min and sort_area_size is the max. You will also use the temporary table space to do sorts. Also as a note not everything will be sorted in memory. Sometimes our sorts go to disk.
Here is a big key when trying to increase the speed of sorts. Check the paramers of the temporary table space. What is the initial extent size and what is the next extent size. Some time our sorts have 100's of extents. This is bad , try and minimize this by increase the extent sizes.
BYE.
MN
>From: Remacle Jean <Jean.Remacle_at_winterthur.be>
>Reply-To: ORACLE-L_at_fatcity.com
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>Subject: SORT
>Date: Thu, 23 May 2002 08:28:38 -0800
>
>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).
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: basher 59 INET: kb7yss_at_hotmail.com 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 - 13:16:48 CDT
 |
 |