oracle执行计划分析



基于oracle的应用系统很多性能问题，是由应用系统sql性能低劣引起的，所以，sql的性能优化很重要，分析与优化sql的性能一般通过查看该sql的执行计划。

一、什么是执行计划

      一条查询语句在oracle中的执行过程或访问路径的描述。

二、如何查看执行计划

       1、set autotrace on;

       2、explain plan for sql语句;

            select plan_table_output from table(dbms_xplan.display());

      3、通过第3方工具，如plsql developer（f5查看执行计划）、toad等
三、执行计划详细分析

SQL> explain plan for select * from scott.emp a,scott.emp b where a.empno=b.mgr and b.mgr is not null; Explained SQL> select plan_table_output from table(dbms_xplan.display()); PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------------| Id  | Operation                    |  Name       | Rows  | Bytes | Cost  |----------------------------------------------------------------------------|   0 | SELECT STATEMENT             |             |       |       |       ||   1 |  NESTED LOOPS                |             |       |       |       ||*  2 |   TABLE ACCESS FULL          | EMP         |       |       |       ||   3 |   TABLE ACCESS BY INDEX ROWID| EMP         |       |       |       ||*  4 |    INDEX UNIQUE SCAN         | PK_EMP      |       |       |       |----------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - filter("B"."MGR" IS NOT NULL)   4 - access("A"."EMPNO"="B"."MGR")Note: rule based optimization 18 rows selected

四、计划列字段解释：

1、Id: 执行序列，但不是执行的先后顺序。执行的先后根据Operation缩进来判断（采用最右最上最先执行的原则看层次关系，在同一级如果某个动作没有子ID就最先执行。 一般按缩进长度来判断，缩进最大的最先执行，如果有2行缩进一样，那么就先执行上面的。）
如：上面执行计划的执行顺序为：4-->2-->3-->1-->0
2、Operation： 当前操作的内容。
3、Name：操作对象
4、Rows：也就是10g版本以前的Cardinality(基数)，Oracle估计当前操作的返回结果集行数。
--Rows的值对于CBO做出正确的执行计划来说至关重要。 如果CBO获得的Rows值不够准确（通常是没有做分析或者分析数据过旧造成），在执行计划成本计算上就会出现偏差，从而导致CBO错误的制定出执行计划。在多表关联查询或者SQL中有子查询时，每个关联表或子查询的Rows的值对主查询的影响都非常大，甚至可以说，CBO就是依赖于各个关联表或者子查询Rows值计算出最后的执行计划。 对于多表查询，CBO使用每个关联表返回的行数（Rows）决定用什么样的访问方式来做表关联（如Nested loops Join 或 hash Join）。
5、Bytes：表示执行该步骤后返回的字节数。
6、Cost（CPU）：表示执行到该步骤的一个执行成本，用于说明SQL执行的代价。
7、Time：Oracle 估计当前操作的时间。

五、谓词说明：

Predicate Information (identified by operation id):---------------------------------------------------   2 - filter("B"."MGR" IS NOT NULL)   4 - access("A"."EMPNO"="B"."MGR")

Access: 表示这个谓词条件的值将会影响数据的访问路劲（全表扫描还是索引）
Filter：表示谓词条件的值不会影响数据的访问路劲，只起过滤的作用。
在谓词中主要注意access，要考虑谓词的条件，使用的访问路径是否正确。

六、统计信息说明：

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

七、 动态分析

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

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

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

这里会出现两种情况：

1、如果表没有做过分析，那么CBO可以通过动态采样的方式来获取分析数据，也可以或者正确的执行计划。
2、如果表分析过，但是分析信息过旧，这时CBO就不会在使用动态采样，而是使用这些旧的分析数据，从而可能导致错误的执行计划。

八、表访问方式
1、Full Table Scan (FTS) 全表扫描
2、Index Lookup 索引扫描，共分五小类

 2.1、index unique scan     --索引唯一扫描
 2.2、index range scan      --索引局部扫描
 2.3、index full scan       --索引全局扫描
 2.4、index fast full scan  --索引快速全局扫描，不带order by情况下常发生
 2.5、index skip scan       --索引跳跃扫描，where条件列是非索引的前导列情况下常发生

3、Rowid 物理ID扫描，Rowid扫描是最快的访问数据方式

九、运算符
1、sort --排序，很消耗资源

如下几个操作会引发排序运算
a、order by clauses b、group by c、sort merge join

2、filter --过滤，如not in、min函数等容易产生

3.view --视图，大都由内联视图产生(可能深入到视图基表)

4.partition view --分区视图

十、oracle优化器（Optimizer）

Oracle 数据库中优化器（Optimizer）是SQL分析和执行的优化工具，它负责指定SQL的执行计划，也就是它负责保证SQL执行的效率最高，比如优化器决定Oracle 以什么样的方式来访问数据，是全表扫描（Full Table Scan），索引范围扫描（Index Range Scan）还是全索引快速扫描（INDEX Fast Full Scan：INDEX_FFS）;对于表关联查询，它负责确定表之间以一种什么方式来关联，比如HASH_JOHN还是NESTED LOOPS 或者MERGE JOIN。 这些因素直接决定SQL的执行效率，所以优化器是SQL 执行的核心，它做出的执行计划好坏，直接决定着SQL的执行效率。

Oracle 的优化器有两种：

1、RBO(Rule-Based Optimization): 基于规则的优化器
2、CBO(Cost-Based Optimization): 基于代价的优化器

从Oracle 10g开始，RBO 已经被弃用，但是我们依然可以通过Hint方式来使用它。

在Oracle 10g中，CBO 可选的运行模式有2种：
1、FIRST_ROWS(n)
Oracle 在执行SQL时，优先考虑将结果集中的前n条记录以最快的速度反馈回来，而其他的结果并不需要同时返回。
2、ALL_ROWS -- 10g中的默认值
Oracle 会用最快的速度将SQL执行完毕，将结果集全部返回，它和FIRST_ROWS（n）的区别在于，ALL_ROWS强调以最快的速度将SQL执行完毕，并将所有的结果集反馈回来，而FIRST_ROWS（n）则侧重于返回前n条记录的执行时间。

修改CBO 模式的三种方法：

1、SQL 语句：

Sessions级别：

SQL> alter session set optimizer_mode=all_rows;

2、修改pfile 参数：

OPTIMIZER_MODE=RULE/CHOOSE/FIRST_ROWS/ALL_ROWS

3、语句级别用Hint（/* + ... */）来设定

Select /*+ first_rows(10) */ name from table;Select /*+ all_rows */ name from table;