The V$PGASTAT view provides instance-level statistics about PGA memory usage and the automatic PGA memory manager.

The following example shows a query of this view.

SELECT *
  FROM V$PGASTAT;

The output of this query might look like the following:

NAME                                                          VALUE UNIT
-------------------------------------------------------- ---------- ------------
aggregate PGA target parameter                             41156608 bytes
aggregate PGA auto target                                  21823488 bytes
global memory bound                                         2057216 bytes
total PGA inuse                                            16899072 bytes
total PGA allocated                                        35014656 bytes
maximum PGA allocated                                     136795136 bytes
total freeable PGA memory                                    524288 bytes
PGA memory freed back to OS                              1713242112 bytes
total PGA used for auto workareas                                 0 bytes
maximum PGA used for auto workareas                         2383872 bytes
total PGA used for manual workareas                               0 bytes
maximum PGA used for manual workareas                       8470528 bytes
over allocation count                                           291
bytes processed                                          2124600320 bytes
extra bytes read/written                                   39949312 bytes
cache hit percentage                                          98.15 percent

Table 16-1 describes the main statistics shown in the V$PGASTAT view.

PGA other = total PGA allocated - total PGA used for auto workareas

Example 16-1 shows how extra passes affect the cache hit percentage metric.

The V$PROCESS view contains one row for each Oracle process connected to the database instance. Use the following columns in this view to monitor the PGA memory usage of these processes:

• PGA_USED_MEM

• PGA_ALLOC_MEM

• PGA_FREEABLE_MEM

• PGA_MAX_MEM

Example 16-2 shows a query of this view.

The V$PROCESS_MEMORY view displays dynamic PGA memory usage by named component categories for each Oracle process. This view contains up to six rows for each Oracle process, one row for:

• Each named component category:

  • Java

  • PL/SQL

  • OLAP

  • SQL

• Freeable

  Memory that has been allocated to the process by the operating system, but not to a specific category

• Other

  Memory that has been allocated to a category, but not to a named category

Use the following columns in this view to dynamically monitor the PGA memory usage of Oracle processes for each of the six categories:

• CATEGORY

• ALLOCATED

• USED

• MAX_ALLOCATED

The V$SQL_WORKAREA_HISTOGRAM view shows the number of work areas executed with optimal, one-pass, and multi-pass memory size since instance startup. Statistics in this view are divided into buckets. The buckets are defined by the optimal memory requirements of the work areas. Each bucket is identified by a range of optimal memory requirements, specified by the values in the LOW_OPTIMAL_SIZE and HIGH_OPTIMAL_SIZE columns.

For example, a sort operation may require 3 MB of memory to run in optimal size (cached). Statistics about the work area used by this sort operation are placed in the bucket defined by:

• LOW_OPTIMAL_SIZE = 2097152 (2 MB)

• HIGH_OPTIMAL_SIZE = 4194303 (4 MB minus 1 byte)

Statistics are segmented by work area size, because the performance impact of running a work area in optimal, one-pass or multi-pass size depends mainly on the size of the work area. In this example, statistics about the work area are placed in this bucket because 3 MB lies within that range of optimal sizes.

Example 16-3 and Example 16-4 show two methods for querying this view.

The V$WORKAREA_ACTIVE view displays the work areas that are active (or executing) in the database instance. Small, active sort operations (under 64 KB) are excluded from this view. Use this view to precisely monitor the size of all active work areas and to determine whether these active work areas spill to a temporary segment.

Example 16-5 shows a query of this view.

Oracle Database maintains cumulative work area statistics for each loaded cursor whose execution plan uses one or more work areas. Each time a work area is deallocated, the V$SQL_WORKAREA view is updated with execution statistics for that work area.

You can join the V$SQL_WORKAREA view with the V$SQL view to relate a work area to a cursor, and with the V$SQL_PLAN view to precisely determine which operator in the plan uses a work area.

Example 16-6 shows three queries of this view.

Oracle Database generates the V$PGA_TARGET_ADVICE and V$PGA_TARGET_ADVICE_HISTOGRAM views by recording the workload history, and then simulating this history for different values of the PGA_AGGREGATE_TARGET parameter. The values of the PGA_AGGREGATE_TARGET parameter are derived from fractions and multiples of its current value to assess possible higher and lower values. These values are used for the prediction and range from 10 MB to a maximum of 256 GB. 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 these views.

To enable automatic generation of PGA performance advice views:

1. Set the PGA_AGGREGATE_TARGET parameter to enable automatic PGA memory management.

   Setting this parameter to 0 disables automatic PGA memory management and is not recommended. For information about setting this parameter, see "Setting the Initial Value for PGA_AGGREGATE_TARGET".

2. Set the STATISTICS_LEVEL parameter to TYPICAL (the default) or ALL.

   Setting this parameter to BASIC disables generation of the PGA performance advice views and is not recommended.

The V$PGA_TARGET_ADVICE view predicts how changing the value of the PGA_AGGREGATE_TARGET initialization parameter will affect the following statistics in the V$PGASTAT view:

• cache hit percentage

• over allocation count

The following example shows a query of this view.

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 following figure illustrates how the result of this query can be plotted.

Figure 16-1 Graphical Representation of V$PGA_TARGET_ADVICE

Description of "Figure 16-1 Graphical Representation of V$PGA_TARGET_ADVICE"

The curve shows how PGA cache hit percentage improves as the value of the PGA_AGGREGATE_TARGET parameter increases. The shaded zone in the graph represents the over allocation zone, where the value of the ESTD_OVERALLOCATION_COUNT column is non-zero. This area indicates that the value of the PGA_AGGREGATE_TARGET parameter is too small to meet the minimum PGA memory requirements. If the value of the PGA_AGGREGATE_TARGET parameter is set within the over allocation zone, then the memory manager will over-allocate memory and the actual PGA memory consumed will exceed the limit that was set. It is therefore meaningless to set a value of the PGA_AGGREGATE_TARGET parameter in that zone. In this particular example, the PGA_AGGREGATE_TARGET parameter should be set to at least 375 MB.

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 optimal or one-pass work areas and a decrease in the number of multi-pass executions. At some point, around 500 MB in this example, an inflection in the curve corresponds to the point where most (probably all) work areas can run in optimal or at least one-pass size. Beyond this point, the cache hit percentage keeps increasing, though at a lower pace, up to the point where it starts to taper off and only slight improvement is achieved with increase in the value of the PGA_AGGREGATE_TARGET parameter. In the figure, this happens when PGA_AGGREGATE_TARGET reaches 3 GB. At this point, the cache hit percentage is 83% and only marginal improvement (by 2%) is achieved with one extra gigabyte of PGA memory. In this example, 3 GB is the optimal value for the PGA_AGGREGATE_TARGET parameter.

Ideally, the value of the PGA_AGGREGATE_TARGET parameter should be set to 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. In this example, the value of the PGA_AGGREGATE_TARGET parameter should be set to at least 500 MB, and as close to 3 GB as possible. However, the correct setting for the PGA_AGGREGATE_TARGET parameter depends on how much memory can be dedicated to the PGA component. Generally, adding PGA memory requires reducing memory for some SGA components—like the shared pool or buffer cache—because the overall memory dedicated to the database instance is often bound by the amount of physical memory available on the system. Therefore, 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 can monitor with shared pool advisory and buffer cache advisory statistics). If you cannot reduce memory from the SGA components, consider adding more physical memory to the system.

The V$PGA_TARGET_ADVICE_HISTOGRAM view predicts how changing the value of the PGA_AGGREGATE_TARGET initialization parameter will affect the statistics in the V$SQL_WORKAREA_HISTOGRAM view. Use this view to display detailed information about the predicted number of optimal, one-pass, and multi-pass work area executions for the PGA_AGGREGATE_TARGET values used 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 the PGA_AGGREGATE_TARGET parameter.

Example 16-7 shows a query of this view that displays the predicted content of the V$SQL_WORKAREA_HISTOGRAM view for a value of the PGA_AGGREGATE_TARGET parameter set to twice its current value.

Statistics in the V$SYSSTAT and V$SESSTAT views show the total number of work areas executed with optimal, one-pass, and multi-pass memory size. These statistics are cumulative since the instance or the session was started.

Example 16-8 shows a query of the V$SYSSTAT view that displays the total number and the percentage of times work areas were executed in these three sizes since the instance was started:

This tutorial provides a guideline for tuning the value of the PGA_AGGREGATE_TARGET parameter using the various views discussed in this chapter.

To tune PGA_AGGREGATE_TARGET:

1. Set the value of the PGA_AGGREGATE_TARGET parameter to avoid memory over-allocation.

   Use the V$PGA_TARGET_ADVICE view to ensure that the PGA_AGGREGATE_TARGET value is not set within the over-allocation zone, as described in "Using the V$PGA_TARGET_ADVICE View". In Example 16–8, the PGA_AGGREGATE_TARGET value should be set to at least 375 MB.

2. Maximize the PGA cache hit percentage, based on response time requirements and memory constraints.

   Use the V$PGA_TARGET_ADVICE view to determine the optimal value for the PGA_AGGREGATE_TARGET parameter and set its value to the optimal value, or to the maximum value possible, as described in "Using the V$PGA_TARGET_ADVICE View".

   Assume a limit X on the memory that can be allocated to PGA:

   • If limit X is higher than the optimal value, set the value of the PGA_AGGREGATE_TARGET parameter to the optimal value.

     In Example 16–8, if you have 10 GB to dedicate to PGA, set the value of the PGA_AGGREGATE_TARGET parameter to 3 GB and dedicate the remaining 7 GB to the SGA.

   • If limit X is less than the optimal value, set the value of the PGA_AGGREGATE_TARGET parameter to X.

     In Example 16–8, if you have only 2 GB to dedicate to PGA, set the value of the PGA_AGGREGATE_TARGET parameter to 2 GB and accept a cache hit percentage of 75%.

3. Verify that the new value of the PGA_AGGREGATE_TARGET parameter will result in the desired number of optimal and one-pass work area executions and avoid any multi-pass work area executions.

   Use the V$PGA_TARGET_ADVICE_HISTOGRAM view to predict the number of optimal, one-pass, and multi-pass work area executions, as described in "Using the V$PGA_TARGET_ADVICE_HISTOGRAM View".

4. If more PGA memory is required, then increase PGA memory by either reducing memory from SGA components or adding more physical memory to the system.

5. At any time, ensure the number of optimal, one-pass, and multi-pass work area executions matches predictions and tune the value of the PGA_AGGREGATE_TARGET parameter if necessary.

   Use the V$SYSSTAT and V$SESSTAT views to verify the total number of work areas executed with optimal, one-pass, and multi-pass memory size since instance or session startup, respectively, as described in "Using the V$SYSSTAT and V$SESSTAT Views".