Oracle 浅谈optimizer_mode优化器模式

╰+哭是因爲堅強的太久メ 2022-06-07 04:07 730阅读 0赞

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

\--------------------------------------------------------------------------

\--------------------------------------------------------------------------

|   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

\--------------------------------------------------------------------------------

\--------------------------------------------------------------------------------

|   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

\------------------------------------------------------------------------------

\------------------------------------------------------------------------------

|   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各个取值的含义和用途，对我们控制优化器生成更好的执行计划至关重要。

原文地址：

http://blog.itpub.net/17203031/viewspace-705012/

Oracle 浅谈optimizer_mode优化器模式

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关浅谈状态模式

相关浅谈ORACLE SQL语句优化经验

相关浅谈JavaScript设计模式——构造器模式（生成器模式）

相关浅谈数据库优化

相关 Oracle 浅谈optimizer_mode优化器模式

相关浅谈索引优化

相关浅谈webpack优化

相关浅谈SQL优化

相关浅谈Iterator迭代器模式

相关浅谈Interpreter解释器模式