SQLを分析する高度なテクニック：DB2チューニング・ベストプラクティス（6）（4/4 ページ）

» 2004年12月08日 00時00分公開

[Fraser McArthur，DB2 Enablement Consultant／IBM Canada Ltd.]

SQLステートメントのチューニング

　SQLステートメントの実行パフォーマンスをさらに向上させるためのテクニックをいくつか紹介します。

　後続の位置指定UPDATEステートメントによって更新される可能性のある行を保護するには、「SELECT … FOR UPDATE」を使用します。これにより、選択されるすべての行に更新ロック（U）がかけられます。これは、後で行を更新する場合にデッドロックが発生するのを防ぐために役立ちます。

　結果表が読み取り専用であることを明示するには、「SELECT … FOR READ ONLY（またはFETCH ONLY）」を使用します。この場合、位置指定UPDATEステートメントや位置指定DELETEステートメントの中でカーソルを参照することはできません。このようにすると、DB2がブロッキング（1つのFETCH要求でクライアントに対して複数の行を戻すこと）を実行できるようになるため、FETCH操作のパフォーマンスが向上します。

　「SELECT … OPTIMIZE FOR n ROWS」を使用して、応答時間を最適化します。これによりオプティマイザは、応答セット全体のコストを最小限に抑える（デフォルトの動作）代わりに、n行を素早く戻すようにします。また、READ ONLY文節を使用している場合は、それぞれのブロック内で戻される行数に影響を与えます（1つのブロック内では最大でn行が戻されます）。これは、フェッチ可能な行数を制限するものではありませんが、n行より多くの行をフェッチするとパフォーマンスが低下します。データ・バッファがこの文節の影響を受けるようにするためには、「n×行サイズ」の値が（DBM CFG RQRIOBLKまたはASLHEAPSZによって定義される）通信バッファのサイズを超えてはいけません。

　「SELECT … FETCH FIRST n ROWS」を使用すると、照会結果セットのサイズを制限できます。

　「ALTER TABLE … ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE」を使用すると、大量のDELETE（パージ）を実現できます。この操作はログに記録されないため、何か問題が発生した場合は、表をドロップしなければなりません。より安全な方法は、REPLACEオプションと空のファイルを指定して、IMPORTユーティリティを使用することです。

　ロック待機やデッドロックの可能性を減らすには、commitを使用して頻繁にロックを解放します。

　ステートメントを繰り返し実行する場合は、リテラルではなくパラメータ・マーカーを使用します。OLTPではコンパイル時間が重要になるため、リテラルをパラメータ・マーカーに置き換えることにより、コンパイルの繰り返しを回避します。パラメータ・マーカーを使用する場合、オプティマイザは値が均一に分散していると仮定するため、データに偏りがあると適切なアクセス・プランが選択されません。一般的に、OLAP環境の方がこの影響をより強く受けます。

　細分性を高め、パフォーマンスと並行性を向上させるには、分離レベルをステートメント・レベルで指定します。DB2では、UR（Uncommitted Read：非コミット読み取り）、CS（Cursor Stability：カーソル固定）、RS（Read Stability：読み取り固定）、RR（Repeatable Read：反復可能読み取り）がサポートされています。例えば「SELECT * FROM STAFF WITH UR」とすると、非コミット読み取り（最小限のロック）を使用してSELECTが実行されます。

DB2 V8 ドキュメンテーション

参照情報→SQL→照会→選択ステートメント

偏ったデータでパラメータ・マーカーを使用する場合の選択性の指定

　データが均一に分散していない場合は、選択性（selectivity）を指定すると便利です。選択性とは、行が述部を満たす（すなわち真になる）可能性のことです。より限定的な述部が望ましいといえます。そのような述部は、（その後の処理を行う）演算子に対してより少ない行を戻すため、照会を完了するのに必要なCPUの負荷と入出力の数が少なくて済みます。

　例えば、100万行の表に対する述部の選択性が0.01（1％）である場合、1万行が述部を満たし、99万行が述部を満たさないと見積もられることを意味します。

　データが均一に分散しているという仮定をそのまま使うのではなく、故意に低い値（例えば0.000001）を選択性に指定することにより、オプティマイザがその列の索引を使用するように「保証」できます。この方法は、表が大きくなると予想されるが、特定の列の索引が常に使用されるように保証したい場合に便利です。選択性に「1」を指定すると、DB2が特定の列の索引を使用しないように指示することができます。

　このテクニックでは、最適化レベルに「5」（例えば、DFT_QUERYOPT=5）を指定すると、最も予想どおりに動作します。また、SELECTIVITY文節を使用するには、まず初めにレジストリ変数「DB2_SELECTIVITY=YES」を設定してインスタンスを再始動する必要があります。

　SELECTIVITY文節は、以下の述部に対して指定できます。

ホスト変数またはパラメータ・マーカーを含んでいる式を1つ以上持つ基本述部（基本述部には、「=」「<>」「<」「<=」のような単純な比較は含まれますが、「IN」「BETWEEN」「IS NULL」などは含まれません）
ホスト変数またはパラメータ・マーカーを含んでいるMATCH式、述部式、エスケープ式のいずれかを持つLIKE述部

　選択性の値は、「0」から「1」までの範囲のリテラルの数値でなければなりません。選択性を指定しない場合は、デフォルト値が使用されます。「0.01」という値は、表の中の1％以外の行が述部によって除外されると期待することを意味します。ただし、選択性の指定は、最後の手段と考えるべきです。

SELECT c1, c2, c3, FROM T1, T2, T3

  WHERE T1.x = T2.x AND

    T2.y = T3.y AND

    T1.x >= ? selectivity 0.000001 AND

    T2.y < ? selectivity 0.5 AND

    T3 = ? selectivity 1

リスト6　選択性の指定の例

DB2 V8 ドキュメンテーション

参照情報→SQL→言語エレメント→述部→検索条件

編集局：第6回は「詳細なSQL分析」を扱いました。次回は「継続的なメンテナンス」「データベース・パーティショニング・フィーチャー（DPF）のパフォーマンス」を取り上げます。

著者紹介

Fraser McArthur

Fraser McArthur氏は、分散プラットフォーム（Windows／UNIX）用のDB2 UDBを開発しているIBMトロント研究所のコンサルタントです。同氏はData Management Partner Enablement organizationのメンバーであり、IBMビジネス・パートナーとともに、DB2へのアプリケーションの移行とパフォーマンス・チューニングに取り組んでいます。また同氏は、DB2管理とアプリケーション開発の両方におけるDB2 Certified Solutions Expertです。

「次回」へ

「DB2チューニング・ベストプラクティス」バックナンバー