Dear Guru's,
DB_VERSION=10.2.0.4
OS_VERSION=64-bit Windows 2008 R2
This morning one of our Production databases peformance was
I have collected AWR information during that time and found "*enq: RO - fast object reuse*" followed by "CPU time".
Also as per the ADDM :
RECOMMENDATION 2: SQL Tuning, 48% benefit (1603 seconds)
ACTION: Investigate the SQL statement with SQL_ID "62fs5g3mqhd4b" for
possible performance improvements.
RELEVANT OBJECT: SQL statement with SQL_ID 62fs5g3mqhd4b and
PLAN_HASH 1328042859
TRUNCATE TABLE Transaction_Tmp
RATIONALE: Waiting for event "enq: RO - fast object reuse" in wait class
"Application" accounted for 98% of the database time spent in
processing the SQL statement with SQL_ID "62fs5g3mqhd4b"
As per the Bug 8544896 - Waits for "enq: RO - fast object reuse" with high DBWR CPU [ID 8544896.8] ,
Workaround:
Flush the buffer cache before truncating
OR
set
dbfast_obj_truncate = FALSE
However they have also mentioned for applying the patch to fix this issue ex:10.2.0.4 Patch 27 on Windows Platforms. But there exist the list of Patches i mean it's so confusing. I do not see anything that suits with my OS i.e. Windows 2008 64-bit :-(
I have following concerns :
Q1. Also according to "'enq: RO - fast object reuse' contention when gathering schema/table statistics in parallel [ID 762085.1]" , Both workarounds could have an impact on the database performance. Instead, it is recommended applying the corresponding patch
I mean shall i ignore this since i have encountered 'enq: RO - fast object reuse' during truncating the Temp Table and not while gathering statistics.
Q2. Or shall i try this workaround and observe the database performance ? ( which i am planning to perform currently)
Q3. Which is the correct Patch Number for the same? Since there are many Patches listed.
Thanks in advance