第三章循规蹈矩——如何读懂SQL执行计划

参考《收获，不止SQL优化》作者: 梁敬彬 / 梁敬弘

一、什么是SQL执行计划

以上SQL，是先访问T1表再访问T2表，还是先访问T2表再访问T1表？这两种访问方式就是两个不同的执行计划。

那Oracle会如何选择执行计划呢？是看哪个执行开销更低。开销更低，意味着性能更好，速度更快。这个过程就是Oracle的解析过程，一般1S内可以完成。然后数据库会把开销更低的这个执行计划保存下来放到SGA的Shared Pool里，后续如果再执行同样的SQL，可能不用再解析，只需要在Shared Pool里获取到执行计划就行。前者是硬解析（Oracle进行解析生成执行计划），后者是软解析（不分析直接从Shared Pool里获取到执行计划）。

二、SQL执行计划选定依据

1、如何判断哪个执行计划开销更低？主要是关注统计信息。

第三章循规蹈矩——如何读懂SQL执行计划

2、这些统计信息是如何进行收集的呢？

Step1:设定空闲的时间，进行集中收集；

Step2:当表变动量很少，未超过设定的阈值，不重新收集；

Step3:可手动进行收集，可只针对某个表的分区、索引进行收集。

第三章循规蹈矩——如何读懂SQL执行计划

3、如何手动进行统计信息的收集？

-- 表相关统计信息
SELECT T.TABLE_NAME, T.NUM_ROWS, T.BLOCKS, T.LAST_ANALYZED
  FROM USER_TABLES T
 WHERE T.TABLE_NAME IN ('TTRD_WMPS_UNIT');

-- 索引相关统计信息
SELECT T.TABLE_NAME,T.INDEX_NAME,T.blevel,T.NUM_ROWS,T.LEAF_BLOCKS,T.LAST_ANALYZED
  FROM USER_INDEXES T
 WHERE T.TABLE_NAME IN ('TTRD_WMPS_UNIT');
 
 
-- 收集表/索引统计信息
CALL dbms_stats.gather_table_stats(ownname => 'TRD_CAMS',tabname => 'TTRD_WMPS_UNIT',estimate_percent => 10,method_opt => 'for all indexed columns');

-- 收集索引统计信息
CALL dbms_stats.gather_index_stats(ownname => 'TRD_CAMS',indname => 'PK_WMPS_UNIT',estimate_percent => 10,degree => 4 );

-- 收集表/索引统计信息
CALL dbms_stats.gather_table_stats(ownname => 'TRD_CAMS',tabname => 'TTRD_WMPS_UNIT',estimate_percent => 10,method_opt => 'for all indexed columns',cascade => true);-- plsql工具执行报错
exec dbms_stats.gather_table_stats(ownname => 'TRD_CAMS',tabname => 'TTRD_WMPS_UNIT',estimate_percent => 10,method_opt => 'for all indexed columns',cascade => true);-- 命令窗口执行成功

-- 分区表
SELECT COUNT(1) FROM REPORT_LD_ASSET_MANAGEUNDER ;
CALL dbms_stats.gather_table_stats(ownname => 'REGULATORY',tabname => 'REPORT_LD_ASSET_MANAGEUNDER',partname => 'P20220701',estimate_percent => 10,method_opt => 'for all indexed columns');

SELECT T.TABLE_NAME, T.NUM_ROWS, T.BLOCKS, T.LAST_ANALYZED
  FROM USER_TABLES T
 WHERE T.PARTITIONED = 'YES'
   AND T.TABLE_NAME IN ('REPORT_LD_ASSET_MANAGEUNDER');-- 更新

SELECT T.TABLE_NAME,T.INDEX_NAME,T.blevel,T.NUM_ROWS,T.LEAF_BLOCKS,T.LAST_ANALYZED
  FROM USER_INDEXES T
 WHERE T.TABLE_NAME IN ('REPORT_LD_ASSET_MANAGEUNDER');-- 更新

-- 只更新一个分区   
select t.table_name,t.partition_name,t.num_rows,t.last_analyzed from user_tab_partitions t where table_name in ('REPORT_LD_ASSET_MANAGEUNDER');

第三章循规蹈矩——如何读懂SQL执行计划

“- dynamic statistics used: dynamic sampling (level=2)”

如果执行计划里有如上信息，表示动态采样(一般USER_TABLES表对应的TABLE，行数块数最后分析时间等为空，会动态采样)。

比如表是早上9点新建的，建完以后再插入数据，Oracle是每天晚上12点动态收集统计信息。那么早9点到晚12点之间执行的SQL语句中包含此表的话，统计表中该TABLE对应的统计字段为空，就会动态收集信息。且动态收集后并不会将统计信息记录下来，只能等晚12点统一收集，或手动执行语句收集。

三、获取执行计划的六种方法

-- 方法一、EXPLAIN PLAN FOR 
set linesize 1000 
set pagesize 2000
-- 1
EXPLAIN PLAN FOR
SELECT * FROM TTRD_WMPS_UNIT WHERE STATUS='1';
-- 2
SELECT * FROM TABLE(dbms_xplan.display());


Plan hash value: 2172943561
 
-----------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                     |     1 |  2241 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TTRD_WMPS_UNIT      |     1 |  2241 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | TTRD_WMPS_UNIT_IDX1 |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("STATUS"=1)
 
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


-- 方法二、 set autotrace on
set linesize 266
set timing on
set pagesize 5000
SET AUTOTRACE OFF ---------------- 不生成AUTOTRACE 报告，这是缺省模式
SET AUTOTRACE ON EXPLAIN ------ AUTOTRACE只显示优化器执行路径报告
SET AUTOTRACE ON STATISTICS -- 只显示执行统计信息
SET AUTOTRACE ON ----------------- 包含执行计划和统计信息
SET AUTOTRACE TRACEONLY ------ 同set autotrace on，但是不显示查询输出

在ORACLE的SQLPLUS里用
输入命令回车就行了
create public synonym plan_table for plan_table;
grant all on plan_table to public;
conn itsm_sac_kaifa/itsm ;


SET AUTOTRACE ON/TRACEONLY
SELECT * FROM TTRD_WMPS_UNIT WHERE STATUS='1';

-- 方法三、statistics_level=all
-- 3.1
set pagesize 0
set linesize 1000
set AUTOTRACE off 

-- （1）
alter session set statistics_level=all ;
-- （2）、执行语句
SELECT * FROM T_SAMPLE_7 WHERE OBJECT_ID=9;
-- （3）、输出
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

-- set serveroutput off ;
-- 3.2，用/*+ gather plan statist cs */，省略statistics_level=all
set pagesize 0
set linesize 1000
-- （1）、执行语句
SELECT /*+ gather plan statist cs */* FROM T_SAMPLE_7 WHERE OBJECT_ID=20;
-- （2）、输出
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
 

-- 方法四、 dbms_xplan.display_cursor 获取
-- 找到sql_id
select * from v$sql l where l.SQL_FULLTEXT like '%T_SAMPLE_7%' ORDER BY L.LAST_ACTIVE_TIME DESC;-- sql_id=3xmv0avhrqkt3

-- 4.1
-- 查询sql_id对应的执行计划
select * from table(dbms_xplan.display_cursor('3xmv0avhrqkt3')) ;

-- 输出结果
SQL_ID  3xmv0avhrqkt3, child number 0 
-------------------------------------
SELECT * FROM T_SAMPLE_7 WHERE OBJECT_ID=9
 
Plan hash value: 1512084632
 
------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                      |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T_SAMPLE_7           |     1 |   132 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | IDX_T_SAMPLE_7_OBJID |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("OBJECT_ID"=9)
 

-- child number 0 表示第一个执行计划，缺省0，若有多个执行计划，加参数即可
select * from table(dbms_xplan.display_cursor('3xmv0avhrqkt3',0)) ;-- 输出执行计划
select * from table(dbms_xplan.display_cursor('3xmv0avhrqkt3',1)) ;-- SQL_ID: 3xmv0avhrqkt3, child number: 1 cannot be found 

-- 4.2
-- 查询sql_id对应的执行计划(如果有AWR性能视图里有的话？)
select * from table(dbms_xplan.display_awr('3xmv0avhrqkt3')) ;


-- 方法五、事件 10046 trace 跟踪
/* 
步骤 1: alter session set events '10046 trace name context forever,level 12 '; （开启跟踪）
步骤 2: 执行你的语旬
步骤 3: alter session set events '10046 trace name context off '; （关闭跟踪）
步骤 4: 找到跟踪后产生的文件
步骤 5: tkprof trc文件 目标文件 sys=no sort=prsela,exeela,fchela （格式化命令）
*/

set autotrace off 
alter session set statistics_level=typical; 

-- 步骤 1: 开启跟踪
alter session set events '10046 trace name context forever,level 12 '; 

-- 步骤 2: 执行你的语旬
SELECT OBJECT_ID,OWNER,DATA_OBJECT_ID FROM T_SAMPLE_7 WHERE OBJECT_ID=20;

-- 步骤 3: 关闭跟踪
alter session set events '10046 trace name context off ';

-- 步骤 4: 找到跟踪后产生的文件
-- 4.1、分别找到路径和会话的spid，拼接而成
select a.spid from v$process a,v$session b where a.addr=b.paddr and b.audsid=userenv('sessionid'); -- 会话的spid=5668

-- 从11gR1开始，Oracle引入了新的诊断结构，以参数DIAGNOSTIC_DEST控制存放trace文件与core文件的路径。
show parameter diagnostic_dest;-- 我本机是19C版本，查出来的路径是'D:\app\qiying.li\'，查看发现文件生成在'D:\app\qiying.li\diag\rdbms\orcl\orcl\trace\'下

-- 11gR1以前,如果是用户进程，10046 trace文件会被生成在user_dump_dest下,如果是后台进程，trace文件会被生成在background_dump_dest下。
SQL> show parameter user_dump_dest ;-- 我本机是19C版本，查出来的路径是'D:\05\19C\RDBMS\TRACE'，但是找不到最新的trace文件
SQL> show parameter background_dump_dest;

-- 4.2、直接用sql语句拼接好路径和spid并输出（11gR1以前,文件路径在参数'user_dump_dest'；之后的版本直接这么获取可能路径不对找不到文件）
select a.value || '/' || b.instance_name || '_ora_' || c.spid || '.trc' trace_file
  from (select value from v$parameter where name = 'user_dump_dest') a,
       (select instance_name from v$instance) b,
       (select spid
          from v$process
         where addr =
               (select paddr
                  from v$session
                 where sid = (select distinct sid from v$mystat))) c;


EXIT;

-- 步骤 5: 格式化命令（orcl_ora_spid.trc，退出sql命令界面，直接cmd中执行以下命令即可）
tkprof D:\app\qiying.li\diag\rdbms\orcl\orcl\trace\orcl_ora_2584.trc  D:\10046.txt  sys=no sort=prsela,exeela,fchela


-- 方法六、awrsqrpt.sql
-- 参考《第一章 全局在胸——用工具对SQL整体优化》

方法一、EXPLAIN PLAN FOR

第三章循规蹈矩——如何读懂SQL执行计划

方法二、SET AUTOTRACE ON

SET AUTOTRACE ON

SET AUTOTRACE TRACEONLY – 返回结果不输出

虽然要先执行语句，再展示执行计划，但这个执行计划并不是真正的执行计划，同EXPLAINPLAN FOR

第三章循规蹈矩——如何读懂SQL执行计划

方法三、statistics_level=all

3.1、statistics_level=all

第三章循规蹈矩——如何读懂SQL执行计划

3.2、直接

第三章循规蹈矩——如何读懂SQL执行计划

方法四、dbms_xplan.display_cursor 获取

第三章循规蹈矩——如何读懂SQL执行计划

方法五、事件10046 TRACE 跟踪

这种方法的关键是找到存放trace文件的路径+该会话的spid值

具体方法和解读可参考：https://blog.csdn.net/weixin_40913898/article/details/120622200

PARSE（SQL解析）,EXEC（执行）,FETCH（获取数据）

第三章循规蹈矩——如何读懂SQL执行计划

方法六、awrsqrpt.sql

第三章循规蹈矩——如何读懂SQL执行计划

四、这六种获取执行计划的方法的差异

什么时候该用何种方法？

1、EXPLAIN PLAIN FOR

这种方法没有真正去执行SQL，是最简单的方法。主要看下访问顺序、用到了什么索引、关联条件，同在PLSQL中按F5获取到的信息差不多。

2、SET AUTOTRACE ON/TRACEONLY

会去执行SQL，但是展示的不是真正的执行计划（SET AUTOTRACE的执行计划仍然来自于EXPLAIN PLAN），除了没有STARTS/E-ROWS/A-ROWS其他信息基本都有。

3、statistics_level=all

展示的是真正执行的执行计划，如果想看STARTS/E-ROWS/A-ROWS这些信息，用此方法。虽没有物理读，但有逻辑读（buffers），逻辑读才是重点。也没有调用次数等信息。（只有此方法有STARTS/E-ROWS/A-ROWS这些信息）

4、dbms_xplan.display_cursor 获取

展示的是真正执行的执行计划，但是是已经执行过的SQL，所以需要先找到SQL_ID。展示的信息同EXPLAIN PLAN FOR差不多，但如果同一SQL（SQL_ID）有多个执行计划，用此方法可以分别展示。（若非分析多个执行计划，一般不用此方法，因为相关信息3中也有了）

5、事件10046 TRACE 跟踪

展示的是真正执行的执行计划，此方法操作比较复杂，如果SQL中使用的函数中还有SQL，用此方法可以清晰列出。还可以看到SQL对应的等待事件。无STARTS/E-ROWS/A-ROWS这些信息。

6、awrsqrpt.sql

展示的是真正执行的执行计划，某段时间某个SQL（SQL_ID）的执行频率和时间，若有多个执行计划，也会一一列出。主要用来分析某段时间内的整体执行情况。

7、方法三和方法五的信息结合，完全足够分析SQL语句的真实执行计划了。

第三章循规蹈矩——如何读懂SQL执行计划

五、如何读懂执行计划

第三章循规蹈矩——如何读懂SQL执行计划

【单独型】

执行顺序：3->2->1，父子关系

第三章循规蹈矩——如何读懂SQL执行计划

【联合型】

1、非关联

第三章循规蹈矩——如何读懂SQL执行计划

2、关联型

2.1 关联型（NL）

情况1：

ID=2处返回10行（A-Rows）,Id=3处DEPT表被访问10次（Starts=10）,驱动表和被驱动表之间有关联关系。

第三章循规蹈矩——如何读懂SQL执行计划

情况2：

以下SQL也有关联关系，但是Id=2处返回14886行，Id=3处为什么表只访问了38次？不应该是14886行？

第三章循规蹈矩——如何读懂SQL执行计划

原因如下：

驱动表的P_CLASS与被驱动表关联，而驱动表过滤后，P_CLASS只有38条不重复的值。驱动表返回多少条不重复记录，被驱动表就被访问多少次。这是Oracle的一种优化。TTRD_P_CLASS表访问了38次，只34行记录能匹配到。

网上说明：

正常的NESTED LOOPS中，被驱动表的执行次数为驱动表的结果集行数。

对于NESTED LOOPS ANTI/SEMI的反连接和半连接，从探究实验的结果看，被驱动表的访问次数为驱动表的连接条件的去重后的值。

第三章循规蹈矩——如何读懂SQL执行计划

情况3：

NESTED LOOPS，被驱动表的执行次数为驱动表的结果集行数。

以下第一张图，no_unnest 好像少了一杠，所以还是NL连接

第三章循规蹈矩——如何读懂SQL执行计划

2.2 关联型（FILTER）

书中原句“FILTER 其实对比 NESTED LOOPS 是一种优化，驱动表返回多少条不重复记录，被驱动表被访问多少次”。

第三章循规蹈矩——如何读懂SQL执行计划

2.3、关联型（ UPDATE )

分析：

ID=2, EMP全表扫描，访问1次，得到14条记录

ID=4, EMP全表扫描，访问3次（不同的deptno有3个）,总共得到14条记录

ID=3, ID=4处得到的结果访问三次，做三次汇总计算，得到3条记录

ID=6, EMP全表扫描，访问1次，得到14条记录

ID=5, ID=6处得到的结果访问1次，全部数据做一次汇总，得到1条记录

ID=1,UPDATE

第三章循规蹈矩——如何读懂SQL执行计划

2.4、关联型（CONNECT BY WITH FLITERING）（树形查询）

为什么ID=5（CONNECT BY PUMP）处,Starts=4？

因为此树结构为4层：

第1次访问是PARENT_ID IS NULL

第2次访问是PRIOR NODE_ID（PARENT_ID IS NULL对应的NODE_ID） = PARENT_ID

……

第三章循规蹈矩——如何读懂SQL执行计划

“TABLE ACCESS BY INDEX ROWID BATCHED”

网上说明：

Oracle 12c中新增通过ROWID BATCHED访问数据块的方式，优化原来使用单个rowid进行数据块访问方式带来的资源消耗及对数据块的扫描次数，即TABLE ACCESS BY INDEX ROWID BATCHED特性。该特性通过隐藏参数“ _optimizer_batch_table_access_by_rowid ”控制，默认值为 true ，即默认开启。数据库将对从索引中检索的rowid进行排序，然后按块顺序访问行，从而减少数据库必须访问的块的次数以降低资源的消耗。

六、从执行计划中判断出问题

第三章循规蹈矩——如何读懂SQL执行计划

除了预测错外。如果在执行计划中有 “COUNT STOPKEY” 关键字 ,还可能是rownum分页查询的执行计划，表示在第10行就停止前进了。

第三章循规蹈矩——如何读懂SQL执行计划

七、问题

问题1：表是很早建的，索引是新加的，索引还未有统计信息，此时执行的SQL会动态收集吗？

加索引的时候就会统计一次当前信息，索引有统计信息，问题不成立。而且SQL是否会动态收集，是看表是否有统计信息，而不是索引是否有统计信息。（加索引是否会统计一次当前信息？不一定，不同的库测出来结果不同）

问题2：本地库测试，建表建索引后，自动就搜集了统计信息，是否跟版本或参数有关？

应该无关。

CREATE TABLE A AS SELECT * FROM TABLE B;-- 用此方法建表，表会自动搜集统计信息。

CREATE TABLE A();

INSERT INTO A SELECT * FROM B; – 用此方法建表，表不会自动搜集统计信息。

建索引时都会搜集一次，数据为空则统计信息为0，数据不为空则有具体的行数等信息。

问题3：FILTER、NESTED LOOPS ANTI/SEMI、NESTED LOOPS的区别

NESTED LOOPS：NL连接，驱动表的返回行数=被驱动表的访问次数

NESTED LOOPS SEMI：半连接，一般用于IN , EXISTS，这种操作join时候，通常查找到一条纪录就可以了，所以被驱动表关联字段去重行数=被驱动表的访问次数

NESTED LOOPS ANTI：反连接，一般用于NOT IN ,NOT EXISTS

FILTER：filter会维护一张hash table(有缓存提高效率),也是被驱动表关联字段去重行数=被驱动表的访问次数,更多参考：https://www.modb.pro/db/383452

NESTED LOOPS ANTI/ FILTER：差别不大，filter本身的算法和nest loop差别不太大,只是内部构造了hash table加快查找,所以走相类似的执行计划（NESTED LOOPS ANTI）的时候效率差别不会特别大

第三章循规蹈矩——如何读懂SQL执行计划

NESTED LOOPS/ FILTER：此处FILTER更优

第三章循规蹈矩——如何读懂SQL执行计划

问题4：如何将NESTED LOOPS改成 FILTER？

/+no_unnest/ 但性能不一定提高

问题5：请总结一下，如何从执行计划中判断出问题

解题思路：先了解执行计划有哪几个大类的信息，再按大类进行分析。

1、从predicate information（谓语信息）中看：关注filter部分，这部分的条件字段用不到索引，特别需要注意是否有类型转换函数。

2、从统计信息看：观察是否有排序（内存/磁盘），能否避免排序；是否有递归调用等。

3、从执行计划列表看：

观察A-Rows和E-Rows差别是否很大，如果很大，则执行计划的准确性很让人怀疑，是否统计信息有问题（当然也不排序执行计划没有问题，比如用到了分页，预计返回行数很多，实际分页结束就停止了，这种事正常的）；

观察A-Rows和Buffers（逻辑读）差别是否很大，如果实际返回是1，逻辑读很大，考虑是否需要建索引；

观察Starts是否很大，如果很大，看是否hash连接，两大表（返回结果均很多）关联一般hash连接比nl连接高效。

第三章循规蹈矩——如何读懂SQL执行计划

一、什么是SQL执行计划

二、SQL执行计划选定依据

1、如何判断哪个执行计划开销更低？主要是关注统计信息。

2、这些统计信息是如何进行收集的呢？

3、如何手动进行统计信息的收集？

三、获取执行计划的六种方法

方法一、EXPLAIN PLAN FOR

方法二、SET AUTOTRACE ON

方法三、statistics_level=all

方法四、dbms_xplan.display_cursor 获取

方法五、事件10046 TRACE 跟踪

方法六、awrsqrpt.sql

四、这六种获取执行计划的方法的差异

五、如何读懂执行计划

【单独型】

【联合型】

六、从执行计划中判断出问题

七、问题

继续阅读

报错：'mysql' 不是内部或外部命令，也不是可运行的程序或批处理文件。

Linxu常用命令技巧汇总

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艰难安装LDAP,SSL认证

《Linux命令行与Shell脚本编程大全第2版.布卢姆》pdf

MySQL的4种隔离级别？出现问题

XX系统实施过程问题总结

无组件上传图片到数据库中，最完整解决方案

【MySQL数据库】数据库索引事务1.索引2.事务

neo4j之cypher使用文档

NOSQL安全攻击

mybatis_入门程序Mybatis入门

登录plsql 报错 the account is locked --用户被锁

sqlServer根据经纬查距离

SequoiaDB巨杉数据库C++驱动概述

Oracle 批量查询传入List 返回List

第三章 循规蹈矩——如何读懂SQL执行计划

一、什么是SQL执行计划

二、SQL执行计划选定依据

1、如何判断哪个执行计划开销更低？主要是关注统计信息。

2、这些统计信息是如何进行收集的呢？

3、如何手动进行统计信息的收集？

三、获取执行计划的六种方法

方法一、EXPLAIN PLAN FOR

方法二、SET AUTOTRACE ON

方法三、statistics_level=all

方法四、dbms_xplan.display_cursor 获取

方法五、事件10046 TRACE 跟踪

方法六、awrsqrpt.sql

四、这六种获取执行计划的方法的差异

五、 如何读懂执行计划

【单独型】

【联合型】

六、 从执行计划中判断出问题

七、问题

继续阅读

第三章循规蹈矩——如何读懂SQL执行计划

五、如何读懂执行计划

六、从执行计划中判断出问题