sequence

- 생성 구문

CREATE SEQUENCE SQ01_TH_SP_AUTH_HST
INCREMENT BY 1
START WITH 00000000001
MAXVALUE 99999999999
CYCLE
CACHE 100
;



- 시퀀스 생성 문법
CREATE SEQUENCE sequence_name
       [INCREMENT BY n1]
       [START WITH n2]
       [MAXVALUE n3 or NOMAXVALUE]
       [MINVALUE n4 or NOMINVALUE]
       [CYCLE or NOCYCLE]
       [NOORDER or ORDER ]
       [CACHE or NOCACHE];

sequence_name : 생성하고자 하는 시퀀스 명
INCREMENT BY n1 : 해당 시퀀스의 증가값을 지정한다. 생략하면 1로 지정됨.
오름차순 : 양의 정수, 내림차순 : 음의정수 사용
START WITH n2 : 해당 시쿼스가 처음 시작하는 값을 지정한다. 생략하면 1로 지정됨.
MAXVALUE n3 : 해당 시퀀스가 생성할 수 있는 최대값,
NOMAXVALUE : 오름차순 최대 10의 27승 까지, 내림차순 -1 까지 생성됨.
MINVALUE n4 : 해당 시퀀스가 생성할 수 있는 최소값
NOMINVALUE : 오름차순 1 까지, 내림차순 -10의 26승 까지
CYCLE : 해당 시퀀스가 최대값에 도달한 후 다시 처음부터 시퀀스를 생성한다.
NOCYCLE : 해당 시퀀스가 최대값에 도달해도 다시 생성하지 않는다.[DEFAULT]
CACHE : 시퀀스 값을 메모리에 할당하여 처리한다. [ORACLE DEFAULT 20]
NOCACHE : 시퀀스를 메모리에 할당하여 사용하지 않음.



# 수정
ALTER SEQUENCE 수정할 시퀀스 이름
    [INCREMENT BY n1]
    [MAXVALUE n3 or NOMAXVALUE]
    [MINVALUE n4 or NOMINVALUE]
    [CYCLE or NOCYCLE]
    [CACHE or NOCACHE];



# 다음 시퀀스 값 확인
 SELECT 시퀀스명.CURRVAL FROM DUAL   

SELECT * 
     FROM USER_SEQUENCES 
   WHERE SEQUENCE_NAME = UPPER('시퀀스명')

ALTER SEQUENCE SEQ_X INCREMENT BY 1;

 

 

 

---------- 주의 사항 ----------------------

* Oracle RAC 일 경우

 시퀀스 생성 시 ORDER 옵션 추가 할 필요성이 있음

 시퀀스 생성 시 cache 옵션을 무조건 사용 되므로,  order 옵션이 없을 경우 데이터 유입순서에 맞게 시퀀스 채번이 되지 않음. 

 

 

1) order 옵션 사용하지 않을 경우

cache  옵션은 시퀀스 채번 후 메모리에 미리 할당함. 

즉,  instance 1번에 시퀀스 1~100 메모리 할당, 

     instance 2번에 시퀀스 101 ~ 200 메모리 할당 됨 

 

서비스가, 1번 ,2번 instance  들어올지 예측이 되지 않으므로 서비스 유입 순서에 맞게 시퀀스 채번이 엉키는 현상비 발생됨 

 

 

* 추가적으로, 트랜잭션이 엄청나게 많을 경우 시퀀스 중복이 발생될 가능성이 있음, 

  그러나 이 중복 부분은 시퀀스 하나만을 가지고 데이터를 넣을 경우에만 발생됨

ex) NUM 컬럼의 데이터를  '시퀀스 + sysadate + 코드명 ' 이런식으로 조합하여 넣으면 중복이 발생되지 않음 

 

* 간혹 DBA 분들이 Order 옵션을 넣으면 속도가 느리지 않냐 라는 식으로 말하는 분들이 있음

   > 속도 차이가 조금 있으나, 큰 영향을 미치지는 않음 

   > 구글링 하면 외국에서 테스트한 결과가 있음 

 

* cache 옵션 

  시퀀스 생성 시 해당 옵션을 생략 하면 default 값으로 20 으로 만들어짐

  cache 값이 부족할 경우(해당 시퀀스를 사용하는 트랜잭션이 많을 경우) enq: SQ - contention 이벤트가 발생됨

 

 

enq: SQ - contention

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

 

Parameter & Wait Time

enq: SQ - contention 대기이벤트의 대기 파라미터는 다음과 같다.

P1 ＝ Enqueue 정보

P2 ＝ Sequence ID. DBA_OBJECTS.OBJECT_ID 컬럼과 조인 가능

P3 =  0

 

Wait Time

enqueue 대기이벤트와 동일하다. 최대 3초까지 기다린다.

만일 SQ 락을 획득하기 못하면 획득할 때까지 대기한다.

 

 

즉, 해당 이벤트가 발생되지 않도록 여유롭게 cache 값을 지정할 필요가 있다. 

     해당 이벤트가 발생되면 시퀀스 값을 늘려주면 된다.