There are two nice papers about it on Steve Adams' site.
http://www.ixora.com.au <http://www.ixora.com.au>
You can find it in the newsletter section.
One for daily oltp :
Basically it says to measure the daily disk sort size used,
dived it by the sort count and you have your average size = sort_area_size.
By the way the script is also on the site: disk_sorts.sql
<http://www.ixora.com.au/scripts/sql/disk_sorts.sql> . This script shows
also the maximum concurrent sorts, which will allow you to know the maximum
memory allocated for sorts.
For batch jobs
Here he uses event 10032 that will output a trace for sorts
during batch processing. From there you can compute the best sort_area_size
to allow a one pass sort. It means that you will only need one merge phase.
I wrote a summary of this if you want I can send it to you.
Jean Remacle
-----Original Message-----
From: Manavendra Gupta [mailto:[EMAIL PROTECTED]]
Sent: jeudi 8 ao�t 2002 19:59
To: Multiple recipients of list ORACLE-L
Subject: SORT_AREA_SIZE
What should be the optimal SORT_AREA_SIZE? What parameters
does it vary on?
Regards,
Manav.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Manavendra Gupta
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: Remacle Jean
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).
