Buffer Cache Experiment 9: Read data block parsing from buffer Caceh-read from logical to physical read

Let's start with a big picture:

SQL statement used:

bys@ ocm1>select dbms_rowid.rowid_relative_fno (ROWID) file#,dbms_rowid.rowid_block_number rowid (Block#,deptno) From Bys.test;

file# block# DEPTNO

---------- ---------- ----------

4 391 10

As an example of the above diagram, the text description analyzes the process of obtaining the required data blocks when the foreground process issues a query statement:

Note: This article does not involve the parsing part of SQL statements, client and server interaction, and only involves buffer cache.

Here the physical reading is not direct path reading, not large table full table Scan-This point will be introduced at the end.

If the UPDATE statement is issued, just the lock on the buffer pin is an X exclusive lock, and the other steps are basically the same.

The example in this article reads only one block of data.

Reading a block of data from buffer cache generally takes about 100ns, and it takes 10ms to read a block of data from a general storage hard disk, so it's probably calculated that reading blocks from memory is nearly 100,000 times times faster than the hard drive.

Therefore, Oracle read the data block, first in the buffer cache to find, if there is, read-logic read; If the data block does not exist, then a physical read occurs, and the data is read into the buffer cache from the physical file (regardless of the direct read).

Previously written logic read: The logical reading of data reading simple analysis--about buffer CACHE