oracle如何查看執行計畫

標籤：div   flush   情況   語句   部署   put   where   count   關閉   

1.在PL/SQL Developer中得到一個SQL的執行計畫
輸入想要查看執行計畫的目標SQL，再按一下快速鍵F5就可以了。
2.explain plan 命令
explain plan for + 目標SQL
select * from table(dbms_xplan.display)
3. DBMS_XPLAN 包
1) select * from table(dbms_xplan.display_cursor(null,null,‘advanced‘))
它用於在SQLPLUS中查看剛剛執行過的SQL的執行計畫。這裡所傳入的第一個和第二個參數的值均為null，第三個參數的值是 advanced，
第三個輸入參數的值也可以是 all，只不過用 advanced 後的顯示結果會比用 all 的顯示結果更詳細一些。
2) select * from table(dbms_xplan.display_cursor(‘sql_id/hash_value‘,child_cursor_number,‘advanced‘))
它用於查看指定SQL的執行計畫。這裡所傳入的第一個參數的值是指定SQL的SQL ID 或者 SQL HASH VALUE，第二個參數的值是要查看的
執行計畫所在的Child Cursor Number，第三個參數和上邊的介紹一樣，我們一般都是用 advanced
只要目標SQL所對應的Child Cursor還在Library Cache中，我們就可以從 V$SQL 中查詢到目標SQL的Child Cursor的詳細資料，包括
SQL ID、SQL HASH VALUE、Child Cursor Number等。
select sql_text,sql_id,hash_value,child_number from v$sql where sql_text like ‘SQL目標語句‘;
只要目標SQL的執行計畫所在的Child Cursor還沒有被age out 出 Shared Pool，就可以使用該方法來查看該SQL的執行計畫。
3) select * from table(dbms_xplan.display_awr(‘sql_id‘))
它用於查看指定SQL的所有曆史執行計畫。使用方法1),2)能夠顯示目標SQL執行計畫的前提是該SQL的執行計畫還在Shared Pool中，而如果
該SQL的執行計畫已經被 age out 出 Shared Pool，那麼只要該SQL的執行計畫被Oracle採集到AWR Repository中，我們就可以使用該方法來查
看該SQL的所有曆史執行計畫。
手工採集AWR報告的方法：exec dbms_workload_repository.create_snapshot();
查看目標SQL的執行計畫是否被 age out 出 Shared Pool: select sql_text,sql_id,version_count,executions from v$sqlarea where
sql_text like ‘目標SQL‘;
清空Shared Pool：alter system flush shared_pool;(請勿隨意在生產環境執行此語句)
4. AUTOTRACE 開關
set autotrace [off | on | traceonly]
1) 在SQLPLUS的當前Session中執行命令 set autotrace on, 可以在當前Session中將autotrace開關完全開啟。這樣，在這個Session中隨後
執行的所有SQL語句，除了顯示SQL執行結果之外，還會額外顯示這些SQL所對應的執行計畫和資源消耗情況。
2) 在SQLPLUS的當前Session中執行命令 set autotrace off, 可以在當前Session中將autotrace開關關閉，這樣，在這個Session中隨後執行
的所有SQL都只會顯示SQL執行結果。autotrace開關的預設值是off.
3) 在SQLPLUS的當前Session中執行命令 set autotrace traceonly, 可以在當前Session中將autotrace開關以不顯示SQL執行結果的具體內容
的方式開啟。適用於那些SQL執行結果的具體內容特別長，會連續刷屏的SQL, 這種情況下我們不關心SQL的執行結果，而只是關係SQL的執行計畫
和資源消耗量。
5. 10046事件與 tkprof命令（得到的執行計畫最準確）
這種方法可以得到執行計畫中每一個執行步驟所消耗的邏輯讀、物理讀和花費的時間。
1) oradebug setmypid
表示準備對當前Session使用oradebug命令。
2) oradebug event 10046 trace name context forever,level 12
在當前Session中啟用10046事件。
3) 執行目標SQL
4) oradebug tracefile_name
顯示當前Session啟用10046事件後所對應的trace檔案的路徑和名稱。
5) oradebug event 10046 trace name context off
在當前Session中關閉10046事件。
6) tkprof trace檔案絕對路徑 翻譯後目標檔案的絕對路徑
使用tkprof命令翻譯trace檔案，使結果更直觀。
6.（得到真實的、準確的執行計畫） 如果目標SQL的執行計畫還在Shared Pool中，可以使用指令碼display_cursor-9i.sql和預存程序printsql來得到其真實的執行計畫和資源消耗情況。如果目標SQL的執行計畫已經被age out 出 Shared Pool了，我們可以執行DBMS_XPLAN.DISPLAY_AWR 或者使用 AWR SQL Reporting和 Statspack SQL Reporting來得到其曆史執行計畫和資源消耗。
1) display_cursor_9i.sql的用法（適用於Oracle 9i 及其以後的資料庫版本）
在執行指令碼時，傳入待查看執行計畫的目標SQL的SQL HASH VALUE 和 Child Cursor Number
a. 執行目標SQL
b. 查詢HASH_VALUE 和 Child Number
select sql_text,hash_value,child_number from v$sql where sql_text like ‘目標SQL‘;
c. 執行指令碼
@指令碼 HASH_VALUE CHILD_NUMBER
2) printsql的用法（適用於9i/10g/11g）
預存程序printsql是在指令碼display_cursor_9i.sql上的封裝，它可以把指定SPID或者Session ID的進程或者Session 正在執行的SQL以及其對應的真實執行計畫、資源消耗情況列印出來。
a. 執行 topas
得到oracle的PID
b. set serveroutput on size 1000000
c. exec printsql(PID,‘SPID‘)
注釋：PID是a查詢出來的PID，‘SPID‘原樣輸入。
3) 擷取AWR SQL Reporting（10g及其以後的版本）
手工執行指令碼 $ORACLE_HOME/rdbms/admin/awrsqrpt.sql,並依此輸入報告類型(text/html)、要查看的快照範圍(最近幾天內的快照)、目標SQL ID和所要生產的AWR SQL Reporting的名稱。
4) 擷取Statspack SQL Reporting（9i）
手工執行指令碼 $ORACLE_HOME/rdbms/admin/sprepsql,並依此輸入要查看的快照的範圍、目標SQL HASH VALUE 和所要產生的Statspack SQL報告的名稱。
注釋：事先已經部署了Statspack報告，並且採集Statspack報告的level值大於等於6（Statspack 報告level的預設值是5）

7.如何查看執行計畫的執行順序
先從最開頭一直連續往右看，直到看到最右邊的並列的地方；對於不並列的，靠右的先執行；如果見到並列的，從上往下看，對於並列的部分，靠上的先執行。

oracle如何查看執行計畫