実行計画を実行順に表示させてみる

JPOUG Advent Calendar 2015 13日目のエントリーです。このイベントは初めての参加、かつブログ投稿自体久しぶりとなりましたのでよろしくお願いします。前日はTakahiro YAMADAさんでした。

Oracleの実行計画ツリーって分かりやすい。。。だけどたどりにくい?

実行計画ツリー(マニュアルでは「行ソース・ツリー」)の解析は、OracleデータベースのSQLチューニング作業においては基本中の基本です。

実行計画ツリーは、OracleのSQL実行が階層的に逐次処理されていることを直感的に把握しやすく、どこにボトルネックが存在するかも分かりやすい表示だと思います。

実行計画ツリーの表示方法は以下のリンクになります。
12.4 PLAN_TABLE出力の表示

右から左、上から下へ

11gR2パフォーマンス・チューニング・ガイドの19.2.3 グローバル表のヒントの指定方法の例を題材に実行計画ツリーの見方をおさらいします。

まずは、ビューの作成から。

SQL> CREATE OR REPLACE VIEW V AS
  2    SELECT
  3       e1.first_name
  4     , e1.last_name
  5     , j.job_id
  6     , sum(e2.salary) total_sal
  7    FROM
  8       employees                     e1
  9     , ( SELECT * FROM employees e3) e2
 10     , job_history                   j
 11    WHERE e1.employee_id = e2.manager_id
 12    AND   e1.employee_id = j.employee_id
 13    AND   e1.hire_date   = j.start_date
 14    AND   e1.salary      = ( SELECT
 15                               max(e2.salary)
 16                              FROM
 17                               employees e2
 18                              WHERE e2.department_id = e1.department_id
 19                            )
 20    GROUP BY e1.first_name, e1.last_name, j.job_id
 21    ORDER BY total_sal;

ビューが作成されました。

次に、Expain Plan文でSQL文の解析結果をPLAN TABLEに格納し、DBMS_XPLANプロシージャで実行計画ツリーを確認します。

SQL> explain plan for
  2  SELECT /*+ INDEX(v.e2.e3 emp_job_ix) */  * FROM V;
 
解析されました。

SQL> select plan_table_output from table(dbms_xplan.display());
 
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------
Plan hash value: 447943362
 
--------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                         |     1 |    46 |    17  (18)| 00:00:01 |
|   1 |  VIEW                              | V                       |     1 |    46 |    17  (18)| 00:00:01 |
|   2 |   SORT ORDER BY                    |                         |     1 |    79 |    17  (18)| 00:00:01 |
|   3 |    HASH GROUP BY                   |                         |     1 |    79 |    17  (18)| 00:00:01 |
|   4 |     NESTED LOOPS                   |                         |       |       |            |          |
|   5 |      NESTED LOOPS                  |                         |     1 |    79 |    15   (7)| 00:00:01 |
|   6 |       NESTED LOOPS                 |                         |     1 |    71 |     6  (17)| 00:00:01 |
|   7 |        NESTED LOOPS                |                         |     1 |    50 |     5  (20)| 00:00:01 |
|   8 |         VIEW                       | VW_SQ_1                 |     1 |    16 |     4  (25)| 00:00:01 |
|*  9 |          FILTER                    |                         |       |       |            |          |
|  10 |           HASH GROUP BY            |                         |     1 |     7 |     4  (25)| 00:00:01 |
|  11 |            TABLE ACCESS FULL       | EMPLOYEES               |   107 |   749 |     3   (0)| 00:00:01 |
|* 12 |         TABLE ACCESS BY INDEX ROWID| EMPLOYEES               |     1 |    34 |     1   (0)| 00:00:01 |
|* 13 |          INDEX RANGE SCAN          | EMP_DEPARTMENT_IX       |    10 |       |     0   (0)| 00:00:01 |
|  14 |        TABLE ACCESS BY INDEX ROWID | JOB_HISTORY             |     1 |    21 |     1   (0)| 00:00:01 |
|* 15 |         INDEX UNIQUE SCAN          | JHIST_EMP_ID_ST_DATE_PK |     1 |       |     0   (0)| 00:00:01 |
|  16 |       INDEX FULL SCAN              | EMP_JOB_IX              |   107 |       |     1   (0)| 00:00:01 |
|* 17 |      TABLE ACCESS BY INDEX ROWID   | EMPLOYEES               |     1 |     8 |     9   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   9 - filter(MAX("E2"."SALARY")>0)
  12 - filter("E1"."SALARY"="MAX(E2.SALARY)")
  13 - access("ITEM_1"="E1"."DEPARTMENT_ID")
  15 - access("E1"."EMPLOYEE_ID"="J"."EMPLOYEE_ID" AND "E1"."HIRE_DATE"="J"."START_DATE")
  17 - filter("E1"."EMPLOYEE_ID"="E3"."MANAGER_ID")
 
33行が選択されました。

行ソース・オペレーション(Operation)の実行順をIDで表すと

11 → 10 → 9 → 8 → 13 → 12 → 7 → 15 → 14 → 6 → 16 → 5 → 17 → 4 → 3 → 2 → 1 → 0

となります。

原則は「右から左、同じレベルは上から下」です。

実際にOracleの中でこの順番に処理が行われているかどうかはわかりませんが、論理的な道筋としてはこのようになります。

SQL文のコストは先に行われたオペレーションのコストに後続のコストが積み上がっていくことによって決まるので、コストが高いオペレーションの中でもより早く実行されるものに着目する必要があります。

しかし、実行計画ツリーは実行計画全体を直感的に把握するには役立ちますが、実際のチューニング作業で実行順序を理解するのは難しく感じます。

例えば、ID=6とID=16が同じ階層にあるということを確認するために、ディスプレイに透明な定規を当ててみた、なんて経験はありませんか?私はよくあります。

秘密兵器を開発してみた

以前から実行順序をもっと簡単に確認する術はないだろうかということを考えていたのですが、以下の要領で確認用スクリプトを実行した結果を紹介します。
(結果をHTMLファイルにスプール出力しますので、一時的に画面出力をオフにしています。)

SQL> set termout off
SQL> @execplan
SQL> set termout on

↓実行結果はこちら
execplan.html

左端(SEQ列)は実行順序になります。そして右端(ID列)は通常の実行計画ツリーにおけるID列と同じです。つまり、実行計画ツリーとこのスクリプトの実行結果を併用して使うことを意識しています。

上から順番に読んでいって、コストが急激に増加しているオペレーションに注目します。(この例は問題ありませんが)

PREDICATES列やPROJECTION列を参考にコストをより小さくするようにチューニングします。(これらの列を表示したいためにあえてHTMLで出力しました。)

スクリプト公開

実際のスクリプトは以下のとおりです。

PLAN_TABLEの内容を加工して出力しているだけですので悪さはしませんが、自己責任でお使いください。

execplan.sql

SET MARKUP HTML ON SPOOL ON PREFORMAT OFF ENTMAP OFF -
HEAD "<TITLE>Execution Plan</TITLE> -
<STYLE type='text/css'> -
<!-- BODY {background: #FFFFFF} --> -
</STYLE>" -
BODY "TEXT='#000000'" -
TABLE "WIDTH='90%' BORDER='1'"
set pages 200
set lines 300
set feed off
spool execplan.html
select
 rownum seq
,pt.OPERATION
,pt.OBJECT_NAME
,pt.ALIAS
,pt.TYPE
,pt.COST
,pt.CARDINALITY
,pt.PREDICATES
,pt.PROJECTION
,pt.ID
from (
select distinct
 ID
,PARENT_ID PID
,POSITION
,DEPTH
,case ID - DEPTH when 0 then DEPTH
                        else DEPTH -1
 end as XDEPTH
,OPERATION||' '||OPTIONS OPERATION
,OBJECT_NAME
,OBJECT_ALIAS ALIAS
,OBJECT_TYPE TYPE
,OBJECT_INSTANCE
,COST
,CARDINALITY
,case when ACCESS_PREDICATES is not null
        then 'access: '||ACCESS_PREDICATES
      when FILTER_PREDICATES is not null
        then 'filter: '||FILTER_PREDICATES
      else null
 end as PREDICATES
,PROJECTION
from PLAN_TABLE
order by
 XDEPTH desc
,POSITION desc
,ID
) pt;
spool off
SET MARKUP HTML OFF

ポイントはDEPTH

このスクリプトのポイントは「DEPTH」列の扱いです。単純にDEPTH列の降順でソートしても正しい結果が得られないのです。

そこで、ID <> DEPTH となる場合のみ DEPTH値から1引くというロジックを入れてみました。(XDEPTH列)

以下のクエリー、特にorder by句に注目してみてください。

SQL> select
  2   ID
  3  ,PARENT_ID PID
  4  ,DEPTH
  5  ,case ID - DEPTH when 0 then DEPTH
  6                          else DEPTH -1
  7   end as XDEPTH
  8  ,POSITION
  9  from PLAN_TABLE
10  order by
11   XDEPTH desc
12  ,POSITION desc
13  ,ID;
 
ID  PID DEPTH XDEPTH POSITION
--- --- ----- ------ --------
 11  10    11     11        1
 10   9    10     10        1
  9   8     9      9        1
  8   7     8      8        1
 13  12     9      8        1
 12   7     8      7        2
  7   6     7      7        1
 15  14     8      7        1
 14   6     7      6        2
  6   5     6      6        1
 16   5     6      5        2
  5   4     5      5        1
 17   4     5      4        2
  4   3     4      4        1
  3   2     3      3        1
  2   1     2      2        1
  1   0     1      1        1
  0         0      0       17
 
18行が選択されました。

  • 常にexecplan.htmlというファイルに出力している。
  • V$SQL_PLANに対応していない。
  • パラレルクエリーに対応していない。

など、改良の余地ありありですが、ご自由にカスタマイズしてください。

最近チューニングの場面に遭遇する機会がめっきり減ってしまって、私自身このスクリプトを実践で試してはいないのですが、使用してみてコメントをいただけたら幸いです。

明日は渡部亮太さんです。(最近すごい勢いで投稿してるみたいですね!w)