[친절한SQL 6장] 기본 DML 튜닝

6.1 기본 DML 튜닝

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

1. 인덱스
2. 무결성 제약
3. 조건절
4. 서브쿼리
5. Redo 로깅
6. Undo 로깅
7. Lock
8. 커밋

ex) 100만개 테이블 A 인덱스 유무

-. 인덱스 없을때 : 4초

-. 인덱스 2개일때 : 38초

-> 인덱스 두개의 영향력이 이 정도로 크다.

 

ex) 제약조건 + 인덱스 유무

Redo 로깅과 DML 성능

Redo 로그의 세 가지 목적

1. Database Recovery
2. Cache Recovery (Instance Recovery 시 roll forward 단계)
3. Fast Commit

Undo 용도

1. Transaction Rollback
2. Transaction Recovery (Instance Recovery시 rollback단계)
3. Read Cnsistency

※ MVCC(Muti-Version Concurrency Control) 모델

[DATABASE] MVCC

 

트랜잭션 데이터 저장 과정

 

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

6.1.2 데이터베이스 Call과 성능

1.  Parse Call : SQL 파싱과 최적화를 수행하는 단계다. SQL과 실행계획을 라이브러리 캐시에서 찾으면, 최적화 단계는 생략할 수 있다.
2. Execute Call : 말 그대로 SQL을 실행하는 단계다. DML은 이 단계에서 모든 과정이 끝나지만, SELECT 문은 Fetch 단계를 거친다.
3. Fetch Call : 데이터를 읽어서 사용자에게 결과집합을 전송하는 과정을 SELECT 문에만 나타난다. 전송할 데이터가 많을 때는 Fetch Call이 여러 번 발생한다.

6.1.3 Arrary Processing 활용

-. Example 

-> Insert를 건건히 하지 않고 한번에 하는 Bulk insert
-> 건바이 건으로 Insert보다 (Commit까지하면 더 느려짐) 속도차이가 많이남

[ORACLE] BULK INSERT TEST

 

 

6.1.4 인덱스 및 제약 해제를 통한 대량 DML 튜닝

-. 인덱스와 무결성 제약 조건은 DML 성능에 큰 영향을 끼친다.

Table	PK, Constraint 유무	1000만건 insert
create table TEST_A	O	1분 20초
create table TEST_A	X	5초

 

 

6.2 Direct Path I/O 활용

6.2.1 Direct Path I/O

-. 오라클은 버퍼캐시를 경유하지 않고 곧바로 데이터 블록을 읽고 쓸 수 있는 Direct Path I/O 기능을 제공한다. 

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

 

6.2.2 Direct Path Insert

※ 일반 INSERT가 느린 이유

1. 데이터를 입력할 수 있는 블록을 Freelist에서 찾는다. 테이블 HWM(High-Water-Mark) 아래쪽에 있는 블록 중 데이터 입력이 가능한 블록을 목록으로 관리하는데, 이를 Freelist락 한다.
2. Freelist에서 할당 받은 블록을 버퍼캐시에서 찾는다. 
3. 버퍼캐시에 없으면, 데이터 파일에서 읽어 버퍼캐시에 적재한다. 
4. Insert 내용을 Undo 세그먿트에 기록한다.
5. Insert 내용을 Redo 로그에 기록한다.

※ Direct Patch 방식을 사용하면, 대량 데이터를 일반적인 Insert 보다 훨씬 더 빠르게 입력 가능ㄷ

1. Insert .. Select  문에 append 힌트 사용
2. parallel 힌트를 이용해 병렬모드로 Insert
3. direct 옵션을 지정하고 SQL Loader 로 데이터 적재
4. CTAS문 수행

-> 빠른 이유

1. Freelist를 참조하지 않고HWM 바깥 영역에 데이터를 순차적으로 입력한다.
2. 블록을 버퍼캐시에서 탐색하지 않는다.
3. 버퍼캐시에 적재하지 않고, 데이터파일에 젝접 기록한다.
4. Undo 로깅을 안한다.
5. Redo 로깅을 안 하게 할 수 있다. -> 데이블을 Nologging 모드로 전환 하면 된다.

 

※ Update나 Delete는 Direct Path Write 불가

alter session set enable parallel dml;

/*+ parallel(c 4)*/  병렬처리로 유도 

 

 

6.3 파티션을 활용한 DML 튜닝

6.3.1 테이블 파티션

-. 관리적 측면 : 파티션 단위 백업, 추가, 삭제, 변경 -> 가용성 향상

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

 

6.3.2 인덱스 파티션

-. 로컬 파티션 인덱스 (Local Partitioned Index)

-> Partition table 과 1:1 매칭

-. 글로벌 파티션 인덱스 (Global Partitioned Index)

-> Partition table과 N:1 매칭

-. 비파티션 인덱스 (Non-Partitioned Index)

 

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

거래 Table : 10억건
거래_X2 : 상태코드 + 거래일자

1. 임시테이블 생성
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 거래일자 < '20140101';

3. 임시 테이블에 원본 테이블과 같은 구조로 인덱스를 생성한다. 
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 

6. nologging으로 작업했다고 다시 logging으로 변경

 

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

비파티션 테이블일 때

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

1. 테이블을 nologging 모드로 전환
2. 인덱스를 Unuable 상태로 전환
3. Direct Path Insert 방식으로 대량 데이터를 입력
4. nologging 모드로 인데스 재생성
5. nologging 모드로 작업했다면 다시 logging모드로 전환

6.4 Lock과 트랜잭션 동시성 제어

6.4.1 오라클 Lock

[ORACLE] LOCK 구조