执行计划-3：解释规则

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

778

在这系列的前两个部分中，我们列举了一些生成或者找回执行计划的方法，也学习了一些额外的步骤可以帮助我们模拟真实的环境来判断执行计划是否存在问题。这部分我们会熟悉整体解释执行计划的基本准则。在第四部分前，我们不用考虑使用谓词的部分。

The Shape of a plan

我们会从一个简单的例子开始-创建几张表，连接它们，在我们检查执行计划的时候需要回答提出的问题。这里是数据生成的脚本：

    create table t1
as
with generator as (
    select  --+ materialize
        rownum id 
    from dual 
    connect by 
        level <= 1e4
)
select
    rownum          id,
    mod(rownum,1000)    n_1000,
    lpad(rownum,6,'0')  v1,
    rpad('x',100,'x')   padding
from
    generator
;

alter table t1 add constraint t1_pk primary key(id);
create index t1_i1 on t1(n_1000);

begin
    dbms_stats.gather_table_stats(
        ownname      => user,
        tabname      =>'T1',
        method_opt   => 'for all columns size 1'
    );
end;
/

我按照同样的脚本新建了一张t2表，这里就不重复了。接下来我会使用explain plan解释一条简单的SQL语句来介绍执行计划中的要点。

explain plan for
select
       t1.v1,t2.v1
from
       t1,t2
where
       t1.n_1000= 1
andt2.id     = t1.id
andt2.n_1000= 100
;

select * from table(dbms_xplan.display);

select
       id,parent_id, position,
       depth,level ? 1 old_depth,
       rpad('',level - 1) ||
              operation|| ' ' ||
              lower(options)|| ' ' ||
              object_name               text_line
from
       plan_table
start with
       id= 0
connect by
       parent_id= prior id
order siblings by
       id,position
;

我用两种不同的方法生成执行计划---一种是对dbms_xplan.display()的调用，另一种是在9i或者更早版本种对于plan table的简化查询。

以下是两种方法生成的结果:

--------------------------------------------------------------------------------------
| 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 byoperation id):
---------------------------------------------------
   1- access("T2"."ID"="T1"."ID")
   3- access("T1"."N_1000"=1)
   5- access("T2"."N_1000"=100)

 Id  Par  Pos DEPTH OLD_DEPTH TEXT_LINE
---- ---- ---- ----- -----------------------------------------------------------
  0        22     0        0 SELECT STATEMENT
  1    0    1    1         1  HASH JOIN
  2    1    1    2         2   TABLE ACCESS by index rowid T1
  3    2    1    3         3    INDEX range scan T1_I1
  4    1    2    2         2   TABLE ACCESS by index rowid T2
  5    4    1    3         3    INDEX range scan T2_I1

我之所以列出较早版本的获取执行计划的方法，是因为这样可以让你将调用dbms_xplan.display的结果的直观展示与plan table中的被隐藏的细节相关联。我们总是看到执行计划都是呈锯齿状，这种方式是为了告诉我们执行计划中操作的关系。也直观的展示id，parent_id和position列之间的关系。

计划中每个操作都有id，这个实际上告诉我们每行输出的顺序。每行都有可能是1个或多个子操作的父操作，每行的parent_id列都会指向这个id的父id。这里例子中我们看Id为2跟4的行，都是Id为1的子操作。如果一行有多个子操作，position列就会列出子操作的顺序，我们看到Id为2的行的position是1，代表他是Id为1的行下第1个子操作，Id为4则是第2个子操作。在dbms_xplan生成的执行计划中我们看不到parent_id和position列，我们需要在很多操作中根据顺序和锯齿的形状来判断父/子关系。

根据id顺序列出的行总是能给我们正确计划的顺序(但是这并不是获取跟操作数据的顺序);而且计算在锯齿状中的行的方法在9i跟10g中也不一样。以前的版本中，来自分级connect by查询的派生列级别允许我们向文本添加适当大小的缩进---但是当Oracle允许我们从内存中获取执行计划的版本(v$sql_plan)时，这种方法就变得十分低效，因此，动态性能视图中包含了一个预先计算的level列(与level相差1，称为depth)，其值在语句被优化时获得，并最终将其添加到plan表中(即使在12c中也有一些生成的值不正确的情况，所以记住怎么根据connect by查询获得执行计划是很有帮助的)。

First Rule for Reading Plans

这篇文章我们不涉及谓词部分，即使它们对于执行计划来说也很重要，我们重点在怎么遍历执行计划主体，并了解Oracle获取和操作数据的顺序。

计划中的每一行都代表一个生成一些"rowsource"(行源)的行操作。这里的行也可能只是从索引中获取到的rowid。一个操作需要几个动作生成一个rowsource，之后将rowsource传递给父操作。如果一个父操作有多个子操作，它会依次调用每个子操作，并通过一些工作将它们提供的rowsource进行合并。你需要学习的最重要的一点是每个操作做了什么，以及该操作"合并"rowsources意味着什么。还有一个稍微复杂的问题是，尽管父操作“依次”调用其子操作，但它可能会多次调用每个子进程，而且重复调用的方式会随着父进程的操作而变化。

这篇文章不可能包含所有的变化和异常情况，但是如果我们暂时忽略特殊情况，那么阅读执行计划的基本方法总结为:"子操作按先后顺序执行，递归向下"。我们通过一个hash join的执行计划来看看是如何工作的。

第0行告诉我们这是一个select语句。我们需要定义它的子集和操作顺序来为这个select语句生成rowsource。在用SQL语句输出的执行计划中可以看到，第1行是第一个也是唯一的属于第0行的子行。如果我们没有parent_id和position列帮助我们判断，我们也可以用直观的方法，一个操作的第一个子操作都是在下一行(它会缩进一格)，之后通过该行画条垂直的线，直到执行计划底部，有相同缩进的行就是这个操作的子操作，并且行的顺序就是子操作的顺序。

通过第1行我们知道这是个hash join操作，这里我们可能需要去查看手册来了解什么是hash join以及它是如何工作的。从SQL生成的执行计划中可以很方便的看到第2行和第4行是hash join的子项，从直观展示中也能看到，第2行是一个子操作，之后垂直对齐到第4行的"table access"的T字母，说明它是第二个子操作。这些信息足够告诉我们，将要在内存里根据t1(第一个子操作)中的一些行建立hash表，并根据t2(第2个子操作)中的行对hash表进行探测，如果符合条件，则构造一个结果集，并返回给在第0行(第1行的父行)。这一系列的hash join操作是一个很好的例子，告诉我们为什么需要考虑子操作的顺序，这些物理上的操作顺序会告诉我们哪些表是作为build表(即需要在内存中建立的表)，哪些是作为probe表(即探测表，不需要放到内存里)。

此刻我们还不知道如何判断t1，t2中所需要的行，我们所做的就是将计划的最顶层的一部分作为开始，了解执行计划整体的工作。我们还不能说:“这是Oracle获得的第一个数据集/这是Oracle访问的第一个表”。但是我们可以通过重复我们目前所采用的方法来达到这一点。

我们将用第2行中的rowsource构建一个内存中的hash表，然后用第4行中的rowsource探测hash表;我们先来看看第一个子项。从第2行开始，我们可以确定整个“子计划”，其中rowsource正是我们需要的最终结果：

--------------------------------------------------------------------------------------
| Id | Operation                    |Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   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 |
--------------------------------------------------------------------------------------

从上面分离出来部分的执行计划可以看到，第2行有个单独的子操作进行调用，这个子操作是index range scan。我们可以很容易的看到，通过index range scan生成的rowsource可能包含rowid，而且第2行根据rowid进行表扫描---当试图理解更复杂的执行计划时，类似这样的完整性检查(将父级的需求与子级的提供的数据进行比较)非常有用。

同样的我们也可以看看第4行的子计划：

--------------------------------------------------------------------------------------
| Id | Operation                    |Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   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 |
--------------------------------------------------------------------------------------

同样这也是一个很简单的计划，我们调用第5行的操作进行index range scan，并且在第4行中根据第5行提供的rowid进行访问表。

将所有的片段整合一起，我们可以将执行步骤标识出来:

------------------------------------------------------
| Id | Operation                    |Name  | Order |
------------------------------------------------------
|   0| SELECT STATEMENT             |       |    6 |
|*  1|  HASH JOIN                   |       |    5 |
|   2|   TABLE ACCESS BY INDEX ROWID| T1    |    2 |
|*  3|    INDEX RANGE SCAN          | T1_I1 |     1 |
|   4|   TABLE ACCESS BY INDEX ROWID| T2    |    4 |
|*  5|    INDEX RANGE SCAN          | T2_I1 |     3 |
------------------------------------------------------

我们根据如下顺序解读执行计划：

第0行调用第1行(第1个子操作)。

第1行调用第2行(第1个子操作)。

第2行调用第3行(第1个子操作)。

第3行通过index range scan生成一些rowsource，并将其传递到第2行。

第2行根据rowid访问t1表并生成rowsource，并传递到第1行。

第1行用这些行源建立内存里的hash表，之后调用第4行(第2个子操作)开始提供用作探测表的rowsource。

第4行调用第5行(第1个子操作)。

第5行根据index range scan生成rowsource，并传递给第4行。

第4行根据rowid访问t2表并生成rowsource，传递给第1行。

第1行探测hash表，并找出符合条件的行，根据结果集生成第5个rowsource，之后传递给第0行，这也就是传递给客户端程序的结果。

计划中还有比这更多的内容——特别是我们需要更多地考虑操作的时机:有些是“批量”处理，有些是“单行”处理;我们需要引入谓词部分并考虑“访问”和“筛选器”的相关性;这些细节我们将在下一篇文章中看到。

Closing thoughts

我想强调的是，将复杂的执行计划分解成简单的部分是非常方便的。我们例子中的执行计划很短，所以分块处理的好处并不是特别明显，但是想想我们是如何从整体看执行计划，并选出最前两行，之后查看一些子计划。我们可以在任何计划中使用这个方法，不管它有多复杂，并单独检查计划的小部分。

原文链接:

https://www.red-gate.com/simple-talk/sql/oracle/execution-plans-part-3-the-rule/

原文作者:Jonathan Lewis

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

执行计划-3：解释规则

评论