PGA advice
Oracle 9i has introduced a 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.
Here is a related script to see your PGA sizing:
SET LINESIZE 200
set pages 1000
COLUMN username FORMAT A20
COLUMN module FORMAT A20
SELECT
a.inst_id,
NVL(a.username,'(oracle)') AS username,
a.module,
a.program,
Trunc(b.value/1024) AS memory_kb
FROM
gv$session a,
gv$sesstat b,
gv$statname
c
WHERE
a.sid = b.sid
AND
a.inst_id = b.inst_id
AND
b.statistic# = c.statistic#
AND
b.inst_id = c.inst_id
AND
c.name = 'session pga memory'
AND
a.program IS NOT
NULL
ORDER BY b.value DESC;
