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以降でしか使えません)
-- Copyright (c) 2014-2017, 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
, POSITION('/' || LPAD(CAST(s.source_id AS VARCHAR(3)), 3, '0') || '/' IN path USING OCTETS)
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
VALUES ('Explain plan (c) 2014-2017 by Markus Winand - NO WARRANTY - V20171102','Z0')
, ('Modifications by Ember Crooks - NO WARRANTY','Z1')
, ('http://use-the-index-luke.com/s/last_explained','Z2')
, ('', 'A')
, ('', 'Y')
, ('Predicate Information', 'AA')
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件のみを選択していると判断できます。このロジックは未完成です。
