Oracle执行计划

执行计划

有时候我们要分析某条SQL的性能问题，通常做法是先看执行计划。通过执行计划看 SQL 执行的瓶颈，比如内存消耗、行源和时间。因此弄懂执行计划也就成了SQL优化的必备技能。

在Oracle 中有三种方式查看执行计划。

autotrace

这种方式在 PLSQL Developer 中很常见，而且提供了多种参数选择。如下：

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

例如

SQL>SET autotrace ON

SQL>SELECT * FROM dual ;

此时Oracle 就会显示该语句的执行计划和统计信息了。

SQL

使用SQL 语句也很简单，这个在SQL Developer中用起来很方便。另外它也不需要等待SQL 的执行结果，显示结果很快。如下

EXPLAIN PLAN FOR
SELECT * FROM dual ;

然后用下面两条语句中任意一条都可以将执行计划显示出来。

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

-- or

SELECT * FROM TABLE(dbms_xplan.display);

SQL Developer

通过 SQL Developer 的按钮可以直接查看刚刚执行过的SQL的执行计划。

说明

下面用个例子介绍下执行计划。先看一个执行计划。

Plan hash value: 2763130098
 
------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                            |     1 |   211 |  1128   (1)| 00:00:14 |
|   1 |  NESTED LOOPS                 |                            |     1 |   211 |  1128   (1)| 00:00:14 |
|   2 |   NESTED LOOPS                |                            |     1 |   211 |  1128   (1)| 00:00:14 |
|   3 |    NESTED LOOPS               |                            |     1 |    51 |  1125   (1)| 00:00:14 |
|   4 |     NESTED LOOPS              |                            |     1 |    45 |  1124   (1)| 00:00:14 |
|*  5 |      TABLE ACCESS STORAGE FULL| SPS_RAIL_BILL_REQUEST      |     1 |    34 |  1123   (1)| 00:00:14 |
|*  6 |      INDEX RANGE SCAN         | SPS_RBREQUEST_ORDER_REL_PK |     1 |    11 |     1   (0)| 00:00:01 |
|*  7 |     INDEX UNIQUE SCAN         | SPS_ORDER_PK               |     1 |     6 |     1   (0)| 00:00:01 |
|*  8 |    INDEX RANGE SCAN           | SPS_EXE_PLAN_FKDX7         |     1 |       |     2   (0)| 00:00:01 |
|   9 |   TABLE ACCESS BY INDEX ROWID | SPS_EXE_PLAN               |     1 |   160 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   5 - storage("REQ"."REQUEST_NUMBER"='cfcef510-d8f9-41d8-b074-dff060f1b05a')
       filter("REQ"."REQUEST_NUMBER"='cfcef510-d8f9-41d8-b074-dff060f1b05a')
   6 - access("REQ"."OID"="REL"."RAIL_BILL_REQUEST_ID")
   7 - access("REL"."ORDER_ID"="SR"."ORDER_ID")
   8 - access("SR"."ORDER_ID"="EP"."ORDER_ID")

字段

这里解释下执行计划中的几个字段：

字段	说明
ID	一个序号，但不是执行的先后顺序。执行的先后根据缩进来判断
Operation	当前操作的内容
Rows	当前操作的Cardinality，Oracle估计当前操作的返回结果集
Cost（CPU）	计算出来的一个数值（代价），用于说明SQL执行的代价
Time	估计当前操作的时间

执行顺序

执行计划给每行操作一个序号，每行代表一个操作步骤，但它的的执行顺序与此无关。那执行计划的执行顺序是怎样的呢？

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

根据这个规则，上面的执行顺序就是：

5 => 6 => 4 => 7 => 3 => 8 => 2 => 9 => 1 => 0

这里为了理解，我们可以把整个执行计划想象成一棵二叉树。上面的执行计划就变成了下面这样。

可以发现，执行计划可以转化成一个只有左子树的二叉树。而且它的执行顺序就是二叉树的后序遍历。

Rows

这里 Rows 是个很重要的信息。它表示CBO预期从一个行源（row source）返回的记录数。另外，它还决定用什么样的访问方式来做表关联（Nested loops Join， hash Join等）。

谓词

Access: 表示这个谓词条件的值将会影响数据的访问路劲（表还是索引）。
Filter：表示谓词条件的值不会影响数据的访问路劲，只起过滤的作用。

统计信息

这里先解释下统计信息里常用的几个信息

db block gets	从buffer cache中读取的block的数量
consistent gets	从buffer cache中读取的undo数据的block的数量
physical reads	从磁盘读取的block的数量
redo size	DML生成的redo的大小
sorts (memory)	在内存执行的排序量
sorts (disk)	在磁盘上执行的排序量

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

这些信息可以通过SQL 查看。

SELECT NAME, VALUE FROM v$sysstat WHERE NAME IN ('db block gets', 'consistent gets','physical reads');

数据缓冲区命中率

这里简单提下数据缓冲区命中率的概念。其公式计算如下

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

用下面语句可以查询下我们的数据缓冲区的命中率：

SELECT NAME, PHYSICAL_READS, DB_BLOCK_GETS, CONSISTENT_GETS,
1 - (PHYSICAL_READS / (DB_BLOCK_GETS + CONSISTENT_GETS)) "Hit Ratio"
FROM V$BUFFER_POOL_STATISTICS
WHERE NAME='DEFAULT';

一般情况想，数据缓冲区的命中率应该要保持 90% 以上，否则就要增加数据缓冲区的大小了。