by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

4.6 병렬 실행(Parallel Execution, PX)




4.6 병렬(Parallel Execution. PX )

  • 10g, PSC(Parallel Single Cursor) 모델 도입,
  • 말 그대로 Parallel Execution Coordinatior(이하 PEC) 와 Parallel Execution Slave(이하 PES)가 하나의 커서(또는 플랜)을 공유
Parallel 9i VS 10g
  • 9i - PEC 가 병렬 작업을 위해서, 특별한 형태의 SQL 문장을 PES 가 실행하도록 하는 형식, 과정이 복잡한 단점
  • 10g - PEC 가 생성한 커서(또는 플랜)을 모든 PES 가 공유함, 각 PES 가 실행 계획의 특정 부분만 수행하도록
    PEC 가 추가적인 메시지를 통해 제어 하는 것. 모니터링및 관리가 쉬운 장점

-- 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 ;
인스턴스내 병렬화 VS 인스턴스간 병렬화
  • 인스턴스내 병렬화(Intra-Instance Parallelism)
    : 병렬 프로세스간의 통신은 공유 메모리를 통해서, 기본적으로 하나의 인스턴스 내에서 병렬화 정책
  • 인스턴스간 병렬화(Inter-Insance Parallelism)
    : 병렬 프로세스간의 통신은 인터커넥트를 통해서, 인스턴스내 병렬화로 처리 힘들경우 병렬화 정책

병렬 실행 관련 파라미터

  • 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 이벤트 대기

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.
  1. 4월 26, 2011

    장태길 says:

    간단히 힌트를 통해서 책과 동일한 PLAN 생성 가능 CASE 1) SELECT /*+ FULL(A) FULL(B) PARALLEL(A 4) P...

    간단히 힌트를 통해서 책과 동일한 PLAN 생성 가능
    CASE 1)
    SELECT /*+ FULL(A) FULL(B) PARALLEL(A 4) PARALLEL(B 4) */
    COUNT
    FROM PX_TEST A, PX_TEST2 B
    WHERE A.ID = B.ID ;

    Execution Plan
    --------------------------------------------------------------------------------
    0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=185 Card=1 Bytes=10)
    1 0 SORT (AGGREGATE) (Card=1 Bytes=10)
    2 1 PX COORDINATOR
    3 2 PX SEND (QC (RANDOM)) OF 'SYS.:TQ10000' (Card=1 Bytes=10) (PARALLEL_TO_SERIAL) (QC (RANDOM))
    4 3 SORT (AGGREGATE) (Card=1 Bytes=10) (PARALLEL_COMBINED_WITH_PARENT)
    5 4 PX PARTITION HASH (ALL) (Cost=185 Card=400K Bytes=4M) (PARALLEL_COMBINED_WITH_CHILD)
    6 5 HASH JOIN (Cost=185 Card=400K Bytes=4M) (PARALLEL_COMBINED_WITH_PARENT)
    7 6 TABLE ACCESS (FULL) OF 'PX_TEST' (TABLE) (Cost=92 Card=400K Bytes=2M) (PARALLEL_COMBINED_WITH_PARENT)
    8 6 TABLE ACCESS (FULL) OF 'PX_TEST2' (TABLE) (Cost=92 Card=402K Bytes=2M) (PARALLEL_COMBINED_WITH_PARENT)

    CASE 2)

    SELECT /*+ ORDERED FULL(A) FULL(B) PARALLEL(A 4) PARALLEL(B 4) PQ_DISTRIBUTE(A PARTITION, NONE) */
    COUNT
    FROM PX_TEST3 B, PX_TEST A
    WHERE A.ID = B.ID

    Execution Plan
    --------------------------------------------------------------------------------
    0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=156 Card=1 Bytes=10)
    1 0 SORT (AGGREGATE) (Card=1 Bytes=10)
    2 1 PX COORDINATOR
    3 2 PX SEND (QC (RANDOM)) OF 'SYS.:TQ10001' (Card=1 Bytes=10) (PARALLEL_TO_SERIAL) (QC (RANDOM))
    4 3 SORT (AGGREGATE) (Card=1 Bytes=10) (PARALLEL_COMBINED_WITH_PARENT)
    5 4 HASH JOIN (Cost=156 Card=400K Bytes=4M) (PARALLEL_COMBINED_WITH_PARENT)
    6 5 PX RECEIVE (Cost=62 Card=401K Bytes=2M) (PARALLEL_COMBINED_WITH_PARENT)
    7 6 PX SEND (PARTITION (KEY)) OF 'SYS.:TQ10000' (Cost=62 Card=401K Bytes=2M) (PARALLEL_TO_PARALLEL) (PARTITION (KEY))
    8 7 PX BLOCK (ITERATOR) (Cost=62 Card=401K Bytes=2M) (PARALLEL_COMBINED_WITH_CHILD)
    9 8 TABLE ACCESS (FULL) OF 'PX_TEST3' (TABLE) (Cost=62 Card=401K Bytes=2M) (PARALLEL_COMBINED_WITH_PARENT)
    10 5 PX PARTITION HASH (ALL) (Cost=92 Card=400K Bytes=2M) (PARALLEL_COMBINED_WITH_CHILD)
    11 10 TABLE ACCESS (FULL) OF 'PX_TEST' (TABLE) (Cost=92 Card=400K Bytes=2M) (PARALLEL_COMBINED_WITH_PARENT)

    CASE 3)

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

    Execution Plan
    --------------------------------------------------------------------------------
    0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=13 Card=1 Bytes=10)
    1 0 SORT (AGGREGATE) (Card=1 Bytes=10)
    2 1 PX COORDINATOR
    3 2 PX SEND (QC (RANDOM)) OF 'SYS.:TQ10002' (Card=1 Bytes=10) (PARALLEL_TO_SERIAL) (QC (RANDOM))
    4 3 SORT (AGGREGATE) (Card=1 Bytes=10) (PARALLEL_COMBINED_WITH_PARENT)
    5 4 HASH JOIN (Cost=13 Card=401K Bytes=4M) (PARALLEL_COMBINED_WITH_PARENT)
    6 5 PX RECEIVE (Cost=6 Card=401K Bytes=2M) (PARALLEL_COMBINED_WITH_PARENT)
    7 6 PX SEND (HASH) OF 'SYS.:TQ10000' (Cost=6 Card=401K Bytes=2M) (PARALLEL_TO_PARALLEL) (HASH)
    8 7 PX BLOCK (ITERATOR) (Cost=6 Card=401K Bytes=2M) (PARALLEL_COMBINED_WITH_CHILD)
    9 8 TABLE ACCESS (FULL) OF 'PX_TEST3' (TABLE) (Cost=6 Card=401K Bytes=2M) (PARALLEL_COMBINED_WITH_PARENT)
    10 5 PX RECEIVE (Cost=6 Card=401K Bytes=2M) (PARALLEL_COMBINED_WITH_PARENT)
    11 10 PX SEND (HASH) OF 'SYS.:TQ10001' (Cost=6 Card=401K Bytes=2M) (PARALLEL_TO_PARALLEL) (HASH)
    12 11 PX BLOCK (ITERATOR) (Cost=6 Card=401K Bytes=2M) (PARALLEL_COMBINED_WITH_CHILD)
    13 12 TABLE ACCESS (FULL) OF 'PX_TEST3' (TABLE) (Cost=6 Card=401K Bytes=2M) (PARALLEL_COMBINED_WITH_PARENT)