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.
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.