SQL Tuning summary No.5
DML -복구를 위한 redo로그
- DML을 수행할 때마다 Redo 로그를 생성한다.
- Redo 로그는 복구를 위해 기록된다.
- 아래와 같이 하면 현재 Redo 로그의 보관 설정을 확인할 수 있다.
SELECT destination, TO_CHAR(next_time, 'YYYY-MM-DD HH24:MI:SS') AS next_archive_time
FROM v$archive_dest
WHERE destination IN ('LOG_ARCHIVE_DEST_1', 'LOG_ARCHIVE_DEST_2'); -- 원하는 로그 보관 설정을 확인합니다.
- 새롭게 들어오는 DML은 아래와 같이 추가된 redo 로그에 기록되게 된다.
- 다만 새로운 redo 로그를 반영하려면 DB서버를 다시 재시작시켜야 한다.
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1 = 'LOCATION=/archivelog DESTINATION=USE_DB_RECOVERY_FILE_DEST REOPEN=60'; -- 60분 유지 설정
ALTER DATABASE ADD LOGFILE GROUP 4 ('/arch1/log4a.rdo', '/arch2/log4b.rdo') SIZE 50M; -- redo 파일 해당 크기로 추가
DML -read consistency를 위한 undo로그
- DML을 수행할 때마다 undo 로그도 생성된다.
- undo로그의 목적은 롤백과 read consistency를 위한 목적이다.
- read consistency는 oracle이 DML을 안전하게 수행하기 위한 방식이다.
- 오라클은 MVCC모델을 쓰는데, select문은 항상 consistent모드로 읽는다.
- 반면에 DML의 경우, consistent 모드로 record를 찾고, current 모드로 DML을 수행한다.
- consistent모드로 DML문이 시작된 지점에 존재했던 데이터 블록을 찾는다.
- consistent모드에서 얻은 rowid를 가지고 current모드로 원본 블록을 찾아서 갱신한다는 의미다.
- current모드는 디스크에서 캐시로 적재된 원본 블록을 현재상태 그대로 읽는 것이다.
- consistent 모드는 트랜잭션에 의해 변경된 블록을 만나면 쿼리가 시작된 지점으로 되돌려 읽는 방법이다.
DML -무결성 보호를 위한 Lock
- 오라클은 공유 리소스와 사용자 데이터를 보호할 목적으로 lock을 사용한다.
- DML lock
- DDL Lock
- latch
- buffer lock
- library cache lock
- pin
- 가장 중요한 것은 DML Lock이다.
- DML Lock은 동시 트랜잭션에 대한 데이터의 무결성을 보호하는 핵심 기법이다.
- DML Lock은 두 가지로 나뉜다.
- Table Lock
- TM Lock이라고도 부른다.
- table Lock은 모드가 여러가지인데, 선행 트랜잭션이 특정 모드가 되면 후행 트랜잭션은 그에 호환되어야 한다.
- Table Lock은 후행 트랜잭션은 절대 접근못하게 막는 그런 개념이 아니다. 다음에 올 수 있는 모드를 제시하는 푯말에 가깝다.
- 예를 들어 insert, update, delete, merge를 하려고 row lock을 설정한다면 table에 먼저 RX 모드 table lock을 설정해야 한다.
- 호환이 안 되는 모드는 기다리던지(wait 3), 포기하던지 해야 한다.(nowait)
- Row Lock
- 두 개의 동시 transaction이 같은 row를 변경하는 것을 방지한다.
- row lock은 모두 배타적 모드를 사용한다.
- update와 delete를 할 때는 어떤 경우에도 row lock이 걸린다.
- insert의 경우 unique index일 때만 row lock이 걸린다.
- select의 경우 row lock이 없다. MVCC 모델이라 consistency 모드로 읽기 떄문이다.
- mysql은 select에도 공유 lock을 사용하기에 DML과 SELECT도 서로 경합한다.
- Table Lock
- lock에 따른 장애가 일어나기도 하는 데 아래 두가지 상황이 있다.
- blocking
- 선행 transaction이 설정한 lock 때문에 후행 transcation이 작업을 진행하지 못하고 멈춰 있는 상태다.
- deadlock
- DB Deadlock은 상대방이 소유하고 있는 Lock을 요청해서 작업의 처리를 진행하지 못하는 상태다.
- 이를 먼저 인지한 transaction이 롤백을 진행한다. 롤백이 되면 이제 blocking 상태로 전환된다.
- blocking 상태에서 커밋을 할지, 롤백을 할 지 결정해야 대기를 지속하지 않는다.
- blocking
- lock은 트랜잭션 격리수준, 레벨과 관련이 높다. 둘 모두 높아질수록 DML 성능이 나빠진다.
- 트랜잭션 격리성 수준은 4가지로 나뉜다. oracle은 read uncommited는 아예 지원하지 않는다.
- 격리성 수준을 올릴수록 lock을 오랫동안 유지한다.
- read uncommitted
- 트랜잭션에서 처리 중인, 아직 커밋되지 않은 데이터를 다른 트랜잭션이 읽는 것을 허용
- Dirty Read, Non-Repeatable Read, Phantom Read 현상 발생
- read commited
- Dirty Read 방지 : 트랜잭션이 커밋되어 확정된 데이터만 읽는 것을 허용
- DB2, SQL Server, Sybase의 경우 select를 shared Lock으로 구현. 하나의 레코드를 읽을 때 Lock을 설정하고 해당 레코드를 빠져 나가는 순간 Lock 해제
- Oracle은 Lock을 사용하지 않고 쿼리시작 시점의 Undo 데이터를 제공하는 방식으로 구현
- repeatable read
- 선행 트랜잭션이 읽은 데이터는 트랜잭션이 종료될 때까지 후행 트랜잭션이 갱신하거나 삭제하는 것을 불가능
- 같은 데이터를 두 번 쿼리했을 때 일관성 있는 결과를 리턴한다.
- Phantom Read 현상은 여전히 발생함.
- MySQL 의 InnoDB 에서 기본으로 사용되는 격리수준.
- Oracle의 select for update가 여기에 해당.
- 선행 트랜잭션이 읽은 데이터는 트랜잭션이 종료될 때까지 후행 트랜잭션이 갱신하거나 삭제하는 것을 불가능
- serializable
- 선형 트랜잭션이 읽은 데이터를 후행 트랜잭션이 갱신/삭제/삽입 모두 금지
- 잘 쓰이지 않는다. 성능이 많이 안 좋아진다.
- read uncommitted
- lock 레벨은 4가지로 나뉜다. oracle은 lock 에스컬레이션은 사용하지 않는다.
- row
- block
- extent
- table
- lock수준을 떨어뜨리면 데이터 품질이 나빠진다.
- lock 수준을 높이면 동시에 들어오는 데이터 처리가 원활하지 않다.
- 동시에 실행되는 트랜잭션 수를 최대화(고성능)하면서 DML 시 데이터 무결성을 유지(고품질)하는 게 동시성 제어다.
DML -LOCK을 푸는 열쇠 commit
- Lock에 걸린 경우 commit을 해야 lock이 풀리게 되므로 commit은 매우 중요한 역할을 한다.
- commit 모드는 아래와 같이 지정할 수 있다.
commit write immediate wait -- LGWR이 로그버퍼를 파일에 기록했다는 완료 메시지를 받을 떄까지 대기
commit write immediate nowait; -- LGWR을 기다리지 않고 다음 transaction 수행
commit write batch wait; -- commit 명령을 받을 때마다 LGWR이 로그 버퍼를 파일에 기록
commit write batch nowait; -- session 내부에 transaction data를 일정량 buffering했다가 일괄 처리
- commit은 아래와 같은 과정을 거쳐서 만들어진다.
- DML 실행 -> redo 로그버퍼에 변경사항 기록
- 버퍼블록에서 데이터를 변경. 버퍼캐시에 없으면 데이터파일 읽기(disk I/O)
- commit
- LGWR(log writer) process가 redo로그버퍼 내용 로그파일에 일괄 저장
- DBWR(database writer) 프로세스가 변경된 버퍼블록을 데이터파일에 일괄 저장.
- 버퍼캐시가 휘발성이어서 redo 로그를 남긴다고 했다.
- 그런데 redo 로그도 휘발성 로그버퍼에 기록하면 영속성이 보장될까?
- 영속성이 보장된다. redo로그만 디스크에 기록되어있다면 영속성이 보장된다.
- commit 시점에 LGWR(log writer) 프로세스가 꺠어나기 때문이다.
- DB서버 프로세스가 commit record를 로그버퍼에 기록한다.
- DB서버 프로세스가 LGWR 프로세스에 신호를 보내고 wait 큐에서 sleep상태로 전환한다.
- LGWR 프로세스가 로그 버퍼를 디스크에 기록하는 작업을 마친다.
- LGWR 프로세스가 wait 큐에 대기중인 DB서버 프로세스에 완료 메시지를 전송한다.
- 신호를 받은 DB서버 프로세스는 실행중인 큐로 옮겨진 후 CPU를 할당받아 다음 작업을 이어간다.
- 위에서 보았듯 LGWR 프로세스가 redo로그를 기록하는 작업은 disk I/O기 때문에 생각보다는 느리다.
- 트랜잭션을 너무 잘게 짜르면 disk I/O가 많아져 성능이 나빠진다.
- 트랜잭션을 너무 길게 유지하면 undo로그 공간이 부족해져 시스템 장애가 올 수 있다.
- Spring에서는 @Transactional로 트랜잭션을 만드는데, 3개의 db조작 method를 모아서 하나의 method에 @Transactional을 달아준다.
- 그럼 그 3개 중 하나만 잘못돼도 모두 rollback되는데, 이러한 작용을 할 때 transaction이 너무 긴 건 아닌지 고려해야 할 때도 있다.
DML -constraint 해제로 성능 향상
- 인덱스가 있으면 DML 성능이 더 떨어진다.
- 테이블 data block에 추가하는 것에 더해 index leaf block에도 추가해야 하기 때문이다.
- 그런데 index leaf block을 찾기 위해 수직적 탐색이 있어야 한다.
- 거기다 인덱스는 정렬된 집합이라서 정렬순서를 맞춰줘야 해서 관련 operation이 모두 이뤄진다.
- index가 PK만 있다면 100만건을 넣을 때 4초지만, index를 2개 더 넣으면 40초다.
- index 중에서도 PK, FK제약이 DML 성능에 미치는 영향이 크다.
- 사용자 정의 제약은 그정도로 큰 편은 아니다.
- 설명했듯 index와 무결성 제약은 DML 성능을 떨어뜨린다.
- 그래서 배치 프로그램에서는 해당 기능을 해제하는 경우도 많다.
- 테스트 데이터를 넣은 테이블을 만들어보자.
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 <= 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);
- source table에 천만건의 데이터가 생겼다.
- 이제 이를 target table에 넣어보자.
- PK와 일반 INDEX 총 2개로 천만개를 넣으면 1분 19초가 걸린다.
SET TIMING ON;
INSERT /*+ append */ into target
SELECT * FROM source;
commit;
- PK제약과 index를 해제해보자.
TRUNCATE TABLE target;
ALTER TABLE target MODIFY CONSTRAINT target_pk DISABLED DROP index; --PK 제약 해제
ALTER INDEX target_x1 UNUSABLE; --일반 INDEX 해제
ALTER SESSION SET skip_unusable_indexes = true; --INDEX unusable 데이터 입력가능하게 설정. 기본이 true
- 해제하고서 다시 insert를 했다.
- 그럼 5초만에 끝난다.
SET TIMING ON;
INSERT /*+ append */ into target
SELECT * FROM source;
commit;
- 그리고 다시 PK와 일반 index제약을 생성해주자.
- 재활성화에 8초가 걸린다.
- 총 13초밖에 안걸린다. 제약이 있는 상태에서 insert는 1분 19초가 걸렸다.
ALTER TABLE target MODIFY CONSTRAINT target_pk enable NOVALIDATE; -- PK제약 재활성화
ALTER INDEX target_x1 rebuild; -- 일반 index 제약 활성화
- NOVALIDATE 옵션을 써서 더 빠른 것도 있다.
- 데이터 무결성 확신이 없다면 아래 query를 통해 확인해보자.
SELECT no, empno, count(*)
FROM source
GROUP BY no, empno
HAVING COUNT(*) > 1;
DML- array를 통한 batch insert로 성능 향상
- db call은 3가지 종류가 있다.
- parse call
- SQL 파싱과 최적화. 소프트파싱이면 최적화는 생략
- execute call
- SQL 실행. DML은 여기까지 끝
- fetch call
- resultSet을 네트워크로 전송하여 사용자가 볼 수 있게 함
- parse call
- 떄로는 인입 경로에 따라 2개로 나누기도 한다.
- user call
- 네트워크를 경유해 DB외부에서 들어오는 call
- 대부분 WAS에서 넘어오는 call이다.
- recursive call
- DB 내부에서 발생하는 call.
- 주로 parse call에서 발생하는 데이터 딕셔너리조회
- PL/SQL로 만들어진 함수/프로시저/트리거에 내장된 SQL 실행
- user call
- db call이 많을수록 당연히 성능이 느리다.
- 그 중에 user call은 특히나 성능에 미치는 영향이 크다.
- WAS와 같은 LNA안에 있으면 낫지만, 다른 LAN이라면 특히 더 크다.
- PL/SQL로 recursive call을 하면 29초인것도 Java로 user call하면 210초 걸린다.
- 그래서 하나의 SQL에서 모든 business를 다 처리하는 게 중요하다.
- 그게 불가능하다면 array processing을 활용하는 게 좋다.
public void execute() throws Exception {
int arraySize = 10000;
long[] no = new long [arraySize];
long[] empno = new long [arraySize];
long[] ename = new long [arraySize];
long[] job = new long [arraySize];
long[] mgr = new long [arraySize];
long[] hiredate = new long [arraySize];
long[] sal = new long [arraySize];
long[] comm = new long [arraySize];
long[] deptno = new long [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); //i = 0이돼도 rs.next로 인해 어차피 다음 record를 받게 됨.
empno [i] = rs.getLong(2);
ename [i] = rs.getString(3);
job[i] = rs.getString(4);
mgr[i] = rs.getInt(5);
hiredate[i] = rs.getString(6);
sal[i] = rs.getLong(7);
comm[i] = rs.getLong(8);
deptno[i] = rs.getInt(9);
i = i + 1
if(i == arraySize) {
insertTarget(i,no,empno,ename,job,mgr,hiredate,sal,comm,deptno);
i = 0;
}
}
//10,000개를 다 못채운 경우도 insert
if(i > 0) {
insertTarget(i,no,empno,ename,job,mgr,hiredate,sal,comm,deptno);
}
rs.close();
st.close();
}
- insert 함수는 아래와 같다.
public void insertTarget(int length
,long[] p_no
,long[] p_empno
,String[] p_ename
,String[] p_job
,int [] p_mgr
,String[] p_hiredate
,long[] p_sal
,long[] p_comm
,int[] p_deptno) throws Exception {
String SQLStmt = "insert into target "
+ "(no, empno, ename, job, mgr, hiredate, sal, comm, deptno) "
+ "values (?, ?, ?, ?, ?, to_date(?, 'yyyymmdd hh24miss'), ?, ?, ?)"
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.setString (4, p_job [i]);
st.setInt (5, p_mgr [i]);
st.setString (6, p_hiredate [i]);
st.setLong (7, p_sal [i]);
st.setLong (8, p_comm [i]);
st.setInt (9, p_deptno [i]);
st.addBatch();
};
st.executeBatch();
st.close();
}
비효율적 update -scalar subquery
- 전통적인 update문으로는 다른 table과 join이 필요한 update를 할 때 select를 여러 번 하는 비효율이 발생한다.
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)))
- 약 4번의 SELECT를 하는 걸 2번의 SELECT로 고칠 수도 있다.
- 다만 여기도 총고객수가 엄청나게 많다면 버벅거릴 수 있다.
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 서브쿼리를 hash semi join으로 바꿨다.
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 -join view
- 수정 가능 조인 뷰를 활용하면 참조 테이블과 두 번 조인하는 비효율마저도 없앨 수 있다.
- 단, 수정 가능 조인 뷰의 경우, 1 : M 집합 중 M 집합만 DML이 허용된다.
- 또한 1쪽 집합에 반드시 unique index 설정이 있어야만 한다.
- 즉 키보존 테이블이어야 한다는 의미다.
- 키보존 테이블이란view에 rowid를 제공할 수 있는 unique index를 가진 column이다.
- 다만 아래 쿼리는 12c 이상 버전부터만 실행된다.
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
)
SET 최종거래일시 = 거래이릿
, 최근거래횟수 = 거래횟수
, 최근거래금액 = 거래금액
- 11g에서는 위의 update를 사용할 수가 없고, merge into문으로 바꿔줘야 한다.
- 12c 이상 버전부터는 unique index가 없어도 inline view에 group by를 쓰면 에러가 나지 않는다.
- GROUP BY를 한 집합과 조인한 테이블은 key가 보존된다는 점을 오라클에서 인정한 것이다.
효율적 update -merge
- 전통적 방법은 update를 할 때 table을 여러번 scan해야 하는 문제가 있었다.
- 아래와 같이 만들면 최소 2번은 scan해야 한다.
UPDATE dept d
SET d.avg_sal = (
SELECT ROUND(AVG(e.sal), 2)
FROM emp e
WHERE e.deptno = d.deptno
)
WHERE EXISTS (
SELECT 1
FROM emp e2
WHERE e2.deptno = d.deptno
);
- 그래서 위에서 소개한 join view가 등장했다.
- join view를 통해 scan을 여러번 하는 비효율은 해결했다.
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 statement가 등장했다.
- 이를 더 간단하게 대체하면 아래와 같이 바뀐다.
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;
- 다만 데이터 검증을 merge 안에 포함해서 쓰면 더 복잡해진다.
- ROWID를 사용했다고 해도 ROWID는 물리 포인터가 아니기 떄문에 성능과 무관하다.
- 또한 검증용 SELECT에서 1번 scan하고, MERGE 할 때 left outer join을 위해 1번 scan하니 2번 scan하게 된다.
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)
WHERE MATCHED THEN UPDATE SET T2.ENMAE = S.ENMAE;
- 그럴땐 1번 SCAN하는 join view 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;
- merge문의 기본 구성은 아래와 같다.
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, ...) values
(s.cust_id, s.cust_nm, s.email, s.tel_no, s.region, s.addr....)
- 두 개 중 하나만 선택해서 할 수도 있다.
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, ...) values
(s.cust_id, s.cust_nm, s.email, s.tel_no, s.region, s.addr....)
- on 외에도 다른 조건절을 where로 걸 수도 있다.
- 이미 저장된 데이터를 조건에 따라 지울 수도 있다.
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.withdraw_dt is not null
WHEN NOT MATCHED THEN insert
(cust_id, cust_nm, email, ...) values
(s.cust_id, s.cust_nm, s.email, s.tel_no, s.region, s.addr....)
효율적 INSERT -배치용 direct path I/O
- direct path는 버퍼캐시를 경유하지 않고 직접 data block을 읽고 쓰는 I/O다.
- 특히 대량 데이터를 읽을 때 많이 사용된다. 대용량 처리 프로그램이 읽은 data를 버퍼캐시에 넣는 건 재활용성이 나쁘기 때문이다.
- 아래와 같은 경우에 direct path I/O가 수행된다.
- 병렬힌트로 select/insert
- direct 옵션 지정하고 SQL Loader로 데이터 적재
- insert … select문에 append hint 사용
- 쿼리에 병렬 hint를 제공하면 병렬도만큼 병렬 프로세스가 떠서 동시에 작업을 한다.
SELECT /*+ full(t) parallel(t 4) */ * FROM big_table t;
- direct path I/O 시에 병렬 DML을 같이 활용하면 효과가 훨씬 좋아진다.
- 위처럼 병렬도를 4로 지정하면 성능이 4배가 아니라 수십 배 빨라진다.
- direct path I/O가 빠른 이유는 아래와 같다.
- freelist를 참조하지 않고 HWM 바깥 영역에 데이터를 순차적으로 입력한다.
- 블록을 버퍼캐시에서 탐색하지 않는다.
- 버퍼캐시에 적재하지 않고, 데이터파일에 직접 기록한다.
- read consistency, transaction rollback, transaction recovery를 위한 undo 로깅이 없다.
- redo 로깅을 안 할수도 있다.
- 한 마디로 많은 절차가 생략되기 때문에 빠른 것이다.
- 성능은 빨라지지만 주의점이 있다.
- exclusive lock이 걸린다. 따라서 transaction이 일어나는 주간에는 쓰면 안 된다.
- table에 여유 공간이 있어도 table 바깥 영역에 할당되어 디스크를 낭비할 수도 있다.
- 특히 레인지 파티션 테이블이면 이게 문제가 될 수 있다.
- 과거 데이터를 delete해도 의미가 없고, 반드시 파티션을 drop해야만 디스크 공간을 반환하기 때문이다.