Re: SORT_AREA_SIZE question

Hi!

You are getting some things a bit wrong here. With pmap you see the whole process, which is much more than just sort area which resides in CGA which resides in PGA which resides in memory allocated to the process. There is much more memory needed for a process, such text, data, and uninitialized data segments. Text segment is read-only, thus shared and can be counted only once when calculating memory requirements (correct if I'm wrong). Others are writable, thus separately allocated for each process. Also there's stack, memory for cursor, probably lot's more of stuff there.

Why you don't see OS memory decreasing for a process when sort is over is because free() call doesn't return memory allocated with malloc() to OS immediately (actually it'll wait for OS to collect the memory... er.. I believe). If never used again, the unused pages are paged out, thus not harming anyone, but hey, in Oracle we expect continous operations, so when next query is executed, malloc() can get exactly same pages back, no memory is wasted.

Btw, you can check the size of executable using (believe the uninitialized data segment isn't allocated during executable load, others are)

bash-2.03$ /usr/ccs/bin/size $ORACLE_HOME/bin/oracle 27230877 + 267348 + 21108 = 27519333
--Above: Text + data + uninitialized sizes for oracle executable (9.2 on solaris)

Lot's of what I've written, comes from Steve Adams site, so you really ought to check http://www.ixora.com.au/newsletter/2000_11.htm for example.

Cheers,
Tanel.

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Thursday, June 26, 2003 1:04 AM

>
> Thanks Dennis. My question was not about the memory release, but more of
> the way memory is allocated to the PGA. Given that Oracle allocates memory
> in small chunks to the PGA until it approaches the sort area size, if I
run
> a huge sort, should I expect the PGA memory to increase, until it reaches
> sort_area_size?
>
> 1. Initial dedicated connection established. The PGA used should be very
> minimal.
>
> 2. Say, I invoke a small sort on DBA_USERS. The PGA (or UGA) should
> increase by a very small value.
>
> 3. I invoke a sort on a huge table. I expect the PGA to keep increasing,
> and flatten out once it reaches the SORT_AREA_SIZE.
>
> I tried to monitor the memory utilization by the session using pmap, and
> v$sysstat, during these 3 steps, and did not see the expected results. It
> was a rudimentary test on a high volume database, so I guess I could have
> been wrong. I remember I tried this coz I read a post from Jonathan Lewis,
> suggesting that the initial memory for sorting is allocated in the UGA
upto
> sort_area_retained_size, after which it is allocated from the PGA upto
> sort_area_size.
>
> 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

> 04:27 PM
> Please respond
> to ORACLE-L
>
>
>
>
>
>
> 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: Tanel Poder
  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).