Enq: SQ - contention

EXEM Knowledge Base

(SQ lock에서 넘어옴)
Jump to: navigation, 찾기

목차

[편집] Basic Info

오라클은 시퀀스를 관리하기 위해서 세 가지의 락을 사용한다.

  • row cache lock : Sequnece.nextval을 호출하는 과정에서 딕셔너리 정보를 물리적으로 변경하는 경우에 획득한다. NOCACHE 속성을 부여한 시퀀스에서 사용된다.
  • SQ 락 : 메모리에 캐시되어 있는 범위 안에서 Sequence.nextval을 호출하는 동안 획득한다. CACHE 속성을 부여한 시퀀스에서 사용된다.
  • SV 락 : RAC에서 노드간에 순서가 보장된 상태로 Sequence.nextval 을 호출하는 동안 획득한다. CACHE + ORDER 속성을 부여한 시퀀스에서 사용된다.

CACHE 속성이 부여된 시퀀스에 대해 nextval을 호출하는 동안, SQ 락을 SSX 모드로 획득해야 한다. 동시에 많은 세션이 SQ 락을 획득하기 위해 경쟁하는 과정에서 경합이 발생하면 enq: SQ - contention 이벤트를 대기하게 된다. enq: SQ - contention 이벤트의 P2 값은 시퀀스의 오브젝트 아이디이다. 따라서 P2의 값을 이용해 DBA_OBJECTS 뷰와 조인하면 어떤 시퀀스에 대해 대기현상이 발생하는지 알 수 있다.

[편집] Parameter & Wait Time

[편집] Wait Parameters

  • P1 : Enqueue 정보
  • P2 : Sequence ID. DBA_OBJECTS.OBJECT_ID 컬럼과 조인 가능
  • P3 : 0

[편집] Wait Time

enqueue 대기이벤트와 동일하다. 최대 3초까지 기다린다. 만일 US 락을 획득하지 못하면 획득할 때까지 대기한다.

[편집] Check Point & Solution

[편집] 시퀀스의 캐시 크기를 크게 부여한다.

DFS lock handle#Sequence의 Cache Size를 늘려라.

[편집] RAC 환경에서는 가능한 NOORDER 속성을 부여하라

RAC의 경우 Sequence에 CACHE+NOORDER속성을 부여하라.

[편집] Event Tip

[편집] 시퀀스와 인덱스 리프 노드 블록의 경합

많은 어플리케이션들이 시퀀스 값을 이용해서 PK 키를 관리한다. 이것은 일반적으로 바람직한 현상이라고 할 수 있으며, 오라클의 기본적인 권고안 중 하나이다. 하지만 시퀀스 값을 이용한 PK 키는 이른바 우편향 인덱스(Right-Hand Index) 현상을 일으킬 우려가 있다. 우편향 인덱스란 키 값이 순차적으로 증가하는 경우 인덱스의 오른쪽 최말단 리프 노드에만 인서트(Insert)가 집중되어 경합이 발생하는 현상을 말한다. 우편향 인덱스 현상은 굳이 시퀀스 객체를 쓰지 않더라도 순차적으로 증가하는 키 값을 사용하는 경우에는 항상 발생할 수 있다. 우편향 인덱스는 다음과 같은 면에서 성능에 부정적인 영향을 준다.

  • 오른쪽 말단 리프 노드에 인서트가 집중되므로 인덱스 분할(Index Split)이 빈번하게 발생한다. 잦은 인덱스 분할은 리두 크기 증가, 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 류의 이벤트 대기로 관찰된다.

다행히 우편향 인덱스에 의한 글로벌 핫 블록 현상은 시퀀스 캐시 크기를 증가시킴으로써 어느 정도 해소가 가능하다. 가령 두 개의 노드로 이루어진 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)

[편집] Analysis Case

[편집] 1. 시퀀스 캐시 크기 증가에 의한 SQ 락 경합 감소

아래와 같은 운영 환경에서 SQ 락 경합에 의한 enq: SQ - contention 이벤트 대기가 과도하게 발생하였다.

  • 여러 프로세스가 시퀀스 값을 얻기 위해 동시에 NEXTVAL을 호출하는 구조를 사용한다.
  • 시퀀스 캐시 크기는 기본값인 20으로 설정되어 있다

Maxgauge의 Real Time Monitoring툴을 이용해서 모니터링한 결과 아래와 같이 대부분들 세션들이 enq: SQ - contention 이벤트를 대기하는 것을 확인할 수 있다.

그림:contention_1.jpg

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 이벤트 대기가 줄어든 것을 확인할 수있다.

그림:contention_2.jpg


[편집] 2. 새로운 세션의 접속과 관련된 SQ 락 경합 현상

Active Session이 급증하는 성능 저하의 원인을 규명하기 위해 문제 시점의 Wait Events 발생내용을 확인 결과, Idle Event를 제외하고, SQ Enqueue 이벤트가 Top으로 나타난 것을 확인할 수 있다.

그림:3_3_1.jpg

Top Event로 나타난 SQ Enqueue와 Active session, wait 이벤트 발생 추이를 비교해 보면, 그래프의 증가 추이가 일치한다.

그림:3_3_2.jpg

Lock Tree 화면을 통해 대기하는 세션들과 SQ Enqueue가 발생하는 Object의 Number를 확인해보면, Object# = 144 로 나타난다.

그림:3_3_3.jpg

DBA_OBJECTS 딕셔너리 뷰의 조회 결과, 144번 오브젝트는 SYS의 AUDSES$ 라는 시퀀스로 확인된다.

그림:3_3_4.jpg

DBA_SEQUENCES 딕셔너리 뷰의 조회 결과, AUDSES$ 시퀀스의 cache 값이 default 인 20으로 설정되어 있다.

그림:3_3_5.jpg

SQ Enqueue를 대기하는 세션들의 로그온 시간을 확인해 보면, 경합 발생 바로 직전에 로그온 한 사실을 알 수 있다.

그림:3_3_6.jpg

[편집] SYS. AUDSES$ 시퀀스 (메타링크 문서 122230.1 참조)

DB에 세션이 접속하면, V$SESSION의 AUDSID를 할당 받으며, 이 값은 USERENV(‘SESSIONID’) 함수로 조회가 가능하다. AUDSID 값은 오라클 딕셔너리의 시퀀스에 의해 할당되며, 이 시퀀스가 바로 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 대기 문제를 해결할 수 있다.