原文地址：http://space.itpub.net/17203031/viewspace-705012
鸣谢：感谢博主"求道的路上"的技术分享！
原文摘录：
Oracle query optimizer（查询优化器）是我们接触最多的一个数据库组件。查询优化器最主要的工作就是接受输入的SQL以及各种环境参数、配置参数，生成合适的SQL执行计划（Execution Plan）。
Query Optimizer一共经历了两个历史阶段：RBO和CBO。RBO时代，Oracle执行计划是通过一系列固化的规则进行执行计划生成。而CBO时代，则是利用系统统计量进行各种执行路径试算，获取相对相对成本最低的执行计划。
进入Oracle 10g之后，Query Optimizer就已经将CBO作为默认优化器，并且Oracle官方不再支持RBO服务。但是，通过优化器参数optimizer_mode，我们可以控制Oracle优化器生成不同模式下的执行计划。本篇通过实验，来验证Oracle 10g下，不同optimizer_mode的取值效应，以及和统计量关系。
1、实验环境准备
我们选择Oracle 10gR2作为基础实验环境。
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 – Production
在Oracle 10gR2下，参数optimizer_mode取值为ALL_ROWS。
SQL> show parameter optimizer_mode
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode string ALL_ROWS
SQL> select name, value from v$parameter where name='optimizer_mode';
NAME VALUE
--------------- ----------
optimizer_mode ALL_ROWS
数据表构建如下：
SQL> create table t as select * from dba_objects;
Table created
SQL> create index idx_t_owner on t(owner);
Index created
2、ALL_ROWS——CBO优化器应用
进入Oracle 10g之后，ALL_ROWS成为Optimizer_Mode的默认参数取值。ALL_ROWS模式下，查询优化器就会采用完全的CBO机制，借助数据表、索引的统计量，可以最大限度的获取到成本最低的执行计划。ALL_ROWS模式的优化器目标就是生成成本cost最低的执行计划。
当数据表上没有统计量信息是，如果优化器模式选择ALL_ROWS，Oracle如何工作呢？
//刻意清除掉统计量；
SQL> exec dbms_stats.delete_table_stats(user,'T',cascade_parts => true,cascade_columns => true);
PL/SQL procedure successfully completed
之后生成执行计划：
SQL> explain plan for select * from t where wner='SYS';
Explained
SQL> select * from table(dbms_xplan.display(null,null,'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 34611 | 5982K| 157 (3)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| T | 34611 | 5982K| 157 (3)| 00:00:02 |
--------------------------------------------------------------------------
Outline Data
-------------
（篇幅原因，有省略。。。。。。）
/*+
BEGIN_OUTLINE_DATA
FULL(@"SEL$1" "T"@"SEL$1")
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Note
-----
- dynamic sampling used for this statement
46 rows selected
在没有统计量的情况下，Oracle还是生成了ALL_ROWS模式下的执行计划。值得关注的是结尾处的“- dynamic sampling used for this statement”。此时，Oracle优化器应用了动态统计量收集技术（Dynamic Sampling）。
动态统计量收集是Oracle CBO优化器的一种特性。优化器生成执行计划是依据成本cost公式计算出的，如果相关数据表没有收集过统计量，又要使用CBO的机制，就会引起动态采样。
动态采样（dynamic sampling）就是在生成执行计划是，以一个很小的采用率现进行统计量收集。由于采样率低，采样过程快但是不精确，而且采样结果不会进入到数据字典中。
如果我们进行统计量收集之后，ALL_ROWS就可以生成标准的CBO执行计划。
SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);
PL/SQL procedure successfully completed
SQL> explain plan for select * from t where wner='SCOTT';
Explained
SQL> select * from table(dbms_xplan.display(null,null,'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1516787156
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 34 | 3128 | 2 (0)|
| 1 | TABLE ACCESS BY INDEX ROWID| T | 34 | 3128 | 2 (0)|
|* 2 | INDEX RANGE SCAN | IDX_T_OWNER | 34 | | 1 (0)|
--------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
INDEX(@"SEL$1" "T"@"SEL$1" ("T"."OWNER"))
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='SCOTT')
44 rows selected
2、RULE——RBO的应用
在CBO时代，虽然已经不提供明确的RBO优化器支持，但是RBO相关代码还是驻留在Query Optimizer中的。我们对optimizer_mode参数设置为RULE，就可以指示优化器生成RBO执行计划。
设置模式参数。
SQL> alter session set optimizer_mode='RULE';
Session altered
SQL> select name, value from v$parameter where name='optimizer_mode';
NAME VALUE
--------------- ----------
optimizer_mode RULE
在optimizer_mode下，我们生成执行计划如下：
SQL> explain plan for select * from t where wner='SYS';
Explained
SQL> select * from table(dbms_xplan.display(null,null,'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1516787156
---------------------------------------------------
| Id | Operation | Name |
---------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID| T |
|* 2 | INDEX RANGE SCAN | IDX_T_OWNER |
---------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
INDEX(@"SEL$1" "T"@"SEL$1" ("T"."OWNER"))
OUTLINE_LEAF(@"SEL$1")
RBO_OUTLINE
OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='SYS')
Note
-----
- rule based optimizer used (consider using cbo)
49 rows selected
注意几个细节：在CBO环境下，由于owner=’SYS’条件进行全表扫描操作成本小于索引路径。于是CBO选择了全表扫描执行计划。
但是在RBO时代，索引路径规则是高于全表扫描的。所以执行计划直接确定为索引路径。Query Optimizer也注意到当前使用的优化器模式不合适，所以显示的提示出“- rule based optimizer used (consider using cbo)”，告知用户当前使用的是RBO，建议使用CBO。
3、CHOOSE——两个时代的过渡
在Oracle 9i一个时期，optimizer_mode的默认参数取值为CHOOSE。那个时期是query optimizer从RBO向CBO转换的时代，Oracle选择了渐变式的转换。由于那时没有专门的夜间统计量收集作业，很多时候数据表是没有统计量的。加之动态抽样技术的不成熟，所以CBO的应用存在一些障碍。在这个时期，Oracle提出了CHOOSE作为优化器默认模式。
CHOOSE是介于自动选择切换RBO和CBO。如果SQL涉及的数据表中有一个有统计量，那么该SQL就是用CBO优化器。否则就是用RBO。动态采样技术是在CBO优化器前提下的技术方案。
SQL> alter session set optimizer_mode='CHOOSE';
Session altered
SQL> select name, value from v$parameter where name='optimizer_mode';
NAME VALUE
--------------- ----------
optimizer_mode CHOOSE
首先实验没有统计量的情况。
//删除统计量；
SQL> exec dbms_stats.delete_table_stats(user,'T',cascade_columns => true,cascade_indexes => true);
PL/SQL procedure successfully completed
SQL> explain plan for select * from t where wner='SYS';
Explained
SQL> select * from table(dbms_xplan.display(null,null,'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1516787156
---------------------------------------------------
| Id | Operation | Name |
---------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID| T |
|* 2 | INDEX RANGE SCAN | IDX_T_OWNER |
---------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
INDEX(@"SEL$1" "T"@"SEL$1" ("T"."OWNER"))
OUTLINE_LEAF(@"SEL$1")
RBO_OUTLINE
OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Note
-----
- rule based optimizer used (consider using cbo)
49 rows selected
SQL>
当没有统计量的时候，Oracle不会如ALL_ROWS的时候进行动态采样，而是直接使用RBO。
强行收集了统计量之后，如果一个表有统计量，而另一个没有会如何呢？
SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);
PL/SQL procedure successfully completed
SQL> create table t_parts as select * from dba_tables;
Table created
SQL> explain plan for select * from t, t_parts where t.object_name=t_parts.table_name and t.owner=t_parts.table_name;
Explained
SQL> select * from table(dbms_xplan.display(null,null,'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2202291360
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1598 | 903K| 171 (4)| 00:00:03 |
|* 1 | HASH JOIN | | 1598 | 903K| 171 (4)| 00:00:03 |
| 2 | TABLE ACCESS FULL| T_PARTS | 1598 | 759K| 12 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T | 50405 | 4528K| 157 (3)| 00:00:02 |
------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
USE_HASH(@"SEL$1" "T"@"SEL$1")
LEADING(@"SEL$1" "T_PARTS"@"SEL$1" "T"@"SEL$1")
FULL(@"SEL$1" "T"@"SEL$1")
FULL(@"SEL$1" "T_PARTS"@"SEL$1")
OUTLINE_LEAF(@"SEL$1")
OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Note
-----
- dynamic sampling used for this statement
123 rows selected
数据表T有统计量，而T_PARTS没有统计量。在CHOOSE模式下，就选择了CBO优化器，进而对T_PARTS进行动态采样。
4、FIRST_ROWS——追求最快相应的优化模式
最后聊聊FIRST_ROWS系列（包括FIRST_ROWS_N）模式。该模式是一种介于CBO与RBO之间的优化器模式，与CHOOSE的切换方式不同。FIRST_ROWS模式系列与CBO一个显著的差异就是追求最优执行计划的标准不同。
ALL_ROWS代表的CBO追求的是生成综合成本最低的SQL执行计划。而FIRST_ROWS追求的是执行计划最快返回结果集合，优先相应前几条的查询结果。所以，FIRST_ROWS与CBO的核心价值有一些差异。
FIRST_ROWS有其用途场景，最多的就是在于分页操作SQL。当结果集合很大的时候，用户或者应用往往不关心全部集合的情况，而是关注返回前几条记录的响应速度。
5、结论
optimizer_mode是Oracle Query Optimizer核心参数，控制优化器工作的取向和目标。从optimizer_mode所支持的各种取值，我们也很容易看到从RBO到CBO的转换过程。
熟悉optimizer_mode各个取值的含义和用途，对我们控制优化器生成更好的执行计划至关重要。
1399
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
