SQL튜닝이론 - DML 성능에 영향을 미치는 요소 무결성 제약 / REDO / UNDO / COMMIT

SQL튜닝이론 - DML 성능에 영향을 미치는 요소 무결성 제약  REDO  UNDO  COMMIT

 

 

DML (Data Manipulation Language)는 데이터베이스 사용자가 저장된 데이터를 실질적으로 처리하는 데 사용하는 언어를 말합니다. 데이터베이스 사용자와 데이터베이스 관리 시스템 간의 인터페이스를 역할을 합니다. DML의 성능에 영향을 미치는 요소를 튜닝하여 DML 성능을 끌어올릴 수 있습니다. DML 성능에 영향을 미치는 요소는 다음과 같습니다.

 

- 인덱스
- 무결성 제약
- 조건절
- 서브쿼리
- REDO 로깅
- UNDO 로깅
- LOCK
- COMMIT

INDEX가 DML의 성능에 미치는 영향

인덱스가 생성된 테이블에 레코드를 입력하면, 인덱스에도 레코드를 입력해야 합니다. 테이블은 FREELIST를 통해 입력할 블록을 할당받지만, 인덱스는 정렬된 자료구조이므로 인덱스 블록의 수직적 탐색을 통해 입력할 블록을 찾아야 합니다. 이는 DML 성능에 영향을 줍니다.

 

FREE LIST

테이블마다 데이터 입력이 가능한 여유공간이 있는 블록 목록을 말합니다.

 

레코드를 삭제할 때도 마찬가지입니다. 삭제할 레코드를 인덱스 블록의 수직적 탐색을 통해 찾아야합니다.

 

레코드를 업데이트 할 때는 변경된 칼럼을 참조하는 인덱스만 찾아서 변경하빈다. 한 건의 변경마다 인덱스에서는 두 개의 오퍼레이션이 발생합니다. 인덱스는 정렬된 자료구조 이므로 레코드를 삭제하고 다시 정렬하는 과정을 거쳐야 하기 때문입니다.

 

인덱스의 수가 DML 성능에 미치는 영향이 매우 큰 만큼, 인덱스를 함부로 생성해선 안됩니다. 인덱스의 수와 초당 트렌젝션 처리 속도(TPS)는 반비례합니다.

 

무결성 제약이 DML 성능에 미치는 영향

 

데이터베이스에 논리적으로 합당한 자료만 저장되게 하는 데이터 무결성 규칙은 네 가지가 있습니다

 

- 개체 무결성
- 참조 무결성
- 도메인 무결성
- 사용자 정의 무결성(업무 제약 조건)

 

DBMS 에서 제공하는 PK, FK, CHECK, NOT UNLL과 같은 제약(CONSTRAINT)을 설정하면 데이터 무결성을 유지할 수 있습니다. 무결성 제약은 DML 성능에 영항을 미치는데 그중 PK, FK 제약은 나머지 제약에 비해 더 큰 영향을 미칩니다. 실제 데이터를 조회하는 과정이 동반되기 때문입니다. 예를 들어 PK제약이 있는 테이블을 삭제할 때 그렇지 않은 테이블에 비해 훨씬 많은 시간이 소요됩니다.

 

조건절이 DML 성능에 미치는 영향

 

테이블을 UPDATE와 DELETE 할때도 SELECT의 성능을 높이는 조건절 튜닝 규칙이 동일하게 적용됩니다.

 

서브쿼리가 DML 성능에 미치는 영향

 

테이블을 UPDATE와 DELETE 할때도 SELECT의 성능을 높이는 서브쿼리 튜닝 규칙이 동일하게 적용됩니다.

 

REDO 로깅

 

오라클은 데이터파일과 컨트롤 파일에 가해지는 모든 변경사항을 REDO 로그에 기록합니다. REDO 로그는 트렌잭션 데이터가 유실됐을 때, 트랜잭션을 재현함으로써 유실 이전 상태로 복구하는 데 사용됩니다. DML 은 반드시 REDO 로그의 생성을 동반하므로 REDO 로그는 DML 성능에 영향을 줍니다. 이에 대용량 데이터를 입력하거나 삭제, 업데이트하는 경우 REDO 로깅을 생략하도록 DBMS를 조작해야 합니다.

 

REDO 로그의 용도

REDO 로그는 아래 세 가지 목적을 위해 사용됩니다.

DATABASE RECOVERY

물리적으로 디스크가 깨지는 등 MEDIA FAIL 발생 시 데이터베이스 복구를 위해 사용됩니다. 온라인 REDO 로그를 백업해 둔 ARCHIVED REDO 로그를 사용합니다.

CACHE RECOVERY (INSTARNCE RECOVERY 시 ROLL FORWARD 단계)

캐시 데이터를 저장하는 버퍼캐시는 휘발성입니다. 인스턴스가 비정상적으로 종료되면 캐싱된 데이터는 모두 유실됩니니다. 이런 트랜젝션 데이터의 유실을 막기 위해 REDO 로그에 저장합니다.

FAST COMMIT

REDO 로그는 FAST COMMIT을 위해 사용합니다. 트렌젝션으로 인해 변경된 메모리 버퍼 블록을 디스크상에 반영하는 작업은 렌덤 액세스 방식으로 이루어지므로 매우 느립니다. 반면 로그는 APPEND 방식으로 REDO 로그에 기록되므로 상대적으로 빠릅니다. 따라서 트랜잭션에 의한 변경사항을 우선 APPEND 방식으로 기록하고 변경된 메모리 버퍼블록과 데이터블록 간의 동기화는 일괄처리(BATCH) 방식으로 수행됩니다.

 

UNDO 로깅

 

UNDO는 흔히 알려진 ROLLBACK의 개념입니다. REDO 로그는 과거 상태를 현재로 불러오는 역할을 하고 UNDO 로그는 트랜잭션을 롤백함으로써 현재상태를 과거의 상태로 되돌리는 데 사용됩니다. UNDO에는 변경된 블록을 이전 상태로 되돌리는데 필요한 정보를 로깅합니다. DML 수행은 항상 UNDO를 동반하므로 성능에 영향을 주지만 UNDO 로깅은 사용자가 임의로 조작할 수 없습니다.

 

UNDO의 용도와 MVCC 모델

 

DML로 데이터를 입력, 수정, 삭제할 때마다 UNDO 세그먼트에 기록을 남깁니다. 트랜잭션을 커밋하는 순간, 기록된 순서대로 삭제되어 그 공간은 다른 트랜잭션이 재사용할 수 있는 상태가 됩니다. UNDO에 기록된 데이터는 다음과 같은 용도로 사용됩니다.

 

첫째, 트랜잭션에 의한 변경사항을 커밋하지 않고 롤백하고자 할 때 사요됩니다.

 

둘째, INSTANCE CRACH 발생 후 REDO를 이용해 ROLL FORWARD 단계가 완료되면 최종 커밋되지 않은 변경사항까지 모두 복구되는데, 커밋되지 않은 변경사항의 롤백을 위해 사용됩니다.

 

마지막으로,  읽기 일관성을 위해 사용합니다. 읽기 일관성은 특정 데이터가 변경 후 최종 커밋되기 전에 다른 트랜잭션에서 그 데이터를 읽으면 변경 전 상태로 조회되는 성질을 말합니다.

 

MCVV 모델

 

MVCC 모델은 오라클이 데이터를 읽는 방식을 의미합니다. 하나는 CURRENT 모드, 또 다른 하나는 CONSISTENT 모드입니다.

 

CURRENT 모드는 디스크에서 변경되어 캐시로 적재된 원본블록을 현재 상태 그대로 읽는 방식을 말합니다.

 

CONSISTENT 모드는 쿼리가 시작된 후 트랜잭션에 의해 변경된 블록을 만나면 원본 블록으로부터 복사복 블록을 만들고,  복사본에 UNDO 데이터를 적용함으로써 쿼리가 시작된 시점의 상태의 블록으로 되돌려서 읽는 방식을 말합니다.

 

오라클은 시스템에서 마지막 커밋이 발생한 시점의 정보를 SCN이라는 GLOBAL 변수 값으로 관리합니다. 또한, 각 블록이 마지막으로 변경된 시점을 관리하기 위해 모든 브록 헤더에 SCN을 기록하는데 이를 블록 SCN이라고 합니다. 그리고 모든 쿼리는 GLOBAL 변수인 SCN 값을 먼저 확인하고 읽기 작업을 시작하는데 이를 쿼리 SCN 이라고 합니다.

 

CONSISTENT 모드는 쿼리 SCN과 블록 SCN을 비교해 쿼리 수행 도중 블록이 변경됐는지 확인하면서 데이터를 읽습니다. 데이터를 읽다가 블록 SCN이 쿼리 SCN 보다 더 큰 블록을 만나면 복사본을 만들고 UNDO 데이터를 적용합니다. UNDO 데이터가 다른 트랜잭션에 의해 재사용됨으로써 쿼리 시작 시점으로 되돌리는 작업에 실패하면 SNAPSHOT TOO OLD 에러가 발생합니다.

 

오라클에서 SELECT 문은 항상 CONSISTENT 모드로 데이터를 읽습니다. 반면 DML 문은 CONSISTENT 모드로 대상 레코드를 찾고 CURRENT 모드로 추가/변경/삭제합니다. 즉, CONSISTENT 모드로 DML문이 시작된 시점에 존재한 데이터 블록을 찾고 다시 CURRENT 모드로 원본 블록을 갱신합니다. 데이터의 변경은 항상 원본블록에만 발생합니다.

 

데이터 LOCK이 DML 성능에 미치는 영향

 

LOCK을 필요 이상으로 자주, 길게 사용하거나 레벨을 높일수록 DML 성능은 느려집니다. 반대의 경우 데이터 품질이 나빠집니다. 성능과 데이터 품질은 언제나 트레이드오프 관계입니다. 두 가지 모두를 잡기 위해선 동시성 제어가 중요합니다. 동시성 제어란 동시에 실행되는 트랜잭션 수를 최대화하면서 트랜잭션의 데이터 변경 과정에서 데이터 무결성을 유지하기 위해 노력하는 것을 말합니다.

 

COMMIT이 DML 성능에 미치는 영향

 

커밋은 DML과 별개의 프로세스지만, DML을 최종적으로 데이터에 적용하려면 커밋까지 완료해야 합니다. 특히 특정 데이터에 대한 DML이 다른 트랜잭션의 DML로 인해 LOCK 된 경우, 커밋은 DML 성능과 직결됩니다. LOCK을 푸는 열쇠가 커밋이기 때문입니다. 커밋의 내부 메커니즘은 다음과 같습니다.

DB 버퍼캐시

 

DB 인스턴스의 서버 프로세스는 버퍼캐시를 통해 데이터를 읽고 씁니다. 버퍼캐시에서 변경된 블록을 모아 주기적으로 데이터 파일에 일괄기록하는 작업은 DBWR(DATABASE WRITER) 프로세스가 처리합니다.

 

REDO 로그버퍼

 

DBWR 프로세스가 데이터파일에 변경 블록을 반영할 때까지 변경사항은 버퍼캐시에 저장됩니다. 버퍼캐시에 가한 변경사항은 REDO 로그에도 기록해 둡니다. 휘발성인 버퍼캐시의 데이터가 유실되더라도 REDO 로그를 이용해 언제든 복구할 수 있습니다. 하지만 REDO로그는 파일이기 때문에 디스크 I/O가 발생합니다. REDO 로깅의 성능 문제를 해결하기 위해 오라클은 별도의 로그버퍼를 이용합니다. REDO 로그 파일에 기록하기 전에 미리 로그버퍼에 기록합니다. 로그버퍼에 기록된 내용은 나중에 LGWR(LOG WRITER) 프로세스가 REDO 로그 파일에 일괄기록합니다.

 

트랜젝션 데이터의 저장괴정

하나의 트랜잭션이 데이터를 변경하고 커밋하는 과정, 그리고 변경된 블록을 데이터파일에 최종적으로 기록하는 과정은 다음과 같습니다.

 

1. DML 문에 의해 발생한 변경사항을 REDO LOG BUFFER에 기록합니다.
2. 버퍼블록에서 DML에 맞게 데이터를 변경합니다. 물론, 버퍼캐시에서 블록을 찾지 못하면, 데이터 파일에서 직접 읽는 작업부터 합니다.
3. 커밋합니다.
4. LGWR 프로세스가 REDO LOG BUFFER의 내용을 REDO 로그파일에 일괄저장합니다.
5. DBWR 프로세스가 변경된 버퍼블록을 데이터파일에 일괄 저장합니다.

 

오라클은 데이터를 변경하기 전에 항상 로그부터 기록합니다. 버퍼블록에 데이터를 변경하기전에 REDO 로그 버퍼를 먼저 기록하고, DBWR 프로그세스가 데이터파일에 버퍼블록의 변경사항을 기록하기 전에 LGWR 프로세스가 REDO LOG BUFFER에 기록하는 이유입니다. 이를 WRITE AHEAD LOGGING이라고 부릅니다. 커밋 시점에는 REDO 로그 버퍼의 내용을 로그파일에 기록하기 때문에 메모리 버퍼캐시의 REDO는 항상 가능합니다 이를 LOG FORCE AT COMMIT이라고 합니다. 이를 통해 트랜잭션의 영속성을 보장합니다.

 

COMMIT

데이터베이스 트랜잭션을 문서작업에 비유하면, 커밋은 문서 작업 도중 '저장' 버튼을 누르는 것과 같습니다. 서버 프로세스가 그때까지 했던 작업을 디스크에 기록하라는 명령입니다. 저장을 완료할 때까지 서버 프로세스는 다음 작업을 진행할 수 없습니다. REDO 로그버퍼에 기록된 내용을 로그버퍼파일에 기록하도록 LGWR 프로세스에 신호를 보낸 후 이 작업이 완료돼야 다음 작업을 진행할 수 있는 동기(SYNC) 방식입니다. LGWR 프로세스는 디스크 I/O 작업이므로 커밋은 생각보다 느립니다. 트랜잭션을 길게 정의함으로써 오랫동안 커밋하지 않는 것도 문제지만 너무 자주 커밋하는 것도 성능에 영향을 줍니다. 오랫동안 커밋하지 않은 채 데이터를 계속 갱신하면 UNDO 공간이 부족해져 시스템 장애를 유발할 수 있습니다. 커밋요청이 빈번하면 프로그램 성능이 매우 느려집니다.