Oracle Scratchpad

April 3, 2014

Cache anomaly

Filed under: Bugs,Oracle,Performance — Jonathan Lewis @ 1:27 pm BST Apr 3,2014

Just a quick heads-up for anyone who likes to play around with the Keep and Recycle caches.

In 11g Oracle introduced the option for serial direct path reads for tablescans on tables that was sufficiently large – which meant more than the small_table_threshold – provided the table wasn’t already sufficient well cached.  (The rules mean that the choice of mechanism can appear to be a little random in the production environment for tables that are near the threshold size – but if you try testing by doing “alter system flush buffer_cache” you find that you always get direct path reads in testing.)

I’ve just discovered a little oddity about this, though.  I have a table of about 50MB which is comfortably over the threshold for direct path reads. But if I create a KEEP cache (db_keep_cache_size) that is a little larger than the table and then assign the table to the KEEP cache (alter table xxx storage(buffer_pool keep)) then stops doing direct path reads, and caches the table.

Now this doesn’t seem unreasonable – if you’ve assigned an object to the KEEP cache you probably want it (or once wanted it) to be kept in cache as much as possible; so using the KEEP cache if it’s defined and specified makes sense. The reason I mention this as an oddity, though, is that it doesn’t reproduce in

I think I saw a bug note about this combination a few months ago- I was looking for something else at the time and, almost inevitably, I can’t find it when I want it – but I don’t remember whether it was the 11.1 or 11.2 behaviour that was deemed to be correct.


See comments 1 and 2 below.  I’ve written about this previously, and the caching bechaviour is the correct behaviour. The patch is in and backports are available for and The patch ensures that the table will be read into the cache if it is smaller than the db_keep_cache_size.  (Although we might want to check – see Tanel’s notes – whether this is based on the high water mark recorded in the segment header or on the optimizer stats for the table; and I suppose it might be worth checking that the same feature applies to index fast full scans). From the MoS document:

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.




  1. Jonathan,
    I think you are referring to the note on MOS Doc ID 1081553.1 on the Bug nr. 8897574

    Comment by Donatello Settembrino — April 3, 2014 @ 4:24 pm BST Apr 3,2014 | Reply

  2. Jonathan,

    I think you have written about that before:

    And as Donatello points out, this is the closing comment on Nigel Noble’s article (

    “The latest patchset (10098816) now includes the fix for Bug 8897574. So large objects assigned to the KEEP pool will always do buffered IO and never direct IO (regardless of the size of the object scan).


    Comment by Randolf Geist — April 3, 2014 @ 4:28 pm BST Apr 3,2014 | Reply

  3. Hello,
    Good point. Take a look in here, I’ll be interested in any kind of comments:


    Comment by Nikolay Kovachev — April 3, 2014 @ 6:58 pm BST Apr 3,2014 | Reply

  4. […] Our immediate fix is to use the KEEP BUFFER CACHE for the objects that are adversely affected by this wait event. This is the fix recommended by Jonathan Lewis when he discusses a Cache anomaly. […]

    Pingback by Random Notes on Direct Path Reads – Yet Another OCM — February 12, 2015 @ 10:02 am GMT Feb 12,2015 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Powered by