Reports 2017-11-18T12:27:27+00:00

DBA AWR PGA History

Description
Categories: DBA, Enginatics
History of database PGA size in megabytes

select * from (
select
dhs.end_interval_time,
dhs.instance_number,
dhp.name,
round(dhp.value/1000000,2) value_
from
dba_hist_snapshot dhs,
dba_hist_pgastat dhp
where
1=1 and
dhs.dbid=(select vd.dbid from v$database vd) and
dhs.dbid=dhp.dbid and
dhs.instance_number=dhp.instance_number and
dhs.snap_id=dhp.snap_id
)
pivot (
sum(value_) val
for
name in (
'aggregate PGA target parameter' aggr_pga_target,
'aggregate PGA auto target' aggr_pga_auto_target,
'bytes processed' bytes_processed,
'global memory bound' glob_mem_bound,
'total PGA allocated' total_pga_alloc,
'maximum PGA allocated' max_pga_alloc,
'PGA memory freed back to OS' pga_freed_back,
'total PGA inuse' total_pga_inuse,
'total freeable PGA memory' total_freeable_pga,
'maximum PGA used for auto workareas' max_pga_used_auto_workareas,
'recompute count (total)' recompute_count,
'cache hit percentage' "CACHE_HIT_%",
'max process count' max_process_count,
'process count' process_count
)
)
order by
end_interval_time desc

Parameter Name SQL text Validation
Date From
dhs.begin_interval_time>=fnd_date.displaydt_to_date(:date_from)
LOV
Date To
dhs.begin_interval_time<=fnd_date.displaydt_to_date(:date_to)
LOV