Re: How does Oracle find a block in the buffer cache.

Scott Watson <rscottwatson_at_hotmail.com> wrote:
>I have been reading a book on Operating System architecture and the
>section on caching has really peaked my curiosity. Of course while
>reading this material I tried to relate it to how Oracle works and
>what I came up with is I really don't know. In the book they define 3
>types of mappings from the lower level (memory) to upper level (L1
>cache) which are direct, set-associative and fully associative. I was
>wondering what Oracle uses as an algorithm to find buffers in the
>cache. The only option I could see as valid is fully associative
>(any block could end up in any buffer) however doesn't that imply that
>oracle has to scan all blocks to find the correct buffer in the cache?
> If this is the case won't performance suffer as we have larger buffer
>caches?

The terminology of CPU caches isn't really very useful here. In those terms, yes the cache is "fully associative", any buffer on disk can end up in any memory location.

There are a number of things that make Oracle's cache management problems very different to a CPUs; for example, a cache miss is (relatively) far more costly for Oracle than a CPU, and space is (relatively) cheap in main memory.

Anyway, to simplify slightly, Oracle (8i, at least) maps each buffer to a hash chain. To locate a block, it first locates the hash chain for the Data Block Address, and then only has to search block headers for a fairly small number of blocks.

-- 
Andrew Mobbs - http://www.chiark.greenend.org.uk/~andrewm/