ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 介绍几种获取SQL执行计划的方法(上)

介绍几种获取SQL执行计划的方法(上)

原创 Linux操作系统 作者:realkid4 时间:2011-08-10 20:08:08 0 删除 编辑

 

SQL调优是很多Oracle DBA和开发人员的重要工作。一个高效的SQL改写调优,可以大幅度优化执行计划,提高执行效率,进而增强关键用例模块的可用性和满意度。

 

进行SQL调优中不可缺少的操作就是获取指定SQL的执行计划。在目前的Oracle版本中,有很多可以使用的执行计划获取方法。本篇就加以总结,供需要的朋友不时之需。

 

1、方便易用的explain plan

 

Explain plan命令在Oracle中,可以对后面的SQL语句进行直接的解析,将执行计划保存在一个plan_table的中间表中。之后通过dbms_xplan包的方法进行获取。

 

ü        确定plan_table的安装

 

使用explain plan命令的一个前提就是系统中存在plan_table数据表。如果没有的话,需要进行脚本调用安装。

 

 

--如果不存在,就生成

SQL> @?/rdbms/admin/catplan.sql

 

程序包体已创建。

 

没有错误。

 

 

这里注意两个细节:

 

首先,调用脚本中的?表示ORACLE_HOME目录。如果是使用sqlplus工具,可以直接使用?代指该目录。其他如PL/SQL Developer第三方工具不支持;

 

其次,如果是Oracle 10g以上的版本,使用脚本名称为catplan.sql。如果是如9i的版本,使用脚本名称为utlxplan.sql。如果在高版本Oracle上使用低版本的plan_table结构,可能在生成执行计划中报错“Plan Table version too old”错误。

 

 

ü        使用explain plan for命令生成执行计划并显示

 

 

SQL> set linesize 10000;

SQL> set wrap off;

SQL> set pagesize 10000;

SQL> explain plan for select * from scott.emp where empno=7839;

 

已解释。

 

 

之后使用dbms_xplan工具包将生成的执行计划展示出。

 

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

-------------------------------------------------------------------------------------------

Plan hash value: 2949544139

--------------------------------------------------------------------------------------

| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |        |     1 |    35 |     1   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    35 |     1   (0)| 00:00:01 |

|*  2 |   INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     0   (0)| 00:00:01 |

--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("EMPNO"=7839)

已选择14行。

 

 

该语句显示出索引执行计划。

 

ü        显示详细执行计划信息

 

上面直接调用,是显示出分析的SQL最简单的执行计划。可以通过设置format参数,显示出关于计划的更详细信息。

 

 

SQL> select * from table(dbms_xplan.display(null,null,'advanced'));

 

PLAN_TABLE_OUTPUT

-------------------------------------------------------------------------------------------

Plan hash value: 2949544139

--------------------------------------------------------------------------------------

| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |        |     1 |    35 |     1   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    35 |     1   (0)| 00:00:01 |

|*  2 |   INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     0   (0)| 00:00:01 |

--------------------------------------------------------------------------------------

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

-------------------------------------------------------------

   1 - SEL$1 / EMP@SEL$1

   2 - SEL$1 / EMP@SEL$1

Outline Data

-------------

  /*+

      BEGIN_OUTLINE_DATA

      INDEX(@"SEL$1" "EMP"@"SEL$1" ("EMP"."EMPNO"))

      OUTLINE_LEAF(@"SEL$1")

      ALL_ROWS

      OPTIMIZER_FEATURES_ENABLE('10.2.0.1')

      IGNORE_OPTIM_EMBEDDED_HINTS

      END_OUTLINE_DATA

  */

 

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("EMPNO"=7839)

Column Projection Information (identified by operation id):

-----------------------------------------------------------

 

   1 - "EMPNO"[NUMBER,22], "EMP"."ENAME"[VARCHAR2,10],

       "EMP"."JOB"[VARCHAR2,9], "EMP"."MGR"[NUMBER,22], "EMP"."HIREDATE"[DATE,7],

       "EMP"."SAL"[NUMBER,22], "EMP"."COMM"[NUMBER,22], "EMP"."DEPTNO"[NUMBER,22]

   2 - "EMP".ROWID[ROWID,10], "EMPNO"[NUMBER,22]

 

已选择41行。

 

 

添加了format参数,Oracle将更加详细的执行计划信息返回,包括Outline信息、结果集合映射等内容。

 

ü        Explain plan for细节

 

Explain plan for使用比较方便,特别是可以支持在pl/sql developer等第三方开发工具中使用的特性,比较吸引人。不过,explain plan在使用的时候,要注意一些潜在问题:

 

首先,explain plan for是单纯对SQL语句进行优化器分析,获取产生到的执行计划。这个过程中,并没有真正执行。所以,生成的执行计划有时候会有bug,而且进行统计的信息情况没有autotrace高;

 

其次,explain plan for由于只是对执行计划进行估计。所以在有绑定变量的SQL时,生成的执行计划并不准确;

 

 

2、  获取“刚刚”的执行计划display_cursor

 

使用dbms_xplan包,还可以获取刚刚执行过的SQL执行计划信息。

 

 

SQL>  select * from scott.emp where empno=7900;

 

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO

---------- ---------- --------- ---------- -------------- ---------- ---------- ----------

      7900 JAMES      CLERK           7698 03-12-81            950                    30

 

SQL> select * from table(dbms_xplan.display_cursor); //获取刚刚的执行计划;

 

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------------------

SQL_ID  66nkfdw21rc9j, child number 0

-------------------------------------

 select * from scott.emp where empno=7900

 

Plan hash value: 2949544139

--------------------------------------------------------------------------------------

| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |        |       |       |     1 (100)|          |

|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    35 |     1   (0)| 00:00:01 |

|*  2 |   INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     0   (0)|          |

--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("EMPNO"=7900)

 

已选择19行。

 

 

直接调用display_cursor,不指定sql_id,就可以将刚刚当前会话执行的SQL命令执行计划从library cache中抽取出来。

 

注意:display_cursor也支持format参数,可以进行详细执行计划信息的抽取。

 

此外还有一点,就是这种方法获取刚刚执行过的SQL执行计划,只能在sqlplus或者sqlplusw上使用。如果是pl/sql developer等第三方工具,可能不适用。

 

 

(注意:在pl/sql developer下使用存在问题)

SQL> select * from table(dbms_xplan.display_cursor);

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

SQL_ID  9m7787camwh4m, child number 0

begin :id := sys.dbms_transaction.local_transaction_id; end;

NOTE: cannot fetch plan for SQL_ID: 9m7787camwh4m, CHILD_NUMBER: 0

      Please verify value of SQL_ID and CHILD_NUMBER;

      It could also be that the plan is no longer in cursor cache (check v$sql_p

 

8 rows selected

 

 

3autotrace工具使用

 

本人以为autotrace工具是获取执行计划信息较为完整的工具。优势在于使用该工具可以获取到执行SQL过程中的读写、调用递归和排序分组消耗。

 

在之前的Blog中,笔者已经撰写过一篇关于autotrace较为详细的文章,有兴趣的读者可以参考:《Autotrace工具使用——小工具,大用场》(http://space.itpub.net/17203031/viewspace-686535)。

 

 

在下篇中,我们会介绍直接从shared_pool中抽取执行计划,和从AWR报告库中抽取。最后介绍使用10046事件跟踪执行计划。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/17203031/viewspace-704626/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
求道~

注册时间:2010-11-30

  • 博文量
    545
  • 访问量
    7753541