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

4. PQ_DISTRIBUTE 힌트




04 PQ_DISTRIBUTE 힌트

  • 4가지 병렬 조인방식의 동작 원리와 특징을 살펴보았고, 이제 이들을 제어하기 위해 사용되는 PQ_DISTRIBUTE 힌트를 소개하려고 한다.

PQ_DISTRIBUTE 힌트의 용도

  • 옵티마이져가 파티션된 테이블을 적절히 활요하지 못하고 동적 재불할을 시도할 때
  • 기존 파티션 키를 무시하고 다른 키 값으로 동적 재분할하고 싶을 때
  • 통계정보가 부정확하거나 통계정보를 제공하기 어려운 상화( 옵티마이저가 잘못된 판단을 하기 쉬운 상황)에서 실행 계획을 고정시키고자 할 때
  • 기타 여러 가지 이유로 데이터 분배 방식을 변경하고자 할 때
SQL> SELECT /*+ gather_plan_statistics LEADING( D ) USE_MERGE( E ) FULL( D ) FULL ( E ) PARALLEL(D 4) PARALLEL(E 4) PQ_DISTRIBUTE(E ,HASH, HASH ) */COUNT(*)
  2    FROM DEPT_NOPARTITION D, EMP_NOPARTITION E
  3  WHERE E.DEPTNO = D.DEPTNO;

  COUNT(*)
----------
   4000000

SQL> @XPLAN

----------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name             | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem | Used-Tmp|
----------------------------------------------------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE            |                  |      1 |      1 |      1 |00:00:10.91 |       8 |       |       |          |         |
|   2 |   PX COORDINATOR           |                  |      1 |        |      4 |00:00:10.80 |       8 |       |       |          |         |
|   3 |    PX SEND QC (RANDOM)     | :TQ10002         |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |         |
|   4 |     SORT AGGREGATE         |                  |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |         |
|   5 |      MERGE JOIN            |                  |      0 |   2888K|      0 |00:00:00.01 |       0 |       |       |          |         |
|   6 |       SORT JOIN            |                  |      0 |      4 |      0 |00:00:00.01 |       0 |  2048 |  2048 | 2048  (0)|         |
|   7 |        PX RECEIVE          |                  |      0 |      4 |      0 |00:00:00.01 |       0 |       |       |          |         |
|   8 |         PX SEND HASH       | :TQ10000         |      0 |      4 |      0 |00:00:00.01 |       0 |       |       |          |         |
|   9 |          PX BLOCK ITERATOR |                  |      0 |      4 |      0 |00:00:00.01 |       0 |       |       |          |         |
|* 10 |           TABLE ACCESS FULL| DEPT_NOPARTITION |      0 |      4 |      0 |00:00:00.01 |       0 |       |       |          |         |
|* 11 |       SORT JOIN            |                  |      0 |   2888K|      0 |00:00:00.01 |       0 |    69M|  2879K|   21M (1)|   16384 |
|  12 |        PX RECEIVE          |                  |      0 |   2888K|      0 |00:00:00.01 |       0 |       |       |          |         |
|  13 |         PX SEND HASH       | :TQ10001         |      0 |   2888K|      0 |00:00:00.01 |       0 |       |       |          |         |
|  14 |          PX BLOCK ITERATOR |                  |      0 |   2888K|      0 |00:00:00.01 |       0 |       |       |          |         |
|* 15 |           TABLE ACCESS FULL| EMP_NOPARTITION  |      0 |   2888K|      0 |00:00:00.01 |       0 |       |       |          |         |
----------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

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

Note
-----
   - dynamic sampling used for this statement


39 개의 행이 선택되었습니다.
{CODE
* 병렬 조인을 수행하기 위해서는 프로세스들이 서로 "독립적을" 작업업 할 수 있도록 사전 준비작업이 필요하다. 먼저 테이터를 적절히 배분하는 작업이 선행
  되어야 하는 것이며, 어떤 방식이 있는지는 앞 절에서 충분히 설명하였다.
* 병렬 쿼리는 '분할 & 정복 원리'에 기초한다. 그 중에서도 병렬 조인을 위해서는 '분배 & 조인 원리'가 작동함을 이해하는 것이 매우 중요하다.
* 이때 [PQ_DISTRIBUTE 힌트는 조인에 앞서 데이터를 분배 하는 과정만 관여하는 힌트]임을 반드시 기억할 필요가 있다.

h3. 구문 이해하기
|| PQ_DISTRIBUTE ||
|{CODE:SQL}
/*+ PQ_DISTRIBUTE( TABLE, OUTER_DISTRIBUTION, INNER_DISTRIBUTION ) */
|
ㅋㅋㅋㅋㅋ

SQL> SELECT /*+ gather_plan_statistics
  2              ORDERED
  3              USE_HASH( D ) USE_HASH( E ) USE_HASH( PE )
  4              FULL( D ) FULL( PE ) FULL( E )
  5              PARALLEL(D 8) PARALLEL(E 8) PARALLEL(PE 8)
  6               PQ_DISTRIBUTE(E, BROADCAST, NONE) PQ_DISTRIBUTE(PE, PARTITION, NONE   ) */
  7              COUNT(*)
  8     FROM DEPT_NOPARTITION D, EMP_NOPARTITION E, EMP_PARTITION3 PE
  9   WHERE D.DEPTNO = E.DEPTNO
 10     AND E.DEPTNO = PE.DEPTNO
 11     AND ROWNUM <= 40000000;

  COUNT(*)
----------
  40000000

SQL> @xplan

----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name             | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
|   1 |  SORT AGGREGATE                |                  |      1 |      1 |      1 |00:00:16.36 |   19 |          |       |          |
|*  2 |   COUNT STOPKEY                |                  |      1 |        |     40M|00:00:11.01 |   19 |          |       |          |
|   3 |    PX COORDINATOR              |                  |      1 |        |     40M|00:00:11.01 |   19 |          |       |          |
|   4 |     PX SEND QC (RANDOM)        | :TQ10002         |      0 |   4005G|      0 |00:00:00.01 |    0 |          |       |          |
|*  5 |      COUNT STOPKEY             |                  |      0 |        |      0 |00:00:00.01 |    0 |          |       |          |
|*  6 |       HASH JOIN BUFFERED       |                  |      0 |   4005G|      0 |00:00:00.01 |    0 |       24M|  4548K| 9477K (1)|
|   7 |        PX PARTITION HASH ALL   |                  |      0 |   4003K|      0 |00:00:00.01 |    0 |          |       |          |
|   8 |         TABLE ACCESS FULL      | EMP_PARTITION3   |      0 |   4003K|      0 |00:00:00.01 |    0 |          |       |          |
|   9 |        PX RECEIVE              |                  |      0 |   3998K|      0 |00:00:00.01 |    0 |          |       |          |
|  10 |         PX SEND PARTITION (KEY)| :TQ10001         |      0 |   3998K|      0 |00:00:00.01 |    0 |          |       |          |
|* 11 |          HASH JOIN             |                  |      0 |   3998K|      0 |00:00:00.01 |    0 |  1517K|  1517K|  993K (0)|
|  12 |           PX RECEIVE           |                  |      0 |      4 |      0 |00:00:00.01 |    0 |          |       |          |
|  13 |            PX SEND BROADCAST   | :TQ10000         |      0 |      4 |      0 |00:00:00.01 |    0 |          |       |          |
|  14 |             PX BLOCK ITERATOR  |                  |      0 |      4 |      0 |00:00:00.01 |    0 |          |       |          |
|* 15 |              TABLE ACCESS FULL | DEPT_NOPARTITION |      0 |      4 |      0 |00:00:00.01 |    0 |          |       |          |
|  16 |           PX BLOCK ITERATOR    |                  |      0 |   3998K|      0 |00:00:00.01 |    0 |          |       |          |
|* 17 |            TABLE ACCESS FULL   | EMP_NOPARTITION  |      0 |   3998K|      0 |00:00:00.01 |    0 |          |       |          |
----------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(ROWNUM<=40000000)
   5 - filter(ROWNUM<=40000000)
   6 - access("E"."DEPTNO"="PE"."DEPTNO")
  11 - access("D"."DEPTNO"="E"."DEPTNO")
  15 - access(:Z>=:Z AND :Z<=:Z)
  17 - access(:Z>=:Z AND :Z<=:Z)


|
  • 1 실제 플랜을 표현하라
  • 2 FROM절에 나열된 순서되로 조인하라.
  • 3 PE 테이블과는 해시 조인, E 테이블과 해쉬조인하라
  • 4 D, PE, E 테이블을 FTS 하라
  • 5 D, PE, E 병렬로 처리하라
  • 6 E 테이블과 조인할때 Outer table( D )을 BROADCAST 방식으로 변환 하라. PE 테이블과 조인 할때 Outer Table( E )을 Inner Table 맞쳐 파티션 하라.

분배방식 지정

PQ_DISTRIBUTE( Inner, none, none )

  • Full-Partition Wise 조인으로 유도할 때 사용한다. 다연히, 양쪽 테이블 모두 조인 컬럼에 대해 같은 기준으로 파티셔닝( equi-partitioning ) 돼 있을 때만 작동한다.

PQ_DISTRIBUTE( Inner, partition, none )

  • Partial-Partition Wise 조인으로 유도할 때 사용하며, outer 테이블을 inner 테이블 파티션기준에 따라 파티셔닝하라는 뜻이다.
    당연히, inner 테이블이 조인 키 컬럼에 대해 파티셔닝 돼 있을 때만 작동한다.

PQ_DISTRIBUTE( Inner, none, partition )

  • Partial-Partition Wise 조인으로 유도할 때 사용하며, inner 테이블을 outer 테이블 파티션기준에 따라 파티셔닝 하라는 뜻이다.

PQ_DISTRIBUTE( Inner, hash, hash )

  • 조인 키 컬럼을 해시 함수에 적용하고 거기서 반환된 값을 기준으로 양쪽 테이블을 동적으로 파티셔닝 하라는 뜻이다.

PQ_DISTRIBUTE( innert, broadcast, none )

  • outer 테이블을 Boardcast 하라는 뜻이다.

PQ_DISTRIBUTE( inner, none, broadcast )

  • inner 테이블을 Broadcast 하라는 뜻이다.

h3 (4) PQ_DISTRIBUTE 힌트를 이용한 튜닝 사례

  • 통계 정보가 없는 상태에서 병렬 조인하면 옵티 마이저가 아주 큰 테이블을 Broadcast 하는 경우를 종종 보게 된다.
    임시 테이블을 많이 사용하는 야간 배치나 데이터 이행 프로그램에서 그런 문제가 자주 발생하는 이유가 여기에 있다.

SQL> SELECT /*+ gather_plan_statistics
  2              ORDERED
  3              USE_HASH( D ) USE_HASH( E ) USE_HASH( PE )
  4              FULL( D ) FULL( PE ) FULL( E )
  5              PARALLEL(D 8) PARALLEL(E 8) PARALLEL(PE 8)
  6               PQ_DISTRIBUTE(E, BROADCAST, NONE) PQ_DISTRIBUTE(PE, PARTITION, NONE   ) */
  7              COUNT(*)
  8     FROM DEPT_NOPARTITION D, EMP_NOPARTITION E, EMP_PARTITION3 PE
  9   WHERE D.DEPTNO = E.DEPTNO
 10     AND E.DEPTNO = PE.DEPTNO
 11     AND ROWNUM <= 40000000;

  COUNT(*)
----------
  40000000

SQL> @xplan

----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name             | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
|   1 |  SORT AGGREGATE                |                  |      1 |      1 |      1 |00:00:16.36 |   19 |          |       |          |
|*  2 |   COUNT STOPKEY                |                  |      1 |        |     40M|00:00:11.01 |   19 |          |       |          |
|   3 |    PX COORDINATOR              |                  |      1 |        |     40M|00:00:11.01 |   19 |          |       |          |
|   4 |     PX SEND QC (RANDOM)        | :TQ10002         |      0 |   4005G|      0 |00:00:00.01 |    0 |          |       |          |
|*  5 |      COUNT STOPKEY             |                  |      0 |        |      0 |00:00:00.01 |    0 |          |       |          |
|*  6 |       HASH JOIN BUFFERED       |                  |      0 |   4005G|      0 |00:00:00.01 |    0 |       24M|  4548K| 9477K (1)|
|   7 |        PX PARTITION HASH ALL   |                  |      0 |   4003K|      0 |00:00:00.01 |    0 |          |       |          |
|   8 |         TABLE ACCESS FULL      | EMP_PARTITION3   |      0 |   4003K|      0 |00:00:00.01 |    0 |          |       |          |
|   9 |        PX RECEIVE              |                  |      0 |   3998K|      0 |00:00:00.01 |    0 |          |       |          |
|  10 |         PX SEND PARTITION (KEY)| :TQ10001         |      0 |   3998K|      0 |00:00:00.01 |    0 |          |       |          |
|* 11 |          HASH JOIN             |                  |      0 |   3998K|      0 |00:00:00.01 |    0 |  1517K|  1517K|  993K (0)|
|  12 |           PX RECEIVE           |                  |      0 |      4 |      0 |00:00:00.01 |    0 |          |       |          |
|  13 |            PX SEND BROADCAST   | :TQ10000         |      0 |      4 |      0 |00:00:00.01 |    0 |          |       |          |
|  14 |             PX BLOCK ITERATOR  |                  |      0 |      4 |      0 |00:00:00.01 |    0 |          |       |          |
|* 15 |              TABLE ACCESS FULL | DEPT_NOPARTITION |      0 |      4 |      0 |00:00:00.01 |    0 |          |       |          |
|  16 |           PX BLOCK ITERATOR    |                  |      0 |   3998K|      0 |00:00:00.01 |    0 |          |       |          |
|* 17 |            TABLE ACCESS FULL   | EMP_NOPARTITION  |      0 |   3998K|      0 |00:00:00.01 |    0 |          |       |          |
----------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(ROWNUM<=40000000)
   5 - filter(ROWNUM<=40000000)
   6 - access("E"."DEPTNO"="PE"."DEPTNO")
  11 - access("D"."DEPTNO"="E"."DEPTNO")
  15 - access(:Z>=:Z AND :Z<=:Z)
  17 - access(:Z>=:Z AND :Z<=:Z)


|

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.