[SQL] 튜닝 - DML 튜닝🔥

DML 튜닝 

DML

• INSERT
• UPDATE
• DELETE

DML 성능에 영향을 미치는 요소

• 인덱스
• 무결성 제약
• 조건절
• 서브쿼리
• Redo 로깅
• Undo 로깅
• Lock
• 커밋

인덱스와 DML 성능

테이블에 레코드를 입력하면, 인덱스에도 입력해야 한다.

테이블은 Freelist(중간에 데이터 입력이 가능한 빈 공간)를 통해 입력할 블록을 할당받는다.

Insert, Update, Delete 중 Update의 부담이 제일 크다. ( Update = Delete + Insert )

 

핵심 트랜잭션 테이블에서 인덱스가 하나라도 줄면 TPS는 그만큼 향상된다.

 

무결성 제약과 DML 성능

• 개체 무결성 = PK
• 참조 무결성 = FK
• 도메인 무결성 = Check
• 사용자 정의 무결성

PK, FK는 중복체크를 해야해서 부담이 상대적으로 많은 편이다.

제약조건이 걸릴수록 성능은 저하된다.

느릴 경우 제약조건을 풀어주는 것이 좋다. (대신 프로그램에서 관리)

 

Redo 로깅과 DML 성능

Redo 로그의 용도

1. Database Recovery
   물리적으로 디스크가 깨지는 등의 Media Fail이 발생 시 데이터베이스를 복구하기위해 사용
2. Cache Recovery
   트랜잭션 데이터 유실에 대비하기 위함
3. Fast Commit
   메모리상의 버퍼블록에만 기록된 채 아직 디스크에 기록되지 않았지만 Redo로그를 믿고 빠르게 커밋을 완료한다는 의미

Undo 로깅과 DML 성능

 

Undo의 용도

1. Transaction Rollback
   트랜잭션에 의한 변경사항을 최종 커밋하지 않고 롤백하고자 할 때
2. Transaction Recovery
   시스템이 셧다운 된 시점에 아직 커밋되지 않았던 트랜잭션들을 모두 롤백해야 할 때
3. Repeatable Read
   일관된 읽기

예전에는 Rollback이었지만 오라클 9i 부터 Undo라는 용어를 사용

MVCC 모델

 

• 오라클은 시스템에서 마지막 커밋이 발생한 시점 정보를 SCN( System Commit Number ) 이라는 글로벌 변수값으로 관리한다.
• Consistent 모드는 쿼리 SCN과 블록 SCN을 비교함으로써 쿼리 수행 도중 블록이 변경됐는지를 확인하면서 읽는 방식이다.
• 블록 SCN이 쿼리 SCN보다 더 큰 블록을 만나면 복사본 블록을 만들고 Undo 데이터를 적용해서 쿼리 시작 시점으로 되돌려 읽는다.

 

Transaction의 속성 - ACID

• 원자성 (Atomicity) - 나눌 수 없는 하나의 작업으로 다뤄져야 한다. 
• 일관성 (Consistency) - Tx 수행 전과 후가 일관된 상태를 유지해야 한다.
• 격리성 (Isolation) - 각 Tx는 독립적으로 수행되어야 한다. (방해x)
• 지속성 (Durability) - 성공한 Tx의 결과는 유지되어야 한다.

Tx의 Isolation level

1. READ UNCOMMITED - 커밋되지 않은 데이터도 읽기 가능
2. READ COMMITED - 커밋된 데이터만 읽기 가능
3. REPEATABLE READ - Tx가 시작된 이휴 변경은 무시됨
4. SERIALIZABLE - 한번에 하나의 Tx만 독립적으로 수행

 

Transaction 데이터 저장 과정

1. DML문을 실행하면 Redo 로그버퍼에 변경사항을 기록한다.
2. 버퍼블록에서 데이터를 변경한다. 버퍼캐시에서 블록을 찾지 못하면, 데이터파일에서 읽는 작업부터 한다.
3. 커밋한다.
4. LGWR 프로세스가 Redo 로그버퍼 내용을 로그파일에 일괄 저장한다.
5. DBWR 프로세스가 변경된 버퍼블록들은 데이터파일에 일괄 저장한다.

 

Lock과 DML 성능

Lock을 필요이상으로 길게 걸면 DML 성능은 느려진다.

반대로 너무 짧게 걸면 데이터 품질이 낮아진다.

중간점을 찾아야한다.

---

Direct Path I/O

Direct Path I/O

일반적인 블록 I/O와 다르게 버퍼캐시를 거치지 않고 바로 디스크로 가는 것이다.

 

Direct Path I/O가 작동하는 경우

1. 병렬 쿼리로 Full Scan을 수행할 때
2. 병렬 DML을 수행할 때
3. Direct Path Insert를 수행할 때
4. Temp 세그먼트 블록들을 읽고 쓸 때
5. direct 옵션을 지정하고 export를 수행할 때
6. nocache 옵션을 지정한 LOB 컬럼을 읽을 때

---

파티션을 활용한 DML 튜닝

 

파티션

테이블 또는 인덱스 데이터를 특정 컬럼 값에 따라 별도 세그먼트에 나눠서 저장하는 것

 

Range 파티션

주로 날짜 컬럼을 기준으로 파티셔닝하는 가장 기초적인 방식

해시 파티션

파티션 키 값을 해시함수에 입력해서 반환받은 값이 같은 데이터를 같은 세그먼트에 저장하는 방식

고객ID처럼 변별력이 좋고 데이터 분포가 고른 컬럼을 파티션 기준으로 선정해야 효과적임

리스트파티션

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

 

---

Lock과 트랜잭션 동시성 제어

오라클 Lock

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

다양한 종류의 Lock을 사용한다.

 

이 중 래치는 SGA에 공유된 각종 자료구조를 보호하기 위해 사용하며,

버퍼 Lock은 버퍼 블록에 대한 액세스를 직렬화하기 위해 사용한다.

 

DML 로우 Lock

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

어떤 DBMS든지 DML 로우 Lock은 배타적 모드를 사용하므로 UPDATE, DELETE 할 수 없다.

 

INSERT에 대한 로우 Lock 경합은 Unique 인덱스가 있는 상황에서 두 트랜잭션이 같은 값을 입력하려고 할 때 블로킹이 발생한다.

두 트랜잭션이 서로 다른 값을 입력하거나, Unique 인덱스가 아예 없으면 로우 Lock 경합은 발생하지 않는다.

DML 테이블 Lock

오라클은 DML 로우 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
• RX : row exclusive
• S : share
• SRX : share row exclusive
• X : exclusive

 

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

1. Lock이 해제될 때까지 기다린다.
2. 일정 시간만 기다리다 포기한다.
3. 기다리지 않고 작업을 포기한다.

Lock을 푸는 열쇠, 커밋

오라클은 데이터를 읽을 때 Lock을 사용하지 않으므로 다른 DBMS에 비해 상대적으로 Lock경합이 적게 발생한다.

 

 

트랜잭션 동시성 제어

비관적 동시성 제어

Lock을 먼저 획득한 후에 작업

/* select문에 for update를 사용하면 고객 레코드에 Lock을 설정하므로 데이터가 잘못 갱신되는 문제를 방지할 수 있다. */
select 적립포인트, 방문횟수, 최근방문일시, 구매실적
from   고객
where  고객번호 = :cust_num for update

낙관적 동시성 제어

Lock을 확인하지 않고 작업 (만약 Lock이 걸렸으면 기다린다.)

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

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

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

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

충돌만 없으면 낙관적이 더 좋다.

 

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

Insert, Update, Delete, Merge 중 가장 중요하고 튜닝 요소가 많은 것 -> Insert

 

3가지 채번 방식

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

채번 테이블

일련번호 또는 순번을 채번하기 위해 별도 테이블을 관리하는 방식

• 범용성이 좋다.
• Insert 과정 중 중복 레코드 발생에 대비한 예외처리에 크게 신경 안써도 된다.
• Insert 과정에 결번을 방지할 수 있다.
• PK가 복합컬럼일 때도 사용할 수 있다.

단점 : 성능이 안좋다.

 

시퀀스 오브젝트

채번 테이블과 마찬가지로 중복 레코드에 대비한 예외처리에 신경 안써도 된다.

장점 : 캐시 사이즈를 적절히 설정한다면 성능이 가장 빠르다.

단점 : PK가 단일컬럼일 때만 사용 가능하다.

 

MAX + 1 조회

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

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

 

 

장점 
시퀀스 또는 별도의 채번테이블을 관리하는 부담이 없다.
동시 트랜잭션에 의한 충돌이 많지 않으면 성능이 매우 빠르다.

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

 

단점

레코드 중복에 대비한 세밀한 예외처리가 필요하다.

다중 트랜잭션에 의한 동시 채번이 심하면 시퀀스보다 성능이 많이 나빠질 수 있다.