tdba_hist_snapshot_full

Set lin 451
Col stat for a4
Col db_name for a10
Col TOPWTCLS for a8
Col DBTim% for a10
Col AAS-CPUs for a8
Col TIMEBEGIN for a12
WITH tdba_hist_snapshot AS (
SELECT instance_number , snap_id,begin_interval_time, end_interval_time, startup_time, round(round((cast(end_interval_time as date) - cast ( begin_interval_time as date)) * 1440 ,4) * 60,0) awr_time_sec
FROM dba_hist_snapshot
where snap_id between &&begin_snap_id-1 and &&end_snap_id and dbid = (
select dbid
from v$database) ), 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, tdba_hist_snapshot dh
where 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 ) group by inst,snap_id ), TOPDBTIME as (
select *
from (
select inst, snap_id,substr(stat_name,1,5) TopDBstat,dbtime topdbtime, rank () over ( partition by inst,snap_id order by dbtime desc ) as rnk
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 ))/1000000 dbtime
from dba_hist_sys_time_model dht, tdba_hist_snapshot dh
where dht.snap_Id = dh.snap_id and dht.instance_number = dh.instance_Number and stat_name not in ( 'DB time','background elapsed time','background cpu time') order by snap_Id ) )
where rnk =1 ), BGSTAT as (
select inst, snap_id, max(decode(evtcode,'bkup',evttime)) bkUpTime, max(decode(evtcode,'bgio',evttime)) bgio
from (
select inst,snap_id, evtcode, sum(timewaited)/(1000000) evttime
from (
select dht.instance_number inst, dht.snap_id,event_name, decode(substr(lower(event_name),1,4),'back','bkup','rman','bkup','bgio') evtCode, TIME_WAITED_MICRO - lag( TIME_WAITED_MICRO) over ( partition by event_name order by dht.instance_number,dht.snap_id ) timewaited
from
---dba_hist_system_event dht ,
DBA_HIST_BG_EVENT_SUMMARY dht, tdba_hist_snapshot dh
where dht.snap_Id = dh.snap_id and dht.instance_number = dh.instance_Number and wait_class !='Idle' ) group by inst,snap_id,evtcode ) group by inst, snap_id ), WAITCLASS as (
select inst,snap_id, fg_event_name, substr(wait_class,1,8) topwtcls
from (
select inst, snap_id, event_name fg_event_name, total_waits waits,timewaited totwaitTime,wait_class, round( (timewaited/total_waits)* 1000 ,0) AvgWaitMs, rank () over ( partition by inst,snap_id order by timewaited desc ) as rnk, row_number() over ( partition by inst,snap_id order by timewaited desc ) as rn
from (
select dht.instance_number inst, dht.snap_id,event_name,wait_class, total_waits_fg - lag(total_waits_fg) over( partition by event_name order by dht.instance_number, dht.snap_id) total_waits, round((TIME_WAITED_MICRO_fg - lag(TIME_WAITED_MICRO_fg) over( partition by event_name order by dht.instance_number, dht.snap_id))/(1000000), 0) timeWaited
from dba_hist_system_event dht ,tdba_hist_snapshot b
where dht.snap_id = b.snap_id and dht.instance_number = b.instance_number and wait_class !='Idle' )
where total_waits > 0 and timewaited > 0 )
where rnk < 2 and rn < 2 ), DBINS as (
select snap_id, instance_number ins, max(decode(stat_name,'db block changes' , value ) ) BlkChg, max(decode(stat_name,'physical reads' , value ) ) PhyRds, max(decode(stat_name,'physical writes' , value ) ) PhyWrts, max(decode(stat_name,'physical write bytes' , value/1048576 ) ) PhyWrtMB, max(decode(stat_name,'redo size' , value ) )/(1024*1024)/1000 Redo, max(decode(stat_name,'gc current blocks received' , value ) ) "GCcurBlks", max(decode(stat_name,'session logical reads' , value ) ) SLogRds, max(decode(stat_name,'logons current' , value ) ) logons, max(decode(stat_name,'queries parallelized' , value ) ) PrllQry, max(decode(stat_name,'DML statements parallelized' , value ) )+ max(decode(stat_name,'DDL statements parallelized' , value ) ) PrllDML, max(decode(stat_name,'user commits' , value ) ) + max(decode(stat_name,'user rollbacks' , value ) ) commit_roll, max(decode(stat_name,'index fast full scans (direct read)', value)) + max(decode(stat_name,'index fast full scans (full)', value)) + max(decode(stat_name,'index fast full scans (rowid ranges)',value)) "IdxFullRds"
from (
SELECT dos.instance_number, dos.snap_id, dos.stat_name, decode(dos.stat_name,'logons current',value,value - lag( value) over ( partition by dos.stat_name order by dos.instance_number,dos.snap_id )) value
FROM dba_hist_sysstat dos, tdba_hist_snapshot dh
Where dos.snap_id = dh.snap_id and dos.instance_number = dh.instance_number and dos.stat_name in ( 'session logical reads', 'db block changes', 'redo size', 'gc current blocks received', 'physical reads', 'physical writes', 'physical writes direct', 'queries parallelized', 'index fast full scans (direct read)', 'index fast full scans (full)', 'index fast full scans (rowid ranges)', 'DML statements parallelized', 'DDL statements parallelized', 'physical write bytes', 'user commits', 'user rollbacks', 'logons current' ) ) group by snap_id, instance_number ), PGASTAT as (
select pga.snap_id,pga.instance_number inst, round(value/1048576/1000 ,0) pga_sum
from dba_hist_pgastat pga , tdba_hist_snapshot dh
where name ='total PGA inuse' and pga.snap_id = dh.snap_id and pga.instance_number = dh.instance_number ) , OSLOADAVG as (
select inst, snap_id, max(load) load, max(cpus) cpus, 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, dos.snap_id, stat_name, decode(stat_name,'LOAD',value) Load, decode(stat_name,'NUM_CPUS',value) cpus, 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, tdba_hist_snapshot dh
where stat_name in ( 'LOAD', 'BUSY_TIME','IDLE_TIME','IOWAIT_TIME','NUM_CPUS') and dos.snap_id =dh.snap_id and dos.instance_number = dh.instance_number ) 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'
), SQLSTAT as (
select *
from (
select inst,snap_id, sql_id , module ,rnk, ROW_NUMBER() OVER (partition by inst,snap_id ORDER BY inst,snap_id ) AS RN
from (
SELECT e.instance_number inst, e.snap_id, sql_id ,module,elapsed_time_delta , rank () over ( partition by e.instance_number,e.snap_id order by disk_reads_delta desc) as rnk
FROM dba_hist_sqlstat e, tdba_hist_snapshot dh
where e.snap_id = dh.snap_id and e.instance_number = dh.instance_number ) )
where rnk=1 and rn=1 ), TOTIO as (
select *
from (
select snap_id, inst, round((readMb - lag( ReadMB) over (partition by inst order by inst, snap_id))/(1024),2) TotRead , round((writeMb - lag( writeMb) over (partition by inst order by inst, snap_id))/(1024),2) TotWrt , round((readMb - lag( ReadMB) over (partition by inst order by inst, snap_id))/(1024),2) + round((writeMb - lag( writeMb) over (partition by inst order by inst, snap_id))/(1024),2) TotIO
from (
select dio.snap_id, dio.instance_number inst, sum(large_read_megabytes+small_read_megabytes ) ReadMB, sum(large_write_megabytes+small_write_megabytes ) WriteMB
from dba_hist_IOSTAT_FUNCTION dio , tdba_hist_snapshot dh
where dio.snaP_id =dh.snap_id and dio.instance_number = dh.instance_number group by dio.snap_id, dio.instance_number )) ), RMANLGWR as (
select snap_id, inst, max(decode(function_name,'RMAN',IO)) "RMAN", max(decode(function_name,'LGWR',IO)) "LGWR", max(decode(function_name,'Direct Reads',IO)) DirRd
from (
select dio.snap_id, dio.instance_number inst,function_name, round( (sum(large_write_megabytes+small_write_megabytes + large_read_megabytes+small_read_megabytes ) - lag ( sum(large_write_megabytes+small_write_megabytes + large_read_megabytes+small_read_megabytes ) ) over (partition by function_name order by dio.instance_number, dio.snap_id) ) /1024,0) IO
from dba_hist_IOSTAT_FUNCTION dio,tdba_hist_snapshot dh
where dio.snaP_id =dh.snap_id and function_name in ( 'RMAN','LGWR','Direct Reads') group by dio.snap_id, dio.instance_number, function_name ) group by inst,snap_id ), STATS as (
select ds.instance_number inst, ds.snap_id,'Y'||'-'||round(sum(elapsed_time_delta)/(1000000)/60) Stat
from dba_hist_sqlstat ds , tdba_hist_snapshot dh
where action like 'ORA$AT_OS_OPT_SY%' and ds.snaP_id = dh.snap_id and ds.instance_number = dh.instance_number group by ds.instance_number,ds.snap_id ), CMETRIC as (
select a.instance_number inst,a.snap_id, round(a.maxval) Mconn
from dba_hist_sysmetric_summary a, dba_hist_metric_name b, tdba_hist_snapshot dh
where b.metric_name ='Logons Per Sec' and a.metric_id = b.metric_id and b.GROUP_ID = 2 and a.snap_id = dh.snap_id and a.instance_number = dh.instance_number ), HISTPARAM as (
select snap_id, instance_Number inst, decode(value,0,1) prllparam
from dba_hist_parameter
where PARAMETER_NAME ='parallel_max_servers' ), DML as (
SELECT a.instance_number inst, a.SNAP_ID,sum(ROWS_PROCESSED_DELTA )/(1000000) dmlrows
FROM dba_hist_sqlstat a, dba_hist_SQLTEXT b, tdba_hist_snapshot dh
WHERE a.snap_id =dh.snap_id and a.instance_number = dh.instance_number AND a.sql_id = b.sql_id and command_type in ( 2,6,7) GROUP BY a.INSTANCE_NUMBER,A.SNAP_ID ), IO_TIME as (
select inst_id inst , snap_id, max(decode(event_name,'db file sequential read',av_wait_ms)) IdxRd , max(decode(event_name,'log file sync',av_wait_ms)) Logsync
from (
select instance_number inst_id, event_name , snap_id , waits_delta , round(waits_Sec,0) waits_sec , to_char(decode(waits_delta, 0, 0, round(waits_sec*1000/waits_delta,2)),'9999.99') av_wait_ms, end_interval_time Timeo
from (
select s1.instance_number,s1.event_name , s1.snap_id,
--to_char(h.begin_interval_time, 'dd-mon-yy hh24:mi') || ' to ' || to_char(h.end_interval_time, 'hh24:mi') snap_int,
to_char(h.begin_interval_time, 'DD-MM-YY hh24:mi') end_interval_time, lag(total_waits) over(order by s1.snap_id) prev_waits, total_waits - lag(total_waits) over(partition by event_name order by s1.instance_number,s1.snap_id) as waits_delta, (TIME_WAITED_MICRO - lag(TIME_WAITED_MICRO) over(partition by event_name order by s1.instance_number,s1.snap_id))/1000000 as waits_sec
from dba_hist_system_event s1, tdba_hist_snapshot h
where h.snap_id = s1.snap_id and s1.INSTANCE_NUMBER = h.INSTANCE_NUMBER and s1.event_name in ( 'db file sequential read', 'log file sync' ) )
where prev_waits is not null and waits_delta is not null and waits_sec > 10 ) group by inst_id,snap_id )
select
--dbtime.inst,
sys_context('userenv','DB_NAME') ||dbtime.inst db_name, to_char(dbtime.snap_id, '9999999') "snap_id", to_char(begin_interval_time,'DD-Mon HH24:MI') TimeBegin,to_char(logons,'99999') "logons", to_char(Mconn,'9999')"Mconn" , to_char(load,'9999')load, to_char(Busy_time,'999')"CPU%", to_char(WIO_time,'999')"WIO", to_char(DBTime,'999999')"DBTime", CASE when dbtime/awr_time_sec > cpus Then to_char(DBTime/awr_time_sec,'999') ||'/'||cpus||'*' ELSE to_char(DBTime/awr_time_sec,'999') ||'/'||cpus END "AAS-CPUs" , decode(TopDBStat,'sql e','SQL','parse','Parse',topdbstat) || '-' || round(TopDBTime/DBTime * 100,0) "DBTim%", decode(topwtcls,'Application','Appln','System I/O','SYS-IO','Configuration','Config',topwtcls) topwtcls, to_char(round(BlkChg/1000000,0),'9999') "BlkChg",to_char(PhyWrtMB,'99999.99')PhyWrtMB , to_char(round(slogrds/1000000,0),'99999') "slogRds", to_char(round(PhyRds/1000000,0),'99999') "Phyrds", to_char(dirrd,'9999')"dirrd", to_char(TotIO,'99999') "IO-GB", to_char(idxrd,'99999') IdxRd, to_char(round(Redo,0),'99999') Redo, to_char(rman,'9999') rman,
--  BGTime,
stat
--      round(PhyWrts/1000,0) phyWrtsK,
--       round(Blkchg/10000000,0) BlkChgK,
--  logsync logsync,
--round(commit_roll/awr_time_sec) txn ,
--lgwr,
--  dmlrows dml,
--prllqry,
--  prllqry/(prllparam*   round( (cast(end_interval_time as date)  - cast ( begin_interval_time as date))  * 1440)) *100  "Prll%",
--prlldml,
--sql_id  "TopSQL" ,
--       pga_sum pgaGB,
-- to_char(BGTime,'99999') ||decode(RmanTIm, 0,' ','-Y:'||Round(rmantim/BGTime *100,0) ) "BGTim-Rman",
--RmanTim "RmanTim",
--       sql_id || ' - ' || substr(module,1,10)   "TopSQL-Module"
---       round(Bgio/BGTime*100,0) "BGIO%",
--       Idle_time "CPU_Idle%",
--       WIO_time "WIO%",
---     round(SQLExeTime/DbTime *100,0) "SQL%DB",
---   round(DB_CPU/DBtime * 100 , 0) "DBCPU%",
---       round(parsetime/DBtime * 100,0)  "Parse%DB",
--       round(hardparsetime/sqlexetime * 100,0)  "HardParse%",
--        round(BGCPU/BGTime *100,0) "BGCPU%",
--       round(BkUpTime/BGTime* 100,0) "BKup%",
--      PrllDML prllDMDL
--      prllDML,
--       prllDDL
from dbtime, dbins, osloadavg os, bgstat bg,
--pgastat pg,
sqlstat , totio, rmanlgwr, stats, topdbtime, cmetric, tdba_hist_snapshot dhs, waitclass, histparam, dml, io_time io
where dbtime.inst = dbins.ins and dbtime.inst = dhs.instance_number and dbtime.inst = os.inst and dbtime.inst = rmanlgwr.inst(+) and dbtime.inst = stats.inst(+) and dbtime.inst = bg.inst and dbtime.inst = cmetric.inst and dbtime.inst = sqlstat.inst and dbtime.inst = totio.inst and dbtime.inst = topdbtime.inst(+) and dbtime.inst = waitclass.inst and dbtime.inst = histparam.inst and dbtime.inst = dml.inst and dbtime.inst = io.inst(+) and dbtime.snap_id > (
select min(snap_id)
from tdba_hist_snapshot ) and dbtime.snap_id = dbins.snap_id and dbtime.snap_id = cmetric.snap_id and dbtime.snap_id = rmanlgwr.snap_id(+) and dbtime.snap_id = os.snap_id and dbtime.snap_id = bg.snap_id and dbtime.snap_id = totio.snap_id and dbtime.snap_id = dhs.snap_id and dbtime.snap_id = sqlstat.snap_id and dbtime.snap_id = stats.snap_id (+) and dbtime.snap_id = topdbtime.snap_id (+) and dbtime.snap_id = waitclass.snap_id and dbtime.snap_id = histparam.snap_id and dbtime.snap_id = dml.snap_id and dbtime.snap_id = io.snap_id(+)
--order by dbtime.inst, dbtime.snap_id
order by dbtime.snap_id, dbtime.inst