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).
