질문을 받다
독자로 부터 다음과 같은 질문을 받았다. "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
,

일부 스크립트에 오타가 있어 수정했음을 알립니다.(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
,

지인에게서 전화가 오다
지인 : 데이터를 체크해야 하는데 오라클에 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



'Oracle > PL/SQL Pattern' 카테고리의 다른 글

PL/SQL-면접문제  (808) 2010.05.07
묵시적인 형변환을 피하라  (414) 2008.05.09
PL/SQL 에서 NUMBER 타입의 성능 테스트  (0) 2008.05.02
Posted by extremedb
,

상식이 발목을 잡다
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
,