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)를 시도했지만 실패했다는 것을 알 수 있음