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

转自：http://blog.csdn.net/leshami/article/details/6837771

SQL语句的性能从一定程度上影响整个数据库的性能。很多情况下，数据库性能的低下差不多都是不良SQL语句所引起。
而SQL语句的执行计划则决定了SQL语句将会采用何种方式从数据库提取数据并返回给客户端，
本文描述的将是如何通过EXPLAIN PLAN来获取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 * 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标识符区分大小写。

ITPub博客

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

最新文章