Oracle的consistent gets是如何计算的
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发帖询问，未获答案）。
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发帖询问，未获答案）。
