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

파티션을 이용하면 대량 추가/변경/삭제 작업을 빠르게 처리할 수 있다. 

🔎 테이블 파티션

  • 파티셔닝(Partitioniong): 테이블 또는 인덱스 데이터를 특정 컬럼(파티션 키) 값에 따라 별도 세그먼트에 나눠서 저장하는 것
  • 일반적으로 시계열에 따라 Range방식으로 분할하지만, 그 외 다른 기준(리스트 또는 해시방식)으로 분할할 수 있다.

⚙️ 파티션이 필요한 이유 

  • 관리적 측면 : 파티션 단위 백업, 추가, 삭제, 변경 → 가용성 향상
  • 성능적 측면 : 파티션 단위 조회 및 DML, 경합 또는 부하 분산

파티션에는 Range, 해시, 리스트 세 종류가 있다.

⚙️ Range 파티션

  • 오라클 8버전부터 제공된 가장 기초적인 방식
  • 주로 날짜 컬럼을 기준으로 파티셔닝함

아래는 주문 테이블을 주문일자 기준으로 분기별 Range 파티셔닝하는 방법을 예시하고 있다. 

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'
);
  • 파티션 테이블에 값을 입력하면 각 레코드를 파티션 키 값에 따라 분할 저장하고, 읽을 때도 검색 조건을 만족하는 파티션만 골라 읽을 수 있어 이력성 데이터를 Full Scan 방식으로 조회할 때 성능을 크게 향상한다.
  • 보관주기 정책에 따라 과거 데이터가 저장된 파티션만 백업하고 삭제하는 등 데이터 관리 작업을 효율적이고 빠르게 수행할 수 있는 장점도 있다.
  • 파티션 테이블에 대한 SQL 성능 향상 원리는 파티션 Pruning(=Elimination)에 있다.
    • 'prune'은 쓸데없는 가지를 치다, 불필요한 부분을 제거한다는 뜻을 가짐

파티션 Pruning은 SQL 하드파싱이나 실행 시점에 조건절을 분석해서 읽지 않아도 되는 파티션 세그먼트를 액세스 대상에서 제외하는 기능이며, 아래와 같은 조건절이 예시이다. 

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

🚩 해당 조건절을 만족하는 데이터는 전체 1,200만 건 중 25%에 해당하는 300만 건이다. 위의 데이터양을 인덱스로 건건이 랜덤 액세스하면 테이블 전체를 스캔하는 것보다 성능이 느리다. 그렇다고 테이블 전체를 스캔하면 사이즈가 너무 커서 부담스럽다.

> 테이블을 100만건 단위로 나눠서 저장하면 Full Scan 하더라도 전체가 아닌 일부 파티션 세그먼트만 읽고 멈출 수 있어 성능을 크게 향상한다,

  • 파티션과 병렬처리가 만나면 그 효과가 배가 된다.
  • 파티션 테이블도 인덱스로 액세스할 수 있지만, 파티션 Pruning을 이용한 테이블 스캔보다 훨씬 느리다.
  • 파티션도 클러스터, IOT와 마찬가지로 관련 있는 데이터가 흩어지지 않고 물리적으로 인접하도록 저장하는 클러스터링 기술에 속한다.
  • 클러스터와 다른 점은 세그먼트 단위로 모아서 저장하는 것이다.
  • 클러스터는 데이터를 블록단위로 모아 저장하고, IOT는 데이터를 정렬한 순서로 저장하는 구조이다. 

 

⚙️ 해시 파티션 

  • Range파티션에 이어 오라클 8i버전부터 제공하기 시작했다. 파티션 키 값을 해시 함수에 입력해서 반환하는 값이 ㄱㅌ은 데이터를 같은 세그먼트를 저장하는 방식이다.
  • 파티션 개수만 사용자가 결정하고 데이터를 분산하고 알고리즘은 오라클 내부 해시함수가 결정한다. 
  • 해시 파티션은 고객ID처럼 변별력이 좋고 데이터 분포가 고른 컬럼을 파티션 기준으로 선정해야 효과적이다. 
  • 아래는 고객ID기준으로 고객 테이블을 해시 파티셔닝하는 방법을 예시한다. 
create table 고객(고객id varchar2(5), 고객명 varchar2(10), … )
partition by hash(고객id) partitions 4;
  • 검색할 때는 조건절 비교값(상수 또는 변수)에 똑같은 해시 함수를 적용함으로써 읽은 파티션을 결정한다. 해시 알고리즘 특성상 등치(=)조건 또는 IN-List 조건으로 검색할 때만 파티션 Pruning이 작동한다.

⚙️ 라스트 파티션 

오라클 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 파티션에는 값의 순서에 따라 저장할 파티션이 결정되지만, 라스트 파티션에서는 순서와 상관없이 불연속적인 값의 목록에 의해 결정된다.
  • 해시파티션과 비교하면, 해시파티션은 오라클이 정한 해시 알고리즘에 따라 임의로 분할하는 반면, 리스트 파티션은 사용자가 정의한 논리적인 그룹에 따라 분할한다.
  • 업무적인 친화도에 따라 그룹핑 기준으로 정하되, 될 수 있으면 각 파티션에 값이 고르게 분산되도록 해야 한다.

🔎 인덱스 파티션

테이블 파티션과 인덱스 파티션은 구분돼야 한다. 인덱스 파티션은 테이블 파티션과 맞물려 다양한 구성이 존재한다. 다양한 인덱스 파티션 구성을 설명하기 위해 우선 테이블 파티션을 아래와 같이 구분하자.

  • 비파티션 테이블(Non-Partitioned Table)
  • 파티션 테이블 (Partitioned Table)

인덱스로 테이블처럼 파티션 여부에 따라 파티션 인덱스와 비파티션 인덱스로 나뉘고, 파티션 인덱스는 각 파티션이 커버하는 테이블 파티션 범위에 따라 로컬과 글로벌로 나뉜다. 

  • 로컬 파티션 인덱스 (Local Partitioned Index)
  • 글로벌 파티션 인덱스 (Global Partitioned Index)
  • 비파티션 인덱스 ( Non-Partitioned Index)

로컬 파티션 인덱스는 각 테이블 파티션과 인덱스 파티션이 서로 1:1 대응관계가 되도록 오라클이 자동으로 관리하는 파티션 인덱스를 말한다. 로컬이 아닌 파티션 인덱스는 모두 글로벌 파티션 인덱스이며, 테이블 파티션과 독립적인 구성(파티션 키, 파티션 기준 값정의)을 갖는다.

테이블과 인덱스 파티션을 조합하면, 아래와 같은 구성이 가능하다.

⚙️ 로컬 파티션 인덱스 

  • 로컬 파티션 인덱스는 별도 색인을 만드는 것과 같다.
  • 주문 테이블에 로컬 파티션 인덱스를 만들어 보자 
    로컬 파티션 인덱스를 만들때는 CREATE INDEX 문 뒤에 LOCAL 옵션을 추가하면 된다.
create index 주문_x01 on 주문 (주문일자, 주문금액) LOCAL;
create index 주문_x02 on 주문 (고객ID, 주문일자) LOCAL;

 

  • 각 인덱스 파티션은 테이블 파티션 속성을 그대로 상속받는다. 따라서 테이블 파티션 키가 주문일자면 인덱스 파티션 키도 주문일자가 된다.
  • 로컬 파티션 인덱스를 로컬 인덱스라고 줄여서 부르기도 한다.
  • 로컬 파티션 인덱스는 테이블과 정확히 1:1 대응관계를 갖도록 오라클이 파티션을 자동으로 관리해준다.
  • 테이블 파티션 구성을 변경(add, drop, exchange등 하더라도) 인덱스를 재생성할 필요가 없다. 변경작업이 순식간에 끝나므로, 피크시시간대만 피하면 서비스를 중단하지 않고도 작업할 수 있다. 로컬 파티션 인덱스 장점은 이처럼 관리 편의성에 있다.

⚙️글로벌 파티션 인덱스 

  • 글로벌 파티션 인덱스는 파티션을 테이블과 다르게 구성한 인덱스다.
  • 구체적으로, 파티션 유형이 다르거나, 파티션 키가 다르거나 파티션 기준 값 정의가 다른 경우다. 
  • 비파티션 테이블이어도 인덱스는 파티셔닝할 수 있다. 
  • Range파티션을 설명하면서 만든 주문 테이블에 주문금액+주문일자 순으로 글로벌 파티션 인덱스를 만들어보자
  • CREATE INDEX 문뒤에 GLOBAL 키워드를 추가하고, 파티션을 정의하면 된다. 
create index 주문_x03 on 주문 (주문금액, 주문일자) GLOBAL
partition by range(주문금액) (
	partition P_01 values less than (100000)
	partition P_MX values less than (MAXVALUE) --> 주문금액 >= 100000
);
  • 글로벌 파티션 인덱스는 테이블 파티션 구성을 변경하는 순간 Unusable상태로 바뀌므로 곧바로 인덱스를 재생성해 줘야 한다.
  • 그동안 해당 테이블을 사용하는 서비스를 중단해야 한다. 
  • 오라클이 인덱스 파티션을 자동으로 관리해주지 않기때문에 테이블과 인덱스가 정확히 1:1관계가 되도록 DB관리자가 파티션을 직접 구성할 수도 있지만, 그렇다고 그것은 로컬 파티션은 아니다.
  • 오라클이 인덱스 파티션을 자동으로 관리해주지 않기 때문이다.(모양은 로컬파티션이지만, 글로벌 파티션에 속한다.)

⚙️ 비파티션 인덱스

  • 비파티션 인덱스는 파티셔닝을 하지 않은 인덱스이다.
  • 만드는 방법은 일반 CREATE INDEX문이다. 글로벌 인덱스라고 부르기도 한다.
CREATE INDEX 주문_X04 ON 주문 (고객ID, 배송일자);
  • 테이블 파티션 구성을 변경 하는 순간 Unusable상태로 바뀌므로 곧바로 인덱스를 재생성해줘야 한다. 
  • 그동안 해당 테이블을 사용하는 서비스를 중단해야 한다.

⚙️ Prefixed vs. Nonprefixed

파티션 인덱스를 Prefixed와 Nonprefixed로 나눌 수 있으며, 인덱스 파티션 키 컬럼이 인덱스 구성상 왼쪽 선두 컬럼에 위치하는지에 따른 구분이다. 

  • Prefixed : 인덱스 파티션 키 컬럼이 인덱스 키 컬럼 왼쪽 선두에 위치한다. 
  • Nonprefixed : 인덱스 파티션 키 컬럼이 인덱스 키 컬럼 왼쪽 선두에 위치하지 않는다. 파티션 키가 인덱스 컬럼에 아예 속하지 않을 때도 여기에 속한다.

로컬, 글로벌, Prefixed와 Nonprefixed를 조합하면 아래 4가지 구성이 나온다. 

구분 Prefixed Nonprefixed
로컬파티션 1 2
글로벌 파티션 3 4(Not Support)

글로벌 파티션 인덱스는 Prefixed 파티션만 지원되므로 결과적으로 세 개의 파티션 인덱스가 있고, 비파티션 인덱스를 포함해 아래 4가지 유형으로 최종정리할 수 있다.

  • 로컬 Prefixed 파티션 인덱스
  • 로컬 NonPrefixed 파티션 인덱스
  • 글로벌 Prefixed 파티션 인덱스
  • 비파티션 인덱스 

⚙️중요한 인덱스 파티션 제약 

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

만약 위의 제약이 없으면 어떻게 될까? 예를들어 주문일자로 파티셔닝한 테이블이 있다. PK는 주문일자 + 주문번호이고, 인덱스는 로컬 파티션이다. 결국 PK 인덱스도 주문일자로 파티셔닝한 셈이므로 파티션 키가 인덱스 구성 컬럼이다.

2017년 12월 25 일 주문번호가 123456인 주문 레코드 입력하면,  중복 값 확인을 위해 p2017_Q4 파티션 인덱스만 탐색하면 된다.
PK 인덱스가 '주문번호' 단일 컬럼인 경우, 테이블 파티션 키인 주문일자가 인덱스 구성 컬럼이 아니다.

여기서 주문번호가 123456인 주문 레코드를 입력하려면, 중복 값이 있는지 확인하기 위해 인덱스 파티션을 모두 탐색해야 한다.

레코드 입력하고, 커밋하기 전까지 다른 트랜잭션이 같은 주문번호로 다른 파티션에 입력하는 현상을 막으려면 추가적인 Lock 매커니즘이 필요하다.

그래서 Unique 인덱스를 파티셔닝할 때 파티션 키가 인덱스 컬럼에 포함되어야 한다는 조건은 DML 성능 보장을 위해  있어야 할 제약조건이다. 
문제는, 이 제약으로 인해 PK 인덱스를 로컬 파티셔닝 하지 못하면 파티션 Drop, Truncate, Exchange, Split, Merge 같은 파티션 구조 변경 작업도 쉽지 않다. 이들 작업을 하는 순간 PK 인덱스가 Unusable 상태로 바뀌기 때문이다.  곧바로 인덱스를 Rebuild 하면되지만, 그동안 해당 테이블을 사용하는 서비스를 중단한다. 

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

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

  • 입력/수정/삭제하는 데이터 비중이 5%를 넘는다면(손익분기점이 5%), 인덱스를 그대로 둔 상태에서 작업하기 보다 인덱스 없이 작업한 후에 재생성하는게 더 빠르다. 
  • 예를 들어 거래테이블에 10억 건이 저장되어 있고, UPDATE문을 통해 2015년 1월 1일 전에 발생한 거래데이터를 수정하는데, 거래 일자 조건을 만족하면서 상태코드 <> 'ZZZ'인 데이터가 5000만건(5%)이다. 테이블 레코드 5,000만 건을 변경하면서 거래_X2인덱스까지 실시간으로 관리하려면 어마어마한 시간이 소요된다.

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

  • 테이블이 파티셔닝이 되어 있고, 인덱스도 다행히 로컬 파티션이라면 좋은 해법이 있다. 
  • 수정된 값을 갖는 임시 세그먼트를 만들어 원본 파티션과 바꿔치기하는 방식이다. 

✨작업순서

1. 임시 테이블을 생성하며, 가능하다면 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;

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;

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

  • 아래와 같은 조건절로 수천만 건의 데이터를 삭제할 때에도 인덱스를 실시간으로 관리하려면 어마어마한 시간이 소요된다.
  • 인덱스를 모두 Drop 했다가 다시 생성하기에도 비용이 만만치 않다. UPDATE는 변경 대상 컬럼을 포함하는 인덱스만 재생성하면 되지만, DELETE는 모든 인덱스를 재 생성해야 한다.
delete 
from 거래 
where 거래일자 < '20150101';

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

  • 테이블이 삭제 조건절 컬럼기준으로 파티셔닝돼 있고 인덱스도 다행히 로컬 파티션이라면, 간단한 문장 하나로 대량 데이터를 순식간에 삭제할 수 있다. 
alter table 거래 drop partition p201412
--오라클 11g 
alter table 거래 drop partition for ('20141201');

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

  • 삭제조건을 만족하는 데이터가 많으면, 대량 데이터를 지울게 아니라 남길 데이터만 백업했다가 재입력하는 방식이 빠르다.

✨작업순서

1. 임시테이블을 생성하고, 남길데이터만 복제한다.

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. 파티션 키가 커팅기준 컬럼이 일치해야 한다.

▶ 예를들어, 파티션 키와 커팅 기준 컬럼이 모두 신청일자

2. 파티션 단위와 커팅 주기가 일치해야한다.

▶ 예를들어, 월 단위 파티션을 월 주기로 커팅

3. 모든 인덱스가 로컬 파티션 인덱스이어야 한다.

▶ 예를들어, 파티션 키는 신청일자, PK는 신청일자 + 신청순전

▶PK인덱스는 지금처럼 삭제 기준 인덱스 컬럼이어야 로컬 파티셔닝이 가능하다.

🔎 파티션을 활용한 대량 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;

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과 트랜잭션 동시성 제어 

Lock은 데이터베이스의 특징을 결정짓는 가장 핵심적인 메커니즘이다. 

🔎 오라클 Lock

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

래치는 SGA에 공유된 각종 자료구조를 보호하기 위해 사용하며, 버퍼 Lock은 버퍼 블록에 대한 액세스를 직렬화하기 위해 사용한다. 라이브러리 캐시 Lock과 Pin은 라이브러리 캐시에 공유된 SQL커서와 PL/SQL 프로그램을 보호하기 위해 사용한다.

애플리케이션 개발 측면에서 DML Lock을 중요하게 다루어야한다. DML Lock은 다중 트랜잭션이 동시 액세스하는 사용자 데이터의 무결성을 보호해주며, 테이블 Lock과 로우 Lock이 있다. 

⚙️ DML 로우 Lock

  • 두 개의 동시 트랜잭션이 같은 로우를 변경하는 것을 방지한다. 하나의 로우를 변경하려면 로우 Lock을 먼저 설정해야 한다. 
  • 어떤 DBMS이든지 DML 로우 Lock에는 배타적 모드를 사용하므로 UPDATE 또는 DELETE를 진행 중인 로우를 다른 트랜잭션이 UPDATE하거나 DELETE 할 수 없다.
  • INSERT에 대한 로우 Lock 경합은 Unique 인덱스가 있을 때만 발생한다. 즉, Unique 인덱스가 있는 상황에서 두 트랜잭션이 같은 값을 입력하라고 할때, 블로킹이 발생한다.
  • 블로킹이 발생하면 후행 트랜잭션은 기다렸다가 선행 트랜잭션이 커밋하면 INSERT에 실패하고, 롤백하면 성공한다.
  • 두 트랜잭션이 서로 다른 값을 입력하거나 Unique인덱스가 아예 없으면 INSERT에 대한 로우 Lock경합은 발생하지 않는다. 
  • MVCC모델을 사용하는 오라클은 SELECT문에 로우 Lock을 사용하지 않는다. 오라클은 다른 트랜잭션이 변경한 로우를 읽을 때 복사본 블록을 만들어서 쿼리가 시작된 시점으로 되돌려서 읽는다.
  • 변경 진행중인 로우를 읽을 때도 Lock이 풀릴 때까지 기다리지 않고 복사본을 만들어서 읽는다. 따라서 SELECT문에 Lock을 사용할 필요가 없다.
  • 결국 오라클에서 DML과 SELECT는 서로 진행을 방해하지 않는다. 참고로 MVCC모델을 사용하지 않는 DBMS는 SELECT문에 공유 Lock을 사용한다. 공유 Lock끼리 호환된다. 두 트랜잭션이 같이 Lock을 설정할 수 있다는 뜻이다. 
  • 반면, 공유Lock과 배타적 Lock은 호환되지 않기 때문에 DML과 SELECT가 서로 진행을 방해할 수 있다. 
  • 즉, 다른 트랜잭션이 읽고 있는 로우를 변경하려면 다음 레코드로 이동할 때까지 기다려야하고, 다른 트랜잭션이 변경 중인 로우를 읽으려면 커밋할 때까지 기다려야 한다. 
  • DML 로우 Lock에 의한 선능 저하를 방지하려면 온라인 트랜잭션을 처리하는 주간에 Lock을 필요 이상으로 오래 유지하지 않도록 커밋 시점을 조절해야 한다. 즉, Lock이 오래 지속되지 않도록 관련 SQL을 모두 튜닝해야 한다. 

⚙️DML 테이블 Lock

  • 오라클은 DML로우 Lock을 설정하기에 앞서 테이블 Lock 먼저 설정한다. 현재 트랜잭션이 갱신 중인 테이블 구조를 다른 트랜잭션이 변경하지 못하게 막기 위해서이다. 테이블 Lock을 TM Lock이라고 부르기도 한다.
  • 오라클은 로우 Lock에 항상 배타적 모드를 사용하지만, 테이블 Lock에는 여러가지 Lock모드를 사용한다. 

🚩Lock모드 간 호환성

  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모드 테이블 Lock을 먼저 설정해야 한다.

SELECT FOR UPDATE문을 위해 로우 Lock을 설정하려면 10gR1이하는 RS모드, 10gR2이상은 RX모드 테이블 Lock설정해야 한다. 같은 로우를 갱신하려고 할때만 로우 Lock에 의한 경합이 발생한다.

⚙️ Lock을 푸는 열쇠, 커밋 

  • 블로킹은 선행 트랜잭션이 설정한 Lock때문에 후행 트랜잭션이 작업을 진행하지 못하고 멈춰있는 상태를 말한다. 이것을 해소하는 방법은 커밋뿐이다. 
  • 교착상태는 두 트랜잭션이 각각 특정 리소스에 Lock을 설정한 상태에서 맞은 편 트랜잭션이 Lock을 설정한 리소스에 또 Lock을 설정하려고 진행하는 상황이다. 
  • 교착상태가 발생하면 둘 중 하나 뒤로 물러나지 않으면 풀릴 수 있다. 
 ORA-00060: deadlock detected while waiting for resource

교착상태가 발생하면, 이를 먼저 인지한 트랜잭션이 문장 수준 롤백을 진행한 후 에러를 던진다. 교착상태는 해소되지만 블로킹 상태에 놓이게 된다. 이 에러에 대한 예외처리를 하지 않는다면 대기 상태를 지속하게 된다.

  • WAIT(Defualt) :  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

⚙️ 트랜잭션 동시성 제어 

동시성 제어는 비관적 동시성 제어와 낙관적 동시성 제어로 나뉜다.

  • 비관적 동시성 제어(Pessimistic Concurrency Control)
    • 사용자들이 같은 데이터를 동시에 수정할 것으로 가정한다. 한 사용자가 데이터를 읽는 시점에 Lock을 걸고 조회 또는 갱신처리가 완료될 때까지 이를 유지한다.
    • Lock은 첫 번째 사용자가 트랜잭션을 완료하기 전까지 다른 사용자들이 같은 데이터를 수정할 수 없게 만들기 때문에 비관적 동시성 제어를 잘못 사용하면 동시성이 나빠진다.
  • 낙관적 동시성 제어(Optimistic Concurrency Control)
    • 사용자들이 같은 데이터를 동시에 수정하지 않을 것으로 가정한다. 데이터를 읽을 때 Lock을 설정하지 않는다.
    • 낙관적 입장이라도 동시 트랜잭션에 의한 잘못된 데이터 갱신을 신경 쓰지 않아도 된다는 것은 아니다. 
    • 읽는 시점에 Lock을 사용하지 않았지만, 데이터를 수정하고자 하는 시점에 앞서 읽은 데이터가 다른 사용자에 의해 변경되었는지 지 검사해야 한다.

⚙️비관적 동시성 제어

  • 우수 고객을 대상으로 적립포인트를 제공하는 이벤트를 실시한다고 가정하다.
  • 고객의 다양한 실적정보를 읽고 복잡한 산출공식을 이용해 적립포인트를 계산하는 동안 다른 트랜잭션이 같은 고객의 실적정보를 변경한다면 문제가 생길 수 있다.
select 적립포인트, 방문횟수, 최근방문일시, 구매실적 from 고객
where 고객번호 = :cust_num;

-- 새로운 적립포인트 계산
update 고객 set 적립포인트 = :적립포인트 where 고객번호 = :cust_num

하지만, 아래와 같이 SELECT문에 FOR UPDATE를 사용하면 고객 레코드에 Lock을 설정하므로, 데이터가 잘못 갱신되는 문제를 방지할 수 있다. 

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

⚙️ 낙관적 동시성 제어 

  • SELECT-LIST에서 4개의 컬럼을 참조했을 때 낙관적 동시성 제어 예시이다. 
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;
  • SELECT문에서 읽은 컬럼이 매우 많다면 UPDATE문에 조건을 일일이 기술하는 것은 쉽지 않다. UPDATE 대상 테이블에 최종 변경일시를 관리하는 컬럼이 있다면 간단히 레코드 갱신여부를 판단 할 수 있다.
select 적립포인트, 방문횟수, 최근방문일시, 구매실적, 변경일시 
into :a, :b, :c, :d
from 고객
where 고객번호 = :cust_num;

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

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

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

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

  • INSERT, UPDATE, DELETE, MERGE 중 가장 중요하고 튜닝 요소가 많은 것은 INSERT이다.
  • 수행빈도가 가장 높기도 하지만, 채번 방식에 따른 성능 차이가 매우 크기 때문이다.
  • 신규 데이터를 입력하려면 PK 중복을 방지하기 위한 채번이 선행되어야 한다.
채번 테이블
시퀀스 오브젝트
MAX + 1 조회

 ⚙️ 채번 테이블 

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

✨장점 

  • 범용성이 좋다.
  • INSERT 과정에 중복 레코드 발생에 대비한 예외 처리에 크게 신경쓰지 않아도 되므로 채번 함수만 잘 정의하면 편리하게 사용할 수 있다. ( MAX + 1 방식과 비교 )
  • INSERT 과정에 결번을 방지할 수도 있다. ( 시퀀스 방식과 비교 ) 
  • PK가 복합컬럼일 때도 사용할 수 있다. ( 시퀀스 방식과 비교 )

✨단점 

  • 다른 채번 방식에 비해 성능이 안좋은데, 채번 레코드를 변경하기 위한 로우 Lock 경합때문이다. 로우 Lock은 기본적으로 대상 테이블에 INSERT를 마치고 커밋 또는 롤백할 때까지 기다린다. 
  • 동시 INSERT가 아주 많으면 채번 레코드뿐만 아니라 채번 테이블 블록 자체에도 경합이 발생한다. 서로 다른 레코드를 변경하는 프로세스끼리도 경합할 수 있다는 뜻이다.

⚙️ 시퀀스 오브젝트

✨장점

  • 성능이 빠르다.
  • 채번 테이블과 마찬가지로 INSERT 과정에 중복 레코드 발생에 대비한 예외처리에 크게 신경 쓰지 않아도 된다.
  • 테이블별로 시퀀스 오브젝트를 생성하고 관리하는 부담은 있지만, 개발팀에서는 사용하기에 매우 편리하다.
  • 시퀀스의 가장 큰 장점이 성능이지만, 성능 이슈가 없는 것은 아니다. 시퀀스 채번 과정에 발생하는 Lock 때문이다.
  • SYS.SEQ$ 테이블을 말하며, DBA_SEQUENCES 뷰를 통해 조회할 수 있다.
시퀀스 오브젝트도 결국 테이블이므로 값을 읽고 변경하는 과정에 Lock 메커니즘이 작동한다.
시퀀스 Lock에 의한 성능 이슈가 있지만, 캐시 사이즈를 적절히 설정하면 가장 빠른 성능을 제공한다.

✨단점 

  • PK가 단일컬럼일 때만 사용가능하다.(PK가 복합컬럼일 때도 사용할 수는 있지만, 각 레코드를 유일하게 식별하는 최소 컬럼으로 PK를 구성해야 한다는 최소성 요건을 위배하게 된다.)
  •  신규 데이터를 입력하는 과정에 결번이 생길 수 있다.
    • 원인 1: 시퀀스 채번 이후에 트랜잭션을 롤백하는 경우
    • 원인 2 : CACHE옵션을 설정한 시퀀스가 캐시에서 밀려나는 경우 

⚙️ MAX + 1  조회 

  • 대상 테이블의 최종 일련번호를 조회하고 거기에 1을 더해서 INSERT 하는 방식이다.
select into 상품거래(거래일련번호, 계좌번호, 거래일시, 상품코드, 거래가격, 거래수량)
values (select max(거래일련번호) + 1 from 상품거래)
        , :anct_no, sysdate, :prod_cd, :trd_price, :trd_qty);

✨장점

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

✨단점

  • 레코드 중복에 대한 세밀한 에외처리가 필요하다
  • 다중 트랜잭션에 의한 동시 채번이 심하면 시퀀스보다 성능이 나빠질 수 있다.
    • 레코드 중복에 의한 로우 Lock 경합 때문이다. 로우 Lock은 선행 트랜잭션이 커밋 또는 롤백할 때까지 지속된다.
    • 선행 트랜잭션이 롤백하지 않는 한, INSERT는 결국 실패하게 되므로 채번과 INSERT를 다시 실행해야 한다.이런 현상이 자주 발생하면 성능이 느릴 수 밖에 없다.

🚩채번 방식에 발생하는 Lock 경합 요소 

🚩Lock 경합 요소를 고려한 채번 방식 선택 기준 정리

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

⚙️시퀀스보다 좋은 솔루션 

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

⚙️인덱스 블록 경합

  • INSERT 성능이 빨라도 문제이다. 이유는 인덱스 경합 때문인데, 채번 과정을 생략하는 순간부터 인덱스 블록 경합이 일어나며, MAX + 1 방식을 사용할 때도 자주 일어난다.
  • 인덱스 블록 경합 해소 방법은 인덱스를 “해시 파티셔닝” 하는 것이다.
  • 인덱스를 해시 파티셔닝을 하면 값이 순차적으로 증가하더라도 해시 함수가 리턴한 값에 따라 서로 다른 파티션에 입력되므로 경합을 줄일 수 있다. 
  • 인덱스를 리버스 키 인덱스로 전환하는 방법도 고려할 수 있다.

 

 

'Study > 친절한 SQL 튜닝' 카테고리의 다른 글

[DB] 7장. SQL 옵티마이저  (0) 2025.04.27
[DB] 6장. DML 튜닝(1)  (0) 2025.03.30
[DB] 5장. 소트 튜닝  (0) 2025.03.17
[DB] 4장. NL조인  (0) 2025.02.25
[DB] 3장. 인덱스 튜닝(2)  (0) 2025.01.31

티스토리툴바