Oracle SQL调优系列之优化器基础知识

朴灿烈づ我的快乐病毒、 2021-09-18 13:08 258阅读 0赞

### 文章目录 ###

*   *  一、访问数据的方法
     *   *  1.1、直接访问数据
         *   *  1.1.1 全表扫描
             *  1.1.2 ROWID扫描
         *  1.2、访问索引
         *   *  1.2.1 索引唯一扫描
             *  1.2.2 索引范围扫描
             *  1.2.3 索引全扫描
             *  1.2.4 索引快速全扫描
             *  1.2.5 索引跳跃式扫描
             *  拓展补充
     *  二、Oracle的优化器
     *   *  2.1 优化器简介
         *  2.2 SQL执行过程
         *  2.3 结果集
     *  三、优化器优化方式
     *   *  3.1 优化器的优化方式
         *  3.2 基于规则的优化器
         *   *  3.2.1 RBO简介
             *  3.2.2 RBO缺陷
             *  3.2.3 RBO执行过程
             *  3.2.4 RBO特殊情况
             *  3.2.5 强制CBO的情况
         *  3.3 基于成本的优化器
         *   *  3.3.1 CBO简介
             *  3.3.2 集的势
             *  3.3.3 可选择率
     *  四、优化器优化模式
     *   *  4.1 优化器优化模式分类
         *  4.2 优化模式使用方法

本博客介绍一下属于oracle优化器范畴的一些基础知识，访问数据的方法，分为直接访问数据的方法和访问索引的方法两种，然后有了这些基础知识后，可以参考学习我的另外一篇博客：Oracle优化器简介，对Oracle  
的一些原理的简单介绍，对于学习oracle方面的SQL优化是有帮助的，https://blog.csdn.net/u014427391/article/details/87656904

## 一、访问数据的方法 ##

Oracle访问表中数据的方法有两种，一种是直接表中访问数据，另外一种是先访问索引，如果索引数据不符合目标SQL，就回表，符合就不回表，直接访问索引就可以。  
本博客先介绍直接访问数据的方法，下一篇博客在访问索引的方法

### 1.1、直接访问数据 ###

Oracle直接访问表中数据的方法又分为两种：一种是全表扫描；另一种是ROWID扫描

#### 1.1.1 全表扫描 ####

全表扫描是Oracle直接访问数据的一种方法，全表扫描时从第一个区(EXTENT)的第一个块(BLOCK)开始扫描，一直扫描的到表的高水位线(High Water Mark)，这个范围内的数据块都会扫描到

全表扫描是采用多数据块一起扫的，并不是一个个数据库扫的，然后我们经常说全表扫描慢是针对数据量很多的情况，数据量少的话，全表扫描并不慢的，不过随着数据量越多，高水位线也就越高，也就是说需要扫描的数据库越多，自然扫描所需要的IO越多，时间也越多

注意：数据量越多，全表扫描所需要的时间就越多，然后直接删了表数据呢？查询速度会变快？其实并不会的，因为即使我们删了数据，高位水线并不会改变，也就是同样需要扫描那么多数据块

#### 1.1.2 ROWID扫描 ####

ROWID也就是表数据行所在的物理存储地址，所谓的ROWID扫描是通过ROWID所在的数据行记录去定位。ROWID是一个伪列，数据库里并没有这个列，它是数据库查询过程中获取的一个物理地址，用于表示数据对应的行数。  
用sql查询：

select t.* , rowid from 表格

随意获取一个ROWID序列：AAAWSJAAFAAAWwUAAA，前6位表示对象编号(Data Object number)，其后3位文件编号(Relative file number)，接着其后6位表示块编号(Block number)， 再其后3位表示行编号(Row number)  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTQ0MjczOTE_size_16_color_FFFFFF_t_70]

**ROWID编码方法是：A ~ Z表示0到25；a ~ z表示26到51；0~9表示52到61；+表示62；/表示63；刚好64个字符。**

这里随意找张表查一下文件编号、区编号、行编号，查询后会返回rowid的一系列物理地址和文件编号(rowid\_relative\_fno(rowid))、块编号(rowid\_block\_number(rowid))、行编号(rowid\_row\_number(rowid))

select t.seq,
           rowid,
           dbms_rowid.rowid_relative_fno(rowid),
           dbms_rowid.rowid_block_number(rowid),
           dbms_rowid.rowid_row_number(rowid)
      from t_info t

SQL查询一下表格名称为TABLE的对象编码

select owner,object_id,data_object_id,status from dba_objects where object_name='TABLE';

**相对文件id和绝对文件编码**  
相对文件id是指相对于表空间，在表空间唯一;绝对文件编码是指相当于全局数据库而言的，全局唯一；下面SQL查询一下相对文件id和绝对文件编码

select file_name,file_id,relative_fno from dba_data_files;

### 1.2、访问索引 ###

对于Oracle数据库来说，B树索引是最常见的了，下面给出B树索引的图，图来自《基于Oracle的SQL优化》一书：

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9zbWlsZW5pY2t5LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70]

对于B树索引，分成两种类型的数据块，一种是索引分支块，另外一种是索引叶子块，索引根块是一种特殊的索引分支块。

影响逻辑读的缓存:

清Buffer Cache

alter system flush buffer_cache;//请勿随意在生产环境执行此语句

清数据字典缓存(Data Dictionary Cache)

alter system flush shared_pool;//请勿随意在生产环境执行此语句

#### 1.2.1 索引唯一扫描 ####

索引唯一性扫描(INDEX UNIQUE SCAN)是针对唯一性索引(UNIQUE INDEX)来说的，也就是建立唯一性索引才能索引唯一性扫描，唯一性扫描，其结果集只会返回一条记录。  
创建唯一性索引SQL是：

create unique index 索引名 on 表名(列名)

例子，例子来自《基于Oracle的SQL优化》一书：

scott用户登录

SQL> create table emp_temp as select * from emp;//创建一个测试表
    SQL> create unique index idx_emp_temp on emp_temp(empno);//创建唯一型索引
    SQL> set autotrace on//设置执行计划显示
    SQL> select * from emp_temp where empno =7369;//走索引唯一性扫描
    
         EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
    ---------- ---------- --------- ---------- -------------- ---------- ----------
        DEPTNO
    ----------
          7369 SMITH      CLERK           7902 17-12月-80            800
            20
    
    
    
    执行计划
    ----------------------------------------------------------
    Plan hash value: 3451700904
    
    --------------------------------------------------------------------------------
    
    ------------
    
    | Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)
    
    | Time     |
    
    --------------------------------------------------------------------------------
    
    ------------
    
    |   0 | SELECT STATEMENT            |              |     1 |    87 |     1   (0)
    
    | 00:00:01 |
    
    |   1 |  TABLE ACCESS BY INDEX ROWID| EMP_TEMP     |     1 |    87 |     1   (0)
    
    | 00:00:01 |
    
    |*  2 |   INDEX UNIQUE SCAN         | IDX_EMP_TEMP |     1 |       |     0   (0)
    
    | 00:00:01 |
    
    --------------------------------------------------------------------------------
    
    ------------
    
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - access("EMPNO"=7369)

#### 1.2.2 索引范围扫描 ####

索引范围扫描(INDEX RANGE SCAN)适用于所有类型的B树索引，一般不包括唯一性索引，因为唯一性索引走索引唯一性扫描。 当扫描的对象是非唯一性索引的情况，where谓词条件为Between、=、<、>等等的情况就是索引范围扫描，注意，可以是等值查询，也可以是范围查询。**如果where条件里有一个索引键值列没限定为非空的，那就可以走索引范围扫描，如果改索引列是非空的，*那就走索引全扫描***

前面说了，同样的SQL建的索引不同，就可能是走索引唯一性扫描，也有可能走索引范围扫描。在同等的条件下，索引范围扫描所需要的逻辑读和索引唯一性扫描对比，逻辑读如何？索引范围扫描可能返回多条记录，所以优化器为了确认，肯定会多扫描，***所以在同等条件，索引范围扫描所需要的逻辑读至少会比相应的唯一性扫描的逻辑读多1***

继续上面的例子：

SQL> drop index idx_emp_temp;//删了刚才的唯一性索引
    
    索引已删除。
    
    SQL> create index idx_emp_temp on emp_temp(empno);//创建一个B树索引
    
    索引已创建。
    
    SQL> select * from emp_temp where empno=7369;//走索引范围扫描，因为empno没有限定为非空
    
         EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
    ---------- ---------- --------- ---------- -------------- ---------- ----------
        DEPTNO
    ----------
          7369 SMITH      CLERK           7902 17-12月-80            800
            20
    
    
    
    执行计划
    ----------------------------------------------------------
    Plan hash value: 351331621
    
    --------------------------------------------------------------------------------
    
    ------------
    
    | Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)
    
    | Time     |
    
    --------------------------------------------------------------------------------
    
    ------------
    
    |   0 | SELECT STATEMENT            |              |     1 |    87 |     2   (0)
    
    | 00:00:01 |
    
    |   1 |  TABLE ACCESS BY INDEX ROWID| EMP_TEMP     |     1 |    87 |     2   (0)
    
    | 00:00:01 |
    
    |*  2 |   INDEX RANGE SCAN          | IDX_EMP_TEMP |     1 |       |     1   (0)
    
    | 00:00:01 |
    
    --------------------------------------------------------------------------------
    
    ------------
    
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - access("EMPNO"=7369)
    
    Note
    -----
       - dynamic sampling used for this statement (level=2)

#### 1.2.3 索引全扫描 ####

索引全扫描(INDEX FULL SCAN)适用于所有类型的B树索引(包括唯一性索引和非唯一性索引)。

索引全扫描过程简述：索引全扫描是指扫描目标索引所有叶子块的索引行，但不意思着需要扫描所有的分支块，索引全扫描时只需要访问必要的分支块，然后定位到位于改索引最左边的叶子块的第一行索引行，就可以利用改索引叶子块之间的双向指针链表，从左往右依次顺序扫描所有的叶子块的索引行

索引全扫描的例子：直接查emp表就好，因为empno是非空的

SQL> select * from emp;
    
         EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
    ---------- ---------- --------- ---------- -------------- ---------- ----------
        DEPTNO
    ----------
          7369 SMITH      CLERK           7902 17-12月-80            800
            20
    
          7499 ALLEN      SALESMAN        7698 20-2月 -81           1600        300
            30
    
          7521 WARD       SALESMAN        7698 22-2月 -81           1250        500
            30
    
    
         EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
    ---------- ---------- --------- ---------- -------------- ---------- ----------
        DEPTNO
    ----------
          7566 JONES      MANAGER         7839 02-4月 -81           2975
            20
    
          7654 MARTIN     SALESMAN        7698 28-9月 -81           1250       1400
            30
    
          7698 BLAKE      MANAGER         7839 01-5月 -81           2850
            30
    
    
         EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
    ---------- ---------- --------- ---------- -------------- ---------- ----------
        DEPTNO
    ----------
          7782 CLARK      MANAGER         7839 09-6月 -81           2450
            10
    
          7788 SCOTT      ANALYST         7566 19-4月 -87           3000
            20
    
          7839 KING       PRESIDENT            17-11月-81           5000
            10
    
    
         EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
    ---------- ---------- --------- ---------- -------------- ---------- ----------
        DEPTNO
    ----------
          7844 TURNER     SALESMAN        7698 08-9月 -81           1500          0
            30
    
          7876 ADAMS      CLERK           7788 23-5月 -87           1100
            20
    
          7900 JAMES      CLERK           7698 03-12月-81            950
            30
    
    
         EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
    ---------- ---------- --------- ---------- -------------- ---------- ----------
        DEPTNO
    ----------
          7902 FORD       ANALYST         7566 03-12月-81           3000
            20
    
          7934 MILLER     CLERK           7782 23-1月 -82           1300
            10
    
    
    已选择14行。
    
    
    执行计划
    ----------------------------------------------------------
    Plan hash value: 3956160932
    
    --------------------------------------------------------------------------
    | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |      |    14 |   532 |     3   (0)| 00:00:01 |
    |   1 |  TABLE ACCESS FULL| EMP  |    14 |   532 |     3   (0)| 00:00:01 |
    --------------------------------------------------------------------------

#### 1.2.4 索引快速全扫描 ####

索引快速全扫描和索引全扫描很类似，也适用于所有类型的B树索引(包括唯一性索引和非唯一性索引)。和索引全扫描类似，也是扫描所有叶子块的索引行，这些都是索引快速全扫描和索引全扫描的相同点

索引快速全扫描和索引全扫描区别：

*  索引快速全扫描只适应于CBO(基于成本的优化器)
 *  索引快速全扫描可以使用多块读，也可以并行执行
 *  索引全扫描会按照叶子块排序返回，而索引快速全扫描则是按照索引段内存储块顺序返回
 *  索引快速全扫描的执行结果不一定是有序的，而索引全扫描的执行结果是有序的，因为索引快速全扫描是根据索引行在磁盘的物理存储顺序来扫描的，不是根据索引行的逻辑顺序来扫描的

条件是使用复合索引，而且使用Hint

/*+ index_ffs(表名 索引名) */

select /*+ index_ffs(emp_test pk_emp_test) */ empno from emp_test;

例子：

SQL> create table emp_test (empno number,ename varchar2(50));
    
    表已创建。
    
    SQL> alter table emp_test add constraint pk_emp_test primary key (empno,ename);
    
    表已更改。
    
    SQL> insert into emp_test select empno,ename from emp;
    
    已创建14行。
    
    SQL> commit;
    
    提交完成。
    
    SQL> select count(1) from emp_test;
    
    SQL> select /*+ index_ffs(emp_test pk_emp_test) */ empno from emp_test;
    
         EMPNO
    ----------
          7369
          7499
          7521
          7566
          7654
          7698
          7782
          7788
          7839
          7844
          7876
    
         EMPNO
    ----------
          7900
          7902
          7934
    
    已选择14行。
    
    
    执行计划
    ----------------------------------------------------------
    Plan hash value: 3550420785
    
    --------------------------------------------------------------------------------
    
    ----
    
    | Id  | Operation            | Name        | Rows  | Bytes | Cost (%CPU)| Time
       |
    
    --------------------------------------------------------------------------------
    
    ----
    
    |   0 | SELECT STATEMENT     |             |    14 |   182 |     2   (0)| 00:00:
    
    01 |
    
    |   1 |  INDEX FAST FULL SCAN| PK_EMP_TEST |    14 |   182 |     2   (0)| 00:00:
    
    01 |
    
    --------------------------------------------------------------------------------
    
    ----
    
    
    Note
    -----
       - dynamic sampling used for this statement (level=2)

#### 1.2.5 索引跳跃式扫描 ####

索引跳跃式扫描(INDEX SKIP SCAN)适用于所有类型的\*\*\*复合B树索引\*\*\*(包括唯一性索引和非唯一性索引)，索引跳跃式扫描可以使那些在where条件中没有目标索引的前导列指定查询条件但是有索引的非前导列指定查询条件的目标SQL依然可以使用跳跃索引，定义解释有点绕，举个例子说明

假如新建了复合索引:

create index 索引名 on 表名(列名1,列名2)

这里新建了复合索引，假如查询如：

select * from 表名 where 列名1 = 条件1

假如改目标SQL符合索引跳跃式扫描的条件，即使，只有列名1这个前导列，还是可以走索引跳跃式扫描的，这个就是跳跃式扫描，不需要如下sql，全部索引列都定位到

select * from 表名 where 列名1 = 条件1 and 列名2 = 条件2

当然索引跳跃式扫描并不是说适用所有情况，不加前导列，有时候是不走跳跃式扫描的，***Oracle中的索引跳跃式扫描仅仅适用于那些目标索引前导列的distinct值数量较少，后续非导列的可选择性又非常好的情况，索引跳跃式扫描的执行效率一定会随着目标索引前导列的distinct值数量的递增而递减的***

#### 拓展补充 ####

对于索引来说，如果索引条件有null值，是不走索引的

## 二、Oracle的优化器 ##

### 2.1 优化器简介 ###

优化器(Optimizer):优化器是Oracle数据库内置的一个核心子系统，负责解析SQL，Oracle优化器是Oracle系统的一个核心组件，其目的是按照一定的原则来获取目标SQL在当前情形下执行的最高效执行路径，也可以说是执行计划。

### 2.2 SQL执行过程 ###

Oracle SQL的执行过程：

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTQ0MjczOTE_size_16_color_FFFFFF_t_70 1]

### 2.3 结果集 ###

结果集(Row Source)是指包含指定执行结果的集合。结果集适用于RBO和CBO。对于CBO而言，结果集对应执行计划中的列(Rows)反映的就是CBO对于相关执行步骤所对应输出结果集的记录数(Cardinality)的估算值

## 三、优化器优化方式 ##

### 3.1 优化器的优化方式 ###

Oracle优化器按照优化方式分为两种

*  基于规则的优化器(Rule-Based Optimizer)，简称RBO
 *  基于成本的优化器(Cost-Based Optimizer)，简称CBO

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

#### 3.2.1 RBO简介 ####

基于规则的优化器(Rule-Based Optimizer)：所谓基于规则的优化器是指Oracle按照硬编码在数据库的一系列规则来决定SQL的执行计划，简称是RBO

#### 3.2.2 RBO缺陷 ####

RBO在oracle10后官方就不建议用，因为RBO并不支持oracle一些性能比较好的功能特性,也不会根据表的数据量等等获取执行计划，而且RBO定的那些规则也不是很容易修改。不过代码在oracle10后版本还是保存的，所以要学习测试也可以用SQL开启RBO模式

alter session set optimizer_mode='RULE';

#### 3.2.3 RBO执行过程 ####

RBO的执行过程：对于一条sql，oracle会事先给sql各种情况的执行计划定一个等级，一共有15个等级，从等级1到等级15，规则是等级越低执行效率越高，也就是等级1的执行计划执行效率是最高的。然后oracle自然就选出等级1的执行路径作为执行计划。

#### 3.2.4 RBO特殊情况 ####

*  对于执行路径一样的情况:假如出现执行路径一样的情况，这时候就要根据数据字典缓存来确定最低的等级了，意思就是获取缓存中的先后顺序确定哪条作为执行计划

#### 3.2.5 强制CBO的情况 ####

上面说了可以通过SQL开启CBO模式，这是针对普通情况的，假如出现下面情况，那就是强制使用CBO

*  SQL涉及对象有IOT(Index Organized Table)
 *  SQL涉及的对象分区表
 *  使用了并行查询或者并行DML
 *  使用了星型连接
 *  使用了哈希连接
 *  使用了索引快速全扫描
 *  使用了函数索引
 *  …

这些情况总结来自《基于Oracle的SQL优化一书》

虽然Oracle针对上述情况都开启了强制CBO，但是我们还是可以手动解决的，方法也是来自《基于Oracle的SQL优化一书》，作者提供了改写等价sql的方法，比如在sql的where条件中对number或者date类型的列加0，

select * from 表格 where a+0>参数

如果是varchar2类型的，加可以加个空字符串

select * from 表格 where a || '' = 参数

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

#### 3.3.1 CBO简介 ####

介绍一下基于成本的优化器(Cost-Based Optimization)：基于成本的优化器简称是CBO，在SQL执行过程，会缓存执行的一些信息到Oracle的数据字典里，这里的信息就有sql执行路径的I/O、网络资源、CPU的使用情况，其实这个就是SQL的执行成本，也是按照这个成本来确定执行计划。所以CBO概念就是根据I/O、网络资源、CPU的使用情况来确定SQL执行路径也可以说是执行计划的优化器。

#### 3.3.2 集的势 ####

集的势(Cardinality)是CBO特有的概念，集的势指结果集的行数。引入这个概念是为了表示SQL执行成本值，Cardinality越大，也就是说sql执行返回的结果集所包含的行数就越多，也说明成本越大。

#### 3.3.3 可选择率 ####

可选择率(Selectivity)：指施加指定谓语条件后返回结果集的记录数占未施加任何谓语条件的原始结果集的记录数的比率。可选择率的范围是0~1,它的值越小，说明可选择性越好，值越大说明可选择性越差，也就是成本值越大。可选择率为1时性能是最差的。

可选择率 = 施加指定谓语条件后返回结果集的记录数/未施加任何谓语条件的原始结果集的记录数。

## 四、优化器优化模式 ##

### 4.1 优化器优化模式分类 ###

优化器优化模式分为Rule、Choose、First rows、All rows

*  Rule：就是基于规则Rule的方式
 *  Choose：当一个表或索引有统计信息,则走CBO的方式,如果表或索引没统计信息,表又不是特别的小,而且相应的列有索引时,那么就走索引,走RBO的方式。这是Oracle的默认方式
 *  First rows：与Choose方式是类似的,所不同的是当一个表有统计信息时,它将是以最快的方式返回查询的最先的几行,从总体上减少了响应时间。
 *  All rows：其实就是基于Cost方式

### 4.2 优化模式使用方法 ###

要修改优化模式可以使用类似SQL

alter session set optimizer_mode='RULE';

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTQ0MjczOTE_size_16_color_FFFFFF_t_70]: /images/20210812/eeedd9af684c4608b3e2cafbd0dde32a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9zbWlsZW5pY2t5LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70]: /images/20210812/bccc5003c7464c21a7860f845944b68f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTQ0MjczOTE_size_16_color_FFFFFF_t_70 1]: /images/20210812/69b62e1e55ab48f182331ede0ddb8fc0.png