메뉴 건너뛰기

bysql.net

제5절_배치_프로그램_튜닝

2012.07.04 19:15

보라빛고양이 조회 수:6906

1. 배치 프로그램 튜닝 개요

가. 배치 프로그램이란

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

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

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

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

기업마다 업무 요건이 워낙 복잡 다양하므로 이 외에도 여러 가지 형태가 존재할 수 있으며, 정기 배치 형태가 가장 일반적이다.

나. 배치 환경의 변화

  • 과거

- 일(Daily) 또는 월(Monthly) 배치 작업 위주

- 야간에 생성된 데이터를 주간 업무시간에 활용

- 온라인과 배치 프로그램의 구분이 비교적 명확

  • 현재

- 시간(Hourly) 배치 작업의 비중이 증가

- 분(minutely) 배치 작업이 일부 존재

- On-Demand 배치를 제한적이나마 허용

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

다. 성능 개선 목표 설정

On-Demand 배치의 등장으로 온라인 프로그램과의 경계가 모호해져, 사실 온라인과 배치로 양분하는 것 자체가 무의미하게 느껴질 수도 있다. 하지만 배치 프로그램에서의 성능 목표와 튜닝 기법은 온라인 프로그램에서의 그것과 달라야 한다. 

온라인 프로그램은 경우에 따라 전체 처리속도 최적화나 최초 응답속도 최적화를 목표로 선택하지만, 배치 프로그램은 항상 전체 처리속도 최적화를 목표로 설정해야 한다. 개별 프로그램 차원에서도 그렇지만 야간에 수행되는 전체 배치 프로그램에 대한 목표도 마찬가지다. 개별 서비스 또는 프로그램을 가장 빠른 속도로 최적화하더라도 전체 배치 프로그램 수행시간을 단축시키지 못하면 무의미하다. 튜닝 대상을 선정할 때도 이런 기준을 갖고 선별해야 한다. 

 자원 사용 측면도 중요한 고려사항이다. 자원에 대한 경합이 극심한 상황에선 프로그램들이 정상적으로 진행하기 어렵기 때문이다. 그런 측면에서 보면, 병렬도(DOP, degree of parallelism)를 32로 지정해서 5분이 소요되는 프로그램을 병렬 처리 없이 10분이 소요되도록 하는 것이 오히려 나을 수 있다. 

시스템 자원을 독점적으로 사용하도록 설정된 프로그램을 찾아 병렬도를 제한하고, 동시에 수행되는 프로그램 개수도 적절히 유지해야 한다. 실제 개발 프로젝트에 가 보면, 시스템 자원에 대한 사용 권한을 적절히 배분하지 않고 각 서브 개발 파트에서 개발한 배치 프로그램을 12시 정각에 동시에 수행하는 경우를 종종 볼 수 있다. 

그럴 때 배치 윈도우(Batch Window)를 적절히 조절하는 것만으로 배치 프로그램 수십 개를 튜닝한 것과 같은 효과를 내기도 한다. 원리는 간단하다. 같은 시간대에 수많은 프로그램이 집중적으로 수행되면 자원(CPU, Memory, Disk 등)과 Lock(Latch와 같은 내부 Lock까지 포함)에 대한 경합이 발생하기 때문이다. 그러면 프로세스가 실제 일한 시간보다 대기하는 시간이 더 많아지므로 총 수행시간이 늘어나는 것이다. 상용 툴(Tool)을 이용하면 좋겠지만, [그림 Ⅲ-5-14]처럼 오피스용 문서를 이용해서도 충분히 배치 윈도우를 관리할 수 있다.

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

개발자 스타일과 애플리케이션 아키텍처에 따라 배치그램의 구현방식이 천차만별이지만, 크게 2가지 스타일로 요약할 수 있다.

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

성능 측면에선 One SQL 위주의 프로그램이 월등하다. 절차형으로 작성된 프로그램은 다음과 같은 비효율 때문에 느릴 수 밖에 없고, 개별 SQL을 최적화하더라도 그것을 담고 있는 프로그램 전체를 최적화하는 데 한계를 보인다.

  • 반복적인 데이터베이스 Call 발생
  • Random I/O 위주
  • 동일 데이터를 중복 액세스

하지만 절차형으로 작성된 프로그램을 One SQL 위주의 프로그램으로 구현하기가 쉽지만은 않다. 개발자의 기술력이 부족한 이유도 있지만, 업무의 복잡성 때문에 불가능한 경우도 많다. 무엇보다, 섣불리 One SQL로 통합했다가 결과가 틀려지는 위험성을 간과하기 어렵다. 할 수 있다면 One SQL 위주로 구현해야겠지만, 불가능하다면 그 안에서 튜닝 방안을 찾으려고?안을 요약하면 [표 Ⅲ-5-4]와 같다.


2. 병렬 처리 활용

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

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

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

select /*+ index_ffs(o, 주문_idx)) parallel_index(o, 주문_idx, 4) */ count(*) 주문건수 from 주문 o where 주문일시 between '20100101' and '20101231'

SQL Server에선 옵티마이저 힌트를 아래와 같이 지정한다.

select count(*) 주문건수 from 주문 where 주문일시 between '20100101' and '20101231' option (MAXDOP 4)

지금부터, 병렬 옵션을 사용했을 때 내부적으로 어떻게 작업을 병렬로 진행하는지 Oracle 아키텍처 중심으로 설명하고, parallel 힌트와 함께 사용할 수 있는 pq_distribute 힌트 활용법까지 소개하고자 한다.

가. Query Coordinator와 병렬 서버 프로세스

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

  • 병렬 SQL이 시작되면 QC는 사용자가 지정한 병렬도(DOP, degree of parallelism)와 오퍼레이션 종류에 따라 하나 또는 두 개의 병렬 서버 집합(Server Set)을 할당한다. 우선 서버 풀(Parallel Execution Server Pool)로부터 필요한 만큼 서버 프로세스를 확보하고, 부족분은 새로 생성한다.

  • QC는 각 병렬 서버에게 작업을 할당한다. 작업을 지시하고 일이 잘 진행되는지 관리감독하는 작업반장 역할이다.
  • 병렬로 처리하도록 사용자가 지시하지 않은 테이블은 QC가 직접 처리한다. 예를 들어, 아래 실행계획에서 dept 테이블을 직렬로 읽어 병렬 서버에 전송하는 8~9번 오퍼레이션은 QC의 몫이다.

  • QC는 각 병렬 서버로부터의 산출물을 통합하는 작업을 수행한다. 예를 들어 집계 함수(sum, count, avg, min, max 등)가 사용된 아래와 같은 병렬 쿼리를 수행할 때, 각 병렬 서버가 자신의 처리 범위 내에서 집계(4번 단계)한 값을 QC에게 전송(3번 단계)하면 QC가 최종 집계 작업을 수행(1번 단계)한다.

  • QC는 쿼리의 최종 결과집합을 사용자에게 전송하며, DML일 때는 갱신 건수를 집계해서 전송해 준다. 쿼리 결과를 전송하는 단계에서 수행되는 스칼라 서브쿼리도 QC가 수행한다.

select /*+ ordered use_hash(d) full(d) full(e) noparallel(d) parallel(e 4) */
count(*), min(sal), max(sal), avg(sal), sum(sal)
from dept d, emp e
where d.loc = 'CHICAGO' and e.deptno = d.deptno 
-------------------------------------------------------------
| Id | Operation                     | Name      | TQ      |IN-OUT | PQ Distrib |
-------------------------------------------------------------
| 0 | SELECT STATEMENT     |               |          |           |                  |
| 1 | SORT AGGREGATE        |               |          |           |                  |
| 2 | PX COORDINATOR         |               |          |           |                  |
| 3 | PX SEND QC (RANDOM) | :TQ10002 | Q1,02 | P->S  | QC (RAND) |
| 4 | SORT AGGREGATE        |               | Q1,02 | PCWP |                  |
| 5 | HASH JOIN                    |               | Q1,02 | PCWP |                  |
| 6 | BUFFER SORT               |               | Q1,02 | PCWC |                  |
| 7 | PX RECEIVE                  |               | Q1,02 | PCWP |                  |
| 8 | PX SEND HASH              | :TQ10000 |          | S->P  | HASH         |
| 9 | TABLE ACCESS FULL     | DEPT      |         |           |                  |
| 10 | PX RECEIVE                 |              | Q1,02 | PCWP |                  |
| 11 | PX SEND HASH             | :TQ10001 | Q1,01 | P->P  | HASH         |
| 12 | PX BLOCK ITERATOR    |               | Q1,01 | PCWC |                 |
| 13 | TABLE ACCESS FULL   | EMP       | Q1,01 | PCWP |                  |
-------------------------------------------------------------

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

나. Intra-Operation Parallelism과 Inter-Operation Parallelism

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

[그림 Ⅲ-5-15]는 order by를 포함하는 위 병렬 쿼리의 수행과정을 도식화한 것이다.

서로 배타적인 범위를 독립적으로 동시에 처리하는 것을 ‘Intra-Operation Parallelism’이라고 한다.

첫 번째 서버 집합(P000~P003)에 속한 4개의 프로세스가 범위를 나눠 고객 데이터를 읽는 작업과,

두 번째 서버 집합(P004~P007)이 첫 번째 서버 집합으로부터 전달받은 고객 데이터를 정렬하는 작업이 모두 여기에 속한다.

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

다. 테이블 큐

Intra-Operation Parallelism은 한 병렬 서버 집합(Server Set)에 속한 여러 프로세스가 처리 범위를 달리하면서 병렬로 작업을 진행하는 것이므로 집합 내에서는 절대 프로세스 간 통신이 발생하지 않는다.

반면, Inter-Operation Parallelism은 프로세스 간 통신이 발생하고, 메시지 또는 데이터를 전송하기 위한 통신 채널이 필요하다. 쿼리 서버 집합 간(P→P) 또는 QC와 쿼리 서버 집합 간(P→S, S→P) 데이터 전송을 위해 연결된 파이프 라인(Pipeline)을 ‘테이블 큐(Table Queue)’라고 한다.

각 테이블 큐에 부여된 :TQ10000, :TQ10001, :TQ10002와 같은 이름을 ‘테이블 큐 식별자(TQ Identifier)’라고 한다.

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

[그림 Ⅲ-5-16]을 보면, 쿼리 서버 집합 간(P→P) Inter-Operation Parallelism이 발생할 때는 사용자가 지정한 병렬도(=2)의 배수(4개)만큼 서버 프로세스가 필요한 것을 알 수 있다.

또한 테이블 큐(:TQ10001)에는 병렬도의 제곱(22=4)만큼 파이프 라인이 필요하다는 사실도 알 수 있다. 참고로 그림 Ⅲ-5-15를 보면, 병렬도가 4이므로 8(=4×2)개 서버 프로세스를 위해 16(=42)개의 파이프 라인이 형성되었다.

  • 생산자 / 소비자 모델

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

[그림 Ⅲ-5-16]을 보면, 처음 dept 테이블을 읽어 분배하는 :TQ10000에서는 QC가 생산자고 서버 집합 1이 소비자다.

이어지는 두 번째 테이블 큐 :TQ10001에서는 서버 집합 1이 생산자가 되고, 서버 집합 2가 소비자가 된다.

마지막으로, 정렬된 최종 결과집합을 전송하는 :TQ10002에서는 서버 집합 2가 생산자가 되고 QC가 소비자가 된다.

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

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

아래는 앞서 본 쿼리에 대한 실행계획이다. Oracle 10g 이후부터는 이처럼 생산자에 ‘PX SEND’, 소비자에 ‘PX RECEIVE’가 표시되므로 테이블 큐를 통한 데이터 분배 과정을 좀 더 쉽게 확인할 수 있게 되었다.

각 오퍼레이션이 어떤 서버 집합에 속한 병렬 프로세스에 의해 수행되는지는 ‘TQ’ 칼럼(4번째 칼럼)에 보이는 서버 집합 식별자를 통해 확인할 수 있다.

QC가 dept 테이블을 읽어 첫 번째 서버 집합(Q1,01)에게 전송한다.

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

마지막으로, 두 번째 서버 집합(Q1,02)은 전송받은 레코드를 정렬하고 나서 QC에게 전송한다.

생산자로부터 소비자로 데이터 재분배가 일어날 때마다 ‘Name’ 칼럼에 테이블 큐(:TQxxxxx 형태)가 표시된다.

라. IN-OUT 오퍼레이션

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

마. 데이터 재분배

병렬 서버 프로세스 간에 데이터를 재분배하는 방식에는 일반적으로 아래 5가지가 사용된다.

  • RANGE

order by 또는 sort group by를 병렬로 처리할 때 사용된다.

정렬 작업을 맡은 두 번째 서버 집합의 프로세스마다 처리 범위(예를 들어, A~G, H~M, N~S, T~Z)를 지정하고 나서,

데이터를 읽는 첫 번째 서버 집합이 두 번째 서버 집합의 정해진 프로세스에게 “정렬 키 값에 따라” 분배하는 방식이다.

QC는 각 서버 프로세스에게 작업 범위를 할당하고 정렬 작업에는 직접 참여하지 않으며, 정렬이 완료되고 나면 순서대로 결과를 받아서 사용자에게 전송하는 역할만 한다.

  • HASH

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

  • BROADCAST

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

  • KEY

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

  • ROUND-ROBIN

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

바. pq_distribute 힌트 활용

1) pq_distribute 힌트의 용도

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

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

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

먼저 데이터를 적절히 배분하는 작업이 선행되어야 하는 것이다.

병렬 쿼리는 ‘분할 & 정복(Divide & Conquer) 원리’에 기초한다.

 그 중에서도 병렬 조인을 위해서는 ‘분배 & 조인(Distribute & Join) 원리’가 작동함을 이해하는 것이 매우 중요하다.

이때, pq_distribute 힌트는 조인에 앞서 데이터를 분배(distribute) 과정에만 관여하는 힌트임을 반드시 기억할 필요가 있다.


select /*+ ordered use_merge(e) parallel(d 4) parallel(e 4) pq_distribute(e hash hash) */ * 

from dept d, emp e 
where e.deptno = d.deptno 
-------------------------------------------------------------
| Id | Operation | Name | TQ |IN-OUT | PQ Distrib |
-------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | PX COORDINATOR | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10002 | Q1,02 | P->S  | QC (RAND) |
| 3 | MERGE JOIN                  |              | Q1,02 | PCWP |                  |
| 4 | SORT JOIN                     |              | Q1,02 | PCWP |                  |
| 5 | PX RECEIVE                  |               | Q1,02 | PCWP |                 |
| 6 | PX SEND HASH              | :TQ10000  | Q1,00 | P->P  | HASH        |
| 7 | PX BLOCK ITERATOR      |               | Q1,00 | PCWC |                |
| 8 | TABLE ACCESS FULL     | DEPT      | Q1,00 | PCWP |                |
| 9 | SORT JOIN                     |               | Q1,02 | PCWP |                |
| 10 | PX RECEIVE                 |               | Q1,02 | PCWP |                |
| 11 | PX SEND HASH             | :TQ10001  | Q1,01 | P->P  | HASH       |
| 12 | PX BLOCK ITERATOR     |               | Q1,01 | PCWC |                |
| 13 | TABLE ACCESS FULL    | EMP        | Q1,01 | PCWP |                |
-------------------------------------------------------------

2) pq_distribute 사용법

pq_distribute 힌트의 사용법은 다음과 같다.

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

  • pq_distribute(inner, broadcast, none)

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

  • pq_distribute(inner, none, broadcast)

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

3) pq_distribute 힌트를 이용한 튜닝 사례

통계 정보가 없거나 잘못된 상태에서 병렬 조인을 수행하면 옵티마이저가 아주 큰 테이블을 Broadcast 하는 경우가 종종 생긴다. 임시 테이블을 많이 사용하는 야간 배치나 데이터 이행(Migration) 프로그램에서 그런 문제가 자주 나타나는데, 아래는 데이터 이행 도중 실제 문제가 발생했던 사례다.

SQL> 
INSERT /*+ APPEND */ INTO 상품기본이력 ( ... )
2 SELECT /*+ PARALLEL(A,32) PARALLEL(B,32) PARALLEL(C,32) PARALLEL(D,32) */ ......
3 FROM 상품기본이력임시 a, 상품 b, 코드상세 c, 상품상세 d
4 WHERE a.상품번호= b.상품번호
5 AND ...
6 /


INSERT /*+ append */ INTO 상품기본이력 (
*
1행에 오류: ORA-12801: 병렬 질의 서버 P013에 오류신호가 발생했습니다
ORA-01652: 256(으)로 테이블 공간 TEMP에서 임시 세그먼트를 확장할 수 없습니다 경 과: 01:39:56.08 
----------------------------------------------------------------------
| Operation | Name | Rows | Pstart | Pstop |IN-OUT | PQ Distrib |
----------------------------------------------------------------------
| INSERT STATEMENT   |                          | 5248   |    |      |          |                   |
| LOAD AS SELECT       |                          |          |    |      |          |                   |
| HASH JOIN                 |                          | 5248   |    |      | P->S  |QC (RAND)   |
| HASH JOIN OUTER      |                          | 5248   |    |      | P->P  |BROADCAST |
| HASH JOIN                 |                          | 5248   |    |      | PCWP |                   |
| PARTITION HASH ALL  |                          |          | 1 | 128 | PCWP |                   |
| TABLE ACCESS FULL | 상품기본이력임시 | 5248   | 1 | 128 | P->P  |BROADCAST |
| TABLE ACCESS FULL | 상품                   | 7595K |    |      | PCWP |                   |
| TABLE ACCESS FULL | 코드상세             | 26      |    |      | P->P  |BROADCAST |
| TABLE ACCESS FULL | 상품상세             | 7772K |    |      | PCWP |                   |
----------------------------------------------------------------------

1시간 40분간 수행되던 SQL이 임시 세그먼트를 확장할 수 없다는 오류 메시지를 던지면서 멈춰 버렸고, 분석해 보니 상품기본이력임시 테이블에 통계 정보가 없던 것이 원인이었다. 실제 천만 건에 이르는 큰 테이블이었는데, 통계 정보가 없어 옵티마이저가 5,248건의 작은 테이블로 판단한 것을 볼 수 있다. 이 큰 테이블을 32개 병렬 서버에게 Broadcast하는 동안 과도한 프로세스 간 통신이 발생했고, 결국 Temp 테이블 스페이스를 모두 소진하고서 멈췄다. pq_distribute 힌트를 이용해 데이터 분배 방식을 조정하고 나서 다시 수행해 본 결과, 아래와 같이 2분 29초 만에 작업을 완료하였다.

SQL> INSERT /*+ APPEND */ INTO 상품기본이력 ( ... )
2 SELECT /*+ ORDERED PARALLEL(A,16) PARALLEL(B,16) PARALLEL(C,16) PARALLEL(D,16)
3 PQ_DISTRIBUTE(B, NONE, PARTITION)
4 PQ_DISTRIBUTE(C, NONE, BROADCAST)
5 PQ_DISTRIBUTE(D, HASH, HASH) */ ......
6 FROM 상품기본이력임시 a, 상품 b, 코드상세 c, 상품상세 d
7 WHERE a.상품번호= b.상품번호
8 AND ...
9 /

8796902 개의 행이 만들어졌습니다. 경 과: 00:02:29.00 
----------------------------------------------------------------------
| Operation | Name | Rows | Pstart | Pstop |IN-OUT | PQ Distrib |
----------------------------------------------------------------------
| INSERT STATEMENT   |                          | 5248   |    |        |          |                   |
| LOAD AS SELECT       |                          |          |    |        |         |                    |
| HASH JOIN                 |                          | 5248   |    |        | P->S |QC (RAND)    |
| HASH JOIN OUTER      |                          | 5248   |    |        | P->P |HASH            |
| HASH JOIN                 |                          | 5248   |    |       | PCWP |                    |
| PARTITION HASH ALL |                           |          | 1 | 128  | PCWP |                    |
| TABLE ACCESS FULL | 상품기본이력임시 | 5248   | 1 | 128  | PCWP |                    |
| TABLE ACCESS FULL | 상품                   | 7595K |    |       | P->P   |PART (KEY)   |
| TABLE ACCESS FULL | 코드상세             | 26      |    |       | P->P   |BROADCAST |
| TABLE ACCESS FULL | 상품상세             | 7772K |    |       | P->P   | HASH          |  ----------------------------------------------------------------------

사. 병렬 처리 시 주의사항

병렬 쿼리를 과도하게 사용하면 시스템을 마비시킬 수도 있다. 적절한 사용 기준이 필요하다는 얘기인데, 그럼 언제 병렬 처리 기법을 사용하는 것이 바람직한가?

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

야간 배치 프로그램에는 병렬 처리를 자주 사용하는데, 야간 배치 프로그램은 전체 목표 시간을 달성하는 것을 목표로 해야지 개별 프로그램의 수행속도를 단축하려고 필요 이상의 병렬도를 지정해선 안 된다. 업무적으로 10분 이내 수행이 목표인 프로그램을 5분으로 단축하려고 병렬 처리 기법을 남용하지 말라는 뜻이다.

야간이더라도, 여러 팀에서 작성한 배치 프로그램이 동시에 수행되는 상황에서 특정 소수 배치 작업이 과도한 병렬 처리를 시도한다면 CPU, 메모리, 디스크 등 자원에 대한 경합 때문에 오히려 전체 배치 수행 시간이 늘어날 수 있음을 기억하자. 그리고 병렬도를 높인다고 성능이 선형적으로 좋아지는 것도 아니다. 결론적으로, 성능개선 효과가 확실한 최소한의 병렬도를 지정하려는 노력이 필요하다. 물론 시스템 리소스를 최대한 사용해야 할 때도 있는데, 데이터 이행(Migration)이 대표적이다. 이때는 모든 애플리케이션을 중지시키고 이행 프로그램이 시스템을 독점적으로 사용하기 때문에 가능한 모든 리소스를 활용해 이행 시간을 최대한 단축하는 것을 목표로 삼는 것이 당연하다. 병렬 DML 수행 시 Exclusive 모드 테이블 Lock이 걸리므로 트랜잭션이 활발한 주간에 절대 사용해선 안 된다

번호 제목 글쓴이 날짜 조회 수
18 제2절_쿼리변환 ljw 2012.05.14 5613
17 제2절_인덱스_튜닝 balto 2012.05.18 9904
16 제1절_인덱스_기본_원리 오예스 2012.05.22 7100
15 제3절 조인 기본 원리 보라빛고양이 2012.05.30 6980
14 제3절_조인_기본_원리 보라빛고양이 2012.05.31 3845
13 제2절_소트_튜닝 file ljw 2012.06.04 12024
12 제4절_고급_조인_기법 suspace 2012.06.05 31231
11 제1절 고급 SQL 활용 실천하자 2012.06.11 8397
10 제1절_고급_SQL_활용 실천하자 2012.06.11 6285
9 제3절_DML_튜닝 balto 2012.06.13 9073
8 제4절_파티션_활용 오예스 2012.06.26 5753
» 제5절_배치_프로그램_튜닝 보라빛고양이 2012.07.04 6906
6 문제작성 실천하자 2012.07.16 5794
5 Week1_위충환 [1] 실천하자 2012.07.16 9272
4 Week1_이진우 [5] ljw 2012.07.16 46262
3 Week1_승대수 보라빛고양이 2012.07.17 3053
2 Week1_이주영 suspace 2012.07.17 5025
1 Week1_박우창 [1] balto 2012.07.17 9892