[친절한 SQL 튜닝 스터디] DML 튜닝

https://velog.io/@wooncloud/series/%EC%B9%9C%EC%A0%88%ED%95%9C-SQL-%ED%8A%9C%EB%8B%9D-%EC%8A%A4%ED%84%B0%EB%94%94

시리즈 | 친절한 SQL 튜닝 스터디 - wooncloud.log

 

기본 DML 튜닝

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

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

인덱스와 DML 성능

INSERT

테이블 레코드를 입력하면 인덱스에도 입력해야 한다.

테이블은 Freelist를 통해 블록을 할당받으나 인덱스는 정렬된 자료구조라 수직적 탐색을 통해 입력할 블록을 찾아야 한다.

인덱스에 입력하는 과정이 더 복잡하므로 DML 성능에 미치는 영향은 크다.

DELETE

테이블에서 레코드 하나 삭제시 인덱스 레코드를 모두 찾아서 삭제 해야한다.

UPDATE

변경된 컬럼을 참조하는 인덱스만 찾아서 변경해주면 된다.

대신, 테이블에서 한 건을 변경할 때마다 인덱스에는 두 개 오퍼레이션이 발생한다.

인덱스는 정렬된 자료구조이기 때문에 만약, A가 K로 변경되면 저장위치도 달라져서 삭제 후 삽입하는 방식으로 처리한다.

인덱스 갯수가 DML 성능에 미치는 영향이 매우 커서 인덱스 설계에 심혈을 기울여야 한다.

핵심 트랜잭션 테이블에서 인덱스 하나라도 줄이면 TPS는 그만큼 향상됨.

예시

• source 테이블에는 레코드가 100만개임.
• target 테이블은 비어있음.
• target 테이블에 pk 인덱스 하나만 생성한 상태에서 source 테이블을 읽어 100만개 입력한다.
• 결과 4.95초

create table source
as
select b.no, a.*
  from (select * from emp where rownum <= 10) a
     , (select rownum as no from dual connect by level <= 100000) b;

-------------------------------------------------
create table target
as
select * from source where 1 = 2;

-------------------------------------------------
alter table target add
constraint target_pk primary key(no, empno);

set timing on;
insert into target
select * from source;

100000 개의 행이 만들어졌습니다.
경 과 : 00.00.04.95

• 인덱스 2개 생성 후 다시 100만건 입력한다
• 결과는 38.98초가 될 정도로 엄청 느려짐. 이것이 인덱스 2개의 영향력

truncate table target;
create index target_x1 on target(ename);
create index target_x2 on target(deptno, mgr);
insert into target
select * from source;

100000 개의 행이 만들어졌습니다.
경 과 : 00.00.38.98

무결성 제약과 DML 성능

데이터 무결성 규칙은 아래 4가지가 있다.

• 개체 무결성
• 참조 무결성
• 도메일 무결성
• 사용자 정의 무결성

이들 규칙을 애플리케이션으로 구현할 수 있지만, DBML에서 PK, FK, Check, Not Null 같은 제약을 설정하면 완벽히 데이터 무결성을 지킬 수 있다.

PK, FK 제약은 Check, Not Null 제약보다 성능에 더 큰 영향을 미친다. Check, Not Null과 달리 PK, FK는 데이터를 실제로 조회해봐야 하기 때문.

앞의 테스트를 이어 일반 인덱스, PK 제약을 모두 제거하고 100만을 입력하면 속도가 빨라짐.

drop index target_x1;
drop index target_x2;
alter table target drop primary key;
truncate table target;

insert into target
select * from source;

100000개의 행이 만들어졌습니다.
경 과 : 00:00:01.32

조건절과 DML 성능

아래는 조건절만 포함하는 가장 기본적인 DML문과 실행계획이다.

set autotrace traceonly exp
update emp set sal = sal * 1.1 where deptno = 40;

delete from emp where deptno = 40;

서브쿼리와 DML 성능

아래는 서브쿼리를 포함하는 DML 문과 실행계획이다.

update emp e set sal = sal * 1.1
where exists 
   (select 'x' from dept where deptno = e.deptno and loc = 'CHICAGO');

delete from emp e
where exists 
  (select 'x' from dept where deptno = e.deptno and loc = 'CHICACO');

insert into emp
select e.*
  from emp_t e
 where exists
  (select 'x' from dept where deptno = e.deptno and loc = 'CHICAGO')

Redo 로깅과 DML 성능

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

Redo 로그는 트랜잭션 데이터가 어떤 이유로 유실됐을 때, 트랜잭션을 재현하여 유실 이전 상태로 북구하는데 사용된다.

DML을 수행할 때마다 Redo 로그를 생성해야 해서 Redo 로깅이 DML 성능에 영향을 줌.

insert 작업에 Redo 로깅 생략 기능이 있는데 왜 제공 되는지 이유가 있다.

• Redo 로그 용도
  1. DB Recovery
  2. Cash Recovery
  3. Fast Commit

Undo 로깅과 DML 성능

과거에 롤백이라는 용어를 사용함. 오라클 9i부터 Undo 라는 용어를 씀

Redo

• 트랜잭션을 재현하여 과거를 현재 상태로 되돌림
• 트랜잭션을 재현하는데 필요한 정보를 로깅함

Undo

• 트랜잭션을 롤백함으로서 현재를 과거 상태로 되돌리는데 사용.
• 변경된 블록을 이전상태로 되돌리는데 필요한 정보를 로깅함.

DML을 수행할때 Undo는 필수적으로 로깅이 되는데, 이것은 DML 성능에 영향을 미치나 Undo를 안남기는 방법은 없다.

Lock과 DML 성능

Lock은 DML 성능에 매우 크고 직접적인 영향을 미친다.

• Lock을 필요이상으로 자주, 길게 사용하거나, 레벨이 높을수록 느려진다.
• Lock을 너무 적게, 짧게 사용하면 데이터 품질이 나빠진다.
• 그 사이를 잘 맞추려면 세심한 동시성 제어가 필요함

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

커밋과 DML 성능

DML을 끝내려면 커밋까지 완료해야한다.

DML이 Lock에 의해 블로킹 된 경우 커밋은 DML 성능과 직결된다.

모든 DBMS가 Fast Commit을 구현하고 있다. 갱신한 데이터가 아무리 많아도 커밋은 빨리 처리한다.

Fast Commit의 도움으로 커밋을 순간적으로 처리하긴 하지만 커밋은 가벼운 작업이 아니다.

DB 버퍼캐시

DB에 접속한 사용자를 대신해 모든 일을 처리하는 서버프로세스는 버버캐시를 읽고 쓴다.

버퍼캐시에서 변경된 블록을 모아 주기적으로 데이터파일에 일괄 기록하는 작업은 DBWR 프로세스가 함.

Redo 로그 버퍼

버퍼캐시는 휘발성이라 DBWR 프로세스가 Dirty 블록들을 데이터파일에 반영할때까지 불안한 상태이다.

하지만 버퍼캐시에 가한 변경사항을 Redo 로그에도 기록하면 된다. 버퍼캐시 데이터가 유실되어도 Redo 로그를 이용해 언제든 복구할 수 있다.

Redo 로그도 파일이다.

디스크 I/O는 느리다. Redo 로깅 성능 문제를 해결하기 위해 오라클은 로그 버퍼를 사용.

⇒ Redo 로그 파일에 기록하기 전 먼저 로그버퍼에 기록하는 방식임. 로그버퍼에 기록되고 난 후 LGWR 프로세스가 Redo 로그파일에 일괄(Batch) 기록함.

트랜잭션 데이터 저장 과정

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

오라클은 데이터를 변경하기 전에 항상 로그부터 기록한다.

서버 프로세스가 버퍼블록에서 데이터를 변경(②)하기 전에 Redo 로그버퍼에 로그를 먼저 기록(①)하는 이유다.

DBWR 프로세스가 Dirty 블록을 디스크에 기록(⑤)하기 전에 LGWR 프로세스가 Redo 로그파일에 로그를 먼저 기록(④)하는 이유이기도 하다.

이를 'Write Ahead Logging' 이라고 부른다.

잠자던 DBWR와 LGWR 프로세스는 '주기적으로'깨어나 각각 Dirty 블록과 Redo 로그버퍼를 파일에 기록한다.

LGWR 프로세스는 서버 프로세스가 커밋을 발행했다가 '신호를 보낼때도' 깨어나서 활동을 시작한다.

'적어도 커밋시점에는' Redo 로그버퍼 내용을 로그파일에 기록한다는 뜻이다.
이를 'Log Force at Commit' 이라고 부른다.

서버 프로세스가 변경한 버퍼블록들을 디스크에 기록하지 않았더라도 커밋 시점에 Redo 로그를 디스크에 안전하게 기록했다면, 그 순간부터 트랜잭션 영속성은 보장된다.

커밋 = 저장버튼

커밋은 서버 프로세스가 그때까지 했던 작업을 디스크에 기록하라는 명령어다.

저장을 완료할 때까지 서버 프로세스는 다음 작업을 진행할 수 없다.

Redo 로그버퍼에 기록된 내용을 디스크에 기록하도록 LGWR 프로세스에 신호를 보낸 후 작업을 완료했다는 신호를 받아야 다음 작업을 진행할 수 있다. Sync 방식.

LGWR 프로세스가 Redo 로그를 기록하는 작업은 디스크 I/O 작업이다.

그래서 커밋은 생각보다 느리다.

• 트랜잭션을 필요 이상으로 길게 정의하여 오랫동안 커밋하지 않는 것은 문제
• 너무 자주 커밋하는 것도 문제.

오래 커밋하지 않은 채 데이터를 계속 갱신하면 Undo 공간이 부족해져 시스템 장애 상황을 유발할 수 있음.

루프를 돌면서 건건이 커밋한다면, 프로그램 자체 성능이 매우 느려짐.

트랜잭션을 논리적으로 잘 정의함으로써 불필요한 커밋이 발생하지 않도록 구현해야 한다.

2022년 6월 20일

데이터베이스 Call과 성능

SQL은 3단계로 나누어 실행된다.

• Parse Call
  • SQL 파싱과 최적화를 수행하는 단계이다. SQL과 실행계획 라이브러리 캐시에서 찾으면 최적화 단계는 생략할 수 있다.
• Execute Call
  • 말그대로 SQL을 실행하는 단계이다. DML은 이 단계에서 모든 과정이 끝나지만 SELECT 문은 Fetch 단계이다.
• Fetch Call
  • 데이터를 읽어서 사용자에게 결과집합을 전송하는 과정으,로 SELECT문에서만 나타난다. 전송할 데이터가 많을 때는 Fetch Call로 나눌수 있다.

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

User Call

User Call은 네트워크를 경유해 DBMS 외부로부터 인입되는 Call이다.

DBMS 입장에서 사용자는 WAS이다. 3Tier 아키텍처에서 User Call은 WAS 서버에서 발생하는 Call이다.

Recursive Call

DBMS 내부에서 발생하는 Call

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

위의 두 Call은 Parse, Execute, Fetch 단계를 거친다. 데이터베이스는 Call이 많을수록 성능은 느릴 수 밖에 없다. User Call이 성능에 미치는 영향은 매우 크다.

절차적 루프 처리

create table source
as
select b.no, a.*
from (select * from emp where rownum <= 10) a,
    (select rownum as no from dual connect by level <= 100000) b;

create table target
as 
select * from source where 1 = 2;

• 생성한 source 테이블에 레코드 100만개 입력함.
• source 테이블을 읽어 100만번 루프 돌면서 target 테이블에 입력

set timing on

begin
    for s in (select * from source)
    loop
        insert into target values ( s.no, s.empno, s.ename, 
                                                                s.job, s.mgr, s.hiredate, 
                                                                s.sal, s.comm, s.deptno );
    end loop;

    commit;
end;

경    과 : 00:00:29:31

루프를 돌면서 건건히 call이 발생하지만, 네트워크를 경유하지 않은 Recursive Call이므로 29초만에 수행을 마침.

One SQL의 중요성

insert into target
    select * from source;

-- 01.46초

업무로직이 복잡해지면 절차적으로 처리할 수 밖에 없지만, 그렇지 않으면 One SQL로 구현하는게 좋다.

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

Array Processing 활용

One SQL은 구현하기 힘든데 Array Processing 쓰면 One SQL 안쓰고도 Call의 부하를 줄일 수 있다.

Array Processing 기능을 활용하면 한 번의 SQL (INSERT/UPDATE/DELETE) 다량의 레코드를 동시에 처리할 수 있다.

이는 네트워크를 통한 데이터베이스 Call을 줄이고, 궁극적으로 SQL 수행시간과 CPU 사용량을 획기적으로 줄여준다.

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

인덱스와 무결성 제약 조건은 DML 성능에 큰 영향을 끼친다.

근데 OLTP에서 이들 기능을 해제할 수 없다. 하지만 Batch 프로그램에서는 이 기능을 해제해서 큰 성능개선 효과를 얻을 수 있다.

책 예시 : 1000만건 입력하기

• PK(Unique, index) + 일반 인덱스 : 1분 19초

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

• PK 제거 + 일반 인덱스 Unusable : 5.84초
• 데이터 1000만건 있는 상태에서 PK 활성화 : 6.77초
• 데이터 1000만건 있는 상태에서 일반 인덱스 활성화 : 8.26초

총 : 20.87초

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

• PK Unique 제거 + PK index Unusable + x1 index Unusable : 5.53초
• 데이터 1000만건 있는 상태에서 일반 인덱스 활성화 : 7.24초
• PK 인덱스 Unique 활성화 : 0.06초

총 : 18.1초

• 데이터 1000만건 있는 상태에서 PK 활성화 : 5.27초

수정가능 조인 뷰

전통적인 방식 UPDATE

다음과 같이 첫번째 쿼리를 두번째 쿼리처럼 고칠 수 있다.

하지만 아래의 쿼리들 모두 전통적인 update 방식이라 완전한 비효율을 없앨 수 없다.

update 고객 c
set 최종거래일시 = (select max(거래일시) from   거래
                     where  고객번호 = c.고객번호
                     and 거래일시 >= trunc(add_months(sysdate, -1)))
  , 최근거래횟수 = (select count(*) from   거래
                     where  고객번호 = c.고객번호
                     and 거래일시 >= trunc(add_months(sysdate, -1)))
  , 최근거래금액 = (select sum(거래금액) from   거래
                     where  고객번호 = c.고객번호
                     and 거래일시 >= trunc(add_months(sysdate, -1)))
where exists (select 'x' from 거래 
               where 고객번호 = c.고객번호
               and 거래일시 >= trunc(add_months(sysdate, -1)));

update 고객 c
set (최종거래일시, 최근거래횟수, 최근거래금액) =
    (select max(거래일시), count(*), sum(거래금액)
     from 거래
     where 고객번호 = c.고객번호
     and 거래일시 >= trunc(add_months(sysdate, -1)))
where exists (select 'x' from 거래
              where 고객번호 = c.고객번호
              and 거래일시 >= trunc(add_months(sysdate, -1)));

update 고객 c
set (최종거래일시, 최근거래횟수, 최근거래금액) =
    (select max(거래일시), count(*), sum(거래금액)
     from 거래
     where 고객번호 = c.고객번호
     and 거래일시 >= trunc(add_months(sysdate, -1)))
where exists (select **/*+ unnest hash_sj */** 'x' from 거래
              where 고객번호 = c.고객번호
              and 거래일시 >= trunc(add_months(sysdate, -1)));

update 고객 c
set (최종거래일시, 최근거래횟수, 최근거래금액 ) =
     (select nvl(max(거래일시), c.최종거래일시)
            ,decode(count(*), 0, c.최근거래횟수, count(*))
            ,nvl(sum(거래금액), c.최근거래금액)
      from 거래
      where 고객번호 = c.고객번호
      and 거래일시 >= trunc(add_months(sysdate, -1)));

수정가능한 조인 뷰

• 조인뷰는 from절에 두 개 이상 테이블을 가진 뷰를 가리키며, 수정 가능 조인 뷰는 입력, 수정, 삭제가 허용되는 조인 뷰를 말한다.
• 1쪽 집합과 조인되는 M쪽 집합에만 입력, 수정, 삭제가 허용된다.
• 수정 가능 조인 뷰를 활용하면 전통적인 방식의 update문에서 참조 테이블을 두번 조인하는 비효율을 없앨 수 있다.

예시1

update
(select /*+ ordered use_hash(c) no_merge(t) */
            c.최종거래일시,  c.최근거래횟수, c.최근거래금액
            , t.거래일시,  t.거래횟수, t.거래금액
from (select 고객번호, max(거래일시) 거래일시, count(*) 거래횟수, sum(거래금액) 거래금액
            from 거래
            where 거래일시 >= trunc(add_months(sysdate, -1))
        group by 고객번호) t, 고객 c
where  c.고객번호 = t.고객번호
)
set 최종거래일시 = 거래일시
    , 최근거래횟수 = 거래횟수
    , 최근거래금액 = 거래금액 ;

예시2

아래와 같이 조인뷰를 통해 job = ‘CLERK’ 인 레코드 loc을 모두 ‘SEOUL’로 변경을 허용하면?

job = ‘CLERK’인 사원 뿐만 아니라 다른 job을 가진 사원의 부서 소재지까지 바뀐다. (개판!)

create table emp as select * from scott.emp;
create table dept as select * from scott.dept;

select e.rowid emp_rid, e.*, d.rowid dept_rid, d.dname, d.loc
from emp e, dept d
where e.deptno = d.deptno;

update emp_dept_view set loc = 'SEOUL' where job = 'CLERK'

EMPNO ENAME      JOB              SAL     DEPTNO DNAME          LOC
---------- ---------- --------- ---------- ---------- -------------- -------------
        7902 FORD       ANALYST         3000         20 RESEARCH       DALLAS
        7788 SCOTT      ANALYST         3000         20 RESEARCH       DALLAS
        7934 MILLER     **CLERK**           1300         **10** ACCOUNTING     NEW YORK
        7369 SMITH      **CLERK**            800         **20** RESEARCH       DALLAS
        7876 ADAMS      **CLERK**           1100         **20** RESEARCH       DALLAS
        7900 JAMES      **CLERK**            950         **30** SALES          CHICAGO
        7782 CLARK      MANAGER         2450         10 ACCOUNTING     NEW YORK
        7566 JONES      MANAGER         2975         20 RESEARCH       DALLAS
        7698 BLAKE      MANAGER         2850         30 SALES          CHICAGO
        7839 KING       PRESIDENT       5000         10 ACCOUNTING     NEW YORK
        7654 MARTIN     SALESMAN        1250         30 SALES          CHICAGO
        7844 TURNER     SALESMAN        1500         30 SALES          CHICAGO
        7521 WARD       SALESMAN        1250         30 SALES          CHICAGO
        7499 ALLEN      SALESMAN        1600         30 SALES          CHICAGO

예시3

아래 쿼리는 문제가 없어 보이지만 에러가 발생한다.

이유는 옵티마이저가 어느 테이블이 1쪽 집합인지 알 수 없기 때문이다.

이것은 DELETE, INSERT도 마찬가지다.

update emp_dept_view set comm = nvl(comm, 0) + (sal * 0.1) where sal <= 1500

=> ORA-01779: 키-보존된것이 아닌 테이블로 대응한 열을 수정할 수 없습니다.

올바른 예시

아래와 같이 Unique 인덱스를 생성해야 수정 가능 조인 뷰를 통한 입력, 수정, 삭제가 가능하다.

이와 같이 PK 제약을 설정하면 EMP 테이블은 ‘키-보존 테이블’이 되고 DEPT는 ‘비 키-보존 테이블’이 된다.

alter table dept add constraint dept_pk primary key(deptno);

update emp_dept_view set comm = nvl(comm, 0) + (sal * 0.1) where sal <= 1500;

키보존 테이블

키 보존 테이블이란, 조인된 결과 집합을 통해서도 중복 값 없이 unique하게 식별히 가능한 테이블,그리고 뷰에 rowid를 제공하는 테이블이다.

ORA-01779

• 11g 이하 버전에서 아래 1번쿼리 실행시 ORA-01779이 발생
• 이유는 EMP 테이블을 DEPTNO로 Group by 해서 DEPTNO 컬럼으로 조인한 DEPT 테이블은 키가 보존되는데 옵티마이저가 불필요한 제약을 가한 것.
• 10g 버전에서는 bypass_ujvc 힌트를 주면 제약을 피할 수 있다.
  (Updatable join view check 생략 지시 힌트)
• 11g 버전부터 이 힌트 사용불가. ⇒ Merge 문으로 바꿔줘야함.

-- 1번 쿼리
update
(select d.deptno, d.avg_sal d_avg_sal, e.avg_sal e_avg_sal
from (select deptno, round(avg(sal), 2) avg_sal from emp group by deptno) e
      , dept d
where d.deptno = e.deptno)
set d_avg_sal = e_avg_sal;

**> ORA-01779: 키-보존된것이 아닌 테이블로 대응한 열을 수정할 수 없습니다**

-- 2번 쿼리 (bypass_ujvc 힌트)
update /*+ bypass_ujvc */
(select d.deptno, d.avg_sal d_avg_sal, e.avg_sal e_avg_sal
 from (select deptno, round(avg(sal), 2) avg_sal from emp group by deptno) e
       , dept d
where d.deptno = e.deptno)
set d_avg_sal = e_avg_sal;

Merge문 활용

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

DW에 데이터 적재 작업을 효과적으로 지원하게 위해 오라클 9i부터 merge 문이 도입됨.

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

create table customer_delta
as
select * from customer
where mod_dt >= trunc(sysdate)-1
and   mod_dt <  trunc(sysdate);

• CUSTOMER_DELTA 테이블을 DW시스템으로 전송 (Transportation)
• 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);

Optional Clauses

update와 insert를 선택적으로 처리할 수 있다.

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, ...;

merge into customer t using customer_delta s on (t.cust_id = s.cust_id)
**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);

Conditional Operations

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

merge into customer t using customer_delta s on (t.cust_id = s.cust_id)
when matched then update
  set t.cust_id = s.cust_id, t.cust_nm = s.cust_nm, t.email = s.email, ...
  **where reg_dt >= to_char('20000101','yyyymmdd')**
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)
  **where reg_dt < trunc(sysdate);**

DELETE Clause

• 이미 저장된 데이터를 조건에 따라 지우는 기능도 제공한다.
• 아래의 merge문에서 update가 이루어진 결과로서 탈퇴일시(withdraw_dt)가 null이 아닌 레코드만 삭제된다.
• 탈퇴일시가 null이 아니었어도 merge문을 수행한 결과가 null이면 삭제하지 않는다.

merge into customer t using customer_delta s on (t.cust_id = s.cust_id)
when matched then update
  set t.cust_id = s.cust_id, t.cust_nm = s.cust_nm, t.email = s.email, ...
  **delete where t.withdraw_dt is not null** --탈퇴일시가 null이 아닌 레코드 삭제
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 Into 활용

• SQL 수행 빈도 개선
  • 저장하려는 레코드가 기존에 있던 것이면 update를 수행
  • 그렇지 않으면 insert를 수행하는 경우, SQL이 항상 두번씩 수행
    (select 한번, insert 또는 update 한 번)
  • merge 문을 활용하면 SQL이 한번만 수행된다
• 논리I/O발생을 감소하여 SQL 수행 속도 개선

Direct Path I/O 활용

OLTP는 기준성데이터, 특정 고객, 특정 상품, 최근 거래 등을 반복적으로 읽어 버퍼캐시가 성능 향상에 도움을 준다.

DW/OLAP 이나 배치 프로그램에서 사용하는 SQL은 주로 대량 데이터를 처리하기 때문에 버퍼캐시를 경유하는 I/O 매커니즘이 오히려 성능을 떨어뜨릴 수 있다.

오라클은 버퍼캐시 경유하지 않고 곧바로 데이터 블록 읽고 쓸 수 있는 Direct Path I/O 기능을 제공한다.

Direct Path I/O

대량의 데이터를 읽고 쓰는데 버퍼캐시를 탐색하면 더 안좋은 이유

• 버퍼캐시에서 블록을 찾을 가능성이 거의 없음.
• 대량의 블록을 건건이 디스크로부터 버퍼캐시에 적재하고 읽어야 하는 부담도 크다.
• 버퍼캐시에 적재해도 재사용 할 가능성이 거의 없다.
• 재사용성 적은 데이터가 버퍼캐시 점유하면 다른 프로그램에 성능에도 안좋다.

Direct Path I/O가 작동하는 경우

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

Direct Path Insert

INSERT가 느린 이유

1. 데이터를 입력할 수 있는 블록을 Freelist에서 찾는다. 테이블 HWM(High water mark) 아래쪽에 있는 블록 중 데이터 입력이 가능한(여유공간이 있는)블록을 목록으로 관리하는데 이를 ‘Freelist’ 라고 한다.
2. Freelist에서 할당받은 블록을 버퍼캐시에서 찾는다
3. 버퍼캐시에 없으면 데이터파일에서 읽어 버퍼캐시에 적재
4. insert 내용을 Undo 세그먼트에 기록
5. insert 내용을 Redo 세그먼트에 기록

Direct Path Insert 방식을 쓰면, 대량 데이터를 일반 INSERT 보다 빨리 입력 가능.

Direct Path Insert 사용법

• INSERT … SELECT 문에 append 힌트 사용
• parallel 힌트를 이용해 병렬 모드로 insert
• direct 옵션을 지정하고 SQL*Loader(sqlldr)로 데이터 적재
• CTAS(create table as select) 문 수행

Direct Path Insert 방식이 빠른 이유

• Freelist를 참조하지 않고 HWM 바깥 영역에서 데이터 순차 입력함.
• 블록을 버퍼캐시에서 탐색하지 않음
• 버퍼캐시에 적재하지 않고, 데이터파일에 직접기록
• Undo 로깅 안함
• Redo 로깅을 안하게 할 수 있다.
  방법 : alter table t NOLOGGING;

Array Processing 도 Direct Path Insert 방식으로 처리가능함. append_values 힌트 사용하면 된다.

Direct Path Insert 사용하면 주의점 두가지

• 이 방식은 성능이 빨라지지만 Exclusive 모드 TM Lock이 걸린다.
  그래서 커밋하기 전까지 다른 트랜잭션은 해당 테이블에 DML을 수행할 수 없다.
• Freelist를 조회하지 않고 HWM 바깥영역에 입력하므로 테이블 여유 공간이 있어도 재활용하지 않는다.
  • 과거 데이터를 주기적으로 DELETE해서 여유공간이 생겨도 이 방식으로만 INSERT 하는 테이블은 사이즈가 계속 늘어난다.
  • DROP해야 공간 반환이 제대로 이루어짐.
  • 비 파티션 테이블이면 주기적으로 Reorg 작업 수행

병렬 DML

update, delete는 기본적으로 Direct Path Write가 불가능함.

방법은 DML로 처리해야 한다.

병렬처리는 대용량 데이터가 전제이므로 오라클은 병렬 DML에 항상 Direct Path Write 방식을 사용해야 한다.

DML을 병렬로 처리하면 병렬 DML을 활성화 해야함.

alter session enable parallel dml;

각 DML 문에 parallel 또는 full parallel 힌트 사용.
그러면 대상 레코드 찾는 작업은 물론 데이터 추가,변경,삭제도 병렬로 진행한다

insert **/*+ parallel(c 4) */** into 고객 c
select /*+ full(o) parallel(o 4) */ * from 외부가입고객 o;

update **/*+ full(c) parallel(c 4) */** 고객 c set 고객상태코드 = 'WD'
where 최종거래일시 < '20100101';

delete **/*+ full(c) parallel(c 4) */** from 고객 c
where 탈퇴일시 < '20100101';

힌트를 썻는데 병렬 DML 활성화 안하면 찾는 작업은 병렬로 하나, 추가/변경/삭제는 QC가 혼자 담당해서 병목생긴다.

병렬 insert는 append 힌트 지정 안해도 direct path insert 방식을 쓴다.

그러나 혹시나 대비하여 append 힌트를 쓰는게 좋다.

12C부터는 enable_parallel_dml 힌트도 지원한다.

병렬 DML이 잘 작동하는지 확인하는 방법

DML 작업을 각 병렬 프로세스가 잘 처리하는지 QC가 처리하는지는 실행계획에서 확인 가능.

파티션을 활용한 DML 튜닝

파티션을 쓰면 대량 추가 변경 삭제 작업을 빠르게 처리할 수 있다.

테이블 파티션

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

파티션이 필요한 이유

• 관리적 측면 : 파티션 단위 백업, 추가, 삭제, 변경 ⇒ 가용성 향상
• 성능적 측면 : 파티션 단위 조회 및 DML, 경합 또는 부하 분산

파티션에는 Range, Hash, List 세 종류가 있다.

Range 파티션

가장 기초적인 방식의 파티션.

주로 날짜 컬럼을 기준으로 파티셔닝한다.

create table 주문(주문번호 number, 주문일자 varchar2(8), 고객id varchar2(5)
                , 배송일자 varchar2(8, 주문금액 number, … )
partition by range(주문일자) (
      partition p2017_q1 values less than (‘20170401’)
    , partition p2017_q2 values less than (‘20170701’)
    , partition p2017_q3 values less than (‘20171001’)
    , partition p2017_q4 values less than (‘20180101’)
    , partition p2018_q1 values less than (‘20180401’)
    , partition p9999_mx values less than (maxvalue) --> 주문일자 >= '20180401'
);

위와 같은 파티션 테이블에 값을 입력하면 각 레코드를 파티션 키 값에 따라 분할 저장하고, 읽을때도 검색조건을 만족하는 파티션만 골라 읽어줄 수 있어 이력성 데이터를 Full Scan 방식으로 조회할 때 성능을 크게 향상시킨다.

부관주기 정책에 따라 과거 데이터가 저장된 파티션만 백업하고 삭제하는 등 데이터 관리 작업을 효율적이고 빠르게 수행할 수 있는 장점도 있음.

테이블 파티션에 대한 SQL 성능 향상 원리

파티션 Pruning이 답이다.

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

select * from 주문 where 주문일자 >= '20120401' and 주문일자 <= '20120630'

위 조건절에 해당하는 데이터는 1200만건중 25%에 해당하는 300만건이라고 치면,
인덱스 타고 랜덤 액세스하면 더 느리다. 그런데 테이블 전체 스캔은 사이즈가 너무커 부담이다.

이때 테이블 파티션을 사용하면 Full Scan 해도 전체가 아닌 일부라서 일부 파티션 세그먼트만 읽고 멈출 수 있다.

파티션과 병렬 처리가 만나면 그 효과는 배가 된다.

해시 파티션

• 파티션 키 값을 해시 함수에 입력해서 반환받은 값이 같은 데이터를 같은 세그먼트에 저장하는 방식
• 파티션 개수만 사용자가 결정하고 데이터를 분산하는 알고리즘은 오라클 내부 해시함수가 결정한다.

해시 파티션은 고객ID처럼 변별력이 좋고 데이터 분포가 고른 컬럼을 파티션 기준으로 선정해야 효과적이다. 아래는 고객ID 기준으로 고객 테이블을 해시 파티셔닝하는 방법을 예시한다.

create table 고객(
      고객id varchar2(5)
    , 고객명 varchar2(10)
    , … 
)
partition by hash(고객id) partitions 4;

• 검색할 때는 조건절 비교 값(상수 또는 변수)에 똑 같은 해시 함수를 적용함으로써 읽을 파티션을 결정한다.
• 해시 알고리즘 특성상 등치(=) 조건 또는 IN-List 조건으로 검색할 때만 파티션 Prning이 작동한다.

리스트 파티션

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

아래는 지역분류 기준으로 인터넷매물 테이블을 리스트 파티셔닝하는 방법을 예시한다.

create table 인터넷매물(
      물건코드 varchar2(5)
    , 지역분류 varchar2(4)
    , …
)
partition by list(지역분류) (
      partition p_지역1 values (‘서울’)
    , partition p_지역2 values (‘경기’. ‘인천’)
    , partition p_지역3 values (‘부산’, ‘대구’, ‘대전’, ‘광주’)
    , partition p_기타 values (default) -> 기타 지역
);

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

하지만 리스트 파티션은 순서와 상관없이 불연속적인 값의 목록에 의해 결정된다.

해시 파티션은 오라클이 정한 해시 알고리즘에 따라 임의로 분할함.

반면 리스트 파티션은 사용자가 정의한 논리적인 그룹에 따라 분할함.

💡 업무적인 친화도에 따라 그룹핑 기준을 정하되, 될 수 있으면 각 파티션에 값이 고르게 분산되도록 해야 한다.

인덱스 파티션

인덱스 파티션은 테이블 파티션과 맞물려 다양한 구성이 있다.

테이블 파티션 구성

• 비파티션 테이블
• 파티션 테이블

인덱스 파티션 구성

• 로컬 파티션 인덱스
• 글로벌 파티션 인덱스
• 비파티션 인덱스

로컬 파티션 인덱스

• 로컬 파티션 인덱스는 별도 색인을 만드는 것과 같음. 줄여서 “로컬 인덱스” 라고도 함.
• 각 테이블 파티션과 인덱스 파티션이 서로 1:1 대응 관계가 되도록 오라클이 자동 관리 파티션 인덱스이다.
• 로컬 파티션 인덱스를 만들때는 CREATE INDEX 문 뒤에 LOCAL 옵션을 추가하면 된다.

create index 주문_x01 on 주문 (주문일자, 주문금액) LOCAL;
create index 주문_x02 on 주문 (고객ID, 주문일자) LOCAL;

• 인덱스 파티션은 테이블 파티션 속성을 그대로 상속받는다.
• 테이블 파티션 키가 주문 일자면 인덱스 파티션키도 주문 일자가 됨.
• 테이블 파티션 구성을 변경(add, drop, exchange 등)하더라도 인덱스 재생성 할 필요가 없음.
• 변경작업이 순식간에 끝나고 피크 시간대만 피하면 서비스 중단하지 않고도 작업가능
• 로컬 인덱스의 장점은 관리 편리성

글로벌 파티션 인덱스

• 파티션을 테이블과 다르게 구성한 인덱스
• 테이블 파티션과 독립적인 구성(파티션 키, 파티션 기준값 정의)을 갖는다.
• (=같은말) 파티션 유형이 다르거나, 파티션 키가 다르거나, 파티션 기준값 정의가 다른 경우임.
• 비 파티션 테이블이어도 인덱스는 파티셔닝 가능.
• 만들때는 CREATE INDEX 문 뒤에 GLOBAL 옵션을 추가하고 파티션을 정의하면 된다.

create index 주문_x03 on 주문 (주문금액, 주문일자) GLOBAL
partition by range(주문금액) (
    partition P_01 values less than (100000)
    partition P_MX values less than (MAXVALUE) --> 주문금액 >= 100000
);

• 글로벌 파티션 인덱스는 테이블 파티션 구성을 변경하면 Unusable 되어서 인덱스를 바로 재생성 해줘야 함. 그동안 해당 테이블을 사용하는 서비스는 중단해야 한다.
• 글로벌 파티션 인덱스를 로컬 인덱스처럼 구성해도 파티션 옵션은 글로벌이라서 오라클이 관리해주지 않는다.

비파티션 인덱스

• 파티셔닝 하지 않은 인덱스
• 만드는 방법은 그냥 CREATE INDEX 한다.
• 그림과 같이 여러 테이블 파티션을 가리킨다. 그래서 ‘글로벌 비파티션 인덱스’라고도 부름.
• 비파티션 인덱스는 테이블 구성이 변경되는 순간 Unusable 상태로 바뀌어서 인덱스를 재생성해줘야 한다. 그동안 해당 테이블을 사용하는 서비스는 중단해야 한다.

Prefixed vs Nonprefixed

• Prefixed : 인덱스 파티션 키 컬럼이 인덱스 키 컬럼 왼쪽 선두에 위치한다.
• Nonprefixed : 인덱스 파티션 키 컬럼이 인덱스 키 컬럼 왼쪽 선두에 위치하지 않는다. 파티션 키가 인덱스 컬럼에 아에 속하지 않을때도 있음.

글로벌 파티션 인덱스는 Prefixed만 지원한다. 로컬 파티션 인덱스는 Prefixed, Nonprefixed 다 가능하다.

그래서 비파티션을 포함해 4가지 유형으로 정리할 수 있다.

• 로컬 Prefixed 파티션 인덱스
• 로컬 Nonprefixed 파티션 인덱스
• 글로벌 Prefix 파티션 인덱스
• 비파티션 인덱스

중요한 인덱스 파티션 제약

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

• 파티션 키가 인덱스 컬럼에 포함되어야 하는 조건은 DML 성능 보장을 위해 당연히 있어야 할 제약조건임.
• 파티션 키 조건 없이 PK 인덱스로 액세스 하는 수많은 쿼리 성능을 위해 필요한 제약조건이다.
• 이 제약으로 인해 PK 인덱스를 로컬 파티셔닝하지 못하면, 파티션 Drop, Truncate, Exchange, Split, Merge 같은 파티션 구조 변경 작업도 쉽지 않음. (Unusable 되기 때문)
• 서비스 중단 없이 파티션 구조를 빠르게 변경하려면 PK를 포함한 모든 인덱스는 로컬 파티션 인덱스여야 한다.

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

인덱스는 DML 성능에 큰 영향을 미침. 그래서 인덱스 없이 대량의 데이터 작업 후, 인덱스 생성이 더 빠르고 그런 방법을 많이 쓴다.

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

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

1. 임시 테이블을 생성, nologging 모드로 생성

create table 거래_t
nologging 
as
select * from 거래 where 1 = 2;

1. 거래 데이터를 읽어 임시 테이블에 입력하면서 상태코드 값을 수정한다.

insert /*+ append */ into 거래_t
select 고객번호, 거래일자, 거래순번 ... 
    ,(case when 상태코드 <> 'ZZZ' then 'ZZZ' else 상태코드 end) 상태코드
from 거래
where 거래일자 < '20150101';

1. 임시테이블에 원본 테이블과 같은 구조로 인덱스 생성 nologging 모드
   2014. 12. 파티션과 임시 파티션을 Exchange 한다.

alter table 거래
exchange partition p201412 with table 거래_t
including indexes without validation;

1. 임시 테이블 Drop
2. nologging 한 파티션 logging 모드 전환

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

DELETE가 느린 이유는 여러 부수적인 작업을 수반해야해서 느리다.

특히 인덱스 레코드를 찾아 삭제하는 작업 부담이 크다.

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

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

삭제 조건절 컬럼 기준으로 파티셔닝 되어 있고, 로컬 파티션이라면 1줄로 대량 데이터를 삭제할 수 있다.

alter table 거래 drop partition p201412;

-- 오라클 11g 버전 : 대상 파티션 지정
alter table 거래 drop partition for('20141201');

파티션 Truncate를 이용한 대량 데이터 삭제

거래일자 조건에 해당하는 데이터를 일괄 삭제하지 하지 않고 다른

1. 임시 테이블을 생성하고 남길 데이터만 복제
2. 삭제 대상 테이블 파티션을 Truncate
3. 임시 테이블에 복제해둔 데이터를 원본 테이블에 입력
4. 임시 테이블 Drop

서비스 중단 없이 파티션 Drop, Turncate 하기 위한 조건

• 파티션 키와 커팅 기준 컬럼이 일치해야 함.
  • ex) 파티션 키와 커팅 기준 컬럼이 모두 ‘신청일자’
• 파티션 단위가 커팅 주기가 일치해야 함.
  • ex) 월 단위 파티션을 월 주기로 커팅
• 모든 인덱스가 로컬 파티션 인덱스여야 함.
  • ex) 파티션 키는 ‘신청일자’, PK는 ‘신청일자 + 신청순번’
  • PK인덱스는 지금처럼 삭제 기준 컬럼이 인덱스 구성 컬럼이어야 로컬 파티셔닝 가능

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

비파티션 테이블일 때

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

1. 테이블을 nologging 모드
2. 인덱스 Unusable 상태로 전환
3. 대량 데이터 입력
4. 인덱스 재생성
5. logging 모드로 전환

파티션 테이블일 때

테이블이 파티셔닝 되어 있고, 인덱스도 로컬 파티션이면 파티션 단위로 인덱스 재생성 할 수 있다.

1. 작업 대상 테이블을 nologging 모드
2. 작업 대상 테이블 파티션과 매칭되는 인덱스 파티션을 Unusable 상태로 전환
3. 대량 데이터 입력
4. 인덱스 파티션 재생성
5. 작업 파티션을 logging 모드로 전환

Lock과 트랜잭션 동시성 제어

Lock은 데이터베이스의 특징을 결정짓는 가장 핵심적인 매커니즘.

자신이 쓰는 DB의 고유 Lock 매커니즘을 이해하지 못하면, 고품질, 고성능 애플리케이션을 구축하기 어렵다.

트랜잭션 동시성 제어도 DB 개발자라면 알아야 함.

오라클 Lock

오라클은 공유 리소스와 사용자 데이터를 보호할 목적으로 다음과 같은 종류의 Lock을 사용함.

• DML Lock
  • 애플리케이션 측면에서 가장 중요하게 다룰 Lock
  • 다중 트랜잭션이 동시에 액세스하는 사용자 데이터의 무결성을 보호해준다.
  • Table Lock과 Row Lock이 있음
• DDL Lock
• 래치
  • SGA에 공유된 각종 자료구조를 보호하기 위해 사용됨.
• 버퍼 Lock
  • 버퍼 블록에 대한 액세스 직렬화하기 위해 사용
• 라이브러리 캐시 Lock/Pin
  • 라이브러리 캐시에 공유된 SQL 커서와 PL/SQL 프로그램을 보호하기 위하 사용
• 기타 등등 Lock

DML Row Lock

• 두 개의 동시 트랜잭션이 같은 로우를 변경하는 것을 방지.
• 하나의 로우를 변경하려면 로우 Lock을 먼저 설정해야 함.
• UPDATE, DELETE 할 때 다른 트랜잭션이 UPDATE, DELETE 할 수 없음.
• INSERT에 대한 로우 Lock 경합은 Unique 인덱스가 있을때 발생.
  • Unique 인덱스가 있는 상황에서 두 트랜잭션이 같은 값을 입력하려고 할때 경합 발생
  • 이 경우, 후행 트랜잭션은 기다렸다 선행 트랜잭션이 커밋하면 INSERT 실패하고 롤백한다.
  • 두 트랜잭션이 다른 값을 입력하거나 Unique 인덱스가 아예 없으면 경합은 발생하지 않는다.

MVCC 모델

• MVCC 모델을 사용하는 오라클은 select 문에 로우 lock을 사용하지 않음. (postgresql도 그럴듯)
• MVCC 모델을 사용하면 DML과 SELECT는 서로 진행을 방해하지 않음.
• 오라클은 DML과 SELECT는 서로 진행을 방해하지 않는다. SELECT 끼리도 마찬가지다.
• DML 끼리는 서로를 방해할 수 있다.

MVCC 모델이 아닌 DBMS

• MVCC 모델을 사용하지 않으면 SELECT문에 공유 lock을 사용함.
• 공유 lock끼리는 호환이 되나, 공유 lock과 배타적 lock은 호환되지 않아, DML과 SELECT는 서로 방해될 수 있다.
• 다른 트랜잭션이 읽고 있는 로우를 변경하려면 다음 레코드로 이동할 때까지 기다려야 함. 그리고 다른 트랜잭션이 변경 중인 로우를 읽으려면 커밋할 때까지 기다려야 한다.

DML 로우 Lock에 대한 성능 저하를 방지하려면, 온라인 트랜잭션을 처리하는 주간에 Lock을 필요이상으로 오래 유지하지 않도록 커밋 시점을 조절해야 한다.

DML 테이블 Lock (TM Lock)

오라클은 DML 로우 Lock을 설정하기 전에 테이블 Lock을 먼저 설정함.

현재 트랜잭션이 갱신중인 테이블 구조를 다른 트랜잭션이 변경하지 못하게 막기위함.

Lock 모드간 호환성 정리

• RS : row share
• RX : row exclusive
• S : share
• SRX : share row exclusive
• X : exclusive

테이블 lock이라고 하면 테이블 전체 lock 걸린다고 생각하기 쉬운데, 그게 아니라 자신이 해당 테이블에서 현재 작업을 수행중인지 알리는 일종의 플래그다.

그래서 여러 테이블 lock 모드가 있다. 어떤 모드 사용했는지에 따라 후행 트랜잭션이 할수 있는 작업 범위가 결정된다.

대상 리소스가 사용중일 때 진로 선택

lock 얻으려는 리소스가 사용중일 때, 프로세스는 3가지 방법 중 하나를 택함.

1. lock이 해제될 때까지 기다린다.
2. 일정 시간만 기다리다 포기
3. 기다리지 않고 포기

그러나 NOWAIT 키워드를 사용하면 바로 작업 포기시킬 수 있다.

lock table emp in exclusive mode NOWAIT

Lock을 푸는 열쇠, 커밋

블로킹은 선행 트랜잭션이 결정한 Lock 때문에 후행 트랜잭션이 작업못하고 멈춘 상태이다.

해소 방법은 커밋 또는 롤백 뿐이다.

데드락은 두 트랜잭션이 각각 특정 리소스에 Lock을 설정한 상태에서 맞은편 트랜잭션이 lock을 설정한 리소스에 또 lock을 설정하려고 진행하는 상황을 말함.

오라클에서 교착상태가 발생하면, 이를 먼저 인지한 트랜잭션이 문장 수준 롤백을 진행한 후에 아래 에러 메시지를 던진다.

ORA-00060: deadlock detected while waiting for resource

교착상태를 발생시킨 문장 하나만 롤백

교착상태가 해소되어도 블로킹 상태라 트랜잭션은 커밋 또는 롤백을 결정해야 함.

프로그램 내에서 예외처리(커밋 또는 롤백)를 하지 않는다면, 대기 상태를 지속하게 되므로 주의

4가지 커밋 명령

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

트랜잭션 동시성 제어

비관적 동시성 제어

• 사용자들이 같은 데이터를 동시에 수정할 것을 가정함.
• 한 사용자가 데이터를 읽는 시점에 lock을 걸고 조회 or 갱신처리가 완료될 때까지 유지.
• lock은 다른 사용자들이 같은 데이터 수정 못하게 만들어서 비관적 동시성 제어는 잘못사용시 동시성이 나빠진다.
• 비관적 동시성 제어는 자칫 시스템 동시성을 심각하게 떨어뜨릴 우려가 있지만 FOR UPDATE 에 WAIIT 또는 NOWAIT 옵션을 사용하면 LOCK을 얻기 위해 무한정 기다리지 않아도 됨.
• 그리고 다른 트랜 잭션에 의해 LOCK이 걸렸을 때 Exception을 만나게 되어 트랜잭션을 종료할 수 있다 ⇒ 오히려 동시성 증가

낙관적 동시성 제어

• 사용자들이 같은 데이터를 동시에 수정하지 않을 것을 가정함.
• 데이터를 읽을 때, lock을 설정하지 않는다.
• 읽는 시점에 lock을 사용하진 않았지만, 데이터를 수정하고자 하는 시점에 앞서 읽은 데이터가 다른 사용자에 의해 변경되었는지 체크해야 한다.

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

저자의 불만?

보통 개발자들 낙관적으로 코딩하는데, 그러지말고 동시성 제어좀 해라.

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

• FOR UPDATE 사용을 두려워 하지 말자
• 다중 트랜잭션이 존재하는 데이터베이스 환경에서 공유자원에 대한 액세스 직렬화는 필수
• 데이터 변경할 목적으로 읽으면 당연히 LOCK을 걸어야 함
• 금융권은 필수! FOR UPDATE를 알고 쓰고 필요한 상황이면 정확히 사용하고, 동시성이 나빠지지 않게 WAIT 또는 NOWAIT 옵션을 활용한 예외처리를 잘하자
• 불필요하게 LOCK을 오래 유지하지 말고, 트랜잭션의 원자성을 보장하는 범위 내에서 가급적 빨리 커밋하자.
• 꼭 주간에 수행할 필요가 없는 배치 프로그램은 야간 시간대에 수행하자.
• 낙관적, 비관적 동시성 제어를 같이 사용하는 방법도 있다. ⇒ 낙관적 쓰다 어? 잘못됐는데? 비관적이여야지 흐히힣
• 동시성 향상하고자 할 때 SQL 튜닝은 기본! 효율적인 인덱스 구성, 데이터량에 맞는 조인 메소드 선택!
• LOCK에 대한 고민은 트랜잭션 내 모든 SQL을 완벽히 튜닝하고 나서 해도 늦지 않다.

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

INSERT, UPDATE, DELETE, MERGE 중 INSERT가 가장 중요하고 튜닝요소가 많음.

• 채번 방식에 따른 성능 차이가 매우 크기 때문
• 채번 방식
  • 채번 테이블
  • 시퀀스 오브젝트
  • MAX + 1 조회

채번 테이블

각 테이블 식별자의 단일 컬럼 일련번호 또는 구분 속성별 순번을 채번하기 위해 별도 테이블을 관리하는 방식

채번 레코드를 읽어 1 더한 값으로 변경 → 그 값을 새로운 레코드 입력하는데 사용.

• 장점
  • 범용성이 좋다
  • INSERT 과정에서 중복 레코드 발생에 대비한 예외 처리에 크게 신경쓰지 않아도 되어 채번함수만 잘 정리하면 편리하게 사용 가능.
  • INSERT 과정에 결번을 방지할 수 있음.
  • PK가 복잡 컬럼일 때도 사용할 수 있음.
• 단점
  • 다른 채번 방식에 비해 성능이 안좋음.
    • 채번 레코드 변경을 위한 로우 LOCK이 심함
    • 동시 INSERT가 아주 많으면 채번 레코드, 채번 테이블 블록도 경합이 발생
    • 서로 다른 레코드를 변경하는 프로세스도 경합이 일어날 수 있음.

동시 INSERT가 많은 테이블은 사용하기 어렵다.

시퀀스 오브젝트

• 시퀀스의 가장 큰 장점
  • 빠름
  • INSERT 과정에서 중복 레코드 발생에 대비한 예외처리에 크게 신경 안써도 됨.
  • 개발팀이 사용하기 편리함
• 단점
  • 시퀀스 채번 과정에서 발생하는 LOCK이 있음.
  • 기본적으로 PK가 단일컬럼일 때만 사용 가능하다.
    (PK가 복합 컬럼일 때도 사용할 수 있지만, 각 레코드를 유일하게 식별하는 최소 컬럼으로 PK를 구성해야 하는 최소성 요건을 위배함.)
  • 신규 데이터를 입력하는 과정에서 결번이 생길 수 있음.
    • 원인1 : 시퀀스 채번 이후 트랜잭션 롤백하는 경우가
    • 원인2 : CACHE 옵션을 설정한 시퀀스가 캐시에서 밀려나는 경우

시퀀스 오브젝트도 결국은 테이블이다.
⇒ 값을 읽고 변경하는 과정에서 LOCK이 발생함.
시퀀스 LOCK에 의한 성능 이슈가 있지만, 캐시 사이즈를 적절히 설정하면 가장 빠른 성능을 제공!

시퀀스 LOCK

오라클 시퀀스 오브젝트에 사용하는 LOCK 3가지

• 로우 캐시 LOCK
  • 로우 캐시는 SGA의 구성요소이므로 정보를 읽고 쓸때 액세스를 직렬화하기 위한 LOCK
• 시퀀스 캐시 LOCK
  • 시퀀스 캐시도 공유 캐시에 위치하며, 시퀀스 캐시 값을 얻을때도 액세스 직렬화가 필요하다.
  • = SQ LOCK
• SV LOCK
  • 데이터베이스 하나에 인스턴스가 여러 개인 RAC 환경에선, 인스턴스마다 시퀀스 캐시를 따로 갖는다. 인스턴스 간에는 번호 순서를 기본적으로 보장하지 않는다.
  • RAC에서 노드 간에 순서가 보장된 상태로 Sequence.nextval을 호출하는 동안 획득한다. CACHE + ORDER 속성을 부여한 Sequence 에서 사용된다.
  • ORDER 속성이 부여된 Sequence 에 대해서 nextval을 호출하면 SSX 모드로 SV 락을 획득해야 한다.
  • RAC란?출처 : t.ly/fKZN
  • 오라클 RAC은 Oracle Real Application Clusters의 약자로서, 2001년 미국 오라클사가 개발한 클러스터링 및 고가용성 을 위한 옵션이다.

MAX + 1 조회

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

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

• 장점
  • 시퀀스 또는 별도의 채번 테이블을 관리하는 부담이 없다.
  • 동시 트랜잭션 충돌이 많지 않으면 성능이 빠름
  • PK가 복합 컬럼인 경우도 사용 가능.
• 단점
  • 레코드 중복에 대비한 세밀한 예외처리 필요.
  • 다중 트랜잭션에 의한 동시 채번이 심하면 시퀀스보다 성능이 많이 나빠질 수 있다.
    ⇒ 레코드 중복에 의한 로우 경합 때문

lock 경합 요소를 고려한 채번 방식 선택 기준

1. 다중 트랜잭션에 의한 동시 채번이 많지 않으면 아무거나 사용해도 상관 없음.
2. 채번 테이블이나 시퀀스 오브젝트 관리가 부담스러우면 MAX + 1 추천
3. 다중 트랜잭션에 의한 동시 채번이 많고 PK가 단일컬럼 일련번호라면 시퀀스 방식이 좋음.
4. 다중 트랜잭션에 의한 동시 채번이 많고 PK 구분 속성에 값 종류 갯수가 많으면, 중복에 의한 로우 LOCK 경합 및 재실행 가능성이 낮음. ⇒ MAX + 1 추천
5. 다중 트랜잭션에 의한 동시 채번이 많고, PK 구준 속성에 값 종류가 적으면 순환 옵션을 가진 시퀀스 오브젝트가 좋음.

시퀀스보다 좋은 솔루션

• 한 개 이상 구분 속성과 함께, 뒤쪽 순번 대신 데이터 입력일시를 두는 방식으로 PK 구조 설계하면, 채번 또는 INSERT 과정에서 생기는 LOCK 이슈를 거의 해소할 수 있다.
• 채번 과정을 생략하고 SYSDATE 또는 SYSTIMESTAMP 추천

정보 생명 주기를 효과적으로 관리하는데 있어 데이터 삭제는 매우 중요하다.

DB가 빠르게 입력하는 만큼 빨리 삭제할 수 있어야 유지보수에 좋다. 그래서 빠르게 삭제할 수 있는 구조로 설계해야 한다.

그래서 입력일시를 PK에 포함하는 것은 의미 있는 일이다. “파티션을 이용한 대량 DELETE 튜닝”처럼 서비스 중단 없이 파티션 단위로 커팅하려면 PK에 입력일시라는 삭제 기준이 있어야 함.

인덱스 블록 경합

• INSERT 성능이 빨라도 문제! 이유는 인덱스 경합 때문.
• 채번 과정을 생략하는 순간부터 인덱스 블록 경합이 일어남. MAX + 1 방식에서 자주 일어난다.
• 인덱스 블록 경합 해소 방법
  • 인덱스를 “해시 파티셔닝” 하는 것이다.
    ⇒ 값이 순차적으로 증가해도 해시 함수 리턴값이 각각 달라 다른 파티션에 입력되기 때문.
  • 인덱스를 리버스키 인덱스로 전환하는 방법