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

【Explain Plan】檢視SQL的執行計劃

相關文章