Wait events vs wait classes

Did you know that all the wait events inside Oracle database are categorized into wait classes? Every wait event always belongs to a wait class. Even before you learn more about each and every wait event, you must be aware of wait classes.

• Wait Classes in Oracle

• Find Top Wait Classes

• Find Top Wait Events in a Wait Class

Wait Classes in Oracle

All the wait events are grouped under wait classes and here are the most important wait classes you must know.

Administrative: Waits resulting from DBA commands that cause users to wait (for example, an index rebuild)

Application: Waits resulting from user application code (for example, lock waits caused by row level locking or explicit lock commands)

Cluster: Waits related to Real Application Cluster resources (for example, global cache resources such as gc cr block busy

Commit: This wait class only comprises one wait event – wait for redo log write confirmation after a commit (that is, ‘log file sync’)

Concurrency: Waits for internal database resources (for example, latches)

Configuration: Waits caused by inadequate configuration of database or instance resources (for example, undersized log file sizes, shared pool size)

Idle: Waits that signify the session is inactive, waiting for work (for example, ‘SQL*Net message from client’)

Network: Waits related to network messaging (for example, ‘SQL*Net more data to dblink’)

Other: Waits which should not typically occur on a system (for example, ‘wait for EMON to spawn’)

Scheduler: Resource Manager related waits (for example, ‘resmgr: become active’)

System I/O: Waits for background process IO (for example, DBWR wait for ‘db file parallel write’)

User I/O: Waits for user IO (for example ‘db file sequential read’)

Find Top Wait Classes

User below query to get the top wait classes in Oracle database

Select wait_class, sum(time_waited), sum(time_waited)/sum(total_waits)
Sum_Waits
From v$system_wait_class
Group by wait_class
Order by 3 desc;

Find Top Wait Events in a Wait Class

From the above query, supply each wait class into below query to get the top wait events in database with respect to particular wait class

Select a.event, a.total_waits, a.time_waited, a.average_wait
From v$system_event a, v$event_name b, v$system_wait_class c
Where a.event_id=b.event_id
And b.wait_class#=c.wait_class#
And c.wait_class = '&Enter_Wait_Class'
order by average_wait desc;