Oracle physical read and logical read

Oracle logical read and physical read
1. Physical reading is the process of reading data from the disk to buffer catch.
Generally, if you find that the data does not exist in buffer catch when you need the data, that is, Oracle performs physical read.
Example:

C:/Documents and Settings/Administrator> sqlplus Jenny/Jenny
SQL> set autotrace traceonly;
SQL> select * From t_test1;

----------------------------------------------------------
Plan hash value: 1883417357
...........
----------------------------------------------------------
0 recursive cballs
0 dB block gets
11 consistent gets
3 physical reads -- three physical reads and writes occurred
0 redo size
10632 bytes sent via SQL * Net to client
451 bytes encoded ed via SQL * Net From Client
8 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (Disk)
97 rows processed

SQL> select * From t_test1;

----------------------------------------------------------
Plan hash value: 1883417357
........
----------------------------------------------------------
0 recursive cballs
0 dB block gets
11 consistent gets
0 physical reads -- there is no physical read here, because the data already exists in buffer catche.
0 redo size
10632 bytes sent via SQL * Net to client
451 bytes encoded ed via SQL * Net From Client
8 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (Disk)
97 rows processed

If you clear the cache again, execute the preceding query statement, and then read the data from the disk again.

SQL> alter session set events 'immediate trace name flush_cache ';
SQL> select * From t_test1;
----------------------------------------------------------
Plan hash value: 1883417355
........
----------------------------------------------------------
0 recursive cballs
0 dB block gets
11 consistent gets
3 physical reads -- reoccurs physical read
0 redo size
10632 bytes sent via SQL * Net to client
451 bytes encoded ed via SQL * Net From Client
8 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (Disk)
97 rows processed

2. Logical reading
Logical read refers to reading data blocks from (or from the View) buffer cache. Different data block access modes can be divided into real-time read and consistent read ).
　
1) Instant read
Real-time reading refers to reading the latest data of the Data Block. At any time, there is only one copy of the current data block in the buffer cache. Real-time reading usually occurs when you modify or delete data.
At this time, the process will add a row lock to the data and identify the data as "dirty" data.
SQL> select * From t_test1 for update;

----------------------------------------------------------
Plan hash value: 3323170753

------------------------------------------------------------------------------
| ID | operation | Name | rows | bytes | cost (% CPU) | time |
------------------------------------------------------------------------------
| 0 | SELECT statement | 97 | 8536 | 3 (0) | 00:00:01 |
| 1 | for update |
| 2 | table access full | t_test1 | 97 | 8536 | 3 (0) | 00:00:01 |
----------------------------------------------------------
1 recursive cballs
99 dB block gets -- instant read
15 consistent gets
0 physical reads
19608 redo size
9123 bytes sent via SQL * Net to client
451 bytes encoded ed via SQL * Net From Client
8 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (Disk)
97 rows processed

2) consistent read
Oracle is a multi-user system. Before a session starts to read data, it may modify the data to be read by another session. If the session reads the modified data, data inconsistency may occur.
Consistent reading is to ensure data consistency. The data block in the buffer cache has the SCN of the last data block modification.
If a transaction needs to modify the data in the data block, it will first save a copy of the data block before the modification and the data block of the SCN In the rollback segment, then, update the data block and Its SCN in the buffer cache, and identify it as "dirty" data.
When other processes read data blocks, the SCN on the data blocks and their own SCN are compared first. If the SCN on the data block is smaller than or equal to the SCN of the process itself, the data on the data block is directly read;
If the SCN on the data block is greater than the SCN of the process itself, the data block read data before modification will be found from the rollback segment. Generally, common queries are consistent reads.

SQL> select * From t_test1
----------------------------------------------------------
Plan hash value: 1883417357

-----------------------------------------------------------------------------
| ID | operation | Name | rows | bytes | cost (% CPU) | time |
-----------------------------------------------------------------------------
| 0 | SELECT statement | 97 | 8536 | 3 (0) | 00:00:01 |
| 1 | table access full | t_test1 | 97 | 8536 | 3 (0) | 00:00:01 |
-----------------------------------------------------------------------------
----------------------------------------------------------
1 recursive cballs
0 dB block gets
11 consistent gets -- consistent reading. This is the case where no rollback segment is read.
0 physical reads
0 redo size
10632 bytes sent via SQL * Net to client
451 bytes encoded ed via SQL * Net From Client
8 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (Disk)
97 rows processed

Open another session and execute update or delete of the same table without commit (only in this case will the rollback segment be read), and then execute SQL

SQL> select * From t_test1;
----------------------------------------------------------
Plan hash value: 1883417357

-----------------------------------------------------------------------------
| ID | operation | Name | rows | bytes | cost (% CPU) | time |
-----------------------------------------------------------------------------
| 0 | SELECT statement | 97 | 8536 | 3 (0) | 00:00:01 |
| 1 | table access full | t_test1 | 97 | 8536 | 3 (0) | 00:00:01 |
-----------------------------------------------------------------------------
----------------------------------------------------------
0 recursive cballs
0 dB block gets
12 consistent gets -- the data in the rollback segment needs to be read, so the consistent gets will be added
0 physical reads
0 redo size
10632 bytes sent via SQL * Net to client
451 bytes encoded ed via SQL * Net From Client
8 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (Disk)
97 rows processed

This is just a simple logical read and physical read. If you can contact the block distribution and hwm of the table, it should be clear.