Performance optimization of databases is one of the routine tasks of DBAs. This task becomes very easy when you have Oracle Enterprise Manager tool installed. You can generate various reports from AWR and analyze the health of the database and then tune it accordingly. This is normal in every DBA’s life.
But what if you don’t have the OEM installed? This is very much possible in a test environment. In this case, you need to generate the reports manually. You need to query the database to extract the data requried for analysis.
For such requirements, we have the below query to find out “Top 5 wait events in the database”. The query gives Date, Event_Name and Total_Waits.
Query to find Top 5 wait events in database:
select Day, Event_name, Total_wait from (
select day, event_name, sum(event_time_waited) total_wait,
row_number() over (partition by day order by sum(event_time_waited) desc) rn from (
SELECT to_date(to_char(begin_interval_time,’dd/mm/yyyy’),’dd/mm/yyyy’) day,s.begin_interval_time, m.*
FROM (SELECT ee.instance_number, ee.snap_id, ee.event_name,
ROUND (ee.event_time_waited / 1000000) event_time_waited,
ee.total_waits,
ROUND ((ee.event_time_waited * 100) / et.total_time_waited,
1
) pct,
ROUND ((ee.event_time_waited / ee.total_waits) / 1000
) avg_wait
FROM (SELECT ee1.instance_number, ee1.snap_id, ee1.event_name,
ee1.time_waited_micro
– ee2.time_waited_micro event_time_waited,
ee1.total_waits – ee2.total_waits total_waits
FROM dba_hist_system_event ee1 JOIN dba_hist_system_event ee2
ON ee1.snap_id = ee2.snap_id + 1
AND ee1.instance_number = ee2.instance_number
AND ee1.event_id = ee2.event_id
AND ee1.wait_class_id <> 2723168908
AND ee1.time_waited_micro – ee2.time_waited_micro > 0
UNION
SELECT st1.instance_number, st1.snap_id,
st1.stat_name event_name,
st1.VALUE – st2.VALUE event_time_waited,
1 total_waits
FROM dba_hist_sys_time_model st1 JOIN dba_hist_sys_time_model st2
ON st1.instance_number = st2.instance_number
AND st1.snap_id = st2.snap_id + 1
AND st1.stat_id = st2.stat_id
AND st1.stat_name = ‘DB CPU’
AND st1.VALUE – st2.VALUE > 0
) ee
JOIN
(SELECT et1.instance_number, et1.snap_id,
et1.VALUE – et2.VALUE total_time_waited
FROM dba_hist_sys_time_model et1 JOIN dba_hist_sys_time_model et2
ON et1.snap_id = et2.snap_id + 1
AND et1.instance_number = et2.instance_number
AND et1.stat_id = et2.stat_id
AND et1.stat_name = ‘DB time’
AND et1.VALUE – et2.VALUE > 0
) et
ON ee.instance_number = et.instance_number
AND ee.snap_id = et.snap_id
) m
JOIN
dba_hist_snapshot s ON m.snap_id = s.snap_id
) group by day ,event_name
order by day desc, total_wait desc
)where rn < 6
The query is very useful for the DBAs because it can give you loads of information. You can modify the query to fetch much more information and use it the way you want.
Best Luck 
