| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: workarea_size_policy=auto and performance efficiency [was: Re:
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).Received on Mon Sep 29 2003 - 16:39:46 CDT
 |
 |