Post

DML 튜닝

Posted
By da9dac
17 min read
DML 튜닝

기본 DML 튜닝

먼저 DML 성능에 영향을 미치는 요소에 대해 알아보자

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

아래의 요소들이 DML 성능에 영향을 미치는 요소들이다.

  • 인덱스
  • 무결성 제약
  • 조건절
  • 서브쿼리
  • Redo 로깅
  • Undo 로깅
  • Lock
  • 커밋

인덱스

테이블에 레코드를 입력하면 인덱스에도 입력해야 하고, 삭제하면 인덱스도 모두 삭제해야 한다.

추가
테이블 : Freelist를 통해 블록을 할당 받아 저장한다.
인덱스 : 수직적 탐색을 통해 입력할 블록을 찾는다.

삭제
레코드 하나를 삭제하면 해당 레코드가 포함된 인덱스를 모두 삭제해야 한다.

수정
테이블의 레코드에 변화가 발생하면, 기존 데이터를 인덱스에서 삭제하고 수정된 데이터를 다시 인덱스에 삽입한다.

트랜잭션이 자주 발생하고, 규모가 크다면 해당 테이블에 인덱스를 추가할 때마다 DML 성능이 눈에 띄게 나빠진다.

무결성 제약

PK, FK 제약은 실제 데이터를 조회해야지만 확인할 수 있어서 DML 성능에 큰 영향을 미치고, Check, Not Null 제약은 데이터 조회 없이 제약 조건을 준수하는지만 확인하면 되서 상대적으로 DML 성능에 큰 영향을 미치지 않는다.

조건절

SELECT 문과 실행계획이 다르지 않기 때문에 인덱스 튜닝 원리를 그대로 적용해 최적화할 수 있다.

서브쿼리

마찬가지로 SELECT 문과 실행계획이 같기에 조인 튜닝 원리를 적용해 최적화할 수 있다.

Redo 로깅

데이터파일과 컨트롤 파일에 가해지는 모든 변경사항을 Redo 로그에 기록해, 트랜잭션 데이터가 유실됐을 때 이전 상태로 복구할 때 사용된다. 즉, DML을 수행할 때마다 Redo 로그를 생성해야 한다.

Redo 로그는 물리적으로 디스크가 손상되었을 때 DB를 복구하기 위해 사용되기도 하고, 휘발성인 캐시에 저장된 변경사항이 데이터 블록에 기록 전에 유실되는 것을 대비하려고 사용되기도 하며, Fast Commit을 위해 사용하기도 한다.

Undo 로깅

Redo는 트랜잭션을 재현해 과거를 현재로 되돌린다면, Undo는 트랜잭션을 롤백해 현재를 과거로 되돌린다. 그래서 Undo에는 변경된 블록을 이전 상태로 돌릴 때 필요한 정보를 로깅한다.

Undo 로그는 트랜잭션을 롤백하거나, 시스템 셧다운 시점에 아직 커밋되지 않은 트랜잭션을 모두 롤백하거나, 읽기 일관성을 위해 사용된다.

오라클은 Current 모드와 Consistent 두 가지 모드로 데이터를 읽는 MVCC 모델을 사용한다. Current 모드는 실제로 데이터를 읽는 그 순간의 데이터를 읽고, Consistent 모드는 쿼리가 실행된 시점의 데이터를 읽는다.

SELECT 문은 대부분의 경우 항상 Consistent 모드로 데이터를 읽고, DML 문은 Consistent 모드로 대상 레코드를 찾고, Current 모드로 데이터 처리를 한다.

Lock

Lock을 필요 이상으로 자주, 길게, 고레벨로 사용하면 DML 성능이 떨어지고, 반대로 적고, 짧게, 필요 레벨 이하로 낮추면 성능은 향상되도 데이터 품질이 떨어진다. 둘의 균형을 잘 잡아서 세심한 동시성 제어를 해줘야 한다.

커밋

커밋과 DML은 별개로 실행하지만, DML을 끝내려면 커밋까지 완료해야 한다. 커밋의 내부 메커니즘을 살펴보자.

(1) DB 버퍼캐시
버퍼캐시에서 변경된 블록을 모아 주기적으로 데이터파일에 일괄 기록한다.

(2) Redo 로그버퍼
버퍼캐시에 가한 변경사항을 Redo 로그에도 기록한다. 그래서 버퍼캐시 데이터가 유실되어도 로그를 이용해 복구할 수 있다. 로그도 파일이기에 저장시 디스크 I/O로 인해 동작이 느리다.

이를 해결하기 위해 로그 파일에 기록하기 전에 로그버퍼에 기록하고, 나중에 LGWR이 로그 파일에 일괄 기록한다.

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

  1. DML 실행
  2. Redo 로그버퍼에 변경사항 기록
  3. 버퍼블록에서 데이터 변경 혹은 데이터파일 읽고 데이터 변경
  4. 커밋 (커밋 시에도 Redo 로그를 디스크에 기록해 영속성을 보장한다.)
  5. 로그버퍼 내용을 로그파일에 일괄 저장
  6. 변경된 버퍼블록들을 데이터파일에 일괄 저장

(4) 커밋
커밋이 수행되는 동안 서버 프로세스는 그때까지 했던 작업을 디스크에 기록해야 하고, 기록이 끝날 때까지 서버 프로세스는 다음 작업을 할 수 없다. (동기 방식)

즉, 로그버퍼 내용을 로그파일에 저장하고, 변경된 버퍼블록을 디스크파일에 저장하고 난 뒤에야 서버 프로세스는 다음 작업을 할 수 있고, 두 작업은 디스크 I/O 작업이기에 커밋은 생각보다 느리다.

트랜잭션을 너무 길게 정의하면 Undo 공간이 부족해져 시스템 장애가 발생할 수 있고, 과하게 자주 커밋하면 성능 저하가 발생할 수도 있다. 따라서 트랜잭션을 논리적으로 잘 정의해 불필요한 커밋이 발생하지 않도록 해야한다.

데이터베이스 Call과 성능

데이터베이스 Call

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

  • Parse Call: SQL 파싱과 최적화 수행(라이브러리 캐시 존재 시 생략 가능)
  • Execute Call: SQL 실행 (DML은 해당 단계에서 종료)
  • Fetch Call: 데이터를 읽어서 사용자에게 결과집합 전송(SELECT 문에서만 발생)

Call은 발생 위치에 따라 아래 두 종류로 나뉜다.

  • User Call: DBMS 외부에서 발생하는 Call이다. (WAS)
  • Recursive Call: DBMS 내부에서 발생하는 Call이다. (딕셔너리 조회, 사용자 정의 함수/프로시저/트리거)

Call 종류와 상관 없이 Parse, Execute, Fetch 단계를 거치고, Call이 많으면 결국 성능은 느려질 수 밖에 없다. 특히, User Call은 성능에 많은 영향을 끼친다.

One SQL의 중요성

업무 로직이 복잡하면 절차적으로 처리할 수 밖에 없지만, 그렇지 않다면 One SQL로 구현해서 Call을 줄이는 것이 좋다. 이때 유용한 구문 활용법은 다음과 같다.

  • Insert Into Select
  • 수정가능 조인 뷰
  • Merge 문

Array Processing 활용

One SQL 구현을 절대 쉽지 않기 때문에, Array Processing을 활용해 복잡한 업무 로직 처리 시 Call 부하를 줄일 수 있다.

1
2
3
4
5
6
7
8

spring:
  jpa:
    properties:
      hibernate:
        jdbc:
          batch_size: 100
        order_inserts: true
        order_updates: true

스프링에서는 배치 사이즈를 설정해 saveAll() 같은 메서드를 통해 배치 처리를 할 수 있다. 즉, 한 번의 Call에 배치 사이즈만큼의 쿼리를 한 번에 보내 Call 횟수를 줄이는 것이다.

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

OLTP 시스템에서는 이들 기능을 해제할 수는 없지만, 동시 트랜잭션 없이 대량 데이터를 적재하는 배치 프로그램에서는 이를 해제해 큰 성능개선 효과를 얻을 수 있다.

  • PK 제약에 Unique 인덱스를 사용한 경우에는 PK 제약을 Unusable로 변경하면 데이터를 입력할 수 없다.
  • 데이터를 입력하려면 인덱스 자체를 Drop 하거나, PK 제약에 Non-Unique 인덱스를 사용해야 한다.

수정가능 조인 뷰

입력, 수정, 삭제가 가능한 조인 뷰로 12c이상 버전 혹은 10g이하 버전에서 힌트를 사용해 실행할 수 있다. 하지만 수정가능 조인 뷰를 사용하기 위한 제약 조건이 있다.

  • 키 보존 테이블이어야 한다. 조인된 결과집합에서도 중복 값 없이 유니크하게 식별이 가능한 테이블이어야 한다.
    즉, 뷰에 ROWID를 제공하는 테이블을 의미한다.
  • 일대다 관계에서 다쪽만 가능하다. 일쪽은 조인 결과에서 키가 보존되지 않아 DML이 허용되지 않는다.
  • 집계함수, 그룹핑(이건 가능한거 같은데 확인 필요), 중복제거, 집합연산이 포함된 경우는 불가능하다.

MERGE 문 활용

원본 테이블 기준으로 대상 테이블과 Left Outer 조인을 수행하고, 조인 성공 시 대상 테이블에 UPDATE, 실패 시 대상 테이블에 INSERT를 수행한다.

Optional Clauses

when matched then update/insert를 통해 선택적으로 처리할 수도 있다.

Conditional Operations

ON 절에 기술한 조인문 외에 추가로 조건절을 기술할 수도 있다.

DELETE Clause

이미 저장된 데이터를 조건에 따라 지울 수도 있다. 주의할 점은 UPDATE 후의 레코드를 기준으로 작동하며, 조인에 성공한 데이터만 삭제할 수 있다.

즉, 조인에 성공한 데이터를 모두 UPDATE 한 후에, 조건절을 만족하는 데이터를 삭제한다.

Direct Path I/O 활용

OLTP는 기준성 데이터 같은 것을 반복적으로 읽어 버퍼캐시가 성능 향상에 도움을 주지만, 정보계 시스템이나 배치 프로그램에서는 버퍼캐시를 경유하는 I/O 메커니즘이 오히려 성능을 저하시킬 수 있다.

그래서 이런 경우를 위해 버퍼캐시를 경유하지 않고 곧바로 데이터 블록을 읽고 쓸 수 있는 기능이 Direct Path I/O이다.

Direct Path I/O

해당 기능은 다음 상황에 작동한다.

  • 병렬 쿼리로 Full Scan을 수행할 때
  • 병렬 DML을 수행할 때
  • Direct Path Insert를 수행할 때
  • Temp 세그먼트 블록들을 읽고 쓸 때
  • direct 옵션을 지정하고 export를 수행할 때
  • nocache 옵션을 지정한 LOB 컬럼을 읽을 때

병렬 쿼리

parallel 또는 parallel_index 힌트를 사용해 지정한 병렬도 만큼 병렬 프로세스가 떠서 동시에 작업을 진행한다. 이때 병렬도만큼 빨라지는게 아니라, Direct Path I/O를 사용해 4배보다 훨씬 더 빨라진다.

Direct Path Insert

INSERT 작업은 Freelist에서 데이터를 입력할 수 있는 블록을 받아, 해당 블록을 버퍼캐시에서 찾고, 없으면 데이터파일에 읽어 버퍼캐시에 적재 후에 INSERT 내용을 Undo 세그먼트와 Redo 로그에 기록하기 때문에 느리다.

Direct Path Insert를 사용하면 Freelist를 참조하지 않고 HWM 바깥 영역에 데이터를 순차적으로 입력하며, 블록을 버퍼캐시에서 탐색하지 않고, 적재하지도 않고, 데이터파일에 직접 기록한다. 또한 Undo 로깅을 안하며, Redo 로깅도 안하게 할 수도 있다.

Array Processing도 append_values 힌트를 사용해 Direct Path Insert 방식으로 처리할 수 있다.

Direct Path Insert 사용 시 주의할 점은, Exclusive 모드 TM Lock이 걸여 커밋 전까지 다른 트랜잭션은 해당 테이블에 DML을 수행할 수 없다. 또한, 무조건 HWM 바깥 영역에 입력하므로 테이블에 여유 공간이 있어도 재활용하지 않는다.

병렬 DML

Book, 친절한 SQL 튜닝
SQL DB Oracle
This post is licensed under CC BY 4.0 by the author.

Trending Tags

CS DB Oracle SQL