Thursday, July 14, 2011

Oracle 10gR2 : Truncating a table causes "Enq: RO - FAST OBJECT REUSE" and DBW High CPU Usage

On Oracle 10gR2 (10.2.0.4) a table truncate is taking lots of minutes and your session si waiting on  "Enq: RO - FAST OBJECT REUSE" then you probably have hit bug8544896 introduced in 10.2.0.4 and it affects both Single instance and RAC environments, and a patch is available.

In order to confirm the bug as sys do :

select waiting_session,holding_session,lock_type,mode_held,mode_requested
from dba_waiters;


select sid, serial#,program from v$session where sid = 1095;

       SID    SERIAL# PROGRAM
---------- ---------- ------------------------------------------------
      1095          1 oracle@castor (CKPT)

If you see that the CKPT Process is then that's the case.


Workaround
1) From an other session as sys issue :
    alter system flush buffer_cache;
2) Set _db_fast_obj_truncate = FALSE to spfile/init.ora

References :
Metalink Note id : 888844.1
http://www.ora-solutions.net/web/2009/01/20/session-waiting-for-enq-ro-fast-object-reuse-dbwr-process-spinning-on-cpu/


3 comments:

  1. This is new concept for me. I had no clue about it before reading your blog. I haven't got chance to implement it in any program. Once I try the logic in my program then I'll be in better position to give you clear view.

    ReplyDelete