DB Buffer Cache Hit Ratio
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: DB Buffer Cache Hit Ratio

  1. #1
    Join Date
    May 2001
    Location
    Delhi
    Posts
    340
    Hi,

    What things hv an effect on DB Buffer Cache Ratio.
    I mean what all factors.

    Like Librray Cache HR can be improved by writing sharable codes.

    Vijay

    --------------------------
    The Time has come ....

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    tune sql statements

  3. #3
    Join Date
    Apr 2002
    Posts
    55
    The question looks stupid.

    any way the solution is tuning.
    VenDe ChukPeen Zuri

    Someone who quotes himself as a DBA and posts simple question in forums is a big idiot to me . I donot care what others think

  4. #4
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    basically it's telling us how many disk reads we are having to make. A low ratio is bad, higher is better. Open to interpretation but a common acceptable ratio seems to be 90%. Initialially one might think that they could increas the size of the buffer cache to cut own on read to disk by adding more db_block_buffers. Doing so may ignor the root of the problem.

    Look for queries with high disk reads and see if they are indexed correctly.

    As an example, consider a 80 million table where you want to get back 100o rows, with no indexes oracle will perform a full table scan to get those rows and blow that ratio out of the water. By indexing correctly you can reduce disk read and have a more appropriate ratio.
    I'm stmontgo and I approve of this message

  5. #5
    Join Date
    Mar 2001
    Posts
    20
    That is called intelligent answer.

  6. #6
    Join Date
    May 2001
    Location
    Delhi
    Posts
    340


    Intelligent answer for a Stupid (vendechukp) Question....


    Stupidity ---------> Intelligence

    --------------------------
    The Time has come ....

  7. #7
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    I would not rely performance diagnostics on hit ratios, it was an old method for older Oracle versions but from Oracle 7 we get better performance pictures with system waits

  8. #8
    Join Date
    Aug 2000
    Location
    Shanghai
    Posts
    433
    Hi , Pando , Can you be more specific on system waits issue
    1. www.dbasupport.com
    2. www.dbforums.com
    3. www.itpub.net
    4. www.csdn.net
    5. www.umlchina.com
    6. www.tek-tips.com
    7. www.cnforyou.com
    8. fm365.federal.com.cn
    9. www.programmersheaven.com
    10.http://msdn.microsoft.com/library/default.asp
    ligang1000@hotmail.com

  9. #9
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    Hi.

    The YAPP method ignores hit ratios in favour of reducing/eliminating wait stats, since these are the cause of performance problems. Hit ratios are merely the symptom. To get an idea of what you should really be doing check these out:

    http://www.oracle-base.com/Links/yapp_anjo_kolk.pdf
    http://www.oracle-base.com/Links/oraperf_bw.pdf
    http://www.oracle-base.com/Articles/8i/Statspack8i.asp
    http://www.oraperf.com/index.html

    Cheers
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

  10. #10
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    Sytem waits are events that are waiting on resource.For example if you query v$session_wait you could see under
    event column values like 'db file sequential reads', 'db file scattered read' these are events where in 'db file sequential reads' is a full table scan and 'db file scattered read' is an index scan.These events will wait if both the index segment and data segment are placed in the same drive which will in turn reduce your hit ratio.You have similar events for redo log buffer cache,database buffer cache and library cache.

    regards
    anandkl
    anandkl

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