dbtimevalue

set lin 450
col db_name for a20
col WAIT_CLASS for a20
col EVENT_NAME for a40
with dbtimevalue as (
select inst_id, snap_id, dbtimevalue/1000000 dbtime
from (
select instance_number inst_id, snap_id,STAT_NAME, value, value - lag( value) over (partition by stat_name order by instance_number,snap_id ) dbtimevalue
from dba_hist_sys_time_model
where snap_id between &&begin_snap_id-1 and &&end_snap_id and stat_name ='DB time' order by instance_number ,snap_id )
where snap_id > &&begin_snap_id - 1 )
select sys_context('userenv','DB_NAME') ||decode(1,1,'-'||a.inst_id) db_name , a.snap_id, to_char(begin_interval_time, 'DD-MON-YYYY HH24:MI') timeo, wait_class ,event_name , totalwaits, timewaited,av_wait_ms, round(timewaited/dt.dbtime * 100,1) "%DB Time"
FROM (
select inst_id , snap_id, event_name, wait_class, to_char(fg_waits_delta,'999,999,999') TotalWaits, rank () over ( partition by inst_id,snap_id order by fg_time_waited/1000000 desc ) as rnk, round((fg_time_waited/1000000),0) TimeWaited, decode(waits_delta, 0, 0, round(waits_sec*1000/waits_delta,2)) av_wait_ms
FROM (
select instance_number inst_id , snap_id, s1.event_name, s1.wait_class, total_waits - lag(total_waits) over(partition by event_name order by instance_number,s1.snap_id) as waits_delta, total_waits_fg - lag(total_waits_fg) over(partition by event_name order by instance_number,s1.snap_id) as fg_waits_delta, TIME_WAITED_MICRO_FG - lag( TIME_WAITED_MICRO_FG) over(partition by event_name order by instance_number,s1.snap_id) as fg_time_waited, (TIME_WAITED_MICRO - lag(TIME_WAITED_MICRO) over(partition by event_name order by instance_number, s1.snap_id))/1000000 as waits_sec
from dba_hist_system_event s1
where s1.snap_id between &&begin_snap_id - 1 and &&end_snap_id
--and s1.INSTANCE_NUMBER = userenv('instance')
and s1.WAIT_CLASS not in ( 'Idle') )
where fg_waits_delta > 0 ) a, dbtimevalue dt, dba_hist_snapshot dh
where a.snap_id = dt.snap_id
--and a.snap_id >       &&begin_snap_id
and a.inst_id =dt.inst_id and a.inst_id = dh.instance_number and a.snap_id = dh.snap_id and a.rnk < 10 order by a.inst_id, a.snap_id,timewaited desc