|
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发帖询问，未获答案）。 |