Hi Tim and Richard Is there another modification to the process as follows
[server process foote]: I'd like to malloc some private heap/data memory use in sorting, hashing, bitmap operations, or whatever? [instance ellison]: OK, what do you need? [server process foote]: Um, I'd like 100Mb, please? Mr Bowie has released a *lot* of records and I need them in date order, by record label and sleeve colour, grouped by NME review rating [instance ellison]: Well, PGA_AGGREGATE_TARGET is 2Gb and currently I see that 150 other server processes are using 1.2Gb at the moment... [server process gorman]: I'm done sorting!... [instance ellison]: shutup Tim you'll have to wait I'm talking to Mr Foote about the man who fell to earth damnit. Or in other words is there not a latch on this tally of allocated memory? This is a real question. Niall But I like the conversation idea anyway.... > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On > Behalf Of Richard Foote > Sent: 29 September 2003 14:30 > To: Multiple recipients of list ORACLE-L > Subject: 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" <[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: Richard Foote > 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: Niall Litchfield 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).