실무에서의 Query Transformation 이슈

• Merge 문과 IN 조건이 만난다면
  • T1 조건을 만족하는 건수만큼 T2 가 FULL SCAN 반복한다.
    어떤 원리로 성능저하 발생하는지 MERGE문을 실행할 때 내부적으로 발생하는 일의 특징과 순서를 알아보자.
    

1. 무조건 Outer 조인이 발생해
   • Merge 문을 실행하면 Target쪽 테이블에는 무조건 Outer 조인으로 바뀐다.
     -> Match 되지 않는 경우 (조인에 실패한 경우) INSERT 해야 하기 때문.
     그리고 Outer 조인은 다시 Lateral view 로 바뀐다. -> view merging이 실패할 경우 FPD (Filter Push Down) 이나 JPPD(JOIN PREDICATE PUSH DOWN) 이 적용되어야 하기 때문.
2. 쿼리변환 순서가 중요하다
   • MERGE 문 실행 시 쿼리 변환 발생 순서
     1. Transformer(Logical Optimizer)는 IN조건을 OR로 바꾼다.
     2. TRANSFORVER(Logical Optimizer) 는 Outer 조인되는 쪽을 Lateral View로 바꾼다.
     3. Lateral view가 해체 (view merging)되어 평범한 Outer조인으로 바뀐다.
     이때 View merging에 실패하면 심각한 성능저하가 발생할 수 있다.
     ( 위 Plan 에서 T2 쪽의 View가 해체되지 못했다)
   • 테스트 환경 실행할 쿼리

CREATE TABLE t1(c1 VARCHAR2(10), c2 INT, c3 INT, c4 INT);
CREATE TABLE t2(c1 VARCHAR2(10), c2 INT, c3 INT, c4 INT);

INSERT INTO t1
   SELECT DECODE (MOD (LEVEL, 2), 0, 'A', 'B') , LEVEL, LEVEL, LEVEL
 FROM DUAL
CONNECT BY LEVEL <= 100000;

INSERT INTO t2
   SELET DECODE (MOD (LEVEL, 2), 0, 'A', 'B') , LEVEL, LEVEL, LEVEL
FROM DUAL
CONNECT BY LEVEL <= 100000;

ANALYZE TABLE t1 COMPUTE STATISTICS ;
ANALYZE TABLE t2 COMPUTE STATISTICS ;

-- Merge 문을 사용

-- case 1

MERGE /*+ gather_plan_statistics */ INTO t2
   USING ( SELECT *
                   FROM t1
                 WHERE c1 IN ('A', 'B')) x
    ON (   x.c1 = t2.c1 
    AND   x.c2 = t2.c2
    AND   x.c3 = t2.c3
    AND   t2.c1 = 'A' )
 WHEN MATCHED THEN
   UPDATE 
     SET t2.c4 = x.c4
WHEN NOT MATCHED THEN
   INESRT (t2.c1, t2.c2, t2.c3, t2.c4)
     VALUES (x.c1, x.c2, x.c3, x.c4) ;

SELECT *
   FROM TABLE (DBMS_XPLAN.display_cursor(NULL, NULL, 'allstats last')) ;

• • 위 쿼리의 plan 은 정상적인 plan 이다.
    
  • Merge 문에 IN 조건을 사용해보자

-- case 2

MERGE /*+ gather_plan_statistics */ INTO t2
   USING ( SELECT *
                   FROM t1
                 WHERE c1 IN ('A', 'B')) x
    ON (   x.c1 = t2.c1 
    AND   x.c2 = t2.c2
    AND   x.c3 = t2.c3
    AND   t2.c1 = 'A', 'B' ))  //  IN 조건을 사용
 WHEN MATCHED THEN
   UPDATE 
     SET t2.c4 = x.c4
WHEN NOT MATCHED THEN
   INESRT (t2.c1, t2.c2, t2.c3, t2.c4)
     VALUES (x.c1, x.c2, x.c3, x.c4) ; 

1. 10만 번 반복된다
   • Starts 항목에 주목. FTS ( Full Table Scan ) 을 10만 번 반복 실행하였다.
   • CASE2 에서 t2.c1 IN ( 'A', 'B') 조건을 사용하였더니 최악의 Plan 이 됨.
     Case1, Case2 실행 시 Logical Optimizer에 의해 변경된 SQL을 보면 이유를 알 수 있다.

SELECT /*+ CASE1 NO_MERGE 상태 */
     lv.rid ,
     lv.c1, lv.c2, lv.c3, lv.c4,
      x.c1,  x.c2,  x.c3,  x.c4
 FROM (SELECT t1.c1, t1.c2, t1.c3, t1.c4 c4
               FROM t1
            WHERE t1.c1 = 'A' OR t1.c1 = 'B' ) x,
            LATERL ( SELECT t2.c1, t2.c2, t2.c4, t2.ROWID as rid 
                              FROM t2
                           WHERE x.c1 = t2.c1
                                AND x.2 = t2.c2
                                AND x.3 = t2.c3
                                AND x.c1 = 'A')(+) lv ;

1. 정상적으로 뷰가 해체되다
   • Lateral view 내부의 T2.C1 = 'A' 조건은 Outer 조인으로 바꿀 수 있으므로, Case1 은 view merging 이 발생하여 인라인 뷰 X 와 Lateral View lv 가 아래처럼 평범한 outer 조인으로 바뀐다.

SELECT /*+CASE1 MERGE 상태 */ 
  t2.roWID rid,
  t2.c1, t2.c2 c2, t2.c3 c3, t2 .c4 c4,
  t1.c1, t1.c2 c2, t1.c3 c3, t1.c4 c4
FROM t1, t2
WHERE t1.c3 = t2.c3(+)
AND t1.c2 = t2.c2(+)
AND t1.c1 = t2.c1(+)
AND t2.c1(+) 'A'
AND(t1.c1= 'A' OR t1.c1= 'B');

• • 이때 옵티마이져가 내부적으로 MERGE 힌트를 사용한다.
    10053 Trace에도 쿼리블록 SEL$2, SEL$3 이 SEL$1에 MERGE 되었다는 정보가 포함되어있다.
    
  • CASE 2 분석

SELECT /*+ CASE2 NO_MERGE 상태 */
   lv.rid,
   lv.c1, lv.c2, lv.c3, lv.c4,
    x.c1, x.c2, x.c3, x.c4
  FROM (SELECT t1.c1 c1, t1.c2 c2, t1.c3 c3, t1.c4 c4
           FROM t1
         WHERE t1.c1 = 'A' OR t1.c1 = 'B' ) x,
      LATERAL(SELECT t2.c1, t2.c2, t2.c3, t2.c4, t2.ROWID AS rid
   FROM t2
  WHERE x.c1 = t2.c1
    AND x.c2 = t2.c2
    AND x.c3 = t2.c3
    AND (t2.c1 = 'A' OR t2.c1 = 'B')) (+) lv ;

1. 제약조건 때문에...
   • 위 SQL은 Lateral view를 적용하였는데, T2.C1 ='A' OR T2.C1 ='B' 조건때문에 Outer 조인으로 바꾸지를 못한다.
     이는 제약사항으로, 제약조건이 있을 경우 view merging이 실패한다.
     아래와 같다.
     
2. IN이 발목을 잡다
   • 인라인뷰 X만 view merging 이 발생하였다.
     IN 혹은 OR 조건이 View Merging이 되지 못하도록 하였다.
     Lateral view 가 살아남게 되었고, 이는 스칼라 서브쿼리처럼 동작된다.
     Lateral view는 Hash 조인으로 실행되지 못한다.
     문제의 Plan에서 Nested loop 조인이 발생한 이유도 여기있다.
     이를 해결할 방법은 ?
3. 해결 방법 3가지
   1) 인덱스를 만들면 문제 해결. 아래는 T2 에 (C1, C2, C3) 인덱스 만든 후
   CASE 2 를 실행한 Plan 이다.
   

2) 인덱스를 만들 수 없는 경우라면, Between 을 사용하면 된다.
Between 은 Outer 조인이 가능하므로 view merging 이 정상적으로 발생된다.

MERGE /*+ gather_plan_statistics */ INTO t2
   USING ( SELECT *
             FROM t1
            WHERE c1 IN('A','B')) x
      ON (  x.c1 = t2.c1 
        AND x.c2 = t2.c2
        AND x.c3 = t2.c3
        AND t2.c1 BETWEEN 'A' AND 'B')
    WHEN MATCHED THEN
       UPDATE
         SET t2.c4 = x.c4
     WHEN NOT MATCHED THEN
       INSERT (t2.c1, t2.c2, t2.c3, t2.c4)
        VALUES (x.c1, x.c2, x.c3, x.c4) ;

• 정상적으로 Hash Join 이 발생하였다.
  
  3) Between 을 사용할 수 없다면 DECODE 를 사용한다.
  DECODE 또한 Outer 조인이 가능하므로, view Merging이 발생된다.

MERGE /*+ gather_plan_statistics */ INTO t2
   USING ( SELECT *
             FROM t1
            WHERE c1 IN ('A','B')) x
        ON ( x.c1 = t2.c1
        AND  x.c2 = t2.c2
        AND  x.c3 = t2.c3
        AND  t2.c1 = DECODE(t2.c1,'A','A','B'))
    WHEN MATCHED THEN
       UPDATE
         SET t2.c4 = x.c4
     WHEN NOT MATCHED THEN
       INSERT (t2.c1, t2.c2, t2.c3, t2.c4)
        VALUES (x.c1, x.c2, x.c3, x.c4) ;

• 정상적으로 Hash 조인이 발생하였다.
  
  • Merge 문 사용 시 On 절에 Target 테이블 조건으로 IN, OR 를 사용하면 View Merging 이 발생하지 않는다. 따라서 Lateral View 해체되지 못하며, lateral view특성상 Nested Loop 조인이 적용됨.
    이때 후행 테이블에 적절한 인덱스가 없으면 Full Table Scan 발생하여 성능저하됨.
    Between 이나 DECODE 등 사용하여 상황에 맞는 해결책을 사용할 수 있다.