DBA AWR System Time Summary

Description

Categories: 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..

To see data in this report based on dba_hist_sys_time_model, set the following:
alter session set container=PDB1;
alter system set awr_pdb_autoflush_enabled=true;

https://www.enginatics.com/blog/updated-dba-awr-blitz-reports-now-work-with-plugglable-databases/

SQL 58 lines

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

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

Parameters 5

Parameter

Type

List of values SQL

Date From

LOV

dhs.end_interval_time>=xxen_util.server_time(fnd_date.displaydt_to_date(:date_from))-1/1440

Date To

LOV

dhs.begin_interval_time<=xxen_util.server_time(fnd_date.displaydt_to_date(:date_to))

Display Mode

LOV

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

Diagnostic Pack enabled

LOV

1=1

Container Data

LOV

dhs.dbid=(select vd.dbid from v$database vd)

Description

SQL 58 lines

Parameters 5

Find out more

Features

Download

FAQ