$30 off During Our Annual Pro Sale. View Details »

アクセスプラン(実行計画)の読み方入門 (Club DB2)

アクセスプラン(実行計画)の読み方入門 (Club DB2)

2014/1/31 アクセスプラン(実行計画)の読み方入門 (Club DB2)
Club DB2 という、IBM DB2 の勉強会イベントで以前発表した資料です。かなり古いものなので情報が今とは異なる部分もあると思いますが、保存のためにアップロードしています。

Akira Shimosako

June 02, 2022
Tweet

More Decks by Akira Shimosako

Other Decks in Technology

Transcript

  1. アクセスプラン(実行計画)
    アクセスプラン(実行計画)
    アクセスプラン(実行計画)
    アクセスプラン(実行計画)の



    読み方入門
    読み方入門
    読み方入門
    読み方入門
    2014年1月31日
    下佐粉 昭 (しもさこ あきら)
    rev. 1.1
    CLUB DB2 第175回

    View Slide

  2. 2
    自己紹介
    下佐粉 昭 ( しもさこ あきら )
    和歌山県生まれ
    2001年 IBMに中途入社
    以来、DB2関連の仕事多し
    現在は金融系のお客様担当SE
    ■書籍
    「即戦力の
    「即戦力の
    「即戦力の
    「即戦力のDB2管理術」
    管理術」
    管理術」
    管理術」
    http://db2.jugem.cc/?eid=2341 (書籍紹介)



    「XML-DB開発
    開発
    開発
    開発 実技コース」(共著)
    実技コース」(共著)
    実技コース」(共著)
    実技コース」(共著)



    「DB2 逆引きリファレンス」(共著
    逆引きリファレンス」(共著
    逆引きリファレンス」(共著
    逆引きリファレンス」(共著)
    ■オンライン
    Twitter - @
    @
    @
    @simosako
    simosako
    simosako
    simosako
    http://twitter.com/simosako
    Unofficial DB2 Blog
    http://db2.jugem.cc/
    絶版になったので本の内容を全てWEB
    で公開しています
    http://db2watch.com/

    View Slide

  3. 3
    今日の内容
    アクセスプラン(実行計画)って聞いた事ありますよね?
    でもなんかすごく難しそう…
    どうやって、見ればよいのか分からない
    そもそも、なぜそんなものが必要なの?
    パフォーマンス問題の解決には、アクセスプランが必須
    用語さえ理解すれば、すぐ読めるようになります
    なぜ必要なのかという「理由」と
    なぜ必要なのかという「理由」と
    なぜ必要なのかという「理由」と
    なぜ必要なのかという「理由」と
    最低限必要となる
    最低限必要となる
    最低限必要となる
    最低限必要となる「


    「読み方
    読み方
    読み方
    読み方」


    」を理解しましょう
    を理解しましょう
    を理解しましょう
    を理解しましょう

    View Slide

  4. 4
    内容
    アクセスプラン(実行計画)とデータベース
    アクセスプランとは?
    なぜ必要なのか
    アクセスプラン決定に影響を与える要因
    表スキャンとインデックス・スキャン
    DB2のアクセスプラン:基礎
    統計情報とアクセスプラン
    DB2でアクセスプランを取得するには
    アクセスプランの基本的な読み方
    フィルター・ファクター
    ジョイン・メソッド
    この資料の記述は、DB2 10.5を対象にしています

    View Slide

  5. アクセスプラン(実行計画)と
    データベース

    View Slide

  6. 6
    そもそもアクセスプラン(実行計画)って?
    取り出したいデータは一つ、でも方法は色々ある
    行きたい場所は一つ、でも方法は色々ある…
    目的地
    一番早く着く
    のはどれ?
    (人が決める)

    View Slide

  7. 7
    ではデータベースの場合は?
    アクセスプラン(方法)は、データベースが決定する
    ユーザが決めない
    ユーザが決めるのは、欲しいデータ(SQL)だけ
    全ての情報を知っているのはデータベース
    ⇒最も速い方法を、自動的に決定してくれる
    具体的に理解するために、まず基本的なアクセスプランである
    表スキャン
    インデックス・スキャン
    の違いを理解しましょう

    View Slide

  8. 8
    表スキャン(テーブル・スキャン)
    表全体を順に読んでいって、必要なデータを発見する
    順に読んで、データを探す
    :
    :
    :
    :
    ANALYST
    HEATHER NICHOLLS
    12
    0012
    ANALYST
    DELORES QUINTANA
    11
    0011
    :
    :
    :
    :
    MANAGER
    SALLY KWAN
    3
    0003
    MANAGER
    MICHAEL THOMPSON
    2
    0002
    PRES
    CHRISTINE HAAS
    1
    0001
    JOB
    NAME
    ID
    レコードID
    例)SELECT * FROM MEMBER WHERE JOB='ANALYST'
    JOB列が'ANALYST'の
    行を読み出すSQL
    レコードIDは内部管理用
    (ユーザには見えない)

    View Slide

  9. 9
    ANALYST
    {0011}
    {0012}
    MANAGER
    {0002}
    {0003}
    {0004}
    PRES
    {0001}
    インデックス・スキャン(索引スキャン)
    インデックスを読んで⇒表からデータを取り出す
    インデックスは目次のようなもの:対象のレコードIDを記録している
    > CREATE INDEX IDX_JOB
    IDX_JOB
    IDX_JOB
    IDX_JOB ON MEMBER
    MEMBER
    MEMBER
    MEMBER(JOB)
    (JOB)
    (JOB)
    (JOB)
    先にインデックスを参照することで
    先にインデックスを参照することで
    先にインデックスを参照することで
    先にインデックスを参照することで必要なデータだけを読む
    必要なデータだけを読む
    必要なデータだけを読む
    必要なデータだけを読む
    インデックス
    インデックス
    インデックス
    インデックス IDX_JOB SELECT * FROM MEMBER WHERE JOB='ANALYST'
    :
    :
    :
    :
    ANALYST
    HEATHER NICHOLLS
    12
    0012
    ANALYST
    DELORES QUINTANA
    11
    0011
    :
    :
    :
    :
    MANAGER
    SALLY KWAN
    3
    0003
    MANAGER
    MICHAEL THOMPSON
    2
    0002
    PRES
    CHRISTINE HAAS
    1
    0001
    JOB
    NAME
    ID
    レコードID

    View Slide

  10. 10
    アクセスプランを人が決定するのは困難 (1/2)
    最適なアクセスプランを選択するには?
    平均処理時間
    取得したいデータ量 [%]
    表スキャン
    0 100

    View Slide

  11. 11
    アクセスプランを人が決定するのは困難 (2/2)
    最適なアクセスプランは常に変わる
    平均処理時間
    取得したいデータ量 [%]
    表スキャン
    0 100
    インデックス・スキャン
    ここで、インデックス・スキャンから表ス
    キャンに切り替わるのが理想

    View Slide

  12. 12
    アクセスプラン決定に影響を与える要素
    データサイズ
    アンサーセットの大きさ
    表の大きさ
    データには偏り
    偏り
    偏り
    偏りがあるかもしれない
    使用可能なインデックスの有無
    どれぐらい絞り込めるか?
    絞り込めるか?
    絞り込めるか?
    絞り込めるか?
    その他リソースの影響
    バッファー(メモリー)の大きさ
    CPU処理速度
    ディスク速度
    SELECT * FROM 社員表 WHERE 職位='一般社員'
    SELECT * FROM 社員表 WHERE 職位='部長'
    アンサーセット
    の量は同じ??

    View Slide

  13. 13
    ではなぜ、アクセスプランをユーザが意
    識する必要があるの?
    理由:データベースは、与えられた材料以上のことは
    出来ないので…
    SQLが遅い理由は?どこで遅いのか
    作成したインデックスが使われているか?
    バグ?
    ユーザが確認するためにアクセスプランを見る機能が
    必要
    ⇒EXPLAIN(エクスプレイン) 豆知識:
    豆知識:
    豆知識:
    豆知識:
    EXPLAINという単語は、
    という単語は、
    という単語は、
    という単語は、DB2独



    自の単語ではなく、
    自の単語ではなく、
    自の単語ではなく、
    自の単語ではなく、DBMSで広く
    で広く
    で広く
    で広く
    一般的に使われている用語です
    一般的に使われている用語です
    一般的に使われている用語です
    一般的に使われている用語です

    View Slide

  14. 14
    ここまでのまとめ
    アクセスプラン=データを「どの方法で取り出すか」
    アクセスプランを見る手段や結果=EXPLAIN
    最適なアクセスプランを選択するには…
    データの量
    データの偏り
    インデックスなどの有無
    ディスク配置
    使用メモリ量
    :
    非常に複雑
    多くの要因
    人間には無理なのでコンピュータに判断させるべき
    コンピュータの判断を確認するためにEXPLAINを使用

    View Slide

  15. 15
    <演習タイム>
    別紙の問題用紙を使ってグループ演習をしてみま
    しょう!
    数人でグループになりましょう
    1. 最初にそれぞれ自己紹介 (一人1分ぐらい)
    2. みんなで回答を考えてください
    • 問題1~5を解いてみて下さい。理由も考えて下さい!
    • 時間があまったグループは問題6~7にトライしてください
    WEB中継で参加の方もぜひ各自で演習をしてみてください
    問題用紙
    問題用紙
    問題用紙
    問題用紙は以下からダウンロード可能です
    http://j.mp/18VqmZv (CLUB DB2ホームページ)

    View Slide

  16. DB2のアクセスプラン:基礎

    View Slide

  17. 17
    アクセスプランと統計情報
    DB2は統計情報を元に最適なアクセス・プランを作成する
    統計情報が古いと、非効率的なアクセス・プランを作成してしまう
    統計情報の更新には、RUNSTATSコマンドを使用する
    ⇒RUNSTATSで統計情報を最新に保つことが重要
    データベース
    データベース
    データベース
    データベース
    SQL
    SQL
    SQL
    SQL
    システム・カタログ
    システム・カタログ
    システム・カタログ
    システム・カタログ
















    インデックス
    インデックス
    インデックス
    インデックス テーブル
    テーブル
    テーブル
    テーブル
    RUNSTAT
    RUNSTAT
    RUNSTAT
    RUNSTATS



    統計情報
    統計情報
    統計情報
    統計情報
    SQL実行
    SQL実行
    SQL実行
    SQL実行
    照会
    照会
    照会
    照会
    グラフ・
    グラフ・
    グラフ・
    グラフ・
    モデル
    モデル
    モデル
    モデル
    SQL
    SQL
    SQL
    SQLコンパイラー
    コンパイラー
    コンパイラー
    コンパイラー
    アクセス
    アクセス
    アクセス
    アクセス
    プラン
    プラン
    プラン
    プラン
    オプティマイザー
    オプティマイザー
    オプティマイザー
    オプティマイザー
    照会書き直し
    照会書き直し
    照会書き直し
    照会書き直し
    代替案の作成
    代替案の作成
    代替案の作成
    代替案の作成
    実行コスト見積もり
    実行コスト見積もり
    実行コスト見積もり
    実行コスト見積もり

    View Slide

  18. 18
    統計情報の自動更新
    自動RUNSTATS(V9.1以降)
    2時間に1度表の更新情報をチェックし、更新が必要となった表に
    RUNSTATSが実行される
    決められた「作業時間枠」の中で実行させる事も可能
    DB CFGのAUTO_RUNSTATS (デフォルトでON)
    上位パラメタのAUTO_TBL_MAINTとAUTO_MAINTもONである必要があります
    リアルタイム統計機能(V9.5以降)
    クエリーを実行した瞬間にその表にRUNSTATSが必要か不要かを判断
    必要であれば先にRUNSTATSを実行してからクエリーを実行する
    統計情報が常に最新の状態に保たれる
    リアルタイムで統計情報を取得するには時間がかかり過ぎる表に関しては、既存の情
    報から統計情報を擬似的に作成して実行
    DB CFGのAUTO_STMT_STATS (デフォルトでON)

    View Slide

  19. 19
    EXPLAIN機能の使い方
    1. EXPLAINモードを有効にする
    > SET CURRENT EXPLAIN MODE EXPLAIN
    2. SQLを実行すると、そのアクセスプランがEXPLAIN表に格納さ
    れる
    3. EXPLAINモードを元に戻す
    > SET CURRENT EXPLAIN MODE NO
    4. なんらかの方法でEXPLAIN表からデータを取得する
    事前準備:
    事前準備:
    事前準備:
    事前準備: 情報を格納するEXPLAIN表をDBに作成しておく必要がある
    方法1)
    方法1)
    方法1)
    方法1)sqllib/misc/EXPLAIN.DDLを実行して作成する
    > db2 -tvf .../sqllib/misc/EXPLAIN.DDL
    方法2)
    方法2)
    方法2)
    方法2) ストアドプロシージャを実行して作成する(DB2 9.5以降)
    > db2 "CALL SYSPROC.SYSINSTALLOBJECTS('EXPLAIN','C',NULL,CURRENT
    SCHEMA)"
    参考)SYSINSTALLOBJECTSプロシージャ
    http://pic.dhe.ibm.com/infocenter/db2luw/v10r5/topic/com.ibm.db2.luw.sql.rtn.doc/doc/r0011876.html

    View Slide

  20. 20
    DB2のEXPLAIN閲覧ユーティリティ
    db2exfmt
    もっとも
    もっとも
    もっとも
    もっとも詳細な情報
    詳細な情報
    詳細な情報
    詳細な情報が得られる
    これがお勧めです(次ページに詳細)
    db2expln
    事前準備無しですぐ使える
    例)
    >db2expln -d DB名 -t -g -q "SQL文"
    >db2expln -d DB名 -t -g -f ファイル
    DataStudio (GUI)内蔵のVisual Explain
    GUIで操作可能

    View Slide

  21. 21
    手順
    1. set current explain mode explain でEXPLAIN取得状態に
    2. 任意のSQLを流す
    SQLは実際には実行されずに、
    アクセスプラン情報がEXPLAIN表に書き出される
    3. set current explain mode no で元に戻す
    4. db2exfmtコマンドで、必要なEXPLAIN情報を取り出す
    db2 connect to sample
    db2 set current explain mode explain
    db2 "任意のSQL"
    db2 set current explain mode no
    db2exfmt -1 -d SAMPLE -o myexplain.txt
    EXPLAIN表へ
    表へ
    表へ
    表へアクセスプラン
    アクセスプラン
    アクセスプラン
    アクセスプランの書き出し
    の書き出し
    の書き出し
    の書き出し
    データーベース名
    標準的なオプションで直近(最新)
    のEXPLAIN結果を出力
    出力ファイル名
    db2exfmtでアクセスプランを取得する方法
    例)
    (参照)db2exfmtコマンド http://pic.dhe.ibm.com/infocenter/db2luw/v10r5/topic/com.ibm.db2.luw.admin.cmd.doc/doc/r0002353.html
    ※表や列名に日本語を使っている場合は、db2exfmtのオプションに-no_map_char が必要です

    View Slide

  22. 22
    アクセスプランの基
    本的な読み方
    ()内はオペレーション番号
    下から上へ
    下から上へ
    下から上へ
    下から上へ読む
    どのオブジェクト(表やインデッ
    クス)を使っているか
    演算子
    演算子
    演算子
    演算子の種類と、戻す行数
    戻す行数
    戻す行数
    戻す行数
    演算子のコスト
    コスト
    コスト
    コスト [timeron]
    時間ではない
    累積値
    例)SELECT * FROM MEMBER WHERE JOB IN
    ('MANAGER','OPERATOR','SALES')
    表スキャンで13,312行
    を戻している
    DB2INST1.MEMBER
    表にアクセス。この表は
    43,008行を保持
    (※「Q3」は自動的に付けら
    れた相関名)
    コスト:304.324

    View Slide

  23. 23
    Filter Factor(フィルター・ファクター)と見積り行数(1/3)
    右と左の違いは?
    WHERE句の演算子の違い
    Filter Factorの違い
    Filter Factor: 0.125
    Filter Factor: 0.125
    Filter Factor: 0.125
    Filter Factor: 0.125
    Predicate Text:
    --------------
    (Q1.JOB = 'PRES ')
    Filter Factor: 0.875
    Filter Factor: 0.875
    Filter Factor: 0.875
    Filter Factor: 0.875
    Predicate Text:
    --------------
    (Q1.JOB <> 'PRES ')
    SELECT * FROM MEMBER WHERE
    JOB<>'PRES'
    SELECT * FROM MEMBER WHERE
    JOB='PRES'

    View Slide

  24. 24
    Filter Factor: 0.875
    Filter Factor: 0.875
    Filter Factor: 0.875
    Filter Factor: 0.875
    Predicate Text:
    --------------
    (Q1.JOB <> 'PRES ')
    Filter Factor(フィルター・ファクター)と見積り行数(2/3)
    見積り行数は正しい?
    Filter Factorは0.875なので37,632行返すと
    DB2は考えている
    43008行 × 0.875 = 37,632行
    実際は何行?
    db2> SELECT COUNT(*) FROM MEMBER WHERE JOB<>'PRES'
    1
    -----------
    41984
    SELECT * FROM MEMBER
    WHERE JOB<>'PRES'

    View Slide

  25. 25
    Filter Factor(フィルター・ファクター)と見積り行数(3/3)
    アクセスプランはあくまで「予想」
    見積りと実際の間で「ずれ」がある可能性
    データは常に変動しているのである程度のずれは問題無い
    見積と実際が大きく異なる場合は統計情報を更新する
    統計情報の更新方法
    統計情報が古い場合
    データが大量に変化した後にRUNSTATSを実行していない
    場合、まずRUNSTATSを実行
    分散統計を利用
    RUNSTATS … WITH DISTRIBUTIONオプションで分散統
    計を取得 (※オプションについては補足資料を参照)
    分散(値のばらつき、偏り)も含めた統計情報を収集すること
    で精度を向上させる
    Filter Factor: 0.97619
    Filter Factor: 0.97619
    Filter Factor: 0.97619
    Filter Factor: 0.97619
    Predicate Text:
    --------------
    (Q1.JOB <> 'PRES ')
    RUNSTATS … WITH DISTRIBUTIONを指定して統
    計情報を更新した直後のアクセスプランの例。偏りが
    反映されて、より正確な見積りが実現できている

    View Slide

  26. 26
    演算子の種類(抜粋)
    TBSCAN:表スキャン(Relation Scan, Table Scan)
    テーブルを直接読み取る
    IXSCAN:インデックス・スキャン(Index Scan)
    最初にインデックスにアクセスする
    FETCH:フェッチ
    特定のレコードID (RID)を使って、表から値を取り
    出す
    RIDSCN:リッドスキャン
    レコードID(RID)の一覧を作成してI/Oサーバー
    (FETCH)に渡す
    SORT:ソート
    与えられたデータを昇順、降順に並べ替える
    重複をなくすためにも使用される
    +ジョインメソッド
    NLJOIN:ネステッド・ループ・ジョイン
    MSJOIN:マージ・スキャン・ジョイン
    HSJOIN:ハッシュ・ジョイン

    View Slide

  27. 27
    ジョイン・メソッド
    ジョイン(JOIN、結合)とは
    (極論すれば)2つの表にあるそれぞれの行で、同じ列の値を持
    つ組み合わせを全て見つける
    組み合わせを全て見つける
    組み合わせを全て見つける
    組み合わせを全て見つける作業
    3種のジョイン・メソッド
    NLJOIN:ネステッド・ループ・ジョイン
    MSJOIN:マージ・スキャン・ジョイン
    HSJOIN:ハッシュ・ジョイン
    ※この後のジョインの説明方法は、以下URLの「DB2 UDB アクセス・プラン速習」を連
    載していたプロフェッサー奏太郎先生からアイデアを教えてもらったものです。
    http://ibm.com/jp/software/data/developer/column/accessplan/

    View Slide

  28. 28
    NLJOIN:ネステッド・ループ・ジョイン
    [Q] 3枚のトランプが左にあり、2枚のトランプを右手にあって、右と左で
    番号が同じものを選ぶ時、どうしますか?
    ⇒左の3枚をそれぞれ総当たりで右と比べて、3×2 = 6回の比較
    2重のFORループのようなイメージ
    片方の表が小さい場合に向く
    小さいとオンメモリで処理可能
    少メモリ
    枚数が多いと遅くなる


    A
    A
    2
    2
    2
    2


    View Slide

  29. 29
    MSJOIN:マージ・スキャン・ジョイン
    [Q] 30枚のトランプが左に山積みで、20枚のトランプが右に山積みで、
    右と左で番号が同じものを選ぶ時、どうしますか?
    1. 左の30枚のトランプを、あらかじめ小さい順にソート
    2. 右の20枚のトランプも、あらかじめ小さい順にソート
    3. 比較して、同じか小さい値は捨てていく
    4
    3
    7 3
    2
    3
    4
    4
    比較(めくり)回数は左枚数+右枚数 = 50回
    ソートが必要だが、枚数が多い場合にも速い
    6
    7

    View Slide

  30. 30
    HSJOIN:ハッシュ・ジョイン
    [Q] 3000枚のはがき束と20枚のはがき束で郵便番号
    郵便番号
    郵便番号
    郵便番号7桁


    桁が一致するも
    のを選ぶならどうしますか?
    1. 郵便番号3桁で区分けする棚を用意する
    2. 先に20枚のはがきを、全て郵便番号3桁の棚へ分ける
    3. 次に3000枚のはがきの束を郵便番号3桁の棚へ振り分ける
    この時、先のはがきが入っていない棚には振り分けずに捨てる
    4. それぞれの棚の中で、つき合わせをする(NLJOINなど)
    ハッシュ計算が高速なら、非常に速度が出る
    CPU負荷は高く、メモリ使用量も大きめ
    1 2 7 9 9 9 9
    2 1 1 3 3 3 3
    2 1 1 1 1 1 1
    1 2 7 1 1 1 1
    1 2 7 1 1 1 1
    127の棚
    211の棚
    2 1 1 3 3 3 3
    2 1 1 1 1 1 1
    1 2 7 9 9 9 9
    1 2 7 1 1 1 1
    1 2 7 1 1 1 1

    View Slide

  31. 31
    まとめ
    アクセスプランは、パフォーマンス問題解決の肝
    詳しく読めなくても、採取できるだけで違う
    DB2の「意図」を理解できるようになる
    障害時の問題解析にも役立つ
    DB2固有の部分を理解しましょう
    RUNSTATSで統計情報を収集(最近は自動化)
    db2exfmtやVisual Explainでアクセスプランを確認
    NLJOIN : 小さいジョイン向け
    MSJOIN , HSJOIN : 大きいジョイン向け

    View Slide

  32. 32
    参考資料
    DB2 UDB アクセス・プラン速習(連載)
    日本一(世界一かも)詳しいDB2のアクセスプラン解説
    http://ibm.com/jp/software/data/developer/column/accessplan/
    カンタン!DB2テクテク第1歩 基本機能編
    EXPLAINやスナップショット・モニターの使い方がやさしく解説されています
    http://ibm.com/jp/software/data/developer/library/techdoc/kantandb2.html
    CLUB DB2 基礎編資料
    運用管理編(バックアップ、表の再編成、統計情報の更新)
    http://www.slideshare.net/simosako/db2-13132216
    簡単運用管理入門 (RUNSTATSの自動化と、オプションの設定)
    http://www.slideshare.net/simosako/club-db2-122-db2-11755316
    DB2 Express-Cの導入方法解説(無料のDB2で試しましょう!)
    http://www.ibm.com/developerworks/jp/offers/db2express-c/installwin_v10/
    (Windows用)
    http://www.ibm.com/developerworks/jp/offers/db2express-c/installlin_v10/
    (Linux用)

    View Slide

  33. 33
    参考資料(マニュアル)
    DB2のオンラインドキュメント:インフォメーションセンター
    常に更新されるので最新の情報が閲覧できます。検索機能付き
    DB2 10.5版
    http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp
    その他のバージョン、PDF版マニュアルなどは以下から
    http://www-01.ibm.com/support/docview.wss?uid=swg27009474
    IBMソフトウェア、ハードウェア総合のマニュアルサイト「Knowledge
    Center」 (公開ベータテスト中)
    http://www.ibm.com/support/knowledgecenter/

    View Slide

  34. 補足資料

    View Slide

  35. 35
    【補足】RUNSTATSコマンド(統計情報の更新)
    RUNSTATSコマンドで統計情報を更新する
    RUNSTATS実行中でも表に読み書きアクセス可能
    少し進んだ使い方
    ①分散統計で収集する
    ②サンプリングでRUNSTATSの実行時間を短くする
    RUNSTATS ON TABLE スキーマ名.表名
    RUNSTATS ON TABLE スキーマ名.表名 AND INDEXES ALL
    (※DB2 10.1からスキーマ名が省略可能になっています)
    多くの場合、この
    基本形でOK
    RUNSTATS ON TABLE スキーマ名.表名 WITH DISTRIBUTION
    RUNSTATS ON TABLE スキーマ名.表名 WITH DISTRIBUTION AND INDEXES ALL
    RUNSTATS ON TABLE SIM.DEPARTMENTS WITH DISTRIBTION TABLESAMPLE
    BERNOULLI (5)
    表を5%サンプリングする例(大
    規模環境用)
    データに「偏り」がある場合、分散統計
    を試してください。こちらもお勧め

    View Slide

  36. 36
    【補足】分散統計とインデックス
    表の分散統計 (WITH DISTRIBUTION)
    表データに「偏り」があり、その表がパフォーマンス上重要な場合はRUNSTATS … WITH
    DISTRIBUTIONで表データの偏り(分散状況)を含めた統計情報を取得することで、より精度
    の高いアクセスプランが作成可能になります
    巨大な表で分散統計を取得すると、実行時間が長くなりすぎる場合があります。その場合は
    TABLESAMPLEオプションでサンプリングを設定することで時間を調整可能です
    インデックスの分散統計
    RUNSTATSで"… AND DETAILED INDEXES ALL"を指定することで、インデックスの分散
    統計を取得できますが、インデックスの分散統計を取得することでアクセスプランが改善出来
    るケースは(表の分散統計と比較すると)少ない傾向にあります
    DB2 9.7までと10.1ではインデックスの分散統計の挙動が変更されています
    9.7まで: デフォルトでインデックス全体の分散情報を取得。SAMPLED DETAILEDと指定すると、サン
    プリングを実施
    10.1から:デフォルトでは、インデックスをサンプリングして部分的な分散統計を取得。UNSAMPLED
    DETAILEDと指定すると、サンプリングせずに全体の分散統計を取得
    インデックスの分散統計取得の例)
    RUNSTATS ON TABLE スキーマ名.表名 WITH DISTRIBUTION AND SAMPLED DETAILED
    INDEXES ALL

    View Slide

  37. 37
    【補足】EXPLAINの演算子(主要なもの)
    ※ XQuery/XPath関連の演算子は除いています
    EXPLAIN演算子の一覧はこちら
    http://pic.dhe.ibm.com/infocenter/db2luw/v10r5/topic/com.ibm.db2.luw.admin.explain.doc/doc/r0052023.html

    View Slide

  38. 38
    【補足】統計情報の移動
    「開発環境で、アクセスプランを確認しながらSQLを開発したい
    … しかし開発環境ではサイズが大きいDBを作成できない」
    ⇒統計情報が異なるため、同じアクセスプランにならない
    同じアクセスプランにならない
    同じアクセスプランにならない
    同じアクセスプランにならない
    表のデータ量が異なる
    バッファープールのサイズが異なる
    統計情報も単なる表なので、本番環境から統計情報のデータをコピーす
    ることで、同じアクセスプランを生成できます。ただしバッファープールの大
    きさがアクセスプランに大きな影響を与える場合があるため、それをごま
    かすためのdb2foptコマンドも用意されています
    db2look -m : 統計情報をコピーするDDLとINSERT文を生成
    db2fopt : バッファープールサイズを「ごまかす」ためのコマンド
    参照
    「db2look を使用してのオプティマイザーのアクセス・プランの再作成」
    http://ibm.com/developerworks/jp/data/library/techarticle/dm-0508kapoor/
    原文(英語)
    http://ibm.com/developerworks/db2/library/techarticle/dm-0508kapoor/

    View Slide

  39. 39
    【補足】 db2batchでSQLの実行時間を測定する
    ベンチマーク用ユーティリティー:db2batch
    ファイルに書かれたSQL(複数可)を実行する
    誤差の少ない測定が可能
    便利なオプション多数 (-o の後に指定)
    アンサーセットを出力しない(内部的にフェッチは行う) : r 0
    EXPLAIN表にアクセスプランを格納する : e 2
    SNAPSHOTを取得する: p 3
    db2batch -d sample -f file1.sql -o r 0 e 2 p 3 -r out1.txt
    データベース
    SQLファイル名 出力ファイル
    オプション
    例)上記オプションを全て指定した例
    SQLの実行時間を測定する場合、端末からの速度測定は誤差が大きい
    ネットワーク経由でデータが転送される時間
    端末画面に表示されるのに掛かる時間 などの誤差

    View Slide

  40. 40
    * タイム・スタンプ: Wed Jan 08 2014 20:17:58 JST
    ---------------------------------------------
    * SQL ステートメント番号 1:
    SELECT * FROM MEMBER WHERE JOB IN ('MANAGER','OPERATOR') ;
    ID NAME JOB SALARY
    ----------- -------------------- -------- -----------
    * 13312 行取り出され、0 行出力されました。
    * 経過時間: 0.037023 秒
    データベース・マネージャー・スナップショット
    DB2 インスタンス内のメンバー数 = 1
    データベース・マネージャー状況 = アクティブ
    製品名 = DB2 v10.5.0.2
    サービス・レベル = s131001 (IP23536)
    データベース名 = SAMPLE
    読み取り行数 = 43008
    書き込み行数 = 0
    削除行数 = 0
    更新行数 = 0
    挿入行数 = 0
    フェッチ行数 = 13312
    バッファー・プール・データ論理読み取り = 244
    バッファー・プール・データ物理読み取り = 1
    * 合計項目数: 1
    * 合計時間: 0.037023 秒
    * 最小時間: 0.037023 秒
    * 最大時間: 0.037023 秒
    * 算術平均時間: 0.037023 秒
    * 幾何平均時間: 0.037023 秒
    【補足】 db2batch 出力例(抜粋)
    db2batchを実行した日時
    を実行した日時
    を実行した日時
    を実行した日時
    スナップショット・モニターからの出
    スナップショット・モニターからの出
    スナップショット・モニターからの出
    スナップショット・モニターからの出
    力(左図は
    力(左図は
    力(左図は
    力(左図はごく
    ごく
    ごく
    ごく一部
    一部
    一部
    一部の


    の抜粋)
    抜粋)
    抜粋)
    抜粋)
    実行時間
    実行時間
    実行時間
    実行時間
    実行した
    実行した
    実行した
    実行したSQLステートメント
    ステートメント
    ステートメント
    ステートメント
    その他、SQL実行時の情報
    を多数得ることが出来ます

    View Slide