oracle优化器

oralce里优化器有RBO与CBO两种

RBO

RBO基于规则进行，根据固有的规则来选择执行sql的方式
CBO基于成本 选择执行成本最小的一条进行执行，依据是sql语句所涉及的表，索引，列等统计信息

在 10g版本以后rbo已经停止支持，单仍然保留源代码，故仍然可以使用该优化器，但调优手段相对cbo要少一些

RBO将执行计划划分为15个执行等级
1级为rowid访问，执行效率最高。15级为全表扫描，认为执行效率最低

注：即使修改了优化器，或者使用了rule hint oracle一般也不会使用rbo，而仍然强制使用cbo

在SQL中使用hint，则意味着启用CBO，也就是oracle会使用CBO来解析执行含hint的目标SQL

RBO调整手段
1.RBO一条可行的方法就是等价改写目标SQL,如：where条件中对number或者date类型的列假话是哪个0 ,varchar2则加上“|| ”
2.sql中有两条或以上执行路径的等级值相同情况，可以调整在数据字典缓存顺序来影响RBO执行计划的选择
3.对于sql出现的对象的先后顺序调整也可能引起执行计划的改变，关键在于决定随时驱动表，谁是被驱动表

例如：

使用alter session set optimizer_mode=’RULE’ 修改执行优化器为RULE，启用RBO

SQL> alter session set optimizer_mode='RULE';

Session altered

先介绍查看执行计划的两种方法：
1.explain plan for + SQL语句
select * from table(dbms_xplay.display)查看刚刚解析的执行计划

2.set autotrace traceonly explain

SQL>  explain plan for select * from nt where userid='10000000557765' and account='325989' ;

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2445682311
---------------------------------------------------
| Id  | Operation                   | Name        |
---------------------------------------------------
|   0 | SELECT STATEMENT            |             |
|   1 |  TABLE ACCESS BY INDEX ROWID| NT          |
|   2 |   AND-EQUAL                 |             |
|*  3 |    INDEX RANGE SCAN         | INX_USERID  |
|*  4 |    INDEX RANGE SCAN         | INX_ACCOUNT |
---------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("USERID"=10000000557765)
   4 - access("ACCOUNT"='325989')
Note
-----

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

21 rows selected

可以看到使用了两个索引 INX_USERID，INX_ACCOUNT

如果我们此时不想让表走索引INX_ACCOUNT，那么可以对SQL进行等价修改：

select * from nt where userid='10000000557765' and account+0='325989' ;

SQL> explain plan for select * from nt where userid='10000000557765' and account+0='325989' ;

Explained

SQL> select * from table(dbms_xplan.display)
  2  ;

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3772635577
--------------------------------------------------
| Id  | Operation                   | Name       |
--------------------------------------------------
|   0 | SELECT STATEMENT            |            |
|*  1 |  TABLE ACCESS BY INDEX ROWID| NT         |
|*  2 |   INDEX RANGE SCAN          | INX_USERID |
--------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(TO_NUMBER("ACCOUNT")+0=325989)
   2 - access("USERID"=10000000557765)
Note
-----
   - rule based optimizer used (consider using cbo)

19 rows selected

可以查看到只是使用了inx_userid

调整对象的顺序，引起执行计划改变

SQL> explain plan for select * from nt where account='325989' and userid='10000000557765' ;

Explained

SQL> select * from table(dbms_xplan.display)
  2  ;

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 17873495
---------------------------------------------------
| Id  | Operation                   | Name        |
---------------------------------------------------
|   0 | SELECT STATEMENT            |             |
|   1 |  TABLE ACCESS BY INDEX ROWID| NT          |
|   2 |   AND-EQUAL                 |             |
|*  3 |    INDEX RANGE SCAN         | INX_ACCOUNT |
|*  4 |    INDEX RANGE SCAN         | INX_USERID  |
---------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("ACCOUNT"='325989')
   4 - access("USERID"=10000000557765)
Note
-----

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

21 rows selected

CBO

CBO基于成本的优化器

cbo生成执行计划会考虑到实际执行对象所涉及的数据量，数据分布情况，网络状态，IO，cpu等资源情况

网络资源一般消耗与使用了dblink的分布式SQL中，但是该种SQL查看执行计划略有特殊

CBO解析目标SQL，首先对sql进行查询转换，转换完成后得到等价改写sql，选择执行路径中成本最小的作为执行计划

cardinality 表示某个具体执行步骤执行结果所包含的记录数量的估算值

SQL> set autotrace traceonly 
SQL> select * from nt;

690622 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3687718604

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   690K|   142M|  6132   (1)| 00:01:14 |
|   1 |  TABLE ACCESS FULL| NT   |   690K|   142M|  6132   (1)| 00:01:14 |
--------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      66775  consistent gets
      22293  physical reads
          0  redo size
  167747687  bytes sent via SQL*Net to client
     506971  bytes received via SQL*Net from client
      46043  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     690622  rows processed

查看到数据量690K

逻辑读66775 consistent gets
物理读22293 physical reads

SQL> select * from nt where userid='10000000557765';


Execution Plan
----------------------------------------------------------
Plan hash value: 3772635577

------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |     1 |   217 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| NT         |     1 |   217 |     4   (0)| 00:00:01 |