Re: Database Buffer Cache Hit Ratio

"pcambraf" <pedro.cambra_at_gmail.com> wrote in message news:1128526193.081128.271240_at_f14g2000cwb.googlegroups.com... Hi people.

I had an 8.1.7.4 database with a 68% of buffer hit ratio and 180 Mb of cache, I've changed the db_block_buffers parameter so we have 234 Mb of db buffer cache, and the hit ratio is now 57%, almost a 10% less!!. I'm mad about this, i've been looking around and i think it could be a I/O retention in the only one DBWR process, żis this possible?

Are there any other factors to consider?

Thank you so much...

Regards

Sorry about arriving late on this discussion.

One reason why increasing the buffer size could decrease the cache hit ratio is because the size of the buffer cache affects the behaviour of tablescans (and index fast full scans).

A table is considered to be small if its size is less than 2% of the buffer cache size. If a LARGE table is scanned, then the blocks tend to pass through a small collection of buffers at the tail-end of the LRU.
The purpose of this strategy is to stop a large tablescan from flushing lots of useful data from the rest of the buffer.

If you are unlucky, by increasing the cache size you could have made Oracle start scanning a table that used to be considered LARGE, but is less than 2% of the increased buffer cache, and therefore is deemed to be small. So it gets loaded into the top (actually middle) of the LRU list, flushing lots of useful data as it does so. This would require you to re-read the useful data - which would rapidly kick out the scanned table, so it would soon have to be re-read again. (The latter problem is made more troublesome by the fact that until 10g, the touch count on blocks loaded by tablescans doesn't get set properly, so scanned blocks age out of the buffer very rapidly).

If the scans are necessary, these problems can often be addressed by careful use of the recycle and keep pools - but without a proper review of why the scans happen, sizes of objects, etc. it is not possible to give you the best advice on how to take advantage of the extra pools.

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
Cost Based Oracle: Fundamentals
Now available to pre-order.

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 4th Sept 2005