친절한 SQL 튜닝_6장.DML 튜닝_1

 

DML 튜닝

 

6.1. 기본 DML 튜닝

 

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

 

인덱스와 DML 성능

 

테이블에 레코드를 입력하면, 인덱스에도 입력해야 한다. 테이블은 Freelist를 통해 입력할 블록을 할당받지만, 인덱스는 정렬된 자료구조이므로 수직적 탐색을 통해 입력할 블록을 찾아야 한다. 인덱스에 입력하는 과정이 더 복잡하므로 DML 성능에 미치는 영향도 크다.

 

DELETE 할 때도 마찬가지다. 테이블에서 레코드 하나를 삭제하면, 인덱스 레코드를 모두 찾아서 삭제해 줘야 한다. UPDATE 할 때는 변경된 컬럼을 참조하는 인덱스만 찾아서 변경해 주면 된다. 그 대신, 테이블에서 한 건 변경할 때마다 인덱스에는 두 개 오퍼레이션이 발생한다. 인덱스는 정렬된 자료구조이기 때문이다. 예를 들어, ‘A’를 ‘K’로 변경하면 저장 위치도 달라지므로 삭제 후 삽입하는 방식으로 처리한다.

 

인덱스 개수가 DML 성능에 미치는 영향이 매우 큰 만큼, 인덱스 설계에 심혈을 기울여야 한다. 핵심 트랜잭션 테이블에서 인덱스 하나라도 줄이면 TPS(Transaction Per Second)는 그만큼 향상된다.

 

 

 

무결성 제약과 DML 성능

 

데이터베이스에 논리적으로 의미 있는 자료만 저장되게 하는 데이터 무결성 규칙으로는 아래 네 가지가 있다.

 

- 개체 무결성(Entity Integrity)

 

- 참조 무결성(Referential Integrity)

 

- 도메인 무결성(Domain Integrity)

 

- 사용자 정의 무결성(또는 업무 제약 조건)

 

이들 규칙을 애플리케이션으로 구현할 수도 있지만, DBMS에서 PK, FK, Check, Not Null 같은 제약(Constraint)을 설정하면 더 완벽하게 데이터 무결성을 지켜낼 수 있다.

 

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

 

 

 

조건절과 DML 성능

 

SELECT 문과 실행계획이 다르지 않으므로 이들 DML 문에는 2장과 3장에서 학습한 인덱스 튜닝 원리를 그대로 적용할 수 있다.

 

 

 

서브쿼리와 DML 성능

 

SELECT 문과 실행계획이 다르지 않으므로 이들 DML 문에는 4장에서 학습한 조인 튜닝 원리를 그대로 적용할 수 있다.

 

 

 

Redo 로깅과 DML 성능

 

오라클은 데이터파일 과 컨트롤 파일에 가해지는 모든 변경사항을 Redo 로그에 기록한다. Redo 로그는 트랜잭션 데이터가 어떤 이유에서건 유실됐을 때, 트랜잭션을 재현함으로써 유실 이전 상태로 복구하는데 사용된다.

 

DML을 수행할 때마다 Redo 로그를 생성해야 하므로 Redo 로깅은 DML 성능에 영향을 미친다. INSERT 작업에 대해 Redo 로깅 생략 기능을 제공하는 이유가 여기에 있다.

 

 

 

Undo 로깅과 DML 성능

 

과거에는 롤백(Rollback)이라는 용어를 주로 사용했지만, 9i부터 오라클은 Undo라는 용어를 사용하고 있다. Redo와 Undo의 차이점을 잘 보여준다. Redo는 트랜잭션을 재현함으로써 과거를 현재 상태로 되돌리는 데 사용하고 있고, Undo는 트랜잭션을 롤백함으로써 현재를 과거 상태로 되돌리는 데 사용한다.

 

따라서 Redo에는 트랜잭션을 재현하는 데 필요한 정보를 로깅하고, Undo에는 변경된 블록을 이전 상태로 되돌리는 데 필요한 정보를 로깅하낟.

 

DML을 수행할 때마다 Undo를 생성해야 하므로 Undo 로깅은 DML 성능에 영향을 미친다. 그렇다고 Undo를 안 남길 순 없다. 오라클은 그런 방법을 아예 제공하지 않는다.

 

 

 

Lock과 DML 성능

 

Lock과 DML 성능에 매우 크고 직접적인 영향을 미친다. Lock을 필요 이상으로 자주, 길게 사용하거나 레벨을 높일수록 DML 성능은 느려진다. 그렇다고 Lock을 너무 적게, 짧게 사용하거나 필요한 레벨 이하로 낮추면 데이터 품질이 나빠진다. 성능과 데이터 품질이 모두 중요한데, 이 둘은 트레이드 오프(Trade-off) 관계여서 어렵다. 두 마리 토끼를 다 잡으려면 매우 세심한 동시성 제어가 필요하다.

 

동시성 제어(Concurrency Control)란, 동시에 실행되는 트랜잭션 수를 최대화(고성능)하면서도 입력, 수정, 삭제, 검색 시 데이터 무결성을 유지(고품질)하기 위해 노력하는 것을 말한다.

 

 

 

커밋과 DML 성능

 

커밋은 DML과 별개로 실행하지만, DML을 끝내려면 커밋까지 완료해야 하므로 서로 밀접한 관련이 있다. 특히 DML이 Lock에 의해 블로킹(Blocking)된 경우, 커밋은 DML 성능과 직결된다. DML을 완료할 수 있게 Lock을 푸는 열쇠가 바로 커밋이기 때문이다.

 

모든 DBMS가 Fast Commit을 구현하고 있다. 구현방식은 서로 다르지만, 갱신한 데이터가 아무리 많아도 커밋만큼은 빠르게 처리한다는 점은 같다. Fast Commit의 도움으로 커밋을 순간적으로 처리하긴 하지만, 커밋은 결코 가벼운 작업이 아니다.

 

 

 

(1) DB 버퍼캐시

 

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

 

 

 

(2) Redo 로그버퍼

 

버퍼캐시는 휘발성이므로 DBWR 프로세스가 Dirty 블록들을 데이터파일에 반영할 때까지 불안한 상태라고 생각할 수 있다. 하지만, 버퍼캐시에 가한 변경사항을 Redo 로그에도 기록해 두었으므로 안심해도 된다. 버퍼캐시 데이터가 유실되더라도 Redo 로그를 이용해 언제든 복구할 수 있기 때문이다.

 

그런데 Redo 로그도 파일이다. Append 방식으로 기록하더라도 디스크 I/O는 느리다. Redo 로깅 성능 문제를 해결하기 위해 오라클은 로그버퍼를 이용한다. Redo 로그 파일에 기록하기 전에 먼저 로그버퍼에 기록하는 방식이다. 로그버퍼에 기록한 내용은 나중에 LGWR(Log Writer) 프로세스가 Redo 로그 파일에 일괄(Batch) 기록한다.

 

 

 

(3) 트랜잭션 데이터 저장 과정

 

1. DML 문을 실행하면 Redo 로그버퍼에 변경사항을 기록한다.

 

2. 버퍼블록에서 데이터를 변경(레코드 추가/수정/삭제)한다. 물론, 버퍼캐시에서 블록을 찾지 못하면, 데이터파일에서 읽는 작업부터 한다.

 

3. 커밋한다.

 

4. LGWR 프로세스가 Redo 로그버퍼 내용을 로그파일에 일괄 저장한다.

 

5. DBWR 프로세스가 변경된 버퍼블록들은 데이터파일에 일괄 저장한다.

 

 

 

(4) ‘커밋 = 저장 버튼’

 

데이터베이스 트랜잭션을 문서 작업에 비유하면, 커밋은 문서 작업 도중에 ‘저장’ 버튼을 누르는 것과 같다. 서버 프로세스가 그때까지 했던 작업을 디스크에 기록하라는 명령어인 셈이다. 저장을 완료할 때까지 서버 프로세스는 다음 작업을 진행할 수 없다. Redo 로그버퍼에 기록된 내용을 디스크에 기록하도록 LGWR 프로세스에 신호를 보낸 후 작업을 완료했다는 신호를 받아야 다음 작업을 진행할 수 있다. Sync 방식이다. LGWR 프로세스가 Redo 로그를 기록하는 작업은 디스크 I/O 작업이다. 커밋은 그래서 생각보다 느리다.

 

 

 

6.1.2 데이터베이스 Call과 성능

 

데이터베이스 Call

 

SQL은 아래 세 단계로 나누어 실행한다.

 

- Parse Call : SQL 파싱과 최적화를 수행하는 단계다. SQL과 실행계획을 라이브러리 캐시에서 찾으면, 최적화 단계는 생략할 수 있다.

 

- Execute Call : 말 그대로 SQL을 실행하는 단계다. DML은 이 단계에서 모든 과정이 끝나지만, SELECT 문은 Fetch 단계를 거친다.

 

- Fetch Call : 데이터를 읽어서 사용자에게 결과집합을 전송하는 과정으로 SELECT 문에서만 나타난다. 전송할 데이터가 많을 때는 Fetch Call이 여러 번 발생한다.

 

 

 

Call이 어디서 발생하느냐에 따라 User Call과 Recursive Call로 나눌 수 있다.

 

User Call은 네트워크를 경유해 DBMS 외부로부터 인입되는 Call이다. 최종 사용자(User)는 맨 왼쪽 클라이언트 단에 위치한다. 하지만, DBMS 입장에서 사용자는 WAS(또는 AP 서버)다. 3-Tier 아키텍처에서 User Call은 WAS(또는 AP 서버)서버에서 발생하는 Call이다.

 

Recursive Call은 DBMS 내부에서 발생하는 Call이다. SQL 파싱과 최적화 과정에서 발생하는 데이터 딕셔너리 조회, PL/SQL로 작성한 사용자 정의 함수/프로시저/트리거에 내장된 SQL을 실행할 때 발생하는 Call이 여기에 해당한다.

 

User Call이든 Recursive Call이든, SQL을 실행할 때마다 Parse, Execute, Fetch Call 단계를 거친다. 데이터베이스 Call이 많으면 성능은 느릴 수밖에 없다. 특히, 네트워크를 경유하는 User Call이 성능에 미치는 영향은 매우 크다.

 

 

 

6.1.3 Array Processing 활용

 

실무에서 절차적 프로그램을 One SQL로 구현하는 일은 절대 쉽지 않다. 복잡한 업무 로직을 포함하는 경우가 많기 때문이다. 그럴 때 Array Processing 기능을 활용하면 One SQL로 구현하지 않고도 Call 부하를 획기적으로 줄일 수 있다.

 

 

 

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

 

앞서 설명했듯 인덱스와 무결성 제약 조건은 DML 성능에 큰 영향을 끼친다. 그렇다고 온라인 트랜잭션 처리 시스템에서 이들 기능을 해제할 수 없다. 반면, 동시 트랜잭션 없이 대량 데이터를 적재하는 배치(Batch) 프로그램에서는 이들 기능을 해제함으로써 큰 성능개선 효과를 얻을 수 있다.

 

PK 제약과 인덱스 해제 1 - PK 제약에 Unique 인덱스를 사용한 경우

 

PK 제약과 인덱스 해제 2 - PK 제약에 Non-Unique 인덱스를 사용한 경우

 

 

 

6.1.5 수정가능 조인 뷰

 

전통적인 방식의 UPDATE

 

수정가능 조인 뷰

 

‘조인 뷰’는 FROM 절에 두 개 이상 테이블을 가진 뷰를 가리키며, ‘수정가능 조인 뷰(Updatable/Modifiable Join View)’는 말 그대로 입력, 수정, 삭제가 허용되는 조인 뷰를 말한다. 단, 1쪽 집합과 조인하는 M쪽 집합에만 입력, 수정, 삭제가 허용된다.

 

키 보존 테이블이란?

 

‘키 보존 테이블’이란’, 조인된 결과집합을 통해서도 중복 값 없이 Unique 하게 식별이 가능한 테이블을 말한다. Unique한 1쪽 집합과 조인되는 테이블이어야 조인된 결과집합을 통한 식별이 가능하다.

 

단적으로 말해 ‘키 보존 테이블’이란, 뷰에 rowid를 제공하는 테이블을 말한다.

 

ORA-01779

 

 

 

6.1.6 MERGE 문 활용

 

DW에서 가장 흔히 발생하는 오퍼레이션은 기간계 시스템에서 가져온 신규 트랜잭션 데이터를 반영함으로써 두 시스템 간 데이터를 동기화하는 작업이다.

 

예를 들어, 고객(customer) 테이블에 발생한 변경분 데이터를 DW에 반영하는 프로세스는 다음과 같다. 이 중에서 3번 데이터 적재 작업을 효과적으로 지원하기 위해 오라클 9i에서 MERGE 문이 도입됐다.

 

1. 전일 발생한 변경 데이터를 기간계 시스템으로부터 추출(Extraction)

 

CREATE 
 TABLE CUSTOMER_DELTA
    AS
SELECT * 
  FROM CUSTOMER
 WHERE MOD_DT >= TRUNC(SYSDATE) - 1
   AND MOD_DT < TRUNC(SYSDATE);

 

 

 

2. CUSTOMER_DELTA 테이블을 DW 시스템으로 전송(Transportaion)

 

 

3. DW 시스템으로 적재(Loading)

 

MERGE INTO CUSTOMER T USING CUSTOMER_DELTA S ON (T.CUST_ID = S.CUST_ID)
 WHEN MATCHED THEN UPDATE SET T.CUST_NM = S.CUST_NM, T.EMAIL = S.EMAIL, …
 WHEN NOT MATCHED THEN INSERT (CUST_ID, CUST_NM, EMAIL, TEL_NO, REGION, ADDR, REG_DT) VALUES (S.CUST_ID, S.CUST_NM, S.EMAIL, S.TEL_NO, S.REGION, S.ADDR, S.REG_DT);

 

 

 

MERGE 문은 Source 테이블 기준으로 Target 테이블과 Left Outer 방식으로 조인해서 성공하면 UPDATE, 실패하면 INSERT 한다. MERGE 문을 UPSERT(=UPDATE + INSERT)라고도 부르는 이유다. 위 MERGE 문에서 Source는 Customer_Delta 테이블이고, Target은 Customer 테이블이다.

 

출처 : 친절한 SQL 튜닝 - 조시형 지음