Hit ratio in oracle

[Aileen_L]

Hello guys !

I need your help to find out the problems regarding Hit ratio.what is the concept behind this.Why oracle uses this type of strategy in tuning.Just make me sure me the uses and purpose of hit ratio on different components of database.

Thanks.

[Praetor]

Hit ratio in oracle :

The Hit ratio term is used to find out the read consistency from the sources.It is used in tuning methodology of oracle database.There are two types of resources is being used to find out.If instance is taking the data upon user request from the disk or the SGA(System global area).

There are three columns in the v$sysstatis used to find out the hit ratio for a running instance.The name and values of the columns are dedicated the accessing the instance.

[Katty]

Columns in V$sysstat :

The V$sysstat dynamic view contains the columns which contains the exact functioning of instance regarding retrieving the data.

The columns and meaning are as follows:

Consistent GetsThe number of retrieves made upon the block buffer in consistent mode.
DB Blk Gets This column shows the number of blocks accessed via single block gets
Physical Reads Number of blocks fetched from the disk.

NOTES:
Logical reads are calculated upon the addition of consistent gets and db block gets.
The Hit Ratio should be greater than 80% otherwise you need to increase the size of DB_BLOCK_BUFFERS parameter in parameter file.

[kelfro]

Data Dictionary Hit Ratio :

You can get the information after calculation of the hit ratio using the v$sysstat view.There are so many dynamic views are provided to find out the results of particular components of SGA.

The v$rowcache will show you the statistics of data dictionary hit ratio.

select SUM(GETS),
SUM(GETMISSES),
ROUND((1 - (sum(GETMISSES) / sum(GETS))) * 100,2)
from v$rowcache

The Getsnumber of request on data object and Cache Misses is number of misses on the data dictionary cache.The Hit Ratio should be greater than 90% other wise you have to increase the size of SHARED POOL using the pfile of the database.

[Zecho]

SQL Cache Hit Ratio :
You can find out the hit ratio on the one of the component of SGA called "SQL Cache" using the following dynamic view.

select sum(PINS) Pins,
sum(RELOADS) Reloads,
round((sum(PINS) - sum(RELOADS)) / sum(PINS) * 100,2) Hit_Ratio
from v$librarycache



Where the PINS is the number of requested made upon the object.
Reloads is the number of failed pins which get reflected from the pins and accessed from the disk.

The Hit Ratio should be greater than 85%

[Reegan]

Library Cache Miss Ratio :

This is one of the component of SGA from where the current transactions are made.you can get the statistics of this component using the following view:

select 	sum(PINS) Executions,
	sum(RELOADS) cache_misses,
	sum(RELOADS) / sum(PINS) miss_ratio
from 	v$librarycache

Where the Executions shows the number times a pin is requests for object and Cache Misses show the number of missed executions.
NOTE : The hitratio should be less than the 1% other increase the SHARED POOL size.