부제: 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 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.

집계함수 내부에 Distinct를 사용할 수 있다는 것은 많은 사람들이 알고 있다. 하지만 실제로 그렇게 사용했을 때 내부적으로 무슨 일이 일어나는지 아는 사람은 드물다. 한걸음 더 나아가서 COUUNT(COL) 대신에 COUNT(Distinct COL)를 사용했다면 분명히 추가적인 부하가 존재할 것인데, 그 부하를 어떻게 해결할 것인가를 아는 사람은 거의 없을 것이다. 만약 그렇다면 SQL을 실행할 때 마다 성능이 느려질 것이고 문제를 해결할 수 없을 것이다. 여러분들에게는 그런 일이 발생하지 않는다. 이미 이 글을 읽고 있기 때문이다.

이 글은 위에서 언급된 두 가지 문제를 다룬다. 즉 내부적으로 어떤 변화가 발생하는지 알아보고, 추가적인 부하를 어떻게 없앨 수 있는지도 연구해보자. 

SQL 변경에 따른 내부적인 변화를 알아보는 가장 좋은 방법은 비교하는 것이다. 다시 말해, COUUNT(COL)로 실행했을 때의 일량과 COUNT(Distinct COL)로 사용했을 때의 일량을 비교해 보는 것이다. 따라서 우리는 SQL 두 개를 실행한 다음 각각의 작업량(실행통계)을 비교할 것이다.

환경: 오라클 11.2.0.1

CREATE TABLE SALE_T AS SELECT * FROM SALES;                                             
                                                                                        
SELECT /*+ NO_USE_HASH_AGGREGATION */                                                   
        S.PROD_ID                                                                       
       ,COUNT(S.CHANNEL_ID)                                                             
       ,SUM(S.AMOUNT_SOLD)                                                              
       ,SUM(S.QUANTITY_SOLD)                                                            
  FROM SALE_T S                                                                         
 GROUP BY S.PROD_ID;                                                                    
                                                                                        
-----------------------------------------------------------------------------------------
| Id  | Operation          | Name   | Starts | A-Rows |   A-Time   | Buffers | Used-Mem |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |      1 |     72 |00:00:01.12 |    4440 |          |
|   1 |  SORT GROUP BY     |        |      1 |     72 |00:00:01.12 |    4440 | 6144  (0)|
|   2 |   TABLE ACCESS FULL| SALE_T |      1 |    918K|00:00:00.32 |    4440 |          |
-----------------------------------------------------------------------------------------

위의 SQL이 실행되는데 시간이 1.12초 걸렸고 PGA 6144 Byte를 소모하였다. 그런데 아래처럼 COUNT DISTINCT를 추가를 추가한다면 어떻게 될까?

 SELECT /*+ NO_QUERY_TRANSFORMATION */                                                  
        S.PROD_ID                                                                       
       ,COUNT(DISTINCT S.CHANNEL_ID)                                                    
       ,SUM(S.AMOUNT_SOLD)                                                              
       ,SUM(S.QUANTITY_SOLD)                                                            
  FROM SALE_T S                                                                         
 GROUP BY S.PROD_ID;                                                                    
                                                                                        
-----------------------------------------------------------------------------------------
| Id  | Operation          | Name   | Starts | A-Rows |   A-Time   | Buffers | Used-Mem |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |      1 |     72 |00:00:02.20 |    4440 |          |
|   1 |  SORT GROUP BY     |        |      1 |     72 |00:00:02.20 |    4440 |14336  (0)|
|   2 |   TABLE ACCESS FULL| SALE_T |      1 |    918K|00:00:00.33 |    4440 |          |
-----------------------------------------------------------------------------------------
 

작업량이 증가된 이유
Distinct 만 추가했을 뿐인데 시간이 약 두 배나 걸리고 PGA도 약 두 배로 사용하였다. 그 이유는 Operation에는 나오지 않지만 내부적으로 SORT UNIQUE가 실행되기 때문이다. PROD_ID별로 SORT GROUP BY를 했음에도 CHANNEL_ID 별로 SORT UNIQUE를 다시 실행해야 한다. 92만 건의 데이터를 CHANNEL_ID 별로 SORT한 후에 중복을 제거하는 작업이 Distinct에 의해서 추가된 것이다. 그렇기 때문에 FULL TABLE SCAN의 수행시간은 거의 같지만 SORT GROUP BY의 수행시간이 0.8초에서 1.87초로 늘어나고 PGA사용량도 두 배가 된 것이다.
 

비효율을 제거하는 방법
첫 번째 의문점인 집계함수에 Distinct가 추가되면 어떤 일이 발생하는지 알아냈다. 그렇다면 두 번째 문제인 비효율(추가적인 Sort와 중복제거)을 없애는 방법은 무엇일까? SQL을 아래처럼 튜닝 할 수 있을 것이다. 

SELECT /*+ NO_USE_HASH_AGGREGATION */                                                     
        PROD_ID,                                                                          
        COUNT(S.CHANNEL_ID),                                                              
        SUM(S.AMOUNT_SOLD),                                                               
        SUM(S.QUANTITY_SOLD)                                                              
 FROM  (SELECT /*+ NO_USE_HASH_AGGREGATION */                                             
                 S.CHANNEL_ID ,                                                           
                 S.PROD_ID ,                                                              
                 SUM(S.AMOUNT_SOLD) AMOUNT_SOLD,                                          
                 SUM(S.QUANTITY_SOLD) QUANTITY_SOLD                                       
           FROM SALE_T S                                                                  
          GROUP BY PROD_ID, CHANNEL_ID) S                                                 
GROUP BY S.PROD_ID ;                                                                      
                                                                                          
-------------------------------------------------------------------------------------------
| Id  | Operation            | Name   | Starts | A-Rows |   A-Time   | Buffers | Used-Mem |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |        |      1 |     72 |00:00:01.39 |    4440 |          |
|   1 |  SORT GROUP BY NOSORT|        |      1 |     72 |00:00:01.39 |    4440 |          |
|   2 |   VIEW               |        |      1 |    228 |00:00:01.39 |    4440 |          |
|   3 |    SORT GROUP BY     |        |      1 |    228 |00:00:01.39 |    4440 |18432  (0)|
|   4 |     TABLE ACCESS FULL| SALE_T |      1 |    918K|00:00:00.33 |    4440 |          |
------------------------------------------------------------------------------------------- 

비록 PGA 사용량은 약간 늘어났지만 수행시간은 DISTINCT가 없는 SQL과 비슷해졌다. 먼저 PROD_ID, CHANNEL_IDGROUP BY 되었기 때문에 인라인뷰 외부에서는 Distinct를 할 필요가 없다. 다른 말로 표현하면 먼저 GROUP BY했기 때문에 PROD_ID 별로는 CHANNEL_ID UNIQUE 하다. 따라서 인라인뷰 외부에서는 Distinct가 필요 없게 된 것이다.

더 좋은 것은 실행계획의 Id 1을 보면 SORT GROUP BY NOSORT가 나온다. NOSORT가 나온 이유는 인라인뷰가 이미 PROD_ID SORT 되어있기 때문에 더 이상의 SORT는 필요 없기 때문이다. 따라서 추가적인 Group By의 부하는 거의 없다. 이렇게 튜닝하면 Distinct에 의한 SORT UNIQUE의 부하가 대부분 사라진다.

옵티마이저가 사람을 대신한다
집계함수에 Distinct를 사용한다면 무조건 위의 SQL처럼 튜닝 해야 하는가? 그건 아니다. 오라클 11.2를 사용한다면 Logical Optimizer SQL을 자동으로 변경시켜 준다. 아래의 튜닝 되지 않은 SQL을 실행시켜보자.
 

 SELECT /*+ NO_USE_HASH_AGGREGATION(@"SEL$5771D262")  */                                    
        S.PROD_ID                                                                           
       ,COUNT(DISTINCT S.CHANNEL_ID)                                                        
       ,SUM(S.AMOUNT_SOLD)                                                                  
       ,SUM(S.QUANTITY_SOLD)                                                                
  FROM SALE_T S                                                                             
 GROUP BY S.PROD_ID   

---------------------------------------------------------------------------------------------
| Id  | Operation            | Name     | Starts | A-Rows |   A-Time   | Buffers | Used-Mem |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |      1 |     72 |00:00:01.39 |    4440 |          |
|   1 |  SORT GROUP BY NOSORT|          |      1 |     72 |00:00:01.39 |    4440 |          |
|   2 |   VIEW               | VW_DAG_0 |      1 |    228 |00:00:01.39 |    4440 |          |
|   3 |    SORT GROUP BY     |          |      1 |    228 |00:00:01.39 |    4440 |18432  (0)|
|   4 |     TABLE ACCESS FULL| SALE_T   |      1 |    918K|00:00:00.32 |    4440 |          |
---------------------------------------------------------------------------------------------
                                                                                            
Outline Data                                                                                
-------------                                                                               
  /*+                                                                                       
      BEGIN_OUTLINE_DATA                                                                    
      …생략                                                                                
      TRANSFORM_DISTINCT_AGG(@"SEL$1")                                                      
      …생략                                                                                
      END_OUTLINE_DATA                                                                      
  */                                                                                        
  

오라클이 내부적으로 TRANSFORM_DISTINCT_AGG  힌트를 사용하였고 SQL을 자동으로 변경하였다. 실행계획도 튜닝된 SQL과 같다. 11.2 버전부터는 집계함수내부에 Distinct가 존재하면 Logical Optimizer SQL을 변경시킴으로써 성능이 향상되는 것이다. 이 기능을 Distinct To Aggregation이라고 부른다. 

아래는 10053 Trace 파일의 내용이다. 내용이 많지만 개념은 간단하다. 쿼리변환 전의 SQL을 보여주고 쿼리변환 후의 SQL을 보여준다. 그리고 두 개의 SQL 사이에는 쿼리블럭 SEL$1Distinct To Aggregation 기능에 의해서 두 개로 찢어지는 과정(SPLIT QUERY BLOCK)을 보여준다.

 

DAGG_TRANSFORM: transforming query block SEL$1 (#0)
qbcp (before transform):******* UNPARSED QUERY IS *******
SELECT "S"."PROD_ID" "PROD_ID",COUNT(DISTINCT "S"."CHANNEL_ID") "COUNT(DISTINCTS.CHANNEL_ID)",SUM("S"."AMOUNT_SOLD") "SUM(S.AMOUNT_SOLD)",SUM("S"."QUANTITY_SOLD") "SUM(S.QUANTITY_SOLD)" FROM "TLO"."SALE_T" "S" GROUP BY "S"."PROD_ID"
pgactx->ctxqbc (before transform):******* UNPARSED QUERY IS *******
SELECT "S"."PROD_ID" "PROD_ID",COUNT(DISTINCT "S"."CHANNEL_ID") "COUNT(DISTINCTS.CHANNEL_ID)",SUM("S"."AMOUNT_SOLD") "SUM(S.AMOUNT_SOLD)",SUM("S"."QUANTITY_SOLD") "SUM(S.QUANTITY_SOLD)" FROM "TLO"."SALE_T" "S" GROUP BY "S"."PROD_ID"
Registered qb: SEL$5771D262 0xea51918 (SPLIT QUERY BLOCK FOR DISTINCT AGG OPTIM SEL$1; SEL$1)
---------------------
QUERY BLOCK SIGNATURE
---------------------
  signature (): qb_name=SEL$5771D262 nbfros=1 flg=0
    fro(0): flg=0 objn=76169 hint_alias="S"@"SEL$1"

Registered qb: SEL$C33C846D 0xde78e84 (MAP QUERY BLOCK SEL$5771D262)
---------------------
QUERY BLOCK SIGNATURE
---------------------
  signature (): qb_name=SEL$C33C846D nbfros=1 flg=0
    fro(0): flg=5 objn=0 hint_alias="VW_DAG_0"@"SEL$C33C846D"

qbcp (after transform):******* UNPARSED QUERY IS *******
SELECT "VW_DAG_0"."ITEM_2" "PROD_ID",COUNT("VW_DAG_0"."ITEM_1") "COUNT(DISTINCTS.CHANNEL_ID)",SUM("VW_DAG_0"."ITEM_4") "SUM(S.AMOUNT_SOLD)",SUM("VW_DAG_0"."ITEM_3") "SUM(S.QUANTITY_SOLD)" FROM  (SELECT /*+ NO_USE_HASH_AGGREGATION */ "S"."CHANNEL_ID" "ITEM_1","S"."PROD_ID" "ITEM_2",SUM("S"."QUANTITY_SOLD") "ITEM_3",SUM("S"."AMOUNT_SOLD") "ITEM_4" FROM "TLO"."SALE_T" "S" GROUP BY "S"."CHANNEL_ID","S"."PROD_ID") "VW_DAG_0" GROUP BY "VW_DAG_0"."ITEM_2" 

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
Distinct To Aggregation 쿼리변환은Heuristic Query Transformation에 속한다. _optimizer_distinct_agg_transform 파라미터로 이 기능을 제어할 수 있고 Defaulttrue이다. 힌트로는 TRANSFORM_DISTINCT_AGG / NO_TRANSFORM_DISTINCT_AGG 를 사용할 수 있다.

이제 우리는 집계함수에 Distinct가 추가되면 SORT UNIQUE의 부하로 성능이 느려짐을 안다. Distinct 대신에 Group By를 사용하여 그 부하를 대부분 없애는 방법도 알게 되었다. 하지만 이제는 이런 일들을 옵티마이저가 대신하게 되었다. 이런 기능들이 계속 추가된다면 언젠가는 튜너라는 직업이 사라지 않을까? 만약 튜너가 없어진다면, 그 후에 옵티마이저를 연구하는 사람까지 사라질 것이다. 왜냐하면 옵티마이저를 연구하는 사람은 튜너를 위해 존재하기 때문이다.


PS
다들 잘 지내시죠? 개인 사정으로 지난 2년간 뵙지 못했습니다. 5월달에 글을 한 두개 더 올릴 생각 입니다. 기대해 주세요. 5월 중순 부터는 바빠서 글쓰기가 힘들 것 같습니다. 

그럼 건강하세요.

저작자 표시 비영리 동일 조건 변경 허락
신고
Posted by extremedb

댓글을 달아 주세요

  1. 혈기린 2013.05.06 18:32 신고  댓글주소  수정/삭제  댓글쓰기

    다시 활동 재계하시는건가요 정말 오랬만에 글이 올라왔네요

  2. feelie 2013.05.07 18:49 신고  댓글주소  수정/삭제  댓글쓰기

    많이 바쁘셨나 보네요...
    그동안 무척 기다렸는데, 엄청 반갑습니다..

  3. 라튜니 2013.05.07 19:46 신고  댓글주소  수정/삭제  댓글쓰기

    정말 2년만의 글이 올라왔네요. 너무 반갑네요~ 자주 올려 주시면 감사하겠지만 또 바빠지신다니
    가끔이라도 부탁드립니다~

  4. 강정식 2013.05.14 09:06 신고  댓글주소  수정/삭제  댓글쓰기

    안녕하세요 오수석님 ^^
    어제 채팅으로 오랜만에 뵈서 반가웠습니다.
    그런데 페북으로 보니 오수석님 포스팅이 올라온걸 보고 바로 달려왔습니다 ㅎㅎ
    역시나 좋은 글을 올려 주셨네요... ^^

    앞으로도 유용한 포스팅 많이 기대 하겠습니다..
    감사합니다.

  5. 2014.02.24 17:08  댓글주소  수정/삭제  댓글쓰기

    비밀댓글입니다

Window 7을 사용하는 사람들은 ReadyBoost 기능을 잘 알 것이다. 부팅 시에 필요한 정보를 성능이 우수한 Flash Disk(USB)에 넣음으로써 부팅속도를 향상시키는 것이다. 즉 부팅 시에 성능이 느린 Hard Disk를 사용하지 않으므로 성능이 향상된다. 이와 비슷한 기능이 오라클에도 있다.

 

Oracle 11.2부터 Database Smart Flash Cache라는 것이 추가되었다. 이 개념은 buffer cache Aging Out되어 Disk로 내려가는 단점을 보완한 것이다. buffer cache에서 Aging Out 되더라도 성능이 우수한 Flash Disk(SSD)로 내려가는 개념이다. 따라서 SSD Aging out된 블록을 다시 읽을 때 기존 Disk보다 빠른 성능을 낼 수 있다. Database Smart Flash Cache 기능이 추가되어 메모리 구조도가 약간 변경되었다.
 

그림의 출처: Database Administrator's Guide 11g Release 2


Flash Disk
2 cache로 사용하는 셈이다. 하지만 Flash Disk에서 조차 aging out될 때는 Disk로 내려가는 것은 막을 수 없다.

 

제약사항

반드시 솔라리스나 오라클 엔터프라이즈 리눅스를 사용해야 한다.

 

어떨 때 사용해야 되나?

아래의 세 가지 경우를 모두 만족하면 Database Smart Flash Cache를 고려해야 한다.


첫 번째, AWR이나 Statspack에서 Buffer Pool Advisory를 참조하여 buffer cache가 부족한 경우

두 번째, db file sequential read Top Wait Eevnt일 경우

세 번째, 여분의 CPU가 있는 경우

 

위의 세 가지 경우를 모두 만족해야 되는 이유는 기존의 Disk에도 약간의 Cache기능이 있으므로, 위의 조건과 같이 buffer cache가 부족하거나 aging out이 많이 발생하는 경우만 성능향상의 폭이 크기 때문이다.

 

All or Nothing

RAC인 경우 하나의 SSD Disk를 다른 노드에 공유할 수 없다. , 노드별로 Smart Flash Cache를 별도로 설정해야 한다. 또한 하나의 노드에 Database Smart Flash Cache를 설정하였으면 나머지 노드에도 모두 Smart Flash Cache를 설정해야 한다.

 

Database Smart Flash Cache Size 설정

Flash Cachebuffer cache 2~10배 정도를 권고한다. Flash Cache의 Sizebuffer cache 2배보다 작으면 효과를 볼 수 없다.

 

Parameters

db_flash_cache_file:

Flash Cache로 사용할 파일의 경로를 설정한다. 만약 파일이 없으면 오라클이 startup시에 생성한다. 반드시 SSD내의 경로를 사용해야 한다. 그렇지 않으면 성능이 떨어진다.
:/dev/fioa1


db_flash_cache_size:

Giga 단위로 기술해야 한다. 이 파라미터를 0으로 설정하면 Database Smart Flash Cache기능이 Disable된다. 이 파라미터는 scope = memory 옵션을 사용할 수 없다. 따라서 이 파라미터를 적용하려면 Shutdown Startup이 필요하다.

: 16G

 

Buffer Cache 튜닝

aging out이 발생하여 buffer cache에서 Flash Cache로 밀려날 때에도 블록의 메타정보는 그대로 남게 된다. 그 메타정보는 어림잡아 한 블럭당 100 byte 정도이다. 만약 RAC라면 한 블럭당 200 byte를 차지한다. 이 메타정보는 블록이 flash cache로 이동될 때 flash cache내의 address 정보를 가지고 있을 것으로 추측된다. 만약 Flash Cache를 사용할 것이라면 buffer cache block 100 byte 정도(RAC라면 200 byte) 줄어들므로 그만큼 더 잡아주기 바란다.

 

필자의 여건상 Database Smart Flash Cache를 테스트 해볼 수 없다. 누가(SSD를 보유한 사람이) 테스트를 하여 후기를 올려주었으면 한다. 테스트 시나리오는 아래와 같다. Flash Cache의 적용 전/후의 성능을 비교하는 것이다. 나라면 이렇게 테스트 할 것이다.

 

Database Smart Flash Cache 미적용 시나리오

1. buffer cache를 아주 작게 잡는다.

2. 큰 테이블 두 개에 각각 사이즈가 큰 인덱스를 하나씩 만들어 Nested Loop Join을 시키면 aging out이 발생될 것이다.

 

Database Smart Flash Cache 적용 시나리오

1. buffer cache를 아주 작게 잡는다.

2. Database Smart Flash Cache기능을 Setup 한다.

3. 큰 테이블 두 개에 각각 사이즈가 큰 인덱스를 하나씩 만들어 Nested Loop Join을 시키면 aging out이 발생될 것이다.

 

측정항목

AWRdb file sequential read의 부하가 얼마나 줄어드는지 관찰한다.

Nested Loop Join의 전체건 처리 속도는 얼마나 빨라지는지

 

이상으로 Oracle 11.2에 추가된 Database Smart Flash Cache 기능에 대해 살펴보았다.  한가지 이상한 점은 Database Smart Flash Cache 기능을 HP IBM 서버 등에는 사용할 수 없다는 것이다. 요즘 Oracle HP와 사이가 좋지 않다. 오라클의 가격정책도 이런 사실을 증명해준다. 돌아올 수 없는 강을 건넌 것인가? 아니면 Oracle 다음 버전(V 12)에서 HP IBM서버도 지원 할 것인가? 나는 전자라고 생각한다.

 

Reference:

Oracle Database 11g Release 1 (11.2.0.1) New Features- 1.8.1.1 Database Smart Flash Cache

Oracle Database Administrator's Guide 11g Release 2-Configuring Database Smart Flash

저작자 표시 비영리 동일 조건 변경 허락
신고
Posted by extremedb

댓글을 달아 주세요

  1. salvationism 2011.05.09 15:23 신고  댓글주소  수정/삭제  댓글쓰기

    [신기능들에 대한 불신과 베타 테스터]
    신기능을 쓰면 항상 따라오는 베타 테스터 라는 느낌.
    어떻게 보면 느낌이 아니라 사실 입니다.

    신기능이 늘어날 수 록..
    한편에서는 장애 포인트만 많아지네.. 이런 생각이 간혹 듭니다.

    ID 1260804.1 를 읽어 보면 ExaData에서
    판치고 있는 Wrong Result의 심각성과 늘어난 장애 포인트를 느끼게 됩니다.
    Exa 지원하면서 많이 느끼기도 했구요.

    smart flasg cache는 Exa에 비해 단순해서 좀 안정적일거라는 짐작은 합니다.
    운영에서의 적용은 번거로운면이 많이서 극히 일부만 쓰지 않을까하는.. ㅎㅎ;;

  2. 라튜니 2011.05.31 15:00 신고  댓글주소  수정/삭제  댓글쓰기

    다음글이 언제 포스팅 되는지 궁금합니다~ 요즘 포스팅이 너무 뜸하신거 같아요~

  3. NCDB 2011.05.31 19:20 신고  댓글주소  수정/삭제  댓글쓰기

    안녕하세요.
    동규님 좋은글 항상 감사하며 보고 있습니다.
    다름이 아니라 동규님 추천으로 relational database index design and the optimizers 란 책을 샀는데요.
    여기 챕터 끝나면 연습문제가 있는데 혹시 솔루션을 어디서 구할수 있는가 해서여.
    FTP로 들어가서 보니 답이 안나와 있습니다.
    바쁘시겠지만 답변 부탁드리겠습니다.
    감사합니다.

  4. ExtraOdinary 2011.06.01 17:40 신고  댓글주소  수정/삭제  댓글쓰기

    좋은 정보 잘 보았습니다. 보면서 한가지 의문이 드는데요. 기존 Buffer Cache에서 자주 밀려나는 Block 즉, FTS에 의해 Buffer Cache에서 자주 밀려나는 블록들이 Flash Cache에 캐싱이 될텐데요. 그 경우 기존의 FTS 보다 훨씬 속도 측면에서
    빨리질 것으로 생각이 됩니다.
    이런 경우 System statistics를 사용하는 환경에서 Multi Block I/O의 속도가 빨라져 실행계획이 FTS에 유리하도록 Cost 계산이 될 것 같은데, 이 때 실행계획이 바뀌는 현상은 없을까요?

  5. Favicon of http://www.battery-uk.co.uk BlogIcon batterypang 2011.10.21 15:05 신고  댓글주소  수정/삭제  댓글쓰기

    좋은 정보 잘 보았습니다. 보면서 한가지 의문이 드는데요. 기존 Buffer Cache에서 자주 밀려나는 Block 즉, FTS에 의해 Buffer Cache에서 자주 밀려나는 블록들이 Flash Cache에 캐싱이 될텐데요. 그 경우 기존의 FTS 보다 훨씬 속도 측면에서
    빨리질 것으로 생각이 됩니다.
    이런 경우 System statistics를 사용하는 환경에서 Multi Block I/O의 속도가 빨라져 실행계획이 FTS에 유리하도록 Cost 계산이 될 것 같은데, 이 때 실행계획이 바뀌는 현상은 없을까요?

  6. DBA 2011.12.14 19:24 신고  댓글주소  수정/삭제  댓글쓰기

    제 생각에는 차라리 Memory를 더 꽂아서 Buffer cache를 늘려주는게 어떨까 하는 것이구요.
    Storage 에도 캐쉬 메모리를 더 늘려주는 방안이 있을 수 있습니다. Read/write 캐쉬를 더 늘려주는 것이죠.

    Buffer cache내에서 Keep pool, recycle pool등을 활용하는 방안도 있을 수 있을 것이구요.

    물론 SSD를 2차 캐쉬처럼 쓰는 위의 방법도 나름 장점은 있을 것 같습니다. 이른바 하이브리드 구성인데요.
    SSD 가격이 비싸서 전체를 SSD로 하기에는 비용 문제가 너무 크다. 따라서 하이브리드 방식을 채택하다는 것인데,
    어느 정도 효과는 있을 것이 분명합니다.

    솔라리스하고 리눅스에서만 구성이 가능하다는 점은 범용적으로 쓰이기에는 한계가 있다는 것이 아쉽군요.

    또 하나 위에도 적었듯이 비용 문제인데요.
    SSD 살 돈으로 메모리 더 사고... 마그네틱 하드디스크 성능에 더 투자를 하는 방법대비 어느게 더 싸게 먹히느냐라는 문제가 있습니다.

  7. 유일환 2012.11.09 17:37 신고  댓글주소  수정/삭제  댓글쓰기

    블로그 매우 잘보고 있습니다. 좋은글 감사드립니다!!! 꾸벅!!

일반적인 의견
흔히 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
요즘 워낙 바빠서 예전에 미리 글을 써놓지 않았더라면 글을 하나도 올리지 못할뻔 하였다. 
저작자 표시 비영리 동일 조건 변경 허락
신고
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

더미 테이블을 사용해서 장애를 만나는 경우

더미 테이블을 사용하는 이유

더미 테이블을 사용하지 않는 방법

 

포장마차에서 지인에게 재미있는 이야기를 들었다. 물론 공장 이야기 이다. 나는 이야기를 재미있게 들었지만, 지인의 입장에서는 머리가 쭈뼛쭈뼛 서는 심각한 일이었다. 사건은 2011년 겨울에 시작된다.

 

2011 1 1일 이른 아침, 갑자기 잘 돌아가던 시스템에 몇몇 프로그램들이 작동하지 않는 장애를 만났다. Y2K 버그도 아니고 2011 1 1일에 장애라니? 서버와 네트워크 그리고 Database는 정상이므로 관심의 화살은 개발팀으로 집중되었다. 개발팀에서 장애 프로그램을 조사해보니 지난 한 달간 프로그램 수정이 없다고 하였다. 결국 모든 것이 정상인데 프로그램만 돌아가지 않는 상황이다. 귀신이 곡할 노릇이 아닌가? 빨리 정상적인 서비스를 해야 하므로 1, 1초가 아쉬운 시점이었다. 모두들 땀을 흘리며 원인을 찾고 있었다. 프로그램 담당자는 장애를 일으킨 사람을 찾으면 죽여버리겠다고 소리쳤다.

 

여러분은 이런 장애에서 안전한가?

다행히 오래 걸리지 않고 원인을 찾았다. 돌아가지 않는 프로그램들의 공통점은 더미테이블을 사용한다는 것이었다. 즉 Copy_ymd를 사용한 것이다. 그 테이블을 조사해보니 일자가 2010년 까지만 들어가 있었다. 그래서 2011년이 되자마자 장애가 발생한 것이었다. 다시 말해, Copy_ymd 테이블에 2011년 데이터가 없으므로, 이 테이블과 조인하면 한 건도 나오지 않는 것이다. 생각해보니, 모든 시스템에 이런 일이 발생할 수 있다. 이야기를 듣는 필자의 간담이 갑자기 서늘해진다.

 

시스템을 구축한 업체에게 항의하려고 문서를 찾아보니 2001년에 Open한 시스템으로 2001년 기준으로 미래의 일자를 10년치 넣어 놓았다. 소프트웨어의 라이프 사이클을 고려한다면, 10년이면 충분하다고 생각했을 것이다. 하지만 운이 없게도 차세대 프로젝트를 하지 않고 10년간 유지보수를 하면서 사용한 것이다. 그리고 인수인계서에 2011년이 되기 전에 몇 년치의 데이터를 더 넣어놓으라고 명시되어 있었다. 시스템을 구축한 업체에게 항의할 수 도 없는 일이었다. 인수인계서를 보는 사람이 한 명이라도 있었을까?

 

왜 더미 테이블을 사용할까?

데이터베이스에 관심이 있는 개발자라면 Copy_ymd, Copy_ym, Copy_y, Copy_t 등 네 개의 더미테이블을 알 것이다. 많은 시스템에 이런 더미 테이블들이 있다. 과거에는 이런 테이블들을 사용해야만 했다. 하지만 2011년의 시점에서 새로운 프로젝트를 할 때 이런 테이블들이 필요할까? 필요한지 아닌지를 알려면 먼저 더미테이블의 용도를 알아야 한다. 이 테이블들의 용도 중에서 대표적인 것은 아래와 같이 세 가지로 볼 수 있다.

 

1. Copy: 같은 집합을 여러 번 복제하여 원하는 결과집합을 구한다.

2. 데이터 체크: 일자의 경우 입력된 값이 올바른지 확인한다. 예를 들면, 2 30일은 잘못된 일자이다.

3. 인덱스의 효율적 사용: 인덱스의 첫 번째 컬럼 혹은 중간 컬럼이 Where 조건에 사용되지 않을 때 더미 테이블을 이용하여 IN으로 공급해주면 인덱스를 효율적으로 사용할 수 있다.

 

물론, 다른 용도로 더미테이블을 사용할 수 도 있지만, 대부분은 위의 세가지 경우 때문에 더미테이블이 필요하다. 가끔 기준일자를 관리하는 테이블을 볼 수 있는데, 이것은 더미테이블이 아니라 business에 필요한 것이다. 더미테이블은 업무적인 것이 아니라, 성능적인 관점, 혹은 관리적인 목적으로 사용되는 것이다. 업무적인 데이터가 없으므로 차세대 시스템을 구축할 때 더미 테이블은 분석 대상에서 빠져도 된다. 이런 이유 때문에 모델러들도 더미테이블을 중요하게 생각하지 않는다.

 

더미 테이블의 단점

위의 세 가지를 더미 테이블을 사용하지 않고 처리할 수 있다면 굳이 사용할 필요는 없다. 왜냐하면 아래와 같은 단점이 있기 때문이다.

 

첫 번째, 더미 테이블이라고 해도 시스템 속성을 추가해야만 한다. 시스템 속성이란 입력자, 입력일시, 수정자, 수정일시 등을 의미한다. 모든 테이블에 이런 컬럼들이 4 ~ 6개 정도 존재한다. 많은 기업들이 메타시스템을 사용하고 있다. 메타시스템에 테이블에 시스템 속성이 없으면 등록할 수가 없는 경우가 많다. 심지어 자동으로 시스템속성을 추가하는 메타시스템도 있다.

 

그런데 더미테이블은 튜닝의 목적이 있으므로 매우 가벼워야 한다. 생각해보라. Copy_t에 존재하는 숫자컬럼의 length3 byte에 불과한데 시스템 속성 네 개가 48 byte를 차지한다. 3 byte를 위해서 건건이 48 byte를 낭비해야 한다. 테이블이 무거워 질 수 밖에 없다. 더미 테이블은 메타시스템으로 관리하지 말고 엑셀로 관리하면 된다고? 왜 추가적인 관리를 해야만 하는가?

 

두 번째, 누가 더미 테이블을 중요하게 생각하는가? 더미 테이블을 인수인계 시 중요항목으로 관리되고 있는가? 2011년이 가까이 다가와도, Copy_ymd에 데이터를 넣어줄 생각을 하는 사람은 아무도 없었다. 왜냐하면 10년간 담당자가 세 번이나 바뀌었고, 더미테이블은 인수인계 시 중요관심사가 아니었기 때문이다. 결국 더미테이블을 신경 쓰는 사람은 아무도 없을 수 있다. 시스템은 이렇게 중요 테이블이 아니더라도 조그만 블랙홀이 생기면 장애를 맞는다. 이런 일이 발생할 수 밖에 없는 걸까?

 

세 번째, 관리해야 할 DB 서버가 많다면 위험이 증가한다. DB 팀이 관리하는 DB30개라고 가정하자. 지금 30개의 DB에 대해서 더미테이블을 관리하고 있는가? Copy_ymd에 추가적인 데이터를 insert 해야 하는 시기를 알고 있는가? 관리하고 있지 않다면 장애를 맞을 가능성이 높다. 그렇다면, 신경 쓰지 않아도 되도록, 시간이 되면 자동으로 insert되는 프로그램을 고려해 보아야 하는가? 아니면 시스템마다 더미테이블 들을 뒤져서 안전하게 100년치를 넣을 것인가? 왜 그래야 하는가? 아예 더미테이블을 사용하지 않으면 될 것을

 

지금은 운영 중이기 때문에 SQL을 바꾸는 것이 어렵다고 하더라도, 차세대 시스템을 구축할 때는 테이블을 관리할 필요도 없고, 장애도 일으키지 않는 방법이 무엇인지 고려하기 바란다. 방법은 얼마든지 있다. 이미 똑똑한 개발자들은 아래의 방법을 사용하고 하고 있다.

 

1. Copy

Copy_t 대신에 Rollup, Cube, Grouping Sets를 활용하면 원하는 집합을 만들 수 있다. 사용방법은 해당 을 참고하라. 물론 내부적으로 쿼리변환이 발생되어 UNION ALL로 풀릴 수도 있으므로 성능이 저하되는지 실행계획의 확인은 필요하다. 이런 경우에도 COPY_T는 필요 없으며 DUAL + CONNECT BY LEVEL을 사용하면 된다. 또한 LEAD/LAG를 사용한다면 복제하지 않고도 전/후의 데이터를 비교할 수 있다.

 

2. 데이터 체크

데이터를 Insert 하기 전에 일자 컬럼을 체크하려고, DBMS Call을 해야만 하나? 다시 말해, 무슨 이유 때문에 DB에 불필요한 부하를 주어야 하는가? 비슷한 노력을 들이고도 DBMS Call을 하지 않을 수 있다. 화면 단에서 Java Script로 처리하던지, 아니면 Constraint를 걸면 Insert할 때에 자동으로 체크 되므로 별도의 DBMS Call은 필요 없다. Constraint에 대해서는 관련 을 참조하라.

 

3. 인덱스의 효율적 사용

INDEX SKIP SCAN 기능이 추가되었기 때문에 IN 서브쿼리를 사용해야 되는 경우는 많이 한정 되었다. 또한 IN 서브쿼리를 사용한다고 하더라도 Copy_t, Copy_ymd 대신에 Dual + Connect By를 사용하면, Pseudo 컬럼인 Level을 사용할 수 있다. 물론 주의사항은 있다. 해당 을 참조하라.

 

3번에 대해서 어느 개발자가 다음과 같이 질문한다.

 

질문1

개발자: 인덱스가 거래일자 + 고객번호 입니다. 거래일자에 Between 조건이 들어오고 고객번호에 = 조건이 들어온다고 칩시다. 인덱스의 선두 컬럼이 Range 조건이므로 똑똑한 고객번호를 인덱스로 액세스 할 수 없습니다. 이럴 때, Copy_ymd가 있어서 거래일자를 IN 서브쿼리로 공급할 수 있었습니다. 그런데 Copy_ymd 테이블 없이 Dual + Connect By + Level로 처리가 가능 한가요? Copy_tLevel로 처리가 가능하지만 일자는 Range 조건으로 만들기 힘들 것 같은데요.

필자: 됩니다.

개발자: 어떻게요?

 

질문2

개발자: INDEX SKIP SCAN은 인덱스가 A+B+C 로 되어있고, A 혹은 B Where 조건에서 생략될 때만 사용할 수 있는 것 아닙니까? , A 컬럼에 Range 조건이 오고 B = 조건이 오면 INDEX SKIP SCAN을 사용할 수 없는 걸로 알고 있습니다만.

필자: 꼭 그런 것은 아닙니다. A 컬럼에 조건이 Between이나 LIKE 조건이 오고 B 컬럼에 = 조건이 오더라도 INDEX SKIP SCAN이 발생합니다. , 선두나 중간 컬럼의 조건이 생략될 때만 INDEX SKIP SCAN이 발생하는 것은 아니며, 선두나 중간 컬럼에 조건이 Range로 들어올 때도 발생합니다.  

개발자: 그럴 리가요?

 

이제부터 두 가지 질문에 대해 대답해보자. 먼저 Sales 테이블에 인덱스를 하나 만들고 Copy_ymd를 만들자.

 

CREATE INDEX IDX_SALES_01 ON SALES (time_id, cust_id, prod_id) ;

 

CREATE TABLE COPY_YMD AS

SELECT TO_CHAR(ROWNUM + TO_DATE('19800101', 'YYYYMMDD'), 'YYYYMMDD') AS YMD_CHAR,

       ROWNUM + TO_DATE('19800101', 'YYYYMMDD') AS YMD_DT

  FROM SALES

WHERE ROWNUM <= 14600;

 

ALTER TABLE COPY_YMD ADD CONSTRAINT PK_COPY_YMD

PRIMARY KEY (YMD_CHAR) USING INDEX; 

 

CREATE UNIQUE INDEX IDX_COPY_YMD_01 ON COPY_YMD(YMD_DT);

 

Sales 테이블의 인덱스는 Time_id _+ cust_id + Prod_id 이다. 해당 매출테이블의 transaction이 많아서 인덱스를 변경할 수도, 생성할 수도 없는 상황이라고 가정한다. 이제 테스트를 시작해보자.

 

참고로 아래의 힌트는 INDEX SKIP SCAN을 방지할 목적으로 사용한 것이다. INDEX SKIP SCAN이 나오기 전에는 이렇게 INDEX RANGE SCAN으로 수행되었다.

 

SELECT /*+ NO_INDEX_SS(S IDX_SALES_01) INDEX_RS_ASC(S IDX_SALES_01) */ s.*

  FROM sales s

 WHERE time_id BETWEEN TO_DATE('20011001', 'YYYYMMDD')

                   AND TO_DATE('20011130', 'YYYYMMDD')

   AND cust_id = 53;

 

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

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

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

|   0 | SELECT STATEMENT                   |              |      1 |      6 |00:00:00.01 |     209 |

|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| SALES        |      1 |      6 |00:00:00.01 |     209 |

|*  2 |   INDEX RANGE SCAN                 | IDX_SALES_01 |      1 |      6 |00:00:00.01 |     203 |

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

 

Predicate Information (identified by operation id):

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

   2 - access("TIME_ID">=TO_DATE(' 2001-10-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "CUST_ID"=53

              AND "TIME_ID"<=TO_DATE(' 2001-11-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

       filter("CUST_ID"=53)

 

과거에는 선두컬럼이 Between이나 Like등의 Range 조건이 들어오면 위의 실행통계에서 볼 수 있듯이 비효율이 심했다. 고작 6건을 출력하기 위해 209 블록이나 Scan했다. 왜냐하면, 똑똑한 조건인 고객번호가 선두컬럼의 Range 조건 때문에 Access 조건이 못되고 Filter로 빠졌기 때문이다. 이런 비효율을 없애기 위해 예전에는 아래와 같이 더미테이블을 이용한 서브쿼리를 사용하였다.

 

SELECT /*+ LEADING(C@SUB) USE_NL(S) */ s.*

  FROM sales s

 WHERE time_id IN ( SELECT /*+ QB_NAME(SUB) */ ymd_dt

                      FROM copy_ymd c

                     WHERE ymd_dt BETWEEN TO_DATE('20011001', 'YYYYMMDD')

                                      AND TO_DATE('20011130', 'YYYYMMDD') )

   AND cust_id = 53;

 

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

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

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

|   0 | SELECT STATEMENT                    |                 |      1 |      6 |00:00:00.01 |     136 |

|   1 |  NESTED LOOPS                       |                 |      1 |      6 |00:00:00.01 |     136 |

|   2 |   NESTED LOOPS                      |                 |      1 |      6 |00:00:00.01 |     130 |

|*  3 |    INDEX RANGE SCAN                 | IDX_COPY_YMD_01 |      1 |     61 |00:00:00.01 |       4 |

|*  4 |    INDEX RANGE SCAN                 | IDX_SALES_01    |     61 |      6 |00:00:00.01 |     126 |

|   5 |   TABLE ACCESS BY GLOBAL INDEX ROWID| SALES           |      6 |      6 |00:00:00.01 |       6 |

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

 

Predicate Information (identified by operation id):

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

   3 - access("YMD_DT">=TO_DATE(' 2001-10-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND

              "YMD_DT"<=TO_DATE(' 2001-11-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

   4 - access("TIME_ID"="YMD_DT" AND "CUST_ID"=53)

       filter(("TIME_ID"<=TO_DATE(' 2001-11-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND

              "TIME_ID">=TO_DATE(' 2001-10-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))

 

서브쿼리를 사용하자 고객번호를 Access 조건으로 사용할 수 있게 되었다. 이에 따라 서브쿼리를 사용하지 않은 경우(209 블럭)보다는 Scan량이 줄어 136 블록이 되었지만 약간의 비효율이 있다. Copy_ymd 때문에 4블럭을 Scan 하였다. 이것을 해결하려면 아래처럼 Dual + Connect By Level을 사용하면 된다. 위의 SQL과 아래의 SQL의 답은 같으며 아래의 SQL은 질문1의 답변에 해당한다.  

 

SELECT s.*

  FROM sales s,

      ( SELECT TO_DATE('20011001', 'YYYYMMDD') + LEVEL - 1 AS time_id

          FROM dual

       CONNECT BY LEVEL <= TO_DATE('20011130', 'YYYYMMDD') - TO_DATE('20011001', 'YYYYMMDD') + 1) d

 WHERE s.time_id = d.time_id

   AND s.cust_id = 53; 

 

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

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

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

|   0 | SELECT STATEMENT                    |              |      1 |      6 |00:00:00.01 |     132 |

|   1 |  NESTED LOOPS                       |              |      1 |      6 |00:00:00.01 |     132 |

|   2 |   NESTED LOOPS                      |              |      1 |      6 |00:00:00.01 |     126 |

|   3 |    VIEW                             |              |      1 |     61 |00:00:00.01 |       0 |

|   4 |     CONNECT BY WITHOUT FILTERING    |              |      1 |     61 |00:00:00.01 |       0 |

|   5 |      FAST DUAL                      |              |      1 |      1 |00:00:00.01 |       0 |

|*  6 |    INDEX RANGE SCAN                 | IDX_SALES_01 |     61 |      6 |00:00:00.01 |     126 |

|   7 |   TABLE ACCESS BY GLOBAL INDEX ROWID| SALES        |      6 |      6 |00:00:00.01 |       6 |

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

 

Predicate Information (identified by operation id):

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

   6 - access("S"."TIME_ID"=INTERNAL_FUNCTION("D"."TIME_ID") AND "S"."CUST_ID"=53)

 

Dual을 사용했기 때문에 Block I/O가 없어졌다. 하지만 여기서 만족하면 안 된다. 왜냐하면 쓸모 없는 조인이 61번이나 시도되었고 이에 따라 126블록을 Scan하였기 때문이다. 따라서 SQL을 아래처럼 바꾸어야 한다.

 

SELECT /*+ INDEX_SS(S IDX_SALES_01) */ s.*

  FROM sales s

 WHERE time_id BETWEEN TO_DATE('20011001', 'YYYYMMDD')

                   AND TO_DATE('20011130', 'YYYYMMDD')

   AND cust_id = 53;

 

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

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

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

|   0 | SELECT STATEMENT                   |              |      1 |      6 |00:00:00.01 |      70 |

|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| SALES        |      1 |      6 |00:00:00.01 |      70 |

|*  2 |   INDEX SKIP SCAN                  | IDX_SALES_01 |      1 |      6 |00:00:00.01 |      64 |

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

 

Predicate Information (identified by operation id):

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

   2 - access("TIME_ID">=TO_DATE(' 2001-10-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "CUST_ID"=53

              AND "TIME_ID"<=TO_DATE(' 2001-11-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

       filter("CUST_ID"=53)

 

불필요한 조인도 없어졌으며 Block I/O도 서브쿼리를 사용할 때에 비해서 약 절반으로 줄어들었다. 이것이 질문 2에 대한 대답이다.

 

참고사항

위의 SQL들을 보면 인덱스가 cust_id + time_id로 되어 있는 것이 최적이지만 막상 튜너가 현장에 투입되면 인덱스를 변경/생성/삭제 하기는 대단히 어려우므로 위의 방법을 잘 알아놓아야 한다.

 

결론

Copy_ymd, Copy_ym, Copy_y, Copy_t는 구시대의 유물이다. 성능에도 좋지 않으며, 코드가 길어지고, 장애가 발생할 수 있음에도 여러 가지 이유를 대어 차세대 시스템에 더미 테이블들이 또 포함될 수 있다. 안타깝게도 관행이나 표준으로 생각하는 사람이 많기 때문이다. 이제는 바뀔 때가 되었다. 지금 운영되는 모든 시스템에서 더미테이블을 사용하는 SQL을 모조리 조사해서 고치라는 이야기가 아니다. 그렇게 하기는 힘들 것이다. 다만 모든 더미테이블을 찾아서 미래의 데이터를 미리 그리고 넉넉히 넣자는 이야기 이다. 그리고 앞으로 시작될 프로젝트에서 더미테이블을 사용하지 않았으면 하는 것이 나의 바램이다. 당신이 발 뻗고 잘 수 있도록
저작자 표시 비영리 동일 조건 변경 허락
신고

'Oracle > SQL Tuning' 카테고리의 다른 글

COPY_T 테이블 필요한가?  (6) 2011.04.04
Sort 부하를 좌우하는 두 가지 원리  (9) 2011.03.29
SQL튜닝 방법론  (17) 2011.01.27
Pagination과 분석함수의 위험한 조합  (26) 2010.12.23
오라클의 Update문은 적절한가?  (15) 2010.04.14
Connect By VS ANSI SQL  (6) 2010.02.11
Posted by extremedb

댓글을 달아 주세요

  1. finecomp 2011.04.06 00:43 신고  댓글주소  수정/삭제  댓글쓰기

    8i, 9i 등 ~i 이전 시대의 방법론들을 현재의 ~g시대에도 고수하려는 고집들은 대부분 말도 안되는 논리인 듯 보입니다.
    (물론, 예~~전엔 그 방법들이 최적일 때가 분명히 있었더랬죠...)

    DB모델링이나 SQL만의 현상은 아니더군요...항상 잘 보고, 느끼고 갑니다...^^;;;

  2. 에너자이져 2011.04.06 09:32 신고  댓글주소  수정/삭제  댓글쓰기

    예전에 유용하게 사용한 적이 있었는데 구시대의 유물이 되어버렸네요..
    좋은글 감사합니다.

  3. 최윤호 2011.04.06 11:32 신고  댓글주소  수정/삭제  댓글쓰기

    좋은 글 항상 감사합니다.

▶적절한 인덱스가 없을 때 Sort의 부하를 줄이는 방법

Pagination에서 Sort의 부하 줄이기

주의사항

 

Order by 절에 의한 Sort의 부하는 성능에 치명적이다. Block I/O의 최소화는 분명 튜닝의 핵심이다. 하지만, Block I/O를 최소화 해도 Sort의 부하가 심하다면 결코 만족스런 성능을 내지 못한다. 특히페이징 처리용 SQL에는 Sort를 대신할 수 있는 인덱스가 있어야 성능을 확보할 수 있다고 많은 튜너들이 주장한다. 맞는 말이다. 그렇게만 된다면 Sort가 전혀 발생하지 않을 테니까. 하지만, 다음과 같은 어려움도 있다.

 

인덱스 최적화가 힘든 이유
첫 번째, 인덱스를 모든 조회화면의 기준에 맞게 만들려면 테이블마다 많은 수의 인덱스가 필요할 것이다. 두 번째, 운영중인 환경에서 인덱스를 생성 혹은 변경하기는 매우 어렵다. 따라서, 인덱스를 만들기 어렵다면, Sort의 부하를 최소화하는 다른 방법은 없는지를 고려해야 한다. 분명히 방법은 있다. 이 방법을 알지 못한다면 오직 인덱스에만 목숨을 거는 사람이 될 가능성이 높다. 오늘은 인덱스를 전혀 만들지 않은 상태에서 Sort의 부하를 최소화 하는 방법에 대해 알아볼 것이다.

 

단 한 가지 개념만 안다면, Order By에 의한 Sort의 부하를 이해한 것이다. Sort의 부하량은 면적에 비례한다는 것. 이 개념은 아래와 같이 표현할 수 있다. 참고로 는 비례한다는 의미이다.

 

Sort의 부하량(PGA 사용량) ∝ 세로(결과 건수) X 가로(컬럼 Size 합계)

 

공식의 이해가 부족하다
주위의 지인들에게 위의 식을 질문한 결과 거의 모두가 세로에 대해서는 정확히 이해하고 있었다. , Sort할 건수가 많아지면 Sort의 부하가 증가 한다는 것이다. 이에 반해서 가로에 대해서는 정확한 이해를 하는 사람이 드물었다. 대부분, Order By절에 의해 Sort의 부하가 발생하므로 Order By절에 존재하는 컬럼 Size의 합계가 가로라고 생각하는 것이다. 다시 말해, Order By절의 컬럼이 세 개라면, 세 컬럼의 Size를 합친 것이 가로라는 것이다. 과연 그럴까?


위의 주장을 검증하기 위해 테이블을 하나 만들고, 추가적으로 컬럼을 3개 만들자.

 

CREATE TABLE SALES_T NOLOGGING AS SELECT * FROM SALES;

ALTER TABLE SALES_T ADD (char_100  CHAR(100)  DEFAULT 'a' NOT NULL );

ALTER TABLE SALES_T ADD (char_1000 CHAR(1000) DEFAULT 'a' NOT NULL );

ALTER TABLE SALES_T ADD (char_2000 CHAR(2000) DEFAULT 'a' NOT NULL );

 

추가된 컬럼은 모두 Char Type이며 Default 값이 ‘a’ 이다. Char Type이므로 Default값인 ‘a’가 들어오는 경우 컬럼 size는 각각 100, 1000, 2000 바이트씩 채워진다. 이제 이 컬럼들을 이용하여 SQL을 각각 실행해보자. 100 byte, 1000 byte, 2000 byte 컬럼으로 각각 Sort하여 Sort의 부하가 어떻게 달라지는지 알아보자.

 

CREATE TABLE SORT_100 NOLOGGING AS

SELECT /*+ full(p) full(c) */

       s.prod_id, p.prod_name, s.cust_id, c.cust_first_name,

       c.cust_last_name, s.time_id, s.channel_id, s.char_100

  FROM sales_t s, customers c, products p

 WHERE s.cust_id = c.cust_id

   AND s.prod_id = p.prod_id

   AND s.prod_id = 30

 ORDER BY s.char_100 ;

 

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

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

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

|   0 | CREATE TABLE STATEMENT |           |      1 |      0 |00:00:01.41 |    7323 |          |

|   1 |  LOAD AS SELECT        |           |      1 |      0 |00:00:01.41 |    7323 |  521K (0)|

|   2 |   SORT ORDER BY        |           |      1 |  29282 |00:00:01.23 |    5915 | 4708K (0)|

|*  3 |    HASH JOIN           |           |      1 |  29282 |00:00:01.15 |    5915 | 3471K (0)|

|   4 |     TABLE ACCESS FULL  | CUSTOMERS |      1 |  55500 |00:00:00.21 |    1468 |          |

|   5 |     NESTED LOOPS       |           |      1 |  29282 |00:00:00.66 |    4447 |          |

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

|*  7 |      TABLE ACCESS FULL | SALES_T   |      1 |  29282 |00:00:00.59 |    4440 |          |

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

 

Predicate Information (identified by operation id):

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

   3 - access("S"."CUST_ID"="C"."CUST_ID")

   6 - filter("P"."PROD_ID"=30)

   7 - filter("S"."PROD_ID"=30)

 

100 byte컬럼으로 Sort하니 PGA4.7MB 사용하였다. 이제 100 byte보다 10배나 큰 1000 byte 컬럼으로 Sort 하여 PGA 사용량을 비교해보자.

 

CREATE TABLE SORT_1000 NOLOGGING AS

SELECT /*+ full(p) full(c) */

       s.prod_id, p.prod_name, s.cust_id, c.cust_first_name,

       c.cust_last_name, s.time_id, s.channel_id, s.char_1000

  FROM sales_t s, customers c, products p

 WHERE s.cust_id = c.cust_id

   AND s.prod_id = p.prod_id

   AND s.prod_id = 30

 ORDER BY s.char_1000 ;

 

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

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

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

|   0 | CREATE TABLE STATEMENT |           |      1 |      0 |00:00:02.17 |   13162 |          |

|   1 |  LOAD AS SELECT        |           |      1 |      0 |00:00:02.17 |   13162 |  521K (0)|

|   2 |   SORT ORDER BY        |           |      1 |  29282 |00:00:01.75 |    5915 |   30M (0)|

|*  3 |    HASH JOIN           |           |      1 |  29282 |00:00:01.56 |    5915 | 3486K (0)|

|   4 |     TABLE ACCESS FULL  | CUSTOMERS |      1 |  55500 |00:00:00.22 |    1468 |          |

|   5 |     NESTED LOOPS       |           |      1 |  29282 |00:00:01.05 |    4447 |          |

|*  6 |      TABLE ACCESS FULL | PRODUCTS  |      1 |      1 |00:00:00.02 |       7 |          |

|*  7 |      TABLE ACCESS FULL | SALES_T   |      1 |  29282 |00:00:00.98 |    4440 |          |

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

 

Predicate Information (identified by operation id):

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

   3 - access("S"."CUST_ID"="C"."CUST_ID")

   6 - filter("P"."PROD_ID"=30)

   7 - filter("S"."PROD_ID"=30)

 

Sort 컬럼의 size 100에서 1000 byte로 늘리자 PGA 사용량도 4.7 MB에서 30 MB로 크게 늘었다. 내친김에 Order By절을 2000 byte 컬럼으로 바꿔서 PGA 사용량이 얼마나 늘어나는지 테스트 해보자.

 

CREATE TABLE SORT_2000 NOLOGGING AS

SELECT /*+ full(p) full(c) */

       s.prod_id, p.prod_name, s.cust_id, c.cust_first_name,

       c.cust_last_name, s.time_id, s.channel_id, s.char_2000

  FROM sales_t s, customers c, products p

 WHERE s.cust_id = c.cust_id

   AND s.prod_id = p.prod_id

   AND s.prod_id = 30

 ORDER BY s.char_2000 ;

 

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

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

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

|   0 | CREATE TABLE STATEMENT |           |      1 |      0 |00:00:03.16 |   19298 |          |

|   1 |  LOAD AS SELECT        |           |      1 |      0 |00:00:03.16 |   19298 |  521K (0)|

|   2 |   SORT ORDER BY        |           |      1 |  29282 |00:00:02.06 |    5915 |   58M (0)|

|*  3 |    HASH JOIN           |           |      1 |  29282 |00:00:01.74 |    5915 | 3515K (0)|

|   4 |     TABLE ACCESS FULL  | CUSTOMERS |      1 |  55500 |00:00:00.24 |    1468 |          |

|   5 |     NESTED LOOPS       |           |      1 |  29282 |00:00:01.19 |    4447 |          |

|*  6 |      TABLE ACCESS FULL | PRODUCTS  |      1 |      1 |00:00:00.02 |       7 |          |

|*  7 |      TABLE ACCESS FULL | SALES_T   |      1 |  29282 |00:00:01.12 |    4440 |          |

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

 

Predicate Information (identified by operation id):

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

   3 - access("S"."CUST_ID"="C"."CUST_ID")

   6 - filter("P"."PROD_ID"=30)

   7 - filter("S"."PROD_ID"=30)

 

예상대로 Sort 대상 컬럼을 1000에서 2000 byte로 바꾸자 PGA 사용량이 30 MB에서 58MB 로 약 두 배 늘었다. 위의 결과를 언뜻 보면, Order By절에 존재하는 컬럼 Size의 합계가 가로라고 생각할 수 있다. 왜냐하면, Sort 대상컬럼의 Size에 비례하여 PGA 사용량이 증가되었다고 판단하기 때문이다. 하지만 이런 생각은 절반만 옳고 나머지 절반은 틀렸다. 제대로 된 식은 다음과 같다.

 

Sort의 부하를 좌우하는 원리

Sort
의 부하량(PGA 사용량) ∝ 세로 X 가로

세로: SQL의 결과 건수

가로: Order by 절의 컬럼 size + Order by 절을 제외한 나머지 컬럼의 size

 

근거 있는 주장인가?
이 공식이 글 전체의 핵심이다. 하지만, 많은 사람들이 위와 같은 가로 세로 개념을 주장할 수 있는 근거가 무엇인지 궁금해한다. 이제 가로가 Order by 절의 컬럼 size + 나머지 컬럼의 size라는 주장에 대한 근거를 보자.

 

SELECT s.channel_id, s.char_2000

  FROM sales_t s

 WHERE s.prod_id = 30

 ORDER BY s.channel_id;

 

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

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

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

|   0 | SELECT STATEMENT   |         |      1 |  29282 |00:00:00.84 |    4440 |          |

|   1 |  SORT ORDER BY     |         |      1 |  29282 |00:00:00.84 |    4440 |   56M (0)|

|*  2 |   TABLE ACCESS FULL| SALES_T |      1 |  29282 |00:00:00.68 |    4440 |          |

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

 

Predicate Information (identified by operation id):

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

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

 

Order By절에는 Size가 작은 channel_id 컬럼 뿐이다. 그런데 PGA 사용량은 56 MB나 된다. 세로가 3만 건도 안 되는 집합을 Sort하는데 그 부하는 56 MB나 된다. 이상하지 않은가? 과부하의 이유는 Select절의 char_2000 컬럼 때문이다. 이 컬럼을 Select 절에서 제거하고 다시 실행해 보자.

 

SELECT s.channel_id

  FROM sales_t s

 WHERE s.prod_id = 30

 ORDER BY s.channel_id;

 

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

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

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

|   0 | SELECT STATEMENT   |         |      1 |  29282 |00:00:00.64 |    4440 |          |

|   1 |  SORT ORDER BY     |         |      1 |  29282 |00:00:00.64 |    4440 |  424K (0)|

|*  2 |   TABLE ACCESS FULL| SALES_T |      1 |  29282 |00:00:00.58 |    4440 |          |

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

 

Predicate Information (identified by operation id):

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

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

 

Order By절 이외의 컬럼에 주목하라 
Select
절의 char_2000 컬럼을 제거하자 Sort의 부하는 424K로 급격히 줄어들었다. 왜냐하면, Sort Area에는 Order By절의 컬럼을 Sort할뿐만 아니라 나머지 컬럼 List Loading 되기 때문이다. Order By절 뿐만 아니라, Select 절에도 size가 큰 컬럼이 있다면 성능이 급격히 저하됨을 알 수 있다. 지금까지 Sort부하량 공식에 의해 가로는 Order By절 컬럼 Size + Sort 대상 이외의 컬럼 Size가 됨을 증명해 보았다.

 

이제 이 개념을 실제 SQL 프로그래밍에 적용해보자. 아래는 전형적인 Pagination SQL이다. 아래의 예제에서 처음의 약속을 지키기 위해 인덱스를 만들지도, 사용하지도 않을 것이다.
 

SELECT *

  FROM (SELECT a.*, ROWNUM rnum

          FROM (SELECT s.prod_id, p.prod_name, p.prod_category_desc, s.cust_id,

c.cust_first_name, c.cust_last_name, s.time_id, s.channel_id,

                       s.char_100, s.char_1000, s.char_2000

                  FROM sales_t s, customers c, products p

                 WHERE s.cust_id = c.cust_id

                   AND s.prod_id = p.prod_id

                   AND s.channel_id = 3

                 ORDER BY c.cust_first_name, c.cust_last_name, p.prod_category_desc, s.time_id ) a

         WHERE ROWNUM <= :v_max_row ) --> 200 대입

 WHERE rnum >= :v_min_row ;           --> 1 대입

 

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

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

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

|   0 | SELECT STATEMENT         |           |      1 |    200 |00:00:08.71 |    5915 |          |

|*  1 |  VIEW                    |           |      1 |    200 |00:00:08.71 |    5915 |          |

|*  2 |   COUNT STOPKEY          |           |      1 |    200 |00:00:08.71 |    5915 |          |

|   3 |    VIEW                  |           |      1 |    200 |00:00:08.71 |    5915 |          |

|*  4 |     SORT ORDER BY STOPKEY|           |      1 |    200 |00:00:08.71 |    5915 | 3321K (0)|

|*  5 |      HASH JOIN           |           |      1 |    540K|00:00:06.30 |    5915 | 1176K (0)|

|   6 |       TABLE ACCESS FULL  | PRODUCTS  |      1 |     72 |00:00:00.01 |       7 |          |

|*  7 |       HASH JOIN          |           |      1 |    540K|00:00:03.91 |    5908 | 3568K (0)|

|   8 |        TABLE ACCESS FULL | CUSTOMERS |      1 |  55500 |00:00:00.21 |    1468 |          |

|*  9 |        TABLE ACCESS FULL | SALES_T   |      1 |    540K|00:00:01.14 |    4440 |          |

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

 

Predicate Information (identified by operation id):

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

   1 - filter("RNUM">=:V_MIN_ROW)

   2 - filter(ROWNUM<=:V_MAX_ROW)

   4 - filter(ROWNUM<=:V_MAX_ROW)

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

   7 - access("S"."CUST_ID"="C"."CUST_ID")

   9 - filter("S"."CHANNEL_ID"=3)

 

페이징 처리된 SQL Sort 부하량은 3321K 이다. 이제 Sort의 부하를 줄이기 위해 select 절의 모든 컬럼을 제거하자.

 

SELECT s.prod_id, p.prod_name, p.prod_category_desc, s.cust_id,

c.cust_first_name, c.cust_last_name, s.time_id, s.channel_id,

        s.char_100, s.char_1000, s.char_2000

  FROM (SELECT a.*, ROWNUM rnum

          FROM (SELECT s.rowid as s_rid, p.rowid as p_rid, c.rowid as c_rid

                  FROM sales_t s, customers c, products p

                 WHERE s.cust_id = c.cust_id

                   AND s.prod_id = p.prod_id

                   AND s.channel_id = 3

                 ORDER BY c.cust_first_name, c.cust_last_name, p.prod_category_desc, s.time_id ) a

         WHERE ROWNUM <= :v_max_row ) a,   --> 200 대입   

       sales_t s, customers c, products p

   WHERE s.rowid  = a.s_rid

   and c.rowid = a.c_rid

   and p.rowid = a.p_rid

   and rnum >= :v_min_row ;                --> 1 대입

 

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

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

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

|   0 | SELECT STATEMENT              |           |      1 |    200 |00:00:06.51 |    6168 |          |

|   1 |  NESTED LOOPS                 |           |      1 |    200 |00:00:06.51 |    6168 |          |

|   2 |   NESTED LOOPS                |           |      1 |    200 |00:00:06.51 |    5969 |          |

|   3 |    NESTED LOOPS               |           |      1 |    200 |00:00:06.51 |    5918 |          |

|*  4 |     VIEW                      |           |      1 |    200 |00:00:06.51 |    5915 |          |

|*  5 |      COUNT STOPKEY            |           |      1 |    200 |00:00:06.51 |    5915 |          |

|   6 |       VIEW                    |           |      1 |    200 |00:00:06.51 |    5915 |          |

|*  7 |        SORT ORDER BY STOPKEY  |           |      1 |    200 |00:00:06.51 |    5915 |96256  (0)|

|*  8 |         HASH JOIN             |           |      1 |    540K|00:00:05.81 |    5915 | 1193K (0)|

|   9 |          TABLE ACCESS FULL    | PRODUCTS  |      1 |     72 |00:00:00.01 |       7 |          |

|* 10 |          HASH JOIN            |           |      1 |    540K|00:00:03.65 |    5908 | 4514K (0)|

|  11 |           TABLE ACCESS FULL   | CUSTOMERS |      1 |  55500 |00:00:00.22 |    1468 |          |

|* 12 |           TABLE ACCESS FULL   | SALES_T   |      1 |    540K|00:00:01.06 |    4440 |          |

|  13 |     TABLE ACCESS BY USER ROWID| CUSTOMERS |    200 |    200 |00:00:00.01 |       3 |          |

|  14 |    TABLE ACCESS BY USER ROWID | PRODUCTS  |    200 |    200 |00:00:00.01 |      51 |          |

|  15 |   TABLE ACCESS BY USER ROWID  | SALES_T   |    200 |    200 |00:00:00.01 |     199 |          |

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

 

Predicate Information (identified by operation id):

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

   4 - filter("RNUM">=:V_MIN_ROW)

   5 - filter(ROWNUM<=:V_MAX_ROW)

   7 - filter(ROWNUM<=:V_MAX_ROW)

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

  10 - access("S"."CUST_ID"="C"."CUST_ID")

  12 - filter("S"."CHANNEL_ID"=3)

 

 

Trade Off가 유리한 경우

Sort 부하량이 3321K에서 96K로 약 34.5배 줄어들었다. 이렇게 ROWID만 남기고 select 절의 모든 컬럼을 제거해도 결과는 같다. 왜냐하면, Sort된 상태로 rowid가 보관되어있기 때문이다. 페이징 처리가 모두 끝나고 200건에 대해서만 rowid로 테이블에 접근하기 때문에 테이블의 중복사용에 의한 비효율은 매우 적다. Buffers 항목을 비교해보면 5915 블록에서 6168 블록으로 비효율은 253 블록(4%) 밖에 차이가 나지 않는다. 하지만 Sort의 부하는 34.5배나 줄어들었다. 약간의 Block I/O를 손해 보더라도 Sort의 부하가 아주 큰 경우는 같은 블록을 중복해서 읽어야 함을 알 수 있다.

 

장점 + 장점

이렇게 Rowid를 제외한 Select List를 인라인뷰 외부로 빼면, Sort 부하의 최소화 이외에 또 다른 효과를 누릴 수도 있다. 인덱스만 읽고 테이블로의 접근을 하지 않을 수 있다. Where 조건에 최적화된 인덱스가 존재하고, 그 인덱스가 Order By절 컬럼을 포함 한다면 인라인뷰 내부에서는 테이블 접근을 하지 않는다. 물론 Select List의 모든 컬럼들을 가져오려면 테이블을 접근해야 한다. 하지만 위의 예제처럼 Rowid를 사용했다면 페이징 처리가 끝난 후에, 특정 페이지에 해당하는 건들만 테이블로 접근할 수 있으므로 Random Access 도 최소화 된다. Sort를 최소화 하려고 했더니 Block I/O를 최소화 하는것까지 덤으로 얻을 수 있는 것이다.    

 

주의사항

SELECT 절에 상수나 변수 심지어 NULL이 오더라도 PGA 사용량은 증가하므로 주의해야 한다.
 

SELECT s.cust_id

  FROM sales_t s

 ORDER BY s.cust_id;

 

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

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

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

|   0 | SELECT STATEMENT   |         |      1 |    918K|00:00:03.38 |    4440 |          |

|   1 |  SORT ORDER BY     |         |      1 |    918K|00:00:03.38 |    4440 |   13M (0)|

|   2 |   TABLE ACCESS FULL| SALES_T |      1 |    918K|00:00:01.38 |    4440 |          |

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

 

Select 절에 다른 컬럼이 없기 때문에 PGA 13MB 사용 하였다. 이번에는 Select절에 Null을 추가해보자.

 

SELECT s.cust_id, null

  FROM sales_t s

 ORDER BY s.cust_id;

 

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

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

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

|   0 | SELECT STATEMENT   |         |      1 |    918K|00:00:03.48 |    4440 |          |

|   1 |  SORT ORDER BY     |         |      1 |    918K|00:00:03.48 |    4440 |   17M (0)|

|   2 |   TABLE ACCESS FULL| SALES_T |      1 |    918K|00:00:01.37 |    4440 |          |

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

 

Select List Null을 추가하자 PGA사용량이 4MB 증가했다. 오라클은 Null이나 상수도 컬럼처럼 취급함을 알 수 있다. 따라서 Order by절이 있는 인라인뷰의 Select List에 상수나 변수 혹은 Null을 집어넣는 것은 Sort의 부하를 증가시킨다. 상수나 변수는 Order By가 있는 인라인뷰에 넣지 말고 외부로 빼서 사용하면 된다.

결론
Sort의 부하를 최소화 하려면 Order By절의 컬럼에만 집중해서는 안되며, 전체 컬럼 List를 바라보아야 한다. 또한 프로그래밍을 할 때 상수 하나, 변수 하나의 위치도 고려해야 최적의 성능을 가진 프로그램이 됨을 알 수 있다. 즉 Sort의 최적화는 튜닝의 문제일 뿐만 아니라 프로그래밍의 문제인 것이다. 이점은 Sort 부하량이 무엇으로 결정되는지 개발자도 알아야 하는 이유가 된다.

면적은 가로와 세로로 구성된다.

Sort의 부하량은 면적의 크기에 비례한다.

Sort의 부하량 ∝ 결과 건수 X 전체 컬럼 Size

저작자 표시 비영리 동일 조건 변경 허락
신고

'Oracle > SQL Tuning' 카테고리의 다른 글

COPY_T 테이블 필요한가?  (6) 2011.04.04
Sort 부하를 좌우하는 두 가지 원리  (9) 2011.03.29
SQL튜닝 방법론  (17) 2011.01.27
Pagination과 분석함수의 위험한 조합  (26) 2010.12.23
오라클의 Update문은 적절한가?  (15) 2010.04.14
Connect By VS ANSI SQL  (6) 2010.02.11
Posted by extremedb

댓글을 달아 주세요

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

    안녕하십니까? 오동규님 ^^ 잘 지내시죠?
    그 동안 개인적으로 일이 있어서, 동규님 글을 잘 보지 못했는데, 오늘 동규님 포스팅 제목이 눈에 확 들어와 바로 보았습니다. ^^
    sort 부하를 제거하기 위한 방법으로 인덱스를 생성하는 솔루션은, 대용량 책에서부터 시작이 된 것 같은데, 그 이후에 추가 방법론에 대해서는 다루지 않은 것 같습니다. 그런데 동규님께서 이런 좋은 솔루션을 포스팅 해 주시다니, 여러 사람들에게 도움이 많이 될 것 같습니다. ^^

    제 경우는 rowid를 이용해서, 아래와 같이 튜닝을 한 적은 있었는데, Sort 부하를 줄이기 위한 rowid 사용법을 보게 되서 너무 좋았습니다.

    -- 1. 튜닝전
    SELECT DISTINCT XPRH.COL_1,
    XPRH.COL_2,
    XPRH.COL_3,
    XBR.COL_1,
    XBR.COL_2,
    XBR.COL_3
    FROM TABLE_1 XPRH,
    TABLE_2 XBR
    WHERE XPRH.REQUEST_ID = XBR.REQUEST_ID
    AND XPRH.PROMPT_LIMIT_TYPE_CODE = :3
    AND XPRH.CURRENCY_CODE = :4
    AND XBR.REQUEST_DATE >= :5
    AND XBR.REQUEST_DATE < :6
    ORDER BY XBR.COL_3 DESC
    ;

    Call Count CPU Time Elapsed Time Disk Query Current Rows
    ------- ------ -------- ------------ ---------- ---------- ---------- ----------
    Parse 1 0.010 0.019 0 7 0 0
    Execute 1 0.040 0.038 0 12 0 0
    Fetch 12 2.010 46.904 7262 44619 0 108
    ------- ------ -------- ------------ ---------- ---------- ---------- ----------
    Total 14 2.060 46.961 7262 44638 0 108

    Misses in library cache during parse: 1
    Optimizer goal: ALL_ROWS
    Parsing user: APPS (ID=44)

    Rows Row Source Operation
    ------- ---------------------------------------------------
    0 STATEMENT
    108 VIEW (cr=44619 pr=7262 pw=0 time=46911777 us)
    108 SORT ORDER BY (cr=44619 pr=7262 pw=0 time=46911758 us)
    108 HASH UNIQUE (cr=44619 pr=7262 pw=0 time=46910961 us)
    108 FILTER (cr=42600 pr=7165 pw=0 time=22479986 us)
    108 NESTED LOOPS (cr=42600 pr=7165 pw=0 time=22479338 us)
    13624 PARTITION HASH SINGLE PARTITION: KEY KEY (cr=631 pr=629 pw=0 time=161365 us)
    13624 TABLE ACCESS FULL TABLE_1 PARTITION: KEY KEY (cr=631 pr=629 pw=0 time=147679 us)
    108 TABLE ACCESS BY INDEX ROWID TABLE_2 (cr=41969 pr=6536 pw=0 time=45016626 us)
    13620 INDEX UNIQUE SCAN TABLE_2_PK (cr=27250 pr=966 pw=0 time=6201521 us)(Object ID 480908)
    ;

    -- 2. 인덱스 생성
    CREATE INDEX USER.TABLE_2_N4 ON USER.TABLE_2 (REQUEST_DATE, REQUEST_ID)

    -- 3. 튜닝후
    SELECT DISTINCT XPRH.COL_1,
    XPRH.COL_2,
    XPRH.COL_3,
    XBR.COL_1,
    XBR.COL_2,
    XBR.COL_3
    FROM TABLE_1 XPRH,
    TABLE_2 XBR
    ------------------------------------------------
    -- TABLE_2 추가(KJS)
    TABLE_2 XBR_T
    ------------------------------------------------
    WHERE XPRH.REQUEST_ID = XBR_T.REQUEST_ID
    ------------------------------------------------
    -- 조인키 추가(KJS)
    AND XBR.ROWID = XBR_T.ROWID
    ------------------------------------------------
    AND XPRH.PROMPT_LIMIT_TYPE_CODE = :3
    AND XPRH.CURRENCY_CODE = :4
    AND XBR.REQUEST_DATE >= :5
    AND XBR.REQUEST_DATE < :6
    ORDER BY XBR.COL_3 DESC
    ;

    Call Count CPU Time Elapsed Time Disk Query Current Rows
    ------- ------ -------- ------------ ---------- ---------- ---------- ----------
    Parse 1 0.010 0.020 0 7 0 0
    Execute 1 0.030 0.028 0 12 0 0
    Fetch 12 0.540 0.941 735 3882 0 108
    ------- ------ -------- ------------ ---------- ---------- ---------- ----------
    Total 14 0.580 0.989 735 3901 0 108

    Misses in library cache during parse: 1
    Optimizer goal: ALL_ROWS
    Parsing user: APPS (ID=44)

    Rows Row Source Operation
    ------- ---------------------------------------------------
    0 STATEMENT
    108 VIEW (cr=3882 pr=735 pw=0 time=939267 us)
    108 SORT ORDER BY (cr=3882 pr=735 pw=0 time=939252 us)
    108 HASH UNIQUE (cr=3882 pr=735 pw=0 time=938580 us)
    108 FILTER (cr=1863 pr=728 pw=0 time=592708 us)
    108 NESTED LOOPS (cr=1863 pr=728 pw=0 time=592486 us)
    108 HASH JOIN (cr=666 pr=664 pw=0 time=350058 us)
    13624 PARTITION HASH SINGLE PARTITION: KEY KEY (cr=631 pr=629 pw=0 time=53849 us)
    13624 TABLE ACCESS FULL TABLE_1 PARTITION: KEY KEY (cr=631 pr=629 pw=0 time=53782 us)
    9219 INDEX RANGE SCAN TABLE_2_N4 (cr=35 pr=35 pw=0 time=100643 us)(Object ID 21787797)
    108 TABLE ACCESS BY USER ROWID TABLE_2 (cr=1197 pr=64 pw=0 time=246521 us)
    ;

    동규님 덕분에 항상 많은 도움을 많이 받고 있어서, 항상 감사하게 생각하고 있습니다. ^^
    시간 허락해 주신다면, 이번에는 제가 술을 살 수 있도록 연락 부탁드립니다. ^^

    그럼 수고하세요.

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

      44619 블럭 I/O를 3882 로 만드셨네요.
      이에따라 46초 이상 걸리던 것이 1초 이내로 들어왔구요.
      대단하십니다. 이번주 중에 전화주시기 바랍니다.^^
      감사합니다.

  2. 2011.04.01 18:22  댓글주소  수정/삭제  댓글쓰기

    비밀댓글입니다

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

      오랜만이군요. TSTORY가 접속된다니 다행입니다. 아마 계속 읽으시면 적응이 되어 속도가 빨라질겁니다. 그리고 휴식은 못 하고 있습니다. 밤에 또 출근해서 블로그 관리와 독서 그리고 연구를 해야하죠. 하나라도 안하면 좀 편해질 거 같은데요.^^ 어쩔 수 없는것 같습니다.

  3. 이장미 2011.04.05 16:57 신고  댓글주소  수정/삭제  댓글쓰기

    출근을 하루에 두 번 하시나 보네요~^^

    게다가 답글을 올리신게 새벽 1시!!

  4. 2011.04.05 21:29  댓글주소  수정/삭제  댓글쓰기

    비밀댓글입니다

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

      놔두셔도 큰문제 없습니다.^^
      블로그를 시작하시는 단계인것 같습니다.
      좋은 글 기대하겠습니다.

    • Favicon of http://1ststreet.tistory.com BlogIcon SITD 2011.04.06 13:38 신고  댓글주소  수정/삭제

      감사합니다 ^^;
      블로그는 어제 시작했습니다.
      아무래도 머릿속 정리하는데는 도움이 많이 될 것 같아서요 ㅎ
      지금은 무리지만 언젠간 저도 좋은 글 하나 남겨보고 싶습니다 ㅎ

11.2.0.2에서 파티션 생성시 용량부족 현상

Partition Large Extents란 무엇인가

Deferred Segment Creation과 Partition Large Extents의 관계 


오라클 11.2.0.2 에서 황당한 일이 발생했다. DBA는 데이터가 없는 빈 테이블을 파티션으로 생성하려고 했다. 그런데 느닷없이 용량부족 에러가 발생한 것이다. 디스크용량은 10 Giga Bytes나 여유가 있으므로 이런 에러메시지를 만날 이유가 없다. 아무리 파티션이 많은 테이블이라도 기껏해야 100MB 정도의 공간만 있으면 테이블을 문제없이 생성했던 DBA로서는 이유를 알 수 없었다. 어찌되었건 문제를 해결해야 하므로 디스크를 30 Giga Bytes 추가했더니 더 이상 에러가 발생하지 않았다고 한다.

 

데이터가 한 건도 없는 파티션 테이블 하나 생성하는데 10 Giga Bytes 이상의 저장공간이 필요하다니? 이런 사실이 이상하다고 생각한다면 오라클 11.2.0.2의 새 기능을 모르고 있는 것이다. 새 기능을 모르면 원인을 파악 할 수 없고, 문제를 해결할 수도 없다. 이 글은 오라클의 새 기능이 어떻게 용량부족 에러를 발생시키는지, 그 에러를 예방하는 방법은 무엇인지 설명한다.

 

해당 테이블 설명

생성하려는 파티션 테이블은 보관기간이 10년이고, 거래일자로 월별 Range 파티션이 되어있고, 서브파티션은 Hash 파티션이 12개씩 잡혀있었다. 그 테이블을 Create 하려면 Disk가 얼마나 필요할까? dba_segments에서 해당 테이블의 용량을 조회 해보니 빈 테이블 하나가 11.52 Giga Bytes나 차지하고 있었다. 이에 따라 다음과 같이 추론 할 수 있다.

 

주 파티션( 10 x 12 개월 ) x 서브 파티션( 12 Hash ) x 8MB = 11520 MB

 

데이터도 없는데 8MB를 소모하는 이유

위의 계산식을 본다면 데이터가 한 건도 없음에도 불구하고, 오라클이 파티션 하나당 8MB를 할당했다는 결론이 나온다. 이런 현상은 11.2.0.2부터 파티션 테이블에서만 발생한다. 파티션 테이블을 만들었을 뿐, 데이터는 한 건도 없다. 그럼에도 불구하고 각 파티션 마다 저장공간을 8MB씩 낭비한 셈이다. 오라클은 왜 이런 무모한 결정을 했을까? 언제나 그렇듯이 비밀은 매뉴얼에 있는 경우가 많다. 아래의 내용이 저장공간을 낭비하는 이유이다.

 

Note:

Beginning with release 11.2.0.2 of Oracle Database, the default size of the first extent of any new segment for a partitioned table is 8 MB instead of 64 KB. This helps improve performance of inserts and queries on partitioned tables.

 

매뉴얼 참조: Estimating the Space Use of a Table

 

위의 글을 해석해보면 성능 향상을 위한 목적임을 알 수 있다. , 처음에 8MB 만큼 공간을 미리 할당하면, 추후에 INSERT가 들어올 때 공간을 다시 할당할 필요가 없으므로 속도가 좋아진다는 것. 그리고 자잘한 extent로 쪼개짐을 방지하여 쿼리의 성능을 높이자는 것이다. 이를 partition large extent 라고 한다. 이론적으로는 무모한 결정이 아님을 알 수 있다.

 

성능 때문에 용량 문제가 발생해도 좋은가?

하지만 이 개념 때문에 파티션 개수가 많은 테이블을 생성하지 못하는 문제가 생긴다. 즉 빈 공간이 90 기가 바이트라고 해도, 파티션 개수가 많은 테이블들은 몇 개도 생성하지 못한다는 결론이 나온다. 이런 기능을 성능이 빨라진다고 해서 오라클이 무조건 적용 할 수 있을까? 오라클은 무언가 믿는 구석이 있었기 때문에 자신 있게 적용할 수 있었다. 그것이 무엇일까? 11.2 매뉴얼을 읽어본 사람이라면 추측 할 수 있을 것이다.

 

빗나간 예측
Understand Deferred Segment Creation note 부분을 참조하면 오라클이 자신감 있게 파티션 마다 8MB를 할당할 수 있었던 이유가 나온다. Deferred Segment Creation(이하 DSC)이라는 기능 때문이다. DSC 기능 덕분에 테이블이 생성될 때는 물리적인 Disk 공간을 차지하지 않는다. 다시 말해, 테이블을 만들 때 공간을 할당하는 것이 아니라, 추후에 데이터가 insert 될 때 각 파티션에 대해 8MB씩 할당한다. DSC는 버전 11.2.0.2 부터 일반테이블 뿐만 아니라, 파티션 테이블에도 적용이 가능해졌다. 오라클은 파티션마다 8MB씩 할당해야 되지만 DSC 기능이 있으므로, 파티션을 생성할 때는 용량문제가 없을 것이라고 생각했을 것이다.

 

하지만, 항상 문제가 되는 것은 이론이 아니라 현실에서 발생한다. 현실에서는 여러 가지 버그 때문에 DSC를 사용할 수 없다. 따라서 해당 파라미터인 DEFERRED_SEGMENT_CREATION False로 놓을 수 밖에 없다. DSC를 사용할 수 없으므로 테이블을 만들 때 각 파티션마다 물리적인 디스크 공간을 8MB씩 차지하게 된다. 따라서, 파티션 개수가 많은 테이블을 만들 때 DSC 기능이 꺼져 있다면 용량부족 현상이 발생할 수 밖에 없다. 오라클로서도 어쩔 수 없는 일이다. 버그를 예상할 수는 없는 노릇 아닌가? DSC의 버그가 해결되면 이런 문제는 자연스럽게 사라질 것이다.

 

DSC에 관한 버그는 조동욱 님의 글 Deferred Segment Creation 재미있는 두가지 버그를 참고하기 바란다.

 

해결방법

DSC는 현재 버그가 있으므로, 기능을 사용하지 못한다. 따라서 무조건 각 파티션에 8MB씩 할당이 되므로 엄청난 Disk 공간이 필요한 것이다. 파티션 테이블을 11.2.0.2 이상에서 생성할 계획이라면 수동으로 STORAGE (INITIAL 65536) 를 주면 된다. 이렇게 하면 처음에 8MB가 아니라 64KB만 공간을 차지한다. 이렇게 설정하면 이전 버전 11.2.0.1처럼 사용하는 것이다.

 

관련 파라미터

_partition_large_extents Default 값은 True이다. True로 되어있으면 각 파티션에 64kb가 아니라 8MB가 할당된다. 이 파라미터는 버전 11.2.0.1cell_partition_large_extents 파라미터를 대체한다. _index_partition_large_extents 도 위의 파라미터와 같은 역할을 한다. 다만 파티션이 아니라 파티션 인덱스라는 점만 다르다. 두 가지 파라미터 _partition_large_extents_index_partition_large_extents 모두 11.2.0.2에서 소개되었다.

 

결론

11.2.0.2에서 버그 때문에 DSC 기능을 막아 놓았다면, 파티션 테이블을 생성할 때 STORAGE 절에 적절한 INITIAL 값을 주기 바란다. 그렇지 않으면 Disk 공간을 많이 차지하게 된다. 특히 각 파티션당 용량이 많지 않지만 관리목적상 파티션을 하는 경우는 INITIAL 값을 주는 것이 유리하다. 이렇게 하면 저장공간을 낭비하지 않고 테이블을 관리 할 수 있다. 또한 개발 DB는 운영 DB에 비해 Disk가 부족하므로 이런 사실을 잘 알아야 에러가 나더라도 당황하지 않는다. 개인적인 목적으로 PC나 노트북등에 오라클을 설치하는 경우도 Disk가 넉넉하지 않을 것이므로 마찬가지이다.

 

오라클 매뉴얼은 여러 번 보아야 한다. 예를 들어 11.2.0.1 매뉴얼을 이미 정독한 사람은 11.2.0.2 매뉴얼을 볼 필요가 없다고 생각 할 수 있다. 결코 그렇지 않다. 이 글에서 나타난 문제만 보아도 11.2.0.2 매뉴얼을 보았다면 전혀 문제가 되지 않을 것이다. 따라서 이전 매뉴얼을 정독 했더라도 11.2.0.2 버전의 새로운 기능은 익혀야 한다. 물론 매뉴얼 전체를 다시 볼 필요는 없다. 예를 들어 Admin가이드의 경우 매뉴얼의 첫 부분인 What's New in Oracle Database Administrator's Guide? 만 참조하면 무슨 기능이 추가된 것인지 쉽게 파악 할 수 있다. 어디에 무슨 내용이 있는지 안다면, 문제의 절반은 해결한 셈이다.

 

관련문서

매뉴얼: Oracle® Database Administrator's Guide 11g Release 2 (11.2)
오라클 노트: 1295484.1
저작자 표시 비영리 동일 조건 변경 허락
신고

'Oracle > Partition' 카테고리의 다른 글

오라클 Upgrade 후에 파티션을 만들 수 없어요  (0) 2011.03.15
Oracle 10g Partitioning 가이드  (12) 2009.08.05
Posted by extremedb

댓글을 달아 주세요

분석함수의 비효율을 찾는 방법
분석함수의 성능개선 원리 

분석함수의 실행계획 3부 - 심화과정의 모범답안

이번 글이 분석함수의 실행계획 시리즈의 마지막이다. 1
2에서 분석함수의 실행계획에 대해서 알아보았다
이 글(3부)은 새로 작성되었으며
, 이전 글( 문제 )은 삭제하지 않고 그대로 두었음을 밝혀둔다. 나중에라도 문제를 풀어볼 사람은 이 글(답안)을 보지 말고, 이전 글을 보기 바란다. 각각의 답안은 오렌지색으로 표시해 두었으므로 쉽게 채점할 수 있을 것이다.


분석함수의 다섯 가지 원리
많은 수의 개발자, DBA, 튜너들은 분석함수를 만나면 식은 땀을 흘린다. 왜냐하면, 분석함수의 비효율을 어떻게 찾을 수 있는지, 또 그 비효율은 어떻게 제거 할 수 있는지 알 수 없기 때문이다. 기껏해야 분석함수의 over절을 만족하는 적절한 인덱스를 생성하는 정도이다. 어쩌면 이런 결과들은 당연하다고 볼 수 있다. 매뉴얼이나 튜닝 책에 분석함수의 비효율을 발견하는 방법과 개선방법에 대한 언급이 별로 없기 때문이다. 이런 어려움을 겪고 있는 여러 사람들의 요구에 의해서 이 글이 만들어졌다. 분석함수의 비효율을 찾고, 성능을 향상시킬 수 있는 다섯 가지 방법을 공개하니, 많은 사람들이 적용하여 더 이상 식은 땀은 흘리지 않기 바란다.