• Arun Kumar

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


Below are the wait classes in Oracle:


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;



202 views

Recent Posts

See All

Temp Tablespace utilization

It is very common that as a DBA you will encounter ‘ORA-1652: unable to extend temp segment’. Handling temporary tablespace is different than permanent tablespaces inside Oracle. On a high level, temp

Estimate flashback destination size

Sometimes application team will ask DBA to enable flashback for x number of days. In such case, a DBA needs to estimate the flashback space required for x number of days in order to store the flashbac

Datafile utilization check

When you want to shrink a datafile, you must always check the single datafile utilization. In case if you shrink datafile more than the used size, it will fail. Below query gives the datafile utilizat

  • Facebook
  • Twitter
  • YouTube
  • Instagram

© 2020 by DBA Genesis

Feedback