SQLP 3-5-4 Partition, Batch Job

in Devlopment on SQLP


3과목 SQL 고급 활용 및 Tuning

5장 고급 SQL Tuning

5.4 Partition 활용

  • Partitioning 이란 ?
    • Table, Index를 Partition 단위로 나누어 저장
    • Partition Key에 따라 물리적으로 별도의 Segment에 저장
  • Partition 장점
    • 관리적 측면 : Partition 단위 백업, 추가, 삭제, 변경이 편리
    • 성능적 측면 : Partition 단위로 Query, DML이 수행되어서 Transaction 경합 및 부하 분산

5.4.1 Partition 유형

1. Range Partition
  • Partition Key 값의 범위(Range)로 분할
  • 가장 일반적인 형태

e.g. 날짜 칼럼 기준으로 판매데이터를 월별로 분할

CREATE TABLE 주문
(
    ...
)
PARTITION BY RANGE(주문일자)
(
    PARTITION P2009_Q1 VALUES LESS THAN ('20090401'),
    PARTITION P2009_Q2 VALUES LESS THAN ('20090701'),
    ...
    PARTITION P9999_MX VALUES LESS THAN (MAXVALUE)
)'
2. Hash Partition
  • Partition Key 값의 Hash 함수를 적용하여 그 값으로 Mapping
  • 고르게 분산되는 대신 각 Row의 저장위치 예측이 불가
  • 병렬처리 시 성능효과 극대화
  • DML 경합 분산에 효과적

e.g. 고객번호, 주문일련번호

3. List Partition
  • 불연속적인 값의 목록을 각 Partition에 지정

e.g. 판매 데이터를 지역별로 분할

4. Composite Partition
  • 2개 이상의 Partition 구성 (단, 맨 처음에 Hash가 올 순 없음)
  • Range나 List Partition에 Range, Hash, List를 Sub-partition으로 구성

e.g. Range + Hash로 구성

CREATE TABLE 주문
(
    ...
)
PARTITION BY RANGE(주문일자)
SUBPARTITION BY HASH(고객ID) SUBPARTITIONS 8
(
    PARTITION P2009_Q1 VALUES LESS THAN ('20090401'),
    PARTITION P2009_Q2 VALUES LESS THAN ('20090701'),
    ...
    PARTITION P9999_MX VALUES LESS THAN (MAXVALUE) 
);

5.4.2 Partition Pruning

  • Optimizer가 SQL의 대상 Table과 조건을 분석하여 불필요한 Partition을액세스 대상에서 제외하는 기능
  1. Static Partition Pruning
    • 액세스할 Partition을 Compile-Time에 미리 결정
    • 상수 조건으로 조회할 경우 작동
  2. Dynamic Partition Pruning
    • 액세스할 Partition을 Run-Time에 결정
    • Bind Variable로 조회하는 경우
    • NL Join시 Inner Table이 Join 칼럼 기준으로 Partition 되 있는 경우

5.4.3 Index Partitioning

  • Local vs Global
    • Local Partition Index
      • Table Partition과 1:1 대응하도록 Index Partitioning
      • Index Partition Key를 사용자가 따로 지정하지 않고, DBMS가 자동으로 관리
    • Global Partition Index
      • Table Partition과는 독립적으로 구성
  • Prefixed vs NonPrefixed
    • Prefixed Partition Index : Partition Key Column이 Index의 왼쪽 선두에 위치
    • NonPrefixed Partition Index : Partition Key Column이 Index의 왼쪽 선두에 있지 않거나, 아에 속하지 않을 경우

  • 위 조합중 Global NonPrefixed Partition Index는 Oracle에서 지원하지 않음

  • Index Partitioning Guide
    • NonPartitioned Index (일반 Index)
      • Partition Key Column이 조건절에 누락되면 여러 Index Partition을 액세스해야 하므로 비효율적
      • 특히 OLTP환경에서는 성능에 미치는 영향이 크므로 NonPartitioned 전략이 유용할 수 있음
      • NL Join에서 Partition Key에 대한 넓은 범위검색 조건을 가지고 Inner Table에 Partitioned Index로 액세스하면 비효율적 -> NonPartitioned Index 사용을 고려
      • Partition Index를 이용하면 SORT ORDER BY 대체 효과 상실 -> Sort 연산을 대체함으로 부분범위 처리를 활용하고자 할 경우 NonPartitioned Index가 유리함
      • Table Partition 이동,삭제 등 작업시 unsuable 되므로 적용 시 주의
    • Global Prefixed
      • Index 경합 분산에 효과적
      • 여러 Local Index Partition을 액세스하는 것이 비효율적일 경우 대안으로 활용
      • Table Partition 이동,삭제 등 작업시 unsuable 되므로 적용 시 주의
    • Local Prefixed
      • 관리적 측면에서 유용 : Table Partition에 대한 추가, 삭제 등의 작업이 빈번할 때
      • 이력성 데이터를 주로 관리하는 DB 환경에 효과적
      • Partition Key Column이 = 조건으로 사용될 때 유용
      • Partition Key Column에 대한 검색 조건이 없으면 정상적 사용이 불가 (Index Full Scan으로는 선택가능)
      • Partition Key Column이 범위검색 (LIKE, BETWEEN, 부등호) 일 경우 불리
    • Local NonPrefixed
    • Local Prefixed와 거의 같은 특징이긴하나
    • 범위검색이 주로 사용될 경우 NonPrefixed가 더 유리 (단, 좁은 범위검색이어야 함)

5.5 Batch Program Tuning

  • Batch Program이란 ?
    • User와의 상호작용(Interface)없이
    • 대량의 데이터를 처리하는
    • 일련의 작업들을 묶어
    • 정기적으로 반복 수행하거나 (정기 배치)
    • 정해진 규칙에 따라 (이벤트 배치)
    • 자동으로 수행 (수동으로 On-Demand 배치로도 수행가능)
  • Batch 환경의 변화
    • 과거 : 일, 월 단위로 주로 야간에 수행되었으며, Online과 Batch가 명확하게 구분되어서 사용되었지만,
    • 현재 : 시간, 분 단위의 짧은 시간에 수행되는 경우가 많으며 On-Demand Batch도 제한적이나마 허용되어야 한다.
  • 성능개선 목표 설정
    • 전체 Batch Program 들의 최종 수행시간 단축
      • Batch Window를 보고 Batch Program 간의 선후 관계를 따져가면서 전체적으로 고려
    • System 부하도 고려해야 함
      • 병렬도 (DOP : Degree of Parallelism)를 32로 하여 5분에 수행되는것보다는 병렬처리 없이 10분 소요되는게 더 나을 수 있다.
    • 경합을 최소화
      • CPU 자원 대량 사용 및 동일한 자원(데이터)에 접근하는 Batch Program을 분산
  • Batch Program 구현 Pattern과 Tuning 방안
    • 절차형으로 작성된 Program
      • Application Cursor를 열고, Loop 내에서 다른 SQL이나 Sub Procedure를 호출하면서 반복처리
      • (One SQL 보다) 구현이 쉽다.
      • 아무리 Tuning을 잘해도 다음과 같은 최적화 한계가 있음
        • 반복적인 DB Call 발생
        • Random I/O 위주
        • 동일한 데이터 중복 액세스
      • Tuning Guide
        • 병목을 일으키는 SQL을 찾아 I/O Tuning : Index 재구성 및 액세스 경로 최적화
        • Program Parallel 활용 : SQL이 읽는 데이터 범위를 달리하여 여러 Program이 동시에 수행
        • Array Processing 활용
        • One SQL로 다시 구현;;;
    • One SQL
      • One SQL로 구현하거나, 집합적으로 정의된 여러 SQL을 단계적으로 실행
      • 구현하기 어렵고, 업무가 복잡할 경우 여러 SQL들을 통합했다가 결과가 틀려질 수 있음
      • Tuning Guide
        • 병목을 일으키는 오퍼레이션을 찾아 I/O Tuning
          • Index Scan 대신 Full Table Scan으로
          • NL Join 보다 Hash Join으로
        • 임시 Table 활용
        • Partition 활용
        • 병렬처리 활용
  • 병렬 처리 활용
    • SELECT /*+ full(T) parallel(T, 4) */ ... : T Table을 Full Scan으로 4개로 병렬처리
    • SELECT /*+ index_ffs(T t_idx) parallel(T, t_idx, 4) */ ... : T Table의 t_idx Index를 Fast Full Scan으로 4개로 병렬처리
    • QC (Query Cordinator)
      • 병렬 SQL문을 발행한 Session
      • 병렬로 처리되지 않는 Table은 QC가 직접처리
      • 각 병렬 Server로부터의 산출물을 통합하는 작업 수행
      • 최종 결과 집합을 User에게 전송
    • Parallel Server Process
    • 병렬 SQL을 실제로 수행하는 개별 Session
    • Operation Parallelism
      • Intra-Opertarion Parallelism
        • 서로 배타적인 범위를 독립적으로 처리
        • 각각의 데이터를 Process별로 읽는 작업
        • 전달받은 데이터를 각각의 Process에서 처리
      • Inter-Operation Parallelism
        • 다른 서버 집합으로 분배하거나 정렬된 결과를 QC에게 전송하는 작업을 병렬로 동시에 진행
        • Process간의 통신이 발생

이 글이 도움이 되셨다면 공감 및 광고 클릭을 부탁드립니다 :)