Re: performance issue on select count(*)

An unusually high BCHR could be an indicator that your database is running Connor McDonald's choose_a_hit_ratio procedure.

http://www.oracledba.co.uk/tips/choose.htm

Jared

Mladen Gogala <mladen_at_wangtrading.com>
Sent by: ml-errors_at_fatcity.com
 10/28/2003 09:09 AM
 Please respond to ORACLE-L  

        To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
        cc: 
        Subject:        Re: performance issue on select count(*)

So, what exactly is indicated by a high or low hit rate? What, exactly, is "high"
and what do you consider "low"?
What "HR" are you talking about?
This would be the infamous BCHR:

select 'bc_hit_ratio' ratio,( sum(decode(name, 'consistent gets',value,0)) + sum(decode(name,'db block gets', value,0)) - sum(decode(name,'physical reads', value,0))) / ( sum(decode(name, 'consistent gets',value,0))   + sum(decode(name,'db block gets', value,0)) ) * 100 from v$sysstat

What exactly should the number returned by this query tell me?

On 10/28/2003 10:59:25 AM, Binley Lim wrote:
>
> The symptom suggests caching is a big factor here - most likely
> block-buffers.
>
> Contrary to ?current? popular beliefs, BCHR is still a very relevant
> performance indicator - either being very high, or being too low - both
of
> which gives a good indication of something that needs to be looked at.
>
>
> > I would be interested to know if there is a way to speed up the
initial
> > execution or how to diagnose what the delay was. It does not seems
right
> > that there is such a big difference in elapsed time between the
initial
> and
> > subsequent execution.
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Binley Lim
> INET: Binley.Lim_at_xtra.co.nz
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 
  INET: Jared.Still_at_radisys.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).