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.
No comments:
Post a Comment