Message-Id: <10536.110131@fatcity.com> From: "Gesler, Richard" Date: Thu, 22 Jun 2000 08:25:01 -0400 Subject: RE: DB Block Buffers - Too Much ??? Gaja, Great point. I feel like Cache hit ratios really misused. All the Oracle books provide the scripts and give a basic rule of thumb such as keep it at 95%+. Seems like this is something easy for the DBA to get their hands around. I suggest DBA's look into Wait-Base performance tuning by checking out Craig Shallahamer's Paper "Direct Contention Identification Using Oracle's Session Wait Virtual Views" at http://www.orapub.com ............................................................................ ................................. Rich Gesler <>< Saved By Grace Database Administrator office: 336 878-7268 LifeStyle Furnishings International fax : 336 878-7038 ............................................................................ ................................. More men fail through lack of purpose than lack of talent. > -----Original Message----- > From: Gaja Krishna Vaidyanatha [SMTP:gajav@yahoo.com] > Sent: Wednesday, June 21, 2000 3:57 PM > To: Multiple recipients of list ORACLE-L > Subject: Re: DB Block Buffers - Too Much ??? > > 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 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@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@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@yahoo.com > Brio Technology | (972)-304-1170 > > "Opinions and views expressed are my own and not of Brio Technology" > > __________________________________________________ > Do You Yahoo!? > Send instant messages with Yahoo! Messenger. > http://im.yahoo.com/ > -- > Author: Gaja Krishna Vaidyanatha > INET: gajav@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@fatcity.com (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L