Tuning PGA_AGGREGATE_TARGET

The oracle 9i introduces a new parameter PGA_AGGREGATE_TARGET to fix the issue of multiple parameters in oracle 9i such as SOR_AREA_SIZE, HASH_AREA_SIZE of earlier version.

The PGA is private memory region that contains the data and control information for a server process. Oracle Database reads and writes information in the PGA on behalf of the server process. The RAM allocated to the PGA_AGGREGATE_TARGET is used by Oracle connections to maintain connection-specific information (e.g., cursor states) and to sort Oracle SQL result sets.

PGA_AGGREGATE_TARGET allows Oracle9i perform far faster than earlier version because the memory is only allocated in used for the duration of the session upon which is immediately freed up to become available for use by other connected Oracle tasks. 

Initial or Default Setting of PGA_AGGREGATE_TARGET
By default oracle database uses PGA_AGGREGATE_TARGET 20% of SGA Size. However the initial or default setting may be too low for database. In that case you need to run a representative workload on the instance and monitor performance of PGA statistics. Finally after monitoring (if required)
tune PGA_AGGREGATE_TARGET using oracle PGA advice setting.
If you have total memory of system = 4GB then memory required for OS = 20% of total Memory 0.8GB resulting memory 3.2GB available for use of SGA+PGA.
For OLTP system the PGA_AGG_TARGET = 20% of resulting memory i.e 3.2 * 0.2 = 0.64GB or 655MB. For DSS system the PGA_AGG_TARGET = 70% of resulting memory i.e 3.2 * 0.7 = 2.24GB or 1600MB

How to Tune PGA_AGG_TARGET
If the amount of data processed fits the size of optimal work area (Optimal size is when the size of a work area is large enough that it can accommodate the input data) then all the operation is performed in memory and cache is almost 100%.
If the amount of data processed larger than the optimal size then the input is divided into smaller pieces and in that case some piece of data are processed in memory while rest are spilled to temporary tablespace, thus an extra parse is performed on all or some input data, that corresponding size of available work area is called "one-pass". when the available work area size even less than "one-pass" then multiple passes over the input data are required causing dramatic increase in response time hence decreasing the cache hit %.
Generally in OLTP system, size of input data is small hence mostly run in optimal mode where as in DSS system, input data is very large thus require more PGA for good or healthy performance rate.
Before tuning PGA you need to monitor performace of automatic PGA memory. For that several dynamic performance views are available. These views show the total amount of RAM memory utilization for every RAM memory region within the database and new statistics to V$SYSTAT and the new V$PGASTAT and V$PGA_TARGET_ADVICE views assist the DBA in determining if this parameter is not set correctly and the best setting for the PGA_AGGREGATE_TARGET parameter.
Oracle allows a single process to use up to 5 %of the PGA_AGGREGATE_TARGET, and parallel operations are allowed to consume up to 30 percent of the PGA RAM pool.

SQL> Select * from v$SYSSTAT;

SQL> select  name, cnt, decode(total, 0, 0, round(cnt*100/total)) percentage

       from (select name, value cnt, (sum(value) over()) total

        from v$sysstat where name like 'workarea exec%'

       );

Profile                                    Count Percentage

----------------------------------- ------------ ----------

workarea executions - optimal              2,258      100

workarea executions - onepass              0            0

workarea executions - multipass            0            0

Select name, value/1024/1024 VALUE_MB
from   V$PGASTAT
where name in ('aggregate PGA target parameter',
'total PGA allocated',
'total PGA inuse')
union
select name, value
from   V$PGASTAT
where name in ('over allocation count');

NAME    VALUE_MB
---------------   ----------------
aggregate PGA target parameter 471
over allocation count  0
total PGA allocated  33.8583984375
total PGA inuse   26.32421875

The optimal executions are performed entirely within the allocated memory areas. This is the most favorable type of execution. Sometimes, the operation is too big to perform within allocated memory area, and then some part of the operation spills on to disk. If only one-pass was needed on disk, then this execution is noted on one-pass statistics. If more than onepass was needed on disk then this execution is noted on multi-pass statistics. Ideally all execution should be in optimal statistics and the statistics for one-pass and multi-pass should be zero.

PGA_AGGREGATE_TARGET multipass executions indicate a RAM shortage, and you should always allocate enough RAM to ensure that at least 95% of connected tasks can acquire their RAM memory optimally. Thus DBA needs to increase this Parameter when "multipass" value is greater than ZERO and Reduce whenever the optimal executions are 100 percent.

SQL> select name, value from v$pgastat;

The following script provides excellent overall usage statistics for all Oracle9i connections.

NAME                                                   VALUE    

------------------------------------------------------ ----------

aggregate PGA target parameter                        284164096

aggregate PGA auto target                             235938816

global memory bound                                   14208000

total PGA inuse                                       25638912

total PGA allocated                                   35466240

maximum PGA allocated   1                             84498176

total freeable PGA memory                             0

PGA memory freed back to OS                           0

total PGA used for auto workareas                     3637248

maximum PGA used for auto workareas                   15818752

total PGA used for manual workareas                   0

maximum PGA used for manual workareas                 0

over allocation count                                 0

bytes processed                                       18302224384

extra bytes read/written                              4149905408

cache hit percentage                                  81.51

In the above display from v$pgastat we see the following statistics.

·         Aggregate PGA auto target - This column gives the total amount of available memory for Oracle9i connections. This value is derived from the value on the INIT.ORA parameter PGA_AGGREGATE_TARGET. 

·         Global memory bound - This statistic measures the max size of a work area, and Oracle recommends that whenever this statistics drops below 1MB, you should increase the value of the PGA_AGGREGATE_TARGET parameter.
·         Total PGA allocated - This statistic display the high-water mark of all PGA memory usage on the database. You should see this value approach the value of PGA_AGGREGATE_TARGET as usage increases.
·         Over allocation Count - If over allocation > 0 indicating that PGA_Target is too small to even meet the minimum PGA memory needs then you must increase the PGA_AGG_TARGET.
·         extra bytes read/write - Ideally it should be small if it is having large value you should increase the PGA_TARGET.

·         Cache hit percentage - A value of 100% means that all work are executed by the system since instance startup time have used an optimal amount of PGA memory. When it is not running optimally one or more extra parse is performed on input data which reduce the cache hit percentage.

1> If available memory >= optimal memory for 100% PGA cache hit %, set PGA_AGGREGATE_TARGET = optimal memory as in PGA_TARGET_ADVICE.

2> If available memory < optimal memory for 100% PGA cache hit %, set PGA_AGGREGATE_TARGET = available memory and settle for a lower PGA cache hit %.

·         Total PGA used for auto workareas - This statistic monitors RAM consumption or all connections that are running in automatic memory mode. Remember, not all internal processes may use the automatic memory feature. For example, Java and PL/SQL will allocate RAM memory, and this will not be counted in this statistic. Hence, we can subtract value to the total PGA allocated to see the amount of memory used by connections and the RAM memory consumed by Java and PL/SQL.

Estimated PGA memory for optimal/one-pass
This statistic estimates how much memory is required to execute all task connections RAM demands in optimal mode. Remember, when Oracle9i experienced a memory shortage, it invoked the multi-pass operation. This statistics is critical for monitoring RAM consumption in Oracle9i, and most Oracle DBA's will increase PGA_AGGREGATE_TARGET to this value.
SQL> 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;

TARGET_MB CACHE_HIT_PERC ESTD_OVERALLOC_COUNT

---------- -------------- --------------------

        34             95                      0

        68            100                     0

       136            100                    0

       203            100                    0

       271            100                    0

       325            100                    0

       379            100                    0

       434            100                    0

       488            100                    0

       542            100                    0

       813            100                    0

Set the value of PGA_AGG_TARGET to a value where we avoid any over allocation, so lower target value we can set 942 where allocation count is 0 as well as cache 100%.

SQL> SELECT LOW_OPTIMAL_SIZE/1024 low_kb, (HIGH_OPTIMAL_SIZE+1)/1024 high_kb,

     estd_total_executions estd_tot_exe,

     estd_optimal_executions estd_opt_cnt,

     estd_onepass_executions estd_onepass_cnt,

     estd_multipasses_executions estd_mpass_cnt

     FROM v$pga_target_advice_histogram

     WHERE pga_target_factor = 2

     AND estd_total_executions != 0

     ORDER BY 1;

    LOW_KB    HIGH_KB ESTD_TOT_EXE ESTD_OPT_CNT ESTD_ONEPASS_CNT ESTD_MPASS_CNT

---------- ---------- ------------ ------------ ---------------- --------------

         8         16         1721         1721                0              0

        16         32           61           61                0              0

        64        128            6            6                0              0

       128        256           22           22                0              0

       512       1024           19           19                0              0

      1024       2048           61           61                0              0

      2048       4096            2            2                0              0

You can use the content of above views to determine how key PGA statistics will be impacted if you change the value of PGA_AGGREGATE_TARGET.

To change the PGA_AGG_TARGET value:
Alter system set pga_aggregate_target=987758592;
After increasing the PGA check PGA_AGG_target again.
select * from v$pgastat;
Some mor dynamic views to check the PGA perfromace:
v$process, PGA_USED_MEM, PGA_ALLOC_MEM, PGA_FREEABLE_MEM and PGA_MAX_MEM
V$SQL_WORKAREA_HISTOGRAM;
V$SQL_WORKAREA_ACTIVE;
V$SQL_WORKAREA;
V$PGA_TARGET_ADVICE;
V$PGA_TARGET_ADVICE_HISTOGRAM
V$SYSSTAT
V$SESSTAT