Another doubt about Hit ratio

[badrinathn]

Hi,

The general expression for the hit ratio is

hit ratio = physical reads /(db block gets + consistent gets)
(From v$sysstat).

My question is, as the data grows,and the block size and other parameters being the same, the number of physical reads will be more
..
So isn't that the hit raio should be more..

please clarify...
Badrinath

[gpsingh]

Wrong
Hit ratio is 1 - (your formula) * 100

[badrinathn]

Sorry, talking about the buffer hit ratio.
Badrinath

[badrinathn]

Does rebuilding of indexes on all the tables improve the hit ratio.
Badrinath

[Queyon]

Rebuilding index will improve the Disk I/O usage but I don't think it'll help with hit ratio directly.

[badrinathn]

So, is there any way other than increasing the buffer area to improve this hit ratio.

Badrinath

[gpsingh]

Ok , little confusion to clear here,

Physical reads generally happen when the blocks are not found in the buffer and u have to do Disk I/O to load it into the buffer.

If you think the db_block_buffers is high enough.

You can do many things to improve hit ratio:

1)Use multiple buffer pool startegy.
2) For small lookup tables, cache them.
3)Usage indexes properly (even though Oracle does not think the index blocks are in buffers and goes to disk but you can tune it with init.ora parameter which shall force Oracle to first look in buffers and then do disk i/o.
Even then the number of disk i/o is less.
4)Check disk sorting, use direct sorts , which is default in 8.1.6.

But if buffers are less, u have no choice.