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

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