DBA AWR PGA History

➜ Back to Library
Description
Categories: Diagnostic Pack, Enginatics, Toolkit - DBA
Repository: Github Columns: End Interval Time, Instance Number, Aggr Pga Target Val, Aggr Pga Auto Target Val, Bytes Processed Val, Glob Mem Bound Val, Total Pga Alloc Val, Max Pga Alloc Val, Pga Freed Back Val, Total Pga Inuse Val ...
History of database PGA size and other statistics from v$pgastat in megabytes
select * from (
select
xxen_util.client_time(dhs.end_interval_time) end_interval_time,
dhs.instance_number,
dhp.name,
case when dhp.name in (
'recompute count (total)',
'cache hit percentage',
'max processes count',
'process count'
) then dhp.value else dhp.value/1000000 end 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 processes count' max_process_count,
'process count' process_count
)
)
order by
end_interval_time desc,
instance_number
Parameter NameSQL textValidation
Date From
dhs.begin_interval_time>=xxen_util.server_time(fnd_date.displaydt_to_date(:date_from))
LOV
Date To
dhs.begin_interval_time<=xxen_util.server_time(fnd_date.displaydt_to_date(:date_to))
LOV
Diagnostic Pack enabled
1=1
LOV
Download
 
Related Blitz Reports
DBA SGA+PGA Memory Configuration DBA AWR System Wait Time Summary DBA AWR Blocking Session Summary DBA AWR Active Session History DBA AWR Latch Summary DBA AWR System Wait Class by Time DBA AWR SQL Performance Summary DBA AWR System Wait Event Summary DBA AWR System Metrics Summary
Blitz Report In Action
Blitz Report™