01 기본개념

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

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

Query Coordinator(QC): 병렬 SQL문을 발행한 세션
병렬 서브 프로세스 : 실제 작업을 수행하는 개별 세션
QC의 역할

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

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


SELECT /*+ full(고객) parallel(고객 4) */ *
  FROM 고객
 ORDER BY 고객명
;

첫번째 : 8명이 각자 자신의 것을 정렬하고 나면 영업팀장이 이를 최종적으로 머지하는 것. 작업자가 많을수록 나중에 영업팀장(QC)이 머지하는 단계에 부하가 걸려 병렬처리 극대화 어려움
두번째 : 두개의 조로 나누고 역할을 분담해서로 다른 작업을 동시에 진행하는것. 즉, 4명은 명함을 분배하고 다른 4명을 그것을 받아 정렬하는 역할. order by를 병렬로 처리할 때 사용하는 방식
- Intra-Orperation Parallelism
  1. 서로 배타적인 범위를 독립적으로 동시에 처리하는것
  2. 한 병렬 서버 집합(Server set)에 속한 여러 프로세스가 처리 범위를 달리하면서 병렬로 작업을 진행하는것
  3. 집합 내에서는 절대 프로세스 간 통신이 발생하지 않음
- Inter-Operation Parallelism
  1. 명함을 읽어 상대편 조(다른 서버집합)에 분배하거나 정렬된 결과를 영업팀장(QC)에게 전송하는 작업을 병렬고 동시에 진행하는 것
  2. 항상 프로세스 간 통신 발생 메세지 또는 데이터를 전송하기 위한 통신 채널 필요

(3) 테이블 큐

테이블 큐 (Table Que) : 쿼리 서버 집합 간(P->P) 또는 QC와 쿼리 서버 집합 간(P->S,S->P) 데이터 전송을 우해 연결된 파이프 라인
테이블 큐 식별자
생산자 / 소비자 모델

(4) IN-OUT 오퍼레이션

h5.S -> P : PARALLEL_FROM_SERIAL

QC가 읽은 데이터를 테이블 큐를 통해 병렬 서버 프로세스에게 전송하는 것

h5.P -> S : PARALLEL_TO_SERIAL

각 병렬 서버 프로세스가 처리한 데이터를 QC에게 전송하는것
병렬 프로세스로부터 QC로 통신이 발생하으로 Inter-Operation Parallelism
QC (ORDER) : QC에게 결과 데이터를 전송할 때 첫 번째 병렬 프로세스로부터 마지막 병렬 프로세스까지 순서대로 진행함을 의미, SQL이 order by 절을 포함할 떄 나타남

P -> P: PARALLEL_TO_SERIAL

데이터를 재분배하는 오퍼레이션,해당 오퍼레이션을 두개의 서버 집합이 처리, 사용자가 지정한 병렬도의 2배수만큼 병렬 프로세스가 필요
데이터를 정렬 또는 그룹핑하거나 조인을 위해 동적으로 파티셔닝할 때 사용됨
첫벙째 병렬 서버 집합이 읽거나 가공한 데이터를 두번째 병렬 서버 집합에 전송하는 과정에서 병렬 프로세스간 통신이 발생하므로 Inter-Opreation Parallelism

PCWP : PARALLEL_COMBINED_WITH_RARENT

한 서버 집합이 현재 스텝과 그 부모(Prent) 스텝을 모두 처리함을 의미
Intra-Operation Parallelism
한 서버 집합에 속한 서버 프로세스들이 각자 맡은 범위 내에서 두 스텝 이상의 오퍼레이션을 처리, 자식 스텝의 처리 결과를 부모 스텝에서 사용할 뿐 프로세스 간 통신은 필요치 않음

PCWC : PARALLEL_COMBINED_WITH_CHILD

한 서버 집합이 현재 스텝과 그 자식(Child) 스텝을 모두 처리함을 의미
Intra-Operation Parallelism
자식 스텝의 처리 결과를 받아 현재 스텝의 입력 값으로 사용할 뿐이며, 프로세스 간 통신은 필요치 않음

in-out 오퍼레이션

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

(5) 데이터 재분배

RANGE

order By 또는 Group By 를 병렬로 처리할 때 사용
정렬작업을 맡은 두번째 서버집합의 프로세스마다 처리범위를 지정하고 나서 데이터를 읽는 첫번째 서버집합이 정렬키 값에 따라 정해진 범위에 해당하는 두번째 프로세스에게 분배하는 방식
QC는 작업범위를 할당하며, 정렬작업에는 참여하지 않는다.
정렬결과를 순서대로 받아서 사용자에게 전송하는 역할만 담당

HASH

조인이나 Hash Group By 를 병렬로 처리할 때 사용
조인 키나 Group By 키값을 해시함수에 적용하여 리턴되는 값에 따라 데이터를 분배
P->P 뿐만 아니라 S->P 방식으로 이루어 질수도 있다.

BROADCAST

QC 또는 첫번째 서버집합의 프로세스들이 각각 읽은 데이터를 두번째 서버집합의 "모든" 병렬프로세스에게 전송하는 방식
병렬 조인에서 크기가 매우 작은 테이블이 있을 때 사용되며 P->P 뿐만 아니라 S->P 방식으로 이루어 진다.
작은 테이블은 병렬로 읽지 않을 때가 많으므로 오히려 S->P가 일반적임

KEY

특정 컬럼(들)을 기준으로 테이블 또는 인덱스를 파티셔닝할때 사용하는 분배 방식
실행계획에는 'PARTITION (KEY)'로 표시된다.(줄여서 'PART (KEY)').

ROUND-ROBIN

파티션키, 정렬키, 해시함수에 의존하지 않고 반대편 정렬 서버에 무작위로 데이터 분배
골고루 분배되도록 ROUND-ROBIN 방식 사용

(6) Granule

Granule : 데이터를 병렬로 처리할 때 일의 최소 단위

블록 기반 Granule : 파티션 테이블 여부와 상관없이 대부분의 병렬 오퍼레이션에 적용되는 기본 작업단위

실행계획상의 PX BLOCK ITERATOR
Granule의 크기와 총갯수는 실행시점에서 오브?트 사이즈와 병렬도에 따라 QC가 동적으로 정한다.

파티션 기반 Granule(= 파티션 Granule)

각 병렬 프로세스는 하나의 파티션 전체를 책임진다.(프로세스 하나가 파티션 두개 처리 불가)
실행계획상의 PX PARTITION RANGE ALL 또는 PX PARTITION RANGE ITERATOR
Granule의 갯수가 테이블과 인덱스의 파티션 구조에 의해 정적으로 결정되므로 유영성이 적다.
병렬도보다 파티션 갯수가 상당히(대략 병렬도늬 3배 이상) 많을 때라야 유용하다
파티션 수보다 많은 병렬도를 지정할 수 없다.

(7) 병렬 처리 과정에서 발생하는 대기 이벤트

병렬 처리 과정에서 자주 발생하는 대기 이벤트 요약

이벤트명	클래스	설명
PX Deq: Execute Reply	Idle	QC가 각 병렬 서버에게 작업을 배분하고서 작업이 완료되기를 기다리는 상태
PX Deq: Execute Msg	Idle	병렬 서버가 자신의 임무를 완수하고서 다른 병렬 서버가 일을 마치기를 기다리는 상태 QC 또는 소비자 병렬 서버에게 데이터 전송을 완료했을 때 나타남
PX Deq: Table Q Normal	Idle	메세지 수신 대기. 메세지 큐에 데이터가 쌓이기를 기다리는 상태
PX Deq Credit: send blkd	Other	메세지 송신 대기. QC 또는 소비자 병렬 서버에게 전송할 데이터가 있는데 블로킹 된 상태 생산자 프로세스가 메세지 큐를 통해 데이터를 전송하려고 하는데 어떤 이유에서건 소비자 프로세스가 버퍼에서 데이터를 빨리 꺼내가지 않을 때 발생
PX Deq Credit: need bulffer	Idle	데이터를 전송하기 전에 상대편 병렬 서버 또는 QC로부터 credit 비트를 얻으려고 대기하는 상태

오라클 측의 설명(메타링크 문서번호 271767.1)에 의하면 'PX Deq Credit:send blkd'와 'PX Deq Credit: need bulffer'는 거의 같은 대기 이벤트이고,
내부적인 이유로 전자는 로컬 시스템에서 자주 발생하며, 후자는 RAC 시스템에서 자주 발생함

(1) 대기 이벤트 모니터링


-- SESSION_1 수행
SELECT /*+ ORDERED USE_HASH(D) FULL(D) FULL(E) NOPARALLEL(D) PARALLEL(E 4) */
       COUNT(*),
       MIN(E.SAL),
       MAX(E.SAL),
       AVG(E.SAL),
       SUM(E.SAL)
FROM   DEPT  D,
       T_EMP E
WHERE  D.LOC    = 'CHICAGO'
AND    E.DEPTNO = D.DEPTNO
;-- SESSION_2 수행
SELECT DECODE(A.QCSERIAL#, NULL, 'PAREMT', 'CHILD') ST_LVL,
       A.SERVER_SET "SET",
       A.SID,
       A.SERIAL#,
       STATUS,
       EVENT,
       WAIT_CLASS
FROM   V$PX_SESSION   A,
       V$SESSION      B
WHERE  A.SID     = B.SID
AND    A.SERIAL# = B.SERIAL#
ORDER  BY A.QCSID,
          ST_LVL DESC,
          A.SERVER_GROUP,
          A.SERVER_SET
;ST_LVL SET  SID SERIAL# STATUS   EVENT                 WAIT_CLASS
------ --- ---- ------- -------- --------------------- ----------
PAREMT     7634    6110 ACTIVE   PX Deq: Execute Reply Idle
CHILD    1 7647     574 ACTIVE   direct path read      User I/O
CHILD    1 7860    1083 ACTIVE   direct path read      User I/O
CHILD    1 7933   11436 ACTIVE   direct path read      User I/O
CHILD    1 7938    4284 ACTIVE   direct path read      User I/O
CHILD    1 7872    2783 INACTIVE PX Deq: Execution Msg Idle
CHILD    1 7801    2574 INACTIVE PX Deq: Execution Msg Idle

작업을 처음 시작하면 QC에는 'PX Deq: Execute Reply'이벤트가 보이며, 가장 먼저 작업을 끝낸 병렬 서버로부터 데이터를 받아 클라이언트에게 전송할때는 'SQL*Net Message from client'로 변경됨
병렬서버에서 'PX Deq: Execution Msg' 메세지는 작업을 다 마친 의미이며, 'direct path read'는 작업이 수행중인 의미임.

2) 대기 이벤트 해소

v$event_name 뷰를 조회해 보면 병렬처리와 관련된 대기 이벤트가 대부분 idle로 분류돼 있음. 이유는, 이들 이벤트를 회피하기 위해 사용자가 할 수 있는 일들이 거의 없기 때문.


insert /*+ append */ into t1
select /*+ full(t2) parallel(t2 4) */ * from t2

여기서 t2 테이블은 병렬로 읽지만 t1으로의 insert는 직렬로 수행되고 있음
이럴 때 t2를 읽어 QC에게 전송하는 4개의 병렬 서버들은 PX Deq Credit: send blkd 이벤트에서 자주 대기하게 됨.
insert 과정에서 병목(direct path write 대기 이벤트 발생)이 생기므로 select 문을 병렬로 수행하는 것은 불필요할 수 있음.
select 문을 직렬로 처리하면 PX Deq Credit: send blkd 이벤트는 사라짐.
병목을 해소함으로써 속도를 향상시키고 싶다면, 아래와 같이 t1으로의 insert도 병렬로 수행되게끔 함.


alter session enable parallel dml;
insert /*+ append parallel(t1 4) */ into t1
select /*+ full(t2) parallel(t2 4) */ * from t2;

insert를 담당한 1번 병렬 서버 집합은 아래에서 보듯 Direct Path Write를 수행하거나 2번 서버 집합으로부터 메시지 수신을 위해 대기(PX Deq: Table Q Normal 대기 이벤트)하고 있음.


ST_LVL   SET  SID   SERIAL# STATUS   EVENT                     WAIT_CLASS
------ --- ---- ------- -------- ----------------------------------------
PARENT  QC    2643    46632  ACTIVE    PX Deq: Execute Reply      Idle
CHILD   1     2778     22712  ACTIVE   direct path write          User I/O
CHILD   1     2753    37169   ACTIVE   direct path write          User I/O
CHILD   1     3096     52396  ACTIVE   PX Deq: Table Q Normal    User I/O
CHILD   1     2483     6508    ACTIVE   direct path write           User I/O
CHILD   1     2512     21611   ACTIVE  direct path read            Idle
CHILD   1     2822     38188   ACTIVE  PX Deq Credit: send blkd     Idle
CHILD   1     2492     18064   ACTIVE  direct path read               Idle
CHILD   1     3186     29287   ACTIVE  PX Deq Credit: send blkd     Idle

select를 담당한 2번 병렬 서버 집합은 Direct Path Read를 수행하거나 1번 서버 집합으로 메시지를 송신하기 위해 대기(PX Deq Credit: send blkd) 하고 있음.

02 병렬 Order By와 Group By

(1)병렬 Order By

테스트 데이터 생성
{CODE:SQL}
SQL> CREATE TABLE 고객
2 AS
3 SELECT ROWNUM 고객ID
4 , DBMS_RANDOM.STRING('U', 10) 고객명
5 , MOD(ROWNUM, 10) + 1 고객등급
6 , TO_CHAR(TO_DATE('20090101', 'yyyymmdd') + (ROWNUM-1), 'yyyymmdd') 가입일
7 FROM dual
8 CONNECT BY LEVEL <= 1000
9 ;

테이블이 생성되었습니다.

SQL> exec dbms_stats.gather_table_stats(user, '고객');

PL/SQL 처리가 정상적으로 완료되었습니다.
{CODE}

고객테이블을 병렬로 읽어 고객명 순으로 정렬
{CODE:SQL}
SQL> set autot traceonly exp
SQL> SELECT /*+ full(고객) parallel(고객 2) */
2 고객ID, 고객명, 고객등급
3 FROM 고객
4 ORDER BY 고객명
5 ;
{CODE}

병렬쿼리 수행속도가 예상만큼 빠르지 않다면 테이블 큐를 통한 데이터 전송량에 편차가 크지 않은지 확인해 볼 필요가 있음

(2)병렬 Group By

order by와 group by를 병렬로 처리하는 내부 수행 원리는 기본적으로 같음
Group By의 방식(Hash/Sort)에 따라 분배과정에서 차이가 남

v$pq_tqstat 쿼리로 테이블 큐를 통한 전송 통계 확인 : Sort Group By 쿼리 기준
{CODE:SQL}
SQL> -- Sort Group By 쿼리 실행--
SQL> /

...

1000 개의 행이 선택되었습니다.

SQL>
SQL> break on dfo_no on tq_id on server_type
SQL> SELECT tq_id
2 , server_type
3 , process
4 , num_rows
5 , bytes
6 , waits
7 FROM v$pq_tqstat
8 ORDER BY dfo_number
9 , tq_id
10 , DECODE(SUBSTR(server_type, 1, 4), 'Rang', 1, 'Prod', 2, 'Cons', 3)
11 , process
12 ;

TQ_ID SERVER_TYPE PROCESS NUM_ROWS BYTES WAITS

--

--
0 Ranger QC 182 4494 1
Producer P002 548 6732 5
P003 452 5580 3
Consumer P000 475 5823 5
P001 525 6423 5
1 Producer P000 475 7720 0
P001 525 8520 0
Consumer QC 1000 16240 1

8 개의 행이 선택되었습니다.
{CODE}

Sort Group By 의 실행계획 및 테이블 큐를 통한 전송 통계는 Hash Group By 의 결과와 다름.
Sort Group By 의 실행계획 및 테이블 큐를 통한 전송 통계는 Sort Order By 의 결과와 같음.

Group By가 두 번 나타날 때의 처리 과정

Group By 고객등급
{CODE:SQL}
SQL> set autot traceonly exp
SQL> SELECT /*+ full(고객) parallel(고객 2) */
2 고객등급, COUNT(*) cnt
3 FROM 고객
4 GROUP BY 고객등급
5 ;

Execution Plan

--
Plan hash value: 406695692

--

Id

Operation

Name

Rows

Bytes

Cost (%CPU)

Time

TQ

IN-OUT

PQ Distrib

--

0	SELECT STATEMENT		10	30	3 (34)	00:00:01
1	PX COORDINATOR
2	PX SEND QC (RANDOM)	:TQ10001	10	30	3 (34)	00:00:01	Q1,01	P->S	QC (RAND)
3	HASH GROUP BY		10	30	3 (34)	00:00:01	Q1,01	PCWP
4	PX RECEIVE		10	30	3 (34)	00:00:01	Q1,01	PCWP
5	PX SEND HASH	:TQ10000	10	30	3 (34)	00:00:01	Q1,00	P->P	HASH
6	HASH GROUP BY		10	30	3 (34)	00:00:01	Q1,00	PCWP
7	PX BLOCK ITERATOR		1000	3000	2 (0)	00:00:01	Q1,00	PCWC
8	TABLE ACCESS FULL	고객	1000	3000	2 (0)	00:00:01	Q1,00	PCWP

--
{CODE}

v$pq_tqstat 쿼리로 테이블 큐를 통한 전송 통계 확인
{CODE:SQL}
SQL> set autot off
SQL> /

고객등급 CNT

--

--
6 100
5 100
1 100
3 100
4 100
7 100
8 100
2 100
9 100
10 100

10 개의 행이 선택되었습니다.

SQL> break on dfo_no on tq_id on server_type
SQL> SELECT tq_id
2 , server_type
3 , process
4 , num_rows
5 , bytes
6 , waits
7 FROM v$pq_tqstat
8 ORDER BY dfo_number
9 , tq_id
10 , DECODE(SUBSTR(server_type, 1, 4), 'Rang', 1, 'Prod', 2, 'Cons', 3)
11 , process
12 ;