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/