执行计划-4：谓词的选择时机与使用细节

数据库平台优化 2020-02-24

528

在第3部分（引用沃趣科技——执行计划-3：解释规则），我们用一个简单的hash join介绍了阅读执行计划最基本的规则---”子操作按先后顺序执行，递归向下“。这让我们了解查询中Oracle生成rowsource的顺序和访问中不同物理对象的顺序（隐式的）。

这个系列的开头，我就强调过规则不是适用于所有的场景，下一部分（第5部分）里我们会来看几个我们需要特别注意的场景。但是在这部分中，我们将继续使用更简单的例子，来了解应用规则时谓词的选择时机和使用的一些细节。

Basics

这里列出上个部分文章中两表hash join的执行计划:

--------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |    10 |   300 |    22   (0)| 00:00:01 |
|*  1 |  HASH JOIN                   |       |    10 |   300 |    22   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1    |    10 |   150 |    11   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | T1_I1 |    10 |       |     1   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID| T2    |    10 |   150 |    11   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN          | T2_I1 |    10 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T2"."ID"="T1"."ID")
   3 - access("T1"."N_1000"=1)
   5 - access("T2"."N_1000"=100)

通过"子操作按先后顺序执行"这个规则，我们可以知道运行引擎会从第3行的范围扫描索引t1_i1开始，之后第2行根据第3行获取到的rowid从t1表中选择行列，之后第1行根据第2行中的数据在内存里建立hash表；然后是第5行的范围扫描索引t1_i2，第4行根据第5行获取到的rowid从表t2中选择行列，最后在第1行中根据T2表中的行列去探测内存中的hash表，如果有匹配项则建立一个新的含有结果集的rowsource，最后传递给客户端程序。

通过上述过程，我们可以有以下描述：Oracle在处理Hash Join的两个子操作的方式上是不一样的。第二个子操作（访问T2）只有在第一个子操作（访问T1）完成后才能开始---hash join是一个"阻塞"操作的例子。只有内存里的hash表建立之后，Oracle才能调用第2个子操作每次返回一行去探测hash表，之后将匹配的行传递给父操作，从这个时刻开始就有了分段的数据流。

我偶尔也会看到这样的说法，因为hash join属于阻塞操作，所以当优化器处于first_rows(n)模式下时，不能进行hash join。这是不对的，如果优化器认为能很迅速的建立起hash表，并且从第2张表返回前N行的代价很低，那么hash join可能仍然会是返回前N列代价最低的路径。

我们可以通过hash join和使用hint让优化器使用merge join这两种不同执行计划，来比较阻塞的效果。这里是merge join的计划：

---------------------------------------------------------------------------------------
| Id  | Operation                     | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |       |    10 |   300 |    24   (9)| 00:00:01 |
|   1 |  MERGE JOIN                   |       |    10 |   300 |    24   (9)| 00:00:01 |
|   2 |   SORT JOIN                   |       |    10 |   150 |    12   (9)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T1    |    10 |   150 |    11   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | T1_I1 |    10 |       |     1   (0)| 00:00:01 |
|*  5 |   SORT JOIN                   |       |    10 |   150 |    12   (9)| 00:00:01 |
|   6 |    TABLE ACCESS BY INDEX ROWID| T2    |    10 |   150 |    11   (0)| 00:00:01 |
|*  7 |     INDEX RANGE SCAN          | T2_I1 |    10 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("T1"."N_1000"=1)
   5 - access("T2"."ID"="T1"."ID")
       filter("T2"."ID"="T1"."ID")
   7 - access("T2"."N_1000"=100)

这个计划里我们可以看到第1行的merge join操作有两个子操作，分别是第2行的sort join（第1个子操作）和第5行的sort join（第2个子操作）。运用"子操作按先后顺序执行"的规则，我们可以知道Oracle从范围扫描索引t1_i1开始，从t1中获取可能需要的数据并且在第2行中对它们进行排序（对id列进行排序，因为它们是连接列）。如果运气好的话，第1行中排序后的数据集会在内存中（在会话的PGA中）：第一个子操作是一个阻塞操作，所以在排序完成前我们不能调用第二个子操作。

之后开始调用第二个子操作，同样的规则，从范围扫描索引t2_i1开始，访问t2表，对结果集排序：第二个子操作仍然是一个阻塞操作，排序完成前，merge join操作自身并不会进行。

当准备好两个完成排序的rowsource时，merge join从第一个rowsource一次取一条，去探测第二个rowsource，如果匹配上就构造结果行并向上传递给父操作。由于对第二个rowsource进行了排序，所以Oracle查找每个匹配行的最坏情况是o(log(N))—其中N是第二个rowsource中的行数；Oracle使用二分法（使用 log2(N)检查）来找到第一个匹配的行，之后按照顺序从该行向下扫描。实际上代码会更灵活，因为探测的行也是从一个排好序的结果集得来的，所以可以通过"记住"上次探测开始的行的位置来降低工作量。

实际上，这种修改连接为merge join的方法也为我们提供了一个执行计划可能不是如展示的那样执行的例子，我们可以从"rowsource执行统计信息"中发现更多信息。现在我们开始执行查询并使用对dbms_xplan更复杂的调用来查看计划中调用不同步骤的次数。

alter session set statistics_level = all;
set linesize 156
set trimspool on
set pagesize 60
set serveroutput off


select
        /*+
                leading(t1, t2)
                use_merge(t2)
        */
        t1.v1, t2.v1
from
        t1, t2
where
        t1.n_1000 = 1
and     t2.id     = t1.id
and     t2.n_1000 = 100
;


select * from table(dbms_xplan.display_cursor(null,null,'iostats last'));


--------------------------------------------------------------------------
| Id  | Operation                     | Name  | Starts | E-Rows | A-Rows |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |       |      1 |        |      0 |
|   1 |  MERGE JOIN                   |       |      1 |     10 |      0 |
|   2 |   SORT JOIN                   |       |      1 |     10 |     10 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T1    |      1 |     10 |     10 |
|*  4 |     INDEX RANGE SCAN          | T1_I1 |      1 |     10 |     10 |
|*  5 |   SORT JOIN                   |       |     10 |     10 |      0 |
|   6 |    TABLE ACCESS BY INDEX ROWID| T2    |      1 |     10 |     10 |
|*  7 |     INDEX RANGE SCAN          | T2_I1 |      1 |     10 |     10 |
--------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("T1"."N_1000"=1)
   5 - access("T2"."ID"="T1"."ID")
       filter("T2"."ID"="T1"."ID")
   7 - access("T2"."N_1000"=100)输出中我们应该关注Starts列，第5行的starts列的值可能是会引起问题的一个小细节(第二个排序操作)。显然Oracle把第二个表中的数据排序了10次，但由于Oracle的开发人员是相当聪明的，所以我们有理由认为这不是真实发生的，我们需要对sort join操作有更好的解释，并更清楚的理解它是如何在执行计划中展示的。

第二个sort join操作其实包含两部分，一部分根据提供的值探测排序好的数据集，另一部分确实是对数据集进行排序。可能这个说明更适合这个操作，'探测内存中已经排序好的数据集，但如果数据集不在内存中，则获取并排序它'。执行计划中的行可能包含类似"如果满足条件X，则执行A，否则执行B"的高级逻辑，sort join具备做或者不做的能力，排序就是一个这样的例子。

第二行的A-rows的值是10（这个行数是从第一个子操作返回的），解释了为什么Oracle需要调用10次第二个子操作，就像是nested loop的操作一样，第一个子操作返回多少行就需要调用相应次数的第二个子操作。我们获取并排序整个数据集一次，之后重用排序后的数据，最终总共探测10次。

在这里我们可以对谓词信息的解释做一个初步介绍。第5行中同时使用了"access"和"filter"谓词，而且这两个谓词使用完全一样的表达式。

简单来说，这两种类型的谓词的区别在于，access谓词告诉我们怎么找到数据行，filter谓词告诉我们怎么在找到数据行后检查这些数据行是否是我们所需要的

以第二个sort join操作为例子，access谓词告诉我们怎么在排好序的数据集中找到第一条匹配的行，filter谓词告诉我们，当我们按照顺序遍历已排序的数据集时，如何检查每一行，以便在不符合filter表达式条件的行前停止。

平常存在这样一种情况，你认为你已经通过正确的索引正确的顺序访问所有的表，但是执行查询时似乎工作量还是很大，这种情况有可能时访问了很多的数据但使用filter谓词过滤了很大一部分。

如果我们有合适的索引，特别是在连接列上的索引，我们可以进一步研究merge join以及阻塞和计时的问题。这里有个例子（还是通过hint，因为优化器对于merge join的选择性不高），执行计划如下：

--------------------------------------------------------------------------
| Id  | Operation                     | Name  | Starts | E-Rows | A-Rows |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |       |      1 |        |      0 |
|   1 |  MERGE JOIN                   |       |      1 |     10 |      0 |
|*  2 |   TABLE ACCESS BY INDEX ROWID | T1    |      1 |     10 |     10 |
|   3 |    INDEX FULL SCAN            | T1_PK |      1 |  10000 |  10000 |
|*  4 |   SORT JOIN                   |       |     10 |     10 |      0 |
|*  5 |    TABLE ACCESS BY INDEX ROWID| T2    |      1 |     10 |     10 |
|   6 |     INDEX FULL SCAN           | T2_PK |      1 |  10000 |  10000 |
--------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("T1"."N_1000"=1)
   4 - access("T2"."ID"="T1"."ID")
       filter("T2"."ID"="T1"."ID")
   5 - filter("T2"."N_1000"=100)

例子中"PK"索引是基于id列，表示第3行和第6行中第index full scan操作访问数据的顺序正是我们所需要的排序的顺序，这也避免了排序操作。我们从计划中可以看到，Oracle并没有对第一个数据集进行排序，只是简单的按照顺序读取行，之后在第2行中使用filter谓词过滤了所有不需要的行。

比较第3行中A-rows和第2行中A-rows的值，我们可以看到先生成了包含10000行的rowsource，之后在根据filter谓词舍弃掉了大部分只留下10行数据，这显然是一个十分低效的行为。同样低效的行为也发生在第5行和第6行中，我们执行index full scan，返回10000个rowid，通过rowid访问表后根据filter舍弃了9990行。然而，关于表t2的处理最奇怪的事情是，我们在第4行中对产生的rowsource进行排序——尽管我们应该知道它已经按照merge join的正确排序顺序排序了。对于这种明显的冗余排序的解释是，它是一种将数据从缓冲区缓存中取出并放入私有工作区域的简便方法，这并不是为了将数据按正确的顺序重新排列。

该计划显示了阻塞操作可能出现的另一种情况。我们访问第一张表的操作并不是一个阻塞操作，只有访问第二张表的操作才是阻塞操作(排序)。这个计划的步骤如下:

1、在表t1上通过index full scan查找第一条符合连接条件的行。

2、在表t2上通过index full scan查找所有符合条件的行，把它们传输到私有工作区；探测工作区的第一条匹配行并将后续匹配行进行合并。

3、从t1中获取第二条匹配的行（遍历索引访问表，并过滤不需要的数据）。

4、探测私有工作区寻找匹配第二行的数据。

5、从第三步开始重复。

这篇文章的关键点是:时机很重要;执行计划中的操作并不总是准确地描述正在发生的事情;谓词部分对于理解优化正在执行的工作是一个重要的帮助，而rowsource（运行时）的统计信息对于查看实际发生的情况是一个非常大的帮助。

原文链接:

https://www.red-gate.com/simple-talk/sql/oracle/execution-plans-part-4-precision-and-timing/

原文作者:Jonathan Lewis

最后修改时间：2020-02-24 12:31:27

文章转载自数据库平台优化，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

执行计划-4：谓词的选择时机与使用细节

评论