wrong expected execution plan

  • From: "Ujang Jaenudin" <ujang.jaenudin@xxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Fri, 21 Sep 2007 00:01:39 +0700

all,

the COSTEDEVENT  table is IOT and
and COSTEDEVENT_PK is IOT primary key index
and COSTEDEVENT_UK1  is secondary of IOT index

my question:
why oracle assumption to take COSTEDEVENT_UK1  which is higher
clustering factor but lesser in  leaf blocks.

i guess oracle will assume that smaller leaf blocks is better than the
higher one. from this 10053, we could see that resources for cpu & io
is small than highr leaf blocks.

any thought?????


SELECT ......................
  FROM e
 WHERE ................
*******************************************
Peeked values of the binds in SQL statement
*******************************************
 bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=03 oacfl2=800000
size=96 offset=0
   bfp=ffffffff7ba79f80 bln=22 avl=00 flg=05
 bind 1: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=03 oacfl2=800000
size=0 offset=24
   bfp=ffffffff7ba79f98 bln=22 avl=00 flg=01
 bind 2: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=03 oacfl2=800000
size=0 offset=48
   bfp=ffffffff7ba79fb0 bln=22 avl=00 flg=01
 bind 3: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=03 oacfl2=800000
size=0 offset=72
   bfp=ffffffff7ba79fc8 bln=22 avl=00 flg=01
***************************************
Column Usage Monitoring is ON: tracking level = 1
***************************************
QUERY BLOCK SIGNATURE
*********************
qb name was generated
signature (optimizer): qb_name=SEL$1 nbfros=1 flg=0
  fro(0): flg=0 objn=197782 hint_alias="COSTEDEVENT"@"SEL$1"
***************************************
BASE STATISTICAL INFORMATION
***********************
Table stats    Table: COSTEDEVENT   Alias: COSTEDEVENT
  (Using composite stats)
  TOTAL ::  CDN: 296160700  NBLKS:  15040307  AVG_ROW_LEN:  261
Index stats
  Index: COSTEDEVENT_PK  COL#: 1 2 3 4 5
    USING COMPOSITE STATS
    TOTAL ::  LVLS: 3   #LB: 15040307  #DK: 361175084  LB/K: 1  DB/K: 1  CLUF: 0
  Index: COSTEDEVENT_UK1  COL#: 2 5
    USING COMPOSITE STATS
    TOTAL ::  LVLS: 3   #LB: 2961679  #DK: 360905528  LB/K: 1  DB/K: 1
 CLUF: 20447015
_OPTIMIZER_PERCENT_PARALLEL = 0
***************************************
SINGLE TABLE ACCESS PATH
  COLUMN: ACCOUNT_NU(VARCHAR2)  Col#: 1      Table: COSTEDEVENT
Alias: COSTEDEVENT
    Size: 8  NDV: 47616  Nulls: 0  Density: 2.1001e-05
  COLUMN: EVENT_TYPE(NUMBER)  Col#: 4      Table: COSTEDEVENT   Alias:
COSTEDEVENT
    Size: 2  NDV: 18  Nulls: 0  Density: 5.5556e-02 Min: 1  Max: 29
  COLUMN: EVENT_ATTR(VARCHAR2)  Col#: 23     Table: COSTEDEVENT
Alias: COSTEDEVENT
    Size: 14  NDV: 1827  Nulls: 402372  Density: 5.4735e-04
  COLUMN:  EVENT_SEQ(NUMBER)  Col#: 2      Table: COSTEDEVENT   Alias:
COSTEDEVENT
    Size: 5  NDV: 5  Nulls: 0  Density: 2.0000e-01 Min: 70916001  Max: 71011001
  TABLE: COSTEDEVENT  Alias: COSTEDEVENT
    Original Card: 296160700   Rounded: 1  Computed: 0.01  Non Adjusted: 0.01
  Access Path: index (index-ffs)
    Index: COSTEDEVENT_PK
    rsc_cpu: 107108643882   rsc_io: 2604638
    ix_sel:  0.0000e+00    ix_sel_with_filters:  1.0000e+00
  Access Path: index-ffs  Resc:  2616210  Resp:  2616210
  Access Path: index (no start/stop keys)
    Index: COSTEDEVENT_PK
    rsc_cpu: 107112578517   rsc_io: 15040859
    ix_sel:  1.0000e+00    ix_sel_with_filters:  5.8337e-08
OPTIMIZER PERCENT INDEX CACHING = 100
  Access Path: index (index-only)
    Index: COSTEDEVENT_UK1
    rsc_cpu: 1322405301   rsc_io: 0
    ix_sel:  9.0000e-03    ix_sel_with_filters:  9.0000e-03
  BEST_CST: 22.43  PATH: 4  Degree:  1
***************************************
OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***********************
Join order[1]:  COSTEDEVENT[COSTEDEVENT]#0
Best so far: TABLE#: 0  CST:         22  CDN:          1  BYTES:        261
(newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:80000
Final - All Rows Plan:
  JOIN ORDER: 1
  CST: 22  CDN: 1  RSC: 22  RSP: 22  BYTES: 261
  IO-RSC: 1  IO-RSP: 1  CPU-RSC: 198360795  CPU-RSP: 198360795

*** 2007-09-20 23:20:37.432
QUERY
SELECT /*+ index(e COSTEDEVENT_PK) */
  FROM e
 WHERE ..................
*******************************************
Peeked values of the binds in SQL statement
*******************************************
 bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=03 oacfl2=800000
size=96 offset=0
   bfp=ffffffff7ba79f80 bln=22 avl=00 flg=05
 bind 1: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=03 oacfl2=800000
size=0 offset=24
   bfp=ffffffff7ba79f98 bln=22 avl=00 flg=01
 bind 2: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=03 oacfl2=800000
size=0 offset=48
   bfp=ffffffff7ba79fb0 bln=22 avl=00 flg=01
 bind 3: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=03 oacfl2=800000
size=0 offset=72
   bfp=ffffffff7ba79fc8 bln=22 avl=00 flg=01
***************************************
Column Usage Monitoring is ON: tracking level = 1
***************************************
QUERY BLOCK SIGNATURE
*********************
qb name was generated
signature (optimizer): qb_name=SEL$1 nbfros=1 flg=0
  fro(0): flg=0 objn=197782 hint_alias="COSTEDEVENT"@"SEL$1"
***************************************
BASE STATISTICAL INFORMATION
***********************
Table stats    Table: COSTEDEVENT   Alias: COSTEDEVENT
  (Using composite stats)
  TOTAL ::  CDN: 296160700  NBLKS:  15040307  AVG_ROW_LEN:  261
Index stats
  Index: COSTEDEVENT_PK  COL#: 1 2 3 4 5
    USING COMPOSITE STATS
    TOTAL ::  LVLS: 3   #LB: 15040307  #DK: 361175084  LB/K: 1  DB/K: 1  CLUF: 0
  Index: COSTEDEVENT_UK1  COL#: 2 5
    USING COMPOSITE STATS
    TOTAL ::  LVLS: 3   #LB: 2961679  #DK: 360905528  LB/K: 1  DB/K: 1
 CLUF: 20447015
_OPTIMIZER_PERCENT_PARALLEL = 0
***************************************
SINGLE TABLE ACCESS PATH
  COLUMN: ACCOUNT_NU(VARCHAR2)  Col#: 1      Table: COSTEDEVENT
Alias: COSTEDEVENT
    Size: 8  NDV: 47616  Nulls: 0  Density: 2.1001e-05
  COLUMN: EVENT_TYPE(NUMBER)  Col#: 4      Table: COSTEDEVENT   Alias:
COSTEDEVENT
    Size: 2  NDV: 18  Nulls: 0  Density: 5.5556e-02 Min: 1  Max: 29
  COLUMN: EVENT_ATTR(VARCHAR2)  Col#: 23     Table: COSTEDEVENT
Alias: COSTEDEVENT
    Size: 14  NDV: 1827  Nulls: 402372  Density: 5.4735e-04
  COLUMN:  EVENT_SEQ(NUMBER)  Col#: 2      Table: COSTEDEVENT   Alias:
COSTEDEVENT
    Size: 5  NDV: 5  Nulls: 0  Density: 2.0000e-01 Min: 70916001  Max: 71011001
  TABLE: COSTEDEVENT  Alias: COSTEDEVENT
    Original Card: 296160700   Rounded: 1  Computed: 0.01  Non Adjusted: 0.01
  Access Path: index (no start/stop keys)
    Index: COSTEDEVENT_PK
    rsc_cpu: 107112578517   rsc_io: 15040859
    ix_sel:  1.0000e+00    ix_sel_with_filters:  5.8337e-08
  BEST_CST: 2258584.64  PATH: 4  Degree:  1
***************************************
OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***********************
Join order[1]:  COSTEDEVENT[COSTEDEVENT]#0
Best so far: TABLE#: 0  CST:    2258585  CDN:          1  BYTES:        261
(newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:80000
Final - All Rows Plan:
  JOIN ORDER: 1
  CST: 2258585  CDN: 1  RSC: 2258585  RSP: 2258585  BYTES: 261
  IO-RSC: 2256129  IO-RSP: 2256129  CPU-RSC: 22730551767  CPU-RSP: 22730551767

*** 2007-09-20 23:23:09.976
QUERY
SELECT /*+ parallel_index(e COSTEDEVENT_PK) */
  FROM e
 WHERE ................
*******************************************
Peeked values of the binds in SQL statement
*******************************************
 bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=03 oacfl2=800000
size=96 offset=0
   bfp=ffffffff7ba79f80 bln=22 avl=00 flg=05
 bind 1: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=03 oacfl2=800000
size=0 offset=24
   bfp=ffffffff7ba79f98 bln=22 avl=00 flg=01
 bind 2: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=03 oacfl2=800000
size=0 offset=48
   bfp=ffffffff7ba79fb0 bln=22 avl=00 flg=01
 bind 3: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=03 oacfl2=800000
size=0 offset=72
   bfp=ffffffff7ba79fc8 bln=22 avl=00 flg=01
***************************************
PARAMETERS USED BY THE OPTIMIZER
********************************
  *************************************
  PARAMETERS WITH ALTERED VALUES
  ******************************
  sort_area_size                      = 0
  db_file_multiblock_read_count       = 128
  optimizer_index_cost_adj            = 15
  optimizer_index_caching             = 100
  *************************************
  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.1.0.5
  _optimizer_search_limit             = 5
  cpu_count                           = 72
  active_instance_count               = 1
  parallel_threads_per_cpu            = 2
  hash_area_size                      = 0
  bitmap_merge_area_size              = 1048576
  sort_area_retained_size             = 0
  _sort_elimination_cost_ratio        = 0
  _optimizer_block_size               = 8192
  _sort_multiblock_read_count         = 2
  _hash_multiblock_io_count           = 0
  _optimizer_max_permutations         = 2000
  pga_aggregate_target                = 9437184 KB
  _pga_max_size                       = 204800 KB
  _sort_space_for_write_buffers       = 1
  _query_rewrite_maxdisjunct          = 257
  _smm_auto_min_io_size               = 56 KB
  _smm_auto_max_io_size               = 248 KB
  _smm_min_size                       = 1024 KB
  _smm_max_size                       = 102400 KB
  _smm_px_max_size                    = 2831154 KB
  _cpu_to_io                          = 0
  _optimizer_undo_cost_change         = 10.1.0.5
  parallel_query_mode                 = enabled
  parallel_dml_mode                   = disabled
  parallel_ddl_mode                   = enabled
  optimizer_mode                      = all_rows
  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
  _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          = false
  _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_push_pred_cost_based     = true
  flashback_table_rpi                 = non_fbt
  _optimizer_cbqt_no_size_restriction = true
  _optimizer_or_expansion             = breadth
  _optimizer_rownum_pred_based_fkr    = true
  _optimizer_better_inlist_costing    = nlonly
  _optimizer_outer_to_anti_enabled    = false
***************************************
Column Usage Monitoring is ON: tracking level = 1
***************************************
QUERY BLOCK SIGNATURE
*********************
qb name was generated
signature (optimizer): qb_name=SEL$1 nbfros=1 flg=0
  fro(0): flg=0 objn=197782 hint_alias="COSTEDEVENT"@"SEL$1"
***************************************
BASE STATISTICAL INFORMATION
***********************
Table stats    Table: COSTEDEVENT   Alias: COSTEDEVENT
  (Using composite stats)
  TOTAL ::  CDN: 296160700  NBLKS:  15040307  AVG_ROW_LEN:  261
Index stats
  Index: COSTEDEVENT_PK  COL#: 1 2 3 4 5
    USING COMPOSITE STATS
    TOTAL ::  LVLS: 3   #LB: 15040307  #DK: 361175084  LB/K: 1  DB/K: 1  CLUF: 0
  Index: COSTEDEVENT_UK1  COL#: 2 5
    USING COMPOSITE STATS
    TOTAL ::  LVLS: 3   #LB: 2961679  #DK: 360905528  LB/K: 1  DB/K: 1
 CLUF: 20447015
_OPTIMIZER_PERCENT_PARALLEL = 0
***************************************
SINGLE TABLE ACCESS PATH
  COLUMN: ACCOUNT_NU(VARCHAR2)  Col#: 1      Table: COSTEDEVENT
Alias: COSTEDEVENT
    Size: 8  NDV: 47616  Nulls: 0  Density: 2.1001e-05
  COLUMN: EVENT_TYPE(NUMBER)  Col#: 4      Table: COSTEDEVENT   Alias:
COSTEDEVENT
    Size: 2  NDV: 18  Nulls: 0  Density: 5.5556e-02 Min: 1  Max: 29
  COLUMN: EVENT_ATTR(VARCHAR2)  Col#: 23     Table: COSTEDEVENT
Alias: COSTEDEVENT
    Size: 14  NDV: 1827  Nulls: 402372  Density: 5.4735e-04
  COLUMN:  EVENT_SEQ(NUMBER)  Col#: 2      Table: COSTEDEVENT   Alias:
COSTEDEVENT
    Size: 5  NDV: 5  Nulls: 0  Density: 2.0000e-01 Min: 70916001  Max: 71011001
  TABLE: COSTEDEVENT  Alias: COSTEDEVENT
    Original Card: 296160700   Rounded: 1  Computed: 0.01  Non Adjusted: 0.01
  Access Path: index (index-ffs)
    Index: COSTEDEVENT_PK
    rsc_cpu: 107108643882   rsc_io: 2604638
    ix_sel:  0.0000e+00    ix_sel_with_filters:  1.0000e+00
  Access Path: index-ffs  Resc:  2616210  Resp:  2616210
  Access Path: index (no start/stop keys)
    Index: COSTEDEVENT_PK
    rsc_cpu: 107112578517   rsc_io: 15040859
    ix_sel:  1.0000e+00    ix_sel_with_filters:  5.8337e-08
    Parallel:  resp: 17427  deg: 144
OPTIMIZER PERCENT INDEX CACHING = 100
  Access Path: index (index-only)
    Index: COSTEDEVENT_UK1
    rsc_cpu: 1322405301   rsc_io: 0
    ix_sel:  9.0000e-03    ix_sel_with_filters:  9.0000e-03
  BEST_CST: 22.43  PATH: 4  Degree:  1
***************************************
OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***********************
Join order[1]:  COSTEDEVENT[COSTEDEVENT]#0
Best so far: TABLE#: 0  CST:         22  CDN:          1  BYTES:        261
(newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:80000
Final - All Rows Plan:
  JOIN ORDER: 1
  CST: 22  CDN: 1  RSC: 22  RSP: 22  BYTES: 261
  IO-RSC: 1  IO-RSP: 1  CPU-RSC: 198360795  CPU-RSP: 198360795


-- 
regards
ujang
--
http://www.freelists.org/webpage/oracle-l


Other related posts:

  • » wrong expected execution plan