DML 비용과 Direct Path

#핵심 요약

테이블은 빈 공간을 찾아 바로 넣지만, 인덱스는 정렬 구조라 알맞은 위치를 찾는 탐색 비용이 듦
인덱스가 많을수록 INSERT가 느려짐

대량 입력은 데이터를 먼저 넣고 인덱스를 나중에 만드는 순서가 빠름
실습에서는 인덱스를 PARALLEL 4 NOLOGGING으로 만든 뒤 병렬도와 로깅을 원래대로 돌렸음

UPDATE는 변경 컬럼을 포함한 인덱스만 영향을 받음
인덱스 키가 바뀌면 기존 위치에서 지우고 새 위치에 넣음

COMMIT 완료는 Redo가 Online Redo Log에 기록되었다는 뜻이며, 그 완료를 기다리는 대기가 log file sync임
데이터 블록은 DBWR가 나중에 기록함

병렬 쿼리 Full Scan, 병렬 DML, Direct Path Insert, Temp 세그먼트 읽기, 쓰기 등은 버퍼 캐시를 거치지 않는 Direct Path I/O를 씀

#개념과 동작 원리

#UPDATE와 COMMIT 처리 과정

실습 5단계 설명은 "3번에서 만든 Undo"라고 적었지만 Undo는 4단계에서 만듦

COMMIT: SCN 할당, 커밋 레코드를 Redo Log Buffer에 추가, LGWR가 버퍼를 Online Redo Log에 동기 기록, 서버 프로세스는 그동안 log file sync로 대기, 완료 후 "commit complete" 반환과 락 해제
데이터 블록은 즉시 기록되지 않고(Fast Commit), Undo는 읽기 일관성을 위해 일정 기간 보존됨
퀴즈 해설: 루프 안에서 행마다 COMMIT하면 LGWR flush가 반복되어 느려짐
리두와 언두의 기본 개념은 언두와 리두에 있음

#Direct Path I/O가 일어나는 경우

1. 병렬 쿼리로 Full Table Scan할 때
2. 병렬 DML을 수행할 때
3. Direct Path Insert를 수행할 때
4. Temp 세그먼트 블록을 읽고 쓸 때
5. direct 옵션을 지정하고 export할 때
6. NOCACHE 옵션 LOB 컬럼을 읽을 때

퀴즈의 Direct Path Insert 주의점: COMMIT 전에 같은 세션에서 대상 테이블을 조회하면 ORA-12838이 날 수 있고, 입력 중 대상 테이블에 Exclusive TM 락이 걸려 다른 DML이 막히며, High Water Mark 바깥에 입력하므로 DELETE로 생긴 빈 공간을 재사용하지 않음
그래서 트랜잭션이 많은 주간에 쓰기 어려움
INSERT INTO t NOLOGGING SELECT...의 NOLOGGING은 테이블 별칭으로 해석될 뿐 로깅 기능과 무관함

#주요 용어와 구성 요소

#필수 명령어 또는 SQL

#인덱스 수와 INSERT 시간

실습 환경: source 100만 건(emp 10행 × 10만), 빈 target에 PK (no, empno)

INSERT INTO target SELECT * FROM source;           -- PK 인덱스만: 실습 4.149초

TRUNCATE TABLE target;
CREATE INDEX target_x1 ON target (ename);
CREATE INDEX target_x2 ON target (deptno, mgr);
INSERT INTO target SELECT * FROM source;           -- 인덱스 3개: 실습 43.411초

결과 해석:

실습 해설은 인덱스 1개에서 3개로 늘자 "약 8배" 느려졌다고 적었지만 기록된 시간으로는 약 10배임

실습 답(입력 후 인덱스 생성):

TRUNCATE TABLE target;
DROP INDEX target_x1;
DROP INDEX target_x2;

INSERT /*+ append */ INTO target SELECT * FROM source;
COMMIT;

CREATE INDEX target_x1 ON target (ename) PARALLEL 4 NOLOGGING;
CREATE INDEX target_x2 ON target (deptno, mgr) PARALLEL 4 NOLOGGING;

ALTER INDEX target_x1 PARALLEL 1 LOGGING;
ALTER INDEX target_x2 PARALLEL 1 LOGGING;

SELECT index_name, degree, logging
  FROM user_indexes
 WHERE index_name IN ('TARGET_X1', 'TARGET_X2');

결과 해석:

실습에서는 이 방식의 소요 시간을 적지 않았음
Direct Path Insert 후 같은 세션에서 대상 테이블을 다루기 전에 커밋해야 함

#무결성 제약과 인덱스가 입력 시간에 주는 영향

같은 source 100만 건을 빈 target에 넣으며 제약과 인덱스를 하나씩 더했음(실습 기록, 환경마다 다름)

UPDATE와 DELETE도 WHERE 조건 컬럼에 맞는 인덱스가 있으면 INDEX RANGE SCAN으로 처리 대상 행만 찾아감

EXPLAIN PLAN FOR
UPDATE /*+ index(target, target_idx2) */ target
   SET sal = 7000
 WHERE job = 'SALESMAN';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

결과 해석:

실습 기록: 실행계획의 UPDATE 아래에 INDEX RANGE SCAN TARGET_IDX2가 나왔음
대량 적재 때는 인덱스와 제약이 입력 시간을 크게 늘리므로, 적재 전에 해제하고 적재 뒤 다시 만드는 방법을 씀

#서브쿼리를 쓴 DML의 실행계획

서브쿼리를 쓴 DML의 실행계획도 SELECT와 다르지 않아 조인 튜닝 원리를 그대로 적용함

EXPLAIN PLAN FOR
UPDATE emp e
   SET comm = 7000
 WHERE EXISTS (SELECT /*+ unnest nl_sj */ 'x'
                 FROM dept d
                WHERE d.deptno = e.deptno
                  AND d.loc = 'CHICAGO');

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

결과 해석:

dept(deptno)에 인덱스를 만들고 unnest nl_sj를 주면 세미 조인을 Nested Loops로 풂

인덱스가 없을 때 실습의 목표 실행계획은 HASH JOIN RIGHT SEMI 아래 DEPT 전체 스캔과 EMP 전체 스캔이었음(서브쿼리 힌트는 서브쿼리 변환에 있음)

#데이터베이스 Call 줄이기

SQL은 Parse Call(문법 검사와 실행계획), Execute Call(실행, DML은 여기서 끝남), Fetch Call(SELECT 결과 전송) 단계를 거침
발생 위치로는 WAS나 클라이언트에서 네트워크를 타고 들어오는 User Call과, DBMS 안에서 생기는 Recursive Call(딕셔너리 조회, PL/SQL 안의 SQL 등)로 나뉨
Call 1회마다 고정 비용이 있으므로 튜닝의 핵심은 Call 횟수를 줄이는 것임

BEGIN
  FOR r IN (SELECT * FROM 주문상품_소스) LOOP
    INSERT INTO 주문상품_적재
    VALUES (r.주문번호, r.상품코드, r.수량, r.단가, r.주문일시, r.상태코드);
  END LOOP;
  COMMIT;
END;
/

SELECT sql_id, executions, parse_calls, rows_processed, sql_text
  FROM v$sql
 WHERE sql_text LIKE 'INSERT INTO 주문상품_적재%'
 ORDER BY last_active_time DESC
 FETCH FIRST 3 ROWS ONLY;

결과 해석: EXECUTIONS가 약 100만이면 INSERT가 100만 번 따로 실행된 것임
PARSE_CALLS는 소프트 파싱이라 낮게 나오지만 반복되는 Execute Call 자체가 부하임
One SQL이 어려운 복잡한 로직이면 배열 처리로 Call 수를 줄임
소프트 파싱은 SQL과 실행계획을 라이브러리 캐시에서 찾아 최적화 단계를 건너뛰고 바로 실행으로 넘어가는 것임

#병렬 DML과 Direct Path Insert

INSERT /*+ enable_parallel_dml parallel(e 4) */ INTO target_emp e
SELECT /*+ full(s) parallel(s 4) */ * FROM source_emp s;

결과 해석:

실습 결과: PX COORDINATOR 아래 LOAD AS SELECT (HYBRID TSM/HWMB)와 OPTIMIZER STATISTICS GATHERING
실습에서는 같은 일을 세션 단위로 ALTER SESSION ENABLE PARALLEL DML을 먼저 실행하는 방식으로도 했음
병렬 쿼리 Full Scan(parallel(e 4))의 실행계획에는 PX BLOCK ITERATOR가 나왔음

실습 교재 438~439쪽 방식(더 빠르게):

ALTER TABLE target_emp NOLOGGING;

INSERT /*+ enable_parallel_dml full(t) parallel(t 4) */ INTO target_emp t
SELECT /*+ full(s) parallel(s 4) */ * FROM source_emp s;
COMMIT;

ALTER TABLE target_emp LOGGING;

결과 해석:

NOLOGGING Direct Path 작업은 데이터 redo가 충분히 남지 않아 기존 백업과 redo만으로 데이터를 재구성할 수 없는 범위를 만들 수 있음
작업 뒤 백업 여부를 확인해야 함

#테이블 CACHE 속성

ALTER TABLE emp CACHE;

SELECT table_name, cache FROM user_tables WHERE table_name = 'EMP';

결과 해석:

실습에서는 버퍼 캐시를 효율적으로 쓰는 방법으로 CACHE 속성을 소개하고 인덱스 스캔과 Full Scan을 각각 실행해 보게 했음
이 속성의 캐시 배치는 아래 옵션 설명으로 구분함
실습의 실제 비교 출력은 기재 없음

#SCN과 로그 버퍼 확인

SELECT current_scn FROM v$database;
SHOW PARAMETER log_buffer
SELECT * FROM v$bgprocess WHERE name LIKE 'LGW%';

결과 해석:

LGWR 프로세스 강제 종료는 인스턴스 장애를 일으킴
실습에서는 강제 종료 후 인스턴스가 내려가는 것을 확인하고 다시 시작했음

#구문과 옵션 설명

APPEND는 INSERT SELECT의 Direct Path를 요청하며 VALUES 방식에는 APPEND_VALUES를 사용함
PDML에는 세션 활성화 또는 ENABLE_PARALLEL_DML과 대상 병렬 조건을 확인함
트리거, 참조 제약 등 제한에 따라 Direct Path 요청이 일반 직렬 입력으로 처리될 수 있음

NOLOGGING은 모든 redo, undo 제거를 뜻하지 않음
데이터와 메타데이터의 기록 조건을 구분하고 FORCE LOGGING 여부를 확인함
작업 뒤 LOGGING으로 바꾸어도 앞서 생략된 데이터 redo를 복구하지는 못함

테이블의 CACHE는 Full Scan으로 읽은 블록을 LRU의 최근 사용 쪽에 두도록 지정함
Direct Read나 병렬 스캔을 선택한 경우에는 이 캐시 배치 설명이 그대로 적용되지 않으며 전체 테이블의 메모리 상주를 보장하지 않음

#사용 조건과 주의사항

실습 과제(구독 해지 MERGE 배치, 주문상세 집계로 상품 테이블을 갱신하는 UPDATE)의 답은 기재 없음
MERGE 문법은 MERGE에 있음

Direct Path 뒤 같은 트랜잭션에서 대상을 재접근하는 제한을 확인하고 트랜잭션 경계를 정함
LGWR 강제 종료 실습을 성능 확인 절차로 사용하지 않음