Oracle Learning----Logical READING

1. Physical Reading

When the block is first read, it is cached in the buffer cache and the second time the data block is read and modified in memory buffer cache

Emptying the data buffer

Sql> alter session SET events ' immediate trace name Flush_cache ';

The session has changed.

Sql> select * FROM dept;

Execution plan
----------------------------------------------------------
Plan Hash value:3383998547

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 120 | 3 (0) | 00:00:01 |
| 1 | TABLE ACCESS full| DEPT | 4 | 120 | 3 (0) | 00:00:01 |
--------------------------------------------------------------------------

Note
-----
-Dynamic sampling used for this statement (level=2)

Statistical information
----------------------------------------------------------
0 Recursive calls
0 db Block gets
8 Consistent gets
6 Physical reads when the data is read for the first time, it is read from the data block and a physical read occurs
0 Redo Size
803 Bytes sent via sql*net to client
519 bytes received via sql*net from client
2 sql*net roundtrips To/from Client
0 Sorts (memory)
0 Sorts (disk)
4 rows processed

Sql> select * FROM dept;

Execution plan
----------------------------------------------------------
Plan Hash value:3383998547

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 120 | 3 (0) | 00:00:01 |
| 1 | TABLE ACCESS full| DEPT | 4 | 120 | 3 (0) | 00:00:01 |
--------------------------------------------------------------------------

Note
-----
-Dynamic sampling used for this statement (level=2)

Statistical information
----------------------------------------------------------
0 Recursive calls
0 db Block gets
8 Consistent gets
0 Physical reads when the data is read for the second time, it is read from the DB Bufffer cache, and no physical reads occur
0 Redo Size
803 Bytes sent via sql*net to client
519 bytes received via sql*net from client
2 sql*net roundtrips To/from Client
0 Sorts (memory)
0 Sorts (disk)
4 rows processed

2. Logical Reading

Logical reads are divided into current read and consistent read

2.1 Current Reading

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

Sql> SELECT * FROM dept for Update; Use for update to add row locks

Execution plan
----------------------------------------------------------
Plan Hash value:1522154571

----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 120 | 3 (0) | 00:00:01 |
| 1 | For UPDATE | | | | | |
| 2 | BUFFER SORT | | | | | |
| 3 | TABLE ACCESS full| DEPT | 4 | 120 | 3 (0) | 00:00:01 |
----------------------------------------------------------------------------

Note
-----
-Dynamic sampling used for this statement (level=2)

Statistical information
----------------------------------------------------------
4 Recursive calls
4 db block gets the current read read=db block because a row lock has been added
Consistent gets
0 physical Reads
884 Redo Size
941 Bytes sent via sql*net to client
519 bytes received via sql*net from client
2 sql*net roundtrips To/from Client
1 Sorts (memory)
0 Sorts (disk)
4 rows processed

Sql> commit; commit the operation, release the row lock, and then view the current read

Submit complete.

Sql> select * FROM dept;

Execution plan
----------------------------------------------------------
Plan Hash value:3383998547

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 120 | 3 (0) | 00:00:01 |
| 1 | TABLE ACCESS full| DEPT | 4 | 120 | 3 (0) | 00:00:01 |
--------------------------------------------------------------------------

Note
-----
-Dynamic sampling used for this statement (level=2)

Statistical information
----------------------------------------------------------
0 Recursive calls
0 db block gets current read as 0
8 Consistent gets
0 physical Reads
0 Redo Size
803 Bytes sent via sql*net to client
519 bytes received via sql*net from client
2 sql*net roundtrips To/from Client
0 Sorts (memory)
0 Sorts (disk)
4 rows processed

2.2 Consistent Reading

Oracle is a multi-user system. There may be other sessions that modify the data that it will read before a session begins reading the data before it finishes reading. If the session reads the modified data, it will result in inconsistent data. Consistent reading is to ensure consistency of data. The data block in buffer cache will have the SCN when the data block is last modified. If a transaction needs to modify data in the data block, a data block of the pre-modified data and SCN is saved in the rollback segment before updating the data and its SCN of the block in the buffer cache and identifying it as "dirty" data. When other processes read the data block, the SCN and its own SCN are compared on the data block first. If the SCN on the data block is less than the SCN of the process itself, the data on the block is read directly, and if the SCN on the data block is larger than the SCN of the process itself, the data block before the modification is fetched from the rollback segment. Generally, common queries are consistent reads.

SQL session1> SELECT * FROM dept;

Execution plan
----------------------------------------------------------
Plan Hash value:3383998547

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 120 | 3 (0) | 00:00:01 |
| 1 | TABLE ACCESS full| DEPT | 4 | 120 | 3 (0) | 00:00:01 |
--------------------------------------------------------------------------

Note
-----
-Dynamic sampling used for this statement (level=2)

Statistical information
----------------------------------------------------------
0 Recursive calls
0 db Block gets
8 consistent gets a consistent read
0 physical Reads
0 Redo Size
803 Bytes sent via sql*net to client
519 bytes received via sql*net from client
2 sql*net roundtrips To/from Client
0 Sorts (memory)
0 Sorts (disk)
4 rows processed

Open a new session Session2

SQL session2> SELECT * FROM dept;

DEPTNO dname LOC
---------- -------------- -------------
Ten ACCOUNTING NEW YORK
DALLAS
SALES CHICAGO
OPERATIONS BOSTON

SQL session2> Update Dept Set loc= ' NEW Yorka ' where deptno=10; Update operation in Session2, but do not raise price, this is again to query Session1

1 rows have been updated.

SQL session1> SELECT * FROM dept;

Execution plan
----------------------------------------------------------
Plan Hash value:3383998547

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 120 | 3 (0) | 00:00:01 |
| 1 | TABLE ACCESS full| DEPT | 4 | 120 | 3 (0) | 00:00:01 |
--------------------------------------------------------------------------

Note
-----
-Dynamic sampling used for this statement (level=2)

Statistical information
----------------------------------------------------------
0 Recursive calls
0 db Block gets
9 Consistent gets because the update operation in Session2 is not committed, resulting in query data is, need to build a CR block, consistent read growth
0 physical Reads
0 Redo Size
803 Bytes sent via sql*net to client
519 bytes received via sql*net from client
2 sql*net roundtrips To/from Client
0 Sorts (memory)
0 Sorts (disk)
4 rows processed

SQL session2> rollback; Roll back the updated data in the Session2, then query in the Session1, observe the consistent read

Fallback is complete.

SQL session1> SELECT * FROM dept;

Execution plan
----------------------------------------------------------
Plan Hash value:3383998547

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 120 | 3 (0) | 00:00:01 |
| 1 | TABLE ACCESS full| DEPT | 4 | 120 | 3 (0) | 00:00:01 |
--------------------------------------------------------------------------

Note
-----
-Dynamic sampling used for this statement (level=2)

Statistical information
----------------------------------------------------------
0 Recursive calls
0 db Block gets
8 Consistent gets consistent read return
0 physical Reads
0 Redo Size
803 Bytes sent via sql*net to client
519 bytes received via sql*net from client
2 sql*net roundtrips To/from Client
0 Sorts (memory)
0 Sorts (disk)
4 rows processed

Oracle Learning----Logical READING