SQLP 요점정리 #20 : 3과목 Tuning - 5장 고급 SQL Tuning - 4절 Partition 활용, 5절 Batch Program Tuning

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간의 통신이 발생