DBA AWR System Time Summary

Description
Categories: Diagnostic Pack, Enginatics, Toolkit - DBA
Repository: Github Columns: Day Of Week, End Interval Time, Instance Number, Sql Execute, Plsql Execution, Inbound Plsql Rpc, Java Execution, Hard Parse, Failed Parse, Soft Parse ...
Historic system time model values from the automated workload repository showing a breakdown of how the database time was spent e.g. on excuting SQL, PL/SQL or Java code, parsing statements etc..
select
*
from
(
select
&pivot_time1
y.instance_number,
y.stat_name,
&elapsed_time
xxen_util.time(nvl(y.delta/1000000,0)) time_
from
(
select distinct
&pivot_time2
x.instance_number,
x.stat_name,
greatest(0, max(x.value) over (partition by x.stat_id, x.dbid, x.instance_number &pivot_partition2)-
&delta_prev_value) delta
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,
dhstm.stat_id,
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,
dba_hist_sys_time_model dhstm
where
1=1 and
dhs.dbid=(select vd.dbid from v$database vd) and
dhstm.stat_name not in (
'DB time',
'DB CPU',
'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>=fnd_date.displaydt_to_date(:date_from)-1/1440
LOV
Date To
dhs.begin_interval_time<=fnd_date.displaydt_to_date(:date_to)
LOV
Diagnostic Pack enabled
1=1
LOV
Display Mode
pivot (
max(time_) " "
for
stat_name in (
'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
Display Mode
pivot (
max(time_) " "
for
stat_name in (
'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
Display Mode
min(x.value) over (partition by x.stat_id, x.dbid, x.instance_number, trunc(x.end_interval_time))
Display Mode
lag(x.value) over (partition by x.stat_id, x.dbid, x.instance_number order by x.end_interval_time)
Display Mode
min(x.value) over (partition by x.stat_id, x.dbid, x.instance_number)
Display Mode
nvl(y.delta/1000000,0) elapsed_time,
Display Mode
partition by y.end_interval_time
Display Mode
partition by y.end_interval_time
Display Mode
, trunc(x.end_interval_time)
Display Mode
, x.end_interval_time
Display Mode
y.day_of_week, y.end_interval_time,
Display Mode
y.day_of_week, y.end_interval_time,
Display Mode
x.day_of_week, trunc(x.end_interval_time) end_interval_time,
Display Mode
x.day_of_week, x.end_interval_time,