RE: SORT_AREA_SIZE question

Raj

   From our perspective as users of the operating system we expect when we release memory that the O.S. will immediately release it to other users.
>From the O.S. perspective, that is treated more like a suggestion. The O.S.
has many tasks to accomplish and reusing released memory is pretty low on the list of priorities.

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

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.

1. 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.
2. Ask your system administrator to monitor whether your system is getting paging/swapping.
3. 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).
-- 
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).