当SQL语句进入Oracle的库缓存后，在该语句准备执行之前，将执行下列步骤：
1) 语法检查：检查SQL语句拼写是否正确和词序
2) 语义分析：核实所有的与数据字典不一致的表和列的名字
3) 轮廓存储检查：检查数据字典，以确定改SQL语句的轮廓是否已经存在
4) 生成执行计划：使用CBO的优化规则和数据字典中的统计表来决定最佳执行计划
5) 建立二进制代码：基于执行计划，Oracle生成二进制代码
第一二步大部分编译器编译都需要的，第三步就是取决于SQL语句的命中率，这块我们可以进行优化，第四步就是取决于Oracle的优化器了，我们可以对其更改，第五步不可更改优化。下面介绍优化器的一些基本知识。
一、优化器的优化方式Oracle的优化器共有两种的优化方式,即基于规则的优化方式(Rule-Based Optimization,简称为RBO)和基于代价的优化方式(Cost-Based Optimization,简称为CBO)。在Oracle8及以后的版本,Oracle列推荐用CBO的方式。
1、RBO(基于规则的优化器,Based-Rule Optimizer)优化器在分析SQL语句时,所遵循的是Oracle内部预定的一些规则。比如我们常见的,当一个where子句中的一列有索引时去走索引。
2、CBO(基于成本的优化器,Based-Cost Optimizer)CBO,以最佳吞吐量为目标，主要看语句的代价(Cost)，这里的Cost主要指CPU和内存。优化器在判断是否使用这种方式时，主要参照的是表及索引的统计信息。统计信息给出表的大小、有少行、每行的长度信息。这些统计信息起初在库内是没有的，是做了Analyze后才出现的，很多时候过期统计信息会令优化器做出一个错误的执行计划，因此应及时更新这些信息。
不一定走索引就是优的,比如一个表只有两行数据,一次IO就可以完成全表的检索,而此时走索引时则需要两次IO,这时对这个表做全表扫描(full table scan)是最好的。
二、优化器的优化模式(Optimizer Mode)： 优化模式包括Rule,Choose,First rows,All rows这四种方式：
1.Rule: 不论是否有统计信息存在，优化器都会使用基于规则的方法。
2.Choose: 此为缺省值。优化器既可以使用基于成本的优化方法(CBO)，也可以使用基于规则的优化方法(RBO)，其决定于是否有可用的统计信息，规则如下：
1)如果在被访问的表中，至少有一个表在数据字典中有可用的统计信息存在，则优化器使用基于成本的方法。
2)如果在被访问的表中，只有部分表在数据字典中有可用的统计信息，优化器仍然会使用基于成本的方法，但是优化器必须为无统计信息的表利用一些内部信息去尝试其他的统计，比如分配给这些表的数据块的数量 ，这可能会导致产生不理想的执行计划。
3)如果在被访问的表中，没有一个表在数据字典中有统计信息，则优化器使用基于规则的方法。
3.First Rows:它与Choose方式是类似的,所不同的是当一个表有统计信息时,它将是以最快速度返回前几行数据集， 从总体上减少了响应时间。
4.All Rows: 不论是否有统计信息存在，优化器都使用基于成本的方法，并以最佳吞吐量为优化目标。
三、如何设定选用哪种优化模式 1、Instance级别我们可以通过在init<SID>.ora文件中设定
OPTIMIZER_MODE=RULE、
OPTIMIZER_MODE=CHOOSE、
OPTIMIZER_MODE=FIRST_ROWS、
OPTIMIZER_MODE=ALL_ROWS
去选用二所提的四种方式,如果你没设定OPTIMIZER_MODE参数则默认用的是Choose这种方式。
2、Sessions级别通过ALTER SESSION SET OPTIMIZER_MODE=<Mode>;来设定。
3、语句级别 这些需要用到Hint。例如
SELECT /*+ RULE */ EMP_NO,EMP_NAM,DAT_IN
FROM EMP
WHERE EMP_NO='SCOTT';
四、问题1.为什么有时一个表的某个字段明明有索引,当观察一些语的执行计划确不走索引呢？如何解决呢？
1)不走索引大体有以下几个原因：
11)、你在Instance级别所用的是all_rows的方式
12)、你的表的统计信息(最可能的原因)
13)、你的表很小,上文提到过的,Oracle的优化器认为不值得走索引
2)解决方法：
21)、可以修改init<SID>.ora中的OPTIMIZER_MODE这个参数,把它改为Rule或Choose,重起数据库。也可以使用4中所提的Hint.
22) 、删除统计信息：
analyze table table_name delete statistics;
23)、表小不走索引是对的,不用调的。
五、相关A、如何看一个表或索引是否是统计信息
SELECT *
FROM user_tables
WHERE table_name=<table_name>
AND num_rows is not null;
SELECT *
FROM user_indexes
WHERE table_name=<table_name>
AND num_rows is not null;
B、如果我们先用CBO的方式,我们应及时去更新表和索引的统计信息,以免生形不切合实的执行计划：
ANALYZE TABLE table_name COMPUTE STATISTICS;
ANALYZE INDEX index_name ESTIMATE STATISTICS;
最新评论