oracle优化器简介

一、oracle优化器模式  
ORACLE的优化器共有3种:
   a.  RULE (基于规则)   b. COST (基于成本)  c. CHOOSE (选择性)
   为了使用基于成本的优化器(CBO, Cost-Based Optimizer) , 你必须定期更新统计信息，以保证数据库中的对象统计信息(object statistics)的准确性.
   如果数据库的优化器模式设置为选择性(CHOOSE),那么实际的优化器模式将和是否运行过analyze命令有关. 如果table已经被analyze过, 优化器模式将自动成为CBO , 反之,数据库将采用RULE形式的优化器。

二．访问Table的方式
ORACLE 采用两种访问表中记录的方式:
a.  全表扫描
      全表扫描就是顺序地访问表中每条记录. ORACLE采用一次读入多个数 据块(database block)的方式优化全表扫描。
   
b.  索引扫描
   你可以采用基于ROWID的访问方式情况,提高访问表的效率, ROWID包含了表中记录的物理位置信息.ORACLE采用索引(INDEX)实现了数据和存放数据的物理位置(ROWID)之间的联系. 通常索引提供了快速访问ROWID的方法,因此那些基于索引列的查询就可以得到性能上的提高.

其中ORACLE对索引又有两种访问模式.
a)索引唯一扫描 ( INDEX UNIQUE SCAN)
大多数情况下, 优化器通过WHERE子句访问INDEX.
例如:
表LOADING有两个索引 : 建立在LOADING列上的唯一性索引LOADING_PK和建立在MANAGER列上的非唯一性索引IDX_MANAGER.
SELECT loading 
FROM LOADING
WHERE LOADING = ‘ROSE HILL’;
   在内部 , 上述SQL将被分成两步执行, 首先 , LOADING_PK 索引将通过索引唯一扫描的方式被访问 , 获得相对应的ROWID, 通过ROWID访问表的方式执行下一步检索.
   如果被检索返回的列包括在INDEX列中,ORACLE将不执行第二步的处理(通过ROWID访问表). 因为检索数据保存在索引中, 单单访问索引就可以完全满足查询结果.
   下面SQL只需要INDEX UNIQUE SCAN 操作.
       SELECT LOADING
       FROM  LOADING
WHERE LOADING = ‘ROSE HILL’;
 
  b)索引范围查询(INDEX RANGE SCAN)
     适用于两种情况:
1. 基于一个范围的检索
2. 基于非唯一性索引的检索
 例1:
      SELECT LOADING
      FROM  LOADING
WHERE LOADING LIKE ‘M%’;
 
WHERE子句条件包括一系列值, ORACLE将通过索引范围查询的方式查询LODGING_PK . 由于索引范围查询将返回一组值, 它的效率就要比索引唯一扫描
低一些. 
例2:
      SELECT LOADING
      FROM  LOADING
WHERE MANAGER = ‘BILL GATES’;
这个SQL的执行分两步, IDX_MANAGER的索引范围查询(得到所有符合条件记录的ROWID) 和下一步同过ROWID访问表得到LOADING列的值. 由于IDX_MANAGER是一个非唯一性的索引,数据库不能对它执行索引唯一扫描.
 
  由于SQL返回LOADING列,而它并不存在于IDX_MANAGER索引中, 所以在索引范围查询后会执行一个通过ROWID访问表的操作.
  WHERE子句中, 如果索引列所对应的值的第一个字符由通配符(WILDCARD)开始, 索引将不被采用.
SELECT LOADING
      FROM  LOADING
WHERE MANAGER LIKE ‘％HANMAN’;
在这种情况下，ORACLE将使用全表扫描.

三．SQL调优的本质就是调整执行计划。
在好多情况下，oracle自动选择的执行计划并不是最优的，这时需要我们人工去干预。(什么是执行计划?)

对SQL调优基本步骤：
a) 捕获SQL语句
b) 产生SQL语句的执行计划；
c) 验证统计信息(SQL语句涉及到的表格是否做过分析)，表格信息(结果集的记录数，索引)，字段上面数据分布特点
d) 通过手工收集到的信息，形成自己理想的执行计划。
e) 如果做过分析，则重新分析相关表格或者做柱状图分析。
f) 如果没有做过分析，则通过尝试不同的Hint，从而获得合适的执行计划。
g) 当我们正常无法调优到位时，可以打开10053事件打开优化器的跟踪，看看Oracle如何选择的.
alter session set events='10053 trace name context forever,level 2';

四．如何捕获SQL语句
捕获SQL语句的方法有如下几种：
1．SQL TRACE或10046跟踪某