enq: SQ – contention

Oracle待機イベント

Oracle待機イベント情報


目次[非表示]

  1. 1.概要
  2. 2.待機パラメータと待機時間
    1. 2.1.待機パラメータ
    2. 2.2.待機時間
  3. 3.チェックポイントと解決策
    1. 3.1.シーケンスのキャッシュ値を大きくする
    2. 3.2.NOORDERオプションを指定する(RAC環境)
  4. 4.豆知識
    1. 4.1.シーケンスと索引リーフ・ブロックの競合
  5. 5.分析事例
    1. 5.1.シーケンスのキャッシュ・サイズとSQロック競合現象
    2. 5.2.セッションの新規接続とSQロック競合現象
      1. 5.2.1.結論

概要

　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パラメータの値は、シーケンスのOBJECTIDを表します。
この値とDBA_OBJECTSビューを結合することで、どのシーケンスで待機が発生しているかが分かります。


待機パラメータと待機時間

待機パラメータ

  • P1 ： エンキュー情報(タイプとモードを含む)
  • P2 ： SEQUENCEIDはDBA_OBJECTS.OBJECT_IDカラムと結合が可能
  • P3 ： 0


待機時間

1回の待機で最大3秒まで待ちますが、USロックを獲得するまで待機します。


チェックポイントと解決策

シーケンスのキャッシュ値を大きくする

　SQロックの競合で問題が発生した場合は、キャッシュを大きくすることが唯一の解決策です。

NOORDERオプションを指定する(RAC環境)

　シーケンスのキャッシュ・サイズが性能に及ぼす影響範囲は、シングル・ノード環境よりRAC構成のようなマルチ・ノード環境の方が大きいです。
　そのため、RAC環境ではできる限りCACHE + NOORDERオプションを指定し、十分なキャッシュ・サイズを確保することをお勧めします。
　必ず順番を保護する必要がある場合には、CACHE + ORDERオプションを指定します。
　しかし、順番を保護し同期を取るためインスタンス間では、メッセージのやり取りが必ず発生します。
　このようなことから、ORDERオプションを指定した場合は、NOORODERオプションを指定した場合より性能は低下します。


豆知識

シーケンスと索引リーフ・ブロックの競合

　アプリケーションでは、シーケンスを利用して主キーを管理します。
　これは一般的によく使われている方法であり、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待機イベントで待機が発生します。

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

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

　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待機イベントの性能問題を解決できます。




前の記事

prev-article-image

大量のDML作業のためのパフォーマンス改善方法

次の記事

next-article-image

Row Cache Lock

CONTACT

他社に頼らず自社でデータベースを監視・運用をしませんか？
MaxGaugeがサポートします

お役立ち資料は
こちらから

資料ダウンロード

不明点がある方は、
こちらからお問い合わせください

お問い合わせ

お電話でのお問い合わせはこちら

03-3518-0771

平日 10時～18時

人気記事ランキング

タグ一覧

logo_img
ページトップへ戻る

©EXEM JAPAN Corp. All Rights Reserved.