最權威Oracle擷取SQL語句執行計畫大全

最權威Oracle擷取SQL語句執行計畫大全

該文檔為根據相關資料整理、總結而成，主要講解Oracle資料庫中，擷取SQL語句執行計畫的最權威、最正確的方法、步驟，此外，還詳細說明了每種方法中可選項的意義及使用方法，以方便大家和自己日常工作中查閱使用。

1、查詢v$sql_plan:

SQL> col "Query Plan_Table" format a100

SQL> select id,lpad(' ', 2*(level-1))||operation||''||options||' '||object_name||' '||decode(id, 0, 'Cost='||cost) "QueryPlan_Table"

      fromv$sql_plan

      startwith id = 0

        andsql_id = '&&sql_id'

        andplan_hash_value = &&plan_hash

    connect byprior id = parent_id

        andsql_id = '&&sql_id'

        andplan_hash_value = &&plan_hash;

--註：：SQL_ID可查v$sqltext和dba_hist_sqltext,也可通過其他途徑獲得。

 

2、通過包DBMS_XPLAN

1）DISPLAY

SQL>explain plan for select * from t_users whereuser_id='TEST';

SQL>select * from table(dbms_xplan.display());

SQL>select * fromtable(dbms_xplan.display(null,null,'BASIC ROWS BYTES'));

--註：

1.TABLE_NAME：儲存查詢計劃的表名，預設值為PLAN_TABLE。

2.STATEMENT_ID ：SQL 陳述式ID。可在執行ExplainPlan 命令時，通過SetStatement_

id 子句來指定。如為NULL，則取最近一條被解釋的語句。

3.FORMAT：輸出格式。在DISPLAY 函數中，有以下預定義格式選：

1)'BASIC' ：基本格式。輸出的內容最少，僅輸出查詢計劃中每個操作的ID、名稱和選項及操作對象名。

2)'TYPICAL'：典型格式。除了基本格式中的內容外，還輸出每個操作的記錄行數、

位元組數、代價和時間，以及相關的提示資訊（如遠程SQL、最佳化器建議等）。如

存在謂詞，還輸出每個操作中的過濾條件和訪問條件。此外，如涉及分區表，還輸出分區裁剪資訊；如涉及並行查詢，還輸出並行操作資訊（如表隊列資訊、並行查詢分布方式等）。為預設格式。

3)'SERIAL'：串列執行格式。這種格式和典型格式的輸出內容基本一致，不同之處在於，對並行查詢，它不會輸出相關的並行內容。

4)'ALL'：完全格式。輸出的內容相對完整。除了典型格式的內容以外，還會輸出欄位投影資訊和別名資訊。

此外，使用者還可通過在格式化字串中添加或屏蔽一些關鍵詞進行細化輸出，

例如：“BASICROWS”，“TYPICAL-PREDICATE”等

1）ROWS：最佳化器估算出的記錄行數；

2）BYTES：最佳化器估算出的位元組數；

3）COST：最佳化器估算出的代價；

4）PARTITION：分區裁剪；

5）PARALLEL：並行查詢；

6）PREDICATE：謂詞；

7）PROJECTION：欄位投射；

8）ALIAS：別名；

9）REMOTE：分散式查詢資訊；

10）NOTE：相關注釋資訊。

2）DISPLAY_CURSOR

SQL>select /*+gather_plan_statistics*/* fromt_users where user_id='TEST'；

SQL>select * fromtable(dbms_xplan.display_cursor(null,null,'BASIC LAST ALLSTATS'));

--註：

1.DISPLAY_CURSOR 函數可以顯示記憶體中一個或者多個遊標的執行計畫；

2.使用者須對視圖V$SQLV、$SQL_PLAN 和V$SQL_PLAN_STATISTICS_ALL 的SELECT有許可權；

3.參數：

1)SQL_ID:如果沒有指定SQL_ID（指定NULL），則預設會顯示當前會話中最後一條執行的SQL 陳述式。

2)CURSOR_CHILD_NO：語句的子遊標序號,如果不指定，則會顯示該語句的所有子遊標的執行計畫。

3)FORMAT：格式化控制字元串。DISPLAY 函數的格式化控制字元串的所有選項都適用於DISPLAY_CURSOR 函數。如運行語句時通過GATHER_PLAN_STATISTICS或設定系統參數STATISTICS_LEVEL 為“ALL”收集語句啟動並執行效能統計資料，則在細化選項中還有額外的選項，以選擇是否輸出這些資料。

4)IOSTATS：是否輸出計劃的輸入輸出（IO）統計資料；

5)MEMSTATS ：在啟用了PGA 自動管理的情況下，是否輸出計劃的輸入記憶體統計資料（記憶體使用量量、記憶體讀次數等）；

6)ALLSTATS：包含了IOSTATS和MEMSTATS 的全部內容；

7) LAST ：以上三個選項輸出的是該遊標所有執行所產生的資料的總和。也可增加LAST選項以限定僅顯示最後一次啟動並執行統計資料。

此外，還有一些選項可用於該函數的輸出控制：

8)'ADVANCED' ：進階格式。進階格式除了會輸出完全格式中的所有內容外，還會視情況輸出綁定變數窺視資訊和計劃概要資訊；

9) OUTLINE：是否以提示方式執行程序表概要；

10)PEEKED_BINDS：是否顯示綁定變數窺視資訊；

11)BUFFSTATS：是否顯示記憶體讀次數（包括一致性讀和當前讀次數），該資訊為IOSTATS 的一部分；

12)PLAN_HASH：是否執行程序表的雜湊值，該選項同樣適用於DISPLAY函數。

3）DISPLAY_AWR

SQL>select sql_id, to_char(substr(sql_text,0,2000))

      fromdba_hist_sqltext

    whereupper(sql_text) like 'SELECT * FROM TEST%';

SQL>select * fromtable(dbms_xplan.display_awr('&sql_id'));

--註：

1.DISPLAY_AWR 函數顯示儲存在AWR曆史資料的執行計畫。

2.須對以下視圖有SELECT許可權：DBA_HIST_SQL_PLAN 和DBA_HIST_SQLTEXT。

3.參數：

1）SQL_ID ：可以從DBA_HIST_SQL_PLAN.SQL_ID或DBA_HIST_SQLTEXT.SQL_ID 獲得，該參數必須指定非空值，沒有預設值；

2）PLAN_HASH_VALUE ：如果該參數未指定或為NULL，則會顯示語句的所有執行計畫；

3）DB_ID：指定顯示哪個資料庫的執行計畫，預設為本機資料庫ID。頁可將其他資料庫的AWR匯入本地庫進行分析。

4）FORMAT：格式化控制字元串。與DISPLAY的相同選項類似。

 

4）DISPLAY_SQLSET

SQL>declare

2 ss_name varchar2(60);

3 begin

4 ss_name :=dbms_sqltune.create_sqlset();

5 dbms_sqltune.capture_cursor_cache_sqlset(ss_name,600,100);

6 dbms_output.put_line(ss_name);

7 end;

8 /

SQL>select sqlset_name,sql_id,sql_text fromDBA_SQLSET_STATEMENTS where upper(sql_text)

like 'SELECT * FROM TEST%';

SQL>select * fromtable(dbms_xplan.display_sqlset('STS_6','abcdefg',null,'BASIC ROWS COST'));

--註：

1.DISPLAY_SQLSET函數顯示儲存在一個SQL調優集中的語句的執行計畫。

2.參數：

1）SQLSET_NAME：SQL 集的名稱。每個SQL 集都有一個單獨的名稱（可在建立時

使用者指定，也可系統自動產生），需指定從哪個SQL集中讀取和顯示語句的執行計畫，該參數沒有預設值，必須指定；

2）SQL_ID ：可從USER/DBA/ALL_SQLSET_PLANS.SQL_ID獲得，該參數必須指定非空值，沒有預設值；

3）PLAN_HASH_VALUE：如果未指定或為NULL，則會顯示語句的所有執行計畫；

4）FORMAT：格式化控制字元串。與DISPLAY的FORMAT 選項相同；

5）SQLSET_OWNER：SQL集的所有者，預設為目前使用者名。

5）DISPLAY_SQL_PLAN_BASELINE

SQL>select * fromtable(dbms_xplan.display_sql_plan_baseline(sql_handle =>

'SYS_SQL_66cc81707e560a32'));

--註：

1.DISPLAY_SQL_PLAN_BASELINE 函數顯示儲存在資料字典當中SQL 執行計畫基準的計劃。

2.參數：

1）SQL_HANDLE：執行計畫基準所屬SQL的控制代碼名稱，由Oracle在建立或載入執行計畫到基準當中時自動產生，可以通過視圖dba_sql_plan_baselines查詢，預設為NULL；

2）PLAN_NAME ：執行計畫基準中某個執行計畫的名稱，由Oracle 建立或載入執行計畫到基準當中時自動產生，可以通過視圖dba_sql_plan_baselines查詢，預設為NULL；

3）FORMAT ：格式化控制字元串。DISPLAY_SQLSET 函數的格式化選項與DISPLAY

的選項相同。

4）當SQL_HANDLE和PLAN_NAME 都為空白時，顯示所有基準資料中的全部執行計畫。

 

3、AUTOTRACE

1)配置test使用者使用autot

SQL>conn sys/sys as sysdba

SQL>@?/SQLPLUS/ADMIN/PLUSTRCE.SQL

SQL>grant plustrace totest;

2)使用方法

1.SET AUTOTRACE ON：開啟AUTOTRACE，並輸出所有內容，包括語句本身的查詢結果、執行計畫，以及效能統計資料。

2.SET AUTOTRACE ON EXPLAIN ：開啟AUTOTRACE，並輸出語句本身的查詢結果和執行計畫，不輸出效能統計資料。

3.SET AUTOTRACE ON STATISTICS ：開啟AUTOTRACE，並輸出語句本身的查詢結果和效能統計資料，不輸出執行計畫。

4.SET AUTOTRACE TRACE ：開啟AUTOTRACE，並輸出執行計畫和效能統計資料，不輸出語句本身的查詢結果。

5.SET AUTOTRACE TRACE EXPLAIN ：開啟AUTOTRACE，並輸出執行計畫，不輸出語句本身的查詢結果和效能統計資料。

6.SET AUTOTRACE TRACESTATISTICS：開啟AUTOTRACE，並輸出效能統計資料，不輸出語句本身的查詢結果和執行計畫。

7.SET AUTOTRACE OFF：關閉AUTOTRACE。

 

4、其他方法

1）SQL_TRACE（或者10046 跟蹤事件）：該方法會在追蹤檔案裡顯示執行計畫及相關統計資訊：

SQL>alter session set sql_trace=true;

SQL>select * from t_users where user_id=’TEST’;

SQL>alter session set sql_trace=false;

SQL>select distinct spid from v$process p,v$session s, v$mystat m where p.addr=s.paddr and s.sid=m.sid;

SQL>show parameter user_dump_dest

SQL>ed/home/oracle/admin/ora10g/udump/ora10g_ora_sid.trc

2）OPTIMIZER_TRACE（或者10053 跟蹤事件）：該方法會在追蹤檔案裡記錄最佳化器分析選擇執行計畫的過程：

SQL>alter session set"_optimizer_trace"=ALL;

SQL>explain plan for select * from t_users whereusername=’TEST’;

SQL>alter session set"_optimizer_trace"=NONE;

SQL>select distinct spid from v$process p,v$session s, v$mystat m where p.addr=s.paddr and s.sid=m.sid;

SQL>ed/home/oracle/admin/ora10g/udump/ora10g_ora_sid.trc