기본 DML 튜닝 (1)

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

• 인덱스 - 가장 큰 영향
• 무결성 제약
• 조건절
• 서브쿼리
• Redo 로깅 - DB에 반영하는 것보다 로깅이 빠르기 때문에 사용
• Undo 로깅  - DB에 반영하는 것보다 로깅이 빠르기 때문에 사용
• Lock
• 커밋

---

인덱스와 DML 성능

• 테이블에 레코드를 입력하면, 인덱스에도 입력해야 한다
• 인덱스에 입력하는 과정이 더 복잡하므로 DML 성능에 미치는 영향도 더 크다
  • 수직적 탐색을 통해 입력할 블록을 찾아야 하기 때문에
• Delete 할 때도 마찬가지이다
  • 테이블에서 레코드 하나를 삭제하면, 인덱스 레코드를 모두 찾아서 삭제해줘야한다
• 인덱스 개수가 DML 성능에 미치는 영향이 매우 큰 만큼, 인덱스 설계를 잘해야한다

---

무결성 제약과 DML 성능

 

무결성 규칙 4가지

• 개체 무결성 (Entity Integrity)
• 참조 무결성 (Referential Integrity)
• 도메인 무결성 (Domain Integrity)
• 사용자 정의 무결성 (또는 업무 제약 조건)

---

Redo 로깅과 DML 성능

로깅은 OLTP를 위한 것이다

• 오라클은 데이터파일과 컨트롤 파일에 가해지는 모든 변경사항을 Redo 로그에 기록한다
• Redo로그
  • 트랜잭션 데이터가 유실됐을 때, 트랜잭션을 재현함으로써 유실 이전 상태로 복구하는 데 사용된다
  • 트랜잭션을 재현하는 데 필요한 정보를 로깅한다
• DML을 수행할 때마다 Redo로그를 생성해야 하므로 Redo 로깅은 DML 성능에 영향을 미친다
• Insert 작업에 대해 Redo 로깅 생략 기능을 제공하는 이유가 여기 있다 
  • 대량 Insert의 경우는 Redo 로깅 기능 off 가능하다

 

Redo 로그의 사용 목적 3가지

• Database Recovery
  
  • Media Fail 발생 시 데이터베이스를 복구하기 위해 사용한다
• Cache Recovery
  • 인스턴스가 비정상적으로 종료되면, 그때까지의 작업내용을 모두 잃게 된다
  • 이러한 트랜잭션 데이터 유실에 대비하기 위해 Redo 로그를 남긴다
• Fast Commit 
  • 로그는 Append 방식으로 기록하므로 상대적으로 빠르다
  • 로그파일에 기록하고, 나중에 배치(Batch) 방식으로 일괄 동기화 한다
  • 로그에만 커밋, DB에는 X

---

Undo 로깅과 DML 성능

Undo

• 트랜잭션을 롤백함으로써 현재를 과거 상태로 되돌리는 데 사용한다
• 변경된 블록을 이전 상태로 되돌리는데 필요한 정보를 로깅한다
• DML을 수행할 때마다 Undo를 생성해야 하므로 Undo 로깅은 DML 성능에 영향을 미친다
  • 오라클은 Undo를 안 남기는 방법을 제공 X

Undo에 기록한 데이터 사용목적 3가지

• Transaction Rollback
  • 트랜잭션에 의한 변경사항을 최종 커밋하지 않고 롤백하고자 할 때 Undo 데이터를 이용
• Transaction Recovery
  • 인스턴스 Crash 발생 후, Redo를 이용해 roll forward 단계가 완료되면 최종 커밋되지 않은 변경사항까지 모두 복구된다
• Read Consistency(읽기 일관성)

 

MVCC(Multi-Version Concurrency Control) 모델

오라클은 데이터를 두가지 모드로 읽는다

• Current 모드
  • 디스크에서 캐시로 적재된 원본(Current) 블록을 현재 상태 그대로 읽는 방식을 말한다
• Consistent 모드
  • 원본블록으로부터 복사본(CR Copy) 블록을 만들고, 거기에 Undo 데이터를 적용함으로써 쿼리가 '시작된'시점으로 되돌려서 읽는 방식을 말한다
  • 오라클은 시스템에서 마지막 커밋이 발생한 시점정보를 'SCN (System Commit Number)' 이라는 Global 변수값으로 관리한다
    • 이 값은 기본적으로 각 트랜잭션이 커밋할 때마다 1씩 증가한다 → 커밋이 몇번인지 확인 가능

 

• Select문은(몇몇 예외 케이스를 제외하곤) 항상 Consistent 모드로 데이터를 읽는다 → 복사본이라 읽기 lock 이 없어 속도가 빠르다
• DML 문은 Consistent 모드로 대상 레코드를 찾고, Current 모드로 추가/변경/삭제한다

---

Lock과 DML 성능

• 여러 사람이 원활하게 쓰려면 lock을 많이 걸면 안된다
• Lock은 DML 성능에 매우 크고 직접적인 영향을 미친다
• Lock을 필요 이상으로 자주, 길게 사용하거나 레벨을 높일수록 DML 성능은 느려진다

네 가지 트랜잭션 격리성 레벨 (Transaction Isolation Level)이 있다
1. Read Unicommitted
2. Read Committed
3. Repeateable Read
4. Serializable
Read Committed 가 기본이며, 숫자가 올라갈수록 Lock을 오래 유지한다
오라클은 Read Committed와 Serializable만 지원한다

• 그렇다고 Lock을 너무 적게, 짧게, 사용하거나 필요한 레벨 이하로 낮추면 데이터 품질이 나빠진다

---

커밋과 DML 성능

커밋이 가벼운 작업이 아닌 이유를 커밋의 내부 메커니즘을 통해 알아보자

1. DB 버퍼캐시
   • 버퍼캐시에서 변경된 블록(Dirty 블록)을 모아 주기적으로 데이터파일에 일괄 기록하는 작업은 DBWR(Database Writer) 프로세스가 맡는다
2. Redo 로그버퍼
   • 버퍼캐시는 휘발성이다. Redo 로그도 파일이라, 디스크 I/O는 느리다. Redo 로깅 성능 문제를 해결하기 위해 오라클은 로그버퍼를 이용한다. 로그버퍼에 기록한 내용은 나중에 LGWR(Log Writer) 프로세스가 Redo 로그파일에 일괄(Batch) 기록한다
3. 트랜잭션 데이터 저장 과정

한 트랜잭션이 데이터를 변경하고 커밋하는 과정, 그리고 변경된 블록을 데이터파일에 기록하는 과정은 다음과 같다

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

---

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

• 대량 DML 시 index를 drop or disable 시킨다
• 대량 DML 이 끝나면 다시 index을 생성한다

---

6.1.6 Merge문 활용

• Merge문은 Source 테이블 기준으로 Target 테이블과 Left Outer 방식으로 조인한다
  • 조인에 성공하면 Update
  • 실패하면 Insert

Optional Clauses

• 아래와 같이 Update와 Insert를 선택적으로 처리할 수도 있다

merge into customer t using customer_delta s on (t.cust_id = s.cust_id)
when matched then update
set t.cust_num = s.cust_nm ...;

merge into customer t using customer_delta s on (t.cust_id = s.cust_id)
when not matched then insert
(cust_id, cust_nm, email) values 
(s.cust_id, s.cust_nm, s.email);

--when matched update, when not matched insert문을 Upsert 문이라고 한다
--Upsert는 중복되는 값이 있다면 Update를 하고 중복되는 값이 없다면 Insert를 하는 쿼리이다