DML 튜닝

DML 튜닝

DML 성능에 영향을 미치는 요소
1. 인덱스
2. 무결성 제약
3. 조건절
4. 서브쿼리
5. Redo 로깅
6. Undo 로깅
7. Lock
8. 커밋

 

Redo Log 목적
1. Database Recovery (물리적인 디스크 오류)
2. Cache Recovery (정전 등 비정상적인 인스턴스 종료)
3. Fast Commit 
 사용자의 갱신내용이 메모리상의 버퍼블록에만 기록된 채 아직 디스크에 기록되지 않았지만 Redo 로그를
 믿고 빠르게 커밋을 완료한다는 의미에서 이를 'Fast Commit'이라고 부른다.
 
 Redo
 트랜잭션을 재현함으로써 과거를 현재 상태로 되돌리는 데 사용
 
 Undo
 트랜잭션을 롤백함으로써 현재를 과거 상태로 되돌리는 데 사용
 
 Undo Log 목적
 1. Transaction Rollback
 2. Transaction Recovery
 3. Read Consistency
 
 Current 모드
 디스크에서 캐시로 적재된 원본 블록을 현재 상태 그대로 읽는 방식
 
 Consistent 모드
 쿼리가 시작된 이후에 다른 트랜잭션에 의해 변경된 블록을 만나면 원본 블록으로부터 복사본 블록을 만들고
 거기에 Undo 데이터를 적용함으로써 쿼리가 '시작된 시점'으로 되돌려서 읽는 방식
 
 Undo 데이터가 다른 트랜잭션에 의해 재사용됨으로써 쿼리 시작 시점으로 되돌리는 작업에 실패할 때
 Snapshot too old(ORA-01555) 에러가 발생한다.

 

#커밋
모든 DBMS가 Fast Commit을 구현하고 있다.

1. DB 버퍼캐시
버퍼캐시에서 변경된 블록을 모아 주기적으로 데이터파일에 일괄 기록하는 작업은 DBWR 프로세스가 맡는다.

2. Redo 로그버퍼
버퍼캐시는 휘발성이므로 Dirty 블록들을 데이터파일에 반영할 때까지 불안한 상태라고 생각할 수 있다.
하지만, 버퍼캐시에 가한 변경사항을 Redo 로그에도 기록한다. 
Redo 로그도 파일이다. Append 방식으로 기록하더라도 디스크 I/O는 느리다.
그래서 Redo 로그버퍼를 이용한다. 로그파일에 기록하기 전에 로그버퍼에 기록했다가 LGWR 프로세스가
Redo 로그 파일에 일괄 기록한다.

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

 

#데이터베이스 Call
1. Parse Call : SQL 파싱과 최적화를 수행하는 단계다.
2. Excuse Call : SQL을 실행하는 단계다.
3. Fetch Call : 데이터를 읽어서 사용자에게 결과집합을 전송하는 과정으로 SELECT문에서만 나타난다.
  전송할 데이터가 많을 때는 Fetch Call이 여러 번 발생한다.
  
User Call : WAS 서버에서 호출하는 Call
Recursive Call : DBMS 내부에서 발생하는 Call

#커밋과 성능
100만건을 User Call 호출을 하면 네트워크 경유한 호출이라 속도가 매우 느릴 것이다.
100만건을 Recursive Call 호출을 하면 네트워크를 경유하지 않아 그나마 속도가 빠를 것이다.

Commit을 100만건 건건이 하는 경우 성능도 매우 느려지고 트랜잭션 원자성에도 문제가 생긴다.
반대로 중간에 한번도 커밋을 하지 않으면 Undo 공간부족으로 인해 시스템에 부작용을 초래한다.
트랜잭션의 원자성을 위해 반드시 그렇게 처리해야 한다면 Undo 공간을 늘려야 하지만, 그렇지 않다면
적당한 주기로 커밋하는 방안을 고려해야 한다.

간단한 예제로 테스트시
User Call : 218초
Recursive Call
1) 매건 Commit : 1분
2) 한번 Commit : 29초
Recursive Call을 절차적으로 변경  : 3.99초
User Call을 10000번에 한 번 Batch Call 하는 경우 : 11.813초

 

#인덱스 및 제약해제를 통한 대량 DML 튜닝
1000만건, PK, INDEX
1. PK + INDEX : 1분 19초
2. PK 제약과 INDEX 해제 : 5초 84
 truncate table target;
 alter table target modify constraint target_pk disable drop index;
 alter index target_idx1 unusable;
 alter session set skip_unusable_indexs = true; //default
 
 작업후
 alter table target modify constraint target_pk enavle NOVALIDATE;
 alter index target_idx1 rebuild;

 

Direct Path I/O 활용

온라인 트랜잭션의 경우는 쿼리를 반복적으로 사용하기 때문에 버퍼캐시가 성능 향상에 도움을 준다.
반면, 정보계 시스템(DW/OLAP 등)이나 배치 프로그램에서 사용하는 SQL은 주로 대량 데이터를 처리하기 때문에 버퍼캐시를 경유하는 I/O 메커니즘이라 오히려 성능을 떨어뜨릴 수 있다. 그래서 Direct Path I/O를 제공하고 있다.

Direct Path I/O를 사용하는 경우
1. 병렬 쿼리로 Full Scan을 사용할 때
   parallel, parallel_index 힌트를 사용
   예) select /*++ full(t) parallel(t 4)*/ t.* from table t
2. 병렬 DML을 수행할 때
3. Direct Path Insert를 수행할 때
4. Temp 세그먼트 블록들을 읽고 쓸 때
5. Direct 옵션을 지정하고 export를 수행할 때
6. nocache 옵션을 지정한 LOB 컬럼을 읽을 때

1~3번이 가장 중요하고 활용도가 높다. 대용량의 데이터의 경우 속도가 수십배 빨라질 수 있다. 
그 이유는 버퍼캐시를 탐색하지 않고, 디스크로부터 버퍼캐시에 적재하는 부담도 없으니 빠른 것이다.
Order by, Group by, 해지 조인, 소트 머지 조인 등을 처리할때는 힌트로 지정한 병렬도보다 
두 배 많은 프로세스를 사용한다.

 

# 일반적인 Insert가 느린이유
1. 데이터를 입력할 수 있는 블록을 Freelist에서 찾는다.
  Freelist : 블록 중 데이터 입력이 가능한 블록 목록을 관리하는 곳
2. Freelist에서 할당받은 블록을 버퍼캐시에서 찾는다.
3. 버퍼캐시에 없으면, 데이터 파일에서 읽어 버퍼캐시에 적재한다.
4. INSERT 내용을 Undo 세그먼트에 기록한다.
5. INSERT 내용을 Redo 로그에 기록한다.

#Direct Path Insert 방식으로 입력하는 방법
1. INSERT ---- SELECT 문에 append 힌트 사용
2. parallel 힌트를 이용해 병렬 모드로 INSERT
3. direct 옵션을 지정하고 SQL*Loader(sqlldr)로 데이터 적재
4. CTAS(create table ---- as select)문 수행

#Direct Path Insert 방식이 빠른 이유
1. Freelist를 참조하지 않고 HWM 바깥 영역에 데이터를 순차적으로 입력한다.
2. 블록을 버퍼캐시에서 탐색하지 않는다.
3. 버퍼캐시에 적재하지 않고, 데이터파일에 직접 기록한다.
4. Undo 로깅을 안한다
5. Redo 로깅을 안 하게 할 수 있다. 테이블을 nologging 모드로 전환한 상태에서 Direct path Insert 한다.
 예) alter table t NOLOGGING;
 
 #Direct Path Insert를 사용할 때 주의할 점
 1. 이 방식을 사용하면 성능은 비교할 수 없이 빨라지지만 TM Lock이 걸린다는 사실이다.
따라서 커밋하기 전까지 다른 트랜잭선은 해당 테이블에 DML을 수행하지 못한다.
트랜잭선이 빈번한 주간에 이 옵션을 사용하는 것은 절대 금물이다.
2. Frelllist를 조회하지 않고 HWM 바깥 영역에 입력하므로 테이블에 여유 공간이 있어도 재활용하지 않는다.

 

#병렬 DML
Insert는 append 힌트를 이용해 Direct Path Write 방식으로 유도할 수 있지만
Update, Delete는 기본적으로 Direct Path Write가 불가능하다.
유일한 방법은 병렬 DML로 처리하는 것이다.
예) alter session enable parallel dml;
예) insert /*+ parallel(c 4) */ into table c 
     select /*+ full(o) parallel(o 4)*/ from table2 o
    update /*+ full(c) parallel(c 4)*/ table c set ...
    delete  /*+ full(c) parallel(c 4)*/ from table c ...
    
힌트는 주었으나 병렬 dml을 활성화 하지 않으면 대상 레코드를 찾는 작업은 병렬로 진행하지만
추가/변경/삭제는 단일 프로세스로 처리되어 병목이 생긴다.

12c 부터는 enable_parrllel_dml 힌트도 지원한다
예) insert /*+ enable_parallel_dml parallel(c 4)*/ into table c
     select /*+ full(o) parallel(o 4)*/ from table2 o 
    update /*+ enable_parallel_dml full(c), parallel(c 4)*/ table c set ...
    delete  /*+ enable_parallel_dml full(c) parallel(c 4)*/ from table c ...

병렬 DML도 Direct Path Write 방식을 사용하므로 테이테 입력/수정/삭제할 때 TM Lock이 걸린다.

병렬 DML이 작동하는지 확인하는 방법
UPDATE/DELETE/INSERT가 'PX COORDINATOR' 아래쪽에 나타나면 UPDATE를 각 병렬 프로세스가 처리한다.

파티션을 활용한 DML 튜닝

파티션 테이블 주요 개념
#테이블 파티션
파티셔닝은 테이블 또는 인덱스 데이터를 특정 컬럼 값에 따라 별도 세그먼트에 나눠서 저장하는 것을 말한다.

#파티션이 필요한 이유
관리적 측면 : 파티션 단위 백업, 추가, 삭제, 변경 --> 가용성 향상
성능적 측면 : 파티션 단위 조회 및 DML, 경합 또는 부하 분산

#Range 파티션
주로 날짜 컬럼을 기준으로 파티셔닝한다. 

#리스트 파티션
사용자가 정의한 그룹핑 기준에 따라 데이터를 분할 저장하는 방식
예) partition by list(지역) (
     partitioon p1 values ('서울')
     ,partition p2 values ('경기','인천')
     ,partition p3 values (default)
     );
     
#파티션 인덱스
1. 로컬 파티션 인덱스 : 파티션별 인덱스
  create index table_idx1 on table LOCAL;
2. 글로벌 파티션 인덱스 : 파티션을 테이블과 다르게 구성
  create index table_idx2 on table(column1, colume2) GLOBAL
   partition by range(column1)(
    partition p1 values less than (10000)
    , partition p2 values less than (MAXVALUE)
   );
3. 비파티션 인덱스 : 말 그대로 파티셔닝하지 않은 인덱스다
 create index table_idx3 on table(column2, column3);
 비파티션 인덱스는 테이블 파티션 구성을 변경하는 순간 Unusable 상태로 바뀌므로 곧바로
 인덱스를 재생성해 줘야 한다.

 

#파티션을 활용한 대량 UPDATE 튜닝
인덱스를 DROP 하거나 Unusable 상태로 변경하고서 작업하는 방법을 사용하기 위한 손익분기점은 5% 정도이다.
수정되는 데이터가 총데이터의 5% 이상이면 작업후에 인덱스를 재생성 하는 것이 유리하다.

1. 임시 테이블을 nologging 모드로 생성
create temp_table t
nologging
as
select * from org where 1=2

2. 데이터를 임시 테이블에 입력
insert /*+ append */ into temp_table t
select * from org where ....

3. 임시 테이블에 원본 테이블과 같은 구조로 인덱스 생성
create unique index table_pk_idx on temp_table (column1, column2) nologging;
create index table_idx1 on temp_table (column3, column4) nologging;

4. 임시 테이블과 p22 exchange
alter table org 
exchange partition p22 with table temp_table
including indexes without validation;

5. 임시 테이블 Drop 한다
drop table temp_table;

6. 파티션을 logging 모드로 전환
alter table org modify partition p22 logging;
alter index table_pk_idx partition p22 logging;
alter index table_idx1 partition p22 logging;

 

#대용량 파티션 삭제
alter table org drop partition p22;
alter table org drop partition for('p22');

 

#파티션을 활용한 대량 insert 튜닝
비파티션 테이블 일때
1. 테이블을 nologging 모드로 전환
alter table target)t nologging;

2. 인덱스를 Unusable 상태로 전환
alter index target_t_idx1 unusable;

3. Direct Path Insert 방식으로 대량 데이터를 입력
insert /*+ append */ into target_t
select * from source_t

4. nologging 모드로 인덱스를 재생성한다.
alter index target_t_idx1 reguild nologging;

5. logging 모드로 전환한다.
alter table target_t logging;
alter index target_t_idx1 logging;

파티션 테이블 일 때
1. 테이블 파티션을 nologging 모드로 전환한다.
alter table target_t modify parition p22 nologging;

2. 인덱스 파티션을 Unusable 상태로 전환
alter index target_t_idx1 modify partition p22 unusable;

3. Direct Path Insert 방식으로 대량 데이터를 입력
insert /*+ append */ into target_t
select * from source_t ...

4. nologging 모드로 인덱스를 재생성한다.
alter index target_t_idx1 rebuild partition p22 nologging;

5. 작업 파티션을 logging 모드로 전환한다.
alter table target_t modify partition p22 logging;
alter index target_t_idx1 partition p22 logging;

 

Lock과 트랜잭션 동시성 제어

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

#DML 로우 Lock
두 개의 동시 트랜잭션이 같은 로우를 변경하는 것을 방지한다.
하나의 로우를 변경하려면 로우 Lock을 먼저 설정해야 한다.
Insert에 대한 로우 Lock 경합은 Unique 인덱스가 있을 때만 발생한다. 즉, Unique 인덱스가 있는 상황에서
두 트랜잭션이 같은 값을 입력하려고 할 때, 블로킹이 발생한다.
MVCC 모델을 사용하는 오라클은 SELECT 문에 로우 Lock을 사용하지 않는다. 변경이 진행 중인 로우를 읽을 때도 
Lock이 풀릴 때까지 기다리지 않고 복사본을 만들어서 읽는다. 

#DML 테이블 Lock
오라클은 DML 로우 Lock을 설정하기에 앞서 테이블 Lock을 먼저 설정한다. 현재 트랜잭션이 갱신 중인
테이블 구조를 다른 트랜잭션이 변경하지 못하게 막기 위해서다. 테이블 Lock을 'TM Lock'이라 부른다.

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

 

채번 방식에 따른 INSERT 성능 비교
1. 채번 테이블
2. 시퀀스 오브젝트
3. MAX + 1 조회

#채번테이블
장점
1. 범용성이 좋다
2. 중복레코드 발생에 대비한 예외 처리에 크게 신경쓰지 않아도 된다.
3. INSERT 과정에 결번을 방지할 수 있다.
4. PK가 복합컬럼일 때도 사용할 수 있다.
단점
1. 성능이 안좋다.
2. LOCK 경합이 발생할 가능성이 높아 INSERT가 아주 많은 테이블에는 사용하기가 어렵다.

#시퀀스 오브젝트
장점
1. 성능이 빠르다.
2. 중복 레코드 발생에 대비한 예외처리에 크게 신경쓰지 않아도 된다.
단점
1. 테이블 별로 시퀀스 오브젝트를 관리하는 부담이 존재한다.
2. 신규 데이터를 입력하는 과정에서 결번이 생길 수 있다. 
 시퀀스 채번이후 롤백하는 경우, 캐쉬에서 삭제되는 경우
 
 #MAX+1
 장점
 1. 시퀀스 또는 별도의 채번 테이블을 관리하는 부담이 없다.
 2. 성능이 빠르다.
 3. PK가 복합컬럼인 경우, 구분 속성별 순번을 채번할 때도 사용할 수 있다.
 단점
 1. 레코드 중복에 대비한 세밀한 예외처리가 필요하다.
 2. 다중 트랜잭션에 의한 동시 채번이 심하면 시퀀스보다 성능이 만히 나빠질 수 있다.
 
 
 1. 다중 트랜잭션에 의한 동시 채번이 많지 않으면, 세가지 방식 모두 괜찮으나 가급적 MAX+1 
 채번 테이블, 시퀀스 오브젝트 관리를 안해도 됨
 2. 다중 트랜잭션에 의한 동시 채번이 많고 PK가 단일컬럼 일련번호라면, 시퀀스 방식
 3. 다중 트랜잭션에 의한 동시 채번이 많고 PK가 구분 속성에 값 종류 개수가 많으면 MAX+1
 4. 다중 트랜잭션에 의한 동시 채번이 많고 PK가 구분 속성게 값 종류 개수가 적으면 
  순환옵션을 가진 시퀀스