'BLOOM FILTER' 태그의 글 목록

'BLOOM FILTER'에 해당되는 글 4건

2013.05.21 PARTITION WISE JOIN이란 무엇인가? 406
2010.10.25 Cardinality Feedback이 위험할 때 10
2010.09.09 Bloom Filter와 Group By의 관계 10
2008.10.12 Parallel Query 의 조인시 또다른 튜닝방법(Parallel Join Filter) 6

PARTITION WISE JOIN이란 무엇인가?

Oracle/Data Join Method 2013. 5. 21. 06:00

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

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

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

| 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 ;

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

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

| 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);

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

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

| 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 ;

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

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

| 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 JOIN이 Bloom 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 Pair는 PWJ의 성능개선 원리가 아니다
많은 책에서 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 ;

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

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

| 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 ;

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

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

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

저작자표시 비영리 변경금지

'Oracle > Data Join Method' 카테고리의 다른 글

Sort Merge Join에 대한 오만과 편견 (476)	2011.04.28
같은 테이블을 두 번 읽었지만 일량이 틀려요 (16)	2011.01.14
Oracle 조인 방법 25가지 (15)	2010.02.26
Nested Loop Join 성능향상과 관련된 2가지 원리 (17)	2009.05.09
Hash Join Right (Semi/Anti/Outer) 의 용도 (17)	2009.03.02

Posted by extremedb

Cardinality Feedback이 위험할 때

Oracle/Optimizer 2010. 10. 25. 00:01

부제: Cardinality Feed Back의 개념과 사용예제

이번 글은 난이도가 높으므로 익숙하지 않은 사람은 Cardinality Feedback의 개념 정도만 이해하기 바란다. 물론 이 블로그를 꾸준히 구독한 독자라면 어려움 없이 볼 수 있다.

현재 많은 시스템이 Oracle11g로 옮겨가고 있다. 11g는 새로운 기능이 많이 추가되었다. 하지만 새롭고 좋은 기능이라도 완벽하지 못하면 문제가 될 수 있다. 오늘은 11g의 새 기능 때문에 성능문제가 발생하는 경우를 소개한다.

시스템이 운영 중에 있을 때 가장 곤욕스러운 경우 중 하나는 SQL의 실행계획이 갑자기 바뀌어 성능이 나빠지는 것이다. SQL과 인덱스 그리고 통계정보가 모두 바뀌지 않아도 실행계획은 바뀔 수 있다. 예를 들면 Oracle11g의 기능인 Cardinality Feedback을 사용함으로 해서 얼마든지 실행계획이 바뀔 수 있는 것이다. 이번 시간에는 실행계획이 변경되는 원인 중 하나인 Cardinality Feedback 의 개념과 작동방식에 대해 알아보고 이것이 언제 문제가 되는지 분석해 보자. 이번에 소개할 예제는 종합적이다. Cardinality Feedback + Cost Based Query Transformation + Bloom Filter가 결합된 것이다. 이를 놓친다면 이들이 어떻게 결합되는지 알 수 없을 뿐만 아니라 성능이 악화된 원인을 파악할 수 없다.

예측, 실행, 비교, 그리고 전달

소 잃고 외양간 고친다는 말이 있다. 이미 늦었다는 이야기 이지만 좋은 말로 바꾸면 실수를 다시 하지 않겠다는 의지이다. cardinality feedback(이후 CF)도 이와 비슷한 개념이다. 예를 들어 col1 = ‘1’ 이라는 조건으로 filter되면 백만 건이 return된다고 옵티마이져가 예측해서 full table scan을 했다. 하지만 예측과 달리 실행결과가 100건이 나왔다면? 해당 SQL을 다시 실행할 때는 full table scan보다는 index scan이 유리할 것이다. 그런데 같은 SQL을 두 번째 실행할 때 "실제로는 백만 건이 아니라 100건 뿐이야"라는 정보를 옵티마이져에게 알려주는 전달자가 필요하다. 그 전달자가 바로 CF이다. CF가 없으면 결과가 100건 임에도 SQL을 실행 할 때마다 full table scan을 반복할 것이다. 결국 CF는 악성 실행계획을 올바로 수정하는 것이 목적이며 매우 유용한 기능임을 알 수 있다. CF의 단점은 최초에 한번은 full table scan이 필요하다는 것이다. 왜냐하면 실행해서 결과가 나와야만 실제 분포도(건수)를 알 수 있기 때문이다.

CF는 어떻게 실행되나?

CF는 같은 SQL을 두 번 이상 실행했을 때 적용된다. 그 이유는 아래의 CF 적용순서를 보면 알 수 있다.

1. 최초의 실행계획을 작성할 때(Hard Parsing 시에) 예측 분포도가 계산된다.

2. SQL이 실행된다. 한번은 실행 해봐야 예측 분포도와 실제 분포도를 비교할 수 있다.

3. 예측 분포도와 실제 분포도의 값이 차이가 크다면 실제 분포도를 저장한다.

4. 두 번째 실행될 때 CF에 의해 힌트의 형태로 옵티마이져에게 전달되어 실제 분포도가 적용된다. 이때 분포도뿐만 아니라 실행계획이 바뀔 수 있다. 두 번째 이후로 실행될 때는 CF가 계속 적용된다.

CF를 발생시켜보자

실행환경 :Oracle 11.2.0.1

ALTER SYSTEM FLUSH SHARED_POOL;

ALTER SESSION SET "_OPTIMIZER_USE_FEEDBACK" = TRUE; -- CF를 활성화 한다. default로 true이다.

SELECT /*+ GATHER_PLAN_STATISTICS LEADING(c) */

c.cust_id, c.cust_first_name, c.cust_last_name,

s.prod_cnt, s.channel_cnt, s.tot_amt

FROM customers c,

(SELECT s.cust_id,

COUNT (DISTINCT s.prod_id) AS prod_cnt,

COUNT (DISTINCT s.channel_id) AS channel_cnt,

SUM (s.amount_sold) AS tot_amt

FROM sales s

GROUP BY s.cust_id) s

WHERE c.cust_year_of_birth = 1987

AND s.cust_id = c.cust_id ;

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

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

| 0 | SELECT STATEMENT | | | 23 |00:00:00.15 | 5075 | |

|* 1 | HASH JOIN | | 162 | 23 |00:00:00.15 | 5075 | 1215K (0)|

| 2 | JOIN FILTER CREATE | :BF0000 | 162 | 151 |00:00:00.01 | 148 | |

| 3 | TABLE ACCESS BY INDEX ROWID | CUSTOMERS | 162 | 151 |00:00:00.01 | 148 | |

| 4 | BITMAP CONVERSION TO ROWIDS| | | 151 |00:00:00.01 | 2 | |

|* 5 | BITMAP INDEX SINGLE VALUE | CUSTOMERS_YOB_BIX | | 1 |00:00:00.01 | 2 | |

| 6 | VIEW | | 7059 | 55 |00:00:00.15 | 4927 | |

| 7 | SORT GROUP BY | | 7059 | 55 |00:00:00.15 | 4927 |88064 (0)|

| 8 | JOIN FILTER USE | :BF0000 | 918K| 7979 |00:00:00.12 | 4927 | |

| 9 | PARTITION RANGE ALL | | 918K| 7979 |00:00:00.11 | 4927 | |

|* 10 | TABLE ACCESS FULL | SALES | 918K| 7979 |00:00:00.09 | 4927 | |

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

Predicate Information (identified by operation id):

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

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

5 - access("C"."CUST_YEAR_OF_BIRTH"=1987)

10 - filter(SYS_OP_BLOOM_FILTER(:BF0000,"S"."CUST_ID")) --> Bloom Filter 적용

SQL 실행결과 sales 테이블의 예측 분포도는 918K건이며 실제 분포도는 Bloom Filter가 적용되어 7979건이다. 그리고 group by된 operation(ID 7번)의 예측 분포도는 7059건이며 실제 분포도는 55건이다. 예측과 실제의 분포도 차이는 두 경우 모두 100배 이상이다. 따라서 CF가 적용될 것이다. 이와는 반대로 customers 테이블의 예측 분포도와 실제 분포도는 162와 152로 크게 다르지 않으므로 CF가 적용되지 않을 것이다. 이제 위의 SQL을 재 실행한다면 CF가 적용되어 실제 분포도가 적용될 것이다.

--> CF를 발생시키기 위해 위의 SQL 다시 실행

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

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

| 0 | SELECT STATEMENT | | | 23 |00:00:05.61 | 5075 | |

| 1 | SORT GROUP BY | | 55 | 23 |00:00:05.61 | 5075 |75776 (0)|

|* 2 | HASH JOIN | | 270 | 3230 |00:00:05.60 | 5075 | 1201K (0)|

| 3 | TABLE ACCESS BY INDEX ROWID | CUSTOMERS | 162 | 151 |00:00:00.01 | 148 | |

| 4 | BITMAP CONVERSION TO ROWIDS| | | 151 |00:00:00.01 | 2 | |

|* 5 | BITMAP INDEX SINGLE VALUE | CUSTOMERS_YOB_BIX | | 1 |00:00:00.01 | 2 | |

| 6 | PARTITION RANGE ALL | | 7979 | 918K|00:00:02.82 | 4927 | |

| 7 | TABLE ACCESS FULL | SALES | 7979 | 918K|00:00:00.98 | 4927 | |

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

Predicate Information (identified by operation id):

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

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

5 - access("C"."CUST_YEAR_OF_BIRTH"=1987)

Note

-----

- cardinality feedback used for this statement --> CF가 발생되었음을 나타냄.

두 번째 실행 할 때 CF가 적용되어 예측 분포도가 7979로 바뀌었고 group by 분포도는 55건으로 바뀌었다. 이에 따라 실행계획도 바뀌었다. 즉 CF에 의해서 쿼리변환(Complex View Merging)이 발생된 것이다. 그리고 note에 CF가 적용되었다고 친절히 설명된다.

이제 더 자세한 분석을 위하여 10053 Trace의 내용을 보자. 두 번째 실행된 SQL의 10053 Trace에 따르면 쿼리변환전의 SQL은 다음과 같다.

SELECT /*+ LEADING (C) */

c.cust_id, c.cust_first_name, c.cust_last_name,

s.prod_cnt, s.channel_cnt, s.tot_amt tot_amt

FROM tlo.customers c,

(SELECT /*+ OPT_ESTIMATE (GROUP_BY ROWS=55.000000 ) OPT_ESTIMATE (TABLE S ROWS=7979.000000 ) */

s.cust_id cust_id, COUNT (DISTINCT s.prod_id) prod_cnt,

COUNT (DISTINCT s.channel_id) channel_cnt, SUM (s.amount_sold) tot_amt

FROM tlo.sales s

GROUP BY s.cust_id) s

WHERE c.cust_year_of_birth = 1987

AND s.cust_id = c.cust_id ;

CF에 의해서 OPT_ESTIMATE 힌트가 적용되었다. 실제 건수로 적용하는 것이므로 일견 문제가 없어 보인다. 하지만 쿼리변환과정(Complex View Merging)을 거치면 문제가 생긴다. 10053 Trace에서 나타난 쿼리변환 후의 SQL은 다음과 같다.

SELECT /*+ OPT_ESTIMATE (GROUP_BY ROWS=55.000000 ) LEADING (C) OPT_ESTIMATE (TABLE S ROWS=7979.000000 ) */

c.cust_id cust_id, c.cust_first_name cust_first_name,

c.cust_last_name cust_last_name, COUNT (DISTINCT s.prod_id) prod_cnt,

COUNT (DISTINCT s.channel_id) channel_cnt, SUM (s.amount_sold) tot_amt

FROM tlo.customers c, tlo.sales s

WHERE c.cust_year_of_birth = 1987

AND s.cust_id = c.cust_id

GROUP BY s.cust_id, c.ROWID, c.cust_last_name, c.cust_first_name, c.cust_id ;

CF의 문제점은?

위의 SQL은 두 가지 문제점이 있다. 두 문제 모두 쿼리변환에 의해 발생된다. 첫 번째 문제는 Bloom Filter와 관련된 것이다. CF의 영향으로 원본 SQL에 존재했던 Group By 뷰(Complex View)가 사라졌다. 뷰가 없어짐으로써 Bloom Filter가 적용되지 않는다. Filter가 사라졌음에도 불구하고 Filter가 존재했던 Cardinality 7979를 적용해 버렸다. 이에 따라 CF를 적용했음에도 7979건과 실제건수인 91만 8천 건과는 엄청난 차이가 나고 말았다. 즉 Bloom Filter가 사라질 때는 CF를 적용하면 안 된다는 이야기이다. 비유하자면 Filter가 없는데도 불구하고 Filter가 존재할 때의 건수를 적용시킨 것이다.

두 번째 문제는 쿼리변환 후 힌트의 상속과 관련된다. 쿼리변환전의 CF의 의한 힌트를 보면 Group By된 뷰의 건수는 55건이다. 그런데 이 힌트는 오직 sales 테이블에 대한 것이다. 그런데 쿼리변환후의 힌트를 보면 그대로 55건이 적용되어 되어버렸다. Group by가 외부로 빠져 나옴으로 해서 GROUP_BY ROWS는 전체건수와 마찬가지가 되어버렸다. sales 테이블의 Group By건수는 55건이 맞다. 하지만 쿼리변환 때문에 조인 후에 Group By 하게 된다면 cardinality를 다시 계산해야 한다. 조인이 없는 테이블의 Group By건수와 조인후의 Group By건수가 어떻게 같을 수 있나?

두 가지의 문제점은 Cost를 계산할 때 그대로 적용되어 버린다. 10053 trace를 보자.

Access path analysis for SALES

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

SINGLE TABLE ACCESS PATH

Single Table Cardinality Estimation for SALES[S]

Table: SALES Alias: S

Card: Original: 918843.000000 >> Single Tab Card adjusted from:918843.000000 to:7979.000000

Rounded: 7979 Computed: 7979.00 Non Adjusted: 918843.00

Access Path: TableScan

Cost: 1328.68 Resp: 1328.68 Degree: 0

Cost_io: 1321.00 Cost_cpu: 155262306

Resp_io: 1321.00 Resp_cpu: 155262306

Bloom Filter가 없음에도 불구하고 Sales 테이블의 건수(Cardinality)가 7979로 적용되어 버렸다. 이제 Group By가 적용된 건수를 보자.

GROUP BY cardinality: 270.000000, TABLE cardinality: 270.000000

>> Query Blk Card adjusted from 270.000000 to: 55.000000

SORT ressource Sort statistics

Sort width: 583 Area size: 510976 Max Area size: 102340608

Degree: 1

Blocks to Sort: 3 Row size: 69 Total Rows: 270

Initial runs: 1 Merge passes: 0 IO Cost / pass: 0

Total IO sort cost: 0 Total CPU sort cost: 20302068

Total Temp space used: 0

Group By Cardinality와 관련된 Trace 내용이다. 여기서도 잘못된 Group By건수인 55를 적용시키고 있다. 조인 후에 Group By할 때는 Cardinality를 다시 계산해야 옳다. 이래서는 제대로 된 Cost가 나올 수 없다. 여기에 밝혀진 문제점은 SQL 하나에서 나온 것이므로 실전에서는 두 가지 문제뿐만 아니라 더 많을 것이다. 물론 옵티마이져가 모든 경우에 완벽할 수는 없다.

해결책
CF 문제의 해결방법을 생각해보자. 갑자기 실행계획이 바뀌어 성능문제가 발생했을 때 dbms_xplan.display_cursor의 note나 10053 Trace의 실행계획 부분을 보면 CF가 적용되었는지 아닌지 알 수 있다. 만약 CF가 적용되었다면 일단 의심해보아야 한다. 아래는 10053 trace의 실행계획 부분이다.

-----------------------------------------------------------+------------------------

-----------------------------------------------------------+------------------------

| 0 | SELECT STATEMENT | | | | 1368 |

| 1 | SORT GROUP BY | | 55 | 2915 | 1368 |

| 2 | HASH JOIN | | 270 | 14K | 1367 |

| 3 | TABLE ACCESS BY INDEX ROWID | CUSTOMERS | 162 | 5832 | 38 |

| 4 | BITMAP CONVERSION TO ROWIDS | | | | |

| 6 | PARTITION RANGE ALL | | 7979 | 132K | 1329 |

| 7 | TABLE ACCESS FULL | SALES | 7979 | 132K | 1329 |

-----------------------------------------------------------+------------------------

Predicate Information:

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

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

5 - access("C"."CUST_YEAR_OF_BIRTH"=1987)

Content of other_xml column

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

nodeid/pflags: 7 17nodeid/pflags: 6 17 cardinality_feedback: yes --> CF가 적용됨

...이후 생략

만약 CF가 문제가 된다면 해당 SQL을 시작하기 전에 세션단위로 _optimizer_use_feedback = false를 적용하거나 opt_param 힌트를 사용하면 된다. 이렇게 하면 CF가 방지되어 쿼리변환의 원인이 제거된다. 따라서 Bloom Filter도 보존할 수 있다. 또 다른 방법은 인라인뷰에 no_merge 힌트를 적용하여 쿼리변환을 방지하면 문제는 해결된다. 이 두 가지 방법은 결국 쿼리변환을 방지하는 것이다.

결론

CF란 건수를 예측하고, 실행해서 실제건수와 예측건수를 비교하여 차이가 많다면 다음 번에 실행할 때 옵티마이져에게 실제건수를 전달해주는 역할을 한다. CF의 개념을 정리 했으므로 이제 큰 그림을 그려보자. 위의 예제에서 성능이 악화된 직접적인 이유는 Bloom Filter가 사라졌기 때문이다. 하지만 그렇게 된 이유는 쿼리변환 때문이며 쿼리변환의 이유는 CF 때문이다. 직접적인 원인을 찾았다고 해도 포기해선 안 된다. 꼬리에 꼬리를 무는 원인이 있을 수 있기 때문이다. 이를 도식화 하면 다음과 같다.

옵티마이져의 설계관점에서 개선해야 될 사항을 논의 해보자. 옵티마이져가 CBQT를 고려할 때는 두 가지의 경우로 판단한다. 쿼리변환을 적용하기 전(Iteration 1)의 Cost와 적용 후(Iteration 2)의 Cost를 비교해야 되기 때문이다. 쿼리변환전의 Cost를 구할 때는 CF를 적용시키고 반대로 쿼리변환 후에는 CF를 적용하지 않는 것이 더 좋은 Cost를 구할 수 있다. 왜냐하면 비록 답이 같다고 하더라도 형태가 전혀 다른 SQL에 대해 CF를 적용시킬 이유는 없기 때문이다. 물론 이렇게 해도 여전히 문제가 될 수는 있다. 하지만 문제의 발생확률은 많이 줄어들지 않겠는가?

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

COUNT(Distinct 컬럼)의 성능 (9)	2013.05.06
공지 - Cardinality Feed Back이 위험할 때 (1)	2010.10.22
union과 union all의 숨겨진 차이점 (6)	2010.10.18
메트릭스나 터미네이터는 먼 미래의 이야기 인가? (2)	2010.04.28
해결사 되기 (15)	2010.02.04

Posted by extremedb

Bloom Filter와 Group By의 관계

Oracle/Data Access Pattern 2010. 9. 9. 00:30

이전에 Parallel Query 의 조인시 또다른 튜닝방법(Parallel Join Filter)과 Partition Access Pattern 이라는 글에서 Bloom Filter의 개념을 설명한적 있다. 이전 글들 때문인지 모르겠으나 많은 사람들이 Parallel Query를 사용하거나 Partition을 엑세스 할때 Bloom Filter로 후행 테이블의 건수를 줄여 조인 건수를 최소화하는 것으로만 생각한다. 맞는 말이지만 그것이 전부가 아니다.
그래서 이번에는 Parallel과 Partition에 상관없이 Bloom Filter가 발생하는 경우를 살펴보고자 한다. 이 글을 통하여 풀고자 하는 오해는 Bloom Filter가 Join 최적화를 위한 후행 테이블의 Filter 알고리즘일 뿐만 아니라 Group By를 최적화하는 도구이기도 하다는 것이다.

실행환경: Oracle11gR2, Windows 32bit

Bloom Filter를 사용하지 않는 경우

먼저 Bloom Filter가 발생하지 않게 힌트를 주고 실행한다. 뒤에서 Bloom Filter를 적용한 경우와 성능을 비교하기 위함이다.

SELECT /*+ LEADING(c) NO_MERGE(S) NO_PX_JOIN_FILTER(S) */

c.cust_id, c.cust_first_name, c.cust_last_name,

s.prod_cnt, s.channel_cnt, s.tot_amt

FROM customers c,

(SELECT s.cust_id,

COUNT (DISTINCT s.prod_id) AS prod_cnt,

COUNT (DISTINCT s.channel_id) AS channel_cnt,

SUM (s.amount_sold) AS tot_amt

FROM sales s

GROUP BY s.cust_id) s

WHERE c.cust_year_of_birth = 1987

AND s.cust_id = c.cust_id ;

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

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

| 0 | SELECT STATEMENT | | 23 |00:00:06.58 | 5075 | |

|* 1 | HASH JOIN | | 23 |00:00:06.58 | 5075 | 1194K (0)|

| 2 | TABLE ACCESS BY INDEX ROWID | CUSTOMERS | 151 |00:00:00.01 | 148 | |

| 3 | BITMAP CONVERSION TO ROWIDS| | 151 |00:00:00.01 | 2 | |

|* 4 | BITMAP INDEX SINGLE VALUE | CUSTOMERS_YOB_BIX | 1 |00:00:00.01 | 2 | |

| 5 | VIEW | | 7059 |00:00:06.56 | 4927 | |

| 6 | SORT GROUP BY | | 7059 |00:00:06.54 | 4927 | 9496K (0)|

| 7 | PARTITION RANGE ALL | | 918K|00:00:02.80 | 4927 | |

| 8 | TABLE ACCESS FULL | SALES | 918K|00:00:00.95 | 4927 | |

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

Predicate Information (identified by operation id):

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

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

4 - access("C"."CUST_YEAR_OF_BIRTH"=1987)

Id 기준으로 8번에서 Buffers 항목을 보면 전체건(4927 블록)을 Scan 하였다. 그리고 A-Rows 항목을 보면 Sales 테이블에 대해 약 92만건(918K)을 읽었다. 이제 Id 6번을 보자. 전체 건수인 92만건에 대하여 Sort Group By를 적용하는데 부하가 집중되는 것을 알 수 있다. 시간상으로도 Group By를 하는데 3.7초 정도 걸렸으며 PGA를 9496K나 사용하였다. 즉 대부분의 시간을 Sort Group By Operation 에서 소비한 것이다.

이제 위의 SQL에 Bloom Filter를 적용해 보자. Sales 테이블에 파티션이 적용되어 있으나 파티션과 상관없이 Bloom Filter가 적용된다.

SELECT /*+ LEADING(c) NO_MERGE(S) PX_JOIN_FILTER(S) */

c.cust_id, c.cust_first_name, c.cust_last_name,

s.prod_cnt, s.channel_cnt, s.tot_amt

FROM customers c,

(SELECT s.cust_id,

COUNT (DISTINCT s.prod_id) AS prod_cnt,

COUNT (DISTINCT s.channel_id) AS channel_cnt,

SUM (s.amount_sold) AS tot_amt

FROM sales s

GROUP BY s.cust_id) s

WHERE c.cust_year_of_birth = 1987

AND s.cust_id = c.cust_id ;

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

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

| 0 | SELECT STATEMENT | | 23 |00:00:00.15 | 5075 | |

|* 1 | HASH JOIN | | 23 |00:00:00.15 | 5075 | 1197K (0)|

| 2 | JOIN FILTER CREATE | :BF0000 | 151 |00:00:00.01 | 148 | |

| 3 | TABLE ACCESS BY INDEX ROWID | CUSTOMERS | 151 |00:00:00.01 | 148 | |

| 4 | BITMAP CONVERSION TO ROWIDS| | 151 |00:00:00.01 | 2 | |

|* 5 | BITMAP INDEX SINGLE VALUE | CUSTOMERS_YOB_BIX | 1 |00:00:00.01 | 2 | |

| 6 | VIEW | | 55 |00:00:00.14 | 4927 | |

| 7 | SORT GROUP BY | | 55 |00:00:00.14 | 4927 |88064 (0)|

| 8 | JOIN FILTER USE | :BF0000 | 7979 |00:00:00.12 | 4927 | |

| 9 | PARTITION RANGE ALL | | 7979 |00:00:00.10 | 4927 | |

|* 10 | TABLE ACCESS FULL | SALES | 7979 |00:00:00.09 | 4927 | |

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

Predicate Information (identified by operation id):

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

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

5 - access("C"."CUST_YEAR_OF_BIRTH"=1987)

10 - filter(SYS_OP_BLOOM_FILTER(:BF0000,"S"."CUST_ID"))

Bloom Filter를 사용해보니

위의 실행계획에서 Id 기준으로 8번을 보면 Name 항목에 Bloom Filter가 사용되었다. Bloom Filter의 위력이 얼마나 대단한지 살펴보자. 먼저 Sales 테이블을 Full Table Scan 하였으므로 Buffers는 4927로 Bloom Filter를 사용하지 않는 경우와 똑같다. 하지만 Bloom Filter가 적용되어 92만건이 아닌 7979건(A-Rows 참조)만 살아남았다. 이처럼 Bloom Filter는 Hash Join시 Probe(후행) 집합에서 조인에 참여하는 건수를 줄임으로써 Join 시간을 단축시킨다. Bloom Filter의 효과는 이것이 끝이 아니다. 건수가 줄어듦으로 해서 Sort Group By 작업 또한 92만 건이 아니라 7979건만 하면 된다. Group By에 의한 PGA 사용량을 Bloom Filter가 적용된 실행계획과 비교해보면 100배 이상 차이가 나는 이유도 Bloom Filter의 효과 때문이다.

제약사항

이번에 test한 케이스는 Parallel Query도 아니며 Partition Pruning과도 관련이 없다. 하지만 항상 발생하지는 않는다. 이유는 세 가지 제약사항이 있기 때문이다.

첫 번째, Hash Join을 사용해야 한다. Sort Merge Join이나 Nested Loop Join에서는 발생하지 않는다.
두 번째, Build Input(Driving) 집합에 Filter 조건이 존재해야 한다. 위의 SQL에서는 cust_year_of_birth = 1987가 Filter 조건으로 사용되었다. Filter가 필요한 이유는 선행집합의 Filter조건을 후행집합에서 Bloom Filter로 사용해야 하기 때문이다.
세 번째, Probe(후행) 집합에서 Group By를 사용해야 한다. 위의 SQL에서도 cust_id로 Group By를 하고 있다. 물론 후행집합에 Group By가 적용되려면 뷰나 인라인뷰가 필요하다.

만약 Bloom Filter가 사라져 전체 건이 조인에 참여한다면?

상상하기 싫은 경우지만 Probe(후행) 집합에 Bloom Filter가 사라지는 경우를 살펴보자. 이 경우는 Sales 테이블 전체건수(약 92만건)가 모두 Hash Join에 참여하게 되므로 성능이 저하될 것이다. 아래의 SQL이 그것인데 위의 SQL에서 NO_MERGE(S) 힌트와 PX_JOIN_FILTER(S)만 뺀 것이다.

SELECT /*+ LEADING(c) */

c.cust_id, c.cust_first_name, c.cust_last_name,

s.prod_cnt, s.channel_cnt, s.tot_amt

FROM customers c,

(SELECT s.cust_id,

COUNT (DISTINCT s.prod_id) AS prod_cnt,

COUNT (DISTINCT s.channel_id) AS channel_cnt,

SUM (s.amount_sold) AS tot_amt

FROM sales s

GROUP BY s.cust_id) s

WHERE c.cust_year_of_birth = 1987

AND s.cust_id = c.cust_id ;

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

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

| 0 | SELECT STATEMENT | | 23 |00:00:05.39 | 5075 | |

| 1 | SORT GROUP BY | | 23 |00:00:05.39 | 5075 |75776 (0)|

|* 2 | HASH JOIN | | 3230 |00:00:05.37 | 5075 | 1185K (0)|

| 3 | TABLE ACCESS BY INDEX ROWID | CUSTOMERS | 151 |00:00:00.01 | 148 | |

| 4 | BITMAP CONVERSION TO ROWIDS| | 151 |00:00:00.01 | 2 | |

|* 5 | BITMAP INDEX SINGLE VALUE | CUSTOMERS_YOB_BIX | 1 |00:00:00.01 | 2 | |

| 6 | PARTITION RANGE ALL | | 918K|00:00:02.70 | 4927 | |

| 7 | TABLE ACCESS FULL | SALES | 918K|00:00:00.94 | 4927 | |

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

Predicate Information (identified by operation id):

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

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

5 - access("C"."CUST_YEAR_OF_BIRTH"=1987)

악성 쿼리변환

힌트를 제거하자 View Merging(뷰 해체)이 발생하여 인라인뷰가 제거되었다. (View Merging이 발생하지 않는 독자는 MERGE(S) 힌트를 추가하기 바란다) 뷰가 없어짐에 따라 후행집합에서 Group By가 없어지고 조인이 끝난 후에 Group By가 발생한다. 후행집합의 Group By가 사라졌으므로 Bloom Filter가 적용되지 않는다. 따라서 Sales 테이블의 전체건 (약 92만건)이 조인에 참여하게 된다. Bloom Filter가 적용된 경우는 단 55건만 조인에 참여하므로 이 차이는 어마 어마한 것이다. 그 결과 전체 수행시간중에서 Hash Join에서만 절반의 시간을 소모하였다. 즉 잘못된 쿼리변환이 발생하여 Bloom Filter를 죽여버린 것이다. View Merging이 발생할 때 Bloom Filter를 적용할 수 없게되어 비효율이 발생되는지 주의깊게 관찰해야 한다.

결론

이번 Test 케이스에서 Bloom Filter의 특징을 두 가지로 압축할 수 있다. Group By 작업량을 최소화 시켜주고 Hash Join 건수를 줄여준다. 이 두 가지 효과가 맞물려 Bloom Filter를 적용한 SQL이 0.15초 만에 끝날 수 있는 것이다. 후행 테이블에서 Bloom Filter로 걸러지는 건수가 많을 때 두 가지 작업(Group By, Hash Join) 모두 최대의 효율을 발휘한다. 바꿔 말하면 Bloom Filter로 제거되는 건수가 미미 하다면 사용해선 안된다.

CVM(Complex View Merging)이 발생하면 여지없이 Bloom Filter가 사라진다. CVM 때문에 성능이 저하된다면 NO_MERGE 힌트를 사용하여 뷰를 유지시켜야 한다. Bloom Filter가 사라지는 경우는 이 경우 뿐만 아니다. 11gR2에서 새로 적용된 Cardinality Feedback 때문에 Bloom Filter가 사라지는 경우가 보고되고 있다. 마지막(세번째) SQL을 최초로 실행시켰을 때와 두번째로 실행시켰을 때 DBMS_XPLAN.DISPLAY_CURSOR의 실행계획이 달라진다면 Cardinality Feedback이 Bloom Filter를 제거시킨것이다. Shared Pool을 Flush하고 두번 연달아 테스트 해보기 바란다. 이런 현상들 때문에 옵티마이져에 새로운 기능이 추가될 때마다 긴장을 늦출 수 없다. 버전이 올라갈수록 튜닝하기가 쉬워지는것인가? 아니면 그 반대인가?

'Oracle > Data Access Pattern' 카테고리의 다른 글

분석함수의 실행계획 - 1부 (12)	2011.02.07
Index_desc 힌트와 rownum = 1 조합은 안전한가? (12)	2010.11.09
Inlist Iterator를 사용하지 말아야 할 때 (13)	2010.06.23
오렌지나 TOAD에서 Predicate Information을 참조하는 방법 (7)	2010.01.12
Partition Access Pattern (13)	2009.12.24

Posted by extremedb

Parallel Query 의 조인시 또다른 튜닝방법(Parallel Join Filter)

Oracle/Data Join Method 2008. 10. 12. 14:51

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

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

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

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

create table emp_2
as
select * from emp_1 ;

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

결론 :
Parallel Join distribution 과 Parallel join filter 을 적절히 이용하면 최적화된 Parallel Join Query를 만들수 있다.
다시한번 말하지만 꼭필요한 경우에만 이런종류의 힌트를 사용하여야 한다.

편집후기 :만약 parallel Join filter 로직에 관심이 있어서 직접 구현하려면 아래의 1번 문서를 참조하기 바란다.

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

'Oracle > Data Join Method' 카테고리의 다른 글

Hash 조인시 Bushy tree Plan 유도하기 (4)	2008.11.01
Hash 조인의 튜닝시 주의사항(Work Area 의 튜닝) (3)	2008.10.30
New Features 의 중요성(Partition Outer Join 의 사용법) (5)	2008.08.29
Parallel Query 의 조인시 Row Distribution (3)	2008.07.19
Lateral View 를 활용한 선택적 조인 (2)	2008.05.29

Posted by extremedb

이전 1 다음

Science of Database

'BLOOM FILTER'에 해당되는 글 4건

PARTITION WISE JOIN이란 무엇인가?

'Oracle > Data Join Method' 카테고리의 다른 글

Cardinality Feedback이 위험할 때

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

Bloom Filter와 Group By의 관계

'Oracle > Data Access Pattern' 카테고리의 다른 글

Parallel Query 의 조인시 또다른 튜닝방법(Parallel Join Filter)

'Oracle > Data Join Method' 카테고리의 다른 글

카테고리

최근에 올라온 글

최근에 달린 댓글

글 보관함

링크

태그목록

최근에 받은 트랙백

티스토리툴바


	오동규 블로그 odong91@naver.com by extremedb