Buffer Cache Hit Ratio
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Buffer Cache Hit Ratio

  1. #1
    Join Date
    Jun 2001
    Posts
    243
    I run the following script and I got 39% of cache hit ratio....isn't this has to be over 90%?....do I have to increase DB_BLOCK_BUFFER?...

    select 1 - (phy.Value / (cur. Value + con. Value)) "Cache Hit Ratio"
    from v$sysstat cur, v$sysstat con, v$sysstat phy
    where cur.name = 'db block gets' and con.name = 'consistent gets'
    and phy.name = 'physical reads'
    /

  2. #2
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    The answer to this would be yes. But before you increase, you would want to check the worse performing sql statements and tune them up. Then even after that your prformance is bad, increase the buffers.

    If your application had been doing a worse query, then it would not make much sense to increase the db buffers.

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  3. #3
    Join Date
    Jun 2001
    Posts
    243
    How do I find the worst query? and how much do I need to increase DB_BLOCK_BUFFER?

  4. #4
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    I have posted the references under the following thread. Please refer to it completely before you get into tuning:

    http://www.dbasupport.com/forums/sho...threadid=15562

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  5. #5
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Originally posted by gaegoori
    How do I find the worst query? and how much do I need to increase DB_BLOCK_BUFFER?
    Answer to Q1:

    select b.username, a.buffer_gets reads,
    a.executions exec, a.buffer_gets / decode
    (a.executions, 0, 1, a.executions) rds_exec_ratio,
    a.command_type, a.sql_text Statement
    from v$sqlarea a, dba_users b
    where a.parsing_user_id = b.user_id
    and a.buffer_gets > 100000
    order by a.buffer_gets desc;

    select b.username, a.disk_reads reads,
    a.executions exec, a.disk_reads / decode
    (a.executions, 0, 1, a.executions) rds_exec_ratio,
    a.command_type, a.sql_text Statement
    from v$sqlarea a, dba_users b
    where a.parsing_user_id = b.user_id
    and a.disk_reads > 100000
    order by a.disk_reads desc;

    Answer to Q2:

    I would advise you to have DB_BLOCK_BUFFERS = 25% of the size allocated to your memory. However, if you tell me how many users you have I might give you a better answer.

    How much memory have you got?

    Run the following:

    SQL> select name, value from v$sysstat
    2 where name in ('db block gets','consistent gets','physical reads');

    NAME VALUE
    ---------------------------------------------------------------- ----------
    db block gets 342670
    consistent gets 1626857
    physical reads 5332

    Physiacl reads should be less than 5% of db block gets + consistent gets.

    What do you get?

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