DML 튜닝

1. 기본 DML튜닝

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

인덱스

- 인덱스 개수가 DML 성능에 미치는 영향이 매우 큰 만큼, 인덱스 설계에 심혈을 기울여야 한다.

무결성 제약

- PK 제약과 기타 무결성제약을 모두 제거한 상태로 테이블에 INSERT하면 훨씬 적은 시간이 소요된다.

조건절

서브쿼리

REDO 로깅

- REDO 로그는 트랜잭션 데이터가 어떤 이유에서건 유실됐을 때, 트랜잭션을 재현함으로써 유실 이전 상태로 복구하는데 사용된다.

- DML을 수행할 때마다 REDO 로그를 생성해야 하므로 REDO 로깅은 DML 성능에 영향을 미친다.

- REDO 로그의 용도: 사용자의 갱신내용이 메모리상의 버퍼블록에만 기록된 채 아직 디스크에 기록되지 않았지만 REDO 로그를 믿고 빠르게 커밋을 완료한다. 

UNDO 로깅

- UNDO 로그는 트랜잭션을 롤백함으로써 현재를 과거 상태로 돌리는 데 사용한다.

- DML을 수행할 때마다 UNDO 로그를 생성해야 하므로 UNDO 로깅은 DML 성능에 영향을 미친다.

- Instance Crash:

- Read Consistency: 사용자들에게 가장 최근 커밋된 데이터를 보여주는 것이다. 즉, 다른 사용자들이 변경중인 데이터를 볼 수 없게 하는 것이다.

- MVCC 모델(p.402): 따로 정리할 것

LOCK

- LOCK을 높이면 DML 성능이 느려지고, 낮추면 데이터 품질이 나빠진다. 성능과 데이터 품질 두가지를 모두 만족시키고 싶다면 세심한 동시성 제어가 필요하다.

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

커밋

- DML이 Lock에 의해 블로킹된 경우, 커밋은 DML 성능과 직결된다. DML을 완료할 수 있게 Lock을 푸는 열쇠가 바로 커밋이기 때문이다.

- 커밋 매커니즘(p.405): 따로 정리할 것

 

2) 데이터베이스 Call과 성능

데이터베이스 Call

- Call이 많으면 성능은 느릴 수 밖에 없다.

[SQL Call 3단계]

- Parse: SQL 파싱과 최적화를 수행하는 단계

- Execute: SQL을 실행하는 단계

- Fetch: 데이터를 읽어서 사용자에게 결과집합을 전송하는 과정 (SELECT문에서만 나타남)

[Call 발생]

- User call: 네트워크를 경유해 DBMS 외부로부터 들어오는 Call

- Recursive Call: DBMS 내부에서 발생하는 Call

절차적 루프 처리

One SQL의 중요성

- 업무 로직이 복잡하면 절차적으로 처리할 수밖에 없지만, 그렇지 않다면 가급적 One SQL로 구현하려고 노력해야 한다. (Insert Into Select, 수정가능 조인 뷰, Merge문 등)

 

3) Array Processing 활용

- Array Processing을 활용하면 One SQL을 구현하지 않고도 Call 부하를 획기적으로 줄일 수 있다.

 

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

- 온라인 트랜잭션 처리 시스템에서 이들 기능을 해제할 순 없다. 반면, 동시 트랜잭션 없이 대량 데이터를 적재하는 배치 프로그램에서는 이들 기능을 해제함으로써 큰 성능개선 효과를 얻을 수 있다.

 

5) 수정가능 조인 뷰

전통적인 방식의 UPDATE

- 만약 한 달 이내에 거래를 발생시킨 고객이 많아 UPDATE 발생량이 많다면, 아래와 같이 쿼리를 변경하는 것을 고려할 수 있다. 하지만 모든 고객 레코드에 LOCK이 걸리는 것은 물론, 이전과 같은 값으로 갱신되는 비중이 높을수록 Redo 로그 발생량이 증가해 오히려 비효율적일 수 있다. (p.423)

- 모든 고객 레코드에 LOCK이 걸리는 것은 UPDATE문에 조건절이 없기 때문으로 이해되나, 

- 이전과 같은 값으로 갱신되는 비중이 높을수록 Redo 로그 발생량이 증가한다는 부분은 이해되지 않는다. 

UPDATE 고객 C
   SET (
        최종거래일시
      , 최근거래횟수
      , 최근거래금액
   ) = (
        SELECT NVL(MAX(거래일시), C.최종거래일시)
             , DECODE(COUNT(*), 0, C.최근거래횟수, COUNT(*))
             , NVL(SUM(거래금액), C.최근거래금액)
          FROM 거래
         WHERE 고객번호 = C.고객번호
           AND 거래일시 >= TRUNC(ADD_MONTHS(SYSDATE, -1))
   )

수정가능 조인 뷰

- 수정가능 조인 뷰: 입력, 수정, 삭제가 허용되는 두 개 이상의 테이블을 가진 뷰

- 단, 1쪽 집합과 조인하는 M쪽 집합(1:M 관계)에만 입력, 수정, 삭제가 허용된다.

키 보존 테이블

- 뷰에 rowid를 제공하는 테이블

 

6) MERGE 문 활용

- DW(Data Warehouse): 데이터 창고. 사용자의 의사결정을 돕기 위해, 기간계 시스템에 축적된 데이터를 공통의 형식으로 변환해 관리하는 데이터베이스.

- 기간계 시스템: 공급망, 구매 등과 같이 기업경영의 '기간' 이 되는 업무를 IT로 관리하는 시스템.

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

- MERGE: SOURCE LEFT OUTER JOIN TARGET 조인에 성공하면 UPDATE, 실패하면 INSERT

 

 

2. Direct Path I/O 활용

1) Direct Path I/O

- 버퍼캐시를 경유하지 않고 곧 바로 데이터 블록을 읽고 쓸 수 있는 기능

병렬쿼리

- 아래처럼 쿼리문에 힌트를 사용하면, 지정한 병렬도만큼 병렬 프로세스가 떠서 동시에 작업을 진행한다.

SELECT /*+ FULL(T) PARALLEL(T 4) */ *
  FROM BIG_TABLE T;

- 놀랍게도, 위처럼 병렬도를 4로 지정하면, 성능이 네 배 빨라지는 게 아니라 수십 배 빨라진다. 바로 Direct Path I/O 때문에 나타나는 효과다. 버퍼캐시를 탐색하지 않고, 디스크로부터 버퍼캐시에 적재하는 부담도 없으니 빠른 것이다.

2) Direct Path Insert

- Direct Path Insert 방식을 사용하면, 대량 데이터를 일반적인 INSERT 보다 훨씬 더 빠르게 입력할 수 있다.

3) 병렬 DML

 

3. 파티션을 활용한 DML 튜닝

1) 테이블 파티션

- 파티셔닝: 테이블 또는 인덱스 데이터를 특정 컬럼(파티션 키) 값에 따라 별도 세그먼트에 나눠서 저장하는 것

- 파티션이 필요한 이유

 1) 관리적 측면: 파티션 단위 백업, 추가, 삭제, 변경 -> 가용성 향상

 2) 성능적 측면: 파티션 단위 조회 및 DML, 경합 또는 부하 분산

Range 파티션

- Range 파티션: 값의 순서에 따라 저장할 파티션이 결정됨

- 파티션 Prunning: SQL 하드파싱이나 실행 시점에 조건절을 분석해서 읽지 않아도 되는 파티션 세그먼트를 액세스 대상에서 제외하는 기능

해시 파티션

- 해시 파티션: 파티션 키 값을 해시 함수에 입력해서 반환받은 값이 같은 데이터를 같은 세그먼트에 저장하는 방식

리스트 파티션

- 리스트 파티션: 사용자가 정의한 그룹핑 기준에 따라 데이터를 분할 저장하는 방식

 

2) 인덱스 파티션

- 로컬 파티션 인덱스: 각 테이블 파티션과 인덱스 파티션이 서로 1:1 대응 관계가 되도록 오라클이 자동으로 관리하는 파티션 인덱스. 테이블 파티션의 속성을 그대로 상속받는다.

- 글로벌 파티션 인덱스: 테이블 파티션과 독립적인 구성(파티션 키, 파티션 기준값 정의)을 갖는다.

중요한 인덱스 파티션 제약

- Unique 인덱스를 파티셔닝하려면, 파티션 키가 모두 인덱스 구성 컬럼이어야 한다.

- 위 조건은 DML 성능 보장을 위해 당연히 필요하다.

 

3) 파티션을 활용한 대량 UPDATE 튜닝

파티션 Exchange를 이용한 대량 데이터 변경

- 테이블이 파티셔닝 돼 있고, 인덱스도 로컬 파티션이라면, 수정된 값을 갖는 임시 세그먼트를 만들어 원본 파티션과 바꿔치기 하는 방식이다.

 

4) 파티션을 활용한 대량 DELETE 튜닝

DELETE가 느린 이유

1. 테이블 레코드 삭제
2. 테이블 레코드 삭제에 대한 Undo Logging
3. 테이블 레코드 삭제에 대한 Redo Logging
4. 인덱스 레코드 삭제
5. 인덱스 레코드 삭제에 대한 Undo Logging
6. 인덱스 레코드 삭제에 대한 Redo Logging
7. Undo(2번과 5번)에 대한 Redo Logging

파티션 Drop을 이용한 대량 데이터 삭제

- 테이블이 삭제 조건절 컬럼 기준으로 파티셔닝 돼 있고, 인덱스도 로컬 파티션이라면 대량 데이터를 순식간에 삭제할 수 있다.

5) 파티션을 활용한 대량 INSERT 튜닝

비파티션 테이블

- 비파티션 테이블에 손익분기점을 넘는 대량 데이터를 INSERT 하려면, 인덱스를 Unusable 시켰다가 재생성하는 방식이 더 빠를 수 있다.

파티션 테이블

- 테이블이 파티셔닝 돼 있고, 인덱스도 로컬 파티션이라면, 파티션 단위로 인덱스를 재생성하면 된다.

 

 

4. Lock과 트랜잭션 동시성 제어

1) 오라클 Lock

- 오라클은 공유 리소스와 사용자 데이터를 보호할 목적으로 DML Lock, DDL Lock, 래치, 버퍼 Lock, 라이브러리 캐시 Lock/Pin 등 다양한 종류의 Lock을 사용한다.

- 래치: SGA에 공유된 각종 자료구조를 보호하기 위해 사용

- 버퍼 Lock: 버퍼 블록에 대한 엑세스를 직렬화하기 위해 사용

- 라이브러리 캐시 Lock/Pin: 라이브러리 캐시에 공유된 SQL커서와 PL/SQL 프로그램을 보호하기 위해 사용

- DML Lock: 다중 트랜잭션이 동시에 엑세스하는 사용자 데이터의 무결성을 보호

DML 로우 Lock

- DML 로우 Lock은 두 개의 동시 트랜잭션이 같은 로우를 변경하는 것을 방지한다. 

- UPDATE 또는 DELETE를 진행 중인(아직 커밋하지 않은) 로우를 다른 트랜잭션이 UPDATE 하거나 DELETE 할 수 없다.

- INSERT 에 대한 로우 Lock 경합은 Unique 인덱스가 있을 때만 발생한다. 두 트랜잭션이 같은 값을 입력하려고 할 때, 블로킹이 발생한다. 블로킹이 발생하면, 후행 트랜잭션은 기다렸다가 선행 트랜잭션이 커밋하면 INSERT에 실패하고, 롤백하면 성공한다. 두 트랜잭션이 서로 다른 값을 입력하거나 Unique 인덱스가 아예 없으면, INSERT에 대한 로우는 Lock 경합은 발생하지 않는다.

- SELECT 문에 로우 Lock을 사용하지 않는다. MVCC모델에 따라 오라클은 다른 트랜잭션이 변경한 로우를 읽을 때 복사본 블록을 만들어서 쿼리가 '시작된 시점'으로 되돌려서 읽는다.

- DML 로우 Lock 에 의한 성능 저하를 방지하려면, 온라인 트랜잭션을 처리하는 중간에 Lock을 필요 이상으로 오래 유지하지 않도록 커밋 시점을 조절해야 한다. 그에 앞서, 트랜잭션이 빨리 일을 마치도록, 즉 Lock이 오래 지속되지 않도록 관련 SQL을 모두 튜닝해야 한다. SQL 튜닝이 곧 Lock 튜닝인 셈이다.

DML 테이블 Lock

- 오라클은 DML 로우 Lock을 설정하기에 앞서, 테이블 Lock 을 먼저 설정한다. 현재 트랜잭션이 갱신 중인 테이블 구조를 다른 트랜잭션이 변경하지 못하게 막기 위해서다.

- 테이블 Lock에는 여러 가지 Lock모드를 사용한다.

- 선행 트랜잭션과 호환되지 않는 모드로 테이블 Lock을 설정하려는 후행 트랜잭션은 대기하거나 작업을 포기해야 한다.

- 테이블 Lock: 테이블 전체에 Lock이 걸리는 것이 아니다. 자신(테이블 Lock을 설정한 트랜잭션)이 해당 테이블에서 현재 어떤 작업을 수행 중인지를 알리는 일종의 푯말(Flag)이다.

- 어떤 모드를 사용했는지에 따라 후행 트랜잭션이 수행할 수 있는 작업의 범위가 결정된다. 기다려야 한다면, 대기자 목록에 Lock 요청을 등록하고 기다린다.

Lock을 푸는 열쇠, 커밋

- 블로킹과 교착상태는 다르다.

- 블로킹(Blocking): 선행 트랜잭션이 설정한 Lock 때문에 후행 트랜잭션이 작업을 진행하지 못하고 멈춰 있는 상태. 해소하는 방법은 커밋 또는 롤백 뿐이다.

- 교착상태(Deadlock): 두 트랜잭션이 각각 특정 리소스에 Lock을 설정한 상태에서 맞은편 트랜잭션이 Lock을 설정한 리소스에 또 Lock을 설정하려고 진행하는 상황. 교착상태가 발생하면 둘 중 하나가 뒤로 물러나지 않으면 영영 풀릴 수 없다. 좁은 골목길에 두 대의 차량이 마주 선 것과 같다.

- 같은 데이터를 갱신하는 트랜잭션이 동시에 수행되지 않도록 애플리케이션을 설계해야 하고, DML Lock 때문에 동시성이 저하되지 않도록 적절한 시점에 커밋해야 한다.

- 반대로, 불필요하게 커밋을 너무 자주 수행하면 서버 프로세스가 LGWR에게 로그 버퍼를 비우도록 요청하고 동기(sync) 방식으로 기다리는 횟수가 늘기 때문에 성능이 느려진다. 

- 잦은 커밋 때문에 성능이 매우 느리다면, 비동기식 커밋과 배치 커밋을 활용하기도 한다.

 

2) 트랜잭션 동시성 제어

비관적 동시성 제어

- 비관적 동시성 제어: 사용자들이 같은 데이터를 동시에 수정할 것으로 가정한다. 

- 한 사용자가 데이터를 읽는 시점에 Lock을 걸고 조회 또는 갱신처리가 완료될 때 까지 이를 유지한다.

- 잘못 사용하면 동시성이 나빠진다.

낙관적 동시성 제어

- 낙관적 동시성 제어: 사용자들이 같은 데이터를 동시에 수정하지 않을 것으로 가정한다. 

- 데이터를 읽을 때 Lock을 설정하지 않는다.

- 동시 트랜잭션에 의한 잘못된 데이터 갱신을 신경 쓰지 않는 것은 절대 아니다. 데이터를 수정하고자 하는 시점에 앞서 읽은 데이터가 다른 사용자에 의해 변경되었는지 반드시 검사해야 한다.

동시성 제어 없는 낙관적 프로그래밍

- 만약 동시에 고객정보르 갱신할 가능성이 전혀 없거나 그렇게 처리하는 거시 업무 규칙이라면 전혀 문제 삼을 일이 아니다. 하지만 그렇지 않은데도 동시성 제어를 제대로 구현하지 않아 고객 정보가 계속 잘못 갱신된다면, 언제고 고객으로부터 클레임을 받을 수 있는 문제다.

데이터 품질과 동시성 향상을 위한 제언

- 성능보다 데이터 품질이 더 중요하다.

- FOR UPDATE 사용을 두려워하지 말자. 다중 트랜잭션이 존재하는 데이터베이스 환경에서 공유 자원에 대한 엑세스 직렬화는 필수다. JAVA 프로그래머라면 멀티 쓰레드 프로그래밍할 때 syncronized 키워드의 역할을 상기하기 바란다.

- FOR UPDATE가 필요한 상황이면 이를 정확히 사용하고, 코딩하기 번거롭더라도 동시성이 나빠지지 않게 WAIT 또는 NOWAIT 옵션을 활용한 예외처리에 세심한 주의를 기울여야 한다.

- 동시성을 향상하고자 할 때 SQL튜닝은 기본이다.

 

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

채번 테이블

- 채번하기 위해 별도 테이블을 관리하는 방식

- 이 방식은 채번 레코드를 변경하는 과정에 자연스럽게 액세스 직렬화(트랜잭션 줄 세우기)가 이루어지므로 두 트랜잭션이 중복 값을 채번할 가능성을 원천적으로 방지해 준다.

- 단점은 성능이 안 좋다. 채번 레코드를 변경하기 위한 로우 Lock 경합 때문이다. 로우 Lock은 대상 테이블에 INSERT를 마치고 커밋 또는 롤백할 때까지 지속된다.

시퀀스 오브젝트

- 성능이 빠르다만 성능이슈가 없는 것은 아니다. 시퀀스 채번 과정에서 발생하는 Lock 때문이다.

- 시퀀스 오브젝트는 사실 오라클 내부에서 관리하는 채번 테이블이다. 결국 시퀀스 오브젝트도 테이블이다.

- 신규 데이터를 입력하는 과정에서 결번이 생길 수도 있다.

MAX + 1 조회

- 대상 테이블의 최종 일련번호를 조회하고, 거기에 1을 더해서 INSERT 하는 방식

- 테이블을 따로 관리하는 부담이 없고, 성능이 매우 빠르다. 또한 구분 속성별 순번을 채번할 때에도 사용할 수 있다.

시퀀스보다 좋은 솔루션

- 한 개 이상의 구분 속성과 함께 뒤쪽에 순번 대신 입력일시를 두는 방식으로 PK 설계하기

인덱스 블록 경합

- 채번 과정을 생략하는 순간부터 인덱스 블록 경합이 나타나기 시작한다.

- 인덱스 블록 경합을 해소하는 가장 일반적인 방법은 인덱스를 해시 파티셔닝하는 것이다. 이렇게 하면 값이 순차적으로 증가하더라도 해시 함수가 리턴한 값에 따라 서로 다른 파티션에 입력되므로 경합을 줄일 수 있다.

 

 

 

 

 

 

 

 

 

 

 

출처: 도서 <친절한 SQL 튜닝>