不要なオブジェクトは解放する

 次のトラブルは,1台のWidnows NTサーバー内に6個のインスタンスを稼働させているケースで発生したものだ。このサーバーの主な構成は,(1)サーバー内に6つのインスタンスが共存している。(2)サーバーには1Gバイトのメモリーを搭載してあるため,1インスタンス当たりのシステム・グローバル領域(SGA)*を100Mバイト前後になるように設定,(3)初期化パラメータ・ファイルの設定内容は,SHARED_POOL_SIZE,DB_BLOCK_BUFFERSの値は少し異なるがほぼ同じ――である。

 このようなサーバーで稼働中のOracleに対して,Visual Basicで開発したクライアント・アプリケーションを実行したところ,以下のORA-04031のエラーが出力され,処理が続けられなくなってしまった。

ORA-04031:共有メモリーの8192バイトを割り当てできません。

 このアプリケーションの処理は,画面から入力したレコード・キーを基にビュー*によって複数のテーブルからレコードを抽出し,対象レコードを画面に表示する,というシンプルなものだ。一般的にORA-04031は,共有プール領域*のメモリー不足によって発生するエラーである。

 共有プール領域は,LRUアルゴリズム*(と同等の方法)で管理されている。そのため,共有プール領域にエリアを確保する必要が生じた場合,基本的には参照頻度の低いオブジェクトから解放され,新たなオブジェクトのために空きエリアが確保される。つまり通常,共有プール領域はリサイクル(再利用)されるため,メモリー不足によるこのようなエラーは発生しないはずである。

図2●キャッシュ中のオブジェクトを調べるステートメント

 このトラブルの原因としては,大きなサイズのパッケージといったオブジェクトが,共有プール領域にエリアを確保しようとした際,そのパッケージに相当する大きさのエリアが解放できない状況が発生していることが考えられる。また,明示的にKEEPを指定してあるオブジェクトが,共有プール領域内に多過ぎる場合にも,このような状況が起こり得る*1

 このトラブルに対しては,以下の方法で調査して対処する。(1)共有プール領域の状態を調べる:スクリプトを実行し,キャッシュされているオブジェクトを調査(図2[拡大表示])。各オブジェクトの占有バイト数と率が表示されるので,キャッシュから解放してもよいユーザー・オブジェクトを探索する。また,DBMS_SHARED_POOL.SIZESプロシージャを使用して,しきい値以上の領域を確保しているパッケージが存在することも確認できる。使用方法は“DBMS_SHARED_POOL.SIZES(minsize);”である。

図3●共有プール領域の使用状況を調査するステートメントの例

 (2)オブジェクトを共有プール領域から解放する:(1)の調査で,解放してもよいユーザー・オブジェクトが特定できた場合,DBMS_SHARED_POOL.UNKEEPプロシージャを使用して,該当のオブジェクトを解放できる。使用方法は“DBMS_SHARED_POOL.UNKEEP('owner.object_name');”。

 (3)初期化パラメータの調整:同様のトラブルの再発を防ぐために,初期化パラメータを調整する。調整の候補となるパラメータは,SHARED_POOL_SIZE, SHARED_POOL_RESERVED_SIZE, LARGE_POOL_SIZEである。共有プール領域の使用状況は,図3[拡大表示]に示したようなSQL文を実行することで把握できるので,この結果を見ながら調整する。

実行計画が共有プール領域を圧迫

図4●共有プール領域の空きメモリーを確認

 Oracle8iにおいては,オプティマイザ*がSQL文の実行計画を作成する際に,多数のプラン(PLAN)を検討し,結果として多くの共有プール領域を消費してしまい,トラブルを引き起こすことがある。この事象が発生してしまう条件としては,(1)多数の表を結合したSQL文であること(個別のSQL文に依存するので,表の数に明確なしきい値は無い)。(2)検索実行中にV$SGASTATを確認すると,共有プール領域内の空きメモリー(Free Memory)が減少していく状況を確認できることである(図4[拡大表示])。

 このトラブルは,オプティマイザが比較検討する実行計画の組み合わせ数を無制限(デフォルト)にせず,以下の方法で上限値を設定することで対処できる。例えば,検討すべき組み合わせの上限値を2000個に設定するには,(1)初期化パラメータとして設定する場合:optimizer_max_permutations=2000,(2)セッション単位で設定する場合:“ALTER SESSION SET OPTIMIZER_MAX_PERMUTATIONS=2000;”,とすればよい。

(内藤 尚=システムコンサルタント オープンシステム統括部 マネージャー)