Oracle Scratchpad

March 20, 2010

Not KEEPing

Filed under: Infrastructure,Performance,Troubleshooting — Jonathan Lewis @ 9:10 am BST 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 11.1.0.7 – 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:

REDISCOVERY INFORMATION:
keep buffer pool does not work for large keep objects
WORKAROUND:
None

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

7 Comments »

  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 BST 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 BST 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 10.2.0.5, 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.

    Regards,

    Martin

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

  4. Hi Jonathan,

    FYI,

    The latest 11.2.0.2 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 ( http://nigelnoble.wordpress.com/2010/07/05/10-2-0-5-keep-pool-serial-direct-read/ ) as well (which was referencing the same issue in 10.2.0.5).

    regards

    Nigel.

    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 BST Jan 18,2012 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 4,011 other followers