[1] 배치 프로그램 튜닝 개요

(1) 배치 프로그램

• 일련의 작업들을 하나의 작업 단위로 묶어 연속적으로 일괄 처리하는 것
• 온라인 프로그램에서도 여러 작업을 묶어 처리하는 경우가 있으므로 이와 구분하려면 한 가지 특징을 더 추가해야 한다.-> 사용자와의 상호작용(Interaction) 여부
• 사용자와의 상호작용 없이 대량의 데이터를 처리하는 일련의 작업들을 묶어 정기적으로 반복 수행하거나 정해진 규칙에 따라 자동으로 수행한다.
• 기업마다 업무 요건이 워낙 복잡 다양하므로 이 외에도 여러 가지 형태가 존재할 수 있으며, 정기 배치 형태가 가장 일반적이다.

1. On-Demand 배치

• 배치 프로그램이 자동으로 수행되는 주기는 월단위, 주단위, 일단위가 보통이지만, 요즘은 주기가 점점 짧아져 종종 실시간이 요구되기도 한다.
• 사용자가 요청한 시점에 바로 작업을 시작한다

2. 정기 배치

• 정해진 시점(주로 야간)에 실행한다.

3. 이벤트성 배치

• 사전에 정의해 둔 조건이 충족되면 자동으로 실행한다.

(2) 배치 환경의 변화

• 과거
  • 일 또는 월 배치 작업 위주
  • 야간에 생성된 데이터를 주간 업무시간에 활용한다.
  • 온라인과 배치 프로그램의 구분이 비교적 명확하다.
• 현재
  • 시간 배치 작업의 비중이 증가했다.
  • 분 배치 작업이 일부 존재한다.
  • On-Demand 배치를 제한적이나마 허용한다.
    • 실시간에 가까운 정보 서비스를 제공하기 위해 온라인 시스템에서 곧바로 대용량 데이터를 가공하는 예도 있지만
    • 대개는 DW 시스템에 근실시간으로 전송해 준 데이터를 가공해서 서비스하는 형태다.
  • 배치 작업을 위한 전용 서버를 두기고 하며, RAC 환경에선 여러 인스턴스 중 하나를 배치 전용 인스턴스로 지정하기도 한다.

(3) 성능 개선 목표 설정

• ⭐️ 배치 프로그램에서의 성능 목표와 튜닝 기법은 온라인 프로그램에서의 그것과 달라야 한다.
• 속도 측면
  • 온라인 프로그램은 경우에 따라 전체 처리속도 최적화나 최초 응답속도 최적화를 목표로 선택한다.
  • 배치 프로그램은 항상 전체 처리속도 최적화를 목표로 설정
    • 개별 프로그램 차원에서도 그렇지만 야간에 수행되는 전체 배치 프로그램에 대한 목표도 마찬가지이다.
• 자원 사용 측면
  • 시스템 자원을 독점적으로 사용하도록 설정된 프로그램을 찾아 병렬도를 제한하고, 동시에 수행되는 프로그램 개수도 적절히 유지해야 한다.
  • 배치 윈도우를 적절히 조절하는 것만으로 배치 프로그램 수십 개를 튜닝한 것과 같은 효과가 나타난다.

(4) 배치 프로그램 구현 패턴과 튜닝 방안

• 개발자 스타일과 애플리케이션 아키텍처에 따라 배치그램의 구현방식이 크게 2가지 방식이 있다.

1. 절차형으로 작성된 프로그램

• 애플리케이션 커서를 열고, 루프 내에서 또 다른 SQL이나 서브 프로시저를 호출하면서 같은 처리를 반복하는 형태
• 반복적인 데이터베이스 Call 발생
• Random I/O 위주로 동일 데이터를 중복 액세스한다.

튜닝법

• 병목을 일으키는 SQL 을 찾아 인덱스를 재구성하고 엑세스 경로 최적화하여 I/O 튜닝
• 프로그램 Parallel 활용 : 메인 SQL을 읽는 데이터 범위를 달리하여 프로그램을 동시에 활용한다.
• ⭐️ Array Processing 을 활용

2. One SQL 위주 프로그램

• One SQL로 구성하거나, 집합적으로 정의된 여러 SQL을 단계적으로 실행한다.
• 성능 측면에선 One SQL 위주의 프로그램이 월등하다.

[2] 병렬 처리 활용

• SQL문이 수행해야 할 작업 범위를 여러 개의 작은 단위로 나누어 여러 프로세스(또는 쓰레드)가 동시에 처리하는 것이다.
• 여러 프로세스가 동시에 작업하므로 대용량 데이터를 처리할 때 수행 속도를 극적으로 단축한다.

(1) Query Coordinator와 병렬 서버 프로세스

1. Query Coordinator(이하 QC)

• 병렬 SQL문을 발행한 세션이다.
• 병렬 서버 프로세스는 실제 작업을 수행하는 개별 세션들이다.
• QC의 역할
  • 병렬 SQL이 시작되면 QC는 사용자가 지정한 병렬도(DOP, degree of parallelism)와 오퍼레이션 종류에 따라 하나 또는 두 개의 병렬 서버 집합(Server Set)을 할당한다.
  • 우선 서버 풀(Parallel Execution Server Pool)로부터 필요한 만큼 서버 프로세스를 확보하고, 부족분은 새로 생성한다.
  • QC는 각 병렬 서버에게 작업을 할당한다.
  • 작업을 지시하고 일이 잘 진행되는지 관리감독하는 작업반장 역할을 한다.
  • 병렬로 처리하도록 사용자가 지시하지 않은 테이블은 QC가 직접 처리한다.
    • 예를 들어, 특정 테이블을 직렬로 읽어 병렬 서버에 전송하는 오퍼레이션은 QC의 몫이다.
  • QC는 각 병렬 서버로부터의 산출물을 통합하는 작업을 수행한다.
    • 예를 들어 집계 함수(sum, count, avg, min, max 등)가 사용된 병렬 쿼리를 수행할 때, 각 병렬 서버가 자신의 처리 범위 내에서 집계한 값을 QC에게 전송하면 QC가 최종 집계 작업을 수행한다.
  • QC는 쿼리의 최종 결과집합을 사용자에게 전송하며, DML일 때는 갱신 건수를 집계해서 전송해 준다.
  • 쿼리 결과를 전송하는 단계에서 수행되는 스칼라 서브쿼리도 QC가 수행한다.

(2) Intra-Operation Parallelism과 Inter-Operation Parallelism

1. Intra-Operation Parallelism

• 서로 배타적인 범위를 독립적으로 동시에 처리하는 것이다.
• 첫 번째 서버 집합(P000~P003)에 속한 4개의 프로세스가 범위를 나눠 고객 데이터를 읽는다.
• 두 번째 서버 집합(P004~P007)이 첫 번째 서버 집합으로부터 전달받은 고객 데이터를 정렬한다.
• 한 병렬 서버 집합(Server Set)에 속한 여러 프로세스가 처리 범위를 달리하면서
  병렬로 작업을 진행하는 것이므로 집합 내에서는 절대 프로세스 간 통신이 발생하지 않는다.

2. Inter-Operation Parallelism

• 같은 서버 집합끼리는 서로 데이터를 반대편 서버 집합에 분배하거나 정렬된 결과를 QC에게 전송하는 작업을 병렬로 동시에 진행하는 것이다.
• 이때는 항상 프로세스 간 통신이 발생한다.(메시지 또는 데이터를 전송하기 위한 통신 채널이 필요하다.)

(3) 테이블 큐

• Inter-Operation Parallelism에서 쿼리 서버 집합 간(P→P) 또는 QC와 쿼리 서버 집합 간(P→S, S→P) 데이터 전송을 위해 연결된 파이프 라인(Pipeline)이다.
• 각 테이블 큐에 부여된 :TQ10000, :TQ10001, :TQ10002와 같은 이름을 ‘테이블 큐 식별자(TQ Identifier)’라고 한다.
• 쿼리 서버 집합 간(P→P) Inter-Operation Parallelism이 발생할 때는 사용자가 지정한 병렬도(=2)의 배수(4개)만큼 서버 프로세스가 필요하다.
• 또한 테이블 큐(:TQ10001)에는 병렬도의 제곱(2^2=4)만큼 파이프 라인이 필요하다는 사실도 알 수 있다.
• 테이블 큐에는 항상 생산자(Producer)와 소비자(Consumer)가 존재한다.
  • 처음 dept 테이블을 읽어 분배하는 :TQ10000에서는 QC가 생산자고 서버 집합 1이 소비자다.
  • 이어지는 두 번째 테이블 큐 :TQ10001에서는 서버 집합 1이 생산자가 되고, 서버 집합 2가 소비자이다.
  • 마지막으로, 정렬된 최종 결과집합을 전송하는 :TQ10002에서는 서버 집합 2가 생산자가 되고 QC가 소비자이다.
  • select 문장에서의 최종 소비자는 항상 QC가 된다.
• [그림 Ⅲ-5-16]에서 보듯 Inter-Operation Parallelism이 나타날 때, 소비자 서버 집합은 from절에 테이블 큐를 참조하는 서브(Sub) SQL을 가지고 작업을 수행한다.

(4) IN-OUT 오퍼레이션

• S→P, P→S, P→P는 프로세스 간 통신이 발생한다.

1. S→P

• QC가 처리한 결과를 병렬 서버 프로세스에 전달한다.
• 직렬(Serial) 오퍼레이션이다.

2. P→S

• 병렬 서버 프로세스가 처리한 결과를 QC에 전달한다.
• 병렬 오퍼레이션이다.

3. P→P

• 두 개의 병렬 서버 프로세스 집합이 처리한다.
• 지정한 병렬도의 2배 만큼의 병렬 프로세스가 생성된다.
• 병렬 오퍼레이션이다.

4. PCWP

• 병렬 서버 프로세스 집합이 현재 스텝과 그 부모 스텝을 모두 처리한다.
• 병렬 오퍼레이션이다.
• ⭐️ 프로세스 간 통신이 발생하지 않는다.
• 각 병렬 서버가 독립적으로 여러 스텝을 처리할 때 나타난다.
• 하위 스텝의 출력 값이 상위 스텝의 입력 값으로 사용된다.

5. PCWC

• 병렬 서버 프로세스 집합이 현재 스텝과 그 자식 스텝을 모두 처리한다.
• 병렬 오퍼레이션이다.
• ⭐️ 프로세스 간 통신이 발생하지 않는다.
• 각 병렬 서버가 독립적으로 여러 스텝을 처리할 때 나타난다.
• 하위 스텝의 출력 값이 상위 스텝의 입력 값으로 사용된다.

(5) ⭐️ 데이터 재분배

• 병렬 서버 프로세스 간에 데이터를 재분배하는 방식이다.

1. RANGE

• order by 또는 sort group by를 병렬로 처리할 때 사용된다.
• 첫 번째 서버 집합
  • 데이터를 읽는다.
  • 두 번째 서버 집합의 정해진 프로세스에게 “정렬 키 값에 따라” 분배한다.
• 두 번째 서버 집합
  • 정렬 작업을 맡는다.
  • 프로세스마다 처리 범위(예를 들어, A~G, H~M, N~S, T~Z)를 지정한다.
• QC의 역할
  • 각 서버 프로세스에게 작업 범위를 할당한다.
  • 정렬 작업에는 직접 참여하지 않는다.
  • 정렬이 완료되고 나면 순서대로 결과를 받아서 사용자에게 전송한다.
  • P→P 뿐만 아니라 S→P 방식으로 이루어질 수도 있다.

3. BROADCAST

• QC 또는 첫 번째 서버 집합에 속한 프로세스들이 각각 읽은 데이터를 두 번째 서버 집합에 속한 “모든” 병렬 프로세스에게 전송하는 방식이다.
• 병렬 조인에서 크기가 매우 작은 테이블이 있을 때 사용한다.
• P→P 뿐만 아니라 S→P 방식으로도 이루어진다.

4. KEY

• 특정 칼럼(들)을 기준으로 테이블 또는 인덱스를 파티셔닝할 때 사용하는 분배 방식이다.

5. ROUND-ROBIN

• 파티션 키, 정렬 키, 해시 함수 등에 의존하지 않고 반대편 병렬 서버에 무작위로 데이터를 분배할 때 사용한다.

(6) ⭐️ pq_distribute 힌트 활용

• pq_distribute 힌트의 용도
  • 조인되는 양쪽 테이블의 파티션 구성, 데이터 크기 등에 따라 병렬 조인을 수행하는 옵티마이저의 선택이 달라질 수 있다.
  • pq_distiribute 힌트를 사용함으로써 옵티마이저의 선택을 무시하고 사용자가 직접 조인을 위한 데이터 분배 방식을 결정한다.
• pq_distribute 힌트 사용의 경우
  • 옵티마이저가 파티션된 테이블을 적절히 활용하지 못하고 동적 재분할을 시도할 때
  • 기존 파티션 키를 무시하고 다른 키 값으로 동적 재분할하고 싶을 때
  • 통계정보가 부정확하거나 통계정보를 제공하기 어려운 상황(→ 옵티마이저가 잘못된 판단을 하기 쉬운 상황)에서 실행계획을 고정시키고자 할 때

1. 사전 준비작업 :병렬 방식으로 조인을 수행하기 위해서는 프로세스들이 서로 “독립적으로” 작업할 수 있도록 한다.

• 1. 먼저 데이터를 적절히 배분하는 작업이 선행되어야 한다.
• 2. 병렬 쿼리는 ‘분할 & 정복(Divide & Conquer) 원리’에 기초하는데 그 중에서도 병렬 조인을 위해서는 ‘분배 & 조인(Distribute & Join) 원리’가 작동함을 이해하는 것이 매우 중요하다.
  • 즉 , pq_distribute 힌트는 조인에 앞서 데이터를 분배(distribute) 과정에만 관여하는 힌트임을 반드시 기억해야한다.

2. ⭐️ pq_distribute 사용법

pq_distribute(inner, none, none)

• Full-Partition Wise Join으로 유도할 때 사용한다.
• 당연히, 양쪽 테이블 모두 조인 칼럼에 대해 같은 기준으로 파티셔닝(equi-partitioning) 돼 있을 때만 작동한다.

pq_distribute(inner, partition, none)

• Partial-Partition Wise Join으로 유도할 때 사용한다.
• outer 테이블을 inner 테이블 파티션 기준에 따라 파티셔닝하라는 뜻이다.
• 당연히, inner 테이블이 조인 키 칼럼에 대해 파티셔닝 돼 있을 때만 작동한다.

pq_distribute(inner, none, partition)

• Partial-Partition Wise Join으로 유도할 때 사용
• inner 테이블을 outer 테이블 파티션 기준에 따라 파티셔닝하라는 뜻이다.
• 당연히, outer 테이블이 조인 키 칼럼에 대해 파티셔닝 돼 있을 때만 작동한다.

pq_distribute(inner, hash, hash)

• 조인 키 칼럼을 해시 함수에 적용하고 거기서 반환된 값을 기준으로 양쪽 테이블을 동적으로 파티셔닝하라는 뜻이다.
• 조인되는 테이블을 둘 다 파티셔닝해서 파티션 짝(Partition Pair)을 구성하고서 Partition Wise Join을 수행한다.

(6-4-5) pq_distribute(inner, broadcast, none)

• outer 테이블을 Broadcast 하라는 뜻이다.

pq_distribute(inner, none, broadcast)

• inner 테이블을 Broadcast 하라는 뜻이다.

[pq_distribute 힌트를 이용한 튜닝 사례] 데이터 이행 도중 실제 문제가 발생했던 사례

• 통계 정보가 없거나 잘못된 상태에서 병렬 조인을 수행하면 옵티마이저가 아주 큰 테이블을 Broadcast 하는 경우가 종종 생긴다.
• 임시 테이블을 많이 사용하는 야간 배치나 데이터 이행(Migration) 프로그램에서 그런 문제가 자주 나타난다.
• 1시간 40분간 수행되던 SQL이 임시 세그먼트를 확장할 수 없다는 오류 메시지를 던지면서 멈추었다.
• 상품기본이력임시 테이블에 통계 정보가 없던 것이 원인이었다.
• 실제 천만 건에 이르는 큰 테이블이었는데, 통계 정보가 없어 옵티마이저가 5,248건의 작은 테이블로 판단한 것이다.
• 이 큰 테이블을 32개 병렬 서버에게 Broadcast하는 동안 과도한 프로세스 간 통신이 발생했고, 결국 Temp 테이블 스페이스를 모두 소진하고서 멈췄다.
  pq_distribute 힌트를 이용해 데이터 분배 방식을 조정하고 나서 다시 수행해 본 결과, 아래와 같이 2분 29초 만에 작업을 완료하였다.

(7) 병렬 처리 시 주의사항

• 병렬 쿼리를 과도하게 사용하면 시스템을 마비시킬 수도 있다.적절한 사용 기준이 필요하다는 얘기이다.
• 병렬 처리 기법을 사용하는 것이 바람직할 때
  • 동시 사용자 수가 적은 애플리케이션 환경(야간 배치 프로그램, DW, OLAP 등)에서 직렬로 처리할 때보다 성능 개선 효과가 확실할 때(→ 이 기준에 따르면 작은 테이블은 병렬 처리 대상에서 제외된다.)
  • OLTP성 시스템 환경이더라도 작업을 빨리 완료함으로써 직렬로 처리할 때보다 오히려 전체적인 시스템 리소스(CPU, Memory 등) 사용률을 감소시킬 수 있을 때(→ 수행 빈도가 높지 않음을 전제로)
• 야간 배치 프로그램에는 병렬 처리를 자주 사용하는데, 야간 배치 프로그램은 전체 목표 시간을 달성하는 것을 목표로 해야지 개별 프로그램의 수행속도를 단축하려고 필요 이상의 병렬도를 지정해선 안 된다.
• 야간이더라도, 여러 팀에서 작성한 배치 프로그램이 동시에 수행되는 상황에서 특정 소수 배치 작업이 과도한 병렬 처리를 시도한다면 CPU, 메모리, 디스크 등 자원에 대한 경합 때문에 오히려 전체 배치 수행 시간이 늘어날 수 있다.
• 병렬도를 높인다고 성능이 선형적으로 좋아지는 것도 아니다.
• 결론적으로, 성능개선 효과가 확실한 최소한의 병렬도를 지정하려는 노력이 필요하다.
• 데이터 이행(Migration) :: 물론 시스템 리소스를 최대한 사용해야 할 때 / 이때는 모든 애플리케이션을 중지시키고 이행 프로그램이 시스템을 독점적으로 사용하기 때문에 가능한 모든 리소스를 활용해 이행 시간을 최대한 단축하는 것을 목표로 삼아야 한다.
• ⭐️ 병렬 DML 수행 시 Exclusive 모드 테이블 Lock이 걸리므로 트랜잭션이 활발한 주간에 절대 사용해선 안 된다.