부제: Partition Wise Join의 성능

 

DBA나 튜닝 컨설턴트들에게 PARTITION WISE JOIN(이후 PWJ)를 설명해 보라고 하면, 언제나 PARALLEL PQ_DISTRIBUTE 힌트를 언급한다. 이상한 일이다. PWJ PARALLEL + PQ_DISTRIBUTE 힌트 조합과 상관없이 독립적으로 존재한다. 그럼에도 불구하고 PWJ를 설명하기 위해 두 힌트를 항상 끌어들인다. 마치 PWJ 기능이 두 힌트에 종속되기라도 하는 것처럼 말이다. 그 이유는 메뉴얼을 포함한 거의 모든 튜닝책에 위의 두 힌트를 사용해서 PWJ를 설명하고 있기 때문이다.

 

 

말도 안 되는 소리
이제는 PWJ를 설명 할 때, PARALLEL PQ_DISTRIBUTE 힌트와 연계하여 설명하지 말기 바란다. 이렇게 연계하여 설명하는 것은 근본원리를 모르면서 활용하려고 하는 것이다. 더 잘못된 것은 성능이 개선되는 이유는 조인할 PARTITION을 미리 짝지어 놓았기 때문(Partition Pair)에 조인이 빠르다고 설명한다. 잘못된 설명이다. 성능문제의 대부분이 두 가지 관점(BLOCK I/O PGA 사용량)으로 결정된다는 것은 상식이다. 그런데 두 가지 관점을 설명하지도 않고 PARTITION을 미리 짝지어 놓았기 때문에 빠르다는 것은 어불성설이다.

 

 

지금까지의 PWJ의 정의를 과감히 버려라. 그리고 지금부터 PWJ를 재정의 해보자. PWJ를 다시 설명하지 않으면 다음의 세가지를 알 수 없다.

 

1. PARALLEL 힌트와 PQ_DISTRIBUTE 힌트를 사용하지 않고 PWJ를 설명 할 수 있다.
2. PWJ
를 사용함으로써 개선된 성능을 BLOCK I/O로 나타낼 수 있다.
3. PWJ
를 사용함으로써 개선된 성능을 PGA 사용량으로 나타낼 수 있다.

 

1. 번은 개념을 설명하는 것이다. 2,3번은 성능향상을 수치로 나타낼 수 있느냐는 것이다. 아마 기존의 상식으로는 힘들것이다. 따라서 이글의 목적은 위의 세 가지를 이해하고 나타낼 수 있는 능력을 기르는 것이다

 

 

먼저 파티션 테이블 두 개(SALES_PT,SALES_ORDER_PT)를 만들고, 파티션이 되지 않은 테이블 두 개(SALES_NO_PT, SALES_ORDER_NO_PT)를 만든다. SALES_PT SALES_NO_PT의 차이점은 전자는 파티셔닝이 적용되고 후자는 적용되지 않았다는 것이다. SALES_ORDER_PT SALES_ORDER_NO_PT의 차이점도 마찬가지다.

 

 

테이블 생성 스크립트 다운로드

 

Table Generation.SQL

환경: 오라클 11.2.0.1 
     
모든 테이블은 대략 92만 건이다.
      SALES_PT, SALES_ORDER_PT 두 테이블의 파티션 키 컬럼은 TIME_ID 이다. 

 

 

먼저 파티션이 되지 않은 테이블 두 개를 이용하여 HASH JOIN으로 실행해보자.

 

SELECT /*+ LEADING(T)  */ COUNT(*)                                                       
  FROM SALES_NO_PT S, SALES_ORDER_NO_PT T                                                
 WHERE T.ORDER_DT = TO_DATE('20010101', 'YYYYMMDD')                                      
   AND S.PROD_ID = T.PROD_ID                                                             
   AND S.CUST_ID = T.CUST_ID                                                             
   AND S.TIME_ID = T.TIME_ID                                                             
   AND S.CHANNEL_ID = S.CHANNEL_ID                                                       
   AND S.PROMO_ID = T.PROMO_ID  ;                                                          
                                                                      

------------------------------------------------------------------------------------------
| Id  | Operation           | Name              | Starts | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                   |      1 |      1 |00:00:05.20 |    9898 |
|   1 |  SORT AGGREGATE     |                   |      1 |      1 |00:00:05.20 |    9898 |
|*  2 |   HASH JOIN         |                   |      1 |    154 |00:00:05.20 |    9898 |
|*  3 |    TABLE ACCESS FULL| SALES_ORDER_NO_PT |      1 |     88 |00:00:03.12 |    5457 |
|   4 |    TABLE ACCESS FULL| SALES_NO_PT       |      1 |    918K|00:00:01.20 |    4441 |
------------------------------------------------------------------------------------------

                                                                                                                                                                                     

Predicate Information (identified by operation id):                                                         
---------------------------------------------------                                                         
   2 - access("S"."PROD_ID"="T"."PROD_ID" AND "S"."CUST_ID"="T"."CUST_ID" AND "S"."TIME_ID"="T"."TIME_ID" AND
              "S"."PROMO_ID"="T"."PROMO_ID")                                                                
   3 - filter("T"."ORDER_DT"=TO_DATE(' 2001-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))                     

 

 

파티션이 아님으로 PWJ가 발생하지 않았다. ID 4 BLOCK I/O(Buffers) 4441임을 주목하라. 또한 후행테이블의 건수(A-Row) 918K임을 기억하라. 이제 PWJ 를 실행할 차례다.

 

 

SELECT /*+ LEADING(T) */ COUNT(*)

  FROM SALES_PT S, SALES_ORDER_PT T

 WHERE T.ORDER_DT = TO_DATE('20010101', 'YYYYMMDD')

  AND S.PROD_ID = T.PROD_ID

  AND S.CUST_ID = T.CUST_ID

  AND S.TIME_ID = T.TIME_ID

  AND S.CHANNEL_ID = S.CHANNEL_ID

AND S.PROMO_ID = T.PROMO_ID ;

 

 

--------------------------------------------------------------------------------------------------------

| Id  | Operation            | Name           | Starts | Pstart| Pstop | A-Rows |   A-Time   | Buffers |

--------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT     |                |      1 |       |       |      1 |00:00:00.18 |    6263 |

|   1 |  SORT AGGREGATE      |                |      1 |       |       |      1 |00:00:00.18 |    6263 |

|   2 |   PARTITION RANGE ALL|                |      1 |     1 |    28 |    154 |00:00:00.18 |    6263 |

|*  3 |    HASH JOIN         |                |     28 |       |       |    154 |00:00:00.18 |    6263 |

|*  4 |     TABLE ACCESS FULL| SALES_ORDER_PT |     28 |     1 |    28 |     88 |00:00:00.10 |    5947 |

|   5 |     TABLE ACCESS FULL| SALES_PT       |      1 |     1 |    28 |  60608 |00:00:00.02 |     316 |

--------------------------------------------------------------------------------------------------------

 

Outline Data

-------------

 

  /*+

      BEGIN_OUTLINE_DATA

      ...생략

      PX_JOIN_FILTER(@"SEL$1" "S"@"SEL$1")

      END_OUTLINE_DATA

  */

 

Predicate Information (identified by operation id):

---------------------------------------------------

   3 - access("S"."TIME_ID"="T"."TIME_ID" AND "S"."PROD_ID"="T"."PROD_ID" AND "S"."CUST_ID"="T"."CUST_ID" AND

              "S"."PROMO_ID"="T"."PROMO_ID")

   4 - filter("T"."ORDER_DT"=TO_DATE(' 2001-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

 

 

조인된 두 테이블은 모두 파티셔닝 되었다. 따라서 PARTITION RANGE ALL이 두 번 나와야 됨에도 불구하고 ID 2을 보면 단 한번만 나온다. PWJ가 실행되었다는 증거다. PWJ가 실행되지 않은 경우와 BLOCK I/O를 비교해 보면 14배 이상 차이가 난다. 바로 이 것이 FULL PWJ가 실행되면 성능에 유리한 이유다. FULL PWJ가 실행되면 후행 테이블의 파티션을 모두 읽을 필요가 없다. 왜냐하면 이미 선행테이블(BUILD INPUT)을 읽는 과정에서 어떤 파티션을 액세스 할 것인지 결정 되었기 때문이다. 따라서 후행테이블의 건수도 918K건이 아니라 59K(60608)에 불과한 것이다.

 

 

위의 Outline Data를 보고 혹자는 'PX_JOIN_FILTER 때문에 PARTITION PRUNING이 일어난 것이다' 고 의혹을 제기한다. 하지만 PX_JOIN_FILTER FULL PWJ는 아무 상관이 없다. 왜냐하면 아래와 같이 NO_PX_JOIN_FILTER 힌트를 사용해서 Filter를 제거해도 PWJ의 효과가 유지되기 때문이다. 

 

 

SELECT /*+ LEADING(T) NO_PX_JOIN_FILTER(S) */ COUNT(*)                                                   

  FROM SALES_PT S, SALES_ORDER_PT T                                                                     

 WHERE T.ORDER_DT = TO_DATE('20010101', 'YYYYMMDD')                                                     

  AND S.PROD_ID = T.PROD_ID                                                                              

  AND S.CUST_ID = T.CUST_ID                                                                             

  AND S.TIME_ID = T.TIME_ID                                                                              

  AND S.CHANNEL_ID = S.CHANNEL_ID                                                                       

  AND S.PROMO_ID = T.PROMO_ID      ;                                                                     

                                                                                                      

--------------------------------------------------------------------------------------------------------

| Id  | Operation            | Name           | Starts | Pstart| Pstop | A-Rows |   A-Time   | Buffers |

--------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT     |                |      1 |       |       |      1 |00:00:00.18 |    6263 |

|   1 |  SORT AGGREGATE      |                |      1 |       |       |      1 |00:00:00.18 |    6263 |

|   2 |   PARTITION RANGE ALL|                |      1 |     1 |    28 |    154 |00:00:00.18 |    6263 |

|*  3 |    HASH JOIN         |                |     28 |       |       |    154 |00:00:00.18 |    6263 |

|*  4 |     TABLE ACCESS FULL| SALES_ORDER_PT |     28 |     1 |    28 |     88 |00:00:00.10 |    5947 |

|   5 |     TABLE ACCESS FULL| SALES_PT       |      1 |     1 |    28 |  60608 |00:00:00.02 |     316 |

--------------------------------------------------------------------------------------------------------

                                                                                                        

Outline Data                                                                                             

-------------                                                                                           

                                                                                                         

  /*+                                                                                                   

      BEGIN_OUTLINE_DATA                                                                                

      IGNORE_OPTIM_EMBEDDED_HINTS                                                                       

      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')                                                             

      DB_VERSION('11.2.0.1')                                                                             

      ALL_ROWS                                                                                          

      OUTLINE_LEAF(@"SEL$1")                                                                            

      FULL(@"SEL$1" "T"@"SEL$1")                                                                        

      FULL(@"SEL$1" "S"@"SEL$1")                                                                        

      LEADING(@"SEL$1" "T"@"SEL$1" "S"@"SEL$1")                                                          

      USE_HASH(@"SEL$1" "S"@"SEL$1")                                                                    

      END_OUTLINE_DATA                                                                                   

  */                                                                                                    

                                                                                                        

Predicate Information (identified by operation id):                                                     

---------------------------------------------------                                                     

   3 - access("S"."TIME_ID"="T"."TIME_ID" AND "S"."PROD_ID"="T"."PROD_ID" AND "S"."CUST_ID"="T"."CUST_ID"

               AND "S"."PROMO_ID"="T"."PROMO_ID")                                                       

   4 - filter("T"."ORDER_DT"=TO_DATE(' 2001-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

 

 

 

위의 SQL에서 NO_PX_JOIN_FILTER를 사용하여 FILTER를 제거시켰지만 여전히 PWJ가 실행되었다. 따라서 PX_JOIN_FILTER FULL PWJ의 원리가 아니다. PWJ의 성능향상 원리는 T.ORDER_DT = TO_DATE('20010101', 'YYYYMMDD') 조건에 있다. 다시 말해 이 조건 때문에 후행 테이블은 2001년도 1분기 파티션만 읽으면 되는 것이다. 중요한 점은 T.ORDER_DT 컬럼이 Partition Key 컬럼이 아님에도 성능이 향상되었다는 점이다. 아래의 SQL이 그것을 증명한다.

 

 

SELECT /*+ FULL(SALES) */ COUNT(*)                  

  FROM SALES_PT PARTITION (ST_Q1_2001);

 

-----------------------------------------------------------------------------------------------------

| Id  | Operation               | Name     | Starts | Pstart| Pstop | A-Rows |   A-Time   | Buffers |

-----------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT        |          |      1 |       |       |      1 |00:00:00.05 |     316 |

|   1 |  SORT AGGREGATE         |          |      1 |       |       |      1 |00:00:00.05 |     316 |

|   2 |   PARTITION RANGE SINGLE|          |      1 |    17 |    17 |  60608 |00:00:00.04 |     316 |

|   3 |    TABLE ACCESS FULL    | SALES_PT |      1 |    17 |    17 |  60608 |00:00:00.02 |     316 |

-----------------------------------------------------------------------------------------------------

 

 

PWJ의 성능개선 원리는 선행집합의 Filter에 있다
FULL PWJ가 발생했을 때와 같이 정확히 316 BLOCK 만 읽었다. PWJ가 실행됨으로써 BLOCK I/O 관점의 성능개선사항은 명확해졌다. 선행집합의 FILTER가 후행집합의 BLOCK I/O를 결정한다는 것이다. 다시 말해 선행집합은 T.ORDER_DT = 상수조건에 상관없이 모든 파티션을 ACCESS 해야 한다. 하지만 후행집합은 T.ORDER_DT = 상수조건에 만족하는 파티션만 ACCESS 하는 것이 PWJ의 성능개선 원리이다.

 

 

Partial PWJ의 성능개선 원리도 FULL PWJ와 같다
FULL PWJ의 성능개선 원리와 Partial PWJ의 성능개선 원리는 같다. 하지만 처리방식이 다르다. Partial PWJ는 내부적으로Bloom Pruning을 이용한다. Bloom Filter를 이용하여 후행테이블의 조인건수를 줄일 수 있는데, Bloom Pruning도 같은 메커니즘을 이용하여 후행 테이블의 파티션 Access 개수를 최소화 하는 것이다. 아래의 SQL Partial PWJ 예제이며, Bloom Pruning을 이용하여 후행집합의 파티션 Access 개수를 최소화 하고 있다. 

 

 

ALTER SESSION SET "_bloom_pruning_enabled" = TRUE;

 

SELECT /*+ LEADING(T) USE_HASH(S) */ COUNT(*)

  FROM SALES_PT S, SALES_ORDER_NO_PT T

 WHERE T.ORDER_DT = TO_DATE('20010101', 'YYYYMMDD')

  AND S.PROD_ID = T.PROD_ID

  AND S.CUST_ID = T.CUST_ID

  AND S.TIME_ID = T.TIME_ID

  AND S.CHANNEL_ID = S.CHANNEL_ID

  AND S.PROMO_ID = T.PROMO_ID        ;

---------------------------------------------------------------------------------------------------------------

| Id  | Operation                     | Name             | Starts| Pstart| Pstop | A-Rows|   A-Time  | Buffers|

---------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT              |                  |      1|       |       |      1|00:00:00.64|    5773|

|   1 |  SORT AGGREGATE               |                  |      1|       |       |      1|00:00:00.64|    5773|

|*  2 |   HASH JOIN                   |                  |      1|       |       |    154|00:00:00.64|    5773|

|   3 |    PART JOIN FILTER CREATE    | :BF0000          |      1|       |       |     88|00:00:00.52|    5457|

|*  4 |     TABLE ACCESS FULL         | SALES_ORDER_NO_PT|      1|       |       |     88|00:00:00.52|    5457|

|   5 |    PARTITION RANGE JOIN-FILTER|                  |      1|:BF0000|:BF0000|  60608|00:00:00.05|     316|

|   6 |     TABLE ACCESS FULL         | SALES_PT         |      1|:BF0000|:BF0000|  60608|00:00:00.03|     316|

---------------------------------------------------------------------------------------------------------------

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     

Predicate Information (identified by operation id):                                   
---------------------------------------------------                                   
   2 - access("S"."TIME_ID"="T"."TIME_ID" AND "S"."PROD_ID"="T"."PROD_ID"             
                AND "S"."CUST_ID"="T"."CUST_ID" AND "S"."PROMO_ID"="T"."PROMO_ID")    
   4 - filter("T"."ORDER_DT"=TO_DATE(' 2001-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
                                                  

 

                                                                                          

FULL PARTITION WISE JOIN PARTIAL PARTITION WISE JOIN의 성능개선 원리는 같으며, BLOCK I/O도 동일하다. 다른 점은 PARTIAL PARTITION WISE JOINBloom Pruning를 이용했다는 것뿐이다. 다시 말해, 아래처럼 Bloom Pruning 기능을 사용할 수 없게 된다면 PARTIAL PARTITION WISE JOIN시 성능향상(후행 집합의 BLOCK I/O 감소)을 기대할 수 없다.   

 

 

ALTER SESSION SET "_bloom_pruning_enabled" = false;

 

SELECT /*+ LEADING(T) USE_HASH(S) */ COUNT(*)

  FROM SALES_PT S, SALES_ORDER_NO_PT T

 WHERE T.ORDER_DT = TO_DATE('20010101', 'YYYYMMDD')

  AND S.PROD_ID = T.PROD_ID

  AND S.CUST_ID = T.CUST_ID

  AND S.TIME_ID = T.TIME_ID

  AND S.CHANNEL_ID = S.CHANNEL_ID

  AND S.PROMO_ID = T.PROMO_ID     ;

------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name              | Starts | Pstart| Pstop | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                   |      1 |       |       |      1 |00:00:01.22 |   10341 |
|   1 |  SORT AGGREGATE       |                   |      1 |       |       |      1 |00:00:01.22 |   10341 |
|*  2 |   HASH JOIN           |                   |      1 |       |       |    154 |00:00:01.22 |   10341 |
|*  3 |    TABLE ACCESS FULL  | SALES_ORDER_NO_PT |      1 |       |       |     88 |00:00:00.54 |    5457 |
|   4 |    PARTITION RANGE ALL|                   |      1 |     1 |    28 |    918K|00:00:00.31 |    4884 |
|   5 |     TABLE ACCESS FULL | SALES_PT          |     28 |     1 |    28 |    918K|00:00:00.15 |    4884 |
------------------------------------------------------------------------------------------------------------
                                                                                                            
Predicate Information (identified by operation id):                                                         
---------------------------------------------------                                                                                                             
   2 - access("S"."PROD_ID"="T"."PROD_ID" AND "S"."CUST_ID"="T"."CUST_ID" AND "S"."TIME_ID"="T"."TIME_ID" AND
              "S"."PROMO_ID"="T"."PROMO_ID")                                                                
   3 - filter("T"."ORDER_DT"=TO_DATE(' 2001-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))                     

 

 

_bloom_pruning_enabled 파라미터를 false로 놓고 실행하자 SALES_PT 테이블의 모든 파티션을 Access하게 되었다. 이에 따라 성능도 저하된다. PARTIAL PARTITION WISE JOIN Bloom Pruning를 이용하고 있다는 증거이다.

 

 

Partition PairPWJ의 성능개선 원리가 아니다
많은 책에서 PWJ를 설명하기 위해 Partition Pair라는 용어를 사용하고 있다. 하지만 Partition Pair PWJ의 원리가 아니라, 조인 시 선행집합에 PARTITION PRUNING이 발생한 것뿐이다. 아래의 SQL을 실행시켜 보자.

 

 

SELECT /*+ LEADING(T) */ COUNT(*)

  FROM SALES_PT S, SALES_ORDER_PT T

 WHERE T.ORDER_DT = TO_DATE('20010101', 'YYYYMMDD')

  AND T.TIME_ID BETWEEN TO_DATE('20010101', 'YYYYMMDD') AND TO_DATE('20011231', 'YYYYMMDD')

  AND S.PROD_ID = T.PROD_ID

  AND S.CUST_ID = T.CUST_ID

  AND S.TIME_ID = T.TIME_ID

  AND S.CHANNEL_ID = S.CHANNEL_ID

  AND S.PROMO_ID = T.PROMO_ID     ;

-------------------------------------------------------------------------------------------------------------

| Id  | Operation                 | Name           | Starts | Pstart| Pstop | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                |      1 |       |       |      1 |00:00:00.08 |    1984 |
|   1 |  SORT AGGREGATE           |                |      1 |       |       |      1 |00:00:00.08 |    1984 |
|   2 |   PARTITION RANGE ITERATOR|                |      1 |    17 |    20 |    154 |00:00:00.08 |    1984 |
|*  3 |    HASH JOIN              |                |      4 |       |       |    154 |00:00:00.08 |    1984 |
|*  4 |     TABLE ACCESS FULL     | SALES_ORDER_PT |      4 |    17 |    20 |     88 |00:00:00.02 |    1668 |
|*  5 |     TABLE ACCESS FULL     | SALES_PT       |      1 |    17 |    20 |  60608 |00:00:00.02 |     316 |
-------------------------------------------------------------------------------------------------------------
                                                                                                            
Predicate Information (identified by operation id):                                                         
---------------------------------------------------                                                         
   3 - access("S"."TIME_ID"="T"."TIME_ID" AND "S"."PROD_ID"="T"."PROD_ID" AND "S"."CUST_ID"="T"."CUST_ID" AND
              "S"."PROMO_ID"="T"."PROMO_ID")                                                                
   4 - filter(("T"."ORDER_DT"=TO_DATE(' 2001-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')                     
                AND "T"."TIME_ID"<=TO_DATE(' 2001-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))              
   5 - filter("S"."TIME_ID"<=TO_DATE(' 2001-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))                     

 

 

 

T.TIME_ID 조건에 의해서 선행집합에 PARTITION PRUNING이 발생하여 전체 파티션이 아닌 2001년도 파티션 4개만 읽으면 된다. 이에 따라 선행 테이블의 I/O 5947에서 1668로 줄어들었다. 비록 선행집합의 I/O가 줄어들었지만, PWJ의 성능 개선 원리인 '선행집합의 FILTER 조건이 후행집합의 BLOCK I/O를 결정한다'는 변치 않는다. 즉 선행테이블의 두 조건인 T.ORDER_DT = 조건과 T.TIME_ID BETWEEN 조건의 교집합이 후행테이블의 ACCESS 범위가 되는 것이다. 만약 PWJ의 성능개선 원리가 없고 Partition Pair만 존재했다면 후행집합이 2001년에 해당하는 파티션 4개를 모두 읽어야 할 것이다. 하지만 위에서 보는 것처럼 후행집합은 단 하나의 파티션만 Access 한다.

 

물론 아무런 조건 없이 Partition Key 조건만 있다면 아래처럼 Partition Pair가 되기는 한다.

 

 

SELECT /*+ LEADING(T) USE_HASH(S) */ COUNT(*)

  FROM SALES_PT S, SALES_ORDER_PT T

 WHERE T.TIME_ID BETWEEN TO_DATE('20010101', 'YYYYMMDD') AND TO_DATE('20011231', 'YYYYMMDD')

  AND S.PROD_ID = T.PROD_ID

  AND S.CUST_ID = T.CUST_ID

  AND S.TIME_ID = T.TIME_ID

  AND S.CHANNEL_ID = S.CHANNEL_ID

  AND S.PROMO_ID = T.PROMO_ID     ;

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name           | Starts | Pstart| Pstop | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                |      1 |       |       |      1 |00:00:00.61 |    3022 |
|   1 |  SORT AGGREGATE           |                |      1 |       |       |      1 |00:00:00.61 |    3022 |
|   2 |   PARTITION RANGE ITERATOR|                |      1 |    17 |    20 |    464K|00:00:00.58 |    3022 |
|*  3 |    HASH JOIN              |                |      4 |       |       |    464K|00:00:00.50 |    3022 |
|*  4 |     TABLE ACCESS FULL     | SALES_ORDER_PT |      4 |    17 |    20 |    259K|00:00:00.05 |    1668 |
|*  5 |     TABLE ACCESS FULL     | SALES_PT       |      4 |    17 |    20 |    259K|00:00:00.05 |    1354 |
-------------------------------------------------------------------------------------------------------------
                                                                                                            
Predicate Information (identified by operation id):                                                         
---------------------------------------------------                                                         
   3 - access("S"."TIME_ID"="T"."TIME_ID" AND "S"."PROD_ID"="T"."PROD_ID" AND "S"."CUST_ID"="T"."CUST_ID" AND
              "S"."PROMO_ID"="T"."PROMO_ID")                                                                
   4 - filter("T"."TIME_ID"<=TO_DATE(' 2001-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))                     
   5 - filter("S"."TIME_ID"<=TO_DATE(' 2001-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))                     

 

 

 

T.ORDER_DT 조건을 삭제하고 T.TIME_ID 조건만 있으므로 정확히 두 테이블은 Pair이다. 즉 두 테이블은 2001년 파티션을 각각 4개씩 읽었다. PWJ 발생시 BLOCK I/O 관점의 성능개선 원리는 FULL PWJ이냐 아니면 Partial PWJ이냐에 따라 변치 않으며 아래처럼 정의 할 수 있다.

 

1. 선행집합의 파티션 ACCESS 범위는 PARTITION KEY 조건에 의한 Partition Pruning에 의해 결정된다. 하지만 이 기능은 PWJ의 장점이 아니다. 왜냐하면 조인이 아닌 경우도 실행되기 때문이다.


2. 후행집합의 파티션 ACCESS 범위는 선행집합의 모든 FILTER에 의해 결정된다. 이 기능이야 말로 Partition Wise Join의 장점이다. 왜냐하면 조인에 의해서 성능이 향상되기 때문이다 

                                                                                                                                                                                                                                                                                    

 

PWJ 성능향상의 두 번째 원리 

이제 BLOCK I/O관점에서 성능개선 원리가 밝혀졌으므로, PGA 사용량 관점에서 PWJ의 성능개선 원리를 나타내 보자.

 

 

SELECT /*+ LEADING(T)  */ COUNT(*)

  FROM SALES_NO_PT S, SALES_ORDER_NO_PT T

 WHERE S.PROD_ID = T.PROD_ID

   AND S.CUST_ID = T.CUST_ID

   AND S.TIME_ID = T.TIME_ID

   AND S.CHANNEL_ID = S.CHANNEL_ID

   AND S.PROMO_ID = T.PROMO_ID   ;

-----------------------------------------------------------------------------------------------------
| Id  | Operation           | Name              | Starts | A-Rows |   A-Time   | Buffers | Used-Mem |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                   |      1 |      1 |00:00:04.22 |    9898 |          |
|   1 |  SORT AGGREGATE     |                   |      1 |      1 |00:00:04.22 |    9898 |          |
|*  2 |   HASH JOIN         |                   |      1 |   1418K|00:00:04.00 |    9898 |   54M (0)|
|   3 |    TABLE ACCESS FULL| SALES_ORDER_NO_PT |      1 |    918K|00:00:00.40 |    5457 |          |
|   4 |    TABLE ACCESS FULL| SALES_NO_PT       |      1 |    918K|00:00:00.36 |    4441 |          |
-----------------------------------------------------------------------------------------------------
                                                                                                    
Predicate Information (identified by operation id):                                                 
---------------------------------------------------                                                 
   2 - access("S"."PROD_ID"="T"."PROD_ID" AND "S"."CUST_ID"="T"."CUST_ID"                           
               AND "S"."TIME_ID"="T"."TIME_ID" AND "S"."PROMO_ID"="T"."PROMO_ID")                   

  

 

PWJ가 실행되지 않는 경우 PGA 54MB나 소모하였다. 이제 PWJ를 실행시켜 성능이 얼마나 개선되는지 알아보자.

 

  

SELECT /*+ LEADING(T)  */ COUNT(*)

  FROM SALES_PT S, SALES_ORDER_PT T

 WHERE S.PROD_ID = T.PROD_ID

   AND S.CUST_ID = T.CUST_ID

   AND S.TIME_ID = T.TIME_ID

   AND S.CHANNEL_ID = S.CHANNEL_ID

   AND S.PROMO_ID = T.PROMO_ID   ;

--------------------------------------------------------------------------------------------------------------

| Id  | Operation            | Name           | Starts | Pstart| Pstop| A-Rows |   A-Time  | Buffers|Used-Mem |

---------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT     |                |      1 |       |      |      1 |00:00:04.48|   10795|         |

|   1 |  SORT AGGREGATE      |                |      1 |       |      |      1 |00:00:04.48|   10795|         |

|   2 |   PARTITION RANGE ALL|                |      1 |     1 |    28|   1418K|00:00:04.26|   10795|         |

|*  3 |    HASH JOIN         |                |     28 |       |      |   1418K|00:00:03.69|   10795|5008K (0)|

|   4 |     TABLE ACCESS FULL| SALES_ORDER_PT |     28 |     1 |    28|    918K|00:00:00.34|    5947|         |

|   5 |     TABLE ACCESS FULL| SALES_PT       |     16 |     1 |    28|    918K|00:00:00.34|    4848|         |

---------------------------------------------------------------------------------------------------------------

                                                                                                                                                                                                                                                                                                            

Predicate Information (identified by operation id):                             
---------------------------------------------------                             
   3 - access("S"."TIME_ID"="T"."TIME_ID" AND "S"."PROD_ID"="T"."PROD_ID"       
               AND "S"."CUST_ID"="T"."CUST_ID" AND "S"."PROMO_ID"="T"."PROMO_ID")

  

 

PWJ를 실행하니 PGA 사용량이 11배나 줄어들었다. PWJ란 덩치가 큰 테이블 두 개를 조인시켜야 될 때, 작은 여러 개의 파티션으로 쪼개서 각각 조인시킴으로써 조인의 성능을 향상 시키는 것이다. 이렇게 되면 당연히 PGA 사용량이 급격히 줄어들 것이다.

 

 

한가지 주의 사항이 있다. PWJ가 발생하려면 파티션 기준 컬럼으로 양측 집합을 조인해야 한다. 그런데 파티션 Key 조인 컬럼을 아래처럼 가공하게 되면 PWJ가 발생되지 않으므로 주의하기 바란다 

 

  

 

SELECT /*+ LEADING(T) */ COUNT(*)

  FROM SALES_PT S, SALES_ORDER_PT T

 WHERE T.ORDER_DT = TO_DATE('20010101', 'YYYYMMDD')

  AND S.PROD_ID = T.PROD_ID

  AND S.CUST_ID = T.CUST_ID

  AND S.TIME_ID + 1 = T.TIME_ID + 1  --파티션 기준컬럼 가공

  AND S.CHANNEL_ID = S.CHANNEL_ID

  AND S.PROMO_ID = T.PROMO_ID     ;

 

---------------------------------------------------------------------------------------------------------------

| Id  | Operation             | Name           | Starts| Pstart| Pstop|A-Rows |   A-Time   |Buffers |Used-Mem |

---------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT      |                |      1|       |      |     1 |00:00:02.23 |  10831 |         |

|   1 |  SORT AGGREGATE       |                |      1|       |      |     1 |00:00:02.23 |  10831 |         |

|*  2 |   HASH JOIN           |                |      1|       |      |   154 |00:00:02.23 |  10831 |1210K (0)|

|   3 |    PARTITION RANGE ALL|                |      1|     1 |    28|    88 |00:00:00.10 |   5947 |         |

|*  4 |     TABLE ACCESS FULL | SALES_ORDER_PT |     28|     1 |    28|    88 |00:00:00.10 |   5947 |         |

|   5 |    PARTITION RANGE ALL|                |      1|     1 |    28|   918K|00:00:00.70 |   4884 |         |

|   6 |     TABLE ACCESS FULL | SALES_PT       |     28|     1 |    28|   918K|00:00:00.33 |   4884 |         |

---------------------------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):                                                                  

---------------------------------------------------                                                                   

   2 - access("S"."PROD_ID"="T"."PROD_ID" AND "S"."CUST_ID"="T"."CUST_ID" AND                                        

              INTERNAL_FUNCTION("S"."TIME_ID")+1=INTERNAL_FUNCTION("T"."TIME_ID")+1 AND "S"."PROMO_ID"="T"."PROMO_ID")

   4 - filter("T"."ORDER_DT"=TO_DATE(' 2001-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

 

 

 

FULL PWJ를 가능하게 하는 파라미터는 _full_pwise_join_enabled 이며  Default True이다. Partial PWJ를 가능하게 하는 기능은 Bloom Pruning이며 파라미터는 _bloom_pruning_enabled이다. 이 파라미터의 Default값은 True이다.

 

 

결론

PARTITION WISE JOIN을 설명하는데 PARALLEL + PQ_DISTIRBUTE 힌트 조합은 필요 없다PARALLEL Operation을 사용할 때 옵티마이저가 잘못된 분배방식을 사용하여 PARTITION WISE JOIN이 실행되지 못할 수 있다. 이때 사용할 수 있는 힌트가 PQ_DISTIRBUTE이며, 이것은 활용법일 뿐이다.

 

 
PARTITION PAIR라는 용어 때문에 미리 짝지어 놓았다고 상상함으로써, 마치 조인되는 양측 Partition이 항상 Pair하게 I/O를 한다고 잘못 생각하게 만든다. PWJ의 성능상 장점은 선행집합의 FILTER에 의해서 후행집합에 Access할 파티션의 개수가 줄어든다는 것이다. 따라서 I/O PAIR하게 발생되지 않는다. 'PARTITION PAIR로 동작한다'라는 개념은 선행집합에 조건이 하나도 없는 경우이거나 혹은 Partition Key로만 조건이 들어오는 경우뿐이다. 다시 말해 선행집합에 Partition Key조건 이외의 조건이 있다면 PARTITION PAIR를 보장하지 않는다
.   

 

 

PWJ가 실행될 때 성능개선사항 세 가지

첫 번째, 조인 선행집합에 Partition Key 조건이 있으면 Partition Access 범위도 줄어듦으로 BLOCK I/O량도 줄어든다.

 

두 번째, 선행집합의 모든 FILTER(Partition Key 조건을 포함한)에 의해서 후행집합의 Partition Access 범위가 줄어듦으로 BLOCK I/O량이 줄어든다.

 

세 번째, JOIN시 큰 테이블을 상대적으로 작은 파티션으로 쪼개어 각각 조인함으로써 PGA 사용량이 감소된다. 

 

 

첫 번째 개선사항을 정확히 말하면 PWJ의 기능이 아니라 일반적인 Partition Pruning에 의한 성능향상이다. Partition Wise Join의 성능개선은 조인에 의해서 성능이 향상되어야 함으로 두 번째, 세 번째가 진정한 PWJ의 성능개선사항이다.

 

이제 필자의 차례는 끝나고, 여러분의 차례이다. 주위 사람들에게 PWJ의 정의를 다시 알려주기 바란다.

 

Posted by extremedb
,
필자가 7월달에  Parallel Query 의 조인시 Row Distribution 이라는 글을 통하여 Parallel + Join 시에
튜닝방법을 설명 한바 있다.
오늘은 최적의 Parallel Join 을 하기 위하여 또다른 튜닝방법을 제시한다.
필자가 이글을 쓰는 원래의 용도는 사내 DB 컨설턴트 들을 교육시키는데 사용하는 것이다.
그렇기 때문에 어려워도 실망하거나 우울증에 걸리지 말자.
최근 최진실씨 사태등등 해서 심히 걱정된다.^^
Parallel Join Filter 를 설명하려고 하는데 용어설명부터 해야겠다.
왜냐하면 어떤곳에서는 Parallel Join Filter 라고 이야기 하고 또다른 곳에서는 Bloom Filter 라고 하는데
그이유는 알고리즘을 최초로 개발한 사람이 오라클사의 Burton H. Bloom 이라는 사람이고 이는 1970 년의
일이다.
실제로 실행계획상에 Bloom 의 이름을 따서 필터명이 BF0000, BF0001, BF0003 .... 이렇게 생성된다.
어쨋든 이런사유로 인하여 2개의 용어가 혼용되는데 여기서는 Parallel Join Filter (힌트로는 px_join_filter) 만 사용할것이다.
아래는 테이블 생성 스크립트 이다.
테스트를 위하여 2개의 테이블이 필요하다.

제약사항
 Parallel Join Filter 는 10gR2 이상에서 실행가능함.

테스트용 테이블 생성 스크립트

create table emp_1
as
with a as
(select /*+ materialize */ level + 10000000 as empno,
       chr(mod(level,90)) as big_ename, chr(mod(level,90)) as big_addr
 from dual
 connect by level <= 100000)
 select empno, 
           lpad(big_ename, 3000,big_ename) as big_ename ,
           lpad(big_addr, 3000,big_addr)  as big_addr
 from a ;
 
create table emp_2
as
select * from emp_1 ;

EXEC dbms_stats.gather_table_stats(user,'EMP_1');
EXEC dbms_stats.gather_table_stats(user,'EMP_2');


테이블이 생성 되었으므로 테스트 스크립트를 실행시켜보자.

explain plan for
 SELECT /*+ full(t1) full(t2) parallel(t1 8) parallel(t2 8) leading(t1) use_hash(t2) NO_PX_JOIN_FILTER(t2) */
        *
  FROM emp_1 t1,
            emp_2 t2
 WHERE t1.empno = T2.empno
       and t1.BIG_ENAME > '1'  ;

아래 PLAN 을 설명하기전에 일단 TQ(Table queues) 개념을 알아야 한다.
복잡한 plan 같지만 원리를 알고 나면 간단하게 해석 할수 있다.
TQ 는 processes간의 데이터를 주고받는 기능을 한다.
하나의 TQ 는 여러개의 parallel Slave 를 가진다.
아래 PLAN 을 보면 TQ 가 3개(:TQ10000, :TQ10001, TQ10002 ) 생성되어 있다.(파란색 부분)


--------------------------------------------------------------------------------------
| Id  | Operation               | Name     | Cost (%CPU)|    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |          | 12132   (1)|        |      |            |
|   1 |  PX COORDINATOR         |          |            |        |      |            |
|   2 |   PX SEND QC (RANDOM)   | :TQ10002 | 12132   (1)|  Q1,02 | P->S | QC (RAND)  |
|*  3 |    HASH JOIN BUFFERED   |          | 12132   (1)|  Q1,02 | PCWP |            |
|   4 |     PX RECEIVE          |          |  3054   (0)|  Q1,02 | PCWP |            |
|   5 |      PX SEND HASH       | :TQ10000 |  3054   (0)|  Q1,00 | P->P | HASH       |
|   6 |       PX BLOCK ITERATOR |          |  3054   (0)|  Q1,00 | PCWC |            |
|*  7 |        TABLE ACCESS FULL| EMP_1    |  3054   (0)|  Q1,00 | PCWP |            |
|   8 |     PX RECEIVE          |          |  3054   (0)|  Q1,02 | PCWP |            |
|   9 |      PX SEND HASH       | :TQ10001 |  3054   (0)|  Q1,01 | P->P | HASH       |
|  10 |       PX BLOCK ITERATOR |          |  3054   (0)|  Q1,01 | PCWC |            |
|  11 |        TABLE ACCESS FULL| EMP_2    |  3054   (0)|  Q1,01 | PCWP |            |
--------------------------------------------------------------------------------------

각 Id 단위의 설명 :
1. Q1,00 의 slave process 들은 emp_1  테이블을 full scan 하면서 t1.BIG_ENAME > '1'  조건을 FILTER
   하였고 process 간의 통신을 위하여 걸러진 데이터를 Q1,02 에 보낸다.
    (Id 기준으로 5~7 이 여기에 해당된다)
2. Q1,02 의 slave process 들은 1번에서 받은 데이터들을 이용해 hash table 을 만든다.
    (Id 기준으로 3~4 가 여기에 해당된다)
3. Q1,01 의 slave process 들은 emp_1  테이블을 full scan 하고 읽은 데이터를 Q1,02 에 보낸다.
    (Id 기준으로 9~11 가 여기에 해당된다)
4.  Q1,02 의 slave process 들은 3번에서 던진 데이터를 받아서 미리 만들어진 hash 테이블을
     검색하면서 조인작업을 진행하고 결과를 Query Cordinator 에 보낸다.
     (Id 기준으로 2~3 이 여기에 해당된다)
5. Query Cordinator 는 각 TQ 로 부터 데이터를 받아서 취합한후에 결과를 Return 한다.
     (Id 기준으로 0~1 이 여기에 해당된다)

위설명을 도식화 하면 아래그림과 같다.
다만 위의 SQL 대로라면 각 TQ 내의 SALVE 는 8개 여야 하지만 화면관계상 2개로 줄여서 나타 내었다.

사용자 삽입 이미지

위그림을 보면 무언가 비효율적인 것을 발견하게 된다.
Q1,01 의 모든 SLAVE 들은 Q1,02 의 모든 SLAVE 들에게 똑같은 데이터를 던져서 체크한후에 만족하면
조인에 성공하고 그렇지 않으면 조인에 실패하는 프로세스를 가지게 된다.
위쿼리를 예를들면 사번 10000100을  Q1,02 의 SLAVE 가 8개라면 8번 던져서 1/8 확률로 조인에 성공하면
다행이지만 아예조인에 실패할 확률도 있는것이다.
이런 비효율을 없애는 것이 Parallel Join Filter 이다.
Parallel Join Filter 의 개념은 Q1,01(후행테이블의 TQ) 이 Q1,02 에게 데이터를 전달하기전에 불필요한
데이터를 걸러 낸다는 것이다.
이제 parallel join filter 를 적용시켜보자.

explain plan for
 SELECT /*+ full(t1) full(t2) parallel(t1 8) parallel(t2 8) leading(t1) use_hash(t2) PX_JOIN_FILTER(t2) */
        *
  FROM emp_1 t1,
       emp_2 t2
 WHERE t1.empno = T2.empno
       and t1.BIG_ENAME > '1'  ;

필자의 연구결과 t1.ename > '1' 등 t1 의 filter predicate 가 없으면 Parallel Join Filter 는 결코 작동하지 않는다.
그럴때는 t1.empno > 0 등의 결과값의 영향을 끼치지 않는 filter 조건을 주는 트릭을 생각할수 있다.
또하나의 Tip 은 PX_JOIN_FILTER 사용시 후행테이블을 사용하여야 한다는것이다.
왜냐하면 아래의 PLAN 을 보면 Filter 의 생성은 t1 에서 하지만(id 가 4번) 사용은 t2 쪽(id 11번)에서
하기때문에 PX_JOIN_FILTER(t1) 을 주면 절대 filter operation 이 생기지 않는다.

---------------------------------------------------------------------------------------
| Id  | Operation                | Name     | Cost (%CPU)|    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |          | 12132   (1)|        |      |            |
|   1 |  PX COORDINATOR          |          |            |        |      |            |
|   2 |   PX SEND QC (RANDOM)    | :TQ10002 | 12132   (1)|  Q1,02 | P->S | QC (RAND)  |
|*  3 |    HASH JOIN BUFFERED    |          | 12132   (1)|  Q1,02 | PCWP |            |
|   4 |     PX JOIN FILTER CREATE| :BF0000  |  3054   (0)|  Q1,02 | PCWP |            |
|   5 |      PX RECEIVE          |          |  3054   (0)|  Q1,02 | PCWP |            |
|   6 |       PX SEND HASH       | :TQ10000 |  3054   (0)|  Q1,00 | P->P | HASH       |
|   7 |        PX BLOCK ITERATOR |          |  3054   (0)|  Q1,00 | PCWC |            |
|*  8 |         TABLE ACCESS FULL| EMP_1    |  3054   (0)|  Q1,00 | PCWP |            |
|   9 |     PX RECEIVE           |          |  3054   (0)|  Q1,02 | PCWP |            |
|  10 |      PX SEND HASH        | :TQ10001 |  3054   (0)|  Q1,01 | P->P | HASH       |
|  11 |       PX JOIN FILTER USE | :BF0000  |  3054   (0)|  Q1,01 | PCWP |            |
|  12 |        PX BLOCK ITERATOR |          |  3054   (0)|  Q1,01 | PCWC |            |
|  13 |         TABLE ACCESS FULL| EMP_2    |  3054   (0)|  Q1,01 | PCWP |            |
---------------------------------------------------------------------------------------

위 plan 은 원래의 PLAN(filter 적용전 plan) 에서 parallel join filter 부분만이 추가 되었다.(파란색 부분)
1. id 4 에서 parallel Join filter 를 생성(create) 하였고 filter 명은 :BF0000 이다.
2. id 11 에서 생성된 :BF0000 filter 를 사용하였다.

주의사항은  parallel Join filter 를 무조건 사용하지말라는 것이다.
걸러지는 데이터가 별로 없을경우 빨라지지도 않을 뿐더러  filter 부하가 더클수 있기 때문이다.
다음의 2가지 경우에  parallel Join filter 를 사용하여야 한다.
1. 많은양의 데이터가 조인에 실패하는경우
2. 1번을 만족하면서 RAC 에서 multi-node 로 Parallel Query 를 실행한경우.
    이경우는대부분 DOP(Degree Of Parallelism)가 클때 발생하며 추가적인 Network I/O 가 발생하므로
    parallel join filter 를 적용할경우 획기적인 성능향상을 기대할수 있다.

parallel Join filter에 의해서 filter 된 데이터를 보려면 아래와 같이 v$sql_join_filter 뷰를 사용하면된다.

select filtered, probed, proved - filtered as sent
   from  v$sql_join_filter
where qc_session_id = sys_context('userenv', 'sid');

결론 :
Parallel Join distribution 과 Parallel join filter 을 적절히 이용하면 최적화된 Parallel Join Query를 만들수 있다.
다시한번 말하지만 꼭필요한 경우에만 이런종류의 힌트를 사용하여야 한다.
 
편집후기 :만약 parallel Join filter 로직에 관심이 있어서 직접 구현하려면 아래의 1번 문서를 참조하기 바란다.

Reference :
1.Bloom Filters ( Cristian Anatognini )
2.Oracle Corp Manual (Data Warehousing Guide 11g)

Posted by extremedb
,

대용량 Parallel 쿼리 에서 조인을 사용할 경우 성능이 저하되는 경우가 많이 있다.
이경우의 원인은 여러가지가 있다.
원인 중에서 가장 치명적인 것은 잘못된 Row Distribution (Row 의 분배방법) 에 있다.
옵티마이져의 잘못된 Row Distribution 을 피하기 위하여 원리및 사용방법 그리고 최후의 방법으로 힌트를 통한 잘못된 Row Distribution 을 피하기 등에 대하여 알아본다.

필자가 이주제를 선택한 이유는 예전에 필자가 그랬듯이 이해하기가 힘들고 DBA 및 튜너라고 할지라도 모르는 사람들이 많이 있기 때문이다.
그렇기 때문에 최대한 이해하기 쉽게 설명 하였다
.  

1. Row Distribution Method

Parallel 옵션을 사용한 Select 에서 조인시에 Row 의 분배방법에는 4 가지가 있다.
한가지씩 알아보자

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 를 분배하는 방식이다.
                  이분배방식은 Partition Wise Join 과 관계가 있다.
4).None : 이미 조인컬럼기준으로 파티션 된 테이블은 Row 가 파티션기준으로 자동으로 분배되거나
              Broadcast 방식일 경우 분배를 받는쪽 테이블의 Row 는 따로 분배가 필요 없으므로
              None 으로 표현된다.




2.조인시 Row Distribution 의 Combination

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

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) PARTITION, NONE : Outer 테이블을 조인된 컬럼기준으로 Partition을 하여 Row 를 분배하며
                                Partition Wise 조인을 한다. 
5) NONE, PARTITION : Inner 테이블을 조인된 컬럼기준으로 Partition을 하여 Row 를 분배하며
                                Partition Wise 조인을 한다. 
6) NONE, NONE : 조인이되는 양측의 테이블이 이미 조인컬럼 기준으로 파티션이 되어 있을때 따로 분배가
                         필요없으므로 이런 Combination 이 발생한다.(양측 테이블이 파티션 기준으로 분배된다.)


                           
3. PQ_DISTRIBUTE 힌트의 사용

다시한번 말하지만 파티션 분배방식을 제외하면 양측 테이블의 Size 가 비슷한 경우는 분배방식은 Hash, Hash 로 풀려야 하고 코드성 테이블과 같이 소형 테이블과 대형테이블의 조인인경우는 Broadcast, None 으로 풀려야 한다.
 
그럼에도 불구하고 Optimizer 가 잘못된 분배방식의 Combination 을 선택하였다면 10중 8, 9 는 통계정보를 제대로 생성해주면 된다.
왜냐하면 파티션 분배방식을 제외하고 Broadcast 나 Hash 등의 분배방식을 선택할떄 Row 수 및 평균 Row 의 길이 등이 결정적인 영향을 끼치기 때문이다.
 
하지만 Temp 성 테이블이나 Global temp Table 등을 사용하면 통계정보가 아예 없다.
또한 통계정보가 있어도 Optimizer 잘못된선택을 할수도 있다.
이때 사용할수 있는 힌트중의 하나가 PQ_DISTRIBUE 이다.
아래의 힌트 옵션을 보고 실제 SQL 을 살펴보자.

§
/*+ PQ_DISTRIBUTE(inner 테이블명 outer_distribution, inner_distribution) */


위의 힌트에서 보듯이 Inner 테이블명이나 Alias 를 먼저적고 Row 분배방식의 Combination 을 작성하면 된다.

예제1)

SELECT /*+ORDERED PARALLEL(r 4) PARALLEL(s 4) PQ_DISTRIBUTE(s HASH, HASH) USE_HASH (s)*/ column_list
FROM r,s
WHERE r.c=s.c;

예제1) 은 Outer Table(R) 과 Inner Table(S) 의 SIZE 가 비슷하므로 각각 Hash 분배방식으로 힌트를 사용하였다.


예제2)

SELECT /*+ORDERED PARALLEL(a 4) PARALLEL(b 4) PQ_DISTRIBUTE(b BROADCAST, NONE) USE_HASH (b) */ column_list
FROM a,b
WHERE a.c = b.c;

예제2)는 Outer Table(a) 가 Inner Table(b) 보다 훨씬 적으므로 BROADCAST, NONE 방식을 취하도록 힌트를 사용하였다.
다시말하면 b 테이블 scan 시에 발생하는 Parallel 의 각각의 Slave 마다  Brodcast 된 a 테이블과의 조인을 동시에 하겠다는 뜻이다.


예제3)
CREATE TABLE dept2 AS SELECT * FROM departments;

ALTER TABLE dept2 PARALLEL 2;

CREATE TABLE emp_comp PARTITION BY RANGE(hire_date)
SUBPARTITION BY HASH(department_id) SUBPARTITIONS 3
(
PARTITION emp_p1 VALUES LESS THAN (TO_DATE(’1-JAN-1992’,’DD-MON-YYYY’)),
PARTITION emp_p2 VALUES LESS THAN (TO_DATE(’1-JAN-1994’,’DD-MON-YYYY’)),
PARTITION emp_p3 VALUES LESS THAN (TO_DATE(’1-JAN-1996’,’DD-MON-YYYY’)),
PARTITION emp_p4 VALUES LESS THAN (TO_DATE(’1-JAN-1998’,’DD-MON-YYYY’)),
PARTITION emp_p5 VALUES LESS THAN (TO_DATE(’1-JAN-2001’,’DD-MON-YYYY’))
)
AS SELECT * FROM employees;

ALTER TABLE emp_comp PARALLEL 2;

EXPLAIN PLAN FOR
SELECT /*+ PQ_DISTRIBUTE(d NONE PARTITION) ORDERED */ e.last_name,
            --> dept2 를 department_id 로 dynamic 파티션을 하여 row 를 분배하겠다는 의미임.
d.department_name
FROM emp_comp e, dept2 d
WHERE e.department_id = d.department_id;

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

사용자 삽입 이미지


예제3)은 dept2 테이블을 조인기준컬럼인 department_id 로 Dynamic Partition 하여 ROW 를 분배한다.
dept2 테이블은 파티션이 되어 있지않으므로 Partial Partioin Wise Join 이 발생 하였다.
Plan 상의 분배방식도 힌트에서 의도한대로 Part(key) 로 나타났다


예제4)
CREATE TABLE dept_hash
PARTITION BY HASH(department_id)
PARTITIONS 3
PARALLEL 2
AS SELECT * FROM departments;

EXPLAIN PLAN FOR
SELECT /*+ PQ_DISTRIBUTE(e NONE NONE) ORDERED */ e.last_name,
            --> 이미 양측 테이블이 파티션이 되어있으므로 분배방식이 따로 필요가 없으나
                  (Hash, hash) 나 (broadcast, none) 등으로 풀리는 것을 방지하는 차원에서 힌트를 사용함.

d.department_name
FROM emp_comp e, dept_hash d
WHERE e.department_id = d.department_id;

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

사용자 삽입 이미지


예제4)는 양측 테이블이 모두 조인기준컬럼으로 파티션 되어 있기 때문에 Full Partition Wise Join 되었으며 분배방식에도 아무것도 나타나지 않았다( 아무것도 나타나지 않으면 None 임)
당연한 이야기 이지만 emp_comp 테이블의 department_id 컬럼은 Sub Partiotion 이다.
Sub Partiion 도 위의 Plan 처럼 Full Partition Wise Join 이 가능하다.

4.결론

Row 분배방식, 분배방식의 Combination , Optimizer 가 잘못된 분배방식을 선택할 경우를 위한 PQ_DISTRIBUTE 힌트의 사용등을 알아보았다.
잘못된 Row Distribution 을 피하기 위한 목적 뿐만 아니라  Parallel Operation 을 이해하기 위해서는 반드시 알야야 하니 다시한번 꼼꼼히 살펴보기 바란다.
Posted by extremedb
,