使用Oracle执行计划分析SQL性能

执行计划：一条查询语句在ORACLE中的执行过程或访问路径的描述。即就是对一个查询任务，做出一份怎样去完成任务的详细方案。

如果要分析某条SQL的性能问题，通常我们要先看SQL的执行计划，看看SQL的每一步执行是否存在问题。 看懂执行计划也就成了SQL优化的先决条件。 通过执行计划定位性能问题，定位后就通过建立索引、修改sql等解决问题。

一、执行计划的查看

1.1 设置autotrace

autotrace命令如下

序号	命令	解释
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相似，但不显示语句的执行结果

1.2 使用SQL

在执行的sql前面加上EXPLAIN PLAN FOR

SQL> EXPLAIN PLAN FOR SELECT * FROM EMP;

已解释。

SQL> SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE'));

或者：

SQL> select * from table(dbms_xplan.display);

1.3 使用PL/SQL Developer，Navicat, Toad等客户端工具

 

二、如何读懂执行计划

2.1执行顺序的原则

执行顺序的原则是：由上至下，从右向左
由上至下：在执行计划中一般含有多个节点，相同级别(或并列)的节点，靠上的优先执行，靠下的后执行
从右向左：在某个节点下还存在多个子节点，先从最靠右的子节点开始执行。

一般按缩进长度来判断，缩进最大的最先执行，如果有2行缩进一样，那么就先执行上面的。

 

以下面的sql为例(sakila样例数据库中的address city country连接查询)

select address.address, city.city, country.country
from address
inner join city on address.city_id = city.city_id
inner join country on city.country_id = country.country_id;

2.2 执行计划中字段解释

ID: 一个序号，但不是执行的先后顺序。执行的先后根据缩进来判断。

Operation： 当前操作的内容。

Rows： 当前操作的Cardinality，Oracle估计当前操作的返回结果集。

Cost（CPU）：Oracle 计算出来的一个数值（代价），用于说明SQL执行的代价。

Time：Oracle 估计当前操作的时间。

在看执行计划的时候，除了看执行计划本身，还需要看谓词和统计信息。 通过整体信息来判断SQL效率。

2.3 谓词说明

Access :

• 通过某种方式定位了需要的数据，然后读取出这些结果集，叫做Access。
• 表示这个谓词条件的值将会影响数据的访问路劲（表还是索引）。

Filter：

• 把所有的数据都访问了，然后过滤掉不需要的数据，这种方式叫做filter 。
• 表示谓词条件的值不会影响数据的访问路劲，只起过滤的作用。

在谓词中主要注意access，要考虑谓词的条件，使用的访问路径是否正确。

2.4 Statistics(统计信息)说明

recursive calls	产生的递归sql调用的条数。
Db block gets:	从buffer cache中读取的block的数量
consistent gets	从buffer cache中读取的undo数据的block的数量
physical reads	从磁盘读取的block的数量
redo size	DML生成的redo的大小
bytes sent via SQL*Net to client	数据库服务器通过SQL*Net向查询客户端发送的查询结果字节数
bytes received via SQL*Net from client	通过SQL*Net接受的来自客户端的数据字节数
SQL*Net roundtrips to/from client	服务器和客户端来回往返通信的Oracle Net messages条数
sorts (memory)	在内存执行的排序量
sorts (disk)	在磁盘上执行的排序量
rows processed	处理的数据的行数

解释：

Recursive Calls：Number of recursive calls generated at both the user and system level.

Oracle Database maintains tables used for internal processing. When it needs to change these tables, Oracle Database generates an internal SQL statement, which in turn generates a recursive call. In short, recursive calls are basically SQL performed on behalf of your SQL. So, if you had to parse the query, for example, you might have had to run some other queries to get data dictionary information. These would be recursive calls. Space management, security checks, calling PL/SQL from SQL—all incur recursive SQL calls。

当执行一条SQL语句时，产生的对其他SQL语句的调用，这些额外的语句称之为''recursive calls''或''recursive SQL statements''. 我们做一条insert 时，没有足够的空间来保存row记录，Oracle 通过Recursive Call 来动态的分配空间。

DB Block Gets：Number of times a CURRENT block was requested.

Current mode blocks are retrieved as they exist right now, not in a consistent read fashion. Normally, blocks retrieved for a query are retrieved as they existed when the query began. Current mode blocks are retrieved as they exist right now, not from a previous point in time. During a SELECT, you might see current mode retrievals due to reading the data dictionary to find the extent information for a table to do a full scan (because you need the "right now" information, not the consistent read). During a modification, you will access the blocks in current mode in order to write to them.

DB Block Gets:请求的数据块在buffer能满足的个数

当前模式块意思就是在操作中正好提取的块数目，而不是在一致性读的情况下而产生的块数。正常的情况下，一个查询提取的块是在查询开始的那个时间点上存在的数据块，当前块是在这个时刻存在的数据块，而不是在这个时间点之前或者之后的数据块数目。

Consistent Gets： Number of times a consistent read was requested for a block.

This is how many blocks you processed in "consistent read" mode. This will include counts of blocks read from the rollback segment in order to roll back a block. This is the mode you read blocks in with a SELECT, for example. Also, when you do a searched UPDATE/DELETE, you read the blocks in consistent read mode and then get the block in current mode to actually do the modification.

(Consistent Gets: 数据请求总数在回滚段Buffer中的数据一致性读所需要的数据块)

这里的概念是在处理你这个操作的时候需要在一致性读状态上处理多少个块，这些块产生的主要原因是因为由于在你查询的过程中，由于其他会话对数据块进行操作，而对所要查询的块有了修改，但是由于我们的查询是在这些修改之前调用的，所以需要对回滚段中的数据块的前映像进行查询，以保证数据的一致性。这样就产 生了一致性读。

Physical Reads：

Total number of data blocks read from disk. This number equals the value of "physical reads direct" plus all reads into buffer cache.

(Physical Reads:实例启动后，从磁盘读到Buffer Cache数据块数量)

就是从磁盘上读取数据块的数量，其产生的主要原因是：

（1） 在数据库高速缓存中不存在这些块

（2） 全表扫描

（3） 磁盘排序

它们三者之间的关系大致可概括为：

逻辑读指的是Oracle从内存读到的数据块数量。一般来说是'consistent gets' + 'db block gets'。当在内存中找不到所需的数据块的话就需要从磁盘中获取，于是就产生了'physical reads'。

Physical Reads通常是我们最关心的，如果这个值很高，说明要从磁盘请求大量的数据到Buffer Cache里，通常意味着系统里存在大量全表扫描的SQL语句，这会影响到数据库的性能，因此尽量避免语句做全表扫描，对于全表扫描的SQL语句，建议增 加相关的索引，优化SQL语句来解决。

关于physical reads ，db block gets 和consistent gets这三个参数之间有一个换算公式：

数据缓冲区的使用命中率=1 - ( physical reads / (db block gets + consistent gets) )。

用以下语句可以查看数据缓冲区的命中率：

SQL>SELECT name, value FROM v$sysstat WHERE name IN ('db block gets', 'consistent gets','physical reads');

查询出来的结果Buffer Cache的命中率应该在90％以上，否则需要增加数据缓冲区的大小。

清空Buffer Cache和数据字典缓存

SQL> alter system flush shared_pool;  //请勿随意在生产环境执行此语句  
 
System altered  
 
SQL> alter system flush buffer_cache;  //请勿随意在生产环境执行此语句  
 
System altered  

bytes sent via SQL*Net to client:

    Total number of bytes sent to the client from the foreground processes.

bytes received via SQL*Net from client:

    Total number of bytes received from the client over Oracle Net.

SQL*Net roundtrips to/from client:

Total number of Oracle Net messages sent to and received from the client.

Oracle Net是把Oracle网络粘合起来的粘合剂。它负责处理客户到服务器和服务器到客户通信，

sorts (memory): 在内存里排序。

Number of sort operations that were performed completely in memory and did not require any disk writes

You cannot do much better than memory sorts, except maybe no sorts at all. Sorting is usually caused by selection criteria specifications within table join SQL operations.

Sorts(disk): 在磁盘上排序。

    Number of sort operations that required at least one disk write. Sorts that require I/O to disk are quite resource intensive. Try increasing the size of the initialization parameter SORT_AREA_SIZE.

所有的sort都是优先在memory中做的，当要排序的内容太多，在sort area中放不下的时候，会需要临时表空间，产生sorts(disk)

 

2.5 动态分析

动态统计量收集是Oracle CBO优化器的一种特性。优化器生成执行计划是依据成本cost公式计算出的，如果相关数据表没有收集过统计量，又要使用CBO的机制，就会引起动态采样。

动态采样（dynamic sampling）就是在生成执行计划是，以一个很小的采用率现进行统计量收集。由于采样率低，采样过程快但是不精确，而且采样结果不会进入到数据字典中。

如果在执行计划中有如下提示：

Note
-------------dynamic sampling used for the statement

这提示用户CBO当前使用的技术，需要用户在分析计划时考虑到这些因素。 当出现这个提示，说明当前表使用了动态采样。 我们从而推断这个表可没有做过分析。

这里会出现两种情况：

（1） 如果表没有做过分析，那么CBO可以通过动态采样的方式来获取分析数据，也可以或者正确的执行计划。

（2） 如果表分析过，但是分析信息过旧，这时CBO就不会在使用动态采样，而是使用这些旧的分析数据，从而可能导致错误的执行计划。

 

三、JOIN方式

在多表联合查询的时候，如果我们查看它的执行计划，就会发现里面有多表之间的连接方式。多表之间的连接有三种方式：Nested Loops，Hash Join 和 Sort Merge Join.具体适用哪种类型的连接取决于

• 当前的优化器模式 （ALL_ROWS 和 RULE）
• 取决于表大小
• 取决于连接列是否有索引
• 取决于连接列是否排序

3.1 hash join

Hash join散列连接是CBO 做大数据集连接时的常用方式，优化器使用两个表中较小的表（通常是小一点的那个表或数据源）利用连接键（JOIN KEY）在内存中建立散列表，将列数据存储到hash列表中，然后扫描较大的表，同样对JOIN KEY进行HASH后探测散列表，找出与散列表匹配的行。需要注意的是：如果HASH表太大，无法一次构造在内存中，则分成若干个partition，写入磁盘的temporary segment，则会多一个写的代价，会降低效率。

这种方式适用于较小的表完全可以放于内存中的情况，这样总成本就是访问两个表的成本之和。但是在表很大的情况下并不能完全放入内存，这时优化器会将它分割成若干不同的分区，不能放入内存的部分就把该分区写入磁盘的临时段，此时要有较大的临时段从而尽量提高I/O 的性能。

可以用USE_HASH(table_name1 table_name2)提示来强制使用散列连接。

使用情况：Hash join在两个表的数据量差别很大的时候.

3.2 merge join

Merge Join 是先将关联表的关联列各自做排序，然后从各自的排序表中抽取数据，到另一个排序表中做匹配。

因为merge join需要做更多的排序，所以消耗的资源更多。 通常来讲，能够使用merge join的地方，hash join都可以发挥更好的性能,即散列连接的效果都比排序合并连接要好。然而如果行源已经被排过序，在执行排序合并连接时不需要再排序了，这时排序合并连接的性能会优于散列连接。

可以使用USE_MERGE(table_name1 table_name2)来强制使用排序合并连接.

适用情况：

1.RBO模式

2.不等价关联(>,<,>=,<=,<>)

3.HASH_JOIN_ENABLED=false

4. 用在没有索引，并且数据已经排序的情况.

3.3 nested loop

Nested loops 工作方式是循环从一张表中读取数据(驱动表outer table)，然后访问另一张表（被查找表 inner table,通常有索引）。驱动表中的每一行与inner表中的相应记录JOIN。类似一个嵌套的循环。

对于被连接的数据子集较小的情况，嵌套循环连接是个较好的选择。在嵌套循环中，内表被外表驱动，外表返回的每一行都要在内表中检索找到与它匹配的行，因此整个查询返回的结果集不能太大（大于1 万不适合），要把返回子集较小表的作为外表（CBO 默认外表是驱动表），而且在内表的连接字段上一定要有索引。当然也可以用ORDERED 提示来改变CBO默认的驱动表。

使用USE_NL(table_name1 table_name2)可是强制CBO 执行嵌套循环连接。

适用情况：

适用于驱动表的记录集比较小（<10000）而且inner表需要有有效的访问方法（Index），并且索引选择性较好的时候.

JOIN的顺序很重要，驱动表的记录集一定要小，返回结果集的响应时间是最快的。

四、表访问方式

4.1表访问方式---->全表扫描（Full Table Scans）

全表扫描是指Oracle在访问目标表里的数据时，会从该表所占用的第一个区（EXTENT）的第一个块（BLOCK）开始扫描，一直扫描到该表的高水位线（HWM，High Water Mark），Oracle会对这期间读到的所有数据施加目标SQL的where条件中指定的过滤条件，最后只返回那些满足过滤条件的数据。

不是说全表扫描不好，事实上Oracle在做全表扫描操作时会使用多块读，ORACLE采用一次读入多个数据块 (database block)的方式优化全表扫描，而不是只读取一个数据块，这极大的减少了I/O总次数，提高了系统的吞吐量，所以利用多块读的方法可以十分高效地实现全表扫描。这在目标表的数据量不大时执行效率是非常高的，但全表扫描最大的问题就在于走全表扫描的目标SQL的执行时间会不稳定、不可控，这个执行时间一定会随着目标表数据量的递增而递增。因为随着目标表数据量的递增，它的高水位线会一直不断往上涨，所以全表扫描该表时所需要读取的数据块的数量也会不断增加，这意味着全表扫描该表时所需要耗费的I/O资源会随之不断增加，当然完成对该表的全表扫描操作所需要耗费的时间也会随之增加。

在Oracle中，如果对目标表不停地插入数据，当分配给该表的现有空间不足时高水位线就会向上移动，但如果你用DELETE语句从该表删除数据， 则高水位线并不会随之往下移动（这在某种程度上契合了"高水位线"的定义，就好比水库的水位，当水库涨水时，水位会往上移，当水库放水后，曾经的最高水位 的痕迹还是会清晰可见）。高水位线的这种特性所带来的副作用是，即使使用DELETE语句删光了目标表中的所有数据，高水位线还是会在原来的位置，这意味着全表扫描该表时Oracle还是需要扫描该表高水位线下的所有数据块，所以此时对该表的全表扫描操作所耗费的时间与之前相比并不会有明显的改观。

使用FTS的前提条件：在较大的表上不建议使用全表扫描，除非取出数据的比较多，超过总量的5% -- 10%，或你想使用并行查询功能时。

4.2表访问方式---->通过ROWID访问表（table access by ROWID）

ROWID是一个伪列，即是一个非用户定义的列，而又实际存储于数据库之中。每一个表都有一个ROWID列，一个ROWID值用于唯一确定数据库表中的的一条记录。因此通过ROWID 方式来访问数据也是 Oracle 数据库访问数据的实现方式之一。一般情况下，ROWID方式的访问一定以索引访问或用户指定ROWID作为先决条件，因为所有的索引访问方式最终都会转换为通过ROWID来访问数据记录。(注：index full scan 与index fast full scan除外)由于Oracle ROWID能够直接定位一条记录，因此使用ROWID方式来访问数据，极大提高数据的访问效率

ROWID扫描是指Oracle在访问目标表里的数据时，直接通过数据所在的ROWID去定位并访问这些数据。

从严格意义上来说，Oracle中的ROWID扫描有两层含义：一种是根据用户在SQL语句中输入的ROWID的值直接去访问对应的数据行记录；另外一种是先去访问相关的索引，然后根据访问索引后得到的ROWID再回表去访问对应的数据行记录。

对Oracle中的堆表而言，我们可以通过Oracle内置的ROWID伪列得到对应行记录所在的ROWID的值（注意，这个ROWID只是一个伪 列，在实际的表块中并不存在该列），然后我们还可以通过DBMS_ROWID包中的相关方法（dbms_rowid.rowid_object,dbms_rowid.rowid_relative_fno、dbms_rowid.rowid_block_number和 dbms_rowid.rowid_row_number）将上述ROWID伪列的值翻译成对应数据行的实际物理存储地址。

4.3索引扫描

• 索引范围扫描(INDEX RANGE SCAN)

 索引范围扫描（INDEX RANGE SCAN）适用于所有类型的B树索引，当扫描的对象是唯一性索引时，此时目标SQL的where条件一定是范围查询（谓词条件为 BETWEEN、<、>等）；当扫描的对象是非唯一性索引时，对目标SQL的where条件没有限制（可以是等值查询，也可以是范围查询）。 索引范围扫描的结果可能会返回多条记录，其实这就是索引范围扫描中"范围"二字的本质含义。

一:唯一索引的范围查询

二:非唯一索引的等值查询

三:非唯一索引的范围查询

• 引唯一性扫描(INDEX UNIQUE SCAN)

索引唯一性扫描（INDEX UNIQUE SCAN）是针对唯一性索引（UNIQUE INDEX）的扫描，它仅仅适用于where条件里是等值查询的目标SQL。因为扫描的对象是唯一性索引，所以索引唯一性扫描的结果至多只会返回一条记录。

• 索引全扫描（INDEX FULL SCAN）

所谓的索引全扫描（INDEX FULL SCAN）就是指要扫描目标索引所有叶子块的所有索引行。这里需要注意的是，索引全扫描需要扫描目标索引的所有叶子块，但这并不意味着需要扫描该索引的所有分支块。在默认情况下，Oracle在做索引全扫描时只需要通过访问必要的分支块定位到位于该索引最左边的叶子块的第一行索引行，就可以利用该索引叶子块之间的双向指针链表，从左至右依次顺序扫描该索引所有叶子块的所有索引行了。

• 索引快速扫描(index fast full scan)

索引快速全扫描（INDEX FAST FULL SCAN）和索引全扫描（INDEX  FULL SCAN）极为类似，它也适用于所有类型的B树索引（包括唯一性索引和非唯一性索引）。和索引全扫描一样，索引快速全扫描也需要扫描目标索引所有叶子块的所有索引行。

索引快速全扫描与索引全扫描相比有如下三点区别。

（1）索引快速全扫描只适用于CBO。

（2）索引快速全扫描可以使用多块读，也可以并行执行。

（3）索引快速全扫描的执行结果不一定是有序的。这是因为索引快速全扫描时Oracle是根据索引行在磁盘上的物理存储顺序来扫描，而不是根据索引行的逻辑顺序来扫描的，所以扫描结果才不一定有序（对于单个索引叶子块中的索引行而言，其物理存储顺序和逻辑存储顺序一致；但对于物理存储位置相邻的索引叶子块而言，块与块之间索引行的物理存储顺序则不一定在逻辑上有序）。

• 索引跳跃式扫描（INDEX SKIP SCAN）

索引跳跃式扫描（INDEX SKIP SCAN）适用于所有类型的复合B树索引（包括唯一性索引和非唯一性索引），它使那些在where条件中没有对目标索引的前导列指定查询条件但同时又对该 索引的非前导列指定了查询条件的目标SQL依然可以用上该索引，这就像是在扫描该索引时跳过了它的前导列，直接从该索引的非前导列开始扫描一样（实际的执行过程并非如此），这也是索引跳跃式扫描中"跳跃"（SKIP）一词的含义。

为什么在where条件中没有对目标索引的前导列指定查询条件但Oracle依然可以用上该索引呢？这是因为Oracle帮你对该索引的前导列的所有distinct值做了遍历。