KEEP BUFFER POOL Does Not Work for Large Objects on 11g

KEEP BUFFER POOL Does Not Work for Large Objects on 11g [ID 1081553.1]

Applies to:

Oracle Server - Enterprise Edition - Version 11.1.0.7 to 11.2.0.1 [Release 11.1 to 11.2]
Information in this document applies to any platform.
***Checked for relevance on 04-Nov-2011***

Symptoms

While doing full table scan, Buffer Cache KEEP pool is not being used to read the blocks of a (KEEP) table which size is >10% of  DB_KEEP_CACHE_SIZE.

These blocks are always being read through DIRECT READ, which can cause performance problem due to non-caching / re-reading of keep object blocks.

Changes

Upgraded to 11g.

Cause

The problem is caused by
Bug 8897574 - KEEP BUFFER POOL DOES NOT WORK

The issue is fixed in 12.1 and the fix is included in patch set 11.2.0.2 and higher.

Due to this bug, tables with size >10% of cache size, were being treated as 'large tables' for their reads and this resulted in execution of a new SERIAL_DIRECT_READ path in 11g.

Solution

Download and apply Patch 8897574 for 11.1.0.7 or 11.2.0.1 for your actual PSU level.

OR

Upgrade to 11.2.0.2 or higher


With the bug fix applied, any object in the KEEP buffer pool, whose size is less than DB_KEEP_CACHE_SIZE, is considered as a small or medium sized object. This will cache the read blocks and avoid subsequent direct read for these objects .

@ As per development update in the bug report, to workaround this issue we can set Event 10949 at level 1.