Archive for the 'Oracle Multiple Buffer Pools' Category

Multiple Buffer Pools with Oracle

The multiple buffer pools feature of Oracle has always seemed much underappreciated. Back in the late 90s we found the feature invaluable when running audited TPC-C benchmarks on Sequent gear. With that workload it is critical to be able to size blocks according to the row size and modify rate of certain relations. I think there may be a perception that this feature is a benchmark special when in fact it can be very helpful.

Half Full or Half Empty
Some folks don’t think about their SGA block buffer pool much beyond the fundamental aspect of having blocks there to satisfy logical reads. Since it is managed with a least recently used (LRU) algorithm, I think folks generally leave it at that. The problem is that if you take a pessimistic look at LRU it is not always sufficient. That is, evicting the least recently used blocks isn’t sufficient if the blocks shouldn’t really be in the cache in the first place. There are some blocks that should simply not remain in the cache and LRU is not aggressive enough to “protect the cache”, as I put it. As an aside, this sort of dovetails in with the thread I have going about saving the overhead of caching certain filesystem data (e.g., compressed archived redo log files) by implementing direct I/O.

The Art of Multiple Buffer Pools Tuning
I think it is an art. The general approach is to figure out what is consuming cache and whether or not you want that particular object to be consuming the cache. The following is a quote from a whitepaper I wrote entitled Buffer Cache Performance Analysis and Multiple Buffer Pools in Oracle9i:

Any table or index sustaining a large number of reads should also have a commensurate presence in the cache. If not, it is safe to surmise that the blocks are being read at random and therefore are not being shared.

Although that paper is old, I think the principles apply quite well even today. Note, I have not done this testing with Oracle10g yet so the queries in the paper may not be correct.

A Current Topic
In a recent thread on the oracle-l list, fellow OakTable Network member Mark Bobak wrote:

I went through an exercise a while ago where I tried to determine which segments were candidates for a KEEP or RECYCLE pool.

The query I came up with is:

select vss.owner,
vss.object_name, vss.statistic_name, vss.value,
ds.bytes segsize, ds.buffer_pool

from v$segment_statistics vss, dba_segments ds
where vss.statistic_name =’physical reads’
and vss.value > 5000000 —You may need to play with this threshold value for your environment
and ds.segment_type = vss.object_type
and ds.segment_name = vss.object_name
and ds.owner=vss.owner
and ds.buffer_pool = ‘DEFAULT’
order by value

Now, this helped me identify the segments that had the largest levels of physical IO, and the size of the segment. The idea is, “show me which segments are in the DEFAULT pool and are doing the largest numbers of physical reads, and show me how big those segments are.”

Mark continued with:

Next, ask yourself these questions:

– How much memory do I have to dedicate to KEEP and/or RECYCLE pools?

– Of the segments returned from the query above, which ones are small, and easy to entirely cache? These are candidates for KEEP pool.

– Which are entirely too large to consider caching? These are candidates for RECYCLE pool.

I hope my old Oracle9i paper and Mark’s oracle-l post are helpful.



I work for Amazon Web Services. The opinions I share in this blog are my own. I'm *not* communicating as a spokesperson for Amazon. In other words, I work at Amazon, but this is my own opinion.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 743 other followers

Oracle ACE Program Status

Click It

website metrics

Fond Memories


All content is © Kevin Closson and "Kevin Closson's Blog: Platforms, Databases, and Storage", 2006-2015. Unauthorized use and/or duplication of this material without express and written permission from this blog’s author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Kevin Closson and Kevin Closson's Blog: Platforms, Databases, and Storage with appropriate and specific direction to the original content.

%d bloggers like this: