RO Enqueue

Definition: New to Oracle 10g is 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, and is used to synchronize the work required between a foreground process and background process such as DBWR or CKPT. This enqueue 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 being a reflection of application logic or administrative tasks have occurred. Since this is a concurrency issue, logic and processes should be examined to eliminate needless DROP or TRUNCATE commands. Also better alternatives should be sought out such as the use of temporary tables or rescheduling for less concurrency. Since the RO enqueue is reliant upon checkpointing, DBWR, and buffer cache performance, tuning these areas will help in reducing the wait time for the RO enqueue locking mechanism.

Problems with the RO enqueue wait often times revolve around bugs in the Oracle software. This isn't new but when trouble shooting, investigations should also be done around 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.

Conclusion

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.