こんにちは。SI部の吉原です。
システム開発において、悩まされる事の多いのがパフォーマンス(処理速度)だと思います。
今回はOracle Databaseを使用したプログラムにて発行するSQLについて、パフォーマンス改善のために意識する点を説明させていただきます。

共有プール

SQL実行時には、まずSQLの解析を行い、実行計画をたてます。
SQLの解析にて新しくたてられた実行計画は、 SGA(System Global Area)内にある”共有プール”というライブラリキャッシュ領域にキャッシュされます。
この”共有プール”とは、SQL文/パッケージ/オブジェクトの情報、シーケンス順序等を保持するキャッシュ領域となってまして
主に3つの領域に別れてそれぞれのデータのキャッシュをしております。

1.ライブラリ・キャッシュ

直近に使用されたSQL、PL/SQLの実行可能な(解析またはコンパイルされた)形式を格納しています。
つまり、SQLの実行計画などの解析された情報がここに格納されています。
この解析された情報は再利用する事が可能であり、再利用によって同じ処理の重複を抑える事ができます。

2.ディクショナリ・キャッシュ

SQLやPL/SQLが解析される際のデータ・ディクショナリの情報を格納します。
ここには、各種オブジェクト(表や列)の定義、権限ロールなどの情報が格納されています。
SQL実行時のテーブルの存在確認やアクセス権限有無などのチェックを行う 再帰的SQL の発生を最小限に抑える事ができます。

3.結果キャッシュ

Oracle 11gより追加された領域で、SELECT結果のレコード情報が格納されています。
この領域により、使用頻度の高いレコード情報を、複数セッション間で再利用する事ができます。


共有プールの領域にキャッシュされた情報は利用し終わっても、領域不足になるまで開放されません。
また、自動的にサイズが増減されるため個々の領域のサイズを設定することはできず、 全体サイズを SHARED_POOL_SIZE パラメータにより決定するのみとなっています。
よって、SHARED_POOL_SIZEの値を適切にを設定しなければパフォーマンス低下という事態となりますので、
共有プール自体のチューニングだけでなく、共有プールを意識したプログラミングも重要となります。
それでは、共有プールを意識したプログラミングについて説明していきます。

実行計画の共有化

SQLの解析処理には、[ハード解析]と[ソフト解析]の2種類があります。
この2つの解析は以下のようなものとなっています。


1.ハード解析

SQL解析に関連する全ての操作を1から処理していくもので、

  1. SQLの文法的チェック
  2. SQLの問合せの変換、アクセス・パス解析
  3. 実行計画生成
  4. 解析済みSQLの共有プールへのキャッシュ

といった処理を行っていきます。
要するに、実行計画を新たに生成する解析です。
たてた実行計画は、共有プールへ格納するので、キャッシュ領域の取得、データキャッシュも行います。

2.ソフト解析

共有プールにキャッシュされている実行計画の再利用を行う解析となりまして、
解析済SQLの中から一致するSQLを探索し、一致するSQLの実行計画をそのまま利用します。
ハード解析で行っていたSQL解析、実行計画生成を行う必要がないため、CPUと共有プール使用率の負荷が軽くなります。

つまり、ハード解析の割合を減らし、ソフト解析の割合を多くすることで、共有プールにキャッシュされている実行計画の共有化の比率が大きくなります。
その結果が、CPU負荷軽減と、SQL解析部分のパフォーマンス改善に繋がるのです。

では、実際にどのようなプログラミングを実装することでソフト解析へ繋がるのか説明していきましょう。

ソフト解析へ繋がるプログラミング

1.ソフト解析

ソフト解析が行われるには、同一のSQLの解析結果が存在していることが条件となります。
解析結果の再利用が行われるタイミングは、以下のようなイメージとなります。


soft_parse_02
①初回のSQL実行であるため、共有できるものがなくハード解析が行われる
②共有SQL領域内に同一SQLが存在するため、ソフト解析となり、実行計画の共有が行われる
③全て小文字のSQLであるため、キャッシュ済のSQLに一致せず、ハード解析される
④WHERE条件の条件値が異なるため、キャッシュ済のSQLに一致せず、ハード解析される


このようにソフト解析を実行するためには、条件値まできちんと同じにしないといけません。
しかし、条件値までが全く同じであるSQLは多くはないでしょうから、ソフト解析を行う回数が少ない状態となってしまいます。
これではOracle側もソフト解析を提供する意味がありません。

そこでOracleでは”バインド変数”を利用することで、共有SQL領域にあるSQLに対し、より高い確率で一致するものを発見できる機能を提供しています。
バインド変数を使用することで解析結果の再利用は、以下のように変わります。


soft_parse_01
①初回のSQL実行であるため、共有できるものがなく、バインド値に基づいたハード解析が行われる
②WHERE条件の条件値は異なるが、同一SQLの存在とし、ソフト解析となり、実行計画の共有が行われる
③全て小文字のSQLであるため、キャッシュ済のSQLに一致せず、ハード解析される
④バインド変数の属性が異なるため、キャッシュ済のSQLに一致せず、ハード解析される


このように

  • 大文字/小文字を区別する
  • バインド変数の属性(型、長さ)を統一する

のルールを意識してバインド変数を利用することで、ソフト解析の使用比率が高くなります。

また CURSOR_SHARING という機能を利用する事で、 リテラルSQLに対してもバインド変数を使用したSQLと同等の効果を得る事も可能です。

[プログラムの発行SQL]
SELECT * FROM tbl01 WHERE col01 = 4000 ;

[Oracleで解析されたSQL]
SELECT * FROM tbl01 WHERE col01 = :SYS_B_1 ;

リテラルで記述されたSQLに対しても、Oracle側でバインド変数と置換して解析を行ってくれます。
バインド変数化していないプログラミングを行っている箇所については、実行計画の共有化の切替を行う事ができます。

しかし、バインド変数を使用するには、いくつかの注意点があります。

2.バインド変数の注意点

それは、Oracle 9iから提供されているバインドピーク(Bind Peek)機能により
初回のハード解析時のバインド変数値より最適な実行計画をたててくれるのですが、それを常に利用し続けてしまうという点です。

例えば、ハード解析時の条件値では フルスキャン が有効であったため、そのように実行計画をたてられたとします。
以降、 インデックススキャン が有効である条件値であったとしても、常に フルスキャン をしてしまいます。
これにより、バインド変数の使い方によっては、安定したパフォーマンスを維持する事ができなる可能性もあります。
また、ハード解析時の条件値が例外的であった場合、大部分のパフォーマンスの劣化を招いてしまうといった事態も起こり得ます。

バインドピーク機能をOFFにする事で、条件値に関わらず一定の実行計画をたてる事も可能ですので
例外的な条件値に対する実行計画を使いまわす事は回避できますが、それでは実行計画の最適性が薄れてしまいます。
そのため、バインド変数を利用するには慎重な判断が必要でもあります。

このようにバインド変数を利用したソフト解析は効果的ではありますが、考慮すべき点も多いです。
そこでOracle 11gからは、バインドピークの改善とも言える機能が提供されています。

3.Adaptive Cursor Sharing
Oracle 11gより Adaptive Cursor Sharing という機能が提供されています。
この機能により、バインド変数の値が既存の実行計画では不適切だと判断した場合、新たに実行計画を生成してくれます。
つまり、従来ではバインド変数を利用したSQLと実行計画は1対1の関係でしたが、1対多の関係へと変わっています。

キャッシュされた解析済SQLと実行計画は、以下イメージのような関係となります。


adp_cur_shar_01

  • 1つの解析済SQLに複数の実行計画が紐づいている
  • 紐づく実行計画は、バインド値に応じた実行計画が選択される
  • 実行計画ごとのバインド値は、SQL実行の度に選択性範囲の計算を行い定めていく
  • キャッシュされている実行計画のパフォーマンスが悪かった場合、新しい実行計画が生成される

初回ハード解析時には1つの実行計画しかキャッシュされていませんが、
SQLの実行時に、条件値の選択率計算を行い、実行計画に対する条件値の範囲を広げていきます。
そして、キャッシュされている実行計画を利用した実行結果のパフォーマンスが著しく悪い場合、新しい実行計画を生成します。
このようにして、徐々にバインド値に応じた適切な実行計画が選択されるような状態へとなっていきます。
Adaptive Cursor Sharing 機能により、従来のバージョンに比べてバインド変数を使いやすくなったので、
Oracle 11g以降を利用するシステムにおいては、バインド変数を活用できる機会が増えると思います。

しかし、実行計画を新しく生成するタイミングは、著しいパフォーマンス劣化が発生した後となるため
少なくとも一度は実行計画が不適切である性能劣化が発生してしまいます。
そこの部分の注意は必要となります。


まとめ

今回は、パフォーマンスを意識したプログラミングにおける基本的概念について説明させていただきました。
バインド変数を利用する事で、実行計画の生成を省略し、パフォーマンス改善へと繋げる事は可能です。

但し、この機能にはデメリットもあるので、そこを考慮する事が重要となります。
便利な機能であっても安易に使うだけではパフォーマンス劣化に繋がることもありますので、慎重な判断をしたうえで機能を活用して貰えればと思います。
特に大量データを扱う処理においては、不適切な実行計画によるパフォーマンス劣化は、致命的となってしまいます。
ですので、バッチ処理などの大量データを取り扱う可能性がある機能においては、バインド変数は使用しないのが安全ではあります。

パフォーマンス改善には様々な手法がありますので、 機会があれば、別のパフォーマンス改善の手法について紹介させていただければと思います。