Oracle中SQL調優（SQL TUNING）之最權威獲取SQL執行計劃大全

該文件為根據相關資料整理、總結而成，主要講解Oracle資料庫中，獲取SQL語句執行計劃的最權威、最正確的方法、步驟，此外，還詳細說明了每種方法中可選項的意義及使用方法，以方便大家和自己日常工作中查閱使用，因本人未發現本部落格支援附件上傳功能，需要PDF檔案格式的朋友可向我要，也可到群裡下載，轉載請註明出處。

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