Awr top events ...
Awr top events .......
from
https://github.com/karlarao/run_awr-quickextract/blob/master/run_awr_topevents.sql
====
--set feedback off pages 0 term off head on und off trimspool on echo off lines 4000 colsep ','
set feedback off pages 0 term off head on und off trimspool on echo off lines 4000
set arraysize 5000
set termout off
set echo off verify off
COLUMN dbid NEW_VALUE _dbid NOPRINT
select dbid from v$database;
COLUMN name NEW_VALUE _instname NOPRINT
select lower(instance_name) name from v$instance;
COLUMN name NEW_VALUE _hostname NOPRINT
select lower(host_name) name from v$instance;
COLUMN instancenumber NEW_VALUE _instancenumber NOPRINT
select instance_number instancenumber from v$instance;
-- ttitle center 'AWR Top Events Report' skip 2
set pagesize 50000
set linesize 550
col instname format a15
col hostname format a30
col snap_id format 99999 heading snap_id -- "snapid"
col tm format a17 heading tm -- "tm"
col inst format 90 heading inst -- "inst"
col dur format 999990.00 heading dur -- "dur"
col event format a55 heading event -- "Event"
col event_rank format 90 heading event_rank -- "EventRank"
col waits format 9999999990.00 heading waits -- "Waits"
col time format 9999999990.00 heading time -- "Timesec"
col avgwt format 99990.00 heading avgwt -- "Avgwtms"
col pctdbt format 9990.0 heading pctdbt -- "DBTimepct"
col aas format 990.0 heading aas -- "Aas"
col wait_class format a15 heading wait_class -- "WaitClass"
VARIABLE g_retention NUMBER
DEFINE p_default = 8
DEFINE p_max = 300
SET VERIFY OFF
DECLARE
v_default NUMBER(3) := &p_default;
v_max NUMBER(3) := &p_max;
BEGIN
select
((TRUNC(SYSDATE) + RETENTION - TRUNC(SYSDATE)) * 86400)/60/60/24 AS RETENTION_DAYS
into :g_retention
from dba_hist_wr_control
where dbid in (select dbid from v$database);
if :g_retention > v_default then
:g_retention := v_max;
else
:g_retention := v_default;
end if;
END;
/
spool awr_topevents-tableau-&_instname-&_hostname..csv
select trim('&_instname') instname, trim('&_dbid') db_id, trim('&_hostname') hostname, snap_id, tm, inst, dur, event, event_rank, waits, time, avgwt, pctdbt, aas, wait_class
from
(select snap_id, TO_CHAR(tm,'MM/DD/YY HH24:MI:SS') tm, inst, dur, event, waits, time, avgwt, pctdbt, aas, wait_class,
DENSE_RANK() OVER (
PARTITION BY snap_id ORDER BY time DESC) event_rank
from
(
select * from
(select * from
(select
s0.snap_id snap_id,
s0.END_INTERVAL_TIME tm,
s0.instance_number inst,
round(EXTRACT(DAY FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 1440
+ EXTRACT(HOUR FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 60
+ EXTRACT(MINUTE FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME)
+ EXTRACT(SECOND FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) / 60, 2) dur,
e.event_name event,
e.total_waits - nvl(b.total_waits,0) waits,
round ((e.time_waited_micro - nvl(b.time_waited_micro,0))/1000000, 2) time, -- THIS IS EVENT (sec)
round (decode ((e.total_waits - nvl(b.total_waits, 0)), 0, to_number(NULL), ((e.time_waited_micro - nvl(b.time_waited_micro,0))/1000) / (e.total_waits - nvl(b.total_waits,0))), 2) avgwt,
((round ((e.time_waited_micro - nvl(b.time_waited_micro,0))/1000000, 2)) / NULLIF(((s5t1.value - nvl(s5t0.value,0)) / 1000000),0))*100 as pctdbt, -- THIS IS EVENT (sec) / DB TIME (sec)
(round ((e.time_waited_micro - nvl(b.time_waited_micro,0))/1000000, 2))/60 / round(EXTRACT(DAY FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 1440
+ EXTRACT(HOUR FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 60
+ EXTRACT(MINUTE FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME)
+ EXTRACT(SECOND FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) / 60, 2) aas, -- THIS IS EVENT (min) / SnapDur (min) TO GET THE % DB CPU ON AAS
e.wait_class wait_class
from
dba_hist_snapshot s0,
dba_hist_snapshot s1,
dba_hist_system_event b,
dba_hist_system_event e,
dba_hist_sys_time_model s5t0,
dba_hist_sys_time_model s5t1
where
s0.dbid = &_dbid -- CHANGE THE DBID HERE!
AND s1.dbid = s0.dbid
and b.dbid(+) = s0.dbid
and e.dbid = s0.dbid
AND s5t0.dbid = s0.dbid
AND s5t1.dbid = s0.dbid
--AND s0.instance_number = &_instancenumber -- CHANGE THE INSTANCE_NUMBER HERE!
AND s1.instance_number = s0.instance_number
and b.instance_number(+) = s0.instance_number
and e.instance_number = s0.instance_number
AND s5t0.instance_number = s0.instance_number
AND s5t1.instance_number = s0.instance_number
AND s1.snap_id = s0.snap_id + 1
AND b.snap_id(+) = s0.snap_id
and e.snap_id = s0.snap_id + 1
AND s5t0.snap_id = s0.snap_id
AND s5t1.snap_id = s0.snap_id + 1
AND s5t0.stat_name = 'DB time'
AND s5t1.stat_name = s5t0.stat_name
and b.event_id = e.event_id
and e.wait_class != 'Idle'
and e.total_waits > nvl(b.total_waits,0)
and e.event_name not in ('smon timer',
'pmon timer',
'dispatcher timer',
'dispatcher listen timer',
'rdbms ipc message')
order by snap_id, time desc, waits desc, event)
union all
select
s0.snap_id snap_id,
s0.END_INTERVAL_TIME tm,
s0.instance_number inst,
round(EXTRACT(DAY FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 1440
+ EXTRACT(HOUR FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 60
+ EXTRACT(MINUTE FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME)
+ EXTRACT(SECOND FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) / 60, 2) dur,
'CPU time',
0,
round ((s6t1.value - s6t0.value) / 1000000, 2) as time, -- THIS IS DB CPU (sec)
0,
((round ((s6t1.value - s6t0.value) / 1000000, 2)) / NULLIF(((s5t1.value - nvl(s5t0.value,0)) / 1000000),0))*100 as pctdbt, -- THIS IS DB CPU (sec) / DB TIME (sec)..TO GET % OF DB CPU ON DB TIME FOR TOP 5 TIMED EVENTS SECTION
(round ((s6t1.value - s6t0.value) / 1000000, 2))/60 / round(EXTRACT(DAY FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 1440
+ EXTRACT(HOUR FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 60
+ EXTRACT(MINUTE FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME)
+ EXTRACT(SECOND FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) / 60, 2) aas, -- THIS IS DB CPU (min) / SnapDur (min) TO GET THE % DB CPU ON AAS
'CPU'
from
dba_hist_snapshot s0,
dba_hist_snapshot s1,
dba_hist_sys_time_model s6t0,
dba_hist_sys_time_model s6t1,
dba_hist_sys_time_model s5t0,
dba_hist_sys_time_model s5t1
WHERE
s0.dbid = &_dbid -- CHANGE THE DBID HERE!
AND s1.dbid = s0.dbid
AND s6t0.dbid = s0.dbid
AND s6t1.dbid = s0.dbid
AND s5t0.dbid = s0.dbid
AND s5t1.dbid = s0.dbid
--AND s0.instance_number = &_instancenumber -- CHANGE THE INSTANCE_NUMBER HERE!
AND s1.instance_number = s0.instance_number
AND s6t0.instance_number = s0.instance_number
AND s6t1.instance_number = s0.instance_number
AND s5t0.instance_number = s0.instance_number
AND s5t1.instance_number = s0.instance_number
AND s1.snap_id = s0.snap_id + 1
AND s6t0.snap_id = s0.snap_id
AND s6t1.snap_id = s0.snap_id + 1
AND s5t0.snap_id = s0.snap_id
AND s5t1.snap_id = s0.snap_id + 1
AND s6t0.stat_name = 'DB CPU'
AND s6t1.stat_name = s6t0.stat_name
AND s5t0.stat_name = 'DB time'
AND s5t1.stat_name = s5t0.stat_name
union all
(select
dbtime.snap_id,
dbtime.tm,
dbtime.inst,
dbtime.dur,
'CPU wait',
0,
round(dbtime.time - accounted_dbtime.time, 2) time, -- THIS IS UNACCOUNTED FOR DB TIME (sec)
0,
((dbtime.aas - accounted_dbtime.aas)/ NULLIF(nvl(dbtime.aas,0),0))*100 as pctdbt, -- THIS IS UNACCOUNTED FOR DB TIME (sec) / DB TIME (sec)
round(dbtime.aas - accounted_dbtime.aas, 2) aas, -- AAS OF UNACCOUNTED FOR DB TIME
'CPU wait'
from
(select
s0.snap_id,
s0.END_INTERVAL_TIME tm,
s0.instance_number inst,
round(EXTRACT(DAY FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 1440
+ EXTRACT(HOUR FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 60
+ EXTRACT(MINUTE FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME)
+ EXTRACT(SECOND FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) / 60, 2) dur,
'DB time',
0,
round ((s5t1.value - s5t0.value) / 1000000, 2) as time, -- THIS IS DB time (sec)
0,
0,
(round ((s5t1.value - s5t0.value) / 1000000, 2))/60 / round(EXTRACT(DAY FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 1440
+ EXTRACT(HOUR FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 60
+ EXTRACT(MINUTE FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME)
+ EXTRACT(SECOND FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) / 60, 2) aas,
'DB time'
from
dba_hist_snapshot s0,
dba_hist_snapshot s1,
dba_hist_sys_time_model s5t0,
dba_hist_sys_time_model s5t1
WHERE
s0.dbid = &_dbid -- CHANGE THE DBID HERE!
AND s1.dbid = s0.dbid
AND s5t0.dbid = s0.dbid
AND s5t1.dbid = s0.dbid
--AND s0.instance_number = &_instancenumber -- CHANGE THE INSTANCE_NUMBER HERE!
AND s1.instance_number = s0.instance_number
AND s5t0.instance_number = s0.instance_number
AND s5t1.instance_number = s0.instance_number
AND s1.snap_id = s0.snap_id + 1
AND s5t0.snap_id = s0.snap_id
AND s5t1.snap_id = s0.snap_id + 1
AND s5t0.stat_name = 'DB time'
AND s5t1.stat_name = s5t0.stat_name) dbtime,
(select snap_id, inst, sum(time) time, sum(AAS) aas from
(select * from (select
s0.snap_id snap_id,
s0.END_INTERVAL_TIME tm,
s0.instance_number inst,
round(EXTRACT(DAY FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 1440
+ EXTRACT(HOUR FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 60
+ EXTRACT(MINUTE FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME)
+ EXTRACT(SECOND FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) / 60, 2) dur,
e.event_name event,
e.total_waits - nvl(b.total_waits,0) waits,
round ((e.time_waited_micro - nvl(b.time_waited_micro,0))/1000000, 2) time, -- THIS IS EVENT (sec)
round (decode ((e.total_waits - nvl(b.total_waits, 0)), 0, to_number(NULL), ((e.time_waited_micro - nvl(b.time_waited_micro,0))/1000) / (e.total_waits - nvl(b.total_waits,0))), 2) avgwt,
((round ((e.time_waited_micro - nvl(b.time_waited_micro,0))/1000000, 2)) / NULLIF(((s5t1.value - nvl(s5t0.value,0)) / 1000000),0))*100 as pctdbt, -- THIS IS EVENT (sec) / DB TIME (sec)
(round ((e.time_waited_micro - nvl(b.time_waited_micro,0))/1000000, 2))/60 / round(EXTRACT(DAY FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 1440
+ EXTRACT(HOUR FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 60
+ EXTRACT(MINUTE FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME)
+ EXTRACT(SECOND FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) / 60, 2) aas, -- THIS IS EVENT (min) / SnapDur (min) TO GET THE % DB CPU ON AAS
e.wait_class wait_class
from
dba_hist_snapshot s0,
dba_hist_snapshot s1,
dba_hist_system_event b,
dba_hist_system_event e,
dba_hist_sys_time_model s5t0,
dba_hist_sys_time_model s5t1
where
s0.dbid = &_dbid -- CHANGE THE DBID HERE!
AND s1.dbid = s0.dbid
and b.dbid(+) = s0.dbid
and e.dbid = s0.dbid
AND s5t0.dbid = s0.dbid
AND s5t1.dbid = s0.dbid
--AND s0.instance_number = &_instancenumber -- CHANGE THE INSTANCE_NUMBER HERE!
AND s1.instance_number = s0.instance_number
and b.instance_number(+) = s0.instance_number
and e.instance_number = s0.instance_number
AND s5t0.instance_number = s0.instance_number
AND s5t1.instance_number = s0.instance_number
AND s1.snap_id = s0.snap_id + 1
AND b.snap_id(+) = s0.snap_id
and e.snap_id = s0.snap_id + 1
AND s5t0.snap_id = s0.snap_id
AND s5t1.snap_id = s0.snap_id + 1
AND s5t0.stat_name = 'DB time'
AND s5t1.stat_name = s5t0.stat_name
and b.event_id = e.event_id
and e.wait_class != 'Idle'
and e.total_waits > nvl(b.total_waits,0)
and e.event_name not in ('smon timer',
'pmon timer',
'dispatcher timer',
'dispatcher listen timer',
'rdbms ipc message')
order by snap_id, time desc, waits desc, event)
union all
select
s0.snap_id snap_id,
s0.END_INTERVAL_TIME tm,
s0.instance_number inst,
round(EXTRACT(DAY FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 1440
+ EXTRACT(HOUR FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 60
+ EXTRACT(MINUTE FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME)
+ EXTRACT(SECOND FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) / 60, 2) dur,
'CPU time',
0,
round ((s6t1.value - s6t0.value) / 1000000, 2) as time, -- THIS IS DB CPU (sec)
0,
((round ((s6t1.value - s6t0.value) / 1000000, 2)) / NULLIF(((s5t1.value - nvl(s5t0.value,0)) / 1000000),0))*100 as pctdbt, -- THIS IS DB CPU (sec) / DB TIME (sec)..TO GET % OF DB CPU ON DB TIME FOR TOP 5 TIMED EVENTS SECTION
(round ((s6t1.value - s6t0.value) / 1000000, 2))/60 / round(EXTRACT(DAY FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 1440
+ EXTRACT(HOUR FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 60
+ EXTRACT(MINUTE FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME)
+ EXTRACT(SECOND FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) / 60, 2) aas, -- THIS IS DB CPU (min) / SnapDur (min) TO GET THE % DB CPU ON AAS
'CPU'
from
dba_hist_snapshot s0,
dba_hist_snapshot s1,
dba_hist_sys_time_model s6t0,
dba_hist_sys_time_model s6t1,
dba_hist_sys_time_model s5t0,
dba_hist_sys_time_model s5t1
WHERE
s0.dbid = &_dbid -- CHANGE THE DBID HERE!
AND s1.dbid = s0.dbid
AND s6t0.dbid = s0.dbid
AND s6t1.dbid = s0.dbid
AND s5t0.dbid = s0.dbid
AND s5t1.dbid = s0.dbid
--AND s0.instance_number = &_instancenumber -- CHANGE THE INSTANCE_NUMBER HERE!
AND s1.instance_number = s0.instance_number
AND s6t0.instance_number = s0.instance_number
AND s6t1.instance_number = s0.instance_number
AND s5t0.instance_number = s0.instance_number
AND s5t1.instance_number = s0.instance_number
AND s1.snap_id = s0.snap_id + 1
AND s6t0.snap_id = s0.snap_id
AND s6t1.snap_id = s0.snap_id + 1
AND s5t0.snap_id = s0.snap_id
AND s5t1.snap_id = s0.snap_id + 1
AND s6t0.stat_name = 'DB CPU'
AND s6t1.stat_name = s6t0.stat_name
AND s5t0.stat_name = 'DB time'
AND s5t1.stat_name = s5t0.stat_name
) group by snap_id, inst) accounted_dbtime
where dbtime.snap_id = accounted_dbtime.snap_id
and dbtime.inst = accounted_dbtime.inst
)
)
)
)
WHERE event_rank <= 5
AND to_date(tm,'MM/DD/YY HH24:MI:SS') > sysdate - :g_retention
-- AND TO_CHAR(tm,'D') >= 1 -- Day of week: 1=Sunday 7=Saturday
-- AND TO_CHAR(tm,'D') <= 7
-- AND TO_CHAR(tm,'HH24MI') >= 0900 -- Hour
-- AND TO_CHAR(tm,'HH24MI') <= 1800
-- AND tm >= TO_DATE('2010-jan-17 00:00:00','yyyy-mon-dd hh24:mi:ss') -- Data range
-- AND tm <= TO_DATE('2010-aug-22 23:59:59','yyyy-mon-dd hh24:mi:ss')
-- and snap_id = 495
-- and snap_id >= 495 and snap_id <= 496
-- and event = 'db file sequential read'
-- and event like 'CPU%'
-- and avgwt > 5
-- and aas > .5
-- and wait_class = 'CPU'
-- and wait_class like '%I/O%'
-- and event_rank in (1,2,3)
ORDER BY snap_id;
--- ===================================================
--with con_id
--set feedback off pages 0 term off head on und off trimspool on echo off lines 4000 colsep ','
set feedback off pages 0 term off head on und off trimspool on echo off lines 4000
set arraysize 5000
set termout off
set echo off verify off
COLUMN dbid NEW_VALUE _dbid NOPRINT
select dbid from v$database;
COLUMN name NEW_VALUE _instname NOPRINT
select lower(instance_name) name from v$instance;
COLUMN name NEW_VALUE _hostname NOPRINT
select lower(host_name) name from v$instance;
COLUMN instancenumber NEW_VALUE _instancenumber NOPRINT
select instance_number instancenumber from v$instance;
-- ttitle center 'AWR Top Events Report' skip 2
set pagesize 50000
set linesize 550
col instname format a15
col hostname format a30
col snap_id format 99999 heading snap_id -- "snapid"
col tm format a17 heading tm -- "tm"
col inst format 90 heading inst -- "inst"
col dur format 999990.00 heading dur -- "dur"
col event format a55 heading event -- "Event"
col event_rank format 90 heading event_rank -- "EventRank"
col waits format 9999999990.00 heading waits -- "Waits"
col time format 9999999990.00 heading time -- "Timesec"
col avgwt format 99990.00 heading avgwt -- "Avgwtms"
col pctdbt format 9990.0 heading pctdbt -- "DBTimepct"
col aas format 990.0 heading aas -- "Aas"
col wait_class format a15 heading wait_class -- "WaitClass"
VARIABLE g_retention NUMBER
DEFINE p_default = 8
DEFINE p_max = 300
SET VERIFY OFF
DECLARE
v_default NUMBER(3) := &p_default;
v_max NUMBER(3) := &p_max;
BEGIN
select
((TRUNC(SYSDATE) + RETENTION - TRUNC(SYSDATE)) * 86400)/60/60/24 AS RETENTION_DAYS
into :g_retention
from dba_hist_wr_control
where dbid in (select dbid from v$database);
if :g_retention > v_default then
:g_retention := v_max;
else
:g_retention := v_default;
end if;
END;
/
----
-- spool awr_topevents-tableau-&_instname-&_hostname..csv
select trim('&_instname') instname, trim('&_dbid') db_id, trim('&_hostname') hostname, snap_id, tm, inst, dur, event, event_rank, waits, time, avgwt, pctdbt, aas, wait_class
from
(select snap_id, TO_CHAR(tm,'MM/DD/YY HH24:MI:SS') tm, inst, dur, event, waits, time, avgwt, pctdbt, aas, wait_class,
DENSE_RANK() OVER (
PARTITION BY snap_id ORDER BY time DESC) event_rank
from
(
select * from
(select * from
(select
s0.snap_id snap_id,
s5t1.CON_ID,
s0.END_INTERVAL_TIME tm,
s0.instance_number inst,
round(EXTRACT(DAY FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 1440
+ EXTRACT(HOUR FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 60
+ EXTRACT(MINUTE FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME)
+ EXTRACT(SECOND FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) / 60, 2) dur,
e.event_name event,
e.total_waits - nvl(b.total_waits,0) waits,
round ((e.time_waited_micro - nvl(b.time_waited_micro,0))/1000000, 2) time, -- THIS IS EVENT (sec)
round (decode ((e.total_waits - nvl(b.total_waits, 0)), 0, to_number(NULL), ((e.time_waited_micro - nvl(b.time_waited_micro,0))/1000) / (e.total_waits - nvl(b.total_waits,0))), 2) avgwt,
((round ((e.time_waited_micro - nvl(b.time_waited_micro,0))/1000000, 2)) / NULLIF(((s5t1.value - nvl(s5t0.value,0)) / 1000000),0))*100 as pctdbt, -- THIS IS EVENT (sec) / DB TIME (sec)
(round ((e.time_waited_micro - nvl(b.time_waited_micro,0))/1000000, 2))/60 / round(EXTRACT(DAY FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 1440
+ EXTRACT(HOUR FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 60
+ EXTRACT(MINUTE FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME)
+ EXTRACT(SECOND FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) / 60, 2) aas, -- THIS IS EVENT (min) / SnapDur (min) TO GET THE % DB CPU ON AAS
e.wait_class wait_class
from
dba_hist_snapshot s0,
dba_hist_snapshot s1,
dba_hist_system_event b,
dba_hist_system_event e,
dba_hist_sys_time_model s5t0,
dba_hist_sys_time_model s5t1
where
s0.dbid = &_dbid -- CHANGE THE DBID HERE!
AND s1.dbid = s0.dbid
and b.dbid(+) = s0.dbid
and e.dbid = s0.dbid
AND s5t0.dbid = s0.dbid
AND s5t1.dbid = s0.dbid
--AND s0.instance_number = &_instancenumber -- CHANGE THE INSTANCE_NUMBER HERE!
AND s1.instance_number = s0.instance_number
and b.instance_number(+) = s0.instance_number
and e.instance_number = s0.instance_number
AND s5t0.instance_number = s0.instance_number
AND s5t1.instance_number = s0.instance_number
AND s1.snap_id = s0.snap_id + 1
AND b.snap_id(+) = s0.snap_id
and e.snap_id = s0.snap_id + 1
AND s5t0.snap_id = s0.snap_id
AND s5t1.snap_id = s0.snap_id + 1
AND s5t0.stat_name = 'DB time'
AND s5t1.stat_name = s5t0.stat_name
and b.event_id = e.event_id
and e.wait_class != 'Idle'
and e.total_waits > nvl(b.total_waits,0)
and e.event_name not in ('smon timer',
'pmon timer',
'dispatcher timer',
'dispatcher listen timer',
'rdbms ipc message')
order by snap_id, time desc, waits desc, event)
union all
select
s0.snap_id snap_id,
s5t1.CON_ID,
s0.END_INTERVAL_TIME tm,
s0.instance_number inst,
round(EXTRACT(DAY FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 1440
+ EXTRACT(HOUR FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 60
+ EXTRACT(MINUTE FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME)
+ EXTRACT(SECOND FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) / 60, 2) dur,
'CPU time',
0,
round ((s6t1.value - s6t0.value) / 1000000, 2) as time, -- THIS IS DB CPU (sec)
0,
((round ((s6t1.value - s6t0.value) / 1000000, 2)) / NULLIF(((s5t1.value - nvl(s5t0.value,0)) / 1000000),0))*100 as pctdbt, -- THIS IS DB CPU (sec) / DB TIME (sec)..TO GET % OF DB CPU ON DB TIME FOR TOP 5 TIMED EVENTS SECTION
(round ((s6t1.value - s6t0.value) / 1000000, 2))/60 / round(EXTRACT(DAY FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 1440
+ EXTRACT(HOUR FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 60
+ EXTRACT(MINUTE FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME)
+ EXTRACT(SECOND FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) / 60, 2) aas, -- THIS IS DB CPU (min) / SnapDur (min) TO GET THE % DB CPU ON AAS
'CPU'
from
dba_hist_snapshot s0,
dba_hist_snapshot s1,
dba_hist_sys_time_model s6t0,
dba_hist_sys_time_model s6t1,
dba_hist_sys_time_model s5t0,
dba_hist_sys_time_model s5t1
WHERE
s0.dbid = &_dbid -- CHANGE THE DBID HERE!
AND s1.dbid = s0.dbid
AND s6t0.dbid = s0.dbid
AND s6t1.dbid = s0.dbid
AND s5t0.dbid = s0.dbid
AND s5t1.dbid = s0.dbid
--AND s0.instance_number = &_instancenumber -- CHANGE THE INSTANCE_NUMBER HERE!
AND s1.instance_number = s0.instance_number
AND s6t0.instance_number = s0.instance_number
AND s6t1.instance_number = s0.instance_number
AND s5t0.instance_number = s0.instance_number
AND s5t1.instance_number = s0.instance_number
AND s1.snap_id = s0.snap_id + 1
AND s6t0.snap_id = s0.snap_id
AND s6t1.snap_id = s0.snap_id + 1
AND s5t0.snap_id = s0.snap_id
AND s5t1.snap_id = s0.snap_id + 1
AND s6t0.stat_name = 'DB CPU'
AND s6t1.stat_name = s6t0.stat_name
AND s5t0.stat_name = 'DB time'
AND s5t1.stat_name = s5t0.stat_name
union all
(select
dbtime.snap_id,
dbtime.CON_ID,
dbtime.tm,
dbtime.inst,
dbtime.dur,
'CPU wait',
0,
round(dbtime.time - accounted_dbtime.time, 2) time, -- THIS IS UNACCOUNTED FOR DB TIME (sec)
0,
((dbtime.aas - accounted_dbtime.aas)/ NULLIF(nvl(dbtime.aas,0),0))*100 as pctdbt, -- THIS IS UNACCOUNTED FOR DB TIME (sec) / DB TIME (sec)
round(dbtime.aas - accounted_dbtime.aas, 2) aas, -- AAS OF UNACCOUNTED FOR DB TIME
'CPU wait'
from
(select
s0.snap_id,
s5t1.CON_ID,
s0.END_INTERVAL_TIME tm,
s0.instance_number inst,
round(EXTRACT(DAY FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 1440
+ EXTRACT(HOUR FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 60
+ EXTRACT(MINUTE FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME)
+ EXTRACT(SECOND FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) / 60, 2) dur,
'DB time',
0,
round ((s5t1.value - s5t0.value) / 1000000, 2) as time, -- THIS IS DB time (sec)
0,
0,
(round ((s5t1.value - s5t0.value) / 1000000, 2))/60 / round(EXTRACT(DAY FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 1440
+ EXTRACT(HOUR FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 60
+ EXTRACT(MINUTE FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME)
+ EXTRACT(SECOND FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) / 60, 2) aas,
'DB time'
from
dba_hist_snapshot s0,
dba_hist_snapshot s1,
dba_hist_sys_time_model s5t0,
dba_hist_sys_time_model s5t1
WHERE
s0.dbid = &_dbid -- CHANGE THE DBID HERE!
AND s1.dbid = s0.dbid
AND s5t0.dbid = s0.dbid
AND s5t1.dbid = s0.dbid
--AND s0.instance_number = &_instancenumber -- CHANGE THE INSTANCE_NUMBER HERE!
AND s1.instance_number = s0.instance_number
AND s5t0.instance_number = s0.instance_number
AND s5t1.instance_number = s0.instance_number
AND s1.snap_id = s0.snap_id + 1
AND s5t0.snap_id = s0.snap_id
AND s5t1.snap_id = s0.snap_id + 1
AND s5t0.stat_name = 'DB time'
AND s5t1.stat_name = s5t0.stat_name) dbtime,
(select snap_id, inst, sum(time) time, sum(AAS) aas from
(select * from (select
s0.snap_id snap_id,
s5t1.CON_ID,
s0.END_INTERVAL_TIME tm,
s0.instance_number inst,
round(EXTRACT(DAY FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 1440
+ EXTRACT(HOUR FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 60
+ EXTRACT(MINUTE FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME)
+ EXTRACT(SECOND FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) / 60, 2) dur,
e.event_name event,
e.total_waits - nvl(b.total_waits,0) waits,
round ((e.time_waited_micro - nvl(b.time_waited_micro,0))/1000000, 2) time, -- THIS IS EVENT (sec)
round (decode ((e.total_waits - nvl(b.total_waits, 0)), 0, to_number(NULL), ((e.time_waited_micro - nvl(b.time_waited_micro,0))/1000) / (e.total_waits - nvl(b.total_waits,0))), 2) avgwt,
((round ((e.time_waited_micro - nvl(b.time_waited_micro,0))/1000000, 2)) / NULLIF(((s5t1.value - nvl(s5t0.value,0)) / 1000000),0))*100 as pctdbt, -- THIS IS EVENT (sec) / DB TIME (sec)
(round ((e.time_waited_micro - nvl(b.time_waited_micro,0))/1000000, 2))/60 / round(EXTRACT(DAY FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 1440
+ EXTRACT(HOUR FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 60
+ EXTRACT(MINUTE FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME)
+ EXTRACT(SECOND FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) / 60, 2) aas, -- THIS IS EVENT (min) / SnapDur (min) TO GET THE % DB CPU ON AAS
e.wait_class wait_class
from
dba_hist_snapshot s0,
dba_hist_snapshot s1,
dba_hist_system_event b,
dba_hist_system_event e,
dba_hist_sys_time_model s5t0,
dba_hist_sys_time_model s5t1
where
s0.dbid = &_dbid -- CHANGE THE DBID HERE!
AND s1.dbid = s0.dbid
and b.dbid(+) = s0.dbid
and e.dbid = s0.dbid
AND s5t0.dbid = s0.dbid
AND s5t1.dbid = s0.dbid
--AND s0.instance_number = &_instancenumber -- CHANGE THE INSTANCE_NUMBER HERE!
AND s1.instance_number = s0.instance_number
and b.instance_number(+) = s0.instance_number
and e.instance_number = s0.instance_number
AND s5t0.instance_number = s0.instance_number
AND s5t1.instance_number = s0.instance_number
AND s1.snap_id = s0.snap_id + 1
AND b.snap_id(+) = s0.snap_id
and e.snap_id = s0.snap_id + 1
AND s5t0.snap_id = s0.snap_id
AND s5t1.snap_id = s0.snap_id + 1
AND s5t0.stat_name = 'DB time'
AND s5t1.stat_name = s5t0.stat_name
and b.event_id = e.event_id
and e.wait_class != 'Idle'
and e.total_waits > nvl(b.total_waits,0)
and e.event_name not in ('smon timer',
'pmon timer',
'dispatcher timer',
'dispatcher listen timer',
'rdbms ipc message')
order by snap_id, time desc, waits desc, event)
union all
select
s0.snap_id snap_id,
s5t1.CON_ID,
s0.END_INTERVAL_TIME tm,
s0.instance_number inst,
round(EXTRACT(DAY FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 1440
+ EXTRACT(HOUR FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 60
+ EXTRACT(MINUTE FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME)
+ EXTRACT(SECOND FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) / 60, 2) dur,
'CPU time',
0,
round ((s6t1.value - s6t0.value) / 1000000, 2) as time, -- THIS IS DB CPU (sec)
0,
((round ((s6t1.value - s6t0.value) / 1000000, 2)) / NULLIF(((s5t1.value - nvl(s5t0.value,0)) / 1000000),0))*100 as pctdbt, -- THIS IS DB CPU (sec) / DB TIME (sec)..TO GET % OF DB CPU ON DB TIME FOR TOP 5 TIMED EVENTS SECTION
(round ((s6t1.value - s6t0.value) / 1000000, 2))/60 / round(EXTRACT(DAY FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 1440
+ EXTRACT(HOUR FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 60
+ EXTRACT(MINUTE FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME)
+ EXTRACT(SECOND FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) / 60, 2) aas, -- THIS IS DB CPU (min) / SnapDur (min) TO GET THE % DB CPU ON AAS
'CPU'
from
dba_hist_snapshot s0,
dba_hist_snapshot s1,
dba_hist_sys_time_model s6t0,
dba_hist_sys_time_model s6t1,
dba_hist_sys_time_model s5t0,
dba_hist_sys_time_model s5t1
WHERE
s0.dbid = &_dbid -- CHANGE THE DBID HERE!
AND s1.dbid = s0.dbid
AND s6t0.dbid = s0.dbid
AND s6t1.dbid = s0.dbid
AND s5t0.dbid = s0.dbid
AND s5t1.dbid = s0.dbid
--AND s0.instance_number = &_instancenumber -- CHANGE THE INSTANCE_NUMBER HERE!
AND s1.instance_number = s0.instance_number
AND s6t0.instance_number = s0.instance_number
AND s6t1.instance_number = s0.instance_number
AND s5t0.instance_number = s0.instance_number
AND s5t1.instance_number = s0.instance_number
AND s1.snap_id = s0.snap_id + 1
AND s6t0.snap_id = s0.snap_id
AND s6t1.snap_id = s0.snap_id + 1
AND s5t0.snap_id = s0.snap_id
AND s5t1.snap_id = s0.snap_id + 1
AND s6t0.stat_name = 'DB CPU'
AND s6t1.stat_name = s6t0.stat_name
AND s5t0.stat_name = 'DB time'
AND s5t1.stat_name = s5t0.stat_name
) group by snap_id, inst) accounted_dbtime
where dbtime.snap_id = accounted_dbtime.snap_id
and dbtime.inst = accounted_dbtime.inst
)
)
)
)
WHERE event_rank <= 5
AND to_date(tm,'MM/DD/YY HH24:MI:SS') > sysdate - :g_retention
-- AND TO_CHAR(tm,'D') >= 1 -- Day of week: 1=Sunday 7=Saturday
-- AND TO_CHAR(tm,'D') <= 7
-- AND TO_CHAR(tm,'HH24MI') >= 0900 -- Hour
-- AND TO_CHAR(tm,'HH24MI') <= 1800
-- AND tm >= TO_DATE('2010-jan-17 00:00:00','yyyy-mon-dd hh24:mi:ss') -- Data range
-- AND tm <= TO_DATE('2010-aug-22 23:59:59','yyyy-mon-dd hh24:mi:ss')
-- and snap_id = 495
-- and snap_id >= 495 and snap_id <= 496
-- and event = 'db file sequential read'
-- and event like 'CPU%'
-- and avgwt > 5
-- and aas > .5
-- and wait_class = 'CPU'
-- and wait_class like '%I/O%'
-- and event_rank in (1,2,3)
ORDER BY snap_id
;
====
https://blog.dbi-services.com/show-the-top-10-events-from-latest-awr-snapshot/
Oracle Top 10 events from latest AWR snapshot
define 1='Top Timed Events'
define 2=''
with snap as (
select * from (
select dbid,lead(snap_id)over(partition by instance_number order by end_interval_time desc) bid,snap_id eid,row_number() over(order by end_interval_time desc) n
from dba_hist_snapshot where dbid=(select dbid from v$database)
) where n=1
),
awr as (
select rownum line,output
from table(
dbms_workload_repository.awr_report_text(l_dbid=>(select dbid from snap),l_inst_num=>(select instance_number from v$instance),l_bid=>(select bid from snap),l_eid=>(select eid from snap),l_options=>1+4+8)
)
),
awr_sections as (
select
last_value(case when regexp_replace(output,' *DB/Inst.*$') in (''
,'Database Summary'
,'Database Instances Included In Report'
,'Top Event P1/P2/P3 Values'
,'Top SQL with Top Events'
,'Top SQL with Top Row Sources'
,'Top Sessions'
,'Top Blocking Sessions'
,'Top PL/SQL Procedures'
,'Top Events'
,'Top DB Objects'
,'Activity Over Time'
,'Wait Event Histogram Detail (64 msec to 2 sec)'
,'Wait Event Histogram Detail (4 sec to 2 min)'
,'Wait Event Histogram Detail (4 min to 1 hr)'
,'SQL ordered by Elapsed Time'
,'SQL ordered by CPU Time'
,'SQL ordered by User I/O Wait Time'
,'SQL ordered by Gets'
,'SQL ordered by Reads'
,'SQL ordered by Physical Reads (UnOptimized)'
,'SQL ordered by Optimized Reads'
,'SQL ordered by Executions'
,'SQL ordered by Parse Calls'
,'SQL ordered by Sharable Memory'
,'SQL ordered by Version Count'
,'SQL ordered by Cluster Wait Time'
,'Key Instance Activity Stats'
,'Instance Activity Stats'
,'IOStat by Function summary'
,'IOStat by Filetype summary'
,'IOStat by Function/Filetype summary'
,'Tablespace IO Stats'
,'File IO Stats'
,'Checkpoint Activity'
,'MTTR Advisory'
,'Segments by Logical Reads'
,'Segments by Physical Reads'
,'Segments by Direct Physical Reads'
,'Segments by Physical Read Requests'
,'Segments by UnOptimized Reads'
,'Segments by Optimized Reads'
,'Segments by Physical Write Requests'
,'Segments by Physical Writes'
,'Segments by Direct Physical Writes'
,'Segments by DB Blocks Changes'
,'Segments by Table Scans'
,'Segments by Row Lock Waits'
,'Segments by ITL Waits'
,'Segments by Buffer Busy Waits'
,'Segments by Global Cache Buffer Busy'
,'Segments by CR Blocks Received'
,'Segments by Current Blocks Received'
,'In-Memory Segments by Scans'
,'In-Memory Segments by DB Block Changes'
,'In-Memory Segments by Populate CUs'
,'In-Memory Segments by Repopulate CUs'
,'Interconnect Device Statistics'
,'Dynamic Remastering Stats'
,'Resource Manager Plan Statistics'
,'Resource Manager Consumer Group Statistics'
,'Replication System Resource Usage'
,'Replication SGA Usage'
,'GoldenGate Capture'
,'GoldenGate Capture Rate'
,'GoldenGate Apply Reader'
,'GoldenGate Apply Coordinator'
,'GoldenGate Apply Server'
,'GoldenGate Apply Coordinator Rate'
,'GoldenGate Apply Reader and Server Rate'
,'XStream Capture'
,'XStream Capture Rate'
,'XStream Apply Reader'
,'XStream Apply Coordinator'
,'XStream Apply Server'
,'XStream Apply Coordinator Rate'
,'XStream Apply Reader and Server Rate'
,'Table Statistics by DML Operations'
,'Table Statistics by Conflict Resolutions'
,'Replication Large Transaction Statistics'
,'Replication Long Running Transaction Statistics'
,'Streams Capture'
,'Streams Capture Rate'
,'Streams Apply'
,'Streams Apply Rate'
,'Buffered Queues'
,'Buffered Queue Subscribers'
,'Persistent Queues'
,'Persistent Queues Rate'
,'Persistent Queue Subscribers'
,'Rule Set'
,'Shared Servers Activity'
,'Shared Servers Rates'
,'Shared Servers Utilization'
,'Shared Servers Common Queue'
,'Shared Servers Dispatchers'
,'init.ora Parameters'
,'init.ora Multi-Valued Parameters'
,'Cluster Interconnect'
,'Wait Classes by Total Wait Time'
,'Top 10 Foreground Events by Total Wait Time'
,'Top ADDM Findings by Average Active Sessions'
,'Cache Sizes'
,'Host Configuration Comparison'
,'Top Timed Events'
,'Top SQL Comparison by Elapsed Time'
,'Top SQL Comparison by I/O Time'
,'Top SQL Comparison by CPU Time'
,'Top SQL Comparison by Buffer Gets'
,'Top SQL Comparison by Physical Reads'
,'Top SQL Comparison by UnOptimized Read Requests'
,'Top SQL Comparison by Optimized Reads'
,'Top SQL Comparison by Executions'
,'Top SQL Comparison by Parse Calls'
,'Top SQL Comparison by Cluster Wait Time'
,'Top SQL Comparison by Sharable Memory'
,'Top SQL Comparison by Version Count'
,'Top Segments Comparison by Logical Reads'
,'Top Segments Comparison by Physical Reads'
,'Top Segments Comparison by Direct Physical Reads'
,'Top Segments Comparison by Physical Read Requests'
,'Top Segments Comparison by Optimized Read Requests'
,'Top Segments Comparison by Physical Write Requests'
,'Top Segments Comparison by Physical Writes'
,'Top Segments Comparison by Table Scans'
,'Top Segments Comparison by DB Block Changes'
,'Top Segments by Buffer Busy Waits'
,'Top Segments by Row Lock Waits'
,'Top Segments by ITL Waits'
,'Top Segments by CR Blocks Received'
,'Top Segments by Current Blocks Received'
,'Top Segments by GC Buffer Busy Waits'
,'Top In-Memory Segments Comparison by Scans'
,'Top In-Memory Segments Comparison by DB Block Changes'
,'Top In-Memory Segments Comparison by Populate CUs'
,'Top In-Memory Segments Comparison by Repopulate CUs'
,'Service Statistics'
,'Service Statistics (RAC)'
,'Global Messaging Statistics'
,'Global CR Served Stats'
,'Global CURRENT Served Stats'
,'Replication System Resource Usage'
,'Replication SGA Usage'
,'Streams by CPU Time'
,'GoldenGate Capture'
,'GoldenGate Capture Rate'
,'GoldenGate Apply Coordinator'
,'GoldenGate Apply Reader'
,'GoldenGate Apply Server'
,'GoldenGate Apply Coordinator Rate'
,'GoldenGate Apply Reader and Server Rate'
,'XStream Capture'
,'XStream Capture Rate'
,'XStream Apply Coordinator'
,'XStream Apply Reader'
,'XStream Apply Server'
,'XStream Apply Coordinator Rate'
,'XStream Apply Reader and Server Rate'
,'Table Statistics by DML Operations'
,'Table Statistics by Conflict Resolutions'
,'Replication Large Transaction Statistics'
,'Replication Long Running Transaction Statistics'
,'Streams by IO Time'
,'Streams Capture'
,'Streams Capture Rate'
,'Streams Apply'
,'Streams Apply Rate'
,'Buffered Queues'
,'Rule Set by Evaluations'
,'Rule Set by Elapsed Time'
,'Persistent Queues'
,'Persistent Queues Rate'
,'IOStat by Function - Data Rate per Second'
,'IOStat by Function - Requests per Second'
,'IOStat by File Type - Data Rate per Second'
,'IOStat by File Type - Requests per Second'
,'Tablespace IO Stats'
,'Top File Comparison by IO'
,'Top File Comparison by Read Time'
,'Top File Comparison by Buffer Waits'
,'Key Instance Activity Stats'
,'Other Instance Activity Stats'
,'Enqueue Activity'
,'Buffer Wait Statistics'
,'Dynamic Remastering Stats'
,'Library Cache Activity'
,'Library Cache Activity (RAC)'
,'init.ora Parameters'
,'init.ora Multi-Valued Parameters'
,'Buffered Subscribers'
,'Persistent Queue Subscribers'
,'Shared Servers Activity'
,'Shared Servers Rates'
,'Shared Servers Utilization'
,'Shared Servers Common Queue'
,'Shared Servers Dispatchers'
,'Database Summary'
,'Database Instances Included In Report'
,'Top ADDM Findings by Average Active Sessions'
,'Cache Sizes'
,'OS Statistics By Instance'
,'Foreground Wait Classes - % of Total DB time'
,'Foreground Wait Classes'
,'Foreground Wait Classes - % of DB time '
,'Time Model'
,'Time Model - % of DB time'
,'System Statistics'
,'System Statistics - Per Second'
,'System Statistics - Per Transaction'
,'Global Cache Efficiency Percentages'
,'Global Cache and Enqueue Workload Characteristics'
,'Global Cache and Enqueue Messaging Statistics'
,'SysStat and Global Messaging - RAC'
,'SysStat and Global Messaging (per Sec)- RAC'
,'SysStat and Global Messaging (per Tx)- RAC'
,'CR Blocks Served Statistics'
,'Current Blocks Served Statistics'
,'Global Cache Transfer Stats'
,'Global Cache Transfer (Immediate)'
,'Cluster Interconnect'
,'Interconnect Client Statistics'
,'Interconnect Client Statistics (per Second)'
,'Interconnect Device Statistics'
,'Interconnect Device Statistics (per Second)'
,'Ping Statistics'
,'Top Timed Events'
,'Top Timed Foreground Events'
,'Top Timed Background Events'
,'Resource Manager Plan Statistics'
,'Resource Manager Consumer Group Statistics'
,'SQL ordered by Elapsed Time (Global)'
,'SQL ordered by CPU Time (Global)'
,'SQL ordered by User I/O Time (Global)'
,'SQL ordered by Gets (Global)'
,'SQL ordered by Reads (Global)'
,'SQL ordered by UnOptimized Read Requests (Global)'
,'SQL ordered by Optimized Reads (Global)'
,'SQL ordered by Cluster Wait Time (Global)'
,'SQL ordered by Executions (Global)'
,'IOStat by Function (per Second)'
,'IOStat by File Type (per Second)'
,'Segment Statistics (Global)'
,'Library Cache Activity'
,'System Statistics (Global)'
,'Global Messaging Statistics (Global)'
,'System Statistics (Absolute Values)'
,'PGA Aggregate Target Statistics'
,'Process Memory Summary'
,'init.ora Parameters'
,'init.ora Multi-valued Parameters'
,'Database Summary'
,'Database Instances Included In Report'
,'Time Model Statistics'
,'Operating System Statistics'
,'Host Utilization Percentages'
,'Global Cache Load Profile'
,'Wait Classes'
,'Wait Events'
,'Cache Sizes'
,'PGA Aggr Target Stats'
,'init.ora Parameters'
,'init.ora Multi-valued Parameters'
,'Global Cache Transfer Stats'
,' Exadata Storage Server Model'
,' Exadata Storage Server Version'
,' Exadata Storage Information'
,' Exadata Griddisks'
,' Exadata Celldisks'
,' ASM Diskgroups'
,' Exadata Non-Online Disks'
,' Exadata Alerts Summary'
,' Exadata Alerts Detail'
,'Exadata Statistics'
) then output end ) ignore nulls over(order by line) section ,output from awr
)
select output AWR_REPORT_TEXT from awr_sections
--where regexp_like(section,'&1') or regexp_like(output,'&2')
where section like ('%Events%')
--w where section like ('%Top 10 Foreground%')
-- w where section like ('%Top Event P1/P2/P3 Values%')
-- w where section like ('%Top SQL%')
/
'Top SQL Comparison by Buffer Gets'
'SQL ordered by User I/O Wait Time'
AWR_REPORT_TEXT
--------------------------------------------------------------------------------
Top 10 Foreground Events by Total Wait Time
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Total Wait Avg % DB Wait
Event Waits Time (sec) Wait time Class
------------------------------ ----------- ---------- --------- ------ --------
DB CPU 2886.9 51.8
direct path read 231,557 815.4 3.52ms 14.6 User I/O
db file sequential read 977,929 760 777.19us 13.6 User I/O
db file scattered read 634,019 585.2 923.01us 10.5 User I/O
log file sync 82,557 138.1 1.67ms 2.5 Commit
gc current block busy 136,235 118.8 872.26us 2.1 Cluster
AWR_REPORT_TEXT
--------------------------------------------------------------------------------
gc buffer busy acquire 77,351 86.7 1.12ms 1.6 Cluster
read by other session 98,405 81.4 826.93us 1.5 User I/O
db file parallel read 32,026 78.7 2.46ms 1.4 User I/O
gc buffer busy release 10,751 69.5 6.46ms 1.2 Cluster
=====
VARIABLE bid NUMBER
VARIABLE eid NUMBER
VARIABLE INST_NUMBER number
exec select max(snap_id) -1 into :bid from dba_hist_snapshot ;
exec select max(snap_id) into :eid from dba_hist_snapshot ;
col WAIT_CLASS for a20
col EVENT_NAME for a35
set linesize 300 pagesize 300
SELECT snap_id,
wait_class,
event_name,
pctdbt,
total_time_s
FROM
(SELECT a.snap_id,
wait_class,
event_name,
b.dbt,
ROUND(SUM(a.ttm) /b.dbt*100,2) pctdbt,
SUM(a.ttm) total_time_s,
dense_rank() over (partition BY a.snap_id order by SUM(a.ttm)/b.dbt*100 DESC nulls last) rnk
FROM
(SELECT snap_id,
wait_class,
event_name,
ttm
FROM
(SELECT
/*+ qb_name(systemevents) */
(CAST (s.end_interval_time AS DATE) - CAST (s.begin_interval_time AS DATE)) * 24 * 3600 ela,
s.snap_id,
wait_class,
e.event_name,
CASE
WHEN s.begin_interval_time = s.startup_time
THEN e.time_waited_micro
ELSE e.time_waited_micro - lag (e.time_waited_micro ) over (partition BY e.instance_number,e.event_name order by e.snap_id)
END ttm
FROM dba_hist_snapshot s,
dba_hist_system_event e
WHERE s.dbid = e.dbid
-- AND s.dbid = :dbid
AND s.instance_number = e.instance_number
AND s.snap_id = e.snap_id
AND s.snap_id BETWEEN :bid AND :eid
AND e.wait_class != 'Idle'
UNION ALL
SELECT
/*+ qb_name(dbcpu) */
(CAST (s.end_interval_time AS DATE) - CAST (s.begin_interval_time AS DATE)) * 24 * 3600 ela,
s.snap_id,
t.stat_name wait_class,
t.stat_name event_name,
CASE
WHEN s.begin_interval_time = s.startup_time
THEN t.value
ELSE t.value - lag (t.value ) over (partition BY s.instance_number order by s.snap_id)
END ttm
FROM dba_hist_snapshot s,
dba_hist_sys_time_model t
WHERE s.dbid = t.dbid
-- AND s.dbid = :dbid
AND s.instance_number = t.instance_number
AND s.snap_id = t.snap_id
AND s.snap_id BETWEEN :bid AND :eid
AND t.stat_name = 'DB CPU'
)
) a,
(SELECT snap_id,
SUM(dbt) dbt
FROM
(SELECT
/*+ qb_name(dbtime) */
s.snap_id,
t.instance_number,
t.stat_name nm,
CASE
WHEN s.begin_interval_time = s.startup_time
THEN t.value
ELSE t.value - lag (t.value ) over (partition BY s.instance_number order by s.snap_id)
END dbt
FROM dba_hist_snapshot s,
dba_hist_sys_time_model t
WHERE s.dbid = t.dbid
-- AND s.dbid = :dbid
AND s.instance_number = t.instance_number
AND s.snap_id = t.snap_id
AND s.snap_id BETWEEN :bid AND :eid
AND t.stat_name = 'DB time'
ORDER BY s.snap_id,
s.instance_number
)
GROUP BY snap_id
HAVING SUM(dbt) > 0
) b
WHERE a.snap_id = b.snap_id
GROUP BY a.snap_id,
a.wait_class,
a.event_name,
b.dbt
)
WHERE pctdbt > 0
AND rnk <= 10
ORDER BY snap_id,
pctdbt DESC;
SNAP_ID WAIT_CLASS EVENT_NAME PCTDBT TOTAL_TIME_S
---------- -------------------- ----------------------------------- ---------- ------------
3101 DB CPU DB CPU 71.16 7750949501
3101 Concurrency cursor: pin S wait on X 4.63 503970620
3101 User I/O direct path read 4.15 451648359
3101 Network SQL*Net message from dblink 2.76 300135356
3101 System I/O log file parallel write 2.62 285647003
3101 Commit log file sync 2.1 228775607
3101 Other LGWR any worker group .55 60287636
3101 Concurrency library cache lock .43 47314514
3101 Other oracle thread bootstrap .32 35209603
3101 System I/O db file async I/O submit .32 34775730
10 rows selected.
VARIABLE bid NUMBER
VARIABLE eid NUMBER
VARIABLE INST_NUMBER number
exec select max(snap_id) -1 into :bid from dba_hist_snapshot ;
exec select max(snap_id) into :eid from dba_hist_snapshot ;
set linesize 1000 pagesize 300
define SNAP_ID_MIN=34000
define SNAP_ID_MAX=34046
REPHEADER PAGE LEFT '~~BEGIN-MAIN-METRICS~~'
REPFOOTER PAGE LEFT '~~END-MAIN-METRICS~~'
select snap_id "snap",num_interval "dur_m", end_time "end",inst "inst",
max(decode(metric_name,'Host CPU Utilization (%)', average,null)) "os_cpu",
max(decode(metric_name,'Host CPU Utilization (%)', maxval,null)) "os_cpu_max",
max(decode(metric_name,'Host CPU Utilization (%)', STANDARD_DEVIATION,null)) "os_cpu_sd",
max(decode(metric_name,'Database Wait Time Ratio', round(average,1),null)) "db_wait_ratio",
max(decode(metric_name,'Database CPU Time Ratio', round(average,1),null)) "db_cpu_ratio",
max(decode(metric_name,'CPU Usage Per Sec', round(average/100,3),null)) "cpu_per_s",
max(decode(metric_name,'CPU Usage Per Sec', round(STANDARD_DEVIATION/100,3),null)) "cpu_per_s_sd",
max(decode(metric_name,'Host CPU Usage Per Sec', round(average/100,3),null)) "h_cpu_per_s",
max(decode(metric_name,'Host CPU Usage Per Sec', round(STANDARD_DEVIATION/100,3),null)) "h_cpu_per_s_sd",
max(decode(metric_name,'Average Active Sessions', average,null)) "aas",
max(decode(metric_name,'Average Active Sessions', STANDARD_DEVIATION,null)) "aas_sd",
max(decode(metric_name,'Average Active Sessions', maxval,null)) "aas_max",
max(decode(metric_name,'Database Time Per Sec', average,null)) "db_time",
max(decode(metric_name,'Database Time Per Sec', STANDARD_DEVIATION,null)) "db_time_sd",
max(decode(metric_name,'SQL Service Response Time', average,null)) "sql_res_t_cs",
max(decode(metric_name,'Background Time Per Sec', average,null)) "bkgd_t_per_s",
max(decode(metric_name,'Logons Per Sec', average,null)) "logons_s",
max(decode(metric_name,'Current Logons Count', average,null)) "logons_total",
max(decode(metric_name,'Executions Per Sec', average,null)) "exec_s",
max(decode(metric_name,'Hard Parse Count Per Sec', average,null)) "hard_p_s",
max(decode(metric_name,'Logical Reads Per Sec', average,null)) "l_reads_s",
max(decode(metric_name,'User Commits Per Sec', average,null)) "commits_s",
max(decode(metric_name,'Physical Read Total Bytes Per Sec', round((average)/1024/1024,1),null)) "read_mb_s",
max(decode(metric_name,'Physical Read Total Bytes Per Sec', round((maxval)/1024/1024,1),null)) "read_mb_s_max",
max(decode(metric_name,'Physical Read Total IO Requests Per Sec', average,null)) "read_iops",
max(decode(metric_name,'Physical Read Total IO Requests Per Sec', maxval,null)) "read_iops_max",
max(decode(metric_name,'Physical Reads Per Sec', average,null)) "read_bks",
max(decode(metric_name,'Physical Reads Direct Per Sec', average,null)) "read_bks_direct",
max(decode(metric_name,'Physical Write Total Bytes Per Sec', round((average)/1024/1024,1),null)) "write_mb_s",
max(decode(metric_name,'Physical Write Total Bytes Per Sec', round((maxval)/1024/1024,1),null)) "write_mb_s_max",
max(decode(metric_name,'Physical Write Total IO Requests Per Sec', average,null)) "write_iops",
max(decode(metric_name,'Physical Write Total IO Requests Per Sec', maxval,null)) "write_iops_max",
max(decode(metric_name,'Physical Writes Per Sec', average,null)) "write_bks",
max(decode(metric_name,'Physical Writes Direct Per Sec', average,null)) "write_bks_direct",
max(decode(metric_name,'Redo Generated Per Sec', round((average)/1024/1024,1),null)) "redo_mb_s",
max(decode(metric_name,'DB Block Gets Per Sec', average,null)) "db_block_gets_s",
max(decode(metric_name,'DB Block Changes Per Sec', average,null)) "db_block_changes_s",
max(decode(metric_name,'GC CR Block Received Per Second', average,null)) "gc_cr_rec_s",
max(decode(metric_name,'GC Current Block Received Per Second', average,null)) "gc_cu_rec_s",
max(decode(metric_name,'Global Cache Average CR Get Time', average,null)) "gc_cr_get_cs",
max(decode(metric_name,'Global Cache Average Current Get Time', average,null)) "gc_cu_get_cs",
max(decode(metric_name,'Global Cache Blocks Corrupted', average,null)) "gc_bk_corrupted",
max(decode(metric_name,'Global Cache Blocks Lost', average,null)) "gc_bk_lost",
max(decode(metric_name,'Active Parallel Sessions', average,null)) "px_sess",
max(decode(metric_name,'Active Serial Sessions', average,null)) "se_sess",
max(decode(metric_name,'Average Synchronous Single-Block Read Latency', average,null)) "s_blk_r_lat",
max(decode(metric_name,'Cell Physical IO Interconnect Bytes', round((average)/1024/1024,1),null)) "cell_io_int_mb",
max(decode(metric_name,'Cell Physical IO Interconnect Bytes', round((maxval)/1024/1024,1),null)) "cell_io_int_mb_max"
from(
select snap_id,num_interval,to_char(end_time,'YY/MM/DD HH24:MI') end_time,instance_number inst,metric_name,round(average,1) average,
round(maxval,1) maxval,round(standard_deviation,1) standard_deviation
from dba_hist_sysmetric_summary
where 1=1
-- and dbid = &DBID
and snap_id between :bid and :eid
--and snap_id = 920
--and instance_number = 4
and metric_name in ('Host CPU Utilization (%)','CPU Usage Per Sec','Host CPU Usage Per Sec','Average Active Sessions','Database Time Per Sec',
'Executions Per Sec','Hard Parse Count Per Sec','Logical Reads Per Sec','Logons Per Sec',
'Physical Read Total Bytes Per Sec','Physical Read Total IO Requests Per Sec','Physical Reads Per Sec','Physical Write Total Bytes Per Sec',
'Redo Generated Per Sec','User Commits Per Sec','Current Logons Count','DB Block Gets Per Sec','DB Block Changes Per Sec',
'Database Wait Time Ratio','Database CPU Time Ratio','SQL Service Response Time','Background Time Per Sec',
'Physical Write Total IO Requests Per Sec','Physical Writes Per Sec','Physical Writes Direct Per Sec','Physical Writes Direct Lobs Per Sec',
'Physical Reads Direct Per Sec','Physical Reads Direct Lobs Per Sec',
'GC CR Block Received Per Second','GC Current Block Received Per Second','Global Cache Average CR Get Time','Global Cache Average Current Get Time',
'Global Cache Blocks Corrupted','Global Cache Blocks Lost',
'Active Parallel Sessions','Active Serial Sessions','Average Synchronous Single-Block Read Latency','Cell Physical IO Interconnect Bytes'
)
)
group by snap_id,num_interval, end_time,inst
order by snap_id, end_time,inst;
=====top 10 foreground events by total wait time
set linesize 300 pagesize 200
set trimspool on
break on startup skip 1 on snap_id skip 1 duplicate
select
to_char(sss.startup_time,'dd-mon-yyyy hh24:mi:ss') startup,
stats.snap_id, event_name,
total_waits, total_timeouts, round(time_waited_micro/1e6,0) wait_seconds
from
(
select
snap_id, dbid, instance_number, event_name,
total_waits, total_timeouts, time_waited_micro,
row_number() over (partition by snap_id order by time_waited_micro desc) rn
from
(
select
snap_id, dbid, instance_number, event_name,
total_waits_fg - lag(total_waits_fg,1) over (partition by event_name order by snap_id) total_waits,
total_timeouts_fg - lag(total_timeouts_fg,1) over (partition by event_name order by snap_id) total_timeouts,
time_waited_micro_fg - lag(time_waited_micro_fg,1) over (partition by event_name order by snap_id) time_waited_micro
from dba_hist_system_event sse
where dbid = (select dbid from v$database)
-- and instance_number = (select instance_number from v$instance)
and wait_class != 'Idle'
)
) stats,
dba_hist_snapshot sss
where
rn <= 10
and sss.begin_interval_time >= sysdate -4/24 ---<<<<< last 4 hr
and sss.snap_id = stats.snap_id
and sss.dbid = stats.dbid
and sss.instance_number = stats.instance_number
order by
snap_id, wait_seconds desc
/
====
Between snap!!!
https://github.com/karlarao/run_awr-quickextract/blob/master/run_awr_topevents.sql
VARIABLE BgnSnap NUMBER
VARIABLE EndSnap NUMBER
VARIABLE DID NUMBER
VARIABLE INST_NUMBER number
VARIABLE x VARCHAR2(30)
exec select max(snap_id) -24 into :BgnSnap from dba_hist_snapshot ;
exec select max(snap_id) into :EndSnap from dba_hist_snapshot ;
exec select DBID into :DID from v$database;
exec select INSTANCE_NUMBER into :INST_NUMBER from v$instance ;
set feedback off pages 0 term off head on und off trimspool on echo off lines 4000
set arraysize 5000
set termout off
set echo off verify off
COLUMN dbid NEW_VALUE _dbid NOPRINT
select dbid from v$database;
COLUMN name NEW_VALUE _instname NOPRINT
select lower(instance_name) name from v$instance;
COLUMN name NEW_VALUE _hostname NOPRINT
select lower(host_name) name from v$instance;
COLUMN instancenumber NEW_VALUE _instancenumber NOPRINT
select instance_number instancenumber from v$instance;
-- ttitle center 'AWR Top Events Report' skip 2
set pagesize 50000
set linesize 550
col instname format a15
col hostname format a30
col snap_id format 99999 heading snap_id -- "snapid"
col tm format a17 heading tm -- "tm"
col inst format 90 heading inst -- "inst"
col dur format 999990.00 heading dur -- "dur"
col event format a55 heading event -- "Event"
col event_rank format 90 heading event_rank -- "EventRank"
col waits format 9999999990.00 heading waits -- "Waits"
col time format 9999999990.00 heading time -- "Timesec"
col avgwt format 99990.00 heading avgwt -- "Avgwtms"
col pctdbt format 9990.0 heading pctdbt -- "DBTimepct"
col aas format 990.0 heading aas -- "Aas"
col wait_class format a15 heading wait_class -- "WaitClass"
VARIABLE g_retention NUMBER
DEFINE p_default = 8
DEFINE p_max = 300
SET VERIFY OFF
DECLARE
v_default NUMBER(3) := &p_default;
v_max NUMBER(3) := &p_max;
BEGIN
select
((TRUNC(SYSDATE) + RETENTION - TRUNC(SYSDATE)) * 86400)/60/60/24 AS RETENTION_DAYS
into :g_retention
from dba_hist_wr_control
where dbid in (select dbid from v$database);
if :g_retention > v_default then
:g_retention := v_max;
else
:g_retention := v_default;
end if;
END;
/
-- spool awr_topevents-tableau-&_instname-&_hostname..csv
select trim('&_instname') instname, trim('&_dbid') db_id, trim('&_hostname') hostname, snap_id, tm, inst, dur, event, event_rank, waits, time, avgwt, pctdbt, aas, wait_class
from
(select snap_id, TO_CHAR(tm,'MM/DD/YY HH24:MI:SS') tm, inst, dur, event, waits, time, avgwt, pctdbt, aas, wait_class,
DENSE_RANK() OVER (
PARTITION BY snap_id ORDER BY time DESC) event_rank
from
(
select * from
(select * from
(select
s0.snap_id snap_id,
s0.END_INTERVAL_TIME tm,
s0.instance_number inst,
round(EXTRACT(DAY FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 1440
+ EXTRACT(HOUR FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 60
+ EXTRACT(MINUTE FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME)
+ EXTRACT(SECOND FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) / 60, 2) dur,
e.event_name event,
e.total_waits - nvl(b.total_waits,0) waits,
round ((e.time_waited_micro - nvl(b.time_waited_micro,0))/1000000, 2) time, -- THIS IS EVENT (sec)
round (decode ((e.total_waits - nvl(b.total_waits, 0)), 0, to_number(NULL), ((e.time_waited_micro - nvl(b.time_waited_micro,0))/1000) / (e.total_waits - nvl(b.total_waits,0))), 2) avgwt,
((round ((e.time_waited_micro - nvl(b.time_waited_micro,0))/1000000, 2)) / NULLIF(((s5t1.value - nvl(s5t0.value,0)) / 1000000),0))*100 as pctdbt, -- THIS IS EVENT (sec) / DB TIME (sec)
(round ((e.time_waited_micro - nvl(b.time_waited_micro,0))/1000000, 2))/60 / round(EXTRACT(DAY FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 1440
+ EXTRACT(HOUR FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 60
+ EXTRACT(MINUTE FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME)
+ EXTRACT(SECOND FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) / 60, 2) aas, -- THIS IS EVENT (min) / SnapDur (min) TO GET THE % DB CPU ON AAS
e.wait_class wait_class
from
dba_hist_snapshot s0,
dba_hist_snapshot s1,
dba_hist_system_event b,
dba_hist_system_event e,
dba_hist_sys_time_model s5t0,
dba_hist_sys_time_model s5t1
where
s0.dbid = &_dbid -- CHANGE THE DBID HERE!
AND s1.dbid = s0.dbid
and b.dbid(+) = s0.dbid
and e.dbid = s0.dbid
AND s5t0.dbid = s0.dbid
AND s5t1.dbid = s0.dbid
--AND s0.instance_number = &_instancenumber -- CHANGE THE INSTANCE_NUMBER HERE!
AND s1.instance_number = s0.instance_number
and b.instance_number(+) = s0.instance_number
and e.instance_number = s0.instance_number
AND s5t0.instance_number = s0.instance_number
AND s5t1.instance_number = s0.instance_number
AND s1.snap_id = s0.snap_id + 1
AND b.snap_id(+) = s0.snap_id
and e.snap_id = s0.snap_id + 1
AND s5t0.snap_id = s0.snap_id
AND s5t1.snap_id = s0.snap_id + 1
AND s5t0.stat_name = 'DB time'
AND s5t1.stat_name = s5t0.stat_name
and b.event_id = e.event_id
and e.wait_class != 'Idle'
and e.total_waits > nvl(b.total_waits,0)
and e.event_name not in ('smon timer',
'pmon timer',
'dispatcher timer',
'dispatcher listen timer',
'rdbms ipc message')
order by snap_id, time desc, waits desc, event)
union all
select
s0.snap_id snap_id,
s0.END_INTERVAL_TIME tm,
s0.instance_number inst,
round(EXTRACT(DAY FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 1440
+ EXTRACT(HOUR FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 60
+ EXTRACT(MINUTE FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME)
+ EXTRACT(SECOND FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) / 60, 2) dur,
'CPU time',
0,
round ((s6t1.value - s6t0.value) / 1000000, 2) as time, -- THIS IS DB CPU (sec)
0,
((round ((s6t1.value - s6t0.value) / 1000000, 2)) / NULLIF(((s5t1.value - nvl(s5t0.value,0)) / 1000000),0))*100 as pctdbt, -- THIS IS DB CPU (sec) / DB TIME (sec)..TO GET % OF DB CPU ON DB TIME FOR TOP 5 TIMED EVENTS SECTION
(round ((s6t1.value - s6t0.value) / 1000000, 2))/60 / round(EXTRACT(DAY FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 1440
+ EXTRACT(HOUR FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 60
+ EXTRACT(MINUTE FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME)
+ EXTRACT(SECOND FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) / 60, 2) aas, -- THIS IS DB CPU (min) / SnapDur (min) TO GET THE % DB CPU ON AAS
'CPU'
from
dba_hist_snapshot s0,
dba_hist_snapshot s1,
dba_hist_sys_time_model s6t0,
dba_hist_sys_time_model s6t1,
dba_hist_sys_time_model s5t0,
dba_hist_sys_time_model s5t1
WHERE
s0.dbid = &_dbid -- CHANGE THE DBID HERE!
AND s1.dbid = s0.dbid
AND s6t0.dbid = s0.dbid
AND s6t1.dbid = s0.dbid
AND s5t0.dbid = s0.dbid
AND s5t1.dbid = s0.dbid
--AND s0.instance_number = &_instancenumber -- CHANGE THE INSTANCE_NUMBER HERE!
AND s1.instance_number = s0.instance_number
AND s6t0.instance_number = s0.instance_number
AND s6t1.instance_number = s0.instance_number
AND s5t0.instance_number = s0.instance_number
AND s5t1.instance_number = s0.instance_number
AND s1.snap_id = s0.snap_id + 1
AND s6t0.snap_id = s0.snap_id
AND s6t1.snap_id = s0.snap_id + 1
AND s5t0.snap_id = s0.snap_id
AND s5t1.snap_id = s0.snap_id + 1
AND s6t0.stat_name = 'DB CPU'
AND s6t1.stat_name = s6t0.stat_name
AND s5t0.stat_name = 'DB time'
AND s5t1.stat_name = s5t0.stat_name
union all
(select
dbtime.snap_id,
dbtime.tm,
dbtime.inst,
dbtime.dur,
'CPU wait',
0,
round(dbtime.time - accounted_dbtime.time, 2) time, -- THIS IS UNACCOUNTED FOR DB TIME (sec)
0,
((dbtime.aas - accounted_dbtime.aas)/ NULLIF(nvl(dbtime.aas,0),0))*100 as pctdbt, -- THIS IS UNACCOUNTED FOR DB TIME (sec) / DB TIME (sec)
round(dbtime.aas - accounted_dbtime.aas, 2) aas, -- AAS OF UNACCOUNTED FOR DB TIME
'CPU wait'
from
(select
s0.snap_id,
s0.END_INTERVAL_TIME tm,
s0.instance_number inst,
round(EXTRACT(DAY FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 1440
+ EXTRACT(HOUR FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 60
+ EXTRACT(MINUTE FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME)
+ EXTRACT(SECOND FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) / 60, 2) dur,
'DB time',
0,
round ((s5t1.value - s5t0.value) / 1000000, 2) as time, -- THIS IS DB time (sec)
0,
0,
(round ((s5t1.value - s5t0.value) / 1000000, 2))/60 / round(EXTRACT(DAY FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 1440
+ EXTRACT(HOUR FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 60
+ EXTRACT(MINUTE FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME)
+ EXTRACT(SECOND FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) / 60, 2) aas,
'DB time'
from
dba_hist_snapshot s0,
dba_hist_snapshot s1,
dba_hist_sys_time_model s5t0,
dba_hist_sys_time_model s5t1
WHERE
s0.dbid = &_dbid -- CHANGE THE DBID HERE!
AND s1.dbid = s0.dbid
AND s5t0.dbid = s0.dbid
AND s5t1.dbid = s0.dbid
--AND s0.instance_number = &_instancenumber -- CHANGE THE INSTANCE_NUMBER HERE!
AND s1.instance_number = s0.instance_number
AND s5t0.instance_number = s0.instance_number
AND s5t1.instance_number = s0.instance_number
AND s1.snap_id = s0.snap_id + 1
AND s5t0.snap_id = s0.snap_id
AND s5t1.snap_id = s0.snap_id + 1
AND s5t0.stat_name = 'DB time'
AND s5t1.stat_name = s5t0.stat_name) dbtime,
(select snap_id, inst, sum(time) time, sum(AAS) aas from
(select * from (select
s0.snap_id snap_id,
s0.END_INTERVAL_TIME tm,
s0.instance_number inst,
round(EXTRACT(DAY FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 1440
+ EXTRACT(HOUR FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 60
+ EXTRACT(MINUTE FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME)
+ EXTRACT(SECOND FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) / 60, 2) dur,
e.event_name event,
e.total_waits - nvl(b.total_waits,0) waits,
round ((e.time_waited_micro - nvl(b.time_waited_micro,0))/1000000, 2) time, -- THIS IS EVENT (sec)
round (decode ((e.total_waits - nvl(b.total_waits, 0)), 0, to_number(NULL), ((e.time_waited_micro - nvl(b.time_waited_micro,0))/1000) / (e.total_waits - nvl(b.total_waits,0))), 2) avgwt,
((round ((e.time_waited_micro - nvl(b.time_waited_micro,0))/1000000, 2)) / NULLIF(((s5t1.value - nvl(s5t0.value,0)) / 1000000),0))*100 as pctdbt, -- THIS IS EVENT (sec) / DB TIME (sec)
(round ((e.time_waited_micro - nvl(b.time_waited_micro,0))/1000000, 2))/60 / round(EXTRACT(DAY FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 1440
+ EXTRACT(HOUR FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 60
+ EXTRACT(MINUTE FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME)
+ EXTRACT(SECOND FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) / 60, 2) aas, -- THIS IS EVENT (min) / SnapDur (min) TO GET THE % DB CPU ON AAS
e.wait_class wait_class
from
dba_hist_snapshot s0,
dba_hist_snapshot s1,
dba_hist_system_event b,
dba_hist_system_event e,
dba_hist_sys_time_model s5t0,
dba_hist_sys_time_model s5t1
where
s0.dbid = &_dbid -- CHANGE THE DBID HERE!
AND s1.dbid = s0.dbid
and b.dbid(+) = s0.dbid
and e.dbid = s0.dbid
AND s5t0.dbid = s0.dbid
AND s5t1.dbid = s0.dbid
--AND s0.instance_number = &_instancenumber -- CHANGE THE INSTANCE_NUMBER HERE!
AND s1.instance_number = s0.instance_number
and b.instance_number(+) = s0.instance_number
and e.instance_number = s0.instance_number
AND s5t0.instance_number = s0.instance_number
AND s5t1.instance_number = s0.instance_number
AND s1.snap_id = s0.snap_id + 1
AND b.snap_id(+) = s0.snap_id
and e.snap_id = s0.snap_id + 1
AND s5t0.snap_id = s0.snap_id
AND s5t1.snap_id = s0.snap_id + 1
AND s5t0.stat_name = 'DB time'
AND s5t1.stat_name = s5t0.stat_name
and b.event_id = e.event_id
and e.wait_class != 'Idle'
and e.total_waits > nvl(b.total_waits,0)
and e.event_name not in ('smon timer',
'pmon timer',
'dispatcher timer',
'dispatcher listen timer',
'rdbms ipc message')
order by snap_id, time desc, waits desc, event)
union all
select
s0.snap_id snap_id,
s0.END_INTERVAL_TIME tm,
s0.instance_number inst,
round(EXTRACT(DAY FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 1440
+ EXTRACT(HOUR FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 60
+ EXTRACT(MINUTE FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME)
+ EXTRACT(SECOND FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) / 60, 2) dur,
'CPU time',
0,
round ((s6t1.value - s6t0.value) / 1000000, 2) as time, -- THIS IS DB CPU (sec)
0,
((round ((s6t1.value - s6t0.value) / 1000000, 2)) / NULLIF(((s5t1.value - nvl(s5t0.value,0)) / 1000000),0))*100 as pctdbt, -- THIS IS DB CPU (sec) / DB TIME (sec)..TO GET % OF DB CPU ON DB TIME FOR TOP 5 TIMED EVENTS SECTION
(round ((s6t1.value - s6t0.value) / 1000000, 2))/60 / round(EXTRACT(DAY FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 1440
+ EXTRACT(HOUR FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 60
+ EXTRACT(MINUTE FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME)
+ EXTRACT(SECOND FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) / 60, 2) aas, -- THIS IS DB CPU (min) / SnapDur (min) TO GET THE % DB CPU ON AAS
'CPU'
from
dba_hist_snapshot s0,
dba_hist_snapshot s1,
dba_hist_sys_time_model s6t0,
dba_hist_sys_time_model s6t1,
dba_hist_sys_time_model s5t0,
dba_hist_sys_time_model s5t1
WHERE
s0.dbid = &_dbid -- CHANGE THE DBID HERE!
AND s1.dbid = s0.dbid
AND s6t0.dbid = s0.dbid
AND s6t1.dbid = s0.dbid
AND s5t0.dbid = s0.dbid
AND s5t1.dbid = s0.dbid
--AND s0.instance_number = &_instancenumber -- CHANGE THE INSTANCE_NUMBER HERE!
AND s1.instance_number = s0.instance_number
AND s6t0.instance_number = s0.instance_number
AND s6t1.instance_number = s0.instance_number
AND s5t0.instance_number = s0.instance_number
AND s5t1.instance_number = s0.instance_number
AND s1.snap_id = s0.snap_id + 1
AND s6t0.snap_id = s0.snap_id
AND s6t1.snap_id = s0.snap_id + 1
AND s5t0.snap_id = s0.snap_id
AND s5t1.snap_id = s0.snap_id + 1
AND s6t0.stat_name = 'DB CPU'
AND s6t1.stat_name = s6t0.stat_name
AND s5t0.stat_name = 'DB time'
AND s5t1.stat_name = s5t0.stat_name
) group by snap_id, inst) accounted_dbtime
where dbtime.snap_id = accounted_dbtime.snap_id
and dbtime.inst = accounted_dbtime.inst
)
)
)
)
WHERE event_rank <= 5
--AND to_date(tm,'MM/DD/YY HH24:MI:SS') > sysdate - :g_retention
and snap_id >= :BgnSnap and snap_id <= :EndSnap
-- AND TO_CHAR(tm,'D') >= 1 -- Day of week: 1=Sunday 7=Saturday
-- AND TO_CHAR(tm,'D') <= 7
-- AND TO_CHAR(tm,'HH24MI') >= 0900 -- Hour
-- AND TO_CHAR(tm,'HH24MI') <= 1800
-- AND tm >= TO_DATE('2010-jan-17 00:00:00','yyyy-mon-dd hh24:mi:ss') -- Data range
-- AND tm <= TO_DATE('2010-aug-22 23:59:59','yyyy-mon-dd hh24:mi:ss')
-- and snap_id = 495
-- and event = 'db file sequential read'
-- and event like 'CPU%'
-- and avgwt > 5
-- and aas > .5
-- and wait_class = 'CPU'
-- and wait_class like '%I/O%'
-- and event_rank in (1,2,3)
ORDER BY snap_id;
===
AWR Top 5 Timed Foreground Events
col Event for a25
VARIABLE BEGIN_SNAP_ID NUMBER
VARIABLE END_SNAP_ID NUMBER
VARIABLE DBID NUMBER
VARIABLE INSTANCE_NUMBER number
VARIABLE x VARCHAR2(30)
exec select max(snap_id) -1 into :BEGIN_SNAP_ID from dba_hist_snapshot ;
exec select max(snap_id) into :END_SNAP_ID from dba_hist_snapshot ;
exec select DBID into :DBID from v$database;
exec select INSTANCE_NUMBER into :INSTANCE_NUMBER from v$instance ;
set pagesize 10000
set linesize 10000
select case wait_rank when 1 then inst_id end "Inst Num",
case wait_rank when 1 then snap_id end "Snap Id",
case wait_rank when 1 then begin_snap end "Begin Snap",
case wait_rank when 1 then end_snap end "End Snap",
event_name "Event",
total_waits "Waits",
time_waited "Time(s)",
round((time_waited/total_waits)*1000) "Avg wait(ms)",
round((time_waited/db_time)*100, 2) "% DB time",
substr(wait_class, 1, 15) "Wait Class"
from (
select
inst_id,
snap_id, to_char(begin_snap, 'DD-MM-YY hh24:mi:ss') begin_snap,
to_char(end_snap, 'hh24:mi:ss') end_snap,
event_name,
wait_class,
total_waits,
time_waited,
dense_rank() over (partition by inst_id, snap_id order by time_waited desc)-1 wait_rank,
max(time_waited) over (partition by inst_id, snap_id) db_time
from (
select
s.instance_number inst_id,
s.snap_id,
s.begin_interval_time begin_snap,
s.end_interval_time end_snap,
event_name,
wait_class,
total_waits-lag(total_waits, 1, total_waits) over
(partition by s.startup_time, s.instance_number, stats.event_name order by s.snap_id) total_waits,
time_waited-lag(time_waited, 1, time_waited) over
(partition by s.startup_time, s.instance_number, stats.event_name order by s.snap_id) time_waited,
min(s.snap_id) over (partition by s.startup_time, s.instance_number, stats.event_name) min_snap_id
from (
select dbid, instance_number, snap_id, event_name, wait_class, total_waits_fg total_waits, round(time_waited_micro_fg/1000000, 2) time_waited
from dba_hist_system_event
where wait_class not in ('Idle', 'System I/O')
union all
select dbid, instance_number, snap_id, stat_name event_name, null wait_class, null total_waits, round(value/1000000, 2) time_waited
from dba_hist_sys_time_model
where stat_name in ('DB CPU', 'DB time')
) stats, dba_hist_snapshot s
where stats.instance_number=s.instance_number
and stats.snap_id=s.snap_id
and stats.dbid=s.dbid
-- and s.instance_number=&instance_number
and stats.snap_id between :BEGIN_SNAP_ID and :END_SNAP_ID
) where snap_id > min_snap_id and nvl(total_waits,1) > 0
) where event_name!='DB time' and wait_rank <= 5
order by inst_id, snap_id;
====
