Introduction to DB buffer cache hit rate

1. Block cache is the data high-speed buffer. It isSGAA major part of a zone. Used to store data blocks read from data files. Its size isInitsid. oraFileDb_block_buffersParameters to set(Oracle9iParameter Name:Db_cache_size). The content of this area is further divided:

LDirty BuffersThe buffer that has been changed but has not been saved to the disk.

LPinned BuffersThe buffer currently being accessed.

LFree BuffersThe currently unused buffer.

2.The data high-speed buffer also has a hit rate problem. If the required data can be found in the data high-speed buffer. It is called hit. The following statement queries information about several parameter values related to the hit rate.

Select name, value from V $ sysstatWhere name in ('consistent gets', 'db block gets', 'Physical reads ')
;

3.The following describes the specific parameters.

Official Website:

Db block gets: the number of accesses to the current image of a block
Consistent gets: the number of accesses to a read-consistent image of a block
Physical reads: The number of blocks read from disk

This mainly involves reading data from OracleConsistent mode and Current ModeThese two modes,Db block gets is the number of blocks read in current mode (unit should be "Block Times", the same block reads two counts for 2), while consistent
Gets is the number of blocks read in consistent mode (unit: Same as above ).

Data is read in current mode to ensure that the read data isLatest data at the current time pointThe purpose of this operation is generally to meet the requirements of DML statements. For example, to update data, you need to know the latest data. The consistent mode is mainly used to ensure ORACLE data.Consistent readGenerallySelectIn this case, the read data may be an actual block, or it may need to be constructed based on the SCN information, transaction information, and data in the rollback segment.

and physical reads is a concept relative to logical reads , the difference between the two is that the data read is from the buffer or from the DB file on the disk . You can also see from V $ sysstat that there are db block gets from cache and consistent gets from cache, the two values are not the same as db block gets and consistent
gets and smaller than the latter two. therefore, whether it is db block gets or consistent gets, both physical reads and logical reads may occur (whether required data already exists in the buffer) , that is, db block gets and consistent gets constitute the total number of times all blocks are read in a database operation. Therefore, logical reads can be calculated using the following formula: logical
reads = (db block gets + consistent gets)-physical reads.

As a result, the formula for cache hit rate is obtained:
hit ratio = (db block gets + consistent gets-physical reads) /(db block gets + consistent gets)
or
hit ratio = 1-(physical reads/(db block gets + consistent gets ))

PS:
since data is finally retrieved from the buffer in Oracle, every occurrence of a physical reads will inevitably result in a logical reads, but here is something to note: when there is a physical reads, there will be a logical reads here, in fact, only 1 block (physical reads) is counted here )!