Question On Hit ratio

[badrinathn]

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

[sreddy]

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.

[badrinathn]

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

[sreddy]

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

[badrinathn]

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

[badrinathn]

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

[sreddy]

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...

[sambavan]

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

[wjramsey]

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