태터데스크 관리자

도움말
닫기
적용하기   첫페이지 만들기

태터데스크 메시지

저장하였습니다.

Enhanced 10053 Trace - Oracle 10gR2

오라클 2008.05.05 22:30
Oracle 10gR2의 Optimizer의 제어에 대한 개선에 대해 몇차례 언급한 바가 있다. 가장 대표적인 것이 opt_param 힌트와 _fix_control 파라미터이다.


또 하나의 중요한 개선이 10053 Trace 파일의 개선이다. 정확하게 말하면 Query Transformation 과정이 10053 Trace 파일에 추가되었다.

Query 최적화 과정은 보통 Query Transformation 단계와 Query Optimization 단계로 이루어진다. 10gR2 이전 버전에서는 Query Optimization 단계에서 발생하는 일만이 10053 Trace 파일에 기록되었다.

하지만, Query Transformation 단계의 혁신적인 발전(예를 들어 Cost Based Query Transformation)으로 인해 이전과 같이 단순한 몇 개의 공식만으로는 Oracle이 어떻게 왜 Query를 Transformation하는지 이해하기가 힘들어졌다. 점점 복잡해지는 Query Transformation 알고리즘으로 인해 버그로 인한 성능 저하 현상이 이전보다 더 광범위하게 발생하는 것또한 주목해야할 현상이다.

이런 이유 때문에 (아마도) Oracle의 CBO 개발팀에서 Query Transformation 단계에 대한 정보를 기록하기로 한 것으로 생각된다. 좋은 현상이다. 이 추가된 정보는 우리가 CBO를 좀 더 자세하게 분석하고 Troubleshooting할 수 있는 길을 제공한다.

아래와 같은 Query를 보자.

select
  sum(x), count(y), sum(z)
from
  (
  select /*+ qb_name(depth1) */
    x, y, z, push_key, max_value, sum_value, rownum as r
  from
    (
    select /*+ qb_name(depth2) */
      *
    from
      (
      select /*+ qb_name(depth3) */
        * 
      from
        (
        select /*+ qb_name(depth4) */
          t1.c1 as x, t2.c2 as y, t3.c2 as z, t1.c2 as push_key,
          max(t1.c3) as max_value,
          sum(t1.c3) as sum_value
        from
          t1, t2, t3
        where
          t1.c1 = t2.c1
          and t2.c1 = t3.c1
        group by
          t1.c1, t2.c2, t3.c2, t1.c2
        order by
          t1.c1, t2.c2, t3.c2, t1.c2
        )
      ) 
    )
  ) where push_key = 1
order by 1
;

이 Query의 작성자는 Complex View Merging이나 Predicate Pushing에 의해 [where push_key = 1] 조건이 depth4에 해당하는 Query의 조건안으로 들어갈 것을 기대했다. 하지만 실제 쿼리는 기대한대로 동작하지 않았고, 원하는 성능을 얻을 수 없었다.

경험이 많은 사람이라면 rownum으로 인해 Query Transformation이 이루어지지 않았다는 것을 쉽사리 짐작할 수 있겠지만, 만일 이 사실을 알지 못하거나 정확한 원인을 알고 싶다면 어떻게 해야할 것인가?

10gR2의 10053 Trace 파일은 Query Transformation이 실패한 원인을 정확하게 기록해 준다. 아래 내용을 보자.


*******************************
Cost-Based Complex View Merging
*******************************
CVM: Finding query blocks in SEL$1 (#1) that are valid to merge.
CVM:   Checking validity of merging SEL$753E1C9A (#2)
CVM:   Checking validity of merging DEPTH4 (#3)
Query block (083C7150) before join elimination:
SQL:******* UNPARSED QUERY IS *******
SELECT /*+ QB_NAME ("DEPTH4") */ "T1"."C1" "X","T2"."C2" "Y","T3"."C2" "Z","T1"."C2" "PUSH_KEY",MAX("T1"."C3") "MAX_VALUE",SUM("T1"."C3") "SUM_VALUE" FROM "UKJA"."T1" "T1","UKJA"."T2" "T2","UKJA"."T3" "T3" WHERE "T1"."C1"="T2"."C1" AND "T2"."C1"="T3"."C1" GROUP BY "T1"."C1","T2"."C2","T3"."C2","T1"."C2" ORDER BY "T1"."C1","T2"."C2","T3"."C2","T1"."C2"
Query block (083C7150) unchanged
CVM:     CVM bypassed: ORDER BY clause
CVM:     CVM bypassed: ROWNUM column


일단, order by에 의해 Complex View Merging이 실패했다는 것을 알 수 있다. rownum을 사용한 것 또한 Complex View Merging을 방해한다. Oracle은 Complex View Merging이 불가능할 경우에는 Predicate Pushing을 시도한다. 즉, Predicate 조건을 View 안으로 집어넣을려고 시도한다.(아래 Section을 보면 실제로는 Predicate Move-Around 과정으로 기술되는 것을 알 수 있다. 아마 내가 Predicate Pushing으로 이해하고 있는 과정이 실제로는 Predicate Move-Around를 포함하고 있는 것 같다)

**************************
Predicate Move-Around (PM)
**************************
PM: Considering predicate move-around in SEL$1 (#1).
PM:   Checking validity of predicate move-around in SEL$1 (#1).
PM:     PM bypassed: Reference to ROWNUM.
PM:   Passed validity checks.

하지만, Predicate의 위치를 바꾸는 시도 또한 rownum에 의해 실패하는 것을 확인할 수 있다. 즉, rownum으로 인해 적절한 Query Transformation이 일어나지 않았고 이로 인해 원하는 효과를 얻을 수 없었다. push_key = 1 조건이 Inline View 안으로 파고 들지 못함으로 인해 많은 양의 데이터를 View에서 먼저 처리하는 비효율적인 실행 계획이 생성되는 것이다.

위의 예를 보면 10gR2에서 추가된 정보가 경우에 따라 얼마나 유용한지를 잘 알 수 있다. 이제 10053 Trace를 봐야할 이유가 하나 더 늘어난 셈이다.



신고
tags :
Trackback 0 : Comments 2
  1. 강정식 2008.05.06 08:24 신고 Modify/Delete Reply

    안녕하세요 조동욱 이사님. 지난번에 lg전자 튜닝교육 하러 오셨을 때 세미나에 참석했었던 강정식이라고 합니다. 지난번 가이드 해주신 rownum이 이번 글에서 좀 더 명확하게 해석이 되어 더 좋은 이해가 된 것 같습니다. 앞으로도 좋은 글 많이 부탁드리겠습니다. 감사합니다 ^^

  2. 욱짜 2008.05.06 21:01 신고 Modify/Delete Reply

    반갑습니다. 도움이 되었다니 다행입니다. 앞으로 틈틈이 Oracle CBO에서의 이슈에 대해 논의하도록 하겠습니다.
    PS) 참고로, 저는 이사가 아닙니다. ^^; 그냥 조동욱님 이렇게 적어주시면 좋겠습니다.

Write a comment

티스토리 툴바