Querying dba_hist_system_event to summarize the class based summarization of system events from awr data

Lab : Querying dba_hist_system_event to summarize the class based summarization of system events from awr data

Query:

with t as
(select INSTANCE_NUMBER
,snap_id
,WAIT_CLASS
,EVENT_NAME
,(total_waits - lag(total_waits,1,0) over (partition by INSTANCE_NUMBER,WAIT_CLASS,EVENT_NAME order by snap_id)) db_tot_waits
,(TOTAL_TIMEOUTS - lag(TOTAL_TIMEOUTS,1,0) over (partition by INSTANCE_NUMBER,WAIT_CLASS,EVENT_NAME order by snap_id)) db_tot_timeouts
,(TIME_WAITED_MICRO - lag(TIME_WAITED_MICRO,1,0) over (partition by INSTANCE_NUMBER,WAIT_CLASS,EVENT_NAME order by snap_id)) db_tot_timemic
,(TOTAL_WAITS_FG - lag(TOTAL_WAITS_FG,1,0) over (partition by INSTANCE_NUMBER,WAIT_CLASS,EVENT_NAME order by snap_id)) fg_tot_waits
,(TOTAL_TIMEOUTS_FG - lag(TOTAL_TIMEOUTS_FG,1,0) over (partition by INSTANCE_NUMBER,WAIT_CLASS,EVENT_NAME order by snap_id)) fg_tot_timeouts
,(TIME_WAITED_MICRO_FG - lag(TIME_WAITED_MICRO_FG,1,0) over (partition by INSTANCE_NUMBER,WAIT_CLASS,EVENT_NAME order by snap_id)) fg_tot_timemic
from DBA_HIST_SYSTEM_EVENT)
select a.END_INTERVAL_TIME tstmp
,a.instance_number in_nu
,t.wait_class
,sum(t.db_tot_timemic) class_agg_timemic
,sum(t.db_tot_timemic)/decode(sum(t.db_tot_waits),0,1,sum(t.db_tot_waits)) class_res_timemic
,round(sum(t.db_tot_timemic/decode(b.agg_db_tot_time,0,1,b.agg_db_tot_time))*100,2) pct_wt_class_time
,sum(t.db_tot_waits) class_agg_waits
,sum(db_tot_timeouts) class_agg_timeo
,round(sum(fg_tot_timemic)/decode(sum(t.db_tot_timemic),0,1,sum(t.db_tot_timemic))*100,2) pct_fg_class
from dba_hist_snapshot a
,t
,(select instance_number
,snap_id
,sum(db_tot_waits) agg_db_tot_wts
,sum(db_tot_timeouts) agg_db_tot_timout
,sum(db_tot_timemic) agg_db_tot_time
,sum(fg_tot_waits) agg_fg_tot_wts
,sum(fg_tot_timeouts) agg_fg_tot_timeo
,sum(fg_tot_timemic) agg_fg_tot_time
from t
group by instance_number
,snap_id) b
where a.snap_id=t.snap_id
and a.snap_id=b.snap_id
and a.INSTANCE_NUMBER=t.INSTANCE_NUMBER
and a.instance_number=b.instance_number
and a.BEGIN_INTERVAL_TIME > sysdate-2
group by a.END_INTERVAL_TIME
,a.instance_number
,t.wait_class
order by a.END_INTERVAL_TIME
,a.instance_number
,t.wait_class;

Result example:

TSTMP                         ,     IN_NU,WAIT_CLASS     ,CLASS_AGG_TIMEMIC,CLASS_RES_TIMEMIC,PCT_WT_CLASS_TIME,CLASS_AGG_WAITS,CLASS_AGG_TIMEO,PCT_FG_CLASS
------------------------------,----------,---------------,-----------------,-----------------,-----------------,---------------,---------------,------------
20-APR-19 04.27.03.450 PM     ,         1,Administrative ,            16321,            16321,                0,              1,        0,         100
20-APR-19 04.27.03.450 PM     ,         1,Application    ,          5658890,         -2829445,                0,             -2,        0,      102.78
20-APR-19 04.27.03.450 PM     ,         1,Commit         ,          2433284,       -110603.82,                0,            -22,        0,      130.45
20-APR-19 04.27.03.450 PM     ,         1,Concurrency    ,          7756959,       -13080.875,                0,           -593,        0,        81.8
20-APR-19 04.27.03.450 PM     ,         1,Configuration  ,         15554245,       5184748.33,                0,              3,        2,         100
20-APR-19 04.27.03.450 PM     ,         1,Idle           ,       -6.258E+11,        1929801.8,            99.98,        -324287,  -126260,         .05
20-APR-19 04.27.03.450 PM     ,         1,Network        ,             3885,       13.7765957,                0,            282,        0,       82.57
20-APR-19 04.27.03.450 PM     ,         1,Other          ,          9562378,       -133.03623,                0,         -71878,    -7152,      143.58
20-APR-19 04.27.03.450 PM     ,         1,Scheduler      ,         14416183,        -30672.73,                0,           -470,        0,       98.34
20-APR-19 04.27.03.450 PM     ,         1,System I/O     ,       -332436358,       5973.91385,              .05,         -55648,        0,       -6.51
20-APR-19 04.27.03.450 PM     ,         1,User I/O       ,        171322583,       -264795.34,             -.03,           -647,        0,       67.25
20-APR-19 05.00.15.137 PM     ,         1,Administrative ,           184875,          9243.75,                0,             20,        0,       -8.83
20-APR-19 05.00.15.137 PM     ,         1,Application    ,            11573,       136.152941,                0,             85,        0,       90.27
20-APR-19 05.00.15.137 PM     ,         1,Commit         ,           213992,          42798.4,                0,              5,        0,           0
20-APR-19 05.00.15.137 PM     ,         1,Concurrency    ,           257055,       3338.37662,                0,             77,        0,       97.82
20-APR-19 05.00.15.137 PM     ,         1,Configuration  ,                0,                0,                0,              0,        0,           0
20-APR-19 05.00.15.137 PM     ,         1,Idle           ,       8.7817E+10,        1917102.4,             99.9,          45807,    15792,        1.59
20-APR-19 05.00.15.137 PM     ,         1,Network        ,             9689,       15.8316993,                0,            612,        0,        79.7
20-APR-19 05.00.15.137 PM     ,         1,Other          ,         12605424,       1320.76949,              .01,           9544,      631,       32.46
20-APR-19 05.00.15.137 PM     ,         1,Scheduler      ,           135399,       1289.51429,                0,            105,        0,         100
20-APR-19 05.00.15.137 PM     ,         1,System I/O     ,         68315547,       5247.37284,              .08,          13019,        0,        1.77
20-APR-19 05.00.15.137 PM     ,         1,User I/O       ,          8369085,        4197.1339,              .01,           1994,        0,        1.97
20-APR-19 06.00.40.902 PM     ,         1,Administrative ,                0,                0,                0,              0,        0,           0
20-APR-19 06.00.40.902 PM     ,         1,Application    ,                0,                0,                0,              0,        0,           0
20-APR-19 06.00.40.902 PM     ,         1,Commit         ,          1673828,       139485.667,                0,             12,        0,       29.57
20-APR-19 06.00.40.902 PM     ,         1,Concurrency    ,            87558,       587.637584,                0,            149,        0,       85.15
20-APR-19 06.00.40.902 PM     ,         1,Configuration  ,                0,                0,                0,              0,        0,           0
...
21-APR-19 04.00.59.614 PM     ,         1,Idle           ,       1.5709E+11,       1906963.95,            99.91,          82376,    29052,        2.75
21-APR-19 04.00.59.614 PM     ,         1,Network        ,             2272,       23.9157895,                0,             95,        0,         100
21-APR-19 04.00.59.614 PM     ,         1,Other          ,         13535952,       756.113954,              .01,          17902,     2075,       29.28
21-APR-19 04.00.59.614 PM     ,         1,Scheduler      ,           213924,       1258.37647,                0,            170,        0,         100
21-APR-19 04.00.59.614 PM     ,         1,System I/O     ,        129506987,       9125.99443,              .08,          14191,        0,           0
21-APR-19 04.00.59.614 PM     ,         1,User I/O       ,          2095721,       1806.65603,                0,           1160,        0,        5.24
21-APR-19 05.00.20.950 PM     ,         1,Application    ,                0,                0,                0,              0,        0,           0
21-APR-19 05.00.20.950 PM     ,         1,Commit         ,          3888804,         388880.4,                0,             10,        0,       92.64
21-APR-19 05.00.20.950 PM     ,         1,Concurrency    ,           122897,       967.692913,                0,            127,        0,       91.07
21-APR-19 05.00.20.950 PM     ,         1,Configuration  ,                0,                0,                0,              0,        0,           0
21-APR-19 05.00.20.950 PM     ,         1,Idle           ,       1.5227E+11,        1915807.4,            99.87,          79481,    28541,         .13
21-APR-19 05.00.20.950 PM     ,         1,Network        ,                0,                0,                0,              0,        0,           0
21-APR-19 05.00.20.950 PM     ,         1,Other          ,         17030382,       1002.90807,              .01,          16981,     1822,       23.76
21-APR-19 05.00.20.950 PM     ,         1,Scheduler      ,           295912,       2026.79452,                0,            146,        0,       77.19
21-APR-19 05.00.20.950 PM     ,         1,System I/O     ,        177589588,       12890.2945,              .12,          13777,        0,           0
21-APR-19 05.00.20.950 PM     ,         1,User I/O       ,          1434472,       1941.09878,                0,            739,        0,           0

160 rows selected.