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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
- » wrong expected execution plan
