'2010/09'에 해당되는 글 3건

  1. 2010.09.16 케리커쳐 (4)
  2. 2010.09.15 The Logical Optimizer-Part 3 PPT (5)
  3. 2010.09.09 Bloom Filter와 Group By의 관계 (9)

케리커쳐

etc 2010.09.16 13:51

블로그 사진을 변경하였다. 계기는 저번주에 딸 아이와 롯데월드에 가서 회전목마를 타는데 우연히 인물을 스케치하고 있는 분을 발견하였다. 알고보니 케리커쳐를 그리는 분이었다. 내친김에 페이스북의 프로필 사진도 같이 변경해 버렸다. 몇년간 블로그를 운영하면서 이전 사진이 식상해졌기 때문이다.

사용자 삽입 이미지

그림은 명지대학교 만화예술창작과의 김희섭 교수님이 직접 그려주셨다. 주말에만 직접 나오셔서 그림을 그려주시는것 같다. 사진의 하단에 교수님의 싸인을 볼 수 있다. 그런데 교수님이 그려서 그런지 몰라도 실물보다 그림더 더 낫다는 의견이 있다. 아니, 그런의견이 대부분이다. 오호, 통제라. 안타깝다. 실물이 더 나으면 좋으련만...^^

지인들이 블로그에 대하여 여러가지 의견을 주었다. Database와 관련된 글만 올라오니 논문처럼 너무 딱딱하다는 것이었다. 어쩌겠는가? 내가 가진것이 이것뿐인 것을... 하지만 지인의 말처럼 한달에 한두번 정도는 Database 글 이외의 것, 예를들면 IT, 세상살이, 좋은 책의 서평, etc 등을 올려야겠다.  
신고

'etc' 카테고리의 다른 글

케리커쳐  (4) 2010.09.16
Posted by extremedb

댓글을 달아 주세요

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

    와우!! 케리커쳐.. 멋지세요..
    개인적으론 케리커쳐도 잘 나왔지만 동규님 실물이 더 나은거 같은데요? ^^

    드뎌 오늘 아이폰4를 받는데.. 참 시간이 안가는거 같습니다.. ㅎㅎ;
    개통하게 되면 동규님 페이스북 신청 드리겠습니다.

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

      정식님 오랜만입니다.
      실물이 더 낫다는 의견은 처음이네요.^^
      아이폰을 오늘 받으시네요. 부럽습니다.
      페이스북으로 오시면 더 많은 이야기를 나눌 수 있겠습니다. 감사합니다.

  2. Favicon of http://prosense.tistory.com BlogIcon kagami™ 2010.09.19 19:46 신고  댓글주소  수정/삭제  댓글쓰기

    와우 잘그렸는대요


PDF 파일의 95 페이지에 타이틀이 잘못되어 수정해서 다시 올림(2010-09-15 오후 6시)

책 (The Logical Optimizer)의 Part 3에 대한 PPT가 완성되었다. Oracle 10g 부터 시작된 CBQT (Cost Based Query Transformation)에 대한 내용이다. 파워포인트 작업을 할때는 몰랐는데 완성하고 보니 130 페이지가 넘어가고 파일크기도 30MB가  넘는다. Tstory의 용량제한 때문에 할 수 없이 파일을 3개로 나눠(분할압축) 올린다. Part 3의 내용을 이해하는데 도움이 되었으면 한다.

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

압축  프로그램 7zip





PS
Part 4 도 작업이 완료되는 대로 올릴 예정이다.
신고
Posted by extremedb

댓글을 달아 주세요

  1. 윤상원 2010.09.15 17:07 신고  댓글주소  수정/삭제  댓글쓰기

    파트3, 기다리고 있었는데 감사합니다!
    책 내용을 정리하는데 많은 도움이 될 거 같습니다.

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

      반갑습니다.
      의외로 파워포인트를 기다리는 분들이 많이 계시는군요.
      Part 4도 힘을 내서 빨리 작업을 해야겠습니다.
      감사합니다.

  2. 윤상원 2010.09.15 17:30 신고  댓글주소  수정/삭제  댓글쓰기

    방금 보는중에 PDF파일 95페이지에 보니 갑자기 3.8 CVM 내용이 나오네요. 앞내용이 3.15 GBPD인데 말이죠. 카피되는 중에 잘못 들어간거 같습니다~

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

   

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

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

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

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

   

 

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

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

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

|   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 4927Bloom Filter를 사용하지 않는 경우와 똑같다. 하지만 Bloom Filter가 적용되어 92만건이 아닌 7979(A-Rows 참조)만 살아남았다. 이처럼 Bloom FilterHash 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 ;

 

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

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

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

|   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하고 두번 연달아 테스트 해보기 바란다. 이런 현상들 때문에 옵티마이져에 새로운 기능이 추가될 때마다 긴장을 늦출 수 없다. 버전이 올라갈수록 튜닝하기가 쉬워지는것인가? 아니면 그 반대인가?


 

신고
Posted by extremedb

댓글을 달아 주세요

  1. 윤상원 2010.09.10 09:05 신고  댓글주소  수정/삭제  댓글쓰기

    Bloom Filter 에 대한 좋은 정보네요~
    근데 11gR2에서 새롭게 추가된 Cardinality Feedback 은 대략 어떤 기능인가요??

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

      윤상원님 반갑습니다.
      Cadinality Feedback을 한마디로 정의하면 "옵티마이져의 예측 건수가 실제 수행한 건수와 차이가 많이 나는 경우 실제 수행건수로 보정해주는 기능" 입니다.
      물론 보정해주는 과정에서 실행계획이 바뀔 수 있습니다.
      감사합니다.

  2. HyDBA 2010.09.14 10:59 신고  댓글주소  수정/삭제  댓글쓰기

    안녕하세요
    오동규님 항상 좋은 내용 많이 올려주셔서 감사합니다.
    글은 처음으로 남기네요.
    NO_PX_JOIN_FILTER Hint는 11g에서 추가된 Hint 인가요?
    정확히 어떤 기능을 수행하는지 궁금하네요.
    간단한 답변 부탁드립니다.
    감사합니다.

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

      PX_JOIN_FILTER/NO_PX_JOIN_FILTER 힌트는 10gR2에서 새로나온것입니다.
      기능은 조인을 하기전에 후행테이블을 Filter로 걸러서 건수를 미리 줄여놓습니다.
      이렇게 한후에 조인을 하면 조인 부하가 줄어드는 효과가 있습니다.

      이 Post에서 말하는 것은 Join Filter가 조인의 부하를 줄이는 것 뿐만 아니라 추가적으로 Group By의 부하 또한 줄일 수 있다는 겁니다.
      도움이 되셨나요?
      감사합니다.

  3. Favicon of http://jc9988.me.hn BlogIcon 사랑은★눈물에 씨앗 2010.10.07 11:05 신고  댓글주소  수정/삭제  댓글쓰기

    사㉭랑ψ해요□ <좋은 글 감사합니다.<늘! 건강하시고 행복하시기를 기원합니다.<평생 건강정보 : 내 병은 내가 고친다.>

  4. J 2010.11.05 16:47 신고  댓글주소  수정/삭제  댓글쓰기

    Bloom filter에 대해서는 알겠는데..ㅋㅋ
    time-out Bloom filter는 뭔지 아세요??

  5. 2010.11.17 13:10 신고  댓글주소  수정/삭제  댓글쓰기

    bloom filter 관련 10.2.0.1 ~ 10.2.0.3 instance Crash 버그 ,
    10.2.0.4 Wrong Result 버그도 언급 되었으면 좋겠습니다!!