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