RE: SORT_AREA_SIZE question

From: DENNIS WILLIAMS <DWILLIAMS_at_LIFETOUCH.COM>
Date: Wed, 25 Jun 2003 09:00:06 -0700
Message-ID: <F001.005B952B.20030625083028@fatcity.com>

Bart -

No it hasn't been recently asked.

SORT_AREA_SIZE is per-process. So be a little wary. Keep in mind that in-memory sorts are much faster than disk sorts. But you knew that.
Ask your system administrator to monitor whether your system is getting paging/swapping.
Find out how many disk sorts are occurring. I prefer STATSPACK. Try to get a handle on how may disk vs. memory sorts are occurring. Try to increase SORT_AREA_SIZE until you have few disk sorts, but not so large you cause paging/swapping. Also look at your temp space settings to make sure when disk is used, it is used most efficiently. With a data mart, you may have some really large sorts that will always be too large for memory sorts.

Dennis Williams
DBA, 80%OCP, 100% DBA
Lifetouch, Inc.
[EMAIL PROTECTED] -----Original Message-----
Sent: Wednesday, June 25, 2003 9:39 AM
To: Multiple recipients of list ORACLE-L

Hello,

I joined this list last week, so I apologize in advance if I'm asking a question that has previously been answered.

I am responsible for a reporting database/data mart that is approximately 175 GB. Our main fact table ranges from 1-14 GB depending upon how far along we are into our financial year. I have large reports that run full table scans on this table daily. In an effort to keep as much of the sorting in memory as possible I have specified SORT_AREA_SIZE to be 100MB. Some of the tuning books I am reading now are making me second-guess myself and I am wondering if this is overkill.

Can anyone provide some advice on how large they are setting their SORT_AREA_SIZE values for their DSS systems?

Thanks in advance,

Bart

Please Note
The information in this E-mail message is legally privileged and confidential information intended only for the use of the individual(s) named above. If you, the reader of this message, are not the intended recipient, you are hereby notified that you should not further disseminate, distribute, or forward this E-mail message. If you have received this E-mail in error, please notify the sender. Thank you

--

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

Author: DENNIS WILLIAMS
INET: [EMAIL PROTECTED]

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

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). Received on Wed Jun 25 2003 - 11:00:06 CDT