이전에 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 https://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 https://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 버그도 언급 되었으면 좋겠습니다!!

오라클 Transformer 때때로 반복작업이 일어날경우 같은 테이블에 대한 부하가 심해지므로 TEMP 테이블을 만들어서 저장하고 테이블을 이용하여 반복적인 작업을 하게되는 경우가 있다. 대표적인 경우가 GROUPING SETS 사용하는 경우이다.

SELECT department_id, JOB_ID, AVG (salary) avg_sal

  FROM employee a

GROUP BY GROUPING SETS(department_id, JOB_ID)   ;

 

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

| Id  | Operation                   | Name                     | Rows  | Cost  | Time      |

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

| 0   | SELECT STATEMENT            |                          |       |    11 |           |

| 1   |  TEMP TABLE TRANSFORMATION  |                          |       |       |           |

| 2   |   LOAD AS SELECT            |                          |       |       |           |

| 3   |    TABLE ACCESS FULL        | EMPLOYEE                 |   107 |     3 |  00:00:01 |

| 4   |   LOAD AS SELECT            |                          |       |       |           |

| 5   |    HASH GROUP BY            |                          |     1 |     3 |  00:00:01 |

| 6   |     TABLE ACCESS FULL       | SYS_TEMP_0FD9D6608_434CFB|     1 |     2 |  00:00:01 |

| 7   |   LOAD AS SELECT            |                          |       |       |           |

| 8   |    HASH GROUP BY            |                          |     1 |     3 |  00:00:01 |

| 9   |     TABLE ACCESS FULL       | SYS_TEMP_0FD9D6608_434CFB|     1 |     2 |  00:00:01 |

| 10  |   VIEW                      |                          |     1 |     2 |  00:00:01 |

| 11  |    TABLE ACCESS FULL        | SYS_TEMP_0FD9D6609_434CFB|     1 |     2 |  00:00:01 |

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

 

위의 PLAN 보면 먼저 ID 기준으로 3번에서 employee 테이블을 읽어서 필요한 컬럼만 TEMP 테이블에 저장해두고 그테이블을 반복해서 이용(ID 기준으로 6, 9, 11)하게 되는것이다. 그렇다면 3번이나 반복해서 temp 테이블을 사용하는걸까?

해답은 10053 trace 있다. id 기준으로 6번에 해당하는 SQL 아래와 같다. 

SELECT  /*+  */ NULL C0, C1 C1, BIN_TO_NUM(1, GROUPING(C1)) D0, COUNT(A0), SUM(A0)  FROM "SYS"."SYS_TEMP_0FD9D6608_434CFB" GROUP BY (C1)

 

위에서 C0, C1 TEMP 테이블의 컬럼 ALIAS 이며 각각 department_id, JOB_ID 의미한다. 또한 BIN_TO_NUM 함수를 사용한 이유는 나중에 ID 기준으로 11번째 에서 Group by 단위를 알아내기 위함이다. COUNT(A0), SUM(A0) select 한이유도 11번에서 AVG (salary) 값을 구하기 위해서이다. 참고로 avg(col1) 함수는 논리적으로 sum(col1)/count(col1) 같다.

또한 id 기준으로 9번에 해당하는 SQL 아래와 같다. 

SELECT  /*+  */ C0 C0, NULL C1, BIN_TO_NUM(GROUPING(C0), 1) D0, COUNT(A0), SUM(A0)  FROM "SYS"."SYS_TEMP_0FD9D6608_434CFB" GROUP BY (C0)

 
최종적으로 Transformation이 적용된 SQL은 다음과 같다.

with SYS_TEMP_0FD9D6608_434CFB as
   (
   SELECT department_id AS C0, JOB_ID AS C1, salary AS A0
     FROM employee a
   )  ,
   TEMP1 as
   (
    SELECT NULL C0, C1, COUNT(salary) AS A1, SUM(salary) AS A0
     FROM SYS_TEMP_0FD9D6608_434CFB
    GROUP BY C1
   ) ,
   TEMP2 as
   (
    SELECT C0, NULL C1, COUNT(salary) AS A1, SUM(salary) AS A0
     FROM SYS_TEMP_0FD9D6608_434CFB
    GROUP BY C0
   ) ,
  SYS_TEMP_0FD9D6609_434CFB AS
  (
   SELECT TEMP1. * FRPM TEMP1
   UNION ALL
   SELECT TEMP1. * FRPM TEMP1
  )
SELECT C0 AS DEPARTMENT_ID, C1 AS JOB_ID, A0 AS AVG_SAL
  FROM (SELECT C0, C1,
                       DECODE(A0, 0, TO_NUMBER(NULL), A1/A0) AS A0 --> 분모가 0 일 경우 처리
              FROM SYS_TEMP_0FD9D6609_434CFB )  ;



불만

id
기준으로 6번과 9번에서 각각 job_id department_id group by 해놓고 이것을 id 11 번에서 합쳐서 보여주게 된다. 하지만 필자는 이런 변환에 대하여 불만이 있다. 위의 쿼리는 EMPLOYEE 테이블을 GROUP BY 하지 않은 상태로 TEMP 테이블에 적재한다. 이것은 매우 비효율적이다. EMPLOYEE 테이블을 Temp 테이블에 적재시 미리 Group BY 하여 넣을 수가 있다. 아래의 SQL 처럼 미리 GROUP BY 하여 건수를 미리 줄여놓으면 반복작업시의 부하가 획기적으로 줄어들수 있기 때문이다.


select  department_id, JOB_ID, sum(sum_sal) / sum(cnt) as avg_sal 

from  (SELECT department_id, JOB_ID, count(salary) cnt, sum(salary) sum_sal

           FROM employee a

          GROUP BY department_id, JOB_ID  )

GROUP BY GROUPING SETS(department_id, JOB_ID)   ;

 

이렇게 수동으로 SQL 바꾸는 방법은 Grouping Sets 변환로직이 변하지 않는한 확실한 튜닝방법이 될수 있으므로 반드시 고려되어야 한다.

Posted by extremedb

댓글을 달아 주세요

  1. feelie 2009.09.28 19:27  댓글주소  수정/삭제  댓글쓰기

    20여일 동안 기다리고 기다렸습니다.

    그동안 많이 바쁘셨던 모양이네요..

    오늘도 좋은 내용 잘 봤습니다. 그런데 하나 궁금한 점이 있습니다.
    내용중에
    "id 기준으로 6번과 9번에서 각각 job_id 와 department_id 로 group by 해놓고 이것을 id 11 번에서 합쳐서 보여주게 된다." 라고 하셨는데

    id 6,9 각각 temp 테이블(SYS_TEMP_0FD9D6608_434CFB)을 사용하고, 결과를 합해서 SYS_TEMP_0FD9D6609_434CFB 테이블에 저장하고 id 11에서 이것을 액세스해서 뷰로 만들고 select 하는것가요?

    실행계획 순서로 헷갈리고, ID 기준으로 : 3->2->1->6->5->4->9->8->7->11->10->1 이런 순서인가요?

    • Favicon of https://scidb.tistory.com BlogIcon extremedb 2009.09.29 11:52 신고  댓글주소  수정/삭제

      네 말씀 하신대로 6번과 9번에서 각각 수행된 쿼리를 union 하여 SYS_TEMP_0FD9D6609_434CFB 테이블에 저장 하고 이것을 11번에서 사용하는 것입니다. 이것은 GROUPING SET 이 기본적으로 UNION 을 적용하기 때문에 그렇습니다.
      최종 SQL 은 다음과 같습니다.

      SELECT /*+ CARDINALITY( 107) */ "sys_view_v"."C0" "DEPARTMENT_ID","sys_view_v"."C1" "JOB_ID","sys_view_v"."A0" "AVG_SAL"
      FROM (SELECT /*+ NO_MERGE NO_PUSH_PRED(v) */ C0, C1, DECODE(A0, 0, TO_NUMBER(NULL), A1/A0) AS A0
      FROM ( SELECT "SYS"."SYS_TEMP_0FD9D661C_25E901".*
      FROM "SYS"."SYS_TEMP_0FD9D661C_25E901" --> UNION 적용된 SQL
      ) v
      ) "sys_view_v"

      그리고 수행순서는 3->2->6->5->4->9->8->7->11->10->1->0 입니다. 이유는 11번에서도 TEMP TABLE 을 사용하기 때문에 그렇습니다.

  2. feelie 2009.09.29 12:33  댓글주소  수정/삭제  댓글쓰기

    감사합니다.

  3. feelie 2009.10.16 13:42  댓글주소  수정/삭제  댓글쓰기

    지난번에 한번보고 오늘다시 보다가 궁금한점이 있습니다.
    테스트를 해서 시간을 비교해보면 먼저 group by후 처리하는부분이 제일 빠를것 같은데요.
    테스트를 해보면 더 늦게 나옵니다.
    1. 00:00:00.11
    2. 00:00:00.37
    3. 00:00:00.21
    데이터의 양에 적어서 그런것인지,A_TIME만으로판단하면 안되는것인지요?
    환경 : 10.2.3.0
    데이터량 : 80000

    1. union all
    ---------------------------------------------------------------------------------------------------------------------------------
    | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
    ---------------------------------------------------------------------------------------------------------------------------------
    | 1 | VIEW | | 1 | 35 | 700 | 684 (4)| 00:00:09 | 38 |00:00:00.11 | 3018 |
    | 2 | UNION-ALL | | 1 | | | | | 38 |00:00:00.11 | 3018 |
    | 3 | HASH GROUP BY | | 1 | 21 | 84 | 342 (4)| 00:00:05 | 23 |00:00:00.11 | 1509 |
    | 4 | TABLE ACCESS FULL| TB_TESTLOGHIST | 1 | 73839 | 288K| 335 (2)| 00:00:05 | 74084 |00:00:00.01 | 1509 |
    | 5 | HASH GROUP BY | | 1 | 14 | 56 | 342 (4)| 00:00:05 | 15 |00:00:00.09 | 1509 |
    | 6 | TABLE ACCESS FULL| TB_TESTLOGHIST | 1 | 73839 | 288K| 335 (2)| 00:00:05 | 74084 |00:00:00.01 | 1509 |
    ---------------------------------------------------------------------------------------------------------------------------------


    2. grouping sets
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem |
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    | 1 | TEMP TABLE TRANSFORMATION | | 1 | | | | | 38 |00:00:00.37 | 1944 | 134 | 134 | | | |
    | 2 | LOAD AS SELECT | | 1 | | | | | 1 |00:00:00.17 | 1646 | 0 | 132 | 525K| 525K| 525K (0)|
    | 3 | TABLE ACCESS FULL | TB_TESTLOGHIST | 1 | 73839 | 576K| 335 (2)| 00:00:05 | 74084 |00:00:00.01 | 1509 | 0 | 0 | | | |
    | 4 | LOAD AS SELECT | | 1 | | | | | 1 |00:00:00.10 | 142 | 132 | 1 | 265K| 265K| 265K (0)|
    | 5 | HASH GROUP BY | | 1 | 1 | 5 | 3 (34)| 00:00:01 | 23 |00:00:00.08 | 138 | 132 | 0 | | | |
    | 6 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6744_BD323BAA | 1 | 1 | 5 | 2 (0)| 00:00:01 | 74084 |00:00:00.01 | 138 | 132 | 0 | | | |
    | 7 | LOAD AS SELECT | | 1 | | | | | 1 |00:00:00.10 | 140 | 0 | 1 | 265K| 265K| 265K (0)|
    | 8 | HASH GROUP BY | | 1 | 1 | 5 | 3 (34)| 00:00:01 | 15 |00:00:00.08 | 135 | 0 | 0 | | | |
    | 9 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6744_BD323BAA | 1 | 1 | 5 | 2 (0)| 00:00:01 | 74084 |00:00:00.01 | 135 | 0 | 0 | | | |
    | 10 | VIEW | | 1 | 1 | 23 | 2 (0)| 00:00:01 | 38 |00:00:00.01 | 7 | 2 | 0 | | | |
    | 11 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6745_BD323BAA | 1 | 1 | 23 | 2 (0)| 00:00:01 | 38 |00:00:00.01 | 7 | 2 | 0 | | | |
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    3. group by 후 grouping sets
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem |
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    | 1 | TEMP TABLE TRANSFORMATION | | 1 | | | | | 38 |00:00:00.21 | 1547 | 3 | 3 | | | |
    | 2 | LOAD AS SELECT | | 1 | | | | | 1 |00:00:00.16 | 1513 | 0 | 1 | 265K| 265K| 265K (0)|
    | 3 | HASH GROUP BY | | 1 | 208 | 1664 | 342 (4)| 00:00:05 | 30 |00:00:00.14 | 1509 | 0 | 0 | | | |
    | 4 | TABLE ACCESS FULL | TB_TESTLOGHIST | 1 | 73839 | 576K| 335 (2)| 00:00:05 | 74084 |00:00:00.07 | 1509 | 0 | 0 | | | |
    | 5 | LOAD AS SELECT | | 1 | | | | | 1 |00:00:00.02 | 10 | 1 | 1 | 265K| 265K| 265K (0)|
    | 6 | HASH GROUP BY | | 1 | 1 | 18 | 3 (34)| 00:00:01 | 23 |00:00:00.01 | 6 | 1 | 0 | | | |
    | 7 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6746_BD323BAA | 1 | 1 | 18 | 2 (0)| 00:00:01 | 30 |00:00:00.01 | 6 | 1 | 0 | | | |
    | 8 | LOAD AS SELECT | | 1 | | | | | 1 |00:00:00.02 | 8 | 0 | 1 | 265K| 265K| 265K (0)|
    | 9 | HASH GROUP BY | | 1 | 1 | 18 | 3 (34)| 00:00:01 | 15 |00:00:00.01 | 3 | 0 | 0 | | | |
    | 10 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6746_BD323BAA | 1 | 1 | 18 | 2 (0)| 00:00:01 | 30 |00:00:00.01 | 3 | 0 | 0 | | | |
    | 11 | VIEW | | 1 | 1 | 23 | 2 (0)| 00:00:01 | 38 |00:00:00.01 | 7 | 2 | 0 | | | |
    | 12 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6747_BD323BAA | 1 | 1 | 23 | 2 (0)| 00:00:01 | 38 |00:00:00.01 | 7 | 2 | 0 | | | |
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    • Favicon of https://scidb.tistory.com BlogIcon extremedb 2009.10.17 23:35 신고  댓글주소  수정/삭제

      데이터의 건수가 적을때는 시간으로 판단하는것은 효율을 알기가 힘듭니다. 따라서 가장 좋은 방법은 Buffers 와 Used-Mem 가 가장 적게 드는것으로 판단 하시면 됩니다.
      1번의 Union all 이 가장 빠른 이유는 Temp 테이블에 데이터를 Load 하는 작업이 없기 때문입니다. 2번은 Loading 작업이 있지요.

부제목 : GBP (Group By Placement ) 의 목적및 용도

Parallel Query 수행시 Group by 를 먼저 수행하라(Group By Push Down) 라는 글에서 먼저 Group By 를 수행하여 성능을 향상시키는 경우를 설명하였다. 오늘도 Group By Push Dwon 과 아주 흡사한 Query Transformation 에 대하여 설명하려 한다.

Group By Push Down 은 Parallel Query 에서 한정적으로 나타나는 기능이지만 Group By Placement 는 이러한 제약이 없다. 또한 Group By Placement 는 Query Transformation 의 종류 이지만 Group By Push Down은 SQL 자체의 변환과정이 없다는 점에서 엄연히 다르다.

GBP 가 뭐하는 거지?
GBP 란 기본적으로 조인의 부하를 줄이기 위한 수단이다. 조인을 수행하기전에 Group By 를 먼저 수행하고 건수를 줄이고 난후에 조인을 수행함으로서 조인건수가 획기적으로 감소되게 하는데 그목적이 있다. 이 기능은 주로 OLTP 보다는 DW 의 대용량 Mart 등에서 사용할 경우 성능향상을 극대화 할수 있다. 아래의 SQL 을 보자.

환경 Oracle 11g (11.1.0.7)

  SELECT   /*+ qb_name(main) place_group_by(@main (S@main)) */

         cust_city_id, SUM (quantity_sold)

    FROM customers c, sales s

   WHERE c.cust_id = s.cust_id AND s.cust_id BETWEEN 5000 AND 5500

   GROUP BY c.cust_city_id ;


위의 SQL의 목적은 고객 테이블(customers)과 판매 테이블(sales)을 조인하여 고객의 도시별 판매수량을 구하는 것이다.

상식적으로는 ..
일반적인 상식으로는 customers 테이블과 sales 테이블을 조인한 후에 Group BY 가 한번 수행된다고 알고 있다. 하지만 Oracle 11g 로 넘어오면서 '상식의 파괴'가 일어난다.      

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

| Id  | Operation                | Name     | Rows  | Bytes | Cost  | Time      |

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

| 0   | SELECT STATEMENT         |          |       |       |   973 |           |

| 1   |  HASH GROUP BY           |          |   620 |   17K |   973 |  00:00:12 |

| 2   |   HASH JOIN              |          |  7059 |  193K |   972 |  00:00:12 |

| 3   |    VIEW                  | VW_GBC_1 |  7059 |  124K |   566 |  00:00:07 |

| 4   |     HASH GROUP BY        |          |  7059 |   90K |   566 |  00:00:07 |

| 5   |      PARTITION RANGE ALL |          |   28K |  370K |   492 |  00:00:06 |

| 6   |       TABLE ACCESS FULL  | SALES    |   28K |  370K |   492 |  00:00:06 |

| 7   |    TABLE ACCESS FULL     | CUSTOMERS|   54K |  542K |   405 |  00:00:05 |

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


Group By 가 두번 발생하다
위의 Plan 을 보면 Group By 가 두번 발생하였으며 조인도 sales 테이블을 Group By 한 이후에 발생하였다.

왜 두번 수행되나?
이것은 대용량 테이블인 sales 테이블을 먼저 조인 기준컬럼인 cust_id 로 먼저 Group By 하고 난후에 조인함으로서 조인의 부하를 줄이기 위함이다. 다시 말하면 오라클 Transformer는 SQL 을 아래와 같이 바꾼 것 이다.

  SELECT c.cust_city_id cust_city_id, SUM (vw_gbc_1.item_2) sum_qt
    FROM (SELECT   s.cust_id item_1, SUM (s.quantity_sold) item_2
              FROM sales s
             WHERE s.cust_id <= 5500 AND s.cust_id >= 5000
          GROUP BY s.cust_id) vw_gbc_1,
         customers c
   WHERE c.cust_id = vw_gbc_1.item_1
   GROUP BY c.cust_city_id;


sales 테이블을 Group By 하여 인라인뷰를 먼저 만들고 customers 와 조인후 다시 c.cust_city_id 로 Group By 하고 있다. 인라인 뷰의 이름이 vw_gbc_1 인데 GBP 가 여러번 발생되면 vw_gbc_1, vw_gbc_2, vw_gbc_3 ... 처럼 숫자 부분이 증가 된다.
 
GBP 는 CBQT(Cost Based Query Transformation) 이다
Query Transformer 는 GBP 를 수행하기 위해 변환된(GBP 가 수행된) SQL 과 변환되지 않은 SQL을 각각 비용을 계산하여 가장 비용이 낮은 SQL 을 선택하게 된다. GBP 가 수행된 SQL 은 여러개 일수 있다.
아래는 작업을 수행하는 과정을 보여주는 10053 Trace 내용이다.

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

Cost-Based Group By Placement

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

GBP: Checking validity of GBP for query block MAIN (#1)

GBP: Checking validity of group-by placement for query block MAIN (#1)

 

GBP: Using search type: exhaustive

GBP: Considering group-by placement on query block MAIN (#1)

GBP: Starting iteration 1, state space = (1,2) : (0,0)

GBP: Transformed query
...중간생략


10053은 어렵지 않다
10053 을 어렵게 생각하는 DBA 들이 있다. 절대 어렵지 않다. GBP를 수행하기 위한 Using search type이 exhaustive 로 되어 있다. Using search type 이라는 것 은 변환 가능한 경우의 수를 어디까지 고려 할것인지 의 정도(level) 을 설명한 것이고 그 level 은 exhaustive 로 되어 있다. exhaustive 라는 것은 모든 변환가능한 경우의 수를 고려 하겠다는 뜻이다. 

Iteration 이란 무엇인가?
Iteration 이란 CBQT 에서만 발생하며 기본적으로 변환이 수행된 경우와 수행되지 않은 경우의 Cost 를 비교하기 위한 경우의 수이다. 일반적으로 iteration 1 에서 변환이 수행되지 않은 경우를 나타내며 iteration 2 에서는 변환이 수행된 경우의 일련의 과정을 나타낸다. 마지막에는 iteration 1 과 iteration 2 의 Cost 를 비교하여 Cost 가 낮은 경우를 선택하게 된다.
 
Iteration 은 여러번 생길 수 있다
복잡한 SQL 의 경우 변환의 결과가 여러개 일수 있는데 이때는 Starting iteration 1, Starting iteration 2, Starting iteration 3 ... 등으로 증가한다. 하지만 원본 SQL 은 place_group_by 힌트를 사용하였으므로 GBP 를 수행한 경우(iteration 1)와 수행하지 않은 경우(iteration 2)의 Cost 를 비교하지 않고 iteration 1 에서 멈추게 된다.


GBP 를 Control 하자
GBP Control 하는 파라미터는 _optimizer_group_by_placement 이며 Default True 이다. 힌트는 GBP 를 강제하려면 place_group_by 헤제 하려면 no_place_group_by 힌트를 사용하면 된다.
 
결론
GBP 는 기본적으로 오라클이 자동으로 수행한다.
GBP 는 성능을 향상시키는 훌륭한 기능이지만 잘못 사용하면 오히려 독이 될수 있다. 조인을 먼저 수행하는 것이 오히려 결과 건수를 획기적으로 줄여주는 경우가 있는데 이런 경우는 GBP 를 사용하면 안된다. 이런 경우가 아니면서 조인하기 전에 먼저 Group By 하여 건수를 확실히 줄일수 있을때만 사용하여야 한다.

 

Posted by extremedb

댓글을 달아 주세요

  1. feelie 2009.08.25 18:49  댓글주소  수정/삭제  댓글쓰기

    일주일이 무척 길다는 생각이 들 정도로 언제 새로운 내용을 올리시나 기다렸습니다.
    좋은 내용 잘 봤습니다.
    지난번에 보내주신 메일도 잘 받았습니다. 늦었지만 감사합니다.
    다음 내용을 기대하면서 열공하렵니다..

    • Favicon of https://scidb.tistory.com BlogIcon extremedb 2009.08.26 00:47 신고  댓글주소  수정/삭제

      네 반갑습니다.
      도움이 된다면 다행입니다.
      한달이 대부분 4주이기 때문에 글이 4번 혹은 5번 밖에 나가지 못합니다. 이점 양해 바랍니다.

  2. 칸쵸뿐이야흥 2009.08.26 11:13  댓글주소  수정/삭제  댓글쓰기

    좋은글 감사드립니다

Parallel Query 수행시 튜닝 방법에 대해 아래와 같이 2개의 글을 연재한 바 있다.
1.Parallel Query 의 조인시 Row Distribution  --> Join 시 Row 의 분배방법 튜닝
2.Parallel Query 의 조인시 또다른 튜닝방법(Parallel Join Filter) --> Join Filter 튜닝

Parallel Query 의 튜닝방법은 많지 않아...
Parallel Query 의 튜닝 방법은 많지 않은데 그 이유는 Parallel Query 기능 자체를 튜닝 하는것이 아니라 Hash 조인의 튜닝 혹은 Block I/O 의 튜닝등 Parallel 자체와는 상관없는 것 을 튜닝하는 경우가 많기 때문이다.
오늘은 3번째로 Parallel Query 자체를 튜닝 하는 또다른 방법에 대하여 논의 할것이다.

잘못된 미신을 믿지 마라.
사실 필자가 Group By Push Down 기능을 설명하는 이유는 잘못된 미신 때문이다.
"Parallel Query 를 수행하면 Group By 가 두번 수행된다. 따라서 Group By 가 있는 SQL 은 Parallel 을 사용하지 마라."
실제 DBA 들의 입에서 오고 가는 말들이다. 잘못된 미신이 퍼져 있다니 참으로 안타까운 현실이 아닐수 없다.
Parallel Query 를 수행하면 Group By 가 무조건 두번 수행되는 것은 아니며 두번 수행 된다면 오히려 성능향상을 기대할 수 있다.
이 글을 읽고 개념을 확실히 하기 바란다.

TQ 를 알고 가자.
먼저 Group By Push Down 기능을 설명하기 전에 TQ(Table queues) 개념을 알아야 한다.
1.TQ 는 Processes간의 데이터를 주고받는 기능을 한다.
2.하나의 TQ 는 여러개의 parallel Slave 를 가진다.
3.TQ 는 Parallel Query 수행시 생성된다.
상세한 개념은 위에서 언급한 글중 2번을 참조하기 바란다.

Group By Push Down 이 뭐야?
1.Group By Push Down 이란 TQ 에 데이터를 전달하기 전에 Group By 를 수행하여 데이터의 건수를 대폭 줄인후에 TQ 에 데이터를 전달함으로서 일량을 줄이고 성능을 향상시키는데 목적이 있다.
2.Group By Push Down 은 Parallel Query 에 Group By 가 포함되어 있는 경우 발생한다.

원리는 같다.
이기능은 마치 DW 용 SQL 작성시 Fact 테이블(대용량) 을 먼저 Group By 한후에 Dimension 테이블(소용량 코드 테이블)과 조인하여 조인 건수를 대폭 줄임으로서  성능 향상을 꾀하는것과 같은 개념이다. 이기능은 오라클이 자동으로 해주기도 하는데 이것을 "Group By Placement" 라고 하며 추후 따로 글을 올릴 생각이다.

어떻게 하는건데?
Group By Push Down을 수행하기위해 아래의 SQL 을 보자.
환경: Oracle 11g 11.1.0.7

SELECT   /*+ GATHER_PLAN_STATISTICS FULL(A) parallel(A 4) GBY_PUSHDOWN */
         prod_id, cust_id, COUNT (*) cnt
    FROM sh.sales A
GROUP BY prod_id, cust_id;

GBY_PUSHDOWN 란 힌트를 사용하였으며 아래의 Plan 에서 보는바와 같이 성공적으로 Group By Push Down 이 수행되었다.

---------------------------------------------------------------------------
| Id  | Operation                | Name     | E-Rows | Used-Mem | Used-Tmp|
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |          |        |          |         |
|   1 |  PX COORDINATOR          |          |        |          |         |
|   2 |   PX SEND QC (RANDOM)    | :TQ10001 |    359K|          |         |
|   3 |    HASH GROUP BY         |          |    359K| 2197K (0)|         |
|   4 |     PX RECEIVE           |          |    359K|          |         |
|   5 |      PX SEND HASH        | :TQ10000 |    359K|          |         |
|   6 |       HASH GROUP BY    |          |    359K| 3284K (1)|    1024 |
|   7 |        PX BLOCK ITERATOR |          |    918K|          |         |
|*  8 |         TABLE ACCESS FULL| SALES    |    918K|          |         |
---------------------------------------------------------------------------

원래 수행되어야 하는 Group By 는 Id 기눈으로 3번(파랑색 부분) 이지만 Id 6번에서 먼저 Group By 가 수행되었다. 그이유는 Id 5번의 :TQ10000 에게 데이터를 전달하기전에 데이터를 줄여서 성능을 향상시키기 위함이다. 실제로 옵티마이져는 Id 5번에서 Group By 된 359K Row에 대한 데이터만 처리 할것으로 예상하고 있다.

튜닝은 Trade Off 이다.
주의 사항이 있다. 실제로 TQ의 일량은 줄어들지만 불필요한 Hash Group By 혹은 Sort Group By 가 수행되어 성능이 더 나빠질수 도 있다는것이다. Id 6 에서 Hash Area Size 가 부족하여 Disk 작업(Used-Tmp 부분 참조)이 발생하였다. 따라서 Group By 를 하면 건수가 몇배~ 몇십배이상 줄어드는 경우에 적용하여야 하며  Disk 에서 Sort 및 Hash 작업이 발생하는 경우는 PGA 튜닝을 동반하여야 한다.

Group By Push Down 이 적용되지 않은 Plan 을 보여다오.
GBY_PUSHDOWN 힌트 대신에 NO_GBY_PUSHDOWN 힌트를 사용하면 Group By Push Down이 발생하지 않는다.

SELECT   /*+ GATHER_PLAN_STATISTICS FULL(A) parallel(A 4) NO_GBY_PUSHDOWN */
         prod_id, cust_id, COUNT (*) cnt
    FROM sh.sales A
GROUP BY prod_id, cust_id;


----------------------------------------------------------------
| Id  | Operation               | Name     | E-Rows | Used-Mem |
----------------------------------------------------------------
|   0 | SELECT STATEMENT        |          |        |          |
|   1 |  PX COORDINATOR         |          |        |          |
|   2 |   PX SEND QC (RANDOM)   | :TQ10001 |    359K|          |
|   3 |    HASH GROUP BY        |          |    359K| 4492K (0)|
|   4 |     PX RECEIVE          |          |    918K|          |
|   5 |      PX SEND HASH       | :TQ10000 |    918K|          |
|   6 |       PX BLOCK ITERATOR |          |    918K|          |
|*  7 |        TABLE ACCESS FULL| SALES    |    918K|          |
----------------------------------------------------------------


정상적으로 Group By 가 한번만 수행되었지만 옵티마이져는 TQ 의 일량이 Group By Push Down 이 적용된 SQL 에 비하여 918K 로 약 3배정도 중가한것으로 판단하였고 실제로도 그렇게 수행된다.

Group By Push Down은 11g 신기능이 아니다.
GBY_PUSHDOWN / NO_GBY_PUSHDOWN 등의 힌트는 11g 에서 새로 나온 것 이다. 하지만 이전버젼에서도 _groupby_nopushdown_cut_ratio 파라미터를 0 으로 세팅하면 Group By Push Down 을 강제로 수행할수 있다. 이파라미터의 Default 값은 3 이며 이경우는 Group By Push Down 의 수행여부를 옵티마이져가 판단한다. 아래는 옵티마이져의 Costing 과정을 10053 Trace 에서 발췌한 것이다.

*********************************
Number of join permutations tried: 1
*********************************
GROUP BY adjustment factor: 0.707107
GROUP BY cardinality:  359386.000000, TABLE cardinality:  918843.000000
Costing group-by pushdown:
    SORT ressource         Sort statistics
      Sort width:         598 Area size:      552960 Max Area size:   104857600
      Degree:               1
      Blocks to Sort: 563 Row size:     20 Total Rows:         229711
      Initial runs:   2 Merge passes:  1 IO Cost / pass:        306
      Total IO sort cost: 869      Total CPU sort cost: 230852464
      Total Temp space used: 4629000
  Distribution cost: resc_cpu: 91885309  resp_cpu: 22971327
  Costing final group-by:
  Subtracting no-pushdown group-by:
    SORT ressource         Sort statistics
... 이후 생략

적재적소에 사용하자.
실제로 옵티마이져는 Group By Push Down 의 수행여부를 Cost 로서 판단하고 있으므로 기본적으로는 오라클에 맏기면 된다. 하지만 아주 복잡한 SQL 이나 옵티마이져가 판단을 잘못할 경우에 Group By Push Down 을 수동으로 적절히 발생시킨다면 성능향상을 꾀할수 있다.

편집후기:
필자가 이글을 올린다고 하니 어느 지인은 컨설턴트의 밥그릇 타령을 한다.
다시말해 이런것들을 모든 개발자가 안다면 컨설턴트가 설자리가 없다는 것인데...
글쎄...
여러분은 어떻게 생각하는가?    

Posted by extremedb

댓글을 달아 주세요

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

    이런 글을 올린다고 해서 밥그릇이 없어진다면 Jonathan Lewis나 Steve Adams는 이미 굶어 죽었을 겁니다. ^^;

    이런 기세로 계속 정리하시면 Parallel Execution에 대해 좋은 내용의 글모음(아마 이번 책으로도 나오겠죠?)이 되겠네요. 개인적으로 한번 다루어주셨으면 하는 것이 RAC 환경에서의 Parallel Query에 대한 내용입니다. RAC와 PQ가 합쳐지면 많은 사람들에게 더 미지의 영역이지요.

    • Favicon of https://scidb.tistory.com BlogIcon extremedb 2009.08.12 12:53 신고  댓글주소  수정/삭제

      저도 같은 생각입니다.
      이런글을 올릴수록 오히려 설자리가 늘지 않을까요?
      RAC + PQ 가 합쳐진 경우는 고려 해야할 것 들이 많네요. 미쳐 생각치 못했던 부분인데 좋은정보 감사드립니다.
      내년에 나올책은 지면이 400 페이지로 한정적이라 ....
      한번 고려해 보아야 할거 같습니다.

  2. feelie 2009.08.12 21:18  댓글주소  수정/삭제  댓글쓰기

    얼마전부터 이곳에서 좋은 자료를 보고 있는데 이제야 댓글을 달아봅니다.
    좋은 내용 감사합니다.
    오라클을 조금안다고 생각했는데. 이곳의 내용을 보니 오라클을 전혀 모르고 있었다는 생각이 듭니다.
    좋은 책들도 추천해주셨는데 대부분이 원서라 조금 아쉽습니다.
    혹시 저정도의 수준에서 (대용량데이터베이스 정독수준) 고수로 가기위해서 해주실 얘기나 책이있으면(원서는 제가 좀) 부탁드리겠습니다.
    다음주 내용도 기대가 됩니다.

    • Favicon of https://scidb.tistory.com BlogIcon extremedb 2009.08.12 22:41 신고  댓글주소  수정/삭제

      원서를 싫어하시니 답변하기가 어렵네요.^^
      하지만 제가 아는데로 나열해 보겠습니다.
      2가지 관점 입니다. 모델링과 튜닝.
      개발관점에서 제일 좋은것은 대용량 시리즈 이기때문에 제외 하였습니다.
      제일 좋은것은 오라클 메뉴얼 입니다.--> 영문임
      메뉴얼 중에서도 Concept Guide, Admin Guide, Performace Tuning Guide 를 추천합니다.
      그것이 영문이라서 싫으시면 OCP 과정의 책을 차선으로 추천합니다. 총 4권으로 알고 있고 한글도 있는것으로 알고 있습니다.
      다음으로 OTN 과정중 RAC for Admin 교육도 한글 책으로 나온것을 보았습니다.
      그리고 OWI 관련 책은 단연 조동욱님의 책 2권을 추천합니다. 튜닝을 하려면 OWI 는 필수라고 할수 있습니다.
      그리고 트랜잭션을 깊이 있게 공부하시려면 김시연님의 책을 추천합니다.
      오라클을 전체 DBMS 관점에서 산을 보고 튜닝하려면 이번에 나온 조시형님의 오라클 성능 고도화 해법시리즈에서 3장 을 추천합니다. 물론 다른장의 내용들은 기본적으로 아셔야 합니다.
      그리고 중요한 모델링 책인데요.
      어쩔수 없이 원서를 추천할수 밖에 없습니다.
      Richard Barker 가 1990년에 저술한 Case*Method: Entity Relationship Modelling 이란 책 입니다.
      저는 이책보다 좋은 책을 아직 보지 못했습니다.
      원서라고해도 반드시 정복해야 할 책입니다.
      지금 이책을 살수 있을지는 저도 확신할수 없습니다.
      그리고 원서를 쉽게 읽을수 있는 방법이 있습니다.
      여러군데의 카페에서 유명한 원서들의 스터디를 진행하고 있으니 기회가 되면 반드시 참석하시기 바랍니다.
      감사합니다.

    • feelie 2009.08.13 11:17  댓글주소  수정/삭제

      너무 성의 없이 날로 먹으려고 생각하실수도 있지만
      여러 카페라면 이것도 좀 추천을 해주실수 있는지요?

      답변 감사합니다.

    • Favicon of https://scidb.tistory.com BlogIcon extremedb 2009.08.13 12:43 신고  댓글주소  수정/삭제

      메일을 남겨주시면 메일로 답변드리겠습니다.

  3. feelie 2009.08.13 16:43  댓글주소  수정/삭제  댓글쓰기

    feelie@empal.com 입니다.
    도움 주시니 열심히 열공하겠습니다.
    감사합니다..

  4. Favicon of https://1ststreet.tistory.com BlogIcon SITD 2011.07.01 16:23 신고  댓글주소  수정/삭제  댓글쓰기

    저같은 경우엔(개발자입니다.)

    extremedb님의 글을 볼 때마다 아...이래서 컨설턴트가 대단한 거구나..하고 감탄을 합니다.

    대대수의 사람들에게 오히려 전문성을 더 보여줄 수 있는 방법이 아닐까 합니다.

    보통, 어렵고 힘들다는건 알면 알수록 더 많이 느끼니까요 ㅎ
    (최근에 한동안 놓았던 영어공부를 다시 시작했는데요,
    예전엔 몰랐는데 해보니까 더 어렵고 막막하다는 걸 느끼고 있습니다...)

치명적인 Hash Group By 버그

10g 부터 group by 시에 느린성능의 Sort Group by 가 사라지고 빠른성능의 Hash Group By 가 등장 했다.
하지만 현재시점(10.2.0.3)에서 항상 Hash Group By 가 동작되는 것은 아니다.
성능면에서 배치 SQL 혹은 Migration 작업시에 몇억건의 데이터를 sort 하게되면 견딜수 없다.
주로 Sort Group by 는 insert - select - group by 상황에서 발생한다.
opt_param 힌트로도 해결되지 않았으며 Only select 문또는
CTAS (Create table as Select)문에서는 발생하지 않는걸로 확인됬다.
아래는 간단한 테스트를 진행 하고 현상황에서 Sort Group by 를 피할수 있는 해법을 제공한다.


1.테스트 환경
버젼 : 10gR2(10.2.0.3) ,
Optimizer mode :all_rows
관련 파라미터 : _gby_hash_aggregation_enabled = true
계정 : scott/tiger


2.Select Test

analyze table dept compute statistics;

analyze table emp compute statistics;

select /*+ use_hash(b a) */ empno, ename, job, mgr, hiredate, sal, comm, a.deptno
from
(select max(EMPNO) as empno, max(ENAME) as ename, max(JOB) as job, max(MGR) as mgr,
max(HIREDATE) as hiredate, max(SAL) as sal, max(COMM) as comm, deptno
from emp
group by DEPTNO) a,
dept b
where a.deptno = b.deptno;



Execution Plan
--------------------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=6 Card=4 Bytes=356)
1 0 HASH JOIN (Cost=6 Card=4 Bytes=356)
2 1 VIEW (Cost=4 Card=4 Bytes=348)
3 2 HASH (GROUP BY) (Cost=4 Card=4 Bytes=128) --> 정상적인 hash group by
4 3 TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes=448)
5 1 INDEX (FULL SCAN) OF 'PK_DEPT' (INDEX (UNIQUE)) (Cost=1 Card=4 Bytes=8)

3.CTAS Test

-- CTAS 시에 inline view 가 merge 되므로 상황을 재현하기위해 no_merge 힌트 사용

create table new_emp as
select /*+ use_hash(b a) */ empno, ename, job, mgr, hiredate, sal, comm, a.deptno
from
(select /*+ no_merge */
max(EMPNO) as empno, max(ENAME) as ename, max(JOB) as job, max(MGR) as mgr,
max(HIREDATE) as hiredate, max(SAL) as sal, max(COMM) as comm, deptno
from emp
group by DEPTNO) a,
dept b
where a.deptno = b.deptno;


Execution Plan
--------------------------------------------------------------------------------
0 CREATE TABLE STATEMENT Optimizer=ALL_ROWS (Cost=8 Card=4 Bytes=356)
1 0 LOAD AS SELECT OF 'NEW_EMP'
2 1 HASH JOIN (Cost=7 Card=4 Bytes=356)
3 2 VIEW (Cost=5 Card=4 Bytes=348)
4 3 HASH (GROUP BY) (Cost=4 Card=4 Bytes=128) --> 정상적인 hash group by
5 4 TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes=448)
6 2 INDEX (FULL SCAN) OF 'PK_DEPT' (INDEX (UNIQUE)) (Cost=1 Card=4 Bytes=8)


4.Insert-Select-group by Test

--2번/3번 테스트 에서 나타나지 않았던 Sort Group By 가 Insert 시에만 나타난다.

insert into emp
select /*+ use_hash(b a) */ empno, ename, job, mgr, hiredate, sal, comm, a.deptno
from
(select max(EMPNO) as empno, max(ENAME) as ename, max(JOB) as job, max(MGR) as mgr,
max(HIREDATE) as hiredate, max(SAL) as sal, max(COMM) as comm, deptno
from emp
group by DEPTNO) a,
dept b
where a.deptno = b.deptno;

Execution Plan
--------------------------------------------------------------------------------

0 INSERT STATEMENT Optimizer=ALL_ROWS (Cost=6 Card=4 Bytes=356)
1 0 HASH JOIN (Cost=6 Card=4 Bytes=356)
2 1 VIEW (Cost=4 Card=4 Bytes=348)
3 2 SORT (GROUP BY) (Cost=4 Card=4 Bytes=128) --> 비정상적인 Sort group by
4 3 TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes=448)
5 1 INDEX (FULL SCAN) OF 'PK_DEPT' (INDEX (UNIQUE)) (Cost=1 Card=4 Bytes=8)


5.해법

1) merge 힌트사용: 두개의 테이블이 merge 되는것이 유리한 경우


insert into emp
select /*+ merge(a) use_hash(b a) */ empno, ename, job, mgr, hiredate, sal, comm, a.deptno
from
(select max(EMPNO) as empno, max(ENAME) as ename, max(JOB) as job, max(MGR) as mgr, max(HIREDATE) as hiredate, max(SAL) as sal, max(COMM) as comm, deptno
from emp
group by DEPTNO) a,
dept b
where a.deptno = b.deptno;

Execution Plan
--------------------------------------------------------------------------------

0 INSERT STATEMENT Optimizer=ALL_ROWS (Cost=6 Card=4 Bytes=184)
1 0 HASH (GROUP BY) (Cost=6 Card=4 Bytes=184) --> 정상적인 hash group by
2 1 HASH JOIN (Cost=5 Card=14 Bytes=644)
3 2 INDEX (FULL SCAN) OF 'PK_DEPT' (INDEX (UNIQUE)) (Cost=1 Card=4 Bytes=56)
4 2 TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes=448)

2)Migration 인 경우는 insert-select 대신에 CTAS 사용

3)With 구문과 Merterialize 힌트사용

insert into emp
with max_emp as
(select /*+ materialize */
max(EMPNO) as empno, max(ENAME) as ename, max(JOB) as job,
max(MGR) as mgr, max(HIREDATE) as hiredate, max(SAL) as sal,
max(COMM) as comm, DEPTNO
from emp
group by DEPTNO),
max_dept_emp as
( select /*+ use_hash(b a) */
empno, ename, job, mgr, hiredate, sal, comm, a.deptno
from max_emp a,
dept b
where a.deptno = b.deptno )
select * from max_dept_emp;

Execution Plan
--------------------------------------------------------------------------------

0 INSERT STATEMENT Optimizer=ALL_ROWS (Cost=8 Card=4 Bytes=356)
1 0 TEMP TABLE TRANSFORMATION
2 1 LOAD AS SELECT OF 'EMP'
3 2 HASH (GROUP BY) (Cost=4 Card=4 Bytes=128) --> 정상적인 hash group by
4 3 TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes=448)
5 1 HASH JOIN (Cost=4 Card=4 Bytes=356)
6 5 VIEW (Cost=2 Card=4 Bytes=348)
7 6 TABLE ACCESS (FULL) OF 'SYS.SYS_TEMP_0FD9D6616_4EBAE980' (TABLE (TEMP)) (Cost=2 Card=4 Bytes=128)
8 5 INDEX (FULL SCAN) OF 'PK_DEPT' (INDEX (UNIQUE)) (Cost=1 Card=4 Bytes=8)


6.결론
적절한 방법을 사용하여 대용량 Group By 시에 Sort 를 피할수 있는방법을 제시하였으나
더뛰어난 solution 이 있을수 있으므로 여러가지 테스트 후에 적용하기 바란다.
Posted by extremedb

댓글을 달아 주세요