In continuation with my earlier article (Tuning PGA Part-I), in this article I will discuss how we can decide on an appropriate value of PGA_AGGREGATE_TARGET for our database.

When configuring a brand new instance, it is hard to know precisely the appropriate setting for PGA_AGGREGATE_TARGET. You can determine this setting in three stages:
1. Make a first estimate for PGA_AGGREGATE_TARGET. By default, Oracle Database uses 20% of the SGA size. However, this initial setting may be too low for a large DSS system.
2. Run a representative workload on the instance and monitor performance, using PGA statistics collected by Oracle Database, to see whether the maximum PGA size is under-configured or over-configured.
3. Tune PGA_AGGREGATE_TARGET, using Oracle PGA advice statistics.


The value of the PGA_AGGREGATE_TARGET initialization parameter  should be set based on the total amount of memory available for the Oracle database instance. This value can then be tuned and dynamically modified at the instance level. illustrates a typical situation.
Let total physical memory available on the system = 4 GB
Memory  left for the OS and other non-Oracle applications running on the same system = 20% i.e. .8 GB
Memory available for Oracle instance = 80% i.e. 3.2 GB
You must then divide the resulting memory between the SGA and the PGA.
For OLTP systems, the PGA memory = 20% i.e. 3.2 * .2 = 0.64 GB leaving 80% for the SGA.
For DSS systems running large, memory-intensive queries, PGA memory can
typically use up to 70% of that total i.e. 3.2 * 0.7 = 2.2 GB ).
Good initial values for the parameter PGA_AGGREGATE_TARGET might be:
For OLTP: PGA_AGGREGATE_TARGET = (total_mem * 80%) * 20%
For DSS: PGA_AGGREGATE_TARGET = (total_mem * 80%) * 70%
where total_mem is the total amount of physical memory available on the system.
In this example, with a value of total_mem equal to 4 GB, you can
initially set PGA_AGGREGATE_TARGET to 1600 MB for a DSS system and to
655 MB for an OLTP system.

Monitoring the Performance of the Automatic PGA Memory Management

Before starting the tuning process, you need to know how to monitor and
interpret the key statistics collected by Oracle Database to help in assessing the performance of the automatic PGA memory management component. Several dynamic performance views are available for this purpose:
This view gives instance-level statistics on the PGA memory usage and the
automatic PGA memory manager. Various columns of interest are:
over allocation count
: This statistic is cumulative from instance startup. Over-allocating PGA memory can happen if the value of PGA_AGGREGATE_TARGET is too small to accommodate the untunable  component of PGA  plus the minimum memory
required to execute the work area workload. When this happens, Oracle Database cannot honor the initialization parameter PGA_AGGREGATE_TARGET, and extra PGA memory must be allocated. If over-allocation occurs, you should increase the value of PGA_AGGREGATE_TARGET using the information provided by the advice view V$PGA_TARGET_ADVICE.
extra bytes read/written:
When a work area cannot run optimally, one or more extra passes is performed over the input data. extra bytesread/written represents the number of bytes processed during these extra passes since instance startup. This number is also used to compute thecache hit percentage. Ideally, it should be small compared to total bytes processed.
cache hit percentage:
This metric is computed by Oracle Database to reflect the performance of the PGA memory component. It is cumulative from instance startup. A value of 100% means that all work areas executed by the system since instance startup have used an optimal amount of PGA memory.  When a work area cannot run optimally, one or more extra passes is performed over the input data. This reduces the cache hit percentage in proportion to the size of the input data and the number of extra passes performed.
This view has one row for each Oracle process connected to the instance. The
columns PGA_USED_MEM, PGA_ALLOC_MEM, PGA_FREEABLE_MEM andPGA_MAX_MEM can be used to monitor the PGA memory usage of these processes.
This view shows the number of work areas executed with optimal memory size,
one-pass memory size, and multi-pass memory size since instance startup.
Statistics in this view are subdivided into buckets that are defined by the optimal memory requirement of the work area. Each bucket is identified by a range of optimal memory requirements specified by the values of the columns LOW_OPTIMAL_SIZE and HIGH_OPTIMAL_SIZE.
You can use this view to display the work areas that are active (or executing) in the instance. Small active sorts (under 64 KB) are excluded from the view. Use this view to precisely monitor the size of all active work areas and to determine if these active work areas spill to a temporary segment.
Oracle Database maintains cumulative work area statistics for each loaded
cursor whose execution plan uses one or more work areas. Every time a work area is deallocated, the V$SQL_WORKAREA table is updated with execution statistics for that work area.


To help you tune the initialization parameter PGA_AGGREGATE_TARGET, Oracle Database provides the V$PGA_TARGET_ADVICE  view.
This 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. A typical query of this view:
SELECT round(PGA_TARGET_FOR_ESTIMATE/1024/1024) target_mb,
       ESTD_PGA_CACHE_HIT_PERCENTAGE cache_hit_perc,
1.  If over allocation count > 0, it indicates that PGA_AGGREGATE_TARGET is too small to even meet the minimum PGA memory needs. If PGA_AGGREGATE_TARGET is set within the over allocation zone, the memory manager will over-allocate memory and actual PGA memory consumed will be more than the limit you set. Set PGA_AGGREGATE_TARGET so that there is no memory over-allocation; avoid setting it in the over-allocation zone.
2. After eliminating over-allocations, aim at maximizing the PGA cache hit percentage, based on your response-time requirement and memory constraints.
   – If available memory >= optimal memory for 100% PGA cache hit %,
        set PGA_AGGREGATE_TARGET = optimal memory as in PGA_TARGET_ADVICE ,
         if AMM is enabled
             Rest of the available memory will be allocated to SGA
              Rest of the available memory will be returned to Operating system
   – If available memory < optimal memory for 100% PGA cache hit %,
        set PGA_AGGREGATE_TARGET = available memory and settle for a lower PGA cache hit %.
This view predicts how the statistics displayed by the performance view will
be impacted if you change the value of the initialization parameter PGA_AGGREGATE_TARGET. You can use the dynamic view V$PGA_TARGET_ADVICE_HISTOGRAM to view detailed information on the predicted number of optimal, one-pass and multi-pass work area executions for the set of PGA_AGGREGATE_TARGET values you use for the prediction.
Statistics in the V$SYSSTAT and V$SESSTAT views show the total number of work areas executed with optimal memory size, one-pass memory size, and
multi-pass memory size. These statistics are cumulative since the instance or the session was started.
The following query gives the total number and the percentage of times work areas were executed in these three modes since the instance was started:
SELECT name profile, 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%’);

In the third and last article on PGA tuning, I will demonstrate both manual and automatic tuning of PGA.

Related links:

Tuning Index
Tuning  PGA : Part – I
Tuning  PGA : Part – III


Your comments and suggestions are welcome!