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 [EMAIL PROTECTED] 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" <[EMAIL PROTECTED]> > 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 [EMAIL PROTECTED] 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. >> [EMAIL PROTECTED] >> >> -----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 >> [EMAIL PROTECTED] 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:[EMAIL PROTECTED] >>> 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 >>> >>> [EMAIL PROTECTED] 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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
