latch: library cache

解析フェーズで使われた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つの方法をご紹介します。

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

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待機イベントの原因を分析するため、性能情報の推移を確認してみます。

解析と関連した指標の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 Stat」タブで「IO Wait CPU」、「User CPU」、「Sys CPU」、「Free Memory(MB)」の推移を確認してみます。

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

該当時点に「セッション・リスト」のPGA(MB)情報を見ると、メモリーを過度に使うセッションは存在していません。 つまり、問題の原因は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枯渇による共有プールのスワップ・アウトとそれに伴う問題発生

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

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

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

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

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

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

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

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

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

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統計値を減らす方法を推奨します。

1)スカラ・サブクエリに変更

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 );

2)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統計値を減らせることができます。 これによってライブラリ・キャッシュ・ラッチを待機するセッション数が減ります。