津島博士のパフォーマンス講座 indexページ▶▶

津島博士のパフォーマンス講座 
第45回 TEMP領域について

■津島博士による解説が動画でも! セミナー動画公開中です。
津島博士のパフォーマンス講座「パフォーマンス問題はなぜ起きるのか」  【WMV】 【MP4】 【PDF

皆さんこんにちは、大分暖かく過ごしやすい季候になってきましたので、この原稿が公開される頃には関東でも桜が開花していますね。
最近では、サーバーの搭載メモリが非常に多くなりましたが、まだTEMP領域を使用することで、性能問題になっていることが多いように思います。そこで今回は、第36回の続きとしてメモリの使用を少なくする(TEMP領域の使用を削減する)方法について、これまで説明していなかったパーティションの使用を中心に説明しようと思いますので、参考にしてください。

1. PGAメモリのチューニング

まずは、PGAメモリのチューニング(TEMP領域の使用の削減)について説明しましょう。
作業メモリが不足するとTEMP領域を使用することで性能が低下しますが、そのときのチューニングに苦労されている方が多いのではないでしょうか。「自動PGAメモリ管理」を使用した場合には、第14回で説明したように「PGA Memory Advisory」を使用して、ソート処理やハッシュ結合などの領域(初期化パラメータPGA_AGGREGATE_TARGET)をチューニングしますが、このパラメータを増やせばすべて改善できる(TEMP領域のI/Oがなくなる)と誤解をされている方が多いように思います。そのため、効果的に改善する方法について少しまとめてみました。基本は、以下のような検討を上から順番に行っていきます。

  • パラレル度を増やす
  • 索引を使用する
  • パーティションを使用する
  • 手動PGAメモリ管理を使用する

(1)パラレル度を増やす
自動PGAメモリ管理を使用している場合は、一つのプロセスで使用できるメモリ・サイズに制限があるので、初期化パラメータPGA_AGGREGATE_TARGETを増やしても効果がない場合があります。そのため、メモリを多く使用する処理については、一つのプロセスに対してのデータ・サイズを小さくする必要があります。それを最も簡単に行うには、パラレル度を増やすことになるので、まずはパラレル実行を行う(またはパラレル度を増やす)ことを検討します。また、パラレル実行を使用しない場合は、プログラムを分割して実行する(または分割する数を増やす)ことを検討してください。

(2)索引を使用する
パラレル実行ができない場合は、索引を使用して作業メモリを使用しないようにします。
第36回などで説明したように、索引を効果的に使用することで、作業メモリの使用を削減するようにします。これは、同時実行が多いなどのリソースを多く使用できない場合には非常に有効ですが、効果的な索引を作成する必要があるので、慣れない方には少し難しいところがあります。第36回の「メモリの使用が少ないSQL」などを参考に行ってください。また、索引の数が多いや作成に時間が掛るなどで、すべての場合で使用できる訳ではありません。そのため、大量のデータを一つのSQLで処理することは、できるだけ行わないのも大事だということを忘れないでください。

(3)パーティションを使用する
索引を作成するのが難しい場合は、パーティション表の使用を検討します。
パーティション表は、パーティション・プルーニング以外にメモリ使用量を削減するためにも使用することができます(索引は、索引範囲スキャンによってソート処理などのメモリ使用をなくしますが、パーティションは処理を分割することでメモリの使用サイズを削減します)。第20回で説明したパーティション・ワイズ結合では、パーティション単位に処理することで、同時に使用するメモリ・サイズを削減できるようになります。これは、結合以外にも使用することができます。例えば、以下のようにGROUP BY句の列でパーティション表を作成することで、GROUP BY処理をパーティションごとに行うことが可能になり、それぞれのパーティション単位に作業メモリを使用するようになります。

tsushima-45-1

これにより、必要なメモリ・サイズは最大のパーティション・サイズになるので、パーティション数を多くするとより小さなメモリ・サイズにすることが可能になります。これは、GROUP BY以外のソート領域を使用する処理(ORDER BY、DISTINCT演算子、分析ファンクションなど)でも使用できるので、そのような処理ではパーティション化を検討してみてください。

(4)手動PGAメモリ管理を使用する
最後に、どれも行うことができない場合は、手動PGAメモリ管理を使用します。
どうしてもTEMP領域の使用を削減できない場合は、以下のように指定することで、そのSQLだけ手動PGAメモリ管理を使用することが可能になります。これで、初期化パラメータ(SORT_AREA_SIZE、HASH_AREA_SIZEなど)を使用して、それぞれのプロセスで使用するサイズを指定できるようになります。

SQL> ALTER SESSION SET WORKAREA_SIZE_POLICY=MANUAL;

ただし、「PGA Memory Advisory」が使用できなくなり、同時実行時のメモリ管理が難しくなるので、あまりお薦めすることができません。そのため、できるだけ使用しなくても問題ないようにしてください。

2. パーティション・ワイズ処理

ここからは、パーティション・ワイズ結合などのパーティション・ワイズ処理についてもう少し説明しましょう。
パーティション表は、パーティション・プルーニングなどのアクセスするデータの削減に有効な機能ですが、それ以外にパーティション・ワイズ処理(パーティションごとに処理を行う)にも効果的に使用できることを知っておくと便利です。特に、TEMP領域を多く使用する処理には有効なので、注意点などを含めて説明していきます。

(1)シリアル処理
シリアル処理では、それぞれのパーティションを順番に処理するので、パーティション・ワイズ結合以外に以下のようなソート領域を使用する処理でも使用することができます。このとき指定されている列のすべてでパーティションする必要はありません。また、ORDER BY以外の処理は、複数列の指定順を気にする必要はありません(例えば、GROUP BY c2,c1に対して列'c1'だけでパーティションしても問題ありません)。

  • GROUP BY処理(GROUP BYの列でパーティション化している場合)
  • DISTINCT演算子(選択リストの列でパーティション化している場合)
  • ROW_NUMBERなどの分析ファンクション(PARTITION BYの列でパーティション化している場合)
    この例では、ORDER BYにNULLを指定していますが、NULL以外でも同じように動作します(第24回で説明したように、PARTITION BYの値ごとにソートするからです)。
  • ORDER BY処理(OREDER BYの列の指定順にレンジ・パーティション化している場合)
    この例では、列'c1'のレンジ・パーティションに対してORDER BY c1,c2を行っています。
tsushima-45-2

この実行計画は、パーティション表't1'(列'c1'でレンジ・パーティションしています)に対して実行したものですが、この処理はPARTITION RANGE ALL(RANGEの部分がパーティション・タイプになります)などが出力されているかで確認します。それぞれの実行計画のように、赤い四角い枠内の操作(PARTITION RANGE ALLより下でインデントが深い操作)がパーティション単位に行われることを意味します。

(2)パラレル処理
シリアル処理では、パーティションを順番に処理していきますが、パラレル実行を行うと複数のパーティションを同時に行うことができます(パラレル度は、パーティション数と同じである必要はありません)。ただし、パラレル実行では、シリアル処理とは異なり、結合とGROUP BYだけしか動作しません。また、パーティション数がパラレル度よりもある程度多くないと、動作しない場合もあるので注意してください(この例では、4つのパーティションに対してパラレル度を2で実行しています)。
以下の実行計画のように、PX PARTITION RANGE ALL(それぞれのパーティションがPQプロセスに分割される)などが出力されているかで確認します。シリアル処理と同じように、赤い四角い枠内の操作(全表スキャンとHASH GROUP BY)がパーティション単位に行われます。

tsushima-45-3

DISTINCT演算子やROW_NUMBERなどの分析ファンクション(WINDOW SORT)では、パラレルでは実行されないので、できるだけGROUP BY句を使用するようにしてください。例えば、以下のようにDISTINCT演算子を行うとPX BLOCK ITERATOR(ブロック単位の分割)になるので、パーティション単位には動作しません。

tsushima-45-4

パーティション数がパラレル度よりもっと多くなると、以下のようにPX PARTITION RANGE ALLが出力される場合がありますが、その下にHASH UNIQUEが出力されないので、このときもパーティション単位には動作していないので、注意してください(全表スキャンだけがパーティション単位の動作です)。

tsushima-45-5

そのため、メモリ・サイズを削減したい場合には、以下のようにGROUP BYを行うようにしてください(第20回で説明したように、DISTINCT演算子はGROUP BYに置き換えることができます)。このように目的によってSQLを使い分ける必要があることも知っておいてください。

tsushima-45-6

それから、第20回の「パーティション・ワイズ結合」では説明していませんでしたが、パーティション・ワイズ結合が行われるのは等価結合の場合だけです。また、結合列のすべてでパーティション化する必要もありません(以下の例は、パーティション列'c1'に対して列'c1'と'c2'で結合しています)。ご存知だと思いましたが念のために載せておきます。

tsushima-45-7

(3)組合せの効果
最後に、処理を組み合わせた場合の効果について説明します。
パーティション・ワイズ処理は、メモリを使用する処理が単独で動作するときだけに効果がある訳ではありません。そのため、同じ列でメモリを使用する処理を行っている場合には、すべてでパーティション・ワイズ処理を行わせることができます。
例えば、パーティション化された列で結合とGROUP BYを行う場合でも動作します(以下の実行計画では、PARTITION RANGE ALLの下にHASH GROUP BYとHASH JOINが出力されているので、パーティション単位に動作していることが分かります)。

tsushima-45-8

ただし、以下のようにビューが存在すると(ビュー・マージされないと)、パーティション・ワイズ処理は動作しないので注意してください(上記の例では、ビュー・マージするようにMERGEヒントを使用しています)。

tsushima-45-9

パラレル実行でも動作しますが、データ分割をブロードキャストなどが選択されると動作しません(パラレル・パーティション・ワイズ結合が選択されないと動作しません)。そのような場合は、第20回で説明したPQ_DISTRIBUTEヒントを使用してください。

tsushima-45-10

このように少し注意するだけで使用できますが、パーティションは一つのテーブルにいくつも指定することができません(コンポジット・パーティションで二つまでです)。そのため、他のSQLも含めて最適なパーティションを決めるようにしてください。

3. おわりに

今回はTEMP領域について説明しましたが、少しは参考になりましたでしょうか。これからも頑張りますのでよろしくお願いします。質問をお待ちしています。
それでは、次回まで、ごきげんよう。

津島博士のパフォーマンス講座 indexページ▶▶