如何阅读执行计划

解释执行计划：

11.4阅读和理解执行计划
要执行一条 SQL 语句，Oracle 数据库可能需要执行许多步骤。每个步骤要么从数据库物理检索数据行，要么以某种方式为发出语句的用户准备它们。Oracle 数据库用来执行语句的步骤的组合就是执行计划。
执行计划包括语句访问的每个表的访问路径和表的顺序（加入顺序）与适当的加入方法。

11.4.2执行计划中的步骤

例如：

EXPLAIN PLAN FOR
SELECT e.employee_id, j.job_title, e.salary, d.department_name
    FROM employees e, jobs j, departments d
    WHERE  e.employee_id < 103
       AND e.job_id = j.job_id 
       AND e.department_id = d.department_id;
       
The resulting output table in Example 11-14 shows the execution plan chosen by the optimizer to execute the SQL statement in the example:

Example 11-14 EXPLAIN PLAN Output

-----------------------------------------------------------------------------------
| Id  | Operation                     |  Name        | Rows  | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              |     3 |   189 |    10  (10)|
|   1 |  NESTED LOOPS                 |              |     3 |   189 |    10  (10)|
|   2 |   NESTED LOOPS                |              |     3 |   141 |     7  (15)|
|*  3 |    TABLE ACCESS FULL          | EMPLOYEES    |     3 |    60 |     4  (25)|
|   4 |    TABLE ACCESS BY INDEX ROWID| JOBS         |    19 |   513 |     2  (50)|
|*  5 |     INDEX UNIQUE SCAN         | JOB_ID_PK    |     1 |       |            |
|   6 |   TABLE ACCESS BY INDEX ROWID | DEPARTMENTS  |    27 |   432 |     2  (50)|
|*  7 |    INDEX UNIQUE SCAN          | DEPT_ID_PK   |     1 |       |            |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("E"."EMPLOYEE_ID"<103)
   5 - access("E"."JOB_ID"="J"."JOB_ID")
   7 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID"

执行计划的每一步都返回一组行。下一步要么使用这些行，要么在最后一步将这些行返回给发出 SQL 语句的用户或应用程序。行集是一个步骤返回的一组行。

步骤 ID 的编号反映了它们响应EXPLAIN PLAN语句的显示顺序。执行计划的每一步要么从数据库中检索行，要么接受来自一个或多个行源的行作为输入。

示例 11-14中的以下步骤以物理方式从数据库中的对象中检索数据：
第 3 步 读取表的所有行employees。

第 5 步 用JOB_ID_PK索引中查找每个job_id，并在jobs表中找到相关行的 rowid。

第 4 步 从jobs表中检索具有第 5 步返回的行 ID 的行。

第 7 步 department_id在DEPT_ID_PK索引中查找每个，并在departments表中找到相关行的 rowid.

第 6 步 从departments表中检索具有第 7 步返回的行 ID 的行。

示例 11-14中的以下步骤对前一个行源返回的行进行操作：

第 2 步 对jobs和employees表中的job_id执行嵌套循环操作，接受第 3 步和第 4 步中的行源，将第 3 步源中的每一行连接到第 4 步中对应的行，并将结果行返回到第 2 步。

步骤 1 执行嵌套循环操作，接受来自步骤 2 和步骤 6 的行源，将来自步骤 2 源的每一行连接到其在​​步骤 6 中的对应行，并将结果行返回到步骤 1。

所以顺序是：3-5-4-2-7-6-1-0

简单的总结：
解释计划中的步骤缩进以指示操作的层次结构以及哪些步骤取决于哪些其他步骤。查看缩进计划时，要查找首先执行的操作，
请检查操作列。在此列中，最右边（即最缩进）最上面的操作是最先执行的操作。换句话说，从顶部开始向下查看操作列，直到找到缩进最多的操作。

解释计划中的其他列提供了确定选择该计划的原因的各种有用信息：
行数 - 这告诉我们优化器期望执行计划的这一行返回的估计行数
字节 - 这告诉我们优化器期望执行计划的这一行将返回的估计字节数
成本 (%CPU) - 这是优化器对查询的“成本”和 %CPU 的估计。成本允许优化器相互比较不同计划的估计性能。
时间 - 这是优化器对查询每个步骤的持续时间的估计

例子2：

In SQLT the plan would look like:

select ename,dname 
  from emp, dept 
 where emp.deptno=dept.deptno 
   and dept.dname in ('ACCOUNTING','RESEARCH','SALES','OPERATIONS'); 
SQLT Join Order Plan
---------------------------------------------------------------
| Id  |exec ord     |   Operation                    | Name    | 
---------------------------------------------------------------
|   0 |     6        | SELECT STATEMENT               |         | 
|   1 |     5        |  MERGE JOIN                    |         |
|*  2 |     2        |   TABLE ACCESS BY INDEX ROWID  | DEPT    |
|   3 |     1        |    INDEX FULL SCAN             | PK_DEPT |
|*  4 |     4        |   SORT JOIN                    |         |
|   5 |     3        |    TABLE ACCESS FULL           | EMP     |
---------------------------------------------------------------

解释：上面执行步骤：
ID=0 上面没有操作，所以它没有父级但有 1 个子级。
ID=0 是 ID=1 的父级，并且依赖于它的行。您可以说它是父级，因为子级是缩进的。
所以 ID=1 必须在 ID=0 之前执行

移动到 ID=1：
和以前一样，ID=1 是 ID=0 的孩子。
从缩进来看，ID=2 和 ID=4 在 ID=1 下方的缩进级别相同。因此 ID=1 是 ID=2 和 ID=4 的父级，并且依赖于它们的行。所以 ID=2 和 ID=4 必须在 ID=1 之前执行

移动到 ID=2：
ID=2 是 ID=1 的第一个孩子。
从缩进来看，ID=2 是 ID=3 的父级，并且依赖于它的行。所以 ID=3 必须在 ID=2 之前执行。继续 ID=3：

移动到 ID=3
ID=3 是 ID=2 的（唯一）孩子。
ID=3 没有子操作。这意味着 ID=3 是查询执行的第一步。从该步骤向 ID=2 提供行。

ID=1 和 ID=0 也依赖于 ID=3。一旦 ID=3 产生了行，它们就会被传递给 ID=2 并且该步骤将它们用于它正在执行的任何操作。然后它将处理后的行提供给其父级，
依此类推。这意味着 ID=2 是执行的第二步。ID=1 接下来不会执行，因为它有 2 个输入。它需要在开始运行之前访问这两个，所以现在让我们看一下 ID=1 的第二个孩子，ID=4：

ID=4 是 ID=1 的第二个孩子。
ID=4 是 ID=5 的父级，并且依赖于它的行。ID=5 必须在 ID=4 之前执行。这意味着 ID=5 是在 ID=4 之后执行的第三步。

一旦 ID=1 有来自其两个孩子的输入，它就可以执行。ID=1 处理它从其相关步骤（ID=2 和 ID=4）接收到的行并将它们返回给它的父 ID=0。

ID=0 将行返回给用户。

一个简短的总结是：

要查找执行顺序：

从 ID=0 开始：SELECT STATEMENT 但这取决于它的子对象
所以它查看它的第一个子步骤：ID=1 MERGE JOIN 但这取决于它的子对象
所以它查看它的第一个子步骤：ID=2 TABLE ACCESS BY INDEX ROWID DEPT 但这取决于它的子对象
因此，它查看其唯一的子步骤：ID=3 INDEX FULL SCAN PK_DEPT。这没有孩子，所以这被执行了。
ID=3 的行被反馈到 ID=2
来自 ID=2 的行被反馈到 ID=1 但这有 2 个孩子，所以需要探索另一个孩子 ID=4
所以它查看它的第二个子步骤：ID=4 SORT JOIN 但这取决于它的子对象
所以它查看它唯一的子步骤： ID=5 TABLE ACCESS FULL EMP 这没有子步骤，所以它被执行。
ID=5 的行被反馈到 ID=4
ID=4 的行被反馈到 ID=1。由于两个孩子现在都提供了行，因此可以执行 ID=1。
ID=1 的行被反馈到 ID=0
ID=0 的行被反馈给客户端
行返回到父步骤直到完成

执行顺序是 3,2,5,4,1,0

Start with ID=0: SELECT STATEMENT but this is dependent on its child objects
So it looks at its first child step:  ID=1  MERGE JOIN but this is dependent on its child objects
So it looks at its first child step:  ID=2   TABLE ACCESS BY INDEX ROWID DEPT but this is dependent on its child object
So it looks at its only child step:  ID=3   INDEX FULL SCAN PK_DEPT. This has no children so this is executed.
Rows from ID=3 are fed back to ID=2
Rows from ID=2 are fed back to ID=1 but this has 2 children so the other child ID=4 needs to be explored
So it looks at its second child step:  ID=4   SORT JOIN but this is dependent on its child object
So it looks at its only child step:  ID=5   TABLE ACCESS FULL EMP This has no children so this is executed.
Rows from ID=5 are fed back to ID=4
Rows from ID=4 are fed back to ID=1. Since both children have now supplied rows, ID=1 can be executed.
Rows from ID=1 are fed back to ID=0
Rows from ID=0 are fed back to the client
Rows are returned to the parent step(s) until finished

Execution order is 3,2,5,4,1,0

12.1.1执行计划如何改变
使用查询优化器，执行计划可以并且确实随着底层优化器输入的变化而变化。EXPLAIN PLAN输出显示在解释语句时 Oracle 数据库将如何运行 SQL 语句。
由于执行环境和解释计划环境的不同，这个计划可能与实际执行计划不同的一条 SQL 语句。

由于以下原因，执行计划可能会有所不同：

不同的模式

不同的成本

12.1.1.1不同的模式
主要原因包括以下几点：

执行和解释计划发生在不同的数据库上。
解释语句的用户与运行语句的用户不同。两个用户可能指向同一个数据库中的不同对象，从而导致不同的执行计划。
两个操作之间的架构更改（通常是索引更改）。

12.1.1.2不同的成本
即使模式相同，优化器也可以在成本不同时选择不同的执行计划。影响成本的一些因素包括：

数据量和统计

绑定变量类型和值

全局或会话级别设置的初始化参数

12.1.2尽量减少丢弃
检查解释计划可让您在以下情况下寻找丢弃物：

全扫描

非选择性范围扫描

后期谓词过滤器

错误的加入顺序

后期过滤操作

12.1.3 查看更好的 Execution Plans
例如，EXPLAIN PLAN显示语句使用索引的输出并不一定意味着该语句有效运行。有时索引效率极低。在这种情况下，您应该检查以下内容：
正在使用的索引的列
Their selectivity (fraction of table being accessed)：他们的选择性（被访问的表格的一部分）
最好用来EXPLAIN PLAN确定一个接入方案，然后通过测试证明它是最优方案。评估计划时，检查语句的实际资源消耗。

reference：
先里后外，先上后下（还要了解计划和执行顺序，有必要了解所涉及的父母 - 孩子关系）

12c：Oracle 数据库 SQL 调优指南