查看Oracle執行計畫的幾種常用方法-系列1

標籤：oracle   執行計畫   dbms_xplan   

SQL的執行計畫實際代表了目標SQL在Oracle資料庫內部的具體執行步驟，作為調優，只有知道了最佳化器選擇的執行計畫是否為當前情形下最優的執行計畫，才能夠知道下一步往什麼方向。

執行計畫的定義：執行目標SQL的所有步驟的組合。

我們首先列出查看執行計畫的一些常用方法：

1. explain plan命令

PL/SQL Developer中通過快速鍵F5就可以查看目標SQL的執行計畫了。但其實按下F5後，實際後台調用的就是explain plan命令，相當於封裝了該命令。

explain plan使用方法：

(1) 執行explain plan for + SQL

(2) 執行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的執行計畫。

這裡test2作為驅動表，進行了全表掃描，test1作為被驅動表，由於其包含主鍵，所以用的是索引全掃描。左側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中查看剛執行過SQL的執行計畫。首先第三個參數可以選擇‘advanced‘：

接下來，第三個參數使用‘all‘：

可以看出‘advanced‘記錄的資訊要比‘all’多，主要就是多一個Outline Data。Outline Data主要是執行SQL時用於固定執行計畫的內部HINT組合，可以將這部分內容摘出來加到目標SQL中以固定其執行計畫。

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

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

其中，使用@dbsnake大牛的SQL可以知道SQL_ID和HASH_VALUE的一一對應關係：

隱藏問題1：

這裡的可能有點問題，結果並不準確，問題就出在這個SQL中使用的演算法中，在另一篇博文中會仔細說明這個問題。

使用：

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

或

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

就可以查出對應這條SQL的執行計畫，內容同(2)中的‘advanced‘，這就不展示了。

注意這還有第二個參數child_cursor_number，指的是子遊標編號，如果未產生新的子遊標，則此處寫的是0。

(2)和(3)的結論相近，區別就是(2)只是針對最近一次執行SQL查看執行計畫，(3)可以針對仍在庫緩衝中的任意一次SQL查看執行計畫。

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

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

隱藏問題2：

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

未完待續 。。。

To be continued ...

查看Oracle執行計畫的幾種常用方法-系列1