檢視Oracle SQL執行計劃方法比較、分析

1.SQL*PLUS AUTOTRACE：

為使所有的使用者都能用到SQLPLUS AUTOTRACE，需要做以下操作。

SQL> @?/rdbms/admin/utlxplan.sql

Table created.

SQL> create or replace public synonym plan_table for plan_table;

Synonym created.

SQL> grant all on plan_table to public;

Grant succeeded.

SQL> @?/sqlplus/admin/plustrce.sql
SQL>
SQL> drop role plustrace;
drop role plustrace
          *
ERROR at line 1:
ORA-01919: role 'PLUSTRACE' does not exist

SQL> create role plustrace;

Role created.

SQL>
SQL> grant select on v_$sesstat to plustrace;

Grant succeeded.

SQL> grant select on v_$statname to plustrace;

Grant succeeded.

SQL> grant select on v_$mystat to plustrace;

Grant succeeded.

SQL> grant plustrace to dba with admin option;

Grant succeeded.

SQL>
SQL> set echo off
SQL> grant plustrace to public;

Grant succeeded.

SQL> connect test/test
Connected.
SQL> set autotrace on
SQL>

AUTOTRACE選項：
Usage: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
SET AUTOTRACE ON;
SET AUTOTRACE TRACE EXPLAIN;
SET AUTOTRACE TRACE EXPLAIN STATISTICS;

啟用AUTOTRACE功能，會在一個伺服器程式對應2個會話，一個查詢資料，一個記錄執行計劃和最終結果。SQLPLUS AUTOTRACE 是基於PLAN_TABLE表的方法來查詢執行計劃，內部實現其實和下面要講到的方法相同：

EXPLAIN PLAN FOR
SELECT * FROM TABLE_NAME;
SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY(format=>'BASIC'));

2.其實下面要說到的方法已經在說SQLPLUS AUTOTRACE時說到了，EXPLAIN PLAN FOR 與DBMS_XPLAN的結合。

EXPLAIN PLAN [SET statement_id='xxx'] FOR
SELECT * FROM TABLE_NAME;
SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY);

加入SET statement_id='xxx'可以使plan_table存放多個執行計劃。獲取執行計劃除了使用dbms_xplan.display外，還可以透過執行
utlxpls.sql //顯示序列查詢的計劃結果
utlxplp.sql //顯示並行查詢的計劃結果


使用這種方法也是基於PLAN_TABLE表來完成。實際和SQLPLUS AUTOTRACE是一樣的。

3.下面這種方法是直接查詢V$SQL_PLAN表，直接查詢V$SQL_PLAN沒有進行很好的格式化，看起來不太方便，ORACLE 10g開始提供了新的包來很好的格式化了V$SQL_PLAN的結果。也是第4種查詢方法。

4.DBMS_XPLAN.DISPLAY_CURSOR
SELECT * FROM TABLE_NAME;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);

普通使用者要使用DBMS_XPLAN.DISPLAY_CURSOR的話需要如下檢視的許可權：
grant select on v_$session to scott;
grant select on v_$sql_plan to scott;
grant select on v_$sql to scott;

這種實現和直接查詢V$SQL_PLAN是相同的。

OK，到這步需要提到一個問題了，我們觀察第一種和第二種方法是基於PLAN_TABLE表來生成的執行計劃，第三種和第四種方法是基於V$SQL_PLAN檢視來生成的執行計劃的。透過實際和一些論壇上的經驗發現對於一條SQL，這2種查詢執行計劃的結果是有可能不同的。當然第三種和第四種查詢的執行計劃是真實LIBRARY CACHE中真實的執行計劃。而第一種和第二種方法生成的執行計劃可以認為是預判斷出來的。所以我們在實際的生產環境中有時候會遇到，在SQLPLUS中執行的速度很快，一旦用到儲存過程或程式裡面就會變得奇慢，很可能就是因為執行計劃不同造成的。我們透過監控會發現2者生成的執行計劃完全不同，具體執行時生成了錯誤的執行計劃。這種情況有可能是由於引數CURSOR_SHARING=FORCE或者索引等造成的執行計劃錯誤。

另外，透過DBMS_XPLAN.DISPLAY_AWR函式獲取的執行計劃來自DBA_HIST_SQL_PLAN檢視，透過歷史資料記錄，甚至一些被老化的SQL執行計劃仍然可以被查到

還可以加入一些引數值：
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

SELECT plan_table_output FROM TABLE (DBMS_XPLAN.DISPLAY('PLAN_TABLE','NO','ALL'));
SELECT plan_table_output FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR('<sql_id>',2,'ALL')); //ALL對應的是FORMAT，有BASIC,TYPICAL,SERIAL,ALL幾個值，每個值對應顯示的內容不同，ALL顯示的內容最詳盡。預設是TYPICAL,SERIAL和TYPICAL顯示是相同的，只是SERIAL去掉了對並行的顯示。
SELECT plan_table_output FROM TABLE (DBMS_XPLAN.AWR('<sql_id>'));

如果要讓普通使用者能夠使用dbms_xplan.display_cursor和dbms_xplan.display_awr的話需要給普通使用者授予SELECT_CATALOG角色。

5.SQL TRACE
啟用：
alter session set sql_trace=on;
禁用：
alter session set sql_trace=off;

跟蹤其他使用者：
exec DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(SID,SERIAL#,SQL_TRACE);

6.診斷事件(10046)
啟用：
alter session set events '10046 trace name context forever,level 12';
禁用：
alter session set events '10046 trace name context off';

啟用其他使用者10046診斷:
exec DBMS_SYSTEM.SET_EV(SI,SE,EV,LE,NM);

開啟:exec DBMS_SYSTEM.SET_EV(1056,232,10046,12,'');
關閉:exec DBMS_SYSTEM.SET_EV(1056,232,10046,0,'');

另外如何檢視是否啟用了10046事件：
SQL> alter session set events '10046 trace name context forever ,level 12';

Session altered.

SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name
/u01/app/oracle/db_1/rdbms/log/test_ora_5529.trc
SQL> oradebug eventdump session
10046 trace name CONTEXT level 12, forever

7.使用oracle第三方工具：
plsql developer(F5)
Toad (Ctrl+E)