태터데스크 관리자

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

태터데스크 메시지

저장하였습니다.

Oracle이 이상하다고 느낄 때 - Query Transformation

오라클 2008.08.30 10:33
Oracle이 사용하는 강력한 Transformation 기법 중 하나가 Join Predicate Pushing이다.

아래 Query를 보자. Inline View가 Union 절을 포함하고 있다.
View Merging이 이루어지지 않기 때문에 매우 비효율적인 실행 계획이 생성될 확률이 높다.


select /*+ gather_plan_statistics  */
  t1.c1, v.c3
from
  t1,
  (select c1, c3 from t2
   union all
   select c1, c3 from t3) v
where
  t1.c1 = v.c1 and
  t1.c3 = 1
;

이때 t1.c3 = 1 조건에 해당하는 t1.c1 값을 Union View 안으로 밀어 넣을 수(Push) 있다면 Union View를 최적화할 수 있다. Join Predicate Pushing이 수행되는 것이다.

하지만 불행하게도 Join Predicate Pushing이 수행되지 않는다. 아래 실행 계획을 보면 잘 알 수 있다.

@stat
------------------------------------------------------------
| Id  | Operation            | Name | Cost (%CPU)| Buffers |
------------------------------------------------------------
|*  1 |  HASH JOIN           |      |    27   (4)|      58 |
|*  2 |   TABLE ACCESS FULL  | T1   |    19   (0)|      38 |
|   3 |   VIEW               |      |     7   (0)|      20 |
|   4 |    UNION-ALL         |      |            |      20 |
|   5 |     TABLE ACCESS FULL| T2   |     4   (0)|      12 |
|   6 |     TABLE ACCESS FULL| T3   |     4   (0)|       8 |
------------------------------------------------------------
                                                           
Predicate Information (identified by operation id):        
---------------------------------------------------        
                                                           
   1 - access("T1"."C1"="V"."C1")                          
   2 - filter("T1"."C3"=1)    
                            

Union View가 완전히 별개의 View로 처리된다. 따라서 Full Table Scan을 수행하게 된다. 비효율적이다.

다음과 같이 PUSH_PRED Hint를 사용하면 Join Predicate를 Push할 수 있다.

select /*+ gather_plan_statistics push_pred(v) */
  t1.c1, v.c3
from
  t1,
  (select c1, c3 from t2
   union all
   select c1, c3 from t3) v
where
  t1.c1 = v.c1 and
  t1.c3 = 1
;

@stat
-----------------------------------------------------------------------
| Id  | Operation                      | Name  | Cost (%CPU)| Buffers |
-----------------------------------------------------------------------
|   1 |  NESTED LOOPS                  |       |    28   (0)|     361 |
|*  2 |   TABLE ACCESS FULL            | T1    |    19   (0)|      44 |
|   3 |   VIEW                         |       |     2   (0)|     317 |
|   4 |    UNION-ALL PARTITION         |       |            |     317 |
|   5 |     TABLE ACCESS BY INDEX ROWID| T2    |     2   (0)|     216 |
|*  6 |      INDEX RANGE SCAN          | T2_N1 |     1   (0)|     206 |
|   7 |     TABLE ACCESS BY INDEX ROWID| T3    |     2   (0)|     101 |
|*  8 |      INDEX RANGE SCAN          | T3_N1 |     1   (0)|     100 |
-----------------------------------------------------------------------
                                                            
Predicate Information (identified by operation id):         
---------------------------------------------------         
                                                            
   2 - filter("T1"."C3"=1)                                  
   6 - access("C1"="T1"."C1")                  <-- Join 조건이 Push되었다.   
   8 - access("C1"="T1"."C1")      
              <-- Join 조건이 Push되었다.


여기까지는 좋다.

누군가 이런 질문을 했다. 위와 같은 Query에서 B*Tree Index를 사용한 경우(t2.c1, t3.c1 컬럼)에는 Join Predicate Pushing이 성공적으로 이루어지는데 Bitmap Index를 사용하는 경우에는 Pushing이 이루어지지 않는다는 것이다.

심지어 PUSH_PRED Hint를 추가해도 마찬가지다.

-- what if we change the index to bitmap index
drop index t2_n1;
drop index t3_n1;
create bitmap index t2_n1 on t2(c1);
create bitmap index t3_n1 on t3(c1);

select /*+ gather_plan_statistics push_pred(v) */
  t1.c1, v.c3
from
  t1,
  (select c1, c3 from t2
   union all
   select c1, c3 from t3) v
where
  t1.c1 = v.c1 and
  t1.c3 = 1
;

@stat
------------------------------------------------------------
| Id  | Operation            | Name | Cost (%CPU)| Buffers |
------------------------------------------------------------
|*  1 |  HASH JOIN           |      |   103   (1)|      58 |
|*  2 |   TABLE ACCESS FULL  | T1   |    19   (0)|      38 |
|   3 |   VIEW               |      |    83   (0)|      20 |
|   4 |    UNION-ALL         |      |            |      20 |
|   5 |     TABLE ACCESS FULL| T2   |     4   (0)|      12 |
|   6 |     TABLE ACCESS FULL| T3   |     4   (0)|       8 |
------------------------------------------------------------
                                                           
Predicate Information (identified by operation id):        
---------------------------------------------------        
                                                           
   1 - access("T1"."C1"="V"."C1")                          
   2 - filter("T1"."C3"=1)                                 


이상한 일이다. 10053 Trace를 수행해도 별 정보가 기록되지 않는다. 그냥 Pushing이 무시될 뿐이다.
한가지 시도할 수 있는 것은 다음과 같이 좀 더 공격적으로 Hint를 사용하는 것이다.

-- what about with full hints?
select /*+ gather_plan_statistics
        push_pred(v) leading(t1) use_nl(t1)
        index(v.t2) index(v.t3) */
  t1.c1, v.c3
from
  t1,
  (select c1, c3 from t2
   union all
   select c1, c3 from t3) v
where
  t1.c1 = v.c1 and
  t1.c3 = 1
;

@stat
------------------------------------------------------------------------
| Id  | Operation                       | Name  | Cost (%CPU)| Buffers |
------------------------------------------------------------------------
|   1 |  NESTED LOOPS                   |       |   167   (0)|     361 |
|   2 |   TABLE ACCESS BY INDEX ROWID   | T1    |    33   (0)|      44 |
|*  3 |    INDEX RANGE SCAN             | T1_N2 |     1   (0)|       8 |
|   4 |   VIEW                          |       |   167   (0)|     317 |
|   5 |    UNION-ALL PARTITION          |       |            |     317 |
|   6 |     TABLE ACCESS BY INDEX ROWID | T2    |     1   (0)|     216 |
|   7 |      BITMAP CONVERSION TO ROWIDS|       |            |     206 |
|*  8 |       BITMAP INDEX SINGLE VALUE | T2_N1 |            |     206 |
|   9 |     TABLE ACCESS BY INDEX ROWID | T3    |     2   (0)|     101 |
|  10 |      BITMAP CONVERSION TO ROWIDS|       |            |     100 |
|* 11 |       BITMAP INDEX SINGLE VALUE | T3_N1 |            |     100 |
------------------------------------------------------------------------
                                                                       
Predicate Information (identified by operation id):                    
---------------------------------------------------                    
                                                                       
   3 - access("T1"."C3"=1)                                             
   8 - access("C1"="T1"."C1")                                          
  11 - access("C1"="T1"."C1")                                          


Pushing이 성공적으로 이루어졌다. 왜 이렇게까지 Hint를 과도하게 사용해야 할까?


실행 계획에서 얻을 수 있는 한가지 Hint는 Bitmap Index를 사용하는 경우 Pushing이 이루어지지 않는 경우, 즉 Full Table Scan이 사용된 경우에 비해 오히려 Cost가 더 높게 나온다는 것이다. (다행히 실제로 일량도 더 많다)


아마 Optimizer의 어떤 Code에서 Cost를 비교해서 Pushing여부를 판단하는 Logic에서 이런 일이 발생한 것으로 추론할 수  있다. 위와 같은 현상은 CBQT(Cost Based Query Transformation)을 비활성화시켜도 동일하게 발생한다. 따라서 CBQT Code가 아닌 Heuristic(Rule) QT Code에서 발생하는 것으로 결론낼 수 있다.

Optimizer를 제어하는 것이 생각보다 쉽지 않다는 것을 알 수 있으며, Hint의 사용 역시 때로는 의도하지 않게 복잡해진다는 것을 알 수 있는 좋은 예인거 같다.



Trackback 0 : Comments 5
  1. 쏘심이 2008.09.02 09:19 신고 Modify/Delete Reply

    좋은정보 감사합니다.

  2. extremedb 2008.09.03 22:57 신고 Modify/Delete Reply

    JPPD(join predicate push-down) 기능과 Partition View Plan 기능을 이용한 좋은 예제입니다.
    아마 실행 하신 Oracle 버젼이 10g R2 인걸로 예상 됩니다.
    10g R2 에서 Costing(CBQT가 작동하도록) 이 되도록 일부러 아우터 조인 트릭을 쓰기도 합니다.
    11g 에서는 JPPD 가 완벽한 CBQT 로 동작 합니다.
    더욱 좋은 것은 예전의 SVM(simple view merging) 에서 CVM(complex view merging)으로 진화한것 처럼
    JPPD 가 group-by, distinct, semi-/anti-joined view 등에서도 작동한다는 겁니다.
    이를 Extend_JPPD 라 부르는데 관련 파라미터는 _optimizer_extend_jppd_view_types 입니다.
    하지만 11g 에서도 여전히 OJPPD ( old-style (non-cost-based) JPPD ) 가 남아 있습니다.
    참고하시기 바랍니다.

  3. 욱짜 2008.09.04 09:31 신고 Modify/Delete Reply

    Test 환경은 10g R2가 맞습니다. 10g R1에서도 동일한 결과가 나오기는 합니다. 9i는 나중에 다시 확인해봐야습니다.

    좋은 정보 감사합니다.

  4. eric 2009.04.07 17:13 신고 Modify/Delete Reply

    좋은정보 감사합니다.
    저의 경우에는 10g R2 10.2.0.4.0 인데 jppd 기능이 구현되지 않고 있습니다.
    이런경우에는 patch를 해야하는건가요.
    아니면 어떤방법으로 해야하는지 궁금합니다.

  5. 욱짜 2009.04.07 19:43 신고 Modify/Delete Reply

    구현되지 않았다고 판단하신 테스트 케이스가 있을 것로 생각됩니다. 이 문제를 Ask Exem에 올려주시면 어떨까요?

    http://ask.ex-em.com

    다음과 같이 테스트 결과를 올려 주시면 좋겠습니다.

    create table ...
    create index ...

    explain plan for <jppd가 이루어지지 않는 query>;

    select * from table(dbms_xplan.display);
    ...

Write a comment