배치 프로그램 튜닝

배치 프로그램 튜닝 개요

배치(Batch) 프로그램

배치 프로그램이란, 일련의 작업들을 하나의 작업 단위로 묶어 연속적으로 일괄 처리하는 것을 말한다. 온라인 프로그램에서도 여러 작업을 묶어 처리하는 경우가 있으므로 이와 구분하려면 한 가지 특징을 더 추가해야 하는데, 사용자와의 상호작용 여부다. 배치 프로그램의 특징을 요약하면 다음과 같다.

배치 프로그램의 특징

• 사용자와의 상호작용 없이
• 대량의 데이터를 처리하는
• 일련의 작업들을 묶어
• 정기적으로 반복 수행하거나
• 정해진 규칙에 따라 자동으로 수행

배치 프로그램 구분

• 정기 배치 : 정해진 시점(주로 야간)에 실행. 가장 일반적인 형태.
• 이벤트성 배치 : 사전에 정의해 둔 조건이 충족되면 자동으로 실행
• On-Demand 배치 : 사용자의 명시적 요구가 있을 때마다 실행 (실시간. 보통 비동기 방식으로 수행)

배치 환경의 변화

과거에는 일(Daily)이나 월(Monthly) 배치 작업 위주였으며 야간에 생성된 데이터를 주간 업무시간에 활용했다. 또한, 온라인과 배치 프로그램의 구분이 비교적 명확했다. 하지만 현재는 시간(Hourly) 배치 작업의 비중이 증가하였으며 분(Minutely) 배치 작업이 일부 존재하고 On-Demand 배치를 제한적이나마 허용한다.

실시간에 가까운 정보 서비스를 제공하기 위해 온라인 시스템에서 곧바로 대용량 데이터를 가공하는 예도 있지만, 대개는 DW 시스템에 근실시간으로 전송해 준 데이터를 가공해서 서비스하는 형태이다. 배치 작업을 위한 전용 서버를 두기도 하며, RAC 환경에서는 여러 인스턴스 중 하나를 배치 전용 인스턴스로 지정하기도 한다,

DW 시스템 : 사용자의 의사결정을 지원하기 위해 기업이 축적한 많은 데이터를 사용자 관점에서 주제별로 통합하여 별도의 장소에 저장.

성능 개선 목표 설정

온라인 프로그램은 경우에 따라 전체 처리속도 최적화나 최초 응답속도 최적화를 목표로 선택하지만, 배치 프로그램은 항상 전체 처리속도 최적화를 목표로 설정해야 한다. 개별 서비스 또는 프로그램을 가장 빠른 속도로 최적화하더라도 전체 배치 프로그램 수행 시간을 단축시키지 못하면 무의미하다.
(병렬도(DOP, degree of parallelism)를 32로 지정해서 5분이 소요되는 프로그램을 병렬 처리 없이 10분이 소요되도록 하는 것이 오히려 나을 수 있다. 시스템 자원을 독점적으로 사용하도록 설정된 프로그램을 찾아 병렬도를 제한하고, 동시에 수행되는 프로그램 개수도 적절히 유지해야 한다.)

실제 개발 프로젝트에 가보면, 시스템 자원에 대한 사용 권한을 적절히 배분하지 않고 각 서브 개발 파트에서 개발한 배치 프로그램을 12시 정각에 동시에 수행하는 경우를 종종 볼 수 있다. 그럴 때 배치 윈도우를 적절히 조절하는 것만으로 배치 프로그램 수십 개를 튜닝한 것과 같은 효과를 내기도 한다.

같은 시간대에 수많은 프로그램이 집중적으로 수행되면 자원(CPU, Memory, Disk 등)과 Lock(Latch와 같은 내부 Lock까지 포함)에 대한 경합이 발생하기 때문이다. 그러면 프로세스가 실제 일한 시간보다 대기하는 시간이 더 많아지므로 총 수행시간이 늘어나는 것이다.

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

절차형으로 작성된 프로그램 : 애플리케이션 커서를 열고, 루프 내에서 또 다른 SQL이나 서브 프로시저를 호출하면서 같은 처리를 반복하는 형태
One SQL 위주 프로그램 : One SQL로 구성하거나, 집합적으로 정의된 여러 SQL을 단계적으로 실행

성능 측면에서는 One SQL 위주의 프로그램이 월등하다. 절차형으로 작성된 프로그램은 이러한 비효율 때문에 느릴 수밖에 없다.

• 반복적인 데이터베이스 Call 발생
• Random I/O 위주
• 동일 데이터를 중복 액세스
  또한, 개별 SQL을 최적화하더라도 그것을 담고있는 프로그램 전체를 최적화하는데 한계를 보인다.

하지만 절차형으로 작성된 프로그램을 One SQL 위주의 프로그램으로 구현하기가 쉽지만은 않다. 개발자의 기술력이 부족한 이유도 있지만, 업무의 복잡성 때문에 불가능한 경우도 많다. 무엇보다, 섣불리 One SQL로 통합했다가 결과가 틀려지는 위험성을 간과하기 어렵다.

배치 프로그램 튜닝 요약

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

• 병목을 일으키는 SQL을 찾아 I/O 튜닝 : 인덱스를 재구성하고 액세스 경로 최적화
• 프로그램 Parallel 활용 : 메인 SQL이 읽는 데이터 범위를 달리하여 프로그램을 동시에 여러 개 수행
• Array Processing 활용 : 한 번의 SQL 수행으로 다량의 로우를 동시에 insert/update/delete 가능
• One SQL 위주 프로그램으로 다시 구현

One SQL 위주 프로그램

• 병목을 일으키는 오퍼레이션을 찾아 I/O 튜닝
  (Index Scan보다 Full Table Scan 방식으로, NL Join보다 Hash Join 방식으로 처리)
• 임시 테이블 활용
• 파티션 활용
• 병렬처리 활용

병렬 처리 활용

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

select /*+ full(o) parallel(o, 4) */ count(*) 주문건수, sum(주문수량), sum(주문금액) 주문금액
from 주문 o
where 주문일자 between '20100101' and '20101231';

parallel 힌트를 사용할 때는 반드시 Full 힌트도 함께 사용하는 습관이 필요하다. 옵티마이저에 의해 인덱스 스캔이 선택되면 parallel 힌트가 무시되기 때문이다.

parallel(테이블, 병렬프로세스 개수) <- 병렬프로세스 개수를 지정해주지 않으면 시스템 디폴트 개수만큼 기동한다.)

select /*+ index_ffs(o, 주문_idx)) parallel_index(o, 주문_idx, 4) */ count(*) 주문건수, sum(주문수량), sum(주문금액) 주문금액
from 주문 o
where 주문일자 between '20100101' and '20101231';

parallel_index 힌트를 사용할 때, 반드시 index 또는 index_ffs 힌트를 함께 사용하는 습관도 필요하다. 옵티마이저에 의해 Full Table Scan이 선택되면 parallel_index 힌트가 무시되기 때문이다.

Query Coordinator와 병렬 서버 프로세스

Query Coordinator(QC)는 병렬 SQL문을 발행한 세션을 말하며, 병렬 서버 프로세스는 실제 작업을 수행하는 개별 세션들을 말한다.

Query Coordinator의 역할

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

병렬 처리에서 실제 QC 역할을 담당하는 프로세스는 SQL문을 발행한 사용자 세션 자신이다.

Intra-Operation Parallelism과 Inter-Operation Parallelism

select /*+ full(고객) parallel(고객 4) */
* from 고객
order by 고객명;

이 병렬 쿼리를 수행할 때 2개 조로 나누고 역할을 분담해 서로 다른 작업을 동시에 진행한다. 이 때 QC는 관리를 담당하고 4명은 분배, 4명은 분배된 내용을 정렬하는 역할을 수행한다. (병렬도가 4이므로)

Intra-Operation Parallelism

서로 배타적인 범위를 독립적으로 동시에 처리하는 것이다. 첫 번째 서버 집합에 속한 4개의 프로세스가 범위를 나눠 고객 데이터를 읽는 작업과 두 번째 서버 집합 고객 데이터를 정렬하는 작업이 모두 여기에 속한다. 즉, 같은 조끼리는 서로 Data를 주고받을 일이 없다.

Inter-Operation Parallelism

같은 서버 집합끼리는 서로 데이터를 반대편 서버 집합에 분배하거나 정렬된 결과를 QC에게 전송하는 작업을 병렬로 동시에 진행하는 것이며 항상 프로세스 간 통신이 발생한다.

테이블 큐

Intra-Operation Parallelism은 한 병렬 서버 집합에 속한 여러 프로세스가 처리 범위를 달리하면서 병렬로 작업을 진행하는 것이므로 집합 내에서는 절대 프로세스 간 통신이 발생하지 않는다. 반면, Inter-Operation Paralelism은 프로세스 간 통신이 발생하고, 메시지 또는 데이터를 전송하기 위한 통신 채널이 필요하다.

쿼리 서버 집합간 또는 QC와 쿼리 서버 집합 간 데이터 전송을 위해 연결된 파이프 라인을 테이블 큐라고 한다.

select /*+ ordered use_hash(e) full(d) noparallel(d) full(e) parallel(e 2) pq_distribute(e broadcast none) */ *
from dept d, emp e
where d.deptno = e.deptno
order by e.ename;

쿼리 서버 집합 간 Inter-Operation Parallelism이 발생할 때는 사용자가 지정한 병렬도의 배수만큼 서버 프로세스가 필요하다.
(P000, P001, P002, P003)
테이블 큐(:TQ10001)에는 병렬도의 제곱(2^2 = 4) 만큼 파이프 라인이 필요하다.

생산자 / 소비자 모델

테이블 큐에는 항상 생산자(Producer)와 소비자(Consumer)가 존재한다.

:TQ10000
QC가 생산자가 되고 서버 집합 1이 소비자가 된다.

:TQ10001
서버 집합 1이 생산자가 되고 서버 집합 2가 소비자가 된다.

:TQ10002
서버 집합 2가 생산자가 되고 QC가 소비자가 된다.

select 문장에서의 최종 소비자는 항상 QC일 것이며, Inter-Operation Parallelism이 나타날 때, 소비자 서버 집합은 from절에 테이블 큐를 참조하는 서브(Sub) SQL을 가지고 작업을 수행한다.

병렬 실행계획에서 생산자와 소비자 식별

Oracle 10g 이후부터는 생산자에 'PX SEND', 소비자에 'PX RECEIVE'가 표시되므로 테이블 큐를 통한 데이터 분배 과정을 좀 더 쉽게 확인할 수 있다.

1. QC가 dept 테이블을 읽어 첫 번째 서버 집합(Q1, 01)에게 전송한다.
2. 첫 번째 서버 집합(Q1, 01)은 emp 테이블을 병렬로 읽으면서 앞서 QC에게서 받아 둔 dept 테이블과 조인한다. 조인에 성공한 레코드는 바로바로 두 번째 서버 집합(Q1, 02)에게 전송한다.
3. 두 번째 서버 집합(Q1, 02)은 전송받은 레코드를 정렬하고 나서 QC에게 전송한다.

생산자로부터 소비자로 데이터 재분배가 일어날 때마다 'Name' 컬럼에 테이블 큐가 표시된다.

IN-OUT 오퍼레이션

• SERIAL(blank)
  Serial하게 실행
• S -> P : PARALLEL_FROM_SERIAL
  QC가 처리한 결과를 병렬 서버 프로세스에게 전달
• P -> S : PARALLEL_TO_SERIAL
  병렬 서버 프로세스가 처리한 결과를 QC에게 전달
• P -> P : PARALLEL_TO_PARALLEL
  두 개의 병렬 서버 프로세스 집합이 처리. 지정한 병렬도의 2배 만큼의 병렬 프로세스 생성
• PCWP : PARALLEL_COMBINED_WITH_PARENT
  병렬 서버 프로세스 집합이 현재 스텝과 그 부모 스텝을 모두 처리
• PCWC : PARALLEL_COMBINED_WITH_CHILD
  병렬 서버 프로세스 집합이 현재 스탭과 그 자식 스텝을 모두 처리

S -> P, P -> S, P -> P는 프로세스 간 통신이 발생한다.
PCWP와 PCWC는 프로세스 간 통신이 발생하지 않으며, 각 병렬 서버가 독립적으로 여러 스텝을 처리할 때 나타난다. 하위 스텝의 출력 값이 상위 스텝의 입력 값으로 사용된다.
P -> P, P -> S, PCWP, PCWC는 병렬 오퍼레이션인 반면 S -> P는 직렬(Serial) 오퍼레이션이다.

데이터 재분배

병렬 서버 프로세스 간에 데이터를 재분배하는 방식. PX SEND 뒤에 나타난다.

RANGE

• order by 또는 sort group by를 병렬로 처리할 때 사용된다. 정렬 작업을 맡은 두 번째 서버 집합의 프로세스마다 처리 범위를 지정하고 나서, 데이터를 읽는 첫 번째 서버 집합이 두 번째 서버 집합의

HASH

• 조인이나 hash group by를 병렬로 처리할 때 사용된다. 조인 키나 group by 키 값을 해시 함수에 적용하고 리턴된 값에 따라 데이터를 분배하는 방식이며, P->P 뿐만 아니라 S->P 방식으로 이루어질 수도 있다.

BROADCAST

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

KEY

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

ROUND-ROBIN

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

pq_distribute 힌트 활용

조인되는 양쪽 테이블의 파티션 구성, 데이터 크기 등에 따라 병렬 조인을 수행하는 옵티마이저의 선택이 달라질 수 있다. 대개 옵티마이저의 선택이 최적이라고 할 수 있지만 가끔 그렇지 못한 경우가 있는데 그럴 때 pq_distribute 힌트를 사용함으로써 옵티마이저의 선택을 무시하고 사용자가 직접 조인을 위한 데이터 분배 방식을 결정할 수 있다.

• 옵티마이저가 파티션된 테이블을 적절히 활용하지 못하고 동적 재분할을 시도할 때
• 기존 파티션 키를 무시하고 다른 키 값으로 동적 재분할을 시도할 때
• 통계정보가 부정확하거나 통계정보를 제공하기 어려운 상황(-> 옵티마이저가 잘못된 판단을 하기 쉬운 상황)에서 실행계획을 고정시키고자 할 때
• 기타 여러 가지 이유로 데이터 분배 방식을 변경하고자 할 때

병렬 방식으로 조인을 수행하기 위해서는 프로세스들이 서로 "독립적으로" 작업할 수 있도록 사전 준비작업이 필요하다. 먼저 데이터를 적절히 배분하는 작업이 선행되어야 하는 것이다. 그 중에서도 병렬 조인을 위해서는 '분배 & 조인(Distribute & Join) 원리'가 작동함을 이해하는 것이 매우 중요하다. 이때, pq_distribute 힌트는 조인에 앞서 데이터를 분배(distribute) 과정에만 관여하는 힌트임을 반드시 기억할 필요가 있다.

(데이터를 재분배하기 위해 해시 함수를 사용하는 것일 뿐 조인 방식과는 무관하다.)

pq_distribute 사용법

/*+ PQ_DISTRIBUTE(table, outer_distribution, inner_distribution) */

table : inner 테이블명 또는 alias
outer_distribution : outer 테이블의 distribution 방식
inner_distribution : inner 테이블의 distribution 방식

• pq_distribute(inner, none, none)
  Full-Partition Wise Join으로 유도할 때 사용하며 양쪽 테이블 모두 조인 컬럼에 대해 같은 기준으로 파티셔닝 되어있을 때만 작동한다.

Partition Wise Join

Partition Wise Join은 조인에 참여하는 두 테이블을 조인 컬럼에 대해 같은 기준으로 파티셔닝 하고서 각 파티션 짝끼리 독립적으로 조인을 수행하는 것을 말한다. 파티션 짝을 구성하고 나면 병렬 프로세스끼리 서로 데이터를 주고 받으며 통신할 필요가 전혀 없으므로 병렬 조인 성능을 크게 향상시킬 수 있다.


Full Partition Wise Join : 양쪽 테이블이 사전에 미리 파티셔닝 되어있어 곧바로 Partition Wise Join 하는 경우
Partial Partition Wise Join : 한쪽만 파티셔닝 돼 있어 나머지 한쪽을 실행시점에 동적으로 파티셔닝하고서 Partition Wise Join 하는 경우

• 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 Wise Join을 수행한다.

• pq_distribute(inner, broadcast, none)
  outer 테이블을 Broadcast하라는 뜻이다.

• pq_distribute(inner, none, broadcast)
  inner 테이블을 Broadcast하라는 뜻이다.

병렬 처리 시 주의사항

병렬 쿼리를 과도하게 사용하면 시스템을 마비시킬 수도 있으므로 적절한 사용 기준이 필요하다.

• 동시 사용자 수가 적은 애플리케이션 환경(야간 배치 프로그램, DW, OLAP 등)에서 직렬로 처리할 때보다 성능 개선 효과가 확실할 때
  (-> 이 기준에 따르면 작은 테이블은 병렬 처리 대상에서 제외됨)
• OLTP성 시스템 환경이더라도 작업을 빨리 완료함으로써 직렬로 처리할 때보다 오히려 전체적인 시스템 리소스(CPU, Memory) 사용률을 감소시킬 수 있을 때
  (-> 수행 빈도가 높지 않음을 전제로)

야간 배치 프로그램에는 병렬 처리를 자주 사용하는데, 야간 배치 프로그램은 전체 목표 시간을 달성하는 것을 목표로 해야지 개별 프로그램의 수행 속도를 단축하려고 필요 이상의 병렬도를 지정해서는 안된다.

여러 팀에서 작성한 배치 프로그램이 동시에 수행되는 상황에서 특정 소수 배치 작업이 과도한 병렬 처리를 시도한다면 CPU, 메모리, 디스크 등 자원에 대한 경합 때문에 오히려 전체 배치 수행이 늘어날 수 있다.

결론적으로, 성능 개선 효과가 확실한 최소한의 병렬도를 지정하려는 노력이 필요하다.

하지만 데이터 이행(Migration)의 경우 성능개선 효과가 확실한 최소한의 병렬도를 지정하려는 노력이 필요한데, 이떄는 모든 애플리케이션을 중지시키고 이행 프로그램이 시스템을 독점적으로 사용하기 때문에 모든 리소스를 활용해 이행 시간을 최대한 단축하는 것을 목표로 삼아야 한다.

병렬 DML 수행 시 Exclusive 모드 테이블 Lock이 걸리므로 트랜잭션이 활발한 주간에 사용해서는 안된다.