enq: RO - fast object reuse

SQL> l
  1* select name, parameter1, parameter2, parameter3 from v$event_name where name like '&event'
SQL> /
Enter value for event: enq: RO - fast object reuse
old   1: select name, parameter1, parameter2, parameter3 from v$event_name where name like '&event'
new   1: select name, parameter1, parameter2, parameter3 from v$event_name where name like 'enq: RO - fast object reuse'

NAME                           PARAMETER1      PARAMETER2      PARAMETER3
------------------------------ --------------- --------------- ---------------
enq: RO - fast object reuse    name|mode       2               0

SQL>

簡述：
This note covers issues where the Guided Resolution Tool indicates contention on "enq: RO - fast object reuse". In effect this enqueue serializes the truncate/drop operations given concurrently and usually manifests itself when dropping objects or truncating tables.

Problem Confirmation:問題確認：
    ~The time spent actively in the local database is significant
    ~Only certain sessions, queries or jobs are experiencing slowness (not throughout the database)
    ~"enqueue: RO - fast object reuse" is a significant component of the DB time
    ~Objects are being dropped or tables truncated

"enq: RO - fast object reuse"

The RO enqueue known as "Multiple object resue" enqueue, is used to synchronise operations between foreground process and a background process such as DBWR or CKPT. It is typically used when dropping objects or truncating tables.

Following is the sequence of events When a truncate/drop occurs:
    1.Foreground process acquires the "RO" enqueue in exclusive mode
    2.Cross instance calls (or one call if it is a single object) are issued ("CI" enqueue is acquired)
    3.CKPT processes on each of instances requests the DBWR to write the dirty buffers to the disk and invalidate all the clean buffers.
    4.After DBWR completes writing all blocks, the foreground process releases the RO enqueue.
    
Application Logic：應用邏輯
由於這是併發問題，因此應該檢查邏輯和程式以消除不必要的DROP或TRUNCATE命令。
您也可以考慮使用臨時表作為替代，因為所用的機制不適用於臨時物件.
另一個選擇是將導致問題的操作重新安排到較低併發期間.

調整緩衝區快取：
RO入隊依賴於檢查點，因此調整DBWR和緩衝區快取記憶體效能可能有助於減少RO入隊鎖定機制的等待時間。see:Document 62172.1 Understanding and Tuning Buffer Cache and DBWR
等待的時間也受掃描緩衝區快取記憶體所需的時間的影響，以便將髒塊寫入磁碟並從快取重新整理。 緩衝區快取越大，找到這些塊所需的時間就越長。 如果緩衝區快取非常大，那麼減小緩衝區大小對於提高效能已經是有影響的