RE: SORT_AREA_SIZE question

From: <Rajesh.Rao_at_jpmchase.com>
Date: Wed, 25 Jun 2003 11:17:26 -0700
Message-ID: <F001.005B97C5.20030625103032@fatcity.com>

Since we are talking of Sort area sizes here, I tried to collect some statistics on the amount of memory used by each dedicated Oracle connection. As I understand it, the PGA is allocated in chunks upto the SORT_AREA_SIZE. Initially, I just started a session, and noted down the memory used using pmap. Then, I did a small sort, and then, a huge sort, noting the memory utilization each time. I was even monitoring the PGA statistics in v$sysstat. I was expecting the sizes to increase, and then decrease when the sorting was done with. (retained_size was 0). I dont recall my observations rite now, but it was definitely not what I expected.

Has anyone done something similar? Are my expectations rite?

Thanks
Raj

                                                                                       
                             
                    DENNIS WILLIAMS                                                    
                             
                    <[EMAIL PROTECTED]       To:     Multiple recipients of list 
ORACLE-L <[EMAIL PROTECTED]>       
                    TOUCH.COM>            cc:                                          
                             
                    Sent by:              Subject:     RE: SORT_AREA_SIZE question     
                             
                    [EMAIL PROTECTED]                                                  
                               
                    ty.com                                                             
                             
                                                                                       
                             
                                                                                       
                             
                    06/25/2003                                                         
                             
                    12:30 PM                                                           
                             
                    Please respond                                                     
                             
                    to ORACLE-L

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 see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author:
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 - 13:17:26 CDT