Re: 9.0.2 64-bit on redhat 3.0r2 AMD64 (4-way) 17G RAM

Christian Antognini wrote:

> **** Post for FREE via your newsreader at post.usenet.com ****
>
> Hi
>
>> db_cache_size is ridiculous, get it down to 256m
>> pga_aggregate_target is ridiculous as this is a *per session* limit.
>> 64 to 128m should be more than enough.
>
> This is wrong. PGA_AGGREGATE_TARGET is for the whole system. Just give a
> look to the documentation...
>

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96536/ch1158.htm#1022446
>
> For a multi GB PGA_AGGREGATE_TARGET I suggest you to read Metalink note
> 147806.1 (and the related notes as well...) to have some information about
> the limits set by Oracle. Here an excerpt:
>
>> PGA_AGGREGATE_TARGET limits both the global PGA consumption and the size
> of a
>> workarea i.e. the memory allocated to a single SQL operator is also
> limited
>> to min(5% PGA_AGGREGATE_TARGET, 100MB) for serial operations, and to
>> 30% PGA_AGGREGATE_TARGET/DOP for parallel operations (DOP=Degree of
> Parallelism).

It's when I see documentation like that I understand why Sybrand posted what he posted.

Translation: PGA_AGGREGATE_TARGET sets a global total of memory for the entire database which can be used by all users collectively as their total combined PGA. No user will use less than 5% of the total, or 100MB, whichever is the smaller (assuming s/he makes use of it at all); and no single user can have more than 30% of the total. If a user issues parallel DML or a parallel query, then the 30% maximum is divided amongst all the possible query slaves that user might spawn. The user, even with a high degree of parallelism, cannot hog more than 30% of the total available PGA resources.

Something like that, anyway.
Regards
HJR Received on Sun Aug 29 2004 - 23:57:31 CDT