시퀀스

SQL> select dbms_metadata.get_ddl('SEQUENCE', 'TEST_SEQUENCE', user) defn from dual;

DEFN
------------------------------------------------------
CREATE SEQUENCE "TEST_USER"."TEST_SEQUENCE"
MINVALUE 1 MAXVALUE 99999999999999999999999999
INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER
NOCYCLE

-- CACHE 20 [NOCACHE] <- RAC 환경에서 문제 요소
-- NOORDER [ORDER] <- RAC 환경에서 문제 요소

시퀀스 캐싱

• 시퀀스 캐시는 전통적인 캐시와 다른 "목표" 이다, 시퀀스 캐시를 100 으로 설정 한다고 해서 100 개의 번호를 생성 저장하지 않음
• 시퀀스는 SGA 내에 캐시 된다.

시퀀스 동작원리

• 시퀀스는 SEQ$ 에 1개 레코드로 정의 됨 (HIGHWATER = 1, CACHE = 20)
• 시퀀스 최초 채번시 시작 값은 캐시 크기에 의해 증가 되고, SEQ$.HIGHWATER에 기록 됨 (HIGHWATER = 21)
• 이후 메모리에서 CURRENT, TARGET 값을 관찰 함
• NEXTVAL 요청시 CURRENT 값을 증가 하고, TARGET 값과 비교한 후 값을 제공
  • CURRENT 값 = TARGET 값 : TARGET 값이 캐시 크기에 의해 증가 되고, SEQ$.HIGHWATER에 기록 됨, HIGHWATER = 41

create sequence test_sequence;

declare
	m_n	number;
begin
	for i in 1..1000 loop
		m_n := test.sequence.nextval;
	end loop;
end;
/

-- SEQ$ 에 대해서 INSERT 1 번, UPDATE 50 번 수행 됨 : 20(CACHE) * 50(UPDATE) = 1000(LOOP)
-- V$ENQUEUE_STAT (TX, TM, SQ ENQUEUE) 에서도 50 관찰 가능
-- V$ROWCACHE (DC_SEQUENCES / MODIFICATIONS, FLUSHES) 에서도 50 관찰 가능

• 캐시 기본 값(20) 상태에서 수 많은 NEXTVAL 을 호출 한다면, 다수의 UPDATE(REDO)가 발생 - 옳지않음(SINGLE/RAC)
• SQ LOCK 및 DC_SEQUENCES 에 대한 딕셔너리 캐시 관리 래치 경합 발생
• RAC 에서는 클러스터 내에서 SEQ$ 블록의 끊임 없는 이동으로 인한 부작용이 추가 됨
  • SEQ$ 테이블은 블록당 80~90 레코드 저장 (소수의 시퀀스 혹은 인스턴스간 다른 시퀀스 사용 시 에도 부작용 발생 가능, buffer busy wait / gc buffer busy wait 이벤트)

Ordered 시퀀스

• RAC 에서 각 인스턴스는 시퀀스에 대한 각자의 세트를 생성 (4노드 RAC, CACHE 20 : INST#1[101~120], INST#2[121~140]...)
• 시퀀스 값이 생성되고 사용되는 순서가 순차적이 아님, 시스템 다운시 번호가 점프
• NOCACHE, ORDER : SEQ$ 블록 대량 캐시 전송 유발
• ORDER : 각 인스턴스는 시퀀스에 대한 각자의 세트를 생성, HIGHWATER, CURRENT 값을 가진 메모리 위치는 하나임 (SV LOCK)
  • SV LOCK : RAC 모드에서 순차적인 시퀀스 할당을 보장하기 위한 락, DFS lock handle 대기 발생 (락모드 5)
  • 자주 사용되는 시퀀스에 ORDER 적용시, latch: ges resource hash list, row cache lock 대기 발생

RAC 락 모드

시퀀스, ORDER, 및 AUDIT

• NOCACHE + ORDER 속성으로도 연속적 순차번호를 보장할 수 없다 (NEXTVAL 후 ROLLBACK 케이스)

시퀀스와 인덱스

• RAC 환경에서 시퀀스로 얻은 연속적 번호를 인덱스 컬럼값 으로 사용한다면, 인스턴스간 hot-spot이 발생하고 gc buffer busy waits 을 유발 (SYSDATE 와 같이 항상 증가하는 값을 사용하는 경우 포함)

인덱스 ITL 이슈

• B-tree 인덱스 구현 내 오래된 결함(버그)
  • 리프 블록이 분할 될 때, 다수의 프로세스가 동시에 해당 리프 블록에 데이터를 입력 하려 하면, 리프 불록 내 불필요하게 많은 ITL 엔트리가 생성 됨 (늘어난 ITL 엔트리는 줄지 않음)
  • 해결책 : 인덱스의 maxtrans 적절히 조정 (하지만 10g 이상 부터 maxtrans 무시, 12.1 에서 해결 예정 이며 backport 제공)
• ASSM(Automatic Segment Space Management)은 인스턴스간 서로 다른 비트맵 블록을 사용하도록 허용함 (인스턴스간 입력 분리로 테이블에 대한 gc buffer busy waits 방지, 인덱스는 hot-spot 해결 불가)
  • 해결책1) reverse-key 인덱스 - INDEX RANGE SCAN 불리
  • 해결책2) 큰 캐시 크기 설정 (5000 ~ 50000) - 리프 블록 분할의 대부분이 50/50
  • 해결책3) 인덱스 해시 파티션 적용 - 16개 해시 파티션 적용, hot-spot -> warm-spot (1/16)