Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: DB Block Buffers - Too Much ???

Re: DB Block Buffers - Too Much ???

From: Gaja Krishna Vaidyanatha <>
Date: Wed, 21 Jun 2000 11:51:39 -0700 (PDT)
Message-Id: <>


At the outset, it seems like you may have measured your cache hit ratios too soon (before the cache got a chance to ramp up). Further, I want to clarify something regarding your quote from Rich Niemiec's book.

You quoted him saying that "too much db_block_buffers is not good too, as it may lead to swapping". While that may be a true statement, I am hoping that you did not infer "not good" to be a reduction in your cache hit ratios. When you overallocate more memory that what you can afford, the cache hit ratios do not get negatively affected. In fact you might even see a very high ratio, but that in itself does not mean anything.

If overallocation is done, and if a significant portion of your SGA is getting paged/swapped out, then you will experience a system-wide degradation in performance. Goes to prove that cache hit ratios by themselves cannot validate that Oracle is running and functioning optimally. They are just one of the indicators for performance. You really have to look at the "amount of work done" or "throughput" on the system and measure the success of your tuning efforts.

I have been involved with Oracle sites, who have had 90+ % cache hit ratios across the board, but whose systems were just crawling and wilting under the pressure of memory overallocation and horrendous SQL. When the bad SQL was fixed, the cache hit ratios dropped to 70%, but the amount of work done was significantly higher.

To quote an example, when the db buffer cache hit ratio was 94%, one of their reports which was using a "correlated sub-query" was running for 45 minutes running away with 1 CPU. When the query was re-written, the db buffer cache hit ratio dropped to 70%, but the report ran in 45 seconds using 65% of 1 CPU.

Moral of the story: Cache hit ratios by themselves do not any way, shape or form prove Oracle's or the system's good health. The ratios should be combined with the O-S level statistics to arrive at any rational conclusion.

Best Regards,


Gaja Krishna Vaidyanatha | Brio Technology | (972)-304-1170

"Opinions and views expressed are my own and not of Brio Technology"

Received on Wed Jun 21 2000 - 13:51:39 CDT

Original text of this message