Shulou(Shulou.com)05/31 Report--

This article shows you how to understand Oracle logical reading and physical reading, the content is concise and easy to understand, absolutely can make your eyes shine, through the detailed introduction of this article I hope you can gain something.

1. physical read

Physical read is the process of reading data from disk to buffer catch. Usually, if the data is not found in the buffer catch when it is needed, oracle will perform a physical read.

The first time a block is read, it is cached in the buffer cache, and the second time it is read and modified, it is cached in the memory buffer cache.

1.1*** Number of readings:

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

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

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

Statistics

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

175 recursive calls

0 db block gets

24 consistent gets

9 physical reads -9 physical reads

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

2 sorts (memory)

0 sorts (disk)

1 rows processed

1.2 the second reading

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 calls

0 db block gets

7 consistent gets

0 physical reads --No physical reads 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 reread into buffer cache , which happens when

If new data needs to be read into Buffer Cache, and Buffer Cache does not have enough free space, Oracle replaces the LRU data in the LRU list according to the LRU algorithm. When this data is accessed again, it needs to be reread from disk.

SQL> alter session set events 'immediate trace name flush_cache';--Empty 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 calls

0 db block gets

7 consistent gets

6 physical reads --physical reads happen again

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 read (buffer read)

A logical read refers to reading blocks of data from (or viewing from) the Buffer Cache. According to the different modes of accessing data blocks, it can be divided into current read and consistent read. Note: Logic IO has only logical reads, no logical writes.

instant reading

Read immediately reads the current *** data of the data block. There is only one copy of the current chunk in Buffer Cache at any time. Instant reads usually occur when data is modified or deleted. At this point, the process places a row-level lock on the data and identifies the data as "dirty."

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 calls

1 db block gets

14 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>

consistent reading

Oracle is a multi-user system. Before a session starts reading data and finishes reading, another session may modify the data it is about to read. If the session reads the modified data, it will cause data inconsistency. Consistent reading is to ensure consistency of data. Each block in Buffer Cache has an SCN *** when a block is modified. If a transaction needs to modify data in a data block, it will first save a copy of the data block before modification and SCN in the rollback segment, and then update the data block and SCN in Buffer Cache and identify it as "dirty" data. When another process reads a data block, it compares the SCN on the data block with its own SCN. If the SCN on the data block is less than or equal to the SCN of the process itself, the data on the data block is read directly; if the SCN on the data block is greater than the SCN of the process itself, the data block before modification is found in the rollback segment to read the data. Usually, ordinary queries are read consistently.

Here is an example to help you understand consistency reading:

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

Statistics

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

0 recursive calls

0 db block gets

9 consistent gets 7 consistent gets when nothing is updated 2 more consistent gets are to be retrieved from the rollback segment

0 physical reads

52 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>

This is how to understand Oracle logical reading and physical reading. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserves, please pay attention to the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.