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 for Object Reuse, 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… more …
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. At the writing of this help aid, there doesn’t seem to be a documented explanation of P2 or P3. So the use of this view is limited to finding the session only. At this point, you should be able to 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’
Solutions
Excessive wait on the RO enqueue is experienced by one of two events:
Check application logic or if 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 waits often 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.
Expanded Definition
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 will hopefully never see.
[…] enq: RO fast object reuse等待事件 […]