查看SQL執行計劃的方法及優劣

知識 11-11

在 Oracle 的性能分析中，很多時候需要對 SQL 進行分析，而最重要的就是對執行計劃的分析。在本次的分享中，我主要介紹常用的查看 SQL 執行計劃的方法。

1AWR SQL report

AWR 報告是對資料庫性能診斷最常用的方式，同時 AWR SQL report 是展示AWR 負載信息中記錄的關於 SQL 的相關信息的報告。

AWR SQL report 使用的腳本為 $ORACLE_HOME/rdbms/admin/awrsqrpt.sql、awrsqrpi.sql。通常使用的腳本為 awrsqrpt.sql，該腳本可生成 SQL 在 AWR 信息內保存的執行計劃等信息。而 awrsqrpi.sql 可以配合 awr 信息的導出與導入的功能，在被導入的庫中生成相關的 AWR 報告。導入與導出的腳本同樣在如上的路徑里，為 awrextr、awrload，本次不進行演示。

使用方法：

與 AWR 報告生成方法一樣，首先登陸資料庫：

[oracle@localhost data]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed May 2 09:45:30 2018

Conneced to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

SQL> @?/rdbms/admin/awrsqrpt

Current Instance

~~~~~~~~~~~~~~~~

DB Id DB Name Inst Num Instance

----------- ------------ -------- ------------

231940243 H 1 h -----當前的資料庫

Specify the Report Type

~~~~~~~~~~~~~~~~~~~~~~~

Would you like an HTML report, or a plain text report?

Enter "html" for an HTML report, or "text" for plain text

Defaults to "html"

Enter value for report_type: -----選擇報告的格式（通常選擇默認HTML）

Instances in this Workload Repository schema

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

DB Id Inst Num DB Name Instance Host

------------ -------- ------------ ------------ ------------

* 231940243 1 H h localhost.lo ----生成報告的資料庫

caldomain

4026143893 1 NETDB netdb1 ywdb1

3391174693 1 FWPTDB fwptdb1 fwptdb01

4026143893 2 NETDB netdb2 ywdb2

3391174693 2 FWPTDB fwptdb2 fwptdb02

Using 231940243 for database Id

Using 1 for instance number

Specify the number of days of snapshots to choose from

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Entering the number of days (n) will result in the most recent

(n) days of snapshots being listed. Pressing <return> without

specifying a number lists all completed snapshots.

Enter value for num_days: -----列出N天前到現在的SNAP_SHOT

Listing the last 2 days of Completed Snapshots

Snap

Instance DB Name Snap Id Snap Started Level

------------ ------------ --------- ------------------ -----

h H 233 02 May 2018 09:45 1

234 02 May 2018 11:00 1

235 02 May 2018 12:00 1

236 02 May 2018 13:00 1

237 02 May 2018 14:00 1

Specify the Begin and End Snapshot Ids

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Enter value for begin_snap:

Enter value for begin_snap: 233 ----填寫開始時間點

Begin Snapshot Id specified: 233

Enter value for end_snap: 234 -----填寫結束時間點

End Snapshot Id specified: 234

Specify the SQL Id

~~~~~~~~~~~~~~~~~~

Enter value for sql_id: -----填寫生成報告的SQL ID

Enter value for sql_id: 0k8522rmdzg4k

SQL ID specified: 0k8522rmdzg4k

Specify the Report Name

~~~~~~~~~~~~~~~~~~~~~~~

The default report file name is awrsqlrpt_1_233_234.html. To use this name,

press <return> to continue, otherwise enter an alternative.

Enter value for report_name: ----填寫報告名稱

報告會生成在當前目錄中。

與 AWR 報告採集相同，因為性能數據的記錄是累計的值，所以兩個 snap_id 間如果重啟過資料庫會報錯。如果在選擇的 snap_id 間沒有該條 SQL 的信息同樣會報錯。

通常會用於問題診斷時對問題 SQL 的總體概況進行了解，因為可以查看 SQL 的多個執行計劃及各執行計劃的執行統計信息。

優勢：可以在報告中查看執行計劃的執行統計信息；

劣勢：需要生成報告；缺少執行計劃的謂詞信息；需要許可權較高。

2Explain plan for

Explain plan for 通常是想要知道某條 SQL 文本在資料庫中的執行計劃會使什麼樣。使用該方法的同時需要使用另一個函數 dbms_xplan.display()。

使用方法是，在想要知道執行計劃的 SQL 文本前加上 explain plan for ，然後執行。需要注意的是，該條 SQL 不會實際執行，只是生成執行計劃，並保存到 PLAN_TABLE$ 中。然後用 dbms_xplan.display() 來查看。

使用方法：

拼寫SQL：

explain plan for select * from a where owner="HR" ;

在會話中執行：

SQL> explain plan for select * from a where owner="HR" ;

Explained. ----執行計劃解析成功

查看執行計劃：

SQL> select * from table(dbms_xplan.display)

需要注意的是，該方法是根據當前的 session 的參數來進行解析，如果業務程序與當前會話的設置存在不同，很可能執行計劃不同。通常可用於驗證自己的優化方法是否會有效。比如在文本中添加了 hint，可以通過該方法驗證是否會奏效。

優勢：不需實際執行SQL；使用方便。

劣勢：可能存在與實際執行計劃不符。

3DBMS_XPLAN

DBMS_XPLAN 是 ORACLE 提供的工具包，如上邊的方法中就使用了其中一個過程。接下來介紹其他幾種常用方法。

DBMS_XPLAN.display_cursor

使用該函數可以查看在當前會話剛執行完畢的 SQL 的執行計劃

使用方法：

SQL> select * from a where owner="HR";

HR A

89290 89290 TABLE 25-APR-17 25-APR-17 2017-04-25:10:02:56 VALID N N N 1

……

SQL>

查看執行計劃：

SQL> select * from table(dbms_xplan.display_cursor(null,null,"advanced"));

SQL_ID cncydzc624m60, child number 1

-------------------------------------

select * from a where owner="HR"

Plan hash value: 3136622568

Query Block Name / Object Alias (identified by operation id):

-------------------------------------------------------------

1 - SEL$1 / A@SEL$1

2 - SEL$1 / A@SEL$1

Outline Data

/*+

BEGIN_OUTLINE_DATA

……

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("OWNER"="HR")

Column Projection Information (identified by operation id):

-----------------------------------------------------------

1 - "OWNER"[VARCHAR2,30], "A"."OBJECT_NAME"[VARCHAR2,128],

……

51 rows selected.

在該方法中存在三個參數第一個參數為 sql_id，第二個為 cursor_child_no，第三個為 format。在如上的部分中前兩個參數都為 null，默認情況下，會顯示當前會話最後執行的執行計劃，而 format 是選擇執行計劃的顯示信息，通常使用顯示最全的『advanced』。

優勢：執行計劃信息全面；

劣勢：存在與業務執行計劃不同的可能；

DBMS_XPLAN.display_cursor(『sql_id』,cursor_child_no,"advanced")

該方法可查看在資料庫 shared pool 中還存在的執行計劃信息。

使用方法：

SQL_ID cncydzc624m60, child number 0

-------------------------------------

select * from a where owner="HR"

Plan hash value: 3136622568

Query Block Name / Object Alias (identified by operation id):

-------------------------------------------------------------

1 - SEL$1 / A@SEL$1

2 - SEL$1 / A@SEL$1

Outline Data

-------------

/*+

BEGIN_OUTLINE_DATA

……

END_OUTLINE_DATA

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("OWNER"="HR")

Column Projection Information (identified by operation id):

-----------------------------------------------------------

1 - "OWNER"[VARCHAR2,30], "A"."OBJECT_NAME"[VARCHAR2,128],

…….

該方法使用簡單，執行計劃信息詳細，通常用於問題剛剛發生，或正在發生，通過該方法可獲取較為詳細的執行計劃信息。如果執行過後時間較長，執行計劃存在被替出的可能。

優勢：信息全面；使用方法簡便。

劣勢：可能存在時間過長已不在 shared pool 中。

dbms_xplan.display_awr("sql_id")

使用該方法可以查看 AWR 信息中的信息，與 AWR 報告查看的執行計劃基本一致，但是無法看到執行計劃的詳細統計信息。

使用方法：

SQL> select * from table(dbms_xplan.display_awr("0k8522rmdzg4k"));

SQL_ID 0k8522rmdzg4k

--------------------

select privilege# from sysauth$ where (grantee#=:1 or grantee#=1) and

privilege#>0

Plan hash value: 2057665657

-------------------------------------------------------------------------

--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | 2 (100)| |

| 1 | INLIST ITERATOR | | | | | |

| 2 | INDEX RANGE SCAN| I_SYSAUTH1 | 2 | 16 | 2 (0)| 00:00:01

15 rows selected.

使用改方法看到的執行計劃與 AWR 報告中的是相同的。但是因為沒有時間限制，會顯示所有的執行計劃。如果存在多個執行計劃，不好判斷問題時段使用的是哪個執行計劃。

優勢：保存時間較長。

劣勢：信息較少缺少謂詞信息等。

4AUTOTRACE

該方法可以在當前 session 開啟，通過該方法可以查看在該 session 執行的所有 SQL 的執行計劃及執行的消耗。

語法如下：

Set autotrace (off|on|traceonly)

[explain]

[statistics]

set autotrace on

這是最簡單的打開方法，當打開後，所有執行過的 SQL 在執行結果後，會顯示該條 SQL 的執行計劃，和資源消耗信息。

使用方法：

SQL> set autotrace on

SQL> select * from a where owner="HR";

HR A

89290 89290 TABLE 25-APR-17 25-APR-17 2017-04-25:10:02:56 VALID N N N 1

…….

Execution Plan

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("OWNER"="HR")

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

151 consistent gets

0 physical reads

0 redo size

59783 bytes sent via SQL*Net to client

942 bytes received via SQL*Net from client

40 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

576 rows processed

如上顯示，在該條 SQL 執行後，首先返回了查詢的數據，然後是該條 SQL 的執行計劃，最後是執行的信息。

Set autotrace traceonly

使用該命令開啟 autotrace 後，該條 SQL 執行完畢後不會返回具體的結果信息，只會返回結果的數量。同時接下來返回的與上個方法一至，返回執行計劃和資源消耗信息。

使用方法：

SQL> set autotrace traceonly

SQL> select * from a where owner="HR";

576 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 3136622568

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("OWNER"="HR")

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

151 consistent gets

0 physical reads

0 redo size

59783 bytes sent via SQL*Net to client

942 bytes received via SQL*Net from client

40 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

576 rows processed

SQL>

如上顯示該條 SQL 執行完畢後，未顯示所有的結果，而是只有返回的行數。接下來返回的內容與上個方法一至。對於該種打開方法，可以在 SQL 需要返回大量數據的情況下開啟。

Set autotrace traceonly explain

使用該方法打開 autotrace，返回的內容里只有該條 SQL 的執行計劃。

使用方法：

SQL>

SQL> Set autotrace traceonly explain

SQL> select * from a where owner="HR";

從如上的返回結果可以看到，只返回了該條 SQL 的執行計劃。

Set autotrace traceonly statistics

使用該方法打開 autotrace，只會顯示 SQL 的結果數量和資源的消耗量。

使用方法：

SQL> Set autotrace traceonly statistics

SQL> select * from a where owner="HR";

576 rows selected.

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

151 consistent gets

0 physical reads

0 redo size

59783 bytes sent via SQL*Net to client

942 bytes received via SQL*Net from client

40 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

576 rows processed

set autotrace off

關閉 autotrace 的功能。

AUTOTACE方式的優劣

優勢：使用方法簡單開啟後自動；信息全面

劣勢：存在與業務執行計劃不同的可能

10046事件

10046事件是在對複雜 SQL 的問題診斷時會使用的方法。使用 10046 事件可以明確看到執行計劃在每一步的資源消耗。

使用方法：

SQL> oradebug setmypid -----設置生成trace的進程

Statement processed.

SQL> oradebug event 10046 trace name context forever,level 12 --開啟10046trace 12級常用

Statement processed.

SQL> select * from a where owner="HR" -----執行SQL語句

……

SQL> oradebug tracefile_name -----查看trace生成路徑

/u01/app/oracle/diag/rdbms/h/h/trace/h_ora_121261.trc

SQL>

SQL> oradebug event 10046 trace name context off -----關閉10046 trace

Statement processed.

在 trace 路徑上使用 tkprof 格式化內容。

oracle@localhost Desktop]$ tkprof h_ora_121261.trc h_ora_121261_tkprof.trc

TKPROF: Release 11.2.0.4.0 - Development on Wed May 2 20:37:14 2018

打開格式化的文件即可查看詳細的各個步驟的消耗。

在 SQL 優化的時候通常是要對其他正 session 中正在運行的 SQL 進行分析。在這種情況下，就需要對其他session開啟10046 trace。

使用方法：

SQL> oradebug setospid 121265 -----設置生成trace的進程

Oracle pid: 44, Unix process pid: 121265, image: oracle@localhost.localdomain

SQL>

SQL> oradebug event 10046 trace name context forever,level 12 --開啟10046trace 12級常用

Statement processed.

SQL> oradebug tracefile_name -----查看trace生成路徑

/u01/app/oracle/diag/rdbms/h/h/trace/h_ora_121265.trc

SQL> oradebug event 10046 trace name context off -----關閉10046 trace

Statement processed.

在 trace 路徑上使用 tkprof 格式化內容。

[oracle@localhostDesktop]$ttkprof /u01/app/oracle/diag/rdbms/h/h/trace/h_ora_121265.trc /usr/oracle/Desktop/h_ora_121265_tkprof.trc

TKPROF: Release 11.2.0.4.0 - Development on Wed May 2 20:55:15 2018

如上為在其他 session 開啟 10046，與上個不同點在於需要指定 process 進程號。可以通過 v$session 和 v$process 聯合查看 session 對應的 process。

SELECT a.spid,

b.sid

FROM v$process a,

v$session b

WHERE a.addr= b.paddr

AND b.sid = &sid

查看SQL執行計劃的方法及優劣

打開今日頭條，查看更多精彩圖片喜歡這篇文章嗎？立刻分享出去讓更多人知道吧！

本站內容充實豐富，博大精深，小編精選每日熱門資訊，隨時更新，點擊「搶先收到最新資訊」瀏覽吧！

請您繼續閱讀更多來自 程序員小新人學習 的精彩文章:

※JS常用方法匯總
※小白也能玩轉開源項目，你與大神只差這幾步！

TAG:程序員小新人學習 |