SQLチューニングするときの心得

ここでは、SQLチューニングするときの心得を紹介します。

私も完成された人間ではないので、全て完璧な答えがある訳ではありませんが、遅いSQLを見たとき、チューニングするときにいくつかの確認ポイントを持っていますので、紹介します。

全表検索・テーブルフルアクセスがないか

まず実行計画を見て、テーブルフルアクセス・フルスキャンになっているところがないか確認します。

もしテーブルフルアクセスがあれば、まずはポイントになります。フルアクセスでも、データが数件とか数百件のレベルであれば遅くないこともありますのでよく確認しましょう。

テーブルフルアクセスを見つけたら、テーブルフルアクセスがSQLの時間がかかっている原因になっているか確認しましょう。例えば、table1とtable2を結合するとして、どちらもフルアクセスになっており、それをネステッドループやハッシュジョインしようとしてしてるから遅いとか、table1のデータ件数が百万件で、かつtable2のデータも数十万件あるとか。

時間がかかる納得の理由があるはずなので、きちんと原因を突き止めましょう。原因を突き止めたら、その原因を解消するためのチューニングを施せばよいだけです。SQLが遅くなる原因については「SQLが突然遅くなる原因と対応方法」で少し紹介していますので参考にしてください。

適切なインデックスが使われているか

次にインデックスが使われているケースです。インデックスが使われていても遅いケースって結構存在します。それはなぜか。

そのSQLで抽出しようとしている条件に合致した適切なインデックスが使用されていないことにより、遅い場合があります。または。適切なインデックスがないために、多少項目が違うインデックスを使用しているケースがあります。

抽出条件と使われているインデックスの項目を比較して適切かどうか確認しましょう。適切なインデックスの項目については、「インデックスで指定する項目の順番」で少し解説していますので、参考にしてください。

MERG JOIN CARTESIANがないか

実行計画の中に、マージジョインカーテシアンと言う文字がないか確認してみましょう。これはいわゆる直積というやつです。つまりtable1とtable2を無条件に全行x全行の組み合わせを作ってしまうという結合の方法になります。

もちろん、これが有効なケースもあります。例えば、データ件数が数件のテーブル同士を結合する場合や一方のテーブル件数が数件の場合などはとても高速に処理されます。しかし、データが数十万件程度のテーブル同士を結合するとなると、それなりに時間がかかります。

このマージジョインカーテシアンですが、意図せず発生しているケースが多いです。例えば次のようにtable1とtable2を結合する場合です。プログラムミス、コーディングミスにより、結合条件が漏れているケースでよくこのマージジョインカーテシアンが発生します。こんなSQLでもORACLEオプティマイザは愚直に実行しようとしますので、まずは結合条件がきちんとあるか確認してください。

select
  a.*
from
  table1 a
  ,table2 b

SQLが複雑ではないか

これはかなり重要ですね。やたらとWITHが多いSQLだったり、副問合せやサブクエリーが多かったり、その副問合せの中で集計関数が呼ばれていたり、EXISTSがネストされていたり。その辺りのSQLのチューニング方法は「WITHを使って遅い場合のチューニング方法」、「EXISTSを使って遅い場合のチューニング方法」、「副問合せをたくさん使うSQLをチューニングする方法」を参考にしてください。

よく聞くのが、参照しているテーブルが5つ以上になってくると、ORACLEは適切でない実行計画を作る確率が飛躍的に上がるそうです。これは経験測からも同じことが言えます。

もちろんコーディングしている方からすると10や20は平気かもしれませんが、ORACLEオプティマイザからするとカンベンしてほしいのです。SQLを簡素にしたり、参照テーブルを減らすことは性能を上げるために結構必要な場合が多いです。私もよくSQLの分割やプログラムの見直しをします。逆に言うとシンプルなSQLで、適切に分割していることが性能向上への近道なのかもしれません。