Oracle Scratchpad

March 20, 2010

Not KEEPing

Filed under: Infrastructure,Performance,Troubleshooting — Jonathan Lewis @ 9:10 am GMT Mar 20,2010

I’ve recently spent some time working with a client to get the maximum benefit from their KEEP pool – I’ll be publishing some interesting demonstrations when I get some time – and thought that some of you would be keen to hear about bug 8897574.

The bug applies to – luckily my client is still on 10.2 – and has the following abstract: KEEP BUFFER POOL DOES NOT WORK.

It’s not quite as bad as it sounds at first sight, because when you read the body of the bug, the “rediscovery information” says:

keep buffer pool does not work for large keep objects

So there is no (currently reported) workaround – but at least it only applies to “large” objects. Of course, this raises the question: “what is a large object?”. Taking a guess here I’d look at objects larger than 10% of the buffer cache***, and do some experiments around the area of adaptive serial direct path reads. For critical systems that had already upgraded I’d even start looking at the impact of playing with the CACHE option for objects.

If you are depending on the KEEP cache to minimise the I/O due to critical large objects – as my client does – you need to look very carefully at what does if you upgrade … and since the bug isn’t labelled as “fixed in 11.2”, you need to check that upgrade path too.

*** It used to be easy to talk about things like 10% of the buffer cache – but in modern versions of Oracle, with the sundry automatic settings and “non-standard block size” caches, which size should we take 10% of ? The memory_target, the memory_max_target, the sga_target, the sga_max_size, the db_cache_size, the sum of the db_keep_cache_size, db_recycle_cache_size and db_cache_size … and should we add in the db_{N}k_cache_size. (Personally I never found time to check even in 8i what the 2% figures for _small_table_threshold applied to.)


  1. jonathan, do you see much use of solid state memory devices in your client base as a substitute for use of KEEP pools or large buffer caches? One hears that the price point is becoming more affordable, and there is that whole Exadata thing … I wonder whether we are approaching a tipping point in their wider adoption.

    Comment by David Aldridge — March 22, 2010 @ 7:20 am GMT Mar 22,2010 | Reply

    • David,

      Good question – and the answer is none. (I hadn’t really noticed that this was the case until you asked, so I’ve just surprised myself slightly.)

      Possibly it’s because of a configuration issue. Although some of my clients have very big systems with a need for very fast response times, the I/O times are very fast without SSD and – to a certain extent – the I/O times are limited by the round-trip time to the SAN, rather than by the action of the disks.

      It’s interesting to note, when thinking of this, that the fundamental architecture of Exadata 2 tries to put the processing very close to the discs, and aims to minimise network traffic.

      Comment by Jonathan Lewis — March 23, 2010 @ 9:03 pm GMT Mar 23,2010 | Reply

  2. […] @ 11:36 am Jonathan Lewis made reference to a 11g bug related to using a KEEP POOL in his note Not KEEPing.  Oracle 11g introduced a new feature called adaptive serial direct path reads which […]

    Pingback by, KEEP pool / Serial Direct Read « Nigel Noble's Oracle Blog — July 5, 2010 @ 10:36 am BST Jul 5,2010 | Reply

  3. Hi Jonathan,

    The bug note now says it is to be fixed in 12.1 (how many of us are struggling to get to 11 :-) )
    There is also an event, 10949, that can be set to avoid the issue by forcing direct reads to be done as normal scattered reads, which presumably will go into the cache for the object.



    Comment by mwidlake — July 14, 2010 @ 1:31 pm BST Jul 14,2010 | Reply

  4. Hi Jonathan,


    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). 

    I have posted a update on the end of my blog item ( ) as well (which was referencing the same issue in



    Comment by Nigel Noble — October 8, 2010 @ 2:40 pm BST Oct 8,2010 | Reply

  5. […] Here’s a note I’ve been meaning to research and write up for more than 18 months – ever since Dion Cho pinged a note I’d written about the effects of partitioning because of a comment it made about the “2% small table threshold”. […]

    Pingback by Small Tables « Oracle Scratchpad — January 18, 2012 @ 5:06 pm GMT Jan 18,2012 | 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.

Website Powered by