부제: 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의 정의를 다시 알려주기 바란다.

 

저작자 표시 비영리 변경 금지
신고
크리에이티브 커먼즈 라이선스
Creative Commons License
Posted by extremedb

댓글을 달아 주세요

  1. 라튜니 2013.05.22 03:10 신고  댓글주소  수정/삭제  댓글쓰기

    바쁘신 와중에도 Partition Wise Join 에 관한 글을 포스팅 해주셨네요. 기존에 제가 잘못 알고 있었던 부분에 대해 명확하게 이해가 되었네요. 동규님의 글은 정말 도움이 많이 되네요. 감사합니다~! 참, 테이블 생성 스크립트 다운로드 링크가 안되어 있네요. 수정 부탁드립니다.

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2013.05.22 11:28 신고  댓글주소  수정/삭제

      라튜니님 안녕하세요.
      도움이 되었다면 다행입니다.
      테이블 생성 스크립트 다운 받을 수 있게 되었습니다.
      감사합니다.

  2. feelie 2013.05.24 11:26 신고  댓글주소  수정/삭제  댓글쓰기

    Partition Wise Join 을 보면서 PQ_Distribute, Bloom Filter, Partition Pruning 한번 더 정리 했습니다..
    이전 포스팅을 보면서 이해했다고 생각했는데.. 다시 보니 생소하네요..
    좋은 내용 감사합니다..

  3. 김시연 2013.05.30 17:08 신고  댓글주소  수정/삭제  댓글쓰기

    2년이란 시간이 지나고 보니 금방이네요? 이렇게 온라인상으로나마 소식 전해듣게되서 반갑습니다. 늘 건강하세요~!

  4. Favicon of http://dataartist.tistory.com BlogIcon 마도전사 2013.06.02 21:48 신고  댓글주소  수정/삭제  댓글쓰기

    오수석님. 접니다. ㅋ 글 매우 잘 읽었습니다.
    바쁘신데도 대단하신 것 같아요.
    도움 많이 되었어요!! ^^

  5. 열공 2013.07.02 15:36 신고  댓글주소  수정/삭제  댓글쓰기

    좋은 내용 감사합니다~
    조건절에 S.CHANNEL_ID = S.CHANNEL_ID 오타 같아요~

  6. 겨울봄 2014.07.16 04:53 신고  댓글주소  수정/삭제  댓글쓰기

    안녕하세요.
    날씨가 무더운데 건강하게 생활하시는지요.
    티스토리라는 곳을 알게되었읍니다.
    티스토리와 함께 일상을 같이 보내고 싶은데 티스토리는 초대장이 있어야 가능하더군요.
    소소한 일상을 네이브블로그운영 해보았답니다 소통이 조금은 뭔지 알것 같더군요~
    초대장이 도착했다는 좋은소식을 희망합니다.
    아름다운 행복한 하루 보내세요^^.

    yousanta@naver.com

  7. EgorBak 2015.04.02 10:15 신고  댓글주소  수정/삭제  댓글쓰기

    Покупаю старые (от 0.5 года) аккаунты адвордс за хорошую цену.
    Желательно, чтобы на них были расход-доход за это время хотя бы
    какой-то.

    Если знаете, где купить можно - также пишите. Заплачу за контакты и
    совершенную сделку. Можно договориться на долгосрочный привод
    продавцов аккаунтов.

    icq - 657838616
    email - egortivosin@yandex.ru

    Tags: продам аккаунт adwords, куплю аккаунт adwords, купить adwords,
    продаю adwords, покупаю адвордс аккаунты, куплю адвордс аки.

  8. LenchikHinE 2015.04.13 00:07 신고  댓글주소  수정/삭제  댓글쓰기

    Есть тут кто занимается теннисом?

  9. LenchikHinE 2015.04.13 09:37 신고  댓글주소  수정/삭제  댓글쓰기

    Есть тут кто занимается теннисом?

  10. Robertboor 2015.05.29 17:16 신고  댓글주소  수정/삭제  댓글쓰기

    отличный веб ресурс http://www.ifmo.ru/ru/viewnews/4728/

  11. Josephdous 2015.05.29 17:41 신고  댓글주소  수정/삭제  댓글쓰기

    добрый ресурс http://newspaper.ifmo.ru/file/book_mpop/part_20.pdf

  12. VirgilEl 2015.05.30 15:28 신고  댓글주소  수정/삭제  댓글쓰기

    Смотрите Новости ДOM 2 ( dom 2 ), Холостяк и др.Самые Интересные Шоу .

    Дом 2 Дневной выпуск Утро 29 мая 4036
    https://youtu.be/LCgODp17iXs

    Дом 2 вечерний выпуск 29 мая 2015
    https://youtu.be/IVUyGuIc5mY

    Дом 2Ночной выпуск 29 мая 2015 после заката
    https://youtu.be/TAdJFfo1KAI

    Дом 2 30.05.2015 АНОНС выпуск 4036
    https://youtu.be/EIlsTRJdPuo


    Дом 2 Дневной выпуск 30 мая 2015 Lite, лайт 4037-й день Эфир 30.05.2015
    http://youtu.be/_jli3ZCYoIQ

  13. MaxikuCof 2015.06.26 04:06 신고  댓글주소  수정/삭제  댓글쓰기

    Какой это язык, кто знает?
    Puelito lamedrosa ban kvestara non citatos. Fistala miona desta les bucles de sutero.La truera[/url

  14. MaxCravcut 2015.08.22 05:34 신고  댓글주소  수정/삭제  댓글쓰기

    Those who are into and use any possible financial bonuses to hedge the risk of losing his capital , the latest shares Welcome Bonus and Contributed Capital Bonus .


    XM
    http://clicks.pipaffiliates.com/afs/come.php?id=49&cid=59860&ctgid=17&atype=1

    Instaforex
    https://www.instaforex.com/ru/forex_promo/bonus55/?x=JWIJ

    All brokers are regulated and licensed , client account segregated and insured.
    if you still have no idea about the way it operates in short it looks like this :
    A real account is created
    You deposit 1000 USD for example
    Based on your deposit the broker offers a bonus of 500 USD for example
    500 USD represent your safety net if losing
    This principle makes you self-confident during trading and doesn’t allow you to make mistakes due to psychological pressure.

일반적인 의견
흔히 Sort Merge Join에 대해 다음과 같이 이야기 한다. “조인에 참여하는 양측 집합에 Sort가 발생하므로 대용량 집합간의 조인에는 불리하다. 그러나 조인 되는 양쪽 집합에 적절한 인덱스가 있다면 Sort가 발생되지 않으므로 성능이 좋다.일견 일리가 있는 말이다. 하지만 이 정도는 튜닝에 입문하는 단계에서 언급되는 정도일 뿐이다. 2단을 외운다고 해서 구구단을 모두 안다고 할 수는 없다. 튜닝에 입문하는 사람과는 반대로 경력이 있는 사람들은 좀더 구체적인 사실들을 알고 있다. Sort Merge Join과 관련된 튜닝을 많이 해보고, Merge Join에 대해 여기저기 튜닝서적들을 탐독한다. 그 결과 다음과 같은 섣부른 결론을 내리는 사람이 많이 있다.

 

1. 양쪽 집합이 Full Table Scan을 사용하면 조인순서에 상관없이 일량이 동일하므로 처리시간도 동일하다.

2. 조인순서에 상관없이 Sort량은 동일하다.

3. 부분범위처리가 안 된다.

4. Full Scan이 발생하면 인덱스를 사용할 수 없으므로 항상 Sort 작업을 동반한다.

5. Sort Merge Join 대신 Cartesian Merge Join이 나오면 조인조건이 빠진 악성 SQL이다.

6. 조인컬럼 기준으로 Sort되므로 Order by절과 조인 컬럼이 일치해야만 Sort가 발생하지 않는다.

 

완벽하지 않거나 잘못된 결론

혹시 당신도 Sort Merge Join에 대해서 1~6번이 옳다고 생각하는가? 위의 List는 깊이 고민해 보지 않고 내린 결론이다. 물론 1~6 번이 옳은 경우도 있다. 하지만 그것은 잘못된 것 혹은 완벽하지 않은 결론이다. 왜냐하면 간단한 테스트로 1~6번이 잘못된 개념임을 증명하거나, 1~6번에 해당하지 않는 경우를 증명할 수 있기 때문이다. 지금부터 시작해 보자.

 

먼저 실습용 테이블을 생성한다.

<환경: Oracle 11.2.0.1>

 

CREATE TABLE SALES_T AS SELECT * FROM SALES;

 

 

1. 조인순서에 상관없이 처리시간이 동일할까?

 

ALTER SYSTEM FLUSH BUFFER_CACHE; 

  

SELECT /*+ leading(s) full(p) full(s) use_merge(p) */

       s.*, p.prod_id

  FROM sales_t s, products p

 WHERE p.prod_name = 'CD-R with Jewel Cases, pACK OF 12'

   AND p.prod_id = s.prod_id ;

 

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

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

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

|   0 | SELECT STATEMENT    |          |      1 |  22189 |00:00:07.74 |    4447 |   4439 |          |

|   1 |  MERGE JOIN         |          |      1 |  22189 |00:00:07.74 |    4447 |   4439 |          |

|   2 |   SORT JOIN         |          |      1 |    590K|00:00:04.75 |    4440 |   4433 |   43M (0)|

|   3 |    TABLE ACCESS FULL| SALES_T  |      1 |    918K|00:00:01.23 |    4440 |   4433 |          |

|*  4 |   SORT JOIN         |          |    590K|  22189 |00:00:01.29 |       7 |      6 | 2048  (0)|

|*  5 |    TABLE ACCESS FULL| PRODUCTS |      1 |      1 |00:00:00.01 |       7 |      6 |          |

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

 

Predicate Information (identified by operation id):

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

   4 - access("P"."PROD_ID"="S"."PROD_ID")

       filter("P"."PROD_ID"="S"."PROD_ID")

   5 - filter("P"."PROD_NAME"='CD-R with Jewel Cases, pACK OF 12')

 

Sales_t 집합을 선행으로 실행하니 Scan한 블록수는 4447 이며 Sort량은 43M + 2048 이다. 그리고 처리시간은 7 74이다. 그리고 조인시도(Merge)횟수는 59만 번이다. 그러면 이제 조인 순서만 바꿔보자. 과연 처리시간이 동일 할까?

 

ALTER SYSTEM FLUSH BUFFER_CACHE; 

 

SELECT /*+ leading(p) full(p) full(s) use_merge(s) */

       s.*, p.prod_id

  FROM sales_t s, products p

 WHERE p.prod_name = 'CD-R with Jewel Cases, pACK OF 12'

   AND p.prod_id = s.prod_id ;

 

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

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

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

|   0 | SELECT STATEMENT    |          |      1 |  22189 |00:00:02.52 |    4447 |   4439 |          |

|   1 |  MERGE JOIN         |          |      1 |  22189 |00:00:02.52 |    4447 |   4439 |          |

|   2 |   SORT JOIN         |          |      1 |      1 |00:00:00.03 |       7 |      6 | 2048  (0)|

|*  3 |    TABLE ACCESS FULL| PRODUCTS |      1 |      1 |00:00:00.03 |       7 |      6 |          |

|*  4 |   SORT JOIN         |          |      1 |  22189 |00:00:02.44 |    4440 |   4433 |   43M (0)|

|   5 |    TABLE ACCESS FULL| SALES_T  |      1 |    918K|00:00:01.25 |    4440 |   4433 |          |

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

 

Predicate Information (identified by operation id):

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

   3 - filter("P"."PROD_NAME"='CD-R with Jewel Cases, pACK OF 12')

   4 - access("P"."PROD_ID"="S"."PROD_ID")

       filter("P"."PROD_ID"="S"."PROD_ID")

 

일량이 같은데 수행시간은 세배이상 빠르다. 그 이유는?

조인 순서를 바꾸어 Products를 선행집합으로 Sort Merge Join을 해도 Scan한 블록 수와 Sort량은 완전히 같다. 일량이 같으므로 처리시간도 같을 것으로 생각해서는 안 된다. 처리시간이 2 52로 무려 세배이상 빨라졌다. 그 이유는 조인시도(Merge)횟수가 단 한번이기 때문이다. 이와는 대조적으로 Sales_t가 선행집합인 경우는 Merge 횟수가 무려 59만 번에 이르므로 성능이 느릴 수 밖에 없는 것이다. 그러므로 다음과 같은 결론을 낼 수 있다.

 

양쪽 집합이 Full Table Scan을 사용하면 조인순서에 상관없이 일량이 동일하므로 처리시간도 동일하다 ( X )

-->일량은 동일하더라도 Merge 횟수가 달라지면 처리시간이 달라진다 ( O )

 

 

2. 조인순서에 상관없이 Sort량이 동일할까?

 

실습을 위해 테이블을 두 개 만든다.

 

CREATE TABLE TAB1 NOLOGGING AS

SELECT ROWNUM AS SALES_NO, A.* FROM SALES A;

 

CREATE INDEX IDX_TAB1_01 ON TAB1 (PROD_ID, SALES_NO); 

 

CREATE TABLE TAB2 NOLOGGING AS

SELECT A.*, B.SEQ

  FROM TAB1 A,

       (SELECT LEVEL AS SEQ

          FROM DUAL

       CONNECT BY LEVEL <= 5) B  ;

    

CREATE INDEX IDX_TAB2_01 ON TAB2 (PROD_ID, SALES_NO, SEQ);

 

SELECT /*+ LEADING(A) INDEX(A)  INDEX(B) USE_MERGE(B) */

       B.*, A.CHANNEL_ID AS  CHAN

  FROM TAB1 a, TAB2 b

 WHERE A.SALES_NO = B.SALES_NO

   AND A.PROD_ID = 22

   AND B.PROD_ID = 22  ;

 

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

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

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

|   0 | SELECT STATEMENT              |             |      1 |  17205 |00:00:00.22 |   17355 |          |

|   1 |  MERGE JOIN                   |             |      1 |  17205 |00:00:00.22 |   17355 |          |

|   2 |   TABLE ACCESS BY INDEX ROWID | TAB1        |      1 |   3441 |00:00:00.01 |      94 |          |

|*  3 |    INDEX RANGE SCAN           | IDX_TAB1_01 |      1 |   3441 |00:00:00.01 |      14 |          |

|*  4 |   SORT JOIN                   |             |   3441 |  17205 |00:00:00.16 |   17261 | 1054K (0)|

|   5 |    TABLE ACCESS BY INDEX ROWID| TAB2        |      1 |  17205 |00:00:00.11 |   17261 |          |

|*  6 |     INDEX RANGE SCAN          | IDX_TAB2_01 |      1 |  17205 |00:00:00.02 |      56 |          |

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

 

Predicate Information (identified by operation id):

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

   3 - access("A"."PROD_ID"=22)

   4 - access("A"."SALES_NO"="B"."SALES_NO")

       filter("A"."SALES_NO"="B"."SALES_NO")

   6 - access("B"."PROD_ID"=22)

 

건수가 적은 tab1을 선행집합으로 하여 실행하였다. 선행집합은 적절한 인덱스 덕분으로 Sort가 발생하지 않았다. 하지만 후행집합 tab2 17205건을 Sort하여 1054K PGA를 사용하였다. 이제 선행집합을 바꿔서 실행하여 Sort량이 같은지 검증해보자.

 

SELECT /*+ LEADING(B) INDEX(A)  INDEX(B) USE_MERGE(A) */

       B.*, A.CHANNEL_ID AS  CHAN

  FROM TAB1 a, TAB2 b

 WHERE A.SALES_NO = B.SALES_NO

   AND A.PROD_ID = 22

   AND B.PROD_ID = 22  ;

 

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

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

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

|   0 | SELECT STATEMENT              |             |      1 |  17205 |00:00:00.34 |   17354 |          |

|   1 |  MERGE JOIN                   |             |      1 |  17205 |00:00:00.34 |   17354 |          |

|   2 |   TABLE ACCESS BY INDEX ROWID | TAB2        |      1 |  17205 |00:00:00.15 |   17263 |          |

|*  3 |    INDEX RANGE SCAN           | IDX_TAB2_01 |      1 |  17205 |00:00:00.04 |      58 |          |

|*  4 |   SORT JOIN                   |             |  17205 |  17205 |00:00:00.08 |      91 |83968  (0)|

|   5 |    TABLE ACCESS BY INDEX ROWID| TAB1        |      1 |   3441 |00:00:00.01 |      91 |          |

|*  6 |     INDEX RANGE SCAN          | IDX_TAB1_01 |      1 |   3441 |00:00:00.01 |      12 |          |

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

 

Predicate Information (identified by operation id):

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

   3 - access("B"."PROD_ID"=22)

   4 - access("A"."SALES_NO"="B"."SALES_NO")

       filter("A"."SALES_NO"="B"."SALES_NO")

   6 - access("A"."PROD_ID"=22)

 

Sort Merge Join도 조인순서가 중요하다

선행집합을 tab2 로 바꾸어 실행하였다. 이번에는 tab1 3441건만 Sort하였으므로 PGA 83K 만 사용하였다. Tab2 Sort하는 경우는 PGA 1054K 나 사용하였으므로 12배 이상 차이가 난다. 다시 말해, 적절한 인덱스가 존재하는 경우는 Filtering 된 건수가 적은 집합을 후행집합으로 하는 것이 Sort의 부하를 줄일 수 있다.

 

하나는 안다. 하지만 둘은?

Sort의 부하를 12배 이상 줄였으므로 만족해서는 안 된다. Sort량이 극적으로 줄어도 속도는 오히려 떨어질 수 있다. 위의 실행계획 두 가지의 처리속도를 비교해보면 Sort량이 많은 것이 오히려 더 빠르다. 그 이유는 건수가 적은 집합을 후행으로 놓으면 선행집합이 건수가 많아지므로 Merge 시도횟수가 증가하기 때문이다. Sort의 부하와 Merge 횟수를 모두 고려해야 최적의 튜닝을 할 수 있다.

 

튜닝의 목적이 무엇인가?

예를 들면, 배치 SQL을 튜닝할 때 응답시간을 단축시키려면 작은 집합을 선행집합으로 하여 Merge 횟수를 줄여야 한다. 이와는 반대로 Sort의 부하가 커서 multi-pass가 나오는 경우라면 작은 집합을 후행집합으로 하여 Sort의 부하를 줄여야 한다. , 응답시간 단축이냐 아니면 Sort량을 감소가 목적이냐에 따라서 튜닝방법이 달라져야 한다.

 

이 테스트를 통하여 다음을 증명해 보았다.

 

조인순서에 상관없이 Sort량이 동일하다 ( X )

-->적절한 인덱스를 사용하는 경우, Sort량은 Join 순서에 의해 달라진다 ( O )

 

참고사항: 첫 번째 조건이 참이 되려면 두 가지 전제가 필요하다. 전체범위를 처리해야 하며, 양측집합이 Full Scan인 경우에 해당한다.

 

 

3. Merge Join은 부분범위처리가 안 될까?

 

SELECT *

  FROM (SELECT /*+ LEADING(B) INDEX(A)  INDEX(B) USE_MERGE(A) */

               B.*, A.CHANNEL_ID AS  CHAN

          FROM TAB1 a, TAB2 b

         WHERE A.SALES_NO = B.SALES_NO

           AND A.PROD_ID = 22

           AND B.PROD_ID = 22

         ORDER BY B.PROD_ID, B.SALES_NO, B.SEQ )

WHERE ROWNUM <= 1 ;

 

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

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

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

|   0 | SELECT STATEMENT                |             |      1 |      1 |00:00:00.02 |      95 |          |

|*  1 |  COUNT STOPKEY                  |             |      1 |      1 |00:00:00.02 |      95 |          |

|   2 |   VIEW                          |             |      1 |      1 |00:00:00.02 |      95 |          |

|   3 |    MERGE JOIN                   |             |      1 |      1 |00:00:00.02 |      95 |          |

|   4 |     TABLE ACCESS BY INDEX ROWID | TAB2        |      1 |      1 |00:00:00.01 |       4 |          |

|*  5 |      INDEX RANGE SCAN           | IDX_TAB2_01 |      1 |      1 |00:00:00.01 |       3 |          |

|*  6 |     SORT JOIN                   |             |      1 |      1 |00:00:00.02 |      91 |83968  (0)|

|   7 |      TABLE ACCESS BY INDEX ROWID| TAB1        |      1 |   3441 |00:00:00.02 |      91 |          |

|*  8 |       INDEX RANGE SCAN          | IDX_TAB1_01 |      1 |   3441 |00:00:00.01 |      12 |          |

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

 

Predicate Information (identified by operation id):

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

   1 - filter(ROWNUM<=1)

   5 - access("B"."PROD_ID"=22)

   6 - access("A"."SALES_NO"="B"."SALES_NO")

       filter("A"."SALES_NO"="B"."SALES_NO")

   8 - access("A"."PROD_ID"=22)

 

 

단 한 건만 읽는다

인라인뷰 외부에서 ROWNUM <= 1 조건을 사용하자, 후행집합은 전체 건을 읽었지만 선행집합은 정확히 한 건만 읽었다. 선행집합이 전체범위로 처리되었다면 17205건을 읽었을 것이다. 즉 선행집합에 대해서는 완벽히 부분범위로 처리된다. 만약 후행집합이 몇 건 안 된다면 부분범위처리의 효율은 더욱 높아진다.

이제 Rownum 조건을 10, 100으로 변경해 가면서 실행계획을 관찰 해보자.

 

ROWNUM <= 10 조건일 때의 실행계획  

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

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

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

|   0 | SELECT STATEMENT                |             |      1 |     10 |00:00:00.02 |     105 |          |

|*  1 |  COUNT STOPKEY                  |             |      1 |     10 |00:00:00.02 |     105 |          |

|   2 |   VIEW                          |             |      1 |     10 |00:00:00.02 |     105 |          |

|   3 |    MERGE JOIN                   |             |      1 |     10 |00:00:00.02 |     105 |          |

|   4 |     TABLE ACCESS BY INDEX ROWID | TAB2        |      1 |     10 |00:00:00.01 |      14 |          |

|*  5 |      INDEX RANGE SCAN           | IDX_TAB2_01 |      1 |     10 |00:00:00.01 |       4 |          |

|*  6 |     SORT JOIN                   |             |     10 |     10 |00:00:00.02 |      91 |83968  (0)|

|   7 |      TABLE ACCESS BY INDEX ROWID| TAB1        |      1 |   3441 |00:00:00.02 |      91 |          |

|*  8 |       INDEX RANGE SCAN          | IDX_TAB1_01 |      1 |   3441 |00:00:00.01 |      12 |          |

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

 

ROWNUM <= 100 조건일 때의 실행계획 

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

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

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

|   0 | SELECT STATEMENT                |             |      1 |    100 |00:00:00.03 |     195 |          |

|*  1 |  COUNT STOPKEY                  |             |      1 |    100 |00:00:00.03 |     195 |          |

|   2 |   VIEW                          |             |      1 |    100 |00:00:00.03 |     195 |          |

|   3 |    MERGE JOIN                   |             |      1 |    100 |00:00:00.03 |     195 |          |

|   4 |     TABLE ACCESS BY INDEX ROWID | TAB2        |      1 |    100 |00:00:00.01 |     104 |          |

|*  5 |      INDEX RANGE SCAN           | IDX_TAB2_01 |      1 |    100 |00:00:00.01 |       4 |          |

|*  6 |     SORT JOIN                   |             |    100 |    100 |00:00:00.03 |      91 |83968  (0)|

|   7 |      TABLE ACCESS BY INDEX ROWID| TAB1        |      1 |   3441 |00:00:00.02 |      91 |          |

|*  8 |       INDEX RANGE SCAN          | IDX_TAB1_01 |      1 |   3441 |00:00:00.01 |      12 |          |

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

 

Rownum 조건을 10, 100으로 변경하자 tab2를 정확히 10, 100건만 읽는다. 도대체 누가 “Sort Merge Join은 부분범위처리가 안 된다라는 말을 한 것일까?

 

Sort Merge Join은 부분범위처리가 안 된다 ( X )

-->적절한 인덱스가 있다면 선행집합은 부분범위처리가 가능하다 ( O )

 

 

4. Full Scan을 하면 인덱스를 사용할 수 없으므로 항상 Sort 작업이 발생할까?

 

SELECT /*+ leading(s) full(p) full(s) use_merge(p) */

       s.*, p.prod_id

  FROM sales_t s, products p

 WHERE p.prod_id = 119

   AND p.prod_id = s.prod_id;

 

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

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

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

|   0 | SELECT STATEMENT     |          |      1 |  22189 |00:00:00.29 |    4450 |          |

|   1 |  MERGE JOIN CARTESIAN|          |      1 |  22189 |00:00:00.29 |    4450 |          |

|*  2 |   TABLE ACCESS FULL  | SALES_T  |      1 |  22189 |00:00:00.06 |    4443 |          |

|   3 |   BUFFER SORT        |          |  22189 |  22189 |00:00:00.07 |       7 | 2048  (0)|

|*  4 |    TABLE ACCESS FULL | PRODUCTS |      1 |      1 |00:00:00.01 |       7 |          |

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

 

Predicate Information (identified by operation id):

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

   2 - filter("S"."PROD_ID"=119)

   4 - filter("P"."PROD_ID"=119)

 

Sort Join이 사라진 이유

힌트를 주어 sales_t products 모두 full table scan을 발생시켰다. 그러자 product 테이블 쪽은 buffer sort가 존재하지만 sales_t 테이블 쪽은 Sort가 사라졌다. Sort가 사라질 수 있는 이유는 product 쪽에 unique 조건(prod_id = 119)에 의해서 집합이 항상 한 건임을 보장하기 때문이다. , 집합이 한 건뿐이므로 조인이 필요 없어지는 것이다. 바로 이것이 Sort Merge Join Cartesian Merge Join 으로 바뀔 수 있는 이유이다. 반대로 이야기하면, 위의 SQL에서 unique 조건이 없다면 Cartesian Merge Join buffer sort는 결코 발생하지 않는다.

 

Full Scan이 발생하면 인덱스를 사용할 수 없으므로 항상 Sort 작업을 동반한다 ( X )

-->Full Scan이 발생해도 Unique 조건이 들어오면 Sort Join Operation이 사라진다 ( O )

 

 

5. Sort Merge Join 대신에 Cartesian Merge Join이 나오면 조인조건이 빠진 악성 SQL일까?

 

위에서 Unique 조건 때문에 Sort Merge Join Cartesian Merge Join으로 바뀐다고 했다. 이 현상은 아주 바람 직한 것이다. 왜냐하면 불필요한 Sort를 없애버리기 때문이다. 따라서 Cartesian Merge Join이라고 해서 항상 실수로 조인을 하지 않은 악성 SQL은 아니다.

 

이번에는 Unique 인덱스를 사용하는 경우를 보자.

 

SELECT /*+ leading(p) full(s) use_merge(s) */

       s.*, p.prod_id

  FROM sales_t s, products p

 WHERE p.prod_id = 119

   AND p.prod_id = s.prod_id;

 

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

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

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

|   0 | SELECT STATEMENT     |             |      1 |  22189 |00:00:00.14 |    4444 |

|   1 |  MERGE JOIN CARTESIAN|             |      1 |  22189 |00:00:00.14 |    4444 |

|*  2 |   INDEX UNIQUE SCAN  | PRODUCTS_PK |      1 |      1 |00:00:00.01 |       1 |

|*  3 |   TABLE ACCESS FULL  | SALES_T     |      1 |  22189 |00:00:00.05 |    4443 |

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

 

Predicate Information (identified by operation id):

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

   2 - access("P"."PROD_ID"=119)

   3 - filter("S"."PROD_ID"=119)

 

Unique 인덱스를 사용하자 Sort가 사라졌고, 심지어 Buffer Sort도 사라졌다. 따라서 성능도 최적이 되었다. 그러므로 MERGE JOIN CARTESIAN 이라는 operation 만 보고 조인절이 빠졌다거나 악성 SQL 이라고 판단해서는 안 된다.

 

만약 조인 순서가 바뀌면 buffer sort가 나타나므로 주의해야 한다. 아래의 SQL을 보자.

 

SELECT /*+ leading(s) full(s) use_merge(p) */

       s.*, p.prod_id

  FROM sales_t s, products p

 WHERE p.prod_id = 119

   AND p.prod_id = s.prod_id;

 

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

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

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

|   0 | SELECT STATEMENT     |             |      1 |  22189 |00:00:00.28 |    4444 |          |

|   1 |  MERGE JOIN CARTESIAN|             |      1 |  22189 |00:00:00.28 |    4444 |          |

|*  2 |   TABLE ACCESS FULL  | SALES_T     |      1 |  22189 |00:00:00.06 |    4443 |          |

|   3 |   BUFFER SORT        |             |  22189 |  22189 |00:00:00.07 |       1 | 2048  (0)|

|*  4 |    INDEX UNIQUE SCAN | PRODUCTS_PK |      1 |      1 |00:00:00.01 |       1 |          |

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

 

Predicate Information (identified by operation id):

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

   2 - filter("S"."PROD_ID"=119)

   4 - access("P"."PROD_ID"=119)

 

카테시안 조인도 순서대로 실행해야 한다

Buffer sort 뿐만 아니라 merge 횟수도 22189번이나 시도되어 성능이 저하되었다. 위의 실행계획에서 볼 수 있듯이 CARTESIAN MERGE JOIN 도 조인의 순서가 중요하므로 실행계획을 유심히 살펴야 한다.

 

카테시안 조인의 발생조건

Unique 컬럼에 조건이 Equal로 들어오면 옵티마이져가 성능향상을 위해서 조인절을 삭제한다. 만약 Unique 컬럼이라도 Equal 조건이 아니라 Range 조건이라면 위의 CARTESIAN MERGE JOIN 실행계획이 나타나지 않는다. 아래의 SQL이 그것을 증명한다.

 

SELECT /*+ leading(s) full(s) use_merge(p) */

       s.*, p.prod_id

  FROM sales_t s, products p

 WHERE p.prod_id >= 119

   AND p.prod_id < 120

   AND p.prod_id = s.prod_id;

  

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

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

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

|   0 | SELECT STATEMENT    |             |      1 |  22189 |00:00:00.35 |    4441 |          |

|   1 |  MERGE JOIN         |             |      1 |  22189 |00:00:00.35 |    4441 |          |

|   2 |   SORT JOIN         |             |      1 |  22189 |00:00:00.16 |    4440 | 1117K (0)|

|*  3 |    TABLE ACCESS FULL| SALES_T     |      1 |  22189 |00:00:00.06 |    4440 |          |

|*  4 |   SORT JOIN         |             |  22189 |  22189 |00:00:00.08 |       1 | 2048  (0)|

|*  5 |    INDEX RANGE SCAN | PRODUCTS_PK |      1 |      1 |00:00:00.01 |       1 |          |

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

 

Predicate Information (identified by operation id):

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

   3 - filter(("S"."PROD_ID">=119 AND "S"."PROD_ID"<120))

   4 - access("P"."PROD_ID"="S"."PROD_ID")

       filter("P"."PROD_ID"="S"."PROD_ID")

   5 - access("P"."PROD_ID">=119 AND "P"."PROD_ID"<120)

 

카테시안 조인이 더 빠르다

비록 SQL의 결과는 같지만 sort join operation에 의해서 PGA를 소모한다. where절의 prod_idequal 조건이냐 아니면 Range조건이냐에 따라서 성능이 좌우된다. , 성능이 나쁜 Sort Merge Join으로 풀리느냐 아니면, 추가적인 Sort가 없어서 성능이 우수한 CARTESIAN MERGE JOIN으로 풀리느냐는 where 조건에 따라 좌우된다. Unique 컬럼에 = 조건인지 아닌지에 따라 Sort의 부하가 좌우되는 것이다.

 

만약 Unique 컬럼에 = 조건이 들어오면 옵티마이져가 hash join을 선택하는 경우가 있을까?

 

SELECT /*+ leading(p) use_hash(s) */

       s.*, p.prod_id

  FROM sales_t s, products p

 WHERE p.prod_id = 119

   AND p.prod_id = s.prod_id;

 

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

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

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

|   0 | SELECT STATEMENT   |             |      1 |   22189 |00:00:00.10 |    4444 |

|   1 |  NESTED LOOPS      |             |      1 |   22189 |00:00:00.10 |    4444 |

|*  2 |   INDEX UNIQUE SCAN| PRODUCTS_PK |      1 |       1 |00:00:00.01 |       1 |

|*  3 |   TABLE ACCESS FULL| SALES_T     |      1 |   22189 |00:00:00.06 |    4443 |

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

 

Predicate Information (identified by operation id):

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

   2 - access("P"."PROD_ID"=119)

   3 - filter("S"."PROD_ID"=119)

 

Hash join은 실행할 수 없다

Unique 컬럼에 = 조건이 들어오면 결코 hash join을 선택하지 않는다. 강제로 힌트를 사용해도 merge join이나 nested loop join을 선택한다. 왜냐하면 Hash Join은 반드시 Equal Join이 필요한데, 조인절이 삭제되어 hash join이 발생될 수 없기 때문이다.

 

Sort Merge Join 대신 Cartesian Merge Join이 나오면 조인조건이 빠진 악성 SQL이다 ( X )

-->Unique 조건이 Equal로 들어오고 같은 컬럼으로 조인하면 옵티마이저는 성능향상을 위해 조인절을 삭제한다 ( O )

 

 

6. 조인컬럼 기준으로 Sort되므로 Order by절과 조인 컬럼이 일치할 때만 Sort가 발생되지 않는다. 정말 그럴까?

 

Sort의 기준이 조인컬럼이라는 말이 항상 참일까? 아래의 SQL을 보자.

 

SELECT /*+ LEADING(B) FULL(A)  FULL(B) USE_MERGE(A) */

       B.*, A.CHANNEL_ID AS  CHAN

  FROM TAB1 a, TAB2 b

 WHERE A.SALES_NO = B.SALES_NO

   AND A.PROD_ID = 22

   AND B.PROD_ID = 22

 ORDER BY B.PROD_ID, B.SALES_NO, B.SEQ ;

 

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

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

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

|   0 | SELECT STATEMENT     |      |      1 |  17205 |00:00:03.34 |   32327 |          |

|   1 |  SORT ORDER BY       |      |      1 |  17205 |00:00:03.34 |   32327 | 1180K (0)|

|   2 |   MERGE JOIN         |      |      1 |  17205 |00:00:03.29 |   32327 |          |

|   3 |    SORT JOIN         |      |      1 |  17205 |00:00:02.62 |   27257 | 1054K (0)|

|*  4 |     TABLE ACCESS FULL| TAB2 |      1 |  17205 |00:00:02.53 |   27257 |          |

|*  5 |    SORT JOIN         |      |  17205 |  17205 |00:00:00.59 |    5070 |83968  (0)|

|*  6 |     TABLE ACCESS FULL| TAB1 |      1 |   3441 |00:00:00.52 |    5070 |          |

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

 

Predicate Information (identified by operation id):

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

   4 - filter("B"."PROD_ID"=22)

   5 - access("A"."SALES_NO"="B"."SALES_NO")

       filter("A"."SALES_NO"="B"."SALES_NO")

   6 - filter("A"."PROD_ID"=22)

 

Order by 절에 조인컬럼(SALES_NO) 이외의 것들이 있으므로 SORT ORDER BY operation이 추가로 발생하여 성능이 저하되었다. 이제 조인컬럼으로만 order by를 해보자.

 

 

SELECT /*+ LEADING(B) FULL(A)  FULL(B) USE_MERGE(A) */

       B.*, A.CHANNEL_ID AS  CHAN

  FROM TAB1 a, TAB2 b

 WHERE A.SALES_NO = B.SALES_NO

   AND A.PROD_ID = 22

   AND B.PROD_ID = 22

 ORDER BY B.SALES_NO;

 

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

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

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

|   0 | SELECT STATEMENT    |      |      1 |  17205 |00:00:02.69 |   32331 |          |

|   1 |  MERGE JOIN         |      |      1 |  17205 |00:00:02.69 |   32331 |          |

|   2 |   SORT JOIN         |      |      1 |  17205 |00:00:02.49 |   27257 | 1054K (0)|

|*  3 |    TABLE ACCESS FULL| TAB2 |      1 |  17205 |00:00:02.41 |   27257 |          |

|*  4 |   SORT JOIN         |      |  17205 |  17205 |00:00:00.11 |    5074 |83968  (0)|

|*  5 |    TABLE ACCESS FULL| TAB1 |      1 |   3441 |00:00:00.04 |    5074 |          |

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

 

Predicate Information (identified by operation id):

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

   3 - filter("B"."PROD_ID"=22)

   4 - access("A"."SALES_NO"="B"."SALES_NO")

       filter("A"."SALES_NO"="B"."SALES_NO")

   5 - filter("A"."PROD_ID"=22)

 

참고사항으로 알아두자. Order by절에 prod_id가 추가되어도 위의 실행계획은 같다. 왜냐하면 prod_id는 상수 22로 고정되어 있으므로 Sort가 필요 없기 때문이다.

 

조인컬럼으로 sort를 하니 SORT ORDER BY operation이 사라져 버렸다. 얼핏 보면 Sort의 기준은 조인컬럼인 것처럼 보인다. 하지만 이 조건을 항상 만족하려면 Full Scan을 해야 한다는 전제조건이 붙어야 한다. 그러면 이제 Full Scan 대신에 인덱스를 사용해보자.

 

 

SELECT /*+ LEADING(A) INDEX(A)  INDEX(B) USE_MERGE(B) */

       B.*, A.CHANNEL_ID AS  CHAN

  FROM TAB1 a, TAB2 b

 WHERE A.SALES_NO = B.SALES_NO

   AND A.PROD_ID = 22

   AND B.PROD_ID = 22

 ORDER BY B.PROD_ID, B.SALES_NO, B.SEQ ;

 

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

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

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

|   0 | SELECT STATEMENT               |             |      1 |  17205 |00:00:02.21 |   17352 |          |

|   1 |  SORT ORDER BY                 |             |      1 |  17205 |00:00:02.21 |   17352 | 1117K (0)|

|   2 |   MERGE JOIN                   |             |      1 |  17205 |00:00:02.16 |   17352 |          |

|   3 |    TABLE ACCESS BY INDEX ROWID | TAB1        |      1 |   3441 |00:00:00.31 |      91 |          |

|*  4 |     INDEX RANGE SCAN           | IDX_TAB1_01 |      1 |   3441 |00:00:00.05 |      12 |          |

|*  5 |    SORT JOIN                   |             |   3441 |  17205 |00:00:01.80 |   17261 | 1054K (0)|

|   6 |     TABLE ACCESS BY INDEX ROWID| TAB2        |      1 |  17205 |00:00:01.75 |   17261 |          |

|*  7 |      INDEX RANGE SCAN          | IDX_TAB2_01 |      1 |  17205 |00:00:00.06 |      56 |          |

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

 

Predicate Information (identified by operation id):

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

   4 - access("A"."PROD_ID"=22)

   5 - access("A"."SALES_NO"="B"."SALES_NO")

       filter("A"."SALES_NO"="B"."SALES_NO")

   7 - access("B"."PROD_ID"=22)

 

인덱스를 사용했음에도 추가적인 Sort가 발생하는 이유

이런! 인덱스를 사용했지만, SORT ORDER BY가 발생하였다. 왜 그럴까? 인덱스를 사용할 때 Sort의 기준은 선행집합의 인덱스 컬럼이다. , 선행집합의 인덱스컬럼이 order by절에 나온다면 Sort가 발생하지 않는다. 위의 SQL에서 선행집합의 인덱스컬럼은 PROD_ID + SALES_NO 이다. 따라서 B.SEQ 컬럼 때문에 Sort가 발생한 것이다. 그러면 이제 Sort를 없애기 위하여 선행집합을 바꿔보자.

 

SELECT /*+ LEADING(B) INDEX(A)  INDEX(B) USE_MERGE(A) */

       B.*, A.CHANNEL_ID AS  CHAN

  FROM TAB1 a, TAB2 b

 WHERE A.SALES_NO = B.SALES_NO

   AND A.PROD_ID = 22

   AND B.PROD_ID = 22

 ORDER BY B.PROD_ID, B.SALES_NO, B.SEQ ;

 

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

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

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

|   0 | SELECT STATEMENT              |             |      1 |  17205 |00:00:03.09 |   17387 |          |

|   1 |  MERGE JOIN                   |             |      1 |  17205 |00:00:03.09 |   17387 |          |

|   2 |   TABLE ACCESS BY INDEX ROWID | TAB2        |      1 |  17205 |00:00:02.58 |   17296 |          |

|*  3 |    INDEX RANGE SCAN           | IDX_TAB2_01 |      1 |  17205 |00:00:00.14 |      91 |          |

|*  4 |   SORT JOIN                   |             |  17205 |  17205 |00:00:00.39 |      91 |83968  (0)|

|   5 |    TABLE ACCESS BY INDEX ROWID| TAB1        |      1 |   3441 |00:00:00.32 |      91 |          |

|*  6 |     INDEX RANGE SCAN          | IDX_TAB1_01 |      1 |   3441 |00:00:00.06 |      12 |          |

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

 

Predicate Information (identified by operation id):

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

   3 - access("B"."PROD_ID"=22)

   4 - access("A"."SALES_NO"="B"."SALES_NO")

       filter("A"."SALES_NO"="B"."SALES_NO")

   6 - access("A"."PROD_ID"=22)

 

선행집합의 인덱스 컬럼과 Order By절의 컬럼이 동일하다. 그리고 인덱스와 Order by절의 컬럼순서도 동일하다. 이 두 가지 조건을 만족하므로 추가적인 SORT ORDER BY operation이 발생하지 않았다. Order By 뿐만 아니라 Group By도 마찬가지이다. 이제 Order by Group By를 동시에 사용해보자.

 

SELECT /*+ LEADING(B) INDEX(A)  INDEX(B) NO_PLACE_GROUP_BY USE_MERGE(A) */

       B.PROD_ID, B.SALES_NO, COUNT(*)

  FROM TAB1 a, TAB2 b

 WHERE A.SALES_NO = B.SALES_NO

   AND A.PROD_ID = 22

   AND B.PROD_ID = 22

 GROUP BY B.PROD_ID, B.SALES_NO, B.SEQ

ORDER BY B.PROD_ID, B.SALES_NO ;

 

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

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

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

|   0 | SELECT STATEMENT     |             |      1 |  17205 |00:00:00.30 |      70 |          |

|   1 |  SORT GROUP BY NOSORT|             |      1 |  17205 |00:00:00.30 |      70 |          |

|   2 |   MERGE JOIN         |             |      1 |  17205 |00:00:00.25 |      70 |          |

|*  3 |    INDEX RANGE SCAN  | IDX_TAB2_01 |      1 |  17205 |00:00:00.02 |      58 |          |

|*  4 |    SORT JOIN         |             |  17205 |  17205 |00:00:00.08 |      12 |57344  (0)|

|*  5 |     INDEX RANGE SCAN | IDX_TAB1_01 |      1 |   3441 |00:00:00.01 |      12 |          |

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

 

Predicate Information (identified by operation id):

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

   3 - access("B"."PROD_ID"=22)

   4 - access("A"."SALES_NO"="B"."SALES_NO")

       filter("A"."SALES_NO"="B"."SALES_NO")

   5 - access("A"."PROD_ID"=22)

 

Order By/Group By절의 컬럼이 모두 선행집합의 인덱스 컬럼과 순서가 같으므로 추가적인 Sort가 전혀 발생하지 않았다. 따라서 다음과 같은 결론을 낼 수 있다.

 

조인컬럼 기준으로 Sort되므로 Order by절과 조인 컬럼이 일치해야만 Sort가 발생하지 않는다. ( X )

-->Full table scan일 때는 조인컬럼 기준으로 sort 되는 것이 옳다. 하지만, index를 사용한다면 조인컬럼 뿐만 아니라, 선행집합의 인덱스 컬럼과 order by/group by절을 일치시켜도 Sort가 발생하지 않는다 ( O )

 

 

결론

6가지의 오만과 편견 중에 하나라도 얻은 것이 있다면 성공이다. 다시 한번 여섯 가지를 정리하기 바란다.

 

양쪽 집합이 Full Table Scan을 사용하면 조인순서에 상관없이 일량이 동일하므로 처리시간도 동일하다 ( X )

-->일량은 동일하더라도 Merge 횟수가 달라지면 처리시간이 달라진다  ( O )

 

조인순서에 상관없이 Sort량이 동일하다 ( X )

-->적절한 인덱스를 사용하는 경우, Sort량은 Join 순서에 의해 달라진다 ( O )

 

Sort Merge Join은 부분범위처리가 안 된다 ( X )

-->적절한 인덱스가 있다면 선행집합은 부분범위처리가 가능하다 ( O )

 

Full Scan이 발생하면 인덱스를 사용할 수 없으므로 항상 Sort 작업을 동반한다 ( X )

-->Full Scan이 발생해도 Unique 조건이 들어오면 Sort Join Operation이 사라진다 ( O )

 

Sort Merge Join 대신 Cartesian Merge Join이 나오면 조인조건이 빠진 악성 SQL이다 ( X )

-->Unique 조건이 Equal로 들어오고 같은 컬럼으로 조인하면 옵티마이저는 성능향상을 위해 조인절을 삭제한다 ( O )

 

조인컬럼 기준으로 Sort되므로 Order by절과 조인 컬럼이 일치해야만 Sort가 발생하지 않는다. ( X )

-->Full table scan일 때는 조인컬럼 기준으로 sort 되는 것이 옳다. 하지만, index를 사용한다면 조인컬럼 뿐만 아니라, 선행집합의 인덱스 컬럼과 order by/group by절을 일치시켜도 Sort가 발생하지 않는다 ( O )


PS
요즘 워낙 바빠서 예전에 미리 글을 써놓지 않았더라면 글을 하나도 올리지 못할뻔 하였다. 
저작자 표시 비영리 동일 조건 변경 허락
신고
크리에이티브 커먼즈 라이선스
Creative Commons License
Posted by extremedb

댓글을 달아 주세요

  1. 라튜니 2011.04.28 10:17 신고  댓글주소  수정/삭제  댓글쓰기

    병풍에 관한 포스팅 이후 다음 포스팅을 오래 기다렸는데~ 정말 좋은 내용을 포스팅 해주셨네요. 항상 좋은 내용 감사합니다. 특히 Cartesian Merge Join에 관한 내용은 새롭게 안 내용이네요. 이번 포스팅을 계기로 Sort Merge Join에 대해 정리할 수 있는 계기가 되었습니다. 감사합니다.

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2011.04.29 09:19 신고  댓글주소  수정/삭제

      글을 미리 써놓은 것이 다행이라고 생각합니다.^^
      한가지 걱정은 어떤 주제에 대해 정리하려다 보니 글이 너무 길어진다는 단점입니다. 위의 글도 14페이지나 됩니다. 블로그에 올리기에는 너무.....
      블로그 글이라기보다는 논문이 되버린 느낌 이네요

  2. Favicon of http://1ststreet.tistory.com BlogIcon SITD 2011.04.29 15:08 신고  댓글주소  수정/삭제  댓글쓰기

    카르테시안이란 실행계획만 봐도 경기를 일으켜야 한다고 생각했는데, 역시 오라클이 더 빠른 걸 찾아가는 거였군요, 좋은 글 감사드림니다 ^^

    참, 3번의 경우 rownum 조건에 의해 1건만 가져온 데이터가 후행 테이블에 없을 땐 당연히 다음 데이터를 가져와서 후행 테이블과 비교를 하겠죠?
    후행은 소트조인의 형태를, 그리고 선행에서 후행을 찾는 방식은 네스티드 루프랑 비슷한 형식이 되지 않을까 싶어서요.

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2011.04.29 15:53 신고  댓글주소  수정/삭제

      SITD님이 말씀하신대로 입니다.
      선행집합의 동작은 nested loop join과 같습니다.
      조인 두번중에 한번을 실패하게 만들어서 테스트 해보시면 정확히 알 수 있습니다.
      아래 조건을 인라인뷰 안에 넣으시고 rownum <= 100건으로 테스트 하시면 merge 횟수가 정확히 두배인것을 관찰 하실수 있을 겁니다. 한번 해보세요.

      ...생략
      AND MOD(A.SALES_NO,2) = 1 --> 추가조건
      ORDER BY B.PROD_ID, B.SALES_NO, B.SEQ )
      WHERE ROWNUM <= 100 ;

    • Favicon of http://1ststreet.tistory.com BlogIcon SITD 2011.05.03 14:24 신고  댓글주소  수정/삭제

      DB 생성 권한이 없어서, 집에가서 한번 해봐야겠네요 ^^
      답변 감사드립니다.

  3. feelie 2011.05.03 22:26 신고  댓글주소  수정/삭제  댓글쓰기

    sort merge join 의 새로운 느낌입니다. 그래서일까요 이해하기가 어렵습니다.
    sort merge join은 각 테이블에 대하여 입력조건으로 데이터를 조회하여 조인컬럼으로 각각 sort을 하고
    하나씩 비교하는 방식인것으로 알고 있습니다.
    이런 개념에 사로잡혀있어서 그런가요? 아님 제가 sort merge join을 잘못 이해하고 있는건가요?
    1. 일량은 동일하더라도 Merge 횟수가 달라지면 처리시간이 달라진다
    => Merge의 횟수는 동일할것 같고,
    2. 적절한 인덱스를 사용하는 경우, Sort량은 Join 순서에 의해 달라진다
    => 개별 처리가되니 sort 량은 동일할것 같습니다.
    참 어렵습니다..

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2011.05.04 11:39 신고  댓글주소  수정/삭제

      승필님 반갑습니다.
      Sort Merge Join의 두 집합이 독립적으로 처리되므로 조인순서는 아무런 상관이 없다는 미신을 깨기 위해서 이글이 작성되었습니다.

      1.Merge 횟수는 선행집합이 filter 된 후의 건수에 비례합니다. 따라서 작은 집합을 선행으로 두면 merge 횟수가 감소하므로 성능이 향상됩니다. 이 성격은 nested loop join과 유사합니다.

      2.Sort를 방지하는 적절한 인덱스가 있으면 후행집합을 작은것으로 선택하면 sort가 최소화 됩니다. 왜냐하면 인덱스가 있으므로 선행집합은 sort를 전혀 하지 않기 때문입니다. sort량을 튜닝하려면 1번과는 반대로 후행집합을 작은 것으로 해야합니다.

      감사합니다.

    • feelie 2011.05.19 09:15 신고  댓글주소  수정/삭제

      볼때마다 생소하니 아직도 너무많이 부족한것 같습니다.
      2. 조인순서에 상관없이 Sort량이 동일할까 ?
      에서 후행테이블은 인덱스을 사용했지만
      소트를 수행한다.
      소트머지의 특징인가요??
      후행테이블도 인덱스를 사용하였으므로 소트가 필요없을것 같은데요..

  4. salvationism 2011.05.09 14:27 신고  댓글주소  수정/삭제  댓글쓰기

    테스트할 수 있도록 스크립도 같이 올려 주셔서 도움이 많이 되고 있습니다.
    보통 정성으로는 힘든 것인데.. ㅎㅎ
    열정 본 받고 싶습니다. ^^

  5. salvationism 2011.05.09 17:19 신고  댓글주소  수정/삭제  댓글쓰기

    5번에서 Unique 컬럼에 = 조건이 들어오면 결코 hash join을 선택하지 않는다고 하셨는데
    다음과 같이 Hash 조인으로 풀립니다.
    왜 그런지요???
    SH 스키마 테이블 입니다.

    SELECT /*+ leading(p) use_hash(s) */
    s.*, p.prod_id
    FROM sales_t s, products p
    WHERE p.prod_id = 119
    AND p.prod_id = s.prod_id;

    -----------------------------------------------------------------------------------------------------------------------
    | Id | Operation | Name | Starts | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
    -----------------------------------------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 1 | 106 |00:00:00.11 | 130 | 148 | | | |
    |* 1 | HASH JOIN | | 1 | 106 |00:00:00.11 | 130 | 148 | 1036K| 1036K| 348K (0)|
    |* 2 | INDEX UNIQUE SCAN| PRODUCTS_PK | 1 | 1 |00:00:00.01 | 1 | 0 | | | |
    |* 3 | TABLE ACCESS FULL| SALES_T | 1 | 106 |00:00:00.10 | 129 | 148 | | | |
    -----------------------------------------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------
    1 - access("P"."PROD_ID"="S"."PROD_ID";)
    2 - access("P"."PROD_ID"=119)
    3 - filter("S"."PROD_ID"=119



    *************************
    Join Elimination (JE)
    *************************
    SQL:******* UNPARSED QUERY IS *******
    SELECT /*+ LEADING ("P";) USE_HASH ("S";) */ "S"."PROD_ID" "PROD_ID","S"."CUST_ID" "CUST_ID","S"."TIME_ID" "TIME_ID","S"."CHANNEL_ID" "CHANNEL_ID","S"."PROMO_ID" "PROMO_ID","S"."QUANTITY_SOLD" "QUANTITY_SOLD","S"."AMOUNT_SOLD" "AMOUNT_SOLD","P"."PROD_ID" "PROD_ID" FROM "SH"."SALES_T" "S","SH"."PRODUCTS" "P" WHERE "P"."PROD_ID"=119 AND "P"."PROD_ID"="S"."PROD_ID"
    SQL:******* UNPARSED QUERY IS *******
    SELECT /*+ LEADING ("P";) USE_HASH ("S";) */ "S"."PROD_ID" "PROD_ID","S"."CUST_ID" "CUST_ID","S"."TIME_ID" "TIME_ID","S"."CHANNEL_ID" "CHANNEL_ID","S"."PROMO_ID" "PROMO_ID","S"."QUANTITY_SOLD" "QUANTITY_SOLD","S"."AMOUNT_SOLD" "AMOUNT_SOLD","P"."PROD_ID" "PROD_ID" FROM "SH"."SALES_T" "S","SH"."PRODUCTS" "P" WHERE "P"."PROD_ID"=119 AND "P"."PROD_ID"="S"."PROD_ID"
    Query block SEL$1 (#0) unchanged
    PM: Considering predicate move-around in query block SEL$1 (#0)
    **************************
    Predicate Move-Around (PM)
    **************************
    PM: PM bypassed: Outer query contains no views.
    PM: PM bypassed: Outer query contains no views.
    query block SEL$1 (#0) unchanged
    FPD: Considering simple filter push in query block SEL$1 (#0)
    "P"."PROD_ID"=119 AND "P"."PROD_ID"="S"."PROD_ID"
    try to generate transitive predicate from check constraints for query block SEL$1 (#0)
    finally: "P"."PROD_ID"=119 AND "P"."PROD_ID"="S"."PROD_ID" AND "S"."PROD_ID"=119

    FPD: transitive predicates are generated in query block SEL$1 (#0)
    "P"."PROD_ID"=119 AND "P"."PROD_ID"="S"."PROD_ID" AND "S"."PROD_ID"=119
    apadrv-start sqlid=14344755796783354000
    :
    call(in-use=26684, alloc=32736), compile(in-use=61628, alloc=64696), execution(in-use=2024, alloc=4060)

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2011.05.13 00:54 신고  댓글주소  수정/삭제

      equal 조인조건이 삭제되지 않아서 hash join이 가능한 것입니다.

      products 테이블이 문제인것 같습니다. pk constraint가 valid 하지 않습니다. 아래처럼 products_t 테이블을 만들고 새로 테스트 해보시기 바랍니다. 그럼 카테시안 조인이 발생할 것입니다.

      create table sh.products_t as select * from sh.products;

      ALTER TABLE SH.PRODUCTS_T ADD CONSTRAINT PRODUCTS_T_PK PRIMARY KEY (PROD_ID) USING INDEX;



      SELECT /*+ leading(s) full(p) full(s) use_merge(p) */
      s.*, p.prod_id
      FROM sales_t s, products_t p
      WHERE p.prod_id = 119
      AND p.prod_id = s.prod_id;

      SELECT /*+ leading(p) use_hash(s) */
      s.*, p.prod_id
      FROM sales_t s, products_t p
      WHERE p.prod_id = 119
      AND p.prod_id = s.prod_id;

      감사합니다.

  6. salvationism 2011.05.09 17:29 신고  댓글주소  수정/삭제  댓글쓰기

    4번 테스트에서 같은 버전인데 결과가 좀 다르게 나오네요.
    글 올리신 것처럼 발생하려면 JE만 발생해야 할거 같은데 FPD로 인해 join predicate가 그대로 있는거 같습니다.
    동일한 Unique 조건인데 왜 그런지 이해가 잘 안가네요.


    SQL> select * from v$version;

    BANNER
    --------------------------------------------------------------------------------
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
    PL/SQL Release 11.2.0.1.0 - Production
    CORE 11.2.0.1.0 Production
    TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
    NLSRTL Version 11.2.0.1.0 - Production


    SELECT /*+ leading(s) full(p) full(s) use_merge(p) */
    s.*, p.prod_id
    FROM sales_t s, products p
    WHERE p.prod_id = 119
    AND p.prod_id = s.prod_id;

    ---------------------------------------------------------------------------------------------------------------------
    | Id | Operation | Name | Starts | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
    ---------------------------------------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 1 | 101 |00:00:00.05 | 127 | 151 | | | |
    | 1 | MERGE JOIN | | 1 | 101 |00:00:00.05 | 127 | 151 | | | |
    |* 2 | TABLE ACCESS FULL | SALES_T | 1 | 101 |00:00:00.03 | 123 | 148 | | | |
    |* 3 | SORT JOIN | | 101 | 101 |00:00:00.02 | 4 | 3 | 73728 | 73728 | |
    |* 4 | TABLE ACCESS FULL| PRODUCTS | 1 | 1 |00:00:00.01 | 4 | 3 | | | |
    ---------------------------------------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------
    2 - filter("S"."PROD_ID"=119)
    3 - access("P"."PROD_ID"="S"."PROD_ID";)
    filter("P"."PROD_ID"="S"."PROD_ID";)
    4 - filter("P"."PROD_ID"=119)

    (10053 TRACE)
    *************************
    Join Elimination (JE)
    *************************
    SQL:******* UNPARSED QUERY IS *******
    SELECT /*+ LEADING ("S";) USE_MERGE ("P";) FULL ("P";) FULL ("S";) */ "S"."PROD_ID" "PROD_ID","S"."CUST_ID" "CUST_ID","S"."TIME_ID" "TIME_ID","S"."CHANNEL_ID" "CHANNEL_ID","S"."PROMO_ID" "PROMO_ID","S"."QUANTITY_SOLD" "QUANTITY_SOLD","S"."AMOUNT_SOLD" "AMOUNT_SOLD","P"."PROD_ID" "PROD_ID" FROM "SH"."SALES_T" "S","SH"."PRODUCTS" "P" WHERE "P"."PROD_ID"=119 AND "P"."PROD_ID"="S"."PROD_ID"
    SQL:******* UNPARSED QUERY IS *******
    SELECT /*+ LEADING ("S";) USE_MERGE ("P";) FULL ("P";) FULL ("S";) */ "S"."PROD_ID" "PROD_ID","S"."CUST_ID" "CUST_ID","S"."TIME_ID" "TIME_ID","S"."CHANNEL_ID" "CHANNEL_ID","S"."PROMO_ID" "PROMO_ID","S"."QUANTITY_SOLD" "QUANTITY_SOLD","S"."AMOUNT_SOLD" "AMOUNT_SOLD","P"."PROD_ID" "PROD_ID" FROM "SH"."SALES_T" "S","SH"."PRODUCTS" "P" WHERE "P"."PROD_ID"=119 AND "P"."PROD_ID"="S"."PROD_ID"
    Query block SEL$1 (#0) unchanged
    PM: Considering predicate move-around in query block SEL$1 (#0)

    **************************
    Predicate Move-Around (PM)
    *************************
    PM: PM bypassed: Outer query contains no views.
    PM: PM bypassed: Outer query contains no views.
    query block SEL$1 (#0) unchanged
    FPD: Considering simple filter push in query block SEL$1 (#0)
    "P"."PROD_ID"=119 AND "P"."PROD_ID"="S"."PROD_ID"
    try to generate transitive predicate from check constraints for query block SEL$1 (#0)
    finally: "P"."PROD_ID"=119 AND "P"."PROD_ID"="S"."PROD_ID" AND "S"."PROD_ID"=119

    FPD: transitive predicates are generated in query block SEL$1 (#0)
    "P"."PROD_ID"=119 AND "P"."PROD_ID"="S"."PROD_ID" AND "S"."PROD_ID"=119


select /*+ full(a) full(b)  leading(a) use_hash(b) */

a.col1, b.col2

  from tab1 a,

       tab1 b

  where a.col1 = b.col2 ;

 

오해와 현실

위의 SQL을 보면 from 절의 두 테이블은 동일하다. 그리고 건수가 많아서 힌트를 주었으므로, 둘 다 full table scan을 할 것이다. 따라서 위의 SQL을 실행하고 결과를 본다면, a b의 일량(block I/O)은 동일하다.”라고 알고 있는 사람이 많이 있다. a를 읽었더니 block I/O 량이 1000 블럭이라면 b를 읽을 때도 1000 블럭이 나올 것이라는 이야기다. 이런 주장이 사실일까? 결론부터 말하자면 사실이 아니다. b쪽이 더 많은 블럭을 scan 해야 한다. 그래서 b쪽을 scan할 때 더 느리다. b쪽에 더 많은 일량이 나온다면 버그라고 생각하는 사람도 있지만, 버그가 아니라 정상적인 결과이다.

 

이 글의 목적

위의 결론에 따르면 후행테이블을 scan 할 때 심각한 성능저하가 발생 할 수 있다. 이런 현상을 주위의 지인들에게 질문한 결과 적절한 이유나 원인을 말하는 사람은 거의 없었다. 성능문제의 원인을 모르면 튜닝을 할 수 없다. 그러므로 이 글에서는 성능이 저하되는 이유를 독자에게 제시하고, 비효율을 해결 할 수 있는 방법을 설명한다. 또한 이런 문제가 발생하지 않는 예외적인 경우도 살펴본다.

 

이제 테스트를 진행하기 위해 테이블을 하나 만들자.

 

create table test1 as

select lpad(level, 5, '0') as num,

       lpad(level, 60, '0') as num_txt

  from dual

connect by level <= 50000 ;

 

인덱스가 없음으로 앞으로 모든 실행계획은 full table scan이 될 것이다. 정확한 분석을 위해 test1 테이블의 full table scan 일량(logical reads)을 알아보자.

 

select count(*)

  from test1;

 

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

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

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

|   0 | SELECT STATEMENT   |       |      1 |      1 |00:00:00.01 |     504 |

|   1 |  SORT AGGREGATE    |       |      1 |      1 |00:00:00.01 |     504 |

|   2 |   TABLE ACCESS FULL| TEST1 |      1 |  50000 |00:00:00.06 |     504 |

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

 

full table scan의 결과 일량은 504 블럭이다. 따라서 test1 테이블의 데이터가 변경되지 않는다면 항상 504 블럭이 나와야 한다. 정말 그렇게 될까?

 

아래 SQL의 조인 순서는 a--> b 이다.

 

select /*+ leading(a b) */ a.num

  from test1 a,

       test1 b

  where a.num = b.num

    and a.num > '00100'

    and substr(b.num_txt,  -5) > '00100'; --> substr의 인자 -5는 마지막 다섯 자리라는 뜻이다.

 

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

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

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

|   0 | SELECT STATEMENT   |       |      1 |  49900 |00:00:00.45 |    5998 |

|*  1 |  HASH JOIN         |       |      1 |  49900 |00:00:00.45 |    5998 |

|*  2 |   TABLE ACCESS FULL| TEST1 |      1 |  49900 |00:00:00.06 |     504 |

|*  3 |   TABLE ACCESS FULL| TEST1 |      1 |  49900 |00:00:00.15 |    5494 |

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

 

Predicate Information (identified by operation id):

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

   1 - access("A"."NUM"="B"."NUM")

   2 - filter("A"."NUM">'00100')

   3 - filter((SUBSTR("B"."NUM_TXT",(-5))>'00100' AND "B"."NUM">'00100'))

 

무려 11배나 차이가 난다

선행테이블은 정상적으로 504블록이 나왔다. 하지만 이상하게도 선행테이블과 동일한 테이블인 후행테이블( b )의 일량이 약 11배나 많다. 수행시간도 후행테이블이 더 느리다. 같은 테이블을 동일한 방법으로 scan 했는데 왜 Block I/O 수가 11배나 차이가 날까?

 

힌트를 주어 조인 순서를 바꿔보자.

 

select /*+ leading(b a) */ a.num

  from test1 a,

       test1 b

  where a.num = b.num

    and a.num > '00100'

    and substr(b.num_txt,  -5) > '00100';

 

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

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

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

|   0 | SELECT STATEMENT   |       |      1 |  49900 |00:00:00.34 |    5998 |

|*  1 |  HASH JOIN         |       |      1 |  49900 |00:00:00.34 |    5998 |

|*  2 |   TABLE ACCESS FULL| TEST1 |      1 |  49900 |00:00:00.11 |     504 |

|*  3 |   TABLE ACCESS FULL| TEST1 |      1 |  49900 |00:00:00.06 |    5494 |

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

 

Predicate Information (identified by operation id):

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

   1 - access("A"."NUM"="B"."NUM")

   2 - filter((SUBSTR("B"."NUM_TXT",(-5))>'00100' AND "B"."NUM">'00100'))

   3 - filter("A"."NUM">'00100')

  

array size가 원인이다

이번에는 반대로 a의 일량이 b보다 11배 많게 나왔다. 즉 일관성 있게 후행테이블의 일량이 11배가 많다. 그 이유는 툴(오렌지) array size 10 으로 되어있었기 때문이다. 다른 말로 바꾸면 array size 10 이기 때문에 49900건을 모두 출력하려면 4990 fetch 해야 한다. 즉 위의 일량 5494는 원래의 블록 수인 504 fetch 회수(4990 블럭)을 더한 것이다. 여기까지는 이해가 될 것인데 문제는 fetch 할 때마다 한 블록을 더 읽어야 하는가?이다.

 

Fetch 할 때마다 이전에 읽었던 1블럭을 더 읽어야 한다

한 블록에 20건이 들어있다고 가정하고, Array size 10 이라고 치자. 그러면 한 블럭의 데이터(20)를 모두 출력 하려면 동일한 블럭을 반복적으로 두 번 fetch 해야 한다. 바로 이것이 fetch 할 때마다 이미 읽었던 블럭(직전에 fetch 했던 block중 마지막 block)을 다시 Scan 할 수 밖에 없는 이유이다.

 

비효율을 없애려면 array size를 적정 수준으로 늘려라

 

set arraysize 100 --array size 100으로 변경

 

select /*+ leading(a b) */ a.num

  from test1 a,

       test1 b

  where a.num = b.num

    and a.num > '00100'

    and substr(b.num_txt,  -5) > '00100';

 

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

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

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

|   0 | SELECT STATEMENT   |       |      1 |  49900 |00:00:00.38 |    1507 |

|*  1 |  HASH JOIN         |       |      1 |  49900 |00:00:00.38 |    1507 |

|*  2 |   TABLE ACCESS FULL| TEST1 |      1 |  49900 |00:00:00.06 |     504 |

|*  3 |   TABLE ACCESS FULL| TEST1 |      1 |  49900 |00:00:00.11 |    1003 |

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

 

Predicate Information (identified by operation id):

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

   1 - access("A"."NUM"="B"."NUM")

   2 - filter("A"."NUM">'00100')

   3 - filter((SUBSTR("B"."NUM_TXT",(-5))>'00100' AND "B"."NUM">'00100'))

  

array size를 올리자 logical read 5494 에서 1003 으로 변경되었다. 5배 이상 일량(logical reads )이 줄어들었다. 하지만 아직도 원래의 블록 수인 504 보다배정도 많다. 

 

set arraysize 1000 --array size 1000으로 변경

 

select /*+ leading(a b) */ a.num

  from test1 a,

       test1 b

  where a.num = b.num

    and a.num > '00100'

    and substr(b.num_txt,  -5) > '00100';

 

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

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

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

|   0 | SELECT STATEMENT   |       |      1 |  49900 |00:00:00.34 |    1058 |

|*  1 |  HASH JOIN         |       |      1 |  49900 |00:00:00.34 |    1058 |

|*  2 |   TABLE ACCESS FULL| TEST1 |      1 |  49900 |00:00:00.06 |     504 |

|*  3 |   TABLE ACCESS FULL| TEST1 |      1 |  49900 |00:00:00.09 |     554 |

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

 

Predicate Information (identified by operation id):

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

 

   1 - access("A"."NUM"="B"."NUM")

   2 - filter("A"."NUM">'00100')

   3 - filter((SUBSTR("B"."NUM_TXT",(-5))>'00100' AND "B"."NUM">'00100'))

 

array size1000으로 올리자 logical read 1003 에서 554로 변경되었다. 이 정도면 원래의 블럭수인 504와 비슷하다. 554와 504의 차이는 50 블럭이므로 fetch를 50번 했다는 것을 알 수 있다.

 

해결방법
테스트의 결과는 fetch
가 발생할 때마다 직전 블럭을 읽어야 함을 알 수 있다. 따라서 array size를 적절히 늘리면 fetch 회수가 줄어들므로 이전 블럭을 읽는 횟수도 같이 줄어든다. 이에 따라 성능도 향상된다. 하지만 array size를 늘려도 선행테이블은 logical read의 변화가 없다. 왜냐하면 선행테이블은 fetch에 영향을 끼치지 못하며, 후행 테이블이 scan 되어 조인에 성공될 때만 데이터가 client로 전송(fetch) 되기 때문이다.

조인이 없을 때도 비효율은 발생한다
이런 현상은 full table scan과 해시조인의 조합에서만 발생하는 것은 아니다. 조인 없이 from 절에 테이블이 하나뿐일 때도 동일하게 발생한다. 아래의 SQL이 전형적인 예제이다.

 

array  size 10일 때       

 

select num

  from test1;

 

Trace Version   : Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

Environment     : Array Size = 10

                  Long  Size = 80

********************************************************************************

 

Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows

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

Parse        1    0.000        0.000          0          0          0          0

Execute      1    0.000        0.000          0          0          0          0

Fetch     5001    0.328        0.219          0       5504          0      50000

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

Total     5003    0.328        0.219          0       5504          0      50000

 

Misses in library cache during parse: 0

Optimizer goal: ALL_ROWS

Parsing user: SYS (ID=0)

 

Rows     Row Source Operation

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

      0  STATEMENT

  50000   TABLE ACCESS FULL TEST1 (cr=5504 pr=0 pw=0 time=67049 us cost=143 size=300000 card=50000)

 

fetch를 5001 번 했기 때문에 원래의 블럭수( 504 )에 비해 logical read량도 약 5000 블럭이 늘었다. 
 


array
 size
100일 때

 

Trace Version   : Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

Environment     : Array Size = 100

                  Long  Size = 80

********************************************************************************

 

Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows

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

Parse        1    0.000        0.000          0          0          0          0

Execute      1    0.000        0.000          0          0          0          0

Fetch      501    0.063        0.041          0       1004          0      50000

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

Total      503    0.063        0.041          0       1004          0      50000

 

Misses in library cache during parse: 1

Optimizer goal: ALL_ROWS

Parsing user: SYS (ID=0)

 

Rows     Row Source Operation

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

      0  STATEMENT

  50000   TABLE ACCESS FULL TEST1 (cr=1004 pr=0 pw=0 time=75254 us cost=143 size=300000 card=50000)

 

Array size 10인 경우(5504)에 비해 일량이 약 5배 정도 감소했다. 그 이유는 fetch 회수가 10배로 줄어들었기 때문이다.

 


array  size
1000 일 때

 

Trace Version   : Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

Environment     : Array Size = 1000

                  Long  Size = 80

 

********************************************************************************

 

Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows

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

Parse        1    0.000        0.000          0          0          0          0

Execute      1    0.000        0.000          0          0          0          0

Fetch       51    0.031        0.016          0        554          0      50000

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

Total       53    0.031        0.017          0        554          0      50000

 

Misses in library cache during parse: 1

Optimizer goal: ALL_ROWS

Parsing user: SYS (ID=0)

 

Rows     Row Source Operation

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

      0  STATEMENT

  50000   TABLE ACCESS FULL TEST1 (cr=554 pr=0 pw=0 time=50383 us cost=143 size=300000 card=50000)        

 

무작정 크게 한다고 좋아지지 않는다

array size 1000으로 변경하니 array size가 10인 경우(5504 블럭)에 비해 일량이 약 10배 정도 감소했다. 하지만 array size 100 인 경우와 비교해 보면 일량이 고작 2배 정도만 줄어들었다. 다시 말해 여기서 array size를 더 크게 하더라도 얻는 이익은 별로 없다는 것이다. 따라서 무작정 array size를 늘려서는 안 된다. 메모리에 부하를 줄 뿐만 아니라 한번에 많은 데이터가 client로 전송되므로 네트웍 I/O가 과도 하게 늘어날 수 있다. 따라서 clientfetch 할 건수가 많고, 네트웍 망의 성능이 좋다면 1000~ 2000 정도를 유지하는 것이 적당하다. 물론 조회 프로그램에서는 페이징 처리를 하는 것이 가장 좋지만, 업무적으로 전체 건을 볼 수 밖에 없는 경우는 array size를 적절히 조절하는 것이 대안이 될 수 있다.


성능문제의 발생조건 
fetch의 비효율은 select문에서만 발생한다. 즉 insert–select CTAS(create table as select) 그리고 merge 문 등에서는 이런 종류의 성능저하가 발생하지 않는다. 왜냐하면 DML문은 select문과 달리 조회(데이터를 clientfetch) 할 필요가 없고, commit이 되면 바로 종료되기 때문이다.

모든 규칙에 예외는 있다

full table scan + sort merge join 의 조합에서는 fetch의 비효율이 발생하지 않는다. 왜냐하면 full table scan + sort merge join 조합은 hash join의 조합과 달라서 모든 데이터를 sort 해야하기 때문이다. 모든 데이터를 sort하려면 어차피 모든 블럭을 scan해야 하므로 fetch를 여러번 해야만 하는 array size를 사용할 필요가 없는 것이다.  그리고 fetch를 여러번 하지 않기 때문에 항상 일량이 일정하다.

또 다른 예외의 경우는
 1 블럭에 1 row만 저장되는 경우이다. 이런 경우는 블럭을 한번만 엑세스 해도 그 블럭의 모든 데이터를 한번에 fetch 할 수 있으므로, 같은 블록을 반복해서 읽을 필요가 없다. 따라서 array size를 변경해도 일량이 달라지지 않는다.

 

호기심이 있는 독자는 아래의 테이블을 만들고 위의 테스트를 똑같이 진행 해보기 바란다. 위의 test 결과와는 다를 것이다.

 

drop table test1 ;

 

create table test1 as

select lpad(level, 5, '0') as num,

       lpad(level, 7000, '0') as num_txt

  from dual

connect by level <= 50000 ;

 
array size 항상 나쁜가?
우리는 array size가 있음으로 해서 부분범위처리를 할 수있다. full table scan을 동반하는 해시조인의 경우에도 중간에 효율적으로 멈출 수 있다. 예를 들어 결과건수가 1억건이며, 만건을 먼저 조회한 후에 다음 만건을 보고 싶다고 할때, 운반단위(array size)가 1000 이라면 10번 fetch 하면 멈출 수 있다. 반면에 array size가 없다면 중간에 멈출 수 없으므로 1억건을 모두 fetch 한후에나 결과를 화면에서 볼 수 있다.

결론

같은 테이블을 두 번 full table scan 하고, 그 둘을 해시조인하면 대부분의 경우 후행 테이블의 I/O량이 더 많다. 그래서 후행테이블을 scan 할 때가 더 느리다. 왜냐하면 직전 fetch 때에 이미 읽었던 block의 데이터가 모두 fetch 되지 않을 수 있으므로 그 블럭을 한번 더 읽어보아야 확인 할 수 있기 때문이다. 이런 비효율이 많이 발생하는 경우는 array size가 작기 때문이다. 따라서 적절한 array size로 늘려주면 성능문제를 해결 할 수 있다. 

fetch의 비효율은 full table scan이나 full table scan + hash join 조합을 사용할 때만 발생하는 것은 아니다. index scan을 할때도 똑같이 비효율이 발생한다.(주1)  즉 fetch의 비효율 문제는 인덱스를 사용할때나 테이블을 scan할때를 가리지 않고 모두 발생한다. 이런 사실들로 미루어 볼때, 위에서 언급한 몇가지의 예외를 제외한다면, 우리는 다음과 같은 결말을 낼 수 있다.

"select문의 결과건수가 많음에도 불구하고, 페이징 처리가 되지 않고, array size가 작은 조회용 프로그램이라면 fetch의 비효율은 존재한다."



주1 : 인덱스 사용시 fetch의 비효율 문제는 이미 책으로 정리가 되어 있으므로 필자가 언급하지 않는다. 이 문제에  관심이 있는 사람은 조동욱 님의 책 Optimizing Oracle Optimizer를 참조하기 바란다.

저작자 표시 비영리 동일 조건 변경 허락
신고
크리에이티브 커먼즈 라이선스
Creative Commons License
Posted by extremedb

댓글을 달아 주세요

  1. feelie 2011.01.14 12:45 신고  댓글주소  수정/삭제  댓글쓰기

    좋은 내용 감사합니다.

    늦었지만 새해 복많이 받으시고, 올해 목표하시는 일 다 이루시길 바랍니다.

  2. Ejql 2011.01.17 15:58 신고  댓글주소  수정/삭제  댓글쓰기

    이런 문의가 종종있었나 보네요? 확실히 알고 갑니다. 감사합니다. 추가 원인이 그 이유였군요.

  3. 오라클완전초보 2011.01.18 17:18 신고  댓글주소  수정/삭제  댓글쓰기

    매일 매일 SQL 을 보면서 사는데
    왜 저는 이런걸 발견하지 못할까요.. 아무생각없이 튜닝을 해서 그런가 봅니다.
    반성하게 되네요

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2011.01.18 17:47 신고  댓글주소  수정/삭제

      너무 걱정하지 마시기 바랍니다.
      튜닝에 집중하다 보면 다른것은 보이지 않기 때문입니다.
      하나의 방법은 Q&A에서 답변을 자주하면 실력이 늡니다.
      답변을 하기위해 공부를 많이 해야하고, 원인을 찾아야 하고.... 하다보면 한단계 업그레드 되어있는 자신을 발견하실 것입니다.
      감사합니다.

  4. sid 2011.01.18 21:00 신고  댓글주소  수정/삭제  댓글쓰기

    “왜 fetch 할 때마다 한 블록을 더 읽어야 하는가?”
    이건 어디서 판단해야 하나요?
    이 부분이 잘 이해가 안가서 계속 보고 있는데, 어디서 블럭을 또 본다는 걸 파악해야 하는지 잘 이해가 안가서요.
    전체적으로 워낙에 잘 풀어쓰셔서 술술 이해가 되는데 그 부분만 막혀버려서, 답답해서 이렇게 질문 드립니다.

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2011.01.19 09:36 신고  댓글주소  수정/삭제

      안녕하세요.
      select num from test1; 부분의 10046 trace 를 보시면 됩니다. 여기를 보시면 패치회수만큼 블럭을 더 읽는다는 것을 알 수 있습니다. 즉 array size가 10일때 5만건(결과건수)을 패치하려면 5천번을 실행해야 합니다. 이 정보가 10046 trace의 fetch에 나타납니다. 그리고 current에 블럭 i/o량이 나타납니다. trace 상의 굵은 글씨를 중점적으로 보시면 됩니다.

      즉 원래의 블럭량인 504와 패치횟수 5000을 더하면 logical read 량인 5504 가 나옵니다. 이해가 되셨나요?

  5. sid 2011.01.19 10:47 신고  댓글주소  수정/삭제  댓글쓰기

    화면상으론 확인할 수 없나 보군요 ㅎ
    네, 알겠습니다. 지금은 권한상 무리니까 집에가서 한번 테스트 해봐야겠네요.
    좋은 글 감사합니다 ^^

  6. salvationism 2011.01.23 20:41 신고  댓글주소  수정/삭제  댓글쓰기

    "select문의 결과건수가 많음에도 불구하고, 페이징 처리가 되지 않고, array size가 작은 조회용 프로그램이라면 fetch의 비효율은 존재한다."
    자연스럽게 고개를 끄덕이게 되는 단순 명료한 정의 같습니다. 좋은 글 감사합니다. ^^

  7. 나그네 2011.01.24 15:35 신고  댓글주소  수정/삭제  댓글쓰기

    궁금한 점이 있습니다. 어레이 사이즈로 인해 후행 테이블의 로지컬 리드가 높아졌다면 왜 선행 테이블의 로지컬 리드는 안 늘어 나는 건가요? 선행 테이블도 어레이 사이즈에 맞춰서 읽지 않는지요. 이 부분이 궁금합니다.

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2011.01.24 16:00 신고  댓글주소  수정/삭제

      반갑습니다.
      선행집합만 화면에 뿌리는 것은 의미가 없습니다. 다시말해,select 결과를 화면에 fetch 하려면 조인에 성공한 건만 해야 합니다. 어차피 후행집합이 조인에 성공한 후에 fetch가 시작되므로 성행집합에 성능이 저하되는 array size를 사용할 필요가 없는것 입니다. 이해가 되셨나요?

  8. 나그네 2011.02.16 20:23 신고  댓글주소  수정/삭제  댓글쓰기

    일량이 틀려요 => 일량이 달라요가 맞습니다.

    다르다 = different, 틀리다 = wrong

    우리나라 사람이 가장 잘못 사용하는 단어 중 하나라고 생각합니다.

    요즘 얼마 전 출간된 AWR 관련 서적을 읽고 있는데, 이 책의 저자는 '다르다'는 표현을 전부 '틀리다'로 써 놓으셨더군요.

    일상 대화 중에서야 그러려니 하겠지만, 전문서적에서 전부 잘못 써 놓으니 책 읽기가 싫어질 정도였습니다.

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2011.02.16 21:23 신고  댓글주소  수정/삭제

      좋은 의견입니다. 제목이 틀렸군요. 개발자에게 들은 것을 그대로 사용하면 안되겠네요.
      한글의 사용이 잘못되어 지적을 하는것은 중요합니다.

      마찬가지로 DB 실력향상도 중요합니다. 국어와 한글을 사랑하시어 댓글을 남기신 만큼,
      이번주에 올라간 분석함수 문제에도 어문규정 만큼 관심을 가져 주시고, 문제를 푸셔서 댓글로 남겨주세요.

      감사합니다.

  9. rose 2011.11.29 18:04 신고  댓글주소  수정/삭제  댓글쓰기

    이런 이유가 있었네요~ 재밌게 잘 읽었습니다 ^^

3 부작의 마지막 편
첫 번째,
Oracle Data Access Pattern을 정복하라
두 번째, Data Access Pattern중의 파티션에 관련된
Partition Access Pattern 에 이어서 마지막 편이다.

지난 글에서 Data Access Pattern 과 Join Method 이 두 가지는 기본 중에 기본이라고 하였다.
또한 이 두 가지를 정복한다면 SQL 튜닝중의 많은 부분을 커버할 수 있다고 하였다.
튜닝에서 이것보다 중요한 것이 있을까? 이것들 중에 하나라도 빠트린다면 제대로 된 튜닝을 할 수 없다.

단순 분류 5가지
데이터의 연결방법은 단순분류해 보면 다음과 같다.

1.Nested Loop Join
2.Sort Merge Join
3.Hash Join
4.Outer Join
5.Using Subquery

세분화
하지만 이것으로는 부족하다. Join Method를 좀더 자세히 나타내면 다음과 같다.

01. Nested Loop Join
02. Sort Merge Join
03. Hash Join
04. Cartesian Join (혹은 Cross Join)
05. Sub Query-In,
06. Sub Query-Any
07. Sub Query-All
08. Sub Query-Exists
09. Subquery Factoring
10. Semi Join-Nested Loop
11. Semi Join-Sort Merge
12. Semi Join-Hash
13. Semi Join-Hash Join Right
14. Anti Join-Nested Loop
15. Anti Join-Sort Merge
16. Anti Join-Hash
17. Anti Join-Hash Join Right
18. Index Join
19. Outer Join-Full
20. Outer Join-Nested Loop
21. Outer Join-Sort Merge
22. Outer Join-Hash
23. Outer Join-Hash Join Right
24. Partition Outer Join
25. Star Query Transformation
 
극한의 세분화
물론 여기서 더 세분화 시킬 수 있다. 예를 들면 Nested Loop Join은 아래와 같이 분류할 수 있다.

Full(선행집합)-Unique (후행집합)
Full(선행집합)-Range (후행집합)
Range(선행집합)-Range (후행집합)
Unique(선행집합)-Unique (후행집합)
....중간생략

이런 방법으로 Sort Merge Join과 Hash Join까지 계속 나열한다면 아마 끝이 없을 것이다.

단 한 줄도 놓치지 마라 
아래의 첨부파일에는 Nested Loop Join도 위와 같은 방법으로 가능한 세분화 하였다. 따라서 이 파일에 담긴 Join method는 25가지가 넘는다. 오늘 이야기하는 조인방법들은 튜닝을 하려면 반드시 정복해야 할 주제이니 꼼꼼히 보기 바란다.


Oracle Data Join Method.pdf

Oracle Data Join Method



PS
Star Join은 Star Query Transformation이 나온 후로 설 땅을 잃었으므로 나타내지 않았다.

신고
크리에이티브 커먼즈 라이선스
Creative Commons License
Posted by extremedb

댓글을 달아 주세요

  1. Kai 2010.02.26 17:45 신고  댓글주소  수정/삭제  댓글쓰기

    열심히 공부하겠습니다. ( '')/☆

  2. starroot 2010.02.26 17:52 신고  댓글주소  수정/삭제  댓글쓰기

    좋은 정보 감사합니다.

  3. feelie 2010.03.02 13:08 신고  댓글주소  수정/삭제  댓글쓰기

    oracle data access pattern, partition access pattern,
    그리고 oracle join 25가지 까지....
    좋은정보에 감사할 따름입니다..

  4. 이쁜이 2010.03.02 22:04 신고  댓글주소  수정/삭제  댓글쓰기

    좋은 정보 감사합니다.

  5. daemon 2010.03.05 15:06 신고  댓글주소  수정/삭제  댓글쓰기

    오동규 컨설턴트님의 글을 항상 감사하게 보고 있습니다.
    이렇게 귀중한 정보를 아무 댓가도 없이 공유해주시는 모습 정말 존경스럽습니다.
    컨설턴트님의 책을 손꼽아 기다리는 한사람으로서 항상 올려주시는 자료에 감사의 말씀이라도
    꼭 드리고 싶었습니다.

  6. Ejql 2010.07.26 10:15 신고  댓글주소  수정/삭제  댓글쓰기

    이제.. 오동규님의 책을 읽을 준비가 되었다고 판단해서. 먼저 기초 공부로 티스토리부터 시작하겠습니다.
    감사합니다.

  7. Favicon of http://bluegom.tistory.com BlogIcon 파란곰팅 2010.12.10 19:47 신고  댓글주소  수정/삭제  댓글쓰기

    오옷... 이런 좋은 자료가 감사합니다.^^....

  8. 힘돌이 2011.10.12 11:46 신고  댓글주소  수정/삭제  댓글쓰기

    오동규님 정말 감사합니다.
    여기서 정말 많은것을 배우게 되네요.

  한 선지자에 의하여 9i/10g 그리고 11g 에서 Nested Loop Join 수행시 buffer pinning 효과에 의한 성능향상이 증명된바 있다.
물론 그글은 명불허전 이다.(해당글 링크:http://ukja.tistory.com/166)
   이미 밝혀진 원리에 대해서 증명이나 검증하는것은 더이상 과학이 아니다. 그리고 재미도 없다. 따라서 오늘은
buffer pinning 에 의한 성능개선이 아닌 또다른 성능개선에 대한 2가지 원리 대해서 연구해보자.

  이제부터 버젼별로 변경된 Nested Loop Join 의 실행계획과 그에 따르는 원리에 대하여 알아보려 한다. 모든 예제는 오라클 설치시 자동으로 설치되는 SH Schema의 customers 테이블과 sales 테이블을 이용한다.
먼저 인덱스를 하나 만든다.

drop index sh.sales_cust_ix;
create index sh.sales_cust_ix on sh.sales(cust_id);


 이어서 buffer cache 를 비우고 SQL 을 실행한다.
고객 테이블을 full scan 하고 sales 테이블과 Nested Loop Join 을 수행한다.

alter system flush buffer_cache;

select /*+ gather_plan_statistics */ count(*)
  from (select /*+ no_merge full(c) use_nl(c s) */
               s.cust_id,
               s.time_id,
               c.cust_year_of_birth
         from  sh.customers c,
               sh.sales s
         where c.cust_id  = s.cust_id  
           and c.cust_year_of_birth between 1960 and 1980
           and s.channel_id  between 2 and 3
           and s.prod_id < 18); 
 
select * from
table(dbms_xplan.display_cursor(null,null, 'allstats last -rows +outline -predicate' ));

Oracle 8i Plan --> just normal
------------------------------------------------
| Id  | Operation                              |
---------------------------------------------- |
|   1 |  SORT AGGREGATE                        |
|   2 |   VIEW                                 |
|   3 |     NESTED LOOPS                       |
|   4 |      TABLE ACCESS FULL                 |
|   5        TABLE ACCESS BY GLOBAL INDEX ROWID|
|   6 |        INDEX RANGE SCAN                |
------------------------------------------------

특별 할것 없는 전통적인 Nested Loop Join 이다.
이제 9i 및 10g 의 plan 을 보자.

9i & 10g Plan --> table prefetch
-------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name          | A-Rows |   A-Time   | Buffers | Reads  |
-------------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE                      |               |      1 |00:00:05.67 |     245K|   1454 |
|   2 |   VIEW                               |               |   8269 |00:00:05.66 |     245K|   1454 |
|   3 |    TABLE ACCESS BY GLOBAL INDEX ROWID| SALES         |   8269 |00:00:05.62 |     245K|   1454 |
|   4 |     NESTED LOOPS                     |               |    327K|00:00:02.83 |   41304 |   1454 |
|   5 |      TABLE ACCESS FULL               | CUSTOMERS     |  20010 |00:00:00.12 |    1457 |   1454 |
|   6 |      INDEX RANGE SCAN                | SALES_CUST_IX |    307K|00:00:00.88 |   39847 |      0 |
-------------------------------------------------------------------------------------------------------

Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      ... 중간생략
      NLJ_PREFETCH(@"SEL$2" "S"@"SEL$2")
      END_OUTLINE_DATA
  */
 

Oracle 9i 에서 table prefetch 기능이 나오다.
  Inner(후행) 테이블의 위치가 Nested Loop Join 위로 올라가 버렸다. 그리고 오라클이 내부적으로
NLJ_PREFETCH 힌트를 사용하였다. 이것은 어떤 의미를 가지고 있을까?
이러한 현상에 대한 원리는 single block I/O request 에 의한 physical read 시 block 을 prefetch(미리 읽는 작업) 한다는데 있다.
여기서 physical read 란 buffer cache 에 데이터가 없어서 disk 에서 데이터를 read 하는것을 의미한다.
어차피 scan할 data 이므로 미리 엑세스할 물리적 주소를 여러개(운반단위) 모은다음 한번에 read 햐여 buffer cache 에 올리게 되는것이다. 여기서 주의할점은 multi block I/O 를 하는것이 아니라 single block I/O 여러개(운반단위만큼)가 동시에 진행된다는 것이다. 이것을 Vector IO 라고 부른다.(Batch IO 라고도 함)  바로 여기에 성능개선 효과가 있는것이다. 이기능에 의해서 rowid 에 의한 테이블 access 는 8i 에 비해서 상당한 개선 효과가 있는 것이다.(Operation ID 로는 3번이 여기 해당된다.)

이것을 증명하기 위해 v$sesstat 에서 SQL 수행전과 수행후의 value 증가분을 비교해보면 아래와 같다.

NAME                                           DIFF
---------------------------------------- ----------
undo change vector size                        2840
physical read IO requests                      3812
... 중간생략                                      
physical reads cache prefetch                  1344


위에서 보는것과 같이 table prefetch 가 발생하였다. 위의 테스트는 11g 에서 수행된것인데 9i 의 살행계획과 실행통계도 10g 와 대동소이 하다. 11g 에서 이전 버젼(9i/10g) 번젼의 plan 을 나타나게 하려면 NO_NLJ_BATCHING(테이블명) 힌트를 사용하면 된다.  9i 나 10g 에서의 후행 테이블 prefetch에 의한 성능 개선효과는 11g 에 와서야 완벽한 모습을 갖추게 된다.

11g Plan --> Index Vector I/O
------------------------------------------------------------------------------------------------------
|Id  | Operation                             | Name          | A-Rows |   A-Time   | Buffers | Reads |
------------------------------------------------------------------------------------------------------
|  1 |  SORT AGGREGATE                       |               |      1 |00:00:04.82 |     245K|   1454|
|  2 |   VIEW                                |               |   8269 |00:00:04.81 |     245K|   1454|
|  3 |    NESTED LOOPS                       |               |   8269 |00:00:04.79 |     245K|   1454|
|  4 |     NESTED LOOPS                      |               |    307K|00:00:01.56 |   41304 |   1454|
|  5 |      TABLE ACCESS FULL                | CUSTOMERS     |  20010 |00:00:00.08 |    1457 |   1454|
|  6 |      INDEX RANGE SCAN                 | SALES_CUST_IX |    307K|00:00:00.47 |   39847 |      0|
|  7 |     TABLE ACCESS BY GLOBAL INDEX ROWID| SALES         |   8269 |00:00:01.93 |     203K|      0|
------------------------------------------------------------------------------------------------------
 
Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      ... 중간생략
      NLJ_BATCHING(@"SEL$2"
"S"@"SEL$2")
      END_OUTLINE_DATA
  */

궁하면 통한다.
이상하지 않은가? 테이블이 2개 인데 Nested Loop Join 이 하나가 아닌 2개가 되어버렸다. 또한 NLJ_PREFETCH 힌트가 사라지고 NLJ_BATCHING 힌트로 대체 되었다.
이러한 현상이 의미하는 바는 무엇일까?
9i/10g 에서 table prefetch 기능이 추가되었지만 index scan 에 관해서는 그런기능이 없었다.
드디어 11g 에서 index scan 시 Vector IO 가 가능해졌다. 궁하면 통한다고 했던가? 오라클이 Nested Loop Join 에 대하여 지속적으로 개선해왔다는것을 알수있다.
참고로 NO_NLJ_BATCHING 힌트를 사용하면 9i/10g 의 Plan 으로 돌아가게 된다.

그러면 11g 의 버젼에서 v$sesstat 통계를 보자.

NAME                                           DIFF
---------------------------------------- ----------
Batched IO vector block count                  3758
Batched IO vector read count                     50
... 이후 생략

위에서 보듯이 Batched IO 란것이 생겼다.
Batched IO (혹은 Vector IO) 기능에 힘입어 table prefetch 에 이어서 11g 에서는 index scan 의 성능까지 향상되었다.

주의사항 : 위에서 수행한 모든 테스트는 Physical read 시에만 해당된다. 위의 예제 스크립트에 buffer cache 를 flush 한 이유도 여기에 있다.

결론:
  오라클 9i, 10g 및 11g 에서 개선된 Nested Loop Join 의 원리는 다음과 같다.
첫번째는 9I/10g 에서 후행 테이블의 TABLE ACCESS BY INDEX ROWID Operation 작업속도가 개선되었다는것과 두번째로 11g 에서 후행 테이블의 인덱스 scan 속도까지 획기적으로 개선되었다는 것이다. 이것은 table prefetch 기능과 Vector I/O 기능에 의해서 각각 구현 되었다. 이기능들과 별도로 이글의 서두에서 이야기한 buffer pinning 기능까지 덤으로 따라오게 되었다.

  앞으로 11g 를 사용시 과거처럼 Batch 용 SQL 에서 무조건 hash 조인을 남발하지 말았으면 한다.
조인건수가 많지 않고 후행 테이블에 적당한 인덱스가 있을 경우에 최소한 Nested Loop Join 과 성능비교를 해보아야 하지않을까?

<편집후기 : 위 테스트는 11g 에서 테스트 되었음을 다시한번 밝혀둡니다.>

신고
크리에이티브 커먼즈 라이선스
Creative Commons License
Posted by extremedb

댓글을 달아 주세요

  1. Favicon of http://ukja.tistory.com BlogIcon 욱짜 2009.05.09 18:14 신고  댓글주소  수정/삭제  댓글쓰기

    테스트하실 때 10g의 정확한 하위 버전이 어떻게 되나요? 그 버전부터 NLJ_PREFETCH Hint와 NO_NLJ_PREFETCH Hint가 추가되었군요!

    11g에서는 NLJ_BATCHING, NO_NLJ_BATCHING Hint가 추가된 것 같습니다. Nested Loop Join과 관련된 동작 방식을 이제 거의 완벽하게 Hint로 제어할 수 있게 되었군요.

    11g에서 V$SQL_HINT view를 보면 아래와 같이 Hint가 등록되어 있네요.

    NAME INVERSE VERSION
    -------------------- -------------------- --------------------
    NLJ_BATCHING NO_NLJ_BATCHING 11.1.0.6
    NO_NLJ_BATCHING NLJ_BATCHING 11.1.0.6

    NAME INVERSE VERSION
    -------------------- -------------------- --------------------
    NLJ_PREFETCH NO_NLJ_PREFETCH 11.1.0.6
    NO_NLJ_PREFETCH NLJ_PREFETCH 11.1.0.6

  2. Favicon of http://scidb.tistory.com BlogIcon extremedb 2009.05.11 09:59 신고  댓글주소  수정/삭제  댓글쓰기

    오타에 의해서 10g 에서 수행되었다고 적었네요.
    수정 하도록 하겟습니다.
    NLJ_BATCHING , NLJ_PREFETCH 힌트등은 11g 에서만 사용하실수 있습니다.
    역 힌트도 마찬 가지 입니다.
    또한 11g 에서도 NO_NLJ_BATCHING, NO_NLJ_PREFETCH 힌트를 같이사용하면 작동을 하지 않는것이 발견 되었습니다.
    따라서 11g 에서도 9i~11g 에서 발전된 모든 기능을 해제 하려면 파라미터를 사용하는수 밖에 없습니다.

  3. 바우 2009.05.11 18:00 신고  댓글주소  수정/삭제  댓글쓰기

    올려주신 글 잘 읽어보고 있습니다. 이번 글에서 의문점이 생겨서 문의를 드립니다.

    9i/10g 에서 table prefetch 기능이 추가되었지만 index scan 에 관해서는 그런기능이 없었다.
    이 부분이 잘 이해가 안됩니다.

    실행계획에서 보면 먼저 테이블 FULL로 읽고 인덱스 RANGE SCAN할때 여러개를 묶어서 하는 것이라고 이해가 되었습니다. 그렇다면 FULL SCAN이 아니라 인덱스 SCAN하고 테이블 갈때 한다는 것인데 INDEX SCAN에 관해서는 그런 기능이 없었다라고 하시니 헷갈립니다. 이것에 대해서 자세히 설명을 해주실 수 있을런지요.
    그리고 여러개라고 하셨는데 그게 몇개인지 알 수 없나요? 그리고 만약 그 몇개를 인위적으로 늘린다면 NL조인으로 부분범위 처리를 하고자 할때 성능 차이가 생길 수 있을 것 같은데 실제로 그렇게 되는지요. 궁금합니다.

    올려주신 글 항상 고맙게 잘 보고 있습니다. 감사합니다.

  4. Favicon of http://scidb.tistory.com BlogIcon extremedb 2009.05.11 21:17 신고  댓글주소  수정/삭제  댓글쓰기

    먼저 "9i/10g 에서 table prefetch 기능이 추가되었다" 함은 TABLE ACCESS BY INDEX ROWID 부분을 두고 하는말입니다. 제가 9i/10g plan 에 파랑색으로 표시한 부분입니다. 그부분은 index scan 이 아니지요.
    index scan 에 의한 부분은 11g 에 나옵니다. 내부적인 NLJ_BATCHING 힌트나 Batched IO vector block count 등이 그증거가 되겟습니다.
    또한 table prefetch 시에 운반단위수의 수정을 말씀해주셨는데 아주 위험 합니다.
    물론 그런기능을 하는 히든 파라미터가 존재 합니다.
    하지만 그것을 수정했을경우 나타나는 버그에 대해서는 누구도 알수 없습니다.
    검증된바가 없습니다.
    따라서 오라클사의 직원들조차 그 파라미터들을 건드리지 않습니다.
    건드리는 경우는 버그에 의해서 기능을 못쓰도록 막을때나 사용합니다.
    하지만 이또한 위험 하므로 히든파라미터는 그대로 놓고 쓰시는것이 제일 좋습니다.
    http://scidb.tistory.com/entry/히든-파라미터-설정의-위험성 를 참조 하시기 바랍니다.

  5. 바우 2009.05.12 09:11 신고  댓글주소  수정/삭제  댓글쓰기

    답변 감사드립니다. ^^

  6. 야함나르 2009.05.18 16:14 신고  댓글주소  수정/삭제  댓글쓰기

    NLJ_BATCHING , NLJ_PREFETCH 힌트등은 11g 에서만 사용하실수 있습니다 라고 되어 있는데
    제가 보고 있는 DB의 경우 9.2.0.7 버전에서 8i와 같은 실행 계획이 나타나고 있습니다. 9 버전에는 힌트가 없다면 파라미터 조정을 통한 방법 말고 table prefetch를 유도하는 방법은 없을까요?.

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2009.05.18 20:09 신고  댓글주소  수정/삭제

      파라미터를 조정하기 전에 table prefetch 기능은
      제약사항이 있으므로 먼저 nested loop join 의 형태를 점검 하셔야 합니다.
      1.nested loop join 일때만 가능함.
      2.선행집합이 index range scan 혹은 full table scan 이어야만 가능함.
      3.후행집합이 index range scan 일때만 가능함.
      위의 3가지 조건을 만족하는지 먼저 살펴보시기 바랍니다.

    • 야함나르 2009.05.19 11:44 신고  댓글주소  수정/삭제

      네 위의 조건을 만족합니다.

      --------------------------------------------------------------------------------
      0 SELECT STATEMENT Optimizer=CHOOSE
      1 0 SORT (AGGREGATE)
      2 1 NESTED LOOPS
      3 2 TABLE ACCESS (FULL) OF 'WRITTEN'
      4 2 TABLE ACCESS (BY INDEX ROWID) OF 'PRODUCT'
      5 4 INDEX (UNIQUE SCAN) OF 'PRODUCT_PK' (UNIQUE)

      이런 플랜이 생성되어서 적용할수 있지 않을까 합니다만.

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2009.05.19 11:48 신고  댓글주소  수정/삭제

      보여주신 plan 은 3번 째 조건을 만족 하지 못합니다.
      후행 테이블이 index unique scan 이므로 동시에 여러건을 가져오지 못합니다. index range scan 일때만 가능 합니다.

    • 야함나르 2009.05.19 13:10 신고  댓글주소  수정/삭제

      아; 그렇군요 ^^.

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2009.08.28 16:10 신고  댓글주소  수정/삭제

      과연 그렇군요.
      간단하지만 매우 유용한 테스트 입니다.
      좋은 정보 감사드립니다.^^

    • table prefetch 제약사항 점검.. 2009.08.28 16:23 신고  댓글주소  수정/삭제

      table prefetch 제약사항 중 "2.선행집합이 index range scan 혹은 full table scan 이어야만 가능함"에 관련하여 테스트를 해 보았습니다.
      결론은 index range scan 뿐만 아니라 index full scan, fsat full scan, skip scan에서도 발생하는 군요..
      (MIX/MAX)에서는 table prefetch가 불가한테 descending + rownum으로 하니 가능하네요...

      --스크립트 (욱짜님 스크립)
      drop table t1 purge;
      --drop table t1;

      drop table t2 purge;
      --drop table t2;

      create table t1(c1 int, c2 int);
      create table t2(c1 int, c2 int);

      --create index t1_n1 on t1(c1);
      alter table T1 add constraint T1_PK primary key(c1);
      create index t1_n2 on t1(c2,c1);
      create index t2_n1 on t2(c1);

      insert into t1
      select rownum, round(rownum,-2)
      from all_objects, all_objects
      where rownum <= 10000
      ;
      commit;

      insert into t2
      select rownum, rownum
      from all_objects, all_objects
      where rownum <= 10000
      ;
      commit;

      EXEC dbms_stats.gather_table_stats(user, 'T1', cascade => true);
      EXEC dbms_stats.gather_table_stats(user, 'T2', cascade => true);


      -- 테스트 결과.
      SELECT /*+ USE_NL(T1 T2) LEADING(T1) INDEX(T2) */
      COUNT(T2.C2)
      FROM
      --CASE 01. INDEX UNIQUE SCAN(X)
      --(SELECT C1 FROM T1 WHERE C1 = 1256) T1 , T2

      --CASE 02. INDEX RANGE SCAN(O)
      --(SELECT /*+ INDEX_RS(T1) */ C1 FROM T1 WHERE C1 <= 1256) T1 , T2

      --CASE 03 INDEX RANGE SCAN DESCENDING(O)
      --(SELECT /*+ INDEX_DESC(T1) */ C1 FROM T1 WHERE C1 <= 1000 ) T1 , T2

      --CASE 04 INDEX RANGE SCAN (MIN/MAX)(X)
      --(SELECT /*+ INDEX_RS(T1) */ MAX(C1) C1 FROM T1 WHERE C1 <= 1000 ) T1 , T2

      --CASE 05 INDEX FULL SCAN(N/A)
      --(SELECT C1 FROM T1 ) T1 , T2

      --CASE 06. INDEX FULL SCAN (MIN/MAX)(X)
      --(SELECT MAX(C1) C1 FROM T1 ) T1 , T2

      --CASE 07 INDEX FULL SCAN DESCENDING(O)
      --(SELECT /*+ INDEX_DESC(T1) */ C1 FROM T1 ) T1 , T2

      --CASE 08 INDEX FAST FULL SCAN(O)
      --(SELECT /*+ INDEX_FFS(T1) */ C1 FROM T1 ) T1 , T2

      --CASE 09 INDEX SKIP SCAN(O)
      (SELECT /*+ INDEX_SS(T1 T1_N2) */ C1 FROM T1 ) T1 , T2
      WHERE T1.C1 = T2.C1 ;

  7. 2009.07.22 11:32 신고  댓글주소  수정/삭제  댓글쓰기

    안녕하세요. 좋은 글 항상 감사합니다.
    다시 보다보니 궁금한 점이 있어서 질문드립니다.

    1. 기존 nested loop은 outer테이블에서 출발, inner인덱스에 엑세스하고 inner테이블에 엑세스해서 해당 정보까지 가져오는데 반해 소위 advanced nested loop(기존 NL과 비교해서 이걸 뭐라고 해야할지 모르겠습니다.)는 outer테이블의 스캔이 끝난 이후 inner인덱스과 join한 중간집합의 rowid로 inner테이블에 엑세스하기 때문에 outer테이블의 크기에 의해 결과반환시간(first_rows)에 영향을 줄 것 같고 결과적으로 기존의 부분처리와는 차이가 있을 것 같습니다.
    (빠른 결과를 위해 운반단위가 다차는 순간 테이블블록에 엑세스한다는 것에 생각해봤지만 반복적인 i/o를 줄이는 효과를 감소시키는 결과라서 이건 아닐 것같은 생각이 들고요.)
    만약 제 생각이 맞다면 어떤 식으로 테스트하면 두 차이를 확연히 드러낼 수 있을까요?

    2. outer테이블의 스캔이 끝난 이후 inner인덱스과 join한 중간집합은 inner인덱스의 rowid로 정렬되어 inner테이블 엑세스 시 logical i/o와 physical i/o를 줄일 수 있다고 이해하고 있습니다.
    그런데 중간집합이 inner인덱스의 rowid로 정렬된다는 것은 어떻게 확인 할 수 있을까요?
    실행계획에는 중간집합정렬에 관한 단서는 찾을 수 없는데..제가 잘못 이해하고 있는 부분이 있나요?


    질문이 장황하지만 제가 잘못 이해하고 있는 부분이 있다면 지적해주시고
    합당한 질문이라면 명쾌한 답을 주시면 감사하겠습니 다. ^^

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2009.07.23 09:58 신고  댓글주소  수정/삭제

      답변이 늦어져서 죄송합니다.
      1번에 대한 답변입니다.
      prefetch 라는 개념은 후행집합이 index range scan 으로 풀리므로 어짜피 scan 해야할 block 이 정해져 있다고 볼수 있습니다. 이때 Range 범위 내에서 Single index Range Scan 을 하는것 보다는 여러개의 Process 혹은 Thread 가 Index range Scan 을 하는 것이 성능상 유리하기 때문에 생긴 개념입니다.
      하지만 이것의 특징은 Hit ratio 가 있다는 점이지요.
      Prefetch 된 Block 들의 데이터를 사용하지 않을수 있기 때문입니다. 이 Hit Ratio 가 떨어지면 Prefetch 의 효과는 감소 됩니다.
      테스트를 수행시 v$sesstat 에서 SQL 수행전과 수행후의 value 의 차이를 모니터링 하시고 이때 나온 값들중에 Hit ratio 로 판단되는 값들이 있었습니다.
      이부분을 중점적으로 테스트 해보시는 것이 좋을거 같습니다.

      두번째 답변입니다.
      inner 테이블의 데이터는 inner 테이블의 인덱스 rowid 로 절렬이 되어 있지 않습니다. 그리고 실제로 정렬이 수행되지 않습니다. 물론 인덱스의 경우 데이터가 같은 값이라면 rowid 로 정렬리 되어 있기는 합니다.

      prefetch 시에 정렬을 하는 기능은 없는것으로 생각 하시면 됩니다.

  8. 2009.07.23 19:07 신고  댓글주소  수정/삭제  댓글쓰기

    답변 감사합니다. (목이 좀 늘어났습니다 ㅎㅎ)
    저는 운반단위로 블럭에 엑세스하면 반복적인 i/o가 발생할 수 있어서 비효율적이고 그러니까 그렇게 처리하지 않을거야라고 생각했었는데 결과적으로 아닐거라고 생각한대로 동작하는군요.

    요즘 cost based oracle fundamentals(비투엔)을 읽고 있습니다. 그중 table prefetching의 동작을 설명하는 부분이 있는데 그중 "결과집합을 rowid순으로 정렬하고 나서 테이블 액세스를 시도하기 때문에 테이블 길이를 따라 블록들을 단 한 번씩만 방문하면 된다."(p386) 라는 글이 있습니다.
    이 부분때문에 여러가지로 궁리하게 되었는데 좀더 궁리해봐야겠습니다.

    답변 감사합니다. ^^

    • 브라이언홍 2009.08.27 15:44 신고  댓글주소  수정/삭제

      http://ukja.tistory.com/166
      에서 댓글 3번에 이런 글이 있네요...
      Jonathan Lewis가 [CBO Fundamentals]에서 "Table Prefetching"이라는 용어를 사용했다고 하셨는데, 제가 볼 때는 이 책에서도 새로운 NL Join 메커니즘을 잘못 설명하고 있다고 여겨집니다(번역서에서 마침 그 부분을 제가 번역했는데, 제가 알고 있는 내용과 달라 많은 고민을 했었죠.)

  9. Favicon of http://scidb.tistory.com BlogIcon extremedb 2009.08.27 16:29 신고  댓글주소  수정/삭제  댓글쓰기

    좋은 정보 감사드립니다.

<2009.03.11 : 아래 내용중의 Right 의 의미는 Oracle 10g Performance Tuning Guide 19-21 에 나온대로 "올바른" 이란 뜻이 아니라 "Left 혹은 Right Outer Join 시의 Right" 의 의미로 바꿉니다.
하지만 이글의 핵심인 "이전버젼까지는 항상 후행집합으로 되던것이 10g 부터는 선행집합이 될수 있다." 는 바뀐것이 없습니다. >

 
오늘은 Hash Join Right  (Semi/Anti/Outer) 의 용도에 대하여 알아보려한다.
Oracle 10g 부터 Hash Join 은 서서히 변화하기 시작하였다.
특히 Hash Join Right  (Semi/Anti/Outer) 기능을 사용하여 대용량 집합의 Join 시 획기적인 성능향상을 이루었다.
Hash Join 에서 Right 옵션이 붙음으로서 획기적인 성능향상을 이루었다는 이유는 무엇일까?
Semi/Anti Join은 항상 메인 쿼리가 수행된 후 서브쿼리의 데이터를 체크하는 방식이다.
따라서 Semi/Anti Join 의 경우 서브쿼리는 항상 후행집합이 될수 밖에 없다.
Hash Outer Join 의 경우도 마찬가지로 (+) 표시가 붙는 쪽의 집합은 항상 후행집합이 될수 밖에 없었다.

하지만 10g 부터 Hash Join Right (Semi/Anti/Outer) 기능이 나오게 되면서 서브쿼리 혹은 아우터 Join 되는 쪽의 집합이 선행집합이 될수 있다.
이때 Right 의 뜻은 left 집합 대신에 right(후행집합)을 선행집합으로 하겠다는 뜻이다.
9i 까지 Hash Join (Semi/Anti/Outer)의 경우에 눈물을 머금고 대량의 집합을 선행처리할수 밖에 없었지만 이제는 자유롭게 선행집합을 선택할수 있는것이다.
국내외 튜닝책을 막론하고 이막강한 기능에 대하여 제대로 다루는 것을 본적이 없다.
왜냐하면 초대용량 DB(VLDB)에서 Join Method 를 튜닝해본 사람만이 이 기능이 얼마나 중요한지 피부로 느낄수 있기 때문이다.
아래의 스크립트를 보자.
환경 : 10.2.0.4

1.테이블을 생성하고 Aanlyze 를 한다.

1.테이블 생성
CREATE TABLE BIG_EMP AS
SELECT ROWNUM AS EMPNO, A.ENAME, A.JOB, A.MGR, A.HIREDATE, A.SAL, A.COMM, A.DEPTNO
  FROM EMP A,
       (SELECT LEVEL AS NO FROM DUAL CONNECT BY LEVEL <= 2000) B;

ALTER TABLE BIG_EMP
   ADD ( CONSTRAINT PK_BIG_EMP PRIMARY KEY (EMPNO) USING INDEX );

dbms_stats.gather_table_stats(user, 'BIG_EMP', cascade => true); 


2.Hash Semi Join 을 유도한다.

select a.empno, a.sal
  from   big_emp a
  where  exists (select /*+ use_hash(b) */
                                  b.deptno
                          from  dept b
                       where  b.deptno = a.deptno
                      ) ;

--------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name    | Starts |Cost (%CPU)| A-Rows |   A-Time   | Buffers | Used-Mem |
--------------------------------------------------------------------------------------------------------
|*  1 |  HASH JOIN RIGHT SEMI|         |      1 |    4  (25)|  26000 |00:00:00.06 |     176 813K (0)|
|   2 |   INDEX FULL SCAN    | PK_DEPT |      1 |    1   (0)|      4 |00:00:00.01 |       1 |          |
|*  3 |   TABLE ACCESS FULL  | BIG_EMP |      1 |    2   (0)|  26000 |00:00:00.01 |     175 |          |
--------------------------------------------------------------------------------------------------------



위의 통계정보를 보면 176 블럭을  scan 했으며  Hash area size 를 813 K를 사용했다는걸 알수 있다.
작은 용량의 테이블인 DEPT 를 Driving 집합(Build Input) 으로 선택하고 BIG_EMP 테이블을
후행(Probe) 테이블로 Hash 조인 함으로서 최적의 조인이 되었다.
그렇다면 Hash Join Right Semi 를 사용하지 않으면 어떻게 될것인가?
Subquery Unnesting 기능을 이용하면 작은 용량의 테이블인 DEPT 를 Driving 집합(Build Input) 으로 선택할수는 있다.
하지만 아래처럼 약간의 손해를 감수해야 한다.

select /*+ gather_plan_statistics ordered */ a.empno, a.sal
 from   big_emp a
 where  exists (select /*+ use_hash(b) */
                                b.deptno
                         from  dept b
                      where  b.deptno = a.deptno
                      );

위의 SQL 을 보면 강제로 MAIN 쿼리에 ordered 힌트를 주어 Semi Join 이 아닌 SubQuery Unnesting 이 되었다.
ordered 힌트를 사용한 이유는 서브쿼리가 Semi Join 에 실패할 경우  Subquery Unnesting 을 시도하게 되는데
이때 서브쿼리블럭이 From 절의 가장 좌측으로 오기 때문이다.
사용자가 ordered 힌트등을 사용하면 오라클 내부적인 leading 힌트와 Swap_join_inputs 힌트 등이 Override 되어 무시된다.
따라서 Semi Join 이 아닌 Subquery Unnesting 되는 것이다.
이제 Plan 을 보자.

------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name    | Starts |Cost (%CPU)| A-Rows |   A-Time   | Buffers | Used-Mem |
------------------------------------------------------------------------------------------------------
|*  1 |  HASH JOIN         |         |      1 |    5  (40)|  26000 |00:00:00.37 |     176808K (0)|
|   2 |   SORT UNIQUE      |         |      1 |    1   (0)|      4 |00:00:00.01 |       1 | 2048  (0)|
|   3 |    INDEX FULL SCAN | PK_DEPT |      1 |    1   (0)|      4 |00:00:00.01 |       1 |          |
|*  4 |   TABLE ACCESS FULL| BIG_EMP |      1 |    2   (0)|  26000 |00:00:00.31 |     175 |          |
------------------------------------------------------------------------------------------------------

 
처음 예제와 조인순서와 Scan 한 블럭수및 Hash area size 사용량은 대동소이 하지만 Subquery Unnesting 이 발생하여 불필요한 Sort 가 발생 하였다.
위의 SQL 의 경우 Subquery Unnesting 은 메인쿼리의 결과집합을 보존하기 위하여 Sort Unique 혹은 Hash Unique 작업이 추가적으로 발생된다.
Subquery Unnesting 이 항상 나쁜것은 아니지만 대용량 집합간의 조인시는 엄청난 부담이 될수 밖에 없다.
서브쿼리쪽은 Sort Unique 작업이 추가적으로 필요하기 때문이다.
그렇다고 덩치가 큰 BIG_EMP를 선행테이블로 할수도 없는것이다.
이것이 바로 Hash Join Right Semi 가 10g 에서 나타난 이유이다.
그렇다면 이럴 경우에 강제로 Hash Join Right Semi 를 발생시키려면 어떻게 해야 되겠는가?
이럴때 간단하게 사용할수 있는것이 QB_NAME 을 이용한 Global 힌트와 USE_HASH 및 SWAP_JOIN_INPUT 힌트이다.
아래의 스크립트를 보자.

select /*+ gather_plan_statistics LEADING(A) USE_HASH(@sub B) SWAP_JOIN_INPUTS(@sub B) */ a.empno, a.sal
 from   big_emp a
 where  exists (select /*+ qb_name(sub) */
                                b.deptno
                        from  dept b
                      where  b.deptno = a.deptno
                      );

---------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name    | Starts | Cost (%CPU)| A-Rows |   A-Time   | Buffers | Used-Mem |
---------------------------------------------------------------------------------------------------------
|*  1 |  HASH JOIN RIGHT SEMI|         |      1 |     4  (25)|  26000 |00:00:01.05 |     176 |  813K (0)|
|   2 |   INDEX FULL SCAN    | PK_DEPT |      1 |     1   (0)|      4 |00:00:00.01 |       1 |          |
|*  3 |   TABLE ACCESS FULL  | BIG_EMP |      1 |     2   (0)|  26000 |00:00:00.99 |     175 |          |
---------------------------------------------------------------------------------------------------------


다시 정상적인 Hash Join Right Semi 로 돌아왔다.
간단히 힌트를 설명하자면 QB_NAME 은 쿼리블럭명을 강제로 지정하는 힌트이고 Swap_join_inputs 힌트는
Probe 쪽 집합(후행 집합) 을 강제로 Build Input 집합(선행집합) 으로 바꾸는 힌트이다.
그리고 Use_hash 힌트에 대하여 한마디 하자면 원래 Use_hash 힌트는 후행 집합에 대해서만 사용하는 힌트이다.
하지만 USE_HASH(A B) 이런식으로 사용해도 ORACLE 이 힌트를 아래처럼 변환시켜버린다.
USE_HASH(A B) --> LEADING(A B) USE_HASH(B)
오라클사에서 명시적인 용어가 없기 때문에 필자는 이것을  Internal Hint Transformation 이라 부른다.
다음에 기회가 되면 Internal Hint Transformation 에 대하여 글을 올릴까 한다.

결론 : 10g 부터 나온 Hash Join Right (Semi/Anti/Outer) 기능을 적재적소에 활용하면 대용량 집합간의 join 성능을 획기적으로 향상시킬수 있다.

참고로 Hash Join Right Anti Plan 으로 유도하는 것은 Exists 대신 Not Exists 로 바꾸면 된다.
Hash Join Right Outer 를 유도하는 예제는 아래와 같다.

select /*+ LEADING(A) USE_HASH(B) SWAP_JOIN_INPUTS(B) */ a.empno, a.sal
 from   big_emp a,
          dept b
where a.deptno = b.deptno(+)  
저작자 표시 비영리 동일 조건 변경 허락
신고
크리에이티브 커먼즈 라이선스
Creative Commons License
Posted by extremedb

댓글을 달아 주세요

  1. Favicon of http://ukja.tistory.com BlogIcon 욱짜 2009.03.03 11:09 신고  댓글주소  수정/삭제  댓글쓰기

    저는 Right의 의미를 Outer Join처럼 Join Operation의 왼쪽(Left) Input으로만 위치할 수 있었던 것을 오른쪽(Right)에 둔다는 의미로 받아 들였는데요. 그러니까..

    select ... from t1, t2 where t1.c1 = t2.c1(+)

    이렇게 되면, Outer Join의 한계상 반드시 Join 순서가 (t1 --> t2)가 되었어야 했는데(t1이 왼쪽에 올 수 밖에 없음), 10g부터는 이 순서를 (t2 --> t1) 으로(t1이 오른쪽으로) 해주겠다는 의미로 생각했습니다. 아마 이것을 구현하기는 쉽지 않았을거 같습니다. Oracle 개발자들의 노고를 치하해야겠죠.

    10g가 Outer, Semi, Anit에 대해서 많은 경우 Right Join을 자연스럽게 해주니까 이것의 혜택을 전혀 인식도 못하고 쓰는 경우가 많죠. 이 시리즈를 잘 정리해서 블로그에 올리시면 상당히 좋은 자료들이 될 거 같습니다.

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2009.03.03 12:45 신고  댓글주소  수정/삭제

      Right 의 의미를 Outer 조인의 경우 그렇게 생각할수 있을거 같습니다.
      물론 이기능을 구현한 설계자에게 Right 의 의미를 정확히 물어보는것이 가장 확실히 알수 있는 방법이 되겠습니다.
      그리고 Hash Right Join 기능을 구현한것은 어렵기는해도 가능한데 Nested Loop Right Join 은 개념상 로직 구현이 불가능 할것으로 생각합니다.
      좋은 정보 감사드립니다.

  2. 고구마 2009.03.03 14:48 신고  댓글주소  수정/삭제  댓글쓰기

    열심히 보고 또 보고 있네요.
    감사합니다.

  3. Favicon of http://blog.naver.com/xsoft BlogIcon 강정식 2009.03.06 09:35 신고  댓글주소  수정/삭제  댓글쓰기

    오동규님 덕분에 'hash join right'와 'SWAP_JOIN_INPUTS'에 대해 알게되서 도움이 많이 될것 같습니다.

    좋은 내용 감사합니다.

  4. 김시연 2009.03.18 11:16 신고  댓글주소  수정/삭제  댓글쓰기

    오랜만에 들어왔더니 좋은 글들이 많네요. 좋은 글 감사합니다~!

  5. Favicon of http://scidb.tistory.com BlogIcon extremedb 2009.03.18 15:22 신고  댓글주소  수정/삭제  댓글쓰기

    오랜만 입니다.
    요즘 바쁘셨나 보네요^^.

  6. 브라이언홍 2009.07.22 17:44 신고  댓글주소  수정/삭제  댓글쓰기

    지난 6월 17일에 진행된 DB2 9.7(KDUG) ‘DB 개발자/관리자의 생산성 향상’에 대한 발표 동영상을 보니 DB2 9.7에서는 Nested Loop Right Join이 가능한가 봅니다. 음...CD를 받으면 설치하고 테스트해 봐야 겠네여..생각나서 처음으로 댓글 달아봅니다..매번 좋은 글을 올려 주셔서 감사해요^^

    http://www.kdug.kr/blog/146
    [Session 3] DB2 9.7과 오라클의 쿼리 최적화 효율성 비교 동영상..

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2009.07.22 18:26 신고  댓글주소  수정/삭제

      그렇군요.
      Nested Loop Right Join 은 오라클에서도 됩니다.
      아마도 Nested Loop Right Outer Join 을 이야기 하시는것 같습니다.
      좋은 정보 감사합니다.

  7. 예맥 2010.04.22 12:06 신고  댓글주소  수정/삭제  댓글쓰기

    use_hash(a b)가 leading(a b) + use_hash(b)로 변형된다고 하셨는데 ordered와 leading, use_hash, swap_joins_inputs를 같이 쓰면 헷갈리네요..

    환경: 10.2.0.4

    1. 힌트에 swap_join_inputs을 단독으로 쓰면 힌트가 사용되지 않고
    2. leading과 같이 사용시 swap_join_inputs의 값이 build가 되고
    3. ordered와 사용시 2번과 동일하고
    4. use_hash와 swap_join_inputs사용시 swap_join_inputs의 값이 사용되지 않고

    use_hash는 내부적으로 leading과 use_hash로 변형된다고 하셨는데 정말 변형되는지 궁굼합니다.
    여러개가 같이 쓰여면 헷갈립니다...

    5. leading + use_hash + swap_join_inputs를 사용시 swap_join_inputs가 사용됩니다.


    =============================================================================================================================
    select /*+ gather_plan_statistics leading(a) */
    a.empno, a.sal, b.deptno
    from big_emp a, dept b
    where a.deptno = b.deptno;
    ------------------------------------------------------------------------------------------------------------------------
    | 2 | TABLE ACCESS FULL| BIG_EMP | 1 | 28122 | 28000 |00:00:00.01 | 172 | | | |
    | 3 | INDEX FULL SCAN | PK_DEPT | 1 | 4 | 4 |00:00:00.01 | 4 | | | |
    -------------------------------------------------------------------------------------------------------------------

    select /*+ gather_plan_statistics leading(b) */
    a.empno, a.sal, b.deptno
    from big_emp a, dept b
    where a.deptno = b.deptno;
    -------------------------------------------------------------------------------------------------------------------
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------
    | 2 | INDEX FULL SCAN | PK_DEPT | 1 | 4 | 4 |00:00:00.01 | 1 | | | |
    | 3 | TABLE ACCESS FULL| BIG_EMP | 1 | 28122 | 28000 |00:00:00.01 | 2032 | | | |
    -------------------------------------------------------------------------------------------------------------------

    =============================================================================================================================

    select /*+ gather_plan_statistics swap_join_inputs(a)
    opt_param('_optimizer_cost_based_transformation','off') */
    a.empno, a.sal, b.deptno
    from big_emp a, dept b
    where a.deptno = b.deptno;
    -------------------------------------------------------------------------------------------------------------------
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------
    | 2 | INDEX FULL SCAN | PK_DEPT | 1 | 4 | 4 |00:00:00.01 | 1 | | | |
    | 3 | TABLE ACCESS FULL| BIG_EMP | 1 | 28122 | 28000 |00:00:00.01 | 2032 | | | |
    -------------------------------------------------------------------------------------------------------------------


    select /*+ gather_plan_statistics swap_join_inputs(b)
    opt_param('_optimizer_cost_based_transformation','off') */
    a.empno, a.sal, b.deptno
    from big_emp a, dept b
    where a.deptno = b.deptno;

    -------------------------------------------------------------------------------------------------------------------
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------
    | 2 | INDEX FULL SCAN | PK_DEPT | 1 | 4 | 4 |00:00:00.01 | 1 | | | |
    | 3 | TABLE ACCESS FULL| BIG_EMP | 1 | 28122 | 28000 |00:00:00.01 | 2032 | | | |
    -------------------------------------------------------------------------------------------------------------------

    =============================================================================================================================

    select /*+ gather_plan_statistics leading(a b) swap_join_inputs(a)
    opt_param('_optimizer_cost_based_transformation','off') */
    a.empno, a.sal, b.deptno
    from big_emp a, dept b
    where a.deptno = b.deptno;
    ------------------------------------------------------------------------------------------------------------------------
    |* 1 | HASH JOIN | | 1 | 28122 | 28000 |00:00:00.04 | 176 | 1888K| 1511K| 2336K (0)|
    | 2 | TABLE ACCESS FULL| BIG_EMP | 1 | 28122 | 28000 |00:00:00.01 | 172 | | | |
    | 3 | INDEX FULL SCAN | PK_DEPT | 1 | 4 | 4 |00:00:00.01 | 4 | | | |
    -------------------------------------------------------------------------------------------------------------------

    select /*+ gather_plan_statistics leading(a b) swap_join_inputs(b)
    opt_param('_optimizer_cost_based_transformation','off') */
    a.empno, a.sal, b.deptno
    from big_emp a, dept b
    where a.deptno = b.deptno;
    ------------------------------------------------------------------------------------------------------------------------
    |* 1 | HASH JOIN | | 1 | 28122 | 28000 |00:00:00.06 | 2033 | 1517K| 1517K| 814K (0)|
    | 2 | INDEX FULL SCAN | PK_DEPT | 1 | 4 | 4 |00:00:00.01 | 1 | | | |
    | 3 | TABLE ACCESS FULL| BIG_EMP | 1 | 28122 | 28000 |00:00:00.01 | 2032 | | | |
    -------------------------------------------------------------------------------------------------------------------


    select /*+ gather_plan_statistics leading(b a) swap_join_inputs(a)
    opt_param('_optimizer_cost_based_transformation','off') */
    a.empno, a.sal, b.deptno
    from big_emp a, dept b
    where a.deptno = b.deptno;
    -------------------------------------------------------------------------------------------------------------------
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------
    |* 1 | HASH JOIN | | 1 | 28122 | 28000 |00:00:00.06 | 176 | 1888K| 1511K| 2336K (0)|
    | 2 | TABLE ACCESS FULL| BIG_EMP | 1 | 28122 | 28000 |00:00:00.01 | 172 | | | |
    | 3 | INDEX FULL SCAN | PK_DEPT | 1 | 4 | 4 |00:00:00.01 | 4 | | | |
    -------------------------------------------------------------------------------------------------------------------

    select /*+ gather_plan_statistics leading(b a) swap_join_inputs(b)
    opt_param('_optimizer_cost_based_transformation','off') */
    a.empno, a.sal, b.deptno
    from big_emp a, dept b
    where a.deptno = b.deptno;
    -------------------------------------------------------------------------------------------------------------------
    |* 1 | HASH JOIN | | 1 | 28122 | 28000 |00:00:00.06 | 2033 | 1517K| 1517K| 814K (0)|
    | 2 | INDEX FULL SCAN | PK_DEPT | 1 | 4 | 4 |00:00:00.01 | 1 | | | |
    | 3 | TABLE ACCESS FULL| BIG_EMP | 1 | 28122 | 28000 |00:00:00.01 | 2032 | | | |
    -------------------------------------------------------------------------------------------------------------------

    =============================================================================================================================

    select /*+ gather_plan_statistics ordered swap_join_inputs(a)
    opt_param('_optimizer_cost_based_transformation','off') */
    a.empno, a.sal, b.deptno
    from big_emp a, dept b
    where a.deptno = b.deptno;
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------
    |* 1 | HASH JOIN | | 1 | 28122 | 28000 |00:00:00.06 | 176 | 1888K| 1511K| 2336K (0)|
    | 2 | TABLE ACCESS FULL| BIG_EMP | 1 | 28122 | 28000 |00:00:00.01 | 172 | | | |
    | 3 | INDEX FULL SCAN | PK_DEPT | 1 | 4 | 4 |00:00:00.01 | 4 | | | |
    -------------------------------------------------------------------------------------------------------------------

    select /*+ gather_plan_statistics ordered swap_join_inputs(b)
    opt_param('_optimizer_cost_based_transformation','off') */
    a.empno, a.sal, b.deptno
    from big_emp a, dept b
    where a.deptno = b.deptno;
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------
    |* 1 | HASH JOIN | | 1 | 28122 | 28000 |00:00:00.06 | 2033 | 1517K| 1517K| 814K (0)|
    | 2 | INDEX FULL SCAN | PK_DEPT | 1 | 4 | 4 |00:00:00.01 | 1 | | | |
    | 3 | TABLE ACCESS FULL| BIG_EMP | 1 | 28122 | 28000 |00:00:00.01 | 2032 | | | |
    -------------------------------------------------------------------------------------------------------------------

    =============================================================================================================================

    select /*+ gather_plan_statistics use_hash(a b) swap_join_inputs(a)
    opt_param('_optimizer_cost_based_transformation','off') */
    a.empno, a.sal, b.deptno
    from big_emp a, dept b
    where a.deptno = b.deptno;
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------
    |* 1 | HASH JOIN | | 1 | 28122 | 28000 |00:00:00.06 | 2033 | 1517K| 1517K| 814K (0)|
    | 2 | INDEX FULL SCAN | PK_DEPT | 1 | 4 | 4 |00:00:00.01 | 1 | | | |
    | 3 | TABLE ACCESS FULL| BIG_EMP | 1 | 28122 | 28000 |00:00:00.01 | 2032 | | | |
    -------------------------------------------------------------------------------------------------------------------


    select /*+ gather_plan_statistics use_hash(a b) swap_join_inputs(b)
    opt_param('_optimizer_cost_based_transformation','off') */
    a.empno, a.sal, b.deptno
    from big_emp a, dept b
    where a.deptno = b.deptno;
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------
    |* 1 | HASH JOIN | | 1 | 28122 | 28000 |00:00:00.06 | 2033 | 1517K| 1517K| 814K (0)|
    | 2 | INDEX FULL SCAN | PK_DEPT | 1 | 4 | 4 |00:00:00.01 | 1 | | | |
    | 3 | TABLE ACCESS FULL| BIG_EMP | 1 | 28122 | 28000 |00:00:00.01 | 2032 | | | |
    -------------------------------------------------------------------------------------------------------------------


    select /*+ gather_plan_statistics use_hash(b a) swap_join_inputs(a)
    opt_param('_optimizer_cost_based_transformation','off') */
    a.empno, a.sal, b.deptno
    from big_emp a, dept b
    where a.deptno = b.deptno;
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------
    |* 1 | HASH JOIN | | 1 | 28122 | 28000 |00:00:00.06 | 2033 | 1517K| 1517K| 814K (0)|
    | 2 | INDEX FULL SCAN | PK_DEPT | 1 | 4 | 4 |00:00:00.01 | 1 | | | |
    | 3 | TABLE ACCESS FULL| BIG_EMP | 1 | 28122 | 28000 |00:00:00.01 | 2032 | | | |
    -------------------------------------------------------------------------------------------------------------------


    select /*+ gather_plan_statistics use_hash(b a) swap_join_inputs(b)
    opt_param('_optimizer_cost_based_transformation','off') */
    a.empno, a.sal, b.deptno
    from big_emp a, dept b
    where a.deptno = b.deptno;
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------
    |* 1 | HASH JOIN | | 1 | 28122 | 28000 |00:00:00.06 | 2033 | 1517K| 1517K| 814K (0)|
    | 2 | INDEX FULL SCAN | PK_DEPT | 1 | 4 | 4 |00:00:00.01 | 1 | | | |
    | 3 | TABLE ACCESS FULL| BIG_EMP | 1 | 28122 | 28000 |00:00:00.01 | 2032 | | | |
    -------------------------------------------------------------------------------------------------------------------

    =============================================================================================================================

    select /*+ gather_plan_statistics leading(a b) use_hash(b) swap_join_inputs(a)
    opt_param('_optimizer_cost_based_transformation','off') */
    a.empno, a.sal, b.deptno
    from big_emp a, dept b
    where a.deptno = b.deptno;
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------
    |* 1 | HASH JOIN | | 1 | 28122 | 28000 |00:00:00.03 | 176 | 1888K| 1511K| 2336K (0)|
    | 2 | TABLE ACCESS FULL| BIG_EMP | 1 | 28122 | 28000 |00:00:00.01 | 172 | | | |
    | 3 | INDEX FULL SCAN | PK_DEPT | 1 | 4 | 4 |00:00:00.01 | 4 | | | |
    -------------------------------------------------------------------------------------------------------------------

    select /*+ gather_plan_statistics leading(a b) use_hash(b) swap_join_inputs(b)
    opt_param('_optimizer_cost_based_transformation','off') */
    a.empno, a.sal, b.deptno
    from big_emp a, dept b
    where a.deptno = b.deptno;
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------
    |* 1 | HASH JOIN | | 1 | 28122 | 28000 |00:00:00.06 | 2033 | 1517K| 1517K| 814K (0)|
    | 2 | INDEX FULL SCAN | PK_DEPT | 1 | 4 | 4 |00:00:00.01 | 1 | | | |
    | 3 | TABLE ACCESS FULL| BIG_EMP | 1 | 28122 | 28000 |00:00:00.01 | 2032 | | | |
    -------------------------------------------------------------------------------------------------------------------


    select /*+ gather_plan_statistics leading(b a) use_hash(a) swap_join_inputs(a)
    opt_param('_optimizer_cost_based_transformation','off') */
    a.empno, a.sal, b.deptno
    from big_emp a, dept b
    where a.deptno = b.deptno;
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------
    |* 1 | HASH JOIN | | 1 | 28122 | 28000 |00:00:00.03 | 176 | 1888K| 1511K| 2336K (0)|
    | 2 | TABLE ACCESS FULL| BIG_EMP | 1 | 28122 | 28000 |00:00:00.01 | 172 | | | |
    | 3 | INDEX FULL SCAN | PK_DEPT | 1 | 4 | 4 |00:00:00.01 | 4 | | | |
    -------------------------------------------------------------------------------------------------------------------

    select /*+ gather_plan_statistics leading(b a) use_hash(a) swap_join_inputs(b)
    opt_param('_optimizer_cost_based_transformation','off') */
    a.empno, a.sal, b.deptno
    from big_emp a, dept b
    where a.deptno = b.deptno;
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------
    |* 1 | HASH JOIN | | 1 | 28122 | 28000 |00:00:00.06 | 2033 | 1517K| 1517K| 814K (0)|
    | 2 | INDEX FULL SCAN | PK_DEPT | 1 | 4 | 4 |00:00:00.01 | 1 | | | |
    | 3 | TABLE ACCESS FULL| BIG_EMP | 1 | 28122 | 28000 |00:00:00.01 | 2032 | | | |
    -------------------------------------------------------------------------------------------------------------------

    =============================================================================================================================

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2010.04.22 18:32 신고  댓글주소  수정/삭제

      안녕하세요. 예맥님
      Swap_join_inputs 힌트는 후행테이블(Probe)에 사용하셔야 합니다.
      그리고 오라클이 order 힌트나 use_hash(a b)를 내부적으로 어떻게 바꾸는지는 아래처럼 포맷을 설정하시면 됩니다. 옵티마이져가 사용한 힌트를 볼 수 있습니다.

      SELECT * FROM
      TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL, 'allstats last -rows +alias +outline +predicate'));

  8. Stargazer 2010.08.20 10:48 신고  댓글주소  수정/삭제  댓글쓰기

    안녕하세요. 오동규님.

    먼저 이렇게 좋은 내용들을 공개해주신것에 대해 많은 고마움을 느끼고 있습니다.


    위에서 말씀하시 HASH JOIN RIGHT SEMI에 대해 제가 약간 혼란 스러운것이 있어
    질문을 드립니다.



    본문내용중에..
    "따라서 Semi Join 이 아닌 Subquery Unnesting 되는 것이다."

    subquery unnest와 Semi Join이 별개 operation 라는 느낌을 받았습니다.

    일단 subquery unnest가 되어 subquery 가 1집합을 보장하지 못할때
    subquery 가 먼저 풀리면 sort unique, hahs unique작업 을 하고

    main query가 먼저 풀리면 semi join으로 풀리는 것으로 알고 있는데...


    HASH JOIN RIGHT SEMI 는 일단 먼저 subquery unnest가 발생하는것이 아닌가요??


    전제조건이 subquery unnest가 발생하고
    subquery가 먼저 풀리면 위에서 말씀하신 HASH JOIN RIGHT SEMI 작업이나 또는 sort unique 작업을 하는게 아닌지요..


    답변 부탁드립니다.

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2010.08.20 11:28 신고  댓글주소  수정/삭제

      Stargazer님 반갑습니다.
      먼저 아래의 글을 읽어보시기 바랍니다.
      http://scidb.tistory.com/entry/SubQuery-Using-Method-1

      글에서 보시는대로 서브쿼리는 크게 5가지로 나눌 수 있습니다.
      _unnest_subquery 파라미터는 semi join에 영향을 끼치지 않고 서브쿼리 Unnesting 에만 영향을 끼칩니다. 반대로 _always_semi_join은 semi join에만 영행을 끼칩니다.

      물론 Unnesting과 Semi/Anti 를 합쳐서 SubQuery Flattening이라고 합니다. 사람들이 SubQuery Flattening을 두리뭉실하게 Unnesting이라고 부릅니다. 하지만 관장하는 파리미터도 다르고 로직도 다릅니다.

      Unnesting과 Semi의 차이는 간단합니다.
      Semi는 첫번째 조건을 만족하면 빠져나가는 것이고 Unnesting은 첫번재 건 뿐만아니라 전체건을 조인해야 하는 것입니다.
      감사합니다.

  9. Stargazer 2010.08.20 19:39 신고  댓글주소  수정/삭제  댓글쓰기

    답변 감사드립니다.

    말씀하신것처럼 ... Semi와 unnesting은 별개 단계의 작업이란것은 알고 있습니다.

    위에서 말씀하신 Hash Join Right Semi는 sub query부분이 먼저 driving이 되었는데도 불구하고
    1집합을 만드는 작업없이 바로 hash semi right 조인으로 풀렸습니다.


    제가 알고 있는 sub query는 크게 unnest로 발생하는경우와 no unnest가 발생하지 않는 단지 두 가지 경우로
    풀리는걸로 알고 있습니다.

    일단 hash semi right 조인도 sub query가 unnest가 되어 inline view로 변경이되어야 한다는 전제 조건을 만족하는게 아닌가 입니다.

    아님 10g부터는 sub query가 1.unnest로 풀릴경우 2.no_unnest로 풀리경우 3. Hash Join Right Semi로 풀리경우
    이렇게 3가지로 봐야 하는지요?

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2010.08.24 18:12 신고  댓글주소  수정/삭제

      답변이 늦어 죄송합니다.
      차이점만 알고 계신다면 서브쿼리가 없어지고 From 절로 올라가는 작업(Subquery Flattening)을 unnesting으로 불러도 무방합니다.

      서브쿼리와 관련된 쿼리변환은 3가지가 아니라 15가지 입니다. 물론 책(The Logical Optimizer)을 기준으로 했습니다. Part 2에서 13가지, Part 3의 두가지 입니다.

  10. Stargazer 2010.08.25 20:36 신고  댓글주소  수정/삭제  댓글쓰기

    음..그럼..일단 subquery가 from절로 올라가는거군요. 말씀하신 Subquery Flattening.

    "Subquery Flattening" 사실 이 용어 자체가 저한텐 아직 낯설군요..

    답변 감사하고요... 좋은글, 좋은 책에 대해 늘 감사하게 생각하고 있습니다.

한 개발자가 Full Outer Join 에 대한 실행계획에 대하여 질문을 해왔다.
그개발자는 아래와 같은 이야기를 설명 하였다.

하나의 테이블 기준으로 outer Join 을 한다
Union all
반대편 테이블을 기준으로 다시 outer join 을 한다.

여러분들은 위의 이야기를 어떻게 생각하는가?
개발자의 이야기는 반(윗부분)은 맞고 반(아랫부분)은 틀리다.

환경은 Oracle 10g 버젼 10.2.0.4 이다.
아래의 SQL 을 보자.

select /*+ gather_plan_statistics */ a.empno, a.ename, b.dname
  from  emp a full outer join dept b
    on  (a.deptno= b.deptno) ;

-------------------------------------------------------------------------------------------
| Id  | Operation                      | Name      | Starts | A-Rows | Buffers | Used-Mem |
-------------------------------------------------------------------------------------------
|   1 |  VIEW                          |           |      1 |     14 |      26 |          |
|   2 |   UNION-ALL                    |           |      1 |     14 |      26 |          |
|   3 |    NESTED LOOPS OUTER          |           |      1 |     14 |      23 |          |
|   4 |     TABLE ACCESS FULL          | EMP       |      1 |     14 |       8 |          |
|   5 |     TABLE ACCESS BY INDEX ROWID| DEPT      |     14 |     13 |      15 |          |
|*  6 |      INDEX UNIQUE SCAN         | PK_DEPT   |     14 |     13 |       2 |          |
|   7 |    MERGE JOIN ANTI             |           |      1 |      0 |       3 |          |
|   8 |     TABLE ACCESS BY INDEX ROWID| DEPT      |      1 |      4 |       2 |          |
|   9 |      INDEX FULL SCAN           | PK_DEPT   |      1 |      4 |       1 |          |
|* 10 |     SORT UNIQUE                |           |      4 |      4 |       1 | 2048  (0)|
|* 11 |      INDEX FULL SCAN           | IX_EMP_N2 |      1 |     13 |       1 |          |
-------------------------------------------------------------------------------------------


윗부분은 분명히 Outer 조인으로 풀렸지만 아래부분은 ANTI 조인으로 풀렸으므로 이것은 서브쿼리를 사용한것이다.
즉 옵티마이져는 아래처럼 Query Transformation 을 하게 되는 것이다.
 
select /*+ gather_plan_statistics */ *
from (
       select a.empno, a.ename, b.dname
       from  emp a, dept b
       where a.deptno = b.deptno(+)
       union all
       select null, null, a.dname
       from dept a
       where not exists (select 1
                                    from emp b
                                where  b.deptno = a.deptno
                                )
       ) ;

-------------------------------------------------------------------------------------------
| Id  | Operation                      | Name      | Starts | A-Rows | Buffers | Used-Mem |
-------------------------------------------------------------------------------------------
|   1 |  VIEW                          |           |      1 |     14 |      26 |          |
|   2 |   UNION-ALL                    |           |      1 |     14 |      26 |          |
|   3 |    NESTED LOOPS OUTER          |           |      1 |     14 |      23 |          |
|   4 |     TABLE ACCESS FULL          | EMP       |      1 |     14 |       8 |          |
|   5 |     TABLE ACCESS BY INDEX ROWID| DEPT      |     14 |     13 |      15 |          |
|*  6 |      INDEX UNIQUE SCAN         | PK_DEPT   |     14 |     13 |       2 |          |
|   7 |    MERGE JOIN ANTI             |           |      1 |      0 |       3 |          |
|   8 |     TABLE ACCESS BY INDEX ROWID| DEPT      |      1 |      4 |       2 |          |
|   9 |      INDEX FULL SCAN           | PK_DEPT   |      1 |      4 |       1 |          |
|* 10 |     SORT UNIQUE                |           |      4 |      4 |       1 | 2048  (0)|
|* 11 |      INDEX FULL SCAN           | IX_EMP_N2 |      1 |     13 |       1 |          |
-------------------------------------------------------------------------------------------

실행계획이 Full Outer Join 과 똑같음을 알수 있다.
하지만 이것도 어디까지나 파라미터 _optimizer_native_full_outer_join 가 off 일때 까지의 이야기이다.

아래의 스크립트를 보자.

/*+ opt_param('_optimizer_native_full_outer_join', 'force') */

위의 힌트를 사용하거나 아니면 아래처럼 ALTER SESSION 을 하면 된다.

alter session set "_optimizer_native_full_outer_join" = 'force';

select /*+ gather_plan_statistics */
      a.empno, a.ename, b.dname
from  emp a full outer join dept b
  on  (a.deptno= b.deptno) ;

----------------------------------------------------------------------------------------------
| Id  | Operation             | Name     | Starts | A-Rows |   A-Time   | Buffers | Used-Mem |
----------------------------------------------------------------------------------------------
|   1 |  VIEW                 | VW_FOJ_0 |      1 |     14 |00:00:00.01 |      15 |          |
|*  2 |   HASH JOIN FULL OUTER|          |      1 |     14 |00:00:00.01 |      15 |  573K (0)|
|   3 |    TABLE ACCESS FULL  | DEPT     |      1 |      4 |00:00:00.01 |       7 |          |
|   4 |    TABLE ACCESS FULL  | EMP      |      1 |     14 |00:00:00.01 |       8 |          |
----------------------------------------------------------------------------------------------


_optimizer_native_full_outer_join 파라미터를 force 로 하자 ANTI 조인마져 사라졌다.
이기능은 Native hash full outer join 이라고 불리고 11g 부터는 기본이 force 로 되어있다.


결론: Full Outer Join 은 10g 버젼 까지는 Outer Join + Union all + Anti Jojn 이지만
      11g 부터는 성능이 향상된 Native hash full outer join 를 사용할수 있음을 기억하자.
저작자 표시 비영리 동일 조건 변경 허락
신고
크리에이티브 커먼즈 라이선스
Creative Commons License
Posted by extremedb

댓글을 달아 주세요

  1. Favicon of http://ukja.tistory.com BlogIcon 욱짜 2009.02.24 10:58 신고  댓글주소  수정/삭제  댓글쓰기

    10.2.0.4 PatchSet에서 Native Full Outer Join 기능이 추가되었군요. 10.2.0.4에서는 기본값이 off인가요? choose인가요?

  2. 박우진 2010.02.17 17:37 신고  댓글주소  수정/삭제  댓글쓰기

    양쪽다 OUTER한 셋으로 FULL OUTER 결과를 만드려면 LEFT JOIN OUTER UNION RIGHT JOIN OUTER 하면 되겠죵~

필자는 Hash 조인의 튜닝시 주의사항(Work Area 의 튜닝) 이라는 이전글에서 Right Deep Tree,
Left Deep Tree Plan 의 개념 및 튜닝에 관한 글을 작성하였는데 Bushy Tree Plan 에 관한 질문을 받았다.
질문은 "Bushy Tree Plan 은 무엇이며 어떨때 사용하는가?" 였다.
간단히 설명하면 T1 과 T2 를 조인하고 T3 와 T4 를 조인하여 2개의 결과 집합을 조인하는 방식이다.
실제로 Bushy Tree Plan SQL 을 실행시켜보자

테이블 생성 스크립트는 Hash 조인의 튜닝시 주의사항(Work Area 의 튜닝) 시 사용했던 스크립트와 동일하다.
먼저 지난번에 언급 되었던  Right Deep Tree PLAN 을 보자.

SELECT /*+ GATHER_PLAN_STATISTICS LEADING(T3 T4 T2) USE_HASH(T1 T2 T4) */
       T1.*, T2.*, T3.*, T4.*
  FROM T1, T2, T3, T4
 WHERE T1.ID = T2.ID
   AND T2.ID = T3.ID
   AND T3.ID = T4.ID
   AND T1.N1 < 50;     --> filter 조건 (대부분의 데이터를 걸러낸다)


-------------------------------------------------------------------------------
| Id  | Operation            | Name | Starts | A-Rows |   A-Time   | Used-Mem |
-------------------------------------------------------------------------------
|*  1 |  HASH JOIN           |      |      1 |     90 |00:00:00.13 | 1212K (0)|
|*  2 |   TABLE ACCESS FULL  | T1   |      1 |     90 |00:
00:00.04 |          |
|*  3 |   HASH JOIN          |      |      1 |  10000 |00:00:00.18 |   11M (0)|
|   4 |    TABLE ACCESS FULL | T2   |      1 |  10000 |00:00:00.03 |          |
|*  5 |    HASH JOIN         |      |      1 |  10000 |00:00:00.10 |   11M (0)|
|   6 |     TABLE ACCESS FULL| T3   |      1 |  10000 |00:00:00.03 |          |
|   7 |     TABLE ACCESS FULL| T4   |      1 |  10000 |00:00:00.03 |          |
-------------------------------------------------------------------------------


아래는 위의 SQL 을 Bushy Tree Plan 으로 유도하는 예제이다.

SELECT /*+ GATHER_PLAN_STATISTICS USE_HASH(T34) */
            *
  FROM (SELECT /*+ NO_MERGE LEADING(T1) USE_HASH(T2) */
                T1.ID, T1.N1, T1.PROBE_VC, T1.PROBE_PADDING,
                T2.ID ID2, T2.N1 N12, T2.PROBE_VC PROBE_VC2, T2.PROBE_PADDING PROBE_PADDING2
          FROM T1, T2
         WHERE T1.ID = T2.ID
           AND T1.N1 < 50 ) T12,
       (SELECT /*+ NO_MERGE LEADING(T3) USE_HASH(T4) */
                T3.ID, T3.N1, T3.PROBE_VC, T3.PROBE_PADDING,
                T4.ID ID4, T4.N1 N14, T4.PROBE_VC PROBE_VC4, T4.PROBE_PADDING PROBE_PADDING4
          FROM T3, T4
         WHERE T3.ID = T4.ID ) T34
WHERE T12.ID = T34.ID   ;  

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ADVANCED ALLSTATS LAST'));

-------------------------------------------------------------------------------
| Id  | Operation            | Name | Starts | A-Rows |   A-Time   | Used-Mem |
-------------------------------------------------------------------------------
|*  1 |  HASH JOIN           |      |      1 |     90 |00:00:00.09 | 1190K (0)|
|   2 |   VIEW               |      |      1 |     90 |00:00:00.06 |          |
|*  3 |    HASH JOIN         |      |      1 |     90 |00:00:00.06 | 1191K (0)|
|*  4 |     TABLE ACCESS FULL| T1   |      1 |     90 |00:00:00.04 |          |
|   5 |     TABLE ACCESS FULL| T2   |      1 |  10000 |00:00:00.03 |          |
|   6 |   VIEW               |      |      1 |  10000 |00:00:00.10 |          |
|*  7 |    HASH JOIN         |      |      1 |  10000 |00:00:00.10 |   11M (0)|
|   8 |     TABLE ACCESS FULL| T3   |      1 |  10000 |00:00:00.03 |          |
|   9 |     TABLE ACCESS FULL| T4   |      1 |  10000 |00:00:00.03 |          |
-------------------------------------------------------------------------------


Right Deep Tree PLAN 과 비교해보면 메모리 사용량이 거의 절반수준으로 떨어졌다.

참고로 DBMS_XPLAN.DISPLAY_CURSOR 사용시 ADVANCED 를 명시하면 튜닝차원에서 필요한 모든정보가
빠짐없이 출력된다.
예를 들면 Outline Data, Query Block Name, Predicate Information, Column Projection Information
등이 모두 출력이 되나
여기서는 지면관게상 생략 하였다.

결론:
Bushy Tree Plan 의 원래의 활용도는 인라인뷰 2개를 만들고 각인라인뷰 내에서 조인이 발생하며
독자적인 똑똑한 FILTER 조건이 있을때 각각의 인라인뷰를 먼저 실행시키고 조인이 완료된 인라인뷰 끼리
다시 조인하는 것이 Bushy Tree Plan의 최적 활용 방안이다.
하지만 원래의 목적과는 상관없이 Driving 테이블에 훌륭한 Filter 조건이 있는경우 Nested Loop Join 이나
Hash Join 시에 Left Deep Tree Plan 으로 유도가 안될때(조인조건의 문제가 제일 많음)
Bushy Tree Plan 으로 유도하여야 한다.
오늘의 예제는 후자를 나타낸것이다.

저작자 표시 비영리 동일 조건 변경 허락
신고
크리에이티브 커먼즈 라이선스
Creative Commons License
Posted by extremedb

댓글을 달아 주세요

  1. 혈기린 2008.11.06 10:50 신고  댓글주소  수정/삭제  댓글쓰기

    좋은자료 잘보았습니다 오늘도 하나를 알게되는군요 근데
    Right Deep Tree PLAN 이나 Left Deep Tree PLAN Bushy tree plan이라는게 정식명칭인가요 오라클에서 사용하는 ?

  2. Favicon of http://scidb.tistory.com BlogIcon extremedb 2008.11.06 17:46 신고  댓글주소  수정/삭제  댓글쓰기

    ~ TREE PLAN 은 오라클 뿐아니라 전체 RDB 에서 사용하는 공통적인 용어 입니다.
    용어를 알면 의사소통 하기가 편한데 안타깝게도 주로 학계에서만 사용하고 현장에서는 사용하지 않고 있습니다.
    장점이 많은데도 말이지요.
    예를들면 튜너끼리 이야기할 경우에도 "plan 을 bushy tree plan 으로 바꾸면 됩니다" 이라고 짧게 이야기 할수 있으나 용어를 모를경우 의사소통할때 힘이 들게 됩니다.

  3. Favicon of http://orapug.tistory.com BlogIcon 오라퍼그 2010.12.06 14:35 신고  댓글주소  수정/삭제  댓글쓰기

    이글만 3번째 봤습니다. 저한테는 어렵던 내용이 튜닝업무를 시작하면서 점점 가슴에 와닿고 있습니다.
    감사합니다.

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2010.12.06 16:13 신고  댓글주소  수정/삭제

      3번이나 읽으셨네요. 이글을 어렵게 느끼셨다면 제 불찰일 겁니다.^^ 가슴에 와 닿으셨다니 다행입니다.
      튜너가 되셨네요. 축하드립니다. 건승하세요.

HASH 조인은 DW 뿐아니라 OLTP 의 배치업무및 심지어 OLTP 의 조회성 업무에까지 적용범위를
넓혀가고 있기 때문에 그중요성은 매우크다고 할수 있다.
한가지 아쉬운점은 개발자및 DBA, 튜너 들이 PLAN 을 보고 Driving 테이블만 제대로 나오면 검증하지 않고
그냥 넘어간다는 것이다.
좀더 꼼꼼한 튜너는 SWAP_JOIN_INPUTS 이나 LEADING, ORDERED 등의 힌트로 SIZE가 적은 집합순으로
HASH 조인을 하고 실행후 결과에서 모든 HASH 조인이 Optimal Pass(주1) 가 나오면 그것으로 튜닝을 끝낸다.
하지만 HASH 조인은 다른 조인들과 달라서 몇가지 더 검증해야될 부분이 있다.
오늘은 그중에 한가지에 대하여 소개한다.


실행환경 : Oracle 10g R2

아래는 테스트시 필요한 테이블과 인덱스 생성및 통계정보를 gathering 하는 스크립트 이다.

1.테스트 테이블및 인덱스 생성

-- 테이블 생성
create table T1 as
with generator as (   select  /*+ materialize */  rownum as id
                                 from all_objects
                              where rownum <= 3000  )
select /*+ ordered use_nl(v2) */
       10000 + rownum                id,
       trunc(dbms_random.value(0,5000))    n1,   
       rpad(rownum,20)                probe_vc,
       rpad('x',1000)                probe_padding
  from generator    v1,
       generator    v2
 where rownum <= 10000;

create table T2 as select * from T1;

create table T3 as select * from T1;

create table T4 as select * from T1;

--인덱스 생성
alter table T1 add constraint T1_PK primary key(id);
alter table T2 add constraint T2_PK primary key(id);
alter table T3 add constraint T3_PK primary key(id);
alter table T4 add constraint T4_PK primary key(id);


2 통계정보 생성

EXEC dbms_stats.gather_table_stats(user, 'T1', cascade => true);
EXEC dbms_stats.gather_table_stats(user, 'T2', cascade => true);
EXEC dbms_stats.gather_table_stats(user, 'T3', cascade => true);
EXEC dbms_stats.gather_table_stats(user, 'T4', cascade => true);
 

3.Right Deep Tree, Left Deep Tree Plan 의 개념

먼저 테스트를 수행하기전에 간단하게 Right Deep Tree, Left Deep Tree Plan 에 대하여 알아보겠다.

-------------------------------------
| Id  | Operation            | Name |
-------------------------------------
|*  1 |  HASH JOIN           |      |
|*  2 |   TABLE ACCESS FULL  | T1   |
|*  3 |   HASH JOIN          |      |
|   4 |    TABLE ACCESS FULL | T2   |
|*  5 |    HASH JOIN         |      |
|   6 |     TABLE ACCESS FULL| T3   |
|   7 |     TABLE ACCESS FULL| T4   |
-------------------------------------

위와 같은 PLAN 을 오라클을 많이 사용하는 사람이라면 자주 보았을 것으로 예상한다.
아래로 내려갈수록 Operation 이 오른쪽으로 밀려난다.
이것이 Right Deep Tree Plan 이다.
필자는 Hash 조인을 사용하면서  T1 을 엑세스 할때 많은 양의 데이터가 filter 되는 경우 
Right Deep Tree Plan 이 나오면 일단 부정적으로 보고 튜닝을 시작한다.
왜냐하면 대부분의 경우 악성 plan 이기 때문이다.
위 plan 의 조인순서는 아래와 같다.
1. T3 와 T4를 조인한다.
2. T2 와 1번의 결과집합을 조인한다.
3. T1 과 2번의 결과집합을 조인한다.
따라서 실제 조인순서는 T3 --> T4 --> T2 --> T1 이지만 많은수의 개발자나 DBA 들은 이점을 놓치고 있다.
즉 T1 이 Driving 이면서 많은 양의 데이터가 filter 되는 경우 위와 같은 plan 이나오면 T1 과의 조인은
맨마지막에 실행되기 떄문에 T3 --> T4 --> T2 조인이 처리될때 까지 데이터의 범위를 줄일수가 없다.
다시말하면 전체건에 대하여 2번을 조인한 후에 T1 과 조인 하기 때문에 최악의 Hash 조인이라는 것이다.

아래의 PLAN 은 위의 plan 과 반대이며 Left Deep Tree PLAN 이라고 한다.
아래 plan 의 조인순서는 당연히 T1 --> T2 --> T3 --> T4 이며 T1 테이블의 엑세스 및 Filter 과정에서
많은수의 데이터가 FILTER 된다면 최적의 PLAN 이다.

-------------------------------------
| Id  | Operation            | Name |
-------------------------------------
|*  1 |  HASH JOIN           |      |
|*  2 |   HASH JOIN          |      |
|*  3 |    HASH JOIN         |      |
|*  4 |     TABLE ACCESS FULL| T1   |
|   5 |     TABLE ACCESS FULL| T2   |
|   6 |    TABLE ACCESS FULL | T3   |
|   7 |   TABLE ACCESS FULL  | T4   |
-------------------------------------


이제 2개의 plan 을 테스트 해보자.
아래의 Right Deep Tree Plan 테스트는 실제환경에서 나오지 말아야 할 PLAN 을 테스트 한것이다.
테스트를 위하여 힌트를 주어 일부러 악성 plan 을 만들었다.
특히 힌트부분을 주목하라.

4.Right Deep Tree Plan 테스트

SELECT /*+ GATHER_PLAN_STATISTICS LEADING(T3 T4 T2) USE_HASH(T1 T2 T4) */
       T1.*, T2.*, T3.*, T4.*
  FROM T1, T2, T3, T4
 WHERE T1.ID = T2.ID
   AND T2.ID = T3.ID
   AND T3.ID = T4.ID
   AND T1.N1 < 50;     --> filter 조건 (대부분의 데이터를 걸러낸다)

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

-------------------------------------------------------------------------------
| Id  | Operation            | Name | Starts | A-Rows |   A-Time   | Used-Mem |
-------------------------------------------------------------------------------
|*  1 |  HASH JOIN           |      |      1 |     90 |00:00:00.13 | 1212K (0)|
|*  2 |   TABLE ACCESS FULL  | T1   |      1 |     90 |00:00:00.04 |          |
|*  3 |   HASH JOIN          |      |      1 |  10000 |00:00:00.18 |   11M (0)|
|   4 |    TABLE ACCESS FULL | T2   |      1 |  10000 |00:00:00.03 |          |
|*  5 |    HASH JOIN         |      |      1 |  10000 |00:00:00.10 |   11M (0)|
|   6 |     TABLE ACCESS FULL| T3   |      1 |  10000 |00:00:00.03 |          |
|   7 |     TABLE ACCESS FULL| T4   |      1 |  10000 |00:00:00.03 |          |
-------------------------------------------------------------------------------


위 SQL 은 FILTER 조건 (T1.N1 < 50) 이 있지만 마지막 조인시 까지 조인건수를 줄일수 없으므로
hash area size 를 많이 쓰게 된다. 
그결과 수행속도가 느리며 특히 메모리 사용량(Used-Mem 참조)이 23.2(11MB + 11MB + 1212K) MB 에 달한다.
Hash 조인 3번의 수행시간(A-Time 참조) 또한 0.13 + 0.18 + 0.10 으로 0.41 초나 걸렸다.
3번의 조인 모두 Optimal Pass(빨강색 부분이 0 이면 Optimal 임) 라고 방심하면 안된다.
여기에는 엄청난 비효율이 숨어 있다.

아래는 Left Deep Tree PLAN 으로 튜닝하여 테스트를 수행한 결과이다.

4.Left Deep Tree PLAN 테스트

SELECT /*+ GATHER_PLAN_STATISTICS LEADING(T1 T2 T3) USE_HASH(T2 T3 T4) */
       T1.*, T2.*, T3.*, T4.*
  FROM T1, T2, T3, T4
 WHERE T1.ID = T2.ID
   AND T2.ID = T3.ID
   AND T3.ID = T4.ID
   AND T1.N1 < 50;        --> filter 조건 (대부분의 데이터를 걸러낸다)

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

-------------------------------------------------------------------------------
| Id  | Operation            | Name | Starts | A-Rows |   A-Time   | Used-Mem |
-------------------------------------------------------------------------------
|*  1 |  HASH JOIN           |      |      1 |     90 |00:00:00.07 | 1220K (0)|
|*  2 |   HASH JOIN          |      |      1 |     90 |00:00:00.10 | 1229K (0)|
|*  3 |    HASH JOIN         |      |      1 |     90 |00:00:00.07 | 1229K (0)|
|*  4 |     TABLE ACCESS FULL| T1   |      1 |     90 |00:00:00.04 |          |
|   5 |     TABLE ACCESS FULL| T2   |      1 |  10000 |00:00:00.03 |          |
|   6 |    TABLE ACCESS FULL | T3   |      1 |  10000 |00:00:00.03 |          |
|   7 |   TABLE ACCESS FULL  | T4   |      1 |  10000 |00:00:00.03 |          |
-------------------------------------------------------------------------------


Used-Mem 컬럼의 사용량이 Right Deep Tree Plan 에 비해 대폭 줄어든것에 주목해야 한다.
Hash 조인시 사용한 총 hash area size 사용량 = 1220K + 1229K + 1229K (약 3.6 MB) 이다.
Left Deep Tree PLAN 을 사용하는것으로 바꾸니 놀랍게도 메모리 사용량이 6.5 배나 줄었다.
또한 A-Time 의 수행시간도 약 2배나 차이가 난다.
테스트를 짧게 끝내기위해 각 테이블을 만건으로 한정 했지만 건수가 많아 질수록 차이는
더 벌어질 것이다.

결론 :
T1.N1 < 50 과 같은 훌륭한 filter 조건이 있는 경우에 통계정보의 부재, 부적절한 조인조건등 여러가지
이유로 인하여 Right Deep Tree Plan 이 나온다면 튜닝을 하여 Left Deep Tree  Plan 으로 만들어야 한다.
물론 예외적인 경우 Left Deep Tree PLAN 으로 유도 하는것이 불가능 할수도 있다.
하지만 그때에도 Bushy Tree Plan(주2) 등으로 유도하여 Hash 조인을 튜닝 하여야 한다.
그렇지 않을 경우 과도한 메모리 사용과 수행속도 저하를 막을수 없다.


주1 : Optimal Pass 란 Hash 조인시 build 테이블(Driving)을 Scan 하면서 메모리에 적재하는 과정을
       거치는데 이때 메모리 공간(Hash Area Size) 가 부족하면 메모리에 적재하지 못하고
       Temp 영역(DISK)에 적재를 하는 비효율이 있지만  Optimal Pass 는 메모리 공간이 충분하여
       Disk 작업이 없는 상태를 말한다.
주2: 간단히 설명하면 T1 과 T2 를 조인하고 T3 와 T4 를 조인하여 2개의 결과 집합을 조인하는 방식임.
     자세한 내용은
Hash 조인시 Bushy tree Plan 유도하기 를 참조하기 바란다.

저작자 표시 비영리 동일 조건 변경 허락
신고
크리에이티브 커먼즈 라이선스
Creative Commons License
Posted by extremedb

댓글을 달아 주세요

  1. 백면서생 2008.10.30 10:51 신고  댓글주소  수정/삭제  댓글쓰기

    좋은 글 잘 보고 갑니다.^^

필자가 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)

신고
크리에이티브 커먼즈 라이선스
Creative Commons License
Posted by extremedb

댓글을 달아 주세요

  1. 금땡이 2008.10.13 19:28 신고  댓글주소  수정/삭제  댓글쓰기

    전체적 내용은 이해하겠는데, 세부적으로 보면 제 지식의 한계가..ㅠㅠ
    잘 읽고, 잘 담아 갑니다.

  2. Favicon of http://scidb.tistory.com BlogIcon extremedb 2008.10.13 21:23 신고  댓글주소  수정/삭제  댓글쓰기

    10 그램 에서 나온 새기능이므로 아직 사용하는 사람이 없지만 정확한 용도와 용법을 안다면 적용할곳이
    적지않을 걸로 생각 합니다.
    참 제가 농담삼아 oracle 10g 대신에 오라클 10 그램으로 발음 합니다.^^
    오해 없으시길...

  3. Favicon of http://oraschool.tistory.com BlogIcon 시연아카데미 2008.10.15 10:55 신고  댓글주소  수정/삭제  댓글쓰기

    좋은글 잘 보고 갑니다. 감사합니다~!

  4. sid 2011.01.27 21:13 신고  댓글주소  수정/삭제  댓글쓰기

    예전에 실행계획 보면서 이건 도대체 뭔 소리야!!
    했던 실행계획의 비밀이 풀렸습니다.
    감사합니다 ^^

기획팀에서 이대리가 전산실에 와서 부탁을 한다.

기획실 이대리:"월별 부서별로 2002년도 실적을 좀 뽑아주실수 있나요?"

전산실 김대리:"네 오늘저녁 6시쯤 오시면 실적 보고서를 드릴수 있습니다."

기획실 이대리:"실적을 만드실때 주의사항이 있습니다.
                     월별 부서별로 실적을 뽑을때 만약 20번 부서에서 5월, 7월에
                     실적이 없다고 하더라고 5월,7월 실적을 0 으로 표시해주세요."

전산실 김대리:"네 알겠습니다. 그것은 별로 어렵지 않습니다."   

년월만 들어있는 테이블과 월별부서별실적 테이블의 구조는 아래와 같다.

사용자 삽입 이미지

















월별 부서별 실적테이블의 2002년 실적은 다음 그림과 같다.
모든 월에 실적이 있는것은 아니다.(예를 들면 10번 부서는 2002년도에 1,3,6,7,8,10,11 월에 실적이 없다. )
사용자 삽입 이미지














30번 부서부터는 지면관계상 그림에서 생략하였다.




기획실 이대리의 요구사항은 아래그림과 같다.
(실적이 없는달은 실적을 0 으로 표시함)
사용자 삽입 이미지
































30번 부서의 실적부터는 지면관계상 그림에서 생략하였다.


다행히 월별, 부서별 실적 테이블이 존재하기 때문에 김대리는 묵묵히 월별 실적 SQL을 아래처럼 작성하였다.
아래처럼 작성한 이유는 부서가 20개(10번부터 200번까지) 있기 때문에 부서별로 무조건 12건(1월~12월)을 만들기 위해서 이다.

SELECT e.deptno, m.yymm, NVL(e.sale_amt,0)
FROM  year_month m , dept_sale_history e
WHERE m.yymm = e.yymm(+)
  AND e.DEPTNO(+) = 10     --> 10번부서에 대해서 1월~12월 실적을 만듬.
  AND m.yymm like '2002%'
Union all
SELECT e.deptno, m.yymm, NVL(e.sale_amt,0)
FROM  year_month m , dept_sale_history e
WHERE m.yymm = e.yymm(+)
  AND e.DEPTNO(+) = 20     --> 20번부서에 대해서 1월~12월 실적을 만듬.
  AND m.yymm like '2002%'
Union all
SELECT e.deptno, m.yymm, NVL(e.sale_amt,0)
FROM  year_month m , dept_sale_history e
WHERE m.yymm = e.yymm(+)
  AND e.DEPTNO(+) = 30     --> 30번부서에 대해서 1월~12월 실적을 만듬.
  AND m.yymm like '2002%'
Union all
...........................................................................................중간생략
Union all
SELECT e.deptno, m.yymm, NVL(e.sale_amt,0)
FROM  year_month m , dept_sale_history e
WHERE m.yymm = e.yymm(+)
  AND e.DEPTNO(+) = 200     --> 200번부서에 대해서 1월~12월 실적을 만듬.
  AND m.yymm like '2002%'


우연히 김대리의 작업을 지켜보던 전산실 박과장이 한마디 한다.
전산실 박과장 :"김대리 그작업할때 200번이나 노가다(Union All) 할생각이냐?
                     "SQL 공부좀해라"
              
김대리에게 호통을 친 박과장은 자신감 있게 아래의 SQL을 1분만에 만들었다.    
           

SELECT dept_month.deptno, dept_month.yymm, NVL(e.sale_amt,0)
   FROM (SELECT d.deptno, m.yymm
                 FROM ( SELECT c.deptno
                                FROM DEPT c
                               WHERE EXISTS (SELECT 1
                                                          FROM dept_sale_history d
                                                        WHERE d.deptno = c.deptno
                                                            AND d.yymm like '2002%')) d,
                             ( SELECT m.yymm
                                  FROM year_month m         
                                 WHERE m.yymm like '2002%' ) m 
             ) dept_month,                                          --> 월별 부서별 집합을 먼저 만든다.
            dept_sale_history e        
 WHERE dept_month.deptno(+) = e.deptno
      AND dept_month.yymm(+) = e.yymm              

위의 SQL 의 핵심은 모든 부서에 대하여 1월~12월 까지 와꾸?(틀)를 만들어 놓고
부서별 월별실적 테이블과 아우터 조인을 하기위해서 이다.
위의 SQL 에서 EXISTS 를 사용한 이유는 2002 년도에 실적이 있는 부서만 뽑기 위해서다.
하지만 위의 SQL 도 비효율이 있다.
부서별 월별 실적테이블을 2번이나 ACCESS 하였다.

박과장의 작업을 옆에서 지켜보던 신입사원이 고개를 기우뚱 하며 박과장에게 말을 건낸다.
전산실 신입사원:"dept_sale_history" 테이블을 2번 사용하지 않고도 같은 실적을 뽑을수 있습니다."
전산실 박과장 :"그래? 그럼 한번해봐"

신입사원을 지켜보던 박과장은 경악을 금치 못한다.
신입사원이 20초만에 SQL 을 작성하고도 성능은 신입사원의 SQL이 우수했기 때문이다.
단 4줄의 SQL 로 기획팀 이대리의 요구사항을 해결하였다.
박과장은 SQL 을 사용한지 10년이 넘는 배테랑 개발자 이지만 10g 신기능은 써보지 못한 상태였다.
아래의 SQL이 신입사원의 SQL 이다.

SELECT e.deptno, m.yymm, NVL(e.sale_amt,0)
FROM  year_month m LEFT OUTER JOIN dept_sale_history e
           PARTITION BY (e.deptno) ON (m.yymm = e.yymm )
WHERE m.yymm like '2002%';

신입사원이 위의 SQL 을 사용할수 있었던건 처음 배운 SQL 문법이 Oracle 10g 기준이었고
박과장은 Oracle 8 버젼의 SQL을 공부 해었기 때문이다.

위의 Partition Outer Join 은 10g 의 새기능이다.
Partition Outer Join 의 기능을 요약하면 부서별로 중간중에 빠진 월의 실적을 생성해주는 기능이다.     

결론 :
Partition Outer Join 은 10g 의 신기능중 일부에 불과하다.
버전별로 New Features의 중요성을 다시한번 강조하지만 위의 경우와 같이
신기능을 모르면 작업량이 늘어날수 밖에 없고 대부분 성능도 느리다.
또한 Oracle 8.0 시절에 최적화된 SQL 이 항상 Oracle 10g 에서 최적화된 SQL 이라고 볼수 없다.
Oracle 9i 가 나온지는 10년이 됬으며 Oralce 10g 가 나온지도 6년이 지났고 2년전에 Oracle 11g 가 나왔다.
신버젼이 나올때 마다 알라딘의 요술램프처럼 주인님이 사용해주기를 기다리는 마술 같은 여러가지 신기능이 숨어있다는 점을 기억하자.

신고
크리에이티브 커먼즈 라이선스
Creative Commons License
Posted by extremedb

댓글을 달아 주세요

  1. 금땡이 2008.08.31 10:44 신고  댓글주소  수정/삭제  댓글쓰기

    100% 공감하는 내용입니다. 공부 좀 해야겠네요.

  2. 투덜이 2011.02.22 18:30 신고  댓글주소  수정/삭제  댓글쓰기

    안녕하세요... 항상 좋은글을 읽고있는 애독자입니다...
    현재는 오동규님께서 추천해주신 Mastering Oracle SQL 2nd Edition 을 잘 보고있습니다.
    근데 한가지 궁금한게 있어 질문을 드립니다.
    ansi 표준의 sql을 이용하여 partition outer join을 구현하셨는데(또 제가 보는 책에서도) 이 문장을 Oracle표준 문장으로
    바꿀 수는 없는지요...
    예로 dept a LEFT OUTER JOIN EMP b ON a.dept_id = b.dept_id를 a.dept_id = b.dept_id(+) 이렇게요...
    아니면 partition outer join은 ansi 표준으로만 구현이 가능한가요??

    제가 아직 초보수준을 벗어나지 못해 무지한 질문을 드린게 아닌지 모르겠네요...

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2011.02.23 10:01 신고  댓글주소  수정/삭제

      투덜이님 반갑습니다.
      투덜이님 말씀대로 partition outer join은 ANSI 표준만 사용할 수 있습니다.

      보시고 계시는 Mastering Oracle SQL 2nd Edition 은 좋은 책입니다. 꼭 정복하시기 바랍니다.

  3. 투덜이 2011.02.23 10:34 신고  댓글주소  수정/삭제  댓글쓰기

    감사합니다...
    꼭 정복하도록 노력하겠습니다...
    그리고 항상 좋은내용 잘 보고있습니다..

대용량 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 을 이해하기 위해서는 반드시 알야야 하니 다시한번 꼼꼼히 살펴보기 바란다.
저작자 표시 비영리 동일 조건 변경 허락
신고
크리에이티브 커먼즈 라이선스
Creative Commons License
Posted by extremedb

댓글을 달아 주세요