Tuning Sorts

From: Johnson Poovathummoottil <joni_65_at_yahoo.com>
Date: Fri, 15 Jun 2001 08:06:00 -0700
Message-ID: <F001.0032B608.20010615075656@fatcity.com>

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). Received on Fri Jun 15 2001 - 10:06:00 CDT