dbtime

col TEVENT for a30
col SQLID_LIST for a50
col LGRD_OBJS for a20
col PHYRD_OBJS for a30
set lin 450
with dbtime as (select inst,snap_id, max(decode(stat_name,'DB time',value/1000000)) DBtime, round(max(decode(stat_name,'DB CPU',value/1000000)) ,0) DB_CPU, round(max(decode(stat_name,'sql execute elapsed time',value/1000000)),0) SQLExeTime, round(max(decode(stat_name,'parse time elapsed',value/1000000)),0) parsetime, round(max(decode(stat_name,'hard parse elapsed time',value/1000000)) ,0) hardparsetime , round(max(decode(stat_name,'background elapsed time',value/1000000)) ,0) BGTime, round(max(decode(stat_name,'background cpu time',value/1000000)) ,0) BGCPU, round(max(decode(stat_name,'RMAN cpu time (backup/restore)',value/1000000)) ,0) RmanTim
--round(max(decode(stat_name,'Backup: MML create a backup',value/1000000)) ,2) BkUpTime
from (
select dht.instance_number inst, dht.snap_id, STAT_NAME, value - lag( value) over ( partition by startup_time, stat_name order by dht.instance_number,dht.snap_id ) value
from dba_hist_sys_time_model dht, dba_hist_snapshot dh
where dht.snap_id between ( &&begin_snap_id - 1) and &&end_snap_id and dht.snap_Id = dh.snap_id and dht.instance_number = dh.instance_Number and stat_name in ( 'sql execute elapsed time', 'background elapsed time', 'background cpu time', 'parse time elapsed', 'hard parse elapsed time', 'RMAN cpu time (backup/restore)', 'Backup: MML create a backup', 'DB time', 'DB CPU') order by snap_Id )
where snap_id > &&begin_snap_id group by inst,snap_id ), topevent as (
select inst, snap_id, rpad(substr(event_name,1,22),22,' ') || '-' || to_char(FGTime_waited,'99999') TEvent
from (
select inst, snap_id, event_name,round(timewaited/1000000) FGTime_waited, rank () over ( partition by inst,snap_id order by timewaited/1000000 desc ) as rnk
from (
select dht.instance_number inst, dht.snap_id,event_name, sum(TIME_WAITED_MICRO_FG) - lag( sum(TIME_WAITED_MICRO_FG)) over ( partition by event_name order by dht.instance_number,dht.snap_id ) timewaited
from dba_hist_system_event dht
where dht.snap_id between &&begin_snap_id -1 and &&end_snap_id and wait_class !='Idle' group by dht.instance_number, dht.snap_id, event_name )
where timewaited > 0 and snap_id > &&begin_snap_id -1 )
where rnk < 2 ), osloadavg as (
select inst, snap_id, max(load) load, to_char( max(busy_time)/( max(idle_time)+ max(busy_time) ) * 100,'999.99') busy_time, to_char( max(idle_time)/( max(idle_time)+ max(busy_time) ) * 100,'999.99') Idle_time, to_char( max(iowait)/( max(idle_time)+ max(busy_time) ) * 100,'999.99') WIO_time
from (
select dos.instance_number inst, snap_id, stat_name, decode(stat_name,'LOAD',value) Load, decode(stat_name,'BUSY_TIME', round( ( (value - lag( value) over ( partition by stat_name order by dos.instance_number,dos.snap_id ) )/100),2)) busy_time, decode(stat_name,'IDLE_TIME', round( ( (value - lag( value) over ( partition by stat_name order by dos.instance_number,dos.snap_id ) )/100),2)) Idle_time, decode(stat_name,'IOWAIT_TIME', round( ( (value - lag( value) over ( partition by stat_name order by dos.instance_number,dos.snap_id ) )/100),2)) iowait
from dba_hist_osstat dos
where stat_name in ( 'LOAD', 'BUSY_TIME','IDLE_TIME','IOWAIT_TIME') and dos.snap_id between &&begin_snap_id-1 and &&end_snap_id )
where snap_id > &&begin_snap_id -1 group by inst, snap_id
--      select dos.instance_number inst, snap_id,  round(value,2) load
--from dba_hist_osstat dos
--where stat_name ='LOAD'
--and dos.snap_id between      &&begin_snap_id   and      &&end_snap_id
), sqlstat as (
select inst, snap_id, listagg(sql_id||' - '||DML ||'- '||rtrim(to_char(exe,'9999')) ,', ') within group ( order by rnk) as sqlid_list
from (
select inst,snap_id, sql_id , module ,rnk,exe,DML, ROW_NUMBER() OVER (partition by inst,snap_id ORDER BY inst,snap_id ) AS RN
from (
SELECT instance_number inst, snap_id, e.sql_id ,module,elapsed_time_delta ,to_char(executions_delta,'9999999') exe, rank () over ( partition by instance_number,snap_id order by elapsed_time_delta desc) as rnk, decode(COMMAND_TYPE,2,'I',3,'S', 6,'U',7,'D',47,'PL',189,'M', command_type) DML
FROM dba_hist_sqlstat e, dba_hist_sqltext t
where e.snap_id between &&begin_snap_id and &&end_snap_id and e.sql_id = t.sql_id ) )
where rnk<3 and rn < 3 group by inst, snap_id ), SEGSTAT AS (
select snap_id, dataobj#, obj#, LOGICAL_READS_DELTA LgRd, PHYSICAL_READS_DELTA PhyRd, PHYSICAL_WRITES_DELTA PhyWrt, DB_BLOCK_CHANGES_DELTA BlkChg, GC_CR_BLOCKS_RECEIVED_DELTA GCRced, rank () over ( partition by snap_id order by DB_BLOCK_CHANGES_DELTA desc ) as bcrnk, rank () over ( partition by snap_id order by LOGICAL_READS_DELTA desc ) as lgRdrnk, rank () over ( partition by snap_id order by PHYSICAL_READS_DELTA desc ) as PhyRdrnk, rank () over ( partition by snap_id order by PHYSICAL_WRITES_DELTA desc ) as PhyWrtrnk, rank () over ( partition by snap_id order by BUFFER_BUSY_WAITS_DELTA desc ) as bufbusyrnk
from dba_hist_seg_stat
where snap_id between &&begin_snap_id and &&end_snap_id and instance_number =userenv('instance') ), LGSTAT AS (
select snap_id, listagg(substr(object_name,1,25)||' ',',') within group ( order by lgRdrnk) as LgRd_objs
from (
select snap_id, dataobj#,obj# , LgRd,lgrdrnk
from segstat
where lgrdrnk < 2 ) a, dba_hist_seg_stat_obj b
where a.dataobj# = b.dataobj# and a.obj# = b.obj# group by snap_id ), PHYSTAT AS (
select snap_id, listagg(substr(object_name,1,30)||' ',',') within group ( order by PhyRdrnk) as PhyRd_objs
from (
select snap_id, dataobj#,obj# , PhyRd,PhyRdRnk
from segstat
where phyrdrnk < 2 ) a, dba_hist_seg_stat_obj b
where a.dataobj# = b.dataobj# group by snap_id )
select dbtime.inst, dbtime.snap_id, to_char(begin_interval_time,'DD-Mon HH24:MI') snapetime,
--  dbtime.dbtime,
--  osloadavg.load,
tevent,
--       waitclass_list,
sqlid_list , lgRd_objs , PhyRd_objs
from dbtime, sqlstat ,lgstat, phystat, topevent, osloadavg, dba_hist_snapshot dhs
where dbtime.inst = dhs.instance_number and dbtime.inst = sqlstat.inst and dbtime.inst = topevent.inst and dbtime.inst = osloadavg.inst and dbtime.snap_id = dhs.snap_id and dbtime.snap_id = sqlstat.snap_id and dbtime.snap_id = osloadavg.snap_id and dbtime.snap_id = topevent.snap_id and dbtime.snap_id = lgstat.snap_id and dbtime.snap_id = phystat.snap_id order by dbtime.inst, dbtime.snap_id

 

 

dbtimevalue

set lin 450
col db_name for a15
col WAIT_CLASS for a15
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;