 |
|
Oracle Tips by Burleson |
pga_aggregate_target in
Oracle9i
In Oracle9i, the sort and hash area parameters such as
sort_area_size, hash_area_size and their associated multi-block read
parameters can be turned over to Oracle for management by using the
pga_aggregate_target and workarea_size_policy parameters.
The pga_aggregate_target parameter is set to the size of memory for
the projected aggregate PGA, including sort area and context areas,
for all users, and the workarea_size_policy parameter is set to AUTO
to turn on automated tuning.
However, how does the DBA know if these are set correctly? Oracle
provides the v_pga_target_advice DPT to offer guidance as to the
appropriateness of the settings. By using a simple select, such as
the one in the script below, it can easily be seen if the
pga_aggregate_target value is set correctly.
* pga_advice.sql
-- *************************************************
-- Copyright © 2005 by Rampant TechPress
-- This script is free for non-commercial purposes
-- with no warranties. Use at your own risk.
--
-- To license this script for a commercial purpose,
-- contact info@rampant.cc
-- *************************************************
ttitle 'PGA Target Advice Report'
set lines 80 pages 47
spool pga_advice
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
/
spool off
ttitle off
The output from the above SQL select will resemble the following:
10/09/03 PGA Target Advice Report Page 1
TARGET_MB CACHE_HIT_PERC ESTD_OVERALLOC_COUNT
---------- -------------- --------------------
13 31 2046
25 31 1999
38 32 926
50 34 591
60 36 461
70 37 353
80 38 251
90 38 168
100 39 100
150 45 8
200 47 0
300 58 0
400 59 0
13 rows selected.
The above report reveals that the over-allocation values indicate
that the setting is too small and should be increased to at least
150 megabytes.
The above book excerpt is from:
Oracle
Tuning Power Scripts
With 100+ High Performance
SQL Scripts
Oracle In-Focus Series
ISBN
0-9744486-7-2
Mike Ault, Donald K.
Burleson. Harry Conway
http://www.rampant-books.com/book_2005_1_power_tuning.htm |
