If you want to know how much time a session has waited on each wait event, here are the most useful queries.
1. Current Session Wait Information
For a specific session:
SELECT
sid,
serial#,
username,
event,
wait_class,
state,
seconds_in_wait
FROM v$session
WHERE sid = &SID;
This shows the current wait event and how long the session has been waiting.
2. Session Wait History (Recent Waits)
SELECT
sid,
event,
wait_time_micro/1000 AS wait_time_ms,
time_since_last_wait_micro/1000 AS since_last_wait_ms,
wait_count
FROM v$session_event
WHERE sid = &SID
ORDER BY wait_time_micro DESC;
Output example:
EVENT WAIT_TIME_MS WAIT_COUNT
------------------------ ------------ ----------
db file sequential read 150000 5000
log file sync 25000 1000
enq: TX row lock contention 10000 100
This is usually the first query I use.
3. Total Time Spent by a Session in Each Wait Event
SELECT
sid,
event,
total_waits,
ROUND(time_waited_micro/1000000,2) wait_time_sec,
ROUND(time_waited_micro/
NULLIF(total_waits,0)/1000,2) avg_wait_ms
FROM v$session_event
WHERE sid = &SID
ORDER BY time_waited_micro DESC;
Example:
EVENT TOTAL_WAITS WAIT_TIME_SEC AVG_WAIT_MS
------------------------ ----------- ------------ -----------
db file sequential read 50000 1200 24
log file sync 10000 150 15
4. All Active Sessions with Their Top Waits
SELECT
se.sid,
s.serial#,
s.username,
se.event,
se.total_waits,
ROUND(se.time_waited_micro/1000000,2) wait_time_sec
FROM v$session_event se
JOIN v$session s
ON se.sid = s.sid
WHERE s.username IS NOT NULL
ORDER BY se.time_waited_micro DESC;
5. Using ASH (Last 1 Hour)
If Diagnostics Pack is licensed:
SELECT
session_id,
sql_id,
event,
COUNT() samples,
ROUND(COUNT()*10,2) wait_seconds
FROM v$active_session_history
WHERE sample_time > SYSDATE - 1/24
AND session_id = &SID
GROUP BY session_id, sql_id, event
ORDER BY samples DESC;
Note: Each ASH sample ≈ 1 second.
6. Historical Session Waits from AWR
SELECT
ash.session_id,
ash.sql_id,
ash.event,
COUNT() samples,
ROUND(COUNT()*10,2) wait_seconds
FROM dba_hist_active_sess_history ash
WHERE ash.session_id = &SID
GROUP BY ash.session_id,
ash.sql_id,
ash.event
ORDER BY samples DESC;
7. Find the Top Wait Event for a Session
SELECT *
FROM (
SELECT
event,
total_waits,
ROUND(time_waited_micro/1000000,2) wait_time_sec
FROM v$session_event
WHERE sid = &SID
ORDER BY time_waited_micro DESC
)
WHERE ROWNUM = 1;
Most Useful DBA Query
Replace 123 with the session ID:
SELECT
event,
total_waits,
ROUND(time_waited_micro/1000000,2) total_wait_sec,
ROUND(time_waited_micro/
NULLIF(total_waits,0)/1000,2) avg_wait_ms
FROM v$session_event
WHERE sid = 123
ORDER BY time_waited_micro DESC;
To get the total wait time aggregated by wait event across all logged-in sessions, use:
SELECT
se.event,
SUM(se.total_waits) AS total_waits,
ROUND(SUM(se.time_waited_micro)/1000000, 2) AS total_wait_time_sec,
ROUND(
SUM(se.time_waited_micro) /
NULLIF(SUM(se.total_waits), 0) / 1000,
2
) AS avg_wait_ms
FROM v$session_event se
JOIN v$session s
ON se.sid = s.sid
WHERE s.username IS NOT NULL
GROUP BY se.event
ORDER BY total_wait_time_sec DESC;
If you also want to see the number of sessions affected by each wait event:
SELECT
se.event,
COUNT(DISTINCT se.sid) AS sessions,
SUM(se.total_waits) AS total_waits,
ROUND(SUM(se.time_waited_micro)/1000000, 2) AS total_wait_time_sec,
ROUND(
SUM(se.time_waited_micro) /
NULLIF(SUM(se.total_waits), 0) / 1000,
2
) AS avg_wait_ms
FROM v$session_event se
JOIN v$session s
ON se.sid = s.sid
WHERE s.username IS NOT NULL
GROUP BY se.event
ORDER BY total_wait_time_sec DESC;
To identify the top wait events by active user:
SELECT
s.username,
se.event,
SUM(se.total_waits) AS total_waits,
ROUND(SUM(se.time_waited_micro)/1000000, 2) AS wait_time_sec
FROM v$session_event se
JOIN v$session s
ON se.sid = s.sid
WHERE s.username IS NOT NULL
GROUP BY s.username, se.event
ORDER BY wait_time_sec DESC;
And if you're troubleshooting performance, exclude idle waits:
SELECT
se.event,
SUM(se.total_waits) total_waits,
ROUND(SUM(se.time_waited_micro)/1000000,2) wait_time_sec
FROM v$session_event se
JOIN v$event_name en
ON se.event = en.name
WHERE en.wait_class <> 'Idle'
GROUP BY se.event
ORDER BY wait_time_sec DESC;
This last query is typically the most useful because it highlights only the waits that contribute to database response-time issues.
No comments:
Post a Comment