Richard, Excellent testing approach! Thanks so much! I'll try it...
-Tim on 10/3/03 6:50 AM, Richard Foote at [EMAIL PROTECTED] wrote: > Hi Tim, > > > > Good questions. > > > > As you mention, the memory Oracle "says and thinks" it's released and what > it "actually" releases to the kernel has generally been two different > things. However, the behaviour with P_A_T is somewhat different. A simple > little test for the unconvinced is to simply issue (this btw is on Tru64, > 5.1): > > > > ls -l /proc/1685047 > > > > where 1685047 is a process id of interest. It lists the sum of all memory > structures associated to the process, the whole lot. With > workarea_size_policy left at manual, listing this before any significant > (say) sort activity and then afterwards, you'll see the amount of total > memory climb but not come down . This extra memory is effectively "hogged" > until the session closes as you describe. > > > > However when you run the same test with work_area_size set to auto, you'll > notice the total memory climb during the workarea operation but importantly > come back down again once complete (except for a little memory leak here or > there). The point is though that the memory is being released and is no > longer associated with the process. > > > > Our extra 2G of available memory on the O/S level suggests that memory is > being more than efficiently reused. > > > > I'm not entirely sure how Oracle differs in it's implementation and what new > O/S calls it performs (far from being obscure, it's certainly something > worth an investigating). > > > > What I would certainly recommend is that one check out this new feature, see > how it performs in one's environment and under one's particular workload > conditions and determine whether or not it's beneficial. > > > > Maybe others have similar real life experiences to share ? > > > > Cheers > > > > Richard Foote > > > > > > > > ----- Original Message ----- > > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > Sent: Thursday, October 02, 2003 3:44 AM > > > Richard, > > Thanks for the detailed explanation! > > As a "C" programmer of some 20 years, I can only assume that Oracle has done > away with the use of the "malloc()", "free()", etc UNIX library calls and is > now calling the UNIX system call "brk()" directly? > > It was the underlying heap-extent management in the standard "malloc" > library, which is of course outside of Oracle's control, which made the > SORT_AREA_RETAINED_SIZE parameter largely ineffective for deallocating > workarea memory back to the OS. > > Because if Oracle is continuing to call "malloc()" and "free()", then I can > only say that the Oracle RDBMS certainly *thinks* it is releasing memory (as > it did in the past), but it really isn't. > > Any idea if this is the case? Kind of obscure, I know, but it is this chain > of reasoning that has allowed a reasonable explanation of the > ineffectiveness of the SORT_AREA_RETAINED_SIZE parameter in performing its > documented purpose in the past. > > Thanks in advance! > > -Tim > > on 9/30/03 6:49 AM, Richard Foote at [EMAIL PROTECTED] wrote: > >> Hi Tim, >> >> I would suggest there are two key advantages to using automatic workspace >> management. >> >> The first and perhaps most important is that yes, unlike the manual method >> by which sessions "cling" onto memory, automatic workspace management can >> deallocate the tuneable portion of the PGAs (those previously set with >> *_AREA_SIZE parameters) when no longer required. This means that the > overall >> memory consumption used during peak periods (when memory is possibly a >> problem) is likely to be less as the average memory used per session is >> likely to be lower due to this deallocation process (although it does >> somewhat depend on both the size and concurrency of these operations). On > a >> key production database at my current site, the vast majority of the >> 1000-1200 sessions are sitting with moderate pga_alloc_mem (1/2M) despite >> most having a substantially larger pga_max_mem due to previous workspace >> activity (as evidenced in v$process). This overall reduction in memory >> consumption is measurable at between 1-2G which for us was significant as > we >> were pushing our memory limit previously. >> >> Secondly, as memory is more effectively returned, Oracle/we can be both > more >> generous and more flexible in how much memory each session "temporarily" >> consumes. With manual tuning, after setting the (say) SAS to (say) 10M, > what >> if a session wanted 11M, or 50M, or 150M etc ? Although as you suggest > there >> are quotas in how much a particular session can consume depending on >> workload (eg. 5% limit for serial operations, etc.), the maximum memory > that >> can be "safely" consumed by a session could be somewhat higher. If too > many >> operations require a onepass/multipass executions, then the P_A_T should >> obviously be reviewed. However although the P_A_T setting kinda provides a >> safety net for memory consumption, if you have few concurrent, largish >> workarea operations, you could set the P_A_T to be somewhat higher than >> perhaps desirable (if reached) knowing it won't in fact be reached because >> of the low concurrency of these operations. This then increases the > maximum >> memory capacity for each session in a controlled manner, knowing that this >> memory won't be hogged by the sessions. As I mentioned before, we now >> experience no disks sorts whatsoever. >> >> In our environment, automatic workspace management has been ideal. We have > a >> large number of sessions most of which perform workspace operations at > some >> stage but not concurrently in any significant numbers. Thereby, we have >> managed to both improve the efficiency of workspace operations by allowing >> sessions to acquire the necessary memory as required while at the same > time >> dramatically reducing overall memory consumption. >> >> Best of both worlds !! >> >> Cheers >> >> ----- Original Message ----- >> To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> >> Sent: Tuesday, September 30, 2003 7:39 AM >> >> >> 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). >> >> > > -- > 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).