SQL튜닝이론 - 레코드 채번 방식에 따른 성능 비교(채번테이블, 시퀀스오브젝트, max + 1 조회 방식)

SQL튜닝이론 - 레코드 채번 방식에 따른 성능 비교(채번테이블, 시퀀스오브젝트, max + 1 조회 방식)

 

 

INSERT, UPDATE, DELETE, MERGE 중 채번 방식에 따른 성능 차이가 가장 큰 명령은 INSERT입니다. 신규 데이터를 입력하려면 PK 중복 방지를 위한 채번이 선행되어야 하기 때문입니다. 대표적인 세 가지 채빈 방식은 다음과 같습니다. 참고로, 복합 컬럼 인덱스에서 사용된 칼럼 중 순번 이외의 인덱스 칼럼을 '구분속성'이라고 합니다.

 

 

- 채번 테이블
- 시퀀스 오브젝트
- MAX + 1 조회

 

 

채번 테이블

 

각 테이블 식별자의 단일칼럼 일련번호 또는 구분 속성별 순번을 채번 하기 위해 별도 테이블을 관리하는 방식입니다. 채번 레코드를 읽어 + 1을 더하고 그 값을 새로운 레코드를 입력하는 데 사용합니다. 채번 레코드를 변경하는 과정에서 자연스럽게 액세스 직렬화가 이루어지므로 두 트랜잭션에 중복 값을 채번 되지 않습니다. 채번 테이블 방식의 장점은 아래와 같습니다.

 

- 범용성이 좋다.
- INSERT 과정에 중복 레코드 발생에 대비한 예외 처리를 신경 쓰지 않아도 된다.
- INSERT 과정에서 결번이 발생하지 않는다.
- PK 가 복합 컬럼일 때도 사용이 가능하다

 

단점은 다른 채번 방식에 비해 성능이 안 좋다는 것입니다. 채번 레코드를 변경하는 과정에서 ROW LOCK 경합이 발생하기 때문입니다. 로우 LOCK 은 기본적으로 대상 테이블에 INSERT 마치고 커밋 또는 롤백할 때까지 지속됩니다. 대량의 동시 INSERT가 발생하면 채번 레코드뿐만 아니라 채번 테이블 블록 자체에도 경합이 발생합니다. 서로 다른 레코드를 변경하는 프로세스끼리도 경합할 수 있습니다.

 

PK 가 복합칼럼인 경우, 즉 구분 속성별 순번을 채번 하는 경우에는 LOCK 경합이 줄어들지만 구분 속성 레코드 수가 소수일 때만 이 방식을 사용하므로 LOCK 경합이 발생할 가능성이 높습니다. 따라서 동시 INSERT 가 아주 많은 테이블에서는 이 방식을 사용하기가 어렵습니다.

 

자율 트랜잭션

 

PL/SQL의 자율 트랜잭션 기능을 이용하면 매인 트랜잭션에 영향을 주지 않고 서브 트랜잭션에서 일부 자원만 LOCK 해재할 수 있습니다. PL/SQL 선언부에 PRAMA AUTONOMOUS_TRANSACTION 이라고 선언하면 자율트랜잭션 방식으로 INSERT 할 수 있습니다.

 

CREATE OR REPLACE FUNCTION SEQ_NEXTVAL(1_GUBUN NUMBER) RETURN NUMBER
AS
    PRAGMA AUTONOMOUS_TRANSACTION;
    1_NEW_SEQ_TAB.SEQ%TYPE;
 BEGIN
     UPDATE SEQ_TAB
    SET SEQ = SEQ + 1
    WHERE GUBUN  = 1_ GUBUN;

    SELECT SEQ INTO 1_NEW_SEQ
    FROM SEQ_TAB
    WHERE GUBUN = L_GUBUN;

    COMMIT;
    RETURN L_NEW_SQP

 

PL/SQL 함수/프로시저를 자율 트랜잭션으로 선언하면, 그 내부에서 커밋을 수행해도 메인 트랜잭션은 커밋하지 않은 상태로 남습니다. 메인 트랜잭션 INSERT 문에서 아래와 같이 채번 함수를 호출하고 최종적으로 커밋하기 전까지 채번 테이블 ROW LOCK 은 이미 해제한 상태이므로 다른 트랜잭션을 블록킹 하지 않습니다.

 

INSERT INTO TARGET_TAB VALUES (SEQ_NEXTVAL(123),:X,:Y,:Z);

 

시퀀스 오브젝트

 

시퀀스 오브젝트의 가장 큰 장점은 빠른 성능입니다. 채번 테이블과 마찬가지로 INSERT 과정에 중복 레코드 발생에 대비한 예외처리에 신경 쓰지 않아도 됩니다. 테이블별로 시퀀스 오브젝트를 생성하고 관리하는 부담이 있습니다. 시퀀스 채벌과정에서 LOCK이 발생합니다. 시퀀스 오브젝트가 오라클 내부에서 관리하는 채번테이블이기 때문입니다. SYS.SEQ% 테이블을 말하며, DBA_SEQUENCES 뷰를 통해 조회할 수 있습니다.

 

시퀀스 오브젝트도 결국 테이블이므로 값을 읽고 변경하는 과정에서 LOCK 메커니즘이 작동합니다. 캐시 사이즈를 적절히 설정하면 가장 빠른 성능을 제공합니다. 시퀀스에는 자율 트랜잭션 기능도 기본적으로 구현돼 있습니다.

 

시퀀스 LOCK

오라클이 시퀀스 오브젝트에 사용하는 LOCK은 세 가지가 있습니다.

 

로우 캐시 LOCK

 

딕셔너리 정보를 매번 디스크에서 읽고 쓰면 성능이 느리므로 오라클은 로우 캐시를 사용합니다. 로우 캐시는 SGA 구성요소이므로 액세스직렬화가 필요합니다. 이를 위해 사용하는 LOCK 이 ROW CACHE LOCK입니다.

 

로우 캐시를 사용하는 대표적인 오브젝트가 시퀀스 이므로 로우 캐시 LOCK 경합이 나타납니다. 죽 NEXTVAL을 호출할 때마다 로우 캐시에서 시퀀스 레코드를 변경해야 하는데, 많은 사용자가 동시에 NEXTVAL 함수를 호출하면 로우 캐시 LOCK 경합이 발생합니다.

 

시퀀스 채번으로 인한 로우 캐시 LOCK을 줄이기 위해 오라클은 기본적으로 CACHE 옵션을 사용합니다. 옵션을 명시적으로 설정하지 않았을 때 기본 값은 20입니다. 시퀀스 채번에 의한 로우 캐시 LOCK 경합을 줄이려면 이 값을 크게 설정해야합니다. 반대로, 채번 빈도가 낮아 캐시를 굳이 사용할 필요가 없는 경우에는 NOCACHE 옵션을 지정하면 됩니다.

 

CREATE SEQUENCE MYSEQ CACHE 1000;

SELECT CACHE_SIZE, LAST_NUMBER
FROM USER_SEQUENCES
WHERE SEQUENCE_NAME = 'MYSEQ';

SELECT MYSEQ.NEXTVAL FROM DUAL;

SELECT CACHE_SIZE, LAST_NUMBER
FROM USER_SEQUENCES
WHERE SEQUENCE_NAME = 'MYSQL'

 

CACHE 크기가 1000이라면 NEXTVAL을 호출하면 LAST_NUMBER 이 1에서 1001로 변경됩니다. 시퀀스 캐시에서 1000 개의 값을 모두 소진한 직후 nextval을 호출하면 그때 다시 로우 캐시에서 시퀀스 레코드를 2001로 변경합니다.

 

시퀀스 캐시 LOCK

 

시퀀스 캐시도 공유 캐시에 위치합니다. 따라서 시퀀스 캐시에서 값을 얻을 때도 액세스 직렬화가 필요하며 이를 SQ LOCK이라고 부릅니다.

 

SV LOCK

시퀀스 캐시는 한 인스턴스 내에서 공유됩니다. NEXTVAL을 호출하는 순서대로 값을 제공하므로 인스턴스 내에서는 번호 순서를 보장합니다. 데이터베이스 하나에 인스턴스가 여러 개인 RAC 환경에서는 인스턴스마다 시퀀스 캐시가 따로 있습니다. 따라서 인스턴스 간에는 번호 순서를 기본적으로 보장하지 않습니다. 예를 들어 첫 번째 NEXTVAL을 1번 인스턴스 A 프로세스가 호출하고, 두 번째 NEXTVAL은 2번 인스턴스 B 프로세스가 호출한다고 가정한다. 1번 인스턴스 시퀀스 캐시는 1부터 1000번까지의 값을 순서대로 반환하고, 2번 인스턴스 시퀀스 캐시는 1001부터 2000까지의 값을 순서대로 반환합니다. 테이블에는 아래와 같은 순서로 값이 입력됩니다.

1 -> 10001 -> 2 -> 1002 -> 3 -> 1003.....

 

 

식별자는 유일해야 하고, 반드시 값이 있어야 합니다.(NOT NULL) 식별자에 값을 순서대로 입력해야 하는 조건은 없습니다. 하지만 실무에서는 식별자 번호가 레코드 입력순이기를 원하는 경우가 많습니다. 업무적으로 순서를 보장해야 한다면, 즉 어떤 인스턴스에서 NEXTVAL을 호출하더라도 순서대로 일련번호를 제공해야 한다면 ORDER 옵션을 사용하면 됩니다. 그러면 시퀀스 캐시 하나를 모든 RAC 노드가 공유합니다.

 

그런데 자원을 공유할 때는 항상 LOCK 이 필요합니다. RAC 환경에서 ORDER 옵션을 사용하면 네트워크를 통해 시퀀스 캐시를 서로 주고받으며 공유합니다. 성능에 문제가 발생합니다. 이에 업무적으로 꼭 필요할 때만 ORDER 옵션을 사용해야 합니다.

시퀀스 오브젝트를 사용하는 가장 큰 담점은 기본적으로 PK가 단일칼럼일 때만 사용 가능하다는 데 있습니다. PK가 복합칼럼인 경우 각 레코드를 유일하게 식별하는 최소 칼럼으로 PK를 구성해야 한다는 최소성 요건을 위배하게 됩니다.

 

순환옵션을 가진 시퀀스 활용

 

PK가 복합칼럼인데 동시성 트랜잭션이 높아 시퀀스가 꼭 필요하다면, 순환 옵션을 가진 시퀀스 활용을 고려할 수 있습니다. 아주 큰 값으로 최댓값을 설정하고 그 값을 도달하면 1부터 다시 시작하도록 순환옵션을 설정합니다. 순환옵션을 사용하는 이유는 값이 무한정 커지지 않게 함으로써 순번 칼럼 길이를 최소화하기 위함입니다.

 

시퀀스의 또 다른 단점은, 신규 데이터를 입력하는 과정에서 결번이 생길 수 있다는 점입니다. 원인은 크게 두 가지입니다.

 

첫째, 시퀀스 채번 이후에 트랜잭션을 롤백하는 경우입니다. 둘째, CACHE 옵션을 설정한 시퀀스가 캐시에서 밀려나는 경우입니다. 자주 사용하지 않아 캐시에서 밀려나거나 인스턴스를 재기동하는 순간, 캐시 돼 있던 번호는 모두 사라지며 디스크에서 다시 읽을 때 그다음 번호부터 읽습니다.

 

사용빈도가 낮아서 생기는 결번은 시퀀스를 SHARED POOL에 KEEP 하도록 아래 명령을 수행하면 해결할 수 있습니다.

 

EXEC SYS.DBMS_SHARED_POOL.KEEP('SCOTT.MY_SEQ', 'Q');

 

MAX + 1 조회

 

MAX + 1 방식은 대상 테이블의 최종 일련번호를 조회하고, 거기에 1을 더해서 INSERT 하는 방식입니다.

 

INSERT INTO 상품거래(거래일련번호, 계좌번호, 거래일시, 상품코드, 거래가격, 거래수량)
VALUE (
    (SELECT MAX(거래일련번호) + 1 FROM  상품거래), :ACNT_NO, SYSDATE, :PROD_CD, :TRD_PRICE, :TRD_QTY
)

 

이 방식의 장점은 시퀀스 또는 별도의 채번 테이블을 관리하는 부담이 없습니다. 둘째 동시 트랜잭션에 의한 충돌이 많지 않으며 성능이 매우 빠릅니다. 셋째, PK 복합칼럼인 경우, 즉 구분 속성별 순번을 채번 할 때도 사용할 수 있습니다. 채번 테이블은 구분 속성 값의 수가 적을 때만 사용할 수 있지만, 이 방식은 값의 수가 아무리 많아도 상관없습니다. 오히려 값의 수가 많을수록 성능이 좋아집니다. 입력 값 중복에 의한 로우 LOCK 경합이 줄고 재실행 횟수가 줄기 때문입니다.

 

이 방식의 단점은 첫째, 레코드 중복에 대비해 세밀한 예외처리가 필요합니다. 둘째, 다중 트랜잭션에 의한 동시 채벌이 심하면 시퀀스보다 성능이 나빠집니다. 레코드 중복에 의한 로우 LOCK 경합 때문입니다. ROW LOCK 경합은 선행 트랜잭션이 커밋 또는 롤백할 때까지 지속됩니다. 선행 트랜잭션이 롤백하지 않는 한, INSERT는 결국 실패하게 되므로 채번과 INSERT를 다시 실행해야 합니다. 이는 성능을 저하시키는 요소입니다.

 

다행히 PK가 복합칼럼이고 구분 속성별 값의 수가 많으면, 구분 속성 값별로 채벌이 분산됩니다. 따라서 동시 채번이 많아도 로우 LOCK 경합 및 재실행 가능성은 현저히 줄어듭니다. ROW LOCK 경합 이외의 성능이슈는 MAX 값 조회에 최적화된 인덱슬르 구성해 주지 않을 때 발생합니다.

 

LOCK 경합 요소를 고려한 채번 방식 선택 기준을 정리하면 다음과 같습니다.

 

1. 다중 트랜잭션에 의한 동시 채번이 많지 않으면, 세 가지 방식 모두 좋다. 하지만 채번 테이블이나 시퀀스 오브젝트 관리 부담을 고려한다면, MAX + 1 방식이 좋다.
2. 다중 트랜잭션에 의한 동시 채번이 많고 PK가 단일칼럼 일련번호라면, 시퀀스 방식이 가장 좋다.
3. 다중 트랜잭션에 의한 동시 채벌이 많고 PK 구분 속성에 값 종류 개수가 많으면, 중복에 의한 로우 LOCK 경합 및 재실행 가능성이 낮습니다. 그렇다면 시퀀스보다 MAX + 1 방식이 구조적으로 좋습니다.
4. 다중 트랜잭션에 의한 동시 채번이 많고 PK 구분 속성에 값 종류 개수가 적으면, MAX + 1 방식은 성능 문제가 생길 수 있습니다. 그럴 때 순환 옵션을 가진 시퀀스 오브젝트의 활용을 고려할 수 있습니다.

 

12C 시퀀스 신기능

 

칼럼 기본값으로 시퀀스 지정

CREATE SEQUENCE MY_SEQ;
CREATE TABLE T(
C1 NUMBER DEFAULT MY_SEQ.NEXTVAL NOT UNLL
, C2 VARCHAR2(5)
);

 

INSERT 시, 값을 입력하지 않으면, 오라클이 대신 시퀀스 NEXTVAL을 호출해서 값을 입력합니다.

INSERT INTO T(C1, C2) VALUES (MY_SEQ.NEXTVAL, 'X');
INSERT INTO T(C2) VALUES ('X');

 

IDENTITY 컬럼

 

CREATE TABLE T(
C1 NUMBER GENERATED ALWAYS AS IDENTITY
, C2 VARCHAR2(5)
);

INSERT INTO T(C2) VALUES ('X');
INSERT INTO T(C1, C2) VALUES (DEFAULT, 'X');

 

DEFAULT로 지정된 칼럼에 값을 직접 입력하려고 하면 에러가 발생합니다.

 

INSERT INTO T(C1, C2) VALUES (3, 'X');

 

사용자가 값을 직접 입력하록 하려면 GENERATED BY DEFAULT 옵션을 지정해야 합니다.

 

CREATE TABLE T(
C1 NUMBER GENERATED BY DEFALT AS IDENTITY
, C2 VARCHAR2(5)
);

INSERT INTO T(C1, C2) VALUES (3, 'X');

 

세션 시퀀스

 

글로벌 시퀀스는 여러 세션이 공유할 수 있는 시스템 레벨 시퀀스로 아래와 같이 생성합니다.

CREATE SQUENCE G_SEQ GLOBAL;

 

세션 시퀀스는 여러 세션이 공유할 수 없는 세션 레벨 시퀀스로 아래와 같이 생성합니다.

 

CREATE SQUENCE G_SEQ SESSION;

 

세션 시퀀스는 세션이 종료되면 초기화됩니다. 즉, 세션 내에서만 유효합니다. LOCK 메커니즘이 불필요하므로 당연히 글로벌 시퀀스보다 성능이 좋습니다. 시퀀스를 여러 세션이 호출하지 않는다면 굳이 글로벌 시퀀스를 사용할 이유가 없습니다. 게다가 스테이징 테이블은 데이터를 새로 적재하기 전에 TRUNCATE 합니다. 기존 마지막 값에 이어서 값을 채번 할 필요가 없으므로 성능이 빠른 세션시퀀스가 유용합니다.

 

시퀀스보다 좋은 솔루션

한 개 이상의 구분 속성과 함께 뒤쪽에 순번 대신 입력일시를 두는 방식으로 PK를 설계하면 채번 또는 INSERT 과정에서 생기는 LOCK 이슈를 해소할 수 있습니다. 채번 과정을 생략하고 SYSDATE OR SYSTIMESTAMP 함수만 호출하면 되기 때문에 빠르고 간편합니다.

구분 속성에 종류 개수가 많으면 입력일시에 DATE 타입을 써도 됩니다. 값의 종류 개수가 적으면 TIMESTAMP 타입을 써야 할 수도 있습니다. 적절한 데이터 타입을 선택하면, 중복 가능성이 매우 희박하지만 예외처리는 필요합니다.

 

정보생명주기를 효과적으로 관리함에 있어 데이터의 삭제는 매우 중요합니다. 빠르게 삭제할 뿐 아니라, 삭제한 공간을 바로 시스템에 반납함으로써 새로 입력하는 데이터를 위해 재활용할 수 있어야 합니다. 이런 의미에서 입력 일시를 PK에 포함하려는 노력은 매우 의미가 있습니다. 파티션을 활용한 대량 DELETE 튜닝처럼 서비스 중단 없이 파티션단위로 커팅하려면 기본적으로 PK 인덱스가 로컬 파티션 이어야 하고 PK 인덱스를 로컬 파티셔닝하려면 삭제 기분 칼럼이 PK에 포함돼 있어야 합니다. 대부분의 삭제 기준은 입력일시 칼럼이 됩니다.

 

인덱스 블록 경합

채번과정이 생략된 INSERT는 인덱스 블록 경합을 유발합니다 MAX + 1 방식을 사용할 때도 자주 나타납니다.

일련번호나 입력일시/변경일시처럼 순차적으로 값이 증가하는 단일 칼럼 인덱스는 항상 맨 우측 블록에만 데이터가 입력 됩니다. 이런 특징을 갖는 인덱스를 RIGHT GROWING 인덱스라고 합니다. 입력하는 값이 달라도 같은 블록을 갱신하려는 프로세스 간 버퍼 LOCK 경합이 발생할 수 있습니다. 이는 동시 INSERT 가 많을 때 트랜잭션 성능을 떨어뜨리는 주범입니다.

인덱스 블록 경합을 해소하는 가장 일반적인 방법은 인덱스를 해시 파티셔닝 하는 것입니다. 인덱스를 해시 파티셔닝하면, 값이 순차적으로 증가하더라도 해시 함수가 리턴한 값에 의해 다른 파티션에 입력되므로 경합을 줄일 수 있습니다.

 

시퀀스 신기능을 활용한 인덱스 블록 경함 해소

CREATE SEQUENCE Q_SEQ GLOBAL;
CREATE SEQUENCE S_SEQ SESSION;

 

글로벌 시퀀스는 데몬 프로세스 또는 커넥션 풀 프로세스가 DB에 접속하는 순간 호출됩니다.

 

select q_seq.nextval from dual;

 

세션 시퀀스는 insert를 수행할 때마다 호출됩니다. 즉, 글로벌 시퀀스 curval과 세션 시퀀스 nextval을 조합한 값으로 insert 합니다.

 

insert into t(id, c1, c2) values
(to_char(q_seq.curval, 'fm0000') || to_char(s_seq.nextval, 'fm000'), 'A', 'B');

 

이렇게 하면 각 프로세스가 서로 다른 리프블록에 값을 입력하므로 인덱스 경합이 발생하지 않습니다.

 

최근 공개된 오라클 18c 버전에서는 Scalable 시퀀스를 이용할 수 있습니다. 시퀀스를 생성할 때 'SCALE' or 'SCALE EXTEND' 옵션을 지정합니다. SCALABLE 시퀀스에서 nextval을 호출하면 인스턴스번호, 세션 id, 시퀀스 번호를 조합한 번호를 반합합니다.extend 옵션을 생략하면, 맨 우측 시퀀스 번호가 1,2,3, 순으로 증가합니다. 리딩 제로 없는 숫자를 반환합니다.