Thursday, July 14, 2011

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

On Oracle 10gR2 ( 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 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;

---------- ---------- ------------------------------------------------
      1095          1 oracle@castor (CKPT)

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

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


  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.