2017.08.23
Enq: SQ – contention
目次
基本情報
Oracleは、シーケンスを管理するために、3つののロックを使用します。
- ・row cache lock:Sequence.nextvalを呼び出す過程でディクショナリ情報を物理的に変更する場合に獲得されます。
NOCACHE属性を付与したシーケンスで使用されます。 - ・SQロック:メモリにキャッシュされている範囲内でSequence.nextvalを呼び出し中に獲得されます。
CACHE属性を付与したシーケンスで使用されます。 - ・SVロック:RACでノード間の順序が保証された状態でSequence.nextvalを呼び出し中に獲得されます。
CACHE+ ORDER属性を付与したシーケンスで使用されます。
CACHE属性が付与されたシーケンスに対するnextvalを呼び出し中に、SQロックをSSXモードで獲得しなければなりません。同時に多くのセッションがSQロックを獲得するために競争する過程で競合が発生した場合enq:SQ – contentionイベントを待機することになります。enq:SQ – contentionイベントのP2の値は、シーケンスのオブジェクトIDです。したがってP2の値を利用してDBA_OBJECTSビューと結合する任意のシーケンスに対して待機現象が発生していることがわかります。
パラメータとウェイト時間
待機パラメータ
- ・P1 :Enqueue情報
- ・P2 :Sequence ID:DBA_OBJECTS.OBJECT_IDカラムと結合可能
- ・P3 :0
待機時間
enqueue待機イベントと同様に、最大3秒間待ちます。もしUSロックを獲得できない場合は、取得するまで待機します。
チェックポイントとソリューション
シーケンスのキャッシュ・サイズ変更
DFS lock handle#Sequenceのキャスサイズを増加させます。
RAC環境の場合
RAC環境の場合、SequenceにCACHE + NOORDER属性を付与します。
チェックポイントとソリューション
シーケンスとインデックスのリーフ・ノードのブロックの競合
多くのアプリケーションが、シーケンス値を利用してPK鍵を管理します。これは、一般的に望ましい現象であり、Oracleの基本的な動作の一つです。しかし、シーケンスの値を用いたPKキーは、いわゆる右偏向インデックス(Right-Hand Index)現象を起こす恐れがあります。右偏向インデックスとキーの値が順次増加した場合、インデックスの右側最末端のリーフ・ノードのみインサート(Insert)が集中して競合が発生する現象をいいます。右偏向インデックス現象はあえてシーケンスオブジェクトを使わなくても順次増加するキー値を使用している場合には、常に発生することになります。右偏向インデックスは次のような面でのパフォーマンスに悪影響を与えます。
・右末端のリーフノードにインサートが集中されるため、インデックスの分割(Index Split)が頻繁に発生します。
頻繁に発生するインデックスの分割は、REDOサイズの増加、TXロック競合の増加、DMLパフォーマンスの低下などの問題を
誘発します。インデックス分割によるTXロック競合はenq:TX – index contentionイベント待ちで見ることができます。
RACシステムでは、gc current splitイベントも一緒に見ることができます。
・複数のプロセスが同時にインサートを実行する場合には、右末端リーフノードがホット・ブロックになる可能性が高くなります。
ホット・ブロックは、バッファロック(Buffer Lock)の競合を誘発し、シングルインスタンス環境では、buffer busy waits
イベント待ちで、RACシステムでは、gc buffer busyイベントやgc cr/ current block busy、gc cr/ current grant busyなどの
イベント待ちで見られます。
右偏向インデックス現象はよく知られている問題であり、次のような一般的な解決策が提示されていいます。
・リバースインデックス(Reverse Index)を使用します。リバースインデックスを使用している場合は、インデックスの
リーフ・ノードに、ある程度ランダムに分散されるので、右偏向現象を減らすことができます。
・インデックスキーを変更します。順次増加していないキーの組み合わせを使用することにより右偏向現象を減らすことができます。
右偏向インデックスは、クラスタ内のすべてのインスタンスが同じリーフブロックを変更する過程で、グローバルブロックの競合が発生するため、RACシステムで追加のオーバーヘッドを誘発します。各インスタンスは、リーフブロックを変更するために、ブロックの最新バージョンを必要とします。各インスタンスが毎回ブロックを変更するので、最新のバージョンのブロックがノード間で絶えず送信される現象が発生するのです。つまりグローバル・ホット・ブロックが生じます。ローカルホット・ブロックは、バッファ・ロック競合を引き起こしてbuffer busy waits待機イベントで見られるのに対し、グローバル・ホット・ブロックは、グローバルバッファロック競合と余分なブロック転送を誘発し、gc buffer busyイベントやgc current requestなどのイベント待ちで見られます。
幸いなことに右偏向インデックスによるグローバル・ホット・ブロック現象は、シーケンスのキャッシュサイズを増加させることによってある程度解消が可能となります。例えば2つのノードで構成されるRACシステムでシーケンスのキャッシュサイズが10,000であるシーケンスの値をPKで使用するインデックスとしましょう。この場合、インスタンス1回{1〜10,000}、インスタンス2回{10,001〜20,000}のシーケンス値のセットを使用します。両方のインスタンスが、他の範囲のシーケンスセットを使用するため、同じリーフブロックを使用するために競争する確率が減少するのです。それだけグローバルブロックの競合が減少することになります。
このような意味で、RACシステムでのシーケンス使用にORDER属性を付与することは、パフォーマンスの面で非常に不利な決定となります。ORDER属性のシーケンスを使用する場合には、ノード間のシーケンス値を絶えず同期するだけでなく、各ノードが同じシーケンスのキャッシュ値のセットを使用するので、右偏向インデックスによるグローバル・ホット・ブロック現象を避けることができないことになるからです。
次の例は、2つのノードからなるRACシステムでシーケンスのキャッシュ・サイズとインデックスのリーフ・ブロックの競合の相関関係をテストしたものです。
-- Case 1: CACHEサイズが20であるシーケンス値 CREATE SEQUENCE seq_gc_current_request CACHE 20 NOORDER; -- 各ノードで同時に10個のセッションがインデックスを持つテーブルに対して、シーケンスキー値を利用してINSERTを実行します。 インデックスのリーフブロックの競合によるクラスタ待機イベントが広範囲に発生することを確認することができます。 Type=EVENT, Name=gc buffer busy, Value=36008(cs) Type=EVENT, Name=enq: SQ - contention, Value=30385(cs) Type=EVENT, Name=enq: TX - index contention, Value=14685(cs) Type=EVENT, Name=buffer busy waits, Value=8191(cs) Type=EVENT, Name=gc current block busy, Value=3770(cs) Type=EVENT, Name=gc current grant busy, Value=1420(cs) Type=EVENT, Name=gc current split, Value=1336(cs) Type=EVENT, Name=enq: TX - row lock contention, Value=1273(cs) Type=EVENT, Name=gc cr block 2-way, Value=1208(cs) Type=EVENT, Name=read by other session, Value=935(cs) Type=EVENT, Name=gc current block 2-way, Value=855(cs) Type=EVENT, Name=gc cr block busy, Value=626(cs) Type=EVENT, Name=row cache lock, Value=571(cs) Type=EVENT, Name=events in waitclass Other, Value=537(cs) Type=EVENT, Name=latch: library cache, Value=233(cs) Type=EVENT, Name=latch: cache buffers chains, Value=128(cs) Type=EVENT, Name=gc current retry, Value=97(cs) Type=EVENT, Name=gc cr multi block request, Value=77(cs) Type=EVENT, Name=latch: library cache pin, Value=75(cs) Type=EVENT, Name=library cache pin, Value=63(cs) Type=EVENT, Name=log file switch completion, Value=52(cs) Type=EVENT, Name=gc current multi block request, Value=37(cs) Type=EVENT, Name=latch: redo copy, Value=36(cs) Type=EVENT, Name=gc current grant 2-way, Value=29(cs) Type=EVENT, Name=enq: HW - contention, Value=27(cs) Type=EVENT, Name=enq: TX - allocate ITL entry, Value=16(cs) Type=EVENT, Name=library cache lock, Value=8(cs) Type=EVENT, Name=db file sequential read, Value=8(cs) Type=EVENT, Name=latch: shared pool, Value=6(cs) Type=EVENT, Name=enq: TM - contention, Value=3(cs) Type=EVENT, Name=gc cr block congested, Value=0(cs)
-- Case 2: CACHEサイズが1,000であるシーケンス値 CREATE SEQUENCE seq_gc_current_request CACHE 1000 NOORDER; -- 各ノードで同時に10個のセッションがインデックスを持つテーブルに対して、シーケンスキー値を利用してINSERTを実行します。 やはりインデックスリーフブロックの競合によるクラスタ待機イベントが広範囲に発生することを確認することができます。 しかし、CACHEサイズが20である場合に比べて、グローバルブロックの競合が多少減ったことを確認することができます。 Type=EVENT, Name=gc buffer busy, Value=31654(cs) Type=EVENT, Name=enq: TX - index contention, Value=30376(cs) Type=EVENT, Name=buffer busy waits, Value=16578(cs) Type=EVENT, Name=gc current block busy, Value=2801(cs) Type=EVENT, Name=enq: SQ - contention, Value=1986(cs) Type=EVENT, Name=events in waitclass Other, Value=1586(cs) Type=EVENT, Name=gc current grant busy, Value=1448(cs) Type=EVENT, Name=latch: library cache, Value=1216(cs) Type=EVENT, Name=gc cr block 2-way, Value=1010(cs) Type=EVENT, Name=gc current split, Value=797(cs) Type=EVENT, Name=enq: TX - row lock contention, Value=723(cs) Type=EVENT, Name=latch: cache buffers chains, Value=710(cs) Type=EVENT, Name=gc current block 2-way, Value=694(cs) Type=EVENT, Name=read by other session, Value=566(cs) Type=EVENT, Name=latch: library cache pin, Value=521(cs) Type=EVENT, Name=gc cr block busy, Value=246(cs) Type=EVENT, Name=latch: redo copy, Value=195(cs) Type=EVENT, Name=gc current retry, Value=155(cs) Type=EVENT, Name=enq: TX - allocate ITL entry, Value=135(cs) Type=EVENT, Name=enq: HW - contention, Value=111(cs) Type=EVENT, Name=undo segment extension, Value=101(cs) Type=EVENT, Name=gc current grant 2-way, Value=61(cs) Type=EVENT, Name=library cache lock, Value=42(cs) Type=EVENT, Name=row cache lock, Value=41(cs) Type=EVENT, Name=gc current multi block request, Value=33(cs) Type=EVENT, Name=library cache pin, Value=16(cs) Type=EVENT, Name=db file sequential read, Value=16(cs) Type=EVENT, Name=gc cr multi block request, Value=10(cs) Type=EVENT, Name=enq: TM - contention, Value=3(cs) Type=EVENT, Name=latch: shared pool, Value=2(cs) Type=EVENT, Name=gc cr grant 2-way, Value=0(cs)
分析事例
シーケンスのキャッシュ・サイズの増加によるSQロック競合の減少
以下のような動作環境で、SQロック競合によるenq:SQ – contention待機イベントが過度に発生します。
・複数のプロセスが、シーケンス値を得るために同時にNEXTVALを呼び出す構造を使用している。
・シーケンスのキャッシュサイズは、デフォルトで20に設定されている。
MaxgaugeのReal Time Monitoringツールを利用して監視した結果、下記のように、ほとんどのセッションにおいて、enq:SQ – contentionイベントを待機することを確認することができます。
V$ SESSION_WAITビューでenq:SQ – contentionイベントを待機しているセッションとそのシーケンスを追跡した結果は以下の通りです。
SQL> select sid, 2 chr(bitand(p1,-16777216)/16777215)|| 3 chr(bitand(p1, 16711680)/65535) "Lock", 4 bitand(p1, 65535) "Mode", 5 p2,p3 6 from v$session_wait 7 where event like 'enq: SQ%'; SID Lock Mode P2 P3 ---------- ---- ---------- ---------- ---------- 125 SQ 6 63120 0 127 SQ 6 63120 0 129 SQ 6 63120 0 131 SQ 6 63120 0 133 SQ 6 63120 0 136 SQ 6 63120 0 138 SQ 6 63120 0 139 SQ 6 63120 0 141 SQ 6 63120 0 144 SQ 6 63120 0 SQL> select object_name from dba_objects where object_id=63120; OBJECT_NAME -------------------------------- SEQ_SQ_ENQUEUE
シーケンスのキャッシュ競合を減らすためにシーケンスのキャッシュサイズを以下のように10,000に増やします。
-- CACHE属性を増加 ALTER SEQUENCE seq_sq_enqueue CACHE 10000;
シーケンスのキャッシュサイズを増加後、同じアプリケーションを監視した結果、次のようにenq:SQ – contention待機イベントが減ったことを確認することができます。
新しいセッションの接続に関するSQロック競合現象
Active Sessionが急増する性能低下の原因を究明するために、問題の発生時点のWait Events発生内容を確認した結果、Idle Eventを除き、SQ EnqueueイベントがTopに現れたことを確認することができます。
Top Eventで示されたSQ EnqueueとActive session、waitイベント発生の推移を比較すると、グラフの増加推移が一致するのがわかります。
Lock Tree画面を介して待機しているセッションとSQ Enqueueが発生するObjectのNumberを確認してみると、Object#=144で表示されます。
DBA_OBJECTS・ディクショナリ・ビューの照会結果、144のオブジェクトはSYSのAUDSES$というシーケンスにおいて確認できます。
DBA_SEQUENCES・ディクショナリ・ビューの照会結果、AUDSES$シーケンスのcache値がdefaultである20に設定されているのがわかります。
SQ Enqueueを待機するセッションのログオン時間を確認してみると、競合が発生する直前にログオンした事実を知ることができます。
SYS. AUDSES$ シーケンス
DBにセッションが接続すれば、V$ SESSIONのAUDSIDが割り当てられ、この値は、USERENV( ‘SESSIONID’)関数で照会が可能です。
AUDSID値は、Oracleディクショナリのシーケンスによって割り当てられ、このシーケンスがまさにSYS.AUDSES$を指します。
新しいセッションが接続すれば、SYS.AUDSES$のNEXTVALが増加し、セッションに新しい値が割り当てられることになります。
INTERNALで接続した場合、(connect internal、as sysdba、バックグラウンドプロセスなど)、SYS.AUDESE$に割り当てられません。
結論
SQ Enqueueが発生したオブジェクトは、ディクショナリのSYS.AUDSES$シーケンスで、新しいセッションの作成時にNEXTVALを利用してAUDSID値を生成します。
SQ Enqueueを待機するセッションは問題時点の直前にログオンしたことが確認され、SYS.AUDSES$のcache値はdefaultである20です。
キャッシュのサイズが小さい場合には、メモリに予めキャッシュされた値が急速に排出され、キャッシュの値が排出された場合には、ディクショナリ情報を物理的に変更して(row cache lock発生する)再キャッシュする作業を実行しなければなりません。キャッシュ中に、SQロックを継続して取得する必要があるので、enq:SQ – contentionイベント時間が増加することになります。
したがって、シーケンスのキャッシュ・サイズを大きく増やすこと、enq:SQ – contention待機問題を解決することができるのです。