To help you tune the initialization parameter PGA_AGGREGATE_TARGET, Oracle provides two PGA advice performance views:
■ V$PGA_TARGET_ADVICE
■ V$PGA_TARGET_ADVICE_HISTOGRAM
By examining these two views, you no longer need to use an empirical approach to tune the value of PGA_AGGREGATE_TARGET. Instead, you can use the content of these views to determine how key PGA statistics will be impacted if you change the value of PGA_AGGREGATE_TARGET.
In both views, values of PGA_AGGREGATE_TARGET used for the prediction are derived from fractions and multiples of the current value of that parameter, to assess possible higher and lower values. Values used for the prediction range from 10 MB to a maximum of 256 GB.
Oracle generates PGA advice performance views by recording the workload history and then simulating this history for different values of PGA_AGGREGATE_TARGET. The simulation process happens in the background and continuously updates the
workload history to produce the simulation result. You can view the result at any time by querying V$PGA_TARGET_ADVICE or V$PGA_TARGET_ADVICE_HISTOGRAM.
To enable automatic generation of PGA advice performance views, make sure the following parameters are set:
■ PGA_AGGREGATE_TARGET, to enable automatic PGA memory management. Set the initial value as described in "Setting PGA_AGGREGATE_TARGET Initially" on page 7-39.
■ STATISTICS_LEVEL. Set this to TYPICAL (the default) or ALL; setting this parameter to BASIC turns off generation of PGA performance advice views.
The content of these PGA advice performance views is reset at instance start-up or when PGA_AGGREGATE_TARGET is altered.
V$PGA_TARGET_ADVICE 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. Example 7–8 shows a typical query of this view:
Note: Simulation cannot include all factors of real execution, so derived statistics might not exactly match up with real performance statistics. You should always monitor the system after changing PGA_AGGREGATE_TARGET, to verify that the new performance is what you expect.
PGA Memory Management
Example 7–8 Querying V$PGA_TARGET_ADVICE
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
The result of the this query can be plotted as shown in Figure 7–3:
PGA Memory Management
Figure 7–3 Graphical Representation of V$PGA_TARGET_ADVICE
The curve shows how the PGA cachehitpercentage improves as the value of PGA_AGGREGATE_TARGET increases. The shaded zone in the graph is the over allocation zone, where the value of the column ESTD_OVERALLOCATION_COUNT is nonzero. 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. It is therefore meaningless to set a value of PGA_AGGREGATE_TARGET in that zone. In this particular example PGA_
AGGREGATE_TARGET should be set to at least 375 MB.
Cache Hit Percentage
85.00 80.00 75.00 70.00
65.00 60.00 55.00 50.00 45.00 40.00 35.00 30.00 25.00 20.00
15.00 10.00 5.00 0.00
0 500MB 1GB 1.5GB 2GB
PGA_AGGREGATE_TARGET
2.5GB 3GB 3.5GB 4GB
Optimal Value
Current setting
PGA Memory Management
Beyond the over allocation zone, the value of the PGA cache hit percentage increases rapidly. This is due to an increase in the number of work areas which run optimally or one-pass and a decrease in the number of multi-pass executions. At some point, somewhere around 500 MB in this example, there is an inflection in the curve that corresponds to the point where most (probably all) work areas can run optimally or at least one-pass. After this inflection, the cache hit percentage keeps
increasing, though at a lower pace, up to the point where it starts to taper off and shows only slight improvement with increase in PGA_AGGREGATE_TARGET. In Figure 7–3, this happens when PGA_AGGREGATE_TARGET reaches 3 GB. At that point, the cache hit percentage is 83% and only improves marginally (by 2%) with one extra gigabyte of PGA memory. In this particular example, 3 GB is probably the optimal value for PGA_AGGREGATE_TARGET.
Ideally, PGA_AGGREGATE_TARGET should be set at the optimal value, or at least to the maximum value possible in the region beyond the over allocation zone. As a rule of thumb, the PGA cache hit percentage should be higher than 60%, because at 60% the system is almost processing double the number of bytes it actually needs to process in an ideal situation. Using this particular example, it makes sense to set PGA_
AGGREGATE_TARGET to at least 500 MB and as close as possible to 3 GB. But the right setting for the parameter PGA_AGGREGATE_TARGET really depends on how much memory can be dedicated to the PGA component. Generally, adding PGA memory requires reducing memory for some of the SGA components, like the shared pool or the buffer cache. This is because the overall memory dedicated to the Oracle instance is often bound by the amount of physical memory available on the system. As a result, any decisions to increase PGA memory must be taken in the larger context of the available memory in the system and the performance of the various SGA components (which you monitor with shared pool advisory and buffer cache advisory statistics). If memory cannot be taken away from the SGA, you might consider adding more physical memory to the system.
How to Tune PGA_AGGREGATE_TARGET You can use the following steps as a tuning guideline in tuning PGA_AGGREGATE_TARGET:
1. Set PGA_AGGREGATE_TARGET so there is no memory over-allocation; avoid setting it in the over-allocation zone. In Example 7–8, PGA_AGGREGATE_TARGET should be set to at least 375 MB.
2. After eliminating over-allocations, aim at maximizing the PGA cachehit
percentage, based on your response-time requirement and memory constraints.
In Example 7–8, assume you have a limit X on memory you can allocate to PGA.
Note: Even though the theoretical maximum for the PGA cache hit percentage is 100%, there is a practical limit on the
maximum size of a work area, which may prevent this theoretical maximum from being reached, even if you further increase PGA_
AGGREGATE_TARGET. This should happen only in large DSS systems where the optimal memory requirement is large and might cause the value of the cache hit percentage to taper off at a lower percentage, like 90%.
See Also:
■ "Shared Pool Advisory Statistics" on page 7-24
■ "Sizing the Buffer Cache" on page 7-6
PGA Memory Management
■ If this limit X is beyond the optimal value, then you would set PGA_
AGGREGATE_TARGET to the optimal value. After this point, the incremental benefit with higher memory allocation to PGA_AGGREGATE_TARGET is very small. In Example 7–8, if you have 10 GB to dedicate to PGA, set PGA_
AGGREGATE_TARGET to 3 GB, the optimal value. The remaining 7 GB is dedicated to the SGA.
■ If the limit X is less than the optimal value, then you would set PGA_
AGGREGATE_TARGET to X. In Example 7–8, if you have only 2 GB to dedicate to PGA, set PGA_AGGREGATE_TARGET to 2 GB and accept a cache hit percentage of 75%.
Finally, like most statistics collected by Oracle that are cumulative since instance start-up, you can take a snapshot of the view at the beginning and at the end of a time interval. You can then derive the predicted statistics for that time interval as follows:
estd_overalloc_count = (difference in estd_overalloc_count between the two snapshots)
(difference in bytes_processed between the two snapshots) estd_pga_cache_hit_percentage = ---
(difference in bytes_processed + extra_bytes_rw between the two snapshots )
V$PGA_TARGET_ADVICE_HISTOGRAM This view predicts how the statistics displayed by the performance view V$SQL_WORKAREA_HISTOGRAM 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.
The V$PGA_TARGET_ADVICE_HISTOGRAM view is identical to the V$SQL_
WORKAREA_HISTOGRAM view, with two additional columns to represent the PGA_
AGGREGATE_TARGET values used for the prediction. Therefore, any query executed against the V$SQL_WORKAREA_HISTOGRAM view can be used on this view, with an additional predicate to select the desired value of PGA_AGGREGATE_TARGET.
Example 7–9 Querying V$PGA_TARGET_ADVICE_HISTOGRAM
The following query displays the predicted content of V$SQL_WORKAREA_HISTOGRAM for a value of the initialization parameter PGA_AGGREGATE_TARGET set to twice its current value.
SELECT LOW_OPTIMAL_SIZE/1024 low_kb, (HIGH_OPTIMAL_SIZE+1)/1024 high_kb, 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;
The output of this query might look like the following.
LOW_KB HIGH_KB ESTD_OPTIMAL_CNT ESTD_ONEPASS_CNT ESTD_MPASS_CNT --- --- -- --
PGA Memory Management
512 1024 653 0 0 1024 2048 530 0 0 2048 4096 509 0 0 4096 8192 227 0 0 8192 16384 176 0 0 16384 32768 133 16 0 32768 65536 66 103 0 65536 131072 15 47 0 131072 262144 0 48 0 262144 524288 0 23 0 The output shows that increasing PGA_AGGREGATE_TARGET by a factor of 2 will allow all work areas under 16 MB to execute in optimal mode.