查看Oracle執(zhí)行計劃的幾種常用方法-系列1

SQL的執(zhí)行計劃實際代表了目標SQL在Oracle數(shù)據(jù)庫內(nèi)部的具體執(zhí)行步驟，作為調(diào)優(yōu)，只有知道了優(yōu)化器選擇的執(zhí)行計劃是否為當前情形下最優(yōu)的執(zhí)行計劃，才能夠知道下一步往什么方向。

執(zhí)行計劃的定義：執(zhí)行目標SQL的所有步驟的組合。

我們首先列出查看執(zhí)行計劃的一些常用方法：

1. explain plan命令

PL/SQL Developer中通過快捷鍵F5就可以查看目標SQL的執(zhí)行計劃了。但其實按下F5后，實際后臺調(diào)用的就是explain plan命令，相當于封裝了該命令。

explain plan使用方法：

(1) 執(zhí)行explain plan for + SQL

(2) 執(zhí)行select * from table(dbms_xplan.display);

實驗表準備：

SQL> desc test1;
 Name  Null           Type
 ----------------------------------------- -------- ----------------------------
 T1ID    NOT NULL NUMBER(38)
 T1V                       VARCHAR2(10)

SQL> desc test2;
 Name  Null           Type
 ----------------------------------------- -------- ----------------------------
 T2ID    NOT NULL NUMBER(38)
 T2V                       VARCHAR2(10)

實驗：

SQL> set linesize 100

SQL> explain plan for select t1id, t1v, t2id, t2v from test1, test2 where test1.t1id = test2.t2id;

Explained.

第一步使用explain plan對目標SQL進行了explain，第二步使用select * from table(dbms_xplan.display)語句展示出該SQL的執(zhí)行計劃。

這里test2作為驅(qū)動表，進行了全表掃描，test1作為被驅(qū)動表，由于其包含主鍵，所以用的是索引全掃描。左側(cè)ID帶*號的第四步操作，表示有謂詞條件，這里可以看到既使用了主鍵索引(access)，又使用了過濾條件(filter)。

2. DBMS_XPLAN包

(1) select * from table(dbms_xplan.display);--上面以說明。

(2) select * from table(dbms_xplan.display_cursor(null, null, 'advanced'));

(3) select * from table(dbms_xplan.display_cursor('sql_id/hash_value', child_cursor_number, 'advanced'));

(4) select * from table(dbms_xplan.display_awr('sql_id'));

(2) select * from table(dbms_xplan.display_cursor(null, null, 'advanced'));

主要用于SQLPLUS中查看剛執(zhí)行過SQL的執(zhí)行計劃。首先第三個參數(shù)可以選擇'advanced'：

接下來，第三個參數(shù)使用'all'：

可以看出'advanced'記錄的信息要比'all’多，主要就是多一個Outline Data。Outline Data主要是執(zhí)行SQL時用于固定執(zhí)行計劃的內(nèi)部HINT組合，可以將這部分內(nèi)容摘出來加到目標SQL中以固定其執(zhí)行計劃。

(3) select * from table(dbms_xplan.display_cursor('sql_id/hash_value', child_cursor_number, 'advanced'));

其中第一個參數(shù)可以輸入SQL的sql_id或hash value，方法就是如果執(zhí)行的SQL仍在庫緩存中，則可以使用V$SQL查詢：

其中，使用@dbsnake大牛的SQL可以知道SQL_ID和HASH_VALUE的一一對應(yīng)關(guān)系：

隱藏問題1：

這里的截圖可能有點問題，結(jié)果并不準確，問題就出在這個SQL中使用的算法中，在另一篇博文中會仔細說明這個問題。

使用：

SQL> select * from table(dbms_xplan.display_cursor('1p2fk2v00c865', 0, 'advanced'));

或

select * from table(dbms_xplan.display_cursor('3221627077', 0, 'advanced'));

就可以查出對應(yīng)這條SQL的執(zhí)行計劃，內(nèi)容同(2)中的'advanced'，這就不展示了。

注意這還有第二個參數(shù)child_cursor_number，指的是子游標編號，如果未生成新的子游標，則此處寫的是0。

(2)和(3)的結(jié)論相近，區(qū)別就是(2)只是針對最近一次執(zhí)行SQL查看執(zhí)行計劃，(3)可以針對仍在庫緩存中的任意一次SQL查看執(zhí)行計劃。

(4) select * from table(dbms_xplan.display_awr('sql_id'));

(1)是使用explain plan for +SQL作為前提，(2)和(3)的前提則是SQL的執(zhí)行計劃還在共享池中，具體講是在庫緩存中。如果已經(jīng)被age out交換出共享池，則不能用這兩種方法了。若該SQL的執(zhí)行計劃被采集到AWR庫中，則可以用(4)來查詢歷史執(zhí)行計劃。

隱藏問題2：

實驗這部分內(nèi)容發(fā)現(xiàn)使用select * from table(dbms_xplan.display_awr('sql_id'));并沒有結(jié)果，@黃瑋老師說有可能是AWR收集的是top的SQL，有可能測試用的SQL不是most intensive SQL，但我是用alter system flush shared_pool后執(zhí)行的手工采集快照，還是未被AWR抓到，比較奇怪的問題，這個也會在另一篇博文中仔細說明。

未完待續(xù) 。。。

To be continued ...

生活不易，碼農(nóng)辛苦
如果您覺得本網(wǎng)站對您的學(xué)習(xí)有所幫助,可以手機掃描二維碼進行捐贈