buffer cache hit ratio

[learning_bee]

Hi all,

I ran the below query on my database and I got:

select a.value + b.value "logical_reads", 
c.value "phys_reads",
round(100 * ((a.value+b.value)-c.value) / 
(a.value+b.value)) 
"BUFFER HIT RATIO" 
from v$sysstat a, v$sysstat b, v$sysstat c
where 
a.statistic# = 38 
and 
b.statistic# = 39 
and 
c.statistic# = 40;

WITH dB_CACHE_SIZE =712M IS IT STILL TOO SMALL???
logical_reads phys_reads BUFFER HIT RATIO
8982109 39133012 -336




just want to confirm with you guys

[OracleDoc]

Learning Bee,
Here are two scripts that will help you out better. IMO

this first one will tell you how many sorts are taking place

select a.value "Disk Sorts",b.value "Memory Sorts",round(100*b.value)/decode((a.value+b.value),
0,1,(a.value+b.value))
"Pct Memory Sorts"
from V$sysstat a, V$sysstat b
where a.name='sorts (disk)'
and b.name='sorts (memory)';
Disk Sorts Memory Sorts Pct Memory Sorts
---------- ------------ ----------------
     56864    186844662       99.9695754

As you can see most of the information that my users want is already in memory.

Now this one will tell you what your db_cache hit ratio is.

select 1-(sum(decode(name,'physical reads',value,0))/
(sum(decode(name,'db block gets',value,0)) +
(sum(decode(name,'consistent gets',value,0)))))
"Hit Ratio"
from V$sysstat;
 Hit Ratio
----------
.958470875

Using these two queries should tell you without a shadow of doubt if your db_cache is big enough or not.

Have that Oracle Kind of day!

[jmodic]

Learning_bee, which version of Oracle database are you using? If I remember correctly from your previous posts, you are using 9i (probably rel.2). So have you checked within your database, what those statistic# from your query actualy corresponds to?

I'm almost certain you are computing your buffer cache hit ration from the statistics about "gcs messages sent" (#38), "ges messages sent"(#39) and "db block gets"(#40). Those are definitely excelent imput data for your formula!!!

As pando allready adviced you once: use some common sence from time to time. Don't blindly follow everything that you find somewhere on the net or in books, try also to understand the logic behind that.

P.S. Those statistics numbers were valid for the buffer cache hit ratio components in 8i.

[learning_bee]

Jmodic and all,

first of all, thanks so much for all of the advises and time you guys are putting into this forum.

The reason I did ask those questions b/c I am new with PT, I read so many docs from Oracle and books and sometime it confused me so that may be I didn't ask the proper question and I apologize for that. I myself still try to learn from you guys and reading more books so hopefully one day it will be clearer.

I have performance problem with one of my database and I am trying to solve it. Right now, I am suspecting two problems on my database, one is I/O and the second one is LOCKING. I am still trying to see where and what part has been locking so I am digging and digging and sometime may be annoyingn to you guys. I apologize.

If you can give me direction so how to ping point the locking method, I would great appreciate ( I did find some articles on metalink but they are not too clear for me).

[slimdave]

Indeed, I believe that somewhere in the documentation it warns against relying on the statistic#, as they are liable to change.

[learning_bee]

as I mentioned earlier, I am suspecting I/O and lock, but I am not sure how to point out it's lock contention.

Do you have any advises of how to go by???

[slimdave]

Originally posted by learning_bee
as I mentioned earlier, I am suspecting I/O and lock, but I am not sure how to point out it's lock contention.

Do you have any advises of how to go by???

The first step is to ensure that you completely understand the Oracle locking mechanisms -- see http:///tahiti.oracle.com for the Concepts Guide.