Re: pga_aggregate_target

two situations that can lead to bigger pga than that limit: 1. someuser did alter session manually and that can work-area-polilcy manual and set some big/huge sort/hash size.
2. shared server configurations.

THere is some confliction in metalink/manual;  Can you post the SQL you use to get your current PGA size?

Metalink note 147806.1:
b. PGA_AGGREGATE_TARGET

*This parameter controls the maximum amount of memory PGA which can be used by the queries when WORKAREA_SIZE_POLICY is set to Auto.*

The value you can be set in Bytes, kilobytes (K), megabytes (M) or gigabytes (G). The default value is 0.

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

*Based on this parameter, the automatic tuning mode always try to achieve the
following:

Untunable Memory Size + Tunable Memory Size <= PGA_AGGREGATE_TARGET*

Document: (9.2 chapter 14)
Under automatic PGA memory management mode, the main goal of Oracle is to honor the PGA_AGGREGATE_TARGET limit set by the DBA, by controlling dynamically the amount of PGA memory allotted to SQL work areas. At the same time, Oracle tries to maximize the performance of all the memory-intensive SQL operators, by maximizing the number of work areas that are using an optimal amount of PGA memory (cache memory). The rest of the work areas are executed in one-pass mode, *unless the PGA memory limit set by the DBA with the parameter *PGA*_AGGREGATE_TARGET** is so low that multi-pass execution is required to reduce even more the consumption of PGA memory and honor the PGA target limit.*

On 12/17/05, Sami Seerangan <dba.orcl_at_gmail.com> wrote:
> Syed,
>
> pga_aggregate_target is just a target , it is not a limit.
> Look for PGA Aggregate Target Advisory in Oracle9i Database Performance
> Tuning Guide and Reference
>
>
> On 12/13/05, The Human Fly < sjaffarhussain_at_gmail.com> wrote:
> > Hello List,
> >
> > I was little confused about the settings of pga_aggregate_target
> > parameter. I was in a assumption that when we set this parameter to a
> > certain value, the usage of pga wont go beond this value. I have set
> > pga as 3gb to one of my business critical database. Recently, I have
> > found that max pga allocation was 4+ Gb.
>
>

--
Regards
Zhu Chao
www.cnoug.org

--
http://www.freelists.org/webpage/oracle-l