by Hayato Matsuura.

実行計画の作成


DB2 LUWで実行計画を得るには、最初にだけ実行する設定も含め3つのステップからなります。

必要なテーブルの作成 (最初だけ)

必要なテーブルを作成する推奨の方法は、以下のプロシージャを呼び出す事です。

CALL SYSPROC.SYSINSTALLOBJECTS('EXPLAIN', 'C', 
        CAST (NULL AS VARCHAR(128)), CAST (NULL AS VARCHAR(128)))

これにより、SYSTOOLSスキーマ内に 必要なテーブルがインストールされます(例えばSYSTOOLS.EXPLAIN_STREAM)。

他のインストール手順やカスタマイズについては、ドキュメント を参照してください。

実行計画の生成

SQL文の前にexplain plan forを付けて実行すると、 実行計画用のテーブルに詳細が保存されます。

このステップでは実行計画の表示はされず、データベースに保存されるだけです。

EXPLAIN PLAN FOR SELECT 1 FROM sysibm.sysdummy1

保存された実行計画の表示

IBMは、実行計画用の テーブルに保存されたデータを表示するツールを提供しています。 しかし、出力のフォーマットは期待するほど便利ではないので、 必要な情報を表示するために独自のクエリを使っています。 DB2のドキュメントでも、そういうった方法を推奨しています。

データベースで最後に実行計画を作った文の フォーマット済み実行計画を表示するlast_explainedビューの 定義をこの後に取り上げます。その時、実行のスコープは現在のスキーマやユーザのみに 限定されていない点に注意しましょう。以下のように簡単に使用できます。

SELECT * FROM last_explained

実行結果は以下のように表示されます。次の節で、 読み方の詳細について説明しています。

Explain Plan                                                                                        
----------------------------------------------
ID | Operation      |             Rows | Cost                                                       
 1 | RETURN         |                  |    0                                                       
 2 |  TBSCAN GENROW | 1 of 1 (100.00%) |    0                                                       
                                                                                                    
Predicate Information                                                                               
                                                                                                    
Explain plan by Markus Winand - NO WARRANTY                                                         
http://use-the-index-luke.com/s/last_explained 

警告

このビュー自体はまだまだ実験的な段階で、何の保証もありません。 ActualRowsも含むようにしたければ、Ember Crooks氏の拡張を使う事もできます。 具体的な修正方法については、彼女の ブログ記事を参照してください。

使用環境 : DB2 LUW 9.7 FixPack 4以上(Express-CはFixPack 2以降でしか使えません)

ヒント

このビューの定義はGitHub (直接 ダウンロード)にもあります。

-- Copyright (c) 2014-2015, Markus Winand - NO WARRANTY
-- Modifications by Ember Crooks - NO WARRANTY
-- Info & license: http://use-the-index-luke.com/s/last_explained
--
--#SET TERMINATOR ;

CREATE OR REPLACE VIEW last_explained AS
WITH tree(operator_ID, level, path, explain_time, cycle)
AS
(
SELECT 1 operator_id 
     , 0 level
     , CAST('001' AS VARCHAR(1000)) path
     , max(explain_time) explain_time
     , 0
  FROM SYSTOOLS.EXPLAIN_OPERATOR O
 WHERE O.EXPLAIN_REQUESTER = SESSION_USER

UNION ALL

SELECT s.source_id
     , level + 1
     , tree.path || '/' || LPAD(CAST(s.source_id AS VARCHAR(3)), 3, '0')  path
     , tree.explain_time
     , CASE WHEN (POSITION(path IN '%/' || LPAD(CAST(s.source_id AS VARCHAR(3)), 3, '0')  || '/%' USING OCTETS) >0)
            THEN 1
            ELSE 0
       END
  FROM tree
     , SYSTOOLS.EXPLAIN_STREAM S
 WHERE s.target_id    = tree.operator_id
   AND s.explain_time = tree.explain_time
   AND S.Object_Name IS NULL
   AND S.explain_requester = SESSION_USER
   AND tree.cycle = 0
   AND level < 100
)
SELECT * 
  FROM (
SELECT "Explain Plan"
  FROM (
SELECT CAST(   LPAD(id,        MAX(LENGTH(id))        OVER(), ' ')
            || ' | ' 
            || RPAD(operation, MAX(LENGTH(operation)) OVER(), ' ')
            || ' | ' 
            || LPAD(rows,      MAX(LENGTH(rows))      OVER(), ' ')
            || ' | ' 
            -- Don't show ActualRows columns if there are no actuals available at all 
            || CASE WHEN COUNT(ActualRows) OVER () > 1 -- the heading 'ActualRows' is always present, so "1" means no OTHER values
                    THEN LPAD(ActualRows, MAX(LENGTH(ActualRows)) OVER(), ' ') || ' | ' 
                    ELSE ''
               END
            || LPAD(cost,      MAX(LENGTH(cost))      OVER(), ' ')
         AS VARCHAR(100)) "Explain Plan"
     , path
  FROM (
SELECT 'ID' ID
     , 'Operation' Operation
     , 'Rows' Rows
     , 'ActualRows' ActualRows
     , 'Cost' Cost
     , '0' Path
  FROM SYSIBM.SYSDUMMY1
-- TODO: UNION ALL yields duplicate. where do they come from?
UNION
SELECT CAST(tree.operator_id as VARCHAR(254)) ID
     , CAST(LPAD(' ', tree.level, ' ')
       || CASE WHEN tree.cycle = 1
               THEN '(cycle) '
               ELSE ''
          END     
       || COALESCE (
             TRIM(O.Operator_Type)
          || COALESCE(' (' || argument || ')', '') 
          || ' '
          || COALESCE(S.Object_Name,'')
          , ''
          )
       AS VARCHAR(254)) AS OPERATION
     , COALESCE(CAST(rows AS VARCHAR(254)), '') Rows
     , CAST(ActualRows as VARCHAR(254)) ActualRows -- note: no coalesce
     , COALESCE(CAST(CAST(O.Total_Cost AS BIGINT) AS VARCHAR(254)), '') Cost
     , path
  FROM tree
  LEFT JOIN ( SELECT i.source_id
              , i.target_id
              , CAST(CAST(ROUND(o.stream_count) AS BIGINT) AS VARCHAR(12))
                || ' of '
                || CAST (total_rows AS VARCHAR(12))
                || CASE WHEN total_rows > 0
                         AND ROUND(o.stream_count) <= total_rows THEN
                   ' ('
                   || LPAD(CAST (ROUND(ROUND(o.stream_count)/total_rows*100,2)
                          AS NUMERIC(5,2)), 6, ' ')
                   || '%)'
                   ELSE ''
                   END rows
              , CASE WHEN act.actual_value is not null then
                CAST(CAST(ROUND(act.actual_value) AS BIGINT) AS VARCHAR(12))
                || ' of '
                || CAST (total_rows AS VARCHAR(12))
                || CASE WHEN total_rows > 0 THEN
                   ' ('
                   || LPAD(CAST (ROUND(ROUND(act.actual_value)/total_rows*100,2)
                          AS NUMERIC(5,2)), 6, ' ')
                   || '%)'
                   ELSE NULL
                   END END ActualRows
              , i.object_name
              , i.explain_time
         FROM (SELECT MAX(source_id) source_id
                    , target_id
                    , MIN(CAST(ROUND(stream_count,0) AS BIGINT)) total_rows
                    , CAST(LISTAGG(object_name) AS VARCHAR(50)) object_name
                    , explain_time
                 FROM SYSTOOLS.EXPLAIN_STREAM
                WHERE explain_time = (SELECT MAX(explain_time)
                                        FROM SYSTOOLS.EXPLAIN_OPERATOR
                                       WHERE EXPLAIN_REQUESTER = SESSION_USER
                                     )
                GROUP BY target_id, explain_time
              ) I
         LEFT JOIN SYSTOOLS.EXPLAIN_STREAM O
           ON (    I.target_id=o.source_id
               AND I.explain_time = o.explain_time
               AND O.EXPLAIN_REQUESTER = SESSION_USER
              )
         LEFT JOIN SYSTOOLS.EXPLAIN_ACTUALS act
           ON (    act.operator_id  = i.target_id
               AND act.explain_time = i.explain_time
               AND act.explain_requester = SESSION_USER
               AND act.ACTUAL_TYPE  like 'CARDINALITY%'
              )
       ) s
    ON (    s.target_id    = tree.operator_id
        AND s.explain_time = tree.explain_time
       )
  LEFT JOIN SYSTOOLS.EXPLAIN_OPERATOR O
    ON (    o.operator_id  = tree.operator_id
        AND o.explain_time = tree.explain_time
        AND o.explain_requester = SESSION_USER
       ) 
  LEFT JOIN (SELECT LISTAGG (CASE argument_type
                             WHEN 'UNIQUE' THEN
                                  CASE WHEN argument_value = 'TRUE'
                                       THEN 'UNIQUE'
                                  ELSE NULL
                                  END
                             WHEN 'TRUNCSRT' THEN
                                  CASE WHEN argument_value = 'TRUE'
                                       THEN 'TOP-N'
                                  ELSE NULL
                                  END   
                             WHEN 'SCANDIR' THEN
                                  CASE WHEN argument_value != 'FORWARD'
                                       THEN argument_value
                                  ELSE NULL
                                  END                     
                             ELSE argument_value     
                             END
                           , ' ') argument
                  , operator_id
                  , explain_time
               FROM SYSTOOLS.EXPLAIN_ARGUMENT EA
              WHERE argument_type IN ('AGGMODE'   -- GRPBY
                                     , 'UNIQUE', 'TRUNCSRT' -- SORT
                                     , 'SCANDIR' -- IXSCAN, TBSCAN
                                     , 'OUTERJN' -- JOINs
                                     )
                AND explain_requester = SESSION_USER
              GROUP BY explain_time, operator_id

            ) A
    ON (    a.operator_id  = tree.operator_id
        AND a.explain_time = tree.explain_time
       )
     ) O
UNION ALL
SELECT 'Explain plan (c) 2014-2015 by Markus Winand - NO WARRANTY - V20151017'
     , 'Z0' FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT 'Modifications by Ember Crooks - NO WARRANTY'
     , 'Z1' FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT 'http://use-the-index-luke.com/s/last_explained'
     , 'Z2' FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT '', 'A' FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT '', 'Y' FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT 'Predicate Information', 'AA' FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT CAST (LPAD(CASE WHEN operator_id = LAG  (operator_id)
                                          OVER (PARTITION BY operator_id
                                                    ORDER BY pred_order
                                               )
                       THEN ''
                       ELSE operator_id || ' - '
                  END
                , MAX(LENGTH(operator_id )+4) OVER()
                , ' ')
             || how_applied
             || ' ' 
             || predicate_text
          AS VARCHAR(100)) "Predicate Information"
     , 'P' || LPAD(id_order, 5, '0') || pred_order path
  FROM (SELECT CAST(operator_id AS VARCHAR(254)) operator_id
             , LPAD(trim(how_applied)
                  ,  MAX (LENGTH(TRIM(how_applied)))
                    OVER (PARTITION BY operator_id)
                  , ' '
               ) how_applied
               -- next: capped to length 80 to avoid
               -- SQL0445W  Value "..." has been truncated.  SQLSTATE=01004
               -- error when long literal values may appear (space padded!)
             , CAST(substr(predicate_text, 1, 80) AS VARCHAR(80)) predicate_text
             , CASE how_applied WHEN 'START' THEN '1'
                                WHEN 'STOP'  THEN '2'
                                WHEN 'SARG'  THEN '3'
                                ELSE '9'
               END pred_order
             , operator_id id_order
          FROM systools.explain_predicate p
         WHERE explain_time = (SELECT MAX(explain_time)
                                 FROM systools.explain_operator)
       )
)
ORDER BY path
);

改善予定

  • 処理IDは現在のところ、実行計画テーブルに保存されている 物理的な処理IDを表示しています。しかし、よりIDを見つけやすくなるように、 OracleのDBMS_XPLANのように行番号を表示した方が良いでしょう。

  • OracleのDBMS_PLANがそうであるように、述語情報の有無が 処理IDの横にアスタリスクで表示されると良いでしょう。

    フィルタ述語の有無を違う記号(例えば!)で表示できると なお良いでしょう。

    以下のようになるのが理想です。

    *

    STARTやSTOP述語が存在している

    >

    START述語はあるがSTOP述語はない

    <

    STOP述語はあるがSTART述語はない

    !

    SARG述語がある(もう1つ!).

  • 経験則による最初のN件のみを選択するクエリの表示。 TBSCANあるいはIXSCANの行数が減っているけれど述語情報がない時、 最初のN件のみを選択していると判断できます。このロジックは未完成です。

著者について

Markus Winandの写真

Markus Winand氏は、開発者がSQLパフォーマンスを改善するお手伝いをしています。彼は、SQL Performance Explainedの 著者でもあり、出張トレーニングhttp://winand.at/での リモート講義も 行っています。

彼の本

カバー『SQLパフォーマンス詳解』

核心をわかりやすく 解説。

Markusから購入します
(送料無料+PDF)

Amazonで購入
(印刷版のみ)

“Use The Index, Luke!” by Markus Winand is licensed under a Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License.
法律上の通知 | 接触 | 無保証 | 商標 | Privacy | CC-BY-NC-ND 3.0 license