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
熟悉optimizer_mode各个取值的含义和用途，对我们控制优化器生成更好的执行计划至关重要。
当前位置：
论坛首页 »
自由讨论区 »
MySQL, Oracle及数据库讨论区
»
发起投票
技术讨论
加好友
发消息
2011-9-2 14:52:19 |
赞(
操作
