바로 지난번 글에 시퀀스에 대한 기초적인 개념과 옵션에 대한 설명을 포스팅했습니다.
사실 이번 글을 위해서 지난 개념을 정리한 것이라고 볼 수 있는데요,
멀티 인스턴스(예 RAC)기반에서 시퀀스를 사용하다가 동일한 시퀀스를 사용하는데도 번호가 다르게 올라가는 현상이 발생하였고 그것이 CACHE 옵션 때문이라는 것을 알게되었습니다.
그래서 그것에 대한 옵션 유무에 따른 차이가 있는지 덧붙여서 Order 옵션에 대한 테스트도 진행하고 정리해보도록 하겠습니다.
(아래의 테스트는 티베로 TAC의 테스트에서도 동일한 결과를 가져왔습니다. 티베로에서도 동일하다고 생각하면 됩니다)
https://bae9086.tistory.com/295
Sequence(시퀀스)
시퀀스는 객체로써 자동으로 증가하는 숫자를 사용할 수 있습니다. 시퀀스는 순차적으로 생성되며 Unique한 속성을 가지고 있을 수 있으므로 PK같은 컬럼에 사용하기에 용이합니다. 생성 문법 CREA
bae9086.tistory.com
테스트 : 여러 옵션으로 시퀀스를 생성하고 그에따른 시퀀스 숫자 올라가는것을 확인
1.CACHE , NOORDER 옵션 (Default)
1 시퀀스 생성
CACHE 옵션일 경우 숫자가 컬럼값에 가지고 있으며 O 컬럼에 N이므로 NOORDER입니다.
SQL > CREATE SEQUENCE SYS.SEQ_TEST
INCREMENT BY 1 START WITH 1 NOMAXVALUE
MINVALUE 1 NOCYCLE CACHE 30 NOORDER ;
SQL> select * from dba_sequences where sequence_name='SEQ_TEST';
SEQUENCE_OWNER SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_B C O CACHE_SIZE LAST_NUMBER
------------------------------ ------------------------------ ----------- ----------- ----------- - - ----------- -----------
SYS SEQ_TEST 1 1.000e+028 1 N N 30 1
1 rows selected.2 시퀀스 증가
RAC1,RAC2 다른 세션을 열어 시퀀스를 증가해봅니다.
테스트 결과 2번 RAC에서는 캐시사이즈(30)만큼의 시퀀스를 뛰어넘어 31부터 시작하고 있습니다.
RAC1 세션
SQL> select sys.seq_test.nextval from dual;
NEXTVAL
----------
1
SQL> select sys.seq_test.nextval from dual;
NEXTVAL
----------
2
RAC2번 세션
SQL> select sys.seq_test.nextval from dual;
NEXTVAL
----------
31
RAC1번 다른 세션
SQL> select sys.seq_test.nextval from dual;
NEXTVAL
-----------
3
1 rows selected.
RAC2번 다른세션
SQL> select sys.seq_test.nextval from dual;
NEXTVAL
----------
323 RAC1의 시퀀스가 30이 넘게되면 어떻게 되는지 확인
테스트 결과 30이 되면 RAC2번에 할당되었던 캐시사이즈(30)의 뒷번호부터인 61번부터 시작되는 것을 확인하였습니다.
SQL> /
NEXTVAL
----------
5
...생략
SQL> /
NEXTVAL
----------
30
SQL> /
NEXTVAL
----------
61
2.CACHE,ORDER 옵션
1 시퀀스 생성
CACHE_SIZE에 30이 할당되어 있으며 O 옵션이 Y로 설정되어 ORDER 옵션이 설정된 것을 확인할 수 있습니다.
SQL> CREATE SEQUENCE SYS.SEQ_TEST
INCREMENT BY 1 START WITH 1 NOMAXVALUE
MINVALUE 1 NOCYCLE CACHE 30 ORDER ;
SQL> select * from dba_sequences where sequence_name='SEQ_TEST';
SEQUENCE_OWNER SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_B C O CACHE_SIZE LAST_NUMBER
------------------------------ ------------------------------ ----------- ----------- ----------- - - ----------- -----------
SYS SEQ_TEST 1 1.000e+028 1 N Y 30 1
1 rows selected.2 시퀀스 증가
테스트 결과 CACHE 옵션이 있음에도 ORDER옵션 때문에 CACHE_SIZE만큼 할당된 번호로 올라가지 않고, 순차적으로 올라가고 있습니다.
RAC1번
SQL> select sys.seq_test.nextval from dual;
NEXTVAL
----------
1
RAC2번
SQL> select sys.seq_test.nextval from dual;
NEXTVAL
----------
2
RAC1번 다른 세션
SQL> select sys.seq_test.nextval from dual;
NEXTVAL
-----------
3
1 rows selected.
RAC2번 다른 세션
SQL> select sys.seq_test.nextval from dual;
NEXTVAL
-----------
4
1 rows selected.
3. NOCACHE,NOORDER 옵션
1 시퀀스 생성
CACHE_SIZE가 0이고 O가 N이므로 NOCACHE,NOORDER옵션으로 생성되었습니다.
SQL> CREATE SEQUENCE SYS.SEQ_TEST
INCREMENT BY 1 START WITH 1 NOMAXVALUE
MINVALUE 1 NOCYCLE NOCACHE NOORDER ;
SQL> select * from dba_sequences where sequence_name='SEQ_TEST';
SEQUENCE_OWNER SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_B C O CACHE_SIZE LAST_NUMBER
------------------------------ ------------------------------ ----------- ----------- ----------- - - ----------- -----------
SYS SEQ_TEST 1 1.000e+028 1 N N 0 1
1 rows selected.2 시퀀스 증가
ORDER 옵션만 부여했을때와 동일하게 인스턴스에 관계없이 순차적으로 증가하는 것을 확인했습니다.
RAC1 세션
SQL> /
NEXTVAL
----------
1
RAC2 세션
SQL> /
NEXTVAL
----------
2
RAC1 다른 세션
SQL> select sys.seq_test.nextval from dual;
NEXTVAL
-----------
3
1 rows selected.
RAC2 다른 세션
SQL> select sys.seq_test.nextval from dual;
NEXTVAL
-----------
4
1 rows selected.
4. NOCACHE,ORDER 옵션
1 시퀀스 생성
CACHE_SIZE가 0이고 O가 Y이므로 NOCACHE에 ORDER 옵션이 적용된 시퀀스임을 확인했습니다.
SQL> CREATE SEQUENCE SYS.SEQ_TEST
INCREMENT BY 1 START WITH 1 NOMAXVALUE
MINVALUE 1 NOCYCLE NOCACHE ORDER ;
SQL> select * from dba_sequences where sequence_name='SEQ_TEST';
SEQUENCE_OWNER SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_B C O CACHE_SIZE LAST_NUMBER
------------------------------ ------------------------------ ----------- ----------- ----------- - - ----------- -----------
SYS SEQ_TEST 1 1.000e+028 1 N Y 0 1
1 rows selected.2 시퀀스 증가
테스트 결과 인스턴스에 관계없이 순차적으로 증가하고 있었습니다.
RAC1 세션
SQL> /
NEXTVAL
----------
1
RAC2 세션
SQL> /
NEXTVAL
----------
2
RAC1 다른 세션
SQL> select sys.seq_test.nextval from dual;
NEXTVAL
-----------
3
1 rows selected.
RAC2 다른 세션
SQL> select sys.seq_test.nextval from dual;
NEXTVAL
-----------
4
1 rows selected.
테스트결과 , CACHE 옵션을 NO CACHE 옵션으로만 설정하여도 인스턴스에 관계없이 시퀀스를 순차적으로 증가하게 하는것을 확인하였습니다. 하지만 CACHE 옵션은 원래 그 용도가 순차적으로 시퀀스를 증가시키는 것에 중점을 둔 기능이 아니라, 성능을 위해 미리 숫자만큼 캐시해놓는다라는 의미가 강한 옵션입니다.
ORDER 옵션이 말그대로 숫자를 순차적으로 사용한다라는 의미의 옵션이므로 ORDER 나 NOCACHE 둘 중 하나만 사용해도 시퀀스가 순차적으로 증가가 되지만 하나만 사용해야 한다면 ORDER옵션을 사용하여 순차적으로 증가하는것이 맞는것이라고 생각이 듭니다. 인스턴스마다 시퀀스번호가 다른것에 대한 권장사항 해결책으로는 NOCACHE ORDER 옵션으로 부여해서 시퀀스를 순차적으로 증가하게 해라라고 듣긴 들었습니다만, 둘 중 하나만 변경하나 두개다 변경하나 같은 결과를 얻기 때문에 ORDER 하나만 변경해도 상관 없을 것 같습니다.
※ 기본적으로 아무옵션없이 생성하면 CACHE 20 NOORDER 옵션으로 생성이 Default로 됩니다.
확인
CREATE SEQUENCE SYS.SEQ_TEST
INCREMENT BY 1
START WITH 1
NOMAXVALUE
MINVALUE 1
;
SQL> select * from dba_sequences where sequence_name='SEQ_TEST';
SEQUENCE_OWNER SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_B C O CACHE_SIZE LAST_NUMBER
------------------------------ ------------------------------ ----------- ----------- ----------- - - ----------- -----------
SYS SEQ_TEST 1 1.000e+028 1 N N 20 1
1 rows selected.
(다시한번 말씀드리지만 티베로 멀티인스턴스에서도 동일한 결과를 얻었습니다!)
'Oracle > 운영' 카테고리의 다른 글
| DBA_DEPENDENCIES (0) | 2021.09.07 |
|---|---|
| [Oracle과 Tibero] DB link 에러 발생시 프로시저 에러발생여부 테스트 (0) | 2021.08.31 |
| Sequence(시퀀스) (0) | 2021.08.02 |
| SID와 SERVICE NAME의 차이 및 확인법 (0) | 2021.07.09 |
| 통계정보 수집 방법 비교 ANALYZE vs DBMS_STATS (0) | 2021.07.02 |
댓글