RBO CBO 优化器 Oracle性能调优

部分内容引自 Oracle优化器的优化方式和优化模式-性能调优
http://www.cnblogs.com/sopost/archive/2010/12/21/2190066.html

书 <<收获，不止Oracle>> 作者梁敬彬。

以前做过生产运行环境慢的问题定位与调优，记忆中影响最大的部分在Oracle数据库。
Oracle的种种问题会导致应用产生性能问题：
数据库服务器操作系统的版本问题(32位或者64位等),
oracle的版本问题(32位或者64位、10g、11g等),
数据表的关键列索引没建,
表空间不足了,
数据库服务器IP地址冲突了,
数据库表设计不合理，
DBA没有建好rac等等。

这篇文章介绍一下Oracle优化器方面的知识，可能只与上面所列条目的索引建立有关系。

索引的概念应用广泛：图书馆图书编目、编码，电视节目列表，文件系统的树形结构编目，全文搜索与搜索引擎也通过建立索引提高检索效率等等。
Oracle索引就是对数据库表中一列或多列的值进行排序的一种结构。
Oracle索引有三种类型： B-Tree索引，位图索引，函数索引。
通常应用较多的是B-Tree索引。

按照常人的理解，当只需要检索数据表中少量数据时，应用索引是可以提高速度的（比如修改某一条记录，查询某几条记录）；但是当要检索大量数据时（比如从100万记录中查询出30万条），索引应该不起什么作用。

对应现实阅读书籍的例子来理解就容易了：当只要查看书籍中某个理论的内容时翻看一下目录，找到具体页数，然后去阅读，非常方便；但是当一本书的很多内容都有吸引力时，我最可能会从头到尾阅读一遍。

当然现实生活中阅读书籍的模式并不只是这两种，如：我对书籍中很多概念都有兴趣，我可能会翻看目录来一一检索查看。

Oracle数据库也有同样的问题：虽然索引号称比较快，但是索引实际的检索效率可能不如别的检索方式。那么Oracle是如何处理这种种情况的呢？
Oracle优化器就是用来权衡检索效率的。

Oracle优化器(Optimizer)主要工作是分析语句的执行计划。

Oracle优化器有两种优化方式： RBO(Rule-Based Optimization) 基于规则的优化, CBO(Cost-Based Optimization)基于代价的优化。
当采用RBO方式时:优化器在分析SQL语句时遵循Oracle内部预定的一些规则，如where 字句中某条件列有索引则走索引等。
当采用CBO方式时：优化器在分析SQL语句参照的是表及索引的统计信息。

Oracle优化器的模式有四种：Rule, Choose, First Rows, All Rows。
Rule既是走RBO方式。
Choose是指当一个表或索引有统计信息则走CBO方式,反之并且表不是特别小且有索引则走RBO方式。Choose模式是Oracle的默认优化器模式。
First Rows与Choose方式是类似的,所不同的是当一个表有统计信息时,它将是以最快的方式返回查询的最先的几行,从总体上减少了响应时间。
All Rows:也就是我们所说的Cost方式,当一个表有统计信息时,它将以最快的方式返回表的所有的行,从总体上提高查询的吞吐量。没有统计信息则走基于规则的方式。


优化器模式的设定：
A、Instance级别
我们可以通过在init.ora文件中设定OPTIMIZER_MODE=RULE、OPTIMIZER_MODE=CHOOSE、OPTIMIZER_MODE=FIRST_ROWS、OPTIMIZER_MODE=ALL_ROWS去选用3所提的四种方式,如果你没设定OPTIMIZER_MODE参数则默认用的是Choose这种方式。


B、Sessions级别
通过SQL> ALTER SESSION SET OPTIMIZER_MODE=?;来设定。


C、语句级别
可能需要用到Hint。


本文要说明的核心思想是： Oracle并不认为SQL语句走索引就是最优的，这和现实生活中阅读书籍有异曲同工之妙。

但是话又说回来：对于普通的应用，表的索引添加是必须的。因为如果表没有索引，Oracle优化器就陷入巧妇难为无米之炊的境地。