James's Blog

Oracle: setup sga_aggregate_targate

- in oracle9i, it intruduced a new feature sga_aggreate_targate/workarea_size_policy

- sga_aggregate_targate will define how much memory will be allocated to sga for all sessions. if there're more user session, each session will be allocated less memory. if there're less user sessions, each session will be allocated more memory.

- this eliminate tunning on hash_area_size, sort_area_size and so on.

- how to set :
set workarea_size_policy to auto
set sga_aggregate_targate to 16% of physical memory on oltp and 40% of memory on dss system.

- how measure the performance
select * from v$pgastat
over allocation count should be 0
cache hit percent should be 100
select name,value from v$sysstat where name like 'workarea exe%'
one pass and multi pass should be zero

- advisor feature in 9iR2
v$pga_target_advice

- how to query v$pga_target_advice
to eliminate over_allocate:
select round(pga_target_for_estimate/1024/1024) as target_size_MB,
bytes_processed,estd_extra_bytes_rw as est_rw_extra_bytes,
estd_pga_cache_hit_percentage as est_hit_pct,
estd_overalloc_count as est_overalloc
from v$pga_target_advice;

to eliminate extra reads/writes
select round(pga_target_for_estimate/1024/1024) as target_size_MB,
bytes_processed,estd_extra_bytes_rw as est_rw_extra_bytes,
estd_pga_cache_hit_percentage as est_hit_pct,
estd_overalloc_count as est_overalloc
from v$pga_target_advice;

posted by James Pei @ 10:44 AM