workarea_size_policy=auto and performance efficiency [was: Re:

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