Question

讲解Oracle优化器的优化方式和优化模式

Answer 1

　　Oracle在执行一个SQL之前 首先要分析一下语句的执行计划 然后再按执行计划去执行 分析语句的执行计划的工作是由优化器(Optimizer)来完成的 不同的情况 一条SQL可能有多种执行计划 但在某一时点 一定只有一种执行计划是最优的 花费时间是最少的 相信你一定会用Pl/sql Developer Toad等工具去看一个语句的执行计划 不过你可能对Rule Choose First rows All rows这几项有疑问 因为我当初也是这样的 那时我也疑惑为什么选了以上的不同的项 执行计划就变了?

　　 优化器的优化方式

　　

　　Oracle的优化器共有两种的优化方式 即基于规则的优化方式(Rule Based Optimization 简称为RBO)和基于代价的优化方式(Cost Based Optimization 简称为CBO)

　　

　　A RBO方式 优化器在分析SQL语句时 所遵循的是Oracle内部预定的一些规则 比如我们常见的 当一个where子句中的一列有索引时去走索引

　　

　　B CBO方式 依词义可知 它是看语句的代价(Cost)了 这里的代价主要指Cpu和内存 优化器在判断是否用这种方式时 主要参照的是表及索引的统计信息 统计信息给出表的大小 有少行 每行的长度等信息 这些统计信息起初在库内是没有的 是你在做 *** yze后才出现的 很多的时侯过期统计信息会令优化器做出一个错误的执行计划 因些我们应及时更新这些信息 在Oracle 及以后的版本 Oracle列推荐用CBO的方式

　　

　　我们要明了 不一定走索引就是优的 比如一个表只有两行数据 一次IO就可以完成全表的检索 而此时走索引时则需要两次IO 这时对这个表做全表扫描(full table scan)是最好的

　　

　　 优化器的优化模式(Optermizer Mode)

　　

　　优化模式包括Rule Choose First rows All rows这四种方式 也就是我们以上所提及的 如下我解释一下

　　

　　Rule:不用多说 即走基于规则的方式

　　

　　Choolse:这是我们应观注的 默认的情况下Oracle用的便是这种方式 指的是当一个表或或索引有统计信息 则走CBO的方式 如果表或索引没统计信息 表又不是特别的小 而且相应的列有索引时 那么就走索引 走RBO的方式

　　

　　First Rows:它与Choose方式是类似的 所不同的是当一个表有统计信息时 它将是以最快的方式返回查询的最先的几行 从总体上减少了响应时间

　　

　　All Rows:也就是我们所说的Cost的方式 当一个表有统计信息时 它将以最快的方式返回表的所有的行 从总体上提高查询的吞吐量 没有统计信息则走基于规则的方式

　　

　　 如何设定选用哪种优化模式

　　◆A Instance级别

　　

　　我们可以通过在init ora文件中设定OPTIMIZER_MODE=RULE OPTIMIZER_MODE=CHOOSE OPTIMIZER_MODE=FIRST_ROWS OPTIMIZER_MODE=ALL_ROWS去选用 所提的四种方式 如果你没设定OPTIMIZER_MODE参数则默认用的是Choose这种方式

　　

　　◆B Sessions级别

　　

　　通过SQL> ALTER SESSION SET OPTIMIZER_MODE= ;来设定

　　

　　◆C 语句级别

　　

　　这些需要用到Hint 比如:

　　SQL> SELECT /*+ RULE */ a userid b name b depart_name FROM tf_f_yhda a tf_f_depart b WHERE a userid=b userid;

  为什么有时一个表的某个字段明明有索引 当观察一些语的执行计划确不走索引呢？如何解决呢？

　　

　　◆A 不走索引大体有以下几个原因

　　

　　你在Instance级别所用的是all_rows的方式

　　

　　你的表的统计信息(最可能的原因)

　　

　　你的表很小 上文提到过的 Oracle的优化器认为不值得走索引

　　

　　◆B 解决方法

　　

　　可以修改init ora中的OPTIMIZER_MODE这个参数 把它改为Rule或Choose 重起数据库 也可以使用 中所提的Hint

　　

　　删除统计信息

　　

　　SQL> *** yze table table_name delete statistics;

　　表小不走索引是对的 不用调的

　　

　　 其它相关

　　

　　◆A 如何看一个表或索引是否是统计信息

　　

　　SQL>SELECT * FROM user_tables WHERE table_name=<table_name> AND num_rows is not null; SQL>SELECT * FROM user_indexes WHERE table_name=<table_name> AND num_rows is not null;

　　◆B 假如我们先用CBO的方式 就应当及时去更新表和索引的统计信息 以免生形不切合实的执行计划

　　

　　SQL> ANALYZE TABLE table_name PUTE STATISTICS; SQL> ANALYZE INDEX index_name ESTIMATE STATISTICS;

lishixinzhi/Article/program/Oracle/201311/18622