Examining Wait Events by Class

Wait events classes information can be obtained from SYS.V$EVENT_NAME view

select wait_class, name
from v$event_name
where name LIKE 'enq%'
and wait_class <> 'Other'
order by wait_class

To view the current waits grouped into various wait classes, issue the following query:

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;

If you see a very high sum of waits for the Idle wait class, not to worry—actually, you should expect
to see this in any healthy database.

In a typical production environment, however, you’ll certainly see more waits under the User I/O and Application wait classes. If you notice that the database has
accumulated a very large wait time for the Application wait class, or the User I/O wait class, for example,
it’s time to investigate those two wait classes further. In the following example, we drill down into a
couple of wait classes to find out which specific waits are causing the high sum of total wait time under
the Application and Concurrency classes. To do this, we use the V$SYSTEM_EVENT and the $EVENT_NAME views in addition to the V$SYSTEM_WAIT_CLASS view. Focus not just on the total time waited, but also on the average wait, to gauge the effect of the wait event.

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 in ('Application','Concurrency')
order by average_wait desc;

Two of the most common Oracle wait events are the db file scattered read and the db file
sequential read events. The db file scattered read wait event is due to full table scans of large tables. If
you experience this wait event, investigate the possibility of adding indexes to the table or tables. The db file
sequential read wait event is due to indexed reads. While an indexed read may seem like it’s a good thing, a
very high amount of indexed reads could potentially indicate an inefficient query that you must tune. If high values
for the db file sequential read wait event are due to a very large number of small indexed reads, it’s not
really a problem—this is natural in a database. You should be concerned if a handful of queries are responsible for
most of the waits.

*************************************************************
If you see that the enqueue waits caused by the row lock contention are what’s causing the most
waits under these two classes. Now you know exactly what’s slowing down the queries in your database!
To get at the session whose performance is being affected by the contention for the row lock, drill down
to the session level using the following query:

select a.sid, a.event, a.total_waits, a.time_waited, a.average_wait
from v$session_event a, v$session b
where time_waited > 0
and a.sid=b.sid
and b.username is not NULL
and a.event='enq: TX - row lock contention';