[친절한 SQL 튜닝] DML 튜닝 - 2

1. 테이블 파티션

파티션 종류 : Range, 해시, 리스트

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

Range 파티션 : 가장 기초적인 방식으로 주로 날짜 컬럼을 기준으로 파티셔닝한다

파티션 테이블에 값을 입력하면 각 레코드를 파티션 키 값에 따라 분할 저장

 

검색할 때 : 조건절 비교 값(상수 또는 변수)에 똑같은 해시 함수를 적용함으로써 읽을 파티션 결정

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

 

라이트 파티션

사용자가 정의한 그룹핑 기준에 따라 데이터를 분할 저장하는 방식

 

리스트 파티션은 순서와 상관없이 불연속적인 값의 목록에 의해 결정

사용자가 정의한 논리적인 그룹에 따라 분할한다

 

테이블 파티션

• 비파티션 테이블
• 파티션 테이블

인덱스 파티션

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

로컬이 아닌 파티션 인덱스는 모두 글로벌 파티션 인덱스이며, 테이블 파티션과 독립적인 구성(파티션키, 파티션 기준값 정의)을 갖는다

 

테이블과 인덱스 파티션 조합

로컬 파티션 인덱스

CREATE INDEX 마지막에 LOCAL옵션을 추가하면 로컬 파티션 인덱스를 생성할 수 있다

 

로컬 파티션 인덱스는 테이블과 1:1 대응 관계를 갖도록 오라클이 파티션을 자동으로 관리해준다

테이블 파티션 구성 변경(add, drop, exchange 등)하더라도 인덱스를 재생성할 필요가 없다

 

글로벌 파티션 인덱스

파티션을 테이블과 다르게 구성한 인덱스이다

구체적으로, 파티션 유형이 다르거나, 파티션 키가 다르거나, 파티션 기준값 정의가 다른 경우 비파티션 테이블이어도 인덱스는 파티셔닝할 수 있다

CREATE INDEX 마지막에 GLOBAL 키워드를 추가하고 파티션을 정의하면 된다

글로벌 파티션 인덱스는 테이블 파티션 구성(drop, exchange, split 등)하는 순간 Unusable 상태로 바뀌므로 재생성해줘야한다

로컬 인덱스 처럼 보이도록 인덱스를 구성해도 로컬 파티션이 아니므로 오라클이 인덱스를 자동으로 관리해주지 않는다

 

비파티션 인덱스

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

일반 CREATE INDEX문으로 생성하면 비파티션 인덱스가 생성된다

비파티션 인덱스는 테이블 파티션 구성을 변경(drop, exchange, split 등)하는 순간 Unusable상태로 바뀌어서 인덱스를 재생성해줘야 한다

 

파티션 인덱스는 Prefixed, Nonprefixed로 나눌 수 있다

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

Nonprefixed : 인덱스 파티션 키 컬럼이 인덱스 키 컬럼 왼쪽 선두에 위치하지 않음, 파티션 키가 인덱스 컬럼에 아예속하지 않은 때도 포함

 

로컬과 글로벌의 Prefixed, Nonprefixed 조합

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

 

중요한 인덱스 파티션 제약

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

 

파티션 키가 인덱스 구성 컬럼인 상태

 

주문 번호로 중복값이 있는지 확인하려면 인덱스 파티션을 모두 탐색해야한다. 주문일자로 나누어진 파티션 테이블 모두에 주문번호가 들어갈 수 있기 때문

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

 

Unique 인덱스를 파티셔닝할 때 파티션 키가 인덱스 컬럼에 포함되어야 DML 성능 보장하기 위한 필수 조건 이다

 

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

 

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

입력/수정/삭제하는 데이터 비중이 5%를 넘는다면, 인덱스를 그대로 둔 상태에서 작업하기 보다는 인덱스 없이 작업한 후 재생성하는 게 더 빠르다

 

다만 대량 데이터의 경우 인덱스를 재생성하는데 걸리는 시간이 만만치 않으므로 이에 대해 고민하고 수행해야한다

 

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

테이블이 파티셔닝돼 있고 인덱스가 로컬 파티션인 경우, 수정된 값을 갖는 임시 세그먼트를 만들어 원본 파티션과 바꿔치기하는 방법이 있다

 

1. 임시 테이블을 생성한다. 가능하면 nologging 모드로 생성

 

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

 

3. 임시 테이블에 원본 테이블과 같은 구조로 인덱스 생성. 가능하면 nologging 모드로 생성

 

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

 

5. 임시 테이블을 Drop한다

 

6. 파티션을 logging 모드로 전환(nologging으로 작업한 경우)

 

 

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

인덱스를 실시간으로 관리하려면 어마어마한 시간이 소요

초대용량 테이블 인덱스를 모두 Drop 했다가 다시 생성하기도 쉽지 않다

 

Delete 가 느린 이유

1. 테이블 레코드 삭제
2. 테이블 레코드 삭제에 대한 Undo Logging
3. 테이블 레코드 삭제에 대한 Redo Logging
4. 인덱스 레코드 삭제
5. 인덱스 레코드 삭제에 대한 Undo Logging
6. 인덱스 레코드 삭제에 대한 Redo Logging
7. Undo에 대한 Redo Logging

 

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

테이블이 삭제 조건절 컬럼 기준으로 파티셔닝돼 있고 인덱스도 로컬 파티션이라면 삭제가 간단하다

 

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

거래일자 조건에 해당하는 데이터를 일괄 삭제하지 않고 아래와 같이 또 다른 삭제 조건이 있는 경우

만약 조건을 만족하는 데이터가 소수이면, Delete 문을 그대로 수행하면 된다

 

조건을 만족하는 데이터가 다수라고 하면, 남길 데이터만 백업했다가 재입력하는 방식이 빠른다

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

 

2. 삭제 대상 테이블 파티션을 Truncate

 

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

4. 임시 테이블 Drop

 

서비스 중단 없이 파티션 Drop 또는 Truncate를 위해서는 아래 조건을 만족해야한다

1. 파티션 키와 커팅 기준 컬럼이 일치해야 함 ex) 파티션 키와 커팅 기준 컬럼이 모두 ‘신청일자’
2. 파티션 단위와 커팅 주기가 일치해야 함 ex) 월 단위 파티션을 월 주기로 커팅
3. 모든 인덱스가 로컬 파티션 인덱스이어야 함 ex) 파티션 키는 ‘신청일자’, PK는 ‘신청일자 + 신청순번’ PK 인덱스는 삭제 기준(파티션 키) 컬럼이 인덱스 구성 컬럼이어야 로컬 파티셔닝 가능

 

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

 

비파티션 테이블일 때

비파티션 테이블에 손익분기점을 넘는 대량 데이터를 INSERT 하려면, 인덱스를 Unusable 시켰다가 재생성하는 방식이 더 빠를 수 있다

 

1. 가능하다면 테이블을 nologging 모드로 전환

 

2. 인덱스를 Unusable 상태로 전황

3. 가능하다면 Direct Path Insert 방식으로 대량 데이터를 입력

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

5. nologging 모드로 작업했다면 logging 모드로 전황

 

파티션 테이블일 때

초대용량 테이블 인덱스 재생성이 부담되기 때문에 인덱스를 그대로 둔 상태로 INSERT 한다

테이블이 파티셔닝돼 있고, 인덱스도 로컬 파티션이라면 파티션 단위로 인덱스를 재생성할 수 있어 좋다

1. 가능하다면 테이블의 파티션을 nologging 모드로 전환

 

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

 

3. 가능하다면 Direct Path Insert 방식으로 대량 데이터 입력

 

4. 가능하다면 nologging 모드로 인덱스 파티션을 재생성

 

5. nologging 모드로 작업했다면, 작업 파티션을 logging 모드로 전환

 

 

Lock과 트랜잭션 동시성 제어

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

 

오라클 Lock

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

 

DML 로우 Lock

두 개의 동시 트랜잭션이 같은 로우를 변경하는 것을 방지

하나의 로우를 변경하려면 로우 Lock을 먼저 실행해야 한다

DML 로우 Lock에는 배타적 모드를 사용하므로 UPDATE 또는 DELETE를 진행중인 로우를 다른 트랜잭션이 UPDATE나 DELETE 할 수 없다

INSERT 에 대한 로우 Lock 경합은 Unique 인덱스가 있을 때만 발생(Unique 인덱스가 있는 상황에서 두 트랜잭션이 같은 값을 입력할 때 발생)

MVCC 모델을 사용하는 오라클은 SELECT 문에 로우 Lock을 사용하지 않는다

따라서 DML 과 SELECT는 서로 진행을 방해하지 않고, SELECT 끼리도 서로 방해하지 않는다

 

 

DML 테이블 Lock

현재 트랜잭션이 갱신 중인 테이블 구조를 다른 트랜잭션이 변경하지 못하게 막기 위해 테이블 Lock이 DML Lock보다 먼저 설정된다

 

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

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

S: share

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

X: exclusive

 

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

 

오라클에서 테이블 Lock은 해당 테이블에서 현재 어떤 작업을 수행중인지 알리는 일종의 Flag이다

 

대상 리소스가 사용중일 때

• Lock이 해제될 때 까지 기다린다.(select * from t for update)
• 일정 시간만 기다리다 포기한다.(select * from t for update wait 3)
• 기다리지 않고 작업을 포기한다.(select * from t for update nowait)

DML을 수행할 때 묵시적으로 테이블 Lock을 설정

설정을 통해서 NOWAIT 옵션 지정 가능

 

 

Lock을 푸는 열쇠는 커밋이다

블록킹 : 선행 트랜잭션이 설정한 Lock 때문에 후행 트랜잭션이 작업을 진행하지 못하고 멈춰있는 상태 ⇒ 커밋으로 해소 가능

교착상태 : 두 트랜잭션이 각각 특정 리소스에 Lock을 설정한 상태에서 맞은편 트랜잭션이 Lock을 설정한 리소스에 또 Lock을 설정하려고 진행하는 상황

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

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

IMMEDIATE : 커밋 명령을 받을 때마다 LGWR가 로그 버퍼를 파일에 기록

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

 

 

트랜잭션 동시성 제어

비관적 동시성 제어 : 사용자들이 같은 데이터를 동시에 수정할 것으로 가정, Lock은 첫 번째 사용자가 트랜잭션을 완료하기 전까지 다른 사용자들이 같은 데이터를 수정할 수 없다

 

적립포인트를 계산하는 동안 고객의 실적정보를 변경하면 문제가 될 수 있다

 

비관적 동시성 제어로 데이터 잘못 갱신되는 문제 방지

 

해당 방식을 통해 Lock을 무한정 기다리지 않을 수 있다

 

 

낙관적 동시성 제어 : 사용자들이 같은 데이터를 동시에 수정하지 않을 것으로 가정, 데이터를 읽을 때 Lock을 설정하지 않는다

그렇기 때문에 데이터 갱신 여부를 판단하는 로직이 필요하다

낙관적 동시성 제어에서도 UPDATE 전 for update를 수행함으로 써 Lock에 대한 예외처리를 하면, 다른 트랜잭션이 설정한 Lock을 기다리지 않게 구현 가능

* for update 사용을 두려워하지 말자

 

두 테이블에 대해 로우 Lock 발생

 

주문수량이 있는 테이블에 대해서만 로우 Lock 발생

 

 

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

 

채번 테이블 : 각 테이블 식별자의 단일 컬럼 일련번호 또는 구분 속성별 순번을 채번하기 위해 별도 테이블을 관리하는 방식. 채번 레코드를 읽어서 1을 더한 값으로 변경하고, 그 값을 새로운 레코드를 입력하는 데 사용

장점:

- 범용성이 좋다

- INSERT 과정에 중복 레코드 발생에 대비한 예외(Exception) 처리에 크게 신경쓰지 앟낭도 되므로 채번 함수만 잘 정의하면 편리하게 사용할 수 있다

- INSERT 과정에 결변을 방지할 수 있다

- PK가 복합컬럼일 때도 사용할 수 있다

 

단점:

- 다른 채번방식에 비해 성능이 않 좋다(채번 레코드를 변경하기 위한 로우 Lock 경합 때문)

 

PL/SQL의 자율 트랜잭션 기능을 이용하면 메인 트랜잭션에 영향을 주지 않고 서브 트랜잭션에서 일부 자원만 Lock을 해제할 수 있다

 

자율 트랜잭션으로 선언하면, 그 내부에서 커밋을 수행해도 메인 트랜잭션은 커밋하지 않은 상태로 남는다

 

시퀀스 오브젝트

장점:

- 성능이 빠르다

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

 

시퀀스는 오라클 내부에서 관리하는 채번 테이블이다

따라서 채번 과정에서 Lock이 발생한다

캐시에 의해 빠른 성능을 제공

 

시퀀스 Lock

- 로우 캐시 Lock : 로우 캐시는 공유 캐시의 구성요소이므로 액세스 시 직렬화 필요

- 시퀀스 캐시 Lock : SQ Lock 이라고 부르며, 시퀀스 캐시는 공유 캐시에 위치하기 때문에 Lock 필요

- SV Lock : RAC 환경에서 시퀀스 캐시에 대한 직렬화(ORDER 옵션 사용), 단, 성능상 손실 순환 옵션을 사용하면 최대값에 도달하면 1부터 다시 시작하도록 할 수 있다

 

MAX + 1

최대 값을 구하고 거기에 1을 더해서 사용

장점:

- 시퀀스 또는 채번 테이블을 관리하는 부담이 없다

- 동시 트랜잭션에 의한 충돌이 많지 않으면, 성능이 매우 빠르다

- PK가 복합 컬럼인 경우, 즉 구분 속성별 순천을 채번할 때도 사용할 수 있다

단점:

- 레코드 중복에 대비한 세밀한 예외처리 필요

- 다중 트랜잭션에 의한 동시 채번이 심하면 시퀀스보다 성능이 많이 나빠질 수 있다(레코드 중복에 의한 로우 Lock 때문)

 

 

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

 

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

1. 다중 트랜잭션에 의한 동시 채번이 많지 않으면 아무거나 사용해도 된다. 다만, 채번 테이블이나 시퀀스 오브젝트 관리 부담을 고려하면 MAX + 1 을 선택하는게 좋다

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

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

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

 

 

시퀀스 보다 좋은 솔루션

한 개 이상의 구분 속성과 함께 뒤쪽에 순번 대시 입력일시 를 두는 방식으로 PK 구조 설계

⇒ 채번의 과정에서 생기는 Lock 이슈를 거의 해소할 수 있다

중복 가능성이 매우 희박하지만 예외처리는 필요하다

 

인덱스 블록 경합

INSERT 성능이 너무 빠른 경우 문제, Right Growing 인덱스에서 가장 흔히 볼 수 있다

인덱스는 키순으로 정렬된 상태를 유지하며 값이 입력된다

따라서 순차적으로 값이 증가하는 단일컬럼 인덱스를 Right Growing 인덱스라고 부르는데, 이 인덱스는 입력하는 값이 달라도 같은 블록을 갱신하려는 프로세스 간 버퍼 Lock 경합이 발생할 수 있다

구분 속성의 값 종류 개수가 적을 수록 경합도 심하다

일반적인 해소 방법은 인덱스를 해시 파티셔닝 하는 것

해시 파티셔닝하면 값이 순차적으로 증가하더라도 해시 함수에 의해 리턴 값이 결정 되므로 경합을 줄일 수 있다

인덱스를 리버스 키 인덱스로 전환하는 방법도 고려될 수 있다

 

글로벌 시퀀스 + 세션 시퀀스

세션시퀀스는 INSERT를 수행할 때마다 호출된다

글로번 시퀀스와 세션 시퀀스를 조합해서 사용하면 프로세스가 서로 다른 리프 블록에 값을 입력하므로 인덱스 경합이 발생하지 않는다

 

Scaleable 시퀀스(18C ^)

시퀀스를 생성할 때 SCALE 또는 SCALE EXTEND 옵션을 지정하면 사용할 수 있다

Scaleable 시퀀스에서 nextval을 호출하면, 인스턴스 번호, 세션 ID, 시퀀스 번호를 조합한 번호를 반환한다

EXTEND 생략 시 VAL3의 리딩 제로 없는 숫자를 반환

EXTEND : 0001, 0002, 0003

no EXTEND : 1, 2, 3