How to troubleshoot and get to know about current top wait events since oracle database instance startup ?

 To get the current top wait events since instance startup, use:

SELECT *

FROM (

SELECT

event,

total_waits,

ROUND(time_waited_micro/1000000,2) AS time_waited_sec,

ROUND(time_waited_micro/NULLIF(total_waits,0)/1000,2) AS avg_wait_ms

FROM v$system_event

WHERE wait_class <> 'Idle'

ORDER BY time_waited_micro DESC

)

WHERE ROWNUM <= 20;

Top Wait Events by Total Wait Time (%)

SELECT

event,

wait_class,

ROUND(time_waited_micro/1000000,2) AS wait_time_sec,

ROUND(

100 * time_waited_micro /

SUM(time_waited_micro) OVER (),

2

) AS pct_wait_time

FROM v$system_event

WHERE wait_class <> 'Idle'

ORDER BY pct_wait_time DESC;

Current Active Waits (Right Now)

SELECT

event,

COUNT(*) sessions_waiting

FROM v$session

WHERE state = 'WAITING'

AND wait_class <> 'Idle'

GROUP BY event

ORDER BY sessions_waiting DESC;

Top Wait Events from ASH (Last 1 Hour)

SELECT

event,

COUNT() samples,

ROUND(

COUNT() * 100 /

SUM(COUNT(*)) OVER (),

2

) pct

FROM v$active_session_history

WHERE sample_time > SYSDATE - 1/24

AND wait_class <> 'Idle'

GROUP BY event

ORDER BY samples DESC;

AWR Top Wait Events for Last 24 Hours

SELECT

event_name,

SUM(total_waits_delta) total_waits,

ROUND(SUM(time_waited_micro_delta)/1000000,2) wait_time_sec

FROM dba_hist_system_event

WHERE wait_class <> 'Idle'

GROUP BY event_name

ORDER BY wait_time_sec DESC;

Quick DBA Query (Most Useful)

This gives the top waits consuming database time:

SELECT *

FROM (

SELECT

event,

wait_class,

ROUND(time_waited_micro/1000000,2) seconds_waited,

ROUND(time_waited_micro/NULLIF(total_waits,0)/1000,2) avg_wait_ms

FROM v$system_event

WHERE wait_class <> 'Idle'

ORDER BY time_waited_micro DESC

)

WHERE ROWNUM <= 10;

If you're investigating a performance issue right now, also run:

SELECT

sid,

serial#,

username,

event,

sql_id,

seconds_in_wait

FROM v$session

WHERE state='WAITING'

AND wait_class <> 'Idle'

ORDER BY seconds_in_wait DESC;

This shows the exact sessions, SQL_IDs, and wait events currently contributing to the database slowdown.