Oracle RO Enqueue Wait Event

Oracle 10g introduced the individual tracking of over 180 specific enqueues or internal Oracle locking mechanisms. One of these is the RO enqueue wait event, which means Object Reuse enqueue. It is used to synchronize the work required between a foreground process and background process such as DBWR or CKPT. It is most often seen when dropping objects or truncating tables.

How do I know if I have a problem?

Tables typically truncate quickly and objects drop relatively fast so unless there is a major issue on your system, you may not see this event in the V$SESSION_WAIT view. It is more likely to be seen in the in the V$SYSTEM_WAIT view, in a statspack report, or trace report.

For the V$SYSTEM_WAIT view a simple check to see if the TOTAL_WAITS and TIME_WAITED are high or are increasing will determine if there is a problem.

SELECT event, total_waits, time_waited
 FROM v$system_event 
 WHERE event like 'enq: RO%'

If you find that the enqueue is excessive you may be able to see it in the V$SESSION_WAIT view through the following query.

SELECT a.sid, c.pid, c.spid, a.username, b.event, b.wait_time, b.seconds_in_wait, b.p1, b.p2, b.p3
 FROM v$session a, v$session_wait b, v$process c 
 WHERE a.sid = b.sid 
 AND a.paddr = c.addr 
 AND b.event LIKE 'enq: RO%'

Evaluation of the P1 value only restates that this lock is of type ‘RO’ and is in exclusive mode. I have yet to see an explanation of P2 or P3. So the use of this view is limited to finding the session only. At this point, you should track back to an application.

You might also find the V$ENQUEUE_STAT view helpful in that it tracks detailed statistics for each enqueue.

As it is never a good idea to continually add and drop tables in an application, you should look for excessive requests, (total_req#), failed requests, (failed_req#), and of course the accumulated amount of time spent on this enqueue (cum_wait_time).

Use the following SQL to monitor this view.

SELECT eq_type, total_req#, total_wait#, succ_req#, failed_req#, cum_wait_time 
 FROM v$enqueue_stat 
 WHERE eq_type = 'RO'

How to combat the event

Excessive wait on the RO enqueue is experienced by one of two events. The first is a reflection of application logic or administrative tasks having occurred. Since this is a concurrency issue, you should examine logic and processes to eliminate needless DROP or TRUNCATE commands. Also, you could look for better alternatives to reduce concurreny, such as the use of temporary tables or rescheduling. Since the RO enqueue is reliant upon checkpointing, DBWR and buffer cache performance, tuning these areas will help reduce the wait time for the RO enqueue locking mechanism.

Problems with the RO enqueue wait often times are caused by bugs in the Oracle software. This isn’t new, but when troubleshooting, you should investigate bugs seen in checkpointing, DBWR and the buffer cache. This is because these areas can adversely effect the time spent waiting for the RO enqueue.

Final thoughts

Under normal circumstances, dropping or truncating a table is a quick event. But when your database is not properly configured or application logic stresses the boundaries of normal processing, you can experience problems with the RO enqueue wait event. So with a small effort to properly configure the database and with some re-visiting of application logic, this wait event should be one you hope to never see.