Oracle的consistent gets是如何計算的
來源：互聯網
上載者：User
Oracle的consistent gets是如何計算的呢？
1.單表掃描
SQL> execute show_space('TEST1','WWJ');
Unformatted Blocks ..................... 0
FS1 Blocks (0-25) ..................... 0
FS2 Blocks (25-50) ..................... 1
FS3 Blocks (50-75) ..................... 0
FS4 Blocks (75-100)..................... 0
Full Blocks ..................... 33
Total Blocks............................ 40
Total Bytes............................. 327,680
Total MBytes............................ 0
Unused Blocks........................... 1
Unused Bytes............................ 8,192
Last Used Ext FileId.................... 5
Last Used Ext BlockId................... 3,321
Last Used Block......................... 7
PL/SQL 過程已成功完成。
多次運行，獲得穩定的consistent gets
SQL> select * from test1;
已選擇12402行。
執行計畫
----------------------------------------------------------
Plan hash value: 4122059633
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12402 | 169K| 9 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| TEST1 | 12402 | 169K| 9 (0)| 00:00:01 |
---------------------------------------------------------------------------
統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
861 consistent gets
0 physical reads
0 redo size
379486 bytes sent via SQL*Net to client
9471 bytes received via SQL*Net from client
828 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
12402 rows processed
資料究竟分布在多少個塊上？
SQL> SELECT count(DISTINCT dbms_rowid.rowid_block_number(ROWID)) FROM test1;
COUNT(DISTINCTDBMS_ROWID.ROWID
------------------------------
34
也可以用show_space的結果，Full Blocks + FS2 Blocks (25-50) =34
公式：consistent gets = rownum / fetch array size + used datablock
861 = CEIL(12402 / 15) + 34
2.Hash join,TEST11表結構和TETS1一致，資料量，資料區塊都一致。
SQL> SELECT * FROM TEST1 A
2 WHERE NOT EXISTS(SELECT b.c1
3 FROM test11 b
4 WHERE b.c1 = a.c1)
5 /
已選擇8671行。
執行計畫
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 35 | 665 | 20 (10)| 00:00:01 |
|* 1 | HASH JOIN RIGHT ANTI| | 35 | 665 | 20 (10)| 00:00:01 |
| 2 | TABLE ACCESS FULL | TEST11 | 12404 | 62020 | 9 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | TEST1 | 12402 | 169K| 9 (0)| 00:00:01 |
-------------------------------------------------------------------------------
統計資訊
----------------------------------------------------------
650 consistent gets
consistent gets = rownum / fetch array size + probe table's block
+ build table's block + 其它
probe表為test1，sql將請求他所有的塊，所有產生34次一致讀
build表為test11，進程將它讀取到hash地區，產生34次一致讀
650 = CEIL (8671/15)+34+34+4
其它也許是oracle在請求hash記憶體產生的內部一致讀，可以忽略。
3.FILTER操作。這個sql的結果和2一樣，但很低效。從對產生一致讀的分析，我們還可以瞭解為什麼filter在這裡很低效。但是關於filter的機制，我找了幾天也沒有在任何文檔看到，這裡僅僅是
我自己的設想。
SQL> SELECT * FROM test1 a WHERE a.c1 NOT IN (SELECT b.c1 FROM test11 b);
已選擇8671行。
執行計畫
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12401 | 169K| 58720 (5)| 00:11:45 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL| TEST1 | 12402 | 169K| 9 (0)| 00:00:01 |
|* 3 |
TABLE ACCESS FULL| TEST11 | 1 | 5 | 9 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM "TEST11" "B" WHERE
LNNVL("B"."C1"<>:B1)))
3 - filter(LNNVL("B"."C1"<>:B1))
PS：這裡可以看出optimizer已經把sql改寫為NOT EXISTS了，但是卻是LNNVL("B"."C1"<>:B1)串連，已經很接近產生hash的sql。
統計資訊
----------------------------------------------------------
380719 consistent gets
首先，我認為oracle會把TEST1的所有所有資料，裝載進一個list，這裡產生34個一致讀。然後
依次讀取這個list中的每一個值，再根據這個值對TEST11進行過濾。
FILTER過濾是怎麼做的呢？最初我曾經以為oracle會把兩個表做descartes，然後再做<>過濾。
但和實際descartes比，成本相差太大。所以應該不是descartes。那是什麼呢？
從這裡觀察：
TABLE ACCESS FULL| TEST11 | 1 |
這個1 是ROWS，表示這裡的operation只包含一行。
再看：
1 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM "TEST11" "B" WHERE
LNNVL("B"."C1"<>:B1)))
3 - filter(LNNVL("B"."C1"<>:B1))
LNNVL是oracle的函數，意思是IS NULL OR NOT TRUE，這樣我認為過程是這樣的，綁定變數:B1實際
是list的一個取值。首先判斷在TEST11裡面是否存在空值比較或者C1=:B1的，如果發現了一行滿足條件那麼NOT EXISTS條件就不成立。
java虛擬碼如下：
List list = new Linklist();//建立鏈表
list = Test1Dao.listC1();
//迴圈鏈表
while(list.size >0){
String c1 = list.remove(0);
//在test11中尋找
if(Test11Dao.find(c1)){
//找到了，不滿足條件
}else{
//告訴client沒有找到，滿足，返回結果
}
}
這樣list的每一條記錄都會請求test11的塊，但是它的機制是發現一行即返回，所以每次他可能會掃描1～34塊，產生相應數量的一致讀。
那麼我來類比計算過程，通過如下SQL：
WITH t AS (
SELECT c1,dense_rank() over(ORDER BY dbms_rowid.rowid_block_number(ROWID)) bn FROM test11)
SELECT test1.c1,nvl(t.bn,34),SUM(nvl(t.bn,34)) over() FROM test1
LEFT JOIN t ON t.c1= test1.c1
結果節選如下：
rownum c1 bn sum(bn)
1 58153 1 367199
2 55746 1 367199
3 17523 1 367199
...
550 48108 5 367199
551 22515 5 367199
552 66287 5 367199
...
4162 7724 34 367199
4163 28976 34 367199
4164 6128 34 367199
4165 39064 null 367199
4166 8441 null 367199
4167 51435 null 367199
首先，我假設oracle在做filter時候，選擇資料區塊的順序是和client獲得資料的順序是差不多的。
SELECT c1,dense_rank() over(ORDER BY dbms_rowid.rowid_block_number(ROWID)) bn FROM test11
獲得的，是test11的資料，和每條資料按資料區塊位置的排序。
然後和test1做串連，這樣bn的結果就是test1中每條記錄，要做多少次塊請求，才能獲得結果。如果bn為null
說明在test11中沒有test1需要的值，則需要掃描所有34塊才能得知。
這樣sum(sn)就統計出所有請求產生的一致讀。
那麼380719 ≈367199 + CEIL(8671/15)+34 +34 + 其它+誤差
實際已經比較接近了。如果有人知道真相，請告訴我。
4.SQL> select * from test1 where 1=2
不會產生一致讀，oracle最佳化器會立刻知道這是一個否條件。
0 consistent gets
5.SQL> select * from test1 where c1<0;
37 consistent gets
除了資料存在的34個塊，還有3個extent header塊。
...
對一致讀的計算，暫時就到這裡，以後會對更多的最佳化器行為，做一致讀演算法描述。
實際本文是我在思考filter的機制的過程，所記錄的（我曾在itpub發帖詢問，未獲答案）。
