优化器

优化器

关注
发信
关注(28)粉丝(399)

optimizer_mode优化器模式

查询优化器最主要的工作就是接受输入的SQL以及各种环境参数、配置参数，生成合适的SQL执行计划（Execution Plan）。

Query Optimizer一共经历了两个历史阶段：

  • RBO: Rule-Based Optimization 基于规则的优化器
  • CBO: Cost-Based Optimization 基于代价的优化器

关于RBO与CBO，一个形象的比喻：大数据时代到来以前，做生意或许凭借多年累计 下来的经验规则（RBO）就能够很好的做出决策，跟随市场变化。但是大数据时代，如果做生意还是靠以前凭经验做决策，而不是靠大数据、数据分析、数据挖掘做决策，那么就有可能做出错误的决策。这也就是越来越多的公司对BI、数据挖掘越来越重视的缘故

一、RBO优化器

RBO(Rule Based Optimizer,基于规则的优化器）就是在优化器里面嵌入15种规则，它根据ORACLE指定的优先顺序规则，对指定的表进行执行计划的选择。执行SQL语句符合哪种规则，就按照规则定制出相应的SQL执行计划。比如在规则中，索引的优先级大于全表扫描;RBO是根据可用的访问路径以及访问路径等级来选择执行计划，在RBO中，SQL的写法往往会影响执行计划，它要求开发人 员非常了解RBO的各项细则，菜鸟写出来的SQL脚本性能可能非常差。由于它他是一种过时呆板的优化器，Oracle官方已不再支持RBO服务。

15种规则及ranking:

二、CBO优化器

CBO是一种比RBO更加合理、可靠的优化器，它是从ORACLE 8中开始引入，但到ORACLE 9i 中才逐渐成熟。进入Oracle 10g之后，Query Optimizer就已经将CBO（Cost Based Optimizer,基于代价的优化器）作为默认优化器，并且Oracle官方不再支持RBO服务。

它的思路是让Oracle获取所有的执行计划的相关信息，利用系统统计量进行各种执行路径试算，计算各种可能“执行计划”的“代价”，即COST，获取相对相对成本最低的执行计划，作为实际运行方案。它依赖数据库对象的统计信息，统计信息的准确与否会影响CBO做出最优的选择。如果对一次执行SQL时发现涉及对象（表、索引 等）没有被分析、统计过，那么ORACLE会采用一种叫做动态采样的技术，

动态统计量收集是Oracle CBO优化器的一种特性。优化器生成执行计划是依据成本cost公式计算出的，如果相关数据表没有收集过统计量，又要使用CBO的机制，就会引起动态采样(Dynamic Sampling）,。

动态采样（dynamic sampling）就是在生成执行计划是，以一个很小的采用率现进行统计量收集, 动态的收集表和索引上的一些数据信息。由于采样率低，采样过程快但是不精确，而且采样结果不会进入到数据字典中。

CBO查询优化器由查询转换器（Query Transform）、代价估算器（Estimator）和计划生成器（Plan Generator）组成。

optimizer_mode优化器模式-LMLPHP

CBO优化器有两种可选的运行模式：

2.1 FIRST_ROWS(n)

当设置优化器模式为：FIRST_ROWS(n)时，意味着Oracle在执行SQL语句时，优先考虑将结果集中的前n条记录以最快的速度反馈回 来，而其他结果并不需要同时反馈，也就是说在处理数据的时候，后面的数据可能还没提取出来，前面的数据已经返回给用户了，这种需求在网站搜索或者BBS的 分页上经常看到。比如每次只显示查询信息的前20条，这时设置FIRST_ROWS(20)就非常合适。对于分页操作，越靠前的页，显示结果需要的时间将越短。

需要注意的是排序使用的X必须创建有索引，否则CBO会忽略FIRST_ROWS(n)而使用ALL_ROWS.

2.2 ALL_ROWS

CBO的模式为ALL_ROWS时，意味着我们需要Oracle以最快的速度将SQL执行完毕，将结果集全部返回。它和FIRST_ROWS(n) 的区别在于，ALL_ROWS强调整体的执行效率，而FIRST_ROWS(n)强调以最快的速度返回前n条记录。ALL_ROWS在OLAP系统中使用 的比较多，它的目的在于快速获取执行结果的最后一条记录。

三、CHOOSE——两个时代的过渡

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

CHOOSE是介于自动选择切换RBO和CBO。如果SQL涉及的数据表中有一个有统计量，那么该SQL就是用CBO优化器。否则就是用RBO。动态采样技术是在CBO优化器前提下的技术方案。

SCOTT@PDBORCL>  alter session set optimizer_mode=choose;

会话已更改。

SCOTT@PDBORCL> set autotrace traceonly;

SCOTT@PDBORCL>  exec dbms_stats.delete_table_stats('scott','emp',cascade_columns => true,cascade_indexes => true);

PL/SQL 过程已成功完成。

SCOTT@PDBORCL> select ename from emp ;

已选择  行。

执行计划

----------------------------------------------------------

Plan hash value:

----------------------------------

| Id  | Operation         | Name |

----------------------------------

|    | SELECT STATEMENT  |      |

|    |  TABLE ACCESS FULL| EMP  |

----------------------------------

Note

-----

   - rule based optimizer used (consider using cbo)

统计信息

----------------------------------------------------------

            recursive calls

            db block gets

            consistent gets

            physical reads

            redo size

          bytes sent via SQL*Net to client

          bytes received via SQL*Net from client

            SQL*Net roundtrips to/from client

            sorts (memory)

            sorts (disk)

           rows processed

SCOTT@PDBORCL> exec dbms_stats.gather_table_stats (ownname => 'scott',tabname => 'emp');

PL/SQL 过程已成功完成。

SCOTT@PDBORCL> select ename  from emp;

已选择  行。

执行计划

----------------------------------------------------------

Plan hash value:

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|    | SELECT STATEMENT  |      |     |     |        ()| :: |

|    |  TABLE ACCESS FULL| EMP  |     |     |        ()| :: |

--------------------------------------------------------------------------

统计信息

----------------------------------------------------------

            recursive calls

            db block gets

            consistent gets

            physical reads

            redo size

          bytes sent via SQL*Net to client

          bytes received via SQL*Net from client

            SQL*Net roundtrips to/from client

            sorts (memory)

            sorts (disk)

           rows processed

SCOTT@PDBORCL>

四、optimizer_mode的查看与修改

通过优化器参数optimizer_mode，我们可以控制Oracle优化器生成不同模式下的执行计划。

4.1查看optimizer_mode

parameter optimizer_mode

SCOTT@PDBORCL> show parameter optimizer_mode

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

optimizer_mode                       string      ALL_ROWS

SCOTT@PDBORCL> select name, value from v$parameter where name='optimizer_mode';

NAME                                                                             VALUE

-------------------------------------------------------------------------------- ---------------------------------------

optimizer_mode                                                                   ALL_ROWS

SCOTT@PDBORCL>

4.2修改optimizer_mode

ORACLE 10g 优化器可以从系统级别、会话级别、语句级别三种方式修改优化器模式，非常方便灵活。 其中optimizer_mode可以选择的值有： first_rows_n,all_rows.  其中first_rows_n又有first_rows_1000, first_rows_100, first_rows_10, first_rows_1

系统级别

可以通过下面语句修改optimizer_mode

alter system set optimizer_mode=all_rows scope=both;

会话级别

会话级别修改优化器模式，只对当前会话有效，其它会话依然使用系统优化器模式。

alter session set optimizer_mode=first_rows_100;

语句级别

语句级别通过使用提示hints来实现。

select /*+ rule */ * from emp;

参考：

ORACLE优化器RBO与CBO介绍总结

浅谈optimizer_mode优化器模式

05-11 17:21
Powered by LMLPHP ©2024 优化器 0.067635