| 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,
Excellent testing approach! Thanks so much! I'll try it...
-Tim
on 10/3/03 6:50 AM, Richard Foote at richard.foote_at_bigpond.com 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" <ORACLE-L_at_fatcity.com>
> 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 richard.foote_at_bigpond.com 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
>> 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
>> 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
>> were pushing our memory limit previously. >> >> Secondly, as memory is more effectively returned, Oracle/we can be both
>> generous and more flexible in how much memory each session "temporarily" >> consumes. With manual tuning, after setting the (say) SAS to (say) 10M,
>> if a session wanted 11M, or 50M, or 150M etc ? Although as you suggest
>> are quotas in how much a particular session can consume depending on >> workload (eg. 5% limit for serial operations, etc.), the maximum memory
>> can be "safely" consumed by a session could be somewhat higher. If too
>> 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
>> 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
>> large number of sessions most of which perform workspace operations at
>> 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
>> dramatically reducing overall memory consumption. >> >> Best of both worlds !! >> >> Cheers >> >> ----- Original Message ----- >> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> >> 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
>> 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
>> a long time, essentially until they disconnect. Is this correct? >> >> Is WORKAREA_SIZE_POLICY = AUTO any different? From what I've gathered,
>> P_A_T algorithms only occur upon allocation of workarea memory. Is there >> any additional logic around de-allocation, possibly when the server
>> 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
>> 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
>>> 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
>>> 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
>>> small number active at a time but the sum of the PGAs was considerable
>>> 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
>>> 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
>>> 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
>>> 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
>>> 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
>>> 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). >>> >>> >> >> -- >> 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). >> >>
-- 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 Sat Oct 04 2003 - 14:19:24 CDT
 |
 |