基于RULE的优化器（学习笔记）

最新推荐文章于 2022-11-20 21:30:17 发布

数仓大山哥

最新推荐文章于 2022-11-20 21:30:17 发布

阅读量2.5k

点赞数 1

分类专栏： Oracle 系列文章标签： ORACLE RULE 优化器

本文链接：https://blog.csdn.net/panfelix/article/details/43424355

版权

Oracle 系列专栏收录该内容

123 篇文章 2 订阅

订阅专栏

崔华《基于Oracle的sql优化》学习笔记

1.1 基于RULE的优化器

(1) CBO

(2)RBO

和CBO相比，RBO是有其明显权限的。在使用RBO的情况下，执行计划一旦出了问题，很难对其做调整。另外，如果使用了RBO则目标SQL的写法，甚至是目标SQL中所涉及的各个对象在该SQL文本中出现的先后顺序都可能影响RBO执行计划的选择我，更糟糕的是，Oracle数据库中很好的特性、功能不能再RBO中使用因为他们不能被RBO锁支持。

只要出现如下情形之一，那么即便修改了优化器模式或者使用了RULE Hnint，Oracle依然不会使用RBO（而是强制使用CBO）。

（1）目标SQL中设计的对此昂有IOT（indexOrganized Table）。

（2）目标SQL中设计的对象有分区表。

（3）使用了平行查询或者并行DML。

（4）使用了星形连接

（5）使用了hash连接

（6）使用了索引快速全扫描

（7）使用了函数索引

（8） …...

这种情况下我们是很难对RBO选择的执行计划做调整的，其中一个十分关键的原因是不能使用hint。因为如果在目标SQL中使用了Hint，就意味着自动启动了CBO，即Oracle会以CBO来解析Hint的目标SQL。这里仅有两个例外，就是RULE Hint和SRIVING_SITE Hint，他可以在RBO下使用并且不自动启用CBO。

那么是不是在使用RBO的情况下就没有办法对执行计划进行调整了呢？？

当然不是这样，只是这种情况下我们的调整手段非常有限。其中的一种可行的方法就是等价改写目标SQL，比如在目标SQL的where条件中对NUMBER或者DATE类型的列上加上0（如果是varchar2或者char类型，可以加上一个空字符，例如||’’）,这样就可以让原本可以走的索引现在走不了。对于包含多表连接的目标sql而言，这种改变甚至可以影响表连接的顺序，进而就可以实现在使用RBO的情况下对该目标SQL的执行计划作出调整的目的。

但是如果出现了两条或者两条以上的等级值相同的执行路径的情况，那么RBO此时该如何选择呢？很简单，此时RBO会依据目标SQL中所涉及的相关对象在数据字典缓存（Data Dictionary cache）中的缓存顺序和目标SQL中所涉及的各个对象在目标SQL文本中出现的先后顺序来综合判断。这也就意味着我们还可以通过调整相关对象在数据字典中的缓存顺序，改变目标SQL中所涉及的各个对象在该SQL文本中出现的先后顺序来调整其执行计划。

实验：使用RBO的情况下，对目标SQL的执行计划调整。

create table emp_temp as select * from  emp;
 
create index idx_mgr_temp on emp_temp(mgr);
 
create index idx_deptno_temp on emp_temp(deptno);
 
 
select * from emp_temp where  mgr>100 and deptno>100;

###在当前session中配置优化器模式为RULE

alter session set optimizer_mode='RULE';

SET AUTOT TRACE EXP

假如我们发现走索引IDX_DEPTNO_TEMP不如走索引IDX_MGR_TEMP的执行效率高，或者我们想让RBO走索引IDX__MGR_TEMP，那么我们该如何让做？？

可以加一个0不让它走索引：

Select * from  emp_temp where mgr>100 anddeptno+0>100;
 
08:28:11 scott@felix SQL>Select * from  emp_temp where mgr>100 anddeptno+0>100;
 
no rows selected
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 2973289657
 
----------------------------------------------------
| Id  |Operation                   | Name         |
----------------------------------------------------
|   0 |SELECT STATEMENT            |              |
|*  1 |  TABLE ACCESS BY INDEX ROWID| EMP_TEMP     |
|*  2 |   INDEX RANGE SCAN          | IDX_MGR_TEMP |
----------------------------------------------------
 
Predicate Information (identified by operationid):
---------------------------------------------------
 
   1 -filter("DEPTNO"+0>100)
   2 -access("MGR">100)
 
Note
-----
   - rulebased optimizer used (consider using cbo)
 
 
Statistics
----------------------------------------------------------
         1  recursive calls
         0  db block gets
         2  consistent gets
         0  physical reads
         0  redo size
       799  bytes sent via SQL*Net toclient
       512  bytes received via SQL*Netfrom client
         1  SQL*Net roundtrips to/fromclient
         0  sorts (memory)
         0  sorts (disk)
         0  rows processed
 
09:58:53 scott@felix SQL>

我们可以看到已经改变了执行计划。

刚才我先创建索引IDX_MGR_TEMP，再创建索引IDX_DEPTNO_TEMP，所以IDX_MGR_TEMP先缓存，在缓存IDX_DEPTNO_TEMP这种情形下RBO选择的是走对索引IDX_DEPTNO_TEMP的索引范围扫，如果反过来呢？？

先删除索引IDDX_MGR_TEMP

09:58:53 scott@felix SQL>DROP INDEXIDX_MGR_TEMP;
 
Index dropped.
再创建该索引：
 
10:15:20 scott@felix SQL>create indexidx_mgr_temp on emp_temp(mgr);
 
Index created.
 
 
10:16:05 scott@felix SQL>Select * from  emp_temp where mgr>100 and deptno>100;
 
no rows selected
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 2973289657
 
----------------------------------------------------
| Id  |Operation                   | Name         |
----------------------------------------------------
|   0 |SELECT STATEMENT            |              |
|*  1 |  TABLE ACCESS BY INDEX ROWID| EMP_TEMP     |
|*<span style="color:#FF0000;">  2 |   INDEX RANGE SCAN          | IDX_MGR_TEMP |</span>
----------------------------------------------------
 
Predicate Information (identified by operationid):
---------------------------------------------------
 
   1 -filter("DEPTNO">100)
   2 -access("MGR">100)
 
Note
-----
   - rulebased optimizer used (consider using cbo)
 
 
Statistics
----------------------------------------------------------
         3  recursive calls
         0  db block gets
         6  consistent gets
         0  physical reads
          0 redo size
       799  bytes sent via SQL*Net toclient
       512  bytes received via SQL*Netfrom client
         1  SQL*Net roundtrips to/fromclient
         0  sorts (memory)
         0  sorts (disk)
         0  rows processed
 
10:17:48 scott@felix SQL>

如上实验可知，当目标SQL有两条或者两条以上的执行路径的登记相同时，我们确实可以通过调整相关对象在数据字典缓存中的缓存顺序来影响RBO对于执行计划的选择。

Create table emp_temp1 as select * from emp;
 
10:25:30 scott@felix SQL>Selectt1.mgr,t2.deptno from emp_temp t1,emp_temp1 t2
10:26:27  2  Where t1.empno=t2.empno;
 
14 rows selected.
 
Execution Plan
----------------------------------------------------------
Plan hash value: 1323777565
 
-----------------------------------------
| Id  |Operation           | Name      |
-----------------------------------------
|   0 |SELECT STATEMENT    |           |
|   1 |  MERGE JOIN         |           |
|   2 |   SORT JOIN         |           |
|   3 |    TABLE ACCESS FULL| EMP_TEMP1 |
|*  4 |   SORT JOIN         |           |
|   5 |    TABLE ACCESS FULL| EMP_TEMP  |
-----------------------------------------
 
Predicate Information (identified by operationid):
---------------------------------------------------
 
   4 -access("T1"."EMPNO"="T2"."EMPNO")
      filter("T1"."EMPNO"="T2"."EMPNO")
 
Note
-----
   - rulebased optimizer used (consider using cbo)
 
 
Statistics
----------------------------------------------------------
         1  recursive calls
         0  db block gets
         6  consistent gets
         4  physical reads
         0  redo size
       778  bytes sent via SQL*Net toclient
       524  bytes received via SQL*Netfrom client
         2  SQL*Net roundtrips to/fromclient
         2  sorts (memory)
         0  sorts (disk)
        14  rows processed
 
10:31:02 scott@felix SQL>

我们发现emp_tem1p作为驱动表，

注意：排序合并连接，从严格意义上来讲，并没有驱动表和被驱动表的概念，这里只是为了方便阐述而人为的给排序合并连接添加了概念。

那么改变该SQL的SQL文本中的位置会怎么样呢？？

 
Select t1.mgr,t2.deptno from emp_temp1  t2,emp_temp  t1
 Where t1.empno=t2.empno;
 
10:31:02 scott@felix SQL>Selectt1.mgr,t2.deptno from emp_temp1 t2,emp_temp  t1
10:36:15  2   Where t1.empno=t2.empno;
 
      MGR     DEPTNO
---------- ----------
     7902         20
     7698         30
     7698         30
     7839         20
     7698         30
     7839         30
     7839         10
     7566         20
                   10
     7698         30
     7788         20
     7698         30
     7566         20
     7782         10
 
14 rows selected.
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 2135683657
 
-----------------------------------------
| Id  |Operation           | Name      |
-----------------------------------------
|   0 |SELECT STATEMENT    |           |
|   1 |  MERGE JOIN         |           |
|   2 |  SORT JOIN         |           |
|   3 |   TABLE ACCESS FULL| EMP_TEMP  |
|*  4 |  SORT JOIN         |           |
|   5 |   TABLE ACCESS FULL| EMP_TEMP1 |
-----------------------------------------
 
Predicate Information (identified by operationid):
---------------------------------------------------
 
   4 -access("T1"."EMPNO"="T2"."EMPNO")
      filter("T1"."EMPNO"="T2"."EMPNO")
 
Note
-----
   - rulebased optimizer used (consider using cbo)
 
 
Statistics
----------------------------------------------------------
         1  recursive calls
          0 db block gets
         6  consistent gets
         0  physical reads
         0  redo size
       778  bytes sent via SQL*Net toclient
       524  bytes received via SQL*Netfrom client
         2  SQL*Net roundtrips to/fromclient
          2 sorts (memory)
         0  sorts (disk)
        14  rows processed
 
10:36:15 scott@felix SQL>

我们发现，驱动表变为emp_temp了。

说明，当目标SQL有两条或者两条以上的执行路径的等级值相同时，我们确定可以通过改变目标SQL中所涉及的各个对象在该SQL文本中出现的先后顺序来影响RBO对其执行计划的选择。

注意：以上几个实验都是就RBO模式。RBO不支持HASH join。

Emp有主键，emp_temp上empno列没有索引。

10:43:50 scott@felix SQL>Select t1.mgr,t2.deptno from emp  t1,emp_temp t2
10:44:00   2   Where t1.empno=t2.empno;

14 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 367190759

-------------------------------------------------
| Id  | Operation                    | Name     |
-------------------------------------------------
|   0 | SELECT STATEMENT             |          |
|   1 |  NESTED LOOPS                |          |
|   2 |   NESTED LOOPS               |          |
|   3 |    TABLE ACCESS FULL         | EMP_TEMP |
|*  4 |    INDEX UNIQUE SCAN         | PK_EMP   |
|   5 |   TABLE ACCESS BY INDEX ROWID| EMP      |
-------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("T1"."EMPNO"="T2"."EMPNO")

Note
-----
   - rule based optimizer used (consider using cbo)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         22  consistent gets
          0  physical reads
          0  redo size
        778  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         14  rows processed

10:44:00 scott@felix SQL>

修改该SQL文本的表顺序：

Select t1.mgr,t2.deptno from emp_temp t2,emp  t1
 Where t1.empno=t2.empno;
 
10:44:00 scott@felix SQL>Selectt1.mgr,t2.deptno from emp_temp t2, emp t1
10:49:57  2    Where t1.empno=t2.empno;
 
14 rows selected.
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 367190759
 
-------------------------------------------------
| Id  |Operation                    | Name     |
-------------------------------------------------
|   0 |SELECT STATEMENT             |          |
|   1 |  NESTED LOOPS                |          |
|   2 |   NESTED LOOPS               |         |
|   3 |   TABLE ACCESS FULL         |EMP_TEMP |
|*  4 |    INDEX UNIQUE SCAN         | PK_EMP   |
|   5 |   TABLE ACCESS BY INDEX ROWID| EMP      |
-------------------------------------------------
 
Predicate Information (identified by operationid):
---------------------------------------------------
 
   4 -access("T1"."EMPNO"="T2"."EMPNO")
 
Note
-----
   - rulebased optimizer used (consider using cbo)
 
 
Statistics
----------------------------------------------------------
         1  recursive calls
         0  db block gets
        22  consistent gets
         0  physical reads
         0  redo size
       778  bytes sent via SQL*Net toclient
       524  bytes received via SQL*Netfrom client
         2  SQL*Net roundtrips to/fromclient
         0  sorts (memory)
         0  sorts (disk)
        14  rows processed
 
10:49:57 scott@felix SQL>

可以看出执行计划走的是嵌套循环，且驱动表依然是表EMP_TEMP，这就证明：如果RBO仅凭目标SQL各条执行路径等级值的大小就可以选择出执行计划。那么无论怎么调整相关对象在该SQL的SQL文本中的位置，对于该SQL最终的执行计划都不会有任何影响。

数仓大山哥

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
基于RULE的优化器（学习笔记）

崔华《基于Oracle的sql优化》学习笔记 1.1 基于RULE的优化器(1) CBO(2)RBO和CBO相比，RBO是有其明显权限的。在使用RBO的情况下，执行计划一旦出了问题，很难对其做调整。另外，如果使用了RBO则目标SQL的写法，甚至是目标SQL中所涉及的各个对象在该SQL文本中出现的先后顺序都可能影响RBO执行计划的选择我，更糟糕的是，Oracle数据库中很好的特性...
复制链接

扫一扫

专栏目录