*以下は、サポート契約締結中のお客様に毎月配信しているサポートレターより一部抜粋して掲載しています。

「本番環境の運用中に特定の SQL 文の子カーソル数が増大しているため、暫定対処として共有プールからこの SQL 文の情報をフラッシュしたい」というお問合せをいただくことがございます。
対策の1つとして alter system flush shared_pool; コマンドで共有プール全体をフラッシュする方法がありますが、この方法ですと他の SQL 文の子カーソル (実行計画)までフラッシュされてしまいパフォーマンスへの影響が危惧されます。
Oracle Database 10.2.0.4 以降であれば特定の SQL 文のみ共有プールからフラッシュすることができます。その方法は下記の通りです。

手順

ご使用のバージョンが Oracle Database 10.2.0.4 の場合のみ、当機能を使用する前にまず事前準備として下記 event をパラメータ・ファイルに設定してからインスタンスを再起動する必要があります。

event="5614566 trace name context forever"

フラッシュ手順は以下の通りです。

1. sys ユーザで接続します。

     $ sqlplus / as sysdba

2. v$sqlarea を検索し、フラッシュしたい SQL 文の address, hash_value を特定します。

例)

     SQL> select address, hash_value, sql_text from v$sqlarea
          where sql_text like 'select empno from emp%';
     
     ADDRESS    HASH_VALUE SQL_TEXT
     ---------- ---------- --------------------------------------
     1111       2222       select empno from emp where empno=7800

上記例では sql_text を where 句に指定していますが、hash_value やold_hash_value 等他の列を用いて検索しても問題ありません。

3. フラッシュを実行します。

SQL> exec dbms_shared_pool.purge ('<ADDRESS>,<HASH_VALUE>','C');

例

ADDRESS: 1111, HASH_VALUE: 2222 の場合は下記の通りとなります。

     SQL> exec dbms_shared_pool.purge ('1111,2222','C');

4. フラッシュされたかどうか確認するため「2.」と同様に v$sqlarea を検索します。

1行も選択されなければ成功です。

例)

     SQL> select address, hash_value, sql_text from v$sqlarea
          where sql_text like 'select empno from emp%';
     
     no rows selected

まとめ

特定の SQL 文の子カーソル数が増大しているということは何らかの性能問題が既に発生しているか、潜在的に発生するリスクがある可能性があります。このような状況において本番環境で共有プール全体のフラッシュを実行するのは難しい場合もあるかと思います。
そのようなときに上述の方法をご利用いただければと思います。

(オラクル事業部 グローバル・アライアンス&サポート・サービス担当 長谷川)

• Oracle関連サービス