'Query Transformation'에 해당되는 글 4건

  1. 2010.10.25 Cardinality Feedback이 위험할 때 (10)
  2. 2010.10.18 union과 union all의 숨겨진 차이점 (6)
  3. 2010.01.20 Index Unque Scan은 SQL을 변경시킨다 (6)
  4. 2009.10.15 Transformer - SQL 튜닝의 새로운 패러다임 (11)

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

 

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

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

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

|   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 다시 실행               

                

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

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

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

|   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의 실행계획 부분이다.

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

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

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

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

| 5   |      BITMAP INDEX SINGLE VALUE  | CUSTOMERS_YOB_BIX|       |       |       |

| 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를 적용시킬 이유는 없기 때문이다. 물론 이렇게 해도 여전히 문제가 될 수는 있다. 하지만 문제의 발생확률은 많이 줄어들지 않겠는가?

 


Posted by extremedb

댓글을 달아 주세요

  1. 윤상원 2010.10.25 16:15  댓글주소  수정/삭제  댓글쓰기

    항상 좋은 정보 감사합니다.
    글을 읽고 궁금한 점이 있어 이렇게 질문을 남깁니다.
    bind 변수를 사용할 경우는 Cardinality Feedback이 적용되지 않나요?
    bind 변수를 사용할 경우는 실행할 때마다 결과가 달라지므로 Cardinality Feedback이 적용되기 어려울꺼 같은데요.
    bind 변수를 사용할 경우는 Adaptive Cursor Sharing가 적용되는 것이고 상수를 사용할 경우는
    Cardinality Feedback이 적용되는 것인가요?
    답변 부탁드립니다~

    • Favicon of https://scidb.tistory.com BlogIcon extremedb 2010.10.25 18:09 신고  댓글주소  수정/삭제

      상수를 변수로 바꾸어 테스트 해도 똑같이 cf가 발생합니다.
      하지만 변수값이 바뀌었을 때는 좀더 깊은 연구가 필요합니다.
      감사합니다.

  2. salvation 2010.10.25 16:15  댓글주소  수정/삭제  댓글쓰기

    글 시작할때 어렵다고 하셨는데 전혀 어렵지않으면 어느정도 수준인가요? 자랑이 아니라 정말궁금...

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

      1.CF
      2.Query Transformation
      3.Bloom Filter

      1,2,3 을 다 안다면 어렵지 않습니다.
      만약 컨설턴트라면 수준이 높다고 할 수 없지만
      DBA라면 옵티마이져를 어느정도 아시는 분이라고 할 수 있습니다. 즉 개발자, DBA, 컨설턴트는 기대치가 다르므로 다르게 판단되어야 할 것 같습니다.

    • 혈기린 2010.10.26 15:24  댓글주소  수정/삭제

      컨설턴트가 될려면 어느정도의 수준이 되어야 할까요?
      멀고먼 길이네요 -.-;;

    • Favicon of https://scidb.tistory.com BlogIcon extremedb 2010.10.26 19:16 신고  댓글주소  수정/삭제

      어려운 질문입니다.
      기술은 기본만 되어 있다면 가능합니다. 제가 컨설팅을 시작할 당시(지금도 마찬가지 이지만)는 실력이 좋지 않았습니다. 실력이란 목표가 있으면 따라 옵니다.
      구체적인 목표가 있어야 훌륭한 컨설턴트가 될 것 같습니다.

      많은 분들이 열정을 이야기 하시는데 열정은 불꽃 같은 것이라 사그라 들 수 있습니다. 큰 목표하나를 세우시고 세부적인 목표를 세우신다면 좋은 성과가 있을것 입니다. 큰 목표가 40 이나 50세에 뭘 하고 있을건지 에 대한 대답이라면 세부적인 목표는 큰꿈을 이루기 위해 이번주에 할일이 무엇인지를 묻는것 입니다. 목표가 세워지면 열정은 따라 옵니다. 도움 되셨나요?

    • 혈기린 2010.10.27 16:02  댓글주소  수정/삭제

      조언 감사 드립니다
      목표를 세워서 열씨미 노력해야겠네요 ^^

  3. salvation 2010.10.25 20:49  댓글주소  수정/삭제  댓글쓰기

    답변 감사합니다. 위의 댓글은 갤럭시S로 쓴겁니다. ㅎㅎ
    저는 개발자도, DBA도, 컨설턴트도 아닌... 어중이 떠중이 입니다.
    저의 수준에 의심이 들어 질문을 던진 것 이였습니다.

    상수를 변수로 바꾸었을때에 CF가 발생한다 말씀 하셨는데..
    바인드 피킹이 off인 상태에서는 발생하지 않을것으로 보이는데..
    바인드 피킹이 on 상태라면 결국 CF라는게 결국 어느 한범주에 속할거 같다는 생각이 듭니다.
    Adaptive Cursor Sharing, CF 이런게 결국 어느 한 범주에 속하는 것이 아닐까요?
    말 그대로 logical optimizer라 하면 너무 큰 범주구요..

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

      Adaptive Cursor Sharing, CF, Dynamic Sampling 등은 Physical 옵티마이져를 보완합니다. SQl Plan Baseline, SQL Profile, Stored OutLine 이놈들은 실행계획을 고정시키는 놈들입니다.

      옵티마이져의 기능이라고 볼 수도 있겠지만 옵티마이져의 실수를 막아주는 모듈이라고 보는 것이 더 정확합니다. 이 모든 것은 옵티마이져 서포터즈들 입니다. 즉 옵티마이져는 Logicl + Physical 이 존재하는 상태이지만 옵티마이져가 완벽하지 못하므로 이를 보완하는 것들 입니다. 하지만 아이러니 하게도 서포터즈들이 문제를 일으킬 수 도 있다는 겁니다.^^

      위의 놈들 중에서 11g의 SQl Plan Baseline이 가장 강력합니다. Adaptive Cursor Sharing, CF, Dynamic Sampling 등이 실행계획을 변경시킬 수 있지만 SQl Plan Baseline이라는 최후의 보루를 두어서 안심할 수 있을것입니다.

  4. ExtraOdinary 2010.11.03 15:52  댓글주소  수정/삭제  댓글쓰기

    아직 11g로 운영중인 사이트가 별로 없었는데, 동규님 블로그를 통해 11g의 좋은 기능들과 함정(?)들을 미리 이해할 수 있어 항상 감사합니다.

부제목: view에서 union, minus, intersect를 제거하라

많은 사람들이 union union all의 차이점에 대해 알고 있다. union Sort와 중복제거라는 기능으로 인해 UNION ALL에 비하여 성능이 떨어진다는 것이다. 옳은 말이다. 하지만 union대신에 union all을 써야 하는 또 다른 이유가 있다는 것을 아는 사람은 얼마나 될까? 이 사실을 알지 못하면 불필요한 엑세스가 추가될 수 있으므로 성능이 저하된다.


먼저 고객테이블을 이용하여 뷰를 하나 만들고 그것을 이용한 SQL문을 만들어 보자
.
환경 : Oracle 11.2.0.1

create or replace view vw_cust5 as
select *
  from customers
union           --> union을 사용함
select *
  from customers;


select a.cust_id,
       b.prod_id,
       b.time_id,
       b.channel_id,
       b.quantity_sold
  from vw_cust5 a,
       sales b
 where a.cust_id = b.cust_id
   and a.cust_id = 14865  ; 

 

뷰에서 사용하는 컬럼은 a.cust_id 하나 뿐이다. 따라서 고객 테이블에 PK인덱스를 사용한다면 customers 테이블로 엑세스 하지 않아도 된다. 하지만 아래의 실행계획을 본다면 문제를 발견할 수 있다.


-------------------------------------------------------------+----------------
| Id  | Operation                            | Name          | Rows  | Cost  |
-------------------------------------------------------------+----------------
| 0   | SELECT STATEMENT                     |               |       |    62 |
| 1   |  HASH JOIN                           |               |   260 |    62 |
| 2   |   VIEW                               | VW_CUST5      |     2 |     6 |
| 3   |    SORT UNIQUE                       |               |     2 |     6 |
| 4   |     UNION-ALL                        |               |       |       |
| 5   |      TABLE ACCESS BY INDEX ROWID     | CUSTOMERS     |     1 |     2 |
| 6   |       INDEX UNIQUE SCAN              | CUSTOMERS_PK  |     1 |     1 |
| 7   |      TABLE ACCESS BY INDEX ROWID     | CUSTOMERS     |     1 |     2 |
| 8   |       INDEX UNIQUE SCAN              | CUSTOMERS_PK  |     1 |     1 |
| 9   |   PARTITION RANGE ALL                |               |   130 |    56 |
| 10  |    TABLE ACCESS BY LOCAL INDEX ROWID | SALES         |   130 |    56 |
| 11  |     BITMAP CONVERSION TO ROWIDS      |               |       |       |
| 12  |      BITMAP INDEX SINGLE VALUE       | SALES_CUST_BIX|       |       |
-------------------------------------------------------------+----------------


PK
인덱스를 사용하였지만 customers 테이블로 불필요한 엑세스를 하였다. 이유가 무엇일까? 10053 trace를 보자.

Final query after transformations:******* UNPARSED QUERY IS *******

SELECT "A"."CUST_ID" "CUST_ID","B"."PROD_ID" "PROD_ID","B"."TIME_ID" "TIME_ID","B"."CHANNEL_ID" "CHANNEL_ID","B"."QUANTITY_SOLD" "QUANTITY_SOLD" FROM  ( (SELECT "CUSTOMERS"."CUST_ID" "CUST_ID","CUSTOMERS"."CUST_FIRST_NAME" "CUST_FIRST_NAME","CUSTOMERS"."CUST_LAST_NAME"
중간생략
"CUST_TOTAL_ID","CUSTOMERS"."CUST_SRC_ID" "CUST_SRC_ID","CUSTOMERS"."CUST_EFF_FROM" "CUST_EFF_FROM","CUSTOMERS"."CUST_EFF_TO" "CUST_EFF_TO" FROM "TLO"."CUSTOMERS" "CUSTOMERS" WHERE "CUSTOMERS"."CUST_ID"=14865 AND "CUSTOMERS"."CUST_ID"=14865)UNION (SELECT "CUSTOMERS"."CUST_ID" "CUST_ID","CUSTOMERS"."CUST_FIRST_NAME" "CUST_FIRST_NAME","CUSTOMERS"."CUST_LAST_NAME"
중간생략
"CUST_TOTAL_ID","CUSTOMERS"."CUST_SRC_ID" "CUST_SRC_ID","CUSTOMERS"."CUST_EFF_FROM" "CUST_EFF_FROM","CUSTOMERS"."CUST_EFF_TO" "CUST_EFF_TO" FROM "TLO"."CUSTOMERS" "CUSTOMERS" WHERE "CUSTOMERS"."CUST_ID"=14865 AND "CUSTOMERS"."CUST_ID"=14865)) "A","TLO"."SALES" "B" WHERE "A"."CUST_ID"="B"."CUST_ID" AND "B"."CUST_ID"=14865


위의 trace는 쿼리변환을 끝낸 상태의 SQL이다. 그런데 SQL을 자세히 보면 뷰 내부의 모든 컬럼을 select 하고 있다. 다시 말해 뷰 내부의 컬럼중에 cust_id만 존재하면 되는데, 나머지 컬럼이 삭제되지 않고 남아있다. 이것 때문에 불필요한 테이블 엑세스가 나타난 것이다.


union
대신에 union all을 사용해보자

테스트를 위하여 100건 짜리 고객테이블을 만들고 unique 인덱스를 만들어 보자.

create table customers_100 as 
select *
  from customers
 where rownum < 101;
 
create unique index PK_CUSTOMERS_100  on customers_100 (cust_id);


이제 union 대신에 union all로 뷰를 생성하여 테스트한다.

create or replace view vw_cust2 as
select *
 from customers
UNION ALL
select *
 from customers_100 ;
 
SELECT a.cust_id,
       b.prod_id,
       b.time_id,
       b.channel_id,
       b.quantity_sold
  FROM vw_cust2 a,
       sales b
 WHERE a.cust_id = b.cust_id
   AND a.cust_id = 14865  ;


---------------------------------------------------------------+----------------
| Id  | Operation                            | Name            | Rows  | Cost  |
---------------------------------------------------------------+----------------
| 0   | SELECT STATEMENT                     |                 |       |    57 |
| 1   |  HASH JOIN                           |                 |   260 |    57 |
| 2   |   VIEW                               | VW_CUST2        |     2 |     1 |
| 3   |    UNION-ALL                         |                 |       |       |
| 4   |     INDEX UNIQUE SCAN                | CUSTOMERS_PK    |     1 |     1 |
| 5   |     INDEX UNIQUE SCAN                | PK_CUSTOMERS_100|     1 |     0 |
| 6   |   PARTITION RANGE ALL                |                 |   130 |    56 |
| 7   |    TABLE ACCESS BY LOCAL INDEX ROWID | SALES           |   130 |    56 |
| 8   |     BITMAP CONVERSION TO ROWIDS      |                 |       |       |
| 9   |      BITMAP INDEX SINGLE VALUE       | SALES_CUST_BIX  |       |       |
---------------------------------------------------------------+----------------


SLP
란 무엇인가?

실행계획에서 보듯이 union all 을 사용하니 테이블 엑세스가 사라졌다. 이것은 SLP(Select List Pruning)라는 쿼리변환의 기능 때문에 가능한 것이다. SLP union all이 들어있는 뷰를 사용할 때 발생하며 뷰의 컬럼중에 사용하지 않는 것을 제거한다. 이제 10053 trace 내용 중에서 SLP에 대해 분석해보자. 특히 SLP 변환 전 SQL SLP 변환 후 SQL을 비교해보라. 아래의 10053 trace 내용이 복잡해 보이지만 구조는 단순하며 다음과 같다.

1) 쿼리변환 전 SQL
2) 쿼리변환(SLP)
3) 쿼리변환 후 SQL

 

SQL:******* UNPARSED QUERY IS *******
SELECT "A"."CUST_ID" "CUST_ID","B"."PROD_ID" "PROD_ID","B"."TIME_ID" "TIME_ID","B"."CHANNEL_ID" "CHANNEL_ID","B"."QUANTITY_SOLD" "QUANTITY_SOLD" FROM  ( (SELECT "CUSTOMERS"."CUST_ID" "CUST_ID","CUSTOMERS"."CUST_FIRST_NAME" "CUST_FIRST_NAME","CUSTOMERS"."CUST_LAST_NAME" "CUST_LAST_NAME","CUSTOMERS"."CUST_GENDER"
중간생략
"CUST_SRC_ID","CUSTOMERS"."CUST_EFF_FROM" "CUST_EFF_FROM","CUSTOMERS"."CUST_EFF_TO" "CUST_EFF_TO" FROM "TLO"."CUSTOMERS" "CUSTOMERS") UNION ALL  (SELECT "CUSTOMERS_100"."CUST_ID" "CUST_ID","CUSTOMERS_100"."CUST_FIRST_NAME""CUST_FIRST_NAME",
"CUSTOMERS_100"."CUST_LAST_NAME" "CUST_LAST_NAME","CUSTOMERS_100"."CUST_GENDER"
중간생략
"CUST_SRC_ID","CUSTOMERS_100"."CUST_EFF_FROM""CUST_EFF_FROM","CUSTOMERS_100"."CUST_EFF_TO"
"CUST_EFF_TO" FROM "TLO"."CUSTOMERS_100" "CUSTOMERS_100")) "A","TLO"."SALES" "B" WHERE "A"."CUST_ID"="B"."CUST_ID" AND "A"."CUST_ID"=14865
Query block SEL$1 (#0) unchanged
SLP: Removed select list item CUST_FIRST_NAME from query block SEL$3
SLP: Removed select list item CUST_FIRST_NAME from query block SEL$2
SLP: Removed select list item CUST_FIRST_NAME from query block SET$1
...
중간생략
SLP: Removed select list item CUST_EFF_TO from query block SEL$3
SLP: Removed select list item CUST_EFF_TO from query block SEL$2
SLP: Removed select list item CUST_EFF_TO from query block SET$1
JE:   Considering Join Elimination on query block SEL$1 (#0)
*************************
Join Elimination (JE)   
*************************
SQL:******* UNPARSED QUERY IS *******
SELECT "A"."CUST_ID" "CUST_ID","B"."PROD_ID" "PROD_ID","B"."TIME_ID" "TIME_ID","B"."CHANNEL_ID" "CHANNEL_ID","B"."QUANTITY_SOLD" "QUANTITY_SOLD" FROM  ( (SELECT "CUSTOMERS"."CUST_ID" "CUST_ID" FROM "TLO"."CUSTOMERS" "CUSTOMERS") UNION ALL  (SELECT "CUSTOMERS_100"."CUST_ID" "CUST_ID" FROM "TLO"."CUSTOMERS_100" "CUSTOMERS_100")) "A","TLO"."SALES" "B" WHERE "A"."CUST_ID"="B"."CUST_ID" AND "A"."CUST_ID"=14865


필요한 컬럼만 살아남다
SLP(Select List Pruning)
가 발생하여 사용하지 않는 모든 컬럼을 삭제하였다. 쿼리변환 후의 SQL을 보면 뷰 내부의 컬럼은 모두 제거되고 cust_id만 남아있다. SLP 기능에 의해 테이블 엑세스가 없어진 것이다.
뷰 내부에는 union뿐만 아니라 minus,intersect 등의 집합 연산의 사용을 자제해야 한다. SLP가 발생하지 않기 때문이다.


그렇다면 뷰내부에 union all과 minus를 동시에 사용하면 어떻게 될까? 이제 union all
minus를 동시에 사용한 뷰를 생성하고 SLP가 발생하는지 테스트 해보자.

CREATE OR REPLACE VIEW vw_cust33 AS
SELECT *
  FROM customers
UNION ALL
SELECT *
  FROM customers
MINUS
SELECT *
  FROM customers_100;


이제 위의 뷰를 사용하여 select문을 실행해보자.

SELECT a.cust_id,
       b.prod_id,
       b.time_id,
       b.channel_id,
       b.quantity_sold
  FROM vw_cust33 a, sales b
 WHERE a.cust_id = b.cust_id
   AND a.cust_id = 14865 ;


---------------------------------------------------------------+----------------
| Id  | Operation                            | Name            | Rows  | Cost  |
---------------------------------------------------------------+----------------
| 0   | SELECT STATEMENT                     |                 |       |    63 |
| 1   |  HASH JOIN                           |                 |   260 |    63 |
| 2   |   VIEW                               | VW_CUST33       |     2 |     7 |
| 3   |    MINUS                             |                 |       |       |
| 4   |     SORT UNIQUE                      |                 |     2 |       |
| 5   |      VIEW                            |                 |     2 |     4 |
| 6   |       UNION-ALL                      |                 |       |       |
| 7   |        TABLE ACCESS BY INDEX ROWID   | CUSTOMERS       |     1 |     2 |
| 8   |         INDEX UNIQUE SCAN            | CUSTOMERS_PK    |     1 |     1 |
| 9   |        TABLE ACCESS BY INDEX ROWID   | CUSTOMERS       |     1 |     2 |
| 10  |         INDEX UNIQUE SCAN            | CUSTOMERS_PK    |     1 |     1 |
| 11  |     TABLE ACCESS BY INDEX ROWID      | CUSTOMERS_100   |     1 |     1 |
| 12  |      INDEX UNIQUE SCAN               | PK_CUSTOMERS_100|     1 |     0 |
| 13  |   PARTITION RANGE ALL                |                 |   130 |    56 |
| 14  |    TABLE ACCESS BY LOCAL INDEX ROWID | SALES           |   130 |    56 |
| 15  |     BITMAP CONVERSION TO ROWIDS      |                 |       |       |
| 16  |      BITMAP INDEX SINGLE VALUE       | SALES_CUST_BIX  |       |       |
---------------------------------------------------------------+----------------


minus
가 존재하여 SLP가 발생되지 않았다. Minus 때문에 불필요한 테이블 엑세스가 세 번이나 발생되었다. 어떻게 하면 이 문제를 해결할 수 있을까? 물론 부정형 서브쿼리(not exists)를 사용하면 minus 를 대신할 수 있으므로 불필요한 테이블 엑세스는 없어질 것이다.


minus
를 사용하면서 SLP가 가능한가?

문제는 minus를 사용하면서 불필요한 엑세스를 방지할 수 있는 방법이 있냐는 것이다. 가장 쉬운 방법은 뷰에서 minus 부분을 제거하는 것이다. 즉 아래의 SQL처럼 minus 대신에 테이블을 직접 사용하면 된다.

create or replace view vw_cust as
select * from customers
UNION ALL
select * from customers ;
 
SELECT a.cust_id,
       b.prod_id,
       b.time_id,
       b.channel_id,
       b.quantity_sold
  FROM VW_CUST a,        --
minus가 빠진 뷰를 사용함

       sales b
 WHERE a.cust_id = b.cust_id
   AND a.cust_id = 14865
MINUS  
SELECT a.cust_id,
       b.prod_id,
       b.time_id,
       b.channel_id,
       b.quantity_sold
  FROM CUSTOMERS_100 a, --
테이블을 직접 사용함
       sales b
 WHERE a.cust_id = b.cust_id
   AND a.cust_id = 14865 ;

-----------------------------------------------------------------+----------------
| Id  | Operation                              | Name            | Rows  | Cost  |
-----------------------------------------------------------------+----------------
| 0   | SELECT STATEMENT                       |                 |       |   115 |
| 1   |  MINUS                                 |                 |       |       |
| 2   |   SORT UNIQUE                          |                 |   260 |    59 |
| 3   |    HASH JOIN                           |                 |   260 |    58 |
| 4   |     VIEW                               | VW_CUST         |     2 |     2 |
| 5   |      UNION-ALL                         |                 |       |       |
| 6   |       INDEX UNIQUE SCAN                | CUSTOMERS_PK    |     1 |     1 |
| 7   |       INDEX UNIQUE SCAN                | CUSTOMERS_PK    |     1 |     1 |
| 8   |     PARTITION RANGE ALL                |                 |   130 |    56 |
| 9   |      TABLE ACCESS BY LOCAL INDEX ROWID | SALES           |   130 |    56 |
| 10  |       BITMAP CONVERSION TO ROWIDS      |                 |       |       |
| 11  |        BITMAP INDEX SINGLE VALUE       | SALES_CUST_BIX  |       |       |
| 12  |   SORT UNIQUE                          |                 |   130 |    56 |
| 13  |    NESTED LOOPS                        |                 |   130 |    55 |
| 14  |     INDEX UNIQUE SCAN                  | PK_CUSTOMERS_100|     1 |     0 |
| 15  |     PARTITION RANGE ALL                |                 |   130 |    55 |
| 16  |      TABLE ACCESS BY LOCAL INDEX ROWID | SALES           |   130 |    55 |
| 17  |       BITMAP CONVERSION TO ROWIDS      |                 |       |       |
| 18  |        BITMAP INDEX SINGLE VALUE       | SALES_CUST_BIX  |       |       |
-----------------------------------------------------------------+----------------
예상대로 뷰에서 minus를 삭제하니 성공적으로 SLP가 발생되었고 테이블 엑세스가 모두 사라졌다.

결론
 
이번 시간에는 union과 union all의 또 다른 차이점에 대해 알아보았다. union all을 사용하면 SLP가 발생되어 뷰에서 사용되지 않는 컬럼을 제거한다. 이때 인덱스만으로 scan을 끝낼수 있는 경우 불필요한 테이블스캔이 방지된다. 따라서 뷰 내부에서는 union, minus, intersect를 빼는 것이 유리하다.
뷰 내부의 minus는 not exists로 바꾸면 된다. 대부분의 경우 뷰 내부에 Intersect도 필요치 않다. Intersect란 교집합이며 이것은 조인으로 해결할 수 있다. 왜냐하면 조인이란 두 집합에서 조인된 컬럼을 기준으로 값이 같은 것만 추출하는 기능이기 때문이다. 그렇지 않은가?

Posted by extremedb

댓글을 달아 주세요

  1. 2010.10.18 11:28  댓글주소  수정/삭제  댓글쓰기

    좋은 내용 잘 보고 갑니다.

    잘 지내시죠?
    언제 함 뵈야 하는데 시간적 여유, 심리적 여유가 잘 안나네요..

    조만간 함 연락드릴께요,....^^

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

    Union, Union all 의 차이에 이런부분도 있다니...
    넓은 지식도 중요하지만..
    깊이있는 지식의 중요성을 한번더 일깨워주시네요.
    내용감사합니다..

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

      승필님 반갑습니다.
      말씀하신대로 상식이 중요하다는 의미에서 넓고 얇게 파는것도 중요하지만, 전공분야의 하나를 깊게 파는 것도 중요한것 같습니다.

  3. Favicon of https://ukja.tistory.com BlogIcon 욱짜 2010.10.18 15:15 신고  댓글주소  수정/삭제  댓글쓰기

    10053 트레이스의 활용 예제로 사용하기 좋군요!

INDEX UNIQUE SCAN비밀
당신은 INDEX UNIQUE SCAN쉬운 Operation쯤으로 여길 것이다. 하지만 여기에는 숨겨진 기능이 있다. 대표적인 경우가 Unique 인덱스를 사용하여 INDEX UNIQUE SCAN Operation나오면 Distinct제거하는 기능이다. 기능의 이름이 없으므로 DEUI*(Distinct Elimination using Unique Index)부르기로 하자. 이제 아래의 SQL보자.

SELECT DISTINCT d.department_id, l.city, l.country_id

  FROM department d, location l

 WHERE d.location_id = l.location_id

   AND d.department_id = 10 ;

 

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

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

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

| 0   | SELECT STATEMENT              |           |       |       |     3 |           |

| 1   |  NESTED LOOPS                 |           |     1 |    22 |     2 |  00:00:01 |

| 2   |   TABLE ACCESS BY INDEX ROWID | DEPARTMENT|     1 |     7 |     1 |  00:00:01 |

| 3   |    INDEX UNIQUE SCAN          | DEPT_ID_PK|     1 |       |     0 |           |

| 4   |   TABLE ACCESS BY INDEX ROWID | LOCATION  |    23 |   345 |     1 |  00:00:01 |

| 5   |    INDEX UNIQUE SCAN          | LOC_ID_PK |     1 |       |     0 |           |

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

 

Predicate Information:

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

3 - access("D"."DEPARTMENT_ID"=10)

5 - access("D"."LOCATION_ID"="L"."LOCATION_ID")

 

SQL 변경되었다
Distinct의한 Sort Unique 혹은 Hash Unique사라졌다. 이유는 테이블 departmentlocation에서 모두 INDEX UNIQUE SCAN사용하였기 때문이다. 하지만 반대로 Unique 인덱스를 사용하지 않는다면 DEUI절대 수행되지 않는다. 아래의 SQL위의 SQL에서 힌트만 추가한 것이다.
 

SELECT /*+ FULL(d) */ DISTINCT d.department_id, l.city, l.country_id

  FROM department d, location l

 WHERE d.location_id = l.location_id

   AND d.department_id = 10 ;

 

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

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

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

| 0   | SELECT STATEMENT               |           |       |       |     5 |           |

| 1   |  HASH UNIQUE                   |           |     1 |    22 |     5 |  00:00:01 |

| 2   |   NESTED LOOPS                 |           |       |       |       |           |

| 3   |    NESTED LOOPS                |           |     1 |    22 |     4 |  00:00:01 |

| 4   |     TABLE ACCESS FULL          | DEPARTMENT|     1 |     7 |     3 |  00:00:01 |

| 5   |     INDEX UNIQUE SCAN          | LOC_ID_PK |     1 |       |     0 |           |

| 6   |    TABLE ACCESS BY INDEX ROWID | LOCATION  |    23 |   345 |     1 |  00:00:01 |

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

 

Predicate Information:

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

4 - filter("D"."DEPARTMENT_ID"=10)

5 - access("D"."LOCATION_ID"="L"."LOCATION_ID")

 

 

순간의 실수로
Department 테이블을 Full Scan 하자 Plan 상에 HASH UNIQUE나타났다. 다시 말해서 옵티마이져의 실수로 다른 종류의 인덱스를 사용하거나 Full Scan된다면 Query Transformation발생되지 않는다.
 

결론

우리가 무심코 사용하는 Unique Index INDEX UNIQUE SCAN Operation DEUI라는 기능을 내장하고 있다. 당연한 기능이라고 여기겠지만 이것을 모르면 많은 것을 놓친다. 서브쿼리가 Unnesting되어 Driving 집합이 되면 메인쿼리의 집합을 보존하기 위하여 Default Distinct가 추가된다. 이제 INDEX UNIQUE SCAN을 사용하는 서브쿼리가 Unnesting되어 Driving 집합이 될 때 Distinct가 사라진 비밀을 이야기 할 수 있겠는가? Unique 한 값을 가지는 컬럼이나 컬럼조합을 Normal 인덱스가 아닌 Unique 인덱스로 만들어야 할 이유를 알겠는가? 단순한 Operation 하나라도 무시할 수 없는 이유가 된다.

PS
다음시간에는 DEUI의 사촌격인 DE에 대하여 논의하자.
이번 내용도 집필중인 책의 일부분이다. 이제 어떤 것을 주제로 책인지 감이 오지 않는가?

Posted by extremedb

댓글을 달아 주세요

  1. Eddy 2010.01.20 18:06  댓글주소  수정/삭제  댓글쓰기

    pk, uk 컬럼에 unique index를 미리 만들면 제약과 관련된 다음 기능을 사용할 수 없게 됩니다.

    - enable novalidate
    - deferrable 제약

    그래서 normal index를 생성하게 하는데, 그 경우 DEUI 기능을 놓칠 수 있겠군요.

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

    알고 있는내용이라고 생각했는데 서버쿼리와 연결해서 생각하니 참...

    아직 기본도 부족하고, 운용도 부족한가 보네요..
    내용 잘봤습니다..

  3. 서상서 2010.02.10 17:41  댓글주소  수정/삭제  댓글쓰기

    11 R1에서는 Sort Unique같이 실행계획이 나타나지는 않지만 실행계획이 언급하신것과 같이는 풀리지 않는것 같읍니다.혹시 이전에 언급하신 히든 파리미터때문인가요?

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

      DE를 이야시하시는 건지 DEUI를 이야기 하시는 건지 알수가 없네요.
      DE든 DEUI든 Distinct(Sort Unique 혹은 Hash Unique)제거하는 기능입니다. 그외에 다른 테이블에 엑세스 하지 않는 기능은 JE(Join Elimination)라는 기능이 따로 존재합니다. 또한 인덱스를 타고 안타고는 JE 나 DE, DEUI 와는 상관없이 여러가지 통계정보나 인덱스의 여부에 따라 Cost Estimator의 Return 값에 따라 판단되는것 입니다.

“SQL 작성시 같은 테이블을 반복해서 사용하지 마라

위와 같은 말을 많이 들어보았을 것이다. 이런 말들은 개발자에게 마치 격언, 명언처럼 취급된다. 오늘도 개발자, DBA, 컨설턴트등 모든 사람들이 위의 명언에 너무도 충실하여 반복되는 테이블을 제거하기 위해 SQL을 재작성 하고 있다. 하지만 이런 말들이 명언이나 격언이 될 수 있을까? 이제는 격언이나 훈수, 명언이라고 생각하는 말도 최소한 상황에 따라 가려서 해야 한다. 왜 그럴까? 아래의 SQL을 보자. 

 

SELECT /*+ use_hash(c s)  */

       s.prod_id, s.cust_id, s.quantity_sold,

       s.amount_sold, c.channel_desc

  FROM sales s, channels c

 WHERE c.channel_id = s.channel_id

   AND c.channel_id = 3

UNION ALL

SELECT /*+ use_hash(c s) */

       s.prod_id, s.cust_id, s.quantity_sold,

       s.amount_sold, c.channel_desc

  FROM sales s, channels c

 WHERE c.channel_id = s.channel_id

   AND c.channel_id = 9 ;

 

위의 SQL은 대용량 테이블인 판매 테이블(sales)을 비효율적으로 2 Scan할 것으로 예상된다. 하지만 아래의 Plan을 보라. 과연 그렇게 수행되는가?

-----------------------------------------------------------+-----------------------
| Id | Operation                       | Name              | Rows  | Bytes | Cost |
-----------------------------------------------------------+-----------------------
| 0  | SELECT STATEMENT                |                   |       |       |   495|
| 1  |  HASH JOIN                      |                   |  449K |   20M |   495|
| 2  |   VIEW                          | VW_JF_SET$0A277F6D|     2 |    50 |     2|
| 3  |    UNION-ALL                    |                   |       |       |      |
| 4  |     TABLE ACCESS BY INDEX ROWID | CHANNELS          |     1 |    13 |     1|
| 5  |      INDEX UNIQUE SCAN          | CHANNELS_PK       |     1 |       |     0|
| 6  |     TABLE ACCESS BY INDEX ROWID | CHANNELS          |     1 |    13 |     1|
| 7  |      INDEX UNIQUE SCAN          | CHANNELS_PK       |     1 |       |     0|
| 8  |   PARTITION RANGE ALL           |                   |  897K |   18M |   489|
| 9  |    TABLE ACCESS FULL            | SALES             |  897K |   18M |   489|
-----------------------------------------------------------+-----------------------

Predicate Information:
----------------------
1 - access("ITEM_1"="S"."CHANNEL_ID")
5 - access("C"."CHANNEL_ID"=3)
7 - access("C"."CHANNEL_ID"=9)

 

이제는 Transformer 가 튜너이다.

환상적이지 않은가? channels 테이블을 정확히 2건만 Scan 하였고 sales 테이블은 1번만 Full Scan 하였다. 오라클 Transformer SQL을 아래처럼 재작성 한 것이다.

SELECT s.prod_id prod_id, s.cust_id cust_id, s.quantity_sold,

       s.amount_sold, vw_jf_set$0a277f6d.item_2 channel_desc

  FROM (SELECT c.channel_id AS item_1, c.channel_desc AS item_2

          FROM channels c

         WHERE c.channel_id = 3

        UNION ALL

        SELECT c.channel_id AS item_1, c.channel_desc AS item_2

          FROM channels c

         WHERE c.channel_id = 9) vw_jf_set$0a277f6d,

       sales s

 WHERE vw_jf_set$0a277f6d.item_1 = s.channel_id ;

 

위와 같은 상황에서 SQL을 재작성 하는 기능을 JF(Join Factorization)라고 부른다. VW_JF_SET으로 시작되는 인라인뷰 명(Plan 상의 파란색 부분) JF가 수행되었음을 나타내는 것이다. 이것은 Oracle11g R2 에서 새로 추가된 대표적인 CBQT(Cost Based Query Transformation)기능 이다.

 

항상 수행되지는 않는다

CBQT Cost SQL튜닝을 수행할 것인지 아닌지를 판단한다. 그런데 복잡한 SQL의 경우에는 Cost Estimator가 판단을 잘못하여 JF를 수행하는 것이 비용이 더 비싸다고 판단할 수 있다. 이런 경우에는 Transformer SQL을 튜닝(재작성) 하지 않는다. 이럴 때는 아래와 같이 힌트를 사용해야 한다.

SELECT /*+ use_hash(c s) FACTORIZE_JOIN(@SET$1(S@SEL$1 S@SEL$2)) */

       s.prod_id, s.cust_id, s.quantity_sold,

       s.amount_sold, c.channel_desc

  FROM sales s, channels c

 WHERE c.channel_id = s.channel_id

   AND c.channel_id = 3

UNION ALL

SELECT /*+ use_hash(c s) */

       s.prod_id, s.cust_id, s.quantity_sold,

       s.amount_sold, c.channel_desc

  FROM sales s, channels c

 WHERE c.channel_id = s.channel_id

   AND c.channel_id = 9 ;

 

쿼리블럭명 SET$1은 전체에 해당하는 쿼리블럭이고 SET$1 의 내부에 또 다른 쿼리블럭인 SEL$1(Union All로 분리된 것 중의 윗부분), SEL$2(Union All로 분리된 것 중의 아랫부분) 이 존재한다. 만약 Union All 이 하나 더 있다면 쿼리블럭명은 SEL$3가 될 것이다.

 

JF를 자세히 분석 하려면 10053 Event Trace를 이용하라

먼저 10053 의 용어 설명부분에 JF 가 아래처럼 추가 되었다.

The following abbreviations are used by optimizer trace.

CBQT - cost-based query transformation

JPPD - join predicate push-down

중간생략

JF - join factorization


아래는 JF 부분에 해당하는 10053 Trace 정보이다.

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

Cost-Based Join Factorization     

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

Join-Factorization on query block SET$1 (#1)

JF: Using search type: exhaustive

JF: Generate basic transformation units

이후생략

 

결론

이제 격언이나 명언이라고 생각되는 말들도 상황을 가려서 해야 한다는 것을 알겠는가?. 그렇지 않으면 똑똑한 개발자에게 오히려 다음과 같은 말을 들을 것이다. “지금 말씀 하신 것은 예전 이야기 입니다. 요즘은 트랜스포머가 알아서 해줍니다.”  적어도 튜닝의 세계에서는 그렇다.

 

새로운 패러다임

JFTransformer가 수행하는 SQL튜닝의 하나일 뿐이다. Oracle11g R2 기준으로 SQL 튜닝(Query Transformation)의 종류는 필자의 짧은 지식으로도 70개 이상일 것으로 판단 된다. Oracle이 발전해 가면서 SQL튜닝은 사람이 관여하는 것에서 오라클이 자동으로 SQL을 변경해주는 것으로 많은 부분이 바뀌었고 앞으로 이런 추세는 점점 강화될 것이다. Query Transformation 은 단순한 Optimizer의 기능이 아니라 SQL 튜닝의 새로운 패러다임인 것이다. 이제는 직접 튜닝 하는 것에서 벗어나 Transformer가 실수하는 경우 새로운 길을 열어주는 것이 튜너가 가야할 길이 아닌가?


Posted by extremedb

댓글을 달아 주세요

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

    잘 봤습니다..

    • Favicon of https://scidb.tistory.com BlogIcon extremedb 2009.10.15 21:27 신고  댓글주소  수정/삭제

      한번도 안거르고 열심히 하시네요.
      반드시 좋은 성과 있을것 입니다.
      좋은밤 되세요.

    • feelie 2009.10.16 09:04  댓글주소  수정/삭제

      항상 좋은 내용 잘봅니다.
      열심히 할려고는하는데 볼때마다 새롭고 지난내용들도
      봤는데 다시볼때마다 생소한 느낌이 많이 드네요.
      아직 내공이 많이 부족한 모양이지요?
      아무튼 열심히 할렴니다..

  2. Favicon of http://blog.naver.com/xsoft BlogIcon 강정식 2009.10.16 09:15  댓글주소  수정/삭제  댓글쓰기

    11g에서 또한번 CBQT가 진일보되었나 보군여. ^^ 좋은 내용 감사합니다.

    "이제는 직접 튜닝 하는 것에서 벗어나 Transformer가 실수하는 경우 새로운 길을 열어주는 것이 튜너가 가야할 길이 아닌가?
    " 이 내용에 대해 저 또한 100% 공감합니다. 모든 SQL들에 대해 총괄감독(?) 아닌 조력자로 가야하지 않을까라고 말이죠.

  3. Favicon of https://ukja.tistory.com BlogIcon 욱짜 2009.10.16 09:21 신고  댓글주소  수정/삭제  댓글쓰기

    11gR2에 관련된 내용이 본격적으로 다루어지네요. 내년에 R2가 얼마나 채택될지 기대됩니다.(경기가 좀 좋아져야겠지만... ^^)

    • Favicon of https://scidb.tistory.com BlogIcon extremedb 2009.10.16 09:54 신고  댓글주소  수정/삭제

      11g R2 내용을 많이 다루려고 하고 있습니다.
      내년에 본격적으로 프로젝트가 시작되면 11g 관련 성능문제가 많이 이슈화 되겠네요. 기대됩니다.

  4. 혈기린 2009.10.16 09:28  댓글주소  수정/삭제  댓글쓰기

    11g 에서 한층더 발전했네요 정말 이러다 나중에는 오라클이 다 알아서 해주는건 아닐지 ㅎㅎ
    좋은내용 감사 드립니다 ^^

    • Favicon of https://scidb.tistory.com BlogIcon extremedb 2009.10.16 09:55 신고  댓글주소  수정/삭제

      말씀 하신것처럼 앞으로는 많은 부분을 Transformer 가 튜너의 일을 대신하게 될거 같습니다. 진정한 튜너만이 살아 남겠죠.

  5. 김봉호 2009.10.16 17:37  댓글주소  수정/삭제  댓글쓰기

    이해는 잘 안가지만..
    가장궁금한건 트랜스포머가 무엇을 지칭하는건지요 -0-?

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

      오라클에서 트랜스포머는 성능향상을 위해서 SQL 을 튜닝(재작성) 하는 역활을 담당합니다. 과거에는 튜너가 이런 역활을 했습니다만 이제는 트랜스포머가 많은 부분을 커버합니다.
      아래의 글을 참조하세요.
      http://scidb.tistory.com/entry/다단계-쿼리변환-Muti-Phase-Query-Transformation