【Explain Plan】查看SQL的执行计划

1.【使用方法】
Explain Plan在sqlplus中使用起来非常的便捷，使用方法如下
sec@ora10g> explain plan for select count(*) from t;

Explained.

sec@ora10g> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------
Plan hash value: 2966233522

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |  8407   (1)| 00:01:41 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T    |  3017K|  8407   (1)| 00:01:41 |
-------------------------------------------------------------------

9 rows selected.

2.【扩展补充】
在10g以前的版本中，需要单独创建PLAN_TABLE并授予,10g中自动创建PLAN_TABLE$不再需要这一步骤
下面是10g之前初始化PLAN_TABLE需要创建的步骤：
SQL> @?/rdbms/admin/utlxplan.sql
SQL> create public synonym plan_table for plan_table;
SQL> grant all on plan_table to public;

进一步谈一下有关怎么样确定Oracle 10g中用到的字典表是PLAN_TABLE$而不再是PLAN_TABLE
1）确定数据库版本
sec@ora10g> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

2）开启autotrace功能
sec@ora10g> set autotrace on explain;

3）跟踪后在执行计划中得到PLAN_TABLE$的信息
sec@ora10g> select * from plan_table;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 103984305

---------------------------------------------------------------------------------
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             |     1 | 11081 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| PLAN_TABLE$ |     1 | 11081 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------

3.小结 and “意犹未尽”
获得SQL的执行计划是对SQL进行优化的第一步，然而使用“explain plan for”方法又是一个最基本的获得执行计划的手段，当然，您也可以选择使用autotrace和sql_trace等工具获得SQL的执行计划。
在使用explain plan for方法得到SQL执行计划的过程中，要频繁的使用“select * from table(dbms_xplan.display());”语句查看执行计划，有些人感觉这样操作非常的不便捷，而且如果不是经常使用就会忘记具体的语法格式，这里最后给出一个迅速查看的另外一种方法——使用Oracle的非常贴心的utlxplp.sql脚本进行查看。
在“$ORACLE_HOME/rdbms/admin”目录下存有一个名叫utlxplp.sql的SQL脚本，其中非常人性化的记录了这个SQL的语法格式，您也可以使用这个脚本直接查看执行计划。
使用方法如下：
sec@ora10g> explain plan for select count(*) from t;

Explained.

sec@ora10g> @?/rdbms/admin/utlxplp.sql

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------
Plan hash value: 2966233522

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    37   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T    | 12779 |    37   (0)| 00:00:01 |
-------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement

13 rows selected.


最后，摘录一下这个脚本的原貌，供参考
$ cat $ORACLE_HOME/rdbms/admin/utlxplp.sql
Rem
Rem $Header: utlxplp.sql 23-jan-2002.08:55:23 bdagevil Exp $
Rem
Rem utlxplp.sql
Rem
Rem Copyright (c) 1998, 2002, Oracle Corporation.  All rights reserved.
Rem
Rem    NAME
Rem      utlxplp.sql - UTiLity eXPLain Parallel plans
Rem
Rem    DESCRIPTION
Rem      script. utility to display the explain plan of the last explain plan
Rem      command. Display also Parallel Query information if the plan happens to
Rem      run parallel
Rem
Rem    NOTES
Rem      Assume that the table PLAN_TABLE has been created. The script
Rem      utlxplan.sql should be used to create that table
Rem
Rem      With SQL*plus, it is recomended to set linesize and pagesize before
Rem      running this script. For example:
Rem         set linesize 130
Rem         set pagesize 0
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    bdagevil    01/23/02 - rewrite with new dbms_xplan package
Rem    bdagevil    04/05/01 - include CPU cost
Rem    bdagevil    02/27/01 - increase Name column
Rem    jihuang     06/14/00 - change order by to order siblings by.
Rem    jihuang     05/10/00 - include plan info for recursive SQL in LE row source
Rem    bdagevil    01/05/00 - make deterministic with order-by
Rem    bdagevil    05/07/98 - Explain plan script. for parallel plans
Rem    bdagevil    05/07/98 - Created
Rem

set markup html preformat on

Rem
Rem Use the display table function from the dbms_xplan package to display the last
Rem explain plan. Use default mode which will display only relevant information
Rem
select * from table(dbms_xplan.display());


-- The End --