Oracle physical read and logical read

PhysicalReads: the logical read content is in the memory and does not need to be read from the hard disk. The formula is as follows: PhysicalReadsdbblockgets + consist

Physical Reads: The logical read content is in the memory and does not need to be read from the hard disk. The formula is as follows: Physical Reads = db block gets + consist

Physical Reads (Physical read ):

The logical read content is in the memory and does not need to be read from the hard disk.

The formula is as follows: Physical Reads = db block gets + consistent gets;

Logical Reads (Logical read ):

Physical reading means that the content is not in the memory and should be read into the memory on the hard disk.

Db block gets -- number of blocks read from the buffer cache.
Consistent gets -- number of undo data blocks read from the buffer cache.

Db block gets is the number of blocks read in current mode. In current mode, data is read to ensure that the data read is the latest data at the current time point. This is generally done for the needs of DML statements, such as updates, naturally, you need to know the latest data;

Consistent gets is the number of blocks read in consistent mode. The consistent mode is mainly used to ensure consistent reading of Oracle data. It generally occurs in the case of select. The read data may be an actual block, it may also need to be constructed based on the scn information, transaction information, and data in the rollback segment.

Summary:

In Oracle, the data is finally retrieved from the Buffer, so logicalreads will inevitably appear every time a physicalreads appears, but there is something worth attention here, that is, when there is a physicalreadsLogicalReadsHere, we actually only calculate 1 block!

Therefore, if you use this formula (Physical Reads = db block gets + consistent gets;) to calculate Physical Reads, the logical read contains Physical Reads.

Both db block gets and consistent gets may occur.Physicalreads andLogicalReadsIn either case, db block gets and consistent gets constitute the total number of times all blocks are read in a database operation.

Therefore, logicalreads can be calculated using the following formula:

Logicalreads = (db block gets + consistent gets)-physicalreads.

As a result, the formula for cache hit rate is obtained:
Hit Ratio = (db block gets + consistent gets-physicalreads)/(db block gets + consistent gets) * 100%;