【基礎篇一致性讀】一致性讀分析
對執行計劃和相關的一致性讀簡單分析一下。
通常，我們使用set autotrace on來看執行計劃和執行的統計資訊。按照前面一片文章的例子示意：
SQL> select count(*) from t_test_notequ1 a where a.object_id =2;
COUNT(*)
----------
59622
執行計劃
----------------------------------------------------------
Plan hash value: 2434651730
--------------------------------------------------------------------------------
----------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
--------------------------------------------------------------------------------
----------
| 0 | SELECT STATEMENT | | 1 | 2 | 78 (3)|
00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 2 | |
|
|* 2 | INDEX FAST FULL SCAN| IDX_TEST_NOTEQU1 | 59323 | 115K| 78 (3)|
00:00:01 |
--------------------------------------------------------------------------------
----------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("A"."OBJECT_ID"=2)
統計資訊
----------------------------------------------------------
96 recursive calls
0 db block gets
359 consistent gets
0 physical reads
0 redo size
410 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
1 rows processed
這裡只關注上面的黑體部分即統計資訊，先來看看recursive calls。
我們知道在很多情況下會產生recursive calls（遞迴呼叫），比如做一些操作的分配空間，執行ddl，或者觸發trigger，或者在解析的時候資料字典快取需要一些物件的資訊。這裡只看select的情況，因此本例中可以認為主要是因為需要資料字典資訊，解析和分配空間導致了遞迴呼叫的產生。
Db block gets
Oracle文件這麼給出來的：
Number of times a CURRENT block was requested.
這裡主要只看query mode的block，也就是在select過程中block並沒有被其他session修改。
consistent gets：Number of times a consistent read was requested for a block.
Oracle利用undo來實現了一致性讀，保證從查詢時間點開始，所有讀取的block都是時間一致的，如果這段時間內block變化了則要前映象這個block，然後再讀取資料。
可以認為一致性讀就是從databuffer中讀取的次數，後面詳細給出計算方法。
physical reads
很好理解，從磁碟或者io快取中讀取的次數
redo size
產生的redo數量。產生redo log情況有很多種，這個例子中只看select產生的情況，這種情況很多時候是oracle的延遲塊清除，僅僅提一下，後面有一個實驗可以具體看到現象。
先主要只看上面的幾個。
SQL> alter system flush shared_pool;
系統已更改。
SQL> alter system flush buffer_cache;
系統已更改。
先從全表掃描來看：
SQL> select count(*) from t_test_notequ1 a;
COUNT(*)
----------
59623
執行計劃
----------------------------------------------------------
Plan hash value: 360092435
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 183 (2)| 00:00:03 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T_TEST_NOTEQU1 | 59623 | 183 (2)| 00:00:03 |
-----------------------------------------------------------------------------
統計資訊
----------------------------------------------------------
171 recursive calls
0 db block gets
845 consistent gets
823 physical reads
0 redo size
410 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
多執行幾次，讓recursive calls和physical reads都變為零，即完全從記憶體中讀取。
SQL> select count(*) from t_test_notequ1 a;
執行計劃
----------------------------------------------------------
Plan hash value: 360092435
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 183 (2)| 00:00:03 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T_TEST_NOTEQU1 | 59623 | 183 (2)| 00:00:03 |
-----------------------------------------------------------------------------
統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
821 consistent gets
0 physical reads
0 redo size
410 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
可以看到至少需要821個邏輯讀，或者821個讀block操作。
對於全表掃描一個表獲取全部行產生的一致性讀的一個計算公式：
consistent gets =numrows/arraysize + blocks
其中numrows為行數，blocks是塊數
SQL> select a.blocks,a.num_rows,a.empty_blocks
2 from dba_tables a where a.table_name = upper ('t_test_notequ1');
BLOCKS NUM_ROWS EMPTY_BLOCKS
---------- ---------- ------------
816 59623 79
SQL> show arraysize
arraysize 15
則有：
SQL> select 59623/15+821 from dual;
59623/15+821
------------
4795.8666666
下面不用count(*)，以免輸出資訊太多，trace模式設定為traceonly。
SQL> select * from t_test_notequ1 a;
已選擇59623行。
執行計劃
----------------------------------------------------------
Plan hash value: 1605230170
--------------------------------------------------------------------------------
----
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
----
| 0 | SELECT STATEMENT | | 59623 | 4890K| 185 (3)| 00:00:
03 |
| 1 | TABLE ACCESS FULL| T_TEST_NOTEQU1 | 59623 | 4890K| 185 (3)| 00:00:
03 |
--------------------------------------------------------------------------------
----
統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
4751 consistent gets
0 physical reads
0 redo size
6276062 bytes sent via SQL*Net to client
44099 bytes received via SQL*Net from client
3976 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
59623 rows processed
SQL>
可以看出這個數字基本很接近了。
現在在另外一個session進行一個update操作，但並不提交：
SQL> update t_test_notequ1 set object_id = 2 where rownum = 1;
已更新 1 行。
多執行幾次使得recursive calls將為0，看到一致性讀增加了1。
SQL> select * from t_test_notequ1 a;
已選擇59623行。
執行計劃
----------------------------------------------------------
Plan hash value: 1605230170
--------------------------------------------------------------------------------
----
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
----
| 0 | SELECT STATEMENT | | 59623 | 4890K| 185 (3)| 00:00:
03 |
| 1 | TABLE ACCESS FULL| T_TEST_NOTEQU1 | 59623 | 4890K| 185 (3)| 00:00:
03 |
--------------------------------------------------------------------------------
----
統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
4752 consistent gets
0 physical reads
0 redo size
6276062 bytes sent via SQL*Net to client
44099 bytes received via SQL*Net from client
3976 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
59623 rows processed
SQL>
而將update操作commit之後，又成了4751：
SQL> select * from t_test_notequ1 a;
已選擇59623行。
執行計劃
----------------------------------------------------------
Plan hash value: 1605230170
--------------------------------------------------------------------------------
----
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
----
| 0 | SELECT STATEMENT | | 59623 | 4890K| 185 (3)| 00:00:
03 |
| 1 | TABLE ACCESS FULL| T_TEST_NOTEQU1 | 59623 | 4890K| 185 (3)| 00:00:
03 |
--------------------------------------------------------------------------------
----
統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
4751 consistent gets
0 physical reads
0 redo size
6276062 bytes sent via SQL*Net to client
44099 bytes received via SQL*Net from client
3976 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
59623 rows processed
SQL>
再來看看修改sqlplus的arraysize之後的情況：
SQL> select * from t_test_notequ1 a;
已選擇59623行。
執行計劃
----------------------------------------------------------
Plan hash value: 1605230170
--------------------------------------------------------------------------------
----
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
----
| 0 | SELECT STATEMENT | | 59623 | 4890K| 185 (3)| 00:00:
03 |
| 1 | TABLE ACCESS FULL| T_TEST_NOTEQU1 | 59623 | 4890K| 185 (3)| 00:00:
03 |
--------------------------------------------------------------------------------
----
統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
880 consistent gets
0 physical reads
0 redo size
5767112 bytes sent via SQL*Net to client
1034 bytes received via SQL*Net from client
61 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
59623 rows processed
SQL>
而：
SQL> select 59623/1000+821 from dual;
59623/1000+821
--------------
880.623
進一步說明了這個計算公式大致符合。
再來看看使用index的情況：
SQL> select * from t_test_notequ1 a where a.object_id = 1;
未選定行
執行計劃
----------------------------------------------------------
Plan hash value: 3227361085
--------------------------------------------------------------------------------
----------------
| Id | Operation | Name | Rows | Bytes | Cost (%
CPU)| Time |
--------------------------------------------------------------------------------
----------------
| 0 | SELECT STATEMENT | | 1 | 84 | 2
(0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_TEST_NOTEQU1 | 1 | 84 | 2
(0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TEST_NOTEQU1 | 1 | | 1
(0)| 00:00:01 |
--------------------------------------------------------------------------------
----------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."OBJECT_ID"=1)
統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
992 bytes sent via SQL*Net to client
374 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
SQL>
這裡的2個一致性讀也很好理解，btree高度為1 ，先找branch block 然後找leaf block兩次均未找到值，直接返回，2個邏輯讀。
修改一下查詢object_id=2的所有行：
SQL> select /*+ index(a)*/* from t_test_notequ1 a where a.object_id > 1;
已選擇59622行。
執行計劃
----------------------------------------------------------
Plan hash value: 3227361085
--------------------------------------------------------------------------------
----------------
| Id | Operation | Name | Rows | Bytes | Cost (%
CPU)| Time |
--------------------------------------------------------------------------------
----------------
| 0 | SELECT STATEMENT | | 59622 | 4890K| 1162
(1)| 00:00:14 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_TEST_NOTEQU1 | 59622 | 4890K| 1162
(1)| 00:00:14 |
|* 2 | INDEX RANGE SCAN | IDX_TEST_NOTEQU1 | 59622 | | 341
(1)| 00:00:05 |
--------------------------------------------------------------------------------
----------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."OBJECT_ID">1)
統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
1273 consistent gets
0 physical reads
0 redo size
5767021 bytes sent via SQL*Net to client
1034 bytes received via SQL*Net from client
61 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
59622 rows processed
SQL>
前面的操作中將一條資料的object_id更新為了null，而null值不被索引，從下面獲得索引的高度和block數：
SQL> select a.blevel,a.leaf_blocks,a.num_rows
2 from dba_indexes a where a.index_name = upper ('idx_test_notequ1');
BLEVEL LEAF_BLOCKS NUM_ROWS
---------- ----------- ----------
1 338 59622
SQL> select a.blocks from dba_segments a
2 where a.segment_name = upper ('idx_test_notequ1');
BLOCKS
----------
384
套用一下前面提到的公式：
SQL> select (59622/1000+384)*2 + 384 from dual;
(59622/1000+384)*2+384
----------------------
1271.244
這裡的計算先做一個推斷，btree高度為1，則每找到一個rowid先訪問branch block找到leafblock，再從leaf block中找到rowid，資料塊都已經在快取中了，直接讀取這些塊。
修改一下arraysize，很容易驗證這個推斷：
SQL> set arraysize 100;
SQL> select /*+ index(a)*/* from t_test_notequ1 a where a.object_id > 1;
已選擇59622行。
執行計劃
----------------------------------------------------------
Plan hash value: 3227361085
--------------------------------------------------------------------------------
----------------
| Id | Operation | Name | Rows | Bytes | Cost (%
CPU)| Time |
--------------------------------------------------------------------------------
----------------
| 0 | SELECT STATEMENT | | 59622 | 4890K| 1162
(1)| 00:00:14 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_TEST_NOTEQU1 | 59622 | 4890K| 1162
(1)| 00:00:14 |
|* 2 | INDEX RANGE SCAN | IDX_TEST_NOTEQU1 | 59622 | | 341
(1)| 00:00:05 |
--------------------------------------------------------------------------------
----------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."OBJECT_ID">1)
統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
2339 consistent gets
0 physical reads
0 redo size
5836831 bytes sent via SQL*Net to client
6941 bytes received via SQL*Net from client
598 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
59622 rows processed
SQL>
而：
SQL> select (59622/100+384)*2 + 384 from dual;
(59622/100+384)*2+384
---------------------
2344.44
當然這些測試僅僅在單例項，單個session，只對select來分析的，實際上加上了其他情況要複雜一些，但也還是有規律可循的。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16179598/viewspace-668279/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【基礎篇一致性讀】一致性讀分析（三）
- 【基礎篇一致性讀】一致性讀分析（續）
- 由讀一致性分析undo
- mysql一致性讀MySql
- redis基礎篇——資料一致性Redis
- 【MySQL】淺談一致性讀MySql
- 【一致性讀的計算】
- zt-當前模式讀與一致性讀模式
- Oracle一致性讀（consistents gets）Oracle
- ORACLE 物理讀 邏輯讀 一致性讀 當前模式讀總結淺析Oracle模式
- 讀寫一致性的一些思考
- 如何實現資料庫讀一致性資料庫
- Oracle一致性讀（Consistent Read）的原理Oracle
- MySQL e二級索引上的一致性讀MySql索引
- consistent read（讀一致性）的通俗理解
- zt_oracle一致性讀consistent readOracle
- MySQL探祕(六):InnoDB一致性非鎖定讀MySql
- consistent read（讀一致性）的通俗理解（轉）
- 分散式系統基礎－一致性雜湊分散式
- （一）基礎篇：速讀Java執行緒池Java執行緒
- .NET Core基礎篇之：配置檔案讀取
- 構造一致性CR塊讀，ORACLE需要做的工作：Oracle
- oracle中同一事務內的一致性讀Oracle
- 解讀爬蟲中HTTP的祕密（基礎篇）爬蟲HTTP
- 提升程式碼的可讀性系列(一)–基礎篇
- 面試必問：讀寫一致性，你需要思考的問題面試
- MySQL半一致性讀原理解析-從原始碼角度解析MySql原始碼
- 從MongoDBReplicaSetHA看分散式系統讀寫一致性問題MongoDB分散式
- 轉:Oracle資料庫一致性讀的原理(Consistent Read)Oracle資料庫
- 參考oracle官方文件關於髒讀、一致性讀、undo中已提交資料塊的理解Oracle
- 深度剖析 | 關於資料鎖定和讀取一致性問題
- MySQL事務（二）事務隔離的實現原理：一致性讀MySql
- 解讀MySQL雙主複製的主備資料一致性GPMySql
- C語言讀取寫入CSV檔案 [一]基礎篇C語言
- 《Django基礎教程》讀後感Django
- 《Hadoop基礎教程》 試讀Hadoop
- 框架篇：分散式一致性解決方案框架分散式
- 資料庫大牛李海翔詳解全域性讀一致性技術資料庫