Oracle SQL script to identify snapshots that are during performance problems

neveralert.sql

set linesize 32000
set pagesize 1000
set long 2000000000
set longchunksize 1000
set head off;
set verify off;
set termout off;
 
column u new_value us noprint;
column n new_value ns noprint;
 
select name n from v$database;
select user u from dual;
set sqlprompt &ns:&us>

set head on
set echo on
set termout on
set feedback on
set trimspool on

spool &ns.neveralert.log

-- look at the top foreground time ratio of the
-- last snapshot. Look at the max ratio of that 
-- type event/cpu for previous 42 snapshots
-- output the ratio of the current one to all the previous
-- no means and stddevs. just flipping max

-- get the database cpu time and total database time since db startup

drop table totalcpudb;

create table totalcpudb as
select 
c.SNAP_ID,
c.VALUE db_cpu,
t.VALUE db_time
from 
DBA_HIST_SYS_TIME_MODEL c,
DBA_HIST_SYS_TIME_MODEL t
where 
c.STAT_NAME = 'DB CPU' and
t.STAT_NAME = 'DB time' and
c.SNAP_ID = t.SNAP_ID and
c.DBID = t.DBID and
c.INSTANCE_NUMBER = t.INSTANCE_NUMBER and
c.INSTANCE_NUMBER = 1 and
c.snap_id >=
(select min(snap_id)
from dba_hist_snapshot
where 
BEGIN_INTERVAL_TIME > sysdate - 42)
order by snap_id;

-- get the delta cpu and db time for past hour

drop table diffcpudb;

create table diffcpudb as
select
curr.snap_id,
(curr.db_cpu - prev.db_cpu) db_cpu,
(curr.db_time - prev.db_time) db_time
from
totalcpudb prev,
totalcpudb curr
where
curr.snap_id = prev.snap_id + 1
order by 
curr.snap_id;

-- get the wait time and number of waits since db startup
-- for each wait event

drop table totalwaittime;

create table totalwaittime as
select 
h.SNAP_ID,
h.EVENT_NAME,
h.TOTAL_WAITS,
h.TIME_WAITED_MICRO
from 
DBA_HIST_SYSTEM_EVENT h
where 
h.INSTANCE_NUMBER = 1 and
h.WAIT_CLASS NOT IN ('Idle', 'System I/O', 'Other', 'Background') and
h.event_name not in ('direct path read','Backup: MML write backup piece','resmgr:cpu quantum','Backup: sbtwrite2') and
h.snap_id >=
(select min(snap_id)
from dba_hist_snapshot
where 
BEGIN_INTERVAL_TIME > sysdate - 42)
order by snap_id,EVENT_NAME;

-- get the delta wait time and number of waits for past hour
-- for each wait event

drop table deltawaittime;

create table deltawaittime as
select
curr.snap_id,
curr.event_name,
(curr.TOTAL_WAITS - prev.TOTAL_WAITS) TOTAL_WAITS,
(curr.TIME_WAITED_MICRO - prev.TIME_WAITED_MICRO) TIME_WAITED_MICRO
from
totalwaittime prev,
totalwaittime curr
where
curr.event_name=prev.event_name and
curr.snap_id = prev.snap_id + 1
order by 
curr.snap_id,
curr.event_name;

-- get ratio of delta wait time to db time for each wait event

drop table waitratio;

create table waitratio as
select
t2.snap_id,
t4.event_name,
t4.TIME_WAITED_MICRO/t2.db_time ratio
from
diffcpudb t2,
deltawaittime t4
where
t2.snap_id = t4.snap_id and
t2.db_time > 0;

-- limit waits to one with highest ratio of
-- wait time / db time for given snapshot

drop table highestwaitratios;

create table highestwaitratios as
select
snap_id,
event_name,
ratio
from
waitratio o
where
ratio =
(select max(ratio) from waitratio i 
 where o.snap_id = i.snap_id);
 
-- get snapshots where the ratio of wait time / db time of the
-- biggest wait ratio is greater than the cpu time / db time ratio

drop table majority_waits;

create table majority_waits as
select
t6.snap_id,
t6.event_name,
t6.ratio,
t2.db_time
from
diffcpudb t2,
highestwaitratios t6
where 
t2.snap_id=t6.snap_id and
t6.ratio > (t2.db_cpu/t2.db_time) and
t2.db_time > 1000000;

-- for a given wait event find the maximum
-- ratio of wait time / db time for that wait
-- on earlier snapshots

drop table maxratio;

create table maxratio as
select
m.snap_id,
t5.event_name,
m.ratio,
max(t5.ratio) max_ratio
from
waitratio t5,
majority_waits m
where
t5.snap_id < m.snap_id and
t5.event_name = m.event_name
group by 
m.snap_id,
t5.event_name,
m.ratio;

-- alert if they are the last snapshot
-- and the wait time / db time is 3 x max of previous
-- snapshots

select 
'ALERT'||'TRIGGERED' x,
m.snap_id,
to_char(s.END_INTERVAL_TIME,'YYYY-MM-DD HH24:MI:SS') snap_dttime,
m.event_name,
m.ratio/m.max_ratio ratio_ratio,
m.ratio,
m.max_ratio
from
maxratio m,
dba_hist_snapshot s
where
m.max_ratio > 0 and
m.ratio/m.max_ratio > 3 and
m.snap_id = s.snap_id and
m.snap_id = (select max(snap_id) from dba_hist_snapshot)
order by
snap_id;

spool off