Batch 성능향상을 위한 기본원리와 실무사례

From DB가이드넷

 

(세션1)Batch 성능향상을 위한 기본원리와 실무사례

[세미나] Oracle User를 위한 비용기반 옵티마이저 최적화 전략
[1회차] 배치 성능향상을 위한 기본원리와 실무사례 - 74분
[2회차] 온라인 거래 처리시의 SQL성능 개선 실전 사례 - 87분
[3회차] 오라클 비용기반 옵티마이저 - 71분

*Overview of Parallel Execution

1. Parallel Execution(or Parallel Processing) 이란?
 1)SQL 구문을 병렬로 실행하면, 멀티 프로세스가 해당 SQL을 동시에 수행
 2)멀티 프로세스 간에 작업량을 분할(divide)함으로써 싱글 프로세스보다 빠르게 수행
 3)병렬(paralleism)은 하나의 프로세스가 모든 작업을 수행하는 대신, 작업 단위를 분할하여 여러 프로세스가 동시에 작업 일부를 수행하는 개념
 4)대용량 데이터베이스(특히 DSS,DW 등)에서 대량 데이터 처리 시 처리시간(throughput)을 적극적으로 감소시킬 수 있음
 5)많은 OLTP 작업이 빠르게 수행되는 환경에서 parallel processing 사용 시 전체 응답시간이 저하될 수 있으므로 주의!

 Serial Full Table Scan
 SELECT * FROM EMP;

 Parallel Full Table Scan
 SELECT /*+PARALLEL(A n) */
 FROM EMP A;

2. 병렬 처리 가능한 작업

 Access method : table scan, index full scan, partitioned index range scan 등
 Join method : nested loop, sort merge, hash, star transformation 등
 DDL statement : CREATE TABLE AS SELECT, CREATE INDEX, REBUILD INDEX, REBUILD INDEX PARTITION, MOVE/SPLIT/COALESCE PARTITION 등
                 LOB 컬럼을 포함하는 테이블에는 parallel DDL 사용 불가
 DML statement : INSERT AS SELECT, UPDATE, DELETE, MERGE 작업 등
                 대용량 DB의 테이블과 인덱스에 parallel DML 작업 시 속도 향상
 기타 SQL 연산 : GROUP BY, NOT IN, SELECT DISTINCT, UNION, UNION ALL, CUBE, ROLLUP, 집계합수, 테이블 함수 등
 Parallel query : SELECT 구문의 query 및 subquery
 SQL*Loader : SQL*Loader 사용 시 병렬 처리 가능
              대량 데이터 로딩 시 속도 향상을 위해서 parallel direct-path load 사용 예
              sqlldr USERID=SCOTT/TIGER CONTROL=LOAD1.CTL DIRECT=TRUE PARALLEL=TRUE
              sqlldr USERID=SCOTT/TIGER CONTROL=LOAD2.CTL DIRECT=TRUE PARALLEL=TRUE
              sqlldr USERID=SCOTT/TIGER CONTROL=LOAD3.CTL DIRECT=TRUE PARALLEL=TRUE

*Degree of Parallelism
1. 병렬도(DOP, Degree of Parallelism)
 1)하나의 작업(single operation)과 관련된 parallel exectuion server의 개수
 2)inter-operation parallelism에서 parallel execution server의 개수는 지정한 DOP의 2배임
 3)parallel execution server 세트는 2개까지 동시에 실행
 4)여러 사용자가 동시에 병렬 처리를 사용하면 CUP, Memory, Disk 자원이 빠르게 고갈됨.
 5)병렬도를 명시하지 않으면 DOP=PARALLEL_THREADS_PER_CPU*CPU_COUNT

 주)sginel operation
    - order by, full table scan 등과 같은 SQL 구문의 일부분
    - 두 테이블 조인 시 테이블 스캔, 조인, 소트 등 여러 단계의 작업을 거치게 되는데, 이들 중에서 한 단계의 작업을 의미

2.Parallel Execution 수행 과정
 1)테이블 전체 스캔
 SELECT /*+PARALEL(E, 2)*/
 *
 FROM SCOTT.EMP E;

 2)테이블 전체 스캔+정렬
 SELECT /*+PARALLEL(E, 2)*/
 *
 FROM SCOTT.EMP E
 ORDER BY ENAME;

 3)테이블 전체 스캔+집계+정렬
 SELECT /*+PARALLEL(E, 2)*/
        DEPTNO, COUNT(*) CNT
 FROM SCOTT.EMP E
 GROUP BY DEPTNO
 ORDER BY CNT;

 4)조인 case 1
 SELECT /*+ USE_HASH(E, D)
            PARALLEL(E, 2)
            PARALLEL(D, 2) */
        *
 FROM SCOTT.EMP E
    , SCOTT.DEPT D
 WHERE E.DEPTNO = D.DEPTNO
 ORDER BY E.ENAME;

 5)조인 case 2
 SELECT /*+ USE_HASH(E, D)
            PARALLEL(E, 2)
            PARALLEL(D, 2)
            PQ_DISTRIBUTE(E HASH, HASH) */
        *
 FROM SCOTT.EMP E
    , SCOTT.DEPT D
 WHERE E.DEPTNO = D.DEPTNO
 ORDER BY E.ENAME;
 

3.Parallel Execution 수행 과정
 SELECT TQ_ID, SERVER_TYPE, PROCESS, NUM_ROWS, BYTES
 FROM V$PQ_TQSTAT
 ORDER BY TQ_ID, SERVER_TYPE, DESC, PROCESS;

4.Partition-Wise 조인의 이점
 1)Reduction of Communications Overhead
  두 테이블이 이미 조인 컬럼으로 분할되어 있으므로 파티션 재분배(redistributing) 필요 없음
   ---> 각 parallel execution server가 매칭되는 파티션까리 조인
  RAC 환경에서 interconnect traffice 획기적으로 감소
   ---> 병렬 partition-wise 조인 사용 시 interconnect latency 감소

2)Reduction of Memory Requirements
 조인 테이블의 전체 집합을 조인하는 것보다 적은 메모리 요청
 직렬(serial) 조인의 경우, 한 번에 하나의 매칭 파티션끼리 조인
  ---> 데이터가 전체 파티션에 고르게 분산되어 있다면, 메모리 요청은 전체 파티션 개수로 나눈 값
 병렬(parallel) 조인의 경우, 메모리 요청은 병렬 조인하는 파티션 개수에 의해 결정
  ---> DOP=20 이고 파티션 개수가 100이라면, 한 번에 20개의 조인만 수행되므로 메모리 요청은 1/5(=20/100) 수준
 hash 조인의 build phase 동안 Disk에 기록할 확률이 줄어듬
  ---> 메모리 요청이 줄어들기 때문에 성능 향상에 직접적인 영향

 

1.절차형 사고방식에서 벗어나자!
 1)배치 SQL은 집합적 사고방식에 익숙해져야 효율적으로 작성할 수 있음.
 2)기존의 절차형 사고방식에 얽매이면
   ---> 손발이 고생하는 것은 참을 수 있지만 목표시간 초과는 어떻게???

2.대형 집합과 대형 집합 조인
ALTER SESSION SET DB_FILE_MULTIBLOCK_READ_COUNT=128;
-- 테이블 전체 스캔 또는 index fast full scan 처리 시 한 번에 128개의 블록(128*8KB=1MB)을 읽어오도록 설정.
-- 기본 설정이 16이므로 한 번 I/O 시 최대 8배(128/16)의 블록을 읽어올 수 있음.
   (이 시스템 환경에서는 128이 최대값이며 블록 크기는 8KB)

 

SELECT A.멤버십종류, A.멤버십등급, A.서비스등급, COUNT(*)
FROM (SELECT /*+ full(tmc) parallel(tmc, 2)
                 index_ffs(tsg) parallel_index(tsg, 2)
                 leading(tmc) use_hash(tmc, tsg) */
             tmc.멤버십종류 멤버십종류
           , tmc.멤버십등급 멤버십등급
           , substr(tsg.서비스등급, 1, 1) 서비스등급
      FROM 멤버십카드   tmc
         , 서비스계약GR tsg
     WHERE tmc.멤버십상태 IN ('정상', '정지')
     AND   tmc.멤버십종류 <> '5'
     AND   tsg.SVCCONTNUM = tmc.SVCCONTNUM
     AND   tsg.등급선정기준YYMM  = '201212'
 ) A
GROUP BY
 a.멤버십종류, a.멤버십등급, a.서비스등급
;

서비스계약GR_PK : SVCCONTNUM + 등급선정기준YYMM
서비스계약GR_X2 : 등급선정기준YYMM + 서비스등급 + SVCCONTNUM *****
멤버십카드_PK : 멤버십카드번호
멤버십카드_X1 : SVCCONTNUM + 멤버십가입일자 + 변경일시 + 멤버십상태
멤버십카드_X2 : 멤버십가입일자 + 카드신청일자

-- 멤버십카드 테이블을 병렬도(DOP, Degree of Parallelism) 2로 전체 스캔하도록 full+parallel 힌트 사용
-- 서비스계약GR_X2 인덱스가 필요한 컬러믈 모두 포함하고 있으므로 병렬로 index fast full scan하도록 index_ffs + parallel_index 힌트 사용
-- tmc(=멤버십카드) 테이블의 대상 집합이 더 작으므로 먼저 액세스하도록 leading 힌트 사용
    ---> tmc의 대상집합 889만 건, tsg의 대상집합 2024만 건
-- 두 개 테이블을 hash 조인하도록 use_hash 힌트 사용

3.Driving 집합이 소형인 경우

1) 1단계

 ALTER SESSION SET DB_FILE_MULTIBLOCK_READ_COUNT=128;
 SELECT /*+ use_hash(ts, tsch) */
        ts.식별번호코드
     ,  tsch.처리조직ID
     ,  COUNT(*)
     ,  COUNT(CASE WHEN tsch.변경후값='010' THEN 1 END)
 FROM 서비스계약MT   ts
    , 서비스계약이력 tsch

 .....................
 GROUP BY ts.식별번호코드
        , tsch.처리조직ID;

2) 2단계: 2차 개선 후 SQL ---> 조인 방법 변경
 -- 2차 개선 후 37초
 -- 경우에 따라 적절한 조인 방법을 선택할 것
 -- 테이블의 크기가 아니라 대상 집합의 크기가 중요 ---> 10억 건 중 3만 3천건이 대상임

 ALTER SESSION SET DB_FILE_MULTIBLOCK_READ_COUNT=128;
 SELECT /*+ leading(tsch) use_nl(ts)             ---> tsch(=서비스계약이력) 테이블 먼저 액세스 후 ts(=서비스계약MT) 테이블과 netsted loops 조인하도록 use_nl 힌트 사용
            full(tsch) parallel(tsch, 2) */      ---> 서비스계약이력 테이블의 파티션 별 건수는 평균 2400만건: 병렬도 2로 전체 스캔하도록 full + parallel 힌트 사용
        ts.식별번호코드
     ,  tsch.처리조직ID
     ,  COUNT(*)
     ,  COUNT(CASE WHEN tsch.변경후값='010' THEN 1 END)
 FROM 서비스계약MT   ts
    , 서비스계약이력 tsch

 .....................
 GROUP BY ts.식별번호코드
        , tsch.처리조직ID;

4.스칼라 서브쿼리의 재구성
*****  스칼라 서브쿼리는 병렬처리가 안된다.
*****  스칼라 서브쿼리를 outer 조인으로 변경
-- 실행겨과의 어느 부분의 병목현상이 발생했는가?
-- 개선 포인트는 무엇인가?

1)개선 전 SQL

ALTER SESSION SET DB_FILE_MULTIBLOCK_READ_COUNT=128;

SELECT /*+leading(tccl) use_hash(tic, tccl)
        full(tic) parallel(tic, 2)
        full(tccl) parallel(tccl, 2) */
       tic.상담원ID
    ,  tic.상담일자
    ,  tic.상담시각
    ,  (SELECT /*+ index(tsg, 서비스계약GR_PK) */
               tsg.서비스등급
        FROM 서비스계약GR tsg
        WHERE tsg.SVCCONTNUM = tic.SVCCONTNUM
        AND   tsg.등급선정기준YYMM = '201212'
       ) 서비스등급
     , tccl.메인상담코드
     , tccl.서브상담코드
     , tccl.상세상담코드1
     , tccl.상세상담코드2
     , tccl.상세상담코드3
FROM IB상담마스터 tic
  ,  상담코드목록 tccl
WHERE tic.상담코드 = tccl.상담코드
AND   tic.상담일자 = '20121201'
;

2)개선 후 SQL ---> 스칼라 서브쿼리를 outer 조인으로 변경

ALTER SESSION SET DB_FILE_MULTIBLOCK_READ_COUNT=128;

SELECT /*+leading(tccl, tic) use_hash(tic, tccl, tsg) --- (1)
        full(tic) parallel(tic, 2)
        full(tccl) parallel(tccl, 2)
        index_ffs(tsg) parallel_index(tsg, 2)*/       --- (1)
       tic.상담원ID
    ,  tic.상담일자
    ,  tic.상담시각
    ,  (SELECT /*+ index(tsg, 서비스계약GR_PK) */
               tsg.서비스등급
        FROM 서비스계약GR tsg
        WHERE tsg.SVCCONTNUM = tic.SVCCONTNUM
        AND   tsg.등급선정기준YYMM = '201212'
       ) 서비스등급
     , tccl.메인상담코드
     , tccl.서브상담코드
     , tccl.상세상담코드1
     , tccl.상세상담코드2
     , tccl.상세상담코드3
FROM IB상담마스터 tic
  ,  상담코드목록 tccl
  ,  서비스계약GR tsg                        --- (2)
WHERE tic.상담코드 = tccl.상담코드
AND   tic.상담일자 = '20121201'
AND   tic.SVCCONTNUM = tsg.SVCCONTNUM (+)    --- (3)
AND   '201212' = tsg.등급선정기준YYMM (+)    --- (3)
;

(1)대상 집합이 작은 순서대로 액세스하도록 leading 힌트 수정
     ---> outer 조인은 집합 크기를 줄이지 못하므로 가능한 나중에 조인하는 것이 좋다.
   세 개의 테이블을 hash 조인하도록 use_hash 힌트 사용
   서비스계약GR_X2 인덱스가 필요한 컬럼을 모두 포함하고 있므로 병렬로 index fast full scan 하도록 index_ffs + parallel_indext 힌트 사용
  
(2)스칼라 서브쿼리를 사용하면 병렬 처리가 불가능하므로
   from 절에 기술 후 병렬 outer 조인할 것

(3)스칼라 서브쿼리 내 조인은 outer조인과 동일하므로 (+)기호를 사용하여 조인.

(가)IB상담마스터 테이블 : 전체 데이터 371,044,837건 중 조건에 해당하는 데이터는 362,231건
    IB상담마스터_PK : 상담원ID + 상담일자 + 상담시각

(나)상담코드목록 테이블 : 전체 데이터 45,270건
    상담코드목록_PK : 상담코드

(다)서비스계약GR 테이블 : 전체 데이터 77,115,650건 중 조건에 해당하는 데이터는 19,807,226건
    서비스계약GR_PK : SVCCONTNUM + 등급선정기준YYMM
    서비스계약GR_X2 : 등급선정기준YYMM + 서비스등급 + SVCCONTNUM

***** 메인 SQL의 결과 집합이 중형 이상이면, 스칼라 서브쿼리 사용 시 주의!!!!
      하나, 서브쿼리 내 테이블이 중대형급 이상이면 outer 조인으로 변경 후 병렬 조인할 것.
      둘, 서브쿼리 내 테이블이 코드성의 소형 테이블이면 개선 전 SQL처럼 그대로 사용해도 큰 문제 없지만
          ---> 프로그램 내에서 정기적으로 실행되는 SQL은 가능한 병렬 outer hash 조인 권장

5. ROW_NUMBER()의 활용
1)개선 전 SQL

ALTER SESSION SET DB_FILE_MULTIBLOCK_READ_COUNT = 128;

SELECT /*+ leading(tsg) use_hash(ts, tsg)
           full(ts) parallel(ts, 4)
           full(tsg) parallel(tsg, 4) */
       tsg.상담원ID
     , COUNT(CASE WHEN ts.서비스상태='정상' THEN 1 END)
     , COUNT(CASE WHEN ts.서비스상태='정지' THEN 1 END)
FROM  서비스계약MT ts
    , 서비스계약GR tsg
WHERE tsg.SVCCONTNUM = ts.SVCCONTNUM
AND   tsg.VIP여부    = 'Y"
AND   tsg.탈락일자 IS NULL
AND   tsg.서비스등급 LIKE 'V%'
AND   tsg.등급선정기준YYMM =
     (SELECT MAX(tsg2.등급선정기준YYMM)
      FROM   서비스계약GR tsg2
      WHERE  tsg2.SVCCONTNUM = tsg.SVCCONTNUM
      GROUP BY tsg2.SVCCONTNUM
     )
GROUP BY tsg.상담원ID;

2)개선 후 SQL ---> ROW_NUMBER() 함수로 최종 건 확인

ALTER SESSION SET DB_FILE_MULTIBLOCK_READ_COUNT = 128;

SELECT /*+ leading(tsg) use_hash(ts, tsg)
           index_ffs(ts) parallel_index(ts,4) */         --- (2)
       tsg.상담원ID
     , COUNT(CASE WHEN ts.서비스상태='정상' THEN 1 END)
     , COUNT(CASE WHEN ts.서비스상태='정지' THEN 1 END)
FROM  서비스계약MT ts
    ,(SELECT /*+ full(tsg) parallel(tsg, 4)*/
             tsg.SVCCONTNUM
           , tsg.VIP여부
           , tsg.탈락여부
           , tsg.서비스등급
           , tsg.상담원ID
           , ROW_NUMBER()                               --- (1)
             OVER       (PARTITION BY tsg.SVCCONTNUM
                         ORDER BY tsg.등급선정기준YYMM DESC
                        ) as rnum
        FROM 서비스계약GR tsg
       ) tsg
WHERE tsg.SVCCONTNUM = ts.SVCCONTNUM
AND tsg.VIP여부 = 'Y'
AND tsg.탈락여부 IS NULL
AND tsg.서비스등급 LIKE 'V%'
AND tsg.rnum = 1                                       --- (1)
GROUP BY tsg.상담원ID
   

(1)SVCCONTNUM(서비스계약번호) 별로 등급선정기준YYMM의 역순(desc)으로 일련번호 부여 후,
   인라인 뷰(tsg) 바깥에서 rnum=1 조건 적용하면 ---> 각 SVCCONTNUM 별로 최종 월의 데이터만 추출
(2)서비스계약MT_X8 인덱스가 필요한 컬럼을 모두 포함하고 있으므로 병렬로 index fast full scan하도록 index_ffs + parallel_index 힌트 사용

(가)서비스계약MT 테이블 : 전체 데이터 33,017,465건
    서비스계약MT_PK : SVCCONTNUM
    서비스계약MT_XB : 서비스상태 + 서비스해지일자 + SVCCONTNUM *****

(나)서비스계약GR 테이블 : 전체 데이터 56,570,996건 중 조건에 해당하는 데이터는 2,747,760건
    서비스계약GR_PK : SVCCONTNUM + 등급선정기준YYMM
    서비스계약GR_X4 : SVCCONTNUM + 등급선정기준YYMM + 서비스등급

 

6. WITH문 활용
1)개선 전 SQL

 

 

2)개선 후 SQL

 

프로그램 병렬 처리
배치 스케줄 조정

 

오브젝트 통계: 통계 뷰
 - 오브젝트 통계는 테이블 및 칼럼, 인덱스 등의 카탈로그 정보와 데이터 값의 분포 정보를 포함하는데, 다음과 같은 카탈로그 정보를 통해 확인 가능하다.
   (단, 9i까지는 주로 ALL_TABLES, ALL_INDEXES, ALL_TAB_COLUMNS 등의 뷰를 사용하여 통계정보 확인)

 - 테이블 통계: ALL_TAB_STATISTICS
 - 컬럼 통계: ALL_TAB_COL_STATISTICS
 - 인덱스 통계: ALL_IND_STATISTICS
 - 히스토그램: ALL_TAB_HISTOGRAMS