Oracle执行计划分析相关基础知识梳理

• 执行计划概念

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

• Oracle查看执行计划

 a. explain plan for (SQL);
 	select * from table(dbms_xplan.display);
 	--select * from table(dbms_xplan.display()); 
 	--SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE'));
        	
 b.PL/SQL中F5(通过F5查看到的执行计划，其实是pl/sql developer工具内部执行查询 plan_table表然后格式化的结果。)或者专业分析工具TOAD;
 
 c.在sqlplus中先设置autotrace ，再执行查询语句
 示例：
 SET AUTOTRACE TRACEONLY;  -- 只显示执行计划，不显示结果集
 select * from scott.emp a,scott.emp b where a.empno=b.mgr;

• 执行计划中字段解释

  Id: 执行序列，但不是执行的先后顺序。执行的先后根据Operation缩进来判断（采用最右最上最先执行的原则看层次关系，在同一级如果某个动作没有子ID就最先执行。
  一般按缩进长度来判断，缩进最大的最先执行，如果有2行缩进一样，那么就先执行上面的。）
  Operation： 当前操作的内容。
  Name：操作对象
  Rows：也就是10g版本以前的Cardinality(基数)，Oracle估计当前操作的返回结果集行数。
  关于Cardinality（基数）/ rows：
  Cardinality值表示CBO预期从一个行源（row source）返回的记录数，这个行源可能是一个表，一个索引，也可能是一个子查询。
  在Oracle 9i中的执行计划中，Cardinality缩写成Card。
  在10g中，Card值被rows替换。
  注意：Cardinality的值对于CBO做出正确的执行计划来说至关重要
  Bytes：表示执行该步骤后返回的字节数。
  Cost（CPU）：表示执行到该步骤的一个执行成本，用于说明SQL执行的代价。
  Time：Oracle 估计当前操作的时间。
  执行计划中的执行顺序
  Toad工具查看的执行计划。 在Toad 里面，很清楚的显示了执行的顺序
  SQLPLUS中一般按缩进长度来判断，缩进最大的最先执行，如果有2行缩进一样，那么就先执行上面的。

• 执行计划的谓词说明

  –access(“A”.“EMPNO”=“B”.“MGR”)
  –filter(“A”.“EMPNO”=“B”.“MGR”)
  –filter(“B”.“MGR” IS NOT NULL)
  Access: 表示这个谓词条件的值将会影响数据的访问路径（全表扫描还是索引）。
  Filter：表示谓词条件的值不会影响数据的访问路劲，只起过滤的作用。
  注意：在谓词中主要注意access，要考虑谓词的条件，使用的访问路径是否正确；
  解释计划不考虑绑定变量的数据类型并假设所有的绑定变量都是字符串类型的方式。
  访问谓语要么进行索引运算，要么进行联结运算
  对于解释计划来说，数据类型被认为都是一样的。然而，当语句真正执行时所准备的执行计划却要考虑数据类型。
  谓语必须严格匹配索引定义，否则将不会使用索引。

• oracle访问数据（访问表）的存取方法

1. 全表扫描 (FTS,Full Table Scans)
2. 通过rowId的表的存取（rowid lookup）
   –Rowid扫描是最快的访问数据方式 通过rowId的表的存取（rowid lookup）
3. 索引扫描 INDEX SCAN(INDEX LOOKUP)
   索引唯一扫描（index unique scan）
   索引范围扫描（index range scan）–索引局部扫描
   索引全扫描（index full scan）
   索引快速全局扫描（index fast full scan）–扫描索引中的所有块，Rows不按排序顺序返回，不带order by情况下常发生
   索引跳跃扫描（index skip scan） – where条件列是非索引的前导列情况下常发生

• 表连接

  排序合并连接（排序归并）（SMJ, sort merge join）
  嵌套循环连接（NL,Nested Loops）
  哈希连接（散列连接）（HJ,Hash Join）
  笛卡尔乘积（星型连接）

• 运算符
  sort --排序，很消耗资源
  filter --过滤，如not in、min函数等容易产生
  view --视图，大都由内联视图产生(可能深入到视图基表)
  partition view --分区视图

• 统计信息说明：

  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语句来解决。

  关于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％以上，否则需要增加数据缓冲区的大小
  

• Oracle的优化器（Optimizer）共有3种模式：
  RULE (基于规则)–RBO(Rule-Based Optimization)，Oracle 10g开始，RBO 已经被弃用,可以通过Hint 方式来使用它；
  COST(基于成本)–CBO(Cost-Based Optimization)；
  CHOOSE(基于选择)；

参考
https://blog.csdn.net/lifetragedy/article/details/51320192
http://dreamoftch.iteye.com/blog/1805899