Comments
Description
Transcript
簡単シリーズ アクセス・プラン Explain 編 200４/03
簡単シリーズ アクセス・プラン Explain 編 200４/03 はじめに この資料は、Explain機能＆ツールを使用して、DB2 UDBのアクセス・プランを調査する方法を示す ことを目的としています。そのために、アクセス・プランを決定する役割を担うDB2オプティマイザー の概要も説明しています。 第1章 アクセス・プラン 1-1 アクセス・プランとは オプティマイザー概要 1-3 アクセス・プラン設計 1-2 第2章 Explain 2-1 Explain概要 Explainの仕組みとツール 2-3 Explainデータの収集方法 2-4 Explainツールの選択 2-2 第3章 Explainツールの使用方法 3-1 Visual Explain 3-2 db2exfmtツール 参考資料 参考-1 Explain表 参考-2 Visual Explainの例 参考-3 Visual Explainの演算子 参考-4 ホスト変数の使用 第１章 アクセス･プラン 1-1. アクセス・プランとは 本当はゆっくり優雅に 行きたいのだけど 1-1. 解説：アクセスプランとは ■ アクセス・プランとは？ DB2がSQL文で指定された条件を満たすレコードを取り出す場合には、どのインデックス経由で実際のデータにア クセスするか、あるいは、途中でマージ・ジョインが発生するかなど、さまざまな経路をたどる可能性があります。例 えば、ヨーロッパに旅行に行く方法としてダイレクト便で行くかどこかの都市でトランジットしていくか、あるいはどの 航空会社を使用するか（航空会社によってなぜか運賃は大きく異なる）、また経由地で友人と待ち合わせする (JOIN)かなどの選択肢があるように、DB2も非常に沢山の選択肢の中からひとつの行程を選びます。 基本的にDB2は、最短の時間で結果が得られる経路が、負荷も一番小さいと考えて、行程を選択します。時間とお 金がかかってもゆっくりと優雅に豪華客船で喜望峰をまわるなどの行程は通常は残念ながら選択できません。 今まで、汎用的にアクセス･プランとアクセス･パスを同じ意味で使用していた場合もありますが、ここで、アクセス･ パスとアクセス･プランという用語の定義を再確認しておきます。 アクセス･パスは、特定の行からデータを取り出すための方法を示します。アクセス･プランは、特定のSQLステート メントを実行するためのアクセス･パスの集まりで、各表をJOINする方法などが含まれていると考えてください。 例えば、旅行を想像してください。目的地までには、いくつかの場所を経由して行くと思います。それぞれの経由地 まで何を使用して行くかがアクセス･パスです。また経由地で、友人と待ち合わせをしてから、最終目的地まで行く ものとします。この最終目的地までの全行程(友人の行程を含め)を記したもの、これがアクセス･プランになると考 えていただければよいと思います。 1-1. アクセス・プランとは - アクセス・プラン決定のタイミング ■ 静的SQL 開発者が 静的SQL文を コーディングします 静的バインド (Application 開発の一部) アクセスプラン決定 ユーザーが ｱﾌﾟﾘｹｰｼｮﾝを 実行します データベース サーバー上で 照会が実行されます パッケージの 実行 BINDコマンド ■ 動的SQL ユーザーが 開発者が ｱﾌﾟﾘｹｰｼｮﾝを 動的SQL文を コーディングします 実行します PREPARE 文 動的準備とバインド アクセスプラン決定 DESCRIBE文 (オプション) データベース サーバー上で 照会が実行されます EXECUTE文 1-1. アクセス・プランとは - 解説：アクセス・プラン決定のタイミング ■ アクセス・プラン決定のタイミング アクセス・プランの決定のタイミングは、静的SQLか動的SQLかによって異なります。 アクセス･プランというのは、照会するために表などにアクセスするための方法を示すものです。使用するアクセ ス･プランを決定する機能を、オプティマイザーと呼びます。 静的SQL文は、COBOLやCのソース中に埋め込まれたSQL文です。プログラムのコンパイル後のバインド時に、 SQLコンパイラーが呼び出され、実行可能アクセス･プランが生成された時点でアクセス・プランが決定されます。 アクセス・プランには、索引の使用法、ソート方式、ロックの目的、および結合方法を含めたデータ･アクセスの方 針が入っています。実行可能形式のSQL文は、BINDコマンドの実行時にシステム･カタログ表に保管されます(据 え置きバインド方式を想定した場合)。 動的SQL文は、JAVAのJDBC接続で発行する場合や、BI系のツールなどで発行するSQL文がこれにあたります。 動的SQL文では、事前にSQLを含んだソースをバインドする必要がなく、実行時にバインドが実施され、その時点 でアクセス・プランが決定されます。 アクセス・プランを決定するために、DB2がDB2カタログを参照し、DB2カタログに登録されている情報が、表やイン デックスにあるレコード件数やデータの並びを捉えてアクセスプランを決定する点は、静的SQLと同じです。 動的SQL文のアクセス･プランは、システム･カタログには保管されません。これらは一時的にメモリー (グローバ ル・パッケージ・キャッシュと呼ばれます)に保管されます。動的SQL文のアクセス･プランがすでにパッケージ･ キャッシュ内に存在する場合には、コンパイラーは呼び出されません。 1-2. オプティマイザー概要 オプティマイザーの目的 SQLステートメントの照会の実行のために「最良」アクセスパスを選択 最良アクセスパスの決定方法 DB2カタログ統計情報に加えて、システムリソース（CPU、ディスク、メモリーと通信速度）も考慮 オプティマイザーは、リソースの使用率が最小となるようなアクセスパスを探索 DB2 カタログ表 照会書き換え SQL オプティマイザーの機能 統計情報 照会書き換え機能（冗長性排除、プッシュダウン) カーディナリティーの正確な予測 ■ プリフェッチの関する正確な判断 ■ ジョインの選択 ● ネステッド･ループ･ジョイン ● マージ･ジョイン ● ハッシュ･ジョイン ■ アウター表、インナー表の決定 ■ 非分散統計情報 ■ コスト予測 RUNSTATＳ → → → → 索引 表 データベース ＳＱＬ実行 アクセスプラン決定 ■ DB2 UDBのオプティマイザーは実績豊富なコスト・ベース・オプティマイザー 20年以上の実績, 常に最新のｵﾌﾟﾃｨﾏｲｽﾞ機能の提供 DB2自体がｵﾌﾟﾃｨﾏｲｽﾞを行い, ﾕｰｻﾞｰ調整は基本的に不要 ﾊﾟﾗﾚﾙ環境, MQTの有無もｺｽﾄ計算に含み最適ｱｸｾｽ・ﾊﾟｽを選択 1-2. 解説：オプティマイザー概要 オプティマイザー アクセス・プランを決定するのがオプティマイザーの役割です。オプティマイザーは、DB2カタログ表を参照し、さら にシステム・リソース（CPU、ディスク、メモリーと通信速度）も考慮して、リソースの使用率が最小となるようなアク セス・プランを決定します。DB2カタログ表には、表やインデックスにストアされているレコード件数やデータの並び などの情報がストアされています。DB2カタログ表を更新するには、RUNSTATSユーティリティーを実行してDB2カ タログ表にストアされている統計情報を更新する必要があります。 少し詳しく！ オプティマイザーには、SQLステートメントをコンパイルし最適化するときに、最も効率的なアクセス・プランの選択 方法を決定する最適化クラスを指定できます。最適化クラスは、0，1，2，3，5，7，9と7段階に分かれており、数字 の大きい方がより複雑なテクニックをオプティマイザーが使用します。当然オプティマイザーの最適化クラスが複 雑になるほど、アクセス・プラン選択のための負荷が大きくなりますので、OLTPで使用する動的SQL文などでは不 必要に複雑な最適化クラスを選択すべきではありません。 最適化クラス 使用するおもな技法、選択の指針 0 非常に単純な動的 SQL ステートメントだけで構成されるアプリケーションなどでのみ選択 1 マージ・スキャン結合等が使用可能。実行時間が 1 秒未満の非常に短い照会などで選択 2 使用可能な統計すべてを使用。特定の照会が繰り返されることが少ない非常に複雑な照会を行う場合などで選択 3 より多くの代替プランを考慮する動的プログラミング結合列挙アルゴリズムを使用。 4 つ以上の結合を含む照会のアクセス・プランを改善 5（デフォルト） 照会のマテリアライズ照会表への経路指定を含めて、すべての照会書き直し規則を適用。 トランザクションと複合的な照会の両方で構成される混合環境に適している 7 複合動的 SQL 照会の照会最適化の量を縮小しない。30 秒以上かかる実行時間の長い照会などで選択 9 すべての最適化技法を使用。照会に対する特別な最適化要件がある場合にのみ選択 1-3. アクセス・プラン設計 DB2 カタログ表 照会書き換え 統計情報 コスト予測 RUNSTATＳ → → → → アクセスプラン決定 ＳＱＬ実行 索引 表 データベース アクセスプラン設計のための３大要素 １．SQL文 ２．インデックス ３．統計情報 SQL 1-3. 解説：アクセス・プラン設計 アクセス・プラン設計のための３大要素 １．SQL文 アクセス・プラン設計のために先ず最初に注意すべき点は、SQL文のコーディングです。効果的なSQL文を コーディングするには、定義されているインデックスとの関係を考慮する必要があります。SQL文で指定される 述部(WHERE文節)は、データの絞り込みを行う条件を付ける場合などに記述されます。ここで、絞り込みを効 果的に行うにはインデックスを使用する必要があります。インデックスで定義された列とSQL文の述部で定義 した列の一致度が高ければそれだけ絞り込みが可能となります。（述部での指定順序は影響しません。DB2 は最初に照会書き換えを行い、適切な述部の並びに書き換えを行います。） ２．インデックス それぞれのSQL文に最適なインデックスを定義できれば良いパフォーマンスが期待できますが、実際には、あ る程度限定したインデックスの個数で、必要なパフォーマンス要件を満たすようにインデックスをデザインする 必要があります。また動的SQLの場合では、実際にSQL文が発行されるまで、SQL文自体が不明のため、あ る程度の想定のもとにインデックスを定義しておく必要があります。 定義するインデックスの数は、参照のみのSQL文であれば、論理的にはいくら多くても良いわけです。しかし、 実際には、SQL文自体は参照のみであっても、データのロードやインポートなどのデータ準備が必ず発生する ため、現実にはインデックスの数を制限する必要があります。一般的に情報系システムやマスター表のような 参照のみの表の場合でも、インデックスの数は、６−７個以内を目安とする場合が多いです。また、更新が発 生するOLTP(オンライン処理）系で使用するのであれば、表への更新と同時にインデックスの更新に伴う負荷 を考慮する必要があるため、２−３個以内に抑えることが推奨されます。 1-3. 解説：アクセス・プラン設計 アクセス・プラン設計のための３大要素（続き） ３．統計情報 オプティマイザーの解説のところでも述べましたが、DB2はアクセス・プランを決定するためにDB2カタログ表上 の統計情報を使用します。統計情報は、RUNSTATSユーティリティーを実行することにより値が更新されます。 すなわち、レコード数が単調増加する表で、実際はレコード件数が１００万件であっても、レコード件数１００件の 時点でRUNSTATSユーティリティーを実行したのであれば、DB2はアクセス・プラン選択のためのレコード件数と して１００件と理解してアクセス・プランを決定します。１００件のレコード件数であればリレーショナル・スキャン（イ ンデックスなしのテーブルの全件読み込み）がアクセス・プランとして最適と判断してしまう可能性もあります。し かし、現実に１００万件のレコードが存在しているのであれば、DB2は１００万件のテーブルに対して全件読み込 みを行うことになってしまいます。最適なインデックスをデザインしても、統計情報が不正確であれば、最適なア クセス・プランが選択されない場合があるというわけです。RUNSTATSユーティリティーが一度も実行されていな い表やインデックスは、DB2カタログ表のそれぞれの列に’-1’が入っており、DB2は独自のロジックで統計値を推 測します。 1-3. アクセス・プラン設計の実際 アクセスプラン上 問題の可能性有の場合 ･ ＳＱＬ コーディング E X P L A I N 統合 システムテスト 単体テスト ＤＢインデックス 設計 Ｄ Ｂ 論 理 設 計 Ｄ Ｂ テ ｜ ブ ル 設 計 EXPLAIN モ ニ タ ｜ アクセスプラン上 問題の可能性有の場合 EXPLAIN 1-3. 解説：アクセス・プラン設計の実際 アクセス・プラン設計において、DB2が実際にどのような行程でデータにたどり着いているかを判断する方法が、 Explainです。システム開発プロジェクトにおいては、パフォーマンス要件を満たすためのチューニングとして大きく 業務アプリケーションのチューニング システム・サイドのチューニング 以上の２つに分けることができます。 Explainを使用したアクセス・プランチューニングは、業務アプリケーションのチューニングに必須です。 大規模なプロジェクトでは、プログラマーが間違って非効率なSQL文を発行しないように、DBA(データベース・アドミ ニストレーター）が予め使用するSQL文をチェックする体制を取ります。単体テストに合格するには、DBAによりお墨 付きをもらう必要があるわけです。DBAは、SQL文を机上でチェックしますが、JOINなども含まない簡単なSQLならば、 熟練したDBAであればその場でDB2の心を見透かしDB2がどのようなアクセス・プランをとるかの大体の判断がつく ものです。しかし、複雑なSQL文で、そのSQL文が扱う表のレコード件数が膨大であれば、DBAは、そのSQL文を DB2がどのように解釈するかを確認するために、Explainを採取してアクセス・プランを確認します。しかし、単体テス ト時には、テスト・データが十分にあるわけではありません。本番で１，０００万件のレコードを有する表のアクセス・プ ランを確認するために１００件のレコードの表を使用していては、正しいアクセス・プランであるかどうかを確認するこ とはできません。 1-3. 解説：アクセス・プラン設計の実際 そのような場合には、DBAはDB2の統計情報をSQL文で更新することにより、見かけ上１，０００万件のデータが入っ ているようDB2を騙してアクセス・プランの確認を行ったりします。 この段階で不合格になったSQL文は、再度SQL文をチェックし、場合によってはインデックス・デザイン（特定のイン デックスの列順序の入れ替え、降順、昇順の入れ替え、あるいは、列の追加など）の設計をやり直します。 単体テスト時のアクセス・プラン検討は、チェック体制を作ることも含め、非常に面倒な作業ですが、ここを通過でき れば、統合テストやシステムテストなどの、サービスイン目前に、パフォーマンス トラブルに悩まされることは少なく なります。 単体テストを通過したSQL文は、次に統合テストやシステムテストで、本番データを使用したテストを通過する必要が あります。ここでは、モニタリング等を行い、レスポンスに問題があるSQL文を中心に再チェックすることになります。 当然、本番データのロード後にRUNSTATSを実行してDB2統計情報を更新し、システム･パラメーターもサービス･イ ン後のものを設定した状態でテストを実施します。 問題のあるSQL文がある場合は、再度Explainを採取してアクセス・プランを確認します。運悪く、SQL文の見直しで は済まず、インデックス デザインの変更が必要になった場合には、少しばかり多くのワークロードが必要になります。 該当インデックスは、問題のあったSQL文だけでなく、他のSQL文も使用しているわけですから、インデックス デザ インの変更でSQL文がそのインデックスを使用しなくなる可能性もあるわけです。また新規のインデックスの追加は、 更新や入力のSQLに若干の影響を及ぼします。 1-3. 解説：アクセス・プラン設計の実際 少し詳しく！ アクセス・プランの肝は、SQL文のインデックスの使用方法です。DB2がインデックスを使用するか否かは、述部 が索引を使用するかどうかによります。 昔はSARGable(Search ARGumentable)（検索引き数）という文学的な造語で呼ばれていたステージ１述部が、イ ンデックス使用可能な述部にあたります。 ステージ２述部（non-SARGable述部）では、インデックスは使用できず、ひとつひとつの処理は重たいが、DB2の 頭脳の中心とも呼べるRDS(リレーショナル・データ・マネージャー）が直接処理をします。 例外的な場合を除き、経験豊かなDBAは、WHERE述部には、なるべくステージ１述部を使用するようにプログラ マーに指示を出します。 以下が典型的なステージ２述部の例です。 ➨ データ・タイプ変換 ➨ 算術演算（Ｃ１＝Ｃ２＋１０） など 第２章 Explain(エクスプレイン) 2-1. Explain(エクスプレイン）概要 Explainの概念 アクセス・プランを適切に設計するためには、実行したSQL文のアクセス・プランを解析できる必 要があります。その解析に必要なツールがExplainです。主にSELECT部分のアクセス・パスの観 察に使用します。 アクセス・プランの分析 使用した索引は何か 索引専用アクセス(Index-only Access)の使用か否か ページの読み取り時にプリフェッチを使用しているか 結合方法の理解 – – 結合方法 表の結合順序 ソートのオカレンスとタイプ アクセス・コストの表示 CPU ページ 2-1. Explain概要 - 実行方法 Explain情報を収集したいSQL 文の決定 Explainステートメントの作成 CONNECT TO SAMPLE; EXPLAIN ALL WITH SNAPSHOT FOR SELECT A.EMPNO ,A.FIRSTNME ,A.LASTNAME ,A.JOB ,A.SALARY FROM EMPLOYEE A INNER JOIN EMPLOYEE B ON A.EDLEVEL = B.EDLEVEL WHERE A.SALARY >1000.00 ORDER BY A.SALARY ; をファイル(SEL1.TXT)で作成してDB2コマンドウィンドウより実行 C:¥TEST>DB2 -tvf SEL1.TXT Explain機能 Explain表への書き出し Explainツール Visual Explainの実行 db2exfmtコマンドの実行 db2exfmt -d sample -s % -e db2admin -n % -w -1 -# 0 -o exp1.txt ; 2-1. 解説：Explain概要 - 実行方法 ここではEXPLAIN機能＆ツールでデータを収集する方法の例を示します。 JDBCなどの動的SQL文で予めアクセス･プランの検討を行う場合や、パフォーマンスに問題のある SQL文が見つかった場合に、アクセス･プランを解析するには、下記の方法が便利です。 Explain表の作成 Explain表は、Explain機能が活動化された時点のアクセス・プランをキャプチャーします。Explain表は基本的にあらかじめ用意する ことを推奨します。 sqllib/misc にあるEXPLAIN.DDLを情報収集するユーザーで実行することにより用意できます。また、Explain表 はVisual Explainを実行すると、自動的に作成されます。詳細は参考資料をご参照ください。 対象のSQL文の抜き出しとExplainステートメントの作成 対象のSQL文を抜き出し、SQL文にExpalinステートメントを追加したファイル（SEL1.TXT)を作成します。ここでは TESTDBに接続し たあと、”EXPLAIN ALL WITH SNAPSHOT FOR”というEXPLAINステートメントを対象のSQL文の前に追加しています。 CONNECT TO TESTDB ; EXPLAIN ALL WITH SNAPSHOT FOR SELECT * FROM NAMETB ; 下線がEXPLAINステートメント ■EXPLAINステートメントの実行 EXPLAINステートメントを実行することにより、Explain機能を呼び出して、Explain情報を収集してExplain表にデータを書き出します。 ここでは、DB2コマンド・ウィンドウよりDB2コマンド行プロセッサーで実行しています。 DB2 -tvf SEL1.TXT (-tvfはコマンド行プロセッサーのオプションです。） ■ExplainデータのExplain表からのフォーマット（db2exfmtコマンドにてキャラクター・ベースの出力） db2exfmtコマンド文をコマンド ウィンドウより実行してExplain表のデータをフォーマットします。 db2exfmt -d TESTDB -s % -e db2admin -n % -w -1 -# 0 -o exp1.txt ; シェル・コマンド（！）を使用してファイル（DB2EXFMT.TXT)で作成して db2 -tvf DB2EXFMT.txtと実行することもできます。 ■ExplainデータのVisual Explainによるグラフ表示フォーマット DB2 UDBコントロール･センターより実行 2-2. Explainの仕組みとツール SQL照会 照会の解析 意味の検査 SQL Explain機能 照会 グラフ・モデル (QGM) 照会のRewrite アクセス･プランの 最適化 アクセス･ プラン 実行可能コードの 生成 Explain 表 Visual Explain 実行プラン db2exfmt 実行可能 プラン db2expln 2-2. 解説：Explainの仕組みとツール ■ SQL Explain機能 SQL Explain機能はSQLコンパイラーの一部で、この機能を使用すると、静的または動的SQLステートメント のアクセス・プランの情報をExplain表に収集することができます。 Explain表に収集された情報を使用して、SQLステートメントの構造や、実行時のパフォーマンスを理解する ことができます。 照会を処理するオペレーションの順序 コスト情報 – 述部(WHERE節)による絞込みの見積もり – Explain実行時にSQLステートメントで参照されている全オブジェクトに関する統計 – – これらの情報を使用して下記のような解析ができます。 照会用で選択されたアクセス・プランを理解するために使用 アプリケーション・プログラムの設計サポート – データベース設計をサポート – – Explain出力はリレーショナル表に保管され、オプションとして、Visual Explain ツールを使用して表示できる 形式でも保管されます。 2-2. Explainの仕組みとツール – Explainツール Explainツール DB2は、Explain情報を元にアクセス・プランを解析するための、Explainツールを複数提供していま す。基本的には、Explain表に書き出されたExplain情報やExplain SNAPSHOT情報を、ビジュア ルかキャラクター・ベースでフォーマットするためのツールですが、当然Explain表をそのまま照 会することも可能です。また、Explain表を使用しない代替方法も提供しています。 Explain情報のExplain表への書き出し SQL Explain機能 – Explainステートメント – Explain特殊レジスター – Explain BIND オプション Explain情報のExplain表からのフォーマット Visual Explain db2exfmtツール パッケージ内の情報の一部からのリポート作成 db2explnツール dynexplnツール 2-2. 解説：Explainの仕組みとツール – Explainツール ■SQL Explain機能 Explain情報のExplain表への書き出し方法は、2-3で説明します。 ■Visual Explain ＆ db2exfmtツール アクセス・プランを徹底的に分析するために使用できるオプティマイザーの詳細情報は、Explain表に保持されます。 Explain表から情報を入手するためには、以下の方法があります。 Visual – Explain GUI上でExplainスナップショット情報を表示します トロール・センターから呼び出します。 db2exfmt – db2exfmtコマンドを使用すれば、Explain表の情報を解釈しやすい書式(テキスト形式)で表示することができます。 db2expln & dynepln ツール 一方、Explain表を使用しないExplainツールには以下のものがあります。 db2expln db2expln ツールは、 SQL ステートメント用に選択されたアクセス・プランを示します。これを使用して、 Explain データがキャプ チャーされなかったときに選択されたアクセス・プランに関する比較的コンパクトなリポートを提供します。 – 静的SQLステートメントのアクセス・プランに関する情報は、パッケージの一部として生成され、システム・カタログに保管されます。 静的 SQL では、 db2expln を使用してシステム・カタログ表に保管されたパッケージを調べます。 – 動的 SQL では、 db2expln を使用して SQL キャッシュ内のセクションを調べます。 – オプティマイザー情報に関しては表示しません。 – – dynamic-options dynamic-optionsを使用すれば、パラメーター・マーカーなど動的 SQL だけで使用できる機能も使用可能になります。 dynexpln – パラメーター・マーカーが入っていない動的SQLステートメントに対してExplainを実行します。 – 後方互換性のために使用可能です。 2-2. 解説：Explainの仕組みとツール – SQLコンパイラー 少し詳しく！ SQLコンパイラー概要 SQLコンパイラーは、いくつかのステップを行って実行可能なアクセス・プランを作成します。 図中の照会グラフ・ モデル(QGM)コンパイル処理全体を通じて照会を表示するために使用されるメモリー内の内部的データ構造で す。 照会の解析 – SQL文を解析して、その構文の妥当性検査を行います。エラーが検出されなかった場合は、初期QGMが作成されます。 エラーが検出されると、処理は停止され、該当するSQLエラーが呼び出しアプリケーションに戻されます。 意味の検査 – 参照されたオブジェクトが存在するか検査し、制約があればそれを識別します。制約には、参照の整合性(RI)、表検査制 約、トリガー、視点が含まれます。これらの制約を含むようにQGMが変更されます。 照会のRewrite – 照会をおり最適化しやすい形に変形し、照会のパフォーマンスを改善します。照会に対して行われる変形は、QGMに書き 戻されます。 2-2. 解説：Explainの仕組みとツール – SQLコンパイラー アクセス・プランの最適化 – QGMを入力として使用しユーザー要求を満たす多数の代替実行プランを生成します。オプティマイザーが、表、索引、列、 関数の統計および表スペースI/O特性に関しての情報を使用して、それぞれの代替プランの実行コストを見積り、最小見 積りコストのプランを選択します。この処理の出力を「アクセス・プラン」と呼びます。 実行可能コードの生成 – SQL文の実行可能アクセス・プランを作成します。このアクセス・プランは、システム・カタログ表またはメモリーに保管され ます。 – 静的SQL文では、システム・カタログ表に保管されます。実行可能アクセス・プランはパッケージの一部です。パッケージが 実行されると、データベース・マネージャーはシステム・カタログ表に保管されている情報を使用して、データのアクセス方 法を決め、照会結果を提供します。db2explnツールによって使用されるのは、この情報です。 動的SQL文はメモリーに保管されます。 2-3. Explainデータの収集方法 Explainデータを収集するには、下記の３つの方法があります。テストや障害判別の局面により使用方 法を検討します。 Explain ステートメント 単一の動的SQL文のExplain情報を収集したい場合。たとえば、JAVAなどで、動的SQL文を発行している場合で、問題のあ るSQL文が特定できている場合などに使用 実行するSQL文の前に、「EXPLAIN ALL WITH SNAPSHOT FOR 」などを追加して実行 Visual Explain使用の場合は、SNAPSHOTデータが必要。詳細は別ページで説明します。 例：EXPLAIN ALL WITH SNAPSHOT FOR SELECT * FROM EMPLOYEE Explain特殊レジスター 全ての動的SQLのExplain情報を収集したい場合。たとえば、単体テスト時など全てのExplainを収集してチェックしたい場合 などに使用 CUREENT EXPLAIN MODEとCUREENT EXPLAIN SNAPSHOTの２つの特殊レジスターがある Explainデータの収集 例： SET CUREENT EXPLAIN MODE YES – Explain SNAPSHOTの収集 例： SET CURRENT EXPLAIN SNAPSHOT YES – Explain BIND オプション C言語などの組み込みSQLを使用した静的SQL文のバインド時にExplain情報を収集したい場合。たとえば、単体、統合、シ ステム・テストのそれぞれの局面で、全てのパッケージのExplain情報を収集して保存しておきたい場合などに使用 EXPLAIN とEXPLSNAPの２つのExplain BINDオプションが設定可能 EXPLAINステートメントと同様、Visual Explain使用の場合は、EXPLSNAPにてSNAPSHOTデータを収集する必要がある 例：BIND xxxxx.BND EXPLSNAP ALL 2-3. 解説：Explainデータの収集方法 Explainデータを収集するには、下記３つの方法があります。テストや障害判別の局面により使用方法を 検討します。2-1の例のようにExplainステートメントによる方法が比較的多く使用されます。 ■ Explainステートメント ➨ 単一の動的SQL文のExplain情報を収集したい場合。JAVAなどで、動的SQL文を発行している場合で、問題 のあるSQL文が特定できている場合などに使用します。あるいは、単体テスト時などでのSQL文チェックで、 机上チェックだけではアクセス・プランの判別が難しい場合など、実際にSQL文を抜き出してExplainステートメ ントを追加してExplain情報を収集する場合などに便利です。 ➨ 使用方法は、実行するSQL文の前に、「EXPLAIN ALL WITH SNAPSHOT FOR 」などを追加して実行します。 ➨ Visual Explain使用の場合は、SNAPSHOTデータが必要です。詳細は別ページで説明します。 ■ Explain特殊レジスター ➨ 全ての動的SQLのExplain情報を収集したい場合。単体テスト時などに、全てのExplainを収集してチェックした い場合などに使用します。 ➨ CUREENT EXPLAIN MODEとCUREENT EXPLAIN SNAPSHOTの２つの特殊レジスターがあります。 –SET CUREENT EXPLAIN MODEは、EXPLAINデータだけを収集します –SET CUREENT EXPLAIN SNAPSHOTは、EXPLAIN SNAPSHOTデータだけを収集します。 ■ Explain BIND オプション ➨ C言語などの組み込みSQLを使用した静的SQL文のバインド時にExplain情報を収集したい場合。単体、統合、 システム・テストのそれぞれの局面で、全てのパッケージのExplain情報を収集して保存しておきたい場合など に使用します。 ➨ EXPLAIN とEXPLSNAPの２つのExplain BINDオプションが設定できます。 ➨ EXPLAINステートメントと同様に、Visual Explain使用の場合は、EXPLSNAPにてSNAPSHOTデータを収集す る必要があります。 2-3. Explainステートメントによるデータ収集 動的SQL文 Visual Explain表示可 WITH SNAPSHOT ・Explain表フル充てん ・スナップショット列充てん 動的SQL文 Visual Explain表示可 FOR SNAPSHOT ・Explain表部分充てん ・スナップショット列充てん 動的SQL文 Visual Explain表示不可 デフォルト ・Explain表部分充てん ・スナップショット列非充てん 2-3. 解説：Explainステートメントによるデータ収集 ここでは、比較的使用頻度の高いExplainステートメントによるデータ収集に関して説明します。Explain表 の一部の表には、SNAPSHOT情報が収集された場合にのみ充てんされる列があります。例えば、 EXPLAIN_STATEMENT表には、SNAPSHOT列(BLOB(10M))があり、SNAPSHOTが収集された場合にの み充てんされます。Visual Explainを使用するには、SNAPSHOT情報が必須です。SNAPSHOT情報が収 集されていなくても、通常のExplain情報が収集されていれば、db2exfmtツールによるキャラクター形式 のリポートをExplain表よりフォーマットすることは可能です。 ■WITH ■FOR SNAPSHOT Explain表にExplain情報とExplain SNAPSHOT情報を収集します。 – 例：EXPLAIN ALL WITH SNAPSHOT FOR SELECT * FROM NAMETB SANPSHOT Explain SNAPSHOT情報だけを収集します。EXPLAIN_INSTANCE表とEXPLAIN_STATEMENT表にあるもののみ充てんします。 – 例：EXPLAIN ALL FOR SNAPSHOT FOR SELECT * FROM NAMETB ■デフォルト Explain情報が指定されていない場合は、Explain情報だけを収集し、Explain SNAPSHOT情報は収集しません。 2-4. Explainツールの選択 Explainツールの選択 Explain表からの情報の入手方法 Visual Explain db2exfmtツール Explain表の照会を作成 Explain機能とともに使用できるツール（網掛けが良く使用するツール） ツール GUIインターフェース Visual Explain Explain表 db2exfmt db2expln dynexpln ○ テキスト出力 ○ ｢簡易｣静的SQLサポート ○ ○ ○ 静的SQLサポート ○ ○ ○ ○ 動的SQLサポート ○ ○ ○ ○ ○※ サポートされるCLIアプリケーション ○ ○ ○ ○ ○ DRDAアプリケーション・リクエスターで使用可能 詳細オプティマイザー情報 ○ ○ ○ ○ 複数ステートメントの分析に適合 ○ ○ アプリケーション内部からアクセス可能な情報 ○ ※:db2explnを間接的に使用します。制限がいくつかあります。 2-4. 解説：Explainツールの選択 Explainツールの選択 2-2でExplainツールの説明をしましたが、ここで再度よく使用するツールについてまとめておきましょ う。DB2は、Explain情報を元にアクセス・プランを解析するための、Explainツールを複数提供していま す。基本的には、Explain表に書き出されたExplain情報やExplain SNAPSHOT情報を、ビジュアルか キャラクター・ベースでフォーマットするためのツールですが、当然Explain表をそのまま照会すること も可能です。また、Explain表を使用しない代替方法も提供しています。 ■ アクセス・プランを分析するために使用できるオプティマイザーの詳細情報が、Explain表に収集 されます。Explain表からアクセス・プラン情報を見易いかたちで入手するには、以下の方法があ ります。 ➨ Visual Explain (Explainスナップショット情報を表示します) Visual Explainを使用すると、GUIを介して、アクセス・プランの分析やExplain表からのオプティマイザーの情報を分析 することができます。静的SQLと動的SQLのどちらも分析することができます。Visual Explainはコントロール・センター、 またはコマンド・センターから呼び出すことが可能です。 ➨ db2exfmt db2exfmtコマンドを使用すれば、Explain表の情報を解釈しやすい書式で表示することができます。ファイルとして出力 できますので、他の技術者に解析を依頼する場合に送付し易く便利です。 ➨ Explain表を照会 Explain表はサポートされるすべてのプラットフォームでアクセス可能であり、静的SQLと動的SQLの両方に関する情報 が含まれています。SQLステートメントを作成してExplain表にアクセスし、アウトプットを加工したり、別の照会と比較し たり、または同じ照会を異なる時間帯で比較したりすることができます。 第3章 Explainツールの使用方法 3-1. Visual Explain Explainツールの選択の節でもご紹介しましたが、アクセス・プランを解析するためのツールはいくつか あります。 その中で、ここではVisual Explainについて説明します。 Visual Explainの特長は以下のとお りです ■ 特長 ‒ 直感的にわかりやすいグラフィック・ユーザー・インター・フェースを使用し、操作性に優れる ‒ 豊富なヘルプで学習目的にも優れる ‒ 動的SQLおよび静的SQLの両方をサポート ‒ オプティマイザー情報の解析が可能 ■ 起動方法 ‒ コントロール・センターから起動します。起動はいくつかのパネルから可能ですが、ここでは最も一般的で覚えやすい方 法を紹介します。 コントロール・センターを立ちあげた後の最初の画面で、Visual Explainを使用したいデータベース・ア イコンを右クリックしてメニューを表示させます。 ここで’Explainされたステートメント履歴の表示’または’SQLのExplain...’ を選択します。 ■ Explainされた ステートメント履歴の表示 ‒ ここで開かれるウインドウではExplain済み(Explain情報の収集を行ったもの)のExplainインスタンスの一覧が表示されま す。一覧から選択していくことによってアクセス・プラン・グラフを表示させます。 ■ SQLのExplain... ‒ ここで開かれるウインドウではExplainしたいSQL文を直接入力することによってアクセス・プラン・グラフを表示させること ができます。あらかじめExplain情報を収集しておく必要はありません。 このウインドウで操作することによりExplain情報 が自動的に収集されるからです。 また、Explain表が定義されていない場合でも自動的に必要な表が定義されます。 こ のウインドウでは入力したSQL文のセーブやファイルからのSQL文の読み込みも可能で、環境を変更した後で同じSQL 文をExplainすることが容易にできます。 3-1. Visual Explain - 起動方法 3-1. Visual Explain - アクセス・プラン・グラフ 長方形 ダイヤモンド型 八角形 平行四辺 形 六角形 矢印 表 索引 演算子 TQUEUE演算子 表関数 データ・ストリーム 3-1. 解説：Visual Explain - アクセス・プラン・グラフ ここでは、アクセス・プラン・グラフ内の記号について説明します。 ■ アクセス・プラン・グラフは、アクセス・プランの構造をツリーで表示します。ツリーのノードは以下のものを表し ます。 ‒ 表は長方形で示されます ‒ 索引はダイヤモンド型で表示されます ‒ 演算子は八角形で表示されます。 ‒ TQUEUE 演算子は 平行四辺形で表示されます。 ‒ 表関数は六角形で表示されます。 ■ 演算子については、その演算子タイプの右側の括弧の中の数字が各ノードの固有のIDです。演算子タイプの 右にある数字は累積コスト(timeron)です。timeronは何らかの実際の経過時間に直接等しいわけではなく、オ プティマイザーがアクセス・プランを決定する指標として使用する、リソース（コスト）の相対的な概算です。 ■ アクセス・プラン・グラフではボトムアップ方向で結果が表示されます。 ■ 表示>設定で表示される設定パネルでは以下の項目について表示の設定が可能です。 ‒ ヘッダーでのID/合計コスト/ズーム・スライダーそれぞれの表示の有無 ‒ 背景色 ‒ ノードの2次元/3次元表示の選択 ‒ 演算子(Operator)ノードの情報表示(コストは累積値になります) 名前のみ 名前と合計コスト(timeron) 名前とCPUコスト(命令数による見積もり) 名前と入出力コスト(シークと転送ページ数による見積もり) 名前とカーディナリティー(カーディナリティー：見積もり行数) ‒ 演算子各種それぞれの色 ‒ 表/索引/表関数それぞれの色 3-1. Visual Explain - 演算子の内容 3-1. 解説：Visual Explain - 演算子の内容 各演算子により表示される情報は異なります。 ここでは各演算子に共通の大項目について説明します。 詳細な内容を表示するには、「すべて」ラジオ・ボタンを選択します。詳細のレベルを低くするには、「概 要」ラジオ・ボタンを選択します。 （詳細な説明は、参考資料に掲載しています） ■ 累積コスト 演算子に関連するさまざまなコストの見積もりを表示します。 ‒ 合計コスト(timeron) ‒ 4KB ページ入出力 ‒ CPU命令の数 ‒ 最初の行を取り出すためのコスト(timeron) ‒ 通信コスト(IPフレームの総数) - 区画間パラレルの場合に表示されます。 演算子によって表される処置が実行されたときに、結果としてもたらされる行のセットの中の最初の行 を作成するために必要な累積通信コストの見積もり（IPフレームの総数）。 - 区画間パラレルの場合に表示されます。 ‒ 最初の通信コスト。 ■ 累積プロパティー 演算子がアクセスする表や列の特性。 ―アクセスされた表のセット ―アクセスされた列のセット ―データが順番に並べられた列 ―適用された述部のセット(その 選択性の見積もりも含む） ―基数：戻される行数の見積もり(カーディナリティー) ―バッファー・プール内の見積もりページ数 ■ 入力引き数 演算子の動作に影響を与える引き数。 ‒ 詳細は、演算子のタイプ、および選択された詳細度のレベルによって異なります。引数をもたない演算子もあります。 算子詳細の画面よりヘルプを呼び出すことにより、入力引数の詳細を表示することができます。 演 3-1. Visual Explain - アクセス・プランの確認例（MQT) EMPLOYEE表 EMPNO FIRSTNME MIDINIT LASTNAME WORKDEPT PHONENO HIREDATE JOB EDLEVEL SEX BIRTHDATE SALARY BONUS COMM CREATE TABLE SUMEMP1 AS (SELECT WORKDEPT,COUNT(*) AS EMPLOYEESUM FROM EMPLOYEE GROUP BY WORKDEPT) DATA INITIALLY DEFERRED REFRESH immediate ENABLE QUERY OPTIMIZATION ; SELECT WORKDEPT,COUNT(*) FROM EMPLOYEE GROUP BY WORKDEPT; WORKDEPT 2 -------- ----------A00 3 B01 1 C01 3 D11 9 D21 6 E01 1 E11 5 E21 4 8 レコードが選択されました。 SUMEMP1表 WORKDEPT EMPLOYEESUM 3-1. 解説：Visual Explain - アクセス・プランの確認例（MQT) ここでは、アクセス・プラン・グラフの例としてMQT(マテリアライズ照会表)の利用例を示しています。 MQTとは、照会結果に基づいて定義される表です。そのデータ形式は、MQT定義の基礎となる 1 つま たは複数の表を対象にして、事前に計算された結果です。 ■EMPLOYEE表に対して、組織（WORKDEPT)毎の従業員人数をカウントして一覧するSQLを実行します。 SELECT WORKDEPT,COUNT(*) FROM EMPLOYEE GROUP BY WORKDEPT; ■MQTを定義しない場合のアクセス・プラン・グラフ 図を参照すると、EMPLOYEE表をスキャンしてからGROUP BYしていること がわかります。 3-1. 解説：Visual Explain - アクセス・プランの確認例（MQT) ■ MQTを定義した場合のアクセス・プラン・グラフ ‒ 図を参照すると、SUMEMP1表(MQT)のみをスキャンしているのがわかります ‒ 下記がMQTを作成するDDL文 CREATE TABLE SUMEMP1 AS (SELECT WORKDEPT,COUNT(*) MQT作成時にはデータは挿入されない AS EMPLOYEESUM CREATE後にRefresh コマンドを発行する必要がある FROM EMPLOYEE 例） REFRESH TABLE SUMEMP1 GROUP BY WORKDEPT) DATA INITIALLY DEFERRED REFRESH immediate MQTのデータが更新されるタイミング ENABLE QUERY OPTIMIZATION ここでは、元表が更新されたら直ぐに反映される オプティマイザーは、MQTをアクセス対象として選択可能とする。 DISABLE QUERY OPTIMIZATIONを指定すると、オプティマイザーはMQTを選択対象外とする。 （直接MQTを検索の対象として明示的にSQL文の中に指定した場合は使用可能） 3-1. Visual Explain - MQT作成方法 少し内容が外れますが、MQTの作成とテスト方法の解説です。 ■ MQTの作成 前ページ参考 ■ REFRESH TABLEでMQTへのデータの更新 例）REFRESH TABLE SUMEMP1 ■ MQTの統計情報の取得 RUNSTATS ON TABLE SUMEMP1 AND INDEXES ALL SHRLEVEL CHANGE ■ SQL実行 例）SELECT WORKDEPT,COUNT(*) FROM EMPLOYEE GROUP BY WORKDEPT; QUERY OPTIMIZATION LEVELを５以上で実行、３以下だとオプティマイザーはMQTを対象としません。 3-2. db2exfmtツール db2exfmt >>-db2exfmt--+------------+--+------------+--+-------+----------> '--d--dbname-' '--e--schema-' '--f--O-' >--+---------------+--+----+--+----------+--+------------+------> | .-------. | '--l-' '--n--name-' '--s--schema-' | V | | ‘---g---+---+-+-' +-O-+ +-T-+ +-I -+ '-C-' >--+-------------+--+----------------------+--------------------> +--o--outfile--+ '--u--userID---password-' | .--t-. | '-+----+---------------' >--+---------------+--+-------------+--+----+------------------>< '--w--timestamp-' '--#--sectnbr-' '--h- Explain表 db2exfmtコマンドのコマンド・ウィンドウからの実行 db2exfmt -d sample -s % -e db2admin -n % -w -1 -# 0 -o exp1.txt ; 3-2. 解説：db2exfmtツール Explainツールの選択の節でもご紹介しましたが、アクセス・プランを解析するためのツールはいくつか あります。ここではdb2exfmtツールについて説明します。 db2exfmtツール db2exfmtツールは、Explain表の内容をフォーマットします。 実行にはフォーマットするExplain表への読み取りのアクセス権が必要です。 ■ 使用上の注意 -h,-l以外のオプションでは、パラメーター値を指定しなかったり、指定が不完全だったりすると、プロンプト指示されます。 Explain表スキーマを指定しない場合、環境変数USERの値がデフォルト値として使用されます。この変数が見つからな い場合、Explain表スキーマの入力を要求するプロンプトが出されます。 ソース名、ソース・スキーマおよびExplainタイムスタンプは、LIKE述部書式で指定できます。一度の呼び出しで複数の ソースを選択するパターン照合文字として、％(パーセント記号)および＿(下線)を使用できます。最新のExplainステートメ ントの場合、Explainタイムに-1を指定します。 -oをファイル名なしで指定し、かつ-tを指定しなかった場合、ファイル名（デフォルト値はdb2exfmt.out）の入力を要求す るプロンプトが出されます。-oも-tも指定しなかった場合、ファイル名（デフォルト・オプション値は端末出力）の入力を要 求するプロンプトが出されます。-oと-tの両方を指定した場合、端末に直接出力されます。 3-2. 解説：db2exfmtツール db2exfmtツールのコマンド・パラメーター コマンド・パラメーター 説明 -d dbname パッケージを含むデータベースの名前｡ -e schema Explain表のスキーマ｡ -f 形式設定フラグ。このリリースでは、O (オペレーターの要約) だけがサポートされています。 -g グラフのプラン。 -g だけを指定すると、グラフの後に、すべての表に関する形式化された情報が生成されます。他にも指定す る場合は、以下の有効値の組み合わせを指定できます。 O: グラフの表示 T:グラフにおけるTotalコストを表示 I: グラフにおけるI/Oコストの表示 C:グラフにおける処理件数を表示 -l パッケージ名の処理時に大文字小文字を考慮します。 -n name Explain 要求のソースの名前 (SOURCE_NAME)。 -s schema Explain 要求のソースのスキーマつまり修飾子 (SOURCE_SCHEMA)。 -o outfile 出力ファイル名｡ -t 出力の宛先を端末にします。 -u userid password データベースに接続するときは、与えられたユーザー ID とパスワードを使用してください。 ユーザー ID とパスワードはどちらも、命名規則に従った有効なもので、さらにデータベースが認識できるものでなければなりま せん。 -w timestamp タイム・スタンプを Explain します。最新の Explain 要求を取得するには、-1 を指定します。 -# sectnbr ソース中のセクション番号。すべてのセクションを要求するには、ゼロを指定します。 -h ヘルプ情報を表示します。このオプションを指定すると、他のオプションはすべて無視され、ヘルプ情報が表示されます。 参考資料：Explain表 参考資料 参考-1. Explain表 Explain表 アクセス･ アクセス･ プラン プラン ■ ■ ■ Explain 表 ■ ■ Visual Explain ■ db2exfmt ■ EXPLAIN_INSTANCE EXPLAIN_STATEMENT EXPLAIN_ARGUMENT EXPLAIN_OBJECT EXPLAIN_OPERATOR EXPLAIN_PREDICATE EXPLAIN_STREAM 参考-1. 解説：Explain表 Visual Explainやdb2exfmtを使用してアクセス・プラン情報を取り出すには、Explain機能にて、Explain情 報をExplain表に書き出したデータをソースとして使用します。 Explain表は、DB2UDBが管理する表（テーブル）で、事前に定められたフォーマットに則り定義して おく必要があります。定義用DDL文は、SQLLIBディレクトリーの下のMISCディレクトリのEXPLAIN.DDL にあります。（このDDLには、Indexアドバイザーで使用する表定義も含まれています） Explain表は下記の７表より構成されます。 ■ EXPLAIN_INSTANCE：主制御表。SQL文のソースに関する基本情報、および環境に関する情 報が入ります。 ■ EXPLAIN_STATEMENT：SQL文のテキストが２つの形式で入ります。１つは、ユーザーが入力 したそのままの形式。もう１つは、コンパイルプロセスの結果再作成 された形式です。 ■ EXPLAIN_ARGUMENT：アクセス・プラン中の個々の演算子(ｵﾍﾟﾚｰﾀ)の固有な特性が入ります。 （例：OUTER JOINがLeftかRightかなど） ■ EXPLAIN_OBJECT：表、インデックスなどのオブジェクト情報が入ります。 ■ EXPLAIN_OPERATOR：SQL文を満たすための演算子(ｵﾍﾟﾚｰﾀ)情報が入っています。 ■ EXPLAIN_PREDICATE：述部が入っています。 ■ EXPLAIN_STREAM：ﾃﾞｰﾀｵﾌﾞｼﾞｪｸﾄと演算子(ｵﾍﾟﾚｰﾀ)間の入出力情報が入っています。 参考-2. Visual Explainの例 今回は、次の設定でVisual Explainによるアクセス･ プラン取得を行ってみます。 ･今回使用するテーブルの定義は、右の図のように なっています。 ･今回実行するSQL文は以下のようになっています。 SELECT OL_W_ID, OL_D_ID, SUM(OL_QUANTITY*I_PRICE) FROM ORDER_LINE, ITEM WHERE OL_W_ID BETWEEN 1 AND 5 AND OL_I_ID=I_ID AND OL_I_ID BETWEEN 10 AND 100 GROUP BY OL_W_ID, OL_D_ID ORDER BY OL_W_ID, OL_D_ID ORDER_LINE表 OL_O_ID OL_D_ID OL_W_ID OL_NUMBER OL_I_ID OL_SUPPLY_W_ID OL_DELIVERY_D OL_QUANTITY OL_AMOUNT OL_DIST_INFO ITEM表 I_ID I_IM_ID I_NAME I_PRICE I_DATA 参考-2. Visual Explainの例 この例では、SQL全体のコストが、292,688.47timeronになっています。 では、その多くのコストがどこでかかっているかを見ますと、 ORDER_LINE表、ITEM表ともに表スキャンしていることがわかります。 この場合に考えられるアクセス・プランのチューニングとしては、 ･INDEXの作成 ･統計情報の取得がなされているか ･INDEXの定義の変更 が挙げられます。 参考-2. Visual Explainの例 インデックスを作成し、RUNSUTATSを実行すること で最新の統計情報を取得します。 ･ITEM表にインデックスの作成 CREATE INDEX IITEM ON ITEM(I_ID) CLUSTER PCTFREE 10 ･ORDER_LINE表にインデックスの作成 CREATE INDEX IORDER_LINE ON ORDER_LINE(OL_W_ID,OL_I_ID,OL_D_ID) PCTFREE 10 ･最新の統計情報を取得 RUNSTATS ON TABLE DB2ADMIN.ORDER_LINE WITH DISTRIBUTION AND INDEXES ALL ORDER_LINE表 OL_O_ID ☆３ OL_D_ID ☆１ OL_W_ID OL_NUMBER ☆２ OL_I_ID OL_SUPPLY_W_ID OL_DELIVERY_D OL_QUANTITY OL_AMOUNT OL_DIST_INFO ITEM表 ☆１ I_ID I_IM_ID I_NAME I_PRICE I_DATA RUNSTATS ON TABLE DB2ADMIN.ITEM WITH DISTRIBUTION AND INDEXES ALL ☆１：インデックス指定の列、数字は指定順序 参考-2. Visual Explainの例 インデックスを作成し、RUNSTATSを実行して最新の統計情報を 取得した結果、全体コストを70，553.3までチューニングすること ができました。 参考-3. Visual Explainの演算子 ■ 演算子一覧 演算子 説明 DELETE 表から行を削除します FETCH 特定のレコードIDを使って、表から列を取り出します FILTER 述部をデータに適用して、データをフィルターにかけます。 GRPBY 列または関数の値に従って行をグループ分けすること。GROUP BY。 HSJOIN ハッシュ・ジョイン INSERT 表に行を挿入します IXAND 複数の索引の走査から、行ID(RID)の論理積を取ります。索引Anding。 IXSCAN 索引スキャン MSJOIN マージ･ジョイン NLJOIN ネスト･ループ･ジョイン RETURN 照会からユーザーへデータが戻されることを表します。 RIDSCAN 索引から取得される行ID(RID)のリストをスキャンします。 SORT 指定された列の順序によりソートします。 TBSCAN データ・ページから直接行を取り出します。 表スキャン。 TEMP データを一時表に保管します。 TQUEUE データベース・エージェント間で表データをやりとりします。 UNION 複数の表から行のストリームを連結します。 UNIQUE 特定の列について、値が重複している行を統合します。 UPDATE 表内の行を更新します｡ 参考-3. Visual Explainの演算子 上記の表は演算子一覧です。 以下に演算子の意味とパフォーマンス上の考慮点を列記します。 ➨ GENROWを演算子として扱う事がありますが、アクセス・プラン・グラフでは表関数として表示されます。 これはINSERT等をするときに行を作成する関数です。 ➨ DELETE 意味: 表からの行を削除します。 この演算子は必須の演算を表します。 アクセス計画のコストを改善するには、削除する行のセットを定義する他の演算子（走 査および結合など）に連結します。 パフォーマンス上の提案。 ‒ 表からすべての行を削除する場合は、空のダミー・ファイルファイルを指定しREPLACEオプションでのLOADまたは IMPORTを検討してください。全行のDELETEのログを出力せずに高速に空にできます。 FETCH 意味: 指定された行識別子 (RID) を使用して、表から列を取り出します。 パフォーマンス上の提案: ‒ そのデータ・ページをアクセスする必要をなくすため、取り出された列を含むよう索引キーを拡張してください。 ‒ 取り出しに関連した索引を見つけ、そのノードをダブルクリックして、その統計ウィンドウを表示させてください。その索引 の クラスター化が高い値になっていることを確認してください。 (I/O) が表のページ数よりも大きい場合は、バッファー・サイズを増やしてください。 ‒ 統計が現行のものでない場合は、 runstats コマンドを使用して更新してください。 ‒ 変位値 (quantile) および頻度値の統計は、述部の選択性に関する情報を提供しており、表の走査より優先して索引の 走査がいつ選ばれるかがそれによって決まります。これらの統計を更新するには、WITH DISTRIBUTION 文節を付けて、 表に対して runstats コマンドを使用します。 ‒ 取り出しに起因する入出力 FILTER 意味: 述部により与えられる基準に従ってデータをフィルター処理するように、残りの述部を適用すること。 パフォーマンス上の提案: 自分が必要とするデータだけを取り出すような 述部を使用していることを確認してください。たとえば、述部の選択性が小さくなる (少ない行に絞り込めるような)述部が先に使われている事を確認してください。 ‒ 最適化クラスが少なくとも 3 になっていることを確認してください。そうすれば、最適化プログラムは副照会ではなく結合を使用しま す。これが不可能な場合は、SQL 照会を手作業で書き直し、副照会を排除してください。 ‒ 参考-3. Visual Explainの演算子 GRPBY 意味: 指定された列または関数の共通の値に従って行をグループ分けすること。値のグループを生成する場合、またはセッ ト関数を評価する場合に、この演算が必要になります。 GROUP BY 列が指定されていないときにも、集合を行うときに列全体を 1 つのグループとして扱うことを指示する総計機能 が SELECT リストの中に指定されているときは、GRPBY 演算子を使用できます。 パフォーマンス上の提案: この演算子は必須の演算を表します。 アクセス計画のコストを改善するには、グループ化する行のセットを定義する他の演算子 （走査および結合など）に連結します。 ‒ 単一の総計機能を持ち GROUP BY 文節のない SELECT ステートメントのパフォーマンスを改善するには、以下の方法を試して みてください。 MIN(C) 総計機能には、C に昇順の索引を作成します。 MAX(C) 総計機能には、C に降順の索引を作成します。 ‒ HSJOIN 意味: 複数の表の修飾行を、表の内容の事前のソートを行わずに直接結合できるようにするハッシュ結合。 文節で参照される表が複数ある場合には、常に結合が必要です。ハッシュ結合は、2 つの異なる表の列を等価にす る結合述部は常に使用できます。結合述部はまったく同じデータ・タイプであることが必要です。ハッシュ結合は、NLJOIN と 同じように書き直された副照会から生じることもあります。 ハッシュ結合は、ソートされた入力表を必要としません。結合は、ハッシュ結合の内部の表を走査し、結合列の値をハッシュ して参照表を生成することによって行われます。その後、外部の表を読み取り、結合列の値をハッシュし、内部表用に生成さ れた参照表をチェックインします。 パフォーマンス上の提案: FROM ローカル述部（つまり、1 つの表だけを参照する述部）を使用することにより、結合する行の数を減らしてください。 ソート・ヒープのサイズを大きくして、ハッシュ参照表をメモリーに保持できる十分な大きさにしてください。 ‒ 統計が現行のものでない場合は、 runstats コマンドを使用して更新してください。 ‒ ‒ INSERT 意味: 表に行を挿入します。 この演算子は必須の演算を表します。 （走査および結合など）に連結します。 アクセス計画のコストを改善するには、挿入する行のセットを定義する他の演算子 参考-3. Visual Explainの演算子 IXAND 意味: 動的ビットマップ技法を使用して、複数の索引走査の結果の論理積を取ります。潜在的な表アクセスを最小限にとどめる ために、この演算子により、述部の論理積を複数の索引に適用させることができます。 この演算子は、以下の目的で実行します。 基礎表にアクセスする前に、行セットの範囲をせばめます。 複数の索引に適用される述部のANDを取ります。 スター型結合で使用し、半結合 (semijoin) の結果の AND を取ります。 パフォーマンス上の提案: ‒ データベースの更新が何度も行われると、索引の断片化が進んで、必要以上に索引ページが増えてしまうことがあります。 この事態を修復するには、索引をいったんドロップして再作成するか、あるいは索引の再編成を行います。 ‒ 統計が現行のものでない場合は、 runstats コマンドを使用して更新してください。 ‒ 一般に、修飾する行の数が少ない場合は、索引の走査が最も効率的です。修飾する行の数を見積もるために、最適化プ ログラムは、述部に参照される列で利用できる統計を使用します。ある値が他の値より頻繁に発生するような場合は、 runstats コマンドに WITH DISTRIBUTION 文節を使用することにより、分散統計を要求することが重要です。一様でない 分散統計を使用することにより、最適化プログラムは、頻繁に発生する値と頻繁でない値とを区別することができます。 ‒ IXAND は単一列の索引で威力を発揮します。IXAND の使用においては開始および停止キーが重要になります。 ‒ スター型結合の場合、実表および関連する次元表で最も選択性の高い列にそれぞれ単一列の索引を作成します。 IXSCAN 意味: 行のストリームを減らすために索引を走査すること。走査では、任意指定の開始／停止条件を使用できます。また、索引 の列を参照する、索引付き述部への適用が可能です。 この演算は、（述部に基づいて）基礎表にアクセスする前に、修飾する行セットの範囲をせばめるために実行されます。 パフォーマンス上の提案: ‒ データベースの更新が何度も行われると、索引の断片化が進んで、必要以上に索引ページが増えてしまうことがあります。 この事態を修復するには、索引をいったんドロップして再作成するか、あるいは索引の再編成を行います。 ‒ 2 つかそれ以上の表がアクセスされているときは、索引を介して内部表にアクセスする場合、外部表の結合列に索引を提 供すると、より効率的に行えます。 ‒ 統計が現行のものでない場合は、 runstats コマンドを使用して更新してください。 ‒ 一般に、修飾する行の数が少ない場合は、索引の走査が最も効率的です。修飾する行の数を見積もるために、最適化プ ログラムは、述部に参照される列で利用できる統計を使用します。ある値が他の値より頻繁に発生するような場合は、 runstats コマンドに WITH DISTRIBUTION 文節を使用することにより、分散統計を要求することが重要です。一様でない 分散統計を使用することにより、最適化プログラムは、頻繁に発生する値と頻繁でない値とを区別することができます。 参考-3. Visual Explainの演算子 MSJOIN 意味: 外部表と内部表の両方からの修飾行が結合述部の順番になっていなければならないマージ結合。マージ結合は、マージ 走査結合または ソート済みマージ結合とも言います。 FROM 文節で参照される表が複数ある場合には、常に結合が必要です。 異なる 2 つの表からの列に等しい結合述部があると きには、常にマージ結合が可能です。このことは、書き直された副照会から生じることもあります。 たいていの場合、表は 1 回だけ走査されるので、マージ結合では結合する列において順序通りの入力を必要とします。この「順 序通りの入力」は、索引またはソートされた表にアクセスすることによって得られます。 パフォーマンス上の提案: ‒ ローカル述部（つまり、1 つの表だけを参照する述部）を使用することにより、結合する行の数を減らしてください。 ‒ 統計が現行のものでない場合は、 runstats コマンドを使用して更新してください。 NLJOIN 意味: 外部表の各行に対して 1 回ずつ内部表を走査（通常は索引走査を使用）する、ネストされたループ結合。 FROM 文節で参照される表が複数ある場合には、常に結合が必要です。 ネストされたループ結合には、結合述部が必要ではあ りませんが、それがあれば一般的にはパフォーマンスが上がります。 ネストされたループ結合は、次のいずれかの方法で実行します。 ‒ 外部表のアクセスされる行のおのおのに対して、内部表全体を通して走査する。 ‒ 外部表のアクセスされる行のおのおのに対して、内部表で索引参照を実行する。 パフォーマンス上の提案: ‒ ネストされたループ結合は、内部表の結合述部列に索引が存在していれば、効率が上がると思われます。（内部表は、 ‒ RETURN ‒ 意味: NLJOIN 演算子の右に表示されます。） 内部表が IXSCAN ではなく TBSCAN になっているかどうか、確認してください。 そうなっているなら、その結合列に索引を追加することを検討してください。 重要性は劣るものの、結合をさらに効率的にするためのもう 1 つの方法は、外部表が順序通りになるように、外部表の結 合列に索引を作成することです。 統計が現行のものでない場合は、 runstats コマンドを使用して更新してください。 照会からユーザーへデータを戻すこと。これはアクセス計画グラフにおける最後の演算子であり、集計された合計値と、ア クセス計画のコストを示します。 この演算子は必須の演算を表します。 パフォーマンス上の提案。 ‒ 自分が必要とするデータだけを取り出すような 述部を使用していることを確認してください。たとえば、述部の選択性値が 戻り値を必要とする表の部分を表していることを確認します。 参考-3. Visual Explainの演算子 RIDSCN 意味: 1 つかそれ以上の索引から取得される行識別子 (RID) のリストを走査します。 最適化プログラムがこの演算子を考慮するのは、以下の場合です。 ‒ 述部が OR キーワードにより接続されているか、IN 述部が存在する場合。索引ORing と呼ばれる技法が使用されます。 これは、同じ表に対する複数の索引アクセスの結果を組み合わせるものです。 ‒ 単一索引アクセスのために、リスト事前取り出しを使用するのが効果的な場合。基礎行にアクセスする前に行識別子を ソートしておくなら、I/O の効率が上がるからです。 SORT 意味: 表内の行を、1 つかそれ以上の列の順序に並べ替えること。その際、任意選択で、行の重複をなくします。 要求された順序づけを満足するような索引が存在していない場合、ソートは必須です。また、ソートが索引走査よりもコストがか からない場合にも使用されます。ソートは通常、必要な行がいったん取り出された後、最後に実行される操作です。あるいは、 結合やグループ化に先立ってデータのソートを行います。 行の数が多い場合や、ソートされたデータをパイプ処理することができない場合、この操作にはコストのかかる一時表の生成が 求められます。 パフォーマンス上の提案: ‒ ソート列に索引を追加することを検討してください。 ‒ 自分が必要とするデータだけを取り出すような 述部を使用していることを確認してください。たとえば、述部の選択性値 が戻り値を必要とする表の部分を表していることを確認します。 ‒ 一時表スペースの事前取り出しサイズが十分であること、つまり、 I/O に縛られていないことを確認してください。（このこ とをチェックするには、 「ステートメント」->「統計の表示」->「表スペース」の順に選択してください。） ‒ 頻繁に大規模なソートが必要とされる場合は、以下の構成パラメーターの値を増やすことを検討してください。 ソート・ヒープ・サイズ (sortheap)。このパラメーターを変更するには、コントロール・センターでデータベースを右ク リックし、そのポップアップ・メニューから「構成」を選択します。そこで表示されるノートブックから、「パフォーマン ス」タブを選択します。 ソート・ヒープしきい値 (sheapthres)。このパラメーターを変更するには、コントロール・センター でデータベース・イ ンスタンスを右クリックし、そのポップアップ・メニューから 構成を選択します。 そこで表示されるノートブックから、 「パフォーマンス」タブを選択します。 ‒ 統計が現行のものでない場合は、 runstats コマンドを使用して更新してください。 参考-3. Visual Explainの演算子 TBSCAN 意味: データ・ページから直接、必要なデータすべてを読み取って、行を取り出す表走査（関係走査）です。 最適化プログラムが索引走査よりもこの種の走査を選択するのは、次のような場合です。 ‒ 走査される値の範囲が頻繁に発生する場合（つまり、表の大部分をアクセスする必要がある場合）。 ‒ 表が小さい場合。 ‒ 索引のクラスター化の程度が低い場合。 ‒ 索引が存在していない場合。 パフォーマンス上の提案: ‒ 表が大きく、表の行の大半がアクセスされない場合は、表走査よりも索引走査の方が効率的です。この状況で最適化プ ログラムが索引走査を採用する可能性を高めるために、 選択述部のある列に索引を追加することを検討してください。 ‒ 索引がすでにあるが使用されていない場合、それに先行する列のおのおのに選択述部があることを確認してください。 選択述部があれば、次に、その索引の クラスター化が高い値になっていることを確認してください。（この統計を見るには、 ソートの下にある表に対して表統計ウィンドウをオープンし、その「索引」押しボタンを選択して、索引統計ウィンドウを表 示させてください。) ‒ 表スペースの事前取り出しサイズが十分であること、つまり、 I/O に縛られていないことを確認してください。（このことを チェックするには、 「ステートメント」->「統計の表示」->「表スペース」の順に選択してください。） ‒ 統計が現行のものでない場合は、 runstats コマンドを使用して更新してください。 ‒ 変位値および頻度値の統計は、述部の選択性に関する情報を提供しています。たとえば、これらの統計を使って、どん なときに、表走査より優先して索引走査を選ぶかを判別できます。これらの値を更新するには、WITH DISTRIBUTION 文 節を付けて、表に対して runstats コマンドを使用します。 TEMP 意味: 他の演算子によるバックアウト読み取り（おそらく何度も行う）のために、データを一時表に保管する処置。その表は、 SQL ステートメントが処理された後に除去されます（それ以前に除去されていない場合）。 この演算子は、副照会の評価や中間結果の保管のために必要になります。状況によっては（ステートメントが更新可能な場合 など）、これは必ず必要です。 参考-3. Visual Explainの演算子 TQUEUE UNION 意味: 複数の表から行のストリームを連結します。 この演算子は必須の演算を表します。 アクセス計画のコストを改善するには、連結する行のセットを定義する他の演算子（走 査および結合など）に連結します。 UNIQUE 意味: 複数のデータベース・エージェントが 1 つの照会を処理している場合に、1 つのデータベース・エージェントから別のデー タベース・エージェントへデータを渡すのに使われる表待ち行列。複数データベース・エージェントは、並列処理が関係している ときに照会を処理するために使用されます。 表待ち行列には次のような種類があります。 ‒ ローカル: 単一のノード内にあるデータベース・エージェント間でデータを受け渡しするために、表待ち行列が使用されま す。ローカル表待ち行列は、 区分内並列性 ‒ 非ローカル: 別々のノードにあるデータベース・エージェント間でデータを受け渡しするために、表待ち行列が使用されま す。 意味: 指定された列について同じ値を持つ行の重複をなくします。 パフォーマンス上の提案。 ‒ この演算子は、該当する列に固有索引が存在する場合に限り、不必要です。 UPDATE 意味: 表の行の中のデータを更新します。 この演算子は必須の演算を表します。 アクセス計画のコストを改善するには、更新する行のセットを定義する他の演算子（走 査および結合など）に連結します。 参考-4. ホスト変数の使用 ホスト変数に推測の値を入れて検証するのは問題があります。 ■ ■ BETWEEN 10 AND 19 --> ヒットするのは10件=1% BETWEEN 1 AND 800 --> ヒットするのは800件=80% ‒ (low2key=1,highkey=999、分散統計がない場合) ホスト変数を使用しているSQLについてExplainでアクセス経路の検証を行うためには、ホスト変数部分 を?として実行する ■ SELECT * FROM TBL1 WHERE COL1 > ? ‒ SQL文の条件に変数が設定されていると、オプティマイザーは省略時値を使用してアクセス経路のための見積もりを行う colcaｒｄ <、>、=<、=>の省略時の選択性 < 100 0.3333 < 1000 0.1 < 10000 0.03333 < 100000 0.01 < 1M 0.003333 < 10M 0.01 < 100M 0.0003333 => 100M 0.001 参考-4. 解説：ホスト変数の使用 ホスト変数使用について ホスト変数を使わず定数を使用した方が、最適化の精度は向上します。 しかし、一般に静的SQLではホスト変数 を使わないコードは現実的ではありません。 ここではホスト変数を使用する上での考慮点を説明します。 静的SQLでは実行時に値をあたえる手法としてホスト変数を使用します。しかし、実行時に与えられる数値により見積もりが 変わってしまいます。 従って、ホスト変数に任意の値を入れて検証するのは問題が残ります。 ‒BETWEEN 10 AND 19 --> ヒットするのは10件=1% ‒BETWEEN 1 AND 800 --> ヒットするのは800件=80% ‒(lowkey=1,highkey=999、分散統計がない場合) そこで、ホスト変数を使用しているSQLについてExplainでアクセス経路の検証を行うためには、ホスト変数部分を?として実行 します。 Explainでは?をホスト変数として認識して、省略時の選択性(selectivity/filter factor)を使用して見積もりコストを計算 しアクセス・パスを表示します。 ‒SELECT * FROM TBL1 WHERE COL1 > ? 省略時の選択性の値は表にあるとおりです。colcardの値を元に設定されます。 SQLのコンパイル時(バインド時)に索引スキャンが選択された場合でも、実行時のホスト変数の値により表スキャンになること があります。