4.6 병렬(Parallel Execution. PX )

• 10g, PSC(Parallel Single Cursor) 모델 도입,
• 말 그대로 Parallel Execution Coordinatior(이하 PEC) 와 Parallel Execution Slave(이하 PES)가 하나의 커서(또는 플랜)을 공유

-- PARALLEL SQL CHECK

SELECT LPAD(NVL(TO_CHAR(PS.SERVER#), 'Main'), 5, ' ') CLASS,
PS.SID || ', ' || PS.SERIAL# AS SID_SERIAL,
PS.QCSID,
PS.DEGREE,
SUBSTR(S.STATUS, 1, 1) AS STATUS,
P.SPID SPID,
DECODE(SUBSTR(S.ACTION, 1, 4),
'FRM:',
SUBSTR(S.MODULE, 1, 15) || '(Form)',
'Onli',
SUBSTR(S.MODULE, 1, 15) || '(Form)',
'Conc',
SUBSTR(S.MODULE, 1, 15) || '(Conc)',
SUBSTR(S.MODULE, 1, 20)) AS MODULE,
SUBSTR(S.PROGRAM, -6, 6) PROGRAM,
SUBSTR(SW.EVENT, 1, 30) WAIT,
LAST_CALL_ET LAST_CALL_ET,
(SELECT SUBSTR(SQL_TEXT, 1, 50)
FROM V$SQL SQ
WHERE SQ.ADDRESS = S.SQL_ADDRESS
AND SQ.HASH_VALUE = S.SQL_HASH_VALUE
AND ROWNUM = 1) SQL_TEXT,
NVL(PS.SERVER#, 0) AS SERVER_NUM
FROM V$SESSION S,
V$PROCESS P,
V$SESSION_WAIT SW,
V$PX_SESSION PS
WHERE S.PADDR = P.ADDR
AND SW.SID = S.SID
AND S.SID = PS.SID
AND S.SERIAL# = PS.SERIAL#
AND NOT EXISTS (SELECT 1
FROM V$BGPROCESS BP
WHERE P.ADDR = BP.PADDR)
ORDER BY PS.QCSID,
DEGREE NULLS FIRST,
SUBSTR(S.PROGRAM, -6, 6) NULLS FIRST,
CLASS ;

병렬 실행 관련 파라미터

• INSTANCE_GROUPS : 특정 인스턴스를 특정 그룹에 지정
• PARALLEL_INSTANCE_GROUP : 병렬 작업 수행 시 어떤 인스턴스를 그룹에 PES 를 배정할지 결정하는 역할

사용 예

-- INSTANCE 1
ALTER SYSTEM SET INSTANCE_GROUPS = SEOUL, BUSAN SCOPE = SPFILE ; 

-- INSTANCE 2
ALTER SYSTEM SET INSTACNE_GROUPS = BUSAN SCOPE = SPFILE ; 

-- DB RESTART 
-- 병렬 작업 시 PARALLE_INSTANCE_GROUP 지정 후 작업 

ALTER SESSION SET PARALLEL_INSTANCE_GROUP = SEOUL ; 
SELECT /+* PARALLEL(A) PARALLEL(B) */ COUNT(*) FROM BIG_SEOUL1 A, BIG_SEOUL2 B
WHERE A.ID = B.ID  ;


ALTER SESSION SET PARALLEL_INSTANCE_GROUP = BUSAN ; 
SELECT /+* PARALLEL(A) PARALLEL(B) */ COUNT(*) FROM BIG_SEOUL1 A, BIG_SEOUL2 B
WHERE A.ID = B.ID 

파티셔닝과 병렬 실행

• 병렬작업을 극대화 하기 위해서 파티션을 적극적으로 활용함
• 파티션으로 분할되어 있는 테이블에 대한 DML작업은 병렬 DML(Parallel DML, PDML) 이 가능

파티션 지향조인(Partition-wise Join)

• 파티션 별로 병렬로 조인 작업을 수행함으로써 대량 데이터에 대한 조인 성능을 최적화 하는 기능
• 조인 성능의 최적화를 위해 DOP 를 파티션 수로 제한함.
• 파티션 지향 조인 수행 시, DOP 를 파티션 수로 자동 결정.
• DOP 강제 지정 시, 파티션 지향 조인이 아닌 일반적인 병렬 조인 수행
• CPU_COUNT = PARTITION 갯수 ==> 파티션 지향 조인 유리,
  CPU_COUNT > PARTITION 갯수 ==> 일반적인 병렬 조인 유리

▶전체 파티션 지향 조인(Full Partition-wise Join)

• 조인 대상이 되는 두 개의 테이블이 동일한 파티션으로 구성
• 파티션 방식(해시,리스트/범위)과 동일한 파티션 수, 동일한 파티션 키를 갖는 경우를 동일한 파티션이라함
• 각 테이블의 파티션들이 1:1 매칭되어, 최적의 성능 발휘
• DOP(Degreee Of Parallelism)가 파티션 수로 제한 ( ex - 파티션 수가 3면, DOP 는 4 )

▶부분 파티션 지향 조인(Paritial Partition-wise Join )

• 조인 대상이 되는 테이블의 파티션 구성이 서로 다른 경우
  선두가 되는 테이블을 기준으로, 나머지 테이블을 가상의 파티션으로 나눈 후, 조인 수행

파티셔닝 예제 10gR2 에서는 다른 실행계획이 나옴

CREATE TABLE PX_TEST (ID NUMBER, NAME VARCHAR2(100))
PARTITION BY HASH(ID) PARTITIONS 4; 

CREATE TABLE PX_TEST2 (ID NUMBER, NAME VARCHAR2(100))
PARTITION BY HASH(ID) PARTITIONS 4 ; 

CREATE TABLE PX_TEST3 (ID NUMBER, NAME VARCHAR2(100)) ; 

exec dbms_stats.gather_table_stats(ownname => 'OPS$ORACLE', tabname=> 'PX_TEST', cascade => TRUE, estimate_percent => 15) ;
exec dbms_stats.gather_table_stats(ownname => 'OPS$ORACLE', tabname=> 'PX_TEST2', cascade => TRUE, estimate_percent => 15) ;
exec dbms_stats.gather_table_stats(ownname => 'OPS$ORACLE', tabname=> 'PX_TEST3', cascade => TRUE, estimate_percent=> 15) ; 

-- PX_TEST 테이블: 파티션수가 4인 해시 파티션
SQL> CREATE TABLE PX_TEST(ID NUMBER, NAME VARCHAR2(100))
 PARTITION BY HASH(ID) PARTITIONS 4;
-- PX_TEST2 테이블: 파티션수가 4인 해시 파티션
SQL> CREATE TABLE PX_TEST2(ID NUMBER, NAME VARCHAR2(100))
 PARTITION BY HASH(ID) PARTITIONS 4;
-- PX_TEST3 테이블: 파티션 없는 테이블
SQL> CREATE TABLE PX_TEST3(ID NUMBER, NAME VARCHAR2(100));

Case1: 전체 파티션 지향 조인. PX_TEST 테이블과 PX_TEST2 테이블

SELECT /*+ PARALLEL(A) PARALLEL(B) */ COUNT(*)
FROM PX_TEST A, PX_TEST2 B
WHERE A.ID = B.ID

--->  두 테이블은 파티션 구성이 동일하므로 전체 파티션 지향 조인으로 풀린다.
 SELECT STATEMENT ALL_ROWS-Cost : 33882
  SORT AGGREGATE 
   PX COORDINATOR  
    PX SEND QC (RANDOM) 
     SORT AGGREGATE  
      PX PARTITION HASH ALL  <---  전체 파티션 지향 조인을 의미
       HASH JOIN   
        TABLE ACCESS FULL OWI.PX_TEST2(2) 
        TABLE ACCESS FULL OWI.PX_TEST(1) 

Case 2: 부분 파티션 지향 조인. PX_TEST 테이블과 PX_TEST3 테이블

SELECT /*+ PARALLEL(A) PARALLEL(B) */ COUNT(*)
FROM PX_TEST A, PX_TEST3 B
WHERE A.ID = B.ID

--->  두 테이블은 파티션 구성이 다르므로 부분 파티션 지향 조인으로 풀린다.
 SELECT STATEMENT ALL_ROWS-Cost : 41720
  SORT AGGREGATE 
   PX COORDINATOR  
    PX SEND QC (RANDOM) 
     SORT AGGREGATE  
      HASH JOIN   
       PX RECEIVE   
        PX SEND PARTITION (KEY)  <---  PX_TEST3는 파티션이 없으므로 논리적인 파티션 생성
         PX BLOCK ITERATOR  
          TABLE ACCESS FULL OWI.PX_TEST3(2) 
       PX PARTITION HASH ALL  <---  PX_TEST는 파티션이 이루어져 있으므로 파티션 스캔
        TABLE ACCESS FULL OWI.PX_TEST(1) 

Case 3: 일반 조인. PX_TEST3 테이블과 PX_TEST3 테이블(셀프 조인)

SELECT /*+ PARALLEL(A) PARALLEL(B) */ COUNT(*)
FROM PX_TEST3 A, PX_TEST3 B
WHERE A.ID = B.ID

---> 두 테이블은 파티션이 없으므로 일반 조인으로 풀린다.
 SELECT STATEMENT ALL_ROWS-Cost : 40564
  SORT AGGREGATE 
   PX COORDINATOR  
    PX SEND QC (RANDOM) 
     SORT AGGREGATE  
      HASH JOIN   
       PX RECEIVE   
        PX SEND  <---  PX_TEST3는 파티션이 이루어져 있지 않으므로 단순 병렬 스캔
         PX BLOCK ITERATOR  
          TABLE ACCESS FULL OWI.PX_TEST3(1) 
       PX RECEIVE   
        PX SEND  <---  PX_TEST3는 파티션이 이루어져 있지 않으므로 단순 병렬 스캔
         PX BLOCK ITERATOR  
          TABLE ACCESS FULL OWI.PX_TEST3(2)

RAC 와 피티셔닝

• 파티션 - 세그먼트 분할
• 클러스터 - 리소스(CPU,메모리) 분할
• 병렬 수행 - 최대한 노드 간에 통신이 발생하지 않는 구조로 PES 을 분배
  

병렬 실행과 관련된 대기 이벤트

• PX Deq: Parse Reply
  : PEC 가 PES 에게 파싱 요청을 한 후 응답이 올 때까지 대기하는 이벤트
  10G 에서 도입된 PSC(Parallel Single Cursor) 모델에서는 PEC가 생성한 커서를 공유하기 때문에 이러한 과정은 생략된다.
  단. RAC 에서는 여전히 PEC 와 다른 노드에 존재하는 PES는 PEC가 생성한 SQL문을 파싱하는 역할을 수행

• PX Deq: Execute Reply
  : PEC가 가장 보편적으로 대기하는 이벤트, PES의 실제 작업이 끝나기를 기다리는 대기이벤트이다.
  즉 PEC가 PES가 작업을 끝낸 후 데이터를 보내주기를 기다리는 동안 이 이벤트를 대기 한다.

• PX Deq Credit : need buffer
  : PEC / PES 간, PES / PES 간의 통신은, 프로세스 간 존재하는 테이블 큐(Table Q)를 통해 이루어진다.
  가령 PES 가 테이블 큐에 데이터를 집어넣으면, PEC 가 테이블 큐에서 그 데이터를 빼가는 형식이다.
  오라클은 두 프로세스 중 한 순간에 오직 하나의 프로세스만이 테이블 큐에 데이터를 집어넣을 수 있도록 보장한다.
  테이블 큐에 데이터를 집어넣을 수 있는 자격을 확보할 때까지 기다리는 이벤트다.

• PX Deq: Execution Msg
  : PES 에게 가장 보편적인 대기 이벤트, PES 가 어떤 작업을 수행하기 위한 메시지를 기다리는 이벤트
  병렬 실행에 관계 하는 각 PES들은 특정 작업이 자신에게 할당될 때까지 기다려야 하며,
  그 동안 PX Deq: Execution Msg 이벤트를 대기한다.

• PX Deq: Table Q Normal
  : PES 가 테이블 큐에 데이터가 들어오기를 기다리는 이벤트
  PES 가 다른 PES 로부터 데이터를 받아서 작업을 수행해야 하는 경우에 보편적으로 발생하는 이벤트
  생산자/소비자(Producer/Consumer)
  SELECT /*+ PARALLEL ... */ FROM TABLE A ORDER BY NAME 과 같은 형태(정렬작업 필요한)의
  병렬작업을 수행하면 테이블로부터 데이터를 페치 하는 생산자 PES 와
  페치된 데이터를 받아서 소비(ORDER BY) 하는 소비자 PES 가 협력하는 방식으로 작동

• direct path read
  : 버퍼 캐시를 경유하지 않고 데이터 파일로부터 직접 데이터를 읽는 과정에서 발생하는 이벤트
  PES 가 테이블로부터 데이터를 페치하는 작업은 대부분 데이터 파일에서 직접 데이터를 읽는 방식을 사용한다.

• enq: TC Contention
  : PES 가 Direct Path I/O를 수행하려면, 해당 테이블에 대한 체크 포인트(Checkpoint)작업이 선행 되어야 한다.
  버퍼 캐시의 더티 버퍼가 모두 데이타 파일에 기록되어야 버퍼 캐시를 경유하지 않고 데이터 파일에서
  직접 데이터를 읽을 수 있기 때문이다.
  PEC는 PES 에게 작업을 지시하기 전에 체크포인트 요청을 하고 작업이 끝날 때 까지 기다려야 하며
  그 동안 enq: TC Contention 이벤트 대기