How to identify the top Wait Events within a Wait Class in Oracle ??
5 min readEach wait event in Oracle belongs to a specific class. Below is a breakdown of the various wait classes and their descriptions:
1. Administrative
These waits occur due to DBA commands that cause users to wait, such as during an index rebuild.
2. Application
Waits caused by user application code, like lock waits due to row-level locking or explicit lock commands.
3. Cluster
Waits related to Oracle Real Application Clusters (RAC) resources, for example, global cache resources such as ‘gc cr block busy’.
4. Commit
This class includes a single wait event: waiting for redo log write confirmation after a commit, known as ‘log file sync’.
5. Concurrency
Waits for internal database resources, such as latches.
6. Configuration
Waits caused by improper configuration of database or instance resources, such as undersized log file sizes or shared pool size.
7. Idle
These waits indicate that the session is inactive, waiting for work, such as ‘SQLNet message from client’.
8. Network
Waits related to network messaging, like ‘SQLNet more data to dblink’.
9. Other
Waits that typically should not occur in a system, such as ‘wait for EMON to spawn’.
10. Queueing
This class contains events indicating delays in obtaining additional data in a pipelined environment. Such waits may affect performance in areas like parallel queries or DBMS_PIPE PL/SQL packages.
11. Scheduler
Waits related to the Resource Manager, for instance, ‘resmgr: become active’.
12. System I/O
Waits related to background process I/O, such as DBWR waiting for ‘db file parallel write’.
13. User I/O
Waits due to user I/O operations, for example, ‘db file sequential read’.
The following query will provide details of the current wait events in the Oracle database:
SQL > SELECT a.sid,substr(b.username,1,10) username,substr(b.osuser,1,10) osuser,
substr(b.program||b.module,1,15) program,substr(b.machine,1,22) machine,
a.event,a.p1,b.sql_hash_value
FROM v$session_wait a,V$session b
where b.sid=a.sid
and a.event not in('SQL*Net message from client','SQL*Net message to client',
'smon timer','pmon timer')
and username is not null
order by 6
/
Understanding what a query has been waiting on is key to identifying areas that need tuning. Oracle’s wait interface offers extensive data to help DBAs determine where to focus their tuning efforts. Here are various methods to analyze what Oracle has been waiting on:
Current session waits: v$session_event
Waits in the last hour: v$waitclassmetric_history
Historical waits, hourly breakdown: dba_hist_system_event
select swc.wait_class,
begin_time ,
end_time ,
SUM(wcmh.average_waiter_count) avg_waiter_count,
SUM(wcmh.dbtime_in_wait) AS dbtime_in_wait ,
SUM(wcmh.time_waited) * 10 AS time_waited_ms,
SUM(wcmh.wait_count) AS wait_count ,
SUM(wcmh.time_waited_fg) * 10 AS time_waited_fg_ms,
SUM(wcmh.wait_count_fg) AS wait_count_fg
from V$SYSTEM_WAIT_CLASS swc, v$waitclassmetric_history wcmh
where swc.wait_class != 'Idle'
AND swc.wait_class_id = wcmh.wait_class_id
group by swc.wait_class, begin_time, end_time
order by 2 desc, 3 desc, 6 desc ;
WAIT_CLASS BEGIN_TIM END_TIME AVG_WAITER_COUNT DBTIME_IN_WAIT TIME_WAITED_MS WAIT_COUNT TIME_WAITED_FG_MS WAIT_COUNT_FG
---------------------------------------- --------- --------- ---------------- -------------- -------------- ---------- ----------------- -------------
System I/O 14-AUG-24 14-AUG-24 .009168298 100 554.407 145 0 0
Other 14-AUG-24 14-AUG-24 .003629916 100 219.501 29 0 0
Administrative 14-AUG-24 14-AUG-24 0 0 0 0 0 0
Network 14-AUG-24 14-AUG-24 0 0 0 0 0 0
Concurrency 14-AUG-24 14-AUG-24 0 0 0 0 0 0
Commit 14-AUG-24 14-AUG-24 0 0 0 0 0 0
Application 14-AUG-24 14-AUG-24 0 0 0 0 0 0
User I/O 14-AUG-24 14-AUG-24 0 0 0 0 0 0
Configuration 14-AUG-24 14-AUG-24 0 0 0 0 0 0
System I/O 14-AUG-24 14-AUG-24 .049226673 100 2927.018 592 0 0
Other 14-AUG-24 14-AUG-24 .005764884 100 342.78 39 0 0
Network 14-AUG-24 14-AUG-24 0 0 0 0 0 0
Administrative 14-AUG-24 14-AUG-24 0 0 0 0 0 0
Concurrency 14-AUG-24 14-AUG-24 0 0 0 0 0 0
Configuration 14-AUG-24 14-AUG-24 0 0 0 0 0 0
Application 14-AUG-24 14-AUG-24 0 0 0 0 0 0
User I/O 14-AUG-24 14-AUG-24 0 0 0 0 0 0
Commit 14-AUG-24 14-AUG-24 0 0 0 0 0 0
System I/O 14-AUG-24 14-AUG-24 .014699454 100 888.141 164 0 0
Other 14-AUG-24 14-AUG-24 .001827193 100 110.399 18 0 0
Network 14-AUG-24 14-AUG-24 0 0 0 0 0 0
Commit 14-AUG-24 14-AUG-24 0 0 0 0 0 0
Application 14-AUG-24 14-AUG-24 0 0 0 0 0 0
Administrative 14-AUG-24 14-AUG-24 0 0 0 0 0 0
Configuration 14-AUG-24 14-AUG-24 0 0 0 0 0 0
User I/O 14-AUG-24 14-AUG-24 0 0 0 0 0 0
Concurrency 14-AUG-24 14-AUG-24 0 0 0 0 0 0
System I/O 14-AUG-24 14-AUG-24 .011028153 100 654.962 164 0 0
Other 14-AUG-24 14-AUG-24 .002105775 100 125.062 24 0 0
Network 14-AUG-24 14-AUG-24 0 0 0 0 0 0
Administrative 14-AUG-24 14-AUG-24 0 0 0 0 0 0
Concurrency 14-AUG-24 14-AUG-24 0 0 0 0 0 0
Configuration 14-AUG-24 14-AUG-24 0 0 0 0 0 0
Application 14-AUG-24 14-AUG-24 0 0 0 0 0 0
User I/O 14-AUG-24 14-AUG-24 0 0 0 0 0 0
Commit 14-AUG-24 14-AUG-24 0 0 0 0 0 0
System I/O 14-AUG-24 14-AUG-24 .018329949 100 1112.078 169 0 0
Other 14-AUG-24 14-AUG-24 .00631434 72.9238764 383.091 133 .326 0
Commit 14-AUG-24 14-AUG-24 .000230081 2.65718691 13.959 1 0 0
User I/O 14-AUG-24 14-AUG-24 .000019581 .226143567 1.188 12 1.03 0
Concurrency 14-AUG-24 14-AUG-24 4.9777E-06 .057487674 .302 3 0 0
Network 14-AUG-24 14-AUG-24 3.6262E-07 .004187844 .022 19 .015 0
Configuration 14-AUG-24 14-AUG-24 0 0 0 0 0 0
Application 14-AUG-24 14-AUG-24 0 0 0 0 0 0
Administrative 14-AUG-24 14-AUG-24 0 0 0 0 0 0
System I/O 14-AUG-24 14-AUG-24 .010886515 100 646.659 164 0 0
Other 14-AUG-24 14-AUG-24 .002678199 100 159.085 25 0 0
Application 14-AUG-24 14-AUG-24 0 0 0 0 0 0
Concurrency 14-AUG-24 14-AUG-24 0 0 0 0 0 0
Commit 14-AUG-24 14-AUG-24 0 0 0 0 0 0
Network 14-AUG-24 14-AUG-24 0 0 0 0 0 0
User I/O 14-AUG-24 14-AUG-24 0 0 0 0 0 0
Configuration 14-AUG-24 14-AUG-24 0 0 0 0 0 0
Administrative 14-AUG-24 14-AUG-24 0 0 0 0 0 0
System I/O 14-AUG-24 14-AUG-24 .036703046 100 2217.231 202 0 0
User I/O 14-AUG-24 14-AUG-24 .008199421 100 495.327 79 0 0
Other 14-AUG-24 14-AUG-24 .007945953 100 480.015 139 0 0
Concurrency 14-AUG-24 14-AUG-24 .000038967 100 2.354 1 0 0 0 0 0
System I/O 14-AUG-24 14-AUG-24 .020081197 100 1194.028 241 0 0
Other 14-AUG-24 14-AUG-24 .010513454 100 625.13 38 0 0
User I/O 14-AUG-24 14-AUG-24 7.4504E-06 100 .443 6 0 0
select snap.END_INTERVAL_TIME,
dhse2.wait_class,
dhse2.event_name ,
dhse2.TOTAL_WAITS - dhse1.total_waits AS number_of_waits,
(dhse2.time_waited_micro - dhse1.time_waited_micro) / 1000 AS time_waited_ms
FROM DBA_HIST_SYSTEM_EVENT dhse1
JOIN DBA_HIST_SYSTEM_EVENT dhse2 ON dhse2.snap_id = dhse1.snap_id + 1
AND dhse2.wait_class_id = dhse1.wait_class_id
AND dhse2.event_id = dhse1.event_id
JOIN DBA_HIST_SNAPSHOT snap ON dhse2.snap_id = snap.snap_id
WHERE snap.END_INTERVAL_TIME > SYSDATE - 1
order by dhse2.snap_id desc ;
SELECT a.event, a.total_waits, a.time_waited, a.average_wait
FROM v$system_event a
JOIN v$event_name b ON a.event_id = b.event_id
JOIN v$system_wait_class c ON b.wait_class# = c.wait_class#
WHERE c.wait_class = '&Enter_Wait_Class'
ORDER BY a.average_wait DESC;
This query selects specific columns from system-related views to retrieve information about system events, their total waits, time waited, and average wait times. It filters the results based on the specified wait class entered by the user and orders the output by the average wait time in descending order.
Top 5 wait events from v$active_session_history
select * from (
select
WAIT_CLASS ,
EVENT,
count(sample_time) as EST_SECS_IN_WAIT
from v$active_session_history
where sample_time between sysdate - interval '1' hour and sysdate
group by WAIT_CLASS,EVENT
order by count(sample_time) desc
)
where rownum <6
select SID,
wait_class,
event,
total_waits AS bg_total_waits,
max_wait * 10 max_wait_ms ,
time_waited_micro / 1000.0 AS bg_time_waited_ms,
average_wait AS bg_average_wait
from v$session_event
where wait_class != 'Idle' ;
select wait_class,
event,
total_waits AS bg_total_waits,
time_waited_micro / 1000.0 AS time_waited_ms,
average_wait * 10 AS average_wait_ms ,
total_waits_fg AS fg_total_waits ,
time_waited_micro_fg / 1000.0 AS fg_time_waited_ms ,
average_wait_fg * 10 AS fg_average_wait_ms
from v$system_event
where wait_class != 'Idle'
order by time_waited_micro desc ;
Hope it helped !! 🙂
