Oracle-L: RE: SORT_AREA

From: Remacle Jean <Jean.Remacle_at_winterthur.be>
Date: Fri, 09 Aug 2002 02:38:22 -0800
Message-ID: <F001.004B0539.20020809023822@fatcity.com>

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:manav_at_zycus.com]
		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: manav_at_zycus.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: 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). Received on Fri Aug 09 2002 - 05:38:22 CDT

RE: SORT_AREA_SIZE