'Partition Pruning'에 해당되는 글 3건

  1. 2013.05.21 PARTITION WISE JOIN이란 무엇인가? (20)
  2. 2010.03.16 Range 파티션에서 maxvalue의 진정한 의미 (6)
  3. 2009.12.24 Partition Access Pattern (13)

부제: Partition Wise Join의 성능

 

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

 

 

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

 

 

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

 

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

 

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

 

 

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

 

 

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

 

Table Generation.SQL

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

 

 

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

 

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

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

                                                                                                                                                                                     

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

 

 

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

 

 

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

  FROM SALES_PT S, SALES_ORDER_PT T

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

  AND S.PROD_ID = T.PROD_ID

  AND S.CUST_ID = T.CUST_ID

  AND S.TIME_ID = T.TIME_ID

  AND S.CHANNEL_ID = S.CHANNEL_ID

AND S.PROMO_ID = T.PROMO_ID ;

 

 

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

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

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

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

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

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

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

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

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

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

 

Outline Data

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

 

  /*+

      BEGIN_OUTLINE_DATA

      ...생략

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

      END_OUTLINE_DATA

  */

 

Predicate Information (identified by operation id):

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

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

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

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

 

 

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

 

 

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

 

 

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

  FROM SALES_PT S, SALES_ORDER_PT T                                                                     

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

  AND S.PROD_ID = T.PROD_ID                                                                              

  AND S.CUST_ID = T.CUST_ID                                                                             

  AND S.TIME_ID = T.TIME_ID                                                                              

  AND S.CHANNEL_ID = S.CHANNEL_ID                                                                       

  AND S.PROMO_ID = T.PROMO_ID      ;                                                                     

                                                                                                      

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

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

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

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

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

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

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

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

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

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

                                                                                                        

Outline Data                                                                                             

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

                                                                                                         

  /*+                                                                                                   

      BEGIN_OUTLINE_DATA                                                                                

      IGNORE_OPTIM_EMBEDDED_HINTS                                                                       

      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')                                                             

      DB_VERSION('11.2.0.1')                                                                             

      ALL_ROWS                                                                                          

      OUTLINE_LEAF(@"SEL$1")                                                                            

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

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

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

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

      END_OUTLINE_DATA                                                                                   

  */                                                                                                    

                                                                                                        

Predicate Information (identified by operation id):                                                     

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

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

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

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

 

 

 

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

 

 

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

  FROM SALES_PT PARTITION (ST_Q1_2001);

 

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

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

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

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

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

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

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

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

 

 

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

 

 

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

 

 

ALTER SESSION SET "_bloom_pruning_enabled" = TRUE;

 

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

  FROM SALES_PT S, SALES_ORDER_NO_PT T

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

  AND S.PROD_ID = T.PROD_ID

  AND S.CUST_ID = T.CUST_ID

  AND S.TIME_ID = T.TIME_ID

  AND S.CHANNEL_ID = S.CHANNEL_ID

  AND S.PROMO_ID = T.PROMO_ID        ;

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

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

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

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

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

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

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

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

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

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

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

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     

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

 

                                                                                          

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

 

 

ALTER SESSION SET "_bloom_pruning_enabled" = false;

 

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

  FROM SALES_PT S, SALES_ORDER_NO_PT T

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

  AND S.PROD_ID = T.PROD_ID

  AND S.CUST_ID = T.CUST_ID

  AND S.TIME_ID = T.TIME_ID

  AND S.CHANNEL_ID = S.CHANNEL_ID

  AND S.PROMO_ID = T.PROMO_ID     ;

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

 

 

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

 

 

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

 

 

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

  FROM SALES_PT S, SALES_ORDER_PT T

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

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

  AND S.PROD_ID = T.PROD_ID

  AND S.CUST_ID = T.CUST_ID

  AND S.TIME_ID = T.TIME_ID

  AND S.CHANNEL_ID = S.CHANNEL_ID

  AND S.PROMO_ID = T.PROMO_ID     ;

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

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

 

 

 

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

 

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

 

 

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

  FROM SALES_PT S, SALES_ORDER_PT T

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

  AND S.PROD_ID = T.PROD_ID

  AND S.CUST_ID = T.CUST_ID

  AND S.TIME_ID = T.TIME_ID

  AND S.CHANNEL_ID = S.CHANNEL_ID

  AND S.PROMO_ID = T.PROMO_ID     ;

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

 

 

 

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

 

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


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

                                                                                                                                                                                                                                                                                    

 

PWJ 성능향상의 두 번째 원리 

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

 

 

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

  FROM SALES_NO_PT S, SALES_ORDER_NO_PT T

 WHERE S.PROD_ID = T.PROD_ID

   AND S.CUST_ID = T.CUST_ID

   AND S.TIME_ID = T.TIME_ID

   AND S.CHANNEL_ID = S.CHANNEL_ID

   AND S.PROMO_ID = T.PROMO_ID   ;

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

  

 

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

 

  

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

  FROM SALES_PT S, SALES_ORDER_PT T

 WHERE S.PROD_ID = T.PROD_ID

   AND S.CUST_ID = T.CUST_ID

   AND S.TIME_ID = T.TIME_ID

   AND S.CHANNEL_ID = S.CHANNEL_ID

   AND S.PROMO_ID = T.PROMO_ID   ;

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

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

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

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

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

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

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

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

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

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

                                                                                                                                                                                                                                                                                                            

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

  

 

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

 

 

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

 

  

 

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

  FROM SALES_PT S, SALES_ORDER_PT T

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

  AND S.PROD_ID = T.PROD_ID

  AND S.CUST_ID = T.CUST_ID

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

  AND S.CHANNEL_ID = S.CHANNEL_ID

  AND S.PROMO_ID = T.PROMO_ID     ;

 

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

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

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

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

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

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

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

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

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

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

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

 

Predicate Information (identified by operation id):                                                                  

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

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

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

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

 

 

 

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

 

 

결론

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

 

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

 

 

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

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

 

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

 

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

 

 

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

 

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

 

Posted by extremedb

댓글을 달아 주세요

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

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

    • Favicon of https://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 https://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.

  15. Favicon of http://magnet1612.tistory.com BlogIcon 부로긱 2018.01.17 22:18 신고  댓글주소  수정/삭제  댓글쓰기

    SQL입문하는 사람입니다.
    출처 남기고 퍼가도 될까요? 단어가 생소해서 어렵네요..

"멀티 컬럼으로 Range 파티션을 할 경우 Where 절에 파티션 선두 컬럼에 해당하는 조건만 주어도 Partition Pruning이 수행된다"

위의 말이 사실일까? 어디서 흘러나온 말인지 모르겠으나 위의 경우는 Partition Pruning이 제대로 되지 않는다. 함정이 기다리고 있기 때문이다.

아래의 스크립트를 실행해서 직접 증명해보자.          

 

환경: Oracle 10.2.0.4

       4 Node RAC

 

CREATE TABLE t (

  id NUMBER,

  d1 DATE,

  day_num VARCHAR2(2), 

  inst_id NUMBER(1),

  pad VARCHAR2(4000),

  CONSTRAINT t_pk PRIMARY KEY (id)

)

PARTITION BY RANGE (day_num,inst_id) (

  PARTITION pt_1_1 VALUES LESS THAN ('1', 2),

  PARTITION pt_1_2 VALUES LESS THAN ('1', 3),

  PARTITION pt_1_3 VALUES LESS THAN ('1', 4),

  PARTITION pt_1_4 VALUES LESS THAN ('1', 5),

  PARTITION pt_2_1 VALUES LESS THAN ('2', 2),

  PARTITION pt_2_2 VALUES LESS THAN ('2', 3),

  PARTITION pt_2_3 VALUES LESS THAN ('2', 4),

  PARTITION pt_2_4 VALUES LESS THAN ('2', 5),

  PARTITION pt_3_1 VALUES LESS THAN ('3', 2),

  PARTITION pt_3_2 VALUES LESS THAN ('3', 3),

  PARTITION pt_3_3 VALUES LESS THAN ('3', 4),

  PARTITION pt_3_4 VALUES LESS THAN ('3', 5),  

  PARTITION pt_4_1 VALUES LESS THAN ('4', 2),

  PARTITION pt_4_2 VALUES LESS THAN ('4', 3),

  PARTITION pt_4_3 VALUES LESS THAN ('4', 4),

  PARTITION pt_4_4 VALUES LESS THAN ('4', 5), 

  PARTITION pt_5_1 VALUES LESS THAN ('5', 2),

  PARTITION pt_5_2 VALUES LESS THAN ('5', 3),

  PARTITION pt_5_3 VALUES LESS THAN ('5', 4),

  PARTITION pt_5_4 VALUES LESS THAN ('5', 5),     

  PARTITION pt_6_1 VALUES LESS THAN ('6', 2),

  PARTITION pt_6_2 VALUES LESS THAN ('6', 3),

  PARTITION pt_6_3 VALUES LESS THAN ('6', 4),

  PARTITION pt_6_4 VALUES LESS THAN ('6', 5),   

  PARTITION pt_7_1 VALUES LESS THAN ('7', 2),

  PARTITION pt_7_2 VALUES LESS THAN ('7', 3),

  PARTITION pt_7_3 VALUES LESS THAN ('7', 4),

  PARTITION pt_7_4 VALUES LESS THAN ('7', 5)

);

 

Table created.

 

먼저 실습을 진행할 테이블을 생성하였다. day_num 컬럼은 1~7 로 구성되어 있다. 모든 일자 데이터를 7등분 하기 위함이다. inst_id 컬럼은 RAC에서 Instance 번호이며 1~4로 구성된다. gc buffer busy등을 줄이기 위한 용도로 자주 사용된다.

 

이제 테스트용 데이터를 insert 하자.

 

INSERT INTO t

SELECT rownum AS id,

       trunc(to_date('2007-01-01','yyyy-mm-dd')+rownum/27.4) AS d1,

       to_char(trunc(to_date('2007-01-01','yyyy-mm-dd')+rownum/27.4),'d') AS day_num,

       1+mod(rownum,4) AS inst_id,

       dbms_random.string('p',255) AS pad

FROM dual

CONNECT BY level <= 10000

ORDER BY dbms_random.value;

 

10000 rows created.

 

commit;

 

BEGIN

  dbms_stats.gather_table_stats(

    ownname          => user,

    tabname          => 'T',

    estimate_percent => 100,

    method_opt       => 'for all columns size skewonly',

    cascade          => TRUE

  );

END;

/

 

PL/SQL procedure successfully completed.

 


각 파티션에 데이터가 골고루 Insert 되었는지 살펴보자.

SELECT partition_name, partition_position, num_rows

 FROM user_tab_partitions

 WHERE table_name = 'T'

 ORDER BY partition_position;

 

PARTITION_NAME                 PARTITION_POSITION   NUM_ROWS

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

PT_1_1                                          1        356

PT_1_2                                          2        358

PT_1_3                                          3        355

PT_1_4                                          4        355

PT_2_1                                          5        364

PT_2_2                                          6        364

PT_2_3                                          7        362

PT_2_4                                          8        362

PT_3_1                                          9        358

PT_3_2                                         10        355

PT_3_3                                         11        355

PT_3_4                                         12        356

PT_4_1                                         13        357

PT_4_2                                         14        355

PT_4_3                                         15        355

PT_4_4                                         16        358

PT_5_1                                         17        355

PT_5_2                                         18        355

PT_5_3                                         19        357

PT_5_4                                         20        358

PT_6_1                                         21        355

PT_6_2                                         22        355

PT_6_3                                         23        358

PT_6_4                                         24        356

PT_7_1                                         25        355

PT_7_2                                         26        358

PT_7_3                                         27        358

PT_7_4                                         28        355

 

28 rows selected.


모든 파티션에 건수가 골고루 분배되었다. 그럼 이제 파티션의 선두 컬럼 조건만 있을 경우 Partition Pruning이 수행되는지 알아보자.

 

SELECT /*+ GATHER_PLAN_STATISTICS */

       COUNT(*)

  FROM T

 WHERE DAY_NUM = '3';

 

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

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

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

|   1 |  SORT AGGREGATE           |      |      1 |       |       |      1 |     115 |

|   2 |   PARTITION RANGE ITERATOR|      |      1 |     9 |    13 |   1424 |     115 |

|*  3 |    TABLE ACCESS FULL      | T    |      5 |     9 |    13 |   1424 |     115 |

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


불필요한 파티션을 액세스 한다
Pstart
Pstop 항목을 보면 9번째 파티션부터 13번째 파티션까지 Scan 되었다.

Partition Pruning이 잘된 것처럼 보이지만 자세히 보면 DAY_NUM 3인 파티션은 4개뿐인데 5개의 파티션을 액세스 하였다.

 

어떻게 된 것인가?

DAY_NUM 3인 파티션은 4개 이지만 inst_id 컬럼에 어떤 값이 있을지 알 수 없으므로 13번째 파티션을 액세스 할 수 밖에 없는 것이다. 다시 말하면 DAY_NUM 3이면서 inst_id 6인 데이터는 13번째 파티션에 들어갈 수 있으므로 불필요한 Scan이 일어나는 것이다.

 

물론 아래와 같이 DAY_NUM 조건과 INST_ID 조건을 같이 준다면 이런 현상은 발생하지 않는다.

 

SELECT /*+ GATHER_PLAN_STATISTICS */

       COUNT(*)

  FROM T

 WHERE DAY_NUM = '3'

   AND INST_ID = 2;

  

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

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

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

|   1 |  SORT AGGREGATE         |      |      1 |       |       |      1 |      23 |

|   2 |   PARTITION RANGE SINGLE|      |      1 |    10 |    10 |    355 |      23 |

|*  3 |    TABLE ACCESS FULL    | T    |      1 |    10 |    10 |    355 |      23 |

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


정확히 10번째 파티션만 액세스 하였다.

만약 INST_ID 조건을 줄 수 없는 경우라면?

모든 경우에 INST_ID 조건을 줄 수는 없을 것이다. 예를 들면 전체를 처리해야 하는 경우는 INST_ID 조건을 줄 수 없을 것이다. 이때 어떻게 하면 비효율을 없앨 수 있겠는가?


해결방법1

특정 inst_id 조건을 주지 못할 때는 inst_id 의 범위를 주어 불필요한 파티션 scan을 방지한다.

 

SELECT /*+ GATHER_PLAN_STATISTICS */

       COUNT(*)

  FROM T

 WHERE DAY_NUM = '3'

   AND INST_ID BETWEEN 1 AND 4;

 

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

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

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

|   1 |  SORT AGGREGATE           |      |      1 |       |       |      1 |      92 |

|   2 |   PARTITION RANGE ITERATOR|      |      1 |     9 |    12 |   1424 |      92 |

|*  3 |    TABLE ACCESS FULL      | T    |      4 |     9 |    12 |   1424 |      92 |

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


정확히 4개의 파티션만 액세스 하여 비효율이 사라졌다..

 

해결방법2

조건을 주지 못할 때는 4번째 inst_id 파티션을 maxvalue로 바꾼다.

 

CREATE TABLE t (

  id NUMBER,

  d1 DATE,

  day_num VARCHAR2(2), 

  inst_id NUMBER(1),

  pad VARCHAR2(4000),

  CONSTRAINT t_pk PRIMARY KEY (id)

)

PARTITION BY RANGE (day_num,inst_id) (

  PARTITION pt_1_1 VALUES LESS THAN ('1', 2),

  PARTITION pt_1_2 VALUES LESS THAN ('1', 3),

  PARTITION pt_1_3 VALUES LESS THAN ('1', 4),

  PARTITION pt_1_4 VALUES LESS THAN ('1', maxvalue),

  PARTITION pt_2_1 VALUES LESS THAN ('2', 2),

  PARTITION pt_2_2 VALUES LESS THAN ('2', 3),

  PARTITION pt_2_3 VALUES LESS THAN ('2', 4),

  PARTITION pt_2_4 VALUES LESS THAN ('2', maxvalue),

  ...중간생략

  PARTITION pt_7_1 VALUES LESS THAN ('7', 2),

  PARTITION pt_7_2 VALUES LESS THAN ('7', 3),

  PARTITION pt_7_3 VALUES LESS THAN ('7', 4),

  PARTITION pt_7_4 VALUES LESS THAN ('7', maxvalue)

);

 

위에서 실행한 insert문과 dbms_stats을 여기서 다시 실행하고 아래의 SQL을 수행하자.

 

SELECT /*+ GATHER_PLAN_STATISTICS */

       COUNT(*)

  FROM T

 WHERE DAY_NUM = '3';


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

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

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

|   1 |  SORT AGGREGATE           |      |      1 |       |       |      1 |      92 |

|   2 |   PARTITION RANGE ITERATOR|      |      1 |     9 |    12 |   1424 |      92 |

|*  3 |    TABLE ACCESS FULL      | T    |      4 |     9 |    12 |   1424 |      92 |

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

 

정확히 4개의 파티션만 액세스 하였다. 비효율이 없어진 것이다.

 

결론

이제 maxvalue의 진정한 의미를 알겠는가?

단순히 마지막 값이라는 의미 이외에 Partition Pruning에 영향을 끼치며 이것은 성능과 직결된다.

또한 멀티 컬럼으로 Range 파티션을 할 경우 where 조건에 따라 불필요한 파티션을 액세스 하므로 주의해야 한다.  

 

PS

위의 해결책 1,2 를 통해 문제를 해결하지 않으면 해당 select문의 성능저하뿐만 아니라 해당 테이블을 사용하는 DML문의 성능이 저하되는 끔찍한 현상이 발생할 수 있다. 다음시간에 이 문제에 대하여 논의 해보자.


Posted by extremedb

댓글을 달아 주세요

  1. 타락천사 2010.03.16 16:25  댓글주소  수정/삭제  댓글쓰기

    MAXVALUE 가 끝임을 알려주는 키워드라는 이야기네요
    감사합니다.

  2. feelie 2010.03.16 17:40  댓글주소  수정/삭제  댓글쓰기

    내용감사합니다.
    궁금한점이 하나 있습니다.
    maxvalue가 끝을알려주는 것이라고 하셨는데요.
    업무가 변경되어 id_num이 새로 2~3개의 값이 추가 되었을 경우
    어떤방법이 일을지 궁금합니다.
    새로 생성을 해야하는지, 아님 기존의 것을 이용할수도 있는지 궁금합니다..

    • Favicon of https://scidb.tistory.com BlogIcon extremedb 2010.03.16 17:46 신고  댓글주소  수정/삭제

      id_num은 무엇을 말씀하시는 건지요?
      만약 INST_ID 컬럼을 말씀하는 것이라면 업무와 상관없습니다. INST_ID 는 RAC의 노드번호 입니다. 즉 1,2,3,4 로 구성되지요. 4대신에 maxvalue를 사용한 것 입니다.

      하지만 미래에 노드가 추가된다면 split을 해야합니다.
      즉 노드가 7번까지라면 4, 5,6을 추가하고 maxvalue를 7번으로 사용해야 겠지요.

      감사합니다.

  3. 2010.03.19 08:49  댓글주소  수정/삭제  댓글쓰기

    정말 무엇인가를 느끼게 해주네요.

    최대값을 특정할 수 없을때 아무 생각없이 MAXVALUE를 주긴했지만,
    이런용도로 사용할 수 있는 것이군요..

    잘 읽었습니다.

지난 2009년 10월달에 Oracle Data Access Pattern을 정복하라 라는 글을 통하여 데이터의 접근방법에 대하여 알아보았다. 오늘은 파티션 데이터의 접근방법에 대하여 알아보자. 필자가 이글을 올리는 이유는 실행계획에 Partition Access Pattern 이 나오지만 해석을 못하는 사람이 많이 있기 때문이다. 오늘 이글을 읽고 이해한다면 파티션에 어떻게 접근하는지 접근하는 방법은 어떤것인지 모두 알수 있다.

기본적인 Partition의 종류는 3가지이다.

1.RANGE
2.LIST
3.HASH

하지만 위의 3가지를 엑세스 패턴으로 나누고자 한다면 매우 종류가 많아진다.


Partition
RANGE Access Pattern  

1.PARTITION RANGE SINGLE
2.2.PARTITION RANGE ITERATOR
3.3.PARTITION RANGE INLIST
4.4.PARTITION RANGE ALL
5.5.PARTITION RANGE EMPTY
6.6.PARTITION RANGE OR
7.7.PARTITION RANGE SUBQUERY
8.8.PARTITION RANGE JOIN-FILTER
9.9.PARTITION RANGE MULTI-COLUMN


Partition
LIST Access Pattern
1.
1.PARTITION LIST SINGLE
2.2.PARTITION LIST ITERATOR
3.3.PARTITION LIST INLIST
4.4.PARTITION LIST ALL
5.5.PARTITION LIST EMPTY
6.6.PARTITION LIST OR
7.7.PARTITION LIST SUBQUERY
8.8.PARTITION LIST JOIN-FILTER


Partition
HASH Access Pattern
1.
1.PARTITION HASH SINGLE
2.2.PARTITION HASH ITERATOR
3.3.PARTITION HASH INLIST
4.4.PARTITION HASH ALL
5.5.PARTITION HASH SUBQUERY
6.6.PARTITION HASH JOIN-FILTER

총 23가지이다.
이모든 것을 언제 다 배운단 말인가?
이럴때 필자가 정리한 파일이 도움이 될것이다.
반드시 Partition Pruning 과 Access Pattern 을 정복하기 바란다.

사용자 삽입 이미지
사용자 삽입 이미지

Partition Access Pattern.pdf

Partition Access Pattern





PS :
첨부된 문서는 Oracle 11.1까지의 Partition Pruning 과 Access Pattern을 정리한 것이다.
배포시 출처를 밝혀주기 바란다.

참조서적:
1.Trouble Shooting Oracle Performance (Christian Antognini)
2.오라클 메뉴얼
Posted by extremedb

댓글을 달아 주세요

  1. Favicon of http://blog.naver.com/xsoft BlogIcon 강정식 2009.12.24 17:37  댓글주소  수정/삭제  댓글쓰기

    이번에도 또 하나의 컨텐츠를 만들어 내셨군요 ^^
    크리스마스 선물로 잘 보겠습니다. 동규님도 즐거운 성탄절 보내시기 바랍니다.

  2. bosingwa 2009.12.27 00:15  댓글주소  수정/삭제  댓글쓰기

    감사합니다. 잘 보겠습니다.
    새해 복 많이 받으시길...

  3. feelie 2009.12.29 17:21  댓글주소  수정/삭제  댓글쓰기

    partition된 테이블의 실행계획을 이해하기기 무척 어려웠는데..
    감사합니다.
    지난내용중에 패러럴에 대한 부분도 여러가지 있는데 아직 패러럴에 대해서도
    모르는 부분이 많습니다.
    하나씩 올려주시는 내용보면서 많이 느끼고 있습니다.

  4. 서은아빠 2009.12.31 18:28  댓글주소  수정/삭제  댓글쓰기

    한해동안 블러그 관리하랴 집필활동 하랴...프로젝트 하랴..

    고생 많으셨습니다.

    내년 한해 건강에 유념하며 원하시는 모든일 이루어지길 바램해봅니다.

    새해 복 마니 받으시기 바랍니다.

  5. patrick 2010.02.13 23:02  댓글주소  수정/삭제  댓글쓰기

    대단히 좋은 글들이 굉장이 많이 있어서 한참 동안 자료를 보다 갑니다. 몇몇 자료는 개인적으로 사용하고자 노트북에 정리를 해두었습니다. 자주 들르도록 하겠습니다. 더불어, 책도 기대하고 있겠습니다.

  6. 공의 2010.12.20 11:31  댓글주소  수정/삭제  댓글쓰기

    올려 주신 자료 잘 보고 있는중인데요
    근데 해당 자료(Partition Access Pattern.pdf)에서

    tx 테이블이 나오는데요
    해당 tx 및 tx의 index생성 스크립트를 받고
    싶은데요

    해당스크립트를 주시면 테스트 하는데 도움이
    되겠습니다.

    감사합니다. 새해복 많이 받으세요 ~~

    • Favicon of https://scidb.tistory.com BlogIcon extremedb 2010.12.20 14:15 신고  댓글주소  수정/삭제

      안녕하세요. 해당 스크립트는 TOP 책의 저자인 Christian Antognini의 책을 정리한 것입니다.
      아래의 스크립트를 받아서 9장을 테스트 해보시면 됩니다.
      http://antognini.ch/downloads/Scripts20100624.zip

      PS 영어가 가능하시다면 책을 구입하시기 바랍니다.
      감사합니다.

  7. 공의 2010.12.20 14:17  댓글주소  수정/삭제  댓글쓰기

    감사합니다.