Understanding of oracle physical reads and logical reads

Oracle physical reading and logical reading comprehension 2008-02-2809: 51: 23 persons classification: Learning View (3120) Comments (22) score (2311) 1. physicalread: when a data block is read for the first time, it is cached in buffercache. When the data block is read and modified for the second time, it is cached in the memory buffercache. The following is an example:

Oracle physical read, logical read comprehension/09:51:23/personal classification: Learning View (3120)/comment (22)/score (23/11) 1. physical read: when a data block is read for the first time, it is cached in the buffer cache. When the data block is read and modified for the second time, it is cached in the memory buffer. For example:

Understanding of oracle physical reads and logical reads

/09:51:23/personal classification: Learning

View (3120)/comment (22)/score (23/11)

1. physical read)

When a data block is read for the first time, it will be cached in the buffer cache, and the second time it reads and modifies the data block, it will be cached in the memory buffer. The following is an example:

1.1 first read:

C: \ Documents ents and Settings \ Paul Yi> sqlplus "/as sysdba"

SQL * Plus: Release 9.2.0.4.0-Production on Thu Feb 28 09:32:042008

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

Connected:
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) OF 'test' (Cost = 2 Card = 4 Bytes = 8)

Statistics
----------------------------------------------------------
175 recursive cballs
0 db block gets
24 consistent gets
9 physical reads -- 9 physical reads
0 redo size
373 bytes sent via SQL * NetClient
503 bytes encoded ed via SQL * Net from client
2 SQL * Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processed

1.2 second read

SQL> select * from test;

Execution Plan
----------------------------------------------------------
0 select statement ptimizer = CHOOSE (Cost = 2 Card = 4 Bytes = 8)
1 0 table access (FULL) OF 'test' (Cost = 2 Card = 4 Bytes = 8)

Statistics
----------------------------------------------------------
0 recursive cballs
0 db block gets
7 consistent gets
0 physical reads -- no physical read occurred, and read it directly from the buffer cache.
0 redo size
373 bytes sent via SQL * Net to client
503 bytes encoded ed 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 re-read into the buffer cache, which occurs in

If new data needs to be read into the Buffer Cache, And the Buffer Cache does not have enough free spaceSpaceOracle will replace the LRU data in the LRU linked list based on the LRU algorithm. When the data is accessed again, it needs to be read from the disk again.

SQL> alter session set events 'immediate trace name flush_cache '; -- clear the Data Buffer

Session altered.

SQL> select * from test;

Execution Plan
----------------------------------------------------------
0 select statement ptimizer = CHOOSE (Cost = 2 Card = 4 Bytes = 8)
1 0 table access (FULL) OF 'test' (Cost = 2 Card = 4 Bytes = 8)

Statistics
----------------------------------------------------------
0 recursive cballs
0 db block gets
7 consistent gets
6 physical reads -- physical read reoccurs.
0 redo size
373 bytes sent via SQL * Net to client
503 bytes encoded ed via SQL * Net from client
2 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

2. Logical read)

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 ). Note: Logical IO only supports logical read and no logical write.

• 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 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) OF 'test' (Cost = 2 Card = 4 Bytes = 8)

Statistics
----------------------------------------------------------
0 recursive cballs
1 db block gets
14 consistent gets
0 physical reads
252 redo size
386 bytes sent via SQL * Net to client
503 bytes encoded ed via SQL * Net from client
2 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL>

• 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 before the modification is located in the rollback segment. Generally, common queries are consistent reads.

The following example helps you understand consistent reading:

Session 1:

SQL> select * from test;

ID
----------
1000

SQL> update test set id = 2000;

1 row updated.

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) OF 'test' (Cost = 2 Card = 4 Bytes = 8)

Statistics
----------------------------------------------------------
0 recursive cballs
0 db block gets
9 when no things are updated in consistent gets, 7 consistent gets indicates that two more consistent gets are to be obtained from the rollback segment.
0 physical reads
52 redo size
373 bytes sent via SQL * Net to client
503 bytes encoded ed via SQL * Net from client
2 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL>