enq: SQ – contention

アプリケーションでは、シーケンスを利用して主キーを管理します。 これは一般的によく使われている方法であり、Oracleでも推奨している項目の一つです。 しかし、シーケンスを付けた主キーは、索引のデータがある特定領域に集中する問題を起こす可能性があります。 キーの値が順次増加された場合、索引の一番右側のリーフ・ブロックにだけ集中して挿入が行われるため、競合が発生します。 このような現象は、シーケンスを使わなくても、順次に増加するキー値を使う場合では発生する可能性があります。 索引のデータが偏ると、下記のような影響を与えます。

• 索引の一番右側のリーフ・ブロックにだけ挿入が集中するため、索引の分割が頻繁に発生します。 索引の分割が頻繁に起きると、REDOの増加、TXロックの増加、DML時の性能低下などの問題を引き起こします。 索引の分割でTXロックが発生した場合は、enq:TX – index contention待機イベントが発生します。 RAC環境の場合、gc current split待機イベントも同時に発生します。
• 複数のセッションが同時に挿入を実行する場合、一番右側のリーフ・ブロックがホット・ブロックになる可能性が高くなります。 ホット・ブロックはバッファ・ロック競合を引き起こし、シングル・インスタンス環境ではbuffer busy waits待機イベントが発生し、 RAC環境ではgc buffer busy待機イベントやgc cr/current block busy、gc cr/current grant busy待機イベントで待機が発生します。

索引の一番右側にデータが集中する現象はよく知らされている問題で、下記のような一般的な解決策が提示されています。

1. 逆キー索引を使います。
   逆キー索引を使う場合には、索引のリーフ・ブロックにある程度ランダムにデータが分散されるため、 一番右側にデータが偏る現象を減らすことができます。
2. 索引キーを変更します。
   順次に増加しないキーを使うことによって一番右側にデータが偏る現象を減らすことができます。

RAC環境では一番右側にデータが偏ると、追加のオーバー・ヘッドが発生します。 なぜなら、クラスタ内の全てのインスタンスが同じリーフ・ブロックを更新する場合、グローバル・ブロック競合が発生するからです。 それぞれのインスタンスでリーフ・ブロックを更新するためには、ブロックの最新情報が必要です。 毎回各インスタンスによってブロックが更新されるため、最新のブロックの情報が頻繁にノード間で転送される現象が発生します。 すなわち、グローバル・ホット・ブロックが発生するのです。 ローカル・ホット・ブロックはバッファ・ロック競合を引き起こし、buffer busy waits待機イベントで待機する一方、 グローバル・ホット・ブロックはグローバル・バッファ・ロック競合及び過剰なブロック送信が発生し、gc buffer busy待機イベントやgc current requestなどの 待機イベントで待機が発生します。

索引の一番右側のリーフ・ブロックにデータが偏るために発生するグローバル・ホット・ブロック現象は、 シーケンスのキャッシュを増やすことである程度解消することが可能です。 例えば、2ノード構成のRAC環境でシーケンスのキャッシュ・サイズが10,000であるシーケンスの値を主キーとして使う 索引があると仮定します。この場合、インスタンス1は{ 1 ～ 10,000 }を、インスタンス2は{ 10,001 ～ 20,000 }のシーケンス値を使います。 2つのインスタンスがそれぞれ別の範囲のシーケンスを使うことによって、同じリーフ・ブロックへの競争が減少し、 それだけグローバル・ブロックの競合も減ります。

このように、RAC環境でシーケンスを使用する時、ORDERオプションを指定するのは性能面で効率がよくないということが言えます。 その理由は、前述したとおり、ORDERオプションを指定したシーケンスを使う場合にはノード間にシーケンス値を頻繁に同期化するだけでなく、 各ノードで同一のシーケンスのキャッシュ値を使う場合、索引の一番右側リーフ・ブロックにデータが集中してしまい、グローバル・ホット・ブロック現象が 発生するためです。

ここでは、2ノード構成のRAC環境でシーケンスのキャッシュ・サイズと索引リーフ・ブロックで競合が発生する現象の関連性を確認します。

-- Case 1: キャッシュのサイズが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: キャッシュのサイズが1,000であるシーケンス

CREATE SEQUENCE seq_gc_current_request CACHE 1000 NOORDER;

--各ノードで同時10セッションが索引がある表にシーケンスキーを利用しINSERTを実行します。
--相変わらず索引リーフ・ブロック競合によるクラスタ待機イベントは広く発生します。
--しかし、キャッシュのサイズが20だった場合に比べ、
--グロパル・ブロック競合が少し減ったことが分かります。

 br br br br br br br br br br br br br br br br br br br br br br br br br br br br br br br br br br br br br br
 
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で確認すると、多くのセッションがenq:SQ – contention待機イベントで待機しています。

enq:SQ – contention待機イベントで待機しているセッションとその対象のシーケンスをV$SESSION_WAITビューで追跡します。

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まで十分増やします。

-- キャッシュのサイズ増加
ALTER SEQUENCE seq_sq_enqueue CACHE 10000;

シーケンスのキャッシュ・サイズを増やした後、もう一度その結果を確認すると、 enq:SQ – contention待機イベント待機が減少したことが確認されます。

セッションの新規接続とSQロック競合現象

性能が低下した原因を調査するために問題時点の待機イベントの発生内容を確認すると、 enq:SQ – contention待機イベントが上位に上がっています。

enq:SQ – contention待機イベントと「Active session」、「wait」の発生状況を比較すると、グラフの増加推移が一致します。

「Lock Tree」を確認すると、enq:SQ – contention待機イベントで待機しているセッションがアクセスしているオブジェクトの番号は、 「Object# = 144」です。

DBA_OBJECTSビューで、144のオブジェクトはSYSのAUDSES$というシーケンスであることが分かります。

DBA_SEQUENCESビューで確認すると、AUDSES$シーケンスのキャッシュ値がデフォルトの20で設定されています。

enq:SQ – contention待機イベントで待機するセッションを確認すると、 競合が発生する直前ログオンされています。

結論

データベースに接続したセッションはV$SESSIONのAUDSIDを割り当てられます。この値はUSERENV(‘SESSIONID’)関数で確認することが可能です。 AUDSID値はOracleディクショナリのシーケンスによって割り当てられますが、このシーケンスが上記のSYS.AUDSES$です。 新しくセッションが接続すると、SYS.AUDSES$のNEXTVALが増加し、セッションには新しいAUDSID値が割り当てられます。 ただし、INTERNALで接続する場合(connect internal、as sysdba、バックグラウンド・プロセスなど)は、新しいAUDSIDの値は割り当られません。

この事例では、enq:SQ – contention待機イベントが発生したオブジェクトはSYS.AUDSES$シーケンスで、 新しいセッションが接続された時、NEXTVALでAUDSID値が生成されています。

また、enq:SQ – contention待機イベントで待機しているセッションは問題時点の直前にログオンしており、 SYS.AUDSES$シーケンスのキャッシュ値はデフォルトの20です。

キャッシュ・サイズが小さい場合は、メモリーにあらかじめキャッシュされていた値がすぐに消費されます。 キャッシュ値を使い切った場合は、ディクショナリ情報を更新し(行キャッシュ・ロックが発生)、再度キャッシュをする作業が行われます。 再度キャッシュを行うと、その間はSQロックを獲得しておかなければならないため、enq:SQ – contention待機イベントで待機する時間が増加します。

従って、シーケンスのキャッシュ・サイズを増やすことで、enq:SQ – contention待機イベントの性能問題を解決できます。