Activating, Monitoring, and Tuning Automatic PGA Memory Management via PGA_AGGREGATE_TARGET Under Oracle 9i - Page 3

March 14, 2003

Monitoring PGA Memory Usage

Once enough processing cycles have elapsed for the database, it is time to determine if the initial PGA size was underestimated (or perhaps even overestimated). Oracle 9i provides several dynamic views for monitoring how well the database is managing PGA memory allocations.

Querying V$PROCESS lets us see how Oracle processes are utilizing PGA memory, especially what PGA memory is in use, how much has been allocated, and what's the high-water mark for PGA memory for the process:

SELECT
   program,
   pga_used_mem "PGA Used",
   pga_alloc_mem "PGA Alloc",
   pga_max_mem "PGA Max"
  FROM v$process;

PROGRAM	 	PGA Used	PGA Alloc   PGA Max
----------------	----------- -----------	----------
ORACLE.EXE		132568	198925	198925
...

V$SQL_WORKAREA_HISTOGRAM contains information about how many work areas have been executed with optimal, one-pass, and multi-pass memory size since the instance was started within a range of work area sizes, defined by LOW_OPTIMAL_SIZE and HIGH_OPTIMAL_SIZE:

SELECT
   low_optimal_size/1024 "Low (K)",
   (high_optimal_size + 1)/1024 "High (K)",
   optimal_executions "Optimal",
   onepass_executions "1-Pass",
   multipasses_executions ">1 Pass"
  FROM v$sql_workarea_histogram
 WHERE total_executions <> 0;

   Low (K)   High (K)    Optimal     1-Pass    >1 Pass
---------- ---------- ---------- ---------- ----------
        16         32     155213          0          0
        32         64       1240          0          0
        64        128        651         42          0
       128        256         86          0          0
       256        512         90          0          0
       512       1024       1444          0          0
      1024       2048         52          0          0
      2048       4096         42          0          0
      4096       8192         14          0          0
      8192      16384         12          0          0
     16384      32768          3          0          0
     32768      65536          1          6          0
     65536     131072          0          6          0

The results from this query show that almost all of the PGA work areas are sized optimally, except for a few in the 64K to 128K range, and a handful of larger queries in the 32M to 128M range.

Yet another query against this view summarizes the number of times work areas were executed in optimal, one-pass, and multi-pass modes since the instance was started. Note that this query looks at all work areas; smaller work areas can be screened out by adding the appropriate selection criteria:

SELECT
   optimal_count "Optimal", 
   round(optimal_count * 100 / total,2) "Optimal %",
   onepass_count "OnePass",
   round(onepass_count * 100 / total,2) "Onepass %",
   multipass_count "MultiPass", 
   round(multipass_count * 100 / total,2) "Multipass %"
  FROM (
      SELECT
         DECODE (SUM(total_executions), 0, 1, SUM(total_executions)) total,
         SUM(optimal_executions) optimal_count,
         SUM(onepass_executions) onepass_count,
         SUM(multipasses_executions) multipass_count
        FROM v$sql_workarea_histogram
-- Limits consideration of queries with LOW_OPTIMAL_SIZE limit <64K 
-- WHERE low_optimal_size > 64*1024);

Optimal Optimal % OnePass Onepass % MultiPass Multipass %
------- --------- ------- --------- --------- -----------
 158848     99.97      54      0.03         0           0

These results reveal some good news--the majority of work areas are being executed in optimal mode, with a very small number requiring one-pass or multi-pass execution. Since my database is primarily used for OLTP, this is not surprising; however, if it were used for DSS, I'd expect a lower optimal percentage.

The V$SQL_WORKAREA view allows us to take a more detailed look at the work areas themselves. Here is a sample query that returns the first 20 work areas with the largest optimal sizes:

SELECT *
  FROM (SELECT
            workarea_address,
            operation_type,
            policy,
            estimated_optimal_size
          FROM v$sql_workarea
      ORDER BY estimated_optimal_size)
 WHERE ROWNUM <= 20;

WORKAREA_ADDRESS OPERATION_TYPE       POLICY     ESTIMATED_OPTIMAL_SIZE
---------------- -------------------- ---------- ----------------------
40328AE0         SORT                 AUTO                        19456
4626B5F8         SORT                 AUTO                        19456
4626B5A0         SORT                 AUTO                        19456
4F50F318         GROUP BY (SORT)      MANUAL                      19456
46273298         SORT                 AUTO                        19456
4F96B514         GROUP BY (SORT)      AUTO                        19456
433B1C9C         GROUP BY (SORT)      AUTO                        19456
4C3401D8         SORT                 AUTO                        19456
41C446B8         SORT                 AUTO                        19456
425B1208         SORT                 AUTO                        19456
40D2160C         GROUP BY (SORT)      AUTO                        19456
44AF763C         GROUP BY (SORT)      AUTO                        19456
3FF5E1E4         GROUP BY (SORT)      AUTO                        19456
3FF5E23C         BUFFER               AUTO                        19456
412EA56C         GROUP BY (SORT)      AUTO                        19456
4EC0CD74         BUFFER               AUTO                        19456
42AA3470         GROUP BY (SORT)      AUTO                        19456
412C3580         GROUP BY (SORT)      AUTO                        19456
4D521A98         SORT                 AUTO                        19456
473CD39C         SORT                 AUTO                        19456

Note that the address column of V$SQL_WORKAREA can be joined to V$SQL.address to obtain the actual SQL statements that are being executed in the work area. This can be helpful in tracking back a SQL query to its work area and determining if it's running in optimal, one-pass, or multi-pass mode.

Tuning PGA Memory Usage

Once a baseline has been established, PGA sizing can be tuned by consulting another useful view, V$PGA_TARGET_ADVICE. This view shows how the current setting for the PGA compares to its optimal usage historical usage, and offers advice on what would happen if the PGA was increased or decreased in size by looking at PGA statistics since the instance was last started. Here is a sample query:

SELECT
   ROUND(pga_target_for_estimate /(1024*1024)) "Target (M)",
   estd_pga_cache_hit_percentage "Est. Cache Hit %",
   estd_overalloc_count "Est. Over-Alloc"
  FROM V$PGA_TARGET_ADVICE;

Target (M) Est. Cache Hit % Est. Over-Alloc
---------- ---------------- ---------------
        50               62              36
       100               85               3
       200               86               0
       300               87               0
       400               88               0
       480               88               0
       560               88               0
       640               88               0
       720               88               0
       800               88               0
      1200               93               0
      1600              100               0
      2400              100               0
      3200              100               0

This result set shows that by increasing the size of PGA_AGGREGATE_TARGET beyond my initial estimate of 400MB to 1200MB, the database would eventually improve its performance significantly beyond the estimated cache hit ratio. This is not surprising, since the original allocation formula suggested a value of approximately 1310MB for OLTP database usage. Of more interest, however, is that there is very little additional gain in allocating more than 1200MB to PGA_AGGREGATE_TARGET.

Oracle acknowledges that there is a point of diminishing return when almost all work areas are executed in optimal or one-pass mode. Therefore, Oracle recommends concentrating any tuning efforts upon first insuring that PGA_AGGREGATE has been set high enough that there is no memory over-allocation (see the previous V$PGASTAT query for more information).

Finally, be sure that the STATISTICS_LEVEL initialization parameter has been set to either TYPICAL (the default) or ALL. If this parameter is set to BASIC, the V$PGA_TARGET_ADVICE view is disabled.

Notes on SHARED connections

Finally, it is important to remember that automatic PGA memory management via PGA_AGGREGATE_TARGET only applies to dedicated server connections. Memory allocation for shared server connections is still managed by SORT_AREA_SIZE, HASH_AREA_SIZE, and so forth.

References and Additional Reading

While there is no substitute for direct experience, reading the manual is not a bad idea, either. I have drawn upon the Oracle documentation in Chapter 14 of Oracle 9i Database Performance Tuning Guide and Reference (A996533-01) for much of the technical details of this article.

Jim Czuprynski is an Oracle DBA for a telecommunications company in Schaumburg, IL. He can be contacted at jczuprynski@zerodefectcomputing.com.