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: [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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Hillman, Alex
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).
