DBA AWR System Time Summary Percentages

➜ Back to Library
Description
Categories: Enginatics, Kcapps
Repository: Github
Historic system time model values from the automated workload repository showing a breakdown of how much percent of the database time was spent e.g. on excuting SQL, PL/SQL or Java code, parsing statements etc..
Run DBA AWR System Time Summary Percentages and other Oracle EBS reports with Blitz Report™ on our demo environment
select
*
from
(
select
&pivot_time1
y.stat_name,
nvl(100*y.delta/xxen_util.zero_to_null(sum(y.delta) over (&pivot_partition1)),0) percentage
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(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
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
percentage 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
Time Split 
pivot (
sum(percentage) "%"
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
time desc
 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 Summary 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: