latch: library cache - 日本エクセム株式会社 Oracle待機イベント情報

基本情報

　共有プール・ラッチが空き領域を探すために空きリストをスキャンして適切なチャンクを割り当てる作業を保護するなら、ライブラリ・キャッシュ・ラッチはSQLを実行するためにライブラリ・キャッシュメモリー領域を探索して管理する全ての作業を保護します。ライブラリ・キャッシュ・ラッチはCPUカウントより大きい素数中一番小さい数だけ子ラッチを持ちます。

　ライブラリ・キャッシュ・ラッチを獲得する中で競合が発生すると、latch: library cache待機イベントを待機します。ライブラリ・キャッシュ・ラッチ競合は主に次のような場合に発生します。

・ハード解析やソフト解析が多過ぎる場合
・バージョン・カウントが高い場合
・SGA領域のページ・アウトが発生する場合

ハード解析やソフト解析が多すぎる場合

　共有プール・ラッチ競合が主にハード解析による空きリストの探索によって発生するように、ライブラリ・キャッシュ・ラッチ競合の最も重要な原因もハード解析にあります。ハード解析がたくさん発生すると、以下のような理由でライブラリ・キャッシュ・ラッチ競合が発生するようになります。

・ライブラリ・キャッシュを探索する回数が増えるため、
　それだけライブラリ・キャッシュ・ラッチを保有する時間と回数が増えます。

・ハード解析の場合、ライブラリ・キャッシュ領域に対する探索だけでなく
　追加的にチャンクの割当が必要なため、それだけライブラリ・キャッシュ・ラッチを
　保有する時間が増えます。

　ライブラリ・キャッシュ・ラッチ競合はハード解析だけではなくソフト解析、すなわちパインド変数をよく使っている場合にも発生します。ソフト解析はハード解析に比べて、コストが非常に低いが、構文検査/意味検査/ライブラリ・キャッシュ探索などの過程は避けられません。ライブラリ・キャッシュを探索する間は、ライブラリ・キャッシュ・ラッチを獲得しなければならないためです。従って、多くのセッションが同時にソフト解析を実行する場合、ライブラリ・キャッシュ・ラッチ競合による性能低下現象が発生することになります。

バージョン・カウントが高い場合

　それぞれ違う3人のユーザーが次のように同じSQL文を実行したと想定してみましょう。

Scott : select * from emp where empno = 1;
Mary : select * from emp where empno = 1;
John : select * from emp where empno = 1;

　上記3つのSQL文はテキストが完全に同じなので同じハッシュ値を持ちます。 従って、同じハッシュ・チェーンの同じハンドルに割り当てられます。しかし、上記のemp表は全て異なるスキーマの表であるため実際には別のSQL文です。 この場合、Oracleはテキストに該当する親ライブラリ・キャッシュ・オブジェクト(Library Cache Object、以下LCO)とその下に3つの子LCOを作って、個別SQL情報を管理します。 3つの子LCOは別のリストに保存されます。そのため、V$SQLAREAビューのVERSION_COUNTカラム値が子LCOの数と同じく3になります。バージョン・カウントが高いということは、子LCO探索する回数が増え、結果的にライブラリ・キャッシュを探索する時間もそれだけ増えるということを意味します。これによってライブラリ・キャッシュ・ラッチ競合が増えることになります。もし、特定SQLでライブラリ・キャッシュ・ラッチ競合が多く発生するなら、該当SQLのバージョン・カウントの値を確認してみる必要があります。

SGA領域のページ・アウトが発生する場合

　共有プールがディスクへページ・アウトされた該当領域に対してスキャンが発生する時、再びディスクの内容がメモリーにページ・インされるまで、待機しなければならないのでライブラリ・キャッシュ・ラッチに対する待ち時間が増えます。もし、latch:library cache待機イベントが高い時に、OSでスワップ現象が発生する場合は、ページ・アウトによる性能低下である可能性が高いです。

待機パラメータと待機時間

待機パラメータ

・P1:ラッチのアドレス
・P2:ラッチ番号(V$LATChNAME.LATCH#と同一)。
　　  ラッチ番号に該当するラッチ名を確認するためには、
　　  下記のSQLを実行します。
　　  SELECT * FROM v$latchname WHERE latch# = &p2_value;
・P3:試行回数。ラッチを獲得するため、プロセスがトライした回数。

待機時間

 待機時間は、指数的に増加します。

チェックポイントとソリューション

解析回数を減らす

　ライブラリ・キャッシュ・ラッチの競合を減らす最も良い方法は、1回の解析で複数回実行することです。解析回数を減らせばそれだけライブラリ・キャッシュ・ラッチ競合を減らすことができます。 たとえば、Java環境の場合は、PreparedStatementオブジェクトを利用して、解析を1回だけ実行してcloseを実行しないで、executeを繰り返し実行することによって解析回数を減らすことができます。しかし、常にこの方法を使えるわけではありません。 なぜならアプリケーションで多くの修正が必要になるためです。また、業務ロジックによってはこのような方法を使うことが不可能な場合もありえます。特定のWebアプリケーションサーバーの場合は、ステートメント・キャッシュ機能を提供していますが、この機能を使うことによって同じ効果を得ることができます。

　PL/SQLの動的SQLを使う時はライブラリ・キャッシュ・ラッチ競合が増える可能性があることに注意しなければなりません。動的SQLを使うと、PL/SQLのメリットの一つであるカーソル再利用(1回の解析で複数回も実行)機能が使えないため、ソフト解析が増えます。その結果、ライブラリ・キャッシュ・ラッチ競合が増えるようになります。従って、できる限り静的SQLを使うことを推奨します。

SESSION_CACHED_CURSORSパラメータを利用する

　SESSION_CACHED_CURSORSパラメータ値が設定されていれば、Oracleは3回以上実行されたSQLカーソルに対する情報をPGA内に保管します。ユーザーがSQL実行を要求する時、OracleはPGA内にキャッシュされた情報があるかを確認して、情報があればその情報を利用します。これによって、ライブラリ・キャッシュ領域を探索する時間が減り、ライブラリ・キャッシュ・ラッチを保有する時間が減ることになります。SESSION_CACHED_CURSORSパラメータのデフォルト値はバージョンごとに異なります。デフォルト値が小さければ、できるだけ50以上の値を設定することが望ましいです。

豆知識

ライブラリ・キャッシュの構造

　共有プールで最も重要な部分の一つであるライブラリ・キャッシュ領域はSQLの実行と関連したすべての情報を管理する領域です。ライブラリ・キャッシュは[ハッシュ表->バケット->チェーン->ハンドル->オブジェクト]という仕組みになっています。

　下図にはライブラリ・キャッシュの構造が説明されています。

　Oracleはオブジェクト名(たとえばSQLテキスト)にハッシュ関数を適用して生成されたハッシュ値を利用し、適切なハッシュ・バケットを割り当てます。この時、同じハッシュ値を持つオブジェクトはチェーン(リスト)で管理されます。一つのライブラリ・キャッシュ・ハンドル(以下ハンドル)は一つのLCOを管理します。ハンドルはLCOに対するメタ情報およびポインタ役割をしており、LCOが実情報を含んでいます。 LCOが含む情報の中で、主なものは次のとおりです。

・依存表 : SQL文が参照する表、ピューなどに対して依存関係に関する情報を持ちます。

・子表 : プロシージャ、表と同じように一意の名前が付与されるLCOは子を持ちません。
　プロシージャや表のようなオブジェクトはスキーマ名と常に一緒に保存するため、
　一意性が保障されます。しかし、SQL文の場合にはSQLテキスト自体が名前で使われるため、
　一意性が保障されません。 従って、SQLテキストを名前で持つ親LCOが生成され、
　実のSQLカーソルに関する情報は子LCOに保存します。
　たとえば、2つの異なるスキーマA、Bで、テキストは同じで実際に参照するオブジェクトだけが
　異なるSQL文を実行した場合、OracleはSQLテキストに該当する親LCOとスキーマAが
　実行したSQLカーソルに該当する子LCO、スキーマBが実行したSQLカーソルに該当する子LCO、
　全部3つのLCOを生成します。
　この場合、V$SQLAREA.VERSION_COUNTの値は3になります。子LCOはライブラリ・キャッシュ領域に
　保存されます。

・データ・ブロック : LCOが含む実情報を保存するチャンク領域に対するポインタ情報。 
　たとえばSQLカーソルの場合、SQL文、実行計画、実行文情報などは
　チャンクに保管され、これらチャンクのアドレス値がLCOのデータ・ブロック領域に管理されます。

　ライブラリ・キャッシュのダンプをとれば、ライブラリ・キャッシュの構造が物理的にどのように構成されているのか確認できます。

SQL> alter session set events 'immediate trace name library_cache level 10';

BUCKET 84:      <-- バケット番号
LIBRARY OBJECT HANDLE: handle=c000000099a05940    <--ハンドル
 mutex=c000000099a05a70(1)    
  name=  <-- LCO名
select   LOW_OPTIMAL_SIZE,           HIGH_OPTIMAL_SIZE,           
       OPTIMAL_EXECUTIONS,           ONEPASS_EXECUTIONS,           
    ...
    DATA BLOCKS: 
    data#     heap  pointer    status pins change whr
    ----- -------- -------- --------- ---- ------ ---
        0 c000000099ad4480 c0000000994b9bd8 I/P/A/-/-    0 NONE   00      
     -->　チャンク領域に対するポインタ(c000000099ad4480はヒープに対する
       　c0000000994b9bd8はチャンクに対するポインタ)
  
 LIBRARY OBJECT HANDLE: handle=c000000099baeb10 mutex=c000000099baec40(0)
  ...

  BUCKET 84 total object count=2

BUCKET 126:
LIBRARY OBJECT HANDLE: handle=c00000009e506c60 
    mutex=c00000009e506d90(0)
  name=SELECT SUM(BLOCKS * BLOCK_SIZE) 
  FROM V$DATAFILE_COPY WHERE DBMS_RCVMAN.ISRECLRECID(:B2 , RECID) = :B1  
  ...

　上記のライブラリ・キャッシュ・ダンプで確認できるように、LCOのデータ・ブロック領域ではヒープ領域に対するポインタ(c000000099ad4480)情報を持っています。

　ヒープ・ダンプ・ファイルでこのアドレスを問い合わせてみると、次のような値を得られます。

Chunk c0000000994b91b0 sz=1112    freeable  "PCursor"  ds=c000000099ad4480 

　上記の情報はアドレス値に該当するサブ・ヒープ領域を指し、サブ・ヒープ・ダンプを利用して正確なサブ・ヒープとチャンクの位置を確認することができます。

　ライブラリ・キャッシュ領域を探索しようとするすべてのプロセスは、必ず該当ハッシュ・バケットを保護するライブラリ・キャッシュ・ラッチを獲得しなければなりません。ライブラリ・キャッシュ・ラッチはCPU数より大きい素数の中一番小さい数だけ使われます。たとえば、CPU数が4つのステムでは5つのライブラリ・キャッシュ・ラッチを使います。ライブラリ・キャッシュにアクセスする過程でライブラリ・キャッシュ・ラッチ競合が発生する場合、プロセスはlatch: library cache待機イベントで待機します。

　ライブラリ・キャッシュ・ラッチがライブラリ・キャッシュ領域に対する探索を同期化するために使われるのに対して、library cache lockとlibrary cache pinという2つのロックはハンドルとLCOを保護する役割をします。たとえば、ALTER TABLEコマンドで、表を変更しようとするプロセスは表情報を保存するLCOに対してlibrary cache lockを排他ロック・モードに獲得しなければなりません。library cache lockとlibrary cache pinを獲得する過程で競合が発生すると、それぞれlibrary cache lock待機イベントとlibrary cache pin待機イベントで待機します。

SQL実行時の内部動作

　ユーザーがSQL文の実行を要求すると、Oracleはライブラリ・キャッシュのメモリー領域とラッチを利用して必要な作業を行います。

　これを時系列でまとめると次のようになります。

・ユーザーが新しいSQL文の実行を要求すると、基本的な文法チェックと権限チェックが行われます。
　その後、ライブラリ・キャッシュ・ラッチを獲得して、ライブラリ・キャッシュ領域に同じSQL文、
　すなわち同じLCOが存在しているのかを確認します。
　ライブラリ・キャッシュ・ラッチを獲得する過程で競合が発生したら、latch:library cache待機イベントで待機します。
　同じLCOが存在する場合には8番目を実行するようになりますが、これをソフト解析と言います。
　SQL解析の要求がある度にparse count(total)統計値は増加します。

・もし、同じSQL文が存在しなければ、共有プール・ラッチを獲得して、空きリストから最も適切な大きさの空きチャンクを探します。
　共有プール・ラッチを獲得する過程で競合が発生したら、latch:shared pool待機イベントで待機します。
　 空きチャンクが確保されるまで共有プール・ラッチを保有し続けます。

・もし、最適な大きさの空きチャンクが存在しなければ、より大きい空きチャンクを探してこれを分割して使います。
　残ったメモリー領域はまた適切な空きリストとして登録されます。

・全ての空きリストを探索しても適切な大きさの空きチャンクが見つからなかったら、LRUリストを探索します。
　LRUリストのチャンクは再生成可能でありながら、現在使用中でないことです。

・LRUリストを探索しても適切な大きさのチャンクを確保できなければ、共有プール内の空いているメモリー空間を追加で割り当てます。

・上記の過程が全て失敗すると、ORA-4031エラーが発生します。

・適切な空きチャンクが見つかったら、SQL文に当てはまるハンドルに対して排他的なライブラリ・キャッシュ・ロックを獲得してLCO情報を生成します。
　LCOが生成されればライブラリ・キャッシュ・ロックをNULLモードに変換して、library cache pinを排他的に獲得した後、実行計画を生成します。
　2番～7番までをハード解析と言います。 ハード解析が発生すると、parse count (hard)統計値が増加します。
　もし、ハード解析でSQL文の誤りが見つかったら(たとえば存在しないオブジェクト参照など)、 parse count (hard)統計値と共にparse count (failures)統計値も増加します。

・SQL文はSQLカーソルに対してlibrary cache lockとlibrary cache pinを共有モードで獲得し実行されます。 
　この過程を実行フェーズと言います。
　SQLカーソルが参照するLCO(表、プロシージャなど)に対しては基本的に
　SQLカーソルと同じモードでlibrary cache lockとlibrary cache pinを獲得します。
　しかし、DDL文のようにオブジェクト情報を変更する作業を実行する場合には
　該当オブジェクトのLCOに対してlibrary cache lockとlibrary cache pinを排他ロック・モードで獲得するケースもあります。
　例えば、ALTER TABLE xxx ADD VARCHAR(10)のようなSQL文が実行されるケースでは、
　実行フェーズでSQLカーソルに対してlibrary cache lockを共有ロック・モードで獲得するが、
　表xxxに該当するLCOに対してはlibrary cache lockを排他ロック・モードで獲得します。
　library cache lockとlibrary cache pinの獲得で競合が発生すると、
　それぞれlibrary cache lock待機イベントとlibrary cache pin待機イベントで待機します。

・実行完了したSQLカーソルはデータをフェッチします。この過程をフェッチ・フェーズと言います。
　フェッチ・フェーズではSQLカーソルに対して
library cache lockをNULLモードに変換し、library cache pinは解除します。
　NULLモードで獲得したlibrary cache lockはbreakable parse lockと言われます。

　解析フェーズで使われたCPU時間と解析を行うためにかかった時間は、parse time cpu統計値とparse time elapsed統計値に記録されます。解析を行う間、ラッチやロックを獲得するために待機する時間が長くなると、parse time elapsed統計値がparse time cpu統計値に比べて、非常に大きくなる場合があります。 特に同時に複数のセッションがハード解析を実行する場合に、parse time elapsed統計値が大幅に増加する現象が起きますが、このほとんどがライブラリ・キャッシュ・ラッチや共有プール・ラッチを獲得する途中で競合が発生し、待ち時間が増加したためです。いくら多くの空きチャンクが存在しても要求されたSQL文が入れる最適な大きさ以上の空きチャンクが見つからなかったら、それ以上の作業は進まないということに注意しなければならなりません。 共有プールに空きメモリーが十分にあるのにももかかわらず、ORA-4031(unable to allocate %s bytes of shared memory)エラーが発生する理由はこのためです。このような現象は、多くの場合過度なハード解析によって、空きチャンクドが小さく分割されている時に発生します。

　このような共有プールの断片化は共有プール・ラッチ競合にも大きい影響を与えます。 共有プールが断片化されると、空きリストには多数の空きチャンクドが含まれることになります。空きチャンクを割当られるために空きリストを探索する時は、共有プール・ラッチを獲得しなければなりませんが、空きチャンクが多数に分割されていると、空きリスト探索に時間がかかり、その分共有プール・ラッチを保有する時間も長くなります。

　ORA-4031エラーが発生した場合、緊急対応としてALTER SYSTEM FLUSH SHARED_POOLコマンドを使うことになります。なぜなら、共有プールをフラッシュすると連続した空きチャンクを結合するため、次回にSQL文実行を要求した時、適当な大きさの空きチャンクが見つかる可能性が高くなるからです。しかし、これは共有プールをフラッシュすることがORA-4031エラーの解決策であるという意味ではありません。フラッシュをすることによって、他の性能問題が引き起こされる可能性があることには十分注意すべきです。ライブラリ・キャッシュ・ラッチは解析が発生する度に獲得しなければならないため、競合が発生する確率が非常に高いです。ライブラリ・キャッシュ・ラッチ競合はシステムの性能に直結する待機イベントのため、Oracleではライブラリ・キャッシュ・ラッチの競合を減らすために様々な方法を提供しています。 ここでは、2つの方法をご紹介します。

・PL/SQLブロック内で繰り返し実行されるSQLカーソルは最初の実行時だけ解析が行われ、
　それ以降はソフト解析なしで実行することが可能です。
　ソフト解析が発生しないため、ライブラリ・キャッシュ・ラッチも獲得しなくなります。 
　SQLカーソルに該当するLCOをPinすることでメモリーに固定させ、
　ライブラリ・キャッシュ・ラッチを獲得しなくてもLCOを参照することができます。

・Oracleではセッション内にLCOの位置をキャッシュする機能が提供されており、
　セッション・カーソルをセッション・カーソル・キャッシュに保存することができます。
　この機能は、あるセッション内で3回以上実行されたSQLカーソルの位置
　(すなわち、ライブラリ・キャッシュ内での位置)とSQLテキストをPGA領域に保存します。
　保存するSQLカーソル数はSESSION_CACHED_CURSORSパラメータで設定できます。
　この機能を利用する場合、ソフト解析は相変らず発生し、ライブラリ・キャッシュ・ラッチも
　獲得しなければならないということには変わりがありません。
　ただ、ライブラリ・キャッシュ・メモリーの構造を探索しなくても、
　そのままLCOの位置に辿り着くことができるため、ライブラリ・キャッシュ・ラッチを
　保有する時間は減ります。 その分ライブラリ・キャッシュ・ラッチによる競合も減ります。

分析事例

OS空きメモリー枯渇による性能低下現象分析

　OracleにアクセスしたプロセスはSQL実行のため解析を行う時、SGA領域の一つである共有プール領域を使うことになります。もし、共有プール領域がOSの空きメモリー不足によりスワップ・アウトされた場合は、深刻な性能低下現象が発生します。OSの空きメモリー枯渇によって発生する性能低下現象を、Oracle DBMSの性能診断/分析ツールのMaxGaugeを活用して分析してみます。

性能低下区間の確認

　性能問題が発生したインスタンスで収集された稼動ログから推移グラフをみると、12時13分から12時25分間、「CPU」使用率、「Active」および「Wait」が連動して急増したことを簡単に確認することができます。

■ 「CPU」使用率の推移グラフ

■ 「Active」セッション数の推移グラフ

■ 「Wait」の推移グラフ(待機時間)

性能低下区間の詳細分析

　問題の区間だけを簡単に確認するために、12時00分～13時00分のデータを拡大して確認してみましょう。

■「Active sessions」推移と「Wait」推移の比較

■「Active sessions」推移と「CPU」推移の比較

　拡大した問題区間のグラフを確認した結果、「Active sessions」推移は「Wait」 推移とは一致しますが、「Active sessions」推移と「CPU」 推移とは若干異なることが分かります。すなわち、「CPU」使用率と「Active sessions」の増加とは直接的には関係しないことが考えられます。

待機イベントの検出および分析

　アクティブ・セッションの急増による性能低下の原因を究明するために、問題時点の待機イベントの発生内容を確認してみます。「Value」タプで同じ時点のトップ待機イベントを確認した結果、アイドル・イベント(SQL*Net message from client)を除いたトップ待機イベントは、latch: librery cache待機イベントということが分かります。

　下図の「セッション・リスト」 画面で同時点のセッションの待機イベントを確認してみると、全てのセッションが同じSELECT文を実行中であることが分かりました。また、latch: library cache待機イベントを待機するセッションは全て同じアドレス(504403159414602544)で待機しています。 latch: library cache待機イベントの原因を分析するため、性能情報の推移を確認してみます。

解析およびSQL実行に関する性能情報分析

　解析と関連した指標のparse count(total)、parse count(hard)、parse time elapsed統計値と、SQL実行と関連した指標のexecute count統計値の推移を確認してみましょう。

　下図のグラフでは、性能低下区間にparse time elapsed統計値が高くなっていますが、この区間にparse count(total)、parse count(hard)、execute count統計値は他の区間に比べて、それほど高い値ではありません。 すなわち、解析に必要とされた時間は長かったが、この時点に発生した解析はそれほど多くないことが分かります。parse time elapsed統計値が高いのはその前の区間にハード解析が実行されており、それによる共有プールの断片化が発生された可能性もあります。

　そこで、24時間内で発生したハード解析の推移を確認してみます。

　下図のグラフを確認すると、性能低下区間の前にハード解析が多く発生した区間はないことが分かります。分析結果から推論すると、parse time elapsed統計値が高いのはハード解析およびSQL実行回数が多過ぎることとは関連がないことが分かります。次に考えられる原因としては、OSの空きメモリー不足により共有プールのスワップ・アウトによる可能性があります。

　そこで、OS性能情報を分析してみることにします。

OS性能情報およびセッション分析による問題原因究明

　「OS Stat」タブで「IO Wait CPU」、「User CPU」、「Sys CPU」、「Free Memory(MB)」の推移を確認してみます。

　下図の推移を確認した結果、性能低下区間に空きメモリーが枯渇するのに伴ってスワップ・アウトが発生し、その結果アクティブ・セッションが急増する現象が発生しています。すなわち、メモリー枯渇によって共有プールがスワップ・アウトされ、これによって解析およびSQL実行をしようとするセッションは共有プールの情報を得るためにスワップ・アウトされたメモリーを再度ロードする作業が必要だったため、latch:library cache待機イベントで長時間待たされたということになります。

　該当時点に「セッション・リスト」のPGA(MB)情報を見ると、メモリーを過度に使うセッションは存在していません。つまり、問題の原因はOracleのプロセスではなく他のプロセスがメモリーを過剰に使っていることが考えられます。

Oracleパラメータ確認

　MaxGaugeの「パラメータ」機能で、データベースのパラメータを確認してみた結果、LOCK_SGAパラメータの設定値がFALSEでした。SunOSやLinuxはISM (Intimate Shared Memory)方式を使うのでLOCK_SGA値と関係なく共有メモリーが物理メモリーに固定されますが、HP-UXやAIXの場合にはLOCK_SGA=FALSEの場合SGAがスワップ・アウトされる場合がありますので、このパラメータの値はTRUEに設定することを推奨します。

結論

latch: library cache待機イベントの急増によるアクティブ・セッションの急増
　　　　　　　　　　　　　　　　　　↓
OSのFree Memory枯渇による共有プールのスワップ・アウトとそれに伴う問題発生

解決方法

・LOCK_SGAパラメータの値をTRUEに設定
・リアルタイムでメモリーを多く使うプロセスを監視

過度な実行回数による性能低下現象

　問題が発生したインスタンスのセッションとSQLの実行履歴をMaxGaugeのログ・データで確認した結果、多くのセッションがlatch:library cache待機イベントで待機しています。 ラッチを待機することによって各セッションのCPU使用率も高くなり、最大70%以上まで使っていることを確認することができます。

　latch: library cache待機イベントはSQLの解析と関係があり、同時にlatch: shared pool待機イベントの待機が発生しないため、ソフト解析が原因であることが考えられます。すなわち、ソフト解析と関連があるSQLの実行回数を確認してみると、セッションのSQL実行回数が4000回以上の非常に高い数値になっていることが分かります。

結論

 ライブラリ・キャッシュ・ラッチの原因は過度な解析(高い実行回数)であるため、このラッチが発生した場合は解析回数を減らさなければなりません。

解決策

　解析回数を減らす方法としては、下記の3つがあります。

・1回の解析で複数回実行するようにすること：JavaでならPreparedStatement、PL/SQLの使用
・バインド変数の使用
・session_cached_cursorsパラメータの調整

過度な実行回数による解析遅延およびロック待機現象

　問題が発生したインスタンスのログを調査すると、多数のセッションでロック競合による遅延が発生していることを確認できます。

　ツリー構造でロックを保持しているセッションと待機セッションの関係を調査したのが下図になります。ロックを保持したセッションはges group parent latch待機イベントを待機しています。ges group parent latch待機イベントは共有プールのエンキュー・リソースを保護するラッチです。ロックを保持したセッションがラッチ待機イベントを待機中であるため、作業が進まずロック待機現象が発生しています。

　下図の「セッション・リスト」では、同一時点にロック待機セッション以外のセッションはlatch: library cache待機イベントを待機していることが確認できます。

　Oracle内部的には、ライブラリ・キャッシュとエンキュー・リソース構造体は全て共有プールに存在するので、多くのセッションが共有プールにアクセスしており、リソースの獲得に時間がかかっていたことが考えられます。

　今までの分析した結果をまとめると、下記のようになります。

・latch free待機イベントが先に発生
・ラッチを獲得したプロセスがSQL文を実行してexecute count統計値とparse countが統計値増加
・エンキュー待機が発生

　latch free待機イベントの発生後に、解析回数が増加しているので、解析においてラッチ競合が発生したことが考えられます。

ファンクション使用による実行回数増加現象

　開発では、ファンクションを使う場合があります。ここでは、このような場合に発生した性能低下の事例を紹介します。

　性能問題が発生したインスタンスで収集されたログから推移グラフを見ると、「Active」と「CPU」、「Wait」のグラフが急増しています。

　「Wait」グラフの推移はlatch free待機イベントと一致します。

　下図の「セッション・リスト」を確認してみると、ほとんどのセッションでlatch free(library cache)待機イベントが発生しています。

　待機するアクティブ・セッションが実行しているSQL文は次のとおりです。

SELECT emp.empno ,
            emp.ename ,
            emp.hiredate , 
            emp.comm ,
            emp.sal ,
            emp.depno ,
            get_dept(&DEPT , ‘1’) 
FROM emp, salgrade
WHERE ( salgrade.grade = 2 )
AND    ( emp.sal between salgrade.losal and salgrade.hisal)
AND    ( emp.deptno = &DEPT ) 

　このSQLはget_deptというファンクションを使用しており、このファンクションは次のようなコードで構成されています。

FUNCTION get_dept(dept_kind  comcod.code_kind%TYPE , dept_gubun varchar2)
return varchar2 as
   v_code_dname    comcod.code_kname%TYPE;
   v_code_loc   comcod.code_skname%TYPE;
   v_code_deptno    comcod.code_ename%TYPE;
begin
   select  dname , loc , deptno
   into    v_code_dname, v_code_loc, v_code_deptno
   from    dept
   where deptno=dept_kind;
   --3つの値を持ってきた後、区分コードに従って1つの値だけをリターンする
     if dept_gubun = '1' then
      return  v_code_dname;
  elsif dept_gubun = '2' then
      return  v_code_loc;
  elsif dept_gubun = '3' then
      return  v_code_deptno;
  else
      return ' ';
  end if;     
exception
   when    others  then
       v_code_dname :=  ' ';
       return  v_code_dname ;
end ;

　このようなファンクションを使う場合、多数の解析によってexecute count統計値が増加します。従って、このファンクションの代わりに他の方法を使ってライブラリ・キャッシュ・ラッチを獲得するためのセッション待機を減らさなければなりません。

　ファンクションの結果セットが多くない場合、スカラ・サブクエリやOUTER JOINに変更してファンクションを使わずexecute count統計値を減らす方法を推奨します。

解決策

１）スカラ・サブクエリに変更

SELECT emp.empno ,
            emp.ename , 
            emp.hiredate , 
            emp.comm ,
            emp.sal ,
            emp.deptno ,
    --     get_dept(&DEPT , '1') 
           (select decode(&dept_gubun,'1',dname , '2',loc,'3',deptno) 
           From dept where deptno=&DEPT)
FROM emp, salgrade
WHERE ( salgrade.grade = 2 )
AND    ( emp.sal between salgrade.losal and salgrade.hisal)
AND    ( emp.deptno = &DEPT );

２）OUTER JOINに変更

SELECT emp.empno ,
            emp.ename ,
            emp.hiredate , 
            emp.comm ,
            emp.sal ,
            emp.deptno ,
    --     get_dept(&DEPT , '1') 
            decode(&dept_gubun,'1',c.dname,'2',c.loc,'3',c.deptno)
FROM emp, salgrade,  (select * From dept ) c 
WHERE ( salgrade.grade = 2 )
AND    ( emp.sal between salgrade.losal and salgrade.hisal)
AND    ( emp.deptno = &DEPT )
AND    (c.deptno(+)=&DEPT);

　単純に一つの結果を求める場合、上記の方法に変えることによってファンクション実行時のexecute count統計値を減らせることができます。これによってライブラリ・キャッシュ・ラッチを待機するセッション数が減ります。