Your eye into the buffer cache

ever asked yourself what is going on in the buffer cache? e.g., does oracle honour my settings for the different buffer cache types, that is, the default, keep and recycle cache? or, does oracle tune the buffer cache nicely, such that the application important database objects are in fact cached?

the latter is, however, not only oracle’s duty. imagine an application where there is an index missing on a large table. on every unindexed select, oracle will have to full scan the table, flooding the buffer cache with unwanted blocks. but how to trap this scenario?

the solution key is v$bh or, internally, x$bh, a system view that allows a blockwise inspection of the buffer cache. some pretend that v$bh has been introduced with oracle parallel server (ops), others relate it to the real application cluster (rac) environment.