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

津島博士のパフォーマンス講座 
第36回 遅くなるSQLについて

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

皆さんこんにちは、関東でも桜が開花してやっと春らしい気候になってきましたね。たまには花見などの気分転換も良いものですよ。この原稿が公開される頃にはもう桜は散っているかもしれませんね。
最近、リソース(メモリなど)の不足により、SQLの実行が終わらないのを目にしました。効果的なSQLにすることで、リソースを有効活用して高速にしますが、データ量によってはリソース不足で急に遅くなってしまうからです。そのため、少量のリソースで効果的に処理するSQL(プログラム)も重要なことを知って欲しいと思い、今回はリソース問題になるSQL(遅くなるSQL)について説明しますので、参考にしてください。

1. リソース問題になるSQLについて
これまでは、大量データを処理するような場合に、パラレル実行などでリソースを効果的に使用することで、高速化するようにしてくださいと説明してきましたが、これはリソース不足にならないことが前提です。一つのSQLにすることで、まとめて処理できて高速化しやすくなりますが、大量のメモリ領域が必要になる場合もあります。これは、処理するデータ量に依存することもそうですが、処理する内容に依存する場合もあるので、どのような処理がメモリを多く使用するかなども知っておくことで、SQLチューニングなどの参考にすることができます。
メモリ不足以外に、多重度が多すぎてI/O競合が多発することで、ストレージのリソース不足なども発生しますが、リソース・マネージャなどで制御できるので、ここでは触れないことにします。

(1)メモリを多く使用するSQL
まずは、メモリを多く使用するSQLについて説明します。
SQLを実行すると基本は効果的にリソースを使用して高速にしますが、リソースが不足すると急激な性能低下になります。その主な原因は、メモリが不足することで発生します。このメモリ不足になるのは、以下のようなメモリを多く使用する処理を行うときです。このメモリ領域は、処理が終了するまで必要になるので、一緒に使用すると更にメモリ不足になりやすくなります。

  • GROUP BY句やORDER BY句などで使用するソート領域(SORT_AREA_SIZE)
    すべてのデータを処理するまで結果を作成できないので、そのデータの格納領域として使用します。
  • ハッシュ結合で使用するハッシュ領域(HASH_AREA_SIZE)
    同一データを見つけるために、表結合が終わるまでハッシュ・テーブルの領域として使用します。
    ハッシュ結合は、第31回の「MERGE文の補足について」で説明したように、結合する片方のデータが少なければメモリの心配は必要ありません。ただし、3テーブル以上の結合になると結果もメモリに格納するので、この結合結果によってメモリ使用量が変わってきます(ハッシュ・テーブルと結合結果が格納されるのはそれぞれ1つだけです)。

オプティマイザ(CBO)は、このメモリ領域(自動PGAメモリ管理のときはPGA_AGGREGATE_TARGET)も使用して実行計画を作成するので、それぞれの状況によって最適な実行計画になります。ただし、オプティマイザ統計の問題、または索引がないなどで最適な実行計画にならない場合もあるので、手動でSQLチューニングすることも必要になります。
例えば、「本日の店舗別商品別売上と過去でそれぞれの売上があった最新日付を出力する」などのような処理を行う場合は、過去の購入履歴から求めるので、履歴が2年間とすると多くのメモリを必要とします。このような処理で注意するのが、過去に同じ条件の購入履歴がどのくらい存在するかです(つまり、リピート客が多いかです)。これが多いとGROUP BYなどで多くのデータが削減されるため中間データは少なくなりますが、そうでないと履歴データと同じサイズぐらいの中間データになってしまいます。このような処理などは、使用する機会が多いと思うので、データ量の増加で急に遅くなるなどがないように注意してください。
この処理を(本日は'2014/04/01'として)簡単なSQLにすると以下のようになります。最初にインライン・ビュー'B'(赤字の部分)で、過去の購入履歴から店舗別商品別顧客別売上の最大日付を求めています(これがリピート客が少ないと大きなサイズになるということです)。実行計画を見ると'Used-Mem'の出力から'HASH GROUP BY'と'HASH JOIN OUTER'でメモリを使用しているのが分かります。
この実行計画は、分かりやすいように第32回で説明したDBMS_XPLAN.DISPLAY_CURSOR関数を使用して、実行時の統計も含めた実行計画にしています。項目'Used-Mem'が使用メモリ・サイズになり、カッコ内の数字'n'が処理方法(マルチ・パスの回数など)になります。これが出力されない場合には、メモリを使用していないことになります。

tsushima-36-1

そして、メモリ領域が不足するとTEMP領域を使用して処理を続けますが、このときデータに対してメモリ領域が小さ過ぎると、処理時間が何倍にもなってしまいます(TEMP領域へのI/Oがマルチ・パスになるからです。詳細は「ソート処理のマルチ・パス動作について」を参照してください)。これがメモリ・チューニングで対応できるレベルであれば良いのですが、そうでない場合にはSQL(プログラム)の変更などを検討する必要があります。これが簡単ではない方も多いと思うので、これ以降でSQLチューニングの参考になることを説明していきます。

ソート処理のマルチ・パス動作について
ご存知ない方のために、ここでソート処理のマルチ・パス動作について簡単に説明します。
ソート処理などは、基本はメモリ上で処理を行いますが、以下のようにメモリが不足するとTEMP領域に退避して処理を続けます。ただし、この時点ではソート領域内だけのソート結果になっているので(ここでは、これを中間ソート結果と呼びます)、最終的にこの中間ソート結果をマージ(一つのソート結果に)する必要があります。このマージ処理をするときに、データに対してソート領域が小さいと一回で行うことができなくなります(そべての中間ソート結果の一部をソート領域上に展開できないからです)。その場合には、中間ソート結果の数を少なくするために、ソート領域上に展開できる範囲で中間ソート結果をマージして、更に中間ソート結果を作成します。このときの結果もTEMP領域に出力する必要があるので、I/O数が増加することになります。これを最終的にマージできる数まで繰返すことで、処理時間が長くなってしまうのが、マルチ・パス処理になります。これを1回のマージで行うことを1パス処理と呼びます。
このマージの回数をできるだけ少ないようにする必要があるので、できれば1パス処理にするのが最適となります。このマルチ・パス処理は、ハッシュ結合でも同じように行われます。 tsushima-36-2

(2)メモリの使用が少ないSQL
次に、メモリを使用するのが少ないSQLについて説明します。
メモリ不足で処理が終了しないなどを防止するには、複数の処理に別けて対象データ量を多くしないようにすることが大事になります。そのようなことができない場合には、以下のようにすることも良い解決策になります。

  • SQLを分割して中間テーブルで対象データを少なくする
    対象データを少なくする以外に、索引を使用しやすくするのにも効果があります(結合とGROUP BYなどは同時に行えないので、GROUP BYなどで索引を使用できるようになります)。
  • ソート処理などを行わないSQLにする
    ソート処理などが本当に必要か再検討する、または索引を効果的に使用してソート処理を行わないようにします。
  • ネステッド・ループ結合にする
    索引を使用したネステッド・ループ結合にすることで、ハッシュ領域などを使用しないようにします。

これの代表的なことが、索引を使用してメモリ上に格納しないようにすることです。索引は、第6回で説明したように、目的のデータに直接アクセスすることで、I/O数を削減して高速にすることができますが、それ以外にもソート処理などを回避するために使用することもできます。これを効果的に使用することで、メモリ使用量を少なくすことができます。ただし、索引アクセスすることでパラレル化するのが難しくなるので、TEMP領域のI/Oが多いときより効果が大きい場合に行うようにします。
以下のSQLでは、「(1)メモリを多く使用するSQL」で使用した例に、「索引(ix_sale01)の作成」と「中間テーブル(w_sal01)を作成するSQLに変更」を行って、GROUP BYを回避しています(このような列の順番で索引を作成するとGROUP BYも回避することが可能になるので、このようなことも知っておくと便利です)。

tsushima-36-3

これは、中間テーブルを作成するときに索引を使用することで、ソート処理をする必要がなくなっています。実行計画に'SORT GROUP BY NOSORT'(ソート処理を行わないGROUP BY)と出力されているので、すべてのデータを処理しないと結果が出ないのがなくなり、途中結果をメモリに格納する必要がなくなっています(メモリを使用しないので、実行計画に'Used-Mem'が出力されていません)。ハッシュ結合のメモリについては、ここでは1日分であれば問題ないデータ量として、そのままにしています。また、'20140401'の全表スキャンも1日単位のレンジ・パーティションにすることで問題はなくなります。
このように索引を効果的に使用することで、無駄なソート処理などを削除することができるので、TEMP領域のI/Oが多くて実行時間が遅くなっている場合には(ソート処理がマルチ・パスになっている場合には)、このようなことを検討してみてください。

(3)テーブル・ファンクションで作成
最後に、テーブル・ファンクションを使用した例を説明します。
「(2)メモリの使用が少ないSQL」の例は、直近の1件だけを出力していますが、複数件出力したい場合には単純なSQLにはできなくなります(MAX関数では行うことができません)。また、中間テーブルの作成やI/Oコストも増加になるので、これも作成しないようにしたいです。そこで、第25回で説明したPL/SQLのテーブル・ファンクションを使用して、索引だけを使用するようにするのも良い方法です。テーブル・ファンクションを使用すると、第31回で説明したようにパラレル化することも簡単に行えるので、使いやすさもSQLとあまり変わりません(私は、最近テーブル・ファンクションが好きで、使用する機会が多いです)。
以下に、直近の2件を出力するサンプル・プログラムを作成してみました(指定日付の店舗別商品別顧客別売上を検索するカーソル'p1'のループ内で、直近2件を索引スキャンしています)。

tsushima-36-4

このテーブル・ファンクションに'20140401'を指定して、実行すると以下のようになります。これではどのように動作しているか良く分からないので、テーブル・ファンクション内で実行しているSQLについて見てみます。

tsushima-36-5

以下がテーブル・ファンクション内で実行しているSQLの実行計画です。索引スキャンしているのが分かります。索引スキャンが'INDEX RANGE SCAN DESCENDING'(降順レンジ・スキャン)になっているのは、索引のdtimeが昇順になっているのに対して、降順のソート(ORDER BY dtime DESC)を行っているからです。索引を作成するときに、dtimeに降順(DESC)を指定すると'INDEX RANGE SCAN'にすることができます。

tsushima-36-6

このように、リソースをあまり使用しないSQLが効果的な場合もあるので、このようなSQL(プログラム)も必要なことを忘れないようにしてください。

2. おわりに
今回は遅くなるSQLについて説明しましたが、少しは参考になりましたでしょうか。また機会があれば他のことについても説明したいと思います。これからもよろしくお願いします。質問をお待ちしています。
それでは、次回まで、ごきげんよう。

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