5.배치 프로그램 튜닝 (by xsoft) [2013.11.27]
목차
1. 배치 프로그램 튜닝 개요
가) 배치 프로그램이란
- 배치 프로그램 특징
- 사용자와의 상호작용 없이
- 대 량의 데이터를 처리하는
- 일련의 작업들을 묶어
- 정기적으로 반복 수행하거나
- 정해진 규칙에 따라 자동으로 수행
- 배치 프로그램 구분
- 정기 배치 : 정해진 시점(주로 야간)에 실행
- 이벤트성 배치 : 사전에 정의해 둔 조건이 충족되면 자동으로 실행
- On-Demand 배치 : 사용자의 명시적인 요구가 있을 때마다 실행
나) 배치 환경의 변화
- 과거
- Daily 또는 Monthly 배치 작업 위주
- 야간에 생성된 데이터를 주간 업무시간에 활용
- 온라인과 배치 프로그램의 구분이 비교적 명확
- 현재
- 시간(Hourly) 배치 작업의 비중이 증가
- 분(minutely) 배치 작업이 일부 존재
- On-Demand 배치를 제한적이나마 허용
다) 성능 개선 목표 설정
- 온라인과 배치 성능 개선 목표 비교
- 온라인 프로그램은 경우에 따라 전체 처리속도 최적화나 최초 응답속도 최적화를 목표
- 배치 프로그램은 항상 전체 처리속도 최적화를 목표로 설정
- 배치 수행시간 배분(Batch Window)
- 같은 시간대에 수많은 프로그램이 집중적으로 수행되면 자원과 Lock에 대한 경합이 발생하므로 수행시간을 다른 시간대에 배정할 경우 속도 개선 가능함
라) 배치 프로그램 구현 패턴과 튜닝 방안
- 일반적인 개발 패턴
- 절차형으로 작성된 프로그램 : 애플리케이션 커서를 열고 루프 내에서 또 다른 SQL이나 서브 프로시저를 호출하면서 같은 처리를 반복하는 형태
- One SQL 위주 프로그램 : One SQL로 구성하거나 집합적으로 정의된 여러 SQL을 단계적으로 실행
- 성능 측면에선 One SQL 위주의 프로그램이 월등하나, 변경이 쉽지 않고, 데이터가 다르게 나올 수 있으므로 주의해야 함
2. 병렬 처리 활용
- 병렬 처리란, SQL문이 수행해야 할 작업 범위를 여러 개의 작은 단위로 나누어 여러 프로세스(또는 쓰레드)가 동시에 처리하는 것을 의미함
- 여러 프로세스가 동시에 작업하므로 대용량 데이터를 처리할 때 수행 속도를 극적으로 단축시킬 수 있음
-- 1. Oracle
SELECT /*+ FULL(O) PARALLEL(O 4) */ -- PARALLEL 힌트 사용 시 가급적 FULL 힌트도 같이 사용
COUNT★ 주문건수,
SUM(주문수량) 주문수량,
SUM(주문금액) 주문금액
FROM 주문 O
WHERE 주문일시 BETWEEN '20100101' AND '20101231';
-- 2. SQL Server
SELECT COUNT★ 주문건수
FROM 주문
WHERE 주문일시 BETWEEN '20100101' AND '20101231'
OPTION(MAXDOP 4)
가) Query Coordinator와 병렬 서버 프로세스
- Query Coordinator(이하 QC)
- 병렬 SQL문을 발행한 세션을 말하며 병렬 서버 프로세스는 실제 작업을 수행하는 개별 세션들을 의미함
-- SQL
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 E.DEPTNO = D.DEPTNO
AND D.LOC = 'CHICAGO'
;
-- 실행계획
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 18 | 8 (13)| 00:00:01 | | | |
| 1 | SORT AGGREGATE | | 1 | 18 | | | | | |
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10002 | 1 | 18 | | | Q1,02 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | 18 | | | Q1,02 | PCWP | |
|* 5 | HASH JOIN | | 5 | 90 | 8 (13)| 00:00:01 | Q1,02 | PCWP | |
| 6 | BUFFER SORT | | | | | | Q1,02 | PCWC | |
| 7 | PX RECEIVE | | 1 | 11 | 5 (0)| 00:00:01 | Q1,02 | PCWP | |
| 8 | PX SEND HASH | :TQ10000 | 1 | 11 | 5 (0)| 00:00:01 | | S->P | HASH |
|* 9 | TABLE ACCESS FULL| DEPT | 1 | 11 | 5 (0)| 00:00:01 | | | |
| 10 | PX RECEIVE | | 14 | 98 | 2 (0)| 00:00:01 | Q1,02 | PCWP | |
| 11 | PX SEND HASH | :TQ10001 | 14 | 98 | 2 (0)| 00:00:01 | Q1,01 | P->P | HASH |
| 12 | PX BLOCK ITERATOR | | 14 | 98 | 2 (0)| 00:00:01 | Q1,01 | PCWC | |
| 13 | TABLE ACCESS FULL| EMP | 14 | 98 | 2 (0)| 00:00:01 | Q1,01 | PCWP | |
-------------------------------------------------------------------------------------------------------------------
-- 모니터링
Pll_Tree SID,Serial# Server# QCSID DEGREE S SPID MODULE PROGRA WAIT LCE SQL_TEXT
---------- ------------ -------- ------ ------ - -------- -------------------- ------ ------------------------------ ------- ------------------------------------------------------------
■+21785 21785,24411 Main 21785 A 66584990 SQL*Plus sw.exe PX Deq Credit: send blkd 1 SELECT /*+ ORDERED USE_HASH(D) FULL(D) FULL(E) NOP
┖2424 2424,11537 1 21785 4 A 41354360 SQL*Plus (P000) PX Deq Credit: send blkd 2 SELECT /*+ ORDERED USE_HASH(D) FULL(D) FULL(E) NOP
┖2850 2850,65135 2 21785 4 A 4064746 SQL*Plus (P001) PX Deq: Table Q Normal 2 SELECT /*+ ORDERED USE_HASH(D) FULL(D) FULL(E) NOP
┖3243 3243,8229 3 21785 4 A 7013862 SQL*Plus (P002) PX Deq: Table Q Normal 2 SELECT /*+ ORDERED USE_HASH(D) FULL(D) FULL(E) NOP
┖3632 3632,849 4 21785 4 A 5244324 SQL*Plus (P003) PX Deq: Table Q Normal 2 SELECT /*+ ORDERED USE_HASH(D) FULL(D) FULL(E) NOP
┖4057 4057,8575 1 21785 4 A 6817210 SQL*Plus (P004) PX Deq: Execution Msg 2 SELECT /*+ ORDERED USE_HASH(D) FULL(D) FULL(E) NOP
┖4458 4458,1683 2 21785 4 A 9962960 SQL*Plus (P005) PX Deq: Execution Msg 2 SELECT /*+ ORDERED USE_HASH(D) FULL(D) FULL(E) NOP
┖4861 4861,2147 3 21785 4 A 55706012 SQL*Plus (P006) PX Deq: Execution Msg 2 SELECT /*+ ORDERED USE_HASH(D) FULL(D) FULL(E) NOP
┖5253 5253,14527 4 21785 4 A 5113530 SQL*Plus (P007) PX Deq: Execution Msg 2 SELECT /*+ ORDERED USE_HASH(D) FULL(D) FULL(E) NOP
*/
- 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가 수행함.
나) Intra-Operation Parallelism과 Inter-Operation Parallelism
SELECT /* + FULL(고객) PARALLEL(고객 4) */
*
FROM 고객
ORDER BY 고객명
- Intra-Operation Parallelism
- 서로 배타적인 범위를 독립적으로 동시에 처리하는 것.
- 첫 번째 서벼 집합(pOOO~P003) 에 속한 4개의 프로세스가 범위를 나눠 고객 데이터를 읽는 작업과, 두 번째 서버 집합(P004~P007)이 첫 번째 서버 집합으로부터 전달받은 고객 데이터를 정렬하는 작업이 모두 여기에 속함. 같은 서벼 집합끼리는 서로 데이터를 주고받을 일이 없음.
- Inter-Operation Parallelism
- 고객 데이터를 읽어 반대편 서버 집합에 분배하거나 정렬된 결과를 QC 에게 전송하는 작업을 병렬로 동시에 진행하는 것.
- 이때는 항상 프로세스 간 통신이 발생함.
다) 테이블 큐
-- 1. SQL
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
;
-- 2. 실행계획
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 812 | 9 (12)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (ORDER) | :TQ10002 | 14 | 812 | 9 (12)| 00:00:01 | Q1,02 | P->S | QC (ORDER) |
| 3 | SORT ORDER BY | | 14 | 812 | 9 (12)| 00:00:01 | Q1,02 | PCWP | |
| 4 | PX RECEIVE | | 14 | 812 | 8 (0)| 00:00:01 | Q1,02 | PCWP | |
| 5 | PX SEND RANGE | :TQ10001 | 14 | 812 | 8 (0)| 00:00:01 | Q1,01 | P->P | RANGE |
|* 6 | HASH JOIN | | 14 | 812 | 8 (0)| 00:00:01 | Q1,01 | PCWP | |
| 7 | BUFFER SORT | | | | | | Q1,01 | PCWC | |
| 8 | PX RECEIVE | | 4 | 80 | 5 (0)| 00:00:01 | Q1,01 | PCWP | |
| 9 | PX SEND BROADCAST | :TQ10000 | 4 | 80 | 5 (0)| 00:00:01 | | S->P | BROADCAST |
| 10 | TABLE ACCESS FULL| DEPT | 4 | 80 | 5 (0)| 00:00:01 | | | |
| 11 | PX BLOCK ITERATOR | | 14 | 532 | 3 (0)| 00:00:01 | Q1,01 | PCWC | |
| 12 | TABLE ACCESS FULL | EMP | 14 | 532 | 3 (0)| 00:00:01 | Q1,01 | PCWP | |
--------------------------------------------------------------------------------------------------------------------
;
- Intra-Operation Parallelism
- 한 병렬 서벼 집합(Server Set)에 속한 여러 프로세스가 처리 범위를 달리하면서 병렬로 작업을 진행하는 것이므로 집합 내에서는 절대 프로세스 간 통신이 발생하지 않음.
- Inter-Operation Parallelism
- 프로세스 간 통신이 발생하고 메시지 또는 데이터를 전송하기 위한 통신 채널이 펼요함.
- 테이블 큐(Table Queue)
- 쿼리 서버 집합 간(P->P) 또는 QC와 쿼리 서버 집합 간(P->S, S->P) 데이터 전송을 위해 연결된 파이프 라인 (Pipeline)
- 테이블 큐 식별자(TQ Identifier)
- <그림 III-5-16> 에서 보듯, 각 태이블 큐에 부여된 :TQI0000, :TQI000l, :TQI0002와 같은 이름
- 쿼리 서버 집합 간(P->P) Inter-Operation Parallelism 이 발생할 때는 사용자가 지정한 병렬도(= 2) 의 배수(4 개)만큼 서버 프로세스가 펼요함.
- 또한 테이블 큐(:TQ10001) 에는 병렬도의 제곱(2*2=4)만큼 파이프 라인이 펼요하다는 사실도 알 수 있음
- 참고로 그림 <1II-5-15>를 보면, 병렬도가 4이므로 8(=4 X 2) 개 서버 프로세스를 위해 16(=4*4) 개의 파이프 라인이 형성됨
1) 생산자 / 소비자 모델
- 테이블 큐에는 항상 생산자(Producer)와 소비자(Consumer)가 존재함
- <그림 Ⅲ-5-16>을 보면, 처음 DEPT 테이블을 읽어 분배하는 :TQ10000에서는 QC가 생산자고 서버 집합 1이 소비자
- 두 번째 테이블 큐 :TQ10001에서는 서버 집합 1이 생산자가 되고, 서버 집합 2가 소비자
- 마지막으로, 정렬된 최종 결과집합을 전송하는 :TQ10002에서는 서버 집합 2가 생산자가 되고 QC가 소비자
- <그림 Ⅲ-5-16>에서 보듯 Inter-Operation Parallelism이 나타날 때, 소비자 서버 집합은 FROM절에 테이블 큐를 참조하는 SUB SQL을 가지고 작업을 수행함
2) 병혈 실행계획에서 생산자와 소비자 식별
- 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를 병렬로 처리할 때 사용 |
| HASH | 조인이나 HASH GROUP BY를 병렬로 처리할 때 사용 |
| BROADCAST | QC 또는 첫 번째 서버 집합에 속한 프로세스들이 각각 읽은 데이터를 두 번째 서버 집합에 속한 모든 병렬 프로세스에게 전송하는 방식 |
| KEY | 특정 칼럼을 기준으로 테이블 또는 인덱스를 파티셔닝할 때 사용하는 분배 방식 |
| ROUND-ROBIN | 파티션 키, 정렬 키, 해시 함수 등에 의존하지 않고 반대편 병렬 서버에 무작위로 데이터를 분배할 때 사용 |
바) pq_distribute 힌트 활용
1) pq_distribute 힌트의 용도
- 데이터 분배 방식을 결정할 수 있는 힌트
- 아래와 같은 경우에 사용 가능
- 옵티마이저가 파티션된 테이블을 적절히 활용하지 못하고 동적 재분할을 시도할 때
- 기존 파티션 키를 무시하고 다른 키 값으로 동적 재분할하고 싶을 때
- 통계정보가 부정확하거나 통계정보를 제공하기 어려운 상황에서 실행계획을 고정시키고자 할 때
- 기타 여러 가지 이유로 데이터 분배 방식을 변경하고자 할 때
- 병렬 쿼리는 '분할 & 정복(Divide & Conquer) 원리'에 기초하며, 그 중에서도 병렬 조인을 위해서는 '분배 & 조인(Distribute & Join) 원리'가 작동함을 이해하는 것이 매우 중요함.
- 이때, pq_distribute 힌트는 조인에 앞서 데이터를 분배(distribute) 과정에만 관여하는 힌트임을 인지해야 함
- 예를 들어, 아래 실행계획을 보면 테이블은 양쪽 모두 Hash 방식으로 분배했지만 조인은 소트 머지 조인 방식으로 수행한것처럼, 데이터를 재분배하기 위해 해시 함수를 사용하는 것일 뿐 조인 방식(method)과는 무관함
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 | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 812 | 5 (20)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10002 | 14 | 812 | 5 (20)| 00:00:01 | Q1,02 | P->S | QC (RAND) |
| 3 | MERGE JOIN | | 14 | 812 | 5 (20)| 00:00:01 | Q1,02 | PCWP | |
| 4 | SORT JOIN | | 4 | 80 | 2 (0)| 00:00:01 | Q1,02 | PCWP | |
| 5 | PX RECEIVE | | 4 | 80 | 2 (0)| 00:00:01 | Q1,02 | PCWP | |
| 6 | PX SEND HASH | :TQ10000 | 4 | 80 | 2 (0)| 00:00:01 | Q1,00 | P->P | HASH |
| 7 | PX BLOCK ITERATOR | | 4 | 80 | 2 (0)| 00:00:01 | Q1,00 | PCWC | |
| 8 | TABLE ACCESS FULL| DEPT | 4 | 80 | 2 (0)| 00:00:01 | Q1,00 | PCWP | |
|* 9 | SORT JOIN | | 14 | 532 | 3 (34)| 00:00:01 | Q1,02 | PCWP | |
| 10 | PX RECEIVE | | 14 | 532 | 2 (0)| 00:00:01 | Q1,02 | PCWP | |
| 11 | PX SEND HASH | :TQ10001 | 14 | 532 | 2 (0)| 00:00:01 | Q1,01 | P->P | HASH |
| 12 | PX BLOCK ITERATOR | | 14 | 532 | 2 (0)| 00:00:01 | Q1,01 | PCWC | |
| 13 | TABLE ACCESS FULL| EMP | 14 | 532 | 2 (0)| 00:00:01 | Q1,01 | PCWP | |
------------------------------------------------------------------------------------------------------------------
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을 수행 |
| PQ_DISTRIBUTE(INNER, BROADCAST, NONE) | OUTER 테이블을 BROADCAST 하라는 의미 |
| PQ_DISTRIBUTE(INNER, NONE, BROADCAST) | INNER 테이블을 BROADCAST 하라는 의미 |
3) pq_distribute 힌트를 이용한 튜닝 사례
INSERT /*+ APPEND */ INTO 상품기본이력 (...)
SELECT /*+ PARALLEL(A,32) PARALLEL(B,32) PARALLEL(C,32) PARALLEL(D,32) */
.....
FROM 상품기본이력임시 A,
상품 B,
코드상세 C,
상품상세 D
WHERE A.상품번호 = B.상품번호
AND .....
/
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 | | | | | P->S | |
| HASH JOIN | | 5248 | | | P->P | QC (RAND) |
| HASH JOIN OUTER | | 5248 | | | PCWP | BROADCAST |
| PARTITION HASH ALL | | | 1 | 128 | PCWP | |
| TABLE ACCESS FULL | 상품기본이력임시 | 5248 | 1 | 128 | P->P | BROADCAST |
| TABLE ACCESS FULL | 상품 | 7595K | | 80 | PCWP | |
| TABLE ACCESS FULL | 코드상세 | 26 | | 80 | P->P | BROADCAST |
| TABLE ACCESS FULL| DEPT | 상품상세 | 7772K | | 80 | PCWP | |
----------------------------=----------------------------------------------------------------
- 1시간 40분간 수행되던 SQL이 TEMP SEGMENT를 확장할 수 없다는 오류 메시지로 인해 중단됐고, 확인 결과 상품기본이력임시 테이블에 통계 정보가 없던 것이 원인
- 실제 천만건에 이르는 큰 테이블이었는데, 통계 정보가 없어 옵티마이저가 5,248건의 작은 테이블로 판단한 것을 볼 수 있음
- 이 큰 테이블블을 32개 병렬 서버에게 Broadcast하는 동안 과도한 프로세스 간 통신이 발생했고, 결국 TEMP TABLESPACE를 모두 소진하고서 멈춤
INSERT /*+ APPEND */ INTO 상품기본이력 (...)
SELECT /*+ ORDERED PARALLEL(A,16) PARALLEL(B,16) PARALLEL(C,16) PARALLEL(D,16)
PQ_DISTRIBUTE(B, NONE, PARTITION)
PQ_DISTRIBUTE(C, NONE, BROADCAST)
PQ_DISTRIBUTE(D, HASH, HASH) */
......
FROM 상품기본이력임시 A,
상품 B,
코드상세 C,
상품상세 D 7
WHERE A.상품번호 = B.상품번호 8
AND ...
/
정 과: 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 | | 80 | P->P | PART(KEY) |
| TABLE ACCESS FULL | 코드상세 | 26 | | 80 | P->P | BROADCAST |
| TABLE ACCESS FULL| DEPT | 상품상세 | 7772K | | 80 | P->P | HASH |
----------------------------=----------------------------------------------------------------
- pq_distribute 힌트를 이용해 데이터 분배 방식을 조정 이후 2분 29초 만에 작업을 완료함
사) 병렬 처리 시 주의사항
- 동시 사용자 수가 적은 애플리케이션 환경(야간 배치 프로그램, DW, OLAP 등)에서 직렬로 처리할 때보다 성능 개선 효과가 확실할 때 사용함(작은 테이블은 제외함)
- OLTP성 시스템 환경이더라도 작업을 빨리 완료함으로써 직렬로 처리할 때보다 오히려 전체적인 시스템 리소스(CPU, Memory 등) 사용률을 감소시킬 수 있을 때(수행 빈도가 낮아야 함)
문서에 대하여
- 최초작성자 : xsoft
- 최초작성일 : 2013년 11월 30일
- 이 문서는 오라클클럽 오라클 성능 트러블슈팅 스터디 모임에서 작성하였습니다.
- {*}이 문서의 내용은 한국 데이터베이스 진흥원에서 출간한 'SQL 전문가 가이드'를 참고하였습니다.*