by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

10053Event




10053 Event

  • Oracle 10g R2 부터는 10053 Event에서 CBQT를 포함한 Transformation의 모든 과정이 기록된다.
select /*+ */
  sum(x), count(y), sum(z)
from
  (
  select /*+ qb_name(depth1) */
    x, y, z, push_key, max_value, sum_value
  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 

Execution Plan
--------------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=8 Card=1 Bytes=41)
   1    0   SORT (AGGREGATE) (Card=1 Bytes=41)
   2    1     VIEW (Cost=8 Card=1 Bytes=41)
   3    2       HASH (GROUP BY) (Cost=8 Card=1 Bytes=91)
   4    3         HASH JOIN (Cost=7 Card=1 Bytes=91)
   5    4           MERGE JOIN (CARTESIAN) (Cost=4 Card=1 Bytes=65)
   6    5             TABLE ACCESS (FULL) OF 'T1' (TABLE) (Cost=2 Card=1 Bytes=39)
   7    5             BUFFER (SORT) (Cost=2 Card=1 Bytes=26)
   8    7               TABLE ACCESS (FULL) OF 'T3' (TABLE) (Cost=2 Card=1 Bytes=26)
   9    4           TABLE ACCESS (FULL) OF 'T2' (TABLE) (Cost=2 Card=1 Bytes=26)

access predicate 4 - "T1"."C1"="T2"."C1" AND "T2"."C1"="T3"."C1"
filter predicate 6 - "T1"."C2"=1

  • push_key = 1 조건이 안으로 Pushing 되었음
  • rownum 조건을 추가
    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 
    
    Execution Plan
    --------------------------------------------------------------------------------
       0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=8 Card=1 Bytes=52)
       1    0   SORT (AGGREGATE) (Card=1 Bytes=52)
       2    1     VIEW (Cost=8 Card=1 Bytes=52)
       3    2       COUNT
       4    3         VIEW (Cost=8 Card=1 Bytes=52)
       5    4           SORT (GROUP BY) (Cost=8 Card=1 Bytes=91)
       6    5             HASH JOIN (Cost=7 Card=1 Bytes=91)
       7    6               MERGE JOIN (CARTESIAN) (Cost=4 Card=1 Bytes=65)
       8    7                 TABLE ACCESS (FULL) OF 'T1' (TABLE) (Cost=2 Card=1 Bytes=39)
       9    7                 BUFFER (SORT) (Cost=2 Card=1 Bytes=26)
      10    9                   TABLE ACCESS (FULL) OF 'T3' (TABLE) (Cost=2 Card=1 Bytes=26)
      11    6               TABLE ACCESS (FULL) OF 'T2' (TABLE) (Cost=2 Card=1 Bytes=26)
    
    access predicate 6- "T1"."C1"="T2"."C1" AND "T2"."C1"="T3"."C1"
    
    filter predicate 2- "PUSH_KEY"=1
    
  • Inline View가 다 처리된 후 마지막 단계에서 Push_key = 1 조건이 처리된다는 것을 알 수 있음

*10054 Event를 사용하여 추적

.....
CVM : CVM bypassed : ORDER BY Clause
CVM : CVM bypassed : Rownum Column
  • ORDER BY 와 rownum으로 때문에 Complex View Merging이 이루어지지 않은 것을 알 수 있음
**************************
Predicate Move-Around (PM)
**************************
PM: Considering Predicate move-around in SEL$1(#1).
PM: Checking validity of Predicate move-around
PM:   PM bypassed : Reference to ROWNUM
PM: passed validity check.

  • Simple Predicate Pushing(또는 Predicate Move-Around)를 시도했지만 실패했다는 것을 알 수 있음

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.