Oracleは3つのロックを用いて、シーケンスを管理します。
CACHEオプションを指定したシーケンスでNEXTVALを実行した時は、SQロックをSSXモードで獲得します。
セッションでSQロックを獲得するため、同時セッションで競合が発生すると、enq:SQ – contention待機イベントで待機します。
enq:SQ – contention待機イベントのP2パラメータの値は、シーケンスのOBJECTIDを表します。
この値とDBA_OBJECTSビューを結合することで、どのシーケンスで待機が発生しているかが分かります。
1回の待機で最大3秒まで待ちますが、USロックを獲得するまで待機します。
SQロックの競合で問題が発生した場合は、キャッシュを大きくすることが唯一の解決策です。
シーケンスのキャッシュ・サイズが性能に及ぼす影響範囲は、 シングル・ノード環境よりRAC構成のようなマルチ・ノード環境の方が大きいです。 そのため、RAC環境ではできる限りCACHE + NOORDERオプションを指定し、十分なキャッシュ・サイズを確保することをお勧めします。 必ず順番を保護する必要がある場合には、CACHE + ORDERオプションを指定します。 しかし、順番を保護し同期を取るためインスタンス間では、メッセージのやり取りが必ず発生します。 このようなことから、ORDERオプションを指定した場合は、NOORODERオプションを指定した場合より性能は低下します。
アプリケーションでは、シーケンスを利用して主キーを管理します。 これは一般的によく使われている方法であり、Oracleでも推奨している項目の一つです。 しかし、シーケンスを付けた主キーは、索引のデータがある特定領域に集中する問題を起こす可能性があります。 キーの値が順次増加された場合、索引の一番右側のリーフ・ブロックにだけ集中して挿入が行われるため、競合が発生します。 このような現象は、シーケンスを使わなくても、順次に増加するキー値を使う場合では発生する可能性があります。 索引のデータが偏ると、下記のような影響を与えます。
索引の一番右側にデータが集中する現象はよく知らされている問題で、下記のような一般的な解決策が提示されています。
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ロックによるenq:SQ – contention待機イベントが過度に発生していました。
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待機イベント待機が減少したことが確認されます。
性能が低下した原因を調査するために問題時点の待機イベントの発生内容を確認すると、 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待機イベントの性能問題を解決できます。