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

DBA AWR System Time Summary

Description
Categories: DBA, Enginatics
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.stat_name,
&elapsed_time
xxen_util.time(nvl(y.delta/1000000,0)) time_
from
(
select distinct
&pivot_time2
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(dhs.end_interval_time,'Day') day_of_week,
dhs.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
Time Split
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
LOV
Time Split
lag(x.value) over (partition by x.stat_id, x.dbid, x.instance_number order by x.end_interval_time)
Time Split
min(x.value) over (partition by x.stat_id, x.dbid, x.instance_number)
Time Split
min(x.value) over (partition by x.stat_id, x.dbid, x.instance_number, trunc(x.end_interval_time))
Time Split
nvl(y.delta/1000000,0) elapsed_time,
Time Split
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
Time Split
partition by y.end_interval_time
Time Split
partition by y.end_interval_time
Time Split
, x.end_interval_time
Time Split
, trunc(x.end_interval_time)
Time Split
y.day_of_week, y.end_interval_time,
Time Split
y.day_of_week, y.end_interval_time,
Time Split
x.day_of_week, x.end_interval_time,
Time Split
x.day_of_week, trunc(x.end_interval_time) end_interval_time,