- 이 문서는 구루비에서 작성하였습니다.
- 이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 출처 : 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) 라이선스에 따라 자유롭게 사용할 수 있습니다.