| 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:
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: ml-errors_at_fatcity.com [mailto:ml-errors_at_fatcity.com] 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" <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).
>
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Richard Foote
> INET: richard.foote_at_bigpond.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: Niall Litchfield INET: niall.litchfield_at_dial.pipex.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:24:40 CDT
 |
 |