1. HOME
  2. [종료]대용량 데이터베이스 스터디
  3. 2010년 하반기 대용량데이터베이스 스터디
  4. 3. 병렬 조인

3. 병렬 조인 (by ljh1148) [2010.12.07]

참조 문서 : http://scidb.tistory.com/entry/Parallel-Query-의-조인시-Row-Distribution

병렬 조인

병렬 조인 메커니즘을 이해하는 핵심 원리?

  • 병렬 프로세스들이 서로 독립적으로 조인을 수행 할 수 있도록 데이터를 분배하느데 있다.
    분배작업이 완료되고 나면 프로세스 간에 서로 방해 받지 않고 각자 할당받은 범위 내에서 조인을 완료한다.

병렬 조인 방식

  • 1. 파티션 방식 : Partition-Pair끼리 조인 수행
  • 2. Broadcast 방식 : 한쪽 테이블을 Broadcast하고 나서 조인 수행( -> 파티셔닝 불필요 )

1번 파티션 방식은 조인되는 두 테이블의 파티션 상태에 따라 아래 세 가지 경우로 나뉜다.

  • 1-1 둘 다 같은 기준으로 파티셔닝된 경우
  • 1-2 둘 중 하나만 파티셔닝된 경우
  • 1-3 둘 다 파티셔닝되지 않은 경우(??)

(1) 둘 다 같은 기준으로 파티셔닝 된 경우 - Full Partition Wise 조인

TEST 스크립트 준비
{CODE:SQL}

SQL> select * from v$version where rownum <= 1
2 ;

BANNER


Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi

SQL>

SQL> CREATE TABLE EMP_PARTITION2(
2 EMPNO NUMBER NOT NULL,
3 ENAME VARCHAR2(10),
4 JOB VARCHAR2(9),
5 MGR NUMBER(4),
6 HIREDATE DATE,
7 SAL NUMBER(7, 2),
8 COMM NUMBER(7, 2),
9 DEPTNO NUMBER(2))
10 PARTITION BY RANGE (DEPTNO) (
11 PARTITION P1 VALUES LESS THAN( 20 )
12 , PARTITION P2 VALUES LESS THAN( 30 )
13 , PARTITION P3 VALUES LESS THAN( 40 )
14 , PARTITION P4 VALUES LESS THAN( 50 )
15 );

SQL> SELECT LEVEL AS EMPNO
2 , 'TEST' AS ENAME
3 , 'JOB' AS JOB
4 , 1000 AS MGR
5 , SYSDATE AS HIREDATE
6 , FLOOR( DBMS_RANDOM.VALUE ) * 10000 AS SAL
7 , FLOOR( DBMS_RANDOM.VALUE ) * 100 AS COMM
8 , MOD( LEVEL, 4 ) * 10 + 10 AS DEPTNO
9 FROM DUAL
10 CONNECT BY LEVEL <= 10;

EMPNO ENAM JOB MGR HIREDATE SAL COMM DEPTNO

--
---
--
--
--
--
1 TEST JOB 1000 10/12/07 0 0 20
2 TEST JOB 1000 10/12/07 0 0 30
3 TEST JOB 1000 10/12/07 0 0 40
4 TEST JOB 1000 10/12/07 0 0 10
5 TEST JOB 1000 10/12/07 0 0 20
6 TEST JOB 1000 10/12/07 0 0 30
7 TEST JOB 1000 10/12/07 0 0 40
8 TEST JOB 1000 10/12/07 0 0 10
9 TEST JOB 1000 10/12/07 0 0 20
10 TEST JOB 1000 10/12/07 0 0 30

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

SQL> INSERT INTO EMP_PARTITION2
2 SELECT LEVEL AS EMPNO
3 , 'TEST' AS ENAME
4 , 'JOB' AS JOB
5 , 1000 AS MGR
6 , SYSDATE AS HIREDATE
7 , FLOOR( DBMS_RANDOM.VALUE ) * 10000 AS SAL
8 , FLOOR( DBMS_RANDOM.VALUE ) * 100 AS COMM
9 , MOD( LEVEL, 4 ) * 10 + 10 AS DEPTNO
10 FROM DUAL
11 CONNECT BY LEVEL <= 4000000;

4000000 개의 행이 만들어졌습니다.

SQL> commit;

SQL> CREATE TABLE DEPT_PARTITION(
2 DEPTNO NUMBER(2)
3 , DNAME VARCHAR2(14)
4 , LOC VARCHAR2(13))
5 PARTITION BY RANGE ( DEPTNO) (
6 PARTITION P1 VALUES LESS THAN( 20 )
7 , PARTITION P2 VALUES LESS THAN( 30 )
8 , PARTITION P3 VALUES LESS THAN( 40 )
9 , PARTITION P4 VALUES LESS THAN( 50 )
10 );

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

SQL> INSERT INTO DEPT_PARTITION VALUES (10, 'ACCOUNTING', 'NEW YORK');
SQL> INSERT INTO DEPT_PARTITION VALUES (20, 'RESEARCH', 'DALLAS');
SQL> INSERT INTO DEPT_PARTITION VALUES (30, 'SALES', 'CHICAGO');
SQL> INSERT INTO DEPT_PARTITION VALUES (40, 'OPERATIONS', 'BOSTON');
SQL> COMMIT;

{CODE}RANGE PARTITION....
{CODE:SQL}
SQL> SELECT /*+ gather_plan_statistics FULL( D ) FULL ( E ) PARALLEL(D 2) PARALLEL(E 2) /COUNT()
2 FROM DEPT_PARTITION D, EMP_PARTITION2 E
3 WHERE E.DEPTNO = D.DEPTNO;

--

IdOperationNameStartsE-RowsA-RowsA-TimeBuffers

--

1SORT AGGREGATE11100:00:03.6720
2PX COORDINATOR1200:00:03.5720
3PX SEND QC (RANDOM):TQ1000101000:00:00.010
4SORT AGGREGATE01000:00:00.010
5NESTED LOOPS04351K000:00:00.010
6PX RECEIVE0000:00:00.010
7PX SEND BROADCAST:TQ100000000:00:00.010
8PX BLOCK ITERATOR04000:00:00.010
  • 9
TABLE ACCESS FULLDEPT_PARTITION04000:00:00.010
10PX BLOCK ITERATOR01087K000:00:00.010
  • 11
TABLE ACCESS FULLEMP_PARTITION201087K000:00:00.010

--

Predicate Information (identified by operation id):

---

9 - access(:Z>=:Z AND :Z<=:Z)
11 - access(:Z>=:Z AND :Z<=:Z)
filter("E"."DEPTNO"="D"."DEPTNO")

SQL> BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS(USER,
3 'EMP_PARTITION2',
4 CASCADE => TRUE);
5 END;
6 /

--

IdOperationNameStartsE-RowsA-RowsA-TimeBuffers

--

1SORT AGGREGATE11100:00:14.1420
2PX COORDINATOR1200:00:14.0420
3PX SEND QC (RANDOM):TQ1000101000:00:00.010
4SORT AGGREGATE01000:00:00.010
5NESTED LOOPS03999K000:00:00.010
6PX RECEIVE0000:00:00.010
7PX SEND BROADCAST:TQ100000000:00:00.010
8PX BLOCK ITERATOR04000:00:00.010
  • 9
TABLE ACCESS FULLDEPT_PARTITION04000:00:00.010
10PX BLOCK ITERATOR0999K000:00:00.010
  • 11
TABLE ACCESS FULLEMP_PARTITION20999K000:00:00.010

--

Predicate Information (identified by operation id):

---

9 - access(:Z>=:Z AND :Z<=:Z)
11 - access(:Z>=:Z AND :Z<=:Z)
filter("E"."DEPTNO"="D"."DEPTNO")
SQL> BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS(USER,
3 'DEPT_PARTITION',
4 CASCADE => TRUE);
5 END;
6 /

--

IdOperationNameStartsE-RowsA-RowsA-TimeBuffers

--

1SORT AGGREGATE11100:00:00.8120
2PX COORDINATOR1200:00:00.7120
3PX SEND QC (RANDOM):TQ1000101000:00:00.010
4SORT AGGREGATE01000:00:00.010
5NESTED LOOPS03999K000:00:00.010
6PX RECEIVE0000:00:00.010
7PX SEND BROADCAST:TQ100000000:00:00.010
8PX BLOCK ITERATOR04000:00:00.010
  • 9
TABLE ACCESS FULLDEPT_PARTITION04000:00:00.010
10PX BLOCK ITERATOR0999K000:00:00.010
  • 11
TABLE ACCESS FULLEMP_PARTITION20999K000:00:00.010

--

Predicate Information (identified by operation id):

---

9 - access(:Z>=:Z AND :Z<=:Z)
11 - access(:Z>=:Z AND :Z<=:Z)
filter("E"."DEPTNO"="D"."DEPTNO")

{CODE}머야 왜안되는거야??? =_= ( HASH PARTITION... )
{CODE:SQL}
QL> CREATE TABLE EMP_PARTITION3(
2 EMPNO NUMBER NOT NULL,
3 ENAME VARCHAR2(10),
4 JOB VARCHAR2(9),
5 MGR NUMBER(4),
6 HIREDATE DATE,
7 SAL NUMBER(7, 2),
8 COMM NUMBER(7, 2),
9 DEPTNO NUMBER(2))
10 PARTITION BY HASH (DEPTNO) (
11 PARTITION P1
12 , PARTITION P2
13 , PARTITION P3
14 , PARTITION P4
15 );

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

QL> CREATE TABLE DEPT_PARTITION3(
2 DEPTNO NUMBER(2)
3 , DNAME VARCHAR2(14)
4 , LOC VARCHAR2(13))
5 PARTITION BY HASH ( DEPTNO) (
6 PARTITION P1
7 , PARTITION P2
8 , PARTITION P3
9 , PARTITION P4
10 );

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

SQL> INSERT INTO EMP_PARTITION3
2 SELECT LEVEL AS EMPNO
3 , 'TEST' AS ENAME
4 , 'JOB' AS JOB
5 , 1000 AS MGR
6 , SYSDATE AS HIREDATE
7 , FLOOR( DBMS_RANDOM.VALUE ) * 10000 AS SAL
8 , FLOOR( DBMS_RANDOM.VALUE ) * 100 AS COMM
9 , MOD( LEVEL, 4 ) * 10 + 10 AS DEPTNO
10 FROM DUAL
11 CONNECT BY LEVEL <= 4000000;

4000000 개의 행이 만들어졌습니다.

SQL> COMMIT;

SQL> INSERT INTO DEPT_PARTITION3 VALUES (10, 'ACCOUNTING', 'NEW YORK');
SQL> INSERT INTO DEPT_PARTITION3 VALUES (20, 'RESEARCH', 'DALLAS');
SQL> INSERT INTO DEPT_PARTITION3 VALUES (30, 'SALES', 'CHICAGO');
SQL> INSERT INTO DEPT_PARTITION3 VALUES (40, 'OPERATIONS', 'BOSTON');
SQL> COMMIT;

SQL> SELECT /*+ gather_plan_statistics FULL( D ) FULL ( E ) PARALLEL(D 2) PARALLEL(E 2) /COUNT()
2 FROM DEPT_PARTITION3 D, EMP_PARTITION3 E
3 WHERE E.DEPTNO = D.DEPTNO;

COUNT(*)

--
4000000

SQL> @XPLAN

---

IdOperationNameStartsE-RowsA-RowsA-TimeBuffers

---

1SORT AGGREGATE11100:00:05.7419
2PX COORDINATOR1200:00:05.6419
3PX SEND QC (RANDOM):TQ1000101000:00:00.010
4SORT AGGREGATE01000:00:00.010
5NESTED LOOPS05195K000:00:00.010
6PX RECEIVE0000:00:00.010
7PX SEND BROADCAST:TQ100000000:00:00.010
8PX BLOCK ITERATOR04000:00:00.010
  • 9
TABLE ACCESS FULLDEPT_PARTITION304000:00:00.010
10PX BLOCK ITERATOR01298K000:00:00.010
  • 11
TABLE ACCESS FULLEMP_PARTITION301298K000:00:00.010

---

Predicate Information (identified by operation id):

---

9 - access(:Z>=:Z AND :Z<=:Z)
11 - access(:Z>=:Z AND :Z<=:Z)
filter("E"."DEPTNO"="D"."DEPTNO")

SQL> BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS(USER,
3 'DEPT_PARTITION3',
4 CASCADE => TRUE);
5 END;
6 /

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

SQL> BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS(USER,
3 'EMP_PARTITION3',
4 CASCADE => TRUE);
5 END;
6 /

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

SQL> SELECT /*+ gather_plan_statistics FULL( D ) FULL ( E ) PARALLEL(D 2) PARALLEL(E 2) /COUNT()
2 FROM DEPT_PARTITION3 D, EMP_PARTITION3 E
3 WHERE E.DEPTNO = D.DEPTNO;

COUNT(*)

--
4000000

---

IdOperationNameStartsE-RowsA-RowsA-TimeBuffers

---

1SORT AGGREGATE11100:00:09.2219
2PX COORDINATOR1200:00:09.2219
3PX SEND QC (RANDOM):TQ1000101000:00:00.010
4SORT AGGREGATE01000:00:00.010
5NESTED LOOPS04003K000:00:00.010
6PX RECEIVE0000:00:00.010
7PX SEND BROADCAST:TQ100000000:00:00.010
8PX BLOCK ITERATOR04000:00:00.010
  • 9
TABLE ACCESS FULLDEPT_PARTITION304000:00:00.010
10PX BLOCK ITERATOR01000K000:00:00.010
  • 11
TABLE ACCESS FULLEMP_PARTITION301000K000:00:00.010

---

Predicate Information (identified by operation id):

---

9 - access(:Z>=:Z AND :Z<=:Z)
11 - access(:Z>=:Z AND :Z<=:Z)
filter("E"."DEPTNO"="D"."DEPTNO")

SQL> SELECT /*+ gather_plan_statistics USE_HASH( D E ) FULL( D ) FULL ( E ) PARALLEL(D 2) PARALLEL(E 2) /COUNT()
2 FROM DEPT_PARTITION3 D, EMP_PARTITION3 E
3 WHERE E.DEPTNO = D.DEPTNO;

COUNT(*)

--
4000000

SQL> @XPLAN

--

IdOperationNameStartsE-RowsA-RowsA-TimeBuffersOMem1MemUsed-Mem

--

1SORT AGGREGATE11100:00:02.7519
2PX COORDINATOR1200:00:02.6519
3PX SEND QC (RANDOM):TQ1000101000:00:00.010
4SORT AGGREGATE01000:00:00.010
  • 5
HASH JOIN04003K000:00:00.0101517K1517K753K (0)
6PX BLOCK ITERATOR04000:00:00.010
  • 7
TABLE ACCESS FULLDEPT_PARTITION304000:00:00.010
8PX RECEIVE04003K000:00:00.010
9PX SEND BROADCAST LOCAL:TQ1000004003K000:00:00.010
10PX BLOCK ITERATOR04003K000:00:00.010
  • 11
TABLE ACCESS FULLEMP_PARTITION304003K000:00:00.010

--

Predicate Information (identified by operation id):

---

5 - access("E"."DEPTNO"="D"."DEPTNO")
7 - access(:Z>=:Z AND :Z<=:Z)
11 - access(:Z>=:Z AND :Z<=:Z)

SQL> SELECT /*+ gather_plan_statistics USE_HASH( D E ) FULL( D ) FULL ( E ) PARALLEL(D 2) PARALLEL(E 2) PQ_DISTRIBUTE(D, NONE, NONE) /COUNT()
2 FROM DEPT_PARTITION3 D, EMP_PARTITION3 E
3 WHERE E.DEPTNO = D.DEPTNO;

COUNT(*)

--
4000000

SQL> @XPLAN

--

IdOperationNameStartsE-RowsA-RowsA-TimeBuffersOMem1MemUsed-Mem

--

1SORT AGGREGATE11100:00:00.7419
2PX COORDINATOR1200:00:00.6419
3PX SEND QC (RANDOM):TQ1000101000:00:00.010
4SORT AGGREGATE01000:00:00.010
  • 5
HASH JOIN04003K000:00:00.0101517K1517K753K (0)
6PX BLOCK ITERATOR04000:00:00.010
  • 7
TABLE ACCESS FULLDEPT_PARTITION304000:00:00.010
8PX RECEIVE04003K000:00:00.010
9PX SEND BROADCAST LOCAL:TQ1000004003K000:00:00.010
10PX BLOCK ITERATOR04003K000:00:00.010
  • 11
TABLE ACCESS FULLEMP_PARTITION304003K000:00:00.010

--

Predicate Information (identified by operation id):

---

5 - access("E"."DEPTNO"="D"."DEPTNO")
7 - access(:Z>=:Z AND :Z<=:Z)
11 - access(:Z>=:Z AND :Z<=:Z)

--RANGE PARTITION
SQL> SELECT DEPTNO, COUNT( * ) FROM EMP_PARTITION2
2 GROUP BY DEPTNO;

DEPTNO COUNT(*)

--
--
10 1000000
20 1000000
30 1000000
40 1000000
--HASH PARTITION
SQL> SELECT DEPTNO, COUNT( * ) FROM EMP_PARTITION3
2 GROUP BY DEPTNO;

DEPTNO COUNT(*)

--
--
30 1000000
40 1000000
10 1000000
20 1000000

{CODE}
그럼 왜 잘 작동하지 않았는가??( 변외 1 )
Pallel Query의 조인시 Row Distribution ( 참조만 하셔요.. 진행상 뒤에서 다시 설명합니다.)

대용량 Parallel 쿼리에서 조인을 사용할 경우 성능이 저하되는 경우가 많다.

  • 이경우는 여러가지 원인이 있는데 가장 치명적인 것은 잘못된 Row Distribution( Row의 분배방법 )에있다.

1.Row Distribution Method ( 로우 분배 메소드 )

  • 1) Hash : 조인기준컬럼을 Hash Function을 이용하여 양쪽 테이블을 Mapping 하는 방식임 ( 조인컬럼 기준으로 각각의 Temp성 매핑테이블을 만들고 마지막에 Join 하는 방식이다. )
  • 2) Broadcast : 조인된 양쪽테이블에서 한쪽 테이블의 모든 Row를 다른쪽 테이블을 Scan시에 분배하는 방식이다.
    BroadCast 받는 Table의 Scan시에 자동으로 조인이 되므로 따로 Join Operation 이 필요가 없다.
    하지만 Broadcast하는측 테이블의 사이즈가 커지면 Parallel Slave 마다 Outer Table을 반복적으로 BroadCast해야 하므로 비효율이 커진다.
  • 3) Partition : 파티션을 이용하여 조인이된 양쪽테이블의 Row를 분배한다. 2개의 조인된 테이블 중에서 조인컬럼을 기준으로 한족 테이블은 파티션이 되어있어야한다.
    파티션이 안된 테이블을 조인 컬럼을 기준으로 파티션하여 Row를 분배하는 방식이다.
    이분배방식은 Partiton Wise Join과 관계가 있다.
  • 4) None : 이미 조인컬럼 기준으로 파티션 된 테이블은 Row가 파티션기준으로 자동으로 분배되거나 Broadcast 방식일 경우 분배를 받는 쪽에 테이블의 Row는 따로 분배가 필요 없으므로
    None으로 표현한다.

1_1 조인시 Row Distribution의 Combination

  • 한테이블의 Row 분배방식을 알았으니 이젠 양측 테이블의 Row 분배를 조인을 위하여 결합해야 하는데
    4가지 분배방식 중에서 Oracle에서 허용되는 Combination은 아래처럼 6가지 이다.
    보는 방법은 Comma( , ) 왼쪽이 Outer Table 오른쪽이 Inner Table이다.
    다시 말하면 조인이 왼쪽테이블에서 오른쪽 테이블로진행된다.

1_2 방식

  • 1) Hash, Hash : 양쪽 테이블의 사이즈가 비슷하고 Hash Join이나 Sort Merge조인을 사용할때 권장된다.
  • 2) BroadCast, None : Outer Table의 사이즈와 Inner Table의 사이즈를 비교하여 Outer 테이블의 사이즈가 훨씬적을때 권장된다.
    예를 들면 코드 테이블과 대용량 테이블을 조인할때 저격이다.
    왜냐하면 Inner Table의 Granule 갯수 만큼 Outer 테이블의 Row가 반복해서
    제공되어야 하기 때문에 BroadCast하는쪽의 테이블이 크면 I/O양이 급격히 늘어난다.
  • 3) None, BroadCast : 2)번의 방법과 같으나 순서가 정반대 이다.
    다시말해 Inner 테이블이 BroadCast된다.
    Outer Table의 사이즈와 Inner Table의 사이즈를 비교하여 Inner 테이블의 사이즈가 휠씬 적을때 권장된다. ( Outer가 Driving되는 Hash Join을 사용시 최악의 Combination 임
  • 4) Partiton, None : Out테이블을 조인된 컬럼기준으로 Partition을 하여 Row를 분배하며 Partition Wise조인을 한다.
  • 5) None, Partition : Inner 테이블을 조인된 커럼 기준으로 Partition을 하여 Row를 분배하며 Partition Wise 조인을 한다.
  • 6) None, None : 조인이 되는 양측의 테이블이 이미 조인컬럼 기준으로 파티션이 되어 있을때 따라 분배가 필요 없으므로 이런 Combination이 발생한다.( 양측 테이블이 파티션 기준으로 분배 )

1_3 PQ_DISTRIBUTE 힌트 사용

힌트
{CODE:SQL}
/*+ PQ_DISTRIBUTE( Inner 테이블 명, outer_distribution, inner_distribution ) */		{CODE}실행 + _ +
{CODE:SQL}
SQL> SELECT /*+ gather_plan_statistics LEADING(E D) FULL( D ) FULL ( E ) PARALLEL(D 2) PARALLEL(E 2) PQ_DISTRIBUTE(D, NONE, NONE) /COUNT()
2 FROM DEPT_PARTITION3 D, EMP_PARTITION3 E
3 WHERE E.DEPTNO = D.DEPTNO;

COUNT(*)

--
4000000

SQL> @XPLAN

IdOperationNameStartsE-RowsA-RowsA-TimeBuffersOMem1MemUsed-MemUsed-Tmp

1SORT AGGREGATE11100:00:01.3419
2PX COORDINATOR1200:00:01.3419
3PX SEND QC (RANDOM):TQ1000001000:00:00.010
4SORT AGGREGATE01000:00:00.010
5PX PARTITION HASH ALL04003K000:00:00.010
  • 6
HASH JOIN04003K000:00:00.010184M15M10M (1)11264
7TABLE ACCESS FULLEMP_PARTITION304003K000:00:00.010
8TABLE ACCESS FULLDEPT_PARTITION304000:00:00.010

Predicate Information (identified by operation id):

---

6 - access("E"."DEPTNO"="D"."DEPTNO")

SQL> SELECT /*+ gather_plan_statistics LEADING(E D) FULL( D ) FULL ( E ) PARALLEL(D 2) PARALLEL(E 2) PQ_DISTRIBUTE(D, NONE, NONE) /COUNT()
2 FROM DEPT_PARTITION D, EMP_PARTITION2 E
3 WHERE E.DEPTNO = D.DEPTNO;

COUNT(*)

--
4000000

SQL> @XPLAN

IdOperationNameStartsE-RowsA-RowsA-TimeBuffersOMem1MemUsed-MemUsed-Tmp

1SORT AGGREGATE11100:00:01.4520
2PX COORDINATOR1200:00:01.3520
3PX SEND QC (RANDOM):TQ1000001000:00:00.010
4SORT AGGREGATE01000:00:00.010
5PX PARTITION RANGE ALL03999K000:00:00.010
  • 6
HASH JOIN03999K000:00:00.01092M8084K3743K (1)11264
7TABLE ACCESS FULLEMP_PARTITION203999K000:00:00.010
8TABLE ACCESS FULLDEPT_PARTITION04000:00:00.010

Predicate Information (identified by operation id):

---

6 - access("E"."DEPTNO"="D"."DEPTNO")

{CODE}
* 우선 설명...
* 그림 7-7 참조 ( P 707 )
* 조인 컬럼으로 파티션이 생성 되어야 한다.
* 병렬도가 2라고 가정하면 그림 7-7에서 보듯 P000과 P001서버 프로세스가 각각 Partition Pair 1과 Partition Pair 2를 처리한다.
* 조인을 수행하는동안 P000과 P001 프로세스가 데이터를 주고 받으며 통신할 필요가 전혀 없다. 이미 상호 배타적인 Partition-Pair가 형성돼 있으돼 있으므로 각 서버 프로세스가 하나씩 독립적으로
조인을 수행할 수 있는 것이다.
* 참고로, P000, P001 서버 프로세스 옆에 표시된 숫자 9와 5는 각각 조인을 수행하고 나서 QC에게 전송한 조인 결과 건수다.
* 만약 Partition-Pair가 10개면, 두 개 서버 프로세스가 각각 5개씩 순차적으로 처리하면 된다.
* Hash join 바로 위쪽에 'PX PARTITION RANGE ALL' OR 'PX PARTITION RANGE ITERATOR'라고 표시되는 것을 통해 Full Partition Wise 조인인 것을 확인 할 수 있다.
* 다른 병렬 조인은 두개의 서버집합이 필요한 반면, 여기서 하나의 서버집합만 필요함
* Full Partition Wise 조인은 파티션 기반 Granule이므로 서버 프로세스 개수는 파티션 개수이하로 제한된다.
* 파티션 방식은 어떤 것이든 상관없다 단지 조인 컬럼의 같은 방식 및 기준으로 파티셔닝 돼 있다면 서로 방해받지 않고 Partition pair끼리 독립적인 조인이 가능하기 때문이다.
PQ_DISTRIBUTE(D, BROADCAST, NONE) 기존에 이렇게 옵티마이져가 판단한거임
{CODE:SQL}

SQL> SELECT /*+ gather_plan_statistics LEADING(E D) FULL( D ) FULL ( E ) PARALLEL(D 2) PARALLEL(E 2) PQ_DISTRIBUTE(D, BROADCAST, NONE) /COUNT()
2 FROM DEPT_PARTITION D, EMP_PARTITION2 E
3 WHERE E.DEPTNO = D.DEPTNO;

COUNT(*)

--
4000000

SQL> @XPLAN

---

IdOperationNameStartsE-RowsA-RowsA-TimeBuffersOMem1MemUsed-MemUsed-Tmp

---

1SORT AGGREGATE11100:00:16.1020
2PX COORDINATOR1200:00:16.0020
3PX SEND QC (RANDOM):TQ1000101000:00:00.010
4SORT AGGREGATE01000:00:00.010
  • 5
HASH JOIN03999K000:00:00.010184M15M3499K (1)40960
6PX RECEIVE03999K000:00:00.010
7PX SEND BROADCAST:TQ1000003999K000:00:00.010
8PX BLOCK ITERATOR03999K000:00:00.010
  • 9
TABLE ACCESS FULLEMP_PARTITION203999K000:00:00.010
10PX BLOCK ITERATOR04000:00:00.010
  • 11
TABLE ACCESS FULLDEPT_PARTITION04000:00:00.010

---

Predicate Information (identified by operation id):

---

5 - access("E"."DEPTNO"="D"."DEPTNO")
9 - access(:Z>=:Z AND :Z<=:Z)
11 - access(:Z>=:Z AND :Z<=:Z)

{CODE}

Parallel Query 의 조인시 또다른 튜닝방법(Parallel Join Filter)

참조 : http://scidb.tistory.com/tag/PQ_DISTRIBUTE ( 시간 남으면 테스트해볼게요 ... 아마 못할듯 =_= )

(2) 둘 중 하나만 파티셔닝 된 경우 - Partial Partition Wise

  • 둘 중 한 테이블만 조인 컬럼에 대해 파티셔닝된 경우, 다른 한쪽 테이블을 같은 기준으로 동적으로 파티셔닝하고 나서 각 Partition-Pair를 독립적으로 병렬 조인하는 것을 말함
    둘 다 파티셔닝되었지만 파티션 기주이 서로 다른 경우도 이방식으로 조인 될 수 있다.

테이터를 동적으로 파티셔닝 하기 위해서 테이터 재분배가 선행되어야 한다는 사실이다. ( Inter-operation parallelism 을 위해 두 개의 서버 집합이 필요 해진다. )

준비 스크립트
{CODE:SQL}
SQL> CREATE TABLE DEPT_NOPARTITION(
2 DEPTNO NUMBER(2)
3 , DNAME VARCHAR2(14)
4 , LOC VARCHAR2(13)
5 );

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

SQL> INSERT INTO DEPT_NOPARTITION VALUES (10, 'ACCOUNTING', 'NEW YORK');
SQL> INSERT INTO DEPT_NOPARTITION VALUES (20, 'RESEARCH', 'DALLAS');
SQL> INSERT INTO DEPT_NOPARTITION VALUES (30, 'SALES', 'CHICAGO');
SQL> INSERT INTO DEPT_NOPARTITION VALUES (40, 'OPERATIONS', 'BOSTON');
SQL> COMMIT;

SQL> begin dbms_stats.gather_table_stats( user , 'DEPT_NOPARTITION' , cascade => true ); end;
2 /

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

SQL> SELECT /*+ gather_plan_statistics LEADING( E D ) USE_HASH( E ) FULL( D ) FULL ( E ) PARALLEL(D 2) PARALLEL(E 2) PQ_DISTRIBUTE(E, NONE , BROADCAST) /COUNT()
2 FROM DEPT_NOPARTITION D, EMP_PARTITION3 E
3 WHERE E.DEPTNO = D.DEPTNO;

COUNT(*)

--
4000000

SQL> @XPLAN
--어라?

-

IdOperationNameStartsE-RowsA-RowsA-TimeBuffersOMem1MemUsed-MemUsed-Tmp

-

1SORT AGGREGATE11100:00:01.8813
2PX COORDINATOR1200:00:01.7813
3PX SEND QC (RANDOM):TQ1000101000:00:00.010
4SORT AGGREGATE01000:00:00.010
  • 5
HASH JOIN04003K000:00:00.01070M8084K8328K (1)31744
6PX PARTITION HASH ALL04003K000:00:00.010
7TABLE ACCESS FULLEMP_PARTITION304003K000:00:00.010
8PX RECEIVE04000:00:00.010
9PX SEND PARTITION (KEY):TQ1000004000:00:00.010
10PX BLOCK ITERATOR04000:00:00.010
  • 11
TABLE ACCESS FULLDEPT_NOPARTITION04000:00:00.010

-

Predicate Information (identified by operation id):

---

5 - access("E"."DEPTNO"="D"."DEPTNO")
11 - access(:Z>=:Z AND :Z<=:Z)

SQL> SELECT /*+ gather_plan_statistics LEADING( D E ) USE_HASH( E ) FULL( D ) FULL ( E ) PARALLEL(D 2) PARALLEL(E 2) PQ_DISTRIBUTE(E,BROADCAST, NONE ) /COUNT()
2 FROM DEPT_NOPARTITION D, EMP_PARTITION3 E
3 WHERE E.DEPTNO = D.DEPTNO;

COUNT(*)

--
4000000

SQL> @XPLAN

---

IdOperationNameStartsE-RowsA-RowsA-TimeBuffersOMem1MemUsed-Mem

---

1SORT AGGREGATE11100:00:00.9013
2PX COORDINATOR1200:00:00.8013
3PX SEND QC (RANDOM):TQ1000101000:00:00.010
4SORT AGGREGATE01000:00:00.010
  • 5
HASH JOIN04003K000:00:00.0101517K1517K990K (0)
6PX RECEIVE04000:00:00.010
7PX SEND BROADCAST:TQ1000004000:00:00.010
8PX BLOCK ITERATOR04000:00:00.010
  • 9
TABLE ACCESS FULLDEPT_NOPARTITION04000:00:00.010
10PX BLOCK ITERATOR04003K000:00:00.010
  • 11
TABLE ACCESS FULLEMP_PARTITION304003K000:00:00.010

---

Predicate Information (identified by operation id):

---

5 - access("E"."DEPTNO"="D"."DEPTNO")
9 - access(:Z>=:Z AND :Z<=:Z)
11 - access(:Z>=:Z AND :Z<=:Z)

SQL> SELECT /*+ gather_plan_statistics LEADING( D ) USE_HASH( E ) FULL( D ) FULL ( E ) PARALLEL(D 2) PARALLEL(E 2) PQ_DISTRIBUTE(E,PARTITION, NONE ) /COUNT()
2 FROM DEPT_NOPARTITION D, EMP_PARTITION3 E
3 WHERE E.DEPTNO = D.DEPTNO;

COUNT(*)

--
4000000

SQL> @XPLAN

---

IdOperationNameStartsE-RowsA-RowsA-TimeBuffersOMem1MemUsed-Mem

---

1SORT AGGREGATE11100:00:01.2513
2PX COORDINATOR1200:00:01.1513
3PX SEND QC (RANDOM):TQ1000101000:00:00.010
4SORT AGGREGATE01000:00:00.010
  • 5
HASH JOIN04003K000:00:00.0101517K1517K869K (0)
6PX RECEIVE04000:00:00.010
7PX SEND PARTITION (KEY):TQ1000004000:00:00.010
8PX BLOCK ITERATOR04000:00:00.010
  • 9
TABLE ACCESS FULLDEPT_NOPARTITION04000:00:00.010
10PX PARTITION HASH ALL04003K000:00:00.010
11TABLE ACCESS FULLEMP_PARTITION304003K000:00:00.010

---

Predicate Information (identified by operation id):

---

5 - access("E"."DEPTNO"="D"."DEPTNO")
9 - access(:Z>=:Z AND :Z<=:Z)

{CODE}
* 그림 7 - 8 ( p709 )
* Full Partition Wise 전에 하나의 순서 서버 집합을 만들어서 실행한다.
* dept 테이블이 emp 테이블 기준으로 파티셔닝 되는 것을 표현 하고 있다.

둘 다 파티셔닝되지 않은 경우 - 동적 파티셔닝

  • 조인 컬럼에 대해 어느 한 쪽도 파티셔닝 되지 않은 상황이라면 오라클은 두 가지 방식 중 하나를 사용한다.

1) 양쪽 테이블을 동적으로 파티셔닝하고서 Full Partition Wise 조인

2) 한쪽 테이블을 BroadCast하고 나서 조인

준비 스크립트
{CODE:SQL}

SQL> CREATE TABLE EMP_NOPARTITION(
2 EMPNO NUMBER NOT NULL,
3 ENAME VARCHAR2(10),
4 JOB VARCHAR2(9),
5 MGR NUMBER(4),
6 HIREDATE DATE,
7 SAL NUMBER(7, 2),
8 COMM NUMBER(7, 2),
9 DEPTNO NUMBER(2)
10 );

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

SQL> INSERT INTO EMP_NOPARTITION
2 SELECT LEVEL AS EMPNO
3 , 'TEST' AS ENAME
4 , 'JOB' AS JOB
5 , 1000 AS MGR
6 , SYSDATE AS HIREDATE
7 , FLOOR( DBMS_RANDOM.VALUE ) * 10000 AS SAL
8 , FLOOR( DBMS_RANDOM.VALUE ) * 100 AS COMM
9 , MOD( LEVEL, 4 ) * 10 + 10 AS DEPTNO
10 FROM DUAL
11 CONNECT BY LEVEL <= 4000000;

4000000 개의 행이 만들어졌습니다.

SQL> COMMIT

{CODE}동적으로 파티셔닝 하는 방식( 테이타 분포도가 같을 경우, HASH, HASH )
{CODE:SQL}

SQL> SELECT /*+ gather_plan_statistics LEADING( D ) USE_HASH( E ) FULL( D ) FULL ( E ) PARALLEL(D 2) PARALLEL(E 2) PQ_DISTRIBUTE(E,HASH, HASH ) /COUNT()
2 FROM DEPT_NOPARTITION D, EMP_NOPARTITION E
3 WHERE E.DEPTNO = D.DEPTNO;

COUNT(*)

--
4000000

SQL> @XPLAN

---

IdOperationNameStartsE-RowsA-RowsA-TimeBuffersOMem1MemUsed-Mem

---

1SORT AGGREGATE11100:00:01.038
2PX COORDINATOR1200:00:01.038
3PX SEND QC (RANDOM):TQ1000201000:00:00.010
4SORT AGGREGATE01000:00:00.010
  • 5
HASH JOIN02888K000:00:00.0101517K1517K874K (0)
6PX RECEIVE04000:00:00.010
7PX SEND HASH:TQ1000004000:00:00.010
8PX BLOCK ITERATOR04000:00:00.010
  • 9
TABLE ACCESS FULLDEPT_NOPARTITION04000:00:00.010
10PX RECEIVE02888K000:00:00.010
11PX SEND HASH:TQ1000102888K000:00:00.010
12PX BLOCK ITERATOR02888K000:00:00.010
  • 13
TABLE ACCESS FULLEMP_NOPARTITION02888K000:00:00.010

---

Predicate Information (identified by operation id):

---

5 - access("E"."DEPTNO"="D"."DEPTNO")
9 - access(:Z>=:Z AND :Z<=:Z)
13 - access(:Z>=:Z AND :Z<=:Z)

{CODE}
* 그림 7-9
* 1단계 : 첫 번째 서버 집합이 DEPT테이블을 읽어 두 번째 서버 집합에 전송한다.( 상호 배타적 )
* 2단계 : 첫 번째 서버 집합이 EMP테이블 읽어 두 번째 서버 집합에 전송한다.( 상호 배타적 이 아닌것 같다...=_= )
* 첫 번째 서버 집합은 데이터를 분배하는 역할을 하고, 두 번째 서버 집합은 받은 데이터를 파티셔닝하는 역할을 한다.
가능하면 메모리 내에서 파티셔닝 하겠지만 공간이 부족할 때는 Temp 테이블 스페이스를 활용 할 것이다.
* 이렇게 2단계까지 완료 하고 나면 이제 Partition-Pair가 구성되었으므로 Full Partition Wise 조인을 수핼 할 수 있게 되었다.

그림 7-10( p712 )

  • 3단계 : 양쪽 테이블 모두의 파티셔닝을 담당한 두 번재 서버 집합이 각 Partition-Pair에 대해 독립적으로 병렬 조인을 수행한다.

위 방식의 특징은..??

  • 조인을 본격적으로 수행하기 전 사전 정지 작업 단계에서 메모리 자원과 Temp 테이블스페이스 공가늘 많이 사용한다는 데에 있다.
    그리고 양쪽 모두 파팃닝 해야 하므로 기본적으로 양쪽 테이블 모두에 대한 전체범위 처리가 불가피하다
  • 또한 조인 컬럼의 데이터 분포가 균일하지 않을 때는 프로세스 간 일량 차이 때문에 병렬 처리효과가 크게 반감 될 수 있다.
    예를 들어, 상품권 업무를 담당하는 사원이 몇몇 사람에게 집중된 상황에서 아래 병렬 쿼리를 이 방식으로 수행한다면 16개중 일부
    일부 프로세스만 열심히 일하고 나머지는 Idle 상태로 대기하는 현상이 발생 할 것이다.
쓸때 없이 노는 프로세스 현상 쿼리
{CODE:SQL}

SELECT /*+ FULL( A ) FULL( B ) PARALLEL( A 16 ) PARALLEL( B 16 ) */ A.사원명, ....
FROM 사원 A, 상품권 B
WHERE A.사번 = B.담당자사번

{CODE}
* 물론 이런 특징은 Partition Wise 조인에서도 똑같이 볼수 있지만 병렬 조인의 비효율 보다는 파티션 전략의 오류로 보는 게 타당하다.
* 동적으로 파티셔닝 해야 하는 상황에서는 위와 같은 현상이 실제 일어 날 수있기 때문에 조인문을 작성 할 때 세심한 주의가 필요하다.
사원 테이블이 매우 적다면 이어서 설명할 BroadCast 방식을 사용함으로써 쉽게 문제를 해결 할 수 있지만
그렇지 않다면 아예 병렬 조인을 포기하는것이 나을 수도 있다.

결론은?? 아래와 같은 상황에서 유용한 병렬 조인 방식이다.

  • 1) 어느 한쪽도 조인 컬럼 기준으로 파티셔닝 되지 않은 상황에서
  • 2) 두 테이블 모두 대용량 테이블이고
  • 3) 조인 컬럼의 데이터 분포가 균일할 때

볼륨 필터를 활용한 프로세스 간 통신 최소화 ( 6장 2절에서 자세히 설명... _ )

(3) 둘 다 파티셔닝 되지 않은 경우 - Broadcast 방식

동적으로 파티셔닝 하는 방식 ( 아웃터 테이블의 테이타가 적을경우 힌트 )
{CODE:SQL}

SQL> SELECT /*+ gather_plan_statistics LEADING( D E ) USE_HASH( E ) FULL( D ) FULL ( E ) PARALLEL(D 2) PARALLEL(E 2) PQ_DISTRIBUTE(E,BroadCast, None ) /COUNT()
2 FROM DEPT_NOPARTITION D, EMP_NOPARTITION E
3 WHERE E.DEPTNO = D.DEPTNO;

COUNT(*)

--
4000000

SQL> @XPLAN

---

IdOperationNameStartsE-RowsA-RowsA-TimeBuffersOMem1MemUsed-Mem

---

1SORT AGGREGATE11100:00:00.898
2PX COORDINATOR1200:00:00.808
3PX SEND QC (RANDOM):TQ1000101000:00:00.010
4SORT AGGREGATE01000:00:00.010
  • 5
HASH JOIN02888K000:00:00.0101517K1517K993K (0)
6PX RECEIVE04000:00:00.010
7PX SEND BROADCAST:TQ1000004000:00:00.010
8PX BLOCK ITERATOR04000:00:00.010
  • 9
TABLE ACCESS FULLDEPT_NOPARTITION04000:00:00.010
10PX BLOCK ITERATOR02888K000:00:00.010
  • 11
TABLE ACCESS FULLEMP_NOPARTITION02888K000:00:00.010

---

Predicate Information (identified by operation id):

---

5 - access("E"."DEPTNO"="D"."DEPTNO")
9 - access(:Z>=:Z AND :Z<=:Z)
11 - access(:Z>=:Z AND :Z<=:Z)

{CODE}동적으로 파티셔닝 하는 방식 ( 아웃터 테이블의 테이타가 적을경우 힌트을 걸어야 하는데 반대로 걸었을 경우.. IO부하가 심하다구..?? =_=)
{CODE:SQL}
SQL> SELECT /*+ gather_plan_statistics LEADING( E ) USE_HASH( E ) FULL( D ) FULL ( E ) PARALLEL(D 2) PARALLEL(E 2) PQ_DISTRIBUTE(D ,BroadCast, NONE ) /COUNT()
2 FROM DEPT_NOPARTITION D, EMP_NOPARTITION E
3 WHERE E.DEPTNO = D.DEPTNO;

COUNT(*)

--
4000000

SQL> @XPLAN

-

IdOperationNameStartsE-RowsA-RowsA-TimeBuffersOMem1MemUsed-MemUsed-Tmp

-

1SORT AGGREGATE11100:00:05.698
2PX COORDINATOR1200:00:05.698
3PX SEND QC (RANDOM):TQ1000101000:00:00.010
4SORT AGGREGATE01000:00:00.010
  • 5
HASH JOIN02888K000:00:00.010184M15M3096K (1)40960
6PX RECEIVE02888K000:00:00.010
7PX SEND BROADCAST:TQ1000002888K000:00:00.010
8PX BLOCK ITERATOR02888K000:00:00.010
  • 9
TABLE ACCESS FULLEMP_NOPARTITION02888K000:00:00.010
10PX BLOCK ITERATOR04000:00:00.010
  • 11
TABLE ACCESS FULLDEPT_NOPARTITION04000:00:00.010

-

Predicate Information (identified by operation id):

---

5 - access("E"."DEPTNO"="D"."DEPTNO")
9 - access(:Z>=:Z AND :Z<=:Z)
11 - access(:Z>=:Z AND :Z<=:Z)

{CODE}
* 그림 7 - 11 ( p715 )
* 조인 컬럼에 대해 어느 한 쪽도 파티션이 되지 않은 상황에서 오라클이 선택할 수 있는 두 번째 방식은 BroadCast 방식으로,
두 테이블 중 작은 쪽을 반대편 서버 집합의 "모든" 프로세스에 Broadcast하고 나서 조인을 수행하는 방식이다.
* 1단계 : 첫 번째 서버 집합에 속한 프로세스들이 각자 읽은 dept 테이블 레코드를 두 번째 서버 집합에 속한 모든 병렬 프로세스에게 전송한다.
* 2단계 : 두 번째 서버 집합에 속한 프로세스들이 각자 맡은 범위와 emp 테이블을 읽으면서 병렬로 조인을 수행한다.
1단계가 완료되면 두 번째 서버 집합에 속한 프로세스 모두 dept 테이블의 완전한 집합을 갖게 되므로 프로세스 간 상호 간섭 없이
독립적으로 조인 수행이 가능하다.
* 양쪽 테이블 모두 파티션 되지 않았을 때는 1차적으로 BroadCast 방식이 고려되어야 한다. 양쪽 테이블을 동적으로 파티셔닝 하는 방식은 앞서 설명한 것처럼
메모리 자원과 Temp 테이블 스페이스 공간을 많이 사용하는 반면 이 방식은 리소스 사용량이 매우 적기 때문이다.
BroadCast 되는 테이블이 아주 작을 때만 적용된다. 만약 Broadcast되는 테이블이 중대형 이상일 때는 과도한 프로세스 간 통신 때문에 성능이 매우 느려질 수 있다.
또한 두 번재 서버 집합이 메모리 내에서는 감당하기 어려울 정도로 큰 테이블을 BroadCast한다면 Temp 테이블스페이스 공간을 사용하게 되면서 그 성능 저하는 심각하게 저하될 것이다.
따라서 한쪽 테이블이 충분히 작을 때만 유용하다.
  • BroadCast는 작은 테이블임이 전제되어야 하므로 Serial 스캔으로 처리할 때도 많다. 따라서 P > P이 아닌 S>P형태가 오히려 일반적이고
    , 이는 두 테이블 중 한쪽 테이블만 병렬로 처리함을 뜻함
  • BroadCast가 이루어지고 나서의 조인 방식은 어떤 것이든 선택 가능하다. NL조인, 소트 머지 조인, 해시 조인등
  • BroadCast되는 작은 쪽 테이블은 전체범위처리가 불가피하지만 큰 테이블은 부분범위 처리가 가능하다.
병렬 조인 방식특징
Full Partition Wise 조인☞ 두 테이블 모두 조인 컬럼에 대해 같은 기준으로 파티셔닝
☞ 데이터 재분배 불필요 -> 단일 서버 집합만으로 수정
Partial Partition Wise 조인☞ 둘 중 한 테이블만 조인 컬럼에 대해 파티셔닝된 경우
☞ 파티셔닝되지 않은 다른 쪽 테이블을 같은 기준으로 파티셔닝하고 나서 Full Partition Wise 조인
☞ 동적 파티셔닝을 위한 데이터 재분배 필요 -> 두 개의 서버 집합이 작업 수행
동적 파티셔닝☞ 어느 한 쪽도 조인 컬럼에 대해 파티셔닝되지 않은 상황
☞ 양쪽 테이블이 모두 대용량
☞ 임시 테이블스페이스를 많이 사용
☞ 양쪽 테이블 모두 전체범위처리
☞ 조인 컬럼의 데이터 분포가 균일해야 함
Broadcase☞ 어느 한 쪽도 조인 컬럼에 대해 파티셔닝되지 않은 상황
☞ 둘 중 하나의 테이블이 매우 적을 때
☞ 동적 파티셔닝이 불필요 -> 큰 테이블에 대한 부분범위처리 가능

문서에 대하여

  • {*}이 문서의 내용은 (주)비투엔컬설팅에서 출간한 '오라클 성능 고도화 원리와 해법II'를 참고하였습니다.*
  1. HOME
  2. [종료]대용량 데이터베이스 스터디
  3. 2010년 하반기 대용량데이터베이스 스터디
  4. 3. 병렬 조인