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' ;ExplainedSQL> 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' ;ExplainedSQL> 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' ;ExplainedSQL> 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 ||*  2 |   INDEX RANGE SCAN          | INX_USERID |     1 |       |     3   (0)| 00:00:01 |------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - access("USERID"=10000000557765)Statistics----------------------------------------------------------          0  recursive calls          0  db block gets          5  consistent gets          0  physical reads          0  redo size       3834  bytes sent via SQL*Net to client        520  bytes received via SQL*Net from client          2  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)          1  rows processedSQL> 

使用where条件筛选，走索引
INX_USERID
行数1
逻辑读5 consistent gets

cbo对sql的可传递性改写

SQL> select * from nt where userid in ('10000000557780','10000000557765');Execution Plan----------------------------------------------------------Plan hash value: 2503616604-------------------------------------------------------------------------------------------| Id  | Operation                    | Name       | Rows  | Bytes | Cost (%CPU)| Time     |-------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT             |            |     2 |   434 |     5   (0)| 00:00:01 ||   1 |  INLIST ITERATOR             |            |       |       |            |          ||   2 |   TABLE ACCESS BY INDEX ROWID| NT         |     2 |   434 |     5   (0)| 00:00:01 ||*  3 |    INDEX RANGE SCAN          | INX_USERID |     2 |       |     4   (0)| 00:00:01 |-------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   3 - access("USERID"=10000000557765 OR "USERID"=10000000557780)Statistics----------------------------------------------------------          1  recursive calls          0  db block gets          9  consistent gets          0  physical reads          0  redo size       4129  bytes sent via SQL*Net to client        520  bytes received via SQL*Net from client          2  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)          2  rows processedSQL> 

即根据提示，
3 - access(“USERID”=10000000557765 OR “USERID”=10000000557780)

原来sql可以修改为

 select * from nt where "USERID"=10000000557765 OR "USERID"=10000000557780

SQL> select * from nt where "USERID"=10000000557765 OR "USERID"=10000000557780;Execution Plan----------------------------------------------------------Plan hash value: 2503616604-------------------------------------------------------------------------------------------| Id  | Operation                    | Name       | Rows  | Bytes | Cost (%CPU)| Time     |-------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT             |            |     2 |   434 |     5   (0)| 00:00:01 ||   1 |  INLIST ITERATOR             |            |       |       |            |          ||   2 |   TABLE ACCESS BY INDEX ROWID| NT         |     2 |   434 |     5   (0)| 00:00:01 ||*  3 |    INDEX RANGE SCAN          | INX_USERID |     2 |       |     4   (0)| 00:00:01 |-------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   3 - access("USERID"=10000000557765 OR "USERID"=10000000557780)Statistics----------------------------------------------------------          1  recursive calls          0  db block gets          9  consistent gets          0  physical reads          0  redo size       4129  bytes sent via SQL*Net to client        520  bytes received via SQL*Net from client          2  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)          2  rows processedSQL> 

注：
CBO在多表关联中，有可能漏选最优执行计划，因为N个表关联，则关联的可能性为N!，CBO在对目标SQL优化调整的时候，有可能会漏选

优化器的模式：

由参数optimizer_mode 设置
修改方法：
1.修改session会话级别的优化器设置 alter session set optimizer_mode=’RULE’;
2.修改全局系统优化器设置 alter system set optimizer_mode=rule scope=both;

有5种值：
1.RULE rbo解析
2.CHOOSE 意味着若该表有统计信息，则选用CBO，若涉及的对象都有没有统计信息，则使用RBO
3.FIRST_ROWS_n（n=1,10,100,1000） 以最快响应速度返回前n行记录
（ps：由于最快响应速度有可能违反CBO最小成本的原则，因此CBO会将该执行成本值修改为较小的值，这样就不违背CBO原则了，但这样选择出来的执行计划并不是最优，对于IO，cpu等消耗巨大）
4.FIRST_ROWS
5.ALL_ROWS 默认值，返回所有的行

SQL> SQL> show parameter optimizer_mode;NAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------optimizer_mode                       string      ALL_ROWSSQL> 

优化器得到执行计划，将会决定db访问数据的方式
oracle访问数据的方式有两种：
1.直接访问表
2.先访问索引，再进行回表读取数据

直接访问表：
1.全表扫描
如：

SQL> select * from test;Execution Plan----------------------------------------------------------Plan hash value: 1357081020--------------------------------------------------------------------------| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |--------------------------------------------------------------------------|   0 | SELECT STATEMENT  |      |     1 |  2354 |     3   (0)| 00:00:01 ||   1 |  TABLE ACCESS FULL| TEST |     1 |  2354 |     3   (0)| 00:00:01 |--------------------------------------------------------------------------Note-----   - dynamic sampling used for this statement (level=2)Statistics----------------------------------------------------------         50  recursive calls          0  db block gets        142  consistent gets          1  physical reads          0  redo size       3834  bytes sent via SQL*Net to client        520  bytes received via SQL*Net from client          2  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)          1  rows processedSQL> 

2.rowid访问，是最快的方式

rowid是oracle存储数据的物理地址，每个表默认都会有rowid列

select rowid from test;AAAkeVAAEAAAW17AAA

通过dbms_rowid包，可以直接得到具体的rowid包含的地址信息：

SQL> select dbms_rowid.rowid_object(rowid) object_id,dbms_rowid.rowid_relative_fno(rowid) file_id,dbms_rowid.rowid_block_number(rowid) block_id,dbms_rowid.rowid_row_number(rowid) row_number from test; OBJECT_ID    FILE_ID   BLOCK_ID ROW_NUMBER---------- ---------- ---------- ----------    149397          4      93563          0SQL> 

表示的是对象编号就是149397，在4号文件的93563块的第0行

SQL> select dbms_rowid.rowid_object('AAAkeVAAEAAAW17AAA') object_id from dual; OBJECT_ID----------    149397SQL> 

查看具体块的信息

SQL> select dump('AAAkeVAAEAAAW17AAA') from test;DUMP('AAAKEVAAEAAAW17AAA')----------------------------------------------------------------------Typ=96 Len=18: 65,65,65,107,101,86,65,65,69,65,65,65,87,49,55,65,65,65SQL> 

（rowid后续进行扩展）

索引：
索引类型：
索引扫描类型：
1） 唯一索引扫描（Index Unique Scans）
2） 索引范围扫描（Index Range Scans）
3） 索引降序范围扫描（Index Range Scans Descending）
4） 跳跃式索引扫描（Index Skip Scans）
5） 全索引扫描（Full Index Scans）
6） 快速全索引扫描（Fast Full Index Scans）
7） 索引连接（Index Joins）

附：

查询数据对象segments的大小：查询dba_segments表

SQL> desc dba_segments;Name             Type         Nullable Default Comments                                                                                                                               ---------------- ------------ -------- ------- -------------------------------------------------------------------------------------------------------------------------------------- OWNER            VARCHAR2(30) Y                Username of the segment owner                                                                                                          SEGMENT_NAME     VARCHAR2(81) Y                Name, if any, of the segment                                                                                                           PARTITION_NAME   VARCHAR2(30) Y                Partition/Subpartition Name, if any, of the segment                                                                                    SEGMENT_TYPE     VARCHAR2(18) Y                Type of segment:  "TABLE", "CLUSTER", "INDEX", "ROLLBACK","DEFERRED ROLLBACK", "TEMPORARY","SPACE HEADER", "TYPE2 UNDO" or "CACHE" SEGMENT_SUBTYPE  VARCHAR2(10) Y                SubType of Lob segment:  "SECUREFILE", "ASSM", "MSSM", NULL                                                                            TABLESPACE_NAME  VARCHAR2(30) Y                Name of the tablespace containing the segment                                                                                          HEADER_FILE      NUMBER       Y                ID of the file containing the segment header                                                                                           HEADER_BLOCK     NUMBER       Y                ID of the block containing the segment header                                                                                          BYTES            NUMBER       Y                Size, in bytes, of the segment                                                                                                         BLOCKS           NUMBER       Y                Size, in Oracle blocks, of the segment                                                                                                 EXTENTS          NUMBER       Y                Number of extents allocated to the segment                                                                                             INITIAL_EXTENT   NUMBER       Y                Size, in bytes, of the initial extent of the segment                                                                                   NEXT_EXTENT      NUMBER       Y                Size, in bytes, of the next extent to be allocated to the segment                                                                      MIN_EXTENTS      NUMBER       Y                Minimum number of extents allowed in the segment                                                                                       MAX_EXTENTS      NUMBER       Y                Maximum number of extents allowed in the segment                                                                                       MAX_SIZE         NUMBER       Y                Maximum number of blocks allowed in the segment                                                                                        RETENTION        VARCHAR2(7)  Y                Retention option for SECUREFILE segment                                                                                                MINRETENTION     NUMBER       Y                Minimum Retention Duration for SECUREFILE segment                                                                                      PCT_INCREASE     NUMBER       Y                Percent by which to increase the size of the next extent to be allocated                                                               FREELISTS        NUMBER       Y                Number of process freelists allocated in this segment                                                                                  FREELIST_GROUPS  NUMBER       Y                Number of freelist groups allocated in this segment                                                                                    RELATIVE_FNO     NUMBER       Y                Relative number of the file containing the segment header                                                                              BUFFER_POOL      VARCHAR2(7)  Y                The default buffer pool to be used for segments blocks                                                                                 FLASH_CACHE      VARCHAR2(7)  Y                                                                                                                                                       CELL_FLASH_CACHE VARCHAR2(7)  Y                                                                                                                                                       SQL> 

segment段对象有如下类型：

"TABLE", "CLUSTER", "INDEX", "ROLLBACK","DEFERRED ROLLBACK", "TEMPORARY","SPACE HEADER", "TYPE2 UNDO" or "CACHE" 1   SYS I_USER1     INDEX   MSSM    SYSTEM  1   416 65536   8   1   65536   1048576 1   2147483645  2147483645              1   1   1   DEFAULT DEFAULT DEFAULT2   SYS CON$       TABLE   MSSM    SYSTEM  1   288 655360  80  10  65536   1048576 1   2147483645  2147483645              1   1   1   DEFAULT DEFAULT DEFAULT3   SYS UNDO$      TABLE   MSSM    SYSTEM  1   224 65536   8   1   65536   1048576 1   2147483645  2147483645              1   1   1   DEFAULT DEFAULT DEFAULT4   SYS C_COBJ#     CLUSTER MSSM    SYSTEM  1   296 3145728 384 18  57344   1048576 1   2147483645  2147483645              1   1   1   DEFAULT DEFAULT DEFAULT5   SYS I_OBJ#      INDEX   MSSM    SYSTEM  1   168 327680  40  5   65536   1048576 1   2147483645  2147483645              1   1   1   DEFAULT DEFAULT DEFAULT6   SYS PROXY_ROLE_DATA$       TABLE   MSSM    SYSTEM  1   264 65536   8   1   65536   1048576 1   2147483645  2147483645              1   1   1   DEFAULT DEFAULT DEFAULT

查看某个用户下的表

select a.owner,a.segment_name,a.BYTES,a.blocks from dba_segments a where a.segment_type='TABLE' and a.owner='xxx' order by a.BYTES desc

查看oracle对于表的统计信息，可以使用dba_tables

SQL> select TABLE_NAME,STATUS,NUM_ROWS,AVG_ROW_LEN,TABLE_LOCK,LAST_ANALYZED from dba_tables where owner='xxx'  2  ;TABLE_NAME                     STATUS     NUM_ROWS AVG_ROW_LEN TABLE_LO LAST_ANALYZED------------------------------ -------- ---------- ----------- -------- -------------------NT                             VALID        690622         217 ENABLED  2017-06-18 06:01:06N_FILE                         VALID       1471988         264 ENABLED  2017-06-18 06:02:15NTTS                           VALID        690622          51 ENABLED  2017-06-19 22:01:39TEST                           VALID                           ENABLED