- 邮箱：
- 广东省广州市天河区88号
- 电话：
- /public/upload/system/2018/07/26/f743a52e720d8579f61650d7ca7a63a0.jpg
- 传真：
- /public/upload/system/2018/07/26/fe272790a21a4d3e1e670f37534a3a7d.png
- 手机：
- 13800000000
- 地址：
- 1234567890
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
-------------
（篇幅原因，有省略。。。。。。）
?
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
-------------
?
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
-------------
?
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
-------------
?
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
-------------
?
?
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各个取值的含义和用途，对我们控制优化器生成更好的执行计划至关重要。
原文地址：
http://blog.itpub.net/17203031/viewspace-705012/
