-
buffer cache hit ratio
Hi all,
I ran the below query on my database and I got:
Code:
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
Last edited by learning_bee; 01-17-2005 at 04:31 PM.
-
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
Code:
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.
Code:
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!
Oracle it's not just a database it's a lifestyle!
--------------
BTW....You need to get a girlfriend who's last name isn't .jpg
-
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.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
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).
-
Indeed, I believe that somewhere in the documentation it warns against relying on the statistic#, as they are liable to change.
-
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???
-
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|