当前位置: > 数据库 > Oracle >

Oracle的consistent gets是如何计算的

时间:2015-10-11 18:11来源:linux.it.net.cn 作者:IT
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发帖询问,未获答案)。
 
 
 
(责任编辑:IT)
------分隔线----------------------------