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
;
以下是两种方法生成的结果:
--------------------------------------------------------------------------------------
| 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
计划中每个操作都有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列，我们需要在很多操作中根据顺序和锯齿的形状来判断父/子关系。
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 | --------------------------------------------------------------------------------------
同样的我们也可以看看第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 | --------------------------------------------------------------------------------------
将所有的片段整合一起，我们可以将执行步骤标识出来:
------------------------------------------------------ | 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/
公众号 
手机访问