Library cache lock

Library cache lockは、library cache object(以下 LCO)にアクセスまたは変更する際、
LCOのハンドル(handle)に対して獲得するロックです。ハンドルは実際のLCOに関するメタデータ、ポイントデータを持っています。

特定SQL文を実行するプロセスは、ソフト解析中にSQL文に該当するLCOに対し、
library cache lockを共有(Shared)モードで獲得する必要があります。
ソフト解析が終了したら(実行またはハード解析の段階)library cache lockをNULLモードに変更します。
この際、SQL文に該当するLCO以外にSQLで参照する全てのオブジェクトに対しても同じモードでlibrary cache lockを獲得します。

ソフト解析中に共有(Shared)モードでlibrary cache lockを獲得するということは、
複数のプロセスが同時に同じSQL文を実行できることを意味します。
すなわち、同じSQLに対してソフト解析中にlibrary cache lockの獲得は必要ですが、イベントの待機、競合は発生しません。

ソフト解析が終了したら、NULLモードにlibrary cache lockの獲得モードを変更しますが、これはSQLで参照するLCOに対して無効化(invalidation)を自動化するためです。
SQLカーソルのようなオブジェクトは、自分が参照しているオブジェクトが変更されたら、自動的に無効化にならなければなりません。
従って、該当するオブジェクトに対してDDL(alter、drop等)が実行されるとNULLモードで獲得しているlibrary cache lock情報を参照し、関連するLCOを無効化します。
上記の理由により、オラクルのコンセプト・マニュアルには、library cache lockをbreakable parse lockと呼びます。

Create or replace procedureコマンドによるプロシージャの生成や変更するプロセスは、プロシージャに該当するLCOに対してlibrary cache lockを排他(exclusive)モードで獲得する必要があります。

テーブルを変更(alter、create、drop)する場合も、テーブルに該当するLCOに対してlibrary cache lockを排他(exclusive)モードで獲得する必要があります。
従って、多数のセッションが特定のテーブルを参照(select)している間に、該当テーブルに対して変更作業を実施するとlibrary cache lockイベントの待機によってselectセッションの性能低下が発生します。

Library cache lock待機イベントのパラメータは次の通りです。

• P1 ： ハンドル・アドレス
• P2 ： ロック・アドレス
• P3 ： モード*100 + ネームスペース(名前空間)
• オラクル7.0から8.1.7までは モード*10 + ネームスペース(名前空間)として表記し、オラクル9.0からは
  モード*100 + ネームスペース(名前空間)と表記します。また、ネームスペース(名前空間)の部分は番号で表示されます。

   < mode >                     
   1: Null
   2: Shared
   3: Exclusive

   < namespace >
   0: SQL Area                                    3: Trigger        6: Object            14: Java Resource
   1: Table/Procedure/Function/Package header     4: Index          7: Pipe              32: Java Data
   2: Package body                                5: Cluster       13: Java Source 

PMONプロセスは1秒間待機し、他のプロセス群は3秒間待機します。当該待機時間までにロックが獲得できなかった場合は繰り返し待機します。

Library cache lock待機による性能低下の現象は、ほとんどが不適切なDDL(create、alter、compile、flush 等)によるものです。
従って、トランザクションが活発なシステムにおいてDDLを実行する際には、この内容を十分に考慮した上で実行することをお勧めします。
場合によってはハード解析が多いシステムで共有プールメモリーの枯渇を避けるために(ORA-4031エラーを防ぐために)フラッシュを実行することがありますが、逆にそれでシステムに悪影響を与える場合もあります。
ハード解析も悪いが、ハード解析中にDDLを実行することは何とも言えないほど悪いです。

latch: library cache#Library Cacheの構造を参照します。

latch: library cache#SQL実行時の内部動作を参照します。

トランザクションの盛んなシステムでは不適切なDDLがLibrary Cacheの競合を増加させ、
システム性能低下の原因になることが多いです。Oracle DBMSの性能診断/分析ツールであるMaxgaugeを活用し、不適切なDDLによる性能低下問題の原因を調べてみましょう。

09時36分の区間にアクティブ・セッション数とイベントの待機時間が急増することが確認できます。

■アクティブ・セッションの推移グラフ

■待機時間の推移グラフ

問題が発生した区間の待機イベントを確認してみると、library cache pin、library cache lock、library cache load lockイベントの待機と同様に
Library Cacheに関連した待機現象が多発していることが確認できます。

問題が起きた区間のアクティブセッション・リストを確認してみると、下図のようにほとんどのセッションがlibrary cache pin イベントや
library cache lockイベントに待機していることが確認できます。

library cache pinイベントとlibrary cache lockイベントが発生する意味は、DDLによってLibrary Cache Objectが
変更されたことを表します。下図は問題区間でDDLを実行したセッションを[Session List]画面で
キャプチャしたものです。SQL*Plusで実行された特定のセッションにalter index … nomonitoringのようなDDL文が継続的に実行されていることが確認できます。

性能問題が発生した原因を調べるためには、library cache lockや library cache pinの待機イベントが発生する理由を明確に理解する必要があります。

library cache lock待機イベントは、Library Cache Objectの定義を変更したり、参照したりする過程で競合が発生すると見られます。
たとえば、alter index …コマンドを実行するセッションは、インデックスに該当するLibrary Cache Objectに対してlibrary cache lockを 排他(exclusive)モードで獲得する必要があります。もし、その時に他のセッションが当該インデックスを経由するSQL文を実行するため、
library cache lockを共有(Shared)モードで獲得中ですとalter index … コマンドを実行するセッションはlibrary cache lockイベントで待機します。

library cache pin待機イベントは、Library Cache Objectの実行情報を変更したり、参照したりする過程で競合が発生すると見られます。
たとえば、特定のSQLに対して最初にハード解析を実行するセッションは、当該Library Cache Objectに対してlibrary cache pinを排他(Exclusive)モードで獲得します。
ハード解析中に同じSQLを実行したいセッションはlibrary cache pinを共有(Shared)モードで獲得するために、待機する必要があります。
この場合、library cache lockイベントで待機します。

上記内容をベースに、問題区間の待機現象を分析すると、library cache pin と library cache lock待機イベントが発生する原因を次の通り引き出すことができます。

現在、多数のセッションがSQLを実行中の場合、その間はSQLカーソル・オブジェクト及びSQLが参照するテーブル/インデックスに対してibrary cache lockとlibrary cache pinを獲得します。

この時にSQL*Plusで実行されたセッションが、多数のインデックスに対し、alter index … nomonitoringコマンドを実行します。

alter index … nomonitoringコマンドを実行するために、インデックスに対してlibrary cache lockを排他(exclusive)モードで獲得する必要があります。
同時に多数のセッションがインデックスを使用するSQL文を実行中であるため、alter index … nomonitoringコマンドを実行したセッションはすべてのSQL文の実行が終了するまでlibrary cache lockイベントで待機します。

alter index … nomonitoringコマンドを実行するセッションでlibrary cache lockを排他(Exclusive)モードで獲得した後に作業を行います。
作業中は当該インデックスを参照する全てのセッションはlibrary cache lockイベントで待機します。
alter indexの作業が終了すると、当該インデックスを参照する全てのSQLのカーソル・オブジェクトは無効になります(Invalidation)。

無効化されたSQLカーソルを最初にアクセスするセッションはハード解析が実行され、ハード解析が実行中にはlibrary cache pinを
排他(Exclusive)モードで獲得します。同じSQLカーソルを実行しようとする全てのセッションはハード解析が終了するまで
library cache pinイベントで待機します。

問題区間でlibrary cache pinイベントの待機が多発していることは、ハード解析の過程で多くの時間を消費していたことを表します。ハード解析に関連する２つ統計値のハード解析回数(parse count(hard)とハード解析時間(parse time elapsed)を確認すると下図のようにになります。

ハード解析回数は20回程度(10回→30回)増加して、ハード解析時間は最大20秒(2000 cent second)まで大幅に伸びていることが確認できます。
従って、alter index …コマンドによってSQLカーソルが無効化になる過程でハード分析に多くの時間がかかり、その過程でlibrary cache pinの待機時間が大幅に増加することになります。

DDL実行によってlibrary cache lock、library cache pin待機イベントの発生を防ぐことはできないため、トランザクションの盛んな時はDDLの実行を控えることが唯一の回避策となります。

不適切なDDL実行により、library cache lock、library cache pinの待機イベントが発生して性能低下現象が発生することがある。

運用中のシステムで不適切なDDL実行を控える。

MaxGaugeのリアル・モニタリング中に、15万秒以上を処理ができず、待機するセッションが確認できました。

Library Cache Lockは Library Cache ハンドルに対するロックですので、該当するオブジェクトを参照する必要があります。
その情報を確認するために、V$SESSIONまたはV$SESSION_WAITビューを利用します。10gにおいてはV$SESSIONでV$SESSION_WAIT内容をすべて確認することができます。

select sid, serial#, event, p1text, p1, p1raw, p2text, p2, p2raw, p3text, p3, p3raw
from v$session 
where event like 'library cache lock%'

v$sessionを確認した結果のうち、P3 カラムの301は排他(Exclusive)モード(3) * 100 + 1(Table/Procedure/Others)を意味します。
すなわち、テーブル / プロシージャ等の変更作業によってLibrary Cache Lockが発生しました。

ハンドル・アドレスのP1Raw値がすべて同じですので、同じオブジェクトによって変更作業が発生したことが確認できます。

x$kglob ビューを利用してP1Raw値を参照すると待機が発生したオブジェクトの特定ができます。

上記の方法でLibrary Cache Lockが発生した原因を追跡できます。