How to configure Oracle database PGA_aggregate_target size

Jephe Wu - http://linuxtechres.blogspot.com

Objective: understanding the concept of PGA (program global area)
Environment: Oracle database


Concepts:

PGA containis private sql area and sql work area, for detail, please refer to
http://docs.oracle.com/cd/B19306_01/server.102/b14220/memory.htm#i14490

1. what's pga_aggregate_target?
The total/aggregated PGA memory( allocated by each server process attached to an Oracle instance.

It is a global target for the Oracle instance, and Oracle tries to ensure that the total amount of PGA memory allocated across all database server processes never exceeds this target. The key word in this statement is "tries".

It is possible that PGA memory will grow beyond the "target".
With PGA_AGGREGATE_TARGET, sizing of work areas for all dedicated sessions is automatic and all *_AREA_SIZE parameters are ignored for these sessions. Beginning with version 10 the PGA_AGGREGATE_TARGET is also applicable to shared server sessions

For overview of PGA, refer to http://docs.oracle.com/cd/B19306_01/server.102/b14220/memory.htm#sthref1416

An important point to understand is that PGA_AGGREGATE_TARGET does not set a hard limit on pga size. It is only a target value used to dynamically size the process work areas.
It also does not affect other areas of the pga that are allowed to grow beyond this limit. for more detail, refer to FAQ: ORA-4030 [Video] [ID 399497.1] - Why do I see processes growing larger than the PGA_AGGREGATE_TARGET/MEMORY_TARGET/MEMORY_MAX/TARGET?

Can you limit the size of a process?
You can take steps to control the size of a process as discussed above.
However, from within the database framework you cannot place a hard limit on the size of a process by setting any initialization parameters or database configuration.
You can limit the size of a process from the OS side by setting kernel limits or user shell limits. However, this leads to the ORA-4030 and will cause transaction rollback.

2. set initial size of pga_aggregate_target

According to Oracle support - Automatic PGA Memory Management [ID 223730.1]

To determine the appropriate setting for PGA_AGGREGATE_TARGET  parameter we
recommend to follow the following steps

- For OLTP(On-line transactional processing system): PGA_AGGREGATE_TARGET  = (total physical RAM * 80%) * 20%
e.g. physical RAM is 16G, then (16 G * 80%)*20% ~= 2.5G

- For DSS(Decision support systems):PGA_AGGREGATE_TARGET  = (total physical RAM * 80%) * 50%

3. check if a running database has suitable pga_aggregate_target size

V$PGA_TARGET_ADVICE view predicts how the statistics cache hit percentage and
over allocation count in V$PGASTAT will be impacted if you change the value of
the initialization parameter PGA_AGGREGATE_TARGET.

- STATISTICS_LEVEL. Set this to TYPICAL (the default) or ALL; setting this
  parameter to BASIC turns off generation of PGA performance advice views.

sql> show parameter statistics_level;

The following select statement can be used to find this information

SELECT round(PGA_TARGET_FOR_ESTIMATE/1024/1024) target_mb,
       ESTD_PGA_CACHE_HIT_PERCENTAGE cache_hit_perc,
       ESTD_OVERALLOC_COUNT
FROM   v$pga_target_advice;

The output of this query might look like the following:

TARGET_MB  CACHE_HIT_PERC ESTD_OVERALLOC_COUNT
---------- -------------- --------------------
63         23             367
125        24             30
250        30             3
375        39             0
500        58             0
600        59             0
700        59             0
800        60             0
900        60             0
1000       61             0
1500       67             0
2000       76             0
3000       83             0
4000       85             0

or more simple one:
select * from v$pga_target_advice order by pga_target_for_estimate;


From the above results we should set the PGA_AGGREGATE_TARGET parameter to a
value where we avoid any over allocation, so lowest PGA_AGGREGATE_TARGET value we can set is 375 ( where ESTD_OVERALLOC_COUNT is 0)

The amount of the PGA memory available to an instance can be changed dynamically by
altering the value of the PGA_AGGREGATE_TARGET parameter making it possible
to add to and remove PGA memory from an active instance online

4. database views

a. $pgastat -This view provides instance-level statistics on the PGA memory usage and
the automatic PGA memory manager. For example:

SELECT * FROM V$PGASTAT;

NAME                                               VALUE
--------------------------------------------------------
aggregate PGA target parameter                     524288000 bytes
aggregate PGA auto target                          463435776 bytes
global memory bound                                25600 bytes
total PGA inuse                                    9353216 bytes
total PGA allocated                                73516032 bytes
maximum PGA allocated                              698371072 bytes
total PGA used for auto workareas                  0 bytes
maximum PGA used for auto workareas                560744448 bytes
total PGA used for manual workareas                0 bytes
maximum PGA used for manual workareas              0 bytes
over allocation count                              0 bytes
total bytes processed                              4.0072E+10 bytes
total extra bytes read/written                     3.1517E+10 bytes
cache hit percentage   

Meanings for above individual items, please refer to Oracle support - Automatic PGA Memory Management [ID 223730.1]

b. v$sysstat 
sql> set line 32767;
sql> select * from v$sysstat where name like '%work%';

'workarea memory allocated' - the total amount of PGA memory dedicated to work
areas allocated in Kb.

'workarea executions - optimal' - the cumulative count of work areas which had
an optimal size. For example optimal size is defined if the sort does not need to
spill to the disk

'workarea executions - onepass' - the cumulative count of work areas using the
one pass size. One pass is generally used for big work areas where spilling to
disk cannot be avoided.

'workarea executions - multipass' - the cumulative count of work areas running
in more than one pass. This should be avoided and is the symptom of poorly
tuned system.

The following query returns a percentage of work areas used with optimal
memory size.

select
trunc (
       (sum(case when name like 'workarea executions - optimal'
                                       then value else 0 end) *100) /
               (
                  sum(case when name like 'workarea executions - optimal'  
                                             then value else 0 end)         +
                  sum(case when name like 'workarea executions - one pass' 
                                             then value else 0 end)         +
                  sum(case when name like 'workarea executions - multipass'
                                             then value else 0 end)
            )
        ) optimal_percent
from v$sysstat
where name like 'workarea executions - %'
/

c. v$process

The following three columns in the V$PROCESS view report the PGA memory allocated and used by an Oracle process:

    PGA_USED_MEM
    PGA_ALLOCATED_MEM
    PGA_MAX_MEM

sql> set line 32767;
sql> select * from v$process;
sql> select max(pga_used_mem), max(pga_alloc_mem), max(pga_max_mem) from v$process;

5. init.ora parameters

When we set the PGA_AGGREGATE_TARGET  and WORKAREA_SIZE_POLICY to auto, then the *_area_size parameter are automatically ignored and oracle will automatically use the computed value for these parameters.

Using automatic PGA memory management will help also reducing the possibility of getting ora-4030 errors unless we hit a OS limit, because work area sizes will be controlled and adjusted automatically based on the PGA_AGGGREGATE_TARGET parameter first and then the current work load.

6. Commands and references:

show parameter WORKAREA_SIZE_POLICY;
show parameter pga_aggregate_target;
show parameter statistics_level;
SELECT * FROM V$PGASTAT;
select * from v$sysstat where name like '%work%';
select * from v$process;
select * from v$pga_target_advice order by pga_target_for_estimate;

Oracle process diagram: http://advait.files.wordpress.com/2008/04/oracle_processes1.gif