在第8小节中,我们举了一个很简单的hash join的例子,但是它的每个操作仅仅执行一次,很难让我们对Oracle的预测有一个全面的认识。我们需要一些比较复杂的执行计划,比如父操作会调用许多次子操作,这也是我们在第9与第10部分所做的实验。


Getting started


由于基于成本查询变化的不断进化,很难找到一个简单的执行计划示例来真正显示它“确实应该”显示的信息,所以我会展示一些数字显示不正确的执行计划,并且解释这些反常的现象。这个小节我们来看一个简单的嵌套循环连接:


select  t1.id, t2.id
 from  t2, t1
 where  t2.n1 = 15  and t1.n1 = t2.n2
; ---------------------------------------------------------------------------------------  | Id  | Operation                     | Name  | Rows  | Bytes | Cost (%CPU)| Time     | ---------------------------------------------------------------------------------------  |   0 | SELECT STATEMENT              |       |   225 |  4500 |    46   (0)| 00:00:01 |
 |   1 |  NESTED LOOPS                 |       |   225 |  4500 |    46   (0)| 00:00:01 |
 |   2 |   NESTED LOOPS                |       |   225 |  4500 |    46   (0)| 00:00:01 |
 |   3 |    TABLE ACCESS BY INDEX ROWID| T2    |    15 |   180 |    16   (0)| 00:00:01 |
 |*  4 |     INDEX RANGE SCAN          | T2_I1 |    15 |        |     1   (0)| 00:00:01 |
 |*  5 |    INDEX RANGE SCAN           | T1_I1 |    15 |       |     1   (0)| 00:00:01 |
 |   6 |   TABLE ACCESS BY INDEX ROWID | T1    |    15 |   120 |     2   (0)| 00:00:01 | ---------------------------------------------------------------------------------------  Predicate Information (identified by operation id): ---------------------------------------------------    4 - access("T2"."N1"=15)
   5 - access("T1"."N1"="T2"."N2")

这个执行计划展示了一个双重嵌套的操作。操作的顺序是4352,6,1,0:对索引T2_I1做索引范围扫描确定T2中的行,对获取的T2中的每一行都对T1_I1做索引范围扫描,之后我们根据T1_I1中获取到的每个rowid去访问T1的行。执行计划预测的行总数本质上是对的—-我们的数据集从T2里提供15行,每个行会连接T1中的15行,所以总数是225.但是这些数据实际告诉了我们什么?

接下来我会忽略Bytes列,我们已经知道它是怎么来的,而且它的准确性很差。同样也会忽略Time列,因为我们知道他是从Cost列衍生出来的,通过Cost乘以sys.aux_stats$中的sreadtim得到的。

第4行和第3行告诉我们我们获取了15个rowid,之后获取15行,两个操作的成本分别为1和16—一个叶块的物理读,之后是15个表块的访问加上一个叶块读所以总成本是16。

第5行我们对T1_I1做索引范围扫描,找到15行的成本为1.这些数据是一次的预测,但是当查询执行时,优化器不希望第5行仅仅执行一次。


Hitting Problems


优化器期望总共做15次索引范围扫描,这也是为什么第2行中嵌套循环告诉我们会生成225行的原因,但是成本46是从哪里来的?答案也很简单:这是个bug。第2行的成本应该是31,计算方法为16(执行一次第3行)+1(执行一次第5行)*15(由于第3行生成15行,需要执行15次)。

暂时不看为什么是46而不是31这个问题,我们先看第6行,它展示的数字完全是错误的。对于第2行中生成的225行,每一行都需要执行一次第6行找到表T1中的行(并不是预测的15行),而且通过rowid单次执行表访问的适合的成本是1(而不是预期的2)。

这里的数据之所以不正确是有原因的:它们是为了反映优化器在Oracle 8.1及更早版本中使用的执行策略而设计的,而优化器在Oracle 8.1及更早版本中使用的执行策略应该是这样的:

 

--------------------------------------------------------------------------------

 | Operation                 |  Name    |  Rows | Bytes|  Cost  | Pstart| Pstop | --------------------------------------------------------------------------------

 | SELECT STATEMENT          |          |   225 |    4K|     35 |       |       |  |  NESTED LOOPS             |          |   225 |    4K|     35 |       |       |  |   TABLE ACCESS BY INDEX RO|T2        |    15 |  180 |      5 |       |       |  |    INDEX RANGE SCAN       |T2_I1     |    15 |      |      1 |       |       |  |   TABLE ACCESS BY INDEX RO|T1        |     3K|   23K|      2 |       |       |  |    INDEX RANGE SCAN       |T1_I1     |     3K|      |      1 |       |       | --------------------------------------------------------------------------------

在这个例子中,访问T1和它的索引的Rows值是完全错误的,但是这个执行计划也告诉了我们,”T2中返回的每行,我们都会通过索引范围扫描来获取T1中的每行(15,而不是3000)”,索引范围扫描的成本是1(1个叶块),访问表的成本是2(需要加上子操作索引扫描),所以总的嵌套循环的成本是:5(访问表2)+2(访问表1)*15(预测访问表1次数)=35。

因此,我们最初计划的第6行是报告8i中相应的操作的行数的,即使执行计划和操作原理不一样了。技术上来说,我认为第6行应该是这样的:

 

---------------------------------------------------------------------------------------

 | Id  | Operation                     | Name  | Rows  | Bytes | Cost (%CPU)| Time     | ---------------------------------------------------------------------------------------

 |   6 |   TABLE ACCESS BY INDEX ROWID | T1    |     1 |     8 |     1   (0)| 00:00:01 | ---------------------------------------------------------------------------------------

每次我们执行,都会访问一个数据块并且获取一行数据。但是,这行会被执行225次,意味着会产生大量的成本。

我们陷入了一个陷阱,一方面优化器使用传统的算法来确定资源需求,另一方面执行计划显示了执行引擎实际采取的策略。可以说,我们可以在Oracle展示的方案和下面的方案之间进行选择,从某些方面来说,下面的方案更真实一些。

 

---------------------------------------------------------------------------------------

 | Id  | Operation                     | Name  | Rows  | Bytes | Cost (%CPU)| Time     | ---------------------------------------------------------------------------------------

 |   0 | SELECT STATEMENT              |       |   225 |  4500 |    46   (0)| 00:00:01 |  |   1 |  NESTED LOOPS                 |       |   225 |  4500 |    46   (0)| 00:00:01 |  |   2 |   NESTED LOOPS                |       |   225 |  2700 |    31   (0)| 00:00:01 |  |   3 |    TABLE ACCESS BY INDEX ROWID| T2    |    15 |   180 |    16   (0)| 00:00:01 |  |*  4 |     INDEX RANGE SCAN          | T2_I1 |    15 |       |     1   (0)| 00:00:01 |  |*  5 |    INDEX RANGE SCAN           | T1_I1 |    15 |       |     1   (0)| 00:00:01 |  |   6 |   TABLE ACCESS BY INDEX ROWID | T1    |     1 |     8 |     1   (0)| 00:00:01 | ---------------------------------------------------------------------------------------

这个版本的执行计划,我调整了第2行的cost和bytes,第6行的cost和bytes。没有调整的是第1行的cost,即使我们可以通过计算得出cost应该是256(31+225*1)。问题是第6行中成本为1是不确定的。因为T1中的数据有可能很好的被聚集了,我们不需要每次执行时都去读取数据块,我们可能只需要在第一次读取数据块,之后的14次可以从内存里读取,所以平均的成本应该是1/15.(注意:事实上,优化器的确会将算术运算精确到小数点后几位,但会将执行计划中的报告数字四舍五入;这就是为什么有时你会看到一些数字,比如2 * 3 = 5,但在内部它可能是1.8 * 2.6 = 4.68)。

就像这个例子看到的,存在一些与执行计划相关的问题。当你试图创建一些简单的规则来理解这些数字时,你会发现一些异常。有时候基本策略“子操作累加至父操作”说不通时,尝试一些灵活的变化,可以让你更好的重新解释这些数字。




沃趣科技,让客户用上更好的数据库技术!