Question On Hit ratio
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Question On Hit ratio

  1. #1
    Join Date
    Jan 2001
    Posts
    642
    Hi,

    Here is a small statistics report I get about the performance of my database daily;;

    I note that
    1)

    AVERAGE HIT RATIO
    -----------------
    91.9043446


    Average hit ratio seems to be decreasing from 96 to 91 (gradually) Is this a matter of consern. What is the Average hit ratio that is optimum

    2)
    ********************** Hit Ratio by User *****************

    SID USERNAME CONSISTENT_GETS BLOCK_GETS PHYSICAL_READS hitratio
    ---------- -------- --------------- ---------- -------------- ----------
    35 REI 4792 14 3932 18.1856013
    48 REI 418110 1238 341465 18.5724029
    66 REI 18584 153 774 95.8691359

    Here in case of the user wise hit ratio's some sid's have a very low hitratio and some have good ratio (95.9). How is this being caused,

    Please advise

    Badrinath


  2. #2
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    You don't need to look from user perspective. It means one user is running memory intensive operations/queries and other is not.

    Keep it at database level in high 90's. that fair hit ratio.
    Reddy,Sam

  3. #3
    Join Date
    Jan 2001
    Posts
    642
    Hi,

    Now my hit ratio has dropped to 89. As I understand ,it's fair to have it > 90.0. Now How do i increase the hit ratio.
    (What will be impact of increasing the db_buffer_blocks in init.ora)

    Badrianth

  4. #4
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    We talked about hit ratio. We didn't talk about which hit ratio ? Hit ratio is nothing but contention for request interms of hits and misses. Could be with Memory/RBS/IO contention etc., Depending up on what you are dealing with, you have increase that paticular component of Oracle. If its memory increase db_buffers and other parameters of that deal with SGA. If its with RBS, try increasing RBS`s... and so on..

    Hope this helps
    Reddy,Sam

  5. #5
    Join Date
    Jan 2001
    Posts
    642
    In my context, I am tking the ratio of

    physical reads/ db_block_gets + consistent Gets (from v$sysstat).

    This seems to be the memory stuff., so can I just increase the db_block_buffers or Do it have anything else to check.
    Badrinath

  6. #6
    Join Date
    Jan 2001
    Posts
    642
    Second thing I wanted to ask was:

    why does this ratio keeps decresing with time. I have the same number of users working on the same application except that the data increases by 10000 records every week(rowsize is 350bytes)

    Badrinath

  7. #7
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    First Q:

    That should solve your problem. Unless you see the contention for SORT_AREA/LOG_BUFFER you should consider increasing those values also.

    Second Q:

    There should be activity increasing, Else should be memory leak. There is high possibility for memory leak on Oracle 805. I dunno waht version you are using...
    Reddy,Sam

  8. #8
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    Since the users' query seems to be fetching more and more data every day. As the data gets to increase, the amount of information that could be stored in the buffer_cache decreases, since it had to service different users queries. as a result the miss rate increases and draws down the hit ratio. To better serve the problem, refine the user queries and use bind variables. Also as Reddy pointed out, increase the apporpriate parameters to serve the need.

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  9. #9
    Join Date
    Jul 2000
    Location
    Oxford, OH
    Posts
    117
    Another possibility might be that someone is running a large query(s) from time to time that are pushing other blocks out of the cache. You may want to consider increasing your buffer cache size or implementing multiple buffer pools and caching oft used tables.

    As far as some users getting higher ratio's than others, that's entirely possible. It could be that a majority of users are querying oft used tables which means they will have a better chance of getting blocks from the cache. If some other users are querying more historical data or different types of data on a more infrequent basis the blocks might be getting aged out of the cache because of the more frequently executed queries.

    As was suggested, the best thing you can do is tune for overall hit ratios and look at your biggest resource hogs and try to optimize those.

    Joe
    _________________________
    Joe Ramsey
    Senior Database Administrator
    dbaDirect, Inc.
    (877)687-3227

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