7회차 DML 튜닝 - 2

3. 파티션을 활용한 DML 튜닝

3.1 테이블 파티션

파티셔닝(Partitioning)은 테이블 또는 인덱스 데이터를 특정 컬럼(파티션 키) 값에 따라 별도 세그먼트에 나눠서 저장하는 것을 의미

일반적으로 시계열에 따라 Range 방식으로 분할하며, 그 외의 다른 기준으로 분할할 수도 있다. (ex. 월별, 분기별, 계절별 등등 ...)

 

😊 파티션이 필요한 이유

  ▶️ 관리적 측면: 파티션 단위 백업, 추가, 삭제, 변경으로 가용성 향상

  ▶️ 성능적 측면: 파티션 단위 조회 및 DML, 경합 또는 부하 분산

 

😊 파티션 종류

   1️⃣ Range 파티션

   2️⃣ 해시

   3️⃣ 리스트

 

1️⃣ Range 파티션

오라클 8 버전부터 제공되는 기초적 방식으로 주로 날짜 컬럼을 기준으로 파티셔닝한다.

create table 주문 (주문번호 number, 주문일자 varchar2(8), 고객ID varchar2(5)
                   , 배송일자 varchar2(8), 주문금액 number, ... )
partition by range(주문) (
   partition P2017_Q1 values less than ('20170401')
 , partition P2017_Q2 values less than ('20170701')
 , partition P2017_Q3 values less than ('20171001')
 , partition P2017_Q4 values less than ('20180101')
 , partition P2018_Q1 values less than ('20180401')
 , partition p9999_mx values less than ( MAXVALUE ) -> 주문일자 >= '20180401'

 

Range 파티션 테이블에 값 입력시 각 레코드를 파티션 키 값에 따라 분할 저장하고 읽을 때 검색 조건을 만족하는 파티션만 골라 읽어 이력성 데이터를 Full Scan 방식으로 조회할 때 성능을 크게 향상시킨다.

보관주기 정책 설정 시 과거 데이터가 저장된 파티션만 백업하고 삭제하는 등 데이터 관리 작업을 효율적이고 빠르게 수행할 수 있다.

파티션 Pruning: 쓸데없는 가지를 치다, 불필요한 부분을 제거한다는 의미의 'prune'으로 하드파싱이나 실행 시점에 조건절을 분석해 읽지 않아도 되는 파티션 세그먼트를 액세스 대상에서 제외하는 기능 ➡️ 파티션 테이블의 SQL 성능 향상 원리

 

예시로 주문 테이블에 1200만건의 데이터가 존재하고 아래 쿼리의 조건을 만족하는 데이터가 300만건일 때 인덱스로 건건이 랜덤 액세스 시 Full Scan 방식보다 성능이 더 느릴 수 있다. 이럴 때 100만건 단위로 나누어 저장하면 Full Scan 이어도 일부 파티션 세그먼트만 읽고 멈출 수 있어 성능을 크게 향상시킬 수 있다. 

select *
from 주문
where 주문일자 >= '20120401'
and 주문일자 <= '20120630'

 

💡 파티션 테이블도 인덱스 액세스가 가능하지만 파티션 Prunning을 이용한 테이블 스캔보다 훨씬 느리다.

 

파티션, 클러스터, IOT의 공통점은 데이터가 흩어지지 않고 물리적으로 인접하도록 저장하는 클러스터링 기술이다.

하지만와 셋의 차이점은 파티션은 세그먼트 단위로, 클러스터는 블록 단위로 모아 저장하며 IOT는 데이터를 정렬된 순서로 저장한다.

 

2️⃣ 해시 파티션

오라클 8i버전부터 제공한 기능으로 파티션 키 값을 해시 함수에 입력해 반환받은 값이 같은 세그먼트에 저장하는 방식

파티션 개수만 사용자가 결정하며, 데이터 분산 알고리즘은 오라클 내부 해시함수가 결정한다.

해시 파티션은 변별력이 좋고 데이터 분포가 고른 컬럼을 파티션 기준으로 선정해야 효과적이다.(ex. 고객ID)

create table 고객 (고객ID varchar2(5), 고객명 varchar2(10), ...)
partition by hash(고객ID) partitions 4;

 

 

검색 시 조건절 비교 값에 똑같은 해시 함수를 적용함으로써 읽을 파티션을 결정한다. 

해시 알고리즘 특성상 등치(=) 조건 또는 IN-List 조건으로 검색할 때만 파티션 Pruning이 작동한다. 

 

3️⃣ 리스트 파티션

오라클 9i 버전부터 제공한 기능으로 순서와 상관 없이  사용자가 정의한 그룹핑 기준(불연속적인 값의 목록)에 따라 데이터를 분할 저장하는 방식

create table 인터넷매물( 물건코드 varchar2(5), 지역분류 varchar2(4), ... )
partition by list(지역분류) (
     partition P_지역1 values ('서울')
   , partition P_지역2 values ('경기', '인천')
   , partition P_지역3 values ('부산', '대구', '대전', '광주')
   , partition P_기타 values (DEFAULT) -> 기타지역
);

 

⭐ 정리 ⭐

Range 파티션	리스트 파티션	해시 파티션
연속적인 값의 목록(순서 정렬 o)	불연속적인 값의 목록(순서 정렬 x)
	사용자가 정의한 논리적인 그룹으로 분할	오라클이 정한 해시 알고리즘으로 분할

 

파티션은 업무적인 친화도에 따라 그룹핑 기준을 정하되, 될 수 있으면 각 파티션에 값이 고르게 분산되도록 해야한다.

---

3.2 인덱스 파티션

😊 테이블 파티션 종류

   1️⃣ 비파티션 테이블(Non-Partitioned Table)

   2️⃣ 파티션 테이블(Partitioned Table)

 

😊 파티션 인덱스 종류

   

   1️⃣ 로컬 파티션 인덱스(Local Partition Index)

        - 각 테이블 파티션과 인덱스 파티션이 서로 1:1 대응 관계가 되도록 오라클이 자동으로 관리하는 파티션 인덱스

        - 테이블 파티션을 계절별로 옷을 관리하는 서랍장에 비유했을 때 계절별로 별도 색인을 만드는 것과 같다.

        - 각 인덱스 파티션은 테이블 파티션 속성을 그대로 상속받아 로컬 파티션 인덱스를 '로컬 인덱스'라고 부른다.

        - 테이블 파티션 구성을 변경(add, drop, exchange 등) 해도 인덱스를 재생성할 필요가 없으며 변경 작업이 오래걸리지 않는다.

        - 아래와 같이 인덱스 생성문 끝에 LOCAL 옵션을 추가해준다.

create index 주문_X01 on 주문 (주문일자, 주문금액) LOCAL;

create index 주문_X02 on 주문 (고객ID, 주문일자) LOCAL;

   

   2️⃣ 글로벌 파티션 인덱스(Global Partition Index)

        - 로컬이 아닌 파티션 인덱스 전체

        - 테이블 파티션과 독립적인 구성(파티션 유형, 파티션 키, 파티션 기준값 정의)을 가진다.

        - 테이블 파티션 구성 변경(add, drop, exchange) 시 Unusable 상태로 바뀌므로 곧바로 인덱스를 재생성 해주어야 하며 그동안 해당 테이블을 사용하는 서비스를 중단해야 한다.

        - 비파티션 테이블이어도 인덱스는 파티셔닝할 수 있다.

create index 주문_X03 on 주문 (주문금액, 주문일자) global
partition by range(주문금액) (
    partition P_01 values less than ( 100000 )
  , partition P_MX values less than ( MAXVALUE ) -> 주문금액 >= 100000

   

3️⃣ 비파티션 인덱스(Non-Partition Index)

 

      - 파티셔닝하지 않은 인덱스이다.

      - 위 그림처럼 여러 테이블 파티션을 가리키며 글로벌 비파티션 인덱스라고 부르기도 한다.

      - 테이블 파티션 구성 변경(add, drop, exchange) 시 Unusable 상태로 바뀌므로 곧바로 인덱스를 재생성 해주어야 하며 그동안 해당 테이블을 사용하는 서비스를 중단해야 한다.

create index 주문_x04 on 주문 (고객ID, 배송일자);

 

😎 Prefixed vs Nonprefixed

 파티션 인덱스를 파티션 키 컬럼이 선두 컬럼인지 여부에 따라 Prefixed와 Nonprefixed로 나눌 수도 있다.

▶️ Prefixed: 인덱스 파티션 키 컬럼이 인덱스 키 컬럼 왼쪽 선두에 위치

▶️ NonPrefixed: 인덱스 파티션 키 컬럼이 인덱스 키 컬럼 왼쪽 선두에 위치하지 않음, 파티션 키가 인덱스 컬럼에 아예 속하지 않을 수도 있음

 

파티션 인덱스는 총 네가지 유형으로 정리할 수 있다.

 

1️⃣ 로컬 Prefixed 파티션 인덱스

2️⃣ 로컬 NonPrefixed 파티션 인덱스

3️⃣ 글로벌 Prefixed 파티션 인덱스

     - 글로벌 파티션 인덱스는 Prefixed 파티션만 지원된다.

4️⃣ 비파티션 인덱스

 

😎 중요한 인덱스 파티션 제약

Unique 인덱스를 파티셔닝하려면 파티션 키가 모두 인덱스 구성 컬럼이어야 한다. 

예를들어 주문일자로 파티셔닝한 테이블이 있고 PK는 주문일자 + 주문번호, 인덱스는 로컬 파티션이면 결국 PK 인덱스도 주문일자로 파티셔닝한 셈이므로 파티션 키가 인덱스 구성 컬럼이다.

 

만약 2017년 12월 25 일 주문번호가 123456인 주문 레코드 입력 시 중복 값 확인을 위해 p2017_Q4 파티션 인덱스만 탐색하면 된다.

 

만약 PK 인덱스가 아래처럼 '주문번호' 단일 컬럼으로 테이블 파티션 키인 주문일자가 인덱스 구성 컬럼이 아닐 때 주문번호가 123456인 주문 레코드를 입력하려면, 중복 값이 있는지 확인하기 위해 인덱스 파티션을 모두 탐색해야 하며, 레코드 입력 및 커밋 전까지 다른 트랜잭션이 같은 주문번호로 다른 파티션에 입력하는 현상을 방지하기 위해 추가적인 Lock 매커니즘을 필요로 한다.

 

 

위와 같은 이유로 DML 성능 보장을 위해 Unique 인덱스를 파티셔닝할 때 파티션 키가 인덱스 컬럼에 포함되어야 한다. 

만약 이 제약으로 인해 PK 인덱스를 로컬 파티셔닝 하지 못하면 파티션 Drop, Truncate, Exchange, Split, Merge 같은 파티션 구조 변경 작업도 쉽지 않아진다. 변경 작업 시 PK 인덱스가 Unusable 상태로 바뀌므로 곧바로 인덱스를 Rebuild 하더라도 그동안 해당 테이블을 사용하는 서비스를 중단해야 하기 때문이다.

 

서비스 중단 없이 파티션 구조를 빠르게 변경하기 위해서는 PK를 포함한 모든 인덱스가 로컬 파티션 인덱스여야 한다.

 

---

3.3 파티션을 활용한 대량 UPDATE 튜닝

인덱스가 DML 성능에 큰 영향을 미치므로 대량 데이터 입력/수정/삭제 시엔 인덱스를 Drop하거나 Unusable 상태로 변경하고서 작업하는 방법을 많이 활용하는데, 손익분기점이 5% 정도로 입력/수정/삭제하는 데이터 비중이 5%를 넘는다면 인덱스를 그대로 둔 상태에서 작업하기보다 인덱스 없이 작업한 후에 재생성하는게 더 빠르다.

 

만약 아래와 같이 UPDATE 문으로 2015년 1월 1일 전에 발생한 거래 데이터를 수정하는데 거래 일자 조건을 만족하면서 상태코드 <> 'ZZZ' 인 데이터가 5,000만 건(5%)이면 테이블 레코드 5,000만 건을 변경하면서 거래_X2 인덱스까지 실시간으로 관리 하기위해 어마어마한 시간이 소요된다.

update 거래 
set 상태코드 = 'ZZZ'
where 상태코드 <> 'ZZZ'
and 거래일자 < '20150101';

 

하지만 10억건의 인덱스를 Drop 했다가 재생성하는 데 걸리는 시간도 만만치 않으므로 상황에 따라 고민해보는 것이 좋다.

 

😎 파티션 Exchange를 이용한 대량 데이터 변경

위 그림처럼 테이블이 파티셔닝 되어있고 인덱스도 로컬 파티션이라면 아래와 같은 순서로 수정된 값을 갖는 임시 세그먼트를 만들어 원본 파티션과 바꿔치기 하는 방식이 좋다.

 

1️⃣ 임시 테이블 거래_t 을 생성 (가능하다면 nologging 모드 생성)

create table 거래_t
nologging
as
select * from 거래 where 1 = 2;

 

2️⃣ 거래 데이터를 읽어 임시 테이블에 입력하면서 상태코드 값을 수정

insert /*+ append */ into 거래_t
select 고객번호, 거래일자, 거래순번, ...
    , (case when 상태코드 <> 'ZZZ' then 'ZZZ' else 상태코드 end) 상태코드
from 거래
where 거래일자 < '20150101';

 

3️⃣ 임시 테이블에 원본 테이블과 같은 구조로 인덱스를 생성(가능하다면 nologging 모드 생성)

create unique index 거래_t_pk on 거래_t (고객번호, 거래일자, 거래순번) nologging;
create index 거래_t_x1 on 거래_t(거래일자, 고객번호) nologging;
create index 거래_t_x2 on 거래_t(상태코드, 거래일자) nologging;

오라클(Oracle)에서 NOLOGGING 옵션은 데이터베이스 작업 중 Redo 로그 생성을 최소화하거나 생략해서 성능을 향상시키는 기능이다. 주로 대량의 데이터를 처리할 때 사용되며, 예를 들어 대량의 데이터를 삽입(INSERT), 인덱스 생성, 테이블 복사 등에 쓰인다.

4️⃣ 2014년 12월 파티션과 임시 테이블을 Exchange한다.

alter table 거래
exchange partition p201412 with table 거래_t
including indexes without validation;

 

5️⃣ 임시 테이블을 Drop 한다.

drop table 거래_t;

 

6️⃣ nologging 모드로 작업했을 경우엔 파티션을 logging 모드로 다시 전환한다.

alter table 거래 modify partition p201412 logging;
alter index 거래_pk modify partition p201412 logging;
alter index 거래_x1 modify partition p201412 logging;
alter index 거래_x2 modify partition p201412 logging;

 

---

3.4 파티션을 활용한 대량 DELETE 튜닝

아래와 같은 조건절로 수천만 건의 데이터를 삭제할 때에도 인덱스를 실시간으로 관리하려면 어마어마한 시간이 소요되며, 역시나 인덱스를 모두 Drop 했다가 다시 생성하기에도 비용이 만만치 않다.

delete 
from 거래 
where 거래일자 < '20150101';

 

DELETE 가 느린 이유

1. 테이블 레코드 삭제

2. 테이블 레코드 삭제에 대한 Undo Logging

3. 테이블 레코드 삭제에 대한 Redo Logging

4. 인덱스 레코드 삭제

5. 인덱스  레코드 삭제에 대한 Undo Logging

6. 인덱스  레코드 삭제에 대한 Redo Logging

7. Undo(2번과 5번)에 대한 Redo Logging

 

또한 각각의 인덱스 레코드를 찾아 건건이 수직적 탐색을 거쳐 레코드를 찾아 삭제해주는 작업은 부담이 크다.

😎 파티션 Drop을 이용한 대량 데이터 삭제

테이블이 삭제 조건절 컬럼 기준으로 파티셔닝 되어있고 , 인덱스도 로컬 파티션이라면 아래와 같이 간단하게 대량 데이터를 삭제할 수 있다. 

alter table 거래 drop partition p201412

-- 오라클 11g 이후로 아래와 같이 대상 파티션 지정 가능

alter table 거래 drop partition for ('20141201');

 

😎 파티션 Truncate를 이용한 대량 데이터 삭제 

삭제 조건을 만족하는 데이터가 대다수이면 대량 데이터를 지우는게 아니라 아래와 같이 남길 데이터만 백업했다가 재입력하는 방식이 빠를수도 있다. 

 

1️⃣ 임시 테이블(거래_t)를 생성하고, 남길 데이터만 복제한다.

create table 거래_t
as 
select *
from 거래 
where 거래일자 < '20150101'
and 상태코드 = 'ZZZ'; -- 남길데이터만 임시 세그먼트로 복제

 

2️⃣ 삭제 대상 테이블 파티션을 Truncate 한다.

alter table 거래 truncate partition p201412;

-- 오라클 11g 이후로 아래와 같이 대상 파티션 지정 가능
alter table 거래 truncate partition for ('20141201');

 

3️⃣ 임시 테이블에 복제해 둔 데이터를 원본 테이블에 입력한다.

insert into 거래
select *
from 거래_t; -- 남길 데이터만 입력

 

4️⃣ 임시 테이블을 Drop 한다.

drop table 거래_t;

 

⭐ 서비스 중단 없이 파티션 Drop 또는 Truncate 하기 위한 조건 ⭐

   1️⃣ 파티션 키와 커팅 기준 컬럼이 일치해야 함 ➡️ ex. 파티션 키와 커팅 기준 컬럼이 모두 '신청일자'

   2️⃣ 파티션 단위와 커팅 주기가 일치해야 함 ➡️ ex. 월 단위 파티션을 월 주기로 커팅

   3️⃣ 모든 인덱스가 로컬 파티션 인덱스이어야 함

       ➡️ ex. 파티션 키는 '신청일자' , PK는 '신청일자 + 신청순번'

       ➡️ PK인덱스는 삭제 기준(파티션 키) 컬럼이 인덱스 구성 컬럼이어야 로컬 파티셔닝 가능

 

---

3.5 파티션을 활용한 대량 INSERT 튜닝

▶️ 비파티션 테이블일 때

비파티션 테이블에 손익분기점을 넘는 대량 데이터 INSERT 시 아래와 같이 인덱스를 Unusable 시켰다가 재생성 하는 방식을 이용하면 더 빠르게 처리할 수 있다.

 

 

1️⃣ 테이블을 nologging 모드로 전환한다.(가능하다면)

alter table target_t nologging;

 

2️⃣ 인덱스를 Unusable 상태로 전환한다.

alter index target_t_x01 unusable;

 

3️⃣ 할 수 있다면 Direct Path Insert 방식으로 대량 데이터를 입력한다.

insert /*+ append */ into target_t
select * from source_t;

Direct Path Insert란?

• 데이터를 버퍼 캐시를 거치지 않고 바로 데이터 블록에 기록하는 방식
• 성능이 뛰어나서 대량 데이터 로딩 시 매우 유용
• 주로 INSERT /*+ APPEND */ 힌트를 사용해 수행

⚠️ 주의사항

• Direct Path Insert는 단일 세션 모드 또는 병렬 처리 시 사용
• 트랜잭션 커밋 전까지 다른 세션은 해당 데이터에 접근할 수 없음
• INSERT 대상 테이블이 Exclusive Lock을 필요로 함.
• 일부 기능(트리거, 일부 제약조건)은 작동하지 않을 수 있음.

4️⃣ 인덱스를 재생성 (가능하다면 nologging 모드로.)

alter index target_t_x01 rebuild nologging;

 

5️⃣ 위 단계들에서 nologging 모드로 작업했다면 다시 logging 모드로 전환

alter table target_t logging;
alter index target_t_x01 logging;

 

▶️ 파티션 테이블일 때

테이블이 파티셔닝 되어있고 인덱스도 로컬 파티션이라면 파티션 단위로 인덱스를 재생성하면 된다.

 

1️⃣ 작업 대상 테이블 파티션을 nologging 모드로 전환한다.(가능하다면.,)

alter table target_t modify partition p_201712 nologging;

 

2️⃣ 작업 대상 테이블 파티션과 매칭되는 인덱스 파티션을 Unusable 상태로 전환한다.

alter index target_t_x01 modify partition p_201712 unusable;

 

3️⃣ 할 수 있다면 Direct Path Insert 방식으로 대량 데이터를 입력한다.

insert /*+ append */ into target_t
select *
from source_t
where dt between '20171201' and '20171231';

 

4️⃣ 인덱스를 재생성 (가능하다면 nologging 모드로.)

 

alter index target_t_x01 rebuild partition p_201712 nologging;

 

5️⃣ 위 단계들에서 nologging 모드로 작업했다면 다시 logging 모드로 전환

alter table target_t modify partition p_201712 logging;
alter index target_t_x01 modify partition p_201712 logging;

 

---

4. LOCK과 트랜잭션 동시성 제어

4.1 오라클 Lock

오라클은 공유 리소스 및 사용자 데이터 보호 목적으로 DML Lock, DDL Lock, 래치, 버퍼 Lock, 라이브러리 캐시 Lock/Pin 등 다양한 종류의 락을 사용한다.

그 중 애플리케이션 개발 측면에서 DML Lock을 가장 중요하게 다루어야한다. DML Lock은 다중 트랜잭션이 동시 액세스하는 사용자 데이터의 무결성을 보호해준다.

 

⭐ DML Lock의 종류

▶️ DML 로우 lock

   - 두개의 동시 트랜잭션이 같은 로우를 변경하는 것을 방지하며, 하나의 로우를 변경하려면 로우 Lock을 먼저 설정해야 한다.

   - 어떤 DBMS에서도 DML 로우 Lock엔 배타적 모드(Exclusive Lock)을 사용하므로 UPDATE/DELETE 중인 로우를 다른 트랜잭션이 UPDATE/DELETE 할 수 없다.

   - INSERT에 대한 로우 Lock 경합은 Unique 인덱스가 있을 때만 발생하며 Unique 인덱스가 있는 상황에서 두 트랜잭션이 같은 값을 입력하려고 하면 블로킹이 발생해 후행 트랜잭션은 선행 트랜잭션을 기다렸다가 선행 트랜잭션이 커밋하면 INSERT 실패, 롤백하면 INSERT 성공한다. 물론 두 트랜잭션이 다른 값을 입력하거나 Unique 인덱스가 없으면 INSERT에 대한 로우 Lock 경합은 발생하지 않는다. 

   - MVCC 모델을 사용하는 오라클은 SELECT 문에 로우 Lock을 사용하지 않으며 다른 트랜잭션이 변경한 로우를 읽을 때 복사본 블록을 만들어 쿼리가 시작된 시점으로 되돌려 읽고, 변경이 진행중인 로우를 읽을 때도 Lock이 풀릴 때까지 기다리지 않고 복사본을 만들어 읽는다.

   - MVCC 모델을 사용하지 않는 DBMS는 SELECT문에 공유 Lock을 사용하며 공유 Lock끼리는 서로 호환된다.

   - 반면 공유 Lock과 배타적 Lock은 서로 호환되지 않기 때문에 DML과 SELECT가 서로 진행을 방해할 수 있다. 즉 다른 트랜잭션이 변경중인 로우를 읽기 위해 커밋까지 기다려야 한다.

   - DML 로우 Lock에 의한 성능 저하를 방지하기 위해 온라인 트랜잭션을 처리하는 주간에 Lock을 필요이상으로 유지하지 않도록 커밋 시점을 조절해야 하며, Lock이 오래 지속되지 않도록 SQL을 튜닝해야한다.

▶️ DML 테이블 lock

   - 오라클은 DML로우 Lock을 설정하기 전에 현재 트랜잭션이 갱신중인 테이블 구조를 다른 트랜잭션이 변경하지 못하도록 테이블 Lock을 먼저 설정한다.

   - TM Lock이라고도 부른다.

   - 테이블 Lock에는 여러가지 Lock 모드가 존재하고, Lock 모드간의 호환성(Compatibility)은 다음과 같다.

	Null	RS	RX	S	SRX	X
Null	O	O	O	O	O	O
RS	O	O	O	O	O
RX	O	O	O
S	O	O		O
SRX	O	O
X	O

 

RS : row share (또는 SS: sub share)

RX : row exclusive (또는 SX : sub exclusive)

S : share

SRX : share row exclusive (또는 SSX : share/sub exclusive)

X : exclusive

- 위와 같이 선행 트랜잭션과 호환되지 않는 모드로 테이블 Lock을 설정하려는 후행 트랜잭션은 대기하거나 작업을 포기해야 한다.

- INSERT, UPDATE, DELETE ,MERGE문을 위한 로우 Lock 설정 시 해당 테이블에 RX(=SX) 모드 테이블 Lock을 먼저 설정해야 한다. 

- SELECT FOR UPDATE를 위해 로우 Lock을 설정 시 10gR1 이하는 RS 모드, 10gR2 이상은 RX모드 테이블 Lock을 설정해야 하고, RS, RX간에는 호환이 되므로 SELECT FOR UPDATE나 DML 수행 시 테이블 Lock에 의한 경합은 발생하지 않는다.

- 테이블 Lock은 테이블 전체에 Lock이 걸리는 것이 아니며 오라클의 테이블 Lock은 자신이 해당 테이블에서 현재 어떤 작업을 수행 중인지를 알리는 일종의 푯말이며 테이블 Lock에는 여러 가지 모드가 있고, 어떤 모드를 사용했는지에 따라 후행 트랜잭션이 수행할 수 있는 작업의 범위가 결정된다. 

- 후행 트랜잭션이 작업을 기다리기로 결정한다면 대기자 목록에 Lock 요청을 등록하고 기다린다. 

 

😎 대상 리소스가 사용 중일 때의 진로 선택

Lock을 얻고자 하는 리소스가 사용중일 때, 보통은 내부적으로 진로가 결정되어있지만 사용자가 선택할 수 있는 경우가 있는데 아래 세가지 방법이 존재한다.

 

1️⃣ Lock이 해제될 때까지 기다린다. ➡️ ex) select * from t for update

2️⃣ 일정시간만 기다리다 포기한다. ➡️ ex) select * from t for update wait 3

3️⃣ 기다리지 않고 작업을 포기한다. ➡️ ex) select * from t for update nowait

 

DML을 수행할 때 묵시적으로 테이블 Lock을 설정하며, 이때는 1️⃣번의 기다리는 방법을 선택한다. Lock Table 명령을 이용해 명시적으로 테이블 Lock을 설정할 때에도 기본적으로 기다리는 방법을 택하지만 NOWAIT 옵션을 이용해 곧바로 작업을 포기하도록 사용자가 지정할 수 있다. (ex) lock table emp in exclusive mode NOWIAT)

+) DDL 수행시에도 내부적으로 테이블 Lock을 설정하며, 이때는 NOWIAT 옵션이 자동으로 지정된다. 오라클 11g 이후로 ddl_lock_timeout 파라미터를 0보다 크게 설정하면 설정한 초만큼 기다리다 작업을 포기하게 할 수 있다.

 

😊Lock 을 푸는 열쇠, 커밋

💡 블로킹(Blocking): 선행 트랜잭션이 설정한 Lock 때문에 후행 트랜잭션이 작업을 진행하지 못하고 멈춰 있는 상태, 해소방법은 커밋 or 롤백 뿐이다.

💡 교착상태(DeadLock): 두 트랜잭션이 각각 특정 리소스에 Lock을 설정한 상태에서 맞은편 트랜잭션이 Lock을 설정한 리소스에 또 Lock을 설정하려고 진행하는 상황, 교착상태 발생 시 둘 중 하나가 뒤로 물러나지 않는다면 영영 풀리지 않는다. 오라클에선 이를 먼저 인지한 트랜잭션이 문장 수준 롤백을 진행한 후 ORA-00060: deadlock detected while waiting for resource 에러를 던진다. 이 때 교착상태는 해결되지만 블로킹 상태에 놓이게 되는데 이 메세지를 받은 트랜잭션은 커밋 또는 롤백을 결정해야하며, 이 에러에 대한 예외처리를 하지 않는다면 대기 상태를 지속하게 된다.

 

 

트랜잭션이 너무 길면 트랜잭션을 롤백해야 할 때 너무 많은 시간이 걸려 고생할 수 있으며 Undo 세그먼트가 고갈되거나 Undo 세그먼트 경합을 유발할 수 있어 불필요하게 트랜잭션을 길게 정의하지 않도록 주의해야한다. 

또 같은 데이터를 갱신하는 트랜잭션이 동시에 수행되지 않도록 설계해야하고, DML Lock 때문에 동시성이 저하되지 않도록 적절한 시점에 커밋해야 한다. 하지만 불필요하게 너무 자주 커밋하면 서버 프로세스가 LGWR에게 로그버퍼를 비우도록 요청하고 동기(sync)방식으로 기다리는 횟수가 늘기 때문에 기본적으로 성능이 느려진다.

잦은 커밋으로 성능이 느릴 때 오라클 10gR2 부터 제공하는 비동기식 커밋과 배치 커밋을 활용할 수 있다.

 

▶️ WAIT(default): LGWR가 로그버퍼를 파일에 기록했다는 완료 메시지를 받을 때까지 기다린다.(동기식 커밋)

▶️ NOWAIT: LGWR의 완료 메세지를 기다리지 않고 바로 다음 트랜잭션을 진행한다.(비동기식 커밋)

▶️ IMMEDIATE(Default): 커밋 명령을 받을 때마다 LGWR가 로그버퍼를 파일에 기록한다. 

▶️ BATCH: 세션 내부에 트랜잭션 데이터를 일정량 버퍼링했다가 일괄 처리한다. 

 

 사용예시)

COMMIT WRITE IMMEDIATE WAIT; -------- (1)
COMMIT WRITE IMMEDIATE NOWAIT; ------ (2)
COMMIT WRITE BATCH WAIT; ------------ (3)
COMMIT WRITE BATCH NOWAIT; ---------- (4)

---

4.2 트랜잭션 동시성 제어

⭐ 비관적 동시성 제어(Pessimistic Concurrency Control)

: 사용자들이 같은 데이터를 수정할 것으로 가정하여 한 사용자가 데이터를 읽는 시점에 Lock을 걸고 조회 또는 갱신처리가 완료될 때까지 이를 유지한다. Lock은 첫 번째 사용자가 트랜잭션을 완료하기 전까지 다른 사용자들이 같은 데이터를 수정할 수 없어 비관적 동시성 제어를 잘못 사용시 동시성이 나빠진다.

 

▶️구현패턴 예시

select 적립포인트, 방문횟수, 최근방문일시, 구매실적
from 고객
where 고객번호 = :cust_num;

-- 새로운 적립포인트 계산

update 고객 set 적립포인트 = :적립포인트 where 고객번호 = :cust_num

 

위 쿼리 예시에서 고객의 적립 포인트를 조회하는 select 문 실행 시점에 같은 고객의 적립포인트 금액이 변경된다면 문제가 발생할 수 있는데 아래와 같이 SELECT FOR UPDATE를 사용하면 고객 레코드에 Lock을 설정해 데이터가 잘못 갱신되는 문제를 방지할 수 있다. SELECT FOR UPDATE 사용 시 동시성을 심각하게 떨어뜨릴 우려가 있지만 WAIT, NOWAIT 옵션을 함께 사용하면 Lock을 얻기 위해 무한정 기다리는 것을 방지할 수 있다.

select 적립포인트, 방문횟수, 최근방문일시, 구매실적
from 고객
where 고객번호 = :cust_num for update;

-- nowait, wait 옵션 사용
for update nowait; -- 대기 없이 Exception(ORA-00054)을 던짐
for update wait 3; -- 3초 대기 후 Exception(ORA-30006)을 던짐

 

큐(Queue) 테이블 동시성 제어

큐 테이블에서 동시성 제어를 하기 위해서 skip locked 옵션을 사용하면, Lock이 걸린 레코드는 생략하고 다음 레코드를 계속 읽도록 구현할 수 있다.

 

select cust_id, rcpt_amt

from cust_rcpt_Q

where yn_upd = 'Y' 

FOR UPDATE SKIP LOCKED;

⭐낙관적 동시성 제어(Optimistic Concurrency Control)

: 사용자들이 같은 데이터를 동시에 수정하지 않을 것으로 가정해 데이터를 읽을 때 Lock을 설정하지 않는다. 이 경우엔 읽는 시점에 Lock을 사용하지 않았지만 데이터를 수정하고자 하는 시점에 앞서 읽은 데이터가 다른 사용자에 의해 변경되었는지 반드시 검사해야한다.

 

▶️ 낙관적 동시성 제어 예시

select 적립포인트, 방문횟수, 최근방문일시, 구매실적 into :a, :b, :c, :d
from 고객
where 고객번호 = :cust_num;

-- 새로운 적립포인트 계산
update 고객 
set 적립포인트    = :적립포인트
where 고객번호    = :cust_num
and 적립포인트    = :a
and 방문횟수      = :b
and 최근방문일시  = :c
and 구매실적      = :d;

if sql%rowcount = 0 then
  alert('다른 사용자에 의해 변경되었습니다.');
end if;

 

위처럼 조건절을 일일이 기술하는것이 귀찮다면 UPDATE 대상 테이블에 최종변경일시를 관리하는 컬럼이 있다면 이를 조건절에 넣어 간단하게 해당 레코드의 갱신여부를 판단할 수 있다.

select 적립포인트, 방문횟수, 최근방문일시, 구매실적 변경일시 into :a, :b, :c, :d, :mod_dt
from 고객
where 고객번호 = :cust_num;

-- 새로운 적립포인트 계산
update 고객 
set 적립포인트    = :적립포인트,
변경일시 = SYSDATE
where 고객번호    = :cust_num
and   변경일시    = :mod_dt; -- 최종 변경일시가 앞서 읽은 값과 같은지 비교

if sql%rowcount = 0 then
  alert('다른 사용자에 의해 변경되었습니다.');
end if;

 

낙관적 동시성 제어에서도 UPDATE 전에 NOWAIT 옵션을 사용한 SEELCT 문을 한번 더 수행해주어 Lock에 대한 예외처리를 해준다면 다른 트랜잭션이 설정한 Lock을 기다리지 않게 구현 가능하다.

select 고객번호
from 고객
where 고객번호 = :cust_num
and 변경일시 = :mod_dt
for update nowait;

 

😎 데이터 품질과 동시성 향상을 위한 제언

- SELECT FOR UPDATE 사용을 두려워하지 말자

- 불필요하게 Lock을 오래 유지하지 않고, 트랜잭션 원자성을 보장하는 범위 내에서 가급적 빨리 커밋하자

- 낙관적 동시성 제어를 시도 했다가 다른 트랜잭션에 의해 데이터가 변경된 사실이 발견되면, 롤백하고 다시 시도할 때 비관적 동시성 제어를 사용하는 방식도 있다.

- 동시성 향상을 위해 SQL 튜닝은 기본이다.

 

💡 로우 Lock 대상 테이블 지정

 

계좌 마스터와 주문 테이블이 존재할 때 쿼리를 아래와 같이 작성하면 계좌마스터와 주문 테이블 양쪽에 모두 로우 Lock이 걸리게 된다.

select b.주문수량
from 계좌마스터 a, 주문 b
where a.고객번호 = :cust_no
and b.계좌번호 = a.계좌번호
and b.주문일자 = :ord_dt
for update

 

이때 아래와 같이 작성하면 주문수량이 있는 주문 테이블에만 로우 Lock이 걸린다.

select b.주문수량
from 계좌마스터 a, 주문 b
where a.고객번호 = :cust_no
and b.계좌번호 = a.계좌번호
and b.주문일자 = :ord_dt
for update of b.주문수량

---

4.3 채번 방식에 따른 INSERT 성능 비교

INSERT, UPDATE, DELETE, MERGE 중 가장 중요하고 튜닝요소가 많은것은 INSERT이다.

그중 채번 방식에 따른 성능 차이가 매우 큰데 아래 세가지 채번 방식의 성능과 장단점을 비교해보자

 

▶️ 채번 테이블

: 각 테이블 식별자의 단일컬럼 일련번호 또는 구분 속성별 순번을 채번하기 위해 별도 테이블을 관리하는 방식으로 채번 레코드를 읽어 1을 더한 값으로 변경하고, 그 값을 새로운 레코드를 입력하는 데 사용한다. 채번 레코드를 변경하는 과정에 자연스럽게 액세스 직렬화가 이루어지므로 두 트랜잭션이 중복 값을 채번할 가능성을 원천적으로 방지해준다.

 

😎 채번 테이블의 장점

   ➡️ 범용성이 좋다.

   ➡️ 중복 레코드 발생에 대비한 예외처리에 크게 신경쓰지 않아도 되어 채번함수만 잘 정의하면 편리하게 사용 가능하다.

   ➡️ INSERT 과정에 결번을 방지할 수 있다.

   ➡️ PK가 복합컬럼일 때도 사용할 수 있다.

😭 채번 테이블의 단점

   ➡️ 채번 레코드를 변경하기 위한 로우 Lock 경합이 발생할 수 있어 다른 채번방식에 비해 성능이 좋지 않다.

   ➡️ 동시 INSERT가 아주 많으면 채번 레코드 뿐만 아니라 채번 테이블 블록 자체에도 경합이 발생하므로 동시 INSERT가 아주 많은 테이블에는 사실상 이 방식을 사용하기 어렵다. 

자율 트랜잭션

PL/SQL의 자율 트랜잭션 기능을 이용하면 메인 트랜잭션에 영향을 주지 않고 서브 트랜잭션에서 일부 자원만 Lock을 해제할 수 있다. 즉 PL/SQL을 자율 트랜잭션으로 선언하면 그 내부에서 커밋을 수행하더라도 메인 트랜잭션은 커밋하지 않은 상태로 남게 되어 다른 트랜잭션을 블록킹 하지 않는다.

 

create or replace function seq_nextval(l_gubun number) return number 
as 
   pragma autonomous_transaction;
   l_new_seq seq_tab.seq%type;
   begin
      update seq_tab
      set    seq = seq + 1
      where  gubun = l_gubun;
      
      select seq into l_new_seq
      from   seq_tab
      where  gubun = l_gubun;
      
      commit;
      return l_new_seq;
end;

--- 사용예
insert into target_tab values (seq_nextval(123), :x, :y, :z);

▶️ 시퀀스 오브젝트

😎 시퀀스 오브젝트 장점

   ➡️ 성능이 빠르다.

   ➡️ INSERT 과정에 중복 레코드 발생에 대비한 예외처리에 크게 신경쓰지 않아도 된다.

   ➡️ 테이블별로 시퀀스 오브젝트를 생성하고 관리하는 부담은 있지만 개발팀 입장에서 사용하기 매우 편리하다.

   ➡️ 자율 트랜잭션 기능이 기본적으로 구현되어 있다. 

😭 시퀀스 오브젝트 단점
   ➡️ 시퀀스는 오라클 내부에서 관리하는 채번 테이블로 채번 과정에 발생하는 Lock 매커니즘 때문에 성능이슈가 발생할 수 있지만 캐시 사이즈를 적절히 설정만 해준다면 가장 빠른 성능을 제공한다.

시퀀스 테이블 SYS.SEQ$ (DBA_SEQUENCES 뷰를 통해 조회 가능)

 

❓시퀀스 오브젝트에서 사용하는 Lock의 종류

1️⃣ 로우 캐시 Lock 

딕셔너리 정보를 매번 디스크에서 읽고 쓰면 성능이 매우 느리므로 오라클은 로우 캐시를 사용한다.

로우 캐시는 SGA 구성요소이므로 정보를 읽고 쓸 때 액세스를 직렬화해야하며, 이 때 로우 캐시 Lock을 사용한다.

시퀀스(SYS.SEQ$)에서도 nextval을 호출할 때마다 로우 캐시에서 시퀀스 레코드를 변경해야 하는데 많은 사용자가 동시에 nextval을 호출하면 로우 캐시 Lock 경합이 발생한다. 

이 시퀀스 채번으로 인한 로우 캐시 Lock 경합을 줄이기 위해 오라클은 기본적으로 CACHE 옵션(기본값은 20)을 사용한다. 만약 채번 빈도가 낮아 굳이 캐시를 이용하고 싶지 않다면 NOCACHE 옵션을 지정하면 된다.

-- 시퀀스 생성 및 캐시 옵션을 1000으로 설정(로우캐시에 1000개씩 불러옴)
create sequence MYSEQ cache 1000;

-- 시퀀스 정보 확인
select cache_size, last_number
from user_seqeunces
where sequence_name = 'MYSEQ';

-- 위 쿼리의 출력 결과
CACHE_SIZE LAST_NUMBER
---------- ------------
     1000            1
  
-- nextval 사용
select MYSQL.NEXTVAL from dual;

-- nextval을 사용함으로써 출력되는 시퀀스 번호
NEXTVAL
--------
      1

-- 다시 시퀀스 확인
select cache_size, last_number
from user_sequences
where sequence_name = 'MYSEQ';

-- 위 쿼리의 출력 결과(last number 가 1001인 이유는 1000개를 시퀀스 캐시에서 담아뒀다가 
-- 모두 소진한 직후에 nextval 호출 시 그때 다시 로우 캐시에서 시퀀스 레코드를 2001로 변경한다.

CACHE_SIZE LAST_NUMBER
---------- ------------
     1000         1001

 

2️⃣ 시퀀스 캐시 Lock(=SQ Lock)

시퀀스 캐시도 공유 캐시이며 시퀀스 캐시에서 값을 얻을 때에도 액세스 직렬화가 필요하다. 

 

3️⃣ SV Lock 

시퀀스 캐시는 한 인스턴스 내에서 공유되며 nextval 호출 순서대로 제공하므로 한 인스턴스 내에서는 번호 순서가 보장된다.

하지만 데이터베이스 하나에 인스턴스가 여러 개인 RAC 환경에서는 인스턴스마다 캐시를 따로 갖는다.

첫 번째 인스턴스는 1~1000까지 값을 두 번째 인스턴스는 1001 ~ 2000, ... 방식으로 가지므로 두 인스턴스가 교차로 nextval 호출 시 아래와 같이 값이 입력된다.

 

1 -> 1001 -> 2 -> 1002 -> 3 -> 1003 -> 4 -> 1004 -> 5 -> 1005 ....

 

식별자의 값을 순서대로 입력해야하는 법은 없지만 업무 담당자의 요청으로 식별자를 일련번호 처럼 쓰기 원한다면 시퀀스 캐시 하나를 모든 RAC 노드가 공유하기 위해 ORDER 옵션을 사용해야한다.

ORDER 옵션을 사용하게 되면 SV Lock 을 통해 시퀀스 캐시에 대한 액세스를 직렬화하며 인스턴스끼리 네트워크를 통해 시퀀스 캐시를 서로 주고받으며 공유하기 때문에 당연히 성능이 좋지 않다.

   ➡️ 시퀀스는 기본적으로 PK가 단일컬럼일 때만 사용 가능하다. 복합 컬럼일 때에도 사용은 할 수 있지만 각 레코드를 유일하게 식별하는 최소 컬럼으로 PK를 구성해야 한다는 최소성(Minimalty) 요건을 위배하게 된다. PK가 복합컬럼인데 동시 트랜잭션이 높아 시퀀스가 꼭 필요하다면 순환(cycle) 옵션을 가진 시퀀스를 활용할 수 있다. 하루에 도저히 도달할 수 없는 값으로 최대값(maxvalue) 으로 설정하고, 그 값에 도달 시 1부터 다시 시작(값이 무한정 커지는 것을 방지하기 위해)하도록 순환 옵션을 설정한다. 

   ➡️ 신규 데이터 입력하는 과정에서 시퀀스 채번 이후에 트랜잭션을 롤백한다거나 CACHE 옵션을 설정한 시퀀스가 자주 사용하지 않아 캐시에서 밀려나거나, 인스턴스를 재기동하는 순간 캐시되어있던 번호가 사라져 디스크에서 읽을 때 그 다음부터 읽게 됨으로써 결번이 발생할 수 있다.

이때 인스턴스 재기동에 의한 결번은 어쩔 수 없지만 사용 빈도가 낮아 생기는 결번은 아래와 같은 명령을 수행해주면 된다.

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

 

하지만 업무 특성을 제외하고는 일련번호에 결번이 생기는 현상은 굳이 막을 필요가 없다.

 

▶️ MAX + 1

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

select into 상품거래(거래일련번호, 계좌번호, 거래일시, 상품코드, 거래가격, 거래수량)
values (select max(거래일련번호) + 1 from 상품거래)
        , :anct_no, sysdate, :prod_cd, :trd_price, :trd_qty);

😎MAX + 1의 장점

   ➡️ 시퀀스 또는 별도의 채번 테이블을 관리하는 부담이 없다.

   ➡️ 동시 트랜잭션에 의한 충돌이 많지 않다면 성능이 매우 빠르다.

   ➡️ PK가 복합컬럼인 경우 구분 속성별 순번을 채번할 때도 사용할 수 있다. 구분 속성 값의 수가 아무리 많아도 상관 없으며, 오히려 값의 수가 많을수록 입력 값 중복에 의한 로우 Lock 경합이 줄고 재실행 횟수가 줄기 때문에 성능이 더 좋아진다.

😭 MAX + 1의 단점
   ➡️ 레코드 중복에 대비한 세밀한 예외처리가 필요하다.

   ➡️ 다중 트랜잭션에 의한 동시 동시 채번이 심하면 레코드 중복에 의한 로우 Lock 경합으로 시퀀스보다 성능이 많이 나빠질 수 있다. 이는 복합 컬럼으로 구분 속성별 값의 수가 많아질 수록 로우 Lock 경합 및 재실행 가능성은 현저히 줄어든다.

   ➡️ 또한 MAX 값 조회에 최적화된 인덱스를 구성해 주지 않을 때 성능 이슈가 발생할 수 있다.

 

💡각 채번 방식에 발생하는 Lock 경합 요소 정리

- 다중 트랜잭션에 의한 동시 채번이 많지 않다면 세 가지 방식 중 어느 방식을 사용해도 상관이 없지만 채번 테이블이나 시퀀스 오브젝트 관리부담을 고려한다면 MAX + 1 방식을 선택하는 것이 좋다.

- 다중 트랜잭션에 의한 동시 채번이 많고 PK 가 단일컬럼이 일련번호라면 시퀀스방식이 가장 좋다.

- 다중 트랜잭션에 의한 동시 채번이 많고 PK 구분 속성 값 종류 개수가 많으면, 중복에 의한 로우 Lock 경합 및 재실행 가능성이 낮으므로, 시퀀스보다 MAX + 1 방식이 구조적 측면으로 좋다.

- 다중 트랜잭션에 의한 동시 채번이 많고 PK 구분 속성에 값 종류 개수가 적으면, 순환(cycle) 옵션을 가진 시퀀스 오브젝트 활용을 고려할 수 있다.

 

오라클 12.1 버전에서 생긴 시퀀스 신기능

1️⃣ 컬럼 기본값으로 시퀀스 지정

create sequence my_seq

create table t (
   c1 number default my_seq.nextval not null
 , c2 varchar2(5));

 

2️⃣ 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');

 

GENERATED ALWAYS 옵션을 지정한 컬럼에 아래와 같이 값을 직접 입력하게 되면 ORA-32792 에러가 발생한다.

insert into t (c1, c2) values (3, 'X');

 

이 때 사용자가 직접 입력할 수 있게 하려면 아래와 같이 GENERATED BY DEFAULT 옵션을 지정하면 된다.

create table t (c1 number generated by default as identity, c2 varchar2(5));

 

3️⃣ 세션 시퀀스

글로벌 시퀀스는 여러 세션이 공유할 수 있는 시스템 레벨 시퀀스로 아래와 같이 생성한다.(생략 가능)

create sequence g_seq GLOBAL;

 

세션 시퀀스는 여러 세션이 공유할 수 없는 세션 레벨 시퀀스로 아래와 같이 생성한다. (생략 불가)

create sequence s_seq SESSION;

 

세션 시퀀스의 경우 세션 내에서만 유효하며 세션이 종료되면 초기화된다. Lock 매커니즘이 불필요해 글로벌 시퀀스보다 성능은 좋지만 기능이 다르므로 용도에 따라 선택해야 한다.

  예를들면 스테이징(Staging) 테이블에 데이터 적재 시 시퀀스를 여러 세션이 호출하지 않기 때문에 굳이 글로벌 시퀀스를 이용할 이유가 없고 데이터를 새로 적재하기 전 Truncate 하므로 마지막 값에 이어 값을 채번할 필요가 없으므로 성능이 더 빠른 세션 시퀀스가 유용하다.

😎 시퀀스보다 좋은 솔루션

한개 이상의 구분 속성과 함께 뒤쪽에 순번 대신 입력일시를 두는 방식으로 PK 구조를 설계하면 채번과정을 생략하고 SYSDATE, SYSTIMESTAMP 함수만 호출하면 되므로 채번 또는 INSERT 과정에 생기는 Lock 이슈를 거의 해소할 수 있다.

정보 생명주기를 관리하는데 있어 데이터 삭제는 매우 중요하다. 파티션을 활용한 대량 DELETE 튜닝으로 서비스 중단 없이 파티션 단위로 커팅할 때 기본적으로 PK 인덱스가 로컬 파티션이여야하고 PK 인덱스를 로컬 파티셔닝 하려면 삭제 기준 컬럼이 PK에 포함되어야 하는데 이 때 삭제 기준을 대게 입력일시 컬럼으로 한다.

 

😊 시퀀스 블록 경합

- Insert 성능이 너무 빨라도 문제인게 인덱스 블록 경합이 나타날 수 있다.

- 인덱스 경합은 Right Growing 인덱스에서 가장 흔하게 볼 수 있는데 인덱스는 키 순으로 정렬된 상태를 유지하는데 값이 입력되므로 '일련번호'나 '입력일시/변경일시' 처럼 순차적으로 값이 증가하는 단일컬럼 인덱스는 항상 맨 우측 블록에만 데이터가 입력된다. 

- Right Growing 인덱스에는 입력하는 값이 달라도 같은 블록을 갱신하려는 프로세스 간 버퍼 Lock 경합이 발생할 수 있다.

- 이런 인덱스 경합은 흔히 발생하며 특히 RAC 환경에서 여러 노드가 동시에 Current 블록 하나를 서로 주고 받으며 값을 입력하기 때문에 심각한 성능 저하를 일으킨다.

- 구분 속성이 앞에 있는 경우 Right Growing 인덱스는 아니지만 동시성이 매우 높으면 역시나 인덱스 블록 경합이 생길 수 있고, 구분 속성의 값 종류 개수가 적을 수록 경합이 심해진다. 

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

- 인덱스를 리버스(Reverse) 키 인덱스로 전환하는 방법도 고려할 수 있다.

 

🥸시퀀스 신기능 활용(오라클 12c)

아래와 같이 글로벌 시퀀스와 세션 시퀀스를 각각 하나씩 만들고 글로벌 시퀀스 currval과 세션 시퀀스 nextval을 조합한 값으로 INSERT 하게 되면 각 프로세스가 서로 다른 리프 블록에 값을 입력하므로 인덱스 경합이 발생하지 않는다.

create sequence g_seq global;
create sequence s_seq session;

select g_seq.nextval from dual;

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

 

🥸시퀀스 신기능 활용(오라클 18c)

Scalable 시퀀스를 이용해 Right Growing 인덱스 성능 문제를 해결할 수도 있는데 아래와 같이 'SCALE' 또는 'SCALE EXTEND' 옵션을 지정하면 된다. Scalable 시퀀스에서 nextval 호출시 아래와 같이 인스턴스 번호, 세션 ID, 시퀀스번호를 조합한 번호를 반환한다.

create sequence my_seq maxvalue 9999 SCALE EXTEND;

select my_seq.nextval as last_value
      , substr(my_seq.nextval, 1, 3) as val1
      , substr(my_seq.nextval, 4, 3) as val2
      , substr(my_seq.nextval, 7)    as val3
      , sys_context('userenv', 'instance') as inst_id
      , sys_context('userenv', 'sid') as sid
from dual;

LAST_VALUE  VAL1   VAL2     VAL3     INST_ID    SID
---------- -----  ------  -------- ---------- -----
1011410001  101     141     0001        1       141

 

EXTEND 옵션을 생략함녀 맨 우측 시퀀스 번호(VAL3)가 1,2,3 순으로 증가하는 리딩 제로(leading zero), 없는 숫자를 반환한다.

18c Scalable 시퀀스에 착안해 하위버전에서도 아래 값들을 조합한다면 같은 기능을 구현할 수 있다.

select sys_context('userenv', 'instance') as 인스턴스번호
     , sys_context('userenv', 'sid') as 세션ID
     , my_seq.nextval as 시퀀스번호
from dual;