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

dani Kim·2024년 7월 5일
Sql튜닝

6.1 기본 DML튜닝

인덱스와 DML성능

  • 테이블에 레코드를 입력하면 인덱스에도 입력해야한다.
  • 테이블에서 한건 변경할때마다 인덱스에는 두개의 오퍼레이션이 발생
  • 인덱스를 하나라도 줄이면 TPS(Tramsaction Per Second)는 향상

무결성 제약과 DML성능

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

Redo 로깅과 DML 성능

오라클은 데이터파일과 컨트롤 파일에 가해지는 모든 변경사항을 Redo 로그에 기록한다

Redo 로그의 용도

  1. Database Recovery (DB복구)
  2. Cache Recovery
  3. Fast Commit
  • 버퍼캐시는 휘발성이다. 캐시에 저장된 변경사항이 디스크싱에 저장되지 않은 상태에서 비정상적인 종료가 발생할경우 그때까지의 작업내용을 잃게된다
    그러하여 트랜잭션 데이터 유실에 대비하기위해 Redo 로그를 남기게된다
  • 사용자의 갱신내용이 메모리상의 버퍼블록에만 기록된채 아직 디스크에 기록되지 않았지만 Redo 로그를 믿고 빠르게 커밋을 완료한다는 의미에서 이를 Fast Commit이라고 부른다.

    커밋정보까지 Redo 로그파일에 안전하게 기록된다면 언제든지 복구될수있다

Undo 용도

  1. Transaction RollBack
  2. Transaction Recovery
  3. Read Consistency

MVCC모델

오라클은 데이터를 2가지 모드로 읽는다 (Current 모드, Consistent 모드)

  • Current모드는 디스크에서 캐시로 적재된 원본(Current) 블록을 현재상태 그대로 읽는 방식
  • Consistent모드는 쿼리가 시작된 이후에 다른 트랜잭션에 의해 변경된 블록을 만나면 원본 블록으로부터 복사본(CR Copy)을 만들고 거기에 Undo(되돌리기) 데이터를 적용함으로써 쿼리가 '시작된 시점'으로 되돌려서 읽는 방식을 말한다
    → 일관된읽기 : 한번 읽기 시작한데이터는 종료될때까지 데이터가 유지되어야한다.

커밋과 DML 성능

  • 버퍼캐시 : 버퍼캐시에서 변경된블록(Dirty 블록 : 누가 손대서 블록이 Dirty)
    을 모아 주기적으로 데이터파일에 일괄기록하는작업은 DBWR(Database Writer)가 맡는다.

  • Redo 로그버퍼

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

    2. 로깅 성능 문제를 해결하기위해 오라클은 로그버퍼를 이용한다.
      Redo 로그파일에 기록하기전에 먼저 로그버퍼에 기록하는방식이다.
      로그버퍼에 기록한 내용은 나중에 LGWR (Log Writer) 프로세스가 Redo 로그파일에 일괄(Batch) 기록된다

  • 트랜잭션 데이터 저장 과정을 정리하면

    1. DML 문을 실행하면 Redo 로그버퍼에 변경사항을 기록한다.
    2. 버퍼블록에서 데이터를 변경(추가/수정/삭제)한다.
      버퍼캐시에서 블록을 찾지못하면 데이터파일에서 읽는 작업진행
    3. 커밋한다
    4. LGWR 프로세스가 Redo 로그버퍼 내용을 로그파일에 일괄저장한다
    5. DBWR 프로세스가 변경된 버퍼블록들은 데이터파일에 일괄 저장한다.

데이터베이스 Call과 성능

User Call은 DBMS 외부로부터 인입되는 Call
Recursive Call은 DBMS 내부에서 발생하는 Call이다.

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

→ 제약이 걸려있으면 느려지니까 해제후 데이터추가
1. 제약 비활성화
2. DML 조작
3. 제약 활성화
인덱스 속도가 빨라진다

MERGE문 활용

기간계 시스템에서 가져온 신규 트랜잭션 데이터를 반영함으로써 두 시스템간 데이터를 동기화하는 작업

-- 1. 전일 발생한 변경 데이터를 기간계 시스템으로부터 추출
create table customer_delta
as
select * from customer
where	mod_dt >= trunc(sysdate)-1
and 	mod_dt < trunc(sysdate);

-- 2. CUSTOMER_DELTA 테이블을 DW시스템으로 전송(Transportation)
-- 3. DW시스템으로 적재

Target 테이블과 Left Outer 방식으로 조인해서 조인에 성공하면 UPDATE,
실패하면 INSERT한다.
MERGE문을 UPSERT(Update + Insert) 라고 부른다

DELETE 절은 조인에 성공한 데이터를 모두 UPDATE 하고서 그 결과값이 DELETE WHERE 조건절을 만족하면 삭제하는 기능이다.

6.2 Direct Path I/O 활용

대량데이터를 처리할때 버퍼캐시를 경유하지않고 곧바로 데이터 블록을 읽고 바로 데이터 블록을 읽고 쓸수 있는 Direct Path I/O 기능을 제공한다.

Direct Path I/O

일반적인 블록 I/O는 읽고자하는 블록을 버퍼캐시에서 찾아보고 찾지못할때만 디스크에서 읽는다. 찾은 버퍼블록에 변경을 가하고 나면 DBWR 프로세스가 변경된 블록 Dirty 블록들을 주기적으로 찾아 데이터파일에 반영해준다.
→ 지연된쓰기

Direct Path Insert

일반적인 Insert보다 Direct Path Insert 방식이 더 빠르다

일반적인 Insert 방식

  1. 테이블 HWM (High-WaterMark 데이터가 어디까지 차있는지) 확인후 빈공간을 FreeList(어디가 비었는지 위치를 저장)에서 찾는다.
  2. FreeList 에서 할당받은 블록을 버퍼캐시에서 찾는다
  3. 버퍼캐시가 없으면 데이터파일에서 읽어 버퍼캐시에 적재
  4. INSERT내용을 Undo 세그먼트에 기록
  5. INSERT내용을 REDO 로그에 기록

Direct Path Insert 방식

  • INSERT ... SELECT 문에 append 힌트
  • parallel 힌트를 이용해 병렬 모드로 INSERT (쓰레드 4개 병렬처리)
  • direct 옵션을 지정하고 SQL*Loader로 데이터 적재 (대용량데이터 DB Upload)

6.3 파티션을 활용한 DML 튜닝

종류: Range, 해시, 리스트파티션
파티션 인덱스는 수평분할방식이다

테이블 파티션

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

해시 파티션

파티션 키 값을 해시함수에 입력해서 반환받은 값이 같은 데이터를 같은 세그먼트에 저장하는 방식이다.
해시 알고리즘 특성상 등치(=) 조건 또는 IN-List 조건으로 검색할 때만 파티션이 작동한다.

해시코드 % 4 하면 나머지가 0,1,2,3 등으로 나온다.
이렇게 나온 값을 공간으로 분리한다

리스트 파티션

사용자가 정의한 그룹핑 기준에 따라 데이터를 분할 저장하는 방식이다.

인덱스 파티션

종류: 로컬 파티션 인덱스, 글로벌 파티션 인덱스, 비파티션 인덱스

로컬 파티션 인덱스

아래 그림처럼 계쩔별로 별도 색인을 만드는 것과 같다
CREATE INDEX 문 뒤에 LOCAL 옵션을 추가하면된다.

create index 주문_x01 on 주문 (주문일자, 주문금액) LOCAL;

글로벌 파티션 인덱스

파티션을 테이블과 다르게 구성한 인덱스
CREATE INDEX 문 뒤에 GLOBAL 옵션을 추가하면 된다

비파티션 인덱스

파티셔닝 하지 않은 인덱스
인덱스가 여러 테이블 파티션을 가르키기때문에 글로벌 비파티션 인덱스라고 부르기도 한다.

6.4 Lock과 트랜잭션 동시성 제어

오라클 Lock

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

DML 로우 Lock (한줄만 Lock)

  • DML 로우 Lock은 두개의 동시 트랜잭션이 같은 로우를 변경하는 것을 방지한다. 하나의 로우를 변경하려면 로우 Lock을 먼저 설정해야한다.

  • INSERT에 대한 로우 Lock 경합은 Unique 인덱스가 있을때만 발생한다.
    Unique 인덱스가 있는 상황에서 두 트랜잭션이 같은 값을 입력하려고 할때 블록킹이 발생한다.

  • 두 트랜잭션이 서로 다른 값을 입력하거나 Unique 인덱스가 아예 없으면 INSERT에 대한 로우 Lock 경합은 발생하지 않는다.

  • MVCC 모델을 사용하는 오라클은 (for Update절이 없는) SELECT 문에 로우 LOCK을 사용하지 않는다
    for Update절 : select for update로 업데기트 하기전에 select로 확인

  • 다른 트랜잭션이 변경한 로우를 읽을때 복사본 블록을 만들어서 쿼리가 '시작된 지점'으로 되돌려서 읽는다
    → MVCC: 복사해서 Undo 하고 복사본 건네줌

  • Lock과 배타적Lock(쓰기)은 호환되기 않는다 (해결하기위해 MVCC 이용)

DML 테이블 Lock

  • 테이블 Lock에 의한 경합은 절대 발생하지 않는다. (서로 다른 row)
    같은 로우를 갱신하려고 할때만 로우 Lock에 의한 경합이 발생한다.
  • DML을 수행하기 전에 항상 테이블 Lock을 먼저 설정한다
  • 테이블 Lock은 자신(테이블 Lock을 설정한 트랜잭션)이 해당 테이블에서 현재 어떤 작업을 수행중인지를 알리고 있는 일종의 푯말이다

→ DML을 수행할때 Table Lock을 걸어서 다른사람이 DDL 구조변경을 실행하지 못하게 테이블에 Lock을 건다
→ 읽기Lock, 쓰기Lock 이 걸리면 count를 하고 Lock이 풀릴때 하나씩 감소한다.

Lock을 얻고자 할때 사용자가 선택할수 있는 세가지 옵션
1. Lock이 해제될때까지 기다린다 (select from t for update)
2. 일정 시간만 기다리다 포기한다 (select from t for update wait 3)
3. 기다리지 않고 작업을 포기한다 (select * from t for update nowait)


Lock을 푸는 열쇠 커밋

블로킹은 선행 트랜잭션이 설정한 Lock 때문에 후행 트랜잭션이 작업을 진행하지 못하고 멈춰있는 상태를 말한다. 이것을 해소하는 방법은 커밋 또는 롤백 뿐이다
교착상태는 두 트랜잭션이 각각 특정 리소스에 Lock을 설정한 상태에서 맞은편 트랜잭션이 Lock을 설정한 리소스에 또 Lock을 설정하려고 진행하는 상황을 말한다.

교착상태가 발생하면 ?
교착상태를 발생시킨 문장 하나만 롤백시킨다
블로킹이 발생하면?
커밋 또는 롤백을 실행시킨다.

비동기식 커밋과 배치 커밋을 활용하는 방안을 검토할수있다

  • WAIT (Default) : LGWR(Log Write) 가 로그버퍼를 파일에 기록했다는 완료 메시지를 받을때까지 기다린다 (동기식 커밋)
  • NOWAIT : LGWR의 완료 메시지를 기다리지 않고 바로 다음 트랜잭션을 진행한다 (비동기식 커밋)
  • IMMEDIATE (Default) : 커밋 명령을 받을때마다 LGWR가 로그 버퍼를 파일에 기록한다.
  • BATCH : 세션 내부에 트랜잭션 데이터를 일정량 버퍼링했다가 일괄 처리한다

이들 옵션을 조합해 아래 네가지 커밋 명령을 사용할수 있다

COMMIT WRITE IMMEDIATE WAIT;
COMMIT WRITE IMMEDIATE NOWAIT;
COMMIT WRITE BATCH WAIT;
COMMIT WRITE BATCH NOWAIT;

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

성능보다 데이터품질이 더 중요하다
FOR UPDATE 사용을 두려워하지 말자.

  • For Update를 제거해서 성능 문제가 해결되었다면, 그 순간부터 데이터 품질은 나빠지고 있다

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

  • 채번 테이블 : 사용자가 채번을 직접 만들어서 씀
  • 시퀀스 오브젝트 : 오라클에서 시스템에서 제공
  • MAX + 1 조회 : 성능이 제일 안좋음

채번 테이블

  • 범용성이 좋다
  • INSERT 과정에 중복 레코드 발생에 대비한 예외처리에 크게 신경쓰지 않아도 되므로 채번 함수만 잘 정리하면 편리하게 사용할수 있다
  • INSERT 과정에 결번을 방지할수 있다
  • PK가 복합 컬럼일때도 사용할수 있다
  • 동시 INSERT가 아주많은 테이블에는 사실상 이 방식을 사용하기 어렵다
    → 여러 프로세스가 같은번호로 INSERT 하려고 할때 경합이 생겨서 중간에 채번 이빨이 빠지는 경우가 있다

시퀀스 오브젝트

채번테이블과 채번시스템은 같은방식을 사용
다른점은 시퀀스 오브젝트는 오라클에서 제공한다는점

  • 성능이 빠르다.
  • 채번테이블과 마찬가지로 INSERT 과정에 중복 레코드 발생에 대비한 예외처리에 크게 신경쓰지 않아도 된다.
  • 캐시 사이즈를 적절히 설정하면 가장 빠른 성능을 제공한다
    → 미리 채번을 만들어서 저장해둠
  • 시퀀스에는 자율 트랜잭션 기능도 기본적으로 구현돼 있다
    → 동기화가 된다.

시퀀스 채번으로 인한 로우 캐시 LOCK 경합을 줄이기 위해 오라클은 기본적으로 CHECK 옵션을 사용한다. 옵션을 명시적으로 설정하지 않았을때 기본값은 20이다.
채번 빈도가 낮아 굳이 캐시를 사용하고 싶지 않다면 NOCACHE 옵션을 지정하면 된다.

MAX+1 조회

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

insert into 상품거래(거래일련번호, 계좌번호, 거래일시, 상품코드, 거래가격, 거래수량) 
values ( (select max(거래 일련번호) + 1 from 상품거래)
		, :acnt_no, sysdate, :prod_cd, :trd_price, :trd_qty );

시퀀스 또는 별도의 채번 테이블을 관리하는 부담이 없다.
동시 트랜잭션에 의한 충돌이 많지 않으면 성능이 매우 빠르다.

결론: 주문번호는 시퀀스를 사용하고, 주문일련번호는 채번방식을 사용하면된다

profile
dani Kim
파라랑푸~~~
이전 포스트

5장 친절한 SQL튜닝 - 소트 튜닝

다음 포스트

스프링부트 시작

0개의 댓글