[Oracle 10g] SQL Plan ，Explain Plan/ DBMS_XPLAN & Autotrace Enhancement in 10g
我们知道查询一条SQL的执行计划，一般可以通过如下两种方式(当然还有其他方式)
(1) Explain plan for / DBMS_XPLAN.DISPLAY
(2) Set autotrace on
Explain plan for 和 Set autotrace 都是SQL*PLUS命令，DBMS_XPLAN是Oracle提供的查询执行计划相关的包。
这两种方法在Oracle10g都得到了加强。DBMS_XPLAN最先是在Oracle9.2中引入的，但是只能用来查看SQL的“理论”上的执行计划(Explain plan for的结果)， 在10g中，可以通过新增加的DISPLAY_CURSOR来得到SQL（CURSOR)的“实际”执行计划信息。
(Tom 在<Effective Oracle by Design> 中提到如何在10g之前如何用DBMS_XPLAN.DISPLAY和V$SQL来得到实际执行的信息，下面会提到）
Autotrace提供了很多种关于SQL执行的信息，其中之一是SQL的执行计划(同样也是“理论”上的，不是实际的），但是Autotrace返回的其他统计信息(physical/logical i/o)却是实际的。 在10.2之前， Autotrace用的是自己的format方法，因此得到的SQL执行计划表同用DBMS_XPLAN不是很一致，但是从10.2开始，autotrace 采用了DBMS_XPLAN的方法来格式化最后输出的执行计划表。
需要注意的是，DBMS_XPLAN(确切说是EXPLAIN PLAN for) 和 autotrace 都依赖于表 PLAN_TABLE, 可能需要手动创建一下。 用autotrace还需要当前用户被被授予PLUSTRACE role. 关于如何进行设置EXPLAIN PLAN 和 Autotrace，参考另外一篇blog。
1. About DBMS_XPLAN
包DBMS_XPLAN在Oracle 9i R2中引入的，用来查看Explain Plan生成的执行计划。
在10g中, DBMS_XPLAN增加了用来查看SQL的实际执行的计划相关信息（DBMS_XPLAN.DISPLAY_CURSOR）。
（可以用DESCRIBE查看包DBMS_XPLAN中的信息）
SQL> desc DBMS_XPLAN
FUNCTION DISPLAY RETURNS DBMS_XPLAN_TYPE_TABLE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
TABLE_NAME VARCHAR2 IN DEFAULT
STATEMENT_ID VARCHAR2 IN DEFAULT
FORMAT VARCHAR2 IN DEFAULT
FILTER_PREDS VARCHAR2 IN DEFAULT
FUNCTION DISPLAY_AWR RETURNS DBMS_XPLAN_TYPE_TABLE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SQL_ID VARCHAR2 IN
PLAN_HASH_VALUE NUMBER(38) IN DEFAULT
DB_ID NUMBER(38) IN DEFAULT
FORMAT VARCHAR2 IN DEFAULT
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
FUNCTION DISPLAY_SQLSET RETURNS DBMS_XPLAN_TYPE_TABLE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SQLSET_NAME VARCHAR2 IN
SQL_ID VARCHAR2 IN
PLAN_HASH_VALUE NUMBER(38) IN DEFAULT
FORMAT VARCHAR2 IN DEFAULT
SQLSET_OWNER VARCHAR2 IN DEFAULT
FUNCTION FORMAT_NUMBER RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NUM NUMBER IN
FUNCTION FORMAT_NUMBER2 RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NUM NUMBER IN
FUNCTION FORMAT_SIZE RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NUM NUMBER IN
FUNCTION FORMAT_SIZE2 RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NUM NUMBER IN
FUNCTION PREPARE_RECORDS RETURNS DBMS_XPLAN_TYPE_TABLE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
PLAN_CUR REF CURSOR IN
I_FORMAT_FLAGS BINARY_INTEGER IN
FUNCTION VALIDATE_FORMAT RETURNS BOOLEAN
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
HASPLANSTATS BOOLEAN IN
FORMAT VARCHAR2 IN
FORMAT_FLAGS BINARY_INTEGER OUT
SQL>
