oracle优化器

不念不忘少年蓝@ 2022-06-14 11:49 162阅读 0赞

### 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 |
    |* 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 processed
    
    SQL>

使用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 processed
    
    SQL>

即根据提示，  
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 processed
    
    SQL>

注：  
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_ROWS
    SQL>

优化器得到执行计划，将会决定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 processed
    
    SQL>

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          0
    
    SQL>

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

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

查看具体块的信息

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,65
    
    SQL>

（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 DEFAULT
    2   SYS CON$       TABLE   MSSM    SYSTEM  1   288 655360  80  10  65536   1048576 1   2147483645  2147483645              1   1   1   DEFAULT DEFAULT DEFAULT
    3   SYS UNDO$      TABLE   MSSM    SYSTEM  1   224 65536   8   1   65536   1048576 1   2147483645  2147483645              1   1   1   DEFAULT DEFAULT DEFAULT
    4   SYS C_COBJ#     CLUSTER MSSM    SYSTEM  1   296 3145728 384 18  57344   1048576 1   2147483645  2147483645              1   1   1   DEFAULT DEFAULT DEFAULT
    5   SYS I_OBJ#      INDEX   MSSM    SYSTEM  1   168 327680  40  5   65536   1048576 1   2147483645  2147483645              1   1   1   DEFAULT DEFAULT DEFAULT
    6   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:06
    N_FILE                         VALID       1471988         264 ENABLED  2017-06-18 06:02:15
    NTTS                           VALID        690622          51 ENABLED  2017-06-19 22:01:39
    TEST                           VALID                           ENABLED