Re: negative value for buffer cache hit ratio

Infact one of our production database (8.0.x) has a buffer cache hit ratio of 2% for the last 2 years. All others are about 90%+.

Strange thing is that users are so happy with the performance of this database with 2% hitratio, they do not want us to increase the buffer cache!!

Rama

Rachel Carmichael wrote:

> Ramon,
>
> if you find the performance of the database is good, why are you trying to
> fix it??????
>
> Cache Hit Ratio is not the be-all and end-all of tuning. If the cache hit
> ratio is high, and you have large numbers of wait events, then your database
> needs tuning.
>
> If the cache hit ratio is low, but there are no wait events then your
> database does not need tuning.
>
> Rachel
>
> >From: "Ramon Estevez" <com.banilejas_at_codetel.net.do>
> >Reply-To: ORACLE-L_at_fatcity.com
> >To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> >Subject: RE: negative value for buffer cache hit ratio
> >Date: Wed, 08 Aug 2001 06:27:11 -0800
> >
> >Thanks,
> >
> >As far as I know, the correct value is as close to 100% as possible.
> >So, if mine is 0.68
> >
> >-------------------------*
> >MISS_RATE I
> >--------- I
> > 0.67% I
> > I
> >This is now 9:15 am. I
> >-------------------------*
> >
> >I find the performance of the DB good, but as the result of the query
> >I think that it could get better.
> >
> >I executed this script that was posted yesterday in the list.
> >
> >
> >select to_char(100 * misses / (logical - physical + misses), '9990.00')
> >||
> > 2 '%' miss_rate
> > 3 from ( select total_waits misses
> > 4 from sys.v_$system_event
> > 5 where event = 'db file sequential read'),
> > 6 ( select value physical
> > 7 from sys.v_$sysstat
> > 8 where name = 'physical reads'),
> > 9 ( select sum(value) logical
> >10 from sys.v_$sysstat
> >11 where name like '%consistent read gets'
> >12 or name = 'db block gets');
> >
> >
> >
> >Ramon Estevez
> >com.banilejas_at_codetel.net.do
> >
> >
> >
> >
> >
> >-----Mensaje original-----
> >De: root_at_fatcity.com [mailto:root_at_fatcity.com]En nombre de K
> >Gopalakrishnan
> >Enviado el: Tuesday, 07 August, 2001 5:07 PM
> >Para: Multiple recipients of list ORACLE-L
> >Asunto: RE: negative value for buffer cache hit ratio
> >
> >
> >Hi,
> >
> >Generally speaking.. HIT RATIOS does not give the true
> >picture always. I have seen databases performing
> >extremely good with 50% and bad databases with 99% hit
> >ratio.
> >
> >You should never decide the database performance based
> >on hit ratio and 90% hit ratio does not mean that 90%
> >of the data is ALWAYS read from the cache. It
> >translates in to something like this..A block is read
> >(consistent get) 9-10 times before written to disk..
> >
> >You should check your system wide wait statistics for
> >better tuning..
> >
> >
> >
> >
> >--- Ramon Estevez <com.banilejas_at_codetel.net.do>
> >wrote:
> > > Hi,
> > >
> > > My misses is
> > >
> > > MISS_RATE
> > > ---------
> > > 0.68%
> > >
> > > Is that good or bad, Oracle 8.0.5 Standard Edition,
> > > Windows 2000.
> > >
> > > Is there a 8.1.7 Standard Edition Version ?
> > >
> > > Ramon Estevez
> > > com.banilejas_at_codetel.net.do
> > >
> > >
> >
> >
> >=====
> >Have a nice day !!
> >------------------------------------------------------------
> >Best Regards,
> >K Gopalakrishnan,
> >Bangalore, INDIA.
> >
> >__________________________________________________
> >Do You Yahoo!?
> >Make international calls for as low as $.04/minute with Yahoo! Messenger
> >http://phonecard.yahoo.com/
> >--
> >Please see the official ORACLE-L FAQ: http://www.orafaq.com
> >--
> >Author: K Gopalakrishnan
> > INET: kaygopal_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).
> >
> >--
> >Please see the official ORACLE-L FAQ: http://www.orafaq.com
> >--
> >Author: Ramon Estevez
> > INET: com.banilejas_at_codetel.net.do
> >
> >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).
>
> _________________________________________________________________
> Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Rachel Carmichael
> INET: carmichr_at_hotmail.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).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Rama Malladi
  INET: rama_at_toyota.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).