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

Friday Mar 14th 2003 by Jim Czuprynski
Share:

Sizing the Program Global Area (PGA) is now one of many tasks made simpler under Oracle 9i. Learn how a database server utilizes the PGA, how the PGA can be automatically sized using PGA_AGGREGATE_TARGET initialization parameter, and how the PGA's size can be monitored and tuned using some new dynamic database views, now part of 9i.

Synopsis. Sizing the Program Global Area (PGA) is now one of many tasks made simpler under Oracle 9i. This article discusses how a database server utilizes the PGA, how the PGA can be automatically sized using PGA_AGGREGATE_TARGET initialization parameter, and how the PGA's size can be monitored and tuned using some new dynamic database views now part of 9i.

As Oracle DBAs, we typically spend considerable time planning, sizing, and monitoring the System Global Area (SGA) because of its importance to our databases' performance. However, the Program Global Area (PGA) can be equally important to throughput and performance because it's where database applications do much of their work.

Recently I had the chance to delve into the PGA's inner workings during a conversion of several Oracle 8i databases to the Oracle 9iR2 environment. I found that under 9i, it is even easier to monitor what is going on inside the PGA, and with the new Automatic PGA Memory Management features of 9i, Oracle now manages sizing for dedicated sessions. (We'll talk about how the PGA is managed for shared sessions a bit later.)

First, some details. Our current production database uses Oracle 9iR2 in a Windows 2000 Advanced Server environment. The database server needs to support a minimum of 200 dedicated connections for our primary Powerbuilder client-server applications. The database also supports a few dozen shared connections for some web and reporting applications, but the majority of the production server is dedicated for online transaction processing (OLTP). Since the database has to manage a considerable number of dedicated connections for OLTP processing, the PGA needed to be configured for maximum efficiency.

PGA Architecture: Work Areas

Oracle defines the PGA as a private memory region containing data and control information for a server process. Essentially, it is where the run time version of the code that is being executed is stored temporarily--for example, the runtime area of a cursor.

Complex queries--for example, ones that use a lot of sorting (GROUP BY, ORDER BY, ROLLUP), or whose query access plans utilize hash joins, bitmap merges, and bitmap creates--tend to allocate a large portion of this runtime area for work areas for these memory-intensive operations. In addition, bulk-loading operations that require large write buffers need large work areas.

Essentially, how a work area is sized determines the efficiency and speed of the query. For the best results the work area for, say, a large query with a lot of sorting should be large enough that all its input data and auxiliary memory structures created by its SQL operators will fit inside that work area. Oracle terms this the optimal size of a work area.

So, what happens when the work area's size is exceeded? Response time increases because the server process has to make an extra pass over the input data (termed the one-pass size of the work area). Moreover, if the work area is exceedingly small, the server process has to make multiple passes over the work area (termed the multi-pass size of the work area). Tuning the work areas in the PGA so that the query runs within the optimal size of the work area eliminates these additional passes over the input data, insuring the query runs faster and uses PGA resources more efficiently.

Automatic PGA Memory Management

Before Oracle 9i, the maximum size of work areas was based on values set for the SORT_AREA_SIZE, HASH_AREA_SIZE, BITMAP_MERGE_AREA_SIZE, and CREATE_BITMAP_AREA_SIZE initialization parameters. Since the optimal work area size is ideally based on the size of the input data and the number of work areas already active, it is difficult to set these parameters for optimum performance for every situation and point in time.

However, with the new automatic PGA memory management features of Oracle 9i, the allocation of optimum sizes for work areas is handed over to the DBMS itself. In fact, for dedicated sessions, the initialization parameter settings mentioned previously are ignored. Instead, Oracle uses the initialization parameter PGA_AGGREGATE_TARGET to derive appropriate memory allocations for work areas sizes.

The Oracle DBMS attempts to honor the target value set by PGA_AGGREGATE_TARGET at all times. The total amount of PGA memory available to the instance's active work areas is automatically derived and is equal to the value of PGA_AGGREGATE_TARGET less the total PGA memory in use by other system components (for example, PGA memory used by sessions). It accomplishes this by dynamically controlling the amount of memory allocated to SQL work areas while simultaneously maximizing the number of optimally-sized work areas. Remaining non-optimally sized work areas are executed in one-pass mode, unless PGA_AGGREGATE_TARGET has been set too low; in that case, multi-pass mode will be used for those work areas.

Jim Czuprynski

Using PGA_AGGREGATE_TARGET To Activate Automatic Memory Management

To activate automatic PGA memory management, the PGA_AGGREGATE_TARGET initialization parameter must be set to a non-zero value (Oracle defaults its setting to zero unless overridden). However, just as we would do with tuning the SGA, Oracle recommends setting an initial value, evaluating the results over time, and then tuning the PGA based on those results.

The recommended initial setting for PGA_AGGREGATE_TARGET depends on whether the database is primarily being used for OLTP processing, which use relatively small amounts of PGA memory, or for decision support systems (DSS), which typically handle much larger, long-running, memory intensive queries. In either case, Oracle suggests using no more than 80% of the total memory available for the database instance to allow enough memory for other non-Oracle applications running on the server.

In my case, my server had 8GB available memory for the instance. For an OLTP-based server, Oracle recommends allocating 20% to the PGA, so PGA_AGGREGATE_TARGET would be set to approximately 1310MB ((8192 MB x 80%) x 20%). For a DSS-based server, Oracle recommends a factor of at least 50%, or approximately 3276MB (8192MB x 80%) x 50%). During my testing, I hedged my bet by quite a bit, knowing that my database server is almost totally dedicated to OLTP, and initially allocated 400MB for PGA_AGGREGATE_TARGET.

After applying the change to my database's INIT.ORA file and restarting the database, I confirmed the results by querying V$PGASTAT. This is a new dynamic view available with Oracle 9i and is useful for obtaining instance-level statistics about PGA memory usage and how well automatic memory management is working:

SQL> SELECT NAME, VALUE FROM v$pgastat;

NAME			VALUE
------------------------------------- 	----------
aggregate PGA target parameter	419430400
aggregate PGA auto target		361433088
global memory bound		20971520
total PGA inuse		17833984
total PGA allocated		34810880
maximum PGA allocated		124318720
total freeable PGA memory		0
PGA memory freed back to OS	0
total PGA used for auto workareas	0
maximum PGA used for auto workareas	60204032
total PGA used for manual workareas	0
maximum PGA used for manual workareas	246784
over allocation count		0
total bytes processed		5866293248
extra bytes read/written		720095232
cache hit percentage		89.06

The statistics returned explain what's going on inside the PGA. Here is a breakout of the more important ones, according to Oracle:

  • aggregate PGA target parameter shows the actual value set for PGA_AGGREGATE_TARGET (in this case, 400MB). This parameter confirms if automatic PGA memory management has been activated--if it hasn't been, then this value will be zero.

  • The Oracle DBMS dynamically derives the value for aggregate PGA auto target from the value set for PGA_AGGREGATE_TARGET and is continuously adjusted by Oracle. It is the amount of memory that can be used for work areas running in automatic mode. If this value is small, it generally indicates that other components of the system--for example, PL/SQL or Java memory--are using a lot of PGA, leaving little behind for work areas to be managed in automatic mode.

  • global memory bound shows the maximum size of a work area executed in automatic mode. The value is constantly adjusted by Oracle based on the current state of the work area workload and generally decreases when the number of active work areas increase. Oracle recommends that this value should never reach 1MB; if it does, it's probably an indicator that PGA_AGGREGATE_TARGET should be increased.

  • total PGA allocated yields the total amount of PGA memory that Oracle has allocated for the instance, while total PGA used for auto workareas tells how much memory is in use by other processes like PL/SQL or Java. Subtracting the second number from the first yields the total PGA memory used by these other processes.

  • over allocation count tells how much PGA memory has been over-allocated cumulatively since the instance was started. If the value returned is anything over zero, it is an indication that the size of PGA_AGGREGATE_TARGET should be increased because it means that Oracle could not honor at least one request for additional PGA work areas.

  • total bytes processed represents how many bytes were processed since instance startup, while extra bytes read/written represents how many bytes were processed via one-pass or multi-pass processing. These two values are used to calculate the cache hit percentage based on the following formula: (100 * total bytes processed) / (total bytes processed + extra bytes read/written).

Jim Czuprynski

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.

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved