カーソル・エラーとオブジェクトの問題切り分け：Oracleパフォーマンス障害の克服（7）（2/2 ページ）

» 2005年03月12日 00時00分公開

オブジェクトをキャッシュに常駐させるテクニック

　前項でも述べたように、PL/SQLストアド・プロシージャやパッケージを利用するアプリケーションを作成する際、カーソルだけでなくPL/SQLのオブジェクトも効率的に利用しないと、データベース処理のボトルネックとなる可能性があります。

ライブラリ・キャッシュに保持されるオブジェクト

　第5回「SGAに起因するパフォーマンス障害を発見する」で説明したように、ユーザー要求によるSQL処理に利用される各オブジェクトは、SGA内のライブラリ・キャッシュに保持されます。要求されたPL/SQLストアド・プロシージャやパッケージが物理ファイルからロードされるわけですから、そのたびにディスクI/Oが発生します。頻繁にロードされるとここがボトルネックとなり、レスポンスに影響が出てしまいます。さらに共有プール内のメモリ・エントリが断片化され、別の問題が発生する要因ともなります。共有SQLおよび共有PL/SQL領域は、古くなるとLRUアルゴリズムによって共有プールから削除されるので、再ロード、再解析が行われないようにするために、サイズが大きく使用頻度の高いSQLまたはPL/SQLオブジェクトが、使用されなくなっても共有プールから削除されないようにすることが有効です。

　オブジェクトのキャッシュは、「V$DB_OBJECT_CACHE」で確認できます。このビューはライブラリ・キャッシュ内にキャッシュされるデータベース・オブジェクトを示します。オブジェクトには、表および索引、クラスタ、シノニム定義、PL/SQLプロシージャ、パッケージ、トリガなどがあります。

SQL> DESC V$DB_OBJECT_CACHE

リスト4　「V$DB_OBJECT_CACHE」を確認


列名	データ型	格納されているデータの内容
OWNER	VARCHAR2(64)	オブジェクトの所有者
NAME	VARCHAR2(1000)	オブジェクト名
NAMESPACE	VARCHAR2(28)	オブジェクトのライブラリ・キャッシュのネームスペース　TABLE/PROCEDURE、BODY、TRIGGER、　INDEX、CLUSTER、OBJECT
TYPE	VARCHAR2(28)	オブジェクトのタイプ　INDEX、TABLE、CLUSTER、VIEW、SET、　SYNONYM、SEQUENCE、PROCEDURE、　FUNCTION、PACKAGE、　PACKAGEBODY、TRIGGER、CLASS、　OBJECT、USER、DBLINK
SHARABLE_MEM	NUMBER	オブジェクトによって消費されている共有プール内の共有可能メモリの量
LOADS	NUMBER	オブジェクトがロードされた回数（オブジェクトが無効になった場合も含む）
LOCKS	NUMBER	オブジェクトをロックしているユーザー数
PINS	NUMBER	オブジェクトを確保しているユーザー数
KEPT	VARCHAR2(3)	オブジェクトがPL/SQL プロシージャDBMS_SHARED_POOL.KEEPによって保持（永続的にメモリに確保）されているかどうか（YES\| NO）
表3　V$DB_OBJECT_CACHE動的パフォーマンスビュー（抜粋）

　共有プール内にロードされたオブジェクト（以下の例ではパッケージ）の名前、形式、使用メモリサイズ、ロード回数を確認する方法は以下になります。

SQL> SELECT
       OWNER AS 所有者,
       NAME AS パッケージ名,
       TYPE AS 形式,
       SHARABLE_MEM AS 使用サイズ,
       LOADS AS ロード回数,
       KEPT AS メモリ確保
     FROM V$DB_OBJECT_CACHE
     WHERE TYPE = 'PACKAGE'
       AND OWNER = '[所有者]'
     ORDER BY LOADS DESC;

リスト5　共有プール内にロードされたオブジェクトを確認

図3　リスト5の出力結果

　ここで特にロード回数に注意してください。先述したように、パッケージがディスクI/Oを発生させてデータファイルからロードされているわけですから、負荷が高い状況で何回もロードされているとしたら、間違いなくボトルネックになります。もちろんメモリ上から削除され、再ロードされた回数も含まれます。このような場合は、共有プール上にこのオブジェクトを予約してしまい、永続的にメモリ上に保持させることで、ディスクI/Oを軽減できます。限りある共有プールに永続的に保持し続けるわけですから、共有プールサイズに注意して設定する必要があります。

共有プールにオブジェクトを保持させる

　共有プールにオブジェクトを保持させるには、DBMS_SHARED_POOL.KEEPプロシージャを使用します。このプロシージャはデフォルトでは保持されておらず、dbmspool.sqlスクリプトを使用することで利用可能になります。このスクリプトはともにRDBMSディレクトリに格納されます（Windows版「[$ORA_HOME]\RDBMS\ADMIN」、UNIX版「ORACLE_HOME/rdbms/admin/」）。

SQL> EXECUTE DBMS_SHARED_POOL.KEEP('[所有ユーザ].パッケージ名',[フラグ（省略時パッケージ）]);

リスト6　DBMS_SHARED_POOL.KEEP使用方法

　第2パラメータのフラグは保持するオブジェクトの形式で省略可能です。省略時はストアドパッケージ、ストアド・プロシージャ、ストアドファンクションとなります。

図4　DBMS_SHARED_POOL.KEEPプロシージャの使用例

　先ほど確認した図3のパッケージがメモリ上に保持されたことは、KEPTカラムの「YES」で確認できます。

共有プールに保持したオブジェクトの解放

　共有プールに保持したオブジェクトを解放するには、DBMS_SHARED_POOL.UNKEEPを使用します。

SQL> EXECUTE DBMS_SHARED_POOL.UNKEEP('[所有ユーザ].パッケージ名',[フラグ（省略時パッケージ）]);

リスト7　DBMS_SHARED_POOL.UNKEEP使用方法

　共有プールから削除されたかどうかは、再度V$DB_OBJECT_CACHEのKEPT列を確認します。「NO」という値が設定されていれば解放されたことになります。

補足：共有プール内バッファを空にする

　レスポンスの遅延が共有プールに起因しているかどうかは、下記のコマンドを発行し、いったん共有プール内バッファを空にすることで確認できることがあります。つまり、強制的に共有プール内データを削除し、レスポンスに影響していないか切り分けができます。このコマンドではメモリ内に確保されたエクステントなどメモリ保持単位を完全に空にするわけではないので、メモリがクリーンになるわけではありません。また上記プロシージャで永続的に保持されたオブジェクトは空になりません。