Science of Database

Query Transformer 의 냉대
  튜닝을 하는 많은 사람들이 PM 의 개념을 모른다는 결과가 나왔다. 정기모임 술자리에서 즉석으로 설문을 하였는데 결과는 충격적이었다. 참석자 10명은 DBA, 튜너, DB 컨설턴트 등등 DB 전문가들의 모임이라고 할수 있는데 단 한명도 아는사람이 없었다.

 필자가 충격적이라고 한 이유는 그모임의 많은 사람들이 왠만한 SQL 의 COST 를 계산할수 있는 내공을 가진 사람들이 었기 때문이다. 다행히 JPPD 나 VIEW MERGING, Unnesting 과 같이 튜닝 책에 소개 되는 간단한 변환들은 알고 있었다. 하지만 Query Transformer 가 푸배접을 받고 있다는 생각은 지울수가 없었다.
 
  Query Transformer 는 그 중요성이 옵티마이져의 50% 를 차지한다. 왜그럴까? 옵티마이져의 3대 Components 는 Query Transformer, Cost Estimator,  Plan Generator  이지만  이중에서 우리가 연구할수 있는 것은  Query Transformer 와  Cost Estimator 이며  Plan Generator 의 비밀은 오라클사의 DBMS 설계자/개발자만이 알수 있는 영역이기 때문이다. 또한 SQL 이 Transformer 에 의하여 변형되고 재작성 되기 때문에 성능에 직접적인 영향을 끼친다. 대부분의 경우 Query Transformation 이 발생하면 성능에 긍정적인 영향을 끼치지만 예외적으로 부정정인 영향을 줄수가 있으므로 가능한 Query Transformer에 대하여 상세히 알아야 한다.

어찌되었건 모임에서 PM 관련 내용을 블로그에 올리겠다는 약속을 하였다.

PM ( Predicate Move Around ) 이란?
 
인라인뷰가 여러 개 있고 각각의 where 절에 공통적인 조건들이 있다고 가정하자.
이럴경우에 모든 인라인뷰의 where 절에 똑 같은 조건들을 반복해서 사용해야 할까?
물론 그렇게 해야 하는 경우가 있지만 아래의 경우에는 그렇지 않음을 알수 있다..

SELECT /*+ qb_name (v_outer) */

       v1.*

  FROM (SELECT /*+ qb_name (IV1) no_merge */

               e1.*,  d1.location_id

          FROM employee e1, department d1

         WHERE e1.department_id = d1.department_id

            AND d1.department_id = 30

        ) v1,

       (SELECT   /*+ qb_name (IV2) no_merge */

                  d2.department_id, AVG (salary) avg_sal_dept

            FROM employee e2, department d2, loc l2

           WHERE e2.department_id = d2.department_id

             AND l2.location_id = d2.location_id

        GROUP BY d2.department_id

       ) v2

 WHERE v1.department_id = v2.department_id

    AND v1.salary > v2.avg_sal_dept  ;

 

 

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

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

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

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

| 1  |  HASH JOIN                      |                  |     1 |   176 |     5 |  00:00:01|

| 2  |   VIEW                          |                  |     1 |    28 |     2 |  00:00:01|

| 3  |    HASH GROUP BY                |                  |     1 |    21 |     2 |  00:00:01|

| 4  |     NESTED LOOPS                |                  |       |       |       |          |

| 5  |      NESTED LOOPS               |                  |     6 |   126 |     2 |  00:00:01|

| 6  |       NESTED LOOPS              |                  |     1 |    14 |     1 |  00:00:01|

| 7  |        INDEX RANGE SCAN         | DEPT_IX_01       |     1 |    11 |     1 |  00:00:01|

| 8  |        INDEX UNIQUE SCAN        | LOC_ID_PK        |    23 |    69 |     0 |          |

| 9  |       INDEX RANGE SCAN          | EMP_DEPARTMENT_IX|     6 |       |     0 |          |

| 10 |      TABLE ACCESS BY INDEX ROWID| EMPLOYEES        |     6 |    42 |     1 |  00:00:01|

| 11 |   VIEW                          |                  |     6 |   888 |     2 |  00:00:01|

| 12 |    NESTED LOOPS                 |                  |       |       |       |          |

| 13 |     NESTED LOOPS                |                  |     6 |   474 |     2 |  00:00:01|

| 14 |      INDEX RANGE SCAN           | DEPT_IX_01       |     1 |    11 |     1 |  00:00:01|

| 15 |      INDEX RANGE SCAN           | EMP_DEPARTMENT_IX|     6 |       |     0 |          |

| 16 |     TABLE ACCESS BY INDEX ROWID | EMPLOYEES        |     6 |   408 |     1 |  00:00:01|

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

Predicate Information:

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

1 - access("V1"."DEPARTMENT_ID"="V2_DEPT"."DEPARTMENT_ID")

1 - filter("V1"."SALARY">"V2_DEPT"."AVG_SAL_DEPT")

7 - access("D2"."DEPARTMENT_ID"=30)

8 - access("L2"."LOCATION_ID"="D2"."LOCATION_ID")

9 - access("E2"."DEPARTMENT_ID"=30)

14 - access("D1"."DEPARTMENT_ID"=30)

15 - access("E1"."DEPARTMENT_ID"=30)

다른뷰에 조건이 추가되었다.

Predicate Information을 보면 두번째 뷰(v2 ) 에 "D2"."DEPARTMENT_ID"=30 조건과 "E2"."DEPARTMENT_ID"=30 조건이 파고들어 간 것을 알수 있다. 이 현상 때문에 D1 과 E1 에서 인덱스를 사용하였는데 결과는 성능면에서 아주 성공적이다. 그렇다면  오라클은 어떤 과정을 거쳐서 이작업을 진행하였을까?

10053 trace 정보를 보면 PM의 진행과정이 매우 상세하게 나와 있다.

PM: Considering predicate move-around in query block V_OUTER (#1)

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

Predicate Move-Around (PM)

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

PM:   Passed validity checks.

PM:   Pulled up predicate "V1"."DEPARTMENT_ID"=30

 from query block IV1 (#2) to query block V_OUTER (#1)

PM:   Pushed down predicate "D2"."DEPARTMENT_ID"=30

 from query block V_OUTER (#1) to query block IV2 (#3)

 

PM 은 순서가 중여하다.
10053 trace 내용을 분석하여 수행과정을 살펴보자.

1.       먼저 PM 이 수행될수 있는지 검사한다.

2.       V1 에서 WHERE 조건 d1.department_id = 30 를 바깥쪽 메인쿼리로 이동시킨다.

      이것을 Predicate pull up 이라고 한다.

3.       메인쿼리로 옮겨진 where 조건을 v2 에 복사한다.

이것을 Predicate Push down 이라고 한다.

4.  최종 결과에서 중복된 조건절이 존재하면 삭제한다..

 

V1, V2 가 메인쿼리(V_OUTER)에서 department_id 기준으로 조인되고 있기 때문에 조건절을 V_OUTER 로 빼낸 다음에 V2 에 조건절을 밀어 넣고 있다. 이것은 JPPD 기능과 유사한 면이 있지만 Predicate pull up 이 반드시 먼저 일어나야 한다는 점과 Hash 조인등에서도 PM 이 발생한다는 점에서 엄연히 다르다. 오라클은 여러분이 모르는 사이에 조건절을 이리 저리 옮겨 다니면서 SQL 의 최적화를 시도하고 있다.

 

PM 아무때나 발생하나?
그럼 PM 이 어떤 조건일 경우 발생하는지 짐작할수 있겠는가?

다음과 같은 조건일 경우 PM 이 발생된다.

1.       뷰(혹은 인라인뷰) 가 2개 혹은 그이상이 되어야 한다.(예제에서 V1, V2 가 있음)

2.       특정 인라인뷰내의 조건이 존재하고 뷰의 바깥쪽에서 조건을 사용한 컬럼으로 조인이 발생할 경우에 발생된다. 예제에서 는 d1.department_id = 30 으로  V1 내부에 조건이 존재하고 V_OUTER 에서 department_id 로 V2 와 조인을 하고 있다.

3.       VIEW MERGE 가 발생하지 않아야 한다. Merging 이 발생되면 PM 대신에 Transitive Predicates 가 발생된다. 

       4.   파라미터 _PRED_MOVE_AROUND 가 true 로 지정이 되어 있어야 한다.

결론 :
  제목에서 보듯이 PM 의 개념은 매우 간단하다. Where 조건을 다른뷰에 이동시키는 기능이며 Heuristic Transformatin 의 대표적인 예제이다.
  오늘 올린 글은 현재 집필중인 책의 내용인데 일부를 먼저 공개하기로 결정 하였다.

편집후기 :  JPPD 와 PM 이 헷갈린다는 보고가 들어왔다. 둘다 WHERE 조건이 PUSH 되는것이지만 가장 결정적이 차이점은 JPPD 는 Predicate pull up 기능이 없다는 것이다. 아주 명확하게 구분할수 있다.

 

부제목: 다단계 쿼리변환 (Muti-Phase Query Transformation)

  SF 영화 트랜스포머를 보면 자동차가 로봇으로 변환하는 과정이 있다. 자동차와 로봇간의 변환과정은 아주 현란하다 못해 활홍하여 시청자자로 하여금 넋을 놓고 빠져들게 한다. 컴퓨터그래픽(CG) 기술의 발전 덕분이다.

변환과정이 있어야 지구를 지킬수 있어
  만약 이 영화에서 자동차가 로봇으로 변환을 못한다고 상상해보자. 악한 로봇이 쳐들어와도 싸울수가 없고 격렬한 전투장면도 사라진다. 이래서는 영화가 재미없을 뿐더러 지구를 지킬수도 없다. 그럼 오라클에서 Query Transformer 가 없어진다면 어떻게 될까? 마찬가지로 Query 의 상당부분을 튜닝할수 없게 되어 전체 시스템이 느려지게된다. Query Transformer 의 목적은 성능향상에 있다.

오라클에도 트랜스포머가 있다.
  오라클 Optimizer 에서 Query Transformer 는 3대 Components 로서 아주 중요한 위치에 있다.
먼저 Query Transformer 를 이해하기 위해서 Optimizer 구조를 살펴볼 필요가 있다.
 
먼저 Query Parser 가 SQL 을 검사하여 넘겨주면 Transformer 가 SQL 을 변신시켜서 Estimator 에 넘겨준다.
이때 Estimator는 통계정보등을 참조하여 가장 낮은 cost 를 갖는 SQL 을 찾아내어 Plan Generator 에 넘겨주고 실행계획을 완성하게 된다.  사실 위의 그림은 오라클 Performance Tuning Guide 에 있는 그림 이지만 잘못된 것이 있다. Query Transformer 가 Estimator 에게 주는 SQL 은 하나이상이 될수 있으므로  Estimator 와 Plan Generator 의 관계처럼 반복적인 Loop 가 있어야 한다.

변환과정도 로봇에 따라 다양하다.
  트랜스포머에서 주인공 로봇의 변환과정은 아주 복잡하다. 하지만 소형 악당 로봇이 카세트 레코더로 변환하는 과정을 유심히 보았는가? 이 과정은 매우 간단하다. 오라클의 쿼리변환(Query Transformation) 과정도 간단한 것에서 부터 아주 복잡한 과정을 거치는 것 까지 다양하다.

구슬이 서말이라도 꿰어야 보배
  오늘은 조금 어려운 다단계 쿼리변환-(Muti-Phase-Query Transformation)에 대하여 알아보려 한다.
참고로 아래의 글이 이해하기 힘든 독자는 필자의 이전글 Using Sub query Method (Sub query Flattening ) 과 Using Sub query Method( Filter / Access sub Query ) 를 먼저 읽어보기 바란다.
그럼 각 단계별로 변환과정을 보자. 

1 단계 : 원본 쿼리
            자신이 속한 부서의 평균급여 보다 돈을 많이 받는 사원을 추출하는 예제이다.

--------------------------------------------------------------------------------------------
| Id  | Operation                     | Name      | Starts | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------
|*  1 |  FILTER                       |           |      1 |      5 |00:00:00.01 |      16 |
|   2 |   TABLE ACCESS FULL           | EMP       |      1 |     14 |00:00:00.01 |       8 |
|   3 |   SORT AGGREGATE              |           |      5 |      5 |00:00:00.01 |       8 |
|   4 |    TABLE ACCESS BY INDEX ROWID| EMP       |      5 |     13 |00:00:00.01 |       8 |
|*  5 |     INDEX RANGE SCAN          | IX_EMP_N3 |      5 |     13 |00:00:00.01 |       3 |
--------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OUTER"."SAL">)
   5 - access("INNER"."DEPTNO"=:B1)

전통적인 Filter Subquery(확인자 SubQuery) 이다.


2.단계 : 서브쿼리를 인라인뷰로 바꿔라.
 이 단계에서 unnest 힌트를 사용함으로서 Subquery 가 인라인뷰로 바뀌며 서브쿼리가 없어진다. 이때 메인쿼리의 건수를 유지하기 위해 인라인뷰에 group by 가 추가된다.

-----------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Starts | A-Rows |   A-Time   | Buffers | Used-Mem |
-----------------------------------------------------------------------------------------------------
|*  1 |  TABLE ACCESS BY INDEX ROWID| EMP       |      1 |      5 |00:00:00.01 |      16 |          |
|   2 |   NESTED LOOPS              |           |      1 |     19 |00:00:00.09 |      10 |          |
|   3 |    VIEW                     | VW_SQ_1   |      1 |      5 |00:00:00.01 |       7 |          |
|   4 |     HASH GROUP BY           |           |      1 |      5 |00:00:00.01 |       7 | 1622K (0)|
|   5 |      TABLE ACCESS FULL      | EMP       |      1 |     14 |00:00:00.01 |       7 |          |
|*  6 |    INDEX RANGE SCAN         | IX_EMP_N3 |      5 |     13 |00:00:00.01 |       3 |          |
-----------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OUTER"."SAL">"VW_COL_1")
   6 - access("DEPTNO"="OUTER"."DEPTNO")
       filter("OUTER"."DEPTNO" IS NOT NULL)  

이것은 Optimizer가 쿼리를 아래처럼 변형시킨것이다.

select /*+ gather_plan_statistics  */
       outer.*
 from emp outer,
       ( select deptno, avg(sal) AS VW_COL_1
            from emp
          group by deptno
        ) A
where outer.sal > A.VW_COL_1
   and outer.deptno = A.deptno ;

-----------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Starts | A-Rows |   A-Time   | Buffers | Used-Mem |
-----------------------------------------------------------------------------------------------------
|*  1 |  TABLE ACCESS BY INDEX ROWID| EMP       |      1 |      5 |00:00:00.01 |      16 |          |
|   2 |   NESTED LOOPS              |           |      1 |     19 |00:00:00.13 |      10 |          |
|   3 |    VIEW                     |           |      1 |      5 |00:00:00.01 |       7 |          |
|   4 |     HASH GROUP BY           |           |      1 |      5 |00:00:00.01 |       7 | 1622K (0)|
|   5 |      TABLE ACCESS FULL      | EMP       |      1 |     14 |00:00:00.01 |       7 |          |
|*  6 |    INDEX RANGE SCAN         | IX_EMP_N3 |      5 |     13 |00:00:00.01 |       3 |          |
-----------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("OUTER"."SAL">"A"."VW_COL_1")
   6 - access("OUTER"."DEPTNO"="A"."DEPTNO")
       filter("OUTER"."DEPTNO" IS NOT NULL)

 2단계의 원본 쿼리와 Plan 이 일치함을 알수 있다.


3단계 : 인라인뷰를 해체하라.
MERGE 힌트를 사용함으로서 2단계에서 Unnesting 된 인라인뷰를 해체하여 조인으로 바뀌었다. 이것을 View Merging 이라고 부른다.

select /*+ gather_plan_statistics MERGE(@SUB) */
       outer.*
 from emp outer
where outer.sal > ( select /*+ QB_NAME(SUB) UNNEST */  avg(inner.sal)
                               from emp inner
                            where inner.deptno = outer.deptno
                          );

다시말하면 위의 쿼리를 Optimizer가 아래처럼 재작성 한것이다.

select /*+ gather_plan_statistics */
             outer.deptno deptno,outer.sal sal,
             outer.empno empno
   from emp inner,
          emp outer
  where inner.deptno=outer.deptno
  group by inner.deptno, outer.rowid, outer.empno, outer.sal, outer.deptno
  having outer.sal > avg(inner.sal) ;

메인쿼리의 결과집합을 보존하기위하여 rowid 로 Group by 를 한것에 유의하자.
두개의 Query  Plan 은 동일하며 아래와 같다.
 
-----------------------------------------------------------------------------------------------
| Id  | Operation                      | Name      | A-Rows |   A-Time   | Buffers | Used-Mem |
-----------------------------------------------------------------------------------------------
|*  1 |  FILTER                        |           |      5 |00:00:00.01 |      12 |          |
|   2 |   HASH GROUP BY                |           |     13 |00:00:00.01 |      12 | 1103K (0)|
|   3 |    MERGE JOIN                  |           |     51 |00:00:00.01 |      12 |          |
|   4 |     TABLE ACCESS BY INDEX ROWID| EMP       |     13 |00:00:00.01 |       5 |          |
|*  5 |      INDEX FULL SCAN           | IX_EMP_N3 |     13 |00:00:00.01 |       1 |          |
|*  6 |     SORT JOIN                  |           |     51 |00:00:00.01 |       7 | 2048  (0)|
|*  7 |      TABLE ACCESS FULL         | EMP       |     13 |00:00:00.01 |       7 |          |
-----------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OUTER"."SAL">AVG("INNER"."SAL"))
   5 - filter("INNER"."DEPTNO" IS NOT NULL)
   6 - access("INNER"."DEPTNO"="OUTER"."DEPTNO")
       filter("INNER"."DEPTNO"="OUTER"."DEPTNO")
   7 - filter("OUTER"."DEPTNO" IS NOT NULL)
 
 드디어  1~3 단계에 걸친 Query Transformation 단계가 완성 되었다. 그결과는 성능면에서 대성공이다. Buffers(읽은 Block수) 가 16(원본쿼리) 에서 12 로 약 25% 감소했다.

오라클 트랜스포머는 악성쿼리와 싸워...
  오라클 Query Transformer 는 SQL 을 멋지게 변화시켰다. 이모든 과정을 개발자가 해야한다고 상상해보자.
개발자들에게 전체과정을 이해시키는 교육과정이 추가되어야 하고 개발속도는 몇배나 느려질것이다. 이는 프로젝트의 Risk 가 될것이다. 하지만 오라클 Query Transformer 가 있으므로 악당 로봇이 아닌 악성쿼리와 멋지게 싸워서 이길수 있는 것이다.

편집후기 :
  Query Transformation 을 하려면 반드시 unnesting 이나 merge 힌트를 써야 하는지 질문이 들어왔다. 대부분의 경우 Query Transformer 가 자동으로 변환과정을 수행해준다. 하지만 이것이 가끔 제대로 수행이 안될수 있으므로 이럴경우에만 명시적으로 힌트를 사용하는것이 바람직하다.