カーソル・エラーとオブジェクトの問題切り分け：Oracleパフォーマンス障害の克服（7）（2/2 ページ）
Oracleデータベースの運用管理者は、突発的に直面するパフォーマンス障害にどうやって対処したらよいか。本連載は、非常に複雑なOracleのアーキテクチャに頭を悩ます管理者に向け、短時間で問題を切り分け、対処法を見つけるノウハウを紹介する。対象とするバージョンはOracle8から9iまでを基本とし、10gの情報は随時加えていく。（編集局）
オブジェクトをキャッシュに常駐させるテクニック
前項でも述べたように、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
| 列名 | データ型 | 格納されているデータの内容 |
|---|---|---|
| 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;
ここで特にロード回数に注意してください。先述したように、パッケージがディスク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('[所有ユーザ].パッケージ名',[フラグ（省略時パッケージ）]);
第2パラメータのフラグは保持するオブジェクトの形式で省略可能です。省略時はストアドパッケージ、ストアド・プロシージャ、ストアドファンクションとなります。
先ほど確認した図3のパッケージがメモリ上に保持されたことは、KEPTカラムの「YES」で確認できます。
共有プールに保持したオブジェクトの解放
共有プールに保持したオブジェクトを解放するには、DBMS_SHARED_POOL.UNKEEPを使用します。
SQL> EXECUTE DBMS_SHARED_POOL.UNKEEP('[所有ユーザ].パッケージ名',[フラグ（省略時パッケージ）]);
共有プールから削除されたかどうかは、再度V$DB_OBJECT_CACHEのKEPT列を確認します。「NO」という値が設定されていれば解放されたことになります。
補足：共有プール内バッファを空にする
レスポンスの遅延が共有プールに起因しているかどうかは、下記のコマンドを発行し、いったん共有プール内バッファを空にすることで確認できることがあります。つまり、強制的に共有プール内データを削除し、レスポンスに影響していないか切り分けができます。このコマンドではメモリ内に確保されたエクステントなどメモリ保持単位を完全に空にするわけではないので、メモリがクリーンになるわけではありません。また上記プロシージャで永続的に保持されたオブジェクトは空になりません。
SQL> ALTER SYSTEM FLUSH SHARED_POOL;
次回は最終回となります。残っている項目の「領域の確認」「ディスクの確認」について解説して、本連載のまとめとします。（次回に続く）
Copyright © ITmedia, Inc. All Rights Reserved.