How To Tune PGA_AGGREGATE_TARGET
----------------------------------
1- Make a first estimate for PGA_AGGREGATE_TARGET based on the following rule
- For OLTP systems
PGA_AGGREGATE_TARGET = (
- For DSS systems
PGA_AGGREGATE_TARGET = (
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- 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 vailable 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.
Oracle92 only:
(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.
Oracle92 only:
(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 (Oracle92 only)
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 inmulti-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.
-
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.
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.
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/22782597/viewspace-619619/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
