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 --