RE: buffer cache

High BCHR is not *necessarily* bad, as Zhu Chao says. But, a systemwide BCHR in excess of 95% or so is often an indication of one or more inefficiencies that can result in greatly improved performance if corrected:

• SQL that motivates excessive numbers of unnecessary LIO calls (consistent gets, db block gets, buffer is pinned count, buffer is not pinned count, etc. [Thanks, Jonathan Lewis for teaching me about the "pinned" statistics.])
• Applications that fetch one row at a time instead of using array fetch technology [thanks again to Jonathan for bringing this to my attention too].
• Applications that roll back more often than they should [Jonathan again].

For more information, see my "Why a 99% buffer cache hit ratio is *not* ok", and perhaps Jonathan will tell you how to access his "The database gets better but the metrics look worse" article that he presented at IOUG.

Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com

Upcoming events:
- Hotsos Clinic 101 in London, Reykjavik, Ottawa, Dallas, Washington, Denver, Sydney
- Visit www.hotsos.com for schedule details...

-----Original Message-----
Sent: Thursday, May 08, 2003 3:32 AM
To: Multiple recipients of list ORACLE-L

Hi,stephane,

    Though BCHR tuning method is widely critiqued, high buffer cache hit ratio is not necessary bad either, right?

    Maybe high cache hit ration and v$bh with a lot of free/cr block means too big data buffer.

    Here is an example of too big data buffer:

15:29:44 SQL> select /*+rule*/ status,count(*) from v$bh group by status;

STATU COUNT(*)
----- ----------

cr         55120
free       54656
scur       17696
xcur       24549

4 rows selected.

Elapsed: 00:00:01.32

    While in another database with large sga,I think this buffer cache is not too big:  

15:27:05 perfstat_at_BIDDB.EACHNET.COM> select /*+rule*/ status,count(*) from v$bh group by status;

 STAT COUNT(*)

• ----------

cr 11162

read 1

xcur 1037413

Regards
zhu chao
msn:chao_ping_at_163.com
www.cnoug.org(China Oracle User Group)
----- Original Message -----
To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Monday, May 05, 2003 7:11 PM

> High BCHR, V$BH ...
>
> >----- ------- Original Message ------- -----
> >From: Arvind Kumar <arvindk_at_sqlstarintl.com>
> >To: Multiple recipients of list ORACLE-L
> ><ORACLE-L_at_fatcity.com>
> >Sent: Mon, 05 May 2003 02:41:36
> >
> >Dear All,
> >
> > how can i check if my buffer cache is bigger
> >than necessary ?oracle db
> >version is 8.1.7.
> >
> >
> >Thanks
> >
> >
> >Arvind
> >
> >
> >--
> >Please see the official ORACLE-L FAQ:
> >http://www.orafaq.net
> >--
> >Author: Arvind Kumar
> > INET: arvindk_at_sqlstarintl.com
> >
> >Fat City Network Services -- 858-538-5051
> >http://www.fatcity.com
> >San Diego, California -- Mailing list and
> >web hosting services
> >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).
> >---------------------------------------------------
> >------------------
>
> Regards,
>
> Stephane Faroult
> Oriole
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Stephane Faroult
> INET: sfaroult_at_oriolecorp.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> 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.net
-- 
Author: zhu chao
  INET: chao_ping_at_vip.163.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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.net
-- 
Author: Cary Millsap
  INET: cary.millsap_at_hotsos.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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).