h2.10053 Event의 출력 결과

• CBO가 참조하는 기본 환경( Optimizer와 관련된 Parameter 및 Bug Fix Patch(?) )
• Query Transformation 과정( 복잡한 SQL문장이 어떻게 Transformation되는지, 혹은 왜 Transformation에 실패하는지에 대한 정보 )
• Query Optimization 과정( System 및 Object Statistics를 참조하는 일련의 과정과 Access Type의 결정, Join Type을 결정하는 과정 )

SCOTT> select /*+ gather_plan_statistics cost_based */
  2       t1.name, v1.name2 , v1.name2
  3   from t_pred1 t1, v_pred v1
  4   where
  5    t1.n = 1 and
  6    t1.id = v1.id2_1(+);

NAME                           NAME2                          NAME2
------------------------------ ------------------------------ ------------------------------
C_COBJ#                        C_COBJ#                        C_COBJ#
                                   생략
I_SYN1                         I_SYN1                         I_SYN1


SCOTT> select * from table(dbms_xplan.display_cursor( null, null, 'all allstats last' ));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------
---- -----------------------------------------------------------------------------------------------------------------------------------
SQL_ID  d2azwbuz9pfk5, child number 0
-------------------------------------
select /*+ gather_plan_statistics cost_based */      t1.name, v1.name2 , v1.name2  from t_pred1 t1, v_pred v1  where   t1.n = 1 and   t1.id =
v1.id2_1(+)

Plan hash value: 3337020919

------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name        | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------------------
|*  1 |  HASH JOIN OUTER        |             |      1 |     20 |   880 |    45   (5)| 00:00:01 |     20 |00:00:00.04 |     172 |   887K|   887K|  934K (0)|
|*  2 |   TABLE ACCESS FULL     | T_PRED1     |      1 |     20 |   280 |     3   (0)| 00:00:01 |     20 |00:00:00.01 |       3 |       |       |          |
|   3 |   VIEW                  | V_PRED      |      1 |  20000 |   585K|    42   (5)| 00:00:01 |  20000 |00:00:00.39 |     169 |       |       |          |
|*  4 |    HASH JOIN            |             |      1 |  20000 |   800K|    42   (5)| 00:00:01 |  20000 |00:00:00.27 |     169 |  1247K|  1247K|  993K (0)|
|   5 |     INDEX FAST FULL SCAN| T_PRED3_IDX |      1 |  20000 |   195K|    14   (0)| 00:00:01 |  20000 |00:00:00.30 |      61 |       |       |          |
|   6 |     TABLE ACCESS FULL   | T_PRED2     |      1 |  20000 |   605K|    26   (0)| 00:00:01 |  20000 |00:00:00.01 |     108 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   2 - SEL$1 / T1@SEL$1
   3 - SEL$2 / V1@SEL$1
   4 - SEL$2
   5 - SEL$2 / T3@SEL$2
   6 - SEL$2 / T2@SEL$2

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."ID"="V1"."ID2_1")
   2 - filter("T1"."N"=1)
   4 - access("T2"."ID"="T3"."ID" AND "T2"."ID2"="T3"."ID2")

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=1) "T1"."NAME"[VARCHAR2,30], "V1"."NAME2"[VARCHAR2,30]
   2 - "T1"."ID"[NUMBER,22], "T1"."NAME"[VARCHAR2,30]
   3 - "V1"."ID2_1"[NUMBER,22], "V1"."NAME2"[VARCHAR2,30]
   4 - (#keys=2) "T2"."ID"[NUMBER,22], "T2"."NAME"[VARCHAR2,30]
   5 - "T3"."ID"[NUMBER,22], "T3"."ID2"[NUMBER,22]
   6 - "T2"."ID"[NUMBER,22], "T2"."ID2"[NUMBER,22], "T2"."NAME"[VARCHAR2,30]


45 rows selected.

^test.sql

위의 Predicate정보는 다음과 같이 해석할 수 있다.

-2번 단계 filter("T1"."N"=1)에서 Table Full Scan을 통해 조건을 만족하는 Row를 Fetch
-4번 단계 ("T2"."ID"="T3"."ID" AND "T2"."ID2"="T3"."ID2")에서 Hash Join을 통해 조건을 만족하는 Row를 Fetch
-1번 단계 ("T1"."ID"="V1"."ID2_1")에서 Hash Outer Join을 통해 2번과 4번 단계의 Data들을 Join

위의 실행 계획의 문제점은 Join Predicate Pushing이 발생하지 않았다는 것이다. t1.n=1의 조건을 만족하는 Data에 대해서 Join조건(Join Predicate)을
View v_pred안으로 밀어 넣을 수(Pushing) 있다면 일량을 줄일 수 있는 기회가 있다.

• Join Predicate Pushing : Query Transformatino기법으로 인해 t2.id = t1.id라는 기존에 존재하지 않던 Join조건이 추가
• page. 76참고

위와 같은 상황에서 우리는 10053Event를 이용해서 왜 Query Transformation중에 Join Predicate Pushing이 발생하지 않았는지를 추적하는 것이다.

***************************************
GENERAL PLANS
***************************************
Considering cardinality-based initial join order.
***********************
Join order[1]:  T_PRED1[T1]#1  V_PRED[V1]#0
***************
Now joining: V_PRED[V1]#0
***************
NL Join
  Outer table: Card: 20.00  Cost: 3.00  Resp: 3.00  Degree: 1  Bytes: 14
  Inner table: V_PRED  Alias: V1
  Access Path: TableScan
    NL Join:  Cost: 83.05  Resp: 83.05  Degree: 0
      Cost_io: 83.00  Cost_cpu: 649401
      Resp_io: 83.00  Resp_cpu: 649401
  Best NL cost: 83.05
          resc: 83.05 resc_io: 83.00 resc_cpu: 649401
          resp: 83.05 resp_io: 83.00 resp_cpu: 649401
Outer Join Card:  20.00 = max ( outer (20.00), (outer (20.00) * inner (20000.00) * sel (5.0000e-05) )
Join cardinality for HJ/SMJ (no post filters):  400000.00, outer: 20.00, inner: 20000.00, sel: 1 
Join Card - Rounded: 20 Computed: 20.00
Best:: JoinMethod: NestedLoop
       Cost: 83.05  Degree: 1  Resp: 83.05  Card: 20.00  Bytes: 52
***********************
Best so far: Table#: 1  cost: 3.0042  card: 20.0000  bytes: 280
             Table#: 0  cost: 83.0542  card: 20.0000  bytes: 1040
(newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:2000
*********************************
Number of join permutations tried: 1
*********************************
Final - All Rows Plan:  Best join order: 1
  Cost: 83.0542  Degree: 1  Card: 20.0000  Bytes: 1040
  Resc: 83.0542  Resc_io: 83.0000  Resc_cpu: 649401
  Resp: 83.0542  Resp_io: 83.0000  Resc_cpu: 649401
kkoipt: Query block SEL$1 (#1)
******* UNPARSED QUERY IS *******
SELECT /*+ */ "T1"."NAME" "NAME","V1"."NAME2" "NAME2","V1"."NAME2" "NAME2" 
FROM "SCOTT"."T_PRED1" "T1",(SELECT /*+*/ "T2"."ID" "ID2_1","T2"."NAME" "NAME2" FROM "SCOTT"."T_PRED2" "T2","SCOTT"."T_PRED3" "T3" 
WHERE "T2"."ID"="T3"."ID" AND "T2"."ID2"="T3"."ID2"AND "T2"."ID"="T1"."ID"AND "T3"."ID"="T1"."ID") "V1" WHERE "T1"."N"=1
kkoqbc-end
          : call(in-use=132596, alloc=147368), compile(in-use=146608, alloc=148236)
CBQT: Saved costed qb# 2 (SEL$639F1A6F)
JPPD: New cost worse than best so far, Cost = 83.05
JPPD: Will not use JPPD from query block SEL$1 (#1).
JPPD: Applying transformation directives
JPPD: Checking validity of push-down in query block SEL$2 (#2)
JPPD:   No view found to push predicate into.
JPPD: Checking validity of push-down in query block SEL$1 (#1)
JPPD:   Pushdown from SEL$1 (#1) passed validity checks.
JPPD: Performing join predicate push-down (no transformation phase)
          From SEL$1 (#1) to SEL$2 (#2)
FPD: Considering simple filter push in SEL$1 (#1)
FPD:   Current where clause predicates in SEL$1 (#1) :
         "T1"."N"=1 AND "T1"."ID"="V1"."ID2_1"(+)
kkogcp: try to generate transitive predicate from check constraints for SEL$1 (#1)
predicates with check contraints: "T1"."N"=1 AND "T1"."ID"="V1"."ID2_1"(+)
after transitive predicate generation: "T1"."N"=1 AND "T1"."ID"="V1"."ID2_1"(+)
finally: "T1"."N"=1 AND "T1"."ID"="V1"."ID2_1"(+)
FPD: Considering simple filter push in SEL$2 (#2)
FPD:   Current where clause predicates in SEL$2 (#2) :
         "T2"."ID"="T3"."ID" AND "T2"."ID2"="T3"."ID2"
kkogcp: try to generate transitive predicate from check constraints for SEL$2 (#2)
predicates with check contraints: "T2"."ID"="T3"."ID" AND "T2"."ID2"="T3"."ID2"
after transitive predicate generation: "T2"."ID"="T3"."ID" AND "T2"."ID2"="T3"."ID2"
finally: "T2"."ID"="T3"."ID" AND "T2"."ID2"="T3"."ID2"
kkoqbc-start
            : call(in-use=132604, alloc=147368), compile(in-use=100312, alloc=148236)

^trace.txt

"T2"."ID"="T1"."ID"AND "T3"."ID"="T1"."ID" 부분과 JPPD: Will not use JPPD from query block SEL$1 (#1)부분에 주목하자.

=>Cost가 더 높기 때문에 JPPD를 수행 하지 않을것임을 의미한다.

h3.이런 류의 정보는 10053 Event가 아니면 얻을 수 없는 것이다.

문서에 대하여

• {*}이 문서의 내용은 조동욱님의 'Optimizing Oracle Optimizer'을 참고하였습니다.*