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

6.1 기본 DML튜닝

인덱스와 DML성능

• 테이블에 레코드를 입력하면 인덱스에도 입력해야한다.
• 테이블에서 한건 변경할때마다 인덱스에는 두개의 오퍼레이션이 발생
• 인덱스를 하나라도 줄이면 TPS(Tramsaction Per Second)는 향상

---

무결성 제약과 DML성능

• PK, FK제약은 Check, Not Null 제약보다 성능에 더큰 영향을 미친다
• Check, Not Null은 정의한 제약조건을 준수하는지만 확인하면 되지만 PK, FK제약은 실제 데이터를 조회해봐야 하기 때문이다.

---

Redo 로깅과 DML 성능

오라클은 데이터파일과 컨트롤 파일에 가해지는 모든 변경사항을 Redo 로그에 기록한다

Redo 로그의 용도

1. Database Recovery (DB복구)
2. Cache Recovery
3. Fast Commit

• 버퍼캐시는 휘발성이다. 캐시에 저장된 변경사항이 디스크싱에 저장되지 않은 상태에서 비정상적인 종료가 발생할경우 그때까지의 작업내용을 잃게된다
  그러하여 트랜잭션 데이터 유실에 대비하기위해 Redo 로그를 남기게된다
• 사용자의 갱신내용이 메모리상의 버퍼블록에만 기록된채 아직 디스크에 기록되지 않았지만 Redo 로그를 믿고 빠르게 커밋을 완료한다는 의미에서 이를 Fast Commit이라고 부른다.
  커밋정보까지 Redo 로그파일에 안전하게 기록된다면 언제든지 복구될수있다

---

Undo 용도

1. Transaction RollBack
2. Transaction Recovery
3. Read Consistency

MVCC모델

오라클은 데이터를 2가지 모드로 읽는다 (Current 모드, Consistent 모드)

• Current모드는 디스크에서 캐시로 적재된 원본(Current) 블록을 현재상태 그대로 읽는 방식
• Consistent모드는 쿼리가 시작된 이후에 다른 트랜잭션에 의해 변경된 블록을 만나면 원본 블록으로부터 복사본(CR Copy)을 만들고 거기에 Undo(되돌리기) 데이터를 적용함으로써 쿼리가 '시작된 시점'으로 되돌려서 읽는 방식을 말한다
  → 일관된읽기 : 한번 읽기 시작한데이터는 종료될때까지 데이터가 유지되어야한다.

커밋과 DML 성능

• 버퍼캐시 : 버퍼캐시에서 변경된블록(Dirty 블록 : 누가 손대서 블록이 Dirty)
  을 모아 주기적으로 데이터파일에 일괄기록하는작업은 DBWR(Database Writer)가 맡는다.
• Redo 로그버퍼

1. 버퍼캐시는 휘발성이므로 DBWR 프로세스가 Dirty 블록들을 데이터파일에 반영할떄까지 불안한 상태라 할수있다.
하지만 버퍼 캐시에 가한 변경사항을 Redo 로그에도 기록해주었으므로 데이터가 유실되더라도 Redo 로그를 이용해 언제든지 복구할수있다.
2. 로깅 성능 문제를 해결하기위해 오라클은 로그버퍼를 이용한다.
Redo 로그파일에 기록하기전에 먼저 로그버퍼에 기록하는방식이다.
로그버퍼에 기록한 내용은 나중에 LGWR (Log Writer) 프로세스가 Redo 로그파일에 일괄(Batch) 기록된다

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

---

데이터베이스 Call과 성능

User Call은 DBMS 외부로부터 인입되는 Call
Recursive Call은 DBMS 내부에서 발생하는 Call이다.

---

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

→ 제약이 걸려있으면 느려지니까 해제후 데이터추가
1. 제약 비활성화
2. DML 조작
3. 제약 활성화
인덱스 속도가 빨라진다

---

MERGE문 활용

기간계 시스템에서 가져온 신규 트랜잭션 데이터를 반영함으로써 두 시스템간 데이터를 동기화하는 작업

-- 1. 전일 발생한 변경 데이터를 기간계 시스템으로부터 추출
create table customer_delta
as
select * from customer
where	mod_dt >= trunc(sysdate)-1
and 	mod_dt < trunc(sysdate);

-- 2. CUSTOMER_DELTA 테이블을 DW시스템으로 전송(Transportation)
-- 3. DW시스템으로 적재

Target 테이블과 Left Outer 방식으로 조인해서 조인에 성공하면 UPDATE,
실패하면 INSERT한다.
MERGE문을 UPSERT(Update + Insert) 라고 부른다

DELETE 절은 조인에 성공한 데이터를 모두 UPDATE 하고서 그 결과값이 DELETE WHERE 조건절을 만족하면 삭제하는 기능이다.

---

6.2 Direct Path I/O 활용

대량데이터를 처리할때 버퍼캐시를 경유하지않고 곧바로 데이터 블록을 읽고 바로 데이터 블록을 읽고 쓸수 있는 Direct Path I/O 기능을 제공한다.

Direct Path I/O

일반적인 블록 I/O는 읽고자하는 블록을 버퍼캐시에서 찾아보고 찾지못할때만 디스크에서 읽는다. 찾은 버퍼블록에 변경을 가하고 나면 DBWR 프로세스가 변경된 블록 Dirty 블록들을 주기적으로 찾아 데이터파일에 반영해준다.
→ 지연된쓰기

Direct Path Insert

일반적인 Insert보다 Direct Path Insert 방식이 더 빠르다

일반적인 Insert 방식

1. 테이블 HWM (High-WaterMark 데이터가 어디까지 차있는지) 확인후 빈공간을 FreeList(어디가 비었는지 위치를 저장)에서 찾는다.
2. FreeList 에서 할당받은 블록을 버퍼캐시에서 찾는다
3. 버퍼캐시가 없으면 데이터파일에서 읽어 버퍼캐시에 적재
4. INSERT내용을 Undo 세그먼트에 기록
5. INSERT내용을 REDO 로그에 기록

Direct Path Insert 방식

• INSERT ... SELECT 문에 append 힌트
• parallel 힌트를 이용해 병렬 모드로 INSERT (쓰레드 4개 병렬처리)
• direct 옵션을 지정하고 SQL*Loader로 데이터 적재 (대용량데이터 DB Upload)

 

 

 

 

 

 

 

"이 포스팅은 쿠팡 파트너스 활동의 일환으로, 이에 따른 일정액의 수수료를 제공받습니다."