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

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