Not KEEPing

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