친절한 SQL 튜닝 - 6장 DML 튜닝

6장 DML 튜닝

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

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

인덱스와 DML성능

• 테이블에 레코드를 입력하면 인덱스에도 입력해야 한다.
• 인덱스는 정렬된 자료구조이므로 수직적 탐색을 통해 입력할 블록을 찾아야하므로 DML 성능에 미치는 영향도 더 크다.
• INSERT, DELETE는 테이블에서 레코드를 모두 찾아 삽입, 삭제해 줘야 한다. UPDATE할 때는 변경된 컬럼을 참조하는 인덱스만 찾아서 변경해 주면 된다.
• 간단한 100만개 레코드를 만든 테이블에서 인덱스를 1개 추가 할 때마다 8배가 느려진다. 이를 통해 인덱스 하나라도 줄이면 TPS(Transaction Per Second)는 그만큼 향상된다는 것을 의미한다.

무결성 제약과 DML 성능

• PK, FK 제약은 실제 데이터를 조회해 봐야 하기 때문에 Check, Not Null 제약보다 성능에 더 큰 영향을 미친다.
• 위와 같이 똑같이 100만개 레코드를 INSERT할 때 PK 제약 조건이 없으면 약 3배 빨라진다.
• 소요시간 : PK (O), 일반 인덱스 (O) > PK (O), 일반 인덱스 (X) > PK (X), 일반 인덱스 (X)

Redo 로깅과 DML 성능

• 오라클은 데이터파일과 컨트롤 파일에 가해지는 모든 변경사항을 Redo 로그에 기록한다.
• Redo 로그는 트랜잭션 데이터가 어떤 이유에서건 유실됐을 때, 트랜잭션을 재현함으로써 유실 이전 상태로 복구하는데 사용한다.
• DML을 수행할 때마다 Redo 로그를 생성하므로 Redo 로깅은 DML성능에 영향을 미친다.

-- Redo 로그 사용 목적 세 가지

1. 물리적으로 디스크가 깨지는 등 작업이 발생할 시 데이터베이스를 복구하기 위해 사용
2. 캐시에 저장된 변경사항이 데이터 블록에 기록되지 않은 상태에서 정전 등이 발생해 인스턴스가 비정상적으로 종료되면, 그때까지의 작업 내용을 모두 잃는 것을 대비해 Redo 로그 사용
3. 트랜잭션에 의한 변경사항을 빠른 Append 방식으로 기록하여 빠르게 커밋을 완료한다는 의미에서의 Fast Commit 작업 수행

트랜잭션

-- 트랜잭션 개념 정리

트랜잭션 정의 : 여러 SQL 작업을 하나의 묶음으로 처리하는 하나의 논리적인 작업 단위 ACID 특성을 기반으로 일관된 처리를 보장.

1. Atomicity (원자성)
-- 트랜잭션 내의 작업이 모두 수행되거나, 모두 취소되는 것.
2. Consistency (일관성)
-- 트랜잭션이 성공적으로 완료되어 일관된 상태 유지. 데이터의 무결성이 보장.
-- 특정 제약 조건을 위반되는 DML작업이 발생할 경우, 롤백.
3. Isolation (고립성)
-- 트랜잭션이 독립적으로 수행되어, 동시에 여러 트랜잭션이 수행되어도 다른 트랜잭션의 중간 결과를 볼 수 없습니다.
-- 일반적으로 많은 데이터베이스의 격리 수준인 Read Committed.
4. Durability (지속성)
-- 트랜잭션이 성공적으로 커밋되면 영구적으로 데이터베이스에 반영됩니다.

 

Lock 과 DML성능

• Lock은 필요 이상으로 자주, 길게 사용하거나 레벨을 높일수록 DML성능은 느려진다. 그렇다고 너무 적게, 짧게 사용하거나 레벨 이하로 낮추면 데이터 품질이 낮아진다.
• 동시성 제어를 통해 트랜잭션 수를 최대화하며 데이터 무결성을 유지하기 위해 노력해야 한다.

DB 버퍼캐시

• DB에 접속한 사용자를 대신해 모든 일을 처리하는 서버 프로세스는 버퍼캐시를 통해 데이터를 읽고 쓴다.
• 버퍼캐시에서 변경된 블록을 모아 주기적으로 데이터파일에 일괄 기록하는 작업은 DBWR 프로세스가 진행한다.

Redo 로그 버퍼

• 버퍼캐시는 휘발성이므로 DBWR 프로세스가 Dirty 블록들을 반영할 떄까지 불안한 상태라고 생각하겠지만, 버퍼캐시에 가한 변경사항을 Redo 로그에도 기록하므로 언제든 복구 가능한 강점이 존재한다.

트랜잭션 데이터 저장 과정

1. DML문 실행 시 Redo 로그버퍼에 변경사항을 기록한다.
2. 버퍼블록에서 데이터를 변경한다.
3. 커밋한다.
4. LGWR 프로세스가 Redo 로그버퍼 내용을 로그파일에 일괄 저장한다.
5. DBWR 프로세스가 변경된 버퍼블록들을 데이터파일에 일괄 저장한다.

6.2 Direct Path I/O

• 트랜잭션은 버퍼캐시가 성능 향상에 도움을 주지만, 대용량의 배치에서 사용하는 SQL은 버퍼캐시를 경유함으로써 I/O 메커니즘이 오히려 성능을 떨어뜨릴 수 있다. 그래서 오라클은 버퍼캐시를 경유하지 않고 곧바로 데이터 블록을 읽고 쓸 수 있는 Direct Path I/O 기능을 제공한다.

6.2.1 Direct Path I/O

• 일반적인 블록 I/O는 DB 버퍼캐시를 경유하고, 읽고자 하는 블록을 찾아보고 없을 때만 디스크에서 읽는다.
• 자주 읽는 블록에 대한 반복적인 I/O Call을 줄임으로써 성능을 높이려고 버퍼캐시를 이용하지만, 대량 데이터를 읽고 쓸 때는 오히려 성능에 좋지 않다.
• 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. 데이터를 입력할 수 있는 아래쪽에 있는 블록 중 입력 가능한 블록을 목록으로 관리한다.
2. 목록 중 할당받은 블록을 버퍼캐시에서 찾는다.
3. 버퍼캐시에 없으면, 데이터파일에서 읽어 버퍼캐시에 적재한다.
4. INSERT 내용을 Undo 세그먼트에 기록한다.
5. INSERT 내용을 Redo 로그에 기록한다.

Direct Path Insert 방식

1. 아래쪽에 있는 블록 중 입력 가능한 블록을 참조하지 않고 HWM 바깥 영역에 데이터를 순차적으로 입력한다.
2. 블록을 버퍼캐시에서 탐색하지 않는다.
3. 버퍼캐시에 적재하지 않고, 데이터파일에 직접 기록한다.
4. Undo 로깅을 안 한다.
5. Redo 로깅을 안하게 할 수 있다.

6.4 Lock 트랜잭션 동시성 제어

DML 로우 Lock

• DML 로우 Lock은 두 개의 동시 트랜잭션이 같은 로우를 변경하는 것을 방지한다. 하나의 로우를 변경하려면 로우 Lock을 먼저 설정해야 한다.
• DML 수행전 항상 테이블 Lock 을 먼저 설정하며 밑의 세 가지 옵션을 통해 Lock을 컨트롤 할 수 있다.

1. Lock이 해제될 때 까지 기다린다. (select * from t for update)
2. 일정 시간만 기다리다 포기한다. (select * from t for update wait3)
3. 기다리지 않고 포기한다. (select * from t for update nowait)

블로킹과 교착상태 Lock을 푸는 열쇠, 커밋

• 블러킹 : 선행 트랜잭션의 Lock 때문에 작업을 진행하지 못하고 멈춰있는 상태. 해소하는 방법은 커밋과 롤백뿐이다.
• 교착상태 : 두 트랜잭션이 Lock을 설정한 상태에서 다른 트랜잭션이 같은 리소스에 또 Lock을 설정하려고 진행하는 상황을 말한다. 교착상태가 발생하면, 트랜잭션이 교착상태를 발생시킨 문장 하나만 롤백한다.
• 교착상태는 해소됐지만 블로킹 상태에 놓이므로, 커밋 또는 롤백을 결정해야하며, 프로그램내에서 에러에 대한 예외처리(커밋, 롤백)를 하지 않는다면 무한 대기 상태가 지속된다.

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

 

1. 채번 테이블

• 각 테이블 식별자의 단일컬럼 일련번호 또는 구분 속성별 순번을 채번하기 위해 별도 테이블을 관리하는 방식이다.
• 가장 큰 단점은 로우 Lock 경합으로 인하여 다른 채번 방식에 비해 성능이 안 좋다는 데 있다.
• 동시 INSERT가 많으면 채번 테이블 블록 자체에도 경합이 발생하므로 동시 INSERT가 많으면 이 방식은 사용하기 어렵다.

2. 시퀀스 오브젝트

• 시퀀스는 성능이 빠르다는 큰 장점이 있고 중복 레코드 발생에 크게 신경 쓰지 않아도 된다는 장점이 존재한다.
  • 시퀀스 오브젝트도 오라클 내부에서 관리하는 채번 테이블이므로 똑같이 Lock에 의한 성능 이슈가 생길 수도 있다. 하지만 캐시 사이즈를 적절히 설정함으로써 트랜잭션과 빠른 성능을 유지할 수 있다.

3. MAX + 1 조회

• 시퀀스나 채번 테이블을 관리하는 부담과 동시 트랜잭션에 의한 충돌이 많지 않으면 성능이 2개 중에 가장 빠르다.
• 하지만 레코드 중복에 의한 예외처리와 다중 트랜잭션에 의한 동시 채번이 심하면 성능이 더 나빠질 수 있다.
• 채번 방식에 따른 Lock 경합 요소는 아래와 같이 정리할 수 있다.

1. 채번 테이블이나 시퀀스 오브젝트 관리 부담을 고려한다면 -> MAX + 1
2. 다중 트랜잭션에 의한 동시 채번이 많고 PK가 단일 컬럼이라면 -> 시퀀스
3. 다중 트랜잭션에 의한 동시 채번이 많고 값 종류 수가 많으면 -> MAX + 1