Interleaving : CBQT중에는 선수조건이 필요한 경우가 있다

Oracle/Optimizer 2009. 12. 7. 00:01

이번 내용은 난위도가 있으므로  'Interleaving은 선수조건이 필요하다' 단락을 읽고 이해가 가는 사람만 보기 바란다. 필자의 글을 꾸준히 구독한 독자라면 어렵지 않게 볼수 있을 것이다.

전략적 결혼
사극을 보면 권력가들이 자신의 세력를 확장하기 위해 자신의 딸을 왕에게 추천하여 결혼시키는 것을 심심치 않게 볼수 있다. 자신의 목표를 이루기 위한 전단계의 포석이라고 할수 있다. 딸이 왕자를 순산하고 그 왕자가 차세대 왕이 된다면 자신은 최고의 권력을 가지는것과 마찬가지 이다. CBQT가 수행될 때에도 이러한 전략적 결혼과 같은것이 존재한다는 사실을 알고 있는가? CBQT를 최적화 하기 위해서 Transformation 간의 관계는 필수적이다. 오라클에서는 Interleaving이라는 기법을 사용하고 있다.

Interleaving은 선수조건이 필요하다
하나의 Transformation(T2) 을 수행하기 위해서 다른 Transformation(T1)이 반드시 먼저 실행되어야 하는 경우가 있다. 이럴 때 Interleaving 기법을 이용하여 T1이 수행되면 마치 기다렸다는 듯이 T2가 실행된다. 대표적인 경우가 CSU(Complex Subquery Unnesting)가 실행되면 연이어 CVM(Complex View Merging)혹은 JPPD(Join Predicate Push Down)가 실행되는 경우이다.

서브쿼리가 Unnesting 되면 초기상태는 인라인뷰로 생성된다. 인라인뷰를 제거하고 정상적인 조인으로 바꾸는 기능이 CVM 이라는 것은 이미 알것이다. 즉 CVM을 시작하기 위해서는 CSU라는 전단계의 포석이 반드시 이루어 져야만 한다. 이 규칙은 JPPD에도 똑같이 적용된다.

숫자표기법의 이해
Interleaving 에 대한 10053 Trace 내용을 분석하려면 숫자 표기법을 알아야 한다. 예를 들어 Complex Subquery가 하나 있는 SQL은 아래와 같이 0 과 1 로 Unnesting 상태를 나타낼수 있다.

CSU(1) : 서브쿼리가 Unnesting 됨
CSU(0) : 서브쿼리가 Unnesting 되지 않음.

만약 서브쿼리가 2개(SUBQ2, SUBQ1)라면 아래처럼 표현된다.
CSU(1,1) : 모든 서브쿼리가 Unnesting 됨
CSU(1,0) : SUBQ2만 Unnesting 됨
CSU(0,1) : SUBQ1만 Unnesting 됨
CSU(0,0) : 모든 서브쿼리가 Unnesting 되지 않음.

이러한 표기법은 CSU 뿐만 아니라 CVM, JPPD 등에도 똑같이 적용되며 10053 Trace에서 자주 나타나므로 반드시 알아두어야 한다.

Interleaving의 용도
Interleaving은 주로 CSU 수행시의 비용계산 오류를 줄이는 용도로 사용한다. 예를 들어 CSU(0)이 최저 Cost로 선택되었다면 JPPD의 입장에서는 전혀 다른 결과를 가져올 수 있다. CSU(0)이 아닌 CSU(1) 상태에서 JPPD을 적용하는 것이 최저 Cost가 될 수 있다. 따라서 Interleaving은 CBQT간에 최저 Cost를 구하기 위해 대화를 하는 기능이라고 할 수 있다. Interleaving 기능이 존재함으로써 CSU + CVM(혹은 JPPD)을 모두 고려한 최적의 Cost를 구 수 있다. 이제 아래의 SQL을 보자.

SELECT /*+ QB_NAME(MAIN_VIEW) */ 

       e1.employee_id, e1.manager_id, e1.salary

  FROM employee e1

 WHERE e1.department_id = 10

     and (e1.manager_id, e1.salary) in (select /*+ QB_NAME(SUBQ) */

                                             e2.manager_id,  max(e2.salary) 

                                        from employee e2

                                       group by e2.manager_id )

     and rownum = 1

 

위의 SQL에 해당하는 10053 Trace의 내용을 미리 예상해보자. 먼저 서브쿼리에 Group By를 사용하였으므로 CSU가 발생할 것이다. 연이어 CSU의 과정 중에 Interleaving 이 발생하여 CVM및 JPPD의 Cost가 같이 고려될 것이다. 하지만 위의 SQL은 조건절에 Rownum을 사용하였으므로 CVM이 발생할 수 없다. 따라서 JPPD만 고려될 것이다. 이제 우리가 예상한 내용이 맞는지 확인 해보자. 아래의 Trace 내용은 필요한 부분만 발췌하여 요약한 것이다.

 

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

Cost-Based Subquery Unnesting
*****************************
SU: Using search type: exhaustive

SU: Starting iteration 1, state space = (2) : (1)

  Cost: 6.0035  Degree: 1  Card: 1.0000  Bytes: 41

 

SU: Considering interleaved complex view merging

CVM:     CVM bypassed: Outer QBc referencing ROWNUM.

  Cost: 5.0035  Degree: 1  Card: 1.0000  Bytes: 41

SU: Interleaved cost better than best so far.
SU: Finished interleaved complex view merging

SU: Considering interleaved join pred push down

JPPD: Using search type: linear

JPPD: Considering join predicate push-down

JPPD: Starting iteration 1, state space = (2) : (0)

  Cost: 6.0035  Degree: 1  Card: 1.0000  Bytes: 41

 

JPPD: Starting iteration 2, state space = (2) : (1)

  Cost: 4.0010  Degree: 1  Card: 1.0000  Bytes: 30

 

JPPD: Selected interleaved query.

SU: Finished interleaved join pred push down

SU: Updated best state, Cost = 4.00

 

SU: Starting iteration 2, state space = (2) : (0)

  Cost: 6.0037  Degree: 1  Card: 1.0000  Bytes: 15

 

SU: Not update best state, Cost = 6.00

SU: Will unnest subquery SUBQ (#2)

SU: Reconstructing original query from best state.


우리의 예상대로 먼저 CSU가 발생되었다. 연이어 Interleaving 기능에 의해 CVM이 고려되고 있지만 Rownum 제약사항 때문에 CVM이 발생되지 못한다.(CVM bypassed 부분 참조) 하지만 불완전 Costing(CVM을 제외한 최적화)은 계속 진행된다.

CVM과 JPPD 는 동시에 고려된다
CVM 과정이 끝나면 JPPD의 Costing 과정이 진행된다. Interleaving이 CVM뿐만 아니라 JPPD도 동시에 고려하고 있음을 알 수 있다. JPPD를 적용한 Iteration(JPPD: Starting iteration 2, state space = (2) : (1) 부분 참조) 의 Cost가 4.0010으로 최적임을 알 수 있다. 이로서 모든 Interleaving이 완료되었다.( SU: Finished interleaved join pred push down 부분 참조) 마지막으로 CSU를 수행하지 않는 Iteration의 Cost를 구하고 모든 CSU과정을 마치게 된다.

CSU의 최저 Cost는 CSU + CVM + JPPD를 모두 고려한것
한가지 주의해야 할 사항은 4.0010은 JPPD의 Cost가 되는 것이 아니라 CSU의 Cost 이다. SU: Updated best state, Cost = 4.00 부분을 보면 이러한 사실을 알 수 있다. 즉 CSU의 Cost는 단순히 CSU 자체의 Cost만 고려하는 것이 아니라 CSU + CVM + JPPD를 모두 고려한 후 최적의 Cost를 갖는 경우를 선택하는 것을 알 수 있다. Interleaving 기능으로 인하여 더욱 정확한 Cost를 구하는 것이 가능한 것이다.

PS
1)위의 내용은 내년초 출간될 책의 일부분을 발췌한 것이다.
2)아이러니 하게도 맨처음에 설명한 전략적 결혼과 같은 비근대적인 방법을 21세기의 우리사회에서도 가끔 볼수 있다.


'Oracle > Optimizer' 카테고리의 다른 글

Index Unque Scan은 SQL을 변경시킨다  (6) 2010.01.20
내가 사용한 Hint 가 무시되는 이유  (10) 2010.01.04
MERGE 문과 IN 조건이 만난다면  (8) 2009.11.26
Transformer - SQL 튜닝의 새로운 패러다임  (11) 2009.10.15
Temp Table Transformation  (5) 2009.09.28
Posted by extremedb
,

MERGE 문과 IN 조건이 만난다면

Oracle/Optimizer 2009. 11. 26. 14:22

질문을 받다
독자로 부터 다음과 같은 질문을 받았다. "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를 주제로 하는 서적은 없다. 다시 말하면 우리는 튜닝을 하지 못할 환경에 살고 있다.


'Oracle > Optimizer' 카테고리의 다른 글

내가 사용한 Hint 가 무시되는 이유  (10) 2010.01.04
Interleaving : CBQT중에는 선수조건이 필요한 경우가 있다  (14) 2009.12.07
Transformer - SQL 튜닝의 새로운 패러다임  (11) 2009.10.15
Temp Table Transformation  (5) 2009.09.28
스칼라 서브쿼리를 서브쿼리로 변환하라  (11) 2009.09.10
Posted by extremedb
,

유명한 Function Based Index 의 버그

Oracle/TroubleShooting 2009. 11. 19. 14:53

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

'Oracle > TroubleShooting' 카테고리의 다른 글

COPY_T를 대신하여 Connect By 문을 사용하는가?  (11) 2010.03.05
한방 Query를 사용하지 말아야 할 때  (17) 2010.02.19
SQL 튜닝이 불가한 경우  (12) 2009.09.16
천당과 지옥의 차이  (3) 2009.06.17
Sequence 의 오작동 (무조건 채번되는 Sequence)  (0) 2009.02.19
Posted by extremedb
,

오라클에서 is_number, is_date 함수 사용하기

Oracle/PL/SQL Pattern 2009. 11. 11. 21:36

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

모든 상식을 의심하라

Oracle/Data Access Pattern 2009. 11. 5. 00:01

상식이 발목을 잡다
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는 필자가 눈으로 확인한 것만 링크로 나타내었다. 실제로는 이것보다 훨씬 많을 것이다.


'Oracle > Data Access Pattern' 카테고리의 다른 글

오렌지나 TOAD에서 Predicate Information을 참조하는 방법  (7) 2010.01.12
Partition Access Pattern  (13) 2009.12.24
Oracle Data Access Pattern을 정복하라  (18) 2009.10.29
Oracle11g 에서 Full Table Scan의 성능 향상  (8) 2009.07.22
InList / Concatnation / Range Scan Control 하기  (4) 2008.12.12
Posted by extremedb
,

Oracle Data Access Pattern을 정복하라

Oracle/Data Access Pattern 2009. 10. 29. 00:01

첨부파일의 일부 인덱스명에 오타가 있으니 댓글을 참조하기 바람(2009.11.05)

SQL 튜닝을 할 때 가장 기본적으로 익혀야 하는 것을 꼽으라면 무엇일까?

첫 번째, Data Access Pattern

두 번째, Join Method

위의 두 가지는 기본 중에 기본이다. 이 두 가지는 너무도 중요하기 때문에 튜닝을 시작하는 사람 혹은 튜너를 지망하는 사람들은 보고 또 보아야 한다. 실제로 이두가지를 모두 정복한다면 SQL 튜닝중의 많은 부분을 커버할수 있다. 이것은 비단 오라클 SQL 튜닝만이 아니라 모든 DBMS가 동일 할 것이다.

오늘은 두가지 중에 첫 번째의 Data Access Pattern의 개념에 대해서 이야기 해보자.

 

Data Access Pattern을 최대한 간단하게 표현하면 아래와 같다.

 

Index Scan       : 인덱스 사용

Full Table Scan  : 인덱스 미사용

Rowid            : Rowid로 테이블 엑세스

 

하지만 위의 3가지는 너무나 추상적이다.
위의 3가지를 좀더 상세히 나타내 보자.


Rowid

Index Unique Scan    

Index Range Scan

Index Inlist Iterator

Index Skip Scan 

Index Full Scan

Index Fast Full Scan 

Bit Map Index Combination

Full Table Scan        

 

3가지가 9가지가 되었다. 이제 제법 실전의 Plan에서 볼 수 있는 모습이 나왔다고 생각할 것이다.

하지만 아직 많이 부족하다.
실전의 Plan에는 위의 모습 보다 더욱 상세한 것이 필요하기 때문이다.

 

Rowid

Index Unique Scan    

Index Range Scan

Index Range Scan Descending

Index Range Scan (min/max)    

Index Inlist Iterator

Index Inlist Iterator Descending

Index Skip Scan 

Index Skip Scan Descending

Index Full Scan

Index Full Scan Descending 

Index Full Scan (min/max)   

Index Fast Full Scan 

BIT MAP OR           

BIT MAP AND          

BIT MAP MINUS        

BIT MAP MERGE

Full Table Scan        

 

드디어 3가지가 18가지가 되었고 실전적인 모습이 되었다.

위의 18가지 중에 한가지라도 찜찜한것이 있으면 첨부파일을 다운받아서 개념을 익히길 바란다.
파일을 배포 할 경우  출처를 밝혀주기 바란다.
파일 3개를 모두 다운 받은후 압축을 풀면 된다.

사용자 삽입 이미지

사용자 삽입 이미지

invalid-file
invalid-file
invalid-file


PS :
여기에 나오지 않은것들이 몇가지 있다. Partition Access Pattern은 이문서에서 언급되지 않았다.
다음 기회에..
Index Join 은 Data Access Pattern에 속하는 것이 아니고 Join method 에 포함되어야 한다.

'Oracle > Data Access Pattern' 카테고리의 다른 글

Partition Access Pattern  (13) 2009.12.24
모든 상식을 의심하라  (16) 2009.11.05
Oracle11g 에서 Full Table Scan의 성능 향상  (8) 2009.07.22
InList / Concatnation / Range Scan Control 하기  (4) 2008.12.12
Range Partition 적용 테이블의 MIN/MAX 처리시의 성능저하 현상  (2) 2008.07.28
Posted by extremedb
,

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

Oracle/Optimizer 2009. 10. 15. 16:37

“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

…이후생략

 

결론

이제 격언이나 명언이라고 생각되는 말들도 상황을 가려서 해야 한다는 것을 알겠는가?. 그렇지 않으면 똑똑한 개발자에게 오히려 다음과 같은 말을 들을 것이다. “지금 말씀 하신 것은 예전 이야기 입니다. 요즘은 트랜스포머가 알아서 해줍니다.”  적어도 튜닝의 세계에서는 그렇다.

 

새로운 패러다임

JF는 Transformer가 수행하는 SQL튜닝의 하나일 뿐이다. Oracle11g R2 기준으로 SQL 튜닝(Query Transformation)의 종류는 필자의 짧은 지식으로도 70개 이상일 것으로 판단 된다. Oracle이 발전해 가면서 SQL튜닝은 사람이 관여하는 것에서 오라클이 자동으로 SQL을 변경해주는 것으로 많은 부분이 바뀌었고 앞으로 이런 추세는 점점 강화될 것이다. Query Transformation 은 단순한 Optimizer의 기능이 아니라 SQL 튜닝의 새로운 패러다임인 것이다. 이제는 직접 튜닝 하는 것에서 벗어나 Transformer가 실수하는 경우 새로운 길을 열어주는 것이 튜너가 가야할 길이 아닌가?


'Oracle > Optimizer' 카테고리의 다른 글

Interleaving : CBQT중에는 선수조건이 필요한 경우가 있다  (14) 2009.12.07
MERGE 문과 IN 조건이 만난다면  (8) 2009.11.26
Temp Table Transformation  (5) 2009.09.28
스칼라 서브쿼리를 서브쿼리로 변환하라  (11) 2009.09.10
Semi Join 의 재조명  (5) 2009.08.31
Posted by extremedb
,

오라클 Regular Expressions 완전정복

Oracle/SQL Pattern 2009. 10. 7. 00:01
오라클의 Regular Expressions을 정의 하면 아래와 같다
  • 유닉스의 정규식과 같음.
  • 강력한 Text 분석도구로서 Like 의 한계를 극복함.
  • Pattern-Matching-Rule의 정의가 자유로움.
  • 다양한 메타문자 제공.
Regular Expressions의 중요성은 아래와 같은 작업시 매우 증대된다
When
•
  • ETL/전환/이행.
  • Data Mining.
  • Data Cleansing.
  • 데이터 검증.
Regular Expressions의 기능은 다음과 같다
What
•
  • Text에서 특정 중복 단어의 확인
  • 특별한 상태에서 공백의 제거
  • 특정 문자의 파싱(parsing)
  • Text 에서 전화 번호, 우편 번호, 이메일 주소, 주민등록번호, IP 주소, 파일 이름, 경로 이름 등을 검증 및 추출이 가능
  • HTML 태그, 숫자, 날짜, 기타 특정 텍스트 데이터와 일치하는 패턴을 확인하고 다른 패턴으로 대체하는 것이 가능
  • Constraints 로 사용가능
이렇게 중요한 정규식(Regular Expressions)을 잘 모르거나 익숙치 못한 사람들을 위하여 개인적으로 정리한 파일을 올리니 이번기회에 반드시 정복하길 바란다.  의외로 고급 개발자들 중에 Regular Expressions의 사용을 꺼려하는 사람들이 많이 있는것 같다. 이 기능을 써야하는 경우임에도 쓰지 않는다면 코드는 길어질 것이며 유지보수 또한 힘들어 질것이다.
 
첨부파일에 정리된 내용은 다음과 같다 
  • 정규식 기본 Syntax.
  • 함수 사용법.
  • 정규식 고급 Syntax.
  • 11g New Features

주의 사항 :

  • 첨부된 파일에는 Oracle11g R1 까지의 내용을 정리한 것임.
  • 배포시에는 반드시 출처를 밝힐것.
  • Upload 시 200K 용량제한 때문에 파일을 2개로 분할 압축 하였으므로 모두 다운 받은후 압축을 풀면 됨.

invalid-file

오라클 Regular Expresssions 완전정복1





invalid-file

오라클 Regular Expresssions 완전정복2




 
사용자 삽입 이미지



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

검색조건을 동적으로 바꾸어야 할 때  (1) 2010.12.14
Oracle Analytic Function의 모든 것  (20) 2010.03.29
간단한 집합개념 Test  (7) 2009.07.06
스칼라 서브쿼리에서 Multi Column, Multi Row Return 하기.  (7) 2009.06.29
Model 절에 대하여  (10) 2009.05.28
Posted by extremedb
,

볼륨매니저는 역사 속으로

Oracle/News 2009. 9. 30. 10:55

추가적인 정보가 있으니 댓글을 반드시 확인하세요.(2009.10.01)

이젠 Oracle11g R2의 시대
2010년도부터 본격적으로 프로젝트시 Oracle11g R2를 선택하게 될 것이다. Oracle11g R2의 장점은 이미 많이 알려져 있다. 신기능은 수도 없이 많지만 대표적인 것들을 소개하면 아래와 같다.

Top 10 Oracle 11gR2 New Features

Edition-Based Redefinition

 

DVM은 필수다
이러한 장점에도 불구하고 치명적일 수 있는 점을 소개한다. 그것은 볼륨매니저를 더 이상 사용할 수 없다는 것이다. Oracle11g R2부터 RAC를 사용할 경우 DVM(ASM Dynamic Volume Manager)을 반드시 사용해야만 한다. 기존의 볼륨매니저를 사용할 수 있는 방법이 있는데 그것은 Oracle11g R2를 바로 설치하는 대신에 Oracle11g R1 에서 Oracle11g R2로 업그레이드하는 방법이 있다. 하지만 이것은 어디까지나 트릭이며 추천할 것은 못 된다.

 

기존의 베리타스등의 Third Party 볼륨매니저는 버려야 하나?

이것은 일종의 끼워팔기 인가?
이젠 Raw Device 대신 ACFS(ASM Cluster File System)을 사용해야 하나?

어찌되었건 내년부터 본격적인 ASM의 시대가 될것이다.



'Oracle > News' 카테고리의 다른 글

오라클은 몇년이나 갈까?  (4) 2009.07.31
Science of DataBase 1주년  (22) 2009.07.02
오라클 공룡기업으로 거듭나는가?  (0) 2009.04.21
Posted by extremedb
,

Temp Table Transformation

Oracle/Optimizer 2009. 9. 28. 18:09

오라클 Transformer 는 때때로 반복작업이 일어날경우 같은 테이블에 대한 부하가 심해지므로 TEMP 테이블을 만들어서 저장하고 그 테이블을 이용하여 반복적인 작업을 하게되는 경우가 있다. 대표적인 경우가 GROUPING SETS 를 사용하는 경우이다.

SELECT department_id, JOB_ID, AVG (salary) avg_sal

  FROM employee a

GROUP BY GROUPING SETS(department_id, JOB_ID)   ;

 

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

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

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

| 0   | SELECT STATEMENT            |                          |       |    11 |           |

| 1   |  TEMP TABLE TRANSFORMATION  |                          |       |       |           |

| 2   |   LOAD AS SELECT            |                          |       |       |           |

| 3   |    TABLE ACCESS FULL        | EMPLOYEE                 |   107 |     3 |  00:00:01 |

| 4   |   LOAD AS SELECT            |                          |       |       |           |

| 5   |    HASH GROUP BY            |                          |     1 |     3 |  00:00:01 |

| 6   |     TABLE ACCESS FULL       | SYS_TEMP_0FD9D6608_434CFB|     1 |     2 |  00:00:01 |

| 7   |   LOAD AS SELECT            |                          |       |       |           |

| 8   |    HASH GROUP BY            |                          |     1 |     3 |  00:00:01 |

| 9   |     TABLE ACCESS FULL       | SYS_TEMP_0FD9D6608_434CFB|     1 |     2 |  00:00:01 |

| 10  |   VIEW                      |                          |     1 |     2 |  00:00:01 |

| 11  |    TABLE ACCESS FULL        | SYS_TEMP_0FD9D6609_434CFB|     1 |     2 |  00:00:01 |

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

 

위의 PLAN 을 보면 먼저 ID 기준으로 3번에서 employee 테이블을 읽어서 필요한 컬럼만 TEMP 테이블에 저장해두고 그테이블을 반복해서 이용(ID 기준으로 6번, 9번, 11번)하게 되는것이다. 그렇다면 왜 3번이나 반복해서 temp 테이블을 사용하는걸까?

그 해답은 10053 trace 에 있다. id 기준으로 6번에 해당하는 SQL 이 아래와 같다. 

SELECT  /*+  */ NULL C0, C1 C1, BIN_TO_NUM(1, GROUPING(C1)) D0, COUNT(A0), SUM(A0)  FROM "SYS"."SYS_TEMP_0FD9D6608_434CFB" GROUP BY (C1)

 

위에서 C0, C1 은 TEMP 테이블의 컬럼 ALIAS 이며 각각 department_id, JOB_ID를 의미한다. 또한 BIN_TO_NUM 함수를 사용한 이유는 나중에 ID 기준으로 11번째 에서 Group by 단위를 알아내기 위함이다. COUNT(A0), SUM(A0) 를 select 한이유도 11번에서 AVG (salary) 값을 구하기 위해서이다. 참고로 avg(col1) 함수는 논리적으로 sum(col1)/count(col1) 과 같다.

또한 id 기준으로 9번에 해당하는 SQL 이 아래와 같다. 

SELECT  /*+  */ C0 C0, NULL C1, BIN_TO_NUM(GROUPING(C0), 1) D0, COUNT(A0), SUM(A0)  FROM "SYS"."SYS_TEMP_0FD9D6608_434CFB" GROUP BY (C0)

 
최종적으로 Transformation이 적용된 SQL은 다음과 같다.

with SYS_TEMP_0FD9D6608_434CFB as
   (
   SELECT department_id AS C0, JOB_ID AS C1, salary AS A0
     FROM employee a
   )  ,
   TEMP1 as
   (
    SELECT NULL C0, C1, COUNT(salary) AS A1, SUM(salary) AS A0
     FROM SYS_TEMP_0FD9D6608_434CFB
    GROUP BY C1
   ) ,
   TEMP2 as
   (
    SELECT C0, NULL C1, COUNT(salary) AS A1, SUM(salary) AS A0
     FROM SYS_TEMP_0FD9D6608_434CFB
    GROUP BY C0
   ) ,
  SYS_TEMP_0FD9D6609_434CFB AS
  (
   SELECT TEMP1. * FRPM TEMP1
   UNION ALL
   SELECT TEMP1. * FRPM TEMP1
  )
SELECT C0 AS DEPARTMENT_ID, C1 AS JOB_ID, A0 AS AVG_SAL
  FROM (SELECT C0, C1,
                       DECODE(A0, 0, TO_NUMBER(NULL), A1/A0) AS A0 --> 분모가 0 일 경우 처리
              FROM SYS_TEMP_0FD9D6609_434CFB )  ;



불만
id 기준으로 6번과 9번에서 각각 job_id 와 department_id 로 group by 해놓고 이것을 id 11 번에서 합쳐서 보여주게 된다. 하지만 필자는 이런 변환에 대하여 불만이 있다. 위의 쿼리는 EMPLOYEE 테이블을 GROUP BY 하지 않은 상태로 TEMP 테이블에 적재한다. 이것은 매우 비효율적이다. EMPLOYEE 테이블을 Temp 테이블에 적재시 미리 Group BY 하여 넣을 수가 있다. 아래의 SQL 처럼 미리 GROUP BY 하여 건수를 미리 줄여놓으면 반복작업시의 부하가 획기적으로 줄어들수 있기 때문이다.


select  department_id, JOB_ID, sum(sum_sal) / sum(cnt) as avg_sal 

from  (SELECT department_id, JOB_ID, count(salary) cnt, sum(salary) sum_sal

           FROM employee a

          GROUP BY department_id, JOB_ID  )

GROUP BY GROUPING SETS(department_id, JOB_ID)   ;

 

이렇게 수동으로 SQL을 바꾸는 방법은 Grouping Sets의 변환로직이 변하지 않는한 확실한 튜닝방법이 될수 있으므로 반드시 고려되어야 한다.

'Oracle > Optimizer' 카테고리의 다른 글

MERGE 문과 IN 조건이 만난다면  (8) 2009.11.26
Transformer - SQL 튜닝의 새로운 패러다임  (11) 2009.10.15
스칼라 서브쿼리를 서브쿼리로 변환하라  (11) 2009.09.10
Semi Join 의 재조명  (5) 2009.08.31
Group By 를 먼저 수행하고 Join 하라( Group By Placement )  (4) 2009.08.24
Posted by extremedb
,

SQL 튜닝이 불가한 경우

Oracle/TroubleShooting 2009. 9. 16. 10:17

SQL 튜닝시의 업무적 접근
SQL 튜닝시 Buisiness 관점으로 접근하는 것은 매우 중요한 일이다. 예를 들면 SQL을 아무리 튜닝을 해도 해결되지 않는 경우가 있다. 당신이 아무리 화려한 튜닝 테크닉을 가지고 있다고 해도 말이다. 이런 경우 발상을 전환하는 것이 필요하다. SQL 튜닝을 하지않고 이슈를 해결해야 한다는 것 이다. 튜닝시 이슈가 가장 많이 발생하는 3가지 경우를 소개한다.

조회조건은 제약이 필요없나?
웹화면에서 조회조건으로 일자 From ~ To 조건으로 주는 경우가 다반사이다. 사용자는 많은 기간을 조회하고 싶어한다. 실제로 10년간의 거래내역집계를 조회하는 경우도 보았다. 이런 경우는 반드시 화면에서 기간의 제한을 두어야 한다. 한달로 제한 한다든지 아니면 일주일로 한다든지 상황에 따라서 얼마든지 제약을 줄수 있다. 조회조건 선택시 Combo Box 나 Drop Down List Box 에서 전체보기를 없앤다면 마찬가지로 성능향상을 기대할수 있다. 개발자와의 대화 혹은 화면분석이 필요한 순간이다. 필자는 이러한 작업들을 튜닝 프로젝트시에 반드시 실행해야하는 필수 과정으로 생각한다. 이러한 과정을 사용자 화면 튜닝으로 정의 해야한다.

집계 테이블은 필요한가?
또 다른 예제는 SQL이 집계(Group By + Count)를 하는 경우 아무리 튜닝을 해보아야 느릴수 밖에 없다. 가장 좋은 것은 회의를 통하여 이러한 화면들을 DW시스템으로 넘기는 것이다. 이것은 어느정도의 정치력이 필요하다. 주장하는 사람의 권위와 Power 가 필요하다는 이야기 이다. 하지만 이것이 안될경우 업무팀과 협의 하여 집계 테이블을 만들어야 한다. 집계테이블은 최소화 시켜야 한다. 예를 들어 년별, 지점별 집계 SQL이 있고 월별 지점별 집계 SQL이 있다면 집계 테이블은 월별로 하나만 만들어야 한다. 집계 테이블이 많아지면 정합성을 저해할수 있다.

 

저작의도를 알면 길이 보인다
계약 테이블과 고객 테이블을 불필요하게 조인 하는 SQL 이 있었다. 하지만 조인만 하고 Select 절에서는 고객 테이블의 컬럼이 하나도 없었다. 상식적으로 보면 계약테이블의 고객번호는 100% 고객 테이블에 있어야 한다. 그렇다면 고객 테이블을 From 절에서 삭제 하면 될까? 아주 위험한 발상이다. 특수한 SQL 의 경우에는 검증용 SQL 을 프로그램으로 만들기도 한다. 이러한 경우 방법은 딱 한가지이다. SQL 을 작성한 개발자에게 질문하여 이것이 계약 테이블의 고객번호를 검증하기 위한 SQL 인지 물어보는 것이다. 경험상 대부분의 경우 불필요한 조인을 삭제할수 있었다. 이러한 상황은 특히 개발자들 끼리 SQL을 Copy 후 약간 수정하여 사용하는 경우 많이 발생 하게된다.

회의나 대화도 중요한 튜닝 Skill 이라는 점
여러분은 위의 3가지 경우를 어떻게 생각하는가? 튜닝의 기술적인 Skill이 아니므로 무시하고 넘어가는 경우를 많이 보아왔다. 절대 문제를 해결할수 없으며 문제를 키울 뿐이다. 이렇게 되지 않으려면 튜닝시에 사용자 혹은 개발자와 자주 회의를 가져야 한다. 경험상 대화를 자주 할수록 문제의 해결 혹은 대안을 빨리 찾는 경우가 많았다. 이때 고객과의 대화의 기술 혹은 설득의 기술이 많은 도움이 된다. 이러한 기술들은 튜닝 자체의 Skill 보다 상위의 개념이며 문제를 해결할 경우 팀원이나 직원들에게 존경을 받거나 부러움의 대상이 될 가능성이 크다.


결론:
실제로 SQL 튜닝 등으로 성능향상이 불가능한 경우는 당신이 생각하는 것 보다 훨씬 많다.
이럴 경우 화려한 튜닝 테크닉으로 문제를 해결을 시도하는 사람, 대화와 설득으로 해결하는 사람 누가 더 뛰어난 튜너인가?


'Oracle > TroubleShooting' 카테고리의 다른 글

한방 Query를 사용하지 말아야 할 때  (17) 2010.02.19
유명한 Function Based Index 의 버그  (9) 2009.11.19
천당과 지옥의 차이  (3) 2009.06.17
Sequence 의 오작동 (무조건 채번되는 Sequence)  (0) 2009.02.19
Sequence 의 오작동 (번호를 건너뛰는 Sequence)  (4) 2009.02.10
Posted by extremedb
,

스칼라 서브쿼리를 서브쿼리로 변환하라

Oracle/Optimizer 2009. 9. 10. 11:50

개발자의 질문
개발자 한명이 DBA 에게 질문을 던졌다. ‘스칼라 서브쿼리를 사용한 SQL 을 인라인뷰로 싸고 인라인뷰 외부에서 스칼라 서브쿼리를 Filter 조건으로 사용하면 스칼라 서브쿼리가 없어지는 현상이 발생합니다. 이 현상이 정상인가요?’  그런데 DBA 는 그런일은 발생할 수가 없다고 하였다. 과연 사실일까? 정답은 스칼라 서브쿼리가 없어진다는 것이다. 예리한 눈을 가진 개발자 임에 틀림없다.

 

백견이 불여일행
SSTS( Scalar Subquery To Subquery ) 는 스칼라 서브쿼리를 서브쿼리로 변경시키는 Transformation 과정이다. 하지만 항상 변환되지 않는다. 스칼라 서브쿼리를 인라인뷰 외부에서 Filter 조건으로 사용할때만 가능하다. 아래의 SQL 을 보자

인덱스 상황 :

EMP_JOB_IX : employee (job_id)

DEPT_ID_PK1 : department(department_id)

SELECT a.employee_id, a.first_name, a.last_name, a.email

  FROM (SELECT e.employee_id, e.first_name, e.last_name, email,

               (SELECT location_id

                  FROM department d

                 WHERE d.department_id = e.department_id) AS location_id

          FROM employee e

         WHERE e.job_id = 'IT_PROG') a

 WHERE a.location_id > 0;


위의 SQL 은 Location 을 스칼라 서브쿼리로 구현하였다. 하지만 스칼라 서브쿼리를 Select 하는데는 사용하지 않고 Where 조건으로 사용하는 것을 주목하라.

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

| Id  | Operation                    | Name        | E-Rows |E-Bytes| Cost (%CPU)|

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

|*  1 |  FILTER                      |             |        |       |            |

|   2 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEE    |      5 |   195 |     2   (0)|

|*  3 |    INDEX RANGE SCAN          | EMP_JOB_IX  |      5 |       |     1   (0)|

|   4 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENT  |      1 |     7 |     1   (0)|

|*  5 |    INDEX UNIQUE SCAN         | DEPT_ID_PK1 |      1 |       |     0   (0)|

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

Predicate Information (identified by operation id):

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

   1 - filter(>0)

   3 - access("E"."JOB_ID"='IT_PROG')

   5 - access("D"."DEPARTMENT_ID"=:B1)

 

위의 Plan 을 보면 본능적으로 스칼라 서브쿼리가 서브쿼리로 바뀐 것을 알수 있어야 한다. 다시말하면 옵티마이져가 SQL 을 아래처럼 바꾼 것 이다.

 

SELECT e.employee_id, e.first_name, e.last_name, email

  FROM employee e

 WHERE e.job_id = 'IT_PROG'

   AND (SELECT location_id

          FROM department d

         WHERE d.department_id = e.department_id) > 0 ;

 

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

| Id  | Operation                    | Name        | E-Rows |E-Bytes| Cost (%CPU)|

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

|*  1 |  FILTER                      |             |        |       |            |

|   2 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEE    |      5 |   195 |     2   (0)|

|*  3 |    INDEX RANGE SCAN          | EMP_JOB_IX  |      5 |       |     1   (0)|

|   4 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENT  |      1 |     7 |     1   (0)|

|*  5 |    INDEX UNIQUE SCAN         | DEPT_ID_PK1 |      1 |       |     0   (0)|

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

Predicate Information (identified by operation id):

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

   1 - filter(>0)

   3 - access("E"."JOB_ID"='IT_PROG')

   5 - access("D"."DEPARTMENT_ID"=:B1)

 

실행계획과 Predicate Information 이 완전히 같음을 알수 있다. 이제 10053 Trace 를 분석해보자.

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

Order-by elimination (OBYE)

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

…중간생략

CVM:   Merging SPJ view SEL$2 (#0) into SEL$1 (#0)

Registered qb: SEL$F5BB74E1 0xc4643d0 (VIEW MERGE SEL$1; SEL$2)

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

QUERY BLOCK SIGNATURE

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

  signature (): qb_name=SEL$F5BB74E1 nbfros=1 flg=0

    fro(0): flg=0 objn=70296 hint_alias="E"@"SEL$2"

 

View Merging 이 발생하여 SEL$2(인라인뷰 a) 가 SEL$1(메인쿼리)에 통합 되어 버렸다. View Merging 이 발생하여 새로운 쿼리블럭인 SEL$F5BB74E1 가 생성 되었다. 하지만 쿼리블럭 SEL$F5BB74E1 의 From 절을 보면 employee(Alias 로는 E) 만 존재하고 department(D)는 존재 하지 않는다. 그렇다면 스칼라 서브쿼리는 어디로 갔을까? 해답은 FPD(Filter Push Down) 기능에 있다.

 

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

Predicate Move-Around (PM)

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

…중간생략

query block SEL$F5BB74E1 (#0) unchanged

FPD: Considering simple filter push in query block SEL$F5BB74E1 (#0)

 (SELECT "D"."LOCATION_ID" FROM "DEPARTMENT" "D")>0 AND "SYS_ALIAS_1"."JOB_ID"='IT_PROG'

FPD: Considering simple filter push in query block SEL$3 (#0)

"D"."DEPARTMENT_ID"=:B1

try to generate transitive predicate from check constraints for query block SEL$3 (#0)

finally: "D"."DEPARTMENT_ID"=:B1


FPD 기능에 의해서 위에서 새로 생성된 쿼리블럭 SEL$F5BB74E1 의 조건절에 서브쿼리를 생성하고 있다. 또한 새로 생성된 서브쿼리에 "D"."DEPARTMENT_ID"=:B1 조건을 밀어넣고 있다.

 

검증

스칼라  서브쿼리가 서브쿼리로 바뀌었으므로 서브쿼리에 사용할수 있는 힌트 Push_subq 를 사용해보자. 이것이 가능해야지만 진정한 서브쿼리이다.

 

SELECT /*+ PUSH_SUBQ(@SUB) */

        a.employee_id, a.first_name, a.last_name, a.email

  FROM (SELECT e.employee_id, e.first_name, e.last_name, email,

               (SELECT /*+ QB_NAME(SUB) */ location_id

                  FROM department d

                 WHERE d.department_id = e.department_id) AS location_id

          FROM employee e

         WHERE e.job_id = 'IT_PROG') a

 WHERE a.location_id > 0;

 

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

| Id  | Operation                    | Name        | E-Rows |E-Bytes| Cost (%CPU)|

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

|*  1 |  TABLE ACCESS BY INDEX ROWID | EMPLOYEE    |      1 |    39 |     2   (0)|

|*  2 |   INDEX RANGE SCAN           | EMP_JOB_IX  |      5 |       |     1   (0)|

|   3 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENT  |      1 |     7 |     1   (0)|

|*  4 |    INDEX UNIQUE SCAN         | DEPT_ID_PK1 |      1 |       |     0   (0)|

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

Predicate Information (identified by operation id):

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

   1 - filter(>0)

   2 - access("E"."JOB_ID"='IT_PROG')

   4 - access("D"."DEPARTMENT_ID"=:B1)

 

실행계획을 보면 알겠지만 Subquery Pushing 이 발생하여 Id 기준으로 2번과 3번이 동일 Level 상에 존재한다. Subquery Pushing 이 성공적으로 수행된것을 알수 있다.

 

한단계 더 나아가 보자

CREATE INDEX HR.EMP_JOB_DEPT_IX ON HR.EMPLOYEE (JOB_ID, DEPARTMENT_ID);

 

EMPLOYEE 테이블에 JOB_ID, DEPARTMENT_ID 를 생성 하였다. 바로 위에서 실행한 SQL 을 다시 실행 해보자.

--> 여기서 PUSH_SUBQ 를 적용한 SQL 실행한다.

 

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

| Id  | Operation                     | Name            | E-Rows |E-Bytes| Cost (%CPU)|

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

|   1 |  TABLE ACCESS BY INDEX ROWID  | EMPLOYEE        |      1 |    39 |     2   (0)|

|*  2 |   INDEX RANGE SCAN            | EMP_JOB_DEPT_IX |      1 |       |     1   (0)|

|   3 |    TABLE ACCESS BY INDEX ROWID| DEPARTMENT      |      1 |     7 |     1   (0)|

|*  4 |     INDEX UNIQUE SCAN         | DEPT_ID_PK1     |      1 |       |     0   (0)|

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

 

Predicate Information (identified by operation id):

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

   2 - access("E"."JOB_ID"='IT_PROG')

       filter(>0)

   4 - access("D"."DEPARTMENT_ID"=:B1)

  

이것은 제공자 서브쿼리가 아니다

실행계획은 마치 제공자 서브쿼리처럼 바뀌었지만 이것은 제공자 서브쿼리가 아니고 Subquery Pushing 에 의한 효과이다. Predicate Information 을 주목하라. ID 기준으로 2번에서 Filter 가 수행된다. . (JOB_ID, DEPARTMENT_ID) 인덱스를 사용하지 않은 SQL의 Predicate Information을 보면 EMPLOYEE 테이블 엑세스 시에 filter 가 발생함을 알수 있다. 이것이 바로 Subquery Pushing 과 인덱스에 의한 Early Filter 의 효과이다. 물론 Early Filter 의 개념은 조인순서상에 서브쿼리를 최대한 먼저 조인 하는것으로 바꾸는 것이다. 하지만 위와 같은 Early Filter의 부가적인 기능도 있음을 알아야 한다.

 Subquery Pushing 에 의한 Early Filter 기능은 Using Sub query Method( Filter / Access sub Query ) 글을 참조하라

결론:
이처럼 Oracle Query Transformer 는 스칼라 서브쿼리를 Filter 조건으로 사용할 때 SSTS를 발생시킨다.. 여러분이 Query Transformation 을 의도 하던 의도하지 않던 말이다.

PS :
제목이 이상하다는 의견이 있다. 제목은 옵티마이져가  변환을 수행한다는 의미이다. 수동으로 스칼라 서브쿼리를 서브쿼리로 고치라는 의미가 아니므로 착오가 없길 바란다.


'Oracle > Optimizer' 카테고리의 다른 글

Transformer - SQL 튜닝의 새로운 패러다임  (11) 2009.10.15
Temp Table Transformation  (5) 2009.09.28
Semi Join 의 재조명  (5) 2009.08.31
Group By 를 먼저 수행하고 Join 하라( Group By Placement )  (4) 2009.08.24
Parallel Query 수행시 Group by 를 먼저 수행하라(Group By Push Down)  (9) 2009.08.12
Posted by extremedb
,

Semi Join 의 재조명

Oracle/Optimizer 2009. 8. 31. 15:45

예전에 Hash Join Right (Semi/Anti/Outer) 의 용도 라는 글에서 서브쿼리가 Semi Join 으로 적용될때 조인의 순서를 바꿀수 있는 방법에 대하여 설명한바 있다. 이어서 오늘도 예전글과 유사한 Semi Join 의 튜닝에 대해서 이야기 하려 한다.

환경 : Oracle 11.1.0.6
먼저 테이블을 단순한 구조로 만들기 위해서 테이블을 2개 생성한다.
ORACLE 의 Sample 스키마인 SH 를 이용한다.

CREATE TABLE SALES_T AS SELECT * FROM sh.sales;

CREATE TABLE PRODUCTS_T AS SELECT * FROM sh.products ;

ALTER TABLE PRODUCTS_T ADD CONSTRAINT PK_PRODUCTS_T         --> PK 및 인덱스 생성
PRIMARY KEY (PROD_ID) USING INDEX;

CREATE BITMAP INDEX SALES_T_PROD_BIX ON SALES_T (PROD_ID);  --> BIT MAP 인덱스 생성

dbms_stats.gather_table_stats(user,'SALES_T',cascade=>true);
dbms_stats.gather_table_stats(user,'PRODUCTS_T',cascade=>true);

이제 SQL 을 실행할 준비가 다 되었다.
아래는 상품의 Category 가 Software/Other 인 제품의 상품코드별, 채널별 판매량을 나타낸 SQL 이다.
이러한 SQL 은 주로 DW 에서 나타난다.

SELECT /*+ GATHER_PLAN_STATISTICS QB_NAME(MAIN) */
            s.prod_id, s.channel_id, SUM (quantity_sold) AS qs, SUM (amount_sold) AS amt
   FROM sales_t s
 WHERE s.prod_id IN (SELECT /*+ QB_NAME(SUB) */
                                             p.prod_id
                                   FROM products_t p
                                 WHERE p.prod_category_desc = 'Software/Other')
GROUP BY s.prod_id, s.channel_id ;

--------------------------------------------------------------------------
| Id  | Operation           | Name       | Starts |   A-Time   | Buffers |
--------------------------------------------------------------------------
|   1 |  HASH GROUP BY      |            |      1 |00:00:08.12 |    4445 |
|*  2 |   HASH JOIN         |            |      1 |00:00:06.94 |    4445 |
|*  3 |    TABLE ACCESS FULL| PRODUCTS_T |      1 |00:00:00.01 |       4 |
|   4 |    TABLE ACCESS FULL| SALES_T    |      1 |00:00:01.84 |    4441 |
--------------------------------------------------------------------------

헉 8초 이상 걸렸다!
Subquery Unnesting 이 발생하여 작은쪽 테이블인 Products_t 가 Driving 테이블로 선정되었고 Salse_t 테이블과 Hash Join 을 하게된다. Scan 한 블럭수는 4445블럭 이며 8초 이상 수행되었다. 조인건수(Starts 항목 참조)는 1회에 불과하다. 이것은 최적으로 튜닝된 SQL 인가?  이것을 알아보기 위해 Hash Join 대신 Nested Loop 조인으로 바꿔보자.

  SELECT /*+ GATHER_PLAN_STATISTICS */
               prod_id, channel_id, SUM (qs) AS qs, SUM (amt) amt
    FROM (SELECT /*+ NO_MERGE USE_NL(PR S) */
                            s.prod_id, s.channel_id,
                            quantity_sold AS qs, amount_sold AS amt
                  FROM sales_t s, products_t pr
                 WHERE s.prod_id = pr.prod_id
                     AND pr.prod_category_desc = 'Photo')
GROUP BY prod_id, channel_id ;

원본 SQL 의 경우 왠만해서는 Nested Loop Join 으로 바뀌지 않으므로 강제로 서브쿼리를 From 절로 끌어 올려서 Nested Loop Join 으로 유도 하였다.

------------------------------------------------------------------------------------
| Id |Operation                       |Name            |Starts|   A-Time   |Buffers|
------------------------------------------------------------------------------------
|  1 | HASH GROUP BY                  |                |    1 |00:00:10.07 |   3099|
|  2 |  VIEW                          |                |    1 |00:00:08.92 |   3099|
|  3 |   NESTED LOOPS                 |                |    1 |00:00:07.70 |   3099|
|  4 |    NESTED LOOPS                |                |    1 |00:00:02.03 |     41|
|* 5 |     TABLE ACCESS FULL          |PRODUCTS_T      |    1 |00:00:00.01 |      4|
|  6 |     BITMAP CONVERSION TO ROWIDS|                |   26 |00:00:00.41 |     37|
|* 7 |      BITMAP INDEX SINGLE VALUE |SALES_T_PROD_BIX|   26 |00:00:00.01 |     37|
|  8 |    TABLE ACCESS BY INDEX ROWID |SALES_T         |  405K|00:00:02.45 |   3058|
------------------------------------------------------------------------------------ 

Hash Join 보다 더 느려졌다
Nested Loop Join 으로 유도 하자 인덱스는 제대로 사용하였지만 비효율이 극심하게 드러난다. 먼저 Nested Loop Join 이 40만번 이상 수행된다.(Starts항목 참조) 그결과로 View 를 만드는데 8초 이상 수행되었다. Buffer 가 30% 이상 줄어들었지만 조인건수 때문에 시간이 많이 걸리므로 이것은 견딜수 없는 결과이다.

그럼 Hash Join 에 만족해야 하나?
지금까지의 상식으로는 그렇다. 하지만 Driving Semi Join 기법을 사용한다면 생각이 달라질것이다.

Driving Semi Join 이란?
Driving Semi Join 이란 Semi Join 의 변형된 형태로서 Hash Join 의 장점(조인건수의 최소화)과 Nested Loop Join 의 장점(선행테이블이 상수화 되어 후행 테이블에서 인덱스를 효율적으로 사용) 을 합친 개념이다. 물론 Driving Semi Join 이라는 Title 자체의 의미대로 Semi Join 이 Driving 집합이 되는것은 당연하다. 다음의 SQL 을 보자  

SELECT /*+ GATHER_PLAN_STATISTICS QB_NAME(MAIN) SEMIJOIN_DRIVER(@SUB) */
             s.prod_id, s.channel_id, SUM (quantity_sold) AS qs, SUM (amount_sold) AS amt
   FROM sales_t s
WHERE s.prod_id IN (SELECT /*+ QB_NAME(SUB) */
                                          p.prod_id
                                FROM products_t p
                               WHERE p.prod_category_desc = 'Photo')
GROUP BY s.prod_id, s.channel_id ;


----------------------------------------------------------------------------------
| Id | Operation                     |Name            |Starts|   A-Time  |Buffers|
----------------------------------------------------------------------------------
|   1|  HASH GROUP BY                |                |     1|00:00:03.40|   2231|
|   2|   TABLE ACCESS BY INDEX ROWID |SALES_T         |     1|00:00:02.04|   2231|
|   3|    BITMAP CONVERSION TO ROWIDS|                |     1|00:00:00.42|     63|
|   4|     BITMAP MERGE              |                |     1|00:00:00.02|     63|
|   5|      BITMAP KEY ITERATION  |                |     1|00:00:00.02|     63|
|*  6|       TABLE ACCESS FULL       |PRODUCTS_T      |     1|00:00:00.01|      4|
|*  7|       BITMAP INDEX RANGE SCAN |SALES_T_PROD_BIX|    26|00:00:00.02|     59|
----------------------------------------------------------------------------------

환상적이다
Hash Join (8초 이상), Nested Loop Join (10초 이상) 인데 반하여 Driving Semi Join 은 3.4초 만에 끝이 났다. 또한 조인횟수도 26 번에 불과하며 I/O Block 수도 2231 로 Hash Join 의 절반에 해당한다.

환상적인 이유는?
그 이유는 Join 대신에 BITMAP KEY ITERATION 이 수행된 까닭이다.
BITMAP KEY ITERATION 의 원리는 INLIST ITERATOR Operation 과 같다고 볼수 있다. 
INLIST ITERATOR 의 개념은 InList / Concatnation / Range Scan Control 하기 을 참조하라.

Driving Semi Join은 B-Tree 인덱스에서도 사용이 가능해
이기능은 Bit Map 인덱스 뿐만 아니라 B-Tree 인덱스 에서도 사용가능하다.
하지만 _b_tree_bitmap_plans 파라미터가 True 로 되어 있어야 한다.
아래의 SQL 을 보자.

DROP INDEX SALES_T_PROD_BIX;  --> Bit Map 인덱스 Drop

CREATE INDEX SALES_T_PROD_IX ON HR.SALES_T (PROD_ID); --> B-Tree 인덱스 생성

ALTER SESSION SET  "_b_tree_bitmap_plans" = TRUE;

SELECT /*+ GATHER_PLAN_STATISTICS QB_NAME(MAIN) SEMIJOIN_DRIVER(@SUB) */
     s.prod_id, s.channel_id, SUM (quantity_sold) AS qs,
     SUM (amount_sold) AS amt
FROM sales_t s
WHERE s.prod_id IN (SELECT /*+ QB_NAME(SUB) */
                          p.prod_id
                     FROM products_t p
                    WHERE p.prod_category_desc = 'Photo')
GROUP BY s.prod_id, s.channel_id;


-------------------------------------------------------------------------------------
| Id |Operation                          |Name           |Starts|   A-Time  |Buffers|
-------------------------------------------------------------------------------------
|   1| HASH GROUP BY                     |               |     1|00:00:04.94|   3045|
|   2|  TABLE ACCESS BY INDEX ROWID      |SALES_T        |     1|00:00:03.76|   3045|
|   3|   BITMAP CONVERSION TO ROWIDS     |               |     1|00:00:02.14|    877|
|   4|    BITMAP MERGE                   |               |     1|00:00:01.73|    877|
|   5|     BITMAP KEY ITERATION          |               |     1|00:00:01.73|    877|
|*  6|      TABLE ACCESS FULL            |PRODUCTS_T     |     1|00:00:00.01|      4|
|   7|      BITMAP CONVERSION FROM ROWIDS|               |    26|00:00:01.73|    873|
|*  8|       INDEX RANGE SCAN            |SALES_T_PROD_IX|    26|00:00:00.86|    873|
-------------------------------------------------------------------------------------

Bit Map 인덱스에 비하여 약간의 비효율이 있다. 그것은 BITMAP CONVERSION FROM ROWIDS Operation 이 추가되었기 때문이다. 하지만 기존의 Hash Join 이나 Nested Loop Join 보다는 수행시간, I/O Block 수, 조인건수등이 우월함을 알수 있다.

당신은 이미 Driving Semi Join을 사용했다
여러분의 SQL 이 Star Transformation 을 수행하는 경우는 싫든 좋든 Driving Semi Join 기능을 이용하고 있는 것이다. 아래는 Star Transformation 을 수행하는 SQL 의 Plan 과 Outline Data 를 출력한 것이다.
  SELECT /*+ gather_plan_statistics */
         p.prod_id,
         c.CHANNEL_ID,
         SUM (quantity_sold) AS qs,
         SUM (amount_sold) AS amt
    FROM sh.sales s, sh.CHANNELS c, sh.products p
   WHERE s.CHANNEL_ID = c.CHANNEL_ID
     AND c.CHANNEL_DESC = 'Internet'
     AND s.prod_id = p.prod_id
     AND p.prod_category_desc = 'Photo'
GROUP BY p.prod_id, c.CHANNEL_ID;

------------------------------------------------------------------+
| Id  | Operation                              | Name             |
------------------------------------------------------------------+
| 0   | SELECT STATEMENT                       |                  |
| 1   |  HASH GROUP BY                         |                  |
| 2   |   HASH JOIN                            |                  |
| 3   |    TABLE ACCESS FULL                   | CHANNELS         |
| 4   |    HASH JOIN                           |                  |
| 5   |     TABLE ACCESS FULL                  | PRODUCTS         |
| 6   |     PARTITION RANGE ALL                |                  |
| 7   |      TABLE ACCESS BY LOCAL INDEX ROWID | SALES            |
| 8   |       BITMAP CONVERSION TO ROWIDS      |                  |
| 9   |        BITMAP AND                      |                  |
| 10  |         BITMAP MERGE                   |                  |
| 11  |          BITMAP KEY ITERATION          |                  |
| 12  |           BUFFER SORT                  |                  |
| 13  |            TABLE ACCESS FULL           | CHANNELS         |
| 14  |           BITMAP INDEX RANGE SCAN      | SALES_CHANNEL_BIX|
| 15  |         BITMAP MERGE                   |                  |
| 16  |          BITMAP KEY ITERATION          |                  |
| 17  |           BUFFER SORT                  |                  |
| 18  |            TABLE ACCESS FULL           | PRODUCTS         |
| 19  |           BITMAP INDEX RANGE SCAN      | SALES_PROD_BIX   |
------------------------------------------------------------------+

  Outline Data:
  /*+
    BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.1.0.6')
      ... 중간생략    
      STAR_TRANSFORMATION(@"SEL$1" "S"@"SEL$1" SUBQUERIES(("C"@"SEL$1") ("P"@"SEL$1")))
      BITMAP_TREE(@"SEL$1" "S"@"SEL$1" AND(("SALES"."CHANNEL_ID") ("SALES"."PROD_ID")))
      SEMIJOIN_DRIVER(@"SEL$1898F719")
      SEMIJOIN_DRIVER(@"SEL$D750A531")
    END_OUTLINE_DATA
  */

Driving Semi Join의 장점은 또 있다
Driving Semi Join Nested Loop Join 시의 조인횟수에 의한 부하나 Hash 조인시의 Full Table Scan + Hashing 작업의 부하를 획기적으로 감소켜준다. 더 좋은것은 Star Transformation 사용시에 바인드 변수를 사용할수 없었지만 Driving Semi Join은 이러한 제약이 없다. 하지만 이 기능은 오라클사의 정식힌트가 아니므로 필자의 경우 데이터 분석 용도로만 사용하고 있다. 이기능이 11.2 버젼에는 정식으로 메뉴얼에 나오길 기대하며 이글을 마칠까 한다.


'Oracle > Optimizer' 카테고리의 다른 글

Temp Table Transformation  (5) 2009.09.28
스칼라 서브쿼리를 서브쿼리로 변환하라  (11) 2009.09.10
Group By 를 먼저 수행하고 Join 하라( Group By Placement )  (4) 2009.08.24
Parallel Query 수행시 Group by 를 먼저 수행하라(Group By Push Down)  (9) 2009.08.12
11g DBMS_STATS 패키지 성능개선의 3가지 원리  (3) 2009.06.15
Posted by extremedb
,

Group By 를 먼저 수행하고 Join 하라( Group By Placement )

Oracle/Optimizer 2009. 8. 24. 19:55

부제목 : GBP (Group By Placement ) 의 목적및 용도

Parallel Query 수행시 Group by 를 먼저 수행하라(Group By Push Down) 라는 글에서 먼저 Group By 를 수행하여 성능을 향상시키는 경우를 설명하였다. 오늘도 Group By Push Dwon 과 아주 흡사한 Query Transformation 에 대하여 설명하려 한다.

Group By Push Down 은 Parallel Query 에서 한정적으로 나타나는 기능이지만 Group By Placement 는 이러한 제약이 없다. 또한 Group By Placement 는 Query Transformation 의 종류 이지만 Group By Push Down은 SQL 자체의 변환과정이 없다는 점에서 엄연히 다르다.

GBP 가 뭐하는 거지?
GBP 란 기본적으로 조인의 부하를 줄이기 위한 수단이다. 조인을 수행하기전에 Group By 를 먼저 수행하고 건수를 줄이고 난후에 조인을 수행함으로서 조인건수가 획기적으로 감소되게 하는데 그목적이 있다. 이 기능은 주로 OLTP 보다는 DW 의 대용량 Mart 등에서 사용할 경우 성능향상을 극대화 할수 있다. 아래의 SQL 을 보자.

환경 Oracle 11g (11.1.0.7)

  SELECT   /*+ qb_name(main) place_group_by(@main (S@main)) */

         cust_city_id, SUM (quantity_sold)

    FROM customers c, sales s

   WHERE c.cust_id = s.cust_id AND s.cust_id BETWEEN 5000 AND 5500

   GROUP BY c.cust_city_id ;


위의 SQL의 목적은 고객 테이블(customers)과 판매 테이블(sales)을 조인하여 고객의 도시별 판매수량을 구하는 것이다.

상식적으로는 ..
일반적인 상식으로는 customers 테이블과 sales 테이블을 조인한 후에 Group BY 가 한번 수행된다고 알고 있다. 하지만 Oracle 11g 로 넘어오면서 '상식의 파괴'가 일어난다.      

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

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

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

| 0   | SELECT STATEMENT         |          |       |       |   973 |           |

| 1   |  HASH GROUP BY           |          |   620 |   17K |   973 |  00:00:12 |

| 2   |   HASH JOIN              |          |  7059 |  193K |   972 |  00:00:12 |

| 3   |    VIEW                  | VW_GBC_1 |  7059 |  124K |   566 |  00:00:07 |

| 4   |     HASH GROUP BY        |          |  7059 |   90K |   566 |  00:00:07 |

| 5   |      PARTITION RANGE ALL |          |   28K |  370K |   492 |  00:00:06 |

| 6   |       TABLE ACCESS FULL  | SALES    |   28K |  370K |   492 |  00:00:06 |

| 7   |    TABLE ACCESS FULL     | CUSTOMERS|   54K |  542K |   405 |  00:00:05 |

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


Group By 가 두번 발생하다
위의 Plan 을 보면 Group By 가 두번 발생하였으며 조인도 sales 테이블을 Group By 한 이후에 발생하였다.

왜 두번 수행되나?
이것은 대용량 테이블인 sales 테이블을 먼저 조인 기준컬럼인 cust_id 로 먼저 Group By 하고 난후에 조인함으로서 조인의 부하를 줄이기 위함이다. 다시 말하면 오라클 Transformer는 SQL 을 아래와 같이 바꾼 것 이다.

  SELECT c.cust_city_id cust_city_id, SUM (vw_gbc_1.item_2) sum_qt
    FROM (SELECT   s.cust_id item_1, SUM (s.quantity_sold) item_2
              FROM sales s
             WHERE s.cust_id <= 5500 AND s.cust_id >= 5000
          GROUP BY s.cust_id) vw_gbc_1,
         customers c
   WHERE c.cust_id = vw_gbc_1.item_1
   GROUP BY c.cust_city_id;


sales 테이블을 Group By 하여 인라인뷰를 먼저 만들고 customers 와 조인후 다시 c.cust_city_id 로 Group By 하고 있다. 인라인 뷰의 이름이 vw_gbc_1 인데 GBP 가 여러번 발생되면 vw_gbc_1, vw_gbc_2, vw_gbc_3 ... 처럼 숫자 부분이 증가 된다.
 
GBP 는 CBQT(Cost Based Query Transformation) 이다
Query Transformer 는 GBP 를 수행하기 위해 변환된(GBP 가 수행된) SQL 과 변환되지 않은 SQL을 각각 비용을 계산하여 가장 비용이 낮은 SQL 을 선택하게 된다. GBP 가 수행된 SQL 은 여러개 일수 있다.
아래는 작업을 수행하는 과정을 보여주는 10053 Trace 내용이다.

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

Cost-Based Group By Placement

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

GBP: Checking validity of GBP for query block MAIN (#1)

GBP: Checking validity of group-by placement for query block MAIN (#1)

 

GBP: Using search type: exhaustive

GBP: Considering group-by placement on query block MAIN (#1)

GBP: Starting iteration 1, state space = (1,2) : (0,0)

GBP: Transformed query
...중간생략


10053은 어렵지 않다
10053 을 어렵게 생각하는 DBA 들이 있다. 절대 어렵지 않다. GBP를 수행하기 위한 Using search type이 exhaustive 로 되어 있다. Using search type 이라는 것 은 변환 가능한 경우의 수를 어디까지 고려 할것인지 의 정도(level) 을 설명한 것이고 그 level 은 exhaustive 로 되어 있다. exhaustive 라는 것은 모든 변환가능한 경우의 수를 고려 하겠다는 뜻이다. 

Iteration 이란 무엇인가?
Iteration 이란 CBQT 에서만 발생하며 기본적으로 변환이 수행된 경우와 수행되지 않은 경우의 Cost 를 비교하기 위한 경우의 수이다. 일반적으로 iteration 1 에서 변환이 수행되지 않은 경우를 나타내며 iteration 2 에서는 변환이 수행된 경우의 일련의 과정을 나타낸다. 마지막에는 iteration 1 과 iteration 2 의 Cost 를 비교하여 Cost 가 낮은 경우를 선택하게 된다.
 
Iteration 은 여러번 생길 수 있다
복잡한 SQL 의 경우 변환의 결과가 여러개 일수 있는데 이때는 Starting iteration 1, Starting iteration 2, Starting iteration 3 ... 등으로 증가한다. 하지만 원본 SQL 은 place_group_by 힌트를 사용하였으므로 GBP 를 수행한 경우(iteration 1)와 수행하지 않은 경우(iteration 2)의 Cost 를 비교하지 않고 iteration 1 에서 멈추게 된다.

GBP 를 Control 하자
GBP 를 Control 하는 파라미터는 _optimizer_group_by_placement 이며 Default 로 True 이다. 힌트는 GBP 를 강제하려면 place_group_by 헤제 하려면 no_place_group_by 힌트를 사용하면 된다.
 
결론
GBP 는 기본적으로 오라클이 자동으로 수행한다.
GBP 는 성능을 향상시키는 훌륭한 기능이지만 잘못 사용하면 오히려 독이 될수 있다. 조인을 먼저 수행하는 것이 오히려 결과 건수를 획기적으로 줄여주는 경우가 있는데 이런 경우는 GBP 를 사용하면 안된다. 이런 경우가 아니면서 조인하기 전에 먼저 Group By 하여 건수를 확실히 줄일수 있을때만 사용하여야 한다.

 

'Oracle > Optimizer' 카테고리의 다른 글

스칼라 서브쿼리를 서브쿼리로 변환하라  (11) 2009.09.10
Semi Join 의 재조명  (5) 2009.08.31
Parallel Query 수행시 Group by 를 먼저 수행하라(Group By Push Down)  (9) 2009.08.12
11g DBMS_STATS 패키지 성능개선의 3가지 원리  (3) 2009.06.15
PM ( Predicate Move Around ) : Where 조건을 다른뷰에 이동시켜라.  (2) 2009.06.09
Posted by extremedb
,

Parallel Query 수행시 Group by 를 먼저 수행하라(Group By Push Down)

Oracle/Optimizer 2009. 8. 12. 00:40
Parallel Query 수행시 튜닝 방법에 대해 아래와 같이 2개의 글을 연재한 바 있다.
1.Parallel Query 의 조인시 Row Distribution  --> Join 시 Row 의 분배방법 튜닝
2.Parallel Query 의 조인시 또다른 튜닝방법(Parallel Join Filter) --> Join Filter 튜닝

Parallel Query 의 튜닝방법은 많지 않아...
Parallel Query 의 튜닝 방법은 많지 않은데 그 이유는 Parallel Query 기능 자체를 튜닝 하는것이 아니라 Hash 조인의 튜닝 혹은 Block I/O 의 튜닝등 Parallel 자체와는 상관없는 것 을 튜닝하는 경우가 많기 때문이다.
오늘은 3번째로 Parallel Query 자체를 튜닝 하는 또다른 방법에 대하여 논의 할것이다.

잘못된 미신을 믿지 마라.
사실 필자가 Group By Push Down 기능을 설명하는 이유는 잘못된 미신 때문이다.
"Parallel Query 를 수행하면 Group By 가 두번 수행된다. 따라서 Group By 가 있는 SQL 은 Parallel 을 사용하지 마라."
실제 DBA 들의 입에서 오고 가는 말들이다. 잘못된 미신이 퍼져 있다니 참으로 안타까운 현실이 아닐수 없다.
Parallel Query 를 수행하면 Group By 가 무조건 두번 수행되는 것은 아니며 두번 수행 된다면 오히려 성능향상을 기대할 수 있다.
이 글을 읽고 개념을 확실히 하기 바란다.

TQ 를 알고 가자.
먼저 Group By Push Down 기능을 설명하기 전에 TQ(Table queues) 개념을 알아야 한다.
1.TQ 는 Processes간의 데이터를 주고받는 기능을 한다.
2.하나의 TQ 는 여러개의 parallel Slave 를 가진다.
3.TQ 는 Parallel Query 수행시 생성된다.
상세한 개념은 위에서 언급한 글중 2번을 참조하기 바란다.

Group By Push Down 이 뭐야?
1.Group By Push Down 이란 TQ 에 데이터를 전달하기 전에 Group By 를 수행하여 데이터의 건수를 대폭 줄인후에 TQ 에 데이터를 전달함으로서 일량을 줄이고 성능을 향상시키는데 목적이 있다.
2.Group By Push Down 은 Parallel Query 에 Group By 가 포함되어 있는 경우 발생한다.

원리는 같다.
이기능은 마치 DW 용 SQL 작성시 Fact 테이블(대용량) 을 먼저 Group By 한후에 Dimension 테이블(소용량 코드 테이블)과 조인하여 조인 건수를 대폭 줄임으로서  성능 향상을 꾀하는것과 같은 개념이다. 이기능은 오라클이 자동으로 해주기도 하는데 이것을 "Group By Placement" 라고 하며 추후 따로 글을 올릴 생각이다.

어떻게 하는건데?
Group By Push Down을 수행하기위해 아래의 SQL 을 보자.
환경: Oracle 11g 11.1.0.7

SELECT   /*+ GATHER_PLAN_STATISTICS FULL(A) parallel(A 4) GBY_PUSHDOWN */
         prod_id, cust_id, COUNT (*) cnt
    FROM sh.sales A
GROUP BY prod_id, cust_id;

GBY_PUSHDOWN 란 힌트를 사용하였으며 아래의 Plan 에서 보는바와 같이 성공적으로 Group By Push Down 이 수행되었다.

---------------------------------------------------------------------------
| Id  | Operation                | Name     | E-Rows | Used-Mem | Used-Tmp|
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |          |        |          |         |
|   1 |  PX COORDINATOR          |          |        |          |         |
|   2 |   PX SEND QC (RANDOM)    | :TQ10001 |    359K|          |         |
|   3 |    HASH GROUP BY         |          |    359K| 2197K (0)|         |
|   4 |     PX RECEIVE           |          |    359K|          |         |
|   5 |      PX SEND HASH        | :TQ10000 |    359K|          |         |
|   6 |       HASH GROUP BY    |          |    359K| 3284K (1)|    1024 |
|   7 |        PX BLOCK ITERATOR |          |    918K|          |         |
|*  8 |         TABLE ACCESS FULL| SALES    |    918K|          |         |
---------------------------------------------------------------------------

원래 수행되어야 하는 Group By 는 Id 기눈으로 3번(파랑색 부분) 이지만 Id 6번에서 먼저 Group By 가 수행되었다. 그이유는 Id 5번의 :TQ10000 에게 데이터를 전달하기전에 데이터를 줄여서 성능을 향상시키기 위함이다. 실제로 옵티마이져는 Id 5번에서 Group By 된 359K Row에 대한 데이터만 처리 할것으로 예상하고 있다.

튜닝은 Trade Off 이다.
주의 사항이 있다. 실제로 TQ의 일량은 줄어들지만 불필요한 Hash Group By 혹은 Sort Group By 가 수행되어 성능이 더 나빠질수 도 있다는것이다. Id 6 에서 Hash Area Size 가 부족하여 Disk 작업(Used-Tmp 부분 참조)이 발생하였다. 따라서 Group By 를 하면 건수가 몇배~ 몇십배이상 줄어드는 경우에 적용하여야 하며  Disk 에서 Sort 및 Hash 작업이 발생하는 경우는 PGA 튜닝을 동반하여야 한다.

Group By Push Down 이 적용되지 않은 Plan 을 보여다오.
GBY_PUSHDOWN 힌트 대신에 NO_GBY_PUSHDOWN 힌트를 사용하면 Group By Push Down이 발생하지 않는다.

SELECT   /*+ GATHER_PLAN_STATISTICS FULL(A) parallel(A 4) NO_GBY_PUSHDOWN */
         prod_id, cust_id, COUNT (*) cnt
    FROM sh.sales A
GROUP BY prod_id, cust_id;


----------------------------------------------------------------
| Id  | Operation               | Name     | E-Rows | Used-Mem |
----------------------------------------------------------------
|   0 | SELECT STATEMENT        |          |        |          |
|   1 |  PX COORDINATOR         |          |        |          |
|   2 |   PX SEND QC (RANDOM)   | :TQ10001 |    359K|          |
|   3 |    HASH GROUP BY        |          |    359K| 4492K (0)|
|   4 |     PX RECEIVE          |          |    918K|          |
|   5 |      PX SEND HASH       | :TQ10000 |    918K|          |
|   6 |       PX BLOCK ITERATOR |          |    918K|          |
|*  7 |        TABLE ACCESS FULL| SALES    |    918K|          |
----------------------------------------------------------------

정상적으로 Group By 가 한번만 수행되었지만 옵티마이져는 TQ 의 일량이 Group By Push Down 이 적용된 SQL 에 비하여 918K 로 약 3배정도 중가한것으로 판단하였고 실제로도 그렇게 수행된다.

Group By Push Down은 11g 신기능이 아니다.
GBY_PUSHDOWN / NO_GBY_PUSHDOWN 등의 힌트는 11g 에서 새로 나온 것 이다. 하지만 이전버젼에서도 _groupby_nopushdown_cut_ratio 파라미터를 0 으로 세팅하면 Group By Push Down 을 강제로 수행할수 있다. 이파라미터의 Default 값은 3 이며 이경우는 Group By Push Down 의 수행여부를 옵티마이져가 판단한다. 아래는 옵티마이져의 Costing 과정을 10053 Trace 에서 발췌한 것이다.

*********************************
Number of join permutations tried: 1
*********************************
GROUP BY adjustment factor: 0.707107
GROUP BY cardinality:  359386.000000, TABLE cardinality:  918843.000000
Costing group-by pushdown:
    SORT ressource         Sort statistics
      Sort width:         598 Area size:      552960 Max Area size:   104857600
      Degree:               1
      Blocks to Sort: 563 Row size:     20 Total Rows:         229711
      Initial runs:   2 Merge passes:  1 IO Cost / pass:        306
      Total IO sort cost: 869      Total CPU sort cost: 230852464
      Total Temp space used: 4629000
  Distribution cost: resc_cpu: 91885309  resp_cpu: 22971327
  Costing final group-by:
  Subtracting no-pushdown group-by:
    SORT ressource         Sort statistics
... 이후 생략

적재적소에 사용하자.
실제로 옵티마이져는 Group By Push Down 의 수행여부를 Cost 로서 판단하고 있으므로 기본적으로는 오라클에 맏기면 된다. 하지만 아주 복잡한 SQL 이나 옵티마이져가 판단을 잘못할 경우에 Group By Push Down 을 수동으로 적절히 발생시킨다면 성능향상을 꾀할수 있다.

편집후기:
필자가 이글을 올린다고 하니 어느 지인은 컨설턴트의 밥그릇 타령을 한다.
다시말해 이런것들을 모든 개발자가 안다면 컨설턴트가 설자리가 없다는 것인데...
글쎄...
여러분은 어떻게 생각하는가?    

'Oracle > Optimizer' 카테고리의 다른 글

Semi Join 의 재조명  (5) 2009.08.31
Group By 를 먼저 수행하고 Join 하라( Group By Placement )  (4) 2009.08.24
11g DBMS_STATS 패키지 성능개선의 3가지 원리  (3) 2009.06.15
PM ( Predicate Move Around ) : Where 조건을 다른뷰에 이동시켜라.  (2) 2009.06.09
Query Transformation Internal ( about JPPD using Lateral View )  (0) 2009.04.20
Posted by extremedb
,

Oracle 10g Partitioning 가이드

Oracle/Partition 2009. 8. 5. 21:32

아래는 약 3년전에 고객사 DBA 와 필자의 대화내용 이다.
물론 내용은 오라클 파티션 가이드 이다.

파티션 가이드? 구글에 있잖아!

DBA : Oracle 10g 기준으로 파티션 가이드를 하나 만들어 주세요.

필자 : 그런것은 구글에서 "파티션 가이드" 혹은 "Partiton 가이드" 로 검색을 하면 주루룩 하고 나올겁니다.

DBA : 제가 검색을 다 해보았지만 그런 것은 없었습니다.

필자 : 그럴리가요?

DBA : 파티션관련 자료는 많이 있지만 문제는 그 문서들이 파티션의 종류와 특성만을 설명하고 있다는 것입니다.
         파티션의 종류와 특징, 파티션 키의 선정, 인덱스 문제(Local, Global, Prefix ..등등 ) 은 왠만한
         개발자들도 알고 있습니다. 따라서 이런 문서는 개발팀에게 배포할 성격의 것은 아닙니다.

필자 : 그렇다면 하나의 테이블이 있을때 그 테이블을 파티션을 적용할것인지 아닌지 판단하는 로직을 원하시는 건가요?

DBA : 그렇습니다. 파티션을 적용할것인지 말것인지의 로직과 적용한다면 어떤 파티션을 적용할것인지 에 대한
         명쾌한 로드맵 같은것이 있으면 좋겠습니다.

이상하네? 가이드가 없네?
그 DBA 의 말이 일리가 있었다. 3년전 구글에서 조회 해보니 파티션 종류별 특성과 설명 뿐이었다. 이것은 놀라운 결과이다. 무수한 프로젝트에서 파티션을 적용해왔을 터
그런데 가이드가 없다니...

우리가 원하는 것은  "테이블을 어떠한 기준으로 무슨 종류의 파티션을 적용하느냐" 이다.
문제는 "각테이블을 어떤 기준으로 파티션을 적용하며 파티션의 종류는 무엇으로 할것인가?" 이다.  그런데 최근에 다시 파티션 가이드에 대해 구글에서 조회해본 결과 이런 성격의 자료가  전혀 없다. 물론 영문검색을 하면 오라클 메뉴얼이 나오긴 한다. 오라클 메뉴얼에도 명확한 기준은 없다. 또한 고객들이 원하는 것은 한글로된 문서이다.

가이드를 작성하는 방법
일반적으로 가이드라고 하는것은 아래와 같은 조건을 충족 해야한다.

1. 구체적인 표현으로 작성해야 한다. 모호한 표현은 가이드가 될수 없다. 더욱 모호해질 뿐이기 때문이다.
2. 판단 기준이 명확해야 한다. 이것은 분기로직이 있으면 더욱 명확해진다.
3. "10 중 8, 9" 라는 말이 있다. 이말은 예외적인 경우가 아니라면 가이드 대로 하는것이 항상 옳아야 한다는 것이다.
4. 분량이 작아야 한다. 가이드란 도로의 로드맵(이정표)라고 할수 있다.  가이드가 100~200 페이지 정도 된다면 이정표라고 할수 없을 것이다. 그것은 메뉴얼에 가깝다.

위의 방법대로 Oracle 10g 파티션 가이드를 작성해보면 다음과 같이 한장으로 요약 할수 있다.

사용자 삽입 이미지

위의 로드맵을 한글로 표현하면 다음과 같다.

사용자 삽입 이미지

이상으로 2장짜리 Oracle 10g 파티션 가이드를 만들어 보았다. 한글로된 가이드를 필자가 작성하였고 그것을 보고 한 지인이 즉석에서 로드맵을 작성한 것 인데 필자가 그 로드맵의 일부분을 알기쉽게 수정한것이다.
아래는 이 가이드를 정리한 pdf 문서이다.
이 문서에는 추가적으로 파티션 적용시의 주의사항 까지 덤으로 있으니 반드시 다운 받아서 읽어 보기 바란다.
물론 문서를 배포시 출처를 밝히는 것은 기본이고 매너의 문제이다.

invalid-file

Oracle 10g Partition 적용가이드





편집후기 :
가이드를 작성 하고 보니 한가지가 위에서 언급한 가이드를 작성하는 방법1 에 위배된다.  대용량 혹은 소용량 테이블이 그것인데 이 용어는 필자로 서도 어쩔수 없다. 테이블의 용량이 어디서 부터 대용량이고 어디서 부터 소용량인지 구분하는 기준은 존재하지 않기 때문이다.

한가지 화두가 있는데 물리 모델링시에 모델러가 파티셔닝을 해야할까? 아니면 이것을 모델링의 개념이 아닌 튜닝의 개념으로 보아서 튜너 혹은 DBA 가 해야할까? 여러분은 어떻게 생각 하는가?

저작자표시 비영리 동일조건

'Oracle > Partition' 카테고리의 다른 글

오라클 Upgrade 후에 파티션을 만들 수 없어요  (0) 2011.03.15
Posted by extremedb
,

오라클은 몇년이나 갈까?

Oracle/News 2009. 7. 31. 13:17
오라클도 역사의 저편으로 사라지나?
HDB, NDB 등의 DBMS 는 아직까지 일부 시스템에서 사용하고 있으나 대세는 이미 RDB 로 넘어갔다.
RDBMS 는 90 대부터 약 20년간 꾸준히 사용되어 왔다. 하지만 오라클 같은 RDBMS 도 HDB 나 NDB 처럼 역사의 저편으로 갈날이 얼마 남지 않은것 같다. 미래에는 SQL 을 사용하며 오라클과 같은 RDBMS 를 사용하는것이 NDB 나 HDB 를 사용했던 것 처럼 구식이 되어버릴 것이다.

근거가 있냐고?
물론 있다. 아래의 2가지가 이미 실현되고 있기 때문이다. 필자는 아래의 2가지에 대해서는 전문가도 아니고 잘 알지도 못하지만 RDBMS 관련 일을 하는 사람으로서 이런 것들은 오라클과 같은 RDBMS 의 입장에서는 아주 위협적이라고 생각한다.

1. NoSQL 운동 
   SQL 과 DBMS 를 사용하지 않는 운동이다. 허무 맹랑해 보이지만 이미 많은 회사들이 오랫동안 웹 2.0 개발자들이 애용해 온 오픈소스 MySQL을 버리고 NoSQL을 대안으로 선택했는데, NoSQL의 장점은 그냥 지나치기에는 매우 우수하기 때문 이라고 한다. 더이상 RDBMS 를 사용하는것이 아니라 데이터 스토어 엔진인 카산드라(Cassandra) 를 이용하여 새로운 검색 기능을  개발 했다고 한다. 카산드라는 0.12ms 만에 50GB에 이르는 데이터 스토어를 디스크에 기록할 수 있는데, 이는 MySQL보다 2,500배 빠른 것이라 한다.


상세한 관련기사는 아래의 링크에서 확인 할수 있다.
출처 : "SQL 반대?" 부상하는 반 데이터베이스 운동


2. 구글의 Fusion Tables
    Fusion Tables 기능의 핵심은  데이터 스페이스 기술이다. 이 기술은 이른바 "바벨탑 문제" 를 해결 하였는데 바벨탑 문제란 서로 다른 포맷과 형식으로 저장된 데이터에 대한 액세스를 제공하고 인덱스를 생성하는 것을 말한다. RDBMS 에는 이런 기능이 없다.

데이터 스페이스 기술을 이용해 전통적인 2차원 데이터베이스 테이블에 3차원 좌표를 추가해 제품 리뷰나 블로그 포스트, 트위터 메시지 등의 요소를 수용할 수 있어야 하고, 여기에 실시간 업데이트라는 4차원 좌표도 구현해야 한다.

기술 분석가인 스테판 아놀드에 따르면  “이제 우리에게 4차원 공간인 하이퍼큐브가 있고, 이 공간에서 새로운 제품과 시장을 창출할 수 있는 새로운 종류의 쿼리를 수행할 수 있다”며, “IBM이나 오라클, 마이크로소프트에겐 최고의 악몽이 시작되는 것이다. 구글은 데이터 스페이스를 자동으로 구축하고 새로운 종류의 쿼리를 도입할 계획”이라고 덧붙였다.
 
Fusion Tables 관련기사는 아래의 링크에서 확인해볼수 있다.
출처 : 구글, 클라우드 기반 신개념 데이터베이스 테스트 중


문제는 상업화및 활성화의 시기 라는것
프로젝트를 하면서 오라클 같은 RDBMS를 사용하고 SQL을 작성 하는것이 당연하다고 생각 할것이다.하지만 미래에는 이러한 생각이 달라질수도 있다. 위의 것들이 상업화및 대중화에 성공한다면 말이다. 하지만 최소한 5~10년간은 위의 일들을 걱정할필요는 없는듯 하다. 오라클의 경우 상업화에 성공하는데만 약 20년 가까이 걸렸기 때문이다. 아무리 구글이라 할지라도 2~3년 내에 DBMS 시장에서 주류에 편입 되진 못할것이다.

여러분들은 위의 2가지에 대해서  어떻게 생각하는가?
RDBMS 에게 위협적인가? 아니면 의례적인 Anti 운동으로 보는가?

'Oracle > News' 카테고리의 다른 글

볼륨매니저는 역사 속으로  (12) 2009.09.30
Science of DataBase 1주년  (22) 2009.07.02
오라클 공룡기업으로 거듭나는가?  (0) 2009.04.21
Posted by extremedb
,

Oracle11g 에서 Full Table Scan의 성능 향상

Oracle/Data Access Pattern 2009. 7. 22. 03:40

문제는 수동이라는 점
예전에 필자가 Full Table Scan 의 비밀 이라는 글에서 _serial_direct_read 파라미터를 true 로 수정하여 Full Table Scan의 성능을 빠르게 한것을 기억하는가? 이것은 Full Table Scan시에 Disk 에서 데이터를 Scan 하여 SGA의 Buffer Cache에 적재한 후에 Logical Reads로 Fetch 를 하게되므로 성능이 저하될 수 있다. 이때 Disk 에서 Buffer Cache를 거치지 않고 바로 Fetch를 하면 성능향상이 가능한데 문제는 10g 까지는 항상 수동으로 파라미터를 수정하여야 한다는 것이다.

11g 부터 자동으로 바뀌어..
또한 테이블의 Size 가 중간크기인 경우 Direct I/O 를 하는것이 좋은지  Buffer Cache I/O 를 하는것이 좋은지 테스트를 해야만 한다. 하지만 Oracle 11g 부터 Direct I/O 를 수동으로 Control 할 필요가 없게 되었다. 11g 부터는 CBO(혹은 Cost Based 분석모듈)가 모든것을 알아서 한다. 이기능은 공식적으로 Cost Based Analysis of Direct I/O Access 라고 불린다.

2010 년 부터는 Oracle 11g 가 대세 라는데... 
세계적인 전문가들도 용어는 물론이며 이러한 기능의 허와 실을 모르고 있다. 전세계에서 손에 꼽는 사람들 까지도 ... 이번기회에 확실히 용어와 개념을 잡아서 선구자가 되길 바란다. 2010 년 부터는 Oracle 11g로 가는것이 대세가 될것이며 누구나 이기능을 사용하게 될것이기 때문이다.
그럼이제 11g 에서 Direct I/O가 어떻게 수행되는지 알아보자.

Oracle 11g 에서 Direct I/O 을 수행하기 위한 일반적인 SQL 의 수행과정은 아래와 같다.

1. SQL을 실행한다.

2. 옵티마이져가 DATA 를 FETCH 하기 위하여 최종 PLAN 을 만든다.

3. Direct I/O 의 후보인지 판별한다.
   예를 들면 full table scan 인가? , 테이블의 블럭사이즈가 _db_block_buffers 의 10% 보다 큰가? 등을 만족한다면 후보 이다.
   참고로 _small_table_threshold 파라미터는 아무상관도 없다. 값을 줄이거나 증가시켜서 테스트 해보기 바란다.

4. Direct I/O 의 후보라면 Cost Based 분석 모듈을 call 하여 Buffer Cache 를 엑세스 시의 Cost 와 Direct I/O 시의 Cost 를 비교하여 비용이 낮은것으로 선택한다. 4번의 로직은 중요하므로 아래의 Cost Based 분석 모듈에서 자세히 설명된다.

5. 4번의 비교에 의하여 Direct I/O를 한다고 판단되었다면 해당 테이블의 Commit 되지 않은 데이터가 Buffer Cache 에 있는경우 데이터를 Disk 로 내려쓴다.

6. 데이터를 Fetch 한다. 이때 Direct I/O 일 경우가 아니라면 Buffer Cache 에서 Fetch 하고 Cost Based 분석 모듈을 끝낸다.
   Direct I/O 일 경우 Disk 에서 Fetch 된다.  

7. Direct I/O 일 경우 4~6을 Fetch 단위마다 반복한다. Buffer Cache를 I/O 일 경우는 계속하여 Fetch만 하게된다.

이것이 전부이다. 7번 까지의 로직은 크게 어렵지 않은 것을 알수 있다. 다만 Direct I/O 를 수행할 경우 경우 잘못이해 하는 부분이 있는데 Disk 의 데이터를 PGA 로 내려쓰지 않는다는 것이다. _kdli_sio_pga 파라미터가 Default 로 False 이기 때문이다.

이제 4번에서 소개되었던 가장 중요한 Cost Based 분석 모듈을 소개할 차례다. 

이 모듈이 CBO 내의 모듈인지는 확실치 않다. 만약 아니라면 다양한 통계정보를 사용하여 Cost 를 구하는 최초의 모듈이 될것이다. 거의 CBO 의 기능에 준한다고 할수 있다. 과연 이것이 가능할까? 이점은 필자로서도 의문이다.
RAC의 경우 이모듈의 특징은 각 노드마다 모듈이 존재한다는 점이다. 각 노드마다 Cost 를 구해야 되기 때문이다.

401. SQL 이 Buffer 에 CACHING 하기 좋은 후보인지 알아본다.
     예를 들면 Buffer CACHE SIZE 가 충분한가? 자주 엑세스 되는가? 적어도 작은 부분이 CACHE 에 있는가?
     (자주 엑세스 되지만 CACHE 에 없을수 있다)

402. Buffer 에 CACHING 하기 좋은 후보라면 Cost Based 분석 모듈을 끝낸다. 이후로 Buffer Cache를 I/O 하게된다.

403. Direct I/O 가 NETWORK I/O 를 증가 시킨다면 Cost Based 분석 모듈을 끝낸다. 이후로 Buffer Cache를 I/O 하게된다.
     왜냐하면 Direct I/O를 해봐야 Fetch Call에 의해서 수행속도가 매우 저하되므로 Direct I/O를 할필요가 없는것이다.
     다행히 Insert~Select, 혹은 CTAS(Create Table as Select), Delete, Update 등은 NETWORK I/O 를 증가시키지
     않으므로 Direct I/O로 동작할 것이다. 이부분을 테스트 해보라. ROWNUM 을 계속 변경해서 테스트 하다보면 어느시점에
     Direct I/O 가 사라지게 된다. 필자가 Heurastic Rule 이 아닌 Cost Based 분석 모듈임을 확신하게 만든 대목이다.

404. Buffer Cache를 엑세스 하는 Cost가 Direct I/O 의 Cost + Commit 되지않은 데이터를 디스크에 쓰는 Cost 보다 큰지
      비교한다.

405. Buffer Cache 엑세스의 Cost가 크다면 Direct I/O 를 해야 하므로 Commit 되지않은 데이터를 디스크에 쓰고
      Cost Based 분석 모듈을 종료한다. 이때 Commit 된 데이터도 Buffer Cache에 남아 있을수 있는데 이 데이터 또한 Disk 로
      내려쓴다. Cost Based 분석 모듈이 체크포인트를 좋아하는 이유가 이것이다. 체크포인트를 만나면 디스크로 내려쓰므로
      Direct I/O 의 Cost 가 줄어들기 때문이다. 참고로 Log File Switch 가 발생하면 같은 효과를 볼수 없다.
      필자의 기억으로 (9i 혹은 8i 마지막 버젼 부터) Log File Switch 발생시 체크포인트가 발생하지 않을수 있기 떄문이다.

406. Commit 되지않은 데이터를 디스크에 쓰는 작업이 끝났으면 Cost Based 분석 모듈을 종료한다.

407. 만약 404번에서 Buffer Cache를 엑세스 하는 Cost가 비용이 적게든다면 이번에는 Direct I/O 의 Cost와 비교한다.

408. 407번에서 Buffer Cache를 엑세스 하는 Cost가 크다면 Direct I/O 의 후보 FLAG 를 Y 로 한다.
     이작업은 Cost Based 분석 모듈이 종료된 이후에 5~7번에서 FLAG 값을 이용하기 위함이다.

409. 408번에서 Direct I/O 의 후보 FLAG 를 Y 로 했다면 Buffer Cache를 엑세스 하기위한 준비를 하고 분석을 끝낸다.
     이경우는 Direct I/O 가 좋은지 Buffer Cache I/O 가 좋은지 확실히 알수 없기 때문에 일단 Buffer Cache I/O를 하고 다음번
     Fetch 시에 재평가 하게된다.

410. 407번에서 Buffer Cache를 엑세스 하는 Cost가 비용이 적게든다면 Buffer Cache를 엑세스 하기 위한 준비를 하고 분석을
       끝낸다.

Cost Based 분석 모듈의 모든기능을 설명하였다. 문제는 이렇게 구해진 Cost 가 Plan 상이나 10053 Event 의 Trace 상에 반영되지 않는다는 점이다. Plan 상에서는 Direct I/O를 로 수행되건 Buffer Cache I/O 로 수행되건 간에 Cost의 변화가 전혀 없는데 이것은 매우 아쉬운 부분이다.

Cost 가 반영되지 않을것 이라면 따로 보여주던지...
Cost 는 수행시간에 비례하는 개념인데 엑세스 방식이 바뀜에 따라 수행시간이 바뀜에도 불구하고 Cost 는 아무런 변화가 없는 것이다. 그럴것이라면 위에서 구한 Cost 라도 따로 보여 주었으면 하는것이 필자의 바램이다. 그렇지 않다면 Cost 와 현실과의 괴리감이 11g 에서 증폭될 것이기 때문이다. Plan 상이나 10053 Event 의 Trace 상에 변화가 나타나지 않으므로 Direct I/O를 확인 할수 있는 제일 위운 방법은 10046 이벤트 Trace에서 Direct Path Read 이벤트를 확인 하는것이다.
 
이제 Cost를 구하기 위해 참조되는 통계정보는 어떤것이 있는지 알아보자.
아래와 같이 CBO 혹은 CBAM(Cost Based Analysis Module)은 여러가지 통계정보들을 사용한다.

-디스크에 미쳐 쓰지못한 BUFFER CACHE 에 존재하는 데이터의 블럭수
-반드시 엑세스 해야하는 데이터의 BLOCK 수
-해당 테이블의 데이터가 Buffer Cache에 존재하는 데이터의 BLOCK 수
-RAC 의 경우 분산된 INSTANCE 의 DISK 블럭수와 CACHE 의 블럭수
-Direct I/O 시 하나의 블럭을 읽는 속도
-Buffer Cache I/O 시 하나의 블럭을 읽는 속도
-RAC 의 경우 다른 인스탄스의 CACHE 된 BLOCK 과 DISK 에 있는 BLOCK 을 가져오는 속도

지면의 한계상 Cost 를 구하는 공식(Formular)은 생략한다. 그것은 다음 기회에...
지면이 길어지면 도망가는 독자를 많이 보아 왔다.^^    

결론 :
우리는 이제 Oracle11g 의 새기능인 Cost Based Analysis of Direct I/O Access 에 대하여 알아보았으며 Cost Based 분석모듈이 어떻게 동작하는지도 알게 되었다. 한가지더 첨언 하자면 위의 기능이 끝이 아니다 라는 사실을 강조 하고 싶다.

11g 부터 Parallel 힌트를 사용한 Select 문이 Serial 하게 수행될수있다. 기준은 테이블의 블럭수가 _small_table_threshold
파라미터 보다 작아야 한다. 필자는 이기능을 더 좋아한다. 왜냐하면 덩치가 작은 테이블은 사실상 Parallel Query 가 필요없으며 오히려 수행속도가 저하되는 경우를 많이 보아왔기 떄문이다.

기억하자!
11g 부터는 Parallel SQL이 Serial로 수행될수 있으며 Parallel 을 사용하지 않는 Full Table Scan 이 Direct Path Read 가 가능하다는 사실을..

P.S
작년 봄부터 이글을 쓸지 고민하다가 이제서야 올리게 되었다. 위의 글이 난위도가 있기 때문에 쉽게 쓰려고 고민 한 것이다. 어려운 개념이지만 단 한명이라도 이글을 보고 이해한다면 필자의 기쁨이 될것이다.

아래의 link 는 유명한 오라클 Guru 인 Doug Burns의 블로그인데 몇년째 위에서 설명한 사실들을 몰라서 고생하고 있다.
누가 대신 설명해주길 바란다.(필자는 영어가 짧아서....)

http://oracledoug.com/serendipity/index.php?/archives/1321-11g-and-direct-path-reads.html
http://oracledoug.com/serendipity/index.php?/archives/1320-Parallel-Query-and-11g-Part-2.html

저작자표시 비영리 동일조건

'Oracle > Data Access Pattern' 카테고리의 다른 글

모든 상식을 의심하라  (16) 2009.11.05
Oracle Data Access Pattern을 정복하라  (18) 2009.10.29
InList / Concatnation / Range Scan Control 하기  (4) 2008.12.12
Range Partition 적용 테이블의 MIN/MAX 처리시의 성능저하 현상  (2) 2008.07.28
Re) LIKE '%XXX' 검색에 에 대한 오해와 진실  (2) 2008.06.23
Posted by extremedb
,

USE_CONCAT 힌트 제대로 알기

Oracle/SQL Tuning 2009. 7. 17. 00:07

글의 내용이 가슴에 와닫지 않는다는 독자가 있다. 필자의 잘못이다. 독자들을위하여 일부 내용을 댓글에 추가 하였으므로 반드시 확인하기 바란다. (2009.07.17)

대분분 튜닝을 해본 사람이면 USE_CONCAT 힌트를 잘 알고 있다고 생각할 것 이다. 하지만 문제는 얼마나 정확히 아는가가 중요하다. IN 이나 OR 조건이 있는 SQL에 USE_CONCAT 힌트를 사용하면 OR_Expansion(Union All 로 분리되는 쿼리변환) 이 발생한다는 것은 누구나 알것이다. 이것은 개발자들에 대한 일반적인 튜닝 가이드인것은 분명하다. 메뉴얼에는 분명히 이렇게 되어있다.

힌트 정확히 알기
이 힌트는 인자가 2개가 필요한데 튜닝을 전담하는 사람들까지도 이런 사실을 모르고 있으니 큰일이 아닐수 없다. 하지만 정확한 용법을 모르고 SQL을 튜닝을 하는 사람들을 비난할수는 없다. 그어떤 문서에도 USE_CONCAT의 용법이 자세히 나온 것이 없기 때문이다. 이럴경우 다양한 연구및 테스트를 진행 해보는수 밖에 없다. 아니면 SR 이라는 방법이 있기는 하다. 하지만 이러한 경우 SR 의 답변 성공률은 아주 낮다.

그럼 이제부터 용법을 하나씩 살펴보자.
환경 : 오라클 11.1.0.6
인덱스 상황 : employees(manager_id) , departments(department_id)

OR_Expansion 이 발생하지 않는 상황
아래의 SQL 은 departments 테이블에서 department_id 컬럼에 대해서 IN 조건이 있고 employees 테이블에서 manager_id 컬럼에 대해서 IN 조건이 있다. 따라서 USE_CONCAT 힌트를 사용한다면 UNION ALL 로 구분될수 있는 SQL 이 4개(departments 2개, employees 2개)가 나올것이다.

explain plan for
SELECT /*+ QB_NAME(MAIN) */
             e.employee_id, e.first_name, e.last_name,  d.department_name
  FROM employees e, departments d
 WHERE e.department_id = d.department_id
   AND (     d.department_id IN (:v_dept1, :v_dept2)
           OR e.manager_id IN (:v_manager1, :v_manager2)
          ) ;

---------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |    19 |   798 |     6  (17)|
|   1 |  MERGE JOIN                  |             |    19 |   798 |     6  (17)|
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENT  |    27 |   432 |     2   (0)|
|   3 |    INDEX FULL SCAN           | DEPT_ID_PK1 |    27 |       |     1   (0)|
|*  4 |   FILTER                     |             |       |       |            |
|*  5 |    SORT JOIN                 |             |   107 |  2782 |     4  (25)|
|   6 |     TABLE ACCESS FULL        | EMPLOYEE    |   107 |  2782 |     3   (0)|
---------------------------------------------------------------------------------
 
Plan 을 보면 OR_Expansion 이 발생하지 않았는데 옵티마이져는 Union All 로 분리하지 않는것이 가장 비용이 저렴했다는 것을 알수 있다.

그럼이제 USE_CONCAT 힌트를 사용해보자.

explain plan for
SELECT /*+ QB_NAME(MAIN) USE_CONCAT */
       e.employee_id, e.first_name, e.last_name,  d.department_name
  FROM employees e, departments d
 WHERE e.department_id = d.department_id
   AND (   d.department_id IN (:v_dept1, :v_dept2)
        OR e.manager_id IN (:v_manager1, :v_manager2)
       ) ;


------------------------------------------------------------------------------------------
| Id  | Operation                       | Name              | Rows  | Bytes | Cost (%CPU)|
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                   |    29 |  1218 |     9  (12)|
|   1 |  CONCATENATION                  |                   |       |       |            |
|   2 |   MERGE JOIN                    |                   |    12 |   504 |     5  (20)|
|   3 |    TABLE ACCESS BY INDEX ROWID  | DEPARTMENT        |    27 |   432 |     2   (0)|
|   4 |     INDEX FULL SCAN             | DEPT_ID_PK1       |    27 |       |     1   (0)|
|*  5 |    SORT JOIN                    |                   |    12 |   312 |     3  (34)|
|   6 |     INLIST ITERATOR             |                   |       |       |            |
|   7 |      TABLE ACCESS BY INDEX ROWID| EMPLOYEE          |    12 |   312 |     2   (0)|
|*  8 |       INDEX RANGE SCAN          | EMP_MANAGER_IX    |    12 |       |     1   (0)|
|   9 |   NESTED LOOPS                  |                   |       |       |            |
|  10 |    NESTED LOOPS                 |                   |    17 |   714 |     4   (0)|
|  11 |     INLIST ITERATOR             |                   |       |       |            |
|  12 |      TABLE ACCESS BY INDEX ROWID| DEPARTMENT        |     2 |    32 |     2   (0)|
|* 13 |       INDEX UNIQUE SCAN         | DEPT_ID_PK1       |     2 |       |     1   (0)|
|* 14 |     INDEX RANGE SCAN            | EMP_DEPARTMENT_IX |    10 |       |     0   (0)|
|* 15 |    TABLE ACCESS BY INDEX ROWID  | EMPLOYEE          |     9 |   234 |     1   (0)|
------------------------------------------------------------------------------------------
 
위의 Plan 을 보면 Id 기준으로 2번에 CONCATENATION 이 발생하였으므로 Union ALL 로 분리가 된것이다.
즉 옵티마이져는 SQL 을 아래와 같이 변형시킨것이다.

SELECT  e.employee_id, e.first_name, e.last_name, e.email, d.department_name
  FROM employee e, department d
 WHERE e.department_id = d.department_id
   AND e.manager_id IN (:v_manager1, :v_manager2)
UNION ALL  
SELECT  e.employee_id, e.first_name, e.last_name, e.email, d.department_name
  FROM employee e, department d
 WHERE e.department_id = d.department_id
   AND d.department_id IN (:v_dept1, :v_dept2)
   AND ( LNNVL(e.manager_id=:v_manager1) AND LNNVL(e.manager_id=:v_manager2) ) ;

Union All 로 분리된 아래쪽의 SQL 에서 LNNVL을 사용한 이유는 윗쪽의 SQL이 이미 e.manager_id IN (:v_manager1, :v_manager2) 조건을 만족하는 데이터에 대하여 SELECT 를 하였으므로 아래쪽에서 또다시 SELECT 되는것을 막기위함이다.

Inlist 에 대해서는 Union All 로 분리되지 않았다.
위의 PLAN 을 자세히 보면 e.manager_id IN (:v_manager1, :v_manager2) 조건에 대해서는 Union All 로 분리되지 않았다. d.department_id IN (:v_dept1, :v_dept2)  조건 또한 마찬가지이다. 하지만 이것은 전통적인 OR_Expansion 변환이 아니다. USE_CONCAT 힌트 대신에 RULE 힌트를 사용할 경우 Plan은 아래와 같다.
 
------------------------------------------------------------
| Id  | Operation                      | Name              |
------------------------------------------------------------
|   0 | SELECT STATEMENT               |                   |
|   1 |  CONCATENATION                 |                   |
|   2 |   NESTED LOOPS                 |                   |
|   3 |    NESTED LOOPS                |                   |
|   4 |     TABLE ACCESS BY INDEX ROWID| EMPLOYEE          |
|*  5 |      INDEX RANGE SCAN          | EMP_MANAGER_IX    |
|*  6 |     INDEX UNIQUE SCAN          | DEPT_ID_PK1       |
|   7 |    TABLE ACCESS BY INDEX ROWID | DEPARTMENT        |
|   8 |   NESTED LOOPS                 |                   |
|   9 |    NESTED LOOPS                |                   |
|  10 |     TABLE ACCESS BY INDEX ROWID| EMPLOYEE          |
|* 11 |      INDEX RANGE SCAN          | EMP_MANAGER_IX    |
|* 12 |     INDEX UNIQUE SCAN          | DEPT_ID_PK1       |
|  13 |    TABLE ACCESS BY INDEX ROWID | DEPARTMENT        |
|  14 |   NESTED LOOPS                 |                   |
|  15 |    TABLE ACCESS BY INDEX ROWID | DEPARTMENT        |
|* 16 |     INDEX UNIQUE SCAN          | DEPT_ID_PK1       |
|* 17 |    TABLE ACCESS BY INDEX ROWID | EMPLOYEE          |
|* 18 |     INDEX RANGE SCAN           | EMP_DEPARTMENT_IX |
|  19 |   NESTED LOOPS                 |                   |
|  20 |    TABLE ACCESS BY INDEX ROWID | DEPARTMENT        |
|* 21 |     INDEX UNIQUE SCAN          | DEPT_ID_PK1       |
|* 22 |    TABLE ACCESS BY INDEX ROWID | EMPLOYEE          |
|* 23 |     INDEX RANGE SCAN           | EMP_DEPARTMENT_IX |
------------------------------------------------------------
 
RULE 힌트를 적용한 Plan은 우리가 예상한대로 UNION ALL 로 구분될수 있는 SQL이 4개(departments 2개, employees 2개)가 생성 되었다. 결국 CBO는 Inlist Plan을 사용할수 있는 경우에는 OR_Expansion 변환을 수행하지 않음을 알수 있다. 하지만 이렇게 결말을 내기에는 너무 싱겁다.

PLAN 고정 시키기
CBO 상황에서 조건절에 Inlist 가 있을 경우 항상 OR_Expansion 변환을 수행하지 않게 PLAN을 고정 하려면 어떻게 하면 될까?
그냥 /*+ USE_CONCAT*/ 이렇게만 사용하면 되는 걸까?
위의 질문들을 해결하려면 DBMS_XPLAN.DISPLAY_CURSOR 함수를 사용해서 Outline Data 정보를 보면 된다.
 

SELECT /*+ GATHER_PLAN_STATISTICS QB_NAME(MAIN) USE_CONCAT */
            e.employee_id, e.first_name, e.last_name,  d.department_name
  FROM employees e, departments d
 WHERE e.department_id = d.department_id
   AND (     d.department_id IN (:v_dept1, :v_dept2)
           OR e.manager_id IN (:v_manager1, :v_manager2)
          ) ;

     
위의 SQL 에 대한 PLAN 은 USE_CONCAT 을 사용하였으므로 이미 수행한 SQL의 PLAN 과 동일하므로 생략하고  Outline Data만 보기로 하자.      
 
 Outline Data
-------------
 
  /*+
      ...중간생략
      USE_CONCAT(@"MAIN" 8)
      ...중간생략
  */
 
숫자 8의 의미
오라클이 내부적으로 USE_CONCAT 힌트에 인자 2개를 사용한것을 알수 있다. 첫번째 인자는 쿼리블럭명이고 두번째 인자인 8의 의미는 Inlist 를 사용할수 있는 경우에는 Union All 로 분리하지 말것을 강제하는 힌트이다.


자존심이 허락지 않는다.
여기서 한단계 더 나아가 보자. 이번에는 거꾸로 Inlist 를 사용한 경우에도 무조건 Union All 로 분리되게 할수 있을까?  RULE 힌트를 사용하라고? 그것은 언제 어떤버젼에서 없어질지 알수없는 아주 위험한 힌트이다.
또한 CBO 상황에서 이러한 힌트를 사용한다는 것은 자존심이 허락지 않는다.
아래의 SQL 을 보자.

SELECT /*+ GATHER_PLAN_STATISTICS QB_NAME(MAIN) USE_CONCAT(@MAIN 1) */
            e.employee_id, e.first_name, e.last_name,  d.department_name
  FROM employees e, departments d
 WHERE e.department_id = d.department_id
   AND (     d.department_id IN (:v_dept1, :v_dept2)
           OR e.manager_id IN (:v_manager1, :v_manager2)
           ) ;

숫자 1의 의미
USE_CONCAT 힌트에 숫자 1을 사용하였다. 이것이 의미하는 바는 가능한 경우 모두 Union All 로 분리하라는 뜻이다. 이제 Plan 을 보자.

--------------------------------------------------------------------------------------------
| Id  | Operation                      | Name              | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------
|   1 |  CONCATENATION                 |                   |      3 |00:00:00.01 |      12 |
|   2 |   NESTED LOOPS                 |                   |      0 |00:00:00.01 |       1 |
|   3 |    NESTED LOOPS                |                   |      0 |00:00:00.01 |       1 |
|   4 |     TABLE ACCESS BY INDEX ROWID| EMPLOYEE          |      0 |00:00:00.01 |       1 |
|*  5 |      INDEX RANGE SCAN          | EMP_MANAGER_IX    |      0 |00:00:00.01 |       1 |
|*  6 |     INDEX UNIQUE SCAN          | DEPT_ID_PK1       |      0 |00:00:00.01 |       0 |
|   7 |    TABLE ACCESS BY INDEX ROWID | DEPARTMENT        |      0 |00:00:00.01 |       0 |
|   8 |   NESTED LOOPS                 |                   |      0 |00:00:00.01 |       1 |
|   9 |    NESTED LOOPS                |                   |      0 |00:00:00.01 |       1 |
|  10 |     TABLE ACCESS BY INDEX ROWID| EMPLOYEE          |      0 |00:00:00.01 |       1 |
|* 11 |      INDEX RANGE SCAN          | EMP_MANAGER_IX    |      0 |00:00:00.01 |       1 |
|* 12 |     INDEX UNIQUE SCAN          | DEPT_ID_PK1       |      0 |00:00:00.01 |       0 |
|  13 |    TABLE ACCESS BY INDEX ROWID | DEPARTMENT        |      0 |00:00:00.01 |       0 |
|  14 |   NESTED LOOPS                 |                   |      2 |00:00:00.01 |       6 |
|  15 |    TABLE ACCESS BY INDEX ROWID | DEPARTMENT        |      1 |00:00:00.01 |       2 |
|* 16 |     INDEX UNIQUE SCAN          | DEPT_ID_PK1       |      1 |00:00:00.01 |       1 |
|* 17 |    TABLE ACCESS BY INDEX ROWID | EMPLOYEE          |      2 |00:00:00.01 |       4 |
|* 18 |     INDEX RANGE SCAN           | EMP_DEPARTMENT_IX |      2 |00:00:00.01 |       2 |
|  19 |   NESTED LOOPS                 |                   |      1 |00:00:00.01 |       4 |
|  20 |    TABLE ACCESS BY INDEX ROWID | DEPARTMENT        |      1 |00:00:00.01 |       2 |
|* 21 |     INDEX UNIQUE SCAN          | DEPT_ID_PK1       |      1 |00:00:00.01 |       1 |
|* 22 |    TABLE ACCESS BY INDEX ROWID | EMPLOYEE          |      1 |00:00:00.01 |       2 |
|* 23 |     INDEX RANGE SCAN           | EMP_DEPARTMENT_IX |      1 |00:00:00.01 |       1 |
--------------------------------------------------------------------------------------------

인자를 1로 바꾸자 IN 조건 혹은 OR 조건에 대하여 모두 Union All 로 분리 되었다. 이제 모든 궁금증이 해소 되었다.

결론 :
인자없이 힌트를 USE_CONCAT(@MAIN)으로 사용 한다면 모든경우의 수를 다 고려하여 가장 비용이 적게드는 Plan 을 선택 할것이다. 심지어 USE_CONCAT 힌트를 사용 하였지만 분리되지 않는 경우가 있는데 이것은 힌트를 무시한 것이 아니라 옵티마이져 입장에서 비용계산을 기준으로 가장 저렴한 PLAN 을 선택한것이다. 만약 힌트를 사용하였지만 Union ALL 로 분리가 안되며 이것 때문에 성능이 문제가 된다면 USE_CONCAT 힌트의 숫자 인자(1혹은 8)를 활용하여 적절하게 튜닝할수 있어야 한다.

힌트를 제대로 아는 것이 얼마나 중요한지 가슴에 와 닿아야 할것이다. 생각해보라 의사들이 수술용 칼이나 마취용 주사 같은 것을 규정에 맞게 아주 정밀하고 세밀하게 사용하지 않고 대충 사용한다면 큰일이 날수 도 있을 것이다. 힌트도 마찬가지로 생각해야 한다.  

'Oracle > SQL Tuning' 카테고리의 다른 글

오라클의 Update문은 적절한가?  (15) 2010.04.14
Connect By VS ANSI SQL  (7) 2010.02.11
분석함수를 이용한 TOP SQL은 튜닝이 불가한가?  (5) 2009.05.22
Top SQL-( Rownum 의 정확한 사용법 )  (2) 2009.03.26
인덱스없는 컬럼의 Order by 시 페이징 처리는 효율적인가?  (2) 2008.08.26
Posted by extremedb
,

간단한 집합개념 Test

Oracle/SQL Pattern 2009. 7. 6. 17:40

pdf 첨부파일에 일부 오류가 있어 수정해서 다시올립니다. 또한 Rollup 의 개념을 추가 하였습니다.
댓글을 참조 바랍니다. (2009.07.17)

테스트의 이유
SQL이 절차적 언어와 비교할때 가장 두드러 지는 특징은 두가지 이다.
첫번째는 집합처리가 된다는 점이며 두번째로는 처리순서및 로직이 필요 없다는 것이다.
오늘은 첫번째 특징인 집합처리에 대해서 간단한 테스트를 진행하려 한다.
이 테스트를 통과한 사람은 집합개념이 확실한 사람이라고 보면 된다.
필자가 이러한 테스트를 하는 이유는 실무에서 Group By Extension(Rollup, Cube, Grouping Sets) 를 잘사용할경우 Union 등을 없애고 같은 테이블을 반복해서 Scan 하는 비효율을 줄일수 있기 때문이다. 이렇게 되려면 최소한 Group By Extension들의 개념을 확실히 알필요가 있다.

먼저 문법을 알아야 한다.
먼저 이 테스트를 진행하기전에 RollUp, Cube, Grouping Sets 를 모른다면 테스트를 진행할수 없으므로 문법책을 먼저 보고오기 바란다. 위의 3가지 문법을 모두 다 안다고 보고 테스트를 진행할것이다.

먼저 답을 내는 방법을 알아보자.
아래와 같이 문제에 답을 하면 된다.

문제 : 아래의 SQL 을 논리적으로 풀어서 나타내시오.

SELECT A, B, SUM(C)
  FROM T1
GROUP BY ROLLUP(A, B)



답변 :

SELECT A, B, SUM(C)
  FROM T1
GROUP BY A, B
UNION ALL
SELECT A, NULL, SUM(C)
  FROM T1
GROUP BY A
UNION ALL
SELECT NULL, NULL, SUM(C)
 FROM T1
GROUP BY NULL



위의 답은 문제의 SQL 을 논리적으로 풀어서 재작성 한것이다. 바로 Rollup 의 개념을 물어본 것이다.
이런식으로 답을 하면 된다. 간단하지 않은가?

이제 시작해보자.
문제는 4가지이며 모두 논리적으로 풀어서 작성하면 된다. 모든 문제는 3줄짜리 SQL 이다.
제약조건은 한문제를 푸는데 3분을 넘기면 안된다.

문제1 :
SELECT A, B, C, SUM(D)
  FROM T1
GROUP BY C, GROUPING SETS(A, B)

문제2 :
SELECT A, B, C, SUM(D)
  FROM T1
GROUP BY CUBE(  (A, B), C )

문제3 :
SELECT A, B, C, SUM(D)
  FROM T1
GROUP BY GROUPING SETS(A,B), GROUPING SETS(C,D)

문제4:
SELECT A, B, C, SUM(D)
  FROM T1
GROUP BY C, GROUPING SETS( ROLLUP(A), ROLLUP(B) )



모두 맟추면 집합개념이 확실한 사람...
위의 4가지 문제를 모두 쉽게 푸는 사람은 집합개념이 확실한 사람이라고 보면 된다.
만약 쉽게 떠오르지 않거나 머리가 아프다면 아직 문법을 모르거나 집합개념이 확실히 서지 않은 사람이라고 보면 된다.
문제가 어렵다고 너무걱정 하지말자. 아래의 자료에서 문제풀이를 보면서 개념정립을 하면 되기 때문이다.
답은 아래에 첨부된 파일에 존재한다.
개인적으로 Group By Extension을 정리한 문서이므로 자유로이 배포해도 된다.
하지만 배포 할때는 꼭 출처를 밝혀주기 바란다.
참고로 아래의 그림은 첨부된 파일의 한부분 이며 1번문제의 모범답안 이다.


사용자 삽입 이미지


invalid-file

Group By Extension 개념정리





편집후기 :
"과연 이런것 까지 알아야 하나" 라는 질문이 들어 왔다. 여러분들은 어떻게 생각하는가?
다음과 같은 한마디로 답변을 대신하고 싶다.
"SQL 문법과 집합개념은 SQL 튜닝의 시작점이다."

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

Oracle Analytic Function의 모든 것  (20) 2010.03.29
오라클 Regular Expressions 완전정복  (22) 2009.10.07
스칼라 서브쿼리에서 Multi Column, Multi Row Return 하기.  (7) 2009.06.29
Model 절에 대하여  (10) 2009.05.28
SQL 포기하지마라.  (7) 2009.05.28
Posted by extremedb
,

티스토리툴바