ORACLE优化器RBO与CBO有什么用

本文主要介绍了ORACLE优化器RBO和CBO的使用，具有一定的参考价值。有兴趣的朋友可以参考一下。希望大家看完这篇文章后收获多多。让边肖带你去了解它。

RBO和CBO的基本概念

Oracle数据库中的优化器也称为查询优化器(Query

优化器).它是一个用于SQL分析和执行的优化工具，负责生成和制定SQL执行计划。Oracle中有两种优化器，即基于规则的优化器(RBO)和基于成本的优化器(CBO)。

基于规则的优化基于规则的优化器

基于成本的优化基于成本的优化器

从ORACLE 6开始采用RBO，一直使用到ORACLE 9i。ORACLE 10g。甲骨文已经完全放弃了RBO。它有一套严格的使用规则。只要你按照它写SQL语句，不管数据表中的内容如何，都不会影响你的“执行计划”，也就是说，RBO对数据并不“敏感”。它根据ORACLE指定的优先级规则为指定的表选择执行计划。比如在规则中，索引的优先级高于全表扫描；RBO根据可用的访问路径和访问路径级别选择执行计划。在RBO，SQL的编写经常影响执行计划。它要求开发者非常了解RBO的所有细节，菜鸟写的SQL脚本性能可能很差。随着RBO的被放弃，它逐渐不为人知。也许只有老一辈的数据库管理员更清楚这一点。官方文件中详细描述了RBO的访问路径：

RBO路1:单行

RBO路径2:按群集连接的单行

RBO路径3:按哈希簇键排列的单行

使用唯一或主键

RBO路径4:单行唯一或

主关键字

RBO路径5:集群连接

RBO路径6:哈希群集密钥

RBO路径7:索引群集密钥

RBO路径8:综合指数

RBO路径9:单列索引

RBO路径10:有界范围搜索开启

索引列

RBO路径11:无边界范围搜索开启

索引列

RBO路径12:排序合并连接

RBO路径13:索引列的最大值或最小值

索引列上的RBO路径14:排序依据

RBO路径15:全表扫描

CBO是比RBO更合理、更可靠的优化者。它是从ORACLE 8引入的，但在ORACLE 9i中逐渐成熟。在ORACLE 10g中，CBO完全取代了RBO。CBO是计算各种可能的“执行计划”的“成本”，即COST，从中选择成本最低的执行方案作为实际操作方案。这取决于数据库对象的统计信息，而统计信息的准确性将影响CBO做出最佳选择。如果发现涉及的对象(表、索引等。)在SQL执行一次时还没有被分析和统计，那么ORACLE就会采用一种叫做动态采样的技术来动态收集一些表和索引上的数据信息。

关于RBO和CBO，我有一个形象的比喻：在大数据时代到来之前，企业或许可以凭借多年积累的经验做出好的决策，跟随市场变化(RBO)。但是，在大数据时代，如果业务仍然依靠以前的经验来做决策，而不是大数据、数据分析和数据挖掘，就有可能做出错误的决策。这就是为什么越来越多的公司越来越重视BI和数据挖掘。电子商务、游戏、电信等行业。已经被广泛使用。以前BI分析都是在某游戏公司的数据库部门做的，挖掘潜在消费者几乎是不可能的。到目前为止，形象还是挺深刻的。

CBO与RBO的优劣

CBO优于RBO，因为RBO是一个僵化过时的优化器，它只识别规则，对数据不敏感。毕竟规则是死的，数据是可变的，所以这样生成的执行计划往往是不可靠的，也不是最优的。因为RBO，CBO可以体现在很多方面。请看下面一个例子，来自《让Oracle跑得更快》。

SQL创建表测试为从dba_objects中选择1 id，object _ name

表已创建。

SQL在测试(I)上创建索引idx_test

d);

Index created.

SQL> update test set id=100 where rownum =1;

1 row updated.

SQL> select id, count(1) from test group by id;

        ID   COUNT(1)

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

       100          1

         1      50314

从上面可以看出，该测试表的数据分布极其不均衡,ID=100的记录只有一条，而ID=1的记录有50314条。我们先看看RBO下两条SQL的执行计划.

SQL> select /*+ rule */ * from test where id =100;

Execution Plan

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

Plan hash value: 2473784974

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

| Id  | Operation                   | Name     |

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

|   0 | SELECT STATEMENT            |          |

|   1 |  TABLE ACCESS BY INDEX ROWID| TEST     |

|*  2 |   INDEX RANGE SCAN          | IDX_TEST |

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

Predicate Information (identified by operation id):

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

   2 - access("ID"=100)

Note

-----

   - rule based optimizer used
(consider using cbo)

Statistics

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

          1  recursive calls

          0  db block gets

          3  consistent gets

          0  physical reads

          0  redo size

        588  bytes sent via SQL*Net to client

        469  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

SQL>

SQL> select /*+ rule */ * from test where id=1;

50314 rows selected.

Execution Plan

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

Plan hash value: 2473784974

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

| Id  | Operation                   | Name     |

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

|   0 | SELECT STATEMENT            |          |

|   1 |  TABLE ACCESS BY INDEX ROWID| TEST     |

|*  2 |   INDEX RANGE SCAN          | IDX_TEST |

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

Predicate Information (identified by operation id):

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

   2 - access("ID"=1)

Note

-----

   - rule based optimizer used
(consider using cbo)

Statistics

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

          1  recursive calls

          0  db block gets

       7012  consistent gets

         97  physical reads

          0  redo size

    2243353  bytes sent via SQL*Net to client

      37363  bytes received via SQL*Net from client

       3356  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

      50314  rows processed

从执行计划可以看出，RBO的执行计划让人有点失望，对于ID=1，几乎所有的数据全部符合谓词条件，走索引只能增加额外的开销（因为ORACLE首先要访问索引数据块，在索引上找到了对应的键值，然后按照键值上的ROWID再去访问表中相应数据），既然我们几乎要访问所有表中的数据，那么全表扫描自然是最优的选择。而RBO选择了错误的执行计划。可以对比一下CBO下SQL的执行计划，显然它对数据敏感，执行计划及时的根据数据量做了调整,当查询条件为1时，它走全表扫描；当查询条件为100时，它走区间索引扫描。如下所示：

SQL> select * from test where id=1;

50314 rows selected.

Execution Plan

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

Plan hash value: 1357081020

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

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

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

|   0 | SELECT STATEMENT  |      | 49075 | 
3786K|    52   (2)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| TEST | 49075 |  3786K|   
52   (2)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   1 -
filter("ID"=1)

Note

-----

   - dynamic
sampling used for this statement

Statistics

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

         32  recursive calls

          0  db block gets

       3644 
consistent gets

          0  physical reads

          0  redo size

    1689175  bytes sent via SQL*Net to client

      37363  bytes received via SQL*Net from client

       3356  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0 
sorts (disk)

      50314  rows processed

SQL> select * from test where id =100;

Execution Plan

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

Plan hash value: 2473784974

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

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

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

|   0 | SELECT STATEMENT            |          |    
1 |    79 |     2  
(0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| TEST     |     1 |   
79 |     2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IDX_TEST
|     1 |       |    
1   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   2 - access("ID"=100)

Note

-----

   - dynamic sampling used for this
statement

Statistics

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

          9  recursive calls

          0  db block gets

         73  consistent gets

          0  physical reads

          0  redo size

        588  bytes sent via SQL*Net to client

        469  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

SQL>

仅此一项就可以看出为什么ORACLE极力推荐使用CBO，从ORACLE
10g开始不支持RBO的缘故。所谓长江后浪推前浪，前浪死在沙滩上。

CBO知识点的总结

CBO优化器根据SQL语句生成一组可能被使用的执行计划，估算出每个执行计划的代价，并调用计划生成器（Plan Generator）生成执行计划，比较执行计划的代价，最终选择选择一个代价最小的执行计划。查询优化器由查询转换器（Query Transform）、代价估算器（Estimator）和计划生成器（Plan Generator）组成。

CBO优化器组件

CBO由以下组件构成：

· 查询转化器（Query Transformer）

查询转换器的作用就是等价改变查询语句的形式，以便产生更好的执行计划。它决定是否重写用户的查询（包括视图合并、谓词推进、非嵌套子查询/子查询反嵌套、物化视图重写），以生成更好的查询计划。

The input to the query transformer is a parsed query, which is represented
by a set of

query blocks. The query blocks are nested or interrelated
to each other. The form of the

query determines how the query blocks are interrelated to each other. The
main

objective of the query transformer is to determine if it
is advantageous to change the

form of the query so that it enables generation of a better query plan.
Several different

query transformation techniques are employed by the query
transformer, including:

■ View Merging

■ Predicate Pushing

■ Subquery Unnesting

■ Query Rewrite with Materialized Views

Any combination of these transformations can be applied to a given query.

· 代价评估器（Estimator）

评估器通过复杂的算法结合来统计信息的三个值来评估各个执行计划的总体成本：选择性（Selectivity）、基数（Cardinality）、成本（Cost）

计划生成器会考虑可能的访问路径（Access Path）、关联方法和关联顺序，生成不同的执行计划，让查询优化器从这些计划中选择出执行代价最小的一个计划。

· 计划生成器（Plan Generator）

计划生成器就是生成大量的执行计划，然后选择其总体代价或总体成本最低的一个执行计划。

由于不同的访问路径、连接方式和连接顺序可以组合，虽然以不同的方式访问和处理数据，但是可以产生同样的结果

下图是我自己为了加深理解，用工具画的图

查看ORACLE优化器

SQL> show parameter optimizer_mode;

NAME                          
TYPE        VALUE

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

optimizer_mode                
string      ALL_ROWS

修改ORACLE优化器

ORACLE
10g 优化器可以从系统级别、会话级别、语句级别三种方式修改优化器模式，非常方便灵活。

其中optimizer_mode可以选择的值有： first_rows_n,all_rows.  其中first_rows_n又有first_rows_1000, first_rows_100, first_rows_10, first_rows_1

在Oracle
9i中，优化器模式可以选择first_rows_n,all_rows, choose, rule 等模式：

Rule： 基于规则的方式。

Choolse：指的是当一个表或或索引有统计信息，则走CBO的方式，如果表或索引没统计信息，表又不是特别的小，而且相应的列有索引时，那么就走索引，走RBO的方式。

If OPTIMIZER_MODE=CHOOSE, if statistics do not exist, and if you do not
add hints to SQL statements, then SQL statements use the RBO. You can use the
RBO to access both relational data and object types. If
OPTIMIZER_MODE=FIRST_ROWS, FIRST_ROWS_n, or ALL_ROWS and no statistics exist,
then the CBO uses default statistics. Migrate existing applications to use the
cost-based approach.

First
Rows：它与Choose方式是类似的，所不同的是当一个表有统计信息时，它将是以最快的方式返回查询的最先的几行，从总体上减少了响应时间。

All
Rows: 10g中的默认值，也就是我们所说的Cost的方式，当一个表有统计信息时，它将以最快的方式返回表的所有的行，从总体上提高查询的吞吐

虽然Oracle
10g中不再支持RBO，Oracle 10g官方文档关于optimizer_mode参数的只有first_rows和all_rows.但是依然可以设置 optimizer_mode为rule或choose，估计是ORACLE为了过渡或向下兼容考虑。如下所示。

系统级别

SQL> alter system set optimizer_mode=rule scope=both;

System altered.

SQL> show parameter optimizer_mode

NAME                                
TYPE        VALUE

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

optimizer_mode                      
string      RULE

 

会话级别

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

SQL>
alter session set optimizer_mode=first_rows_100;

Session
altered.

语句级别

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

SQL> select /*+ rule */ * from dba_objects
where rownum <= 10;

感谢你能够认真阅读完这篇文章，希望小编分享的“ORACLE优化器RBO与CBO有什么用”这篇文章对大家有帮助，同时也希望大家多多支持，关注行业资讯频道，更多相关知识等着你来学习!