Re: DB Block Buffers - Too Much ???

From: Gaja Krishna Vaidyanatha <gajav_at_yahoo.com>
Date: Wed, 21 Jun 2000 11:51:39 -0700 (PDT)
Message-Id: <10535.110050@fatcity.com>

Deepak,

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.

• Deepak Sharma <sharmakdeep_at_yahoo.com> wrote:
  > Recently on one of our test systems the Buffer Cache
  > Hit Ratio was showing 80%. I increased
  > db_block_buffers from 6000 to 16000, and the after
  > that the hit ratio has dropped to 55%. It seemed
  > strange to me that increasing db_block_buffers should
  > actually decrease performance, until I read Oracle
  > tuning tips from Richard Niemiec, where he mentions
  > too much db_block_buffers is not good too, as it may
  > lead to swapping. My question is how do you determine
  > whether the buffers are too low or too high ?
  >
  > SQL> select state, count(*) from x$bh group by state;
  >
  > STATE COUNT(*)
  > ---------- ----------
  > 1 13047
  > 3 2953
  >
  > Does state = '1' mean this memory is not being used
  > and if so, should I decrease the buffers ?
  >
  > -- Deepak
  >
  > =====
  > Oracle DBA,
  > Minneapolis, MN
  > USA
  >
  > __________________________________________________
  > Do You Yahoo!?
  > Send instant messages with Yahoo! Messenger.
  > http://im.yahoo.com/
  > --
  > Author: Deepak Sharma
  > INET: sharmakdeep_at_yahoo.com
  >
  > Fat City Network Services -- (858) 538-5051 FAX: (858)
  > 538-5051
  > San Diego, California -- Public Internet access /
  > Mailing Lists
  >

  ---

  
  > To REMOVE yourself from this mailing list, send an E-Mail
  > message
  > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru')
  > and in
  > the message BODY, include a line containing: UNSUB ORACLE-L
  > (or the name of mailing list you want to be removed from).
  > You may
  > also send the HELP command for other information (like
  subscribing).

  ---

Gaja Krishna Vaidyanatha | gajav_at_yahoo.com 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