第6回から大分期間が空いてしまい申し訳ありません。
個人的な事情により、執筆の時間が取れませんでした。やっと落ち着いてきましたので、今月から連載を再開しますので、よろしくお願いします。
それでは、今回はメモリチューニングについて説明します。その中でも、最もチューニングが難しい共有プールについて説明します。最近は、第3回目で紹介した自動メモリ管理や自動共有メモリ管理を使用すれば自動的に設定してくれるからか、基本メカニズムをあまり知らない方も多いようですね。
■1．共有プールについて
先ずは共有プールのメカニズムから説明しようと思います。いくつかのトピックはご存知ですが、完全に把握できている方は少ないようです。共有プールには、SQL文、パッケージ、オブジェクトの情報等を保持するライブラリ・キャッシュ、ディクショナリ・キャッシュ、結果キャッシュ（Oracle Database 11gから）などの領域があります。バッファ・キャッシュと違って以下のように複雑な管理を行います。
- 割り当てるサイズが一定でない。
（数バイトのものから数キロバイト以上のものまで） - 領域は利用し終わっても解放されない。
情報を共有すること（再利用されること）を最大の目的としているため（どの情報を共有すれば有益かを判断できないため全てを対象にしています）、領域不足になるまでは解放されません。 - メモリ不足になってもディスクに退避されない。
ディスクなどに退避すると、そのチューニングが大変になってしまうので、再構築可能なものは破棄され、必要になった際に再構築を行います。そのため、メモリが不足するとCPU消費が多くなります。とはいえ、単純に共有プールサイズを大きくしても意味がありません。共有できないSQL文が多いのに共有プールサイズを大きくしても、逆に割り当て可能なメモリを探すことに時間がかかってしまう場合もあるからです。
このような問題に対応するために、共有プールの管理方法は非常に複雑になっています。
（1）共有プールの獲得及び参照
共有プールの獲得や解放を行う場合には、操作が複数プロセスから同時に行われないようにshared poolラッチの獲得を行います。これは、メモリ上でのデータ整合性のためです。そのため、共有プールの獲得や解放を行うプロセス数が多くなると、shared poolラッチの獲得競合により待機が発生する可能性が高くなります（Oracle9iからは、共有プール・サイズとCPUコア数によって最大7個のヒープに分割するようになり競合が軽減されています）。
SQL文解析時のハード・パースにはlibrary cacheラッチ（Oracle Database 11gからは効率が良いmutexメカニズムを使用しています。そのため、より細かい単位で排他制御が可能になり待機が少なくなります）が獲得されます（ソフト・パースもSQLカーソルを探すときに獲得しますが、ハード・パースの方が獲得する時間が長くなります。これについてはここでは省略します）。このように、ラッチが獲得されると並列性が低下しますので、できるだけ少なくした方がパフォーマンスが良いことになります。そのため、ハード・パースはできるだけ避けたい訳です。
（2）共有プールの断片化
共有プールは、割り当てるサイズが一定でなく、更にメモリの解放時期を指定できないので断片化が発生し易いです。割り当てと開放を繰り返していると断片化が発生してしまいます（小さいサイズの領域が飛び飛びに割り当てられていて、大きいサイズの領域が割り当てられなくなるためです）。例えば、領域がA,B,C,D,Eの順に割り当てられていた場合に、領域BとDを解放すると図のように連続しない状態になります。これが領域A,B,Cなどの連続領域を解放できれば良いのですが、再利用状況（頻繁に再利用されている領域は解放されない）によるのでそのように調整はできないからです。
そのため、断片化を回避するために予約領域があり、初期化パラメータSHARED_POOL_RESERVED_SIZE（デフォルトはSHARED_POOL_SIZEの5％）とSHARED_POOL_RESERVED_MIN_ALLOC（Oracle8iから廃止になり4400バイト固定になっています）で指定します。これは、小さいサイズで大きな連続領域を使用しないようにして（SHARED_POOL_RESERVED_MIN_ALLOCより小さいサイズは予約領域を割り当てないようにして）、大きなサイズを割り当てできないことが無いようにします。
（3）共有サーバ接続について
共有サーバ接続を使用すると（最近では搭載するメモリ容量が増えたので使用する機会は少なくなったと思いますが・・）、ラージプールを指定しないとソート領域などのユーザ・グローバル領域(UGA)が共有プール内に存在するため、共有プールに負荷がかかり更に難しくなりますので注意が必要です。ラージプールを用意することによって、UGAの領域の大部分はラージプールから獲得されますが、ラージプールからのメモリ割り当てもshared poolラッチを必要とします。共有サーバ接続を使用していてshared poolラッチでの競合が多い場合には、専用サーバ接続に変更する事によりパフォーマンスが改善する可能性があります。これは、専用サーバ接続ではUGAをラッチが必要でないPGAから割り当てるからです。
■2．共有プールのチューニング
共有プールのチューニングは意外と大変ですが、第3回で紹介した共有プール・アドバイス機能が便利です。
一般的には、最もパフォーマンスに影響するライブラリ・キャッシュに対して行います（アドバイス機能もライブラリ・キャッシュに対して行うようになっています）が、ライブラリ・キャッシュのサイズを明示的に指定できないので、難しいように感じるのだと思います。ライブラリ・キャッシュは、できるだけ最初に解析をしたSQLカーソルを再利用する（ハード・パースをしないようにする）のがベストな状態です。そのため、共有プールサイズをできるだけ大きくするか、以下のことに気を付けて行います。
- SQLを共有可能にする（バインド変数を使用する）。
- SQLカーソルの無効化をできるだけしないようにする。
- 別バージョンにしないようにする。
このようにハード・パースの割合を少なくすることで、ある程度のパフォーマンスは確保できますが、やはり長い間使用しているとメモリの使用効率が悪くなります。そのために、定期的に共有プールをフラッシュする必要があることも忘れないようにして下さい。
（1）バインド変数
SQLカーソルを共有するときは、SQL文にリテラルを使用せずにバンド変数を使用して下さい。第4回で説明した初期化パラメータCURSOR_SHARINGを使用することで、リテラルを使用していてもSQLカーソルを共有することもできますが、基本はSQL文を共有したいときはバインド変数を使用する、共有したくないときはリテラルを使用するようにしましょう。
それではどのような時に共有しない方が良いか説明します。
SQL文を共有できる場合でもすべてがベスト・パフォーマンスとは限りません。OLTPシステムのように、例えば一意索引を使用して検索するような単純なSQL文（一意索引を使用して検索するような）だとでは同一実行計画でも、（バインド変数を使用しても）た際の影響は小さい問題ないと思います。が、例えば、一意でないデータを使用したSQL文、アドホックなSQL文など複雑なSQL文（一意でないデータを使用したSQL文、アドホックなSQL文など）では、同じ実行計画でない方がベストな場合もあります。（例えば、以下のように電話番号は一意なデータなので実行計画は同じになりますが、郵便番号は重複データが多いので値によって実行計画が異なると思われます）。
このようなときは、バインド変数を使用しない方が良いです。
Oracle9iからの「バインド変数の先読み」機能では最初のハード・パースの時にバインド変数内の値を基に実行計画を作成しますが、2回目以降の実行では値をチェックされませんので全ての値に最適な実行計画とは限りません。（そこでOracle Database 11gからは「優れたカーソル共有」機能が提供されました。これはバインド変数の値をチェックして異なる実行計画が適切の場合には、子カーソルを生成して対応する機能です。子カーソルは後で説明します）。
これは、何も考えずにSQL文を作成しても良いと言うことではないので注意して下さい。あくまでも、そのように作成されたプログラムを修正せずに（修正することができない場合でも）最適にできますと言うことです。基本は、最初に言いましたように目的に合わせてリテラルかバインド変数を使用してプログラミングを行って下さい。また、すべてを共有するれば良いというものではないことを知っておきましょう。
（2）SQLカーソルの無効化（INVALID）
SQLカーソルはメモリ不足により上書きされるまで無くなることはありませんが、以下のメンテナンスを行うことによって、関連するSQLカーソルをINVALID（無効化）の状態にします。（他のアクセスパスが最適になる可能性やアクセスパスが存在しなくなる場合が発生するため）です。
このような場合にSQL文を実行すると再度ハード・パースが行われますので、実行するときは注意して下さい。（できれば、システムの負荷が高いときは実行しないようにしましょう）。
- テーブルの削除/TRUNCATE/ALTER TABLE 文/RENAME
- 索引の作成/削除/ALTER INDEX文
- オプティマイザ統計の収集・削除
- アウトラインの変更/削除
- SQLが参照しているビュー、順序、シノニム、ファンクションの変更/作成
（3）共有されないSQL
共有できるような同一SQL文でも共有されない場合があります。そのようなSQL文は別バージョンとして新たな子カーソルの実行計画が作成されます（カーソルは子カーソル毎に実行計画を作成できるようになっています。このとき統計情報のversion_countがアップされます）。StatspackやAWRの「SQL」セクションの「SQL ordered by Version Count」を参照するとversion_countの多い順に出力されますので、SQL文を確認することができます。以下が別バージョンとして扱われる代表的な理由になります（V$SQL_SHARED_CURSORを参照すると子カーソルを共有されない理由が分かります）ので、できるだけ別バージョンにならないように注意して下さい。
-
オブジェクトが異なる（アクセスするスキーマが異なる）。TRANSLATION_MISMATCH列=’Y’
- バインド変数の属性（型、長さ）が異なる。BIND_MISMATCH列=’Y’
- 初期化パラメータOPTIMIZER_GOALが異なる。OPTIMIZER_MISMATCH列=’Y’
（4）断片化の解消
断片化を回避するために予約領域を使用することで、ある程度防ぐことはできますが、限界があります。この予約領域が使用されない場合もありますのでV$SHARED_POOL_RESERVEDで確認が必要です。列FREE_SPACEの減少が見られない場合は、予約領域は有効に利用されていませんので初期化パラメータSHARED_POOL_RESERVED_SIZEを小さくして下さい。
最終的には以下のように共有プールをフラッシュして解消します。ただし、これを行うとシステムに負荷がかかりますので（shared poolラッチを獲得するため、その間はlibrary cacheラッチも獲得できません）、実行するときは注意して下さい。定期的にシステムの負荷が少ない時間帯に実行して下さい。
Oracle Database 10gR2からは、自動メモリ管理（自動共有メモリ管理）を使用すると内部的に断片化の発生を抑止するように管理してくれます。共有プールをサイズによって、いくつかのヒープ（サブプール）に分割して使用しています。このサブプールごとに同等の存続期間になるようにして断片化を防止しています。「共有プールの断片化」のところで解放時期が指定できないので断片化が発生すると説明しましたが、それを解消することができる訳です。
そのため、共有プールのフラッシュは実行する必要がなくなっていますので、システムに負荷がかかるフラッシュは実行しないようにして下さい。
（5）セッション・カーソル・キャッシュ
最後にあまり知られていないと思われるセッション・カーソル・キャッシュについて説明します。セッション・カーソル・キャッシュは、SQL文の解析処理においてSQLカーソルが存在するかを最初に確認する領域です。つまり、セッション・カーソル・キャッシュが多い方がパフォーマンスが良くなるということです。
SQL文の解析処理は以下の手順で行われます。
- 先ずはセッション・カーソル・キャッシュをアクセスします（ここで見つけられると再オープンも必要ない最少負荷で行うことができます）。
- なければライブラリ・キャッシュを探します（ソフト・パース）。
- 最後にSQL文を一から解析します（ハード・パース）。
最初のセッション・カーソル・キャッシュは、セッション毎にクライアント側にキャッシュするカーソル数です。初期化パラメータSESSION_CACHED_CURSORSで指定します（Oracle Database 11gからのデフォルトは50です）。セッション・カーソル・キャッシュは、ライブラリ・キャッシュで3回以上解析が行われたSQLカーソルをPGA(UGA)に格納します。全て使用済みであれば、最も古いものが上書きされます。カーソル・キャッシュのヒット数はstatspackの「session cursor cache hits」統計の値で参照できます（この統計値を参照して増減すると良いです）。「parse count(total)」と比較すると（効果が）ヒット率がわかります。以下のように計算するとカーソル・キャッシュ・ヒット率、ソフト・パース率、ハード・パース率を求めることができます。
- セッション・キャッシュ・ヒット率：session cursor cache hits / parse count(total) * 100
- ハード・ パース率：parse count(hard) / parse count(total) * 100
- ソフト・パース率：(parse count(total) – session cursor cache hits – parse count(hard)) / parse count(total) * 100
■3．おわりに
今回は共有プールについていろいろと説明しました。少し間が空いたので今回は書くのに多少苦労しました。次回も頑張りますのでよろしくお願いします。
それでは、次回まで、ごきげんよう。