Latch: shared pool

共有プール（shared pool）ラッチは、共有プール（shared pool）の基本メモリ構造であるヒープを保護する役割を行います。フリーチャンクを見つけるためにフリーリストをナビゲートして、適切なチャンクを割り当て、必要に応じてフリーチャンクを分割する一連の作業は、すべて共有プールラッチ（latch: shared pool ）を獲得した後にのみ可能となります。 共有プールラッチ（latch: shared pool ）を獲得する過程で競合が発生した場合共有プールラッチ（latch: shared pool ）イベントを待機します。

ヒープに関連する一連の作業は、ほとんど非常に早い時間内に完了するため、通常の場合には、共有プールラッチ（latch: shared pool ）からの競合は表示されません。しかし、次のような事実により、latch：shared pool待機が増加することがあります。

・共有プールラッチ（latch: shared pool ）は、基本的に、全体のインスタンスにだけが存在します。
　すなわち、一つの共有プール（shared pool）に1つの共有プールラッチ（latch: shared pool ）が使用されます。
　これは、共有プール（shared pool）の基本メモリ構造であるヒープのアーキテクチャに起因するものです。
　したがって、同時に複数のセッションがチャンクを割り当てる必要がある場合には、1つの共有プールラッチ
　（latch: shared pool ）を獲得するために競合が発生することになるのです。

・ハード解析がうまくいかない場合、チャンクを裂く（Split）現象が頻繁に発生して、これにより、フリーリスト
　に多数の小さいサイズのフリーチャンクがつく現象が発生します。このような現象をよく共有プール（shared pool）
　の断片化（fragmentation）と呼びます。共有プール（shared pool）の断片化により、フリーリストをナビゲート
　する時間が長くなり、その分共有プールラッチ（latch: shared pool ）を保有する時間が増えることになります。
  共有プール（shared pool）断片化は、共有プールラッチ（latch: shared pool ）競合を発生させる根本的な原因
  です。 これは、ORA-4031エラーまた断片化が原因で発生します。

ハード解析が発生した場合、新しいSQL情報を入れるチャンクを割り当て受けるためフリーリストを探索しなければなりません。このような一連の事象の間に1つのプロセスだけが共有プールラッチ（latch: shared pool ）を独占必要があるため、同時に多くのセッションがハード解析を実行した場合、共有プールラッチ（latch: shared pool ）待機が多く発生することになります。したがって、ハード解析が過度に発生しているシステムで共有プールラッチ（latch: shared pool ）待機を減らすために共有プール（shared pool）のサイズを増やすことは、非常に危険な発想だと言えます。 共有プール（shared pool）のサイズが大きくなると、それだけフリーリストの数とフリーリストから管理する必要があるフリーチャンクの数も増加します。したがってフリーリストをナビゲートするために、共有プールラッチ（latch: shared pool ）を支えている時間が増えるのです。それに比例して共有プールラッチ（latch: shared pool ）待機時間も増加することになります。

ラッチ　フリー（latch free）待機イベントと同じです。

ラッチ　フリー（latch free）待機イベントと同じです。

Oracle 9iの以降、共有プール（shared pool）を複数のサブプールとして最大7個まで分けて管理することができます。 _KGHDSIDX_COUNT隠しパラメータを使用すると、サブプールの数を管理することができます。オラクルは、CPUの数が4以上であり、共有プール（shared pool）のサイズが250M以上の場合、_KGHDSIDX_COUNTの値だけサブプールを作成して共有プール（shared pool）を管理します。サブプールは、それ自体が独立した共有プール（shared pool）に管理され、独自のフリーリスト（Freelist）、LRUリスト、共有プールラッチ（latch: shared pool ）を有します。したがって共有プール（shared pool）のサイズが大きい場合には、サブプールに分けて管理することにより、共有プールラッチ（latch: shared pool ）競合を減らすことができるのです。

ハード解析によって共有プールラッチ（latch: shared pool ）競合が発生した場合、別の解決策は、共有プール（shared pool）のサイズを小さくするものとなります。 共有プール（shared pool）のサイズが減っただけフリーリストごとにフリーチャンクの数も減少し、したがって、フリーリストのナビゲーションにかかる時間が減るからです。しかし、この場合、ORA-4031エラーが発生する確率が高くなり共有プール（shared pool）に常駐することができるオブジェクトの数が減少して、付加的なハード解析が誘発されることが欠点となります。この欠点を解消するためにdbms_shared_pool.keepプロシージャを使用して、頻繁に使用されるSQLカーソルやパッケージ、プロシージャなどを共有プール（shared pool）に永久常駐させる方法を使用することができます。 dbms_shared_pool.keepを利用して指定されたオブジェクトは、共有プール（shared pool）に永久常駐するようになり、alter system flush shared_poolコマンドでも下がらりません。要約すると、共有プール（shared pool）のサイズを削減し、同時にdbms_shared_poolパッケージを利用して、頻繁に使用されるオブジェクトをメモリに常駐させることがもう一つの方法となるのです。

カーソル共有手法を使用します。これは、カーソル共有と定数（Literal）を使用したSQL文を自動的にバインド変数を使用して変換し、カーソルが共有されるようにしてくれる機能をいいます。 カーソル共有機能は、既存のリテラルSQL(Literal SQL)をバインド変数に変換する時間的余裕がない場合にのみ使用することが望ましい使用法です。ただし、この機能を使用する場合、既存のSQL文の実行計画が変更される場合もありますので、適切なテストの後に適用することが必要です。

共有プール（shared pool）はSGA（System Global Area）を構成する要素です。 SGAの構成を取得します。

SQL> show sga

otal System Global Area  524288000 bytes
Fixed Size                 2007624 bytes
Variable Size            142609848 bytes
Database Buffers         373293056 bytes
Redo Buffers               6377472 bytes

共有プール（shared pool）はSGA領域のうち、Variable領域に属します。Variable領域はShared Pool+ Java Pool+ Large Pool+ Streams Poolなどで構成されます。共有プール（shared pool）はさらにいくつかの種類のメモリ領域に分けられます。代表的なものは、Library CacheとRow Cacheです。Shared Poolがどのメモリ領域に分けられるかは、V$ SGASTATビューを次のように照会することにより、確認可能です。

SQL> select count(*) from v$sgastat where pool = 'shared pool';

  COUNT(*)
----------
       599     --> 599 個のメモリー領域の分けられます。
 
-- 大きさが大きい順で20個を問い合わせてみると下記のとおりとなります。 
   各メモリー領域の大きさは動的に変わることになります。
SQL> select * from (
 select name, bytes from v$sgastat
 where pool = 'shared pool'
 order by bytes desc
 ) where rownum <= 20;


NAME                                BYTES
------------------------------ ----------
free memory                      38515152
ASH buffers                       8388608
sql area                          8074192
row cache                         7480368
library cache                     5792120
kglsim hash table bkts            4194304
KCB Table Scan Buffer             3981120
KSFD SGA I/O b                    3977064
CCursor                           2586248
private strands                   2396160
db_block_hash_buckets             2342912
PL/SQL MPCODE                     2261672
KQR M PO                          1860816
kks stbkt                         1572864
event statistics per sess         1555840
KGLS heap                         1485744
FileOpenBlock                     1447104
PCursor                           1339640
KTI-UNDO                          1286064
PL/SQL DIANA                      1282376

Shared Poolの構成要素は、大きく次のように区分されます。

・Permanent Area：process、sessioin、segmented array（enqueue、transaction、..）など。
　プロセスのリスト、セッションのリスト、Enqueueリスト、Transactionリストなどのリソースは、
　共有プール（shared pool）の永久領域（Permanent Area）に割り当てられ、インスタンスとその
　寿命を共にします。
・Library Cache：SQLステートメントを実行するのに必要なすべてのオブジェクト（SQL、テーブル、
　ビュー、プロシージャなど）に関する情報を管理します。
・Row Cache：Dictionary Cacheと呼び、Oracleが使用するディクショナリ情報を管理します。
・Reserved Area：予約領域。動的なメモリ割り当てのために、Oracleは予約領域を割り当てておきます。

共有プール（shared pool）はヒープ（Heap）と呼ばれるメモリ管理手法を用いて管理されます。 共有プール（shared pool）のヒープからメモリを割り当てを受けようとするすべてのプロセスは、必ず共有プールラッチ（latch: shared pool ）を獲得しなければなりません。例えば、ハード解析が発生した場合、プロセスは、新しいSQL文を格納する領域を共有プール（shared pool）から割り当てられなければならず、必ず共有プールラッチ（latch: shared pool ）を獲得しなければならないのです。 共有プールラッチ（latch: shared pool ）は、基本的に、全体のインスタンスにのみ存在し、必要なメモリ（チャンク）を割り当てられる全体の過程で保持する必要があります。したがって、同時に多くのプロセスが共有プール（shared pool）メモリを使用する場合は、ラッチを獲得する過程で競合が発生することになるのです。共有プールラッチ（latch: shared pool ）を獲得する過程で競合が発生した場合、共有プールラッチ（latch: shared pool ）イベントを待機します。

ヒープの基本的な事実を整理すると、次の通りとなります。

・Heap Manager（KGH、Kernel Generic Heap）によって管理され、Oracleの最も基本的なメモリ割り当て方法です。
　Heap Managerは、Memory Manager（KSM、Kernel Service Memory）が提供するサービスを利用して、動的に
　メモリを管理します。
　Library Cache Manager（KGL、Kernel Generic Library Cache）は、Heap Managerを使用して、Library Cache
　を管理します。

・共有プール（shared pool）は、基本的に一つの最上位のヒープ（Top-level heap）を有し、最上位のヒープは再び複数
　のサブ・ヒープ（Subheap）を含んでいます。サブ・ヒープはまた、彼自身のサブ・ヒープを含んでいて、ヒープとサブヒ
  ープは基本的に同じ構造を持ちます。ヒープの観点から見ると、ヒープ内の1つのチャンク（Chunk）がサブヒープへのポ
  インタの役割をします。Oracle 9i以降の適切な環境を満足する、複数の最上位のヒープを持つことができます。

・ヒープは、複数のエクステント（Extent）をリンクリスト（Linked List）形式で率いています。一つのエクステントは、
  物理的に一つのグラニュー（Granule）を使用します。グラニューは、Oracle 9i以降で使用される連続した物理メモリの
  塊の単位でSGAサイズが<1Gであれば4M、SGAサイズ> = 1Gであれば16Mです（10gあたり）。一つのエクステントは、複数
  のチャンク（Chunk）で構成されています。各チャンクの使用状況は、X $ KSMSPビューで照会可能です。サブヒープの場
  合には、エクステントのサイズは可変であり、物理的な観点から、共有プール（shared pool）のメモリは、グラニューを
  追加で積む形式に割り当てられるため、ヒープという用語を使用します。

・チャンクの状態は大きくfree（直ちに使用可能）、recr（Recreatable。再生成可能）、freeabl（Freeable　Sessionや
　Call区間の間だけ必要なオブジェクトを含んでいる状態）、perm（Permanent。永久。再生成不可）に分けられます。 
　freeあるいはrecr状態であるチャンクは再利用が可能です。

・ヒープはフリーリスト（Freelist）を管理するバケットテーブルを持ちます。各バケットは、フリーチャンク（Free chunk）
　のフリーリストをリンクリスト形式で持っています。一つのヒープに合計255個のバケットが存在し、バケットに格納できる
　フリーチャンクのサイズは、バケット番号が増加するにつれて順次に増加します。

・ヒープはLRUリストを持っています。 LRUリストはUnpinned recreatable chunk、すなわち現在使用中でなく、再生成可能
　なチャンクのリストです。

・最上位のヒープは予約フリーリスト（Reserved freelist）という別のフリーリストを管理します。予約フリーリストのサイ
　ズが大きいオブジェクトを保存するShared Pool内の予約領域（reserved part of shared pool）のフリーリスト情報を
　管理します。

ヒープ・ダンプ（Heap Dump）を利用すれば、ヒープの構造を物理的に観察することができます。

SQL> alter session set events 'immediate trace name heapdump level 2';

上記のコマンドを実行すると、User dump directoryにヒープ領域のダンプファイルが生じます。ファイルの内容を見てみると、以下の通りです。

******************************************************
HEAP DUMP heap name="sga heap"  desc=c000000002e15030

上記のようにヒープ・ダンプを実行してみるとヒープの構造についての正確な情報を得ることができます。各ヒープは再びサブヒープ構造を含むことができます。ヒープは、自分が含まれている特定のチャンクにサブヒープのアドレスを保存することにより、サブ・ヒープの位置を管理するのです。ヒープ・ダンプでは、次のように「ds」（Heap Descriptor）の値が設定されているチャンクがサブヒープの位置を保存しているチャンクです。

Chunk c00000009a6f2800 sz=1112 freeable  "CCursor"  ds=c0000000993293e8 

dsはHeap Descriptorという意味であり、ds= c0000000993293e8がサブヒープのアドレスを指します。次のコマンドを使用して、そのサブ・ヒープをダンプすることができます。

SQL> alter session set events 'immediate trace name heapdump_addr addr 0xc0000000993293e8';

サブヒープのダンプ内容を確認してみると、以下のようにヒープ・ダンプとほぼ同じ形式を持つことを知ることができます。サブヒープはエクステントサイズが可変であり、予約フリーリストを管理していない点を除けば、最上位のヒープと同じ構造を持ちます。

******************************************************
HEAP DUMP heap name="CCursor"  desc=c0000000993293e8
 extent sz=0x440 alt=32767 het=32 rec=9 flg=2 opc=0  ? エクステントのサイズが変動
 parent=c000000002e15030 owner=c00000009a6f3110 nex=0000000000000000 xsz=0x440
EXTENT 0 addr=c00000009a6f2818   ? 익스텐트
  Chunk c00000009a6f2828 sz=      504    perm      "perm           "  alo=96
  Chunk c00000009a6f2a20 sz=      384    free        "               "
  Chunk c00000009a6f2ba0 sz=       32     freeable  "kksfbc:hash1   "
  Chunk c00000009a6f2bc0 sz=      152    freeable  "kgltbtab       "
EXTENT 1 addr=c00000009a6f2c70
  Chunk c00000009a6f2c80 sz=     1072    perm      "perm           "  alo=1040
EXTENT 2 addr=c00000009a6f30e0
  Chunk c00000009a6f30f0 sz=      776     perm      "perm           "  alo=776
  Chunk c00000009a6f33f8 sz=      240     perm      "perm           "  alo=232
  Chunk c00000009a6f34e8 sz=       32     free        "               "
Total heap size    =     3192
FREE LISTS:    ? 프리리스트
 Bucket 0 size=0
  Chunk c00000009a6f34e8 sz=       32     free       "               "
  Chunk c00000009a6f2a20 sz=      384    free       "               "
Total free space   =      416
UNPINNED RECREATABLE CHUNKS (lru first):  ? LRU 리스트
PERMANENT CHUNKS:   ?   Permanent Chunks
  Chunk c00000009a6f2828 sz=      504    perm      "perm           "  alo=96
  Chunk c00000009a6f33f8 sz=      240     perm      "perm           "  alo=232
  Chunk c00000009a6f2c80 sz=     1072    perm      "perm           "  alo=1040
  Chunk c00000009a6f30f0 sz=      776     perm      "perm           "  alo=776
Permanent space    =     2592
******************************************************

下の図は、ハード解析が過度に発生するアプリケーションを監視した結果です。

ハード解析により共有プールラッチ（latch: shared pool ）待機イベントが起きていることを確認することができます。

次の結果は、V$ SESSION_EVENTビューとV$ SESSTATビューで関連待機現象と仕事量を分析したもので共有プールラッチ（latch: shared pool ）のイベントが最も一般的に発生することを確認することができます。また、ハード解析回数が全体の解析回数と同じくらい過度に発生することを確認することができます。

実行結果         Type= EVENT、Name = latch：shared pool、Value=112（cs）

Type=EVENT, Name=latch: library cache, Value=72(cs)
Type=EVENT, Name=events in waitclass Other, Value=7(cs)
Type=EVENT, Name=library cache load lock, Value=3(cs)
Type=EVENT, Name=library cache pin, Value=3(cs)
Type=EVENT, Name=row cache lock, Value=1(cs)
Type=EVENT, Name=latch: row cache objects, Value=1(cs)
Type=EVENT, Name=latch: library cache pin, Value=0(cs)
Type=EVENT, Name=latch: library cache lock, Value=0(cs)
Type=EVENT, Name=latch: cache buffers chains, Value=0(cs)
Type=EVENT, Name=cursor: mutex S, Value=0(cs)
Type=EVENT, Name=enq: TX – row lock contention, Value=0(cs)
Type=EVENT, Name=buffer busy waits, Value=0(cs)
Type=STATS, Name=session pga memory max, Value=3325096
Type=STATS, Name=session logical reads, Value=42162
Type=STATS, Name=execute count, Value=40570
Type=STATS, Name=parse count (total), Value=40268
Type=STATS, Name=parse count (hard), Value=40063
Type=STATS, Name=parse time elapsed, Value=7195
Type=STATS, Name=redo size, Value=5692
Type=STATS, Name=session cursor cache hits, Value=409
Type=STATS, Name=sorts (memory), Value=199
Type=STATS, Name=redo entries, Value=11
Type=STATS, Name=user commits, Value=4
Type=STATS, Name=physical reads, Value=0
Type=STATS, Name=sorts (disk), Value=0

カーソル共有手法を適用することにより、ハード解析を避けることができます。以下のようなSQL文を使用してCURSOR_SHARINGパラメータ値をFORCEに変更すると、すべてのLiteral SQLが自動的にパラメータの処理が行われるます。

ALTER SYSTEM SET CURSOR_SHARING=FORCE SCOPE=MEMORY;

次の結果は、カーソル共有を適用した後のパフォーマンスの測定結果です。共有プールラッチ（latch: shared pool ）待機が消え、ハード解析回数が大きく減少したことが確認できます。

実行結果　　　　　　　　Type= EVENT、Name = events in waitclass Other、Value=6（cs）

Type=EVENT, Name=latch: library cache, Value=72(cs)
Type=EVENT, Name=events in waitclass Other, Value=7(cs)
Type=EVENT, Name=library cache load lock, Value=3(cs)
Type=EVENT, Name=library cache pin, Value=3(cs)
Type=EVENT, Name=row cache lock, Value=1(cs)
Type=EVENT, Name=latch: row cache objects, Value=1(cs)
Type=EVENT, Name=latch: library cache pin, Value=0(cs)
Type=EVENT, Name=latch: library cache lock, Value=0(cs)
Type=EVENT, Name=latch: cache buffers chains, Value=0(cs)
Type=EVENT, Name=cursor: mutex S, Value=0(cs)
Type=EVENT, Name=enq: TX – row lock contention, Value=0(cs)
Type=EVENT, Name=buffer busy waits, Value=0(cs)
Type=STATS, Name=session pga memory max, Value=3325096
Type=STATS, Name=session logical reads, Value=42162
Type=STATS, Name=execute count, Value=40570
Type=STATS, Name=parse count (total), Value=40268
Type=STATS, Name=parse count (hard), Value=40063
Type=STATS, Name=parse time elapsed, Value=7195
Type=STATS, Name=redo size, Value=5692
Type=STATS, Name=session cursor cache hits, Value=409
Type=STATS, Name=sorts (memory), Value=199
Type=STATS, Name=redo entries, Value=11
Type=STATS, Name=user commits, Value=4
Type=STATS, Name=physical reads, Value=0
Type=STATS, Name=sorts (disk), Value=0

カーソル共有手法を適用することにより、ハード解析を避けることができます。以下のようなSQL文を使用してCURSOR_SHARINGパラメータ値をFORCEに変更すると、すべてのLiteral SQLが自動的にパラメータの処理が行われます。

ALTER SYSTEM SET CURSOR_SHARING=FORCE SCOPE=MEMORY;

次の結果は、カーソル共有を適用した後のパフォーマンスの測定結果です。共有プールラッチ（latch: shared pool ）待機が消え、ハード解析回数が大きく減少したことが確認できます。

実行結果　　　　　　　　　　Type= EVENT、Name = events in waitclass Other、Value=6（cs）

Type=EVENT, Name=library cache load lock, Value=3(cs)
Type=EVENT, Name=library cache pin, Value=3(cs)
Type=EVENT, Name=cursor: mutex S, Value=0(cs)
Type=EVENT, Name=enq: TX – row lock contention, Value=0(cs)
Type=EVENT, Name=latch: library cache, Value=0(cs)
Type=EVENT, Name=buffer busy waits, Value=0(cs)
Type=EVENT, Name=row cache lock, Value=0(cs)
Type=STATS, Name=session pga memory max, Value=2800808
Type=STATS, Name=execute count, Value=40391
Type=STATS, Name=session cursor cache hits, Value=40263
Type=STATS, Name=parse count (total), Value=40199
Type=STATS, Name=redo size, Value=4932
Type=STATS, Name=session logical reads, Value=1655
Type=STATS, Name=parse time elapsed, Value=151
Type=STATS, Name=sorts (memory), Value=119
Type=STATS, Name=parse count (hard), Value=52
Type=STATS, Name=redo entries, Value=16
Type=STATS, Name=user commits, Value=4
Type=STATS, Name=physical reads, Value=0
Type=STATS, Name=sorts (disk), Value=0
Type=TIME, Name=parse time elapsed, Value=190(cs)
Type=TIME, Name=hard parse elapsed time, Value=23(cs)
Type=TIME, Name=sql execute elapsed time, Value=5(cs)
Type=TIME, Name=DB time, Value=2(cs)

同時ユーザーが多いOLTPOn-Line Transaction Process）とWEB環境では、Literal SQLの過度の使用は、パフォーマンス上の深刻な問題を引き起こす場合が多いことがあります。Oracle DBMSの性能診断/分析ツールMaxGauge（マックスゲージ）を活用し、Literal SQLの過度の使用によって発生したパフォーマンスの低下の問題の原因を究明してみましょう。

パフォーマンスの問題が発生したインスタンスの収集された稼働履歴ログから日間の推移グラフを確認してみると、「CPU使用率」には、明確な変化がないように見え、10時17分をPeakで「Active Session」と「Wait Events」が急増していることを簡単に確認することができます。

■CPU使用率の推移グラフ

■Active Session数の推移グラフ

■待機イベントの推移グラフ（待機時間）

アクティブセッションの急増による性能低下（Performance Slow-Down）の原因を究明するために、問題の時点（10時17分）の待機イベントの発生内容を確認してみます。

「Stat/ Wait」で同時点のTop Wait Eventを確認した結果、Idle Event（= SQL* Net message from client）を除いたTop Wait Eventはlatch freeであることが確認できます。

アクティブセッションの急増のラッチフリー待機イベントの関連性を規定するために、待機イベントとの発生パターンを比較した結果、「Active Session」の発生推移と非常に類似しており、問題の時点で発生したWait Events（Wait Time）の約74％（全392.53秒の中で、291.91秒を占有する）を占めていることから、Active Sessionの急増は、latch free待機イベントの急激な発生と関連があることを推測することができます。

実際には、同じ時点の詳細データを表示する表画面でも、latch free待機イベントがTop Wait Eventであり、その中でもlatch free（shared pool）とlatch free（library cache）待機イベントが多く発生していることを確認することができます。

ラッチフリー待機イベントの発生原因はいくつかありますが、一般的にラッチフリー（共有プール（shared pool））とラッチフリー（ライブラリキャッシュ）待機イベントが同時に発生した場合には、ハード解析がその原因である場合が多くあります。その内容を確認するために解析関連指標であるparse count（hard）、parse time cpu、parse time elapsedの推移を確認してみます。

推移グラフの分析の結果、アクティブセッションが急増した時点で、parse time elapsedの値が20,000 centi-sec/ sec（=200sec/ sec）で高くなっており、パフォーマンスの低下現象が発生する前（09時26分）のparse count（hard）の値が200回/秒に発生した事実を確認することができます。これにより、前の時点の過度なハード解析によるSQLによって共有プール（shared pool）にmemory fragmentationが発生し、これにより、ライブラリキャッシュでのSQL検索や新規SQLの登録などを行うことが時に遅延が発生していると分析できます。

ハード解析の実行が多かった09時00分〜09時30分では、parse count（hard）の値が「1秒間に10回以上」であるセッションを検索した結果、「1秒間に100回以上」ハード解析を実行したセッションは、ほとんどが「JDBC Thin Client」プログラムであることを確認することができます。

過度にハード解析を実行している、上記のセッションを調べた結果、ほとんどが同様のSQLを使用しており、バインド変数を使用していない「Literal SQL」であることがわかります。

ラッチフリー待機イベントの急増によるActive sessionの急増☞

バインド変数を使用していないLiteral SQLによるハード解析の余分な実行

1. リテラルSQLのバインド化

2. Prepared Statementの使用によるJDBC PG内のリテラル SQLの削除