如何看懂ORACLE的执行计划

mahan · 发表于 2023-9-17 12:37:27

1、看懂Oracle执行计划
上面已经介绍了如何查看执行计划，现在简单介绍一下一些基本方法和相关理论知识

1.1 查看explain
找一条比较复杂的SQL：

SQL_ID  4qfq3t2ukm0y1, child number 0
-------------------------------------
SELECT /*+ monitor*/ A.USER_CODE, A.FULL_NAME, A.USER_PWD, C.UNIT_CODE,
C.UNIT_NAME FROM BASE_USER A LEFT JOIN (SELECT UR.USER_CODE,
UR.UNIT_CODE FROM APPR_USER_ROLE UR WHERE UR.USER_ROLE < 10) B ON
A.USER_CODE = B.USER_CODE LEFT JOIN LZCITY_APPROVE_UNIT_INFO C ON
B.UNIT_CODE = C.UNIT_CODE WHERE C.UNIT_CODE ='15803'

Plan hash value: 3288287052

------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                      | Starts | E-Rows | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT             |                            |    1 |       |    3 (100)|       |    16 |00:00:00.01 |    38 |
| 1 |  NESTED LOOPS                |                            |    1 |    1 |    3 (0)| 00:00:01 |    16 |00:00:00.01 |    38 |
| 2 | NESTED LOOPS                |                            |    1 |    1 |    3 (0)| 00:00:01 |    16 |00:00:00.01 |    22 |
| 3 | NESTED LOOPS             |                            |    1 |    1 |    2 (0)| 00:00:01 |    16 |00:00:00.01 |    5 |
| 4 |    TABLE ACCESS BY INDEX ROWID| LZCITY_APPROVE_UNIT_INFO |    1 |    1 |    1 (0)| 00:00:01 |    1 |00:00:00.01 |    3 |
|*  5 |    INDEX UNIQUE SCAN       | PK_LZCITY_APPROVE_UNIT_INFO |    1 |    1 |    0 (0)|       |    1 |00:00:00.01 |    2 |
|*  6 |    INDEX RANGE SCAN          | PK_APPR_USER_ROLE          |    1 |    1 |    1 (0)| 00:00:01 |    16 |00:00:00.01 |    2 |
|*  7 | INDEX UNIQUE SCAN          | PK_BASE_USER             |    16 |    1 |    0 (0)|       |    16 |00:00:00.01 |    17 |
| 8 | TABLE ACCESS BY INDEX ROWID  | BASE_USER                |    16 |    1 |    1 (0)| 00:00:01 |    16 |00:00:00.01 |    16 |
------------------------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$E3445A69
4 - SEL$E3445A69 / C@SEL$4
5 - SEL$E3445A69 / C@SEL$4
6 - SEL$E3445A69 / UR@SEL$2
7 - SEL$E3445A69 / A@SEL$3
8 - SEL$E3445A69 / A@SEL$3

Outline Data
-------------

  /*+
   BEGIN_OUTLINE_DATA
   IGNORE_OPTIM_EMBEDDED_HINTS
   OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
   DB_VERSION('11.2.0.4')
   ALL_ROWS
   OUTLINE_LEAF(@"SEL$E3445A69")
   MERGE(@"SEL$2")
   OUTLINE(@"SEL$A2E96217")
   OUTER_JOIN_TO_INNER(@"SEL$E9F4A6F9" "B"@"SEL$1")
   OUTER_JOIN_TO_INNER(@"SEL$E9F4A6F9" "C"@"SEL$4")
   OUTLINE(@"SEL$2")
   OUTLINE(@"SEL$E9F4A6F9")
   MERGE(@"SEL$80808B20")
   OUTLINE(@"SEL$6")
   OUTLINE(@"SEL$80808B20")
   MERGE(@"SEL$4")
   MERGE(@"SEL$F1D6E378")
   OUTLINE(@"SEL$5")
   OUTLINE(@"SEL$4")
   OUTLINE(@"SEL$F1D6E378")
   MERGE(@"SEL$1")
   OUTLINE(@"SEL$3")
   OUTLINE(@"SEL$1")
   INDEX_RS_ASC(@"SEL$E3445A69" "C"@"SEL$4" ("LZCITY_APPROVE_UNIT_INFO"."UNIT_CODE"))
   INDEX(@"SEL$E3445A69" "UR"@"SEL$2" ("APPR_USER_ROLE"."UNIT_CODE" "APPR_USER_ROLE"."USER_CODE" "APPR_USER_ROLE"."AREA_SEQ"
            "APPR_USER_ROLE"."USER_ROLE"))
   INDEX(@"SEL$E3445A69" "A"@"SEL$3" ("BASE_USER"."USER_CODE"))
   LEADING(@"SEL$E3445A69" "C"@"SEL$4" "UR"@"SEL$2" "A"@"SEL$3")
   USE_NL(@"SEL$E3445A69" "UR"@"SEL$2")
   USE_NL(@"SEL$E3445A69" "A"@"SEL$3")
   NLJ_BATCHING(@"SEL$E3445A69" "A"@"SEL$3")
   END_OUTLINE_DATA
  */

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

5 - access("C"."UNIT_CODE"='15803')
6 - access("UR"."UNIT_CODE"='15803' AND "UR"."USER_ROLE"<10)
   filter("UR"."USER_ROLE"<10)
7 - access("A"."USER_CODE"="UR"."USER_CODE")

1.2 explain执行顺序
所以不管是用F5方式还是set statistics_level=ALL方式，都有Operation参数，Operation表示sql执行过程，查看怎么执行的，有两个规则：

根据Operation缩进判断，缩进最多的最先执行；
Operation缩进相同时，最上面的是最先执行的；
如图执行计划，根据规则，可以得出执行顺序：INDEX UNIQUE SCAN->TABLE ACCESS BY INDEX ROWID->INDEX RANGE SCAN ->NESTED LOOPS ->INDEX UNIQUE SCAN->NESTED LOOPS ->TABLE ACCESS BY INDEX ROWID->NESTED LOOPS-> SELECT STATEMENT

登录/注册后可看大图

59797650681b3ce282.png (58.35 KB, 下载次数: 339)

下载附件

2023-9-17 12:33 上传

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

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

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

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

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

1.3.2 ROWID扫描（TABLE ACCESS BY ROWID）
ROWID扫描（TABLE ACCESS BY 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)

登录/注册后可看大图

54898650681dee31e3.png (39.06 KB, 下载次数: 345)

下载附件

2023-9-17 12:34 上传

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;

访问索引（TABLE ACCESS BY INDEX SCAN）的情况就比较多了，可以分为：

索引唯一扫描（INDEX UNIQUE SCAN）
索引全扫描（INDEX FULL SCAN）
索引范围扫描（INDEX RANGE SCAN）
索引快速全扫描（INDEX FAST FULL SCAN）
索引跳跃式扫描（INDEX SKIP SCAN）
1.3.3 索引唯一扫描（INDEX UNIQUE SCAN）
索引唯一扫描（INDEX UNIQUE SCAN）
索引唯一性扫描(INDEX UNIQUE SCAN)是针对唯一性索引(UNIQUE INDEX)来说的，也就是建立唯一性索引才能索引唯一性扫描，唯一性扫描，其结果集只会返回一条记录。

索引范围扫描（INDEX RANGE SCAN）
1.3.4 索引范围扫描（INDEX RANGE SCAN）
索引范围扫描（INDEX RANGE SCAN）索引范围扫描(INDEX RANGE SCAN)适用于所有类型的B树索引，一般不包括唯一性索引，因为唯一性索引走索引唯一性扫描。当扫描的对象是非唯一性索引的情况，where谓词条件为Between、=、<、>等等的情况就是索引范围扫描，注意，可以是等值查询，也可以是范围查询。如果where条件里有一个索引键值列没限定为非空的，那就可以走索引范围扫描，如果改索引列是非空的，那就走索引全扫描
前面说了，同样的SQL建的索引不同，就可能是走索引唯一性扫描，也有可能走索引范围扫描。在同等的条件下，索引范围扫描所需要的逻辑读和索引唯一性扫描对比，逻辑读如何？索引范围扫描可能返回多条记录，所以优化器为了确认，肯定会多扫描，所以在同等条件，索引范围扫描所需要的逻辑读至少会比相应的唯一性扫描的逻辑读多1

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

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

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

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

索引快速全扫描只适应于CBO(基于成本的优化器)
索引快速全扫描可以使用多块读，也可以并行执行
索引全扫描会按照叶子块排序返回，而索引快速全扫描则是按照索引段内存储块顺序返回
索引快速全扫描的执行结果不一定是有序的，而索引全扫描的执行结果是有序的，因为索引快速全扫描是根据索引行在磁盘的物理存储顺序来扫描的，不是根据索引行的逻辑顺序来扫描的
1.3.7 索引跳跃式扫描（INDEX SKIP SCAN）
索引跳跃式扫描（INDEX SKIP SCAN）
索引跳跃式扫描(INDEX SKIP SCAN)适用于所有类型的复合B树索引(包括唯一性索引和非唯一性索引)，索引跳跃式扫描可以使那些在where条件中没有目标索引的前导列指定查询条件但是有索引的非前导列指定查询条件的目标SQL依然可以使用跳跃索引

如图执行计划就有INDEX RANGE SCAN、 INDEX UNIQUE SCAN 等等

登录/注册后可看大图

8617965068217ee08f.png (86.27 KB, 下载次数: 356)

下载附件

2023-9-17 12:35 上传

1.4 表连接方法
如图，执行计划中有如下NESTED LOOPS等等这些，是什么？这种其实就是Oracle中表连接的方法

登录/注册后可看大图

245906506823df017b.png (79.42 KB, 下载次数: 340)

下载附件

2023-9-17 12:36 上传

两个表之间的表连接方法有排序合并连接、嵌套循环连接、哈希连接、笛卡尔连接

排序合并连接（merge sort join）
merge sort join是先将关联表的关联列各自做排序，然后从各自的排序表中抽取数据，到另一个排序表中做匹配

嵌套循环连接（Nested loop join）
Nested loops 工作方式是循环从一张表中读取数据(驱动表outer table)，然后访问另一张表（被查找表 inner table,通常有索引）。驱动表中的每一行与inner表中的相应记录JOIN。类似一个嵌套的循环。对于被连接的数据子集较小的情况，nested loop连接是个较好的选择

哈希连接（Hash join）
散列连接是CBO 做大数据集连接时的常用方式，优化器使用两个表中较小的表（或数据源）利用连接键在内存中建立散列表，然后扫描较大的表并探测散列表，找出与散列表匹配的行。

笛卡尔连接（Cross join）
如果两个表做表连接而没有连接条件，而会产生笛卡尔积，在实际工作中应该尽可能避免笛卡尔积

对于这些连接的详细介绍可以查看《收获，不止sql调优》一书，或者查看我做的读书笔记

1.5 explain参数信息
前面的学习，我们已经知道了执行计划执行的顺序、sql是做索引，还是全表扫描，或者是rowid扫描，但是如图执行计划还有很多参数，如图，比如Starts，E-Rows，Cost (%CPU)等等，这些参数表示什么含义？

登录/注册后可看大图

帐号		自动登录	找回密码
密码			注册

[Oracle] 如何看懂ORACLE的执行计划