6회차 DML 튜닝 - 1
1. 기본 DML 튜닝
1.1 DML 성능에 영향을 미치는 요소
✔️ 인덱스
✔️ 무결성 제약
✔️ 조건절
✔️ 서브쿼리
✔️ Redo 로깅
✔️ Undo 로깅
✔️ Lock
✔️ 커밋
테이블에 레코드 입력(Insert) 시 인덱스에도 입력을 해야하며, 테이블은 Freelist를 통해 입력할 블록을 할당받지만 인덱스는 정렬된 자료구조이므로 수직적 탐색을 통해 입력할 블록을 찾아야한다.
Delete 할 때도 마찬가지로 테이블에서 레코드 하나를 삭제 시 인덱스 레코드를 모두 찾아서 삭제해 줘야 한다.
Update 시는 변경된 컬럼을 참조하는 인덱스만 찾아서 변경을 해주되 대신 테이블에서 한 건 변경 시 인덱스에는 삭제 후 삽입하는 식으로 두 개의 오퍼레이션이 발생한다.
⭐ Freelist: 테이블마다 관리하는 여유공간이 있는 블록 목록
인덱스 개수가 DML 성능에 미치는 영향이 매우 크며, 핵심 트랜잭션 테이블에서 인덱스를 하나라도 줄이면 TPS(Transaction Per Second)는 그만큼 향상된다.
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);
위와 같이 레코드가 100만개 있는 SOURCE 테이블과 비어있는 TARGET 테이블이 존재할 때 TARGET 테이블에 PK 인덱스 하나만 생성한 상태에서 SOURCE 테이블을 읽어 100만개 입력 시 4초만에 수행을 마치는걸 확인할 수 있다.
set timing on;
insert into target select * from source;
1000000 개의 행이 만들어졌습니다.
경 과: 00:00:04.95
만약 인덱스를 두 개 더 생성하고나서 다시 100만 건을 입력하게 되면 수행시간이 38.98초로 인덱스 하나일 때 보다 여덟 배나 느려졌다.
truncate table target;
create index target_x1 on target(ename);
create index target_x2 on target(deptno, mgr);
insert into target
select * from source;
1000000 개의 행이 만들어졌습니다.
경 과: 00:00:38.98
😎 데이터 무결성 규칙
✔️ 개체 무결성(Entity Integrity)
✔️ 참조 무결성(Referential Integrity)
✔️ 도메인 무결성(Domain Integrity)
✔️ 사용자 정의 무결성(또는 업무 제약 조건)
위의 규칙을 애플리케이션에서 구현할 수도 있겠지만 DBMS에서 PK, FK, Check, Not Null과 같은 제약(Constraint)을 설정하면 더 완벽하게 데이터 무결성을 지킬 수 있다.
Check, Not Null은 정의한 제약조건을 준수하는지만 확인하며, PK, FK 제약은 실제 데이터를 조회하기 때문에 보다 성능에 더 큰 영향을 미친다.
만일 위와 동일하게 100만건 입력하는데 인덱스와 PK 제약을 모두 제거한다면 입력 시의 수행 상태는 다음과 같다.
drop index target_x1;
drop index target_x2;
alter table target drop primary key;
truncate table target;
insert into target select * from source;
1000000 개의 행이 만들어졌습니다.
경 과: 00:00:01.32
😎 조건절과 DML 성능
이번엔 조건절에 따른 DML 성능을 비교해보자
1) 조건절을 포함한 가장 기본적인 DML
update emp set sal = sal * 1.1 where deptno = 40;
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | 7 | 2 (0)| 00:00:01 |
| 1 | UPDATE | EMP | | | | |
| 2 | INDEX RANGE SCAN | EMP_X01 | 1 | 7 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
delete from emp where deptno = 40;
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 1 | 13 | 1 (0)| 00:00:01 |
| 1 | DELETE | EMP | | | | |
| 2 | INDEX RANGE SCAN | EMP_X01 | 1 | 13 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
위 실행계획들은 SELECT 문과 다르지 않고 이들 DML문에는 인덱스 튜닝 원리를 그대로 적용할 수 있다.
2) 서브쿼리와 DML
update emp set sal = sal * 1.1
where exists (select 'x'
from dept
where deptno = e.deptno and loc = 'CHICAGO'
);
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 5 | 90 | 5 (20) |
| 1 | UPDATE | EMP | | | |
| 2 | NESTED LOOPS | | 5 | 90 | 5 (20) |
| 3 | SORT UNIQUE | | 1 | 11 | 2 (0) |
| 4 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 11 | 2 (0) |
| 5 | INDEX RANGE SCAN | DEPT_X01 | 1 | | 1 (0) |
| 6 | INDEX RANGE SCAN | EMP_X01 | 5 | 35 | 1 (0) |
-----------------------------------------------------------------------------------
delete emp set sal = sal * 1.1
where exists (select 'x'
from dept
where deptno = e.deptno and loc = 'CHICAGO'
);
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 5 | 120 | 4 (25) |
| 1 | DELETE | EMP | | | |
| 2 | HASH JOIN SEMI | | 5 | 120 | 4 (25) |
| 3 | INDEX RANGE SCAN | EMP_X01 | 14 | 182 | 1 (0) |
| 4 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 11 | 2 (0) |
| 5 | INDEX RANGE SCAN | DEPT_X01 | 1 | | 1 (0) |
-----------------------------------------------------------------------------------
insert into emp
select e.*
from emp_t e
where exists (select 'x'
from dept
where deptno = e.deptno and loc = 'CHICAGO'
);
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 5 | 490 | 6 (17) |
| 1 | LOAD TABLE COVENTIONAL | EMP | | | |
| 2 | HASH JOIN SEMI | | 5 | 490 | 6 (17) |
| 3 | TABLE ACCESS FULL | EMP_T | 14 | 1218 | 3 (0) |
| 4 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 11 | 2 (0) |
| 5 | INDEX RANGE SCAN | DEPT_X01 | 1 | | 1 (0) |
-----------------------------------------------------------------------------------
역시 SELECT 문과 실행계획이 다르지 않으므로 DML 문에 조인 튜닝의 원리를 그대로 적용할 수 있다.
😎 Redo 로깅과 DML 성능
오라클은 데이터 파일과 컨트롤 파일에 가해지는 모든 변경사항을 Redo 로그에 기록한다.
Redo 로그란 트랜잭션 데이터가 유실되었을 때 트랜잭션을 재현함으로 유실 이전 상태로 복구하는 데 사용된다.
다만 DML 수행 시마다 Redo 로그를 생성해야 해 Redo 로깅은 DML 성능에 영향을 미친다. 그래서 INSERT 작업에 대해 Redo 로깅 생략 기능이 제공된다.
⭐ Redo 로그의 용도
1️⃣ Database Recovery
: 물리적으로 디스크가 깨지는 등의 Media Fail 발생 시 데이터베이스를 복구하기 위한 목적으로 사용
: 이때 온라인 Redo 로그를 백업해 둔 Archived Redo 로그를 이용
: Media Recovery 라고도 불림
2️⃣ Cache Recovery (Instance Recovery 시 Roll forward 단계)
: DBMS의 버퍼캐시는 휘발성으로 캐시에 저장된 변경사항이 디스크 상의 데이터 블록에 아직 기록되지 않은 상태로 정전 등이 발생해 인스턴스 비정상 종료 시 그때까지의 작업내용을 모두 잃게 되는데 이러한 트랜잭션 데이터 유실에 대비해 Redo 로그를 남김
: Instancce Recovery라고도 불림
3️⃣ Fast Commit
: 변경된 메모리 버퍼블록을 디스크 상의 데이터 블록에 반영하는 작업은 랜덤 액세스 방식으로 이루어져 매우 느린 반면 로그는 Append 방식으로 기록해 상대적으로 빠름
: 그래서 트랜잭션에 의한 변경사항을 우선 Append 방식으로 빠르게 로그 파일에 기록하고, 변경된 메모리 버퍼 블록과 데이터파일 블록 간 동기화는 적절한 수단(DBWR, Checkpoint)을 이용해 나중에 배치(Batch)방식으로 일괄 수행
: 사용자의 갱신내용이 메모리 상의 버퍼 블록에만 기록된 채 아직 디스크에 기록되지 않았지만 Redo 로그를 믿고 빠르게 커밋을 완료한다는 의미로 Fast Commit이라 불림
😎 Undo 로깅과 DML 성능
과거의 롤백(Rollback) 과 같은 의미로 오라클 9i 버전부터 Undo 라는 용어로 사용되었다.
Undo는 트랜잭션을 롤백함으로써 현재를 과거 상태로 되돌리는데 사용된다.
따라서 Redo는 트랜잭션을 재현하는 데 필요한 정보를 로깅, Undo는 변경된 블록을 이전 상태로 되돌리는데 필요한 정보를 로깅한다.
역시 DML 수행시마다 Undo 를 생성해야 해 DML 성능에 영향을 미치며 오라클에서 이를 생략하는 기능은 따로 제공하지 않는다.
⭐ Undo 로그의 용도
오라클은 데이터 입력, 수정, 삭제 시마다 Undo 세그먼트에 기록을 남기며 데이터에 기록한 공간은 트랜잭션을 커밋하는 순간 다른 트랜잭션이 재사용할 수 있는 상태로 바뀐다. (가장 오래 전에 커밋한 Undo 공간부터 재사용)
1️⃣ Transaction Rollback
: 트랜잭션에 의해 변경사항을 최종 커밋하지 않고 롤백하고자 할 때 Undo 데이터 이용
2️⃣ Transaction Recovery (Instance Recovery 시 rollback 단계)
: 시스템 셧다운과 같은 Instance Crash 발생 후 Redo를 이용해 roll forward 단계가 완료되면 최종 커밋되지 않은 변경사항까지 모두 롤백하기 위해 Undo데이터 사용
3️⃣ Read Consistency
: 읽기 일관성(Read Consistency)을 위해 사용. 읽기 일관성을 위해 Consistent 모드로 데이터를 읽는 오라클에선 동시 트랜잭션이 많을수록 블록 I/O가 증가하면서 성능 저하로 이어짐
😎 MVCC(Multi-Version Concurrency Control) 모델
오라클은 데이터를 읽을 때 Current 와 Consistent 두가지 모드로 읽는다.
▶️ Current 모드: 디스크에서 캐시로 적재된 원본(Current) 블록을 현재 상태로 그대로 읽는 방식
▶️ Consistent 모드: 쿼리가 시작된 이후에 다른 트랜잭션에 의해 변경된 블록을 만나면 원본 블록으로부터 복사본(CR Copy) 블록을 만들고, 거기에 Undo 데이터를 적용함으로써 쿼리가 '시작된 시점'으로 되돌려서 읽는 방식 (💡원본블록 하나에 여러 복사본이 캐시에 존재할 수 있음)
⭐SCN(System Commit Number)
▶️시스템에서 마지막 커밋이 발생한 시점정보, Global 변수값으로 관리하며 트랜잭션 커밋 시 또는 오라클 백그라운드 프로세서에 의해서 1씩 증가한다.
✔️ 블록 SCN: 오라클은 각 블록이 마지막으로 변경된 시점을 관리하기 위해 모든 블록 헤더에 SCN을 관리한다.
✔️쿼리 SCN: 모든 쿼리는 이 Global 변수인 SCN 값을 먼저 확인하고서 읽기 작업을 시작한다.
▶️Consistent 모드는 쿼리 SCN과 블록 SCN을 비교함으로써 쿼리 수행 도중에 블록이 변경됐는지를 확인하면서 데이터를 읽는 방식으로 데이터를 읽다 블록 SCN이 쿼리 SCN보다 더 큰 블록을 만나면 복사본 블록을 만들고 Undo 데이터를 적용함으로써 쿼리가 시작된 시점으로 되돌려서 읽는다.
▶️Undo 데이터가 이미 다른 트랜잭션에 의해 재사용되어 쿼리 시작시점으로 되돌리는 작업에 실패하면 Snapshot too old(ORA-01555) 에러가 발생한다.
▶️SELECT문은 보통 Consistent 모드로 데이터를 읽고 DML문은 보통 Consistenct 모드로 시작된 지점에 존재했던 대상 레코드를 찾고, Current 모드로 원본 블록을 찾아 갱신(추가/변경/삭제)한다.
😎 Lock과 DML 성능
Lock은 필요 이상으로 자주, 길게 사용하거나 레벨을 높일수록 DML 성능이 느려지며, Lock을 너무 적게, 짧게 사용하거나 필요 레벨 이하로 낮추면 데이터 품질이 나빠진다. 성능과 데이터 품질은 트레이드 오프(Trade-off) 관계이며 동시성 제어를 통해 이 둘의 적정선을 찾아야 한다.
😎 커밋과 DML 성능
DML을 끝내려면 커밋을 완료해야 하므로 커밋과 DML은 밀접한 관련이 있다. DML이 LOCK에 의해 블로킹(Blocking)된 경우 Lock을 푸는 열쇠인 커 밋은 DML 성능과 직결된다.
⭐ 커밋의 내부 메커니즘
(1) 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 프로세스가 변경된 버퍼블록들을 데이터파일에 일괄 저장한다.
💡 데이터 변경 전 항상 로그부터 기록하는 것을 Write Ahead Logging 이라고 부른다.
DBWR와 LGWR 프로세스는 잠자고 있다가 주기적으로 깨어나 각각 Dirty 블록과 Redo 로그 버퍼를 파일에 기록한다.
LGWR 프로세스는 서버 프로세스가 커밋 발행 신호를 낼때도 깨어나 활동을 시작하며 Redo 로그버퍼 내용을 로그파일에 기록한다. => Log Force Commit
서버프로세스가 변경한 버퍼블록들을 디스크에 기록하지 않았더라도 커밋 시점에 Redo로그를 디스크에 안전하게 기록했다면 그 순간부터 트랜잭션의 영속성이 보장된다.
(4) 커밋 = 저장버튼
커밋을 한다는건 워드프로세서에서 입력한 내용을 메모리에 기록하다가 저장 버튼을 눌러서 디스크 파일에 저장하는 것을 말하며 저장을 완료할 때까지 사용자는 작업을 계속할 수 없는 Sync 방식이다.
문서 저장과 관련해 안좋은 습관 3가지를 보자
1️⃣ 문서 작성을 모두 완료할 때까지 저장 버튼을 한번도 누르지 않음
2️⃣ 너무 자주, 수시로 저장 버튼을 누름
3️⃣ 습관적으로 저장 버튼을 연속해서 두번 씩 누름
커밋도 이와 비슷한데 커밋은 Redo 로그버퍼에 기록된 내용을 디스크에 기록하도록 LGWR 프로세스에 신호를 보낸 후 작업을 완료했다는 신호를 받아야 다음 작업을 진행할 수 있는 Sync 방식이다.
LGWR 프로세스가 Redo 로그를 기록하는 작업은 디스크 I/O 작업이므로 커밋은 생각보다 느리다.
트랜잭션을 필요이상으로 길게 정의해 커밋을 오랫동안 하지 않으면 Undo 공간이 부족해져 시스템 장애를 유발할 수 있고, 너무 자주 커밋하게 되면 프로그램 자체 성능이 느려지므로 트랜잭션을 논리적으로 잘 정의해 불필요한 커밋을 방지해야 한다.
1.2 데이터베이스 Call과 성능
⭐ 데이터베이스 Call 통계
select cust_nm, birthday from customer where cust_id = :cust_id
call count cpu elapsed disk query current rows
-------- ------- ------ ---------- ------- ------- --------- --------
Parse 1 0.00 0.00 0 0 0 0
Execute 5000 0.18 0.14 0 0 0 0
Fetch 5000 0.21 0.25 0 20000 0 50000
-------- ------- ------ ---------- ------- ------- --------- --------
total 10001 0.39 0.40 0 20000 0 50000
Misses in library cache during pasrse: 11️⃣Prase Call
: SQL 파싱과 최적화를 수행하는 단계
: SQL과 실행계획을 라이브러리 캐시에서 찾으면 최적화 단계는 생략된다.
2️⃣Execute Call
: SQL을 실행하는 단계
: DML은 이 단계에서 모든 과정이 끝나지만 SELECT 문은 Fetch 단계를 거친다.
3️⃣Fetch Call
: 데이터를 읽어서 사용자에게 결과집합을 전송하는 과정
: SELECT문에서만 나타나며 전송할 데이터가 많으면 Fetch Call이 여러번 발생한다.
⭐ UserCall과 Recursive Call
▶️ User Call
: 네트워크를 경유해 DBMS 외부로부터 인입되는 Call
: 위의 그림( 3-Tier 아키텍쳐)에서 최종 사용자는 맨 왼쪽 클라이언트 단에 위치하지만 DBMS 입장에서 사용자는 WAS(또는 AP)이므로 User Call은 WAS서버에서 발생한다.
▶️ Recursive Call
: DBMS 내부에서 발생한 Call
: SQL 파싱과 최적화 과정에서 발생하는 데이터 딕셔너리 조회, PL/SQL로 작성한 사용자 정의 함수/프로시저/트리거에 내장된 SQL을 실행할 때 발생하는 Call
User Call, Recursive Call 둘 다 SQL 실행할 때마다 Parse, Execute, Fetch Call 단계를 거치는 것은 동일하다.
Call이 많으면 성능은 느릴 수 밖에 없고, 특히 네트워크를 경유하는 User Call은 성능에 더더욱 영향을 미친다.
😎 절차적 루프 처리
데이터 베이스 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;
PL/SQL 프로그램으로 SOURCE 테이블을 읽어 100만 번 루프를 돌아 건건이 TARGET 테이블에 입력해본다.
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
위 PL/SQL을 보면 루프를 돌며 건건이 Call이 발생했지만 네트워크를 경유하지 않는 Recursive Call로 29초만에 수행을 마쳤다.
위에선 모든 루프를 처리하고서 커밋을 진행했는데 만약 루프안에서 commit을 건건이 하게 되면 어떻게될까?
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);
commit;
end loop;
end;
경 과: 00:01:00.50
29초에서 1분이 늘어난 것을 볼 수 있다. 성능도 저하가 되지만 커밋을 자주 발행하게 되면 트랜잭션 원자성(Atomicity)에도 문제가 발생한다. 반대로 매우 오래 걸리는 트랜잭션에 한번도 커밋을 하지 않고 진행 시 Undo 공간 부족으로 시스템 오류를 발생시킬 수 있다.
이럴 때 루프 안에 커밋을 넣되 아래와 같은 조건을 추가하면 된다.
if mod(i, 100000) = 0 then -- 10만 번에 한번 씩 커밋하라
comit;
end if;
여기에 더해 JAVA 프로그램(WAS)으로 루프 내 커밋을 수행하는 경우에는 네트워크를 경유하는 User call이므로 성능이 급격히 나빠질 수 있다. (29초 => 218.392초)
😎One SQL의 중요성
이 경우 가능하다면 아래와 같이 Insert into Select 구문으로 수행하게 되면 단 한번의 Call로 최적의 성능을 낼 수 있다.
업무로직이 복잡하지 않다면 가급적 One SQL로 구현하려고 노력하자
insert into target
select * from source;
경 과: 00:00:01.46
1.3 Array Processing 활용
복잡한 업무로직으로 One SQL로 구현이 어려울 때 Array Processing 기능을 활용하면 Call 부하를 획기적으로 줄일 수 있다.
declare
cursor c is select * from source;
type typ_source is table of c%rowtype;
l_source typ_source;
l_array_size number default 10000;
procedure insert_target(p_source in typ_source) is
begin
forall i p_source.first..p_source.last
insert into target values p_source(i);
end insert_target;
begin
open c;
loop
fetch c bulk collect into l_source limit l_array_size;
insert_target(l_source);
exit when c%notfound;
end loop;
close c;
commit;
end;
경 과 : 00:00:03.99
Java 도 Array Processing 으로 처리하면 Call 부하를 줄일 수 있다. Call을 하나로 줄이지는 못해도 Array Processing을 통해 10~100번 수준으로 줄일 수 있다면 One SQL에 준하는 성능효과를 얻을 수 있다.
public class JavaArrayProcessing {
public void execute() throws Exception {
int arraySize = 10000;
long no [] = new long [arraySize];
long empno [] = new long [arraySize];
String ename [] = new String [arraySize];
...
String SQLStmt = "select no, empno, ename, job, mgr "
+ ", to_char(hiredate, 'yyyymmdd hh24miss'), sal, comm, deptno "
+ " from source";
PreparedStatement st = con.prepareStatement(SQLStmt);
st.setFetchSize(arraySize);
ResultSet rs = st.executeQuery();
int i = 0;
while(rs.next()) {
no [i] = rs.getLong(1);
empno [i] = rs.getLong(2);
ename [i] = rs.getString(3);
...
if(++i == arraySize) {
insertTarget(i, no, empno, ...);
i = 0;
}
}
if(i > 0 ) insertTarget(i,no,empno,....);
rs.close();
st.close();
}
public void insertTARGET(int length, long [] p_no, long[] p_empno ....) throws Exeception{
String SQLStrmt = "insert into target (no, empno, ename....)"
+ "values(?, ?, ? ....)";
PreparedStatement st = con.prepareStatement(SQLStmt);
for(int i =0; i < length; i++) {
st.setLong(1, p_no [i]);
st.setLong(2, p_empno[i]);
st.setString(3, p_ename[i]);
...
st.addBatch(); /* insert 할 값들을 배열에 저장 */
};
st.executeBatch(); /* 배열에 저장된 값을 한번에 insert; - for 문 밖에서 실행 */
st.close();
}
1.4 인덱스 및 제약 해제를 통한 대량 DML 튜닝
인덱스와 무결성 제약조건은 DML 성능에 큰 영향을 끼치며 온라인 트랜잭션 처리 시스템에서는 이들 기능을 해제하긴 어렵겠지만 동시 트랜잭션 없이 대량 데이터를 적재하는 배치(Batch)프로그램에서는 이들 기능을 해제해 큰 성능 개선 효과를 얻을 수 있다.
아래 SQL을 실행해 SOURCE 테이블에 1000만건을 넣고, TARGET 테이블 생성 및 인덱스를 추가해보자
create table source
as
selectr b.no, a.*
from (select * from emp where rownum <= 10) a
,(select rownum as no from dual connect by level <= 1000000)b;
create table target
as
select * from source where 1 = 2;
alter table target add
constraint target_pk primary key(no, empno);
create index target_x1 on target(ename);
PK 제약 생성 시 Unique 인덱스가 자동으로 생성되는데 인덱스를 추가 생성했기 때문에 TARGET 테이블엔 인덱스가 총 두개이다.
이 상태로 TARGET 테이블에 SOURCE 테이블에 있는 1000만건을 입력하면 1분 19초가 걸린다.
insert /*+ append */ into target
select * from source;
10000000 개의 행이 만들어졌습니다.
경 과 : 00:01:19.10
commit;
😎 PK 제약과 인덱스 해제 - PK 제약에 Unique 인덱스를 사용한 경우
pk 제약과 인덱스를 해제한 상태에서 데이터 데이터를 입력하면 어떻게될까?
truncate table target;
-- pk 제약 비활성화, 인덱스 drop
alter table target modify constraint target_pk disable drop index;
-- 일반 인덱스 unusable 상태로 변경
alter index trarget_x1 unusable;
-- 인덱스가 Unusable 상태에서 데이터를 입력하기 위해 아래 파라미터를 true로 설정
alter session skip_unusable_indexes = true;
-- 1000만건 insert
insert /*+ append */ into target
select * from source;
10000000 개의 행이 만들어졌습니다.
경 과 : 00:00:05.84
commit;
-- 1000만개 데이터 insert 후 pk 제약조건 활성화 및 일반 인덱스 재생성
-- pk 제약조건 활성화, pk 인덱스 자동 생성, NOVALIDATE를 활용하여 무결성 체크 생략
alter table target modify constraint target_pk enable NOVALIDATE;
경 과 : 00:00.06.77
-- 일반 인덱스 재생성
alter index target_x1 rebuild;
경 과 : 00:00:08.26
1000만건의 데이터 입력 시간이 5초만에 수행을 마쳤으며 작업 후 PK 제약 및 인덱스를 재생성한 시간까지 포함하면 21초로 기존 1분 19초보다 훨씬 빠르게 작업을 마쳤다.
😎 PK 제약과 인덱스 해제2 - PK 제약에 Non-Unique 인덱스를 사용한 경우
좀전엔 pk 제약을 비활성화 하면서 아예 Drop 을 했는데 그 이유는 PK 인덱스는 Unusable 상태에서 데이터를 입력할 수 없기 때문이다.
alter index target_pk unusable;
insert into target
select * from source;
1행에 오류:
ORA-01502: index 'TARGET_PK' or partition of such index is in unusable state
insert /*+ append */ into target
select * from source;
1행에 오류:
ORA-266026: unique index TARGET_PK initially in unusable state
PK 인덱스를 DROP 하지 않고 Unusable 상태에서 데이터를 입력하고 싶다면 PK 제약에 Non-Unique 인덱스를 사용한다.
truncate table target;
alter table target drop primary key drop index;
create index taret_pk on target(no, empno); -- Non-Unique 인덱스 생성
alter table target add
constraint target_pk primary key (no, empno)
using index target_pk; -- PK 제약에 Non-Unique 인덱스 사용
-- ⭐ PK 제약을 비활성화하되 인덱스는 Drop 하지 않고 유지한다. ⭐
alter table target modify constraint target_pk disable keep index;
alter index target_pk unusable;
alter index target_x1 unusable;
이제 다시 대량 insert 작업을 진행해도 수행속도에 아무런 문제가 없다.
insert /*+ append */ into target
select * from source;
10000000개의 행이 만들어졌습니다.
경 과 : 00:00:05.53
commit;
작업을 마치고 다시 원상복구를 위해 인덱스를 재생성하고 PK제약을 다시 활성화한다.
alter index target_x1 rebuild;
경 과 : 00:00:07.24
alter index target_pk rebuild;
경 과 : 00:00:05.27
alter table target modify constraint target_pk enable novaildate;
경 과 : 00:00:00.061.5 수정가능 조인 뷰
😎 전통적인 방식의 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)))
위방식도 한 달 이내 거래 데이터를 두 번 조회하므로 비효율일 수 있는데 총 고객수가 아주 많다면 Exists 쿼리를 아래와 같이 해시 세미 조인으로 유도하는 것을 고려할 수 있다.
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 발생량이 많다면 아래와 같이 일괄 변경하는 것도 고려할 수 있는데 모든 고객 레코드에 LOCK 이 걸리고 이전과 같은 값으로 갱신되는 비중이 높을수록 Redo 로그 발생량이 증가해 더 비효율일 수 있다.
update 고객 c
set (최종거래일시, 최근거래횟수, 최근거래금액) =
(select nvl(max(거래일시), c.최종거래일시)
, decode(count(*), 0, c.최근거래횟수, count(*))
, nvl(sum(거래금액), c.최근거래금액)
from 거래
where 고객번호 = c.고객번호
and 거래일시 >= trrunc(add_months(sysdate, -1)))
😎 수정가능 조인 뷰
어래와 같이 "수정가능 조인 뷰"를 활용 시 참조 테이블과 두번 조인하는 비효율을 없앨 수 있다.
이 방식은 12c 이상 버전에서만 사용 가능하며, 10g 이하 버전에는 UPDATE 옆에 bypass_ujvc 힌트 사용 시 실행 가능하다. (※ 11g는 실행불가)
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 최종거래일시 = 거래일시
, 최근거래횟수 = 거래횟수
, 최근거래금액 = 거래금액
조인 뷰는 FROM 절에 두 개 이상의 테이블을 가진 뷰를 가리키며 '수정 가능 조인 뷰'는 말 그대로 입력, 수정, 삭제가 허용되는 조인 뷰를 말하며 1쪽 집합과 조인하는 M쪽 집합에만 입력, 수정, 삭제가 허용된다.
create or replace view EMP_DEPT_VIEW as
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';
만약 위의 조인뷰를 통해 job = 'CLERK'인 레코드의 loc를 모두 'SEOUL'로 변경하는 것을 허용한다면 어떻게 될까?
create table emp as select * from scott.emp;
create table dept as select * from scott.dept;
create or replace view EMP_DEPT_VIEW as
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';
JOB = 'CLERK'인 사원이 10, 20, 30 부서에 모두 속해있다고 할 때 위 쿼리의 UPDATE를 수행하고 나면 세 부서의 loc가 모두 'SEOUL'로 바뀔 것이다. 다른 JOB을 가진 사원의 부서 소재지까지 바뀌어버리는 결과를 초래할 수 있다.
그럼 아래와 같이 1 쪽 집합(DEPT)와 조인하는 M쪽 집합(EMP)의 컬럼을 수정하면 어떻게 될까?
update EMP_DEPT_VIEW set comm = nvl(comm, 0) + (sal * 0.1) where sal <= 1500;
위 쿼리를 실제 수행 시 ORA-01779:cannot modify a column which maps to a non key-preserved table 과 같은 에러가 발생한다.
이유는 옵티마이저가 어느 테이블이 1쪽의 집합인지 알 수 없기 때문에 발생하는 에러이다. 물론 이 상태에서는 DELETE와 INSERT도 허용되지 않는다.
delete from EMP_DEPT_VIEW where job = 'CLERK';
1행에 오류:
ORA-01779:cannot delete from view without exactly one key-preserved table
이때 아래와 같이 1쪽 집합에 PK 제약을 설정하거나 Unique 인덱스를 생성하면 수정가능 조인뷰를 통한 입력/수정/삭제가 가능하다.
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;
4 rows updated;
PK 제약 설정 시 EMP 테이블은 키-보존 테이블(Key-Preserved Table)이 되고 DEPT 테이블은 비 키-보존 테이블(Non Key Preserved Table)로 남는다.
⭐키 보존 테이블(Key-Preserved Table)
- 조인된 결과집합을 통해서도 중복 값 없이 Unique하게 식별이 가능한 테이블
- Unique한 1쪽 집합과 조인되는 테이블이어야 조인된 결과집합을 통한 식별 가능하다.
- 뷰에 rowid를 제공하는 테이블(위 경우엔 중복값이 없는 emp 테이블의 rowid를 뷰의 rowid로 사용)
- 참고로 rowid는 Unique 인덱스가 존재해야 키 보존 테이블에서 출력할 수 있다.
😎 ORA-01779 오류회피
DEPT 테이블에 AVG_SAL 컬럼을 추가해 emp 부서로부터 부서별 평균 급여를 계산해 추가한 컬럼에 반영하는 update 문을 실행해보자
alter table dept add avg_sal number(7,2);
update
(select d.deptno, d.avg_sal as d_avg_sal, e.avg_sal as 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;
11g 이하 버전에서 위 UPDATE 문 실행하게 되면 ORA-01779 에러가 발생한다.
EMP 테이블을 DEPTNO로 Group By 했으므로 DEPTNO 컬럼으로 조인한 DEPT 테이블은 키가 보존되는데도 옵티마이저가 불필요한 제약을 가했다.
이때 10g 에선 아래와 같이 bypass_ujvc 힌트를 이용해 Updatable Join View Check를 생략하라고 옵티마이저에 지시해 제약을 회피할 수 있다.
update /*+ bypass_ujvc */
(select d.deptno, d.avg_sal as d_avg_sal, e.avg_sal as 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;
다만 11g 부터는 위의 힌트(bypass_ujvc)를 사용할 수 없으므로 UPDATE 문 대신 MERGE문으로 바꿔줘야한다.
11g에서도 1쪽 집합에 Unique 인덱스가 있으면 "수정가능 조인뷰"를 이용한 UPDATE는 가능하다.
12c에선 수정가능 조인뷰의 기능이 개선되어 힌트를 사용하지 않아도 위 UPDATE문이 잘 실행된다.
12c에서 고객 테이블을 group by 처리해 Unique하게 만들어주면 ORA-01779를 회피하고 수정할 수 있게 된다.
update (
select o.주문금액, o.할인금액
from 주문_t o
, (select 고객번호 from 고객_t where 고객등급 = 'A' group by 고객번호) c
where o.고객번호 = c.고객번호
and o.주문금액 >= 10000000)
set 할인금액 = 주문금액 * 0.2, 주문금액 = 주문금액 * 0.8
배치프로그램, 데이터 이행 프로그램에서 사용하는 중간 임시 테이블에는 일일이 PK 제약이나 인덱스를 생성하지 않으므로 이 패턴이 유용할 수 있다.
1.6 MERGE 문 활용
MERGE문은 오라클 9i 부터 도입된 문법이다.
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 테이블 (위 쿼리에선 customer_delta) 기준으로 Target 테이블(위 쿼리에선 customer)과 Left Outer 방식으로 조인해서 조인에 성공 시 UPDATE, 실패하면 INSERT 하게된다.
MERGE문은 UPSERT(= UPDATE + INSERT)라고도 부른다.
⭐ 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);
위에서 배운 수정 가능 조인 뷰 기능도 MERGE문으로 대체 가능하다.
<수정가능 조인 뷰>
update
(select d.deptno, d.avg_sal as d_avg_sal, e.avg_sal as 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문>
merge into dept d
using (select deptno, round(avg(sal), 2) avg_sal from emp group by deptno) e
on (d.deptno = e.deptno)
when matched then update set d.avg_sal = e.avg_sal;
⭐ 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_nm = s.cust_nm, t.email = s.email, ...
where reg_dt >= to_date('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 cluase
이미 저장된 데이터를 조건에 따라 지우는 기능도 제공한다.
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, ...
delete where t.withraw_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문을 수행해 UPDATE가 이루어진 결과로서의 데이터안에서만 조건에 해당 시 삭제한다.
또 MEREG문 DELETE 절은 조인에 성공한 데이터만 삭제할 수 있다. 즉 Source테이블에서 삭제된 데이터를 Target 테이블에서 동시에 지울 수 없다. Source 테이블에서 삭제된 데이터는 조인데 실패하기 때문이다.
😎 수정가능 조인 뷰 VS MERGE 문
MERGE INTO EMP T2
USING (SELECT T.ROWID AS RID, S.ENAME
FROM EMP T, EMP_SRC S
WHERE T.EMPNO = S.EMPNO
AND T.ENAME <> S.ENAME) S
ON (T2.ROWID = S.RID)
WHEN MATCHED THEN UPDATE SET T2.ENAME = S.ENAME;위 MERGE 패턴은 성능이 사실 좋지 않다. UPDATE 대상 테이블인 EMP를 두번 액세스하기 때문이다.
아래와같이 작성을 하면 위의 MERGE문 보다는 낫다. 하지만 복잡한 조인과 서브쿼리를 포함하는 경우 데이터 검증용 SELECT 문을 따로 하나 더 만들어야하는 불편함이 있을 수는 있다.
MERGE INTO EMP T
USING EMP_SRC S
ON (T.EMPNO = S.EMPNO)
WHEN MATCHED THEN UPDATE SET T.ENAME = S.ENAME
WHERE T.ENAME <> E.ENAME;
이때는 수정 가능 조인뷰를 이용해 SELECT 문을 먼저 만들어 데이터 검증을 마친 후 바깥에 UPDATE 문을 씌우는 패턴도 좋다.
UPDATE (
SELECT S.ENAME AS S_ENAME, T.ENAME AS T_ENAME
FROM EMP T, EMP_SRC S
WHERE T.EMPNO = S.EMPNO
AND T.ENAME <> S.ENAME
)
SET T_ENAME = S_ENAME;
하지만 EMP_SRC 테이블의 EMPNO 컬럼에 Unique 인덱스가 생성되어있어야하고, Unique 인덱스가 없을 때 10g 이하는 bypass_ujvc 힌트를 통해, 12c 부터는 Group By 처리를 통해 ORA-01779 에러를 회피해야하며, 11g에서는 사용할 수 없는 불편함이 있는 단점이 있으니 MERGE 문을 사용할지 UPDATE 문 사용할 것인지 충분히 고려해서 선택하자
2. Direct Path I/O 활용
2.1 Direct Path I/O
- 일반적인 블록 I/O는 DB 버퍼캐시를 경유하는데 데이터를 읽거나 변경 하고자 하는 블록을 먼저 버퍼캐시에서 찾아보고 찾지 못할 때만 디스크에서 읽는다.
- 찾은 버퍼블록에 변경을 가하고 나면, DBWR 프로세스가 변경된 블록(Dirty 블록)들을 주기적으로 찾아 데이터파일에 반영해준다.
- 온라인 트랜잭션의 경우 자주 읽는 블록에 대해 반복적인 I/O를 줄임으로써 버퍼캐시가 시스템 전반적인 성능을 높여주지만 대량 데이터를 읽고 쓸 땐 보통 데이터들이 버퍼캐시의 블록에 존재하지 않을 가능성이 높아 성능에 오히려 좋지 않다.
대량 데이터를 읽고 쓸 때 건건이 버퍼캐시를 탐색했을 때 블록에 존재하지 않아 건건이 디스크로부터 읽어와 버퍼캐시에 적재하고 다시 읽는데, 심지어 적재한 블록을 재사용할 가능성도 낮다. 이런 데이터 블록들이 버퍼 캐시를 점유한다면 다른 프로그램 성능에도 나쁜 영향을 미친다.
이 경우 오라클에서 제공하는 Direct Path I/O 기능을 사용 시 버퍼캐시를 경유하지 않고 곧바로 데이터 블록을 읽고 쓸 수 있다.
⭐Direct Path I/O가 작동하는 예
✔️ 병렬 쿼리로 Full Scan 을 수행할 때
✔️ 병렬 DML을 수행할 때
✔️ Direct Path Insert를 수행할 때
✔️ Temp 세그먼트 블록들을 읽고 쓸 때
✔️ direct 옵션을 지정하고 export를 수행할 때
✔️ nocache 옵션을 지정한 LOB 컬럼을 읽을 때
병렬 쿼리
쿼리문에 아래와 같이 parallel 또는 parallel_index 힌트 사용 시 지정한 병렬도(Parallel Degree)만큼 병렬 프로세스가 떠서 동시에 작업을 진행한다.
병렬도를 아래와같이 4로 지정하면, Direct Path I/O 덕분에 성능은 수십배 빨라진다.
버퍼캐시를 탐색하지 않고, 디스크로부터 버퍼캐시에 적재하는 부담이 없는 덕분이다.
select /*+ full(t) parallel(t 4) */ * from big_table t;
select /*+ index_ffs(t big_table_x1) parallel_index(t big_table_x1 4) */ count(*) from big_table t;
2.2 Direct Path Insert
😎 일반적인 INSERT 과정
1️⃣ 테이블 HWM(High-WaterMark) 아래쪽에 있는 블록 중 데이터를 입력할 수 있는 여유 공간이 있는 블록을 Freelist에서 찾는다.
2️⃣ Freelist에서 할당받은 블록을 버퍼캐시에서 찾는다.
3️⃣ 버퍼캐시에 없으면, 데인터파일에서 읽어 버퍼캐시에 적재한다.
4️⃣ INSERT 내용을 Undo 세그먼트에 기록한다.
5️⃣ INSERT 내용을 Redo 로그에 기록한다.
➡️ 이 과정을 다 거치기 때문에 느리다
😊 Direct Path Insert 방식이 빠른 이유
1️⃣ Freelist를 참조하지 않고 HWM 바깥 영역에 데이터를 순차적으로 입력한다.
2️⃣ 블록을 버퍼캐시에서 탐색하지 않는다.
3️⃣ 버퍼캐시에 적재하지 않고, 데이터 파일에 직접 기록한다.
4️⃣ Undo 로깅을 안한다.
5️⃣테이블을 nologging 모드로 전환하면 Redo 로깅을 안하게 할 수 있다.(alter table t NOLOGGING;)
💡일반 INSERT 문에서는 로깅하지 않게 하는 방법은 없다. (오라클은 nologging에 대한 힌트를 제공하지 않음)
🤩 Direct Path Insert 방식으로 입력하는 방법(더보기)
▶️ INSERT ... SELECT 문에 append 힌트 사용
▶️ parallel 힌트를 이용해 병렬 모드로 INSERT
▶️ direct 옵션을 지정하고 SQL*Loader(sqlldr)로 데이터 적재
▶️ CTAS(create table ... as select )문 수행
▶️ append_values 힌트를 사용한 Array Processing 처리
...
procedure insert_target( p_source in typ_source) is
begin
forall i in p_source.first..p_source.last
insert /*+ append_values */ into target values p_source(i);
end insert_target;
...
‼️Direct Path Insert 사용 시 주의할 점
1️⃣ Direct Path Insert 기능 사용 시 성능은 빨라지지만 Exclusive 모드 TM Lock이 걸려 커밋 전까지 다른 트랜잭션은 해당 테이블에 DML을 수행하지 못한다. (트랜잭션이 빈번한 시간대에 옵션 사용 절대 금물)
2️⃣ Freelist를 조회하지 않고 HWM 바깥 영역에 입력하므로 테이블에 여유 공간이 있어도 재활용하지 않기 때문에 과거 데이터가 주기적으로 DELETE 되어 여유공간이 생기더라도 이 방식으로 Insert 시 테이블은 사이즈가 줄지 않고 계속 늘어만 간다.
이 경우 Range 파티션 테이블이면 과거 데이터를 DELETE 가 아닌 파티션 DROP 방식으로 지워야 공간 반환이 제대로 이루어지며, 비 파티션 테이블이면 주기적으로 Reorg 작업을 수행해주어야 한다.
💡 참고로 DELETE로 지운 공간은 자동으로 반환되지 않으며 INSERT 로 인해서 재활용만 가능하다.
2.3 병렬 DML
INSERT 는 append 힌트를 통해 Direct Path Write 방식으로 유도가 가능하지만 UPDATE, DELETE 는 기본적으로 Direct Path Write가 불가능하므로 병렬 DML로 처리하는 것이 유일한 방법이다.
DML을 병렬로 처리하기 위해서 아래와 같이 병렬 DML을 활성화해야 하며, 그러고 나서는 DML 문에 Parallel 힌트 사용 시 INSERT에서의 SELECT절, UPDATE/DELETE의 조건 절과 같이 대상 레코드를 찾는 작업은 물론 데이터 추가,변경,삭제도 병렬로 진행한다.
alter session eanble parallel dml;
insert /*+ parallel(c 4) */ into 고객 c
select /*+ full(o) parallel(o 4) */ * from 외부가입고객 o;
update /*+ full(o) parallel(c 4) */ 고객 c set 고객상태코드 = 'WD'
where 최종거래일시 < '20100101';
delete /*+ full(c) parallel(c 4) */ from 고객 c
where 탈퇴일시 < '20100101';
만약 병렬 DML을 활성화하지 않은채로 위의 힌트를 기술한다면 대상 레코드를 찾는 작업(INSERT에서의 SELECT와 UPDATE/DELETE의 조건절(Consistent모드 조회))은 병렬로 진행되지만 추가/변경/삭제는 QC(Query Cordinator)가 혼자 담당해 병목현상이 생긴다.
Query Cordinator는 SQL을 병렬로 실행하면 병렬도로 지정한 만큼 또는 두 배로 병렬 프로세스를 띄워 동시에 작업을 진행하는데, 이 때 최초 DB로 접속해서 SQL을 수행한 프로세스는 Query Cordinator가 역할을 맡는다.
단, 병렬로 처리할 수 없거나 병렬로 처리하도록 지정하지 않은 작업은 Query Cordinator가 직접 처리한다.
병렬 INSERT의 경우 append를 지정하지 않아도 Direct Path Insert 방식을 사용하지만 병렬 DML이 작동하지 않을 경우를 대비해 append 힌트를 같이 사용하는 것이 좋다.
insert /*+ append parallel(c 4) */ into 고객 c
select /*+ full(o) parallel(o 4) */ * from 외부가입고객 o;
‼️오라클 12c 부터는 enable_parallel_dml 힌트도 지원‼️
insert /*+ enable_parallel_dml parallel(c 4) */ into 고객 c
update /*+ enable_parallel_dml full(c) parallel(c 4) */ 고객 c
set 고객상태코드 = 'WD'
where 최종거래일시 < '20100101';
delete /*+ enable_parallel_dml full(c) parallel(c 4) */ from 고객 c
where 탈퇴일시 < '20100101';
병렬 DML도 마찬가지로 Direct Path Write 방식 사용 시 테이블에 Exclusive 모드 TM Lock이 걸리므로 트랜잭션이 빈번한 시간대에 이 옵션을 사용하는 것은 금물이다.
😎 병렬 DML이 잘 작동하는지 확인하는 방법
실행계획에서 아래와 같이 UPDATE/DELETE/INSERT가 'PX COORDINATOR' 아래쪽에 나타난다면 UPDATE/DELETE/INSERT 를 각 병렬 프로세스가 처리하는 것으로 보면 된다.
------------------------------------------------------------------------------------------
Id | Operation |Name | Pstart | Pstop | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | | | | | |
| 1 | PX COORDINATOR | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | | |Q1,00| P->S | QC (RAND) |
| 3 | UPDATE | 고객 | | |Q1,00| PCWP | |
| 4 | PX BLOCK ITERATOR | | 1 | 4 |Q1,00| PCWC | |
| 5 | TABLE ACCESS FULL | 고객 | 1 | 4 |Q1,00| PCWP | |
------------------------------------------------------------------------------------------
반면 아래와 같이 UPDATE/DELETE/INSERT가 'PX COORDINATOR' 위쪽에서 나타나면 UPDATE/DELETE/INSERT 를 QC가 처리한 것이다.
------------------------------------------------------------------------------------------
Id | Operation |Name | Pstart | Pstop | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | | | | | |
| 3 | UPDATE | 고객 | | | | | |
| 1 | PX COORDINATOR | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | | |Q1,00| P->S | QC (RAND) |
| 4 | PX BLOCK ITERATOR | | 1 | 4 |Q1,00| PCWC | |
| 5 | TABLE ACCESS FULL | 고객 | 1 | 4 |Q1,00| PCWP | |
------------------------------------------------------------------------------------------