Buffer Hit Ratio is 0

[Chandra]

Hi,
My instance is showing Buffer Hit Ratio = 0. it looks weared for me. you guys have any idea.

Thanks

[pando]

well what's your query

also post consistent gets, db block gets and physical reads

[launchpad]

and you are also sure that this is buffer hit ratio and not buffer cache miss ratio.. also if this is the case then have you identified the sql's that are performing poorly ??

[Chandra]

Here are the hitratios.
DB BLOCK HIT RATIO
Read-Buffer Hit ration
---------------------0
Elapsed: 00:00:00.00

DICTIONARY CACHE HIT RATIO
Gets Misses HitRatio
---------- ---------- ----------
40756073 3853 99.9905471

Elapsed: 00:00:00.00

LIBRARY CACHE HIT RATIO
PINS PINHITS PHITRAT MISSES HITRAT
---------- ---------- ---------- ---------- ----------
148947286 146576019 98.4079824 14724 99.9901156

Elapsed: 00:00:00.00
Thanks.

[pando]

please post the values I mentioned

also the query

[Chandra]

select * from history where phone='4569870092';

i got a local index on phone;
i check the explan plan it is showing the full table scan. i am seeing this change since last 2 days.

Thanks.

[pando]

hmm am I missing something here? What has that query to do with buffer ratio?

[Chandra]

SQL> select lpad(name,20,' ')||' = '||value xn1,value xv1
2 from v$sysstat where name='db block gets';

XN1 XV1
--------------------------------------------------------------- ----------
db block gets = 1472728 1472728

SQL> select lpad(name,20,' ')||' = '||value xn21,value xv2
2 from v$sysstat where name='physical reads';

XN21 XV2
--------------------------------------------------------------- ----------
physical reads = 2829324 2829324

SQL> select lpad(name,20,' ')||' = '||value xn3,value xv3
2 from v$sysstat where name='consistent gets';

XN3 XV3
--------------------------------------------------------------- ----------
consistent gets = 2017164 2017164

SQL>

[launchpad]

I am not sure about your post...
what I am sure is :
hit ratio = 1 - (physical reads) / (block gets + consistant gets)

can you try something like this please ?

select trunc((1-(sum(decode(name,'physical reads',value,0))/
(sum(decode(name,'db block gets', value,0))+
(sum(decode(name,'consistent gets',value,0)))))
)* 100) "Buffer Hit Ratio"
from v$sysstat;
I hope this will show u a better figure!

[pando]

Originally posted by Chandra
SQL> select lpad(name,20,' ')||' = '||value xn1,value xv1
2 from v$sysstat where name='db block gets';

XN1 XV1
--------------------------------------------------------------- ----------
db block gets = 1472728 1472728

SQL> select lpad(name,20,' ')||' = '||value xn21,value xv2
2 from v$sysstat where name='physical reads';

XN21 XV2
--------------------------------------------------------------- ----------
physical reads = 2829324 2829324

SQL> select lpad(name,20,' ')||' = '||value xn3,value xv3
2 from v$sysstat where name='consistent gets';

XN3 XV3
--------------------------------------------------------------- ----------
consistent gets = 2017164 2017164

SQL>

well with those values it is impossible you get 0 unless your query is wrong