ソート・パフォーマンスのチューニング

照会において、ソートまたはグループ化された結果が必要になることがよくあるので、 ソート・ヒープを正しく構成することは、照会で高いパフォーマンスを得るためにきわめて重要です。

次のような場合に、ソートが必要になります。
  • 要求された配列を満たす索引がない場合 (例えば、ORDER BY 節を使用する SELECT ステートメント)
  • 索引はあるが、索引を使用するよりもソートを行う方が効率が良い場合
  • 索引が作成される場合
  • 索引がドロップされることにより、索引ページ番号がソートされる場合

ソートに影響を与えるエレメント

ソート・パフォーマンスに影響を与える次のような因子があります。
  • 次の構成パラメーターの設定値:
    • ソート・ヒープ・サイズ (sortheap)。各ソートに使用するメモリー量を指定します。
    • ソート・ヒープしきい値 (sheapthres)、および共有ソートのソート・ヒープしきい値 (sheapthres_shr)。インスタンス全体でソートに使用できるメモリーの合計量を制御します
  • 大量のソートを必要とするワークロードにおけるステートメントの数
  • 不要なソートを避けるのに役立つ索引の存在と不在
  • ソートの必要を最小限に抑える機能がないアプリケーション論理の使用
  • 並列ソート。これはソートのパフォーマンスを向上させますが、ステートメントがパーティション内並列処理を使用する場合しか行えません。
  • ソートがオーバーフロー するかどうか。 ソートされたデータがソート・ヒープ (ソートの実行ごとに割り当てられるメモリーのブロック) に収まらない場合は、データベースが所有する一時表にデータがオーバーフローします。
  • ソートの結果がパイプ接続 されるかどうか。 ソートされたリストを保管する一時表を使わなくてもソートされたデータを直接返せる場合は、パイプ・ソートになります。

    パイプ・ソートでは、アプリケーションがそのソートに関連したカーソルをクローズするまで、ソート・ヒープは解放されません。 パイプ・ソートはカーソルがクローズされるまでメモリーを消費し続けることができます。

ソートはソート・メモリー内で全体的に実行されますが、 これは過度のページ・スワッピングを引き起こす可能性があります。 このような場合、ラージ・ソート・ヒープの利点が生かされません。 そのため、ソート構成パラメーターを調整するときには、 オペレーティング・システム・モニターを用いて、 システム・ページングの変更を追跡してください。

ソート・パフォーマンスの管理技法

ソートが重大なパフォーマンス問題となっている特定のアプリケーションおよびステートメントを識別します。
  1. イベント・モニターをアプリケーションおよびステートメントのレベルでセットアップして、 ソート合計時間が最も長いアプリケーションを識別します。
  2. そのようなアプリケーションのそれぞれにおいて、 ソート合計時間 が最も長いステートメントを見つけます。

    Explain 表を検索して、 ソート操作が行われている照会を識別することもできます。

  3. これらのステートメントを設計アドバイザーへの入力として使用します。 これにより、ソートの必要を減らす索引が識別され、作成できます。
セルフチューニング・メモリー・マネージャー (STMM) を使用すると、ソートに必要なメモリー・リソースを、自動的かつ動的に、割り振りおよび割り振り解除できます。 この機能を使用するには、以下のことを行ってください。
  • self_tuning_mem 構成パラメーターを ON に設定して、データベースのセルフチューニング・メモリーを有効にします。
  • sortheap および sheapthres_shr 構成パラメーターを AUTOMATIC に設定します。
  • sheapthres 構成パラメーターを 0 に設定します。
また、データベース・システム・モニターおよびベンチマーク技法を使用して、sortheap、sheapthres_shr、および sheapthres 構成パラメーターの設定に役立てることもできます。 データベース・マネージャーごとに、またデータベースごとに、次のことを実行してください。
  1. 代表的なワークロードを設定し稼働します。
  2. 適用するデータベースごとに、 ベンチマーク・ワークロード期間中の次のパフォーマンス変数の平均値を収集します。
    • 使用中のソート・ヒープの合計 (sort_heap_allocated モニター・エレメントの値)
    • アクティブ・ソートおよびアクティブ・ハッシュ結合 (active_sorts および active_hash_joins モニター・エレメントの値)
  3. データベースごとに sortheap を使用中のソート・ヒープの合計 の平均値に設定します。
    注: ソートに長いキーを使用する場合は、 sortheap 構成パラメーターの値を増やす必要がある場合があります。
  4. sheapthres を設定します。 適切なサイズを見積もるには、次のようにします。
    1. インスタンス内のどのデータベースの sortheap 値が最大であるかを判別します。
    2. このデータベースのソート・ヒープの平均サイズを判別します。

      判別するのが困難である場合、最大のソート・ヒープの 80% を使用します。

    3. sheapthres を、アクティブ・ソートの平均数に、上記で算出したソート・ヒープの平均サイズを掛けた値を設定します。 これは、初期設定としてお勧めします。 次に、ベンチマーク技法を使用して、この値をより良いものにすることができます。

IBM® Data Server Manager は、照会ワークロードと呼ばれる単一 SQL ステートメントのパフォーマンスおよび SQL ステートメントのグループのパフォーマンスを向上させるためのツールを提供します。 この製品について詳しくは、 チューニングを参照してください。