1. Physics read (physical read) 2. Logical reading (buffer read)

1. Physics reading (physical read)

When the data block is first read, it will be cached in the buffer cache, while the second read and modify the data block in memory buffer cache The following is an example:

1.1 First read:

C:\Documents and Settings\paul yi>sqlplus "/as sysdba"

Sql*plus:release 9.2.0.4.0-production on Thu Feb 09:32:04

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0-production
With the partitioning, OLAP and Oracle Data Mining options
Jserver Release 9.2.0.4.0-production

Sql> Set Autotrace traceonly
Sql> select * from test;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=choose (cost=2 card=4 bytes=8)
1 0 TABLE ACCESS (full) ' TEST ' (cost=2 card=4 bytes=8)

Statistics
----------------------------------------------------------
175 recursive calls
0 db Block gets
Consistent gets
9 Physical reads--9 physical reads
0 Redo Size
373 Bytes sent via sql*net toClient
503 Bytes received via sql*net from client
2 sql*net roundtrips To/from Client
2 Sorts (memory)
0 Sorts (disk)
1 rows processed

1.2 Second reading

Sql> select * from test;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=choose (cost=2 card=4 bytes=8)
1 0 TABLE ACCESS (full) ' TEST ' (cost=2 card=4 bytes=8)

Statistics
----------------------------------------------------------
0 Recursive calls
0 db Block gets
7 Consistent gets
0 physical reads--No physical reading occurred, read directly from buffer cache
0 Redo Size
373 Bytes sent via sql*net to client
503 Bytes received via sql*net from client
2 sql*net roundtrips To/from Client
0 Sorts (memory)
0 Sorts (disk)
1 rows processed

1.3 Data blocks are read back into the buffer cache, which occurs in the

If new data needs to be read into the buffer cache, and the buffer cache does not have enough free space , Oracle replaces the LRU end of the LRU list with the LRU algorithm. When this data is accessed again, it needs to be read back from disk.

Sql> alter session SET events ' immediate trace name Flush_cache ';--empty data buffers

Session altered.

Sql> select * from test;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=choose (cost=2 card=4 bytes=8)
1 0 TABLE ACCESS (full) ' TEST ' (cost=2 card=4 bytes=8)

Statistics
----------------------------------------------------------
0 Recursive calls
0 db Block gets
7 Consistent gets
6 physical reads--again physical reading
0 Redo Size
373 Bytes sent via sql*net to client
503 Bytes received via sql*net from client
2 sql*net roundtrips To/from Client
0 Sorts (memory)
0 Sorts (disk)
1 rows processed

2. Logical reading (buffer read)

Logical reading is the reading of data blocks from (or view) Buffer cache. Depending on the pattern of access blocks, it can be divided into instant read (current read) and consistent read (consistent read). Note: Logical IO is only logically readable and not logically written. Instant Reading

Instant reading reads the current data in the block. There is only one copy of the current block of data in the buffer cache at any time. Instant reading usually occurs when the data is modified and deleted. At this point, the process adds row-level locks to the data and identifies the data as "dirty" data.

Sql> SELECT * from test for update;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=choose (cost=2 card=4 bytes=8)
1 0 for UPDATE
2 1 TABLE ACCESS (full) ' TEST ' (cost=2 card=4 bytes=8)

Statistics
----------------------------------------------------------
0 Recursive calls
1 db block gets
Consistent gets
0 physical Reads
252 Redo Size
386 bytes sent via sql*net to client
503 Bytes received via sql*net from client
2 sql*net roundtrips To/from Client
0 Sorts (memory)
0 Sorts (disk)
1 rows processed

Sql> Consistency Read

Oracle is a multiuser system. There may be other sessions that modify the data it will read before a session begins reading data and has not finished reading. If the session reads the modified data, it can cause inconsistencies in the data. Consistency reading is to ensure consistency of data. The SCN is the last time the data block is modified on the data block in the buffer cache. If a transaction needs to modify data in a block of data, it first saves a data block in the rollback segment that modifies the data and the SCN, and then updates the data in the buffer cache and its SCN, and identifies it as "dirty" data. When another process reads a block of data, it first compares the SCN on the data block and its own SCN. If the SCN on the block is less than the SCN of the process itself, the data on the data block is read directly, and if the SCN on the block is greater than the process itself, the modified block reads the data from the rollback segment. Generally, common queries are read consistently.

The following example helps you understand consistency reading:

In Session 1:

Sql> select * from test;

Id
----------
1000

sql> update test set id=2000;

1 row updated.

In Session 2:

Sql> set Autotrace on
Sql> select * from test;

Id
----------
1000

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=choose (cost=2 card=4 bytes=8)
1 0 TABLE ACCESS (full) ' TEST ' (cost=2 card=4 bytes=8)

Statistics
----------------------------------------------------------
0 Recursive calls
0 db Block gets
9 consistent gets nothing to do when update is 7 consistent gets description of 2 consistent gets these 2 are to be obtained from the rollback segment
0 physical Reads
Redo Size
373 Bytes sent via sql*net to client
503 Bytes received via sql*net from client
2 sql*net roundtrips To/from Client
0 Sorts (memory)
0 Sorts (disk)
1 rows processed

Sql>