10053 Event의 출력 결과

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

10053 Event의 사용 방법

  alter session set events '10053 trace name context forever, level 1';
      쿼리 실행;
  alter session set events '10053 trace name context off';

• trace 파일 위치 : User Dump Directory
• 주의 : 10053 Event는 Query에 대해 Hard Parse( 혹은 Optimization )가 발생할 때만 수행된다는 사실에 유의

최신버전의 trace파일 내용 순서

1. Query Block생성
2. Optimizer Parameter, Bug Fix Control(Oracle 10.2.0.2부터출력) 정보
3. Query Transformation과정
4. Statistics 정보
5. Single Table Access방식 선택
6. Join순서와 Join방식 선택

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

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

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).

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

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

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