'enq: RO - fast object reuse' contention Slow Truncate


I have Database Management Software Oracle 10.2.0.4 installed on RHEL 4 Server. In one of Database  "Truncate Table" is used frequently. At same time some queries are used to fetch data from same table. 

Issue:

Truncate table having 3000 records starts taking hours to complete in wait event "enq: RO - fast object reuse" cause of major delay in process. I observe this is an Oracle bug "Bug 7385253 - Slow Truncate / DBWR uses high CPU / CKPT blocks on RO enqueue".

Below are the symptoms for this bug.

1. Truncate will wait on "enq: RO - fast object reuse" wait event. 
2. CKPT process will block truncate table session. 
3. At OS level DBWR will be using 100% CUP. 

Solution:

On opening a ticket, i got confirmation about this bug, so following are two proposed solutions from Oracle Support for above Bug.

1. Install Patch having number 7385253

To download a patch:

+ Login to Internal Support Portal
+ Click on Patches & Updates
+ Enter Patch Number 7385253
+ Select your OS and click on search

From the list of available patches, download the one matching your version, follow the readme's and apply the patch.

2. Flushing the shared pool or setting "_db_fast_obj_truncate" =FALSE is a temporary workaround.
The command to set the parameter is

SQL> ALTER SYSTEM SET "_db_fast_obj_truncate"=FALSE SCOPE=BOTH;

or change the initora/pfile as appropriate

NOTE: Setting the parameter "_db_fast_obj_truncate" will revert back to 9i way of invalidating buffers in buffercache. This could have an impact on performance.Kindly note that both workarounds(flushing shared pool and setting the underscore parameter)could have an impact on the database performance.Instead, it is recommended applying the corresponding patch.

I choose to implement workaround i.e changing "_db_fast_obj_truncate"=FALSE Steps to implement workaround:


1. Use below query for verify for parameter change.

sql> SELECT a.ksppinm  "Parameter",
       c.ksppstvl "InstanceValue"
FROM   x$ksppi a,
       x$ksppcv b,
       x$ksppsv c
WHERE  a.indx = b.indx
AND    a.indx = c.indx
AND    a.ksppinm LIKE '/_%' escape '/' and a.ksppinm LIKE '%_db_fast_obj_truncate%' ;

Output:

Parameter                     InstanceValue
----------------------------- --------------------
_db_fast_obj_truncate         TRUE

2. Execute command to change parameter:

SQL> ALTER SYSTEM SET "_db_fast_obj_truncate"=FALSE SCOPE=BOTH;

System altered. 

3. Verify result using first query:

Parameter                     InstanceValue
----------------------------- --------------------
_db_fast_obj_truncate         FALSE

No comments:

Post a Comment