DBA AWR System Time Summary

➜ Back to Library
Description
Categories: Diagnostic Pack, Enginatics, Kcapps
Repository: Github
Historic system time model values from the automated workload repository showing how the database time was spent e.g. on excuting SQL, PL/SQL or Java code, parsing statements etc..
Run DBA AWR System Time Summary and other Oracle EBS reports with Blitz Report™ on our demo environment
select
*
from
(
select
&pivot_time1
y.instance_number,
y.stat_name,
&elapsed_time
case when y.stat_name in ('active sessions','active CPU sessions') then to_char(y.delta/1000000/y.time_interval,'fm999g990d00') else xxen_util.time(nvl(y.delta/1000000,0)) end time_
from
(
select distinct
&pivot_time2
x.instance_number,
x.stat_name,
greatest(0, max(x.value) over (partition by x.stat_name, x.dbid, x.instance_number &pivot_partition2)-
&delta_prev_value) delta,
sum(x.time_interval) over (partition by x.stat_name, x.dbid, x.instance_number &time_partition_by) time_interval
from
(
select
to_char(xxen_util.client_time(dhs.end_interval_time),'Day') day_of_week,
xxen_util.client_time(dhs.end_interval_time) end_interval_time,
(cast(dhs.end_interval_time as date)-cast(dhs.begin_interval_time as date))*86400 time_interval,
dhstm.dbid,
dhstm.instance_number,
case
when dhstm.stat_name='parse time elapsed' then 'soft parse elapsed time'
when dhstm.stat_name='background elapsed time' then 'background elapsed other'
when dhstm.stat_name='background cpu time' then 'background cpu other'
else dhstm.stat_name end stat_name,
case
when dhstm.stat_name='parse time elapsed' then greatest(0,dhstm.value-sum(case when dhstm.stat_name in ('hard parse elapsed time','failed parse elapsed time') then dhstm.value end) over (partition by dhstm.snap_id,dhstm.dbid,dhstm.instance_number))
when dhstm.stat_name='background elapsed time' then greatest(0,dhstm.value-sum(case when dhstm.stat_name='background cpu time' then dhstm.value end) over (partition by dhstm.snap_id,dhstm.dbid,dhstm.instance_number))
when dhstm.stat_name='background cpu time' then greatest(0,dhstm.value-sum(case when dhstm.stat_name='RMAN cpu time (backup/restore)' then dhstm.value end) over (partition by dhstm.snap_id,dhstm.dbid,dhstm.instance_number))
else dhstm.value end value
from
dba_hist_snapshot dhs,
(
select dhstm.dbid, dhstm.instance_number, dhstm.snap_id, dhstm.stat_name, dhstm.value from dba_hist_sys_time_model dhstm union all
select dhstm.dbid, dhstm.instance_number, dhstm.snap_id, decode(dhstm.stat_name,'DB time','active sessions','active CPU sessions') stat_name, dhstm.value from dba_hist_sys_time_model dhstm where dhstm.stat_name in ('DB time','DB CPU')
) dhstm
where
1=1 and
dhstm.stat_name not in (
'hard parse (sharing criteria) elapsed time',
'hard parse (bind mismatch) elapsed time',
'failed parse (out of shared memory) elapsed time'
) and
dhs.snap_id=dhstm.snap_id and
dhs.dbid=dhstm.dbid and
dhs.instance_number=dhstm.instance_number
) x
) y
order by nvl(y.delta,0) desc
)
&pivot
Parameter Name SQL text Validation
Date From 
dhs.end_interval_time>=xxen_util.server_time(fnd_date.displaydt_to_date(:date_from))-1/1440
 LOV
Date To 
dhs.begin_interval_time<=xxen_util.server_time(fnd_date.displaydt_to_date(:date_to))
 LOV
Display Mode 
pivot (
max(time_) " "
for
stat_name in (
'active sessions' active_sessions,
'active CPU sessions' active_cpu_sessions,
'DB time' db_time,
'DB CPU' cpu_time,
'sql execute elapsed time' sql_execute,
'PL/SQL execution elapsed time' plsql_execution,
'inbound PL/SQL rpc elapsed time' inbound_plsql_rpc,
'Java execution elapsed time' java_execution,
'hard parse elapsed time' hard_parse,
'failed parse elapsed time' failed_parse,
'soft parse elapsed time' soft_parse,
'repeated bind elapsed time' repeated_bind,
'connection management call elapsed time' conn_management,
'PL/SQL compilation elapsed time' plsql_compilation,
'sequence load elapsed time' sequence_load,
'background elapsed other' background_elapsed_other,
'background cpu other' background_cpu_other,
'RMAN cpu time (backup/restore)' rman_cpu
)
)
order by
end_interval_time desc,
instance_number
 LOV
Diagnostic Pack enabled 
1=1
 LOV
Container Data 
dhs.dbid=(select vd.dbid from v$database vd)
 LOV
Download
 
Related Blitz Reports
DBA AWR System Wait Time Summary DBA AWR System Time Percentages DBA AWR Blocking Session Summary DBA AWR Active Session History DBA AWR SQL Performance Summary DBA AWR Settings DBA SGA+PGA Memory Configuration DBA AWR System Metrics Summary DBA AWR System Wait Event Summary
Blitz Report™

Blitz Report™ provides multiple benefits: