Message-Id: <10535.110049@fatcity.com> From: "Cole, Bill" Date: Wed, 21 Jun 2000 14:40:59 -0400 Subject: RE: DB Block Buffers - Too Much ??? If you get into swapping, you've gone too far. I've got one system that works okay until it swaps and then it stops. Queries that took seconds (30) now take minutes (30+). Not to mention that Hit Ratio is based on large numbers. 99% may be too low and 80% too high depending on your applications. We manage 6 instances on a 12GB server. I've seen 99% hit ratios while users complain about response time and then periods of 90% hit ratios where users are very happy. Depends on the mix of queries. /Bill > -----Original Message----- > From: Weaver, Walt > Sent: Wednesday, June 21, 2000 2:26 PM > To: Multiple recipients of list ORACLE-L > Subject: RE: DB Block Buffers - Too Much ??? > > Just out of curiousity, did you measure hit ratio soon after re-starting > the > database? Wouldn't the hit ratio be misleadingly low for a while, since > Oracle was probably busy reading disk and loading stuff into the buffers? > > Other than that, I suppose you could monitor vmstat or some other utility > to > see if excessive memory swapping is going on. > > --Walt Weaver > Bozeman, Montana, USA > > -----Original Message----- > Sent: Wednesday, June 21, 2000 11:26 AM > To: Multiple recipients of list ORACLE-L > > > 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). > -- > Author: Weaver, Walt > INET: wweaver@rightnowtech.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