이 문서는 구루비에서 작성하였습니다.
이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
출처 : http://wiki.gurubee.net/display/CORE/10053+Event?
구루비 지식창고의 모든 문서는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.
TRACE 10053

특징과 용도

Query Transformation 단계에 대한 정보를 기록한다
Query Transformation이 실패한 원인을 정확하게 기록해 준다
Evnet 10053에서확인할 수 있는 옵티마이져 동작

(Hard Parse(혹은 Optimization)가 발생할 때만 수행된다.)
1단계 : Query Block을 출력
2단계 : Parameter 정보를 출력
   - Bug Fix Control정보를 출력한다.
      (Bug Fix Control에 따라 옵티마이져의 안정성또는 비정상적인 동작을 하는 경우 제어하기 위함)
         - 사 용 법 : Alter Session Set "_FIX_CONTROL" = '3746511:on','4519016:off'
         - 적용여부 : V$SYSTEM_FIX_CONTROL, V$SESSION_FIX_CONTROL
- OPT_PARAM Hint에 의해 변경된 Parameter 정보를 출력한다.
- /*+ opt_param('_optimizer_push_pred_cost_based','false') */
3단계 : QT(Query Transformation), CBQT(Cost Based Query Transformation)수행
   QT(Query Transformation) : Cost 계산이 불필요한 간단한 Transformation이 수행된다.
   CBQT(Cost Based Query Transformation) : Cost를 감안해서 Transformation이 수행된다.
      - Optimization단계에서 실제 Transformation이 이루어진다는 의미
        (Transformation단계와 Optimization단계의 융합)
   CBQT의 수행제어방법
    - alter session set "_optimizer_cost_based_transformation" = off;
        alter session set "_optimizer_push_pred_cost_based" = false;
      - select /*+ opt_param('_optimizer_push_pred_cost_based','false') */ ...
4단계 : Statistics정보를추출하고, Optimization을 위한 기본적인 정보를 계산
- Optimization의 기본단위는 Query Block
- CBO는 Query Level에서 Optimization을 수행하고 그 결과를 상위 Query Block에서 사용하는 방식을 사용
5단계 : Statistics정보를추출을 기준으로 Single Table Access 방식을 판단
Dynamic_Sampling Hint가 사용된 경우 Dynamic Sampling을 수행된다.
6단계 : join순서와 join 종류를 결정
Dump file d:\oracle\product\10.2.0\admin\orcl\udump\orcl_ora_4620.trc
Wed Feb 18 17:40:26 2009
ORACLE V10.2.0.3.0 - Production vsnsta=0
vsnsql=14 vsnxtr=3
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
Windows NT Version V6.0 Service Pack 1
CPU                 : 2 - type 586, 2 Physical Cores
Process Affinity    : 0x00000000
Memory (Avail/Total): Ph:892M/3045M, Ph+PgF:3339M/6292M, VA:1269M/2047M
Instance name: orcl

Redo thread mounted by this instance: 1

Oracle process number: 16

Windows thread id: 4620, image: ORACLE.EXE (SHAD)


*** 2009-02-18 17:40:26.985
*** ACTION NAME:() 2009-02-18 17:40:26.982
*** MODULE NAME:(SQL*Plus) 2009-02-18 17:40:26.982
*** SERVICE NAME:(SYS$USERS) 2009-02-18 17:40:26.982
*** SESSION ID:(133.132) 2009-02-18 17:40:26.982

SQL에서 제공된 쿼리블럭을 정의
Registered qb: MAIN_QUERY 0x8306808 (HINT MAIN_QUERY)
  signature (): qb_name=MAIN_QUERY nbfros=1 flg=0
    fro(0): flg=4 objn=55296 hint_alias="A"@"MAIN_QUERY"
Registered qb: SUB_QUERY 0x8305dec (HINT SUB_QUERY)
  signature (): qb_name=SUB_QUERY nbfros=1 flg=0
    fro(0): flg=4 objn=55330 hint_alias="B"@"SUB_QUERY"
**************************
Predicate Move-Around (PM)
**************************
PM: Considering predicate move-around in MAIN_QUERY (#0).
PM:   Checking validity of predicate move-around in MAIN_QUERY (#0).
CBQT: Validity checks passed for cjtzqad54bcmy.
apadrv-start: call(in-use=656, alloc=0), compile(in-use=39648, alloc=0)
******************************************
Current SQL statement for this session:
select /*+ gather_plan_statistics qb_name(main_query)*/
       a.col1, a.col2
  from t_plan a
 where (a.col1, a.col2) in (select /*+ parallel(b,8) qb_name(sub_query) */
                                   b.col1, b.col2
                              from t_plan2 b
                             where col1 = :ag_bind
                               and col2 <= 100)
*******************************************
Legend
The following abbreviations are used by optimizer trace.
CBQT - cost-based query transformation
JPPD - join predicate push-down
FPD - filter push-down
PM - predicate move-around
CVM - complex view merging
SPJ - select-project-join
SJC - set join conversion
SU - subquery unnesting
OBYE - order by elimination
ST - star transformation
qb - query block
LB - leaf blocks
DK - distinct keys
LB/K - average number of leaf blocks per key
DB/K - average number of data blocks per key
CLUF - clustering factor
NDV - number of distinct values
Resp - response cost
Card - cardinality
Resc - resource cost
NL - nested loops (join)
SM - sort merge (join)
HA - hash (join)
CPUCSPEED - CPU Speed
IOTFRSPEED - I/O transfer speed
IOSEEKTIM - I/O seek time
SREADTIM - average single block read time
MREADTIM - average multiblock read time
MBRC - average multiblock read count
MAXTHR - maximum I/O system throughput
SLAVETHR - average slave I/O throughput
dmeth - distribution method
  1: no partitioning required
  2: value partitioned
  4: right is random (round-robin)
  512: left is random (round-robin)
  8: broadcast right and partition left
  16: broadcast left and partition right
  32: partition left using partitioning of right
  64: partition right using partitioning of left
  128: use hash partitioning dimension
  256: use range partitioning dimension
  2048: use list partitioning dimension
  1024: run the join in serial
  0: invalid distribution method
sel - selectivity
ptn - partition

바인드변수의 값을 확인한다.
*******************************************
Peeked values of the binds in SQL statement
*******************************************
kkscoacd
 Bind#0
  oacdty=01 mxl=32(05) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1000000 frm=01 csi=846 siz=32 off=0
  kxsbbbfp=08417b34  bln=32  avl=05  flg=05
  value="Many2"

2단계 파라미터정보 출력
***************************************
PARAMETERS USED BY THE OPTIMIZER
********************************

  2단계 변경된 파라미터 정보출력
  *************************************
  PARAMETERS WITH ALTERED VALUES
  ******************************
  optimizer_mode                      = choose

  2단계 Bug Fix Control정보출력
  *********************************
  Bug Fix Control Environment
  ***************************
  fix  4611850 = enabled
  fix  4663804 = enabled
  fix  4663698 = enabled
  fix  4545833 = enabled
  fix  3499674 = disabled
  fix  4584065 = enabled
  fix  4602374 = enabled
  fix  4569940 = enabled
  fix  4631959 = enabled
  fix  4519340 = enabled
  fix  4550003 = enabled
  fix  4488689 = enabled
  fix  3118776 = enabled
  fix  4519016 = enabled
  fix  4487253 = enabled
  fix  4556762 = 15
  fix  4728348 = enabled
  fix  4723244 = enabled
  fix  4554846 = enabled
  fix  4175830 = enabled
  fix  4722900 = enabled
  fix  5094217 = enabled
  fix  4904890 = enabled
  fix  4483286 = disabled
  fix  4969880 = disabled
  fix  4711525 = enabled
  fix  4717546 = enabled
  fix  4904838 = enabled
  fix  5005866 = enabled
  fix  4600710 = enabled
  fix  5129233 = enabled
  fix  5195882 = enabled
  fix  5084239 = enabled
  fix  4595987 = enabled
  fix  4134994 = enabled
  fix  5104624 = enabled
  fix  4908162 = enabled
  fix  5015557 = enabled
  fix  5240607 = enabled

  2단계 Default 파라미터 정보 출력
  *************************************
  PARAMETERS WITH DEFAULT VALUES
  ******************************
  optimizer_mode_hinted               = false
  optimizer_features_hinted           = 0.0.0
  parallel_execution_enabled          = true
  parallel_query_forced_dop           = 0
  parallel_dml_forced_dop             = 0
  parallel_ddl_forced_degree          = 0
  parallel_ddl_forced_instances       = 0
  _query_rewrite_fudge                = 90
  optimizer_features_enable           = 10.2.0.3
  _optimizer_search_limit             = 5
  cpu_count                           = 2
  active_instance_count               = 1
  parallel_threads_per_cpu            = 2
  hash_area_size                      = 131072
  bitmap_merge_area_size              = 1048576
  sort_area_size                      = 65536
  sort_area_retained_size             = 0
  _sort_elimination_cost_ratio        = 0
  _optimizer_block_size               = 8192
  _sort_multiblock_read_count         = 2
  _hash_multiblock_io_count           = 0
  _db_file_optimizer_read_count       = 128
  _optimizer_max_permutations         = 2000
  pga_aggregate_target                = 198656 KB
  _pga_max_size                       = 204800 KB
  _query_rewrite_maxdisjunct          = 257
  _smm_auto_min_io_size               = 56 KB
  _smm_auto_max_io_size               = 248 KB
  _smm_min_size                       = 198 KB
  _smm_max_size                       = 39731 KB
  _smm_px_max_size                    = 99328 KB
  _cpu_to_io                          = 0
  _optimizer_undo_cost_change         = 10.2.0.3
  parallel_query_mode                 = enabled
  parallel_dml_mode                   = disabled
  parallel_ddl_mode                   = enabled
  sqlstat_enabled                     = false
  _optimizer_percent_parallel         = 101
  _always_anti_join                   = choose
  _always_semi_join                   = choose
  _optimizer_mode_force               = true
  _partition_view_enabled             = true
  _always_star_transformation         = false
  _query_rewrite_or_error             = false
  _hash_join_enabled                  = true
  cursor_sharing                      = exact
  _b_tree_bitmap_plans                = true
  star_transformation_enabled         = false
  _optimizer_cost_model               = choose
  _new_sort_cost_estimate             = true
  _complex_view_merging               = true
  _unnest_subquery                    = true
  _eliminate_common_subexpr           = true
  _pred_move_around                   = true
  _convert_set_to_join                = false
  _push_join_predicate                = true
  _push_join_union_view               = true
  _fast_full_scan_enabled             = true
  _optim_enhance_nnull_detection      = true
  _parallel_broadcast_enabled         = true
  _px_broadcast_fudge_factor          = 100
  _ordered_nested_loop                = true
  _no_or_expansion                    = false
  optimizer_index_cost_adj            = 100
  optimizer_index_caching             = 0
  _system_index_caching               = 0
  _disable_datalayer_sampling         = false
  query_rewrite_enabled               = true
  query_rewrite_integrity             = enforced
  _query_cost_rewrite                 = true
  _query_rewrite_2                    = true
  _query_rewrite_1                    = true
  _query_rewrite_expression           = true
  _query_rewrite_jgmigrate            = true
  _query_rewrite_fpc                  = true
  _query_rewrite_drj                  = true
  _full_pwise_join_enabled            = true
  _partial_pwise_join_enabled         = true
  _left_nested_loops_random           = true
  _improved_row_length_enabled        = true
  _index_join_enabled                 = true
  _enable_type_dep_selectivity        = true
  _improved_outerjoin_card            = true
  _optimizer_adjust_for_nulls         = true
  _optimizer_degree                   = 0
  _use_column_stats_for_function      = true
  _subquery_pruning_enabled           = true
  _subquery_pruning_mv_enabled        = false
  _or_expand_nvl_predicate            = true
  _like_with_bind_as_equality         = false
  _table_scan_cost_plus_one           = true
  _cost_equality_semi_join            = true
  _default_non_equality_sel_check     = true
  _new_initial_join_orders            = true
  _oneside_colstat_for_equijoins      = true
  _optim_peek_user_binds              = true
  _minimal_stats_aggregation          = true
  _force_temptables_for_gsets         = false
  workarea_size_policy                = auto
  _smm_auto_cost_enabled              = true
  _gs_anti_semi_join_allowed          = true
  _optim_new_default_join_sel         = true
  optimizer_dynamic_sampling          = 2
  _pre_rewrite_push_pred              = true
  _optimizer_new_join_card_computation = true
  _union_rewrite_for_gs               = yes_gset_mvs
  _generalized_pruning_enabled        = true
  _optim_adjust_for_part_skews        = true
  _force_datefold_trunc               = false
  statistics_level                    = typical
  _optimizer_system_stats_usage       = true
  skip_unusable_indexes               = true
  _remove_aggr_subquery               = true
  _optimizer_push_down_distinct       = 0
  _dml_monitoring_enabled             = true
  _optimizer_undo_changes             = false
  _predicate_elimination_enabled      = true
  _nested_loop_fudge                  = 100
  _project_view_columns               = true
  _local_communication_costing_enabled = true
  _local_communication_ratio          = 50
  _query_rewrite_vop_cleanup          = true
  _slave_mapping_enabled              = true
  _optimizer_cost_based_transformation = linear
  _optimizer_mjc_enabled              = true
  _right_outer_hash_enable            = true
  _spr_push_pred_refspr               = true
  _optimizer_cache_stats              = false
  _optimizer_cbqt_factor              = 50
  _optimizer_squ_bottomup             = true
  _fic_area_size                      = 131072
  _optimizer_skip_scan_enabled        = true
  _optimizer_cost_filter_pred         = false
  _optimizer_sortmerge_join_enabled   = true
  _optimizer_join_sel_sanity_check    = true
  _mmv_query_rewrite_enabled          = true
  _bt_mmv_query_rewrite_enabled       = true
  _add_stale_mv_to_dependency_list    = true
  _distinct_view_unnesting            = false
  _optimizer_dim_subq_join_sel        = true
  _optimizer_disable_strans_sanity_checks = 0
  _optimizer_compute_index_stats      = true
  _push_join_union_view2              = true
  _optimizer_ignore_hints             = false
  _optimizer_random_plan              = 0
  _query_rewrite_setopgrw_enable      = true
  _optimizer_correct_sq_selectivity   = true
  _disable_function_based_index       = false
  _optimizer_join_order_control       = 3
  _optimizer_cartesian_enabled        = true
  _optimizer_starplan_enabled         = true
  _extended_pruning_enabled           = true
  _optimizer_push_pred_cost_based     = true
  _sql_model_unfold_forloops          = run_time
  _enable_dml_lock_escalation         = false
  _bloom_filter_enabled               = true
  _update_bji_ipdml_enabled           = 0
  _optimizer_extended_cursor_sharing  = udo
  _dm_max_shared_pool_pct             = 1
  _optimizer_cost_hjsmj_multimatch    = true
  _optimizer_transitivity_retain      = true
  _px_pwg_enabled                     = true
  optimizer_secure_view_merging       = true
  _optimizer_join_elimination_enabled = true
  flashback_table_rpi                 = non_fbt
  _optimizer_cbqt_no_size_restriction = true
  _optimizer_enhanced_filter_push     = true
  _optimizer_filter_pred_pullup       = true
  _rowsrc_trace_level                 = 0
  _simple_view_merging                = true
  _optimizer_rownum_pred_based_fkr    = true
  _optimizer_better_inlist_costing    = all
  _optimizer_self_induced_cache_cost  = false
  _optimizer_min_cache_blocks         = 10
  _optimizer_or_expansion             = depth
  _optimizer_order_by_elimination_enabled = true
  _optimizer_outer_to_anti_enabled    = true
  _selfjoin_mv_duplicates             = true
  _dimension_skip_null                = true
  _force_rewrite_enable               = false
  _optimizer_star_tran_in_with_clause = true
  _optimizer_complex_pred_selectivity = true
  _optimizer_connect_by_cost_based    = true
  _gby_hash_aggregation_enabled       = true
  _globalindex_pnum_filter_enabled    = true
  _fix_control_key                    = 0
  _optimizer_skip_scan_guess          = false
  _enable_row_shipping                = false
  _row_shipping_threshold             = 80
  _row_shipping_explain               = false
  _optimizer_rownum_bind_default      = 10
  _first_k_rows_dynamic_proration     = true
  _optimizer_native_full_outer_join   = off
  *********************************
  Bug Fix Control Environment
  ***************************
  fix  4611850 = enabled
  fix  4663804 = enabled
  fix  4663698 = enabled
  fix  4545833 = enabled
  fix  3499674 = disabled
  fix  4584065 = enabled
  fix  4602374 = enabled
  fix  4569940 = enabled
  fix  4631959 = enabled
  fix  4519340 = enabled
  fix  4550003 = enabled
  fix  4488689 = enabled
  fix  3118776 = enabled
  fix  4519016 = enabled
  fix  4487253 = enabled
  fix  4556762 = 15
  fix  4728348 = enabled
  fix  4723244 = enabled
  fix  4554846 = enabled
  fix  4175830 = enabled
  fix  4722900 = enabled
  fix  5094217 = enabled
  fix  4904890 = enabled
  fix  4483286 = disabled
  fix  4969880 = disabled
  fix  4711525 = enabled
  fix  4717546 = enabled
  fix  4904838 = enabled
  fix  5005866 = enabled
  fix  4600710 = enabled
  fix  5129233 = enabled
  fix  5195882 = enabled
  fix  5084239 = enabled
  fix  4595987 = enabled
  fix  4134994 = enabled
  fix  5104624 = enabled
  fix  4908162 = enabled
  fix  5015557 = enabled
  fix  5240607 = enabled
  ***************************************
  PARAMETERS IN OPT_PARAM HINT
  ****************************
***************************************
Column Usage Monitoring is ON: tracking level = 1
***************************************

3단계 QT단계실행
********************************
COST-BASED QUERY TRANSFORMATIONS
********************************
FPD: Considering simple filter push (pre rewrite) in MAIN_QUERY (#0)
FPD:   Current where clause predicates in MAIN_QUERY (#0) :
         ("A"."COL1","A"."COL2")=ANY (SELECT /*+ QB_NAME ("SUB_QUERY") PARALLEL ("B",8) */ "B"."COL1","B"."COL2" FROM "T_PLAN2" "B")
Registered qb: MAIN_QUERY 0x830410c (COPY MAIN_QUERY)
  signature(): NULL
Registered qb: SUB_QUERY 0x8301500 (COPY SUB_QUERY)
  signature(): NULL

3단계 CBQT단계실행
*****************************
Cost-Based Subquery Unnesting
*****************************
SU: No subqueries to consider in query block SUB_QUERY (#2).
SU: Considering subquery unnesting in query block MAIN_QUERY (#1)
SU: Performing unnesting that does not require costing.
SU: Considering subquery unnest on MAIN_QUERY (#1).
SU:   Checking validity of unnesting subquery SUB_QUERY (#2)
SU:   Passed validity checks.
SU:   Transforming ANY subquery to a join.
Registered qb: SEL$DBE3B336 0x830410c (SUBQUERY UNNEST MAIN_QUERY; SUB_QUERY)
  signature (): qb_name=SEL$DBE3B336 nbfros=2 flg=0
    fro(0): flg=0 objn=55296 hint_alias="A"@"MAIN_QUERY"
    fro(1): flg=0 objn=55330 hint_alias="B"@"SUB_QUERY"
*******************************
Cost-Based Complex View Merging
*******************************
CVM: Finding query blocks in SEL$DBE3B336 (#1) that are valid to merge.
SU:   Transforming ANY subquery to a join.
*************************
Set-Join Conversion (SJC)
*************************
SJC: Considering set-join conversion in SEL$DBE3B336 (#1).
Query block (08306808) before join elimination:
SQL:
Query block (08306808) unchanged
**************************
Predicate Move-Around (PM)
**************************
PM: Considering predicate move-around in SEL$DBE3B336 (#1).
PM:   Checking validity of predicate move-around in SEL$DBE3B336 (#1).
PM:     PM bypassed: Outer query contains no views.
JPPD: Applying transformation directives
JPPD: Checking validity of push-down in query block SEL$DBE3B336 (#1)
JPPD:   No view found to push predicate into.
FPD: Considering simple filter push in SEL$DBE3B336 (#1)
FPD:   Current where clause predicates in SEL$DBE3B336 (#1) :
         "A"."COL1"="B"."COL1" AND "A"."COL2"="B"."COL2" AND "B"."COL1"=:B1 AND "B"."COL2"<=100
kkogcp: try to generate transitive predicate from check constraints for SEL$DBE3B336 (#1)
predicates with check contraints: "A"."COL1"="B"."COL1" AND "A"."COL2"="B"."COL2" AND "B"."COL1"=:B1 AND "B"."COL2"<=100 AND "A"."COL1"=:B2 AND "A"."COL2"<=100
after transitive predicate generation: "A"."COL1"="B"."COL1" AND "A"."COL2"="B"."COL2" AND "B"."COL1"=:B1 AND "B"."COL2"<=100 AND "A"."COL1"=:B2 AND "A"."COL2"<=100
finally: "A"."COL1"="B"."COL1" AND "A"."COL2"="B"."COL2" AND "B"."COL1"=:B1 AND "B"."COL2"<=100 AND "A"."COL1"=:B2 AND "A"."COL2"<=100
FPD:   Following transitive predicates are generated in SEL$DBE3B336 (#1) :
         "A"."COL1"=:B1 AND "A"."COL2"<=100
kkoqbc-start
            : call(in-use=1124, alloc=0), compile(in-use=49448, alloc=0)
****************
QUERY BLOCK TEXT
****************
select /*+ gather_plan_statistics qb_name(main_query)*/
       a.col1, a.col2
  from t_plan a
 where (a.col1, a.col2) in (select /*+ parallel(b,8) qb_name(sub_query) */
                                   b.col1, b.col2
                              from t_plan2 b
                             where col1 = :ag_bind
                               and col2 <= 100)

4단계 쿼리블록 정보계산
*********************
QUERY BLOCK SIGNATURE
*********************
qb name was generated
signature (optimizer): qb_name=SEL$DBE3B336 nbfros=2 flg=0
  fro(0): flg=0 objn=55296 hint_alias="A"@"MAIN_QUERY"
  fro(1): flg=0 objn=55330 hint_alias="B"@"SUB_QUERY"

4단계 시스템통계정보조사
*****************************
SYSTEM STATISTICS INFORMATION
*****************************
  Using NOWORKLOAD Stats
  CPUSPEED: 1280 millions instruction/sec
  IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
  IOSEEKTIM: 17 milliseconds (default is 10)

4단계 오브젝트(딕셔너리)통계정보조사
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: T_PLAN  Alias:  A
    #Rows: 10000  #Blks:  43  AvgRowLen:  9.00
  Column (#1): COL1(VARCHAR2)
    AvgLen: 6.00 NDV: 1 Nulls: 0 Density: 5.0000e-005
    Histogram: Freq  #Bkts: 1  UncompBkts: 10000  EndPtVals: 1
  Column (#2): COL2(NUMBER)
    AvgLen: 4.00 NDV: 10000 Nulls: 0 Density: 1.0000e-004 Min: 1 Max: 10000
Index Stats::
  Index: T_PLAN_IDX  Col#: 1
    LVLS: 1  #LB: 31  #DK: 1  LB/K: 31.00  DB/K: 21.00  CLUF: 21.00
***********************
Table Stats::
  Table: T_PLAN2  Alias:  B  Partition [0]
    #Rows: 2999  #Blks:  13  AvgRowLen:  9.00
    #Rows: 2999  #Blks:  13  AvgRowLen:  9.00

5단계 single table access방식을 판단
***************************************
SINGLE TABLE ACCESS PATH
  Column (#1): COL1(VARCHAR2)  Part#: 0
    AvgLen: 6.00 NDV: 1 Nulls: 0 Density: 1.6672e-004
    Histogram: Freq  #Bkts: 1  UncompBkts: 2999  EndPtVals: 1
  Column (#1): COL1(VARCHAR2)
    AvgLen: 6.00 NDV: 1 Nulls: 0 Density: 1.6672e-004
    Histogram: Freq  #Bkts: 1  UncompBkts: 2999  EndPtVals: 1
  Column (#2): COL2(NUMBER)  Part#: 0
    AvgLen: 4.00 NDV: 2999 Nulls: 0 Density: 3.3344e-004 Min: 1 Max: 2999
  Column (#2): COL2(NUMBER)
    AvgLen: 4.00 NDV: 2999 Nulls: 0 Density: 3.3344e-004 Min: 1 Max: 2999
  Table: T_PLAN2  Alias: B
    Card: Original: 2999  Rounded: 1  Computed: 0.02  Non Adjusted: 0.02
  Access Path: TableScan
    Cost:  3.03  Resp: 2.00  Degree: 0
      Cost_io: 3.00  Cost_cpu: 659805
      Resp_io: 2.00  Resp_cpu: 91640
  Best:: AccessPath: TableScan
         Cost: 2.00  Degree: 8  Resp: 2.00  Card: 0.02  Bytes: 0
***************************************
SINGLE TABLE ACCESS PATH
  Table: T_PLAN  Alias: A
    Card: Original: 10000  Rounded: 1  Computed: 0.01  Non Adjusted: 0.01
  Access Path: TableScan
    Cost:  6.10  Resp: 6.10  Degree: 0
      Cost_io: 6.00  Cost_cpu: 2506247
      Resp_io: 6.00  Resp_cpu: 2506247
  Access Path: index (AllEqRange)
    Index: T_PLAN_IDX
    resc_io: 2.00  resc_cpu: 15488
    ix_sel: 5.0000e-005  ix_sel_with_filters: 5.0000e-005
    Cost: 2.00  Resp: 2.00  Degree: 1
  Best:: AccessPath: IndexRange  Index: T_PLAN_IDX
         Cost: 2.00  Degree: 1  Resp: 2.00  Card: 0.01  Bytes: 0
Multi-column join key card: 10000  #cols: 2  table: T_PLAN
Multi-column join key card: 9999  #cols: 2  table: T_PLAN2

6단계 조인순서와 join종류를 결정
***************************************
OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***************************************
Considering cardinality-based initial join order.
Permutations for Starting Table :0
***********************
Join order[1]:  T_PLAN[A]#0  T_PLAN2[B]#1
***************
Now joining: T_PLAN2[B]#1
***************
NL Join
  Outer table: Card: 0.01  Cost: 2.00  Resp: 2.00  Degree: 1  Bytes: 9
  Inner table: T_PLAN2  Alias: B
  Access Path: TableScan
    NL Join:  Cost: 5.03  Resp: 4.00  Degree: 100
      Cost_io: 5.00  Cost_cpu: 675293
      Resp_io: 4.00  Resp_cpu: 107127
  Best NL cost: 4.00
          resc: 5.03 resc_io: 5.00 resc_cpu: 675293
          resp: 4.00 resp_io: 4.00 resp_cpu: 107127
Using multi-column join key sanity check for table T_PLAN
Revised join sel:1.0000e-004 = 1 * (1/10000.00) * (1/1)
Semi Join Card:  0.00 = outer (0.01) * sel (1.0000e-004)
Join Card - Rounded: 1 Computed: 0.00
SM Join
  Outer table:
    resc: 2.00  card 0.01  bytes: 9  deg: 1  resp: 2.00
  Inner table: T_PLAN2  Alias: B
    resc: 3.03  card: 0.02  bytes: 9  deg: 8  resp: 2.00
    using dmeth: 2  #groups: 1
    SORT resource      Sort statistics
      Sort width:         231 Area size:      202752 Max Area size:    40684544
      Degree:               1
      Blocks to Sort:       1 Row size:           20 Total Rows:              1
      Initial runs:         1 Merge passes:        0 IO Cost / pass:          0
      Total IO sort cost: 0      Total CPU sort cost: 24708786
      Total Temp space used: 0
    SORT resource      Sort statistics
      Sort width:         231 Area size:      202752 Max Area size:    40684544
      Degree:               1
      Blocks to Sort:       1 Row size:           20 Total Rows:              1
      Initial runs:         1 Merge passes:        0 IO Cost / pass:          0
      Total IO sort cost: 0      Total CPU sort cost: 24708786
      Total Temp space used: 0
    SORT response      Sort statistics
      Sort width:          71 Area size:     2542797 Max Area size:    12713984
      Degree:               8
      Blocks to Sort:       1 Row size:           20 Total Rows:              1
      Initial runs:         1 Merge passes:        0 IO Cost / pass:          0
      Total IO sort cost: 0      Total CPU sort cost: 27454207
  SM join: Resc: 7.03  Resp: 6.12  [multiMatchCost=0.00]
  SM cost: 6.12
     resc: 7.03 resc_io: 5.00 resc_cpu: 50092866
     resp: 6.12 resp_io: 4.00 resp_cpu: 52270121
HA Join
  Outer table:
    resc: 2.00  card 0.01  bytes: 9  deg: 1  resp: 2.00
  Inner table: T_PLAN2  Alias: B
    resc: 3.03  card: 0.02  bytes: 9  deg: 8  resp: 2.00
    using dmeth: 2  #groups: 1
    Cost per ptn: 0.50  #ptns: 8
    hash_area: 0 (max=0)       buildfrag: 1  probefrag: 1  passes: 1
  Hash join: Resc: 9.03  Resp: 4.50  [multiMatchCost=0.00]
  HA cost: 4.50
     resc: 9.03 resc_io: 5.00 resc_cpu: 99512438
     resp: 4.50 resp_io: 4.00 resp_cpu: 12461771
Best:: JoinMethod: HashSemi
       Cost: 4.50  Degree: 8  Resp: 4.50  Card: 0.00  Bytes: 18
***********************
Best so far: Table#: 0  cost: 2.0006  card: 0.0050  bytes: 9
             Table#: 1  cost: 4.5043  card: 0.0000  bytes: 18
***********************
Join order[2]:  T_PLAN2[B]#1  T_PLAN[A]#0
    SORT resource      Sort statistics
      Sort width:         231 Area size:      202752 Max Area size:    40684544
      Degree:               1
      Blocks to Sort:       1 Row size:           20 Total Rows:              1
      Initial runs:         1 Merge passes:        0 IO Cost / pass:          0
      Total IO sort cost: 0      Total CPU sort cost: 24708786
      Total Temp space used: 0
    SORT response      Sort statistics
      Sort width:          71 Area size:     2542797 Max Area size:    12713984
      Degree:               8
      Blocks to Sort:       1 Row size:           20 Total Rows:              1
      Initial runs:         1 Merge passes:        0 IO Cost / pass:          0
      Total IO sort cost: 0      Total CPU sort cost: 27454207
***************
Now joining: T_PLAN[A]#0
***************
NL Join
  Outer table: Card: 0.02  Cost: 3.11  Resp: 3.11  Degree: 8  Bytes: 9
  Inner table: T_PLAN  Alias: A
  Access Path: TableScan
    NL Join:  Cost: 10.13  Resp: 3.96  Degree: 100
      Cost_io: 9.00  Cost_cpu: 27874838
      Resp_io: 2.83  Resp_cpu: 27893937
kkofmx: index filter:"A"."COL1"="B"."COL1" AND "A"."COL2"="B"."COL2" AND "B"."COL1"=:B1 AND "A"."COL1"=:B2 AND "A"."COL2"<=100 AND "B"."COL2"<=100
  Access Path: index (AllEqJoinGuess)
    Index: T_PLAN_IDX
    resc_io: 32.00  resc_cpu: 229131
    ix_sel: 5.0000e-005  ix_sel_with_filters: 5.0000e-005
    NL Join: Cost: 36.04  Resp: 7.56  Degree: 8
      Cost_io: 35.00  Cost_cpu: 25597722
      Resp_io: 6.44  Resp_cpu: 27577670
  Best NL cost: 3.96
          resc: 10.13 resc_io: 9.00 resc_cpu: 27874838
          resp: 3.96 resp_io: 2.83 resp_cpu: 27893937
Using multi-column join key sanity check for table T_PLAN
Revised join sel:1.0000e-004 = 1 * (1/10000.00) * (1/1)
Join Card:  0.00 = outer (0.02) * inner (0.01) * sel (1.0000e-004)
Join Card - Rounded: 1 Computed: 0.00
SM Join
  Outer table:
    resc: 4.03  card 0.02  bytes: 9  deg: 8  resp: 3.11
  Inner table: T_PLAN  Alias: A
    resc: 2.00  card: 0.01  bytes: 9  deg: 1  resp: 2.00
    using dmeth: 2  #groups: 1
    SORT resource      Sort statistics
      Sort width:         231 Area size:      202752 Max Area size:    40684544
      Degree:               1
      Blocks to Sort:       1 Row size:           20 Total Rows:              1
      Initial runs:         1 Merge passes:        0 IO Cost / pass:          0
      Total IO sort cost: 0      Total CPU sort cost: 24708786
      Total Temp space used: 0
    SORT response      Sort statistics
      Sort width:          71 Area size:     2542797 Max Area size:    12713984
      Degree:               8
      Blocks to Sort:       1 Row size:           20 Total Rows:              1
      Initial runs:         1 Merge passes:        0 IO Cost / pass:          0
      Total IO sort cost: 0      Total CPU sort cost: 27454207
    SORT resource      Sort statistics
      Sort width:         231 Area size:      202752 Max Area size:    40684544
      Degree:               1
      Blocks to Sort:       1 Row size:           20 Total Rows:              1
      Initial runs:         1 Merge passes:        0 IO Cost / pass:          0
      Total IO sort cost: 0      Total CPU sort cost: 24708786
      Total Temp space used: 0
  SM join: Resc: 8.03  Resp: 7.23  [multiMatchCost=0.00]
  SM cost: 7.23
     resc: 8.03 resc_io: 5.00 resc_cpu: 74801652
     resp: 7.23 resp_io: 4.00 resp_cpu: 79724328
HA Join
  Outer table:
    resc: 4.03  card 0.02  bytes: 9  deg: 8  resp: 3.11
  Inner table: T_PLAN  Alias: A
    resc: 2.00  card: 0.01  bytes: 9  deg: 1  resp: 2.00
    using dmeth: 2  #groups: 1
    Cost per ptn: 0.50  #ptns: 8
    hash_area: 0 (max=0)       buildfrag: 1  probefrag: 1  passes: 1
  Hash join: Resc: 10.03  Resp: 5.62  [multiMatchCost=0.00]
  HA cost: 5.62
     resc: 10.03 resc_io: 5.00 resc_cpu: 124221225
     resp: 5.62 resp_io: 4.00 resp_cpu: 39915978
Plan cardinality mismatch: best card= 0.00000050005   curr card= 0.00000000834
Join order aborted: cost > best plan cost
***********************
(newjo-stop-1) k:0, spcnt:0, perm:2, maxperm:2000
*********************************
Number of join permutations tried: 2
*********************************
-- Enumerating distribution methods for #Hash Join:
---- cost VALUE = 0.00
  Outer table:
    resc: 2.00  card 0.01  bytes: 9  deg: 1  resp: 2.00
  Inner table: T_PLAN2  Alias: B
    resc: 3.03  card: 0.02  bytes: 9  deg: 8  resp: 2.00
    using dmeth: 2  #groups: 1
    Cost per ptn: 0.50  #ptns: 8
    hash_area: 0 (max=0)       buildfrag: 1  probefrag: 1  passes: 1
  Hash join: Resc: 9.03  Resp: 4.50  [multiMatchCost=0.00]
---- cost(Hash Join) = 4.50 (w/o dist), 4.50 (w/ dist)
kkoBloomFilter: join between  A.T_PLAN (left) and  B.T_PLAN2 (right) -> ndv:0 (1) reduction factor:1.000000 (limit:0.500000)  one of the DFO is serial
(newjo-save)    [0 1 ]
Final - All Rows Plan:  Best join order: 1
  Cost: 4.5043  Degree: 8  Card: 1.0000  Bytes: 18
  Resc: 9.0274  Resc_io: 5.0000  Resc_cpu: 99512438
  Resp: 4.5043  Resp_io: 4.0000  Resc_cpu: 12461771
kkoipt: Query block SEL$DBE3B336 (#1)
kkoqbc-end
          : call(in-use=44492, alloc=0), compile(in-use=54336, alloc=0)
apadrv-end: call(in-use=44492, alloc=0), compile(in-use=54924, alloc=0)

sql_id=cjtzqad54bcmy.
Current SQL statement for this session:
select /*+ gather_plan_statistics qb_name(main_query)*/
       a.col1, a.col2
  from t_plan a
 where (a.col1, a.col2) in (select /*+ parallel(b,8) qb_name(sub_query) */
                                   b.col1, b.col2
                              from t_plan2 b
                             where col1 = :ag_bind
                               and col2 <= 100)

============
Plan Table
============
-------------------------------------------------------+-----------------------------------+-------------------------+---------------+
| Id  | Operation                          | Name      | Rows  | Bytes | Cost  | Time      |  TQ  |IN-OUT|PQ Distrib | Pstart| Pstop |
-------------------------------------------------------+-----------------------------------+-------------------------+---------------+
| 0   | SELECT STATEMENT                   |           |       |       |     5 |           |      |      |           |       |       |
| 1   |  PX COORDINATOR                    |           |       |       |       |           |      |      |           |       |       |
| 2   |   PX SEND QC (RANDOM)              | :TQ10002  |     1 |    18 |     5 |  00:00:01 |:Q1002| P->S |QC (RANDOM)|       |       |
| 3   |    HASH JOIN SEMI BUFFERED         |           |     1 |    18 |     5 |  00:00:01 |:Q1002| PCWP |           |       |       |
| 4   |     BUFFER SORT                    |           |       |       |       |           |:Q1002| PCWC |           |       |       |
| 5   |      PX RECEIVE                    |           |     1 |     9 |     2 |  00:00:01 |:Q1002| PCWP |           |       |       |
| 6   |       PX SEND HASH                 | :TQ10000  |     1 |     9 |     2 |  00:00:01 |      | S->P |HASH       |       |       |
| 7   |        TABLE ACCESS BY INDEX ROWID | T_PLAN    |     1 |     9 |     2 |  00:00:01 |      |      |           |       |       |
| 8   |         INDEX RANGE SCAN           | T_PLAN_IDX|     1 |       |     1 |  00:00:01 |      |      |           |       |       |
| 9   |     PX RECEIVE                     |           |     1 |     9 |     2 |  00:00:01 |:Q1002| PCWP |           |       |       |
| 10  |      PX SEND HASH                  | :TQ10001  |     1 |     9 |     2
문서에 대하여

최초작성자 : 이지웅
최초작성일 : 2009년 2월 21일
이 문서는 오라클클럽 코어 오라클 데이터베이스 스터디 모임에서 작성하였습니다.
이 문서의 내용은 조동욱님의 'Optimizing Oracle Optimizer'을 참고하였습니다.
문서정보

이 문서는 구루비에서 작성하였습니다.
이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
출처 : http://wiki.gurubee.net/display/CORE/10053+Event?
구루비 지식창고의 모든 문서는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.
10053 Event

TRACE 10053

특징과 용도

Evnet 10053에서확인할 수 있는 옵티마이져 동작

문서에 대하여

문서정보

Labels
