Sql优化（四） oracle优化器(optimizer)介绍

对于一句sql，oracle可以有不同的执行方法，例如full table scan或者走索引，nested loop join
或者hash join等等。Oracle的优化器负责选择合适的执行计划。
1. 什么是优化器：
当oracle对Sql进行解析时，优化器用来决定sql的执行计划(execute plan).
2. RBO和CBO
RBO(rule based optimizer)是基于规则的优化器，即优化器在分析SQL语句时,所遵循的是Oracle内部预定的一些规则，如主键优先于普通索引;
CBO(cost based optimizer)是基于代价的，这里的代价主要指Cpu和内存。优化器在判断是否用这种方式时,主要参照表的统计信息（通过analyze得到）
在Oracle8及以后的版本,Oracle推荐用CBO的方式；10g以后则只能使用CBO[@more@]

3. ORACLE各版本optimizer_mode

4. 如何选择optimizer mode
在oracle 10g/11g中，优化目标为最大吞吐量或最快响应时间。当然很多时候不同优化目标产生的执行计划是相同的，优化目标打个比喻好比你打车时告诉驾驶员选择最快还是最省钱的路线。

OLTP操作，优化器目标应该是最快响应时间，后台报表，目标则是最大吞吐量。
1) First_rows还是first_rows_n?
感觉两者的区别不是很大，该如何选择呢？看看oracle下面的说明：
FIRST_ROWS_n The optimizer uses a cost-based approach, regardless of the presence of statistics, and optimizes with a goal of best response time to return the first n number of rows, where n equals 1, 10, 100, or 1000.
FIRST_ROWS The optimizer uses a mix of cost and heuristics to find a best plan for fast delivery of the first few rows.
Note that using heuristics sometimes leads the optimizer to generate a plan with a cost that is significantly larger than the cost of a plan without applying the heuristic. FIRST_ROWS is available for backward compatibility and plan stability; use FIRST_ROWS_n instead.
提到了first_rows两个缺点：
有时可能错误选择性能开销大的
Oracle保留这个参数是为了向后兼容（特别是升级时设此参数）
这说明oracle是推荐用first_rows_n的。那就简单了，听话就是了。
2) ALL_ROWS还是first_rows_n?
可以借鉴oracle自己的CRM系统siebel中的设置方法：
系统缺省： ALL_ROWS
Siebel应用中设置alter session set optimizer_mode=first_rows_10
这就兼顾了siebel系统中兼有oltp和批处理应用的情况。

批处理应该用ALL_ROWS，前台需要快速返回的应该用first_rows_n。 ORACLE缺省设置是ALL_ROWS，这是有道理的，因为系统整体吞吐量较高。因此个人认为除了需要响应时间快的应用要设置first_rows_n（n值的大小根据应用特征来设），其他都可以使用默认设置。

5. optimizer mode设置方法
初始化参数 optimizer_mode
Session级别：alter session set optimizer_mode=
语句级别,使用hint： select /*+FIRST_ROWS */ from …
6. 关于统计信息
8i/9i中，optimizer_mode=CHOOSE,当表上面缺统计信息，则会使用RBO。需要手工进行统计信息收集
10g/11g中，当表上面缺统计信息时，oracle在分析sql时会根据optimizer_dynamic_sampling（default 2）动态取样，这会导致sql的分析变慢。因此保持统计信息准确对于优化器至关重要。
10g/11g中，oracle通过自动作业收集统计信息（当数据变化量超过10%，则触发）
统计信息包括表、索引的物理特征，包括数据量、数据分布等。当字段存在大量重复值（skewed data），则应收集histograms
7. 访问路径和关联方法
根据优化目标，优化器决定sql的执行计划。执行计划的主要内容是：
1) 访问路径(access path)，就是访问数据的方式，包括：
Full table scan
Rowed scan(很少)
Index scan
Cluster scan
Hash scan
Sample table scan
如：select count(*)*100 from ××× sample(1);
2) 关联方法
详见《sql优化(二) 关联》一文中的介绍

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/18474/viewspace-1060730/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/18474/viewspace-1060730/