RE: Tuning Sorts

From: Hillman, Alex <Alex.Hillman_at_usmint.treas.gov>
Date: Fri, 15 Jun 2001 08:48:00 -0700
Message-ID: <F001.0032B764.20010615083859@fatcity.com>

I would make sort_area_retained_size = your average sort size. Otherwize your sorts which are executed in memory will dump result after sorting into temp tablespace and then server process will read it from there to return rows. Sort allocate memory in chunks of 8K, so you should worry only in case when there are too many users who are doing large sorts simultaniously. Also I think memory is freed when records returned ( I am not sure).

Alex Hillman

-----Original Message-----
Sent: Friday, June 15, 2001 11:57 AM
To: Multiple recipients of list ORACLE-L

Hi All,

Regarding Sorting.

In my database memory sort is 99% compared to disk sorts. But the actual number of disk sorts are nearly 500 to 600 per week. This is a Data warehouse. I have a few large fact tables ranging from (45GB to 10GB). Some users do sort on the whole table. The top waits are
Top 5 Wait Events

       Wait     % Total
Event

Waits Time (cs) Wt Time

------------ ------- db file scattered read 3,886,044 22,739,445 65.99 db file sequential read 9,582,433 6,583,891 19.11 direct path read 9,514,554 2,805,084 8.14 direct path write 1,250,033 1,732,938 5.03 SQL*Net more data to client 735,751 122,820 .36

We have a weekly load into database. but during the week days no loading is done as all data and index tablespaces are made readonly. So the direct path read and write waits are from sort write and reads in the temp tablespace which uses tempfiles.

DB_BLOCK_SIZE = 16K My plan is to use IXORA's formula
SQRT(48318382080*65536*2/0.81) and

set the SORT_AREA_SIZE = 88423692
        SORT_AREA_REATINED_SIZE = 1M
        SORT_MULTIBLOCK_READ_COUNT = 4

My concern is that since I have dedicated server connections the sort area size allocated in the PGA will not be returned to the OS (although it is only for the life of the session) resulting in paging and swapping if too many users start big sorts.

Please shoot down whatever you think is amiss.

Do You Yahoo!?
Get personalized email addresses from Yahoo! Mail - only $35 a year! http://personal.mail.yahoo.com/
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Johnson Poovathummoottil
INET: joni_65_at_yahoo.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).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Hillman, Alex
INET: Alex.Hillman_at_usmint.treas.gov

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------