질문을 받다
독자로 부터 다음과 같은 질문을 받았다. "MERGE 문에 IN 조건을 사용한다면 아래의 Plan처럼 심각한 성능저하가 발생 하였다. 왜그런가?"


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

| Id  | Operation              | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |

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

|   1 |  MERGE                 | T2   |      1 |        |      2 |00:25:50.73 |      50M|    821 |

|   2 |   VIEW                 |      |      1 |        |    100K|00:25:52.38 |      50M|    795 |

|   3 |    NESTED LOOPS OUTER  |      |      1 |    100K|    100K|00:25:52.28 |      50M|    795 |

|*  4 |     TABLE ACCESS FULL  | T1   |      1 |    100K|    100K|00:00:00.60 |     316 |    629 |

|   5 |     VIEW               |      |    100K|      1 |    100K|00:25:34.26 |      50M|    166 |

|*  6 |      FILTER            |      |    100K|        |    100K|00:25:33.92 |      50M|    166 |

|*  7 |       TABLE ACCESS FULL| T2   |    100K|      1 |    100K|00:25:33.68 |      50M|    166 |

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


T1
의 조건을 만족하는 건수만큼 T2 FULL SCAN을 반복하고 있다. 이것은 재앙이나 다름없다. 어떤 원리로 성능저하가 발생하는지 MERGE문을 실행할 때 내부적으로 발생하는 일들의 특징과 순서를 알아보자.

무조건 아우터 조인이 발생해
MERGE
문을 실행하면 Target 쪽 테이블에는 무조건 아우터 조인으로 바뀐다. 왜냐하면 Match 되지 않는 경우(조인에 실패한 경우)에도 INSERT 해야 하기 때문이다. 그리고 아우터 조인은 다시 LATERAL VIEW로 바뀐다. 왜냐하면 View Merging이 실패할 경우 FPD(Filter Push Down)이나 JPPD(JOIN PREDICATE PUSH DOWN)이 적용 되어야 하기 때문이다. LATERAL VIEW의 개념은 아래의 POST를 참조하라
.

http://scidb.tistory.com/entry/Outer-Join-의-재조명


쿼리변환 순서가 중요하다
아래는 MERGE문 실행시 쿼리변환이 발생하는 순서 이다
.

1.
먼저 Transformer(Logical Optimizer) IN 조건을 OR 로 바꾼다
.

2.TRANSFORMER(Logical Optimizer)
는 아우터 조인되는 쪽을 LATERAL VIEW로 바꾼다
.

3.LATERAL VIEW
가 해체(View Merging이라 불림) 되어 평범한 아우터 조인으로 바뀐다
. 이때 View Merging에 실패하면 심각한 성능저하가 발생할 수 있다. 위의 Plan을 보면 T2 쪽의 View 가 해체되지 못했다. 이것이 실마리가 될 것이다.

아래의 스크립트를 실행하여 실제로 이런 일들이 발생하는지 Test 환경을 만들어 보자
.

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

select 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

      INSERT (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')) ;


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

| Id  | Operation               | Name | Starts | A-Rows |   A-Time   | Buffers | Reads  | Used-Mem |

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

|   1 |  MERGE                  | T2   |      1 |      2 |00:00:12.52 |     105K|    655 |          |

|   2 |   VIEW                  |      |      1 |    100K|00:00:00.65 |     632 |    626 |          |

|*  3 |    HASH JOIN RIGHT OUTER|      |      1 |    100K|00:00:00.65 |     632 |    626 | 4686K (0)|

|*  4 |     TABLE ACCESS FULL   | T2   |      1 |  50000 |00:00:00.02 |     316 |    313 |          |

|*  5 |     TABLE ACCESS FULL   | T1   |      1 |    100K|00:00:00.01 |     316 |    313 |          |

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

 

Predicate Information (identified by operation id):

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

   3 - access("T1"."C3"="T2"."C3" AND "T1"."C2"="T2"."C2" AND "T1"."C1"="T2"."C1")

   4 - filter("T2"."C1"='A')

   5 - filter(("C1"='A' OR "C1"='B'))

 

아주 정상적인 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 IN ('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) ;

 

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

| Id  | Operation              | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |

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

|   1 |  MERGE                 | T2   |      1 |        |      2 |00:25:50.73 |      50M|    821 |

|   2 |   VIEW                 |      |      1 |        |    100K|00:25:52.38 |      50M|    795 |

|   3 |    NESTED LOOPS OUTER  |      |      1 |    100K|    100K|00:25:52.28 |      50M|    795 |

|*  4 |     TABLE ACCESS FULL  | T1   |      1 |    100K|    100K|00:00:00.60 |     316 |    629 |

|   5 |     VIEW               |      |    100K|      1 |    100K|00:25:34.26 |      50M|    166 |

|*  6 |      FILTER            |      |    100K|        |    100K|00:25:33.92 |      50M|    166 |

|*  7 |       TABLE ACCESS FULL| T2   |    100K|      1 |    100K|00:25:33.68 |      50M|    166 |

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

 

Predicate Information (identified by operation id):

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

   4 - filter(("C1"='A' OR "C1"='B'))

   6 - filter(("T1"."C1"='A' OR "T1"."C1"='B'))

   7 - filter(("T1"."C1"="T2"."C1" AND "T1"."C2"="T2"."C2" AND "T1"."C3"="T2"."C3" AND

              INTERNAL_FUNCTION("T2"."C1")))

 

10만번 반복된다
Starts 항목에 주목하라 FTS(Full Table Scan)을 10만번 반복 실행하였다. CASE2 에서 t2.c1 IN ('A', 'B')
조건을 사용하였더니
최악의 PLAN이 만들어 졌으며 Buffers 50M 에 육박하고 시간상으로도 25분 이상 걸렸다. 왜 그럴까? 아래의 SQL처럼 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 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'  )(+) lv

 

 

정상적으로 뷰가 해체되다
Case1의
 LATERAL VIEW 내부의 T2.C1='A' 조건은 아우터 조인으로 바꿀 있으므로 View Merging 발생하여 인라인뷰 X LATERAL VIEW lv 아래처럼 평범한 아우터 조인으로 바뀐다.

 

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 되었다는 정보가 포함되어 있다.

 

Registered qb: SEL$5428C7F1 0x9f7b318 (VIEW MERGE SEL$1; SEL$2 SEL$3)


Out Line
정보에도 이런 사항이 잘 나타난다.

Outline Data

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

  /*+

생략
MERGE(@"SEL$2")      -->
쿼리블럭 2

          MERGE(@"SEL$3")      --> 쿼리블럭 3 MERGE 되어 LATERAL VIEW 가 없어짐
          
생략

  */

 

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

 

제약조건 때문에...
위의
SQL LATERAL VIEW 적용한 모습이다. 그런데 (T2.C1='A' OR T2.C1='B') 조건 때문에 아우터 조인으로 바꾸질 못한다. 이것은 오라클 제약사항 이다. 제약조건이 있을 경우는 View Merging이 실패한다. 아래처럼 말이다.

 

SELECT /*+ CASE2 MERGE 상태 */

       lv.RID,

       lv.C1, lv.C2, lv.C3, lv.C4,

       X.C1,  X.C2,  X.C3,  X.C4

  FROM T1,

       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

 WHERE T1.C1='A' OR T1.C1='B'

 

IN 이 발목을 잡다
인라인뷰
X View Merging이 발생하였다. 결국 IN 혹은 OR  조건이 View Merging이 되지 못하도록 발목을 잡은 셈이다. 그리하여 Lateral View 가 살아남게 되었다. Lateral View
는 스칼라 서브쿼리처럼 동작하게 된다. 다시 말하면 LATERAL VIEW 
Hash 조인으로 실행되지 못한다. 문제의 Plan에서 Nested Loop 조인이 발생한 이유도 여기 있다.
 

그렇다면 이 문제를 어떻게 해결할 수 있을까?

문제 해결방법 3가지
1)
만약 인덱스를 만들 수 있다면 문제가 해결된다. 아래는 T2 (C1, C2, C3) 인덱스를 만든후 CASE 2를 다시 실행한 Plan 이다.

 

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

| Id  | Operation                        | Name   | Starts | A-Rows |   A-Time   | Buffers | Reads  |

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

|   1 |  MERGE                           | T2     |      1 |      2 |00:00:16.95 |     303K|    934 |

|   2 |   VIEW                           |        |      1 |    100K|00:00:03.92 |     200K|    932 |

|   3 |    NESTED LOOPS OUTER            |        |      1 |    100K|00:00:03.92 |     200K|    932 |

|*  4 |     TABLE ACCESS FULL            | T1     |      1 |    100K|00:00:00.12 |     316 |    313 |

|   5 |     VIEW                         |        |    100K|    100K|00:00:02.51 |     200K|    619 |

|*  6 |      FILTER                      |        |    100K|    100K|00:00:02.29 |     200K|    619 |

|   7 |       TABLE ACCESS BY INDEX ROWID| T2     |    100K|    100K|00:00:02.09 |     200K|    619 |

|*  8 |        INDEX RANGE SCAN          | T2_IDX |    100K|    100K|00:00:01.24 |     100K|    320 |

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

 

Predicate Information (identified by operation id):

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

   4 - filter(("C1"='A' OR "C1"='B'))

   6 - filter(("T1"."C1"='A' OR "T1"."C1"='B'))

   8 - access("T1"."C1"="T2"."C1" AND "T1"."C2"="T2"."C2" AND "T1"."C3"="T2"."C3")

       filter(("T2"."C1"='A' OR "T2"."C1"='B'))

2) 인덱스를 만들 수 없는 경우라면 아래처럼 Between 을 사용하면 된다. Between 은 아우터 조인이 가능하다.

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

     

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

| Id  | Operation            | Name | Starts | A-Rows |   A-Time   | Buffers | Reads  | Used-Mem |

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

|   1 |  MERGE               | T2   |      1 |      2 |00:00:14.99 |     104K|    817 |          |

|   2 |   VIEW               |      |      1 |    100K|00:00:00.58 |     816 |    810 |          |

|*  3 |    HASH JOIN OUTER   |      |      1 |    100K|00:00:00.48 |     816 |    810 | 7600K (0)|

|*  4 |     TABLE ACCESS FULL| T1   |      1 |    100K|00:00:00.01 |     316 |    313 |          |

|*  5 |     TABLE ACCESS FULL| T2   |      1 |    100K|00:00:00.01 |     500 |    497 |          |

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

 

Predicate Information (identified by operation id):

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

   3 - access("T1"."C3"="T2"."C3" AND "T1"."C2"="T2"."C2" AND "T1"."C1"="T2"."C1")

   4 - filter(("C1"='A' OR "C1"='B'))

   5 - filter(("T2"."C1"<='B' AND "T2"."C1">='A'))


정상적으로 Hash 조인이 발생하였다.

3) Between 을 사용할 수 없는 경우라면 아래처럼 Decode를 사용하면 된다. Decode 또한 아우터 조인이 가능하다.

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

     

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

| Id  | Operation               | Name | Starts | A-Rows |   A-Time   | Buffers | Reads  | Used-Mem |

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

|   1 |  MERGE                  | T2   |      1 |      2 |00:00:15.16 |     104K|    967 |          |

|   2 |   VIEW                  |      |      1 |    100K|00:00:00.72 |     816 |    810 |          |

|*  3 |    HASH JOIN RIGHT OUTER|      |      1 |    100K|00:00:00.72 |     816 |    810 | 8568K (0)|

|*  4 |     TABLE ACCESS FULL   | T2   |      1 |    100K|00:00:00.02 |     500 |    497 |          |

|*  5 |     TABLE ACCESS FULL   | T1   |      1 |    100K|00:00:00.01 |     316 |    313 |          |

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

 

Predicate Information (identified by operation id):

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

   3 - access("T1"."C3"="T2"."C3" AND "T1"."C2"="T2"."C2" AND "T1"."C1"="T2"."C1")

   4 - filter("T2"."C1"=DECODE("T2"."C1",'A','A','B'))

   5 - filter(("C1"='A' OR "C1"='B'))

정상적으로 Hash 조인이 발생하였다.

 

결론

Merge문 사용시 On 절에 Target 테이블의 조건으로 IN 이나 OR를 사용하면 View Merging 이 발생하지 않는다. 따라서 LATERAL VIEW가 해체되지 못하며 LATERAL VIEW의 특성상 Nested Loop 조인이 적용된다. 이때 후행 테이블은 적절한 인덱스가 없다면 Full Table Scan이 발생하여 재앙과 같은 성능저하 현상이 발생된다. 이때 BETWEEN 이나 DECODE등 상황에 맞는 해결책을 사용할 수 있다.  

모르면 못한다
결국 Query Transformation의 원리와 순서 그리고 제약조건을 알게 된다면 누가 해법을 말해주지 않아도 자연스럽게 알 수 있다. IN 과 OR의 아우터 조인 제약조건은 누구나 알고 있으므로 문제가 될수 없다. 문제는 Query Transformation을 모른다면 튜닝을 못하는 시대가 이미 왔다는 사실이다. 안타깝게도 이런 원리를 설명해주는 서적은 어디에도 없다. 물론 몇가지 Query Transformation을 소개한 책은 있지만 Logical Optimizer를 주제로 하는 서적은 없다. 다시 말하면 우리는 튜닝을 하지 못할 환경에 살고 있다.


신고
Posted by extremedb

댓글을 달아 주세요

  1. Favicon of http://oraclub.tistory.com BlogIcon 서은아빠 2009.11.26 14:41 신고  댓글주소  수정/삭제  댓글쓰기

    마지막 문구가 섬뜩하게 다가 오네여.... (다시 말하면 우리는 튜닝을 하지 못할 환경에 살고 있다.)
    결국은 Query Transformation 활용여부에 따라 튜너의 능력이 평가되는 세상이 오지 않았나 싶습니다.
    좋은 정보 감사드립니다.

  2. feelie 2009.11.26 21:10 신고  댓글주소  수정/삭제  댓글쓰기

    좋은 내용에 중요부분이 파란/빨강으로 보이니 더 눈에 잘들어옵니다.
    내용중에
    view merging 에 제약조건이 있으면 실패한다고 했는데요.
    제가 책에만 나오는 내용만을 알고 있어서 그런가요
    view merging 실패
    1. set operators
    2. a connect by
    3. rownum
    4. aggregation functions
    으로 딱 정리했는데 IN,OR에도 제약이 있다니....

    IN,OR는 outer join에서 view merging이 실패하는것인가요?
    아님 In, or가 있으면 view merging 이 실패하는건가요?

    • feelie 2009.11.26 21:22 신고  댓글주소  수정/삭제

      죄송합니다.
      outer join의 재조명에서
      적어주신 url에 들어가보니 설명하신 제약조건이 나오네요
      영어가 짧아 읽어보지 않았는데.
      죄송합니다.

  3. Favicon of http://scidb.tistory.com BlogIcon extremedb 2009.11.26 21:42 신고  댓글주소  수정/삭제  댓글쓰기

    블로그 내용중 case2를 분석한 경우를 보면

    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

    에서 X는 OR 가 있지만 정상적으로 View Merging(뷰 해체)이 발생 합니다.
    따라서 IN 이나 OR는 View Merging이 정상적으로 됩니다.
    문제는 아우터조인 되는 뷰에 OR 나 IN 조건이 있으면 뷰 해체가 불가능 합니다.
    위의 Lateral View 가 View Merging이 발생했다면 아래의 SQL처럼 되어야 하는데 에러가 발생합니다.
    따라서 IN 이나 OR 조건이 뷰내부에 있고 그 뷰가 아우터 조인 된다면 View Merging은 발생될수 없습니다.
    아래의 SQL을 직접 실행 해보시기 바랍니다. 그러면 쉽게 이해가 가실겁니다.

    SELECT /*+ CASE2 SQL 에러 발생 */

    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' OR T2.C1(+)='B')

    AND (T1.C1='A' OR T1.C1='B')

    위의 예에서도 볼수 있듯이 view merging 의 제약조건은 4가지 뿐아니라 더 많습니다. 기계적으로 외우지 마시고 항상 plan과 10053을 가까이 하시면 많은 것을 얻을수 있습니다.

  4. feelie 2009.11.27 08:54 신고  댓글주소  수정/삭제  댓글쓰기

    감사합니다.
    아직도 외우는 일에 너무 익숙한듯 합니다.
    말씀하신 plan, 10053을 가까이 해야겠네요...

  5. 안녕하세요 2009.12.01 17:50 신고  댓글주소  수정/삭제  댓글쓰기

    지난번에 Partition Wise Join에 대한 글을 이 블로그에서 봤었던 것 같은데,
    그 때 많은 도움이 되어 다시 확인하려니 글이 삭제된 것 같네요-

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2009.12.04 10:27 신고  댓글주소  수정/삭제

      파티션 wise join 과 관련된 글은 올린적이 없는것 같습니다.
      아래의 글에 일부 관련정보가 있으니 참조하시기 바랍니다.
      http://scidb.tistory.com/entry/Parallel-Query-의-조인시-Row-Distribution

일부 스크립트에 오타가 있어 수정했음을 알립니다.(2009.11.19)

거의 모든 시스템에 버그가 존재해 
아주 유명한 FBI 인덱스 관련 버그를 소개한다. 이 버그는 Wrong result 버그로 분류되며 특징은 조회가 되지 않는다는 것이다. 최근까지 Patch 가 나오지 않아 악명이 높았다. 필자의 경험으로는 거의 모든 시스템에서 이 버그가 재연되었다.

버그 재연방법 
CHAR 컬럼의 길이가 2 BYTE 이상이고 이 컬럼에 SUBSTR 함수로 FBI인덱스 생성할 때.

이제 아래의 스크립트를 사용하여 버그를 재연해보자.

환경 : Oracle 11.1.0.6

drop table fbi_test;

create table fbi_test(col1 char(14) );

insert into fbi_test values('01234567890123');
commit;

create index fbi_idx on fbi_test(substr(col1,1,10));

analyze table fbi_test compute statistics;
analyze index fbi_idx compute statistics;

이제 모든 준비가 끝났으므로 데이터를 조회해보자.

select /*+ gather_plan_statistics index(a  fbi_idx ) */  *
 from fbi_test a
where col1 = '01234567890123' ;
no rows selected.

한 건도 나오지 않는다. 이것은 분명 버그이다.
  
--------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------
|*  1 |  TABLE ACCESS BY INDEX ROWID| FBI_TEST |      1 |      1 |      0 |00:00:00.01 |       1 |
|*  2 |   INDEX RANGE SCAN          | FBI_IDX  |      1 |      1 |      0 |00:00:00.01 |       1 |
--------------------------------------------------------------------------------------------------

 
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("COL1"='01234567890123')
   2 - access("FBI_TEST"."SYS_NC00002$"='0123456789')
 
위의 Plan을 보면 Virtual Column을 사용하였다. 오라클은 FBI를 만들면 Virtual Column을 생성 한다.
실행계획상에서 Virtual Column을 사용하는 경우는 위와 같이 조회 되지 않는 버그가 발생한다.
이런 상황에서 버그를 피하려면 해당 인덱스를 사용하지 않거나(Full Scan 이용) 아니면 아래와 같이 명시적으로 SQL을 수정해야 한다.

select /*+ gather_plan_statistics index(a  fbi_idx ) */  *
 from fbi_test a
where col1 = '01234567890123'
  and substr(col1,1,10) = '0123456789' ;

COL1         
--------------
01234567890123

1 row selected.

정상적으로 1건이 나왔다.

--------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------
|*  1 |  TABLE ACCESS BY INDEX ROWID| FBI_TEST |      1 |      1 |      1 |00:00:00.01 |       2 |
|*  2 |   INDEX RANGE SCAN          | FBI_IDX  |      1 |      1 |      1 |00:00:00.01 |       1 |
--------------------------------------------------------------------------------------------------

 
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("COL1"='01234567890123')
   2 - access("FBI_TEST"."SYS_NC00002$"='0123456789')
 
편법은 안돼... 패치가 정답이다
하지만 이런 편법으로 문제를 해결하는 것은 임시방편 밖에 되지 못한다. 또한 모든 개발자들이 substr 함수를 추가하여 개발한다고 보장 할 수 없다. 이 편법을 사용 하더라도 조건절에 > 혹은 < 사용시에는 여전히 조회가 되지 않는다.

select /*+ gather_plan_statistics index(a  fbi_idx ) */  *
 from fbi_test a
where col1 > '0123456789012'
 and substr(col1,1,10) > '012345678' ;

no rows selected.

한가지 다행스러운 점은 최근에 BUG Patch 2개가 새로 나왔다는 것이다. 또한 아쉬운 점은 HP 시스템용 Patch는 아직 없다는 것이다. 각자의 플랫폼에 맞는 Patch가 있는지 확인해보라.

문서번호 : 6131467
패치 ID : 6131467

신고
Posted by extremedb

댓글을 달아 주세요

  1. newyemac 2009.11.19 15:36 신고  댓글주소  수정/삭제  댓글쓰기

    정상적으로 수행되는것 같은데 제가 이해를 잘 못했나요?

    CMBC at DEVPIA SQL)SELECT * FROM V$VERSION WHERE ROWNUM=1;

    BANNER
    ----------------------------------------------------------------
    Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

    CMBC at DEVPIA SQL)select /*+ gather_plan_statistics index(fun funidx) */ *
    from fbi_test
    where col1 = '01234567890123'; 2 3

    COL1
    --------------
    01234567890123

    CMBC at DEVPIA SQL)@x

    Plan hash value: 596953665

    --------------------------------------------------------------------------------------------------
    | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
    --------------------------------------------------------------------------------------------------
    |* 1 | TABLE ACCESS BY INDEX ROWID| FBI_TEST | 1 | 1 | 1 |00:00:00.01 | 2 |
    |* 2 | INDEX RANGE SCAN | FBI_IDX | 1 | 1 | 1 |00:00:00.01 | 1 |
    --------------------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------

    1 - filter("COL1"='01234567890123')
    2 - access("FBI_TEST"."SYS_NC00002$"='0123456789')

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2009.11.19 16:25 신고  댓글주소  수정/삭제

      이버그는 모든 플랫폼에서 발생하는 것은 아닙니다.
      캐릿터 셋이 멀티바이트 인 경우만 발생합니다. 예를 들어 KO16~ 혹은 WIN949~등으로 나갈때 입니다.

  2. newyemac 2009.11.19 16:40 신고  댓글주소  수정/삭제  댓글쓰기

    아.. 감사합니다. 저는 참고로 US7ASCII(hp-ux11.23)를 쓰고 있습니다. KO16에서 테스트 해봐야 겠네요.

  3. feelie 2009.11.19 20:02 신고  댓글주소  수정/삭제  댓글쓰기

    fbi을 그다지 써볼기회가 없어(초대용량이 아니라 그러나요?) 이론적으로만 알고 있는데.
    오라클에 이런 bug도 있다니 참 의외네요..

    좋은 내용잘 봤습니다..

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2009.11.19 22:21 신고  댓글주소  수정/삭제

      오라클 뿐만 아니라 모든 DBMS에는 무수한 버그가 있습니다. 그것중에 모르고 지나가는 경우가 대부분 이지요. 버그를 발견하고 패치 Request를 하는 경우는 그나마 나은편이라고 할수 있습니다.

  4. basedba 2009.11.20 14:36 신고  댓글주소  수정/삭제  댓글쓰기

    cahr 타입에서는 문제가 생기지만 varchar2타입에서는 이상없이 잘 나옵니다. 이것말고도 잘못된 결과값들이 나오는것을 몇개 알고 있는데 주로 char타입에서 발생하더라구요. 오라클은 각 패치버젼마다 엄청난(?)
    bug를 가지고 있는데 10.2.0.4패치리스트는 약 3000개 정도의 bug 패치를 가지고 있고 그중 잘못된 결과값이 나오는
    것들도 몇백개정도 되더라구요. 심심해서 제가 한번 count해 봤습니다. 그기다가 이 리스트는 평법한 일반 dba에게
    보여주는 것이지 오라클 본사 dba에게 보여주는 중요 정보는 좀 틀린것으로 알고 있습니다. 오라클 엔지니어에게 제공되는 정보중 치명적인 정보는 메타링크에서 대외비로 빨간색으로 나오는데 타소속 일반 dba에게 보여주지 않습니다. 그래도 오라클이 가장 안정적인 dbms라는것은 저도 인정합니다. 동규님의 글 잘 읽고 있습니다. 수고하세요 ^^

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2009.11.20 14:52 신고  댓글주소  수정/삭제

      basedba 님의 말이 맞습니다. 외부에 보여주지 않는 정보도 많이 있습니다. 이런 면에서는 오라클 직원이 유리할수 박에 없습니다. 좋은 하루 되세요.

  5. 하하 2010.03.11 11:00 신고  댓글주소  수정/삭제  댓글쓰기

    강제적으로 힌트로 FBI 인덱스를 사용하도록 해놓고서 WHERE절에는 FUNCTION을 사용안한다는게 일단 모순된 예제가 아닐까요?
    제 생각에는 버그라기 보다는 당연한 결과인것같은데..

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2010.03.11 11:05 신고  댓글주소  수정/삭제

      두가지만 말씀드리겟습니다.
      1.버그이므로 버그 패치가 있는것 입니다. 버그가 없으면 버그 패치도 없습니다.
      2.인덱스의 사용유무에 따라 답이 달라지는 것은 당연한 결과가 아니라 버그입니다.

지인에게서 전화가 오다
지인 : 데이터를 체크해야 하는데 오라클에 is_number, is_date 함수가 없어서 데이터를 체크하기가 불편합니다.
          데이터를 오라클에서 가져와서 자바에서 체크하고 있습니다. 그러다 보니 너무 느립니다.
필자 : 그럴 필요 없습니다.
지인 : 물론 External Function을 사용하면 자바를 사용하여 오라클에 함수를 생성할수도 있겠지요.
필자 : 그냥 PL/SQL 로 하시면 됩니다.
지인 : 네?

무서운 일이다. 전체 데이터를 Network를 타고 가져와서 자바로 체크하다니... Network I/O 가 엄청 날것이다.          

오라클에서 제공하는 함수가 없다
"오라클에서 is_number, is_date 함수가 없어서 데이터를 체크하기가 불편하다" 이말은 옳다. 하지만 오라클에서 체크함수를 제공하지 않는 이유는 아마도 개발자가 너무도 쉽게 만들 수 있어서 그런 것이 아닐까?

is_number, is_date
함수를 직접 만들어 보자.

 

CREATE OR REPLACE FUNCTION is_number(v_str_number IN varchar2)

RETURN NUMBER

IS  /* 데이터가 number 형인지 검사하는 함수임. 1 이 나오면 NUMBER 형임 */

     V_NUM NUMBER;

BEGIN

  V_NUM := TO_NUMBER(v_str_number);

  RETURN 1;

EXCEPTION

  WHEN OTHERS THEN RETURN 0    ;

END;


CREATE OR REPLACE FUNCTION is_date(v_str_date IN varchar2, V_FORMAT IN VARCHAR2 DEFAULT 'YYYYMMDD')

RETURN NUMBER

IS   /* 데이터가 DATE 형인지 검사하는 함수임. 1 이 나오면 DATE 형임 */

     V_DATE DATE;

BEGIN

  V_DATE := TO_DATE(v_str_date, V_FORMAT);

  RETURN 1;   

EXCEPTION

  WHEN OTHERS THEN RETURN 0    ;

END; 


너무나 쉽게 생성 되었다. 그럼 이제 사용해보자.


함수사용법


select  is_number('abcd'), is_number('1234'),

        is_date('20090230'), is_date('20090228')

  from dual ;

 

결과: 

IS_NUMBER('ABCD') IS_NUMBER('1234') IS_DATE('20090230') IS_DATE('20090228')

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

                0                 1                   0                   1

1 row selected.


number 형 에서 벗어나는 데이터와 date 형 에서 벗어나는 데이터를 가려 내었다. DBMS 에서 사용할 수 있는 함수와 기능이 똑같다. 이렇게 해서 개발자의 문제가 일시적으로 해결되었다.



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

문제는 성능이다
다음날 다시 전화가 왔다. 일회성이 아닌 지속적으로 데이터를 체크해야 하는데 이전보다는 빨라졌지만 여전히 성능이 느리다는 것이었다. 이제부터 함수의 성능에 대해 논의 해보자. 먼저 올바른 데이터 1000만 건을 만들고 number형이 아닌 데이터와 date형이 아닌 데이터를 1건 추가해보자.  

 

drop table test_tbl purge;

 

create table test_tbl nologging as

select a.*

  from (select to_char(level) as varchar_num, to_char(level + sysdate, 'YYYYMMDD') as varchar_date

          from dual

       connect by level <= 100) a,

       (select level from dual connect by level <= 100000) b ;

 

insert into test_tbl values('ABCD', '20090230');

commit;


이제 함수를 실행 해보자.

 

alter session set statistics_level = all;

alter system flush buffer_cache;

 

select /*+ gather_plan_statistics */ *

  from test_tbl a

 where is_number(varchar_num) = 0;


결과 :

VARCHAR_NUM      VARCHAR_DATE

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

ABCD             20090230

 

select * from table(dbms_xplan.display_cursor(null,null,'allstats last')) ;

 

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

| Id  | Operation         | Name     | A-Rows |   A-Time   | Buffers | Reads  |

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

|   0 | SELECT STATEMENT  |          |      1 |00:00:45.47 |   23463 |  23447 |

|*  1 |  TABLE ACCESS FULL| TEST_TBL |      1 |00:00:45.47 |   23463 |  23447 |

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

 

Predicate Information (identified by operation id):

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

   1 - filter("IS_NUMBER"("VARCHAR_NUM")=0)

 


함수를 사용하면 너무 느리다

함수를 천만번 수행하는데 무려 45초 이상 걸렸다. 너무나 느려서 사용할 수 없는 수준이다. 함수를 빠르게 실행하기 위해서 Deterministic 형 함수로 수정해보자. Deterministic 함수는 Input 에 대한 Output 의 값이 항상 같을 때만 사용해야 한다. Deterministic 함수를 사용하면 같은 값의 Input이 여러 번 들어올 경우 한번만 수행할 수 있다. 하지만 Deterministic 함수도 비효율이 있다. Post 의 마지막에 Deterministic 함수 의 비효율과 관련된 Link를 표시하였으므로 반드시 읽어보기 바란다.

 

-- Deterministic 함수로 바꿈

CREATE OR REPLACE FUNCTION is_number(v_str_number IN varchar2)

RETURN NUMBER DETERMINISTIC IS 

이후 생략

/

DETERMINISTIC 함수를 사용해보자

alter system flush buffer_cache;

 

select /*+ gather_plan_statistics */ *

  from test_tbl a

 where is_number(varchar_num) = 0;

 

select * from table(dbms_xplan.display_cursor(null,null,'allstats last')) ;

 

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

| Id  | Operation         | Name     | A-Rows |   A-Time   | Buffers | Reads  |

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

|   0 | SELECT STATEMENT  |          |      1 |00:00:07.50 |   23463 |  23447 |

|*  1 |  TABLE ACCESS FULL| TEST_TBL |      1 |00:00:07.50 |   23463 |  23447 |

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

 

Predicate Information (identified by operation id):

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

   1 - filter("IS_NUMBER"("VARCHAR_NUM")=0)

 

대단한 성능향상이다. 수행시간이 45초 에서 7초로 줄어들었다. 하지만 여기서 멈출순 없다.

alter system flush buffer_cache;

 

select /*+ gather_plan_statistics */ *

  from test_tbl a

 where (select is_number(varchar_num) from dual) = 0;

 

select * from table(dbms_xplan.display_cursor(null,null,'allstats last')) ;

 

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

| Id  | Operation          | Name     | A-Rows |   A-Time   | Buffers | Reads  |

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

|   0 | SELECT STATEMENT   |          |      1 |00:00:03.00 |   23439 |  23433 |

|*  1 |  FILTER            |          |      1 |00:00:03.00 |   23439 |  23433 |

|   2 |   TABLE ACCESS FULL| TEST_TBL |     10M|00:00:00.01 |   23439 |  23433 |

|   3 |   FAST DUAL        |          |    101 |00:00:00.01 |       0 |      0 |

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

 

Predicate Information (identified by operation id):

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

   1 - filter(=0)  


함수사용시 스칼라서브쿼리를 활용하라
천만 건을 체크하는데 3초 밖에 걸리지 않았다. 함수 사용시 스칼라 서브쿼리를 사용하면 비효율 없이 함수 호출을 최소화 할 수 있다. Deterministic 함수든 아니든 상관없이 스칼라 서브쿼리의 효과는 동일하다. 그렇다면 함수 + 스칼라서브쿼리의 조합이 최선인가? 만약 일회성이 아닌 지속적으로 데이터를 체크해야 하는 경우라면 FBI(Function Based Index)를 생성해야 한다. 

 

create index idx_is_number on test_tbl (is_number(varchar_num)) ; -- FBI 생성

 

alter system flush buffer_cache;

 

select /*+ gather_plan_statistics index_rs(a idx_is_number) */ *

  from test_tbl a

 where is_number(varchar_num) = 0;

 

select * from table(dbms_xplan.display_cursor(null,null,'allstats last')) ; 

 

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

| Id  | Operation                   | Name          | A-Rows |   A-Time   | Buffers | Reads  |

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

|   0 | SELECT STATEMENT            |               |      1 |00:00:00.03 |       5 |      4 |

|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_TBL      |      1 |00:00:00.03 |       5 |      4 |

|*  2 |   INDEX RANGE SCAN          | IDX_IS_NUMBER |      1 |00:00:00.03 |       4 |      3 |

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

 

Predicate Information (identified by operation id):

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

   2 - access("A"."SYS_NC00003$"=0)

FBI 가 최적이다
Block I/O 수(Buffers 항목)를 비교해보라. 함수 + 스칼라 서브쿼리를 사용하는 것과 인덱스를 사용하는 것은 성능의 비교가 되지 않는다.  지속적으로 데이터를 검증해야 하고 테이블의 건수가 많지만 데이터를 체크하여 만족하지 않는 데이터의 건수가 적은 경우는 인덱스를 사용하는 것이 최적임을 알 수 있다
.

이제 내일은 is_date, is_number 함수와 관련된 문제로 필자에게 전화가 오지는 않으리라 믿는다.^^

관련 Post :
http://ukja.tistory.com/159
http://adap.tistory.com/entry/Deterministic-의-진실Multi-buffer



신고
Posted by extremedb

댓글을 달아 주세요

  1. Favicon of http://blog.naver.com/gseducation BlogIcon Eddy 2009.11.11 16:05 신고  댓글주소  수정/삭제  댓글쓰기

    와우! 멋진 전개입니다.

  2. newyemac 2009.11.11 20:12 신고  댓글주소  수정/삭제  댓글쓰기

    와우 !.. 놀랍네요.... 감사합니다.

  3. feelie 2009.11.12 09:21 신고  댓글주소  수정/삭제  댓글쓰기

    언젠가 이런 경우가 있었는데..
    이런 해결책이 있었네요.
    select 리스트에 컬럼 대신에 쿼리를 사용하는 경우 스칼라 서버쿼리라고 정의하고 있었는데
    조건절에서 사용되는 경우도 있네요..

    감사합니다.

  4. 혈기린 2009.11.12 11:06 신고  댓글주소  수정/삭제  댓글쓰기

    관련 Post도 재미나는군요 오라킹 님의 Deterministic 사용할때 오라클의 읽기일관성 모델때문에 다른결과값이 나올수 있으니 사용시 주의하라는점 좋은 내용입니다

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

      말씀하신것 처럼 Function 내에서 테이블을 Select 하는 경우는 Deterministic을 적용하면 안될것입니다. Read Only Tablespace 로 바꾼다면 가능할지도....

  5. 바인드 2009.12.29 12:11 신고  댓글주소  수정/삭제  댓글쓰기

    안녕하세요.
    오라클을 공부하는데 참으로 많은 도움이 되었습니다!
    이런 멋진 글이...늦었지만 감사드립니다!^^
    자주 들러서 배우는데 ...
    ㅎ_ㅎ 고맙습니다!!! (글쓰기 누르면 로그인하라고;; 해서 매번 못써서 댓글에 글 답니다~)
    새해 복 많이 받으세요~~

  6. 분당냐옹 2014.04.22 10:50 신고  댓글주소  수정/삭제  댓글쓰기

    너무너무 재밌네요~
    해보니 똑같이 시간이 줄어듭니다.
    신기합니다 감사합니다! 자료좀 퍼갈게요~

상식이 발목을 잡다
SQL
튜닝을 해본 사람들은 "같은 테이블을 반복해서 사용하지 말라" 라는 보편화된 상식을 가지고 있다. 위와 같은 말들은 상창력(상상력 + 창의력)이 필요한 튜닝의 세계에서 오히려 그것을 발휘하지 못하게 발목을 잡을 수 있다. 상식을 비판 없이 따르는 것이 얼마나 위험한지 증명해보자.

 

환경 Oracle 11.1.0.6


--테스트를 위한 회원탈퇴고객 테이블 생성

CREATE TABLE SH.WITHDRAWAL_CUST AS
SELECT * FROM sh.CUSTOMERS WHERE rownum <= 5000;

 

--UNIQUE INDEX 생성

CREATE UNIQUE INDEX SH.PK_WITHDRAWAL_CUST ON SH.WITHDRAWAL_CUST (CUST_ID);

 

--SALES 테이블에 PROD_ID를 선두로 하는 결합인덱스 생성

CREATE INDEX SH.SALES_IDX_02 ON SH.SALES (PROD_ID, CUST_ID);

 

dbms_stats.gather_table_stats('SH','SALES',cascade=>true);

 

dbms_stats.gather_table_stats('SH','WITHDRAWAL_CUST',cascade=>true); 

 

후행테이블에서 건수가 줄어드는 경우

회원탈퇴고객 테이블(WITHDRAWAL_CUST)을 생성 하였고 SALES 테이블에 결합인덱스를 생성 하였다. 이제 모든 준비가 끝났으므로 상품번호 144을 구입한 고객 중 회원탈퇴고객을 조회해 보자.  

 

SELECT /*+ GATHER_PLAN_STATISTICS FIRST_ROWS(1) */

             s.cust_id, w.cust_first_name, w.cust_last_name,

             s.prod_id,  s.time_id, s.channel_id, s.quantity_sold

   FROM sh.sales s, sh.withdrawal_cust w

 WHERE s.prod_id = 144

      AND s.cust_id = w.cust_id ; 

  

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST')) ;  



상식적인 수준에서 SQL이 작성되었다. 아래의 Plan을 보자.  


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

| Id  | Operation                            | Name               | A-Rows |   A-Time   | Buffers |

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

|   1 |  NESTED LOOPS                        |                    |    423 |00:00:00.11 |    3708 |

|   2 |   NESTED LOOPS                       |                    |    423 |00:00:00.11 |    3285 |

|   3 |    TABLE ACCESS BY GLOBAL INDEX ROWID| SALES              |   4091 |00:00:00.07 |    3209 |

|*  4 |     INDEX RANGE SCAN                 | SALES_IDX_02       |   4091 |00:00:00.01 |      17 |

|*  5 |    INDEX UNIQUE SCAN                 | PK_WITHDRAWAL_CUST |    423 |00:00:00.02 |      76 |

|   6 |   TABLE ACCESS BY INDEX ROWID        | WITHDRAWAL_CUST    |    423 |00:00:00.01 |     423 |

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

 

Predicate Information (identified by operation id):

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

   4 - access("S"."PROD_ID"=144)

   5 - access("S"."CUST_ID"="W"."CUST_ID")


최적의 SQL인가?  

Plan을 보면 SALES 테이블(테이블 건수: 92만건) SALES_IDX_02 인덱스를 이용하여 4091건을 Select 하였고 연이어 SALES 테이블을 같은 건수만큼 Select 하였다. 그런 후에 WITHDRAWAL_CUST 테이블의 Unque 인덱스를 엑세스 하여 건수가 423 건으로 거의 10분의 1 수준으로 감소하였다. 건수가 줄어드는 이유는 전체고객에 비하여 회원탈퇴고객이 많지 않은 까닭이다. 많은 사람들이 위의 Plan은 현재 상태에서 최적이라고 생각할 것이다. 아래의 SQL은 결과는 같지만 SALES 테이블을 2번 사용하는 예제이다.


SELECT /*+ GATHER_PLAN_STATISTICS FIRST_ROWS(1) LEADING(S1 W S) */

             s.cust_id, w.cust_first_name, w.cust_last_name,

             s.prod_id,  s.time_id, s.channel_id, s.quantity_sold

  FROM sh.sales s1, sh.withdrawal_cust w, sh.sales s

 WHERE s1.prod_id = 144

     AND s1.cust_id = w.cust_id

     AND s1.rowid = s.rowid ;   


SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST')) ; 


위의 SQL을 보면 SALES 테이블을 두 번 사용하였다. 상식 대로라면 같은 테이블의 중복사용으로 Read 한 Block  개수(Buffers 항목)가 원본 SQL보다 증가하여야 한다. 과연 그런가? 아래의 Plan을 보자.

  

--------------------------------------------------------------------------------------------
| Id  | Operation                     | Name               | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------
|   1 |  NESTED LOOPS                 |                    |    423 |00:00:00.08 |     842 |
|   2 |   NESTED LOOPS                |                    |    423 |00:00:00.07 |     516 |
|*  3 |    INDEX RANGE SCAN           | SALES_IDX_02       |   4091 |00:00:00.02 |      17 |
|   4 |    TABLE ACCESS BY INDEX ROWID| WITHDRAWAL_CUST    |    423 |00:00:00.04 |     499 |
|*  5 |     INDEX UNIQUE SCAN         | PK_WITHDRAWAL_CUST |    423 |00:00:00.01 |      76 |
|   6 |   TABLE ACCESS BY USER ROWID  | SALES              |    423 |00:00:00.01 |     326 |
--------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("S1"."PROD_ID"=144)
   5 - access("S1"."CUST_ID"="W"."CUST_ID")

 

오히려 성능이 향상 되었다
원본 SQL에 비하여 Block
I/O 개수가 4배 이상 줄어들었다. 대단하지 않은가? ID 기준으로 3 에서
SALES 테이블의 SALES_IDX_02 인덱스만 엑세스 하고 SALES 테이블(Alias 로는 S1)은 엑세스 하지않는다. 연이어 WITHDRAWAL_CUST 테이블과 조인함으로써 건수를 423으로 줄여 버린다. 건수를 10분의 1로 줄인 후에 마지막으로 Rowid를 이용하여 SALES 테이블(Alias 로는 S)에 S1의 Rowid로 엑세스 함으로서 성능향상이 가능한 것이다.

 

언제 같은 테이블을 두번 사용해야 하나?
Nested Loop 조인을 사용할때 선행 테이블의 인덱스 Scan후 곧장 선행 테이블로 엑세스 할때 Filter 가 없거나 Filter의 효과가 약하여 건수를 줄여주지 못하고 오히려 후행 테이블에서 건수를 획기적으로 줄여주는 경우가 있다. 이런 경우는 예외없이 같은 테이블을 2번 사용하여야 한다. 물론 Rowid를 이용해야 하는것은 당연하다. 여러분도 SQL 튜닝시 이런 상황을 많이 만났을 것이고 앞으로도 자주 만나게 될것이다. 이런 상황에서는 꼭 위의 방법을 사용하기 바란다.

 

지식의 저주

상창력을 이용해야 하는 경우는 상식이 오히려 방해가 될 수 있다. 이것은 지식의 저주라 불린다. 경험이 많거나 아는것이 많을수록 지식의 저주에 걸리게 될 확률은 높아진다. 누구라도 이런것이 조금씩은 있기 마련이다. 하지만 이런 종류의 저주를 많이 혹은 오래 받은 사람은 발전이 없거나 더디게 마련이다.

 
회피하기
지식의 저주를 받지 않은 사람은 역사를 바꾸기도 하였다. 갈릴레이의 지동설, 다윈의 진화론, 에디슨의 여러가지 발명품 등이 그것이다. 발상을 전환하기, 생각을 뒤집어 보기, 다른 방법으로 접근하기, 의도적으로 엉뚱한 생각하기, 동료와 BrainStorming 하기 등등 저주를 피할 방법은 많다. 이 개념을 SQL튜닝에만 한정시키면 곤란하다. 모든 분야에 사용할수 있다. 누가 알겠는가? 이러한 방법을 사용하여 여러분이 역사를 바꿀지……


PS
이렇게 상식을 뒤집는 케이스는 생각보다 많다. 아래에 링크된 Post 가 그런 경우이다.

Transformer - SQL 튜닝의 새로운 패러다임

Top SQL-( Rownum 정확한 사용법 )

위의 Link는 필자가 눈으로 확인한 것만 링크로 나타내었다. 실제로는 이것보다 훨씬 많을 것이다.


신고
Posted by extremedb

댓글을 달아 주세요

  1. feelie 2009.11.05 09:21 신고  댓글주소  수정/삭제  댓글쓰기

    발상의 전환이이라 무척 힘든 얘기네요..
    테이블을 두번읽는것이 이런 이점이 있다고는 생각하지 못했습니다.
    항상 새로운 내용에 감탄합니다.

  2. 혈기린 2009.11.05 11:11 신고  댓글주소  수정/삭제  댓글쓰기

    멋진발상의 전환이네요 같은테이블을 읽지마라 이건 sql작성할때 꼭지키기 위해 노력하던 문구였는데 그 고정관념이 파괴되는 발상의 전환이네요

    항상 좋은글 감사 드립니다 ^^

  3. 백면서생 2009.11.05 14:35 신고  댓글주소  수정/삭제  댓글쓰기

    feelie,혈기린님 말씀 처럼 "같은 테이블(정확히 말하면 한번 읽은것이겠죠)을 반복해서 읽지 마라"는 일반적인 sql에서는 거의 변하지 않는 튜닝관점이라 생각이 듭니다.(물론 dw,mart에서는 반복해서 읽는게 성능-group by사용등등-에 엄청난 도움이 되기도 하지만요^^)
    하지만 우리가 위의 말을 "같은 테이블을 from절에 반복해서 사용하지 마라"고 오해를 해서 그런게 아닌가 생각이 듭니다.
    extremedb님이 올리신 sql도 from 절에 반복해서 사용은 되지만 반복해서 읽는 것은 아니니까요.^^


    오랜만에 들렀는데 너무 좋은 글들이 많네요.
    extremdb님의 수고에 늘 감사 드립니다.^^

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2009.11.05 15:03 신고  댓글주소  수정/삭제

      이글의 본래의도는 Nested Loop 조인을 사용할때 선행 집합처리를 완료하고 후행집합을 처리해야 한다는 고정관념을 깨는 것이었습니다. "후행집합의 처리가 완료되고 선행 테이블로 엑세스 할수 있다." 이런 의미였습니다. 그런데 백면서생님이 또다른 관점으로 지적을 잘해주셨습니다. "한번 scan한 블럭을 다시 scan 하지마라." 이런 의미인 것으로 판단 되네요.

  4. Favicon of http://dbguy.tistory.com BlogIcon 호야 2009.11.05 19:51 신고  댓글주소  수정/삭제  댓글쓰기

    정말 몸에 배어나고 머리속에 깊숙이 박혀있는 테이블 한번만 읽어서 처리하기를

    이번 프로젝트에서 테이블 한번만 읽어서 처리한다는 상식을 깨지는 경험을 했습니다.

    sort 비용보다 테이블을 두번 읽어서 처리하는 비용이 더 싸다는것을 .. (모든 경우는 아닐것 같지만.. )

  5. Favicon of http://yangrak.tistory.com BlogIcon Yangrak Im 2009.11.05 21:07 신고  댓글주소  수정/삭제  댓글쓰기

    멋진 말이네요 ㅋ '모든 상식을 의심해라'

  6. hyun 2009.11.06 08:24 신고  댓글주소  수정/삭제  댓글쓰기

    정말 멋진 발상의 전환이군요.
    상식이 상상력을 방해한 것이 아니라 상식안에 숨어 있는 고정관념을 깬 것이군요.
    from 절에 테이블을 두번 쓴 것일 뿐
    실제 테이블을 두번 읽은 것은 아니네요...
    우린 from 절에 테이블을 두번쓰면 두번 읽는다고 생각하게 되니까....

    좋은 글 잘 읽었습니다.

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2009.11.06 21:20 신고  댓글주소  수정/삭제

      "상식이 상상력을 방해한 것이 아니라 상식안에 숨어 있는 고정관념을 깬 것" 이라는 말에 공감합니다.
      다른말로 나타내면 "사과가 익으면 떨어진다" 라는 말을 뉴튼은 "사과가 떨어진것이 아니라 지구가 끌어당긴것" 이라고 생각했지요. 이것도 숨어있는 고정관념을 깬것이라 할수 있죠.

  7. Favicon of http://http://blog.naver.com/amadeus__ BlogIcon baekihwan 2010.02.04 09:17 신고  댓글주소  수정/삭제  댓글쓰기

    좋은 글 잘 읽었습니다.
    선생님의 블로그를 rss로 걸어 놓고 편히(?) 받아 보기만 하다가 이렇게 글 올립니다.

    올려 주시는 거의 모든 글들이 저한테는 정말 많은 도움이 되고 있습니다.
    감사합니다.

  8. Favicon of http://1ststreet.tistory.com BlogIcon SITD 2011.04.06 13:33 신고  댓글주소  수정/삭제  댓글쓰기

    상식이란게 참 무서운거 같습니다.
    생각이 굳혀 버리니까요.
    좋은 글 감사드립니다 ^^
    자주자주 써먹어 봐야겠어요