buffer cache hit ratio
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: buffer cache hit ratio

  1. #1
    Join Date
    Oct 2003
    Posts
    312

    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 03:31 PM.

  2. #2
    Join Date
    Sep 2003
    Location
    over the hill and through the woods
    Posts
    995
    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

  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  4. #4
    Join Date
    Oct 2003
    Posts
    312
    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).

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Indeed, I believe that somewhere in the documentation it warns against relying on the statistic#, as they are liable to change.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  6. #6
    Join Date
    Oct 2003
    Posts
    312
    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???

  7. #7
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width