最權威Oracle獲取SQL執行計劃大全

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

1、查詢v$sql_plan:

SQL> col "Query Plan_Table" format a100

SQL> select id,lpad(' ', 2*(level⑴))||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，則取最近1條被解釋的語句。

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

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

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

字節數、代價和時間，和相干的提示信息（如遠程SQL、優化器建議等）。如

存在謂詞，還輸出每一個操作中的過濾條件和訪問條件。另外，如觸及分區表，還輸出分區裁剪信息；如觸及并行查詢，還輸出并行操作信息（如表隊列信息、并行查詢散布方式等）。為默許格式。

3)'SERIAL'：串行履行格式。這類格式和典型格式的輸出內容基本1致，不同的地方在于，對并行查詢，它不會輸出相干的并行內容。

4)'ALL'：完全格式。輸出的內容相對完全。除典型格式的內容之外，還會輸出字段投影信息和別名信息。

另外，用戶還可通過在格式化字符串中添加或屏蔽1些關鍵詞進行細化輸出，

例如：“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 函數可以顯示內存中1個或多個游標的履行計劃；

2.用戶須對視圖V$SQLV、$SQL_PLAN 和V$SQL_PLAN_STATISTICS_ALL 的SELECT有權限；

3.參數：

1)SQL_ID:如果沒有指定SQL_ID（指定NULL），則默許會顯示當前會話中最后1條履行的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 ：以上3個選項輸出的是該游標所有履行所產生的數據的總和。也可增加LAST選項以限定僅顯示最后1次運行的統計數據。

另外，還有1些選項可用于該函數的輸出控制：

8)'ADVANCED' ：高級格式。高級格式除會輸出完全格式中的所有內容外，還會視情況輸出綁定變量窺視信息和計劃概要信息；

9) OUTLINE：是不是以提示方式顯示計劃概要；

10)PEEKED_BINDS：是不是顯示綁定變量窺視信息；

11)BUFFSTATS：是不是顯示內存讀次數（包括1致性讀和當前讀次數），該信息為IOSTATS 的1部份；

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函數顯示存儲在1個SQL調優集中的語句的履行計劃。

2.參數：

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

用戶指定，也可系統自動生成），需指定從哪一個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

生活不易，碼農辛苦
如果您覺得本網站對您的學習有所幫助,可以手機掃描二維碼進行捐贈