獲得目標SQL語句執行計劃的方法

Oracle用來執行目標SQL語句的一系列步驟的組合被稱為執行計劃。
得到目標SQL執行計劃的方法：

1、explain plan命令
explain plan for select ename,sal from emp;
select * from table(dbms_xplan.display);

2、dbms_xplan包
（1）select * from table(dbms_xplan.display); /*需要與explain plan for命令配合使用*/
（2）select empno,ename,dname from emp,dept where emp.deptno=dept.deptno;
     select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
     第三個引數可以是advanced，也可以是all，all與advanced比，輸出的結果少了outline data部分的內容
（3）只要目標SQL的執行計劃所在的child cursor還沒有被age out出Shared Pool,就可以是用該種方法來檢視執行計劃
     select sql_text,sql_id,hash_value,child_number from v$sql where sql_text like 'select empno,ename,dname%';
     select * from table(dbms_xplan.display_cursor('sql_id/hash_value',child_cursor_number,'advanced'));
     第一個引數為SQL的SQL ID或者SQL hash value，第二個引數為要檢視的執行計劃所在的child cursor number，第三個引數為advanced或者all
舉例：
SQL> select sql_text,sql_id,hash_value,child_number from v$sql where sql_text like 'select empno,ename,dname%';
SQL_TEXT           SQL_ID         HASH_VALUE CHILD_NUMBER
---------------------------------------------------------------------- ------------- ---------- ------------
select empno,ename,dname from emp,dept where emp.deptno=dept.deptno    7ww0fhpbqqt0n 1466655764   0
select * from table(dbms_xplan.display_cursor('7ww0fhpbqqt0n',0,'advanced'));
（4）select * from table(dbms_xplan.display_awr('sql_id'));
只要SQL的執行計劃被Oracle採集到AWR repository中，不管現在執行計劃是不是還在Shard Pool中，都可以是用改語句檢視SQL的所有歷史執行計劃。

3、autotrace開關
set autotrace on                       /*顯示SQL執行結果、對應的執行計劃和資源消耗情況*/
set autotrace off                      /*只會顯示SQL執行結果，為autotrace開關的預設值*/
set autotrace traceonly                /*顯示執行計劃和資源消耗情況*/
set autotrace traceonly explain        /*只顯示執行計劃*/
set autotrace traceonly statistics     /*只顯示資源消耗情況*/

4、10046事件與tkprof命令
這種方法所得到的執行計劃比前三種方法好的地方在於：明確顯示了目標SQL實際執行過程中每一個執行步驟所消耗的邏輯讀、物理讀和所花費的時間。這種細粒度的顯示對我們診斷複雜的SQL效能問題時特別有用。
用該種方法得到執行計劃的步驟：
（1）在當前session中啟用10046事件：
alter session set events '10046 trace name context forever,level 12';
或者
oradebug event 10046 trace name context forever,level 12;
/*推薦使用第二種方法，可以是用oradebug tracefile_name來得到當前session所對應的trace檔案的路徑和名稱*/
（2）在session中執行目標SQL
（3）在當前session中關閉10046事件
alter session set events '10046 trace name context off';
或者
oradebug event 10046 trace name context off;

舉例說明：
SQL> conn / as sysdba;
SQL> oradebug setmypid
Statement processed.
/*一定要有此步驟，要不會有ORA-00074: no process has been specified錯誤*/
SQL> oradebug event 10046 trace name context forever,level 12;
Statement processed.
SQL> select empno,ename,dname from scott.emp,scott.dept where emp.deptno=dept.deptno;
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_29480.trc
SQL> oradebug event 10046 trace name context off
$ tkprof /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_29480.trc /tmp/wjw_trace.txt