マイク ゲイン著

私たちにも自分の行動を浮き彫りにして解説するような建設的なフィードバックが必要な場合があります。このフィードバックを取得して利用することは非常にメリットがあります。これは、DB2 UDB for iSeriesについても言えることです。

私たちのほとんどにとって、データベース・エンジンとクエリー最適化プログラムは「ブラック・ボックス」です。データ中心作業をアプリケーションで達成するために、構造化照会言語(SQL)要求を発行して、DB2にその方法ではなく実行する内容を指示します。クエリー最適化プログラムはSQL要求を実現する最適な方法と戦略を判断し、データベース・エンジンは最適化プログラムにより提供された方法と戦略を実行します。しかし、データベース・パフォーマンスのチューニングを支援するためにこれらの方法と戦略を理解し分析する場合はどうなるでしょう。言い換えれば、どのようにブラック・ボックス内に侵入してDB2がどのように私たちの要求を実施したか判断するのでしょうか。ここでデータベースのフィードバックが思いがけず役立ちます。DB2セラピストの前に腰をかけ、何が学べるのか見てみることにしましょう。

通常ユーザーがSQL DML要求を開始する場合、以下のように3つの段階を経過します。

・ 最適化 −クエリー・プランが作成される(クエリー・プランにはユーザーの要求を
　 満たすオブジェクト、方法、戦略が含まれています)
・ オープン −　カーソルを開く
・ 実行 − データベース・エンジンによりクエリー・プランが実　 行される

これらの段階には時間とリソースが必要になります。特定のクエリーがこれらの段階を経ることを理解するため、またさらに重要なこととして、これらの段階の背後に「なぜ」「何か」があるのか分析するため、さまざまなフィードバック・メカニズムを採用できます。これはDB2を開きすべてを語らせることだ考えてください。

DB2 UDB for iSeriesが生きている間、さまざまなフィードバック・オプションが開発され、使用されました。アプリケーション環境、必要な情報のレベル、クエリー・プランの複雑さにより、オプションによっては他のオプションより優れている場合があります。フィードバック・メカニズムのほとんどはデフォルトでは有効でないため、情報を収集して提供するためにスイッチを入れなければなりません。これらのメカニズムの中にはオーバーヘッドがほとんどない、またはまったくないものがあったり、一方でアプリケーションのスループットに影響を与え、多量のリソースを使用するものもあります。

OS/400 V5R2以降、IBMは新しいSQLクエリー・エンジンを導入しました。この新型エンジンは、さまざまなリリースで段階的に使用されます。IBMはこの新型エンジンを「SQE」、元のクエリー・エンジンを「CQE」と呼んでいます。新型SQLクエリー・エンジンの詳細については、ibm.com/servers/eserver/iseries/db2/sqe.htmlをご覧ください。

i5/OSおよびDB2 UDB for iSeriesのV5R3におけるフィードバック・メカニズム(図 1)は以下のとおりです。

・ Print SQL Information (PRTSQLINF)
　　 高水準言語プログラムの組み込み静的 SQL、ODBCまたはJDBCコネクション
　　 からの拡張動的SQL、DRDAコネクションからのSQL、ジョブ内の動的SQLに対し
　　 てスプール・ファイルまたはGUIウィンドウにテキスト・メッセージを作成する
・ デバッグ・メッセージング
　　 ジョブ・ログにテキスト・メッセージを作成する
・ 詳細データベース・モニター
　　 単一テーブルに詳細な「トレース」データを作成する
・ 要約データベース・モニター
　　 複数のテーブルにサマリー・データを作成する
・ Visual Explain
　　 詳細モニター・データまたはSQEプラン・キャッシュからグラフィックスおよび
　　 テキスト情報を作成する

さらに追求してみると

OSコマンドPRTSQLINFまたはiSeriesナビゲーター・グラフィカル・インターフェースでPrint SQL Informationを起動します。この情報は、高水準言語プログラム・オブジェクト(*PGM)、SQLパッケージ・オブジェクト(*SQLPKG)、または任意のジョブ(*JOB)に保管されたクエリー・プラン情報から作成されます。情報はクエリーのオープン処理または実行のためには提供されていません。メッセージはスプール・ファイルに置かれ、SQL Package、Explain SQLの順に選択することでiSeriesナビゲーターによりグラフィカルに表示できます。PRTSQLINFインターフェースは最後に保管したプランのみ表示するため、オブジェクトに置かれたプラン情報は実行時に実際に使用されているプランを反映または反映していない場合があります。大雑把に言えば、PRTSQLINFは他のデータベース管理システムで見られるSQL "EXPLAIN"ステートメントと同等です。 デバッグ・メッセージングは以下、または特定のデータベース接続属性により開始および停止します。

STRDBG UPDPROD(*YES)
ENDDBG

iSeriesナビゲーターのRun SQL Scriptをクエリーを分析している場合は、ジョブ・ログでOptions、Include Debug Messagesの順にクリックし、データベース・ホスト・ジョブをデバッグ・モードにします。クエリーを最適化しているジョブがデバッグ・モードになっている場合は、最適化プログラムは情報を作成し、クエリーの最適化、オープン処理、および実行中にジョブ・ログにメッセージを配置します。最適化プログラムは最適化時に小さなメッセージの固まりのみジョブ・ログに送信しているため、デバッグ経由でフィードバックを収集する際のパフォーマンス・オーバーヘッドは最小限になります。

使用できるデバッグ・メッセージの集合が現行の最適化プログラムで使用できるすべての方法と戦略を扱うわけではないことを頭に入れておくことが重要です。例えば、新型SQLクエリー・エンジンが機能強化されるにつれ、デバッグ・メッセージが改善されているわけではありません。さらに、基本的なSQEメソッドは既存のCQEメッセージにマッピングされています。この結果、クエリー・プランを誤解したり完全に表示できない場合があります。最適化のフィードバックを戦略的に提供するには、データベース・モニターおよびVisual Explainで導きます。

詳細データベース・モニターはSTRDBMON/ENDDBMON、iSeriesナビゲーター、およびデータベース接続属性により開始されます。個々のジョブまたはジョブすべてをモニターできます。システムで1つまたはすべてのジョブについて詳細モニターを起動すると、SQL要求およびそれぞれのプランを記述したデータが、異なる行形式、行ID、一意のクエリーIDを使用した単一のテーブルに配置されます。SQL要求ごとの詳細な列情報として以下のような情報があります。

・ QQRID −　行タイプID (1000、3000、3001など)
・ QQUCNT −ジョブごとのクエリー/要求ID
・ QQJOB −ジョブ名 ・ QQUSER −ジョブ・ユーザー名
・ QVC102 −現行ジョブ
・ ユーザー名(V5R3の新機能)
・ QQJNUM −ジョブ番号
・ QQI9 −スレッドID
・ QQTIME −行が挿入された時間
・ QQ1000 −通知されたSQLテキストまたは指標などの行固有の情報

同じSQL要求が複数回最適化され、実行されると、各要求についてデータが取り込まれます。例えば、ユーザー・クエリーSELECT * FROM TABLE1 WHERE COLUMN1 = :HVが26回実行されると(使用できるホスト:HVは異なる値AからZに設定)、詳細モニターは26回の実行すべてについて情報を取り込み、すべてのクエリー実行インスタンスのクエリー・プラン情報を保持します。実質的に、モニターが動作している間SQL要求とフローをトレースし、より大規模なデータ・セットが生み出されます。最近のお客様の例では、ライブ実働システムで詳細データベース・モニターを5分間実行した結果、モニター・テーブルのデータは6 GBになりました。

さらに、詳細データは最適化およびオープン処理中にテーブルに挿入されます。これによりさらにオーバーヘッドが発生し、他の作業に影響を与える場合があります。情報は、クエリーの最適化、オープン処理、および実行に対して提供されます。詳細データベース・モニター・データはクエリーの視覚的なExplainのための1次入力です。

要約データベース・モニターはAPIを使用して開始および停止します。iSeriesナビゲーターはこのプロセスで役立つようなグラフィカル・インターフェースを提供します(図 2)が、 OSコマンド・インターフェースは提供していません。要約モニターを起動すると、SQL要求およびプランを記述したデータが一連のテーブルに配置されます。同じSQL要求が複数回最適化され、実行されると、データが要約され、よりコンパクトで詳細でない形式になります。例えば、ユーザー・クエリーSELECT * FROM TABLE1 WHERE COLUMN1 = :HVが26回実行されると(使用できるホスト:HVは異なる値AからZに設定)、要約モニターは26回の実行すべてに渡る情報を要約し、最も時間が掛かるクエリー実行インスタンスのクエリー・プラン情報を保持します。他の25回の実行に対するクエリー・プラン情報は表示されません。

さらに、モニターが完了すると要約データはメモリーに保持され、テーブルに挿入されます。これによりオーバーヘッドが減り、他の作業への影響も少なくなります。情報は、要約レベルでのみ、クエリーの最適化、オープン処理、および実行に対して提供されます。クエリーは要約データから視覚的にExplainできません。

Visual ExplainはRun SQL Scripts (図 3)をクリックするか、SQL Performance Monitors、List Explainable Statements (図 4)の順に選択して起動します。Visual Explainメカニズムへの入力は詳細モニター・データまたはSQEプラン・キャッシュに常駐しているクエリー・プラン情報からもたらされます。Visual ExplainはiSeriesナビゲーターのDB2へのJDBCコネクションを使用して、クエリーの実行有無に関わらずクエリーをダイナミックかつ視覚的にExplainします。すべてのSQEクエリーはクエリーを実行せずに視覚的にExplainできますが、(ハッシュ・テーブル経由の結合とグループ化など) 複数のステップが必要なより複雑なCQE クエリーにはrun-and-explainオプションが必要になります。

クエリーの最適化の前に、詳細データベース・モニターが自動的に開始され必要なデータを収集します。Visual Explainウィンドウを閉じると、詳細データベース・モニター・データを保管または破棄できます。モニター・データを保管すると最適化プログラムのフィードバックが保管されるため、後でクエリーを視覚的にExplainできるのです。

また、既存のデータベース・モニター・データで表示されたクエリーを視覚的にExplainすることもできます。クエリーの最適化、オープン処理、実行の情報、およびクエリー環境の記述を取得します。クエリー最適化プログラムの原価計算プロセスに影響を与えるさまざまな要因すべてが1つのインターフェースに表示されます。別の役立つ機能により、アクティブ・ジョブ内で現在実行中のSQLステートメントを特定し、データベース・モニターを開始せずにこのクエリーを視覚的にExplainできます(図 5)。この場合、Visual Explainへの入力はSQEプラン・キャッシュに保管されているクエリー・プランです。

他のSQEプラン・キャッシュおよびトレース・オプションが使用できますが、それらはIBM ServiceおよびDB2 Developmentのみが使用できます。

とにかく決める

使用する特定のメカニズムと使用するタイミングは分析要件とアプリケーション環境により異なります。一般的に、データベース・モニターとVisual ExplainはDB2 UDB for iSeriesフィードバックを収集するのに最も役立つツールです。PRTSQLINFとデバッグ・メッセージが提供するテキスト情報は、特に永遠に拡大を続ける方法と戦略のリストを考えた場合、最適化プログラムにより作成されたより複雑なクエリー・プランを記述するには十分ではありません。この欠点は、効率性とパフォーマンスを得るため最適化プログラムがしばしば暗黙的にユーザーのクエリーを書き直すことで複雑化しています。最適化プログラムで提供されたさまざまなフィードバックを比較対照するため、いくつかクエリーを取り上げ、内容と表示でフィードバックがどのように異なるか見てみることにしましょう。まず以下の単純なクエリーを見てみましょう。

SELECT COUNT(*) AS NO_ORDERS_JUNE_30
FROM　　　　 　SALES S
WHERE S.ORDERDATE = '2005/06/30';

単純なクエリーでは、デバッグ・メッセージ(図 6)、Print SQL Information (図 7)、Visual Explain (図 8)で表現された最適化プログラムのフィードバックで適切にプランを理解できます。これらのメカニズムはすべてテーブルSALESのテーブル・スキャン(到着順)を記述しています。デバッグ・メッセージとVisual Explainはローカル選択述部WHERE S.ORDER DATE = '2005/06/30'に対応するため指標(アクセス・パス)の作成を提案しています。デバッグ・メッセージ経由でキー欄にアドバイスするには、第2レベルのメッセージ・テキストを表示する必要があります。Visual Explain経由でキー欄にアドバイスするには、Index Advisor (図 9)を起動します。

Visual Explainグラフの方がより正確です。中でも、COUNT(*)を表示する集合ノード、テーブル・スキャンごとに処理される行数(600,122行)、およびクエリーにより戻される行数(1行)を表示しています。さらにVisual Explainと基礎となるデータベース・モニター・データはクエリー最適化プログラムに影響を与える環境要因への洞察力を与えてくれます。これらの要因として、メモリー・スプール・サイズ、並列処理の程度、データの選択および処理について各ノードで使用するロジックなどがあります。この単純なクエリーに対する詳細データベース・モニター・データ(図 10)にも最適化プログラムからの詳細フィードバックがテキスト形式で含まれています。

より複雑なクエリー(図 11)を見てみると、クエリー最適化プログラムにより選択された方法と戦略を明確に表現する場合にVisual Explainと詳細データベース・モニターがなぜ優れているのか次第にわかってきます。

デバッグ・メッセージ(図 12)とPRTSQLINF (図 13)は、各テーブルのアクセス方式(第2レベル・テキスト経由)と結合順を示しています。デバッグ・メッセージはグループ化方式または配列方式を浮き彫りにしてはいません。デバッグ・メッセージもPRTSQLINFも結合方式(ハッシュ・テーブルのプローブ)を適切に記述してはいません。この場合、Visual Explainグラフ(図 14)のみがローカル選択述部および結合述部を含む推奨する指標を含む、クエリー・プラン全体を正確に表現しています(図 15)。Visual Explainグラフは「1000回の言葉で語るより一枚の絵の方がわかり易い」という言葉を具体的に表現したものです 。

この複雑なクエリーの詳細データベース・モニター・データ(図 16)にも最適化プログラムからの詳細フィードバックがテキスト形式で含まれています。クエリー・プランはより複雑なため、方法と戦略を記述するにはより多くのデータが必要です。

ちょっとしたアドバイスとカウンセリング

指標によりクエリー最適化プログラムとデータベース・エンジンのパフォーマンス改善に役立つことを前提に、アドバイスとカウンセリングができます。すでに強調したように、「指標がアドバイスした」情報は最適化処理の一部として提供できます。このアドバイスの質と量は使用しているクエリー・エンジン(SQE、CQEなど)とフィードバック・メカニズムにより異なります。一般的にSQEの方がCQEより優れた指標アドバイスを行うことができます。CQEは、指標が存在せずテーブル・スキャンが使用できる場合にローカル選択述部(WHERE COLOR = 'BLUE'など)に対して指標を提案します。CQEがクエリー・プランの一部として一時指標を作成する場合、一時指標のキー欄に情報が提供されます。
デバッグから作成されたメッセージは、指標がアドバイスした情報を含むことができます。メッセージの第2レベル・テキストにはどのキー欄を使用するかについての詳細が含まれています。PRTSQLINFが提供する情報には、指標が作成したアドバイスは含まれていません。データベース・モニターはそのデータ・セットの一部として指標作成アドバイスを提供しています。

完全な指標には、任意のローカル選択述部、結合述部、および可能性としてグループ化列および配列列が含まれています。CQE最適化プログラムがクエリーに対する「完全な」指標作成についてのアドバイスをしない一方、Visual Explainはさまざまな情報の断片をつなぎ合わせてより優れた推奨を行おうとします。一方SQEは、クエリーに対する完全な指標にアドバイスを行おうとします。

指標がアドバイスしたフィードバックの違いを理解するために、簡単な例を見てみましょう。図 17のクエリーを基に、SALESテーブルの完全な指標にはキー欄ORDERDATE、CUSTKEYがあります。これはローカル選択(S.ORDERDATE = '2005/06/30')および結合(S.CUSTKEY = C.CUSTKEY)をサポートします。CUSTOMERテーブルの完全な指標にはキー欄CUSTKEYがあります。これは結合(S.CUSTKEY = C.CUSTKEY)をサポートします。

デバッグ・メッセージおよびCQEデータベース・モニター・データは、結合列を省き、キー欄ORDERDATEのあるSALESテーブルに対する指標にのみアドバイスします。CUSTOMERテーブルに対する指標にはアドバイスはされません。しかし、Visual ExplainおよびSQEデータベース・モニター・データは、ローカル選択列と結合列を指定したSALESテーブルとCUSTOMERテーブル両方の指標にアドバイスします。

列データとアクセスに使用できるさまざまな方式すべての情報が欠けている場合、SQEは任意のテーブルに複数の指標を推奨します。例えば、ローカル選択術部にある指標を、列順の配列に別の指標を推奨するといった具合です。これにより、最適化プログラムにテーブル・スキャン、指標プローブ、見出しスキャンなど多数のデータ・アクセスの選択肢が与えられます。

最適化プログラムのアドバイスを受け入れ、推奨された指標を作成することにした場合、クエリーは以前より高速に実行されるでしょうか。私はいつもそれは「場合による」と答えています。推奨された指標を作成することで、最適化プログラムにテーブルの実データおよびそのデータと他のデータとの関係に関するより詳細な情報を提供しています。この新しい情報により最適化プログラムは異なる戦略に達する可能性があります。この戦略では、以前に推奨された指標は必要ないかもしれません。この時点で、フィードバックに飽き飽きしているかもしれませんが、あきらめないでください。新たに作成されたこれらの指標にはメリットがあります。指標の作成と使用についての詳細情報は、ibm.com/servers/enable/site/education/abstracts/indxng_abs.htmlをご覧ください。

i5/OSおよびDB2 UDB for iSeriesのV5R3以前は、最適化プログラムがどのように指標を使用しているのか正確に判断することはできませんでした。例えば、指標を実装ではなく統計情報に使用していた場合、この動作を表すのにフィードバックを使用できませんでした。現在では、Tables、Indexes under Environment: My Connectionsの順にクリックすることでこの情報をiSeriesナビゲーター経由で使用できます。

報告書には、任意のデータベース・テーブルに対する指標、その属性、その使用方法が記載されています。この報告書はどの指標が使用できるかということと、その使用パターンを理解するのに役立ちます。また、統計情報または実装に対していつ指標を最後に何回使用したのかを示しています(図 18)。この情報を表示するには、報告書を右にスクロールする必要があります。

専門家に聞く

分析の範囲により、あるフィードバック・メカニズムが他のメカニズムより適している場合があります。例えば、データベース・モニターはある期間、1つ以上のデータベース接続の間、あるいは特定のユーザーに対して情報収集するのに最も適しています。これを使用して、言うなれば狭いまたは広い網をかけることができます。さらに、収集した情報はすべて一箇所に置かれます。しかし、同じ期間またはユーザー・セットに対してデバッグ・メッセージをオンにすると、多数のジョブ・ログに情報が散乱し、データベース・モニター・テーブルで示されたデータほど完全ではなくなります。このよい例として、データベース・モニターがユーザーのSQLステートメント収集するが、デバッグ・メッセージはしないということがあります。また、さまざまなOSメッセージの形でクエリー・プランを分析するのはわずらわしいことを考えてください。

PRTSQLINFは、プログラムまたはパッケージでどのようなSQLステートメントが実行されるのか洞察力を与えてくれます。すでに述べたように、プログラムまたはSQLパッケージ・オブジェクトには実行する実際のプランが含まれていない場合があります。これによりExplain情報に頼るのはリスクが高くなります。さらに、クエリー・プランの情報はテキストで、より複雑な戦略を適切に説明するほど堅固ではありません。

Visual Explainとデータベース・モニターの使用を見てみると、別の全体像が見えてきます。Visual Explainは詳細モニター・データまたはSQEプラン・キャッシュから単一のクエリー・プランを表示しています。SQL要求のセットまたはSQL要求のフローを分析する場合、それはあまり役に立ちません。一方、データベース・モニターは時間、ユーザー・セット、特定のSQL要求に対してSQLのパフォーマンスを分析できます。データベース・モニターとともに使用した場合、クエリーがデータベース・モニター・データ内で特定されればVisual Explainはプランを表示できます。

私の経験では、最適な分析プロセスとしてデータベース・モニター・データを戦略的に取り込み、積極的にVisual Explainを使用します。モニター・データの分析によりSQL実行環境を幅広い視点で見つめ、同時に個々のクエリーの詳細情報を表示できます。

目を閉じて想像してみてください

未来の予測には常にリスクが伴いますが、i5/OSの次のリリースではフィードバックと分析の分野で興味深い機能拡張をいくつか期待できます。その例をいくつかご紹介します。

さまざまなメカニズムがクエリーごとに指標の作成に関するアドバイスを行う一方で、次のリリースではシステムで動作しているすべてのクエリーの指標アドバイスを集中的に収集し、表示できます(図 19)。この指標セットを分析して、アプリケーションまたはクエリー・グループ間で最もメリットを生み出す最適な指標を判別できます。キー欄に規定するだけでなく、このツールは何度指標がアドバイスされたかも示します。iSeriesナビゲーター・データベース・ツールのShow IndexesとIndex advisorは任意のテーブルについて互いに補完します。テーブルの現行の指標を最適化プログラムが提案した指標と比較し、それにしたがってテーブルの指標戦略を調整できます。

今日、詳細データベース・モニターを開始した場合、モニターの範囲と細分性のレベルは極めて荒いものです。つまり、すべてのジョブまたは単一のジョブをモニターできます。QAQQINIオプションは、i5/OSからSQL要求を除外したり、指定の時間しきい値より長く実行すると期待されているSQL要求のみ含めるなど、その他の前置きフィルター処理を提供します。その他のフィルター処理は収集後手動で扱う必要があります。今後、より多くのオプションが最低見積実行時間、特定または一般的なジョブ名やユーザー名、クライアントのIPアドレス、また特定のテーブルに基づいてSQL要求を含めたり、除外することができるようになるでしょう(図 20)。これらのオプションはアナリストが収集範囲を絞り、オーバーヘッドを最小限に抑えるのに役立ちます。

一般的な調査方法として詳細モニター・データを分析し、特定のクエリーまたはクエリー・セットに的を絞り、Visual Explainを実行して任意のクエリー・プランを理解する方法があります。残念ながら、データベース・モニター・クエリーの結果セットとVisual Explainツール間はリンクされていません。iSeriesナビゲーター内で提供されたデータベース・モニター分析は今後、フィルター処理と分析の結果についてVisual Explainを起動できるように機能拡張されるでしょう。例えば、ジョブ・セットの詳細データベース・モニター・データを収集後、最も時間が掛かったジョブを見つけ、このジョブ内で最も長時間実行していたクエリーを見つけてそれを視覚的にExplainできるでしょう。

i5/OSの次リリースの別の機能追加にSQEプラン・キャッシュに含まれている情報へのグラフィカル・インターフェースがあります(図 21)。この情報は、マクロまたミクロ両方の視点からSQL要求を理解できる能力を大幅に強化します。特定の収集ツールを開始せずに、最も広く実行されたクエリーを識別し、それらのさまざまなプランを理解できるでしょう。

現在iSeriesナビゲーターを使用していない場合は、その最新バージョンをインストールし、そのインターフェースとツールに慣れておくほうがよいでしょう。これはDB2 UDB for iSeriesの戦略的インターフェースだからです。iSeries AccessおよびiSeriesナビゲーターの詳細については、ibm.com/servers/eserver/iseries/accessをご覧ください。

セッションの終わりに

DB2のフィードバック・メカニズムの効率的な使用方法を学ぶには、DB2 UDB for iSeries SQL Performance Workshop (ibm.com/servers/eserver/iseries/service/igs/db2performance.html)の受講を検討してみてください。

おわかりのように、DB2 UDB for iSeriesの真の価値としてオンデマンドでフィードバックを要求し、受け取る能力があります。この情報は、SQL要求の分析、理解、チューニングの際に役立ちます。この情報の最適な送達機構はiSeriesナビゲーターです。ではゆったり腰を下ろして、フィードバックを見てみましょうか。

マイク ゲイン: IBM Systems and Technology Groupのシニア・テクニカル・スタッフのメンバーであり、DB2 UDB for iSeries Center of Competencyのチーム・リーダー。かつて、IBM AS/400システムのエンジニア兼テクニカル・コンサルタントとして従事。