hi,
can you please also post information on the following:
a. Dynamic performance view:
select name,to_char(decode( unit,'bytes', value/1024/1024, value ),'999,999,999.9') value,
decode( unit, 'bytes', 'mbytes', unit ) unit
from v$pgastat
/
b. The effectiveness of the PGA workareas:
SELECT
case when low_optimal_size < 1024*1024
then to_char(low_optimal_size/1024,'999999') ||
'kb <= PGA < ' ||
(HIGH_OPTIMAL_SIZE+1)/1024|| 'kb'
else to_char(low_optimal_size/1024/1024,'999999') ||
'mb <= PGA < ' ||
(high_optimal_size+1)/1024/1024|| 'mb'
end pga_size,
optimal_executions,
onepass_executions,
multipasses_executions
from v$sql_workarea_histogram
where total_executions <> 0
order by low_optimal_size
/
C. For each PGA_AGGREGATE_TARGET size what the estimated PGA cache hit would be (all work done in memory gives 100%) and how many bytes it estimates would be read/written to temporary space with that size:
select
trunc(pga_target_for_estimate/1024/1024)
pga_target_for_estimate,
to_char(pga_target_factor * 100,'999.9') ||'%'
pga_target_factor,
trunc(bytes_processed/1024/1024) bytes_processed,
trunc(estd_extra_bytes_rw/1024/1024) estd_extra_bytes_rw,
to_char(estd_pga_cache_hit_percentage,'999') || '%'
estd_pga_cache_hit_percentage,
estd_overalloc_count
from v$pga_target_advice
/
that would give a clearer idea on your system.
kind regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
