1. HOME
  2. [종료]구루비 DB 스터디
  3. 2013년 하반기 - 오라클 트러블슈팅 스터디
  4. 3.DML 튜닝

3.DML 튜닝 (by xsoft) [2013.11.13]

목차

1. 인덱스 유지 비용
2. Insert 튜닝
3. Update 튜닝

1. 인덱스 유지 비용

  • 인덱스가 많은 테이블에 UPDATE 수행 시 레코드는 직접 변경하지만, 인덱스는 Delete/Insert 방식으로 처리되고, 정렬상태 유지를 해야하며 Undo 레코드도 2개씩 기록되므로 인덱스 개수에 따라 UPDATE 성능이 좌우됨.
  • 대량 데이터를 DML 할 때, 인덱스를 DROP 또는 Unusable 상태로 변경한 다음 작업하는 것이 빠를 수 있음

2. Insert 튜닝

가) Oracle Insert 튜닝

1) Direct Path Insert
  • 힙 구조 테이블 Insert
    • 순서 없이 Freelist로부터 할당받은 블록에 무작위로 값을 입력함
    • Freelist에서 할당받은 블록을 버퍼 캐시에서 찾아보고, 없으면 데이터 파일에서 읽어 캐시에 적재 후 데이터를 Insert함
  • Direct Path Insert
    • Freelist를 거치지 않고 HWM 바깥 영역에, 버퍼 캐시를 거치지 않고 데이터 파일에 바로 Insert함
    • 또한 Undo 데이터를 만들지 않기 때문에 속도 향상에 도움을 줌
  • Direct Path Insert 방법
    • 'INSERT /*+ APPEND / SELECT ' 내용처럼 /+ APPEND */ 힌트 사용
    • 병렬 모드로 INSERT
    • direct 옵션을 지정하고 SQL*Loader(sqlldr)로 데이터를 로드
    • CTAS 문장을 수행

2) nologging 모드 Insert

  • 테이블 속성을 nologging으로 바꿔주면 Redo 로그까지 최소화되므로 더 빠르게 insert 가능
    • 이 기능은 Direct Path Insert일 때마 작동함
  • 단점
    • Direct Path Insert 시 해당 테이블 전체에 Exclusive 모드 Lock이 걸림
    • nologging 상태에서 입력한 데이터는 장애 발생 시 복구가 불가능하므로 백업을 반드시 병행해야함

나) SQL Server Insert 튜닝

1) 최소 로깅(minimal nologging)
  • 최소 로깅 기능을 사용하려면, 우선 해당 데이터베이스의 복구 모델 (Recovery Model)이 'Bulk-logged' 또는 'Simple'로 설정돼 있어야 함

alter database SQLPRO set recovery SIMPLE
  • 첫 번째로, 아래와 같이 파일 데이터를 읽어 DB로 로딩하는 Bulk Insert 구문을 사용할 때, With 옵션에 TABLOCK 힌트를 추가하면 최소 로깅 모드로 작동함

BULK INSERT AdventureWorks.Sales.SalesOrderDetail
FROM 'C:￦orders￦Iineitem.txt'
WITH
(
DATAFILETYPE    = 'CHAR',
FIELDTERMINATOR = ' |',
ROWTERMINATOR   = ' |\n'，
TABLOCK
)
  • 두 번째로, Oracle CT AS와 같은 문장이 select into 인데， 복구 모델이 'BULK-logged' 또는 'Simple'로 설정된 상태에서 이 문장을 사용하면 최소 로깅 모드로 작통함

SELECT *
INTO   TARGET
FROM   SOURCE;
  • 세 번째로, SQL Server 2008 버전부터 최소 로깅 기능을 일반 Insert문에서 활용할 수 있게 됨
  • 힙(Heap) 테이블에 Insert할 땐 아래와 같이 간단히 TABLOCK 힌트를 사용하면 되며, X 테이블 Lock 때문에 여러 트랜잭션이 동시에 Insert 할 수 없게 되므로 주의해야 함

insert into t_heap with (TABLOCk) select * from t_source

3. Update 튜닝

가) Truncate & Insert 방식 사용


UPDATE 주문
SET    상태코드 = '9999'
WHERE  주문일시 < TO_DATE('20000101', 'yyyymmdd')
;
  • 대량의 데이터를 위와 같이 UPDATE 하면 오래 걸리며(DELETE도 마찬가지임) 그 이유는 아래와 같음
    • 테이블 데이터를 갱신하는 본연의 작업
    • 인텍스 데이터까지 갱신
    • 벼퍼 캐시에 없는 블록를 디스크에서 읽어 버퍼 캐시에 적재한 후에 갱신
    • 내부적으로 Redo와 Undo 정보 생성
    • 블록에 빈 공간이 없으면 새 블록 할당(→ Row Migration 발생)
  • 대량의 데이터를 UPDATE 해야 할 경우 아래와 같이 진행하면 성능 개선 가능(포인트는 인덱스 없이 수행)

-- Oracle
CREATE TABLE 주문_임시 AS 
SELECT *
FROM   주문
;

-- SQL Server
SELECT *
INTO   #EMP_TEMP
FROM   EMP
;

ALTER TABLE EMP DROP CONSTRAINT 주문_PK;

DROP INDEX [주문.]주문_idx1;

TRUNCATE TABLE 주문;

INSERT INTO 주문(고객번호, 주문일시, 상태코드)
SELECT 고객변호, 
       주문일시,
       (CASE WHEN 주문일시 > = TO DATE('20000101' 'yyyymmdd') THEN '9999'
             ELSE STATUS
             END) 상태코드
FROM   주문_임시
;

ALTER TABLE 주문 ADD CONSTRAINT 주문_PK PRIMARY KEY(고객변호, 주문일시) ;

CREATE INDEX 주문_idx1 ON 주문(주문일시, 상태코드);

나) 조인을 내포한 Update 튜닝

  • 조인을 내포한 Update문을 수행할 때는, Update 자체의 성능보다 조인 과정에서 발생하는 비 효율 때문에 성능이 느려 지는 경우가 더 많음.
1) 전통적인 방식의 Update문

UPDATE 고객
SET    (최종거래일시,
        최근거래금액) = (SELECT MAX(거래일시) ， SUM(거래금액)
                         FROM   거래
                         WHERE  고객번호 = 고객.고객번호
                         AND    거래일시 > = TRUNC(ADD_MONTHS(SYSDATE, -1)))
WHERE  EXISIS (SELECT 'x'
               FROM   거래
               WHERE  고객번호 = 고객.고객변호
               AND    거래일시 > = TRUNC(ADD_MONTHS(SYSDATE, -1)));
  • 성능을 위해서는 기본적으로 거래 테이블에 <고객번호, 거래일시> 인텍스가 있어야 함
  • 그러나 인텍스가 있어도 고객별 거래 데이터가 많으면, 랜덤액세스로 인해 성능을 낼 수 없음
    이 경우 서브쿼리에 /*+ UNNEST HASH_SJ */ 힌트를 사용하는 것이 효과적임
  • 그러나 이 힌트는 두 개의 서브쿼리에 사용해야 하므로 비효율은 여전히 남음
2) SQL Server 확장 Update문 활용

UPDATE 고객
SET    최종거래일시 = B 거래일시,
       최근거래금액 = B 거래금액 
FROM   고객 A INNER JOIN (SELECT 고객변호,
                                 MAX(거래일시) 거래일시,
                                 SUM(거래금액) 거래금액
                          FROM   거래
                          WHERE  거래일시 > = DATEADD(MM, -1, CONVERT(DATETIME, CONVERT(CHAR(8), GETDATE(), 112), 112))
                          GROUP  BY 고객 변호
                          ) B 
ON      A.고객변호 = B.고객변호
3) Oracle 수정 가능 조인 뷰 활용

UPDATE /*+ BYPASS_UJVC */ 
       (SELECT C.최종거래일시,
               C.최근거래금액,
               T.거래일시,
               T.거래금액
        FROM    (SELECT 고객번호, 
                        MAX(거래일시) 거래일시,
                        SUM(거래금액) 거래금액
                 FROM   거래
                 WHERE  거래일시 >= TRUNC(ADD_MONTHS(SYSDATE, -1))
                 GROUP  BY 고객번호
                ) T,
                고객 C
        WHERE   C.고객번호 = T.고객번호
        ) 
SET    최종거래일시 = 거래일시, 
       최근거래금액 = 거래금액
  • '조인 뷰'는 FROM절에 두 개 이상 테이블을 가진 뷰를 말하며, 조인 뷰를 통해 원본 테이블에 입력, 수정, 삭제가 가능함
    (제약사항 키-보존 태이블에만 입력 수정 삭제가 허용됨)
  • 키-보존 테이블(KEY-Preserved TABLE) 이란, 조인된 결과집합을 통해서도 중복 없이 UNIQUE하게 식별이 가능한 테이블을 말함.
  • 이를 위해선 UNIQUE한 집합과 조인되어야 하는데, 옵티마이저는 조인되는 테이블에 UNIQUE 인텍스가 있는지를 보고 UNIQUE 집합 여부를 판단하며, 결국 UNIQUE 인텍스가 없는 테이블과 조인된 테이블에는 입력, 수정, 삭제가 허용되지 않음
  • 위의 SQL을 볼 경우 고객 테이블이 커-보존 테이블이어야 하는데 거래 데이터를 집계한 인라인 뷰에 UNIQUE 인텍스가 존재할 수 없으므로 ORACLE은 고객 태이블을 커 보존 테이블로 인정하지 않음.
  • 집합적으로 UNIQUE성이 보장됨에도 불구하고 UNIQUE 인텍스를 찾을 수 없다는 이유로 옵티마이저가 필요 이상의 제약을 가한 셈인데, 다행히 이를 피해갈 수 있는 /*+ BYPASS_UJVC */ 힌트가 제공됨.
    (주의 : 이 힌트는 UPDATE를 위해 참조하는 집합에 중복 레코드가 없음이 100% 보장될 때만 사용해야 함)
  • 11g 부터는 지원 안함
4) Oracle Merge문 활용
  • MERGE INTO문을 이용하면 하나의 SQL 안에서 INSERT, UPDATE, DELETE 작업을 한번에 처리할 수 있음
  • 이 기능은 ORACLE 9i부터 제공되기 시작했고, DELETE 작업까지 처리할 수 있게 된 것은 10g 부터 지원함
    (SQL-SERVER도 2008 벼전부터 이 문장을 지원하기 시작함)
  • MERGE_INTO 예제

MERGE INTO 고객 T
USING 고객변경분 S
ON (T.고객변호 = S.고객번호)
WHEN MATCHED THEN
    UPDATE
    SET    T.고객변호 = S.고객번호,
           T.고객명   = S.고객명,
           T.이메일   = S.이메일
WHEN NOT MATCHED THEN
    INSERT (고객번호, 고객명, 이메일, 전화번호, 거주지역, 주소, 등록일시)
    VALUES (S.고객변호, S.고객명, S.이메일, S.전화번호, S.거주지역, S.주소, S.등록일시)
;
  • MERGE_INTO에서 UPDATE만 처리

MERGE INTO 고객 T
USING 고객변경분 S
ON (T.고객번호 = S.고잭변호)
WHEN MATCHED THEN
    UPDATE
    SET    T.고객번호 = S.고객변호,
           T.고객명   = S.고객명,
           T.이메일   = S.이메일
;
  • MERGE_INTO에서 INSERT만 처리

MERGE INTO 고객
USING 고객변경분 S
ON (T.고객번호 = S.고객번호)
WHEN NOT MATCHED THEN
    INSERT (고객번호, 고객명, 이메일, 전화번호, 거주지역, 주소, 등록일시)
    VALUES (S.고객번호, S.고객명, S.이메일, S.전화번호, S.거주지역, S.주소, S.등록일시)
;
  • /*+ BYPASS_UJVC */ SQL 대체

MERGE INTO 고객 C
USING (SELECT 고객변호,
              MAX(거래일시) 거래일시,
              SUM(거래금액) 거래금액
       FROM   거래
       WHERE  거래일시 >= TRUNC(ADD_MONTHS(SYSDATE, -1)) 
       GROUP BY 고객변호) T
ON (C.고객번호 = T.고객변호)
WHEN MATCHED THEN
    UPDATE
    SET    C.최종거래일시 = T.거래일시,
           C.최근거래금액 = T.거래금액
;

문서에 대하여

  1. HOME
  2. [종료]구루비 DB 스터디
  3. 2013년 하반기 - 오라클 트러블슈팅 스터디
  4. 3.DML 튜닝