看懂Oracle中的执行计划

  

成都创新互联是一家集网站建设,牡丹江企业网站建设,牡丹江品牌网站建设,网站定制,牡丹江网站建设报价,网络营销,网络优化,牡丹江网站推广为一体的创新建站企业，帮助传统企业提升企业形象加强企业竞争力。可充分满足这一群体相比中小企业更为丰富、高端、多元的互联网需求。同时我们时刻保持专业、时尚、前沿，时刻以成就客户成长自我，坚持不断学习、思考、沉淀、净化自己，让我们为更多的企业打造出实用型网站。

从事Oracle相关的工作，从最初的一脸懵逼到现在的略有所知，也来总结一下自己最近学习关于Oracle中SQL语句的执行计划的相关内容。下面是文章的目录结构：

 

一、什么是Oracle执行计划？

执行计划是一条查询语句在Oracle中的执行过程或访问路径的描述

 
 

二、怎样查看Oracle执行计划？

2.1 explain plan for命令查看执行计划

在sql*plus中，执行如下命令：

1）explain plan for select * from XXXX; 

2）select * from table(dbms_xplan.display); 

 

2.2 SET AUTOTRACE ON查看执行计划

语法：SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]

序号	命令	解释
1	SET AUTOTRACE OFF	此为默认值，即关闭Autotrace
2	SET AUTOTRACE ON EXPLAIN	只显示执行计划
3	SET AUTOTRACE ON STATISTICS	只显示执行的统计信息
4	SET AUTOTRACE ON	包含2,3两项内容
5	SET AUTOTRACE TRACEONLY	与ON相似，但不显示语句的执行结果

 

 

2.3 PLSQL Developer查看执行计划

 

① 配置执行计划需要显示的项：

工具—>首选项 —>窗口类型—>计划窗口—>根据需要配置要显示在执行计划中的列

执行计划的常用列字段解释：

基数（Rows）：Oracle估计的当前操作的返回结果集行数

字节（Bytes）：执行该步骤后返回的字节数

耗费（COST）、CPU耗费：Oracle估计的该步骤的执行成本，用于说明SQL执行的代价，理论上越小越好（该值可能与实际有出入）

时间（Time）：Oracle估计的当前操作所需的时间

 

② 打开执行计划：

在SQL窗口执行完一条select语句后按 F5 即可查看刚刚执行的这条查询语句的执行计划

 

注：在PLSQL中使用SQL命令查看执行计划的话，某些SQL*PLUS命令PLSQL无法支持，比如SET AUTOTRACE ON

三、看懂Oracle执行计划

 

3.1 执行顺序：

根据Operation缩进来判断，缩进最多的最先执行；（缩进相同时，最上面的最先执行）

例：上图中 INDEX RANGE SCAN和 INDEX UNIQUE SCAN两个动作缩进最多，最上面的 INDEX RANGE SCAN先执行；

同一级如果某个动作没有子ID就最先执行

同一级的动作执行时遵循最上最右先执行的原则

例：上图中 TABLE ACCESS BY GLOBAL INDEX ROWID和 TABLE ACCESS BY INDEX ROWID两个动作缩进都在同一级，则位于上面的TABLE ACCESS BY GLOBAL INDEX ROWID这个动作先执行；这个动作又包含一个子动作 INDEX RANGE SCAN，则位于右边的子动作 INDEX RANGE SCAN先执行；

 

图示中的SQL执行顺序即为：

INDEX RANGE SCAN —> TABLE ACCESS BY GLOBAL INDEX ROWID —> INDEX UNIQUE SCAN —> TABLE ACCESS BY INDEX ROWID —> NESTED LOOPS OUTER —> SORT GROUP BY —> SELECT STATEMENT, GOAL = ALL_ROWS

（注：PLSQL提供了查看执行顺序的功能按钮(上图中的红框部分)）

 

3.2 对图中动作的一些说明：

1、上图中 TABLE ACCESS BY … 即描述的是该动作执行时表访问（或者说Oracle访问数据）的方式；

表访问的几种方式：（非全部）

●TABLE ACCESS FULL（全表扫描）

●TABLE ACCESS BY ROWID（通过ROWID的表存取）

●TABLE ACCESS BY INDEX SCAN（索引扫描）

 

（1）TABLE ACCESS FULL（全表扫描）：

Oracle会读取表中所有的行，并检查每一行是否满足SQL语句中的 Where限制条件；全表扫描时可以使用多块读（即一次I/O读取多块数据块）操作，提升吞吐量；

使用建议：数据量太大的表不建议使用全表扫描，除非本身需要取出的数据较多，占到表数据总量的 5% ~ 10%或以上

 

（2）TABLE ACCESS BY ROWID（通过ROWID的表存取）：

先说一下什么是ROWID？

ROWID是由Oracle自动加在表中每行最后的一列伪列，既然是伪列，就说明表中并不会物理存储ROWID的值；

你可以像使用其它列一样使用它，只是不能对该列的值进行增、删、改操作；一旦一行数据插入后，则其对应的ROWID在该行的生命周期内是唯一的，即使发生行迁移，该行的ROWID值也不变。

 

让我们再回到 TABLE ACCESS BY ROWID来：

行的ROWID指出了该行所在的数据文件、数据块以及行在该块中的位置，所以通过ROWID可以快速定位到目标数据上，这也是Oracle中存取单行数据最快的方法；

 

（3）TABLE ACCESS BY INDEX SCAN（索引扫描）：

在索引块中，既存储每个索引的键值，也存储具有该键值的行的ROWID。

一个数字列上建索引后该索引可能的概念结构如下图：

所以索引扫描其实分为两步：

Ⅰ：扫描索引得到对应的ROWID

Ⅱ：通过ROWID定位到具体的行读取数据

 

-----------------------------------索引扫描延伸--------------------------------

索引扫描又分五种： a)INDEX UNIQUE SCAN（索引唯一扫描）： b)INDEX RANGE SCAN（索引范围扫描）： c)INDEX FULL SCAN（索引全扫描）： d)INDEX FAST FULL SCAN（索引快速扫描）: e)INDEX SKIP SCAN（索引跳跃扫描）：

 

2、上图中的 NESTED LOOPS …描述的是表连接方式；

JOIN关键字用于将两张表作连接，一次只能连接两张表，JOIN操作的各步骤一般是串行的（在读取做连接的两张表的数据时可以并行读取）；

表（row source）之间的连接顺序对于查询效率有很大的影响，对首先存取的表（驱动表）先应用某些限制条件（Where过滤条件）以得到一个较小的row source，可以使得连接效率提高。

 

-----------延伸阅读：驱动表（Driving Table）与匹配表（Probed Table）------------

驱动表（Driving Table）：
表连接时首先存取的表，又称外层表（Outer Table），这个概念用于NESTED LOOPS（嵌套循环）与 HASH JOIN（哈希连接）中；
如果驱动表返回较多的行数据，则对所有的后续操作有负面影响，故一般选择小表（应用Where限制条件后返回较少行数的表）作为驱动表。

匹配表（Probed Table）：
又称为内层表（Inner Table），从驱动表获取一行具体数据后，会到该表中寻找符合连接条件的行。故该表一般为大表（应用Where限制条件后返回较多行数的表）。

--------------------------------------------------------------------------------

 

表连接的几种方式：

●SORT MERGE JOIN（排序-合并连接）

●NESTED LOOPS（嵌套循环）

●HASH JOIN（哈希连接）

●CARTESIAN PRODUCT（笛卡尔积）

注：这里将首先存取的表称作 row source 1，将之后参与连接的表称作 row source 2；

 

（1）SORT MERGE JOIN（排序-合并连接）

假设有查询：select a.name, b.name from table_A a join table_B b on (a.id = b.id)

 

内部连接过程：

a) 生成 row source 1需要的数据，按照连接操作关联列（如示例中的a.id）对这些数据进行排序

b) 生成 row source 2需要的数据，按照与 a)中对应的连接操作关联列（b.id）对数据进行排序

c) 两边已排序的行放在一起执行合并操作（对两边的数据集进行扫描并判断是否连接）

 

延伸：
如果示例中的连接操作关联列 a.id，b.id之前就已经被排过序了的话，连接速度便可大大提高，因为排序是很费时间和资源的操作，尤其对于有大量数据的表。
故可以考虑在 a.id，b.id上建立索引让其能预先排好序。不过遗憾的是，由于返回的结果集中包括所有字段，所以通常的执行计划中，即使连接列存在索引，也不会进入到执行计划中，除非进行一些特定列处理（如仅仅只查询有索引的列等）。
排序-合并连接的表无驱动顺序，谁在前面都可以；
排序-合并连接适用的连接条件有： <、<=、=、>、>=，不适用的连接条件有： <>、like

 

（2）NESTED LOOPS（嵌套循环）

内部连接过程：

a) 取出 row source 1的 row 1（第一行数据），遍历 row source 2的所有行并检查是否有匹配的，取出匹配的行放入结果集中

b) 取出 row source 1的 row 2（第二行数据），遍历 row source 2的所有行并检查是否有匹配的，取出匹配的行放入结果集中

c) ……

若 row source 1（即驱动表）中返回了 N行数据，则 row source 2也相应的会被全表遍历 N次。

因为 row source 1的每一行都会去匹配 row source 2的所有行，所以当 row source 1返回的行数尽可能少并且能高效访问 row source 2（如建立适当的索引）时，效率较高。

 

延伸：

 

（3）HASH JOIN（哈希连接）

哈希连接只适用于等值连接（即连接条件为  = ）

HASH JOIN对两个表做连接时并不一定是都进行全表扫描，其并不限制表访问方式；

 

内部连接过程简述：

a) 取出 row source 1（驱动表，在HASH JOIN中又称为Build Table） 的数据集，然后将其构建成内存中的一个 Hash Table（Hash函数的Hash KEY就是连接操作关联列），创建Hash位图（bitmap）

b) 取出 row source 2（匹配表）的数据集，对其中的每一条数据的连接操作关联列使用相同的Hash函数并找到对应的 a)里的数据在 Hash Table中的位置，在该位置上检查能否找到匹配的数据

 

----------------------------延伸阅读：Hash Table相关---------------------------

来自Wiki的解释：
In computing, a hash table (hash map) is a data structure used to implement an associative array, a structure that can map keys to values. A hash table uses a hash function to compute an index into an array of buckets or slots, from which the desired value can be found.
散列（hash）技术：在记录的存储位置和记录具有的关键字key之间建立一个对应关系 f ，使得输入key后，可以得到对应的存储位置 f(key)，这个对应关系 f 就是散列（哈希）函数；

采用散列技术将记录存储在一块连续的存储空间中，这块连续的存储空间就是散列表（哈希表）

不同的key经同一散列函数散列后得到的散列值理论上应该不同，但是实际中有可能相同，相同时即是发生了散列（哈希）冲突，解决散列冲突的办法有很多，比如HashMap中就是用链地址法来解决哈希冲突；

哈希表是一种面向查找的数据结构，在输入给定值后查找给定值对应的记录在表中的位置以获取特定记录这个过程的速度很快。

-------------------------------------------------------------------------------

 

HASH JOIN的三种模式：

●OPTIMAL HASH JOIN

●ONEPASS HASH JOIN

●MULTIPASS HASH JOIN

 

1) OPTIMAL HASH JOIN

OPTIMAL 模式是从驱动表（也称Build Table）上获取的结果集比较小，可以把根据结果集构建的整个Hash Table都建立在用户可以使用的内存区域里。

连接过程简述：

Ⅰ：首先对Build Table内各行数据的连接操作关联列使用Hash函数，把Build Table的结果集构建成内存中的Hash Table。如图所示，可以把Hash Table看作内存中的一块大的方形区域，里面有很多的小格子，Build Table里的数据就分散分布在这些小格子中，而这些小格子就是Hash Bucket（见上面Wiki的定义）。

Ⅱ：开始读取匹配表（Probed Table）的数据，对其中每行数据的连接操作关联列都使用同上的Hash函数，定位Build Table里使用Hash函数后具有相同值数据所在的Hash Bucket。

Ⅲ：定位到具体的Hash Bucket后，先检查Bucket里是否有数据，没有的话就马上丢掉匹配表（Probed Table）的这一行。如果里面有数据，则继续检查里面的数据（驱动表的数据）是否和匹配表的数据相匹配。

 

2) ONEPASS HASH JOIN

从驱动表（也称Build Table）上获取的结果集较大，无法将根据结果集构建的Hash Table全部放入内存中时，会使用 ONEPASS模式。

连接过程简述：

Ⅰ：对Build Table内各行数据的连接操作关联列使用Hash函数，根据Build Table的结果集构建Hash Table后，由于内存无法放下所有的Hash Table内容，将导致有的Hash Bucket放在内存里，有的Hash Bucket放在磁盘上，无论放在内存里还是磁盘里，Oracle都使用一个Bitmap结构来反映这些Hash Bucket的状态（包括其位置和是否有数据）。

Ⅱ：读取匹配表数据并对每行的连接操作关联列使用同上的Hash函数，定位Bitmap上Build Table里使用Hash函数后具有相同值数据所在的Bucket。如果该Bucket为空，则丢弃匹配表的这条数据。如果不为空，则需要看该Bucket是在内存里还是在磁盘上。

如果在内存中，就直接访问这个Bucket并检查其中的数据是否匹配，有匹配的话就返回这条查询结果。

如果在磁盘上，就先把这条待匹配数据放到一边，将其先暂存在内存里，等以后积累了一定量的这样的待匹配数据后，再批量的把这些数据写入到磁盘上（上图中的 Dump probe partitions to disk）。

Ⅲ：当把匹配表完整的扫描了一遍后，可能已经返回了一部分匹配的数据了。接下来还有Hash Table中一部分在磁盘上的Hash Bucket数据以及匹配表中部分被写入到磁盘上的待匹配数据未处理，现在Oracle会把磁盘上的这两部分数据重新匹配一次，然后返回最终的查询结果。

 

3) MULTIPASS HASH JOIN

当内存特别小或者相对而言Hash Table的数据特别大时，会使用 MULTIPASS模式。MULTIPASS会多次读取磁盘数据，应尽量避免使用该模式。

 

3、上图中的 … OUTER描述的是表连接类型

表连接的两种类型：

●INNER JOIN（内连接）

●OUTER JOIN（外连接）

 

通过下面的示例进行演示：

现有test1、test2两表，test1和test2表信息如下：

   

 

下面的例子都用test1、test2两表来演示。

（1） INNER JOIN（内连接）

只返回两表中相匹配的记录。

INNER JOIN 又分为两种：

等值连接（连接条件为 = ）

非等值连接（连接条件为 非 = ，如  >  >=  <  <= 等）

 

等值连接用的最多，下面以等值连接举例：

内连接的两种写法：

Ⅰ：select a.id ID1, a.name NAME1, b.id ID2, b.name NAME2 from test1 a inner join test2 b on (a.id = b.id);

Ⅱ：select a.id ID1, a.name NAME1, b.id ID2, b.name NAME2 from test1 a join test2 b on (a.id = b.id);

连接时只返回满足连接条件（test1.id = test2.id）的记录：

 

（2）OUTER JOIN（外连接）：

OUTER JOIN分为三种：

●LEFT OUTER JOIN（可简写为 LEFT JOIN，左外连接）

●RIGHT OUTER JOIN（ RIGHT JOIN，右外连接）

●FULL OUTER JOIN（ FULL JOIN，全外连接）

 

a)LEFT JOIN（左连接）：

返回的结果不仅包含符合连接条件的记录，还包含左边表中的全部记录。（若返回的左表中某行记录在右表中没有匹配项，则右表中的返回列均为空值）

 

两种写法：

Ⅰ：select a.id ID1, a.name NAME1, b.id ID2, b.name NAME2 from test1 a left outer join test2 b on (a.id = b.id);

Ⅱ：select a.id ID1, a.name NAME1, b.id ID2, b.name NAME2 from test1 a left join test2 b on (a.id = b.id);

返回结果：

 

b)RIGHT JOIN（右连接）：

返回的结果不仅包含符合连接条件的记录，还包含右边表中的全部记录。（若返回的右表中某行记录在左表中没有匹配项，则左表中的返回列均为空值）

两种写法：

Ⅰ：select a.id ID1, a.name NAME1, b.id ID2, b.name NAME2 from test1 a right outer join test2 b on (a.id = b.id);

Ⅱ：select a.id ID1, a.name NAME1, b.id ID2, b.name NAME2 from test1 a right join test2 b on (a.id = b.id);

 

返回结果：

 

c)FULL JOIN（全连接）：

返回左右两表的全部记录。(左右两边不匹配的项都以空值代替)

两种写法：

Ⅰ：select a.id ID1, a.name NAME1, b.id ID2, b.name NAME2 from test1 a full outer join test2 b on (a.id = b.id);

Ⅱ：select a.id ID1, a.name NAME1, b.id ID2, b.name NAME2 from test1 a full outer join test2 b on (a.id = b.id);

 

返回结果：

 

------------------------------延伸阅读：(+)操作符-----------------------------

(+) 操作符是Oracle特有的表示法，用来表示外连接（只能表示 左外、右外 连接），需要配合Where语句使用。
特别注意：(+)操作符在左表的连接条件上表示右连接，在右表的连接条件上表示左连接。
如：
Ⅰ：select a.id ID1, a.name NAME1, b.id ID2, b.name NAME2 from test1 a,test2 b where a.id = b.id(+);

查询结果：

实际与左连接select a.id ID1, a.name NAME1, b.id ID2, b.name NAME2 from test1 a left join test2 b on (a.id = b.id);效果等价

Ⅱ：select a.id ID1, a.name NAME1, b.id ID2, b.name NAME2 from test1 a,test2 b where a.id(+) = b.id;

查询结果：

实际与右连接select a.id ID1, a.name NAME1, b.id ID2, b.name NAME2 from test1 a right join test2 b on (a.id = b.id);效果等价

-------------------------------------------------------------------------------

 

补充：自连接

通过给一个表赋两个不同的别名让其与自身内连或外连接

在oracle的scott的schema中有一个表是emp。在emp中的每一个员工都有自己的mgr(经理)，并且每一个经理自身也是公司的员工，自身也有自己的经理。

查询语句如下：

select work.ename worker,mgr.ename  manager from scott.emp work, scott.emp mgr where work.empno=mgr.mgr order by work.ename;

 

四、使用执行计划进行SQL调优

1、查看总COST，获得资源耗费的总体印象 

一般而言，执行计划第一行所对应的COST(即成本耗费)值，反应了运行这段SQL的总体估计成本，单看这个总成本没有实际意义，但可以拿它与相同逻辑不同执行计划的SQL的总体COST进行比较，通常COST低的执行计划要好一些。

 

2、按照从左至右，从上至下的方法，了解执行计划的执行步骤 

执行计划按照层次逐步缩进，从左至右看，缩进最多的那一步，最先执行，如果缩进量相同，则按照从上而下的方法判断执行顺序，可粗略认为上面的步骤优先执行。每一个执行步骤都有对应的COST,可从单步COST的高低，以及单步的估计结果集（对应ROWS/基数），来分析表的访问方式，连接顺序以及连接方式是否合理。 

 

3、分析表的访问方式 

表的访问方式主要是两种：全表扫描（TABLE ACCESS FULL）和索引扫描(INDEX SCAN)，如果表上存在选择性很好的索引，却走了全表扫描，而且是大表的全表扫描，就说明表的访问方式可能存在问题；若大表上没有合适的索引而走了全表扫描，就需要分析能否建立索引，或者是否能选择更合适的表连接方式和连接顺序以提高效率。

 

4、分析表的连接方式和连接顺序 

表的连接顺序：就是以哪张表作为驱动表来连接其他表的先后访问顺序。 

表的连接方式：简单来讲，就是两个表获得满足条件的数据时的连接过程。主要有三种表连接方式，嵌套循环（NESTED LOOPS）、哈希连接（HASH JOIN）和排序-合并连接（SORT MERGE JOIN）。

 

我们常见得是嵌套循环和哈希连接。 

嵌套循环：最适用也是最简单的连接方式。类似于用两层循环处理两个游标，外层游标称作驱动表，Oracle检索驱动表的数据，一条一条的代入内层游标，查找满足WHERE条件的所有数据，因此内层游标表中可用索引的选择性越好，嵌套循环连接的性能就越高。 

哈希连接：先将驱动表的数据按照条件字段以散列的方式放入内存，然后在内存中匹配满足条件的行。哈希连接需要有合适的内存，而且必须在CBO优化模式下，连接两表的WHERE条件有等号的情况下才可以使用。哈希连接在表的数据量较大，表中没有合适的索引可用时比嵌套循环的效率要高。 

 

总结： 

1、这里看到的执行计划，只是SQL运行前可能的执行方式，实际运行时可能因为软硬件环境的不同，而有所改变，而且cost高的执行计划，不一定在实际运行起来，速度就一定差，我们平时需要结合执行计划，和实际测试的运行时间，来确定一个执行计划的好坏。 

 

2、对于表的连接顺序，多数情况下使用的是嵌套循环，尤其是在索引可用性好的情况下，使用嵌套循环式最好的，但当ORACLE发现需要访问的数据表较大，索引的成本较高或者没有合适的索引可用时，会考虑使用哈希连接，以提高效率。排序合并连接的性能最差，但在存在排序需求，或者存在非等值连接无法使用哈希连接的情况下，排序合并的效率，也可能比哈希连接或嵌套循环要好。 

 

本文参考自：https://www.cnblogs.com/Dreamer-1/p/6076440.html



作者：SEian.G（苦练七十二变，笑对八十一难）


网页题目：看懂Oracle中的执行计划
文章网址：http://6mz.cn/article/giippi.html