PLSQL_性能优化系列15_Oracle Explain Plan解析计划解读

PLSQL_性能优化系列15_Oracle Explain Plan解析计划解读

2014-12-19 Created By BaoXinjian

一、摘要


在SQL语句的执行计划中,包含很多字段项和很多模块,其不同字段代表了不同的含义且在不同的情形下某些字段、模块显示或不显示,下

面的描述给出了执行计划中各字段的含义以及各模块的描述。

 

二、执行计划分析过程


1. 分析解析计划

Step1. 打开熟悉的查看工具:PL/SQL Developer - Toad。

在PL/SQL Developer中写好一段SQL代码后,按F5,PL/SQL Developer会自动打开执行计划窗口,显示该SQL的执行计划。

Step2. 查看总COST,获得资源耗费的总体印象

 一般而言,执行计划第一行所对应的COST(即成本耗费)值,反应了运行这段SQL的总体估计成本,单看这个总成本没有实际意义,

但可以拿它与相同逻辑不 同执行计划的SQL的总体COST进行比较,通常COST低的执行计划要好一些。  

Step3. 按照从左至右,从上至下的方法,了解执行计划的执行步骤

执行计划按照层次逐步缩进,从左至右看,缩进最多的那一步,最先执行,如果缩进量相同,则按照从上而下的方法判断执行顺序,可粗略认为上面的步骤优先执行。

每一个执行步骤都有对应的COST,可从单步COST的高低,以及单步的估计结果集(对应ROWS/基数),来分析表的访问方式,连接顺序以及连接方式是 否合理。

Step4. 分析表的访问方式

表的访问方式主要是两种:

全表扫描(TABLE ACCESS FULL)和索引扫描(INDEX SCAN),如果表上存在选择性很好的索引,却走了全表扫描,而且是大表的全表扫描,就说明表的访问方式可能存在问题;

若大表上没有合适的索引而走了全表 扫描,就需要分析能否建立索引,或者是否能选择更合适的表连接方式和连接顺序以提高效率。

Step5. 分析表的连接方式和连接顺序

表的连接顺序:就是以哪张表作为驱动表来连接其他表的先后访问顺序。

表的连接方式:简单来讲,就是两个表获得满足条件的数据时的连接过程。

主要有三种表连接方式,嵌套循环(NESTED LOOPS)、哈希连接(HASH JOIN)和排序-合并连接(SORT MERGE JOIN)。我们常见得是嵌套循环和哈希连接。

嵌套循环:

最适用也是最简单的连接方式。类似于用两层循环处理两个游标,外层游标称作驱动表,Oracle检索驱动表的数据,一条一条的代入内层游标,查找满足WHERE条件的所有数据,因此内层游标表中可用索引的选择性越好,嵌套循环连接的性能就越高。

哈希连接:

先将驱动表的数据按照条件字段以散列的方式放入内存,然后在内存中匹配满足条件的行。

哈希连接需要有合适的内存,而且必须在CBO优化模式下,连接两表的WHERE条件有等号的情况下才可以使用。哈希连接在表的数据量较大,表中没有合适的索引可用时比嵌套循环的效率要高。

 

2. 总结两点:

2.1 这里看到的执行计划,只是SQL运行前可能的执行方式,实际运行时可能因为软硬件环境的不同,而有所改变,而且cost高的执行计划,不一定在实际运行起来,速度就一定差,我们平时需要结合执行计划,和实际测试的运行时间,来确定一个执行计划的好坏。

2.2 对于表的连接顺序,多数情况下使用的是嵌套循环,尤其是在索引可用性好的情况下,使用嵌套循环式最好的,但当ORACLE发现需要访问的数据表较大,索引 的成本较高或者没有合适的索引可用时,会考虑使用哈希连接,以提高效率。排序合并连接的性能最差,但在存在排序需求,或者存在非等值连接无法使用哈希连接 的情况下,排序合并的效率,也可能比哈希连接或嵌套循环要好。

 

三、执行计划中各字段的描述


1. 基本字段(总是可用的)

  • Id            执行计划中每一个操作(行)的标识符。如果数字前面带有星号,意味着将在随后提供这行包含的谓词信息
  • Operation  对应执行的操作。也叫行源操作
  • Name        操作的对象名称

2. 查询优化器评估信息

  • Rows(E-Rows)     预估操作返回的记录条数
  • Bytes(E-Bytes)    预估操作返回的记录字节数
  • TempSpc            预估操作使用临时表空间的大小
  • Cost(%CPU)        预估操作所需的开销。在括号中列出了CPU开销的百分比。注意这些值是通过执行计划计算出来的。换句话说,父操作的开销包含子操作的开销
  • Time                  预估执行操作所需要的时间(HH:MM:SS)

3. 分区(仅当访问分区表时下列字段可见)

  • Pstart   访问的第一个分区。如果解析时不知道是哪个分区就设为KEY,KEY(I),KEY(MC),KEY(OR),KEY(SQ)
  • Pstop    访问的最后一个分区。如果解析时不知道是哪个分区就设为KEY,KEY(I),KEY(MC),KEY(OR),KEY(SQ)

4. 并行和分布式处理(仅当使用并行或分布式操作时下列字段可见)

  • Inst         在分布式操作中,指操作使用的数据库链接的名字
  • TQ          在并行操作中,用于从属线程间通信的表队列
  • IN-OUT    并行或分布式操作间的关系
  • PQ Distrib 在并行操作中,生产者为发送数据给消费者进行的分配

5. 运行时统计(当设定参数statistics_level为all或使用gather_plan_statistics提示时,下列字段可见)

  • Starts       指定操作执行的次数
  • A-Rows     操作返回的真实记录数
  • A-Time     操作执行的真实时间(HH:MM:SS.FF)

6. I/O 统计(当设定参数statistics_level为all或使用gather_plan_statistics提示时,下列字段可见)

  • Buffers     执行期间进行的逻辑读操作数量
  • Reads      执行期间进行的物理读操作数量
  • Writes      执行期间进行的物理写操作数量         

7. 内存使用统计

  • OMem        最优执行所需内存的预估值
  • 1Mem        一次通过(one-pass)执行所需内存的预估值
  • 0/1/M        最优/一次通过/多次通过(multipass)模式操作执行的次数
  • Used-Mem  最后一次执行时操作使用的内存量
  • Used-Tmp  最后一次执行时操作使用的临时空间大小。这个字段必须扩大1024倍才能和其他衡量内存的字段一致(比如,32k意味着32MB)
  • Max-Tmp   操作使用的最大临时空间大小。这个字段必须扩大1024倍才能和其他衡量内存的字段一致(比如,32k意味着32MB)

 

四、执行计划中各模块的描述与举例


1. 执行前,系统预估解析计划,Explain Plan

复制代码
SQL> explain plan for                                                                                                    
02.  2  select * from emp e,dept d                                                                                          
03.  3  where e.deptno=d.deptno                                                                                             
04.  4  and e.ename='SMITH';                                                                                                
05.                                                                                                                         
06.Explained.                                                                                                               
15.                                                                                                                         
16.SQL> set linesize 180                                                                                                    
17.SQL> set pagesize 0                                                                                                      
18.SQL> select * from table(dbms_xplan.display(null,null,'advanced'));   --使用dbms_xplan.display函数获得语句的执行计划     
19.Plan hash value: 351108634                                            --SQL语句的哈希植                                  
20.                                                                                                                         
21.----------------------------------------------------------------------------------------   /*执行计划部分*/              
22.| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |                                 
23.----------------------------------------------------------------------------------------                                 
24.|   0 | SELECT STATEMENT             |         |     1 |   117 |     4   (0)| 00:00:01 |                                 
25.|   1 |  NESTED LOOPS                |         |     1 |   117 |     4   (0)| 00:00:01 |                                 
26.|*  2 |   TABLE ACCESS FULL          | EMP     |     1 |    87 |     3   (0)| 00:00:01 |                                 
27.|   3 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    30 |     1   (0)| 00:00:01 |                                 
28.|*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |                                 
29.----------------------------------------------------------------------------------------                                 
30.                                                                                                                         
31.Query Block Name / Object Alias (identified by operation id):  --这部分显示的为查询块名和对象别名                        
32.-------------------------------------------------------------                                                            
33.                                                                                                                         
34.   1 - SEL$1                    --SEL$为select 的缩写,位于块1,相应的还有DEL$,INS$,UPD$等                                
35.   2 - SEL$1 / E@SEL$1          --E@SEL$1,对应到执行计划中的操作ID为2上,即在表E上的查询,E为别名,下面类同             
36.   3 - SEL$1 / D@SEL$1                                                                                                   
37.   4 - SEL$1 / D@SEL$1                                                                                                   
38.                                                                                                                         
39.Outline Data                    --提纲部分,这部分将执行计划中的图形化方式以文本形式来呈现,即转换为提示符方式           
40.-------------                                                                                                            
41.                                                                                                                         
42.  /*+                                                                                                                    
43.      BEGIN_OUTLINE_DATA                                                                                                 
44.      USE_NL(@"SEL$1" "D"@"SEL$1")                           --使用USE_NL提示,即嵌套循环                                
45.      LEADING(@"SEL$1" "E"@"SEL$1" "D"@"SEL$1")              --指明前导表                                                
46.      INDEX_RS_ASC(@"SEL$1" "D"@"SEL$1" ("DEPT"."DEPTNO"))   --指明对于D上的访问方式为使用索引                           
47.      FULL(@"SEL$1" "E"@"SEL$1")                             --指明对于E上的访问方式为全表扫描                           
48.      OUTLINE_LEAF(@"SEL$1")                                                                                             
49.      ALL_ROWS                                                                                                           
50.      OPTIMIZER_FEATURES_ENABLE('10.2.0.3')                                                                              
51.      IGNORE_OPTIM_EMBEDDED_HINTS                                                                                        
52.      END_OUTLINE_DATA                                                                                                   
53.  */                                                                                                                     
54.                                                                                                                         
55.Predicate Information (identified by operation id): --谓词信息部分,在执行计划中ID带有星号的每一行均对应到下面中的一行   
56.---------------------------------------------------                                                                      
57.                                                                                                                         
58.   2 - filter("E"."ENAME"='SMITH')                                                                                       
59.   4 - access("E"."DEPTNO"="D"."DEPTNO")                                                                                 
60.                                                                                                                         
61.Column Projection Information (identified by operation id):  --执行时每一步骤所返回的列,下面的不同步骤返回了不同的列    
62.-----------------------------------------------------------                                                              
63.                                                                                                                         
64.   1 - (#keys=0) "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10],                                                       
65.       "E"."JOB"[VARCHAR2,9], "E"."MGR"[NUMBER,22], "E"."HIREDATE"[DATE,7],                                              
66.       "E"."SAL"[NUMBER,22], "E"."COMM"[NUMBER,22], "E"."DEPTNO"[NUMBER,22],                                             
67.       "D"."DEPTNO"[NUMBER,22], "D"."DNAME"[VARCHAR2,14], "D"."LOC"[VARCHAR2,13]                                         
68.   2 - "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10], "E"."JOB"[VARCHAR2,9],                                          
69.       "E"."MGR"[NUMBER,22], "E"."HIREDATE"[DATE,7], "E"."SAL"[NUMBER,22],                                               
70.       "E"."COMM"[NUMBER,22], "E"."DEPTNO"[NUMBER,22]                                                                    
71.   3 - "D"."DEPTNO"[NUMBER,22], "D"."DNAME"[VARCHAR2,14], "D"."LOC"[VARCHAR2,13]                                         
72.   4 - "D".ROWID[ROWID,10], "D"."DEPTNO"[NUMBER,22]                                                                      
73.                                                                                                                         
74.Note    --注释与描述部分,下面的描述中给出了本次SQL语句使用了动态采样功能                                                
75.-----                                                                                                                    
76.   - dynamic sampling used for this statement                                                                            
77.                                                                                                                         
78.58 rows selected. 

复制代码

 

2. 执行后,系统实际解析计划,Explain Plan

复制代码
SQL> select /*+ gather_plan_statistics */ *          --注意此处增加了提示gather_plan_statistics并且该语句被执行          
02.  2  from emp e,dept d                                                                                                   
03.  3  where e.deptno=d.deptno                                                                                             
04.  4  and e.ename='SMITH';                                                                                                
05.                                                                                                                         
06.      7369 SMITH      CLERK           7902 17-DEC-80        800                    20         20 RESEARCH       DALLAS   
07.                                                                                                                         
08.SQL> select * from table(dbms_xplan.display_cursor(null,null,'iostats last')); --使用display_cursor获取实际的执行计划    
09.                                                                                                                         
10.SQL_ID  fpx7zw59f405d, child number 0              --这部分给出了SQL语句的SQL_ID,子游标号以及原始的SQL语句               
11.-------------------------------------                                                                                    
12.select /*+ gather_plan_statistics */ * from emp e,dept d where e.deptno=d.deptno and                                     
13.e.ename='SMITH'                                                                                                          
14.                                                                                                                         
15.Plan hash value: 351108634              --SQL 语句的哈希值                                                               
16.                                        --SQL语句的执行计划,可以看到下面显示的字段一部分不同于预估执行计划中的字段      
17.-----------------------------------------------------------------------------------------------------------              
18.| Id  | Operation                    | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |              
19.-----------------------------------------------------------------------------------------------------------              
20.|   1 |  NESTED LOOPS                |         |      1 |      1 |      1 |00:00:00.01 |      10 |      1 |              
21.|*  2 |   TABLE ACCESS FULL          | EMP     |      1 |      1 |      1 |00:00:00.01 |       8 |      0 |              
22.|   3 |   TABLE ACCESS BY INDEX ROWID| DEPT    |      1 |      1 |      1 |00:00:00.01 |       2 |      1 |              
23.|*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |      1 |      1 |      1 |00:00:00.01 |       1 |      1 |              
24.-----------------------------------------------------------------------------------------------------------              
25.                                                                                                                         
26.Predicate Information (identified by operation id):                                                                      
27.---------------------------------------------------                                                                      
28.                                                                                                                         
29.   2 - filter("E"."ENAME"='SMITH')                                                                                       
30.   4 - access("E"."DEPTNO"="D"."DEPTNO")                                                                                 
31.                                                                                                                         
32.Note                                                                                                                     
33.-----                                                                                                                    
34.   - dynamic sampling used for this statement                                                                            
35.                                                                                                                         
36.                                                                                                                         
37.26 rows selected.
复制代码

 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值