제5절 배치 프로그램 튜닝

배치 프로그램 튜닝 개요

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

2. 병렬 처리 활용

• 병렬 처리란, SQL문이 수행해야 할 작업 범위를 여러 개의 작은 단위로 나누어 여러 프로세스(또는 쓰레드)가 동시에 처리하는 것을 말한다.

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

 select /*+ index_ffs(o, 주문_idx)) parallel_index(o, 주문_idx, 4) */ count(*) 주문건수 
 from 주문 o 
 where 주문일시 between '20100101' and '20101231'
 
 -- parallel 힌트를 사용할 때는 반드시 Full 힌트도 함께 사용하는 습관이 필요하다.
 -- 옵티마이저에 의해 인덱스 스캔이 선택되면 parallel 힌트가 무시되기 때문이다.
 -- parallel_index 힌트를 사용할 때, 반드시 index 또는 index_ffs 힌트를 함께 사용하는 습관도 필요하다. 
 -- 옵티마이저에 의해 Full Table Scan이 선택되면 parallel_index 힌트가 무시되기 때문이다.

 -- SQL Server --

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

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

• Query Coordinator(이하 QC)는 병렬 SQL문을 발행한 세션을 말하며, 병렬 서버 프로세스는 실제 작업을 수행하는 개별 세션들을 말한다. QC의 역할은 다음과 같다.
• 병렬 SQL이 시작되면 QC는 사용자가 지정한 병렬도(DOP, degree of parallelism)와 오퍼레이션 종류에 따라 하나 또는 두 개의 병렬 서버 집합(Server Set)을 할당한다.
• 우선 서버 풀(Parallel Execution Server Pool)로부터 필요한 만큼 서버 프로세스를 확보하고, 부족분은 새로 생성한다.
• QC는 각 병렬 서버에게 작업을 할당한다. 작업을 지시하고 일이 잘 진행되는지 관리감독하는 작업반장 역할이다.
• 병렬로 처리하도록 사용자가 지시하지 않은 테이블은 QC가 직접 처리한다.

나. Intra-Operation Parallelism과 Inter-Operation Parallelism

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

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

다. 테이블 큐

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

라. IN-OUT 오퍼레이션

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

마. 데이터 재분배

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) 과정에만 관여하는 힌트임을 반드시 기억할 필요가 있다.

2) pq_distribute 사용법

/*+ pq_distribute(inner, none, none) */
/*+ pq_distribute(inner, partition, none) */
/*+ pq_distribute(inner, none, partition) */
/*+ pq_distribute(inner, hash, hash) */
/*+ pq_distribute(inner, broadcast, none) */
/*+ pq_distribute(inner, none, broadcast) */

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

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  |           | 
--------------------------------------------------------------------------------

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 등) 사용률을 감소시킬 수 있을 때(→ 수행 빈도가 높지 않음을 전제로)