Tuning PGA Memory
April 22, 2003by Sreeram Surapaneni
Oracle is becoming one of the most flexible and sophisticated database management systems. Its ability to provide performance views for memory management components of the database is an invaluable tool especially for managing DSS systems. With it, DBA`s can more easily ensure that all the settings for Oracle memory utilization are optimal for their applications. Process Global Area, often known as the Program Global Area (PGA) is a dedicated area of Oracle memory used by individual processes to perform memory intensive functions, such as sorting or hash join. In previous releases of Oracle the PGA is configured by carefully adjusting a number of initialization parameters, such as, SORT_AREA_SIZE, HASH_AREA_SIZE, BITMAP_MERGE_AREA_SIZE, and CREATE_BITMAP_AREA_SIZE, etc. Oracle9i provides an option to completely automate the management of PGA memory. Oracle professionals merely need to specify the maximum amount of PGA memory available to an instance using a newly introduced initialization parameter PGA_AGGREGATE_TARGET.
When an Oracle process requires an operation, such as a sort , hash join or group by it goes to the shared tunable memory area within pga_aggregate_target region and attempts to obtain enough contiguous memory structures to perform the operation. This shared turnable memory part can be shrunk/expanded in function of the system load. If the process is able to acquire these memory structures immediately, it is marked as an optimal memory access. If the memory acquisition requires a single pass through pga_aggregate_target, the memory allocation is marked as one pass. If all memory is in use, Oracle may have to make multiple passes through pga_aggregate_target to acquire the memory. Multipass executions indicate a memory shortage, and you should always allocate enough PGA memory to ensure that at least 95 percent of connected tasks can acquire their memory optimally.
To illustrate this consider the following queries
column name format a40 column value format 999,999,999 select name, value from v$pgastat;
The result of the query might look like the following:
NAME VALUE -------------------------------------------------------- aggregate PGA target parameter 524288000 bytes aggregate PGA auto target 463435776 bytes global memory bound 25600 bytes total PGA inuse 9353216 bytes total PGA allocated 73516032 bytes maximum PGA allocated 698371072 bytes total PGA used for auto workareas 0 bytes maximum PGA used for auto workareas 560744448 bytes total PGA used for manual workareas 0 bytes maximum PGA used for manual workareas 0 bytes over allocation count 0 bytes bytes processed 4.0072E+10 bytes extra bytes read/written 3.1517E+10 bytes cache hit percentage 60.02 percent
Important parameter to look at in the above output is the value of aggregate PGA auto target, which represents shared tunable part of PGA memory and it should not be small compared to the value of PGA_AGGREGATE_TARGET. Oracle 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.You must ensure that enough PGA memory is left for work areas running in automatic mode for memory intensive operations. The other untunable memory part of PGA memory contains context information for each session, for each open/active cursor, PL/SQL or Java memory. The other parameter in the above ouput to look at is over allocation count which means the number of times Oracle did not honor the parameter pga_aggregate_target. This happens when PGA memory is too small and Oracle is unable to accommodate the untunable PGA memory part plus the minimum memory required to execute the work area workload. Ideally, this value should be zero. Parameter cache hit percentage in the above output means that percentage of work areas that has used an optimal amount PGA memory during execution since instance start-up.
To substantiate this consider the following query:
col c1 heading 'Workarea|Profile' format a35 col c2 heading 'Count' format 999,999,999 col c3 heading 'Percentage' format 99 select name c1,count c2,decode(total, 0, 0, round(count*100/total)) c3 from ( select name,value count,(sum(value) over ()) total from v$sysstat where name like 'workarea exec%' );
PROFILE CNT PERCENTAGE ----------------------------- ---------- ---------- workarea executions - optimal 5395 98 workarea executions - onepass 284 2 workarea executions - multipass 0 0
To determine how key PGA statistics will be impacted if you change the value of PGA_AGGREGATE_TARGET, can be illustrated with the following example. The following select statement can be used to find this information
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
From the above results we should set the PGA_AGGREGATE_TARGET parameter to a value where we avoid any over allocation, so lowest PGA_AGGREGATE_TARGET value we can set is 375 (where ESTD_OVERALLOC_COUNT is 0). After eliminating over-allocations, the goal is to maximize the PGA cache hit Percentage based on your response-time requirement and memory constraints.Restrictions: 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.
Sreeram Surapaneni is an Oracle Certifited DBA (7.3,8,8i,9i) working currently as DBA, Research In Motion Ltd, Canada and can be reached at Sreeram Surapaneni.