Bug 8544896 Waits for "enq: RO - fast object reuse" with high DBWR CPU

Hi All,

There was observed some issues with performance on #Production database. Checkpoint process is locking some other processes running on database.

Cause of the issue is a bug. It was already identified and remediated last year (workaround provided by oracle was applied):

Bug 8544896  Waits for "enq: RO - fast object reuse" with high DBWR CPU

Description

This problem is introduced in 10.2.0.4.

Sessions can wait on "enq: RO - fast object reuse" while DBWR consumes

lots of CPU when performing truncate type operations.

Workaround:

 Flush the buffer cache before truncating

 OR

 set _db_fast_obj_truncate = FALSE.

It looks like issue is reoccurring again now, and workaround  didn’t helped to fix this permanently.

If You will observer similar issue, or if You will be asked to look on it, please try to follow up below 3 steps to identify and confirm if thi is same issue.

If database owner will confirm/agree, flush buffer cache to solve lock:

1. check if lock exist on tabase

            select 'SID ' || l1.sid ||' is blocking ' || l2.sid blocking

      from v$lock l1, v$lock l2

      where l1.block =1 and l2.request > 0

      and l1.id1=l2.id1

      and l1.id2=l2.id2

      /

2. identify locked processes, if checkpoint process is the locker

            col "SID/SERIAL" format a10

      col username format a15

      col osuser format a20

      col program format a40

      select s.sid || ',' || s.serial# "SID/SERIAL"

      , s.username

      , s.osuser

      , p.spid "OS PID"

      , s.program

      from v$session s

      , v$process p

      Where s.paddr = p.addr

      and s.sid = 204 --SID

      order by to_number(p.spid)

      /

3. if that's the issue, please confirm with appteam and execute below. lock should disapear, but it may take couple of minutes:

            alter system flush buffer_cache;