DBA AWR System Time Summary

Description
Categories: Diagnostic Pack, Enginatics
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..

Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

Contact us to schedule a demo or if you need help with the installation

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