Re: pga_aggregate_target

This looks like it may turn into a variant of the 'is it a hint or a directive' argument. Is a target something that Oracle TRIES to hit, or something that it HAS to hit ?

My understanding to date is that Oracle operates its PGA allocation with the intent of keeping the total PGA size of dedicated servers below the target value. However, the strategy can only cover certain aspects of PGA management (e.g. sort_area_size) not all of them (e.g. pl/sql array processing memory).

Consequently it is possible for a process to grow it's PGA outside the control of pga_aggregate_target. You might note, particularly, that the PGA allocated for AUTO workareas is zero - I think this means that all your PGA (over-)allocation is down to 'ordinary' PGA use, not to 'tunable' PGA use.

10M is a very small pga_aggregate_target, by the way. A single process is only allowed 5% of the aggregate; and there is a bit of a hint in the hidden parameter descriptions that any value less than 32M is ignored anyway.

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

For one-day tutorials:
(see http://www.jlcomp.demon.co.uk/tutorial.html )

____UK_______April 8th
____UK_______April 22nd
____Denmark May 21-23rd
____USA_(FL)_May 2nd

Next dates for the 3-day seminar:
(see http://www.jlcomp.demon.co.uk/seminar.html )
____UK_(Manchester)_May
____Estonia___June (provisional)
____USA_(CA, TX)_August

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


"Buck Turgidson" <jc_va_at_hotmail.com> wrote in message
news:258ga.13018$LH3.366517_at_news1.east.cox.net...


> I am trying to understand this parameter.  Is it correct that the

total


> PGA inuse (line 4) can exceed the aggregate PGA target parameter

(line


> 1)?  I thought line1 was fixed.

>

> SQL> SQL> select * from v$pgastat;

>

> NAME                                          VALUE UNIT

> ---------------------------------------- ---------- ------------

> aggregate PGA target parameter             10485760 bytes

> aggregate PGA auto target                   4194304 bytes

> global memory bound                          524288 bytes

> total PGA inuse                            13915136 bytes

> total PGA allocated                        17977344 bytes

> maximum PGA allocated                      17977344 bytes

> total freeable PGA memory                         0 bytes

> PGA memory freed back to OS                  131072 bytes

> total PGA used for auto workareas                 0 bytes

> maximum PGA used for auto workareas               0 bytes

> total PGA used for manual workareas               0 bytes

> maximum PGA used for manual workareas             0 bytes

> over allocation count                            37

> bytes processed                              602112 bytes

> extra bytes read/written                          0 bytes

> cache hit percentage                            100 percent

>

>

>