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;
====
Top 5 Event
set linesize 500
VARIABLE BgnSnap NUMBER
VARIABLE EndSnap NUMBER
VARIABLE DID NUMBER
VARIABLE INST_NUMBER number
VARIABLE x VARCHAR2(30)
exec select max(snap_id) -1 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 ;
col event format a35 heading "Event"
col waits format 999,999,990 heading "Waits"
col time format 999,999,990 heading "Time (s)"
col avgwt format 990 heading "Avg|wait|(ms)"
col pctwtt format 9,999.9 heading "%Total| Call| Time"
col wait_class format a30 heading "Wait Class" justify right
SELECT EVENT,
WAITS,
TIME,
DECODE(WAITS,
NULL,
TO_NUMBER(NULL),
0,
TO_NUMBER(NULL),
TIME / WAITS * 1000) AVGWT,
PCTWTT,
WAIT_CLASS
FROM (SELECT EVENT, WAITS, TIME, PCTWTT, WAIT_CLASS
FROM (SELECT E.EVENT_NAME EVENT,
E.TOTAL_WAITS - NVL(B.TOTAL_WAITS, 0) WAITS,
(E.TIME_WAITED_MICRO - NVL(B.TIME_WAITED_MICRO, 0)) /1000000 TIME,100 * (E.TIME_WAITED_MICRO - NVL(B.TIME_WAITED_MICRO, 0)) /((SELECT sum(value) FROM DBA_HIST_SYS_TIME_MODEL e
WHERE e.SNAP_ID = :EndSnap
AND e.DBID = :DID
AND e.INSTANCE_NUMBER = :INST_NUMBER
AND e.STAT_NAME = 'DB time') -
(SELECT sum(value)
FROM DBA_HIST_SYS_TIME_MODEL b
WHERE b.SNAP_ID = :BgnSnap
AND b.DBID = :DID
AND b.INSTANCE_NUMBER = :INST_NUMBER
AND b.STAT_NAME = 'DB time')) PCTWTT,
E.WAIT_CLASS WAIT_CLASS
FROM DBA_HIST_SYSTEM_EVENT B, DBA_HIST_SYSTEM_EVENT E
WHERE B.SNAP_ID(+) = :BgnSnap
AND E.SNAP_ID = :EndSnap
AND B.DBID(+) = :DID
AND E.DBID = :DID
AND B.INSTANCE_NUMBER(+) = :INST_NUMBER
AND E.INSTANCE_NUMBER = :INST_NUMBER
AND B.EVENT_ID(+) = E.EVENT_ID
AND E.TOTAL_WAITS > NVL(B.TOTAL_WAITS, 0)
AND E.WAIT_CLASS != 'Idle'
UNION ALL
SELECT 'CPU time' EVENT,
TO_NUMBER(NULL) WAITS,
((SELECT sum(value)
FROM DBA_HIST_SYS_TIME_MODEL e
WHERE e.SNAP_ID = :EndSnap
AND e.DBID = :DID
AND e.INSTANCE_NUMBER = :INST_NUMBER
AND e.STAT_NAME = 'DB CPU') - (SELECT sum(value)
FROM DBA_HIST_SYS_TIME_MODEL b
WHERE b.SNAP_ID = :BgnSnap
AND b.DBID =:DID
AND b.INSTANCE_NUMBER = :INST_NUMBER
AND b.STAT_NAME = 'DB CPU')) / 1000000 TIME,
100 * ((SELECT sum(value)
FROM DBA_HIST_SYS_TIME_MODEL e
WHERE e.SNAP_ID = :EndSnap
AND e.DBID = :DID
AND e.INSTANCE_NUMBER = :INST_NUMBER
AND e.STAT_NAME = 'DB CPU') -
(SELECT sum(value)
FROM DBA_HIST_SYS_TIME_MODEL b
WHERE b.SNAP_ID = :BgnSnap
AND b.DBID = :DID
AND b.INSTANCE_NUMBER = :INST_NUMBER
AND b.STAT_NAME = 'DB CPU')) /
((SELECT sum(value)
FROM DBA_HIST_SYS_TIME_MODEL e
WHERE e.SNAP_ID = :EndSnap
AND e.DBID = :DID
AND e.INSTANCE_NUMBER = :INST_NUMBER
AND e.STAT_NAME = 'DB time') -
(SELECT sum(value)
FROM DBA_HIST_SYS_TIME_MODEL b
WHERE b.SNAP_ID = :BgnSnap
AND b.DBID = :DID
AND b.INSTANCE_NUMBER = :INST_NUMBER
AND b.STAT_NAME = 'DB time')) PCTWTT,
NULL WAIT_CLASS
from dual
WHERE ((SELECT sum(value)
FROM DBA_HIST_SYS_TIME_MODEL e
WHERE e.SNAP_ID = :EndSnap
AND e.DBID = :DID
AND e.INSTANCE_NUMBER = :INST_NUMBER
AND e.STAT_NAME = 'DB CPU') -
(SELECT sum(value)
FROM DBA_HIST_SYS_TIME_MODEL b
WHERE b.SNAP_ID = :BgnSnap
AND b.DBID =:DID
AND b.INSTANCE_NUMBER = :INST_NUMBER
AND b.STAT_NAME = 'DB CPU')) > 0)
ORDER BY TIME DESC, WAITS DESC)
WHERE ROWNUM <= 5;
Avg %Total
wait Call
Event Waits Time (s) (ms) Time Wait Class
----------------------------------- ------------ ------------ ---- -------- --------------------
CPU time 33,930 86.3
SQL*Net more data from client 34,494 4,147 120 10.6 Network
db file async I/O submit 537,638 684 1 1.7 System I/O
cursor: pin S wait on X 1,836 460 251 1.2 Concurrency
db file sequential read 37,938,424 331 0 .8 User I/O
SQL> SQL> SQL>
************************
top event
VARIABLE BgnSnap NUMBER
VARIABLE EndSnap NUMBER
VARIABLE DID NUMBER
VARIABLE INST_NUMBER number
VARIABLE x VARCHAR2(30)
exec select max(snap_id) -1 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 ;
COLUMN DBTIME NEW_VALUE _dbtime NOPRINT
COLUMN TCPU NEW_VALUE _TCPU NOPRINT
col TCPU for 99999999999999
col DBTIME for 9999999999999
set linesize 300
select DBID, DB_NAME , INSTANCE_NUMBER, INST_NAME , BEGIN_SNAP_ID, END_SNAP_ID , TCPU , DBTIME from (select dbid,
db_name,
instance_number,
inst_name,
begin_snap_id,
end_snap_id,
(SELECT sum(e.VALUE-b.value) as diff_value
FROM dba_hist_sys_time_model B,
dba_hist_sys_time_model E
WHERE e.dbid = b.dbid
and e.instance_number = b.instance_number
and e.STAT_ID = b.STAT_ID
and B.DBID = base_info.dbid
AND B.INSTANCE_NUMBER = base_info.instance_number
AND B.SNAP_ID = base_info.begin_snap_id
AND E.SNAP_ID = base_info.end_snap_id
and e.stat_name in ('DB CPU') ) as tcpu,
(SELECT sum(e.VALUE-b.value) as diff_value
FROM dba_hist_sys_time_model B,
dba_hist_sys_time_model E
WHERE e.dbid = b.dbid
and e.instance_number = b.instance_number
and e.STAT_ID = b.STAT_ID
and B.DBID = base_info.dbid
AND B.INSTANCE_NUMBER = base_info.instance_number
AND B.SNAP_ID = base_info.begin_snap_id
AND E.SNAP_ID = base_info.end_snap_id
and e.stat_name in ('DB time') ) as dbtime
from (with db_info as (select d.dbid dbid,
d.name db_name,
i.instance_number instance_number,
i.instance_name inst_name
from v$database d, v$instance i),
snap_info as (select c.*,
EXTRACT(DAY FROM c.max_end_interval_time - c.min_end_interval_time) * 86400
+ EXTRACT(HOUR FROM c.max_end_interval_time - c.min_end_interval_time) * 3600
+ EXTRACT(MINUTE FROM c.max_end_interval_time - c.min_end_interval_time) * 60
+ EXTRACT(SECOND FROM c.max_end_interval_time - c.min_end_interval_time) ELAPSED
from (select min(snap_id) begin_snap_id,
max(snap_id) end_snap_id,
min(END_INTERVAL_TIME) as min_end_interval_time,
max(END_INTERVAL_TIME) as max_end_interval_time
from dba_hist_snapshot sn
where 1=1
and SNAP_ID between :BgnSnap and :EndSnap
-- sn.begin_interval_time >= trunc(sysdate) - 1 and sn.begin_interval_time < sysdate
) c
)
select * from db_info, snap_info)
base_info)
;
col Event for a35
col "Wait Class" for a20
select event "Event",
waits "Waits",
round(time,2) "Time(s)",
round(avwait,2) "Avg wait (ms)",
round(pctwtt,2) "% DB time",
wcls "Wait Class"
from ( select event
, wtfg waits
, tmfg/1000000 time
, decode(wtfg, 0, to_number(null), tmfg/wtfg)/1000 avwait
, decode(&_dbtime, 0, to_number(null), tmfg/&_dbtime)*100 pctwtt
, wcls
from ( select event, wtfg, ttofg, tmfg, wcls
from (
select e.event_name event
, case when e.total_waits_fg is not null
then e.total_waits_fg - nvl(b.total_waits_fg,0)
else (e.total_waits - nvl(b.total_waits,0))
- greatest(0,(nvl(ebg.total_waits,0)
- nvl(bbg.total_waits,0)))
end wtfg
, case when e.total_timeouts_fg is not null
then e.total_timeouts_fg - nvl(b.total_timeouts_fg,0)
else (e.total_timeouts - nvl(b.total_timeouts,0))
- greatest(0,(nvl(ebg.total_timeouts,0)
- nvl(bbg.total_timeouts,0)))
end ttofg
, case when e.time_waited_micro_fg is not null
then e.time_waited_micro_fg - nvl(b.time_waited_micro_fg,0)
else (e.time_waited_micro - nvl(b.time_waited_micro,0))
- greatest(0,(nvl(ebg.time_waited_micro,0)
- nvl(bbg.time_waited_micro,0)))
end tmfg
, e.wait_class wcls
from dba_hist_system_event b
, dba_hist_system_event e
, dba_hist_bg_event_summary bbg
, dba_hist_bg_event_summary ebg
where b.snap_id (+) = :BgnSnap
and e.snap_id = :EndSnap
and bbg.snap_id (+) = :BgnSnap
and ebg.snap_id (+) = :EndSnap
/*and e.dbid = :dbid*/
and e.instance_number = :INST_NUMBER
and e.dbid = b.dbid (+)
and e.instance_number = b.instance_number (+)
and e.event_id = b.event_id (+)
and e.dbid = ebg.dbid (+)
and e.instance_number = ebg.instance_number (+)
and e.event_id = ebg.event_id (+)
and e.dbid = bbg.dbid (+)
and e.instance_number = bbg.instance_number (+)
and e.event_id = bbg.event_id (+)
and e.total_waits > nvl(b.total_waits,0)
and e.wait_class <> 'Idle'
union all
select 'DB CPU' event
, to_number(null) wtfg
, to_number(null) ttofg
, &_tcpu tmfg
, ' ' wcls
from dual
where &_tcpu > 0)
order by tmfg desc, wtfg desc)
where rownum <= 5
)
Event Waits Time(s) Avg wait (ms) % DB time Wait Class
----------------------------------- ---------- ---------- ------------- ---------- --------------------
DB CPU 1128290.65 32.09
SQL*Net more data from client 34697 4315.17 124.37 .12 Network
cursor: pin S wait on X 1800 457.21 254.01 .01 Concurrency
db file sequential read 34056796 211.47 .01 .01 User I/O
SQL*Net message from dblink 388380 191.16 .49 .01 Network
SQL>
set linesize 500 pagesize 300
col WAIT_CLASS for a20
col EVENT for a35
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)
SELECT EVENT,
DECODE(WAITS,
NULL,
TO_NUMBER('0'),
0,
TO_NUMBER(NULL),
TIME / WAITS * 1000) AVGWT,
PCTWTT, TIME, WAIT_CLASS
FROM (SELECT EVENT, WAITS, TIME, PCTWTT, WAIT_CLASS
FROM (SELECT E.EVENT_NAME EVENT,
E.TOTAL_WAITS - NVL(B.TOTAL_WAITS, 0) WAITS,
(E.TIME_WAITED_MICRO - NVL(B.TIME_WAITED_MICRO, 0)) /
1000000 TIME,
100 *
(E.TIME_WAITED_MICRO - NVL(B.TIME_WAITED_MICRO, 0)) /
((SELECT sum(value)
FROM DBA_HIST_SYS_TIME_MODEL e
WHERE e.SNAP_ID = (select eid from snap)
AND e.DBID = (select dbid from snap)
AND e.INSTANCE_NUMBER = (select instance_number from v$instance)
AND e.STAT_NAME = 'DB time') -
(SELECT sum(value)
FROM DBA_HIST_SYS_TIME_MODEL b
WHERE b.SNAP_ID = (select bid from snap)
AND b.DBID = (select dbid from snap)
AND b.INSTANCE_NUMBER = (select instance_number from v$instance)
AND b.STAT_NAME = 'DB time')) PCTWTT,
E.WAIT_CLASS WAIT_CLASS
FROM DBA_HIST_SYSTEM_EVENT B, DBA_HIST_SYSTEM_EVENT E
WHERE B.SNAP_ID = (select bid from snap)
AND E.SNAP_ID = (select eid from snap)
AND B.DBID = (select dbid from snap)
AND E.DBID = (select dbid from snap)
AND B.INSTANCE_NUMBER = (select instance_number from v$instance)
AND E.INSTANCE_NUMBER = (select instance_number from v$instance)
AND B.EVENT_ID(+) = E.EVENT_ID
AND E.TOTAL_WAITS > NVL(B.TOTAL_WAITS, 0)
AND E.WAIT_CLASS != 'Idle'
UNION ALL
SELECT 'CPU time' EVENT,
TO_NUMBER(NULL) WAITS,
((SELECT sum(value)
FROM DBA_HIST_SYS_TIME_MODEL e
WHERE e.SNAP_ID = (select eid from snap)
AND e.DBID = (select dbid from snap)
AND e.INSTANCE_NUMBER = (select instance_number from v$instance)
AND e.STAT_NAME = 'DB CPU') -
(SELECT sum(value)
FROM DBA_HIST_SYS_TIME_MODEL b
WHERE b.SNAP_ID = (select bid from snap)
AND b.DBID = (select dbid from snap)
AND b.INSTANCE_NUMBER = (select instance_number from v$instance)
AND b.STAT_NAME = 'DB CPU')) / 1000000 TIME,
100 * ((SELECT sum(value)
FROM DBA_HIST_SYS_TIME_MODEL e
WHERE e.SNAP_ID = (select eid from snap)
AND e.DBID = (select dbid from snap)
AND e.INSTANCE_NUMBER = (select instance_number from v$instance)
AND e.STAT_NAME = 'DB CPU') -
(SELECT sum(value)
FROM DBA_HIST_SYS_TIME_MODEL b
WHERE b.SNAP_ID = (select bid from snap)
AND b.DBID = (select dbid from snap)
AND b.INSTANCE_NUMBER = (select instance_number from v$instance)
AND b.STAT_NAME = 'DB CPU')) /
((SELECT sum(value)
FROM DBA_HIST_SYS_TIME_MODEL e
WHERE e.SNAP_ID = (select eid from snap)
AND e.DBID = (select dbid from snap)
AND e.INSTANCE_NUMBER = (select instance_number from v$instance)
AND e.STAT_NAME = 'DB time') -
(SELECT sum(value)
FROM DBA_HIST_SYS_TIME_MODEL b
WHERE b.SNAP_ID = (select bid from snap)
AND b.DBID = (select dbid from snap)
AND b.INSTANCE_NUMBER = (select instance_number from v$instance)
AND b.STAT_NAME = 'DB time')) PCTWTT,
NULL WAIT_CLASS
from dual
WHERE ((SELECT sum(value)
FROM DBA_HIST_SYS_TIME_MODEL e
WHERE e.SNAP_ID = (select eid from snap)
AND e.DBID = (select dbid from snap)
AND e.INSTANCE_NUMBER = (select instance_number from v$instance)
AND e.STAT_NAME = 'DB CPU') -
(SELECT sum(value)
FROM DBA_HIST_SYS_TIME_MODEL b
WHERE b.SNAP_ID = (select bid from snap)
AND b.DBID = (select dbid from snap)
AND b.INSTANCE_NUMBER = (select instance_number from v$instance)
AND b.STAT_NAME = 'DB CPU')) > 0)
ORDER BY TIME DESC, WAITS DESC)
WHERE ROWNUM <= 10;
EVENT AVGWT PCTWTT TIME WAIT_CLASS
----------------------------------- ---------- ---------- ---------- --------------------
CPU time 0 85.996498 32701.2587
SQL*Net more data from client 85.2547252 11.1312839 4232.81185 Network
cursor: pin S wait on X 254.015085 1.4562378 553.752886 Concurrency
log file sync 2.79578428 .550594904 209.370693 Commit
db file sequential read .005765488 .514617521 195.689837 User I/O
SQL*Net message from dblink .452614936 .460765091 175.211768 Network
log file parallel write .37174284 .352692763 134.115895 System I/O
db file async I/O submit 2.41185522 .08757232 33.300485 System I/O
direct path read .025731834 .080723604 30.696174 User I/O
LGWR any worker group .885262368 .065128926 24.7661 Other
10 rows selected.
*******************************
for snap info !!
http://anuj-singh.blogspot.com/2011/10/oracle-awr-snap-id.html
VARIABLE BgnSnap NUMBER
VARIABLE EndSnap NUMBER
VARIABLE DID NUMBER
VARIABLE INST_NUMBER number
VARIABLE x VARCHAR2(30)
exec select max(snap_id) -3 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 linesize 500 pagesize 300
col WAIT_CLASS for a20
col EVENT for a35
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)
SELECT EVENT,
DECODE(WAITS,
NULL,
TO_NUMBER('0'),
0,
TO_NUMBER(NULL),
TIME / WAITS * 1000) AVGWT,
PCTWTT, TIME, WAIT_CLASS
FROM (SELECT EVENT, WAITS, TIME, PCTWTT, WAIT_CLASS
FROM (SELECT E.EVENT_NAME EVENT,
E.TOTAL_WAITS - NVL(B.TOTAL_WAITS, 0) WAITS,
(E.TIME_WAITED_MICRO - NVL(B.TIME_WAITED_MICRO, 0)) /
1000000 TIME,
100 *
(E.TIME_WAITED_MICRO - NVL(B.TIME_WAITED_MICRO, 0)) /
((SELECT sum(value)
FROM DBA_HIST_SYS_TIME_MODEL e
WHERE e.SNAP_ID = :EndSnap
AND e.DBID = (select dbid from snap)
AND e.INSTANCE_NUMBER = (select instance_number from v$instance)
AND e.STAT_NAME = 'DB time') -
(SELECT sum(value)
FROM DBA_HIST_SYS_TIME_MODEL b
WHERE b.SNAP_ID = :BgnSnap
AND b.DBID = (select dbid from snap)
AND b.INSTANCE_NUMBER = (select instance_number from v$instance)
AND b.STAT_NAME = 'DB time')) PCTWTT,
E.WAIT_CLASS WAIT_CLASS
FROM DBA_HIST_SYSTEM_EVENT B, DBA_HIST_SYSTEM_EVENT E
WHERE B.SNAP_ID = :BgnSnap
AND E.SNAP_ID = :EndSnap
AND B.DBID = (select dbid from snap)
AND E.DBID = (select dbid from snap)
AND B.INSTANCE_NUMBER = (select instance_number from v$instance)
AND E.INSTANCE_NUMBER = (select instance_number from v$instance)
AND B.EVENT_ID(+) = E.EVENT_ID
AND E.TOTAL_WAITS > NVL(B.TOTAL_WAITS, 0)
AND E.WAIT_CLASS != 'Idle'
UNION ALL
SELECT 'CPU time' EVENT,
TO_NUMBER(NULL) WAITS,
((SELECT sum(value)
FROM DBA_HIST_SYS_TIME_MODEL e
WHERE e.SNAP_ID = :EndSnap
AND e.DBID = (select dbid from snap)
AND e.INSTANCE_NUMBER = (select instance_number from v$instance)
AND e.STAT_NAME = 'DB CPU') -
(SELECT sum(value)
FROM DBA_HIST_SYS_TIME_MODEL b
WHERE b.SNAP_ID = :BgnSnap
AND b.DBID = (select dbid from snap)
AND b.INSTANCE_NUMBER = (select instance_number from v$instance)
AND b.STAT_NAME = 'DB CPU')) / 1000000 TIME,
100 * ((SELECT sum(value)
FROM DBA_HIST_SYS_TIME_MODEL e
WHERE e.SNAP_ID = :EndSnap
AND e.DBID = (select dbid from snap)
AND e.INSTANCE_NUMBER = (select instance_number from v$instance)
AND e.STAT_NAME = 'DB CPU') -
(SELECT sum(value)
FROM DBA_HIST_SYS_TIME_MODEL b
WHERE b.SNAP_ID = :BgnSnap
AND b.DBID = (select dbid from snap)
AND b.INSTANCE_NUMBER = (select instance_number from v$instance)
AND b.STAT_NAME = 'DB CPU')) /
((SELECT sum(value)
FROM DBA_HIST_SYS_TIME_MODEL e
WHERE e.SNAP_ID = :EndSnap
AND e.DBID = (select dbid from snap)
AND e.INSTANCE_NUMBER = (select instance_number from v$instance)
AND e.STAT_NAME = 'DB time') -
(SELECT sum(value)
FROM DBA_HIST_SYS_TIME_MODEL b
WHERE b.SNAP_ID = :BgnSnap
AND b.DBID = (select dbid from snap)
AND b.INSTANCE_NUMBER = (select instance_number from v$instance)
AND b.STAT_NAME = 'DB time')) PCTWTT,
NULL WAIT_CLASS
from dual
WHERE ((SELECT sum(value)
FROM DBA_HIST_SYS_TIME_MODEL e
WHERE e.SNAP_ID = :EndSnap
AND e.DBID = (select dbid from snap)
AND e.INSTANCE_NUMBER = (select instance_number from v$instance)
AND e.STAT_NAME = 'DB CPU') -
(SELECT sum(value)
FROM DBA_HIST_SYS_TIME_MODEL b
WHERE b.SNAP_ID = :BgnSnap
AND b.DBID = (select dbid from snap)
AND b.INSTANCE_NUMBER = (select instance_number from v$instance)
AND b.STAT_NAME = 'DB CPU')) > 0)
ORDER BY TIME DESC, WAITS DESC)
WHERE ROWNUM <= 10;
=======
set numf 9999999999999999 linesize 300
col dbid1 new_value dbid2 noprint
col db_name1 new_value db_name2 noprint
col inst_name1 new_value inst_name2 noprint
col instance_number1 new_value instance_number2 noprint
col begin_snap_id1 new_value begin_snap_id2 noprint
col end_snap_id1 new_value end_snap_id2 noprint
col tcpu1 new_value tcpu2 noprint
col dbtime1 new_value dbtime2 noprint
select dbid dbid1 ,
db_name db_name1 ,
instance_number instance_number1 ,
inst_name inst_name1 ,
begin_snap_id begin_snap_id1 ,
end_snap_id end_snap_id1 ,
(SELECT sum(e.VALUE-b.value) as diff_value
FROM dba_hist_sys_time_model B,
dba_hist_sys_time_model E
WHERE e.dbid = b.dbid
and e.instance_number = b.instance_number
and e.STAT_ID = b.STAT_ID
and B.DBID = base_info.dbid
AND B.INSTANCE_NUMBER = base_info.instance_number
AND B.SNAP_ID = base_info.begin_snap_id
AND E.SNAP_ID = base_info.end_snap_id
and e.stat_name in ('DB CPU') ) tcpu1 ,
(SELECT sum(e.VALUE-b.value) as diff_value
FROM dba_hist_sys_time_model B,
dba_hist_sys_time_model E
WHERE e.dbid = b.dbid
and e.instance_number = b.instance_number
and e.STAT_ID = b.STAT_ID
and B.DBID = base_info.dbid
AND B.INSTANCE_NUMBER = base_info.instance_number
AND B.SNAP_ID = base_info.begin_snap_id
AND E.SNAP_ID = base_info.end_snap_id
and e.stat_name in ('DB time') ) dbtime1
-- into :DBID, :DB_NAME , :INSTANCE_NUMBER ,:INST_NAME , :BEGIN_SNAP_ID , :END_SNAP_ID, :TCPU, :DBTIME
from (with db_info as (select d.dbid dbid,
d.name db_name,
i.instance_number instance_number,
i.instance_name inst_name
from v$database d, v$instance i),
snap_info as (select c.*,
EXTRACT(DAY FROM c.max_end_interval_time - c.min_end_interval_time) * 86400
+ EXTRACT(HOUR FROM c.max_end_interval_time - c.min_end_interval_time) * 3600
+ EXTRACT(MINUTE FROM c.max_end_interval_time - c.min_end_interval_time) * 60
+ EXTRACT(SECOND FROM c.max_end_interval_time - c.min_end_interval_time) ELAPSED
from (select min(snap_id) begin_snap_id,
max(snap_id) end_snap_id,
min(END_INTERVAL_TIME) as min_end_interval_time,
max(END_INTERVAL_TIME) as max_end_interval_time
from dba_hist_snapshot sn
where 1=1
and sn.begin_interval_time >= trunc(sysdate) - 1/24 and sn.begin_interval_time < sysdate
) c
)
select * from db_info, snap_info) base_info
;
col Event for a35
col "Wait Class" for a15
select event "Event",
waits "Waits",
round(time,2) "Time(s)",
round(avwait,2) "Avg wait (ms)",
round(pctwtt,2) "% DB time",
wcls "Wait Class"
from ( select event
, wtfg waits
, tmfg/1000000 time
, decode(wtfg, 0, to_number(null), tmfg/wtfg)/1000 avwait
, decode(&dbtime2, 0, to_number(null), tmfg/&dbtime2)*100 pctwtt
, wcls
from ( select event, wtfg, ttofg, tmfg, wcls
from (
select e.event_name event
, case when e.total_waits_fg is not null
then e.total_waits_fg - nvl(b.total_waits_fg,0)
else (e.total_waits - nvl(b.total_waits,0))
- greatest(0,(nvl(ebg.total_waits,0)
- nvl(bbg.total_waits,0)))
end wtfg
, case when e.total_timeouts_fg is not null
then e.total_timeouts_fg - nvl(b.total_timeouts_fg,0)
else (e.total_timeouts - nvl(b.total_timeouts,0))
- greatest(0,(nvl(ebg.total_timeouts,0)
- nvl(bbg.total_timeouts,0)))
end ttofg
, case when e.time_waited_micro_fg is not null
then e.time_waited_micro_fg - nvl(b.time_waited_micro_fg,0)
else (e.time_waited_micro - nvl(b.time_waited_micro,0))
- greatest(0,(nvl(ebg.time_waited_micro,0)
- nvl(bbg.time_waited_micro,0)))
end tmfg
, e.wait_class wcls
from dba_hist_system_event b
, dba_hist_system_event e
, dba_hist_bg_event_summary bbg
, dba_hist_bg_event_summary ebg
where b.snap_id (+) = &begin_snap_id2
and e.snap_id = &end_snap_id2
and bbg.snap_id (+) = &begin_snap_id2
and ebg.snap_id (+) = &end_snap_id2
/*and e.dbid = :dbid*/
and e.instance_number = &instance_number2
and e.dbid = b.dbid (+)
and e.instance_number = b.instance_number (+)
and e.event_id = b.event_id (+)
and e.dbid = ebg.dbid (+)
and e.instance_number = ebg.instance_number (+)
and e.event_id = ebg.event_id (+)
and e.dbid = bbg.dbid (+)
and e.instance_number = bbg.instance_number (+)
and e.event_id = bbg.event_id (+)
and e.total_waits > nvl(b.total_waits,0)
and e.wait_class <> 'Idle'
union all
select 'DB CPU' event
, to_number(null) wtfg
, to_number(null) ttofg
, &tcpu2 tmfg
, ' ' wcls
from dual
where &tcpu2 > 0
)
order by tmfg desc, wtfg desc)
where rownum <= 5
)
Event Waits Time(s) Avg wait (ms) % DB time Wait Class
----------------------------------- ----------------- ----------------- ----------------- ----------------- ---------------
DB CPU 13011 95
SQL*Net vector data to client 437666295 3181 0 23 Network
direct path read 466825923 1964 0 14 User I/O
direct path write 989554 924 1 7 User I/O
SQL*Net more data to client 37250820 285 0 2 Network
SQL>
VARIABLE BgnSnap NUMBER
VARIABLE EndSnap NUMBER
VARIABLE DID NUMBER
VARIABLE INST_NUMBER number
VARIABLE x VARCHAR2(30)
exec select max(snap_id) -1 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 linesize 400 pagesize 300
COL EVENT FOR a50 HEAD "Top 10 Foreground Events by Total Wait Time"
COL Waits HEAD "Waits|"
COL TIME HEAD "Total Wait|Time(sec)"
COL AVWAIT HEAD "Wait|Avg(ms)"
COL PCTWTT for 99999.9 HEAD "% DB|time"
COL WCLS FOR a70 HEAD "Wait Class"
WITH snp
AS ( SELECT DBID,
INSTANCE_NUMBER,
MIN (SNAP_ID) bid,
MAX (SNAP_ID) eid,
ROUND (
EXTRACT (
DAY FROM MAX (END_INTERVAL_TIME)
- MIN (END_INTERVAL_TIME))
* 86400
+ EXTRACT (
HOUR FROM MAX (END_INTERVAL_TIME)
- MIN (END_INTERVAL_TIME))
* 3600
+ EXTRACT (
MINUTE FROM MAX (END_INTERVAL_TIME)
- MIN (END_INTERVAL_TIME))
* 60
+ EXTRACT (
SECOND FROM MAX (END_INTERVAL_TIME)
- MIN (END_INTERVAL_TIME)))
ets
FROM DBA_HIST_SNAPSHOT
WHERE INSTANCE_NUMBER = USERENV ('Instance')
--AND snap_id BETWEEN :BgnSnap AND :EndSnap
AND BEGIN_INTERVAL_TIME BETWEEN trunc(sysdate)-1 AND trunc(sysdate)
GROUP BY DBID, INSTANCE_NUMBER),
dbtime
AS (SELECT NVL ( (e1.VALUE - b1.VALUE), -1) dbtimev
FROM dba_hist_sys_time_model e1, dba_hist_sys_time_model b1, snp
WHERE b1.snap_id = snp.bid
AND e1.snap_id = snp.eid
AND b1.dbid = snp.DBID
AND e1.dbid = snp.DBID
AND b1.instance_number = snp.INSTANCE_NUMBER
AND e1.instance_number = snp.INSTANCE_NUMBER
AND b1.stat_name = 'DB time'
AND b1.stat_id = e1.stat_id)
SELECT event,
wtfg waits,
ROUND (tmfg / 1000000, 1) time,
ROUND (DECODE (wtfg, 0, TO_NUMBER (NULL), tmfg / wtfg) / 1000) avwait,
ROUND (DECODE (dbtimev, 0, TO_NUMBER (NULL), tmfg / dbtimev) * 100, 1)
pctwtt,
wcls
FROM ( SELECT event,
wtfg,
ttofg,
tmfg,
wcls
FROM (SELECT e.event_name event,
CASE
WHEN e.total_waits_fg IS NOT NULL
THEN
e.total_waits_fg - NVL (b.total_waits_fg, 0)
ELSE
(e.total_waits - NVL (b.total_waits, 0))
- GREATEST (
0,
(NVL (ebg.total_waits, 0)
- NVL (bbg.total_waits, 0)))
END
wtfg,
CASE
WHEN e.total_timeouts_fg IS NOT NULL
THEN
e.total_timeouts_fg
- NVL (b.total_timeouts_fg, 0)
ELSE
(e.total_timeouts - NVL (b.total_timeouts, 0))
- GREATEST (
0,
(NVL (ebg.total_timeouts, 0)
- NVL (bbg.total_timeouts, 0)))
END
ttofg,
CASE
WHEN e.time_waited_micro_fg IS NOT NULL
THEN
e.time_waited_micro_fg
- NVL (b.time_waited_micro_fg, 0)
ELSE
(e.time_waited_micro
- NVL (b.time_waited_micro, 0))
- GREATEST (
0,
(NVL (ebg.time_waited_micro, 0)
- NVL (bbg.time_waited_micro, 0)))
END
tmfg,
e.wait_class wcls
FROM dba_hist_system_event b,
dba_hist_system_event e,
dba_hist_bg_event_summary bbg,
dba_hist_bg_event_summary ebg,
snp
WHERE
/* b.snap_id (+) = :bid
and e.snap_id = :eid
and bbg.snap_id (+) = :bid
and ebg.snap_id (+) = :eid
and e.dbid = :dbid
and e.instance_number = :inst_num
b.snap_id(+) = snp.bid
*/ bbg.snap_id (+)= 35279
AND b.snap_id(+) =35279
AND ebg.snap_id (+) = 35280
AND e.snap_id = snp.eid
AND e.dbid = snp.DBID
AND e.instance_number = snp.INSTANCE_NUMBER
AND e.dbid = b.dbid(+)
AND e.instance_number = b.instance_number(+)
AND e.event_id = b.event_id(+)
AND e.dbid = ebg.dbid(+)
AND e.instance_number = ebg.instance_number(+)
AND e.event_id = ebg.event_id(+)
AND e.dbid = bbg.dbid(+)
AND e.instance_number = bbg.instance_number(+)
AND e.event_id = bbg.event_id(+)
AND e.total_waits > NVL (b.total_waits, 0)
AND e.wait_class <> 'Idle'
UNION ALL
SELECT 'DB CPU' event,
TO_NUMBER (NULL) wtfg,
TO_NUMBER (NULL) ttofg,
NVL ( (e1.VALUE - b1.VALUE), -1) tmfg,
' ' wcls
FROM dba_hist_sys_time_model e1,
dba_hist_sys_time_model b1,
snp,
dbtime
WHERE b1.snap_id = snp.bid
AND e1.snap_id = snp.eid
AND b1.dbid = snp.DBID
AND e1.dbid = snp.DBID
AND b1.instance_number = snp.INSTANCE_NUMBER
AND e1.instance_number = snp.INSTANCE_NUMBER
AND b1.stat_name = 'DB CPU'
AND b1.stat_id = e1.stat_id
UNION ALL
SELECT ' ' event,
TO_NUMBER (NULL) wtfg,
TO_NUMBER (NULL) ttofg,
TO_NUMBER (NULL) tmfg,
'------------ For Inst-'
|| snp.INSTANCE_NUMBER
|| ' btwn snap_id '
|| snp.bid
|| ' and '
|| snp.eid
|| CHR (10)
|| '------------ Elp Time '
|| ROUND (ets / 60, 2)
|| '(mins) and DB Time '
|| ROUND (dbtimev / 1000000 / 60, 2)
|| '(mins)'
wcls from
snp,
dbtime )
ORDER BY tmfg DESC, wtfg DESC, event),
dbtime
WHERE (ROWNUM <= 11 AND tmfg > 0) or wcls like '%For Inst%'
;
Waits Total Wait Wait % DB
Top 10 Foreground Events by Total Wait Time Time(sec) Avg(ms) time Wait Class
-------------------------------------------------- ---------- ---------- ---------- -------- ----------------------------------------------------------------------
------------ For Inst-1 btwn snap_id 364 and 387
------------ Elp Time 1379.68(mins) and DB Time 11699.35(mins)
log file sync 263324271 1778916.4 7 253.4 Commit
direct path read 268631222 925651.9 3 131.9 User I/O
DB CPU 274497.2 39.1
write complete waits 1218575 113776.7 93 16.2 Configuration
db file sequential read 11650627 63752.4 5 9.1 User I/O
control file sequential read 9256027 46057.1 5 6.6 System I/O
SQL*Net vector data to client 74340146 11576.6 0 1.6 Network
SQL*Net more data to client 118968690 11320.6 0 1.6 Network
db file scattered read 1724480 9921.6 6 1.4 User I/O
SQL*Net message to client 7649097212 6706.5 0 1.0 Network
11 rows selected.
****
set pages 999 lines 200
column c1 heading 'Event|Name' format a50
column c2 heading 'Total|Waits' format a10
column c3 heading 'Waiting' format a10
column c4 heading 'Total|Timeouts' format a10
column c5 heading 'Average|Wait' format a10
ttitle 'System-wide Wait Analysis|for current wait events
-- TOTAL_WAITS NUMBER Total number of waits for the event
select
event c1,
dbms_xplan.format_number(total_waits) c2,
dbms_xplan.FORMAT_TIME_S(time_waited / 100 ) c3,
dbms_xplan.format_number(total_timeouts) c4,
dbms_xplan.FORMAT_TIME_S(average_wait /100 ) c5
from
sys.v_$system_event
where
event not in (
'dispatcher timer',
'lock element cleanup',
'Null event',
'parallel query dequeue wait',
'parallel query idle wait - Slaves',
'pipe get',
'PL/SQL lock timer',
'pmon timer',
'rdbms ipc message',
'slave wait',
'smon timer',
'SQL*Net break/reset to client',
'SQL*Net message from client',
'SQL*Net message to client',
'SQL*Net more data to client',
'virtual circuit status',
'WMON goes to sleep'
)
AND
event not like 'DFS%'
and
event not like '%done%'
and
event not like '%Idle%'
AND
event not like 'KXFX%'
order by
c2 desc
;
-- from Web
set serveroutput on linesize 300 pagesize 300
COLUMN p1 NEW_VALUE 1
COLUMN p2 NEW_VALUE 2
set termout off
SELECT null p1, null p2 FROM dual WHERE 1=2;
SELECT nvl('&1','5') p1, nvl('&2','db file sequential read') p2 from dual;
set termout on
DECLARE
v_sleep_time number;
v_event_name varchar2(50) := '&2';
v_delta_waits number;
v_delta_waits_per_sec number;
v_delta_time_waited_estimate number;
v_delta_time_waited_micro number;
v_avg_wait_time_micro number;
v_latencybin varchar2(100);
CURSOR c1 IS
SELECT event, wait_time_micro, sum(wait_count) wait_count, max(last_update_time) last_update_time
FROM v$event_histogram_micro
WHERE event = v_event_name
GROUP BY event, wait_time_micro
ORDER BY event, wait_time_micro;
CURSOR c2 IS
SELECT event, sum(time_waited_micro) time_waited_micro, sum(total_waits) total_waits
FROM v$system_event
WHERE event = v_event_name
GROUP BY event
ORDER BY event;
TYPE EventHisto IS TABLE OF c1%ROWTYPE;
TYPE SysEvent IS TABLE OF c2%ROWTYPE;
t0_histval EventHisto; -- nested table of records for t0 snapshot
t1_histval EventHisto; -- nested table of records for t1 snapshot
t0_eventval SysEvent; -- nested table of records for t0 snapshot
t1_eventval SysEvent; -- nested table of records for t1 snapshot
BEGIN
-- input validation
BEGIN
v_sleep_time := TO_NUMBER('&1');
IF (v_sleep_time <= 0) THEN
raise value_error;
END IF;
EXCEPTION
WHEN value_error THEN
RAISE_APPLICATION_ERROR(-20001,'Wait time must be numeric and >0. Example use wait time = 10');
END;
-- collect t0 data
OPEN c1;
OPEN c2;
FETCH c1 BULK COLLECT INTO t0_histval;
FETCH c2 BULK COLLECT INTO t0_eventval;
CLOSE c1;
CLOSE c2;
IF t0_eventval.COUNT <=0 THEN
RAISE_APPLICATION_ERROR(-20001,'Not enough data. Probably wrong event name. Tip, try event = "db file sequential read".');
END IF;
IF t0_eventval.COUNT >= 100 THEN
RAISE_APPLICATION_ERROR(-20002,'Too many values, soft limit set to 100');
END IF;
-- put wait time here note user needs exec privilege on dbms_lock
sys.DBMS_LOCK.SLEEP (v_sleep_time);
-- collect t1 data
OPEN c1;
OPEN c2;
FETCH c1 BULK COLLECT INTO t1_histval;
FETCH c2 BULK COLLECT INTO t1_eventval;
CLOSE c1;
CLOSE c2;
-- check and report error if number of points is different between the two snapshots
-- (rare, but can happen if a new histogram bin has been created)
IF t0_histval.COUNT <> t1_histval.COUNT THEN
RAISE_APPLICATION_ERROR(-20003,'Number of histogram bins changed during collection. Cannot handle it.');
END IF;
-- print out results
-- compute delta values and print.
-- format with rpad to keep column width constant
-- Latency histogram from v$event_histogram_micro
DBMS_OUTPUT.PUT_LINE(chr(13));
DBMS_OUTPUT.PUT_LINE('Latency histogram for event "&2" from V$EVENT_HISTOGRAM_MICRO:');
DBMS_OUTPUT.PUT_LINE(chr(13));
DBMS_OUTPUT.PUT_LINE ('Latency Bucket Num Waits/DeltaT Wait Time/DeltaT Event Name Last Update Time');
DBMS_OUTPUT.PUT_LINE ('(microsec) (Hz) (microsec/sec) ');
DBMS_OUTPUT.PUT_LINE ('------------------- ---------------- ------------------ ------------------------- -----------------------------------');
FOR i IN t1_histval.FIRST .. t1_histval.LAST LOOP
v_delta_waits := t1_histval(i).wait_count - t0_histval(i).wait_count;
v_delta_waits_per_sec := round(v_delta_waits / v_sleep_time,1);
v_delta_time_waited_estimate := round(0.75 * t1_histval(i).wait_time_micro * v_delta_waits_per_sec,1); -- estimated value
IF (t1_histval(i).wait_time_micro <> 1) THEN
v_latencybin := to_char(t1_histval(i).wait_time_micro/2) ||' -> ' || to_char(t1_histval(i).wait_time_micro);
ELSE
v_latencybin := '0 -> 1';
END IF;
DBMS_OUTPUT.PUT_LINE (
rpad(v_latencybin,19,' ')||' '||
lpad(to_char(v_delta_waits_per_sec),16,' ')||' '||
lpad(to_char(v_delta_time_waited_estimate),18,' ')||' '||
rpad(t1_histval(i).event,24,' ') ||' '||
t1_histval(i).last_update_time
);
END LOOP;
-- This is the summary from v$system_event
DBMS_OUTPUT.PUT_LINE(chr(13));
DBMS_OUTPUT.PUT_LINE('Average values from V$SYSTEM_EVENT:');
DBMS_OUTPUT.PUT_LINE(chr(13));
DBMS_OUTPUT.PUT_LINE ('Mean Wait Time Num Waits/DeltaT Wait Time/DeltaT Event Name ');
DBMS_OUTPUT.PUT_LINE ('(microsec) (Hz) (microsec/sec) ');
DBMS_OUTPUT.PUT_LINE ('------------------- ---------------- ------------------ -------------------------');
FOR i IN t1_eventval.FIRST .. t1_eventval.LAST LOOP
v_delta_time_waited_micro := t1_eventval(i).time_waited_micro - t0_eventval(i).time_waited_micro;
v_delta_waits := t1_eventval(i).total_waits - t0_eventval(i).total_waits;
v_delta_waits_per_sec := round(v_delta_waits / v_sleep_time, 1);
IF v_delta_waits <> 0 then
v_avg_wait_time_micro := round(v_delta_time_waited_micro/v_delta_waits,1);
ELSE
v_avg_wait_time_micro := 0;
END IF;
DBMS_OUTPUT.PUT_LINE(
rpad(to_char(v_avg_wait_time_micro),19,' ')||' '||
lpad(to_char(v_delta_waits_per_sec),16,' ')||' '||
lpad(to_char(round(v_delta_time_waited_micro/v_sleep_time,1)),18,' ')||' '||
rpad(t1_histval(i).event,24,' ')
);
END LOOP;
DBMS_OUTPUT.PUT_LINE(chr(13));
END;
/
Latency Bucket Num Waits/DeltaT Wait Time/DeltaT Event Name Last Update Time
(microsec) (Hz) (microsec/sec)
------------------- ---------------- ------------------ ------------------------- -----------------------------------
0 -> 1 0 0 db file sequential read
1 -> 2 0 0 db file sequential read
2 -> 4 0 0 db file sequential read
4 -> 8 0 0 db file sequential read
8 -> 16 0 0 db file sequential read
No comments:
Post a Comment