Automatic PGA Memory Management
-------------------------------------------------
Process Global Area,
often known as the Program Global
Area (PGA) resides in the process private memory of the server process. It contains global variables
and data structures and control information for a server process. example of
such information is the runtime area of a cursor. Each time a cursor is
executed, a new runtime area is created for that cursor in the PGA memory
region of the server process executing that cursor.
The performance of complex long running queries, typical in
a DSS environment ,depend to a large extent on the memory available in the
Program Global Area (PGA).which is also called work area. The size of a work area can be controlled and tuned.
Generally, bigger work areas can significantly improve the performance of a
particular operator at the cost of higher memory consumption . Ideally, the
size of a work area is big enough that it can accommodate the input data and
auxiliary memory structures allocated by its associated SQL operator. This is
known as the optimal size of a work area
(e.g.a memory sort). When the size of the work area is smaller than optimal (e.g.
a disk sort), the response time increases, because an extra pass is performed
over part of the input data. This is known as the one-pass size of the work area. Under the one-pass threshold, when
the size of a work area is far too small compared to the input data size,
multiple passes over the input data are needed. This could dramatically
increase the response time of the operator. This is known as the multi-pass size of the work area.
In Oracle8i administrators sized the PGA 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.
Starting with Oracle9i, an option is provided to completely
automate the management of PGA memory.
Administrators merely need to specify the maximum amount of PGA memory
available to an instance using a newly introduced initialization parameter PGA_AGGREGATE_TARGET.
The database server automatically distributes this memory
among various active queries in an intelligent manner so as to ensure maximum
performance benefits and the most efficient utilization of memory. Furthermore,
Oracle9i and newer releases can adapt itself to changing workload thus
utilizing resources efficiently regardless of the load on the system. The
amount of the PGA memory available to an instance can be changed dynamically by
altering the value of the PGA_AGGREGATE_TARGET parameter making it possible to
add to and remove PGA memory from an active instance online. Since the database
engine itself is better equipped to determine SQL execution memory requirements,
database administrators should use this feature and not try to tune the PGA
manually. This should translate to better throughput for large number of users
on the system as well as improved response time for queries.
The automatic SQL execution memory management feature is
enabled by setting the parameter WORKAREA_SIZE_POLICY
to AUTO and by specifying a size of PGA_AGGREGATE
_TARGET in the initialization file. These two parameters can also be set
dynamically using the ALTER SYSTEM command. In the absence of either of these parameters,
the database will revert to manual PGA management mode. In Oracle9i Release 2,
an advisory for PGA_AGGREGATE_TARGET was introduced. Just like in Buffer Cache
Advisory, the PGA Advisory will suggest the appropriate size for PGA memory and
thus make PGA tuning an even simpler task.
Version specific notes:
Until 9iR2, PGA_AGGREGATE_TARGET parameter controls the
sizing of workareas for all dedicated server connections, but it has no effect
on shared servers (aka MTS) connections and the *_AREA_SIZE parameters will
take precedence in this case.
In 10g, PGA_AGGREGATE_TARGET
controls workareas allocated by both dedicated and shared connections.
As of 11g, Automatic Memory
Management (AMM) expands to managing both SGA and PGA memory. Under memory pressure for PGA memory, SGA
memory will be re-allocated for use by a process to accommodate workarea
needs. On the flip-side, if PGA memory
is under allocated, memory can be added to the auto-tuned components in the SGA
beyond the original SGA configuration.
NOTE: With AMM, setting an explicit value for
PGA_AGGREGATE_TARGET will act as a minimum setting that AMM will not shrink
below.
See Note:443746.1 for more information.
How To Tune PGA_AGGREGATE_TARGET
----------------------------------
The first question we will have when we set this parameter
is what is the best value for it?
To determine the appropriate setting for PGA_AGGREGATE_TARGET
parameter we recommend to follow the following steps
1- Make a first estimate for
PGA_AGGREGATE_TARGET based on the following rule
- For OLTP systems
PGA_AGGREGATE_TARGET = (<Total
Physical Memory > * 80%) * 20%
- For DSS systems
PGA_AGGREGATE_TARGET = (<Total Physical Memory > * 80%) * 50%
So for example, if we have an Oracle
instance configured on system with 16G of Physical memory, then the suggested
PGA_AGGREGATE_TARGET parameter value we should start with incase we have OLTP
system is (16 G * 80%)*20% ~= 2.5G and incase we have DSS system is (16 G *
80%)* 50% ~= 6.5 G.
In the above equation, we assume
that 20% of the memory will be used by the OS, and in OLTP system 20% of the
remaining memory will be used for PGA_AGGREGATE_TARGET and the remaining memory is going for Oracle
SGA memory and non-oracle processes memory. So make sure that you have enough memory for your SGA and also for
non-oracle processes
2- A second step in tuning the
PGA_AGGREGATE_TARGET is to monitor performance using available PGA statistics
and see if PGA_AGGREGATE_TARGET is under sized or over sized. Several dynamic
performance views are available for this purpose:
-
V$PGASTAT
This view provides instance-level
statistics on the PGA memory usage and the automatic PGA memory manager. For
example:
SELECT *
FROM V$PGASTAT;
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
total bytes processed 4.0072E+10 bytes
total extra bytes read/written 3.1517E+10 bytes
cache hit percentage 55.97 percent
Main statistics to look at
(a) AGGREGATE PGA AUTO TARGET : This gives the amount of PGA
memory Oracle can use for work areas running in automatic mode. This part of
memory represent the tunable part of PGA memory,i.e. memory allocated for
intensive memory SQL operators like sorts, hash-join, group-by, bitmap merge
and bitmap index create. This memory part can be shrinked/expanded in function
of the system load. Other parts of PGA memory are known as untunable, i.e. they
require a size that can't be negociated (e.g.
context information for each session, for each open/active cursor, PL/SQL or
Java memory).
So, the aggregate PGA auto target
should not be small compared to the value of PGA_AGGREGATE_ TARGET. You must ensure that
enough PGA memory is left for work areas running in automatic mode.
(b) TOTAL
PGA USED FOR AUTO WORKAREA: This gives the actual tunable PGA memory used by the
system. The 'maximum PGA used for auto workareas' gives the maximum reached by
previous statistic since instance startup.
(c) TOTAL PGA IN USED: This gives the total PGA memory in
use. The detail of this value can be found in the PGA_USED_MEM column of the
v$process view.
(d) OVER ALLOCATION COUNT:
Over-allocating PGA memory can happen if the value of PGA_AGGREGATE _ TARGET is
too small to accommodate the untunable PGA memory part plus the minimum memory
required to execute the work area workload. When this happens, Oracle cannot honor the initialization
parameter PGA_AGGREGATE_TARGET, and extra PGA memory needs to be allocated.
over allocation count is the number of time the system was detected in this
state since database startup. This count should ideally be equal to zero.
(e) CACHE HIT PERCENTAGE: This metric is
computed by Oracle to reflect the performance of the PGA memory component. It
is cumulative from instance start-up. A value of 100% means that all work areas
executed by the system since instance start-up have used an optimal amount of
PGA memory. This is, of course, ideal but rarely happens except maybe for pure
OLTP systems. In reality, some work areas run one-pass or even multi-pass, depending on the overall size of the PGA
memory. When a work area cannot run optimally, one or more extra passes is
performed over the input data. This reduces the cache hit percentage in
proportion to the size of the input data and the number of extra passes
performed. this value if computed from the "total bytes processed" and
"total extra bytes read/written" statistics available in the same
view using the following formula:
total bytes
processed * 100
PGA Cache Hit Ratio =
------------------------------------------------------
(total bytes processed + total
extra bytes read/written)
- V$SQL_WORKAREA_HISTOGRAM
This view shows the number of work
areas executed with optimal memory size, one- pass memory size, and multi-pass
memory size since instance start-up. Statistics in this view are subdivided
into buckets that are defined by the optimal memory requirement of the work
area. Each bucket is identified by a range of optimal memory requirements specified by the values of
the columns LOW_OPTIMAL_SIZE and HIGH_OPTIMAL_SIZE.
Example :
The following query shows statistics
for all nonempty buckets.
SELECT
LOW_OPTIMAL_SIZE/1024 LOW_KB,(HIGH_OPTIMAL_SIZE+1)/1024 HIGH_KB,
OPTIMAL_EXECUTIONS, ONEPASS_EXECUTIONS,
MULTIPASSES_EXECUTIONS FROM V$SQL_WORKAREA_HISTOGRAM WHERE TOTAL_EXECUTIONS != 0;
The result of the query might look like the following:
LOW_KB HIGH_KB OPTIMAL_EXECUTIONS ONEPASS_EXECUTIONS
MULTIPASSES_EXECUTIONS
------ ------- ------------------ ------------------
----------------------
8 16 156255 0 0
16 32 150 0 0
32 64 89 0 0
64 128 13 0 0
128 256 60 0 0
256 512 8 0 0
512 1024 657 0 0
1024 2048 551 16 0
2048 4096 538 26 0
4096 8192 243
28 0
8192 16384 137 35 0
16384 32768 45 107 0
32768 65536 0 153 0
65536 131072 0 73 0
131072 262144 0 44 0
262144 524288 0 22 0
The query result shows that, in the
1024 KB to 2048 KB bucket, 551 work areas used an optimal amount of memory,
while 16 ran in one-pass mode and none ran in multi-pass mode. It also shows
that all work areas under 1 MB were able to run in optimal mode.
You
can also use V$SQL_WORKAREA_HISTOGRAM to find the percentage of times work areas were executed in optimal, one-pass, or
multi-pass mode since start-up.
Example :
SELECT
OPTIMAL_COUNT, ROUND(OPTIMAL_COUNT*100/TOTAL, 2) OPTIMAL_PERC, ONEPASS_COUNT,
ROUND(ONEPASS_COUNT*100/TOTAL, 2) ONEPASS_PERC, MULTIPASS_COUNT, ROUND(MULTIPASS_COUNT*100/TOTAL,
2) MULTIPASS_PERC
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
WHERE
LOW_OPTIMAL_SIZE > 64*1024); ---- for 64 K optimal size
- V$SQL_WORKAREA_ACTIVE
This view can be used to display the
work areas that are active (or executing) in the instance. Small active sorts (under 64
KB) are excluded from the view. Use this
view to precisely monitor the size of all active work areas and to determine if these active work areas spill to
a temporary segment.
Example :
SELECT
TO_NUMBER(DECODE(SID, 65535, NULL, SID)) SID,
OPERATION_TYPE OPERATION,TRUNC(EXPECTED_SIZE/1024)
ESIZE,
TRUNC(ACTUAL_MEM_USED/1024) MEM,
TRUNC(MAX_MEM_USED/1024) "MAX MEM",
NUMBER_PASSES PASS,
TRUNC(TEMPSEG_SIZE/1024) TSIZE
FROM
V$SQL_WORKAREA_ACTIVE
ORDER
BY 1,2;
SID OPERATION
ESIZE MEM MAX MEM
PASS TSIZE
--- ----------------- --------- --------- --------- -----
-------
8 GROUP BY
(SORT) 315 280
904 0
8 HASH-JOIN 2995
2377 2430 1
20000
9 GROUP BY
(SORT) 34300 22688
22688 0
11 HASH-JOIN 18044
54482 54482 0
12 HASH-JOIN 18044
11406 21406 1
120000
This output shows that session 12
(column SID) is running a hash-join having its work area running in one-pass
mode (PASS column). This work area is currently using 11406 KB of memory (MEM
column) and has used, in the past, up to 21406 KB of PGA memory (MAX MEM
column). It has also spilled to a temporary segment of size 120000 KB. Finally,
the column ESIZE indicates the maximum amount of memory that the PGA memory
manager expects this hash-join to use. This maximum is dynamically computed by
the PGA memory manager according to workload.
When a work area is deallocated—that
is, when the execution of its associated SQL operator is complete—the work area
is automatically removed from the V$SQL_WORKAREA_ACTIVE
view.
- Note: 148346.1 have some other queries we use to
monitor SQL execution memory
3- The Third and last step is tuning
the PGA_AGGREGATE_TARGET. In Oracle 9i Release 2 we have 2 new views that help
us in this task
- V$PGA_TARGET_ADVICE
- V$PGA_TARGET_ADVICE_HISTOGRAM
By examining these two views, you
will be able to determine how key PGA statistics will be impacted if you change
the value of PGA_AGGREGATE_TARGET.
To enable automatic generation of
PGA advice performance views, make sure the
following parameters are set:
- PGA_AGGREGATE_TARGET
- 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. NOTE: PGA_AGGREGATE can change automatically over
time starting with 11g as part of the Automatic Memory Management enhancements
available at 11g. See Note:443746.1 for more details.
V$PGA_TARGET_ADVICE 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.
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.
V$PGA_TARGET_ADVICE_HISTOGRAM 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.
