使用dbms_xplan工具查看執行計畫

9i有一個新的包 dbms_xplan，對查詢plan_table表是一個很有用的工具，相對於以前寫一個複雜的SQL語句，然後從plan_table看執行計畫，不如調用 dbms_xplan 包，還可以顯示格式，這個工具的使用也非常方便。
調用的文法類似

select * from table(dbms_xplan.display(format=>'BASIC'))
使用 TABLE() 操作符，或者 CAST 操作。

DISPLAY 函數有三個參數
TABLE_NAME 指出最佳化計劃放在哪個表裡面，預設是 PLAN_TABLE.
STATEMENT_ID 指的是plan table中的statement_id欄位，預設是last ID 或者 NULL.
FORMAT 指的是顯示的格式

FORMAT參數有三個可選值，原文如下
BASIC It provides only the minimum amount of information, as in
case of the example above, similar to a query from
PLAN_TABLE directly.
TYPICAL This is the default value. It provides a variety of the
information useful for understanding how the optimizer
works for this statement. For instance, in case of partitioned
table operation, the columns PARTITION_START,
PARTITION_STOP, PARTITION_ID, and
FILTER_PREDICATES are displayed in addition to COST
for that step, the number of rows expected to be retrieved,
and number of bytes those rows may have. This provides
the information to understand statements involving
partitioned objects.
ALL This setting displays all the information displayed for the
BASIC and TYPICAL values, and also displays parallel
query operations and the related SQL statements, if those
are involved.
SERIAL This setting gets results similar to those retrieved by the
TYPICAL setting, but the queries are explained serially even
if a parallel query will be used.

一般推薦使用typical 參數，把SQLPLUS的linesize 參數調整到至少 120
[Q]如果設定自動跟蹤 zt
[A]用system登入
執行$ORACLE_HOME/rdbms/admin/utlplan.sql建立計劃表
執行$ORACLE_HOME/rdbms/admin/plustrce.sql建立plustrace角色
如果想計劃表讓每個使用者都能使用，則
SQL>create public synonym plan_table for plan_table;
SQL> grant all on plan_table to public;
如果想讓自動跟蹤的角色讓每個使用者都能使用，則
SQL> grant plustrace to public;
通過如下語句開啟/停止跟蹤
SET AUTOTRACE ON |OFF 
| ON EXPLAIN | ON STATISTICS | TRACEONLY | TRACEONLY EXPLAIN
下面是測試步驟
一
用sys使用者建立
PLUSTRACE 角色
$ORACLE_HOMEE:oracleora92sqlplusadminPlustrce.sql

二：把許可權授予某個人
grant plustrace to mjs;

三：建立表
建表SQL指令碼為在${ORACLE_HOME}/rdbms/admin/下的utlxplan.sql。

四：使用說明
1：我們用一個大表來舉例說明如何使用

dw_for_bo@MJS.SENSKY.COM> select count(*) from tbl_fact_sublog;

COUNT(*)
----------
1757960

2：一個很平常的SQL語句
用常規方法如下分析執行計畫
analyze table tbl_fact_sublog compute statistics;

set autotrace traceonly

select id,handsetname,count(*) from tbl_fact_sublog group by id,handsetname ;
結果如下：

1757960 rows selected.

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=30264 Card=1757960 B
ytes=24611440)

1 0 SORT (GROUP BY) (Cost=30264 Card=1757960 Bytes=24611440)
2 1 TABLE ACCESS (FULL) OF 'TBL_FACT_SUBLOG' (Cost=2284 Card
=1757960 Bytes=24611440)

Statistics
----------------------------------------------------------
0 recursive calls
88 db block gets
23749 consistent gets
35593 physical reads
0 redo size
47677309 bytes sent via SQL*Net to client
1289670 bytes received via SQL*Net from client
117199 SQL*Net roundtrips to/from client
0 sorts (memory)
1 sorts (disk)
1757960 rows processed

用 dbms_xplan 方法分析

delete from plan_table;
explain plan for select id,handsetname,count(*) from tbl_fact_sublog group by id,handsetname ;
dw_for_bo@MJS.SENSKY.COM> select * from table( dbms_xplan.display );

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------

---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1757K| 23M| | 30264 |
| 1 | SORT GROUP BY | | 1757K| 23M| 80M| 30264 |
| 2 | TABLE ACCESS FULL | TBL_FACT_SUBLOG | 1757K| 23M| | 2284 |
---------------------------------------------------------------------------------

Note: cpu costing is off

10 rows selected.

可見，用dbms_xplan這個包可以發現排序的時候需要大概 80M的臨時空間

dw_for_bo@MJS.SENSKY.COM> select * from table( dbms_xplan.display('PLAN_TABLE',null,'BASIC'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------

-------------------------------------------------
| Id | Operation | Name |
-------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT GROUP BY | |
| 2 | TABLE ACCESS FULL | TBL_FACT_SUBLOG |
-------------------------------------------------

8 rows selected.

用BASIC參數得到的資訊就少多了。

另：一個有趣的現象，如果我刪除統計資訊，結果是什麼樣的？
analyze table tbl_fact_sublog delete statistics;

dw_for_bo@MJS.SENSKY.COM> set autotrace traceonly
dw_for_bo@MJS.SENSKY.COM> select id,handsetname,count(*) from tbl_fact_sublog group by id,handsetname ;

1757960 rows selected.

Execution Plan
---------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (GROUP BY)
2 1 TABLE ACCESS (FULL) OF 'TBL_FACT_SUBLOG'

Statistics
---------------------------------------------------------
0 recursive calls
88 db block gets
23749 consistent gets
35641 physical reads
0 redo size
47677309 bytes sent via SQL*Net to client
1289670 bytes received via SQL*Net from client
117199 SQL*Net roundtrips to/from client
0 sorts (memory)
1 sorts (disk)
1757960 rows processed

用 dbms_xplan 方法分析

delete from plan_table;

explain plan for select id,handsetname,count(*) from tbl_fact_sublog group by id,handsetname ;

dw_for_bo@MJS.SENSKY.COM> select * from table( dbms_xplan.display );

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------

-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | SORT GROUP BY | | | | |
| 2 | TABLE ACCESS FULL | TBL_FACT_SUBLOG | | | |
-------------------------------------------------------------------------

Note: rule based optimization

10 rows selected.

從提示看出，用的是基於規則的最佳化器，而且沒有顯示排序大概需要多少空間，看來還是經過分析後用CBO比較好