ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 浅谈optimizer_mode优化器模式

浅谈optimizer_mode优化器模式

原创 Linux操作系统 作者:realkid4 时间:2011-08-16 20:47:05 0 删除 编辑

 

Oracle query optimizer(查询优化器)是我们接触最多的一个数据库组件。查询优化器最主要的工作就是接受输入的SQL以及各种环境参数、配置参数,生成合适的SQL执行计划(Execution Plan)。

 

Query Optimizer一共经历了两个历史阶段:RBOCBORBO时代,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

 

 

2ALL_ROWS——CBO优化器应用

 

进入Oracle 10g之后,ALL_ROWS成为Optimizer_Mode的默认参数取值。ALL_ROWS模式下,查询优化器就会采用完全的CBO机制,借助数据表、索引的统计量,可以最大限度的获取到成本最低的执行计划。ALL_ROWS模式的优化器目标就是生成成本cost最低的执行计划。

 

当数据表上没有统计量信息是,如果优化器模式选择ALL_ROWSOracle如何工作呢?

 

 

//刻意清除掉统计量;

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

 

 

2RULE——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

 

 

3CHOOSE——两个时代的过渡

 

Oracle 9i一个时期,optimizer_mode的默认参数取值为CHOOSE。那个时期是query optimizerRBOCBO转换的时代,Oracle选择了渐变式的转换。由于那时没有专门的夜间统计量收集作业,很多时候数据表是没有统计量的。加之动态抽样技术的不成熟,所以CBO的应用存在一些障碍。在这个时期,Oracle提出了CHOOSE作为优化器默认模式。

 

CHOOSE是介于自动选择切换RBOCBO如果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进行动态采样。

 

4FIRST_ROWS——追求最快相应的优化模式

 

最后聊聊FIRST_ROWS系列(包括FIRST_ROWS_N)模式。该模式是一种介于CBORBO之间的优化器模式,与CHOOSE的切换方式不同。FIRST_ROWS模式系列与CBO一个显著的差异就是追求最优执行计划的标准不同。

 

ALL_ROWS代表的CBO追求的是生成综合成本最低的SQL执行计划。而FIRST_ROWS追求的是执行计划最快返回结果集合,优先相应前几条的查询结果。所以,FIRST_ROWSCBO的核心价值有一些差异。

 

FIRST_ROWS有其用途场景,最多的就是在于分页操作SQL。当结果集合很大的时候,用户或者应用往往不关心全部集合的情况,而是关注返回前几条记录的响应速度。

 

 

5、结论

 

optimizer_modeOracle Query Optimizer核心参数,控制优化器工作的取向和目标。从optimizer_mode所支持的各种取值,我们也很容易看到从RBOCBO的转换过程。

 

熟悉optimizer_mode各个取值的含义和用途,对我们控制优化器生成更好的执行计划至关重要。

 

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

请登录后发表评论 登录
全部评论
求道~

注册时间:2010-11-30

  • 博文量
    545
  • 访问量
    7678122