Oracle 10.2.0.4: Truncating a table causes “Enq: RO – FAST OBJECT REUSE”

I have encountered the following problem on a 10.2.0.4 database on AIX 5.3 x86_64 today:

A user session has been waiting for “enq: RO – fast object reuse” for almost 60 minutes while executing a “truncate table” SQL statement.

SQL> select username, event, sql_id, taddr, last_call_et from v$session where sid = 1086;

USERNAME EVENT SQL_ID TADDR LAST_CALL_ET
———- —————————————- ————- —————- ————
STSC enq: RO – fast object reuse 4y213r2udxp9z 0700000109250A40 5304

SQL> select sql_text from v$sqlstats where sql_id = ’4y213r2udxp9z’;

SQL_TEXT
——————————————–
truncate table stsc.planarriv

The Session was blocked by the CKPT process:

SQL> select * from dba_waiters;

WAITING_SESSION HOLDING_SESSION LOCK_TYPE MODE_HELD MODE_REQUESTED LOCK_ID1 LOCK_ID2
————— ————— ————————– —————————————- —————————————- ———- ———-
1086 1099 RO Row-S (SS) Exclusive 65613 1

SQL> select sid, serial#, sql_id, last_call_et, machine, program, username from v$session where sid=1099;

SID SERIAL# SQL_ID LAST_CALL_ET MACHINE PROGRAM USERNAME
———- ———- ————- ———— ———————— ———————————————— ———-
1099 1 9831952 ksfrprddb01 oracle@ksfrprddb01 (CKPT)

Issue is because of the 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.

Temporary fix:

1) Flushing the buffer cache.
OR
2) Setting “_db_fast_obj_truncate” =FALSE.
– ALTER SYSTEM SET “_db_fast_obj_truncate”=FALSE SCOPE=BOTH;

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.

Permanent Fix:

1. Install Patch having number 7385253

References :
10.2.0.4 Slowness: High DBW* CPU Usage with ‘Enq: RO – FAST OBJECT REUSE’ and ( High DFS Lock Handle ID1=3 in RAC) [ID 888844.1]
‘enq: RO – fast object reuse’ contention when gathering schema/table statistics in parallel [ID 762085.1]