Buffer hit ratio
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Buffer hit ratio

  1. #1
    Join Date
    Apr 2001
    Posts
    16

    Exclamation

    How do i increase the buffer hit ratio ??
    What is a good buffer hit ratio for a database...

  2. #2
    Join Date
    Feb 2001
    Location
    Bombay,India
    Posts
    530
    Hi,
    Database buffer hit ratio shows ur database buffer cache hit.Hit means that whenever an application requests data and that data is in database buffer cache,Oracle doesnot have to perform an I/O to get the data from disk to the database buffer.
    Higher the database buffer cache ,better the performance in terms of I/O.A good buffer cache hit ratio should be more than 90%.It should be in the range of 90-98.
    U have to see the database buffer cache hit ratio during normal business hours and peak time and ove a period of time.If ur database buffer cache hit ratio is less than 90%,shutdown the database change the init.ora file and increase the DB_BLOCK_BUFFERS parameter and bounce back the database.Check whether the hit ratio has increased.
    The following formula is used to get the cache hit ratio

    select (1-(phy.value/(db.value+cons.value)))*100 "Cache Hit Percentage"
    from v$sysstat phy,v$sysstat db,v$sysstat cons
    where phy.name='physical reads' and
    db.name='db block gets' and
    cons.name='consistent gets';

    ****cache hit ratio should be more than 90%(Oracle
    recommendation)

    Check the cache hit ratio over a period of time and during peak business hours

    Regards,
    Rohit Nirkhe,Oracle DBA,OCP 8i
    rohitsn@altavista.com

    Thanks and Regards
    Rohit S Nirkhe
    rohitsnirkhe@rediffmail.com

  3. #3
    Join Date
    Mar 2000
    Location
    Chennai.Tamilnadu.India.
    Posts
    658

    Cache Hit Ratio

    Hi, 4th May 2001 17:01 hrs chennai

    Cache Hit ratio.

    STEP==>I

    The tuning goals should go like this
    ======================
    i.Servers should find data in the memory.
    ii.90% hit Ratio for OLTP systems.

    The tuning techniques goes like this.
    =======================
    i.increase buffer cache size
    ii.Use multiple buffer pools
    iii.Cache tables

    STEP==>II

    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';

    simple as Hit Ratio=1-(physical read/(db block gets+consistent gets))

    III)

    How to find by Adding and Decreasing the Buffers
    ================================
    While you add say 100 buffers and to see the performance of db buffer cache

    Enable DB_BLOCK_LRU_EXTENDED_STATISTICS=200 in init.ora.

    V$RECENT_BUCKETS Contains statistics that estimate the perfromance of a larger cache.

    use query
    Select sum(count) act from V$RECENT_BUCKETS where rownum<20;

    This you are doing to find the impact of additional cache hits that would have incur by increasing the cache size i.e from 100 to 120 buffers.

    Now you can find the impact on the cache hit ratio by including the additional cache hits in the formula above used.
    Since the additional buffers are reducing the physical I/O's we have to subtract the act value from the phy reads.

    SELECT 1-((PHY.VALUE-act)/(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';

    Decreasing the buffers if the hit ratio is high and want to save excess memory by testing as follows.

    V$CURRENT_BUCKETS==>use here DB_BLOCK_LRU_STATISTICS=200

    and all other steps you can do here you have to add in the above formula etc.Check in docs you get infomation in this or post if you have problem in this thread.

    Cheers

    Padmam



    Attitude:Attack every problem with enthusiasam ...as if your survival depends upon it

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