This discussion is archived
1 2 3 4 Previous Next 59 Replies Latest reply: Nov 12, 2007 11:02 AM by ca110974 RSS

How to tune effectively to the buffer cache hit ratio?

544368 Newbie
Currently Being Moderated
We have a database with following sga sizes;
shared pool : 256 MB
Buffer Cache: 880 MB
Large pool: 32 MB
Java pool: 32 MB
Total SGA: 1201.435 mb. From the health check, i found Buffer Cache Hit Ratio is giving the trouble. so, i need advice from dba gurus, should Buffer Cache Hit ratio be increase or decreasee, in order to improve the performance?
Please help me
Ateeq
  • 1. Re: How to tune effectively to the buffer cache hit ratio?
    108476 Journeyer
    Currently Being Moderated
    Hi Ateeq,
    should Buffer Cache Hit ratio be increase or decreasee, in order to improve the performance?
    No, the BCHR is ONLY a measure of the probability that a block will be in the buffer upon re-read:

    http://www.dba-oracle.com/m_buffer_cache_hit_ratio.htm

    If you want to add more buffers for faster performance, first, check v$db_cache_advice and see if you have cached your working set of frequently referenced blocks:

    http://www.dba-oracle.com/art_orafaq_oracle_buffer_hot_ratio.htm

    Hope this helps. . .

    Don Burleson
    Oracle Press author
  • 2. Re: How to tune effectively to the buffer cache hit ratio?
    153119 Pro
    Currently Being Moderated
    BCHR is a meaningless indicator for the performance of the database.
    Also tuning BCHR is often symptom fighting as the root cause is inefficient SQL.
    Stop fighting symptoms, fix the application.

    --
    Sybrand Bakker
    Senior Oracle DBA
  • 3. Re: How to tune effectively to the buffer cache hit ratio?
    108476 Journeyer
    Currently Being Moderated
    BCHR is a meaningless indicator for the performance of the database.
    Meaningless, no, super-useful, no.

    If it's "meaningless", why then does Oracle offer a buffer cache advisor?

    http://www.dba-oracle.com/oracle10g_tuning/t_memory_advisor.htm
    Stop fighting symptoms, fix the application.
    That's nonsense. He did not say that anything was broken.

    Many people have well-tuned systems, and they just want to allocate the optimal buffer cache size.
  • 4. Re: How to tune effectively to the buffer cache hit ratio?
    544368 Newbie
    Currently Being Moderated
    May be rebuilding the indexes and tables having > 100 extents can solve the problem? We are running on 9.0.2 on windows NT server.
    Please advice
  • 5. Re: How to tune effectively to the buffer cache hit ratio?
    108476 Journeyer
    Currently Being Moderated
    Hi Ateek,
    solve the problem?
    We don't know that your problem is, do we?

    Can you generate a statspack or AWR report, and paste it into my statspackanalyzer:

    http://www.statspackanalyzer.com

    That might give us a clue . . . .
  • 6. Re: How to tune effectively to the buffer cache hit ratio?
    153119 Pro
    Currently Being Moderated
    Well-tuned systems are an exception in my book, as most systems I know have been developed by people who know 0 about Oracle. Also, they are usually 'database neutral' which means they are sqlserver apps running unchanged on Oracle.

    In my experience tuning the database,as you advocate by
    - throwing memory at the problem
    - using multiple block sizes
    - purchasing solid state disks
    won't help, EVER.
    The result will be marginal.
    It simply won't help.
    Consequently, the application should be fixed, and throwing memory at unscalable applications won't help at all, ever.
    You are promoting non-functional methods to resolve problems, and even more: you know it.
    The only advantage of your method is it will make you rich.

    --
    Sybrand Bakker
    Senior Oracle DBA
  • 7. Re: How to tune effectively to the buffer cache hit ratio?
    153119 Pro
    Currently Being Moderated
    Multiple extents are not an issue.
    This is a myth

    --
    Sybrand Bakker
    Senior Oracle DBA
  • 8. Re: How to tune effectively to the buffer cache hit ratio?
    544368 Newbie
    Currently Being Moderated
    I will generate statspack tommorrow morning when there will be lot of activity on the database. In the meantime, some output from Health check toad are:

    ->!Buffer Cache Hit Ratio : 82.3246

    -->[Segments with > 100 extents] Exec Time 1 seconds
    ! TABLE IN_CHIYODA.SPEC_REVISION_DOCUMENT_MAIN: 107 Extents
    ! TABLE EF2_DATA.N3RGRPRGRP: 126 Extents
    ! TABLE EF2_DATA.N3OBJATTR: 192 Extents
    ! TABLE EF2_DATA.EFT_UID: 119 Extents
    ! TABLE EF2_DATA.N2FILEOBJ: 121 Extents
    ! TABLE EF2_DATA.N2IFOBJ: 211 Extents
    ! TABLE EF2_DATA.N2REVGRP: 128 Extents
    ! INDEX EF2_DATA.N3OBJATTR_RLTLEFTRELINDEX: 122 Extents
    ! INDEX EF2_DATA.N3OBJATTR_RLTRIGHTRELINDEX: 114 Extents
    ! INDEX EF2_DATA.PK_N3OBJATTR: 119 Extents
    ! INDEX EF2_DATA.EFI_UID_N1UID: 120 Extents
    ! INDEX EF2_DATA.EFI_UID_N1OBJOBID: 103 Extents
    ! INDEX EF2_DATA.N1BOBJOBID_N2IFOBJ: 186 Extents
    ! INDEX EF2_DATA.N2IFOBJ_N1BOBJOBID: 159 Extents
    ! INDEX EF2_DATA.N2IFOBJ_N1NAME: 115 Extents
    ! INDEX EF2_DATA.PK_N2IFOBJ: 158 Extents
    ! INDEX EF2_DATA.N2IFOBJ_MYINDEX: 187 Extents
    Can you advice anthing based on these results.
    Thanks
  • 9. Re: How to tune effectively to the buffer cache hit ratio?
    544368 Newbie
    Currently Being Moderated
    I will generate statspack tommorrow morning when there will be lot of activity on the database. In the meantime, some output from Health check toad are:

    ->!Buffer Cache Hit Ratio : 82.3246

    -->[Segments with > 100 extents] Exec Time 1 seconds
    ! TABLE IN_CHIYODA.SPEC_REVISION_DOCUMENT_MAIN: 107 Extents
    ! TABLE EF2_DATA.N3RGRPRGRP: 126 Extents
    ! TABLE EF2_DATA.N3OBJATTR: 192 Extents
    ! TABLE EF2_DATA.EFT_UID: 119 Extents
    ! TABLE EF2_DATA.N2FILEOBJ: 121 Extents
    ! TABLE EF2_DATA.N2IFOBJ: 211 Extents
    ! TABLE EF2_DATA.N2REVGRP: 128 Extents
    ! INDEX EF2_DATA.N3OBJATTR_RLTLEFTRELINDEX: 122 Extents
    ! INDEX EF2_DATA.N3OBJATTR_RLTRIGHTRELINDEX: 114 Extents
    ! INDEX EF2_DATA.PK_N3OBJATTR: 119 Extents
    ! INDEX EF2_DATA.EFI_UID_N1UID: 120 Extents
    ! INDEX EF2_DATA.EFI_UID_N1OBJOBID: 103 Extents
    ! INDEX EF2_DATA.N1BOBJOBID_N2IFOBJ: 186 Extents
    ! INDEX EF2_DATA.N2IFOBJ_N1BOBJOBID: 159 Extents
    ! INDEX EF2_DATA.N2IFOBJ_N1NAME: 115 Extents
    ! INDEX EF2_DATA.PK_N2IFOBJ: 158 Extents
    ! INDEX EF2_DATA.N2IFOBJ_MYINDEX: 187 Extents
    Can you advice anthing based on these results.
    Thanks
  • 10. Re: How to tune effectively to the buffer cache hit ratio?
    544368 Newbie
    Currently Being Moderated
    Then what else should be looked upon?
  • 11. Re: How to tune effectively to the buffer cache hit ratio?
    108476 Journeyer
    Currently Being Moderated
    Well-tuned systems are an exception in my book . . .
    This, I believe.
    In my experience tuning the database
    YOU DO NOT HAVE ANY verifiable experience whatsoever. . . .

    Why should anybody listen to you?
    purchasing solid state disks won't help, EVER.
    Nope, totally untrue, and there are reproduceable benchmarks to prove it:

    http://www.praetoriate.com/t_%20oraclerac_ssd_hypothesis_.htm
  • 12. Re: How to tune effectively to the buffer cache hit ratio?
    3520 Explorer
    Currently Being Moderated
    If you'd like to tune your ratio then there is a script [url http://www.oracledba.co.uk/tips/choose.htm]here ;)
    If you'd like to tune your app and especially if you know what exactly is slow then that is completely different question and then ask another question describing the problem what is slow.

    Gints Plivna
    http://www.gplivna.eu

    P.S. For those individuals everything taking seriously the first part was a joke.
  • 13. Re: How to tune effectively to the buffer cache hit ratio?
    108476 Journeyer
    Currently Being Moderated
    Hi Gints,
    For those individuals everything taking seriously the first part was a joke.
    May I ask you a question? Obviously, you can flood the buffer with reads against pre-cached rows and change the BCHR at-will, but does that mean that we conclude that it's totally useless?

    I find some limited use in monitoring BCHR, to alert me to load changes, something unexpected going on.
  • 14. Re: How to tune effectively to the buffer cache hit ratio?
    3520 Explorer
    Currently Being Moderated
    May I ask you a question? Obviously, you can flood
    the buffer with reads against pre-cached rows and
    change the BCHR at-will, but does that mean that we
    conclude that it's totally useless?

    I find some limited use in monitoring BCHR, to alert
    me to load changes, something unexpected going on.
    Don, You know I don't want to beat dead horse again, again and again. Beating dead horses is not one of my hobbies. You showed OP your article(-s) I showed him an article explaining a particular nature of this ratio ;) and let's allow the OP make his decision.
    Especially when the OP can put bchr oracle in google and got quite many discussions beating THE HORSE.

    Gints Plivna
    http://www.gplivna.eu
1 2 3 4 Previous Next