使用 EXPLAIN PLAN 获取SQL语句执行计划

     SQL查询语句的性能从一定程度上影响整个数据库的性能。很多情况下,数据库性能的低下差不多都是不良SQL语句所引起。而SQL语句的执行 计划则决定了SQL语句将会采用何种方式从数据库提取数据并返回给客户端,本文描述的将是如何通过EXPLAIN PLAN 获取SQL语句执行计划来获 取SQL语句的执行计划。

一、获取SQL语句执行计划的方式      1. 使用explain plan 将执行计划加载到表plan_table,然后查询该表来获取预估的执行计划      2. 查询动态性能视图v$sql_plan,v$sql_plan_statistics,v$sql_workarea 等来获取已缓存到库缓存中的真实执行计划      3. 查询自动工作量资料库(Automatic Workload Repository)或查询Statspack,即从资料库中获取执行计划      4. 启用执行计划跟踪功能,即autotrace功能      5. 使用PL/SQL Developer提供的获取执行计划方法      6. 使用Toad工具来获取执行计划

 下面主要讨论使用explain plan获取执行计划的方法

二、explain plan工作实质、前提及操作方法      1. 工作实质       将SQL语句预估的执行计划加载到表plan_table,是对表plan_table 执行了DML操作,故不会执行隐式提交       可以对select,insert,update,merge,delete,create table, create index,alter index等加载执行计划到plan_table

     2. 前提条件       需要先创建plan_table,创建方法:@?/rdbms/admin/utlxplan       对当前的SQL语句有执行权限以及对依赖的对象有相应操作的权限

     3. 使用方法:        explain plan for select * from scott.emp where ename='SCOTT';    --未设置标记位        explain plan set statement_id='TEST' for select * from scott.emp where ename='SCOTT'  --设置标记位为TEST

三、实战演习  1.环境  

scott@ORCL> select * from v$version;                            
                                                                
BANNER                                                          
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

 2.创建测试表演示获取执行计划 

scott@ORCL> create table t as select * from all_objects where rownum<=1000;                                     
                                                                                                                
Table created.                                                                                                  
                                                                                                                
--加载创建表的执行计划(DDL 执行计划)                                                                            
scott@ORCL> explain plan set statement_id='T1' for create table t1 as select * from t;                          
                                                                                                                
Explained.                                                                                                      
                                                                                                                
--使用下面的语句从plan_table 获取执行计划                                                                       
col OPERATION format a25                                                                                        
col OPTIONS format a25                                                                                          
col OBJECT_NAME format a25                                                                                      
SELECT lpad(' ', 2 * (LEVEL - 1)) || operation operation                                                        
    ,options                                                                                                    
    ,object_name                                                                                                
    ,position pos                                                                                               
    ,bytes                                                                                                      
    ,cost                                                                                                       
FROM plan_table                                                                                                 
START WITH id = 0                                                                                               
AND statement_id =upper( '&input_statement_id')                                                                 
CONNECT BY PRIOR id = parent_id;                                                                                
                                                                                                                
Enter value for input_statement_id: T1                                                                          
old   9: AND statement_id =upper( '&input_statement_id')                                                        
new   9: AND statement_id =upper( 'T1')                                                                         
                                                                                                                
OPERATION                 OPTIONS                   OBJECT_NAME                      POS      BYTES       COST  
------------------------- ------------------------- ------------------------- ---------- ---------- ----------  
CREATE TABLE STATEMENT                                                                 8      79000          8  
  LOAD AS SELECT                                    T1                                 1                        
    TABLE ACCESS          FULL                      T                                  1      79000          5  
                                                                                                                
--创建测试表t1并收集统计信息                                                                                    
scott@ORCL> create table t1 nologging as select * from t;                                                       
                                                                                                                
scott@ORCL> exec dbms_stats.gather_table_stats('SCOTT','T1');                                                   
                                                                                                                
--使用explain plan加载创建索引的执行计划                                                                        
scott@ORCL> explain plan set statement_id='IDX' for create index i_t1 on t1(object_id);                         
                                                                                                                
Explained.                                                                                                      
                                                                                                                
scott@ORCL> @Get_Plan                                                                                           
Enter value for input_statement_id: IDX                                                                         
old   9: AND statement_id =upper( '&input_statement_id')                                                        
new   9: AND statement_id =upper( 'IDX')                                                                        
                                                                                                                
OPERATION                 OPTIONS                   OBJECT_NAME                      POS      BYTES       COST  
------------------------- ------------------------- ------------------------- ---------- ---------- ----------  
CREATE INDEX STATEMENT                                                                 6       4000          6  
  INDEX BUILD             NON UNIQUE                I_T1                               1                        
    SORT                  CREATE INDEX                                                 1       4000             
      TABLE ACCESS        FULL                      T1                                 1       4000          5  
                                                                                                                
scott@ORCL> CREATE INDEX i_t1 ON t1 (object_id);                                                                
                                                                                                                
scott@ORCL> delete from plan_table;          

 3.使用自顶向下的读取方法获取执行计划

--使用explain plan加载重建索引的执行计划                                                                          
scott@ORCL> explain plan set statement_id='A_IDX' for alter index i_t1 rebuild;                                   
                                                                                                                  
Explained.                                                                                                        
                                                                                                                  
--执行下面的语句来获的A_IDX的执行计划,其结果是从上至下来读,从最内侧往最外侧读。                                 
SELECT LPAD(' ', 2 * (LEVEL - 1)) || LEVEL || '.' || NVL(POSITION, 0) || ' ' ||                                   
    OPERATION || ' ' || OPTIONS || ' ' || OBJECT_NAME || ' ' ||                                                   
    OBJECT_TYPE || ' ' ||                                                                                         
    DECODE(ID, 0, STATEMENT_ID || ' Cost = ' || POSITION) || COST || ' ' ||                                       
    OBJECT_NODE "Query Plan"                                                                                      
FROM PLAN_TABLE                                                                                                   
START WITH ID = 0                                                                                                 
AND STATEMENT_ID = UPPER('&input_statement_id')                                                                   
CONNECT BY PRIOR ID = PARENT_ID                                                                                   
AND STATEMENT_ID = UPPER('&input_statement_id');                                                                  
                                                                                                                  
Enter value for input_statement_id: A_IDX                                                                         
old   8: AND STATEMENT_ID = UPPER('&input_statement_id')                                                          
new   8: AND STATEMENT_ID = UPPER('A_IDX')                                                                        
Enter value for input_statement_id: A_IDX                                                                         
old  10: AND STATEMENT_ID = UPPER('&input_statement_id')                                                          
new  10: AND STATEMENT_ID = UPPER('A_IDX')                                                                        
                                                                                                                  
Query Plan                                                                                                        
---------------------------------------------------------------------------------------------                     
1.2 ALTER INDEX STATEMENT    A_IDX Cost = 22                                                                      
  2.1 INDEX BUILD NON UNIQUE I_T1                                                                                 
    3.1 SORT CREATE INDEX                                                                                         
      4.1 INDEX FAST FULL SCAN I_T1 INDEX 2                                                                       
                                                                                                                  
--使用explain plan加载查询语句的执行计划                                                                          
scott@ORCL> explain plan set statement_id='QUERY' for                                                             
  2  select ename,dname                                                                                           
  3  from emp join dept                                                                                           
  4  on emp.deptno=dept.deptno                                                                                    
  5  where dept.deptno=30;                                                                                        
                                                                                                                  
Explained.                                                                                                        
                                                                                                                  
scott@ORCL> @Get_Plan2                                                                                            
                                                                                                                  
Query Plan                                                                                                        
--------------------------------------------------------------------------------------------                      
1.4 SELECT STATEMENT    QUERY Cost = 44                                                                           
  2.1 NESTED LOOPS    4                                                                                           
    3.1 TABLE ACCESS BY INDEX ROWID DEPT TABLE 1                                                                  
      4.1 INDEX UNIQUE SCAN PK_DEPT INDEX (UNIQUE) 0                                                              
    3.2 TABLE ACCESS FULL EMP TABLE 3                                                                             

  上面的例子的读取方法:        执行4.1的索引唯一扫描        将4.1的结果集返回给3.1        执行3.2的全表扫描        将3.1和3.2步骤的结果集返回给2.1        执行2.1的嵌套循环        返回最终结果集        注意嵌套循环的查询方法        Oracle 从第一个行源中读取第一行,然后和第二个行源中的所有记录行进行比对,所有匹配的记录放在结果集中,然后Oracle 将读第一        个行源中的下一行。依次类推,直到第一行源中的所有行处理完毕。  4.使用构建树方式查看执行计划

scott@ORCL> delete from plan_table;                                                                          
                                                                                                             
--使用explian plan加载SQL查询执行计划                                                                        
scott@ORCL> explain plan set statement_id='QUERY2' for                                                       
  2  select ename,dname                                                                                      
  3  from emp join dept                                                                                      
  4  on emp.deptno=dept.deptno                                                                               
  5  where emp.empno=7788;                                                                                   
                                                                                                             
Explained.                                                                                                   
                                                                                                             
--使用下面的SQl查询来生成构建树                                                                              
col operation format a30                                                                                     
col options format a20                                                                                       
col "OBJECT NAME" format a25                                                                                 
col order format a10                                                                                         
col opt format a15                                                                                           
SELECT LPAD(' ', 2 * (LEVEL - 1)) || operation "OPERATION",                                                  
    options "OPTIONS",                                                                                       
    DECODE(TO_CHAR(id),                                                                                      
        '0',                                                                                                 
        'COST = ' || NVL(TO_CHAR(position), 'n/a'),                                                          
        object_name) "OBJECT NAME",                                                                          
    id || '-' || NVL(parent_id, 0) || '-' || NVL(position, 0) "ORDER",                                       
    SUBSTR(optimizer, 1, 6) "OPT"                                                                            
FROM plan_table                                                                                              
START WITH id = 0                                                                                            
AND statement_id = UPPER('&input_statement_id')                                                              
CONNECT BY PRIOR id = parent_id                                                                              
AND statement_id = UPPER('&input_statement_id');                                                             
                                                                                                             
OPERATION                      OPTIONS              OBJECT NAME               ORDER      OPT                 
------------------------------ -------------------- ------------------------- ---------- ---------------     
SELECT STATEMENT                                    COST = 2                  0-0-2      ALL_RO              
  NESTED LOOPS                                                                1-0-1                          
    TABLE ACCESS               BY INDEX ROWID       EMP                       2-1-1      ANALYZ              
      INDEX                    UNIQUE SCAN          PK_EMP                    3-2-1      ANALYZ              
    TABLE ACCESS               BY INDEX ROWID       DEPT                      4-1-2      ANALYZ              
      INDEX                    UNIQUE SCAN          PK_DEPT                   5-4-1      ANALYZ              

      查询结果中的order列与opt列       order            order列的指名了ID,父ID,以及执行计划中这一步骤的位置。            ID列标识了这个步骤,但并没有说明执行的顺序            父ID表明了这个步骤中的父步骤            位置信息说明了父ID相同的子操作的执行顺序           opt            说明当前优化器使用的模式       分析            首先会从步骤3开始执行,步骤3通过索引唯一扫描PK_EMP将得到的结果集返回给父步骤2            步骤2根据上一子步骤3得到的rowid访问表EMP并将结果集返回给父步骤1            对于步骤2检索到的每一行数据,步骤1会将deptno传递给步骤5            步骤5根据得到的deptno执行索引唯一扫描并将结果集返回给步骤4            步骤4根据步骤5得到的rowid 访问表dept,并将结果集返回给父步骤1            对于步骤3中剩余的行依次按上述方式将所有结果集返回给步骤1            步骤1将获得的最终结果集返回给步骤0,SQL完成查询       根据查询返回的结果来构建执行计划树            从ID为1的列开始,作为根节点            寻找所有父ID为1的所有子ID,如本例为2和4,将其纳入树中            分别寻找以2和4为父ID的所有子ID,将其纳入树中            如此循环直到所有的ID没有父ID

              ---------------               NESTED LOOP (1)               ---------------                -        -              -           -               -              -        ---------        ----------        EMP (2)           DEPT(4)        ---------        ----------            -                -           -                  -    ---------              ----------    PK_EMP(3)              PK_DEPT(5)        ---------             ----------  5.通过Oracle 自带的SQL语句执行计划   可以通过Oracle提供的SQl语句来获得当前会话最后一条SQL语句的执行计划

   utlxpls.sql   -->用于查看串行执行计划    utlxplp.sql   -->用于查看并行执行计划 

scott@ORCL> @?/rdbms/admin/utlxpls.sql    --获得当前session plan_table 最后一条SQL语句的执行计划                     
                                                                                                                     
PLAN_TABLE_OUTPUT                                                                                                    
--------------------------------------------------------------------------------------------------                   
Plan hash value: 2385808155                                                                                          
                                                                                                                     
----------------------------------------------------------------------------------------                             
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |                             
----------------------------------------------------------------------------------------                             
|   0 | SELECT STATEMENT             |         |     1 |    28 |     2   (0)| 00:00:01 |                             
|   1 |  NESTED LOOPS                |         |     1 |    28 |     2   (0)| 00:00:01 |                             
|*  2 |   TABLE ACCESS BY INDEX ROWID| EMP     |     1 |    14 |     1   (0)| 00:00:01 |                             
|*  3 |    INDEX UNIQUE SCAN         | PK_EMP  |     1 |       |     0   (0)| 00:00:01 |                             
|   4 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    56 |     1   (0)| 00:00:01 |                             
|*  5 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |                             
----------------------------------------------------------------------------------------                             
                                                                                                                     
Predicate Information (identified by operation id):                                                                  
---------------------------------------------------                                                                  
                                                                                                                     
   2 - filter("EMP"."DEPTNO" IS NOT NULL)                                                                            
   3 - access("EMP"."EMPNO"=7788)                                                                                    
   5 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")                                                                        
                                                                                                                     
19 rows selected.	                                                                                                   
                                                                                                                     
--加载并行SQL的执行计划                                                                                              
scott@ORCL> explain plan for select /*+ parallel(t4,2) */ * from t4;                                                 
                                                                                                                     
Explained.                                                                                                           
                                                                                                                     
scott@ORCL> @?/rdbms/admin/utlxplp.sql                                                                               
                                                                                                                     
PLAN_TABLE_OUTPUT                                                                                                    
----------------------------------------------------------------------------------------------------------------     
Plan hash value: 128826497                                                                                           
                                                                                                                     
--------------------------------------------------------------------------------------------------------------       
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |       
--------------------------------------------------------------------------------------------------------------       
|   0 | SELECT STATEMENT     |          |   400K|  7817K|   183   (4)| 00:00:03 |        |      |            |       
|   1 |  PX COORDINATOR      |          |       |       |            |          |        |      |            |       
|   2 |   PX SEND QC (RANDOM)| :TQ10000 |   400K|  7817K|   183   (4)| 00:00:03 |  Q1,00 | P->S | QC (RAND)  |       
|   3 |    PX BLOCK ITERATOR |          |   400K|  7817K|   183   (4)| 00:00:03 |  Q1,00 | PCWC |            |       
|   4 |     TABLE ACCESS FULL| T4       |   400K|  7817K|   183   (4)| 00:00:03 |  Q1,00 | PCWP |            |       
--------------------------------------------------------------------------------------------------------------       

四、总结:      1. explain plan并不执行当前的SQL语句,而是根据数据字典中记录的统计信息获取最佳的执行计划并加载到表plan_table。      2. 由于统计信息,执行环境的变化,explain plan与实际的执行计划可能会有差异。      3. 对于运行时将较长的SQL语句,不需要等到结果输出即可提前获得该SQL的执行计划,对于生产环境调试情况会减轻数据库负荷。      4. 注意set statement_id标识符区分大小写。

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏乐沙弥的世界

Oracle ROWID 方式访问数据库

    和ROWNUM一样,ROWID是一个伪列,即是一个非用户定义的列,而又实际存储于数据库之中。每一个表都有一个ROWID列,一个ROWID值用于 唯一确定...

10720
来自专栏杨建荣的学习笔记

深度解析dba_segments和sys.seg$中的细节差异(上) (r5笔记第27天)

今天在查看系统空间使用情况的时候,发现一个细节的问题,自己死磕了一把,还是发现了不少有价值的东西。 事情的起因是我在使用脚本在某个环境中查看每个用户所占有的空间...

36280
来自专栏乐沙弥的世界

FORALL 之 SAVE EXCEPTIONS 子句应用一例

     对于大批量的DML操作中出现的错误,除了使用DML error logging特性来记录在DML期间出现的错误之外,使用批量SQL语句FORALL的S...

7610
来自专栏乐沙弥的世界

SQL*Plus copy 命令处理大批量数据复制

    对于数据库表级上的数据复制,我们最常用的是CREATE TABLE AS(CTAS)..方式。其实在SQL*Plus下面copy命令可以完成同样的工作,...

12510
来自专栏杨建荣的学习笔记

简单实用的sql小技巧(第一篇) (r3笔记第36天)

今天和大家简单分享几个实用的sql小技巧。还有一些还在整理中,会不断的分享出来。 有些其实也不算是sql的技巧,可能大家在写sql语句的时候没有意识到我们可以通...

31530
来自专栏恰童鞋骚年

《T-SQL查询》读书笔记Part 1.逻辑查询处理知多少

  T-SQL是ANSI和ISO SQL标准的MS SQL扩展,其正式名称为Transact-SQL,但一般程序员都称其为T-SQL。

9740
来自专栏菩提树下的杨过

mybatis 使用经验小结

一、多数据源问题 主要思路是把dataSource、sqlSesstionFactory、MapperScannerConfigurer在配置中区分开,各Map...

30060
来自专栏乐沙弥的世界

Oracle ROWID

  ROWID 是一个类似于rownum的伪列,用于定位数据库中一条记录的一个相对唯一地址值。通常情况下,该值在该行数据插入到数据库表时即 被确定且唯一。而...

19040
来自专栏deed博客

Oracle在Linux下安装

27420
来自专栏杨建荣的学习笔记

关于drop user的cascade选项解惑(52天)

在数据库中,有时候需要删除用户,大多数时候都需要使用cascade选项,有些时候却不需要,想知道在这个简单的命令之后数据库倒底在干什么, 这时候给一些指定的操作...

40180

扫码关注云+社区

领取腾讯云代金券