oracle學習----邏輯讀

標籤：style   io   ar   color   os   使用   sp   for   on   

1.物理讀

當資料區塊第一次讀取到,就會緩衝到buffer cache 中,而第二次讀取和修改該資料區塊時就在記憶體buffer cache

清空資料緩衝區

SQL> alter session set events ‘immediate trace name flush_cache‘;

會話已更改。

SQL> select * from dept;

執行計畫
----------------------------------------------------------
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)

統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
8 consistent gets
6 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> select * from dept;

執行計畫
----------------------------------------------------------
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)

統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
8 consistent gets
0 physical reads 當第二次讀取資料時，是從db bufffer cache中讀取，不發生物理讀
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.1 當前讀

當前讀即讀取資料區塊當前的最新資料。任何時候在Buffer Cache中都只有一份當前資料區塊。當前讀通常發生在對資料進行修改、刪除操作時。這時，進程會給資料加上行級鎖，並且標識資料為“髒”資料。

SQL> select * from dept for update;使用for update添加行鎖

執行計畫
----------------------------------------------------------
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)

統計資訊
----------------------------------------------------------
4 recursive calls
4 db block gets 由於添加了行鎖，產生了當前讀 current read=db block gets
15 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;    提交操作，釋允許存取鎖，再來查看當前讀

提交完成。

SQL> select * from dept;

執行計畫
----------------------------------------------------------
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)

統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets 當前讀為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 一致讀

Oracle是一個多使用者系統。當一個會話開始讀取資料還未結束讀取之前，可能會有其他會話修改它將要讀取的資料。如果會話讀取到修改後的資料，就會造成資料的不一致。一致性讀就是為了保證資料的一致性。在Buffer Cache中的資料區塊上都會有最後一次修改資料區塊時的SCN。如果一個事務需要修改資料區塊中資料，會先在復原段中儲存一份修改前資料和SCN的資料區塊，然後再更新Buffer Cache中的資料區塊的資料及其SCN，並標識其為“髒”資料。當其他進程讀取資料區塊時，會先比較資料區塊上的SCN和自己的SCN。如果資料區塊上的SCN小於等於進程本身的SCN，則直接讀取資料區塊上的資料；如果資料區塊上的SCN大於進程本身的SCN，則會從復原段中找出修改前的資料區塊讀取資料。通常，普通查詢都是一致性讀。

 

SQL session1> select * from dept;

執行計畫
----------------------------------------------------------
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)

統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
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

開啟一個新的會話session2

SQL session2> select * from dept;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

SQL session2> update dept set loc=‘NEW YORKA‘ where deptno=10; 在session2中進行更新操作，但是不提價，這是再來查詢session1

已更新 1 行。

 

SQL session1> select * from dept;

執行計畫
----------------------------------------------------------
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)

統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
9 consistent gets 由於session2中的更新操作沒有提交，導致查詢資料是，需要構建CR塊，一致讀增長
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; 在session2中復原更新的資料，再在session1中查詢，觀察一致讀

回退已完成。

SQL session1> select * from dept;

執行計畫
----------------------------------------------------------
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)

統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
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

oracle學習----邏輯讀