Question: I need to understand the
v$memory_target_advice view and see how it is used to size my
pga_aggregate_target and memory_target parameters.
Answer: For tuning the PGA memory within Oracle 11g, either the memory
advisors can be used as shown previously or automatic memory
management can be deployed to tune the sizes of the PGA memory
buffers via the v$pga_target_advice or query v$memory_target_advice as shown in the figure
listed below.
SQL> select pga_target_for_estimate,
pga_target_factor, estd_time
2 from v$pga_target_advice;
PGA_TARGET_FOR_ESTIMATE
PGA_TARGET_FACTOR ESTD_TIME
----------------------- ----------------- ----------
20447232 .125 8754
40894464 .25 8754
81788928 .5 8754
122683392 .75 8754
163577856 1 8754
196292608 1.2 8754
229008384 1.4 8754
261724160 1.6 8754
294439936 1.8 8754
327155712 2 8754
490733568 3 8754
Tuning the size of the PGA using the
above view for
v$pga_target_advice operates in a similar fashion
to that of the SGA query (
v$sga_target_advice) in that one needs
to look at the PGA_TARGET_FACTOR column and compare to the
ESTD_TIME column from the query result to determine the best fit
size for the PGA with Oracle 11g.
Another option is to experiment with the new 11g dynamic
performance view v$memory_target_advice as shown below.
SQL> select * from
v$memory_target_advice;
MEMORY_SIZE MEMORY_SIZE_FACTOR
ESTD_DB_TIME ESTD_DB_TIME_FACTOR VERSION
----------- ------------------ ------------ -------------------
----------
380 1 2283
1 3
475 1.25 2284
1 3
570 1.5 2222
.9729 3
665 1.75 2222
.9729 3
760 2 2222
.9729 3
So what can be ascertained from the output of the above query
with v$memory_target_advice is that with a memory size between
570 megabytes and 665 megabytes, there is about the same performance estimate for
the memory_target parameter.
This is beneficial because you can avoid wasting memory allocation
by using 570 megabytes instead of the larger 760 megabytes maximum value
available.
PGA
target advice
Oracle 9i
first introduced the new advisory utility dubbed
v$pga_target_advice. This utility will show the marginal changes in
optimal, one-pass, and multipass PGA execution for different sizes of
pga_aggregate_target, ranging from 10% to 200% of the current value.
-- ************************************************
-- Display pga target advice
-- ************************************************
column c1 heading 'Target(M)'
column c2 heading 'Estimated|Cache Hit %'
column c3 heading 'Estimated|Over-Alloc.'
SELECT
ROUND(pga_target_for_estimate /(1024*1024)) c1,
estd_pga_cache_hit_percentage c2,
estd_overalloc_count c3
FROM
v$pga_target_advice;
Here is
the output.
Estimated Estimated
Target(M) Cache Hit % Over-Alloc.
---------- ----------- -----------
188 93 0
376 95 0
752 96 0
1128 97 0
1504 98 0
1805 98 0
2106 98 0
2406 98 0
2707 98 0
3008 98 0
4512 98 0
6016 98 0
9024 98 0
12032 98 0
pga_target_advice_histogram.sql
-- ************************************************
-- Display pga target advice histogram
-- ************************************************
SELECT
low_optimal_size/1024 "Low(K)",
(high_optimal_size+1)/1024 "High(K)",
estd_optimal_executions "Optimal",
estd_onepass_executions "One Pass",
estd_multipasses_executions "Multi-Pass"
FROM
v$pga_target_advice_histogram
WHERE
pga_target_factor = 2
AND
estd_total_executions != 0
ORDER BY
1;
Low(K) High(K) Optimal One Pass Multi-Pass
---------- ---------- ---------- ---------- ----------
8 16 19218271 0 0
16 32 8190 0 0
32 64 1787 0 0
64 128 1274804 0 0
128 256 204 0 0
256 512 267 0 0
512 1024 3271389 0 0
1024 2048 260 0 0
2048 4096 247 0 0
4096 8192 166 0 0
8192 16384 876 0 0
16384 32768 1492 0 0
32768 65536 2506 0 0
65536 131072 180 41 0
131072 262144 11 102 0
262144 524288 9 13 0
524288 1048576 2 33 0
As we can see,
these utilities make it easy to size your PGA regions in Oracle. If you
want more details on PGA sizing you might enjoy my latest book “Oracle Tuning: The Definitive Reference” by Rampant TechPress.
|
|
