ORACLE的執行計劃分為預估執行計劃和實際執行計劃。其中，你用Toad、PL/SQL Developer、SQL Developer、EXPLAIN PLAN FOR或者SET ATUOTRACE TRACEONLY等獲取的執行計劃都是預估的執行計劃。有時候預估執行計劃和實際執行計劃有很大的差別，所以有時候，調優的時候需要對比實際執行計劃和預估的執行計劃，不能被預估的執行計劃給欺騙了。那麼我們怎麼查看實際的執行計劃呢？
方法1：查詢v$sql_plan視圖中的實際執行計劃
1:在窗口執行下面SQL語句
SELECT ENAME, SAL FROM SCOTT.EMP, SCOTT.DEPT
WHERE EMP.DEPTNO =DEPT.DEPTNO
AND DEPT.LOC='CHICAGO';
2：查看執行SQL的SQL_ID
SELECT SQL_ID, SQL_TEXT FROM V$SQL WHERE SQL_TEXT LIKE '%SELECT ENAME, SAL FROM SCOTT.EMP, SCOTT.DEPT%'
3: 查看v$sql_plan視圖
SELECT * FROM v$sql_plan WHERE SQL_ID='7m5qbhn98j1xw';
通過v$sql_plan視圖查看實際執行計劃，其實是不太現實的，因為閱讀困難，可讀性差，尤其是復雜SQL語句。
方法2：SET AUTOTRACE ON查看實際執行計劃
其實這種方式有很大的限制，例如SQL輸出大量的記錄，那麼在SQL Plus裡面就會刷屏，而且等待的時間較長；另外DML操作不能用這種方法。所以其實也並不實用，只是作為一種方法羅列於此。
方法3：DBMS_XPLAN.DISPLAY_CURSOR查看實際執行計劃
DBMS_XPLAN這個包最初是在ORACLE 9i R2中引入的，用來查看Explain Plan生成的執行計劃。DBMS_XPLAN 在ORACLE 10g 以及ORACLE 11g中，功能都有所增長。
在ORACLE 10g提供了下面4個函數的功能。
· DISPLAY - to format and display the contents of a plan table.
· DISPLAY_CURSOR - to format and display the contents of the execution plan of any loaded cursor.
· DISPLAY_AWR - to format and display the contents of the execution plan of a stored SQL statement in the AWR.
· DISPLAY_SQLSET - to format and display the contents of the execution plan of statements stored in a SQL tuning set.
在ORACLE 11g提供了下面5個函數的功能。
· DISPLAY - to format and display the contents of a plan table.
· DISPLAY_AWR - to format and display the contents of the execution plan of a stored SQL statement in the AWR.
· DISPLAY_CURSOR - to format and display the contents of the execution plan of any loaded cursor.
· DISPLAY_SQL_PLAN_BASELINE - to display one or more execution plans for the SQL statement identified by SQL handle
· DISPLAY_SQLSET - to format and display the contents of the execution plan of statements stored in a SQL tuning set.
這裡不打算詳細介紹DBMS_XPLAN各個函數的功能，具體可以參考官方文檔。這裡只介紹DISPLAY_CURSOR函數，它顯示存儲在庫緩存（library cache）中的實際執行計劃，當然你要查詢某個SQL語句的實際執行計劃，前提是這個SQL的執行計劃還在庫緩存中，如果它已經被刷出庫緩存，就無法獲取其實際執行計劃。
DISPLAY_CURSOR 的參數介紹如下：
FUNCTION DISPLAY_CURSOR RETURNS DBMS_XPLAN_TYPE_TABLE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SQL_ID VARCHAR2 IN DEFAULT
CURSOR_CHILD_NO NUMBER(38) IN DEFAULT
FORMAT VARCHAR2 IN DEFAULT
SQL_ID
指定位於庫緩存執行計劃中SQL語句的父游標。默認值為null。當使用默認值時當前會話的最後一條SQL語句的執行計劃將被返回
可以通過查詢V$SQL 或 V$SQLAREA的SQL_ID列來獲得SQL語句的SQL_ID。
CURSOR_CHILD_NO
指定父游標下子游標的序號。即指定被返回執行計劃的SQL語句的子游標。默認值為0。如果為null，則sql_id所指父游標下所有子游標的執行計劃都將被返回。
FORMAT
控制SQL語句執行計劃的輸出部分，即哪些可以顯示哪些不顯示。使用與display函數的format參數與修飾符在這裡同樣適用。
除此之外當在開啟statistics_level=all時或使用gather_plan_statistics提示可以獲得執行計劃中實時的統計信息
· BASIC: Displays the minimum information in the plan—the operation ID, the operation name and its option.
BASIC：僅顯示最少信息。基本上只包括操作ID、操作名稱和操作對象。
· TYPICAL: This is the default. Displays the most relevant information in the plan (operation id, name and option, #rows, #bytes and optimizer cost). Pruning, parallel and predicate information are only displayed when applicable. Excludes onlyPROJECTION, ALIAS and REMOTE SQL information (see below).
TYPICAL：這個是默認值，顯示執行計劃中大部分信息（operation id, name and option, #rows, #bytes and optimizer cost），並行、謂詞信息等， 除了別名，提綱和字段投影外。
· SERIAL: Like TYPICAL except that the parallel information is not displayed, even if the plan executes in parallel.
SERIAL：跟TYPICAL類似，除了並行操作信息沒有顯示，即使執行計劃有並行處理。
· ALL: Maximum user level. Includes information displayed with theTYPICAL level with additional information (PROJECTION, ALIASand information about REMOTE SQL if the operation is distributed).
ALL：顯示所有信息。
下面我們來看看，首先我們執行下面一個SQL語句
SQL> SELECT ENAME, SAL
2 FROM SCOTT.EMP E, SCOTT.DEPT D
3 WHERE E.DEPTNO =D.DEPTNO
4 AND D.LOC='CHICAGO';
ENAME SAL
---------- ----------
ALLEN 1600
WARD 1250
MARTIN 1250
BLAKE 2850
TURNER 1500
JAMES 950
6 rows selected.
然後查詢v$sql視圖，找到該語句的sql_id，有可能該SQL語句不在Share Pool裡面了，此時表明該SQL已經被踢出Share Pool
SQL> SELECT SQL_ID,CHILD_NUMBER
2 FROM V$SQL
3 WHERE SQL_TEXT LIKE '%SELECT ENAME, SAL FROM SCOTT.EMP E, SCOTT.DEPT D WHERE E.DEPTNO =D.DEPTNO AND D.LOC=''CHICAGO''%';
SQL_ID CHILD_NUMBER
------------- ------------
7wga0v6nhkjug 0
SQL>
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('7wga0v6nhkjug',0));PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 7wga0v6nhkjug, child number 0
-------------------------------------
SELECT ENAME, SAL FROM SCOTT.EMP E, SCOTT.DEPT D WHERE E.DEPTNO
=D.DEPTNO AND D.LOC='CHICAGO'
Plan hash value: 615168685
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 5 (100)| |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 1 | HASH JOIN | | 5 | 120 | 5 (20)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| DEPT | 1 | 11 | 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 14 | 182 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("E"."DEPTNO"="D"."DEPTNO") 2 - filter("D"."LOC"='CHICAGO')22 rows selected.
SQL>
不傳遞任何參數給display_cursor函數，表示顯示當前會話最後一條SQL語句的執行計劃.例如，我在SQL*Plus中執行下面SQL語句
SQL> SELECT * FROM SCOTT.DEPT;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(null,null));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 4db4txmchwqqh, child number 0
-------------------------------------
SELECT * FROM SCOTT.DEPT
Plan hash value: 3383998547
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS FULL| DEPT | 4 | 80 | 2 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------
13 rows selected.
參考資料：
http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_xplan.htm#CACFJGHG
https://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_xplan.htm
