Re: workarea_size_policy=auto and performance efficiency [was: Re:

Hi Tim,

Good questions.

As you mention, the memory Oracle "says and thinks" it's released and what it "actually" releases to the kernel has generally been two different things. However, the behaviour with P_A_T is somewhat different. A simple little test for the unconvinced is to simply issue (this btw is on Tru64, 5.1):

ls -l /proc/1685047

where 1685047 is a process id of interest. It lists the sum of all memory structures associated to the process, the whole lot. With workarea_size_policy left at manual, listing this before any significant (say) sort activity and then afterwards, you'll see the amount of total memory climb but not come down . This extra memory is effectively "hogged" until the session closes as you describe.

However when you run the same test with work_area_size set to auto, you'll notice the total memory climb during the workarea operation but importantly come back down again once complete (except for a little memory leak here or there). The point is though that the memory is being released and is no longer associated with the process.

Our extra 2G of available memory on the O/S level suggests that memory is being more than efficiently reused.

I'm not entirely sure how Oracle differs in it's implementation and what new O/S calls it performs (far from being obscure, it's certainly something worth an investigating).

What I would certainly recommend is that one check out this new feature, see how it performs in one's environment and under one's particular workload conditions and determine whether or not it's beneficial.

Maybe others have similar real life experiences to share ?

Cheers

Richard Foote

• Original Message -----

To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Thursday, October 02, 2003 3:44 AM

Richard,

Thanks for the detailed explanation!

As a "C" programmer of some 20 years, I can only assume that Oracle has done away with the use of the "malloc()", "free()", etc UNIX library calls and is now calling the UNIX system call "brk()" directly?

It was the underlying heap-extent management in the standard "malloc" library, which is of course outside of Oracle's control, which made the SORT_AREA_RETAINED_SIZE parameter largely ineffective for deallocating workarea memory back to the OS.

Because if Oracle is continuing to call "malloc()" and "free()", then I can only say that the Oracle RDBMS certainly *thinks* it is releasing memory (as it did in the past), but it really isn't.

Any idea if this is the case? Kind of obscure, I know, but it is this chain of reasoning that has allowed a reasonable explanation of the ineffectiveness of the SORT_AREA_RETAINED_SIZE parameter in performing its documented purpose in the past.

Thanks in advance!

-Tim

on 9/30/03 6:49 AM, Richard Foote at richard.foote_at_bigpond.com wrote:

> Hi Tim,
>
> I would suggest there are two key advantages to using automatic workspace
> management.
>
> The first and perhaps most important is that yes, unlike the manual method
> by which sessions "cling" onto memory, automatic workspace management can
> deallocate the tuneable portion of the PGAs (those previously set with
> *_AREA_SIZE parameters) when no longer required. This means that the
overall
> memory consumption used during peak periods (when memory is possibly a
> problem) is likely to be less as the average memory used per session is
> likely to be lower due to this deallocation process (although it does
> somewhat depend on both the size and concurrency of these operations). On
a
> key production database at my current site, the vast majority of the
> 1000-1200 sessions are sitting with moderate pga_alloc_mem (1/2M) despite
> most having a substantially larger pga_max_mem due to previous workspace
> activity (as evidenced in v$process). This overall reduction in memory
> consumption is measurable at between 1-2G which for us was significant as
we
> were pushing our memory limit previously.
>
> Secondly, as memory is more effectively returned, Oracle/we can be both
more
> generous and more flexible in how much memory each session "temporarily"
> consumes. With manual tuning, after setting the (say) SAS to (say) 10M,
what
> if a session wanted 11M, or 50M, or 150M etc ? Although as you suggest
there
> are quotas in how much a particular session can consume depending on
> workload (eg. 5% limit for serial operations, etc.), the maximum memory
that
> can be "safely" consumed by a session could be somewhat higher. If too
many
> operations require a onepass/multipass executions, then the P_A_T should
> obviously be reviewed. However although the P_A_T setting kinda provides a
> safety net for memory consumption, if you have few concurrent, largish
> workarea operations, you could set the P_A_T to be somewhat higher than
> perhaps desirable (if reached) knowing it won't in fact be reached because
> of the low concurrency of these operations. This then increases the
maximum
> memory capacity for each session in a controlled manner, knowing that this
> memory won't be hogged by the sessions. As I mentioned before, we now
> experience no disks sorts whatsoever.
>
> In our environment, automatic workspace management has been ideal. We have
a
> large number of sessions most of which perform workspace operations at
some
> stage but not concurrently in any significant numbers. Thereby, we have
> managed to both improve the efficiency of workspace operations by allowing
> sessions to acquire the necessary memory as required while at the same
time
> dramatically reducing overall memory consumption.
>
> Best of both worlds !!
>
> Cheers
>
> ----- Original Message -----
> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> Sent: Tuesday, September 30, 2003 7:39 AM
>
>
> Richard,
>
> I take it that your two points are...shall we say...enhancement requests,
> not current functionality? :-)
>
> Following up on the discussion of "space-efficiency" and tabling (for the
> moment) my questions about the "performance-efficiency" side of things.
> Yes, there certainly is an element of "performance-efficiency" to
> "space-efficiency" if it keeps you from swapping...
>
> ..anyway...
>
> Using WORKAREA_SIZE_POLICY = MANUAL, only the sort workarea has ever even
> pretended to give memory back for the duration of the session, depending
on
> the relationship between SORT_AREA_SIZE and SORT_AREA_RETAINED_SIZE. The
> hash and bitmap workareas have never had this functionality, as near as I
> can tell.
>
> So, I think that you're absolutely correct that sessions using
> WORKAREA_SIZE_POLICY = MANUAL will allocate the memory and hold onto it
for
> a long time, essentially until they disconnect. Is this correct?
>
> Is WORKAREA_SIZE_POLICY = AUTO any different? From what I've gathered,
the
> P_A_T algorithms only occur upon allocation of workarea memory. Is there
> any additional logic around de-allocation, possibly when the server
process
> has finished using the workarea? Perhaps there is logic to de-allocate
> before beginning another operation requiring? Or do server processes hold
> onto workarea memory forever here as well?
>
> I'm prepared to accept P_A_T as the "best thing since LMT", but so far I
> don't see it. At least not for all circumstances (as with LMT). I see it
> as a good thing in memory-constrained environments, but in environments
with
> plenty of RAM I see it so far as a possible source of unnecessary
> instability with no upside.
>
> Thanks!
>
> -Tim
>
>
>
> on 9/29/03 5:10 AM, Richard Foote at richard.foote_at_bigpond.com wrote:
>
>> Hi Tim,
>>
>> There are couple of parts of the conversation we've missed out ;)
>>
>> Firstly, the server process when talking to the P_A_T instance should
have
>> said, "What the hell is going on here, what do you mean I can't have my
> full
>> 100M, this keeps on happening and it's just good enough. Get a bloody DBA
> to
>> increase the P_A_T now because it's bloody obvious that the damn thing is
>> set too low ....." (especially if the load you describe is typical).
>>
>> Secondly, the server process when talking to the non P_A_T should have
> said
>> upon receiving the memory, "ha, thanks, and guess what, no one else can
> have
>> this memory back until I decide to rack off, and no I don't care if
you're
>> running short of memory, bugger ya, page for all I care ...."
>>
>> These are very important parts of the conversion !!
>>
>> At the site I currently work at, we had 12G of memory which at peak load
> was
>> just about running out. We have 1000-1200 sessions with (generally) only
a
>> small number active at a time but the sum of the PGAs was considerable
and
>> the major contributor. We had a number of disk sorts occurring although
> the
>> SAS kept the number within acceptable limits. After setting the P_A_T, we
>> now have a comfortable buffer of free memory (generally sitting around
> 1G),
>> disk sorts have disappeared entirely (in four months, we've had 2 disk
>> sorts) and hash joins have improved considerably.
>>
>> Based on my experience, P_A_T is the best thing Oracle has introduced
> since
>> LMT !!
>>
>> Cheers
>>
>> Richard Foote
>> ----- Original Message -----
>> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
>> Sent: Monday, September 29, 2003 6:59 AM
>>
>>
>> Referencing the article mentioned in this thread, I'd also like to
>> understand exactly what is meant by the phrase "[PGA_AGGREGATE_TARGET]
> leads
>> to a more efficient use of RAM memory"?
>>
>> From what I've been able to determine about this functionality,
> "efficient"
>> merely means "space-efficient", not "performance-efficient" (i.e. Fewer
>> cycles? Smarter cycles?). Is this correct? Does anyone know of
anything
>> in WORKAREA_SIZE_POLICY=AUTO which improves performance over
>> WORKAREA_SIZE_POLICY=MANUAL?
>>
>> Please correct me if I'm wrong, but I think the algorithm for
>> WORKAREA_SIZE_POLICY=AUTO can be characterized something like:
>>
>> [server process]: I'd like to malloc some private heap/data memory
>> use in sorting, hashing, bitmap operations, or whatever?
>> [instance]: OK, what do you need?
>> [server process]: Um, I'd like 100Mb, please?
>> [instance]: Well, PGA_AGGREGATE_TARGET is 2Gb and currently I see
>> that 150 other server processes are using 1.2Gb at the
>> moment...
>> [another server process]: I'm done sorting! I've released the
>> 100Mb I was using! Thanks...
>> [instance]: OK, so now it is 149 other server processes using
>> 1.19Gb at the moment. So, you wanted 100Mb? Well, since
>> the amount in use is over 50% of the target, I have to
>> scale your request back by 25%, so I'll let you take 75Mb
>> [server process]: Well, OK. My execution plan was originally
>> devised under the assumption that I'd have 100Mb of sort
>> space in memory, but...
>> [instance]: Hey pal! Take it or leave it! Someone else just
>> took 75Mb, so if you think about it much longer, the
>> total amount in use might grow and then I might
>> only be able to give you 50% of your request!
>> [server process]: OK! OK! I'll take it. (goes off and sadly
>> mallocs only 75Mb of sort space in private memory)...
>>
>> Whereas in WORKAREA_SIZE_POLICY=MANUAL, it goes:
>>
>> [server process]: I'd like to malloc some private heap/data memory
>> use in sorting, hashing, bitmap operations, or whatever.
>> I'd like 100Mb, so that's what I'll allocate...
>>
>> I mean, other than anthropomorphizing the whole thing, is this the
general
>> gist of it? Obviously, since the "instance" isn't a process and I'm not
>> aware of another background process dedicated to this kind of thing, I'd
> say
>> that it is a tally kept someplace in the SGA that is latched and updated
> by
>> each server process in kind, but I thought the idea of a dialogue more
>> amusing... :-)
>>
>> If this is the case, then if I have a server which is not constrained for
>> memory, then why should I be concerned about space-efficiency?
>>
>> I tend to visit 2-3 different companies/organizations per week on a
> regular
>> basis, and while I do find plenty of under-sized servers laboring under
>> over-sized Oracle instances, I just as often find over-sized servers with
>> acres of RAM, in which I'm certain entire DIMMs have never felt a volt of
>> electricity. Typical example is a customer I started at two weeks ago,
> with
>> 12 CPUs and 24 Gb of RAM and 30Gb of swap space, whose database instance
> is
>> demanding about 4 Gb of virtual memory, primarily due to
>> PGA_AGGREGATE_TARGET being set to 1.5Gb. There's typically 20Gb of
>> untouched RAM on this thing!
>>
>> Customer is being hosted by Oracle Apps hosting company and I queried
them
>> on this, suggesting they abandon WORKAREA_SIZE_POLICY = AUTO and go back
> to
>> MANUAL, allowing each process to simply allocate sort, hash, bitmap, etc
>> without regard for one another, as in previous Oracle versions. Of
> course,
>> (as expected) I received a very condescending reply from the hosting
DBAs.
>> No problem -- I'm used to that, and there are much bigger fish to fry
> first
>> (i.e. five SQL statements consuming 60% of LIO and PIO), but I was
> wondering
>> if anyone had any insight into WORKAREA_SIZE_POLICY=AUTO and performance
>> (not space) efficiency?
>>
>> Of course, in this situation I could recommend that PGA_AGGREGATE_TARGET
> be
>> resized to 16-20Gb (as indicated in sizing advice in docs), but how would
>> this functionality help performance in contrast to just generously
setting
>> SORT_AREA_SIZE et al?
>>
>>
>>
>> on 9/26/03 8:34 AM, Cary Millsap at cary.millsap_at_hotsos.com wrote:
>>
>>> I'd be interested to see the sequence of tests that support the
>>> hypothesis that "disk sorts are about 14,000 times slower than memory
>>> sorts."
>>>
>>>
>>> Cary Millsap
>>> Hotsos Enterprises, Ltd.
>>> http://www.hotsos.com
>>>
>>> Upcoming events:
>>> - Performance Diagnosis 101: 10/28 Phoenix, 11/19 Sydney
>>> - Hotsos Symposium 2004: March 7-10 Dallas
>>> - Visit www.hotsos.com for schedule details...
>>>
>>>
>>> -----Original Message-----
>>> DENNIS WILLIAMS
>>> Sent: Thursday, September 25, 2003 12:30 PM
>>> To: Multiple recipients of list ORACLE-L
>>>
>>> Rich - Actually the hint in that posting made me realize what I was
>>> wrestling with on an index build. Don Burleson explains it well
>>> http://www.praetoriate.com/oracle_tips_sorting_operations.htm
>>>
>>> Dennis Williams
>>> DBA, 80%OCP, 100% DBA
>>> Lifetouch, Inc.
>>> dwilliams_at_lifetouch.com
>>>
>>> -----Original Message-----
>>> Sent: Thursday, September 25, 2003 11:35 AM
>>> To: Multiple recipients of list ORACLE-L
>>>
>>>
>>> OK, I'll bite: If SORT_AREA_SIZE isn't to be set, then what is? Are
>>> you
>>> referring to the automagic PGA management?
>>>
>>> Rich
>>>
>>> Rich Jesse System/Database Administrator
>>> rjesse_at_qtiworld.com Quad/Tech Inc, Sussex, WI USA
>>>
>>> 10:30 AM CST = 5:30 PM CEST. Rats! Now I'll have to wait until
>>> tomorrow
>>> for an answer... :)
>>>
>>>
>>>> -----Original Message-----
>>>> From: Mogens Nørgaard [mailto:mln_at_miracleas.dk]
>>>> Sent: Wednesday, September 24, 2003 6:45 PM
>>>> To: Multiple recipients of list ORACLE-L
>>>> Subject: Re: guidance
>>>>
>>>>
>>>> Just talked to Jonathan Lewis from Helsinki. He went through
>>>> some of the
>>>> examples given in the latest issue of Oracle Magazine, and they were
>>>> just plain wrong.
>>>>
>>>> I can't recall them in detail, but I think one of the questions were
>>>> which parameter to set in order to let a user do large sorts.
>>>> In 9i you
>>>> shouldn't set sort_area_size, but that was the correct answer. And so
>>>> on, and so forth.
>>>>
>>>> So the important advise is to do what you think they would
>>>> like to hear :).
>>>>
>>>> Mogens
>>>>
>>>> bulbultyagi_at_now-india.net.in wrote:
>>>>
>>>>> List , I am planning to give my 9i performance tuning exam
>>>> on the first .
>>>>> Any advice you all want to give me ? Pretty nervous about
>>>> it. Sure would
>>>>> appreciate your guidance.
>>>>> ........
>>
>> --
>> Please see the official ORACLE-L FAQ: http://www.orafaq.net
>> --
>> Author: Tim Gorman
>> INET: tim_at_sagelogix.com
>>
>> 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: 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.net
> --
> Author: Tim Gorman
> INET: tim_at_sagelogix.com
>
> 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: 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.net
--
Author: Tim Gorman
  INET: tim_at_sagelogix.com

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: 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.net
-- 
Author: Richard Foote
  INET: richard.foote_at_bigpond.com

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: 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).