Re: performance issue after upgrade to oracle 11.2.0.1 linux 32 bit.
- From: lsllcm <lsllcm@xxxxxxxxx>
- Date: Wed, 4 Nov 2009 04:01:40 -0800 (PST)
The trace file is too long, I use three parts.
Trace file /u01/app/oracle/diag/rdbms/dbs26/dbs26/trace/
dbs26_ora_29590.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing
options
ORACLE_HOME = /u01/app/oracle/product/1101/db
System name: Linux
Node name: localhost.localdomain
Release: 2.6.18-164.el5xen
Version: #1 SMP Thu Sep 3 02:41:56 EDT 2009
Machine: i686
Instance name: dbs26
Redo thread mounted by this instance: 1
Oracle process number: 21
Unix process pid: 29590, image: oracle@xxxxxxxxxxxxxxxxxxxxx
*** 2009-11-04 12:27:11.627
*** SESSION ID:(91.2909) 2009-11-04 12:27:11.627
*** CLIENT ID:() 2009-11-04 12:27:11.627
*** SERVICE NAME:(dbs26) 2009-11-04 12:27:11.627
*** MODULE NAME:(SQL*Plus) 2009-11-04 12:27:11.627
*** ACTION NAME:() 2009-11-04 12:27:11.627
Registered qb: SEL$1 0x6d3604 (PARSER)
*** 2009-11-04 12:27:12.103
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (): qb_name=SEL$1 nbfros=4 flg=0
fro(0): flg=4 objn=73740 hint_alias="F4"@"SEL$1"
fro(1): flg=4 objn=73746 hint_alias="FINV"@"SEL$1"
fro(2): flg=4 objn=74784 hint_alias="SD"@"SEL$1"
fro(3): flg=4 objn=74848 hint_alias="XFI"@"SEL$1"
SPM: statement not found in SMB
**************************
Automatic degree of parallelism (ADOP)
**************************
Automatic degree of parallelism is disabled: Parameter.
PM: Considering predicate move-around in query block SEL$1 (#0)
**************************
Predicate Move-Around (PM)
**************************
OPTIMIZER INFORMATION
******************************************
----- Current SQL Statement for this session (sql_id=5n7ufx7tz1uks)
-----
SELECT xfi.serv_prov_code,
xfi.b1_per_id1,
xfi.b1_per_id2,
xfi.b1_per_id3,
xfi.feeitem_seq_nbr,
xfi.invoice_nbr,
xfi.gf_fee_period,
xfi.gf_fee,
xfi.gf_des,
xfi.gf_unit,
xfi.gf_udes,
finv.invoice_date AS gf_fee_apply_date,
xfi.feeitem_invoice_status,
xfi.gf_l1,
xfi.gf_l2,
xfi.gf_l3,
xfi.x4feeitem_invoice_udf1,
xfi.x4feeitem_invoice_udf2,
xfi.x4feeitem_invoice_udf3,
xfi.x4feeitem_invoice_udf4,
xfi.gf_fee_schedule,
xfi.fee_schedule_version,
xfi.rec_date,
xfi.rec_ful_nam,
xfi.rec_status,
f4.GF_COD,
f4.GF_PRIORITY
FROM x4feeitem_invoice xfi, setdetails sd, f4invoice finv, F4FEEITEM
f4
WHERE sd.serv_prov_code = xfi.serv_prov_code
AND sd.b1_per_id1 = xfi.b1_per_id1
AND sd.b1_per_id2 = xfi.b1_per_id2
AND sd.b1_per_id3 = xfi.b1_per_id3
AND xfi.serv_prov_code = f4.serv_prov_code
AND xfi.b1_per_id1 = f4.b1_per_id1
AND xfi.b1_per_id2 = f4.b1_per_id2
AND xfi.b1_per_id3 = f4.b1_per_id3
And xfi.feeitem_seq_nbr = f4.feeitem_seq_nbr
AND finv.serv_prov_code = xfi.serv_prov_code
AND finv.invoice_nbr = xfi.invoice_nbr
AND sd.serv_prov_code = 'SACRAMENTO'
AND upper(sd.set_id) = 'SET07'
AND xfi.rec_status = 'A'
AND xfi.feeitem_invoice_status = 'INVOICED'
ORDER BY gf_fee_apply_date
*** 2009-11-04 12:27:13.146
*******************************************
Legend
The following abbreviations are used by optimizer trace.
CBQT - cost-based query transformation
JPPD - join predicate push-down
OJPPD - old-style (non-cost-based) JPPD
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
OST - old style star transformation
ST - new (cbqt) star transformation
CNT - count(col) to count(*) transformation
JE - Join Elimination
JF - join factorization
SLP - select list pruning
DP - distinct placement
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)
CPUSPEED - 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)
128: 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
256: run the join in serial
0: invalid distribution method
sel - selectivity
ptn - partition
***************************************
PARAMETERS USED BY THE OPTIMIZER
********************************
*************************************
PARAMETERS WITH ALTERED VALUES
******************************
Compilation Environment Dump
optimizer_features_enable = 11.1.0.6
optimizer_mode = first_rows_100
optimizer_index_cost_adj = 10
optimizer_index_caching = 90
_optimizer_cost_based_transformation = off
Bug Fix Control Environment
*************************************
PARAMETERS WITH DEFAULT VALUES
******************************
Compilation Environment Dump
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_search_limit = 5
cpu_count = 4
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 = 8
_optimizer_max_permutations = 2000
pga_aggregate_target = 204800 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 = 204 KB
_smm_max_size = 40960 KB
_smm_px_max_size = 102400 KB
_cpu_to_io = 0
_optimizer_undo_cost_change = 11.1.0.6
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
_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_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
_optimizer_null_aware_antijoin = true
_optimizer_extend_jppd_view_types = 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
_px_minus_intersect = true
_fix_control_key = 0
_force_slave_mapping_intra_part_loads = false
_force_tmp_segment_loads = false
_query_mmvrewrite_maxpreds = 10
_query_mmvrewrite_maxintervals = 5
_query_mmvrewrite_maxinlists = 5
_query_mmvrewrite_maxdmaps = 10
_query_mmvrewrite_maxcmaps = 20
_query_mmvrewrite_maxregperm = 512
_query_mmvrewrite_maxmergedcmaps = 50
_query_mmvrewrite_maxqryinlistvals = 500
_disable_parallel_conventional_load = false
_trace_virtual_columns = false
_replace_virtual_columns = true
_virtual_column_overload_allowed = true
_kdt_buffering = true
_first_k_rows_dynamic_proration = true
_optimizer_sortmerge_join_inequality = true
_optimizer_aw_stats_enabled = true
_bloom_pruning_enabled = true
result_cache_mode = MANUAL
_px_ual_serial_input = true
_optimizer_skip_scan_guess = false
_enable_row_shipping = true
_row_shipping_threshold = 80
_row_shipping_explain = false
transaction_isolation_level = read_commited
_optimizer_distinct_elimination = true
_optimizer_multi_level_push_pred = true
_optimizer_group_by_placement = true
_optimizer_rownum_bind_default = 10
_enable_query_rewrite_on_remote_objs = true
_optimizer_extended_cursor_sharing_rel = simple
_optimizer_adaptive_cursor_sharing = true
_direct_path_insert_features = 0
_optimizer_improve_selectivity = true
optimizer_use_pending_statistics = false
_optimizer_enable_density_improvements = true
_optimizer_aw_join_push_enabled = true
_optimizer_connect_by_combine_sw = true
_enable_pmo_ctas = 0
_optimizer_native_full_outer_join = force
_bloom_predicate_enabled = true
_optimizer_enable_extended_stats = true
_is_lock_table_for_ddl_wait_lock = 0
_pivot_implementation_method = choose
optimizer_capture_sql_plan_baselines = false
optimizer_use_sql_plan_baselines = true
_optimizer_star_trans_min_cost = 0
_optimizer_star_trans_min_ratio = 0
_with_subquery = OPTIMIZER
_optimizer_fkr_index_cost_bias = 10
_optimizer_use_subheap = true
parallel_degree_policy = manual
parallel_degree = 0
parallel_min_time_threshold = 10
_parallel_time_unit = 10
_optimizer_or_expansion_subheap = true
_optimizer_free_transformation_heap = true
_optimizer_reuse_cost_annotations = true
_result_cache_auto_size_threshold = 100
_result_cache_auto_time_threshold = 1000
_optimizer_nested_rollup_for_gset = 100
_nlj_batching_enabled = 1
parallel_query_default_dop = 0
is_recur_flags = 0
optimizer_use_invisible_indexes = false
flashback_data_archive_internal_cursor = 0
_optimizer_extended_stats_usage_control = 240
_parallel_syspls_obey_force = true
cell_offload_processing = true
_rdbms_internal_fplib_enabled = false
db_file_multiblock_read_count = 128
_bloom_folding_enabled = false
_mv_generalized_oj_refresh_opt = true
cell_offload_compaction = ADAPTIVE
parallel_degree_limit = 65535
parallel_force_local = false
parallel_max_degree = 8
total_cpu_count = 4
cell_offload_plan_display = AUTO
_optimizer_coalesce_subqueries = false
_optimizer_fast_pred_transitivity = false
_optimizer_fast_access_pred_analysis = false
_optimizer_unnest_disjunctive_subq = false
_optimizer_unnest_corr_set_subq = false
_optimizer_distinct_agg_transform = false
_aggregation_optimization_settings = 32
_optimizer_connect_by_elim_dups = false
_optimizer_eliminate_filtering_join = false
_connect_by_use_union_all = old_plan_mode
dst_upgrade_insert_conv = true
advanced_queuing_internal_cursor = 0
_optimizer_unnest_all_subqueries = true
_bloom_predicate_pushdown_to_storage = true
_bloom_vector_elements = 0
_bloom_pushing_max = 524288
parallel_autodop = 0
parallel_ddldml = 0
_parallel_cluster_cache_policy = adaptive
_parallel_scalability = 50
iot_internal_cursor = 0
_optimizer_instance_count = 0
_optimizer_connect_by_cb_whr_only = false
_suppress_scn_chk_for_cqn = nosuppress_1466
_optimizer_join_factorization = false
_optimizer_use_cbqt_star_transformation = false
_optimizer_table_expansion = false
_and_pruning_enabled = false
_deferred_constant_folding_mode = DEFAULT
_optimizer_distinct_placement = false
partition_pruning_internal_cursor = 0
parallel_hinted = none
_sql_compatibility = 0
_optimizer_use_feedback = false
_optimizer_try_st_before_jppd = false
Bug Fix Control Environment
fix 3834770 = 1
fix 3746511 = enabled
fix 4519016 = enabled
fix 3118776 = enabled
fix 4488689 = enabled
fix 2194204 = disabled
fix 2660592 = enabled
fix 2320291 = enabled
fix 2324795 = enabled
fix 4308414 = enabled
fix 3499674 = disabled
fix 4569940 = enabled
fix 4631959 = enabled
fix 4519340 = enabled
fix 4550003 = enabled
fix 1403283 = enabled
fix 4554846 = enabled
fix 4602374 = enabled
fix 4584065 = enabled
fix 4545833 = enabled
fix 4611850 = enabled
fix 4663698 = enabled
fix 4663804 = enabled
fix 4666174 = enabled
fix 4567767 = enabled
fix 4556762 = 15
fix 4728348 = enabled
fix 4708389 = enabled
fix 4175830 = enabled
fix 4752814 = enabled
fix 4583239 = enabled
fix 4386734 = enabled
fix 4887636 = enabled
fix 4483240 = enabled
fix 4872602 = disabled
fix 4711525 = enabled
fix 4545802 = enabled
fix 4605810 = enabled
fix 4704779 = enabled
fix 4900129 = enabled
fix 4924149 = enabled
fix 4663702 = enabled
fix 4878299 = enabled
fix 4658342 = enabled
fix 4881533 = enabled
fix 4676955 = enabled
fix 4273361 = enabled
fix 4967068 = enabled
fix 4969880 = disabled
fix 5005866 = enabled
fix 5015557 = enabled
fix 4705343 = enabled
fix 4904838 = enabled
fix 4716096 = enabled
fix 4483286 = disabled
fix 4722900 = enabled
fix 4615392 = enabled
fix 5096560 = enabled
fix 5029464 = enabled
fix 4134994 = enabled
fix 4904890 = enabled
fix 5104624 = enabled
fix 5014836 = enabled
fix 4768040 = enabled
fix 4600710 = enabled
fix 5129233 = enabled
fix 4595987 = enabled
fix 4908162 = enabled
fix 5139520 = enabled
fix 5084239 = enabled
fix 5143477 = disabled
fix 2663857 = enabled
fix 4717546 = enabled
fix 5240264 = disabled
fix 5099909 = enabled
fix 5240607 = enabled
fix 5195882 = enabled
fix 5220356 = enabled
fix 5263572 = enabled
fix 5385629 = enabled
fix 5302124 = enabled
fix 5391942 = enabled
fix 5384335 = enabled
fix 5482831 = enabled
fix 4158812 = enabled
fix 5387148 = enabled
fix 5383891 = enabled
fix 5466973 = enabled
fix 5396162 = enabled
fix 5394888 = enabled
fix 5395291 = enabled
fix 5236908 = enabled
fix 5509293 = enabled
fix 5449488 = enabled
fix 5567933 = enabled
fix 5570494 = enabled
fix 5288623 = enabled
fix 5505995 = enabled
fix 5505157 = enabled
fix 5112460 = enabled
fix 5554865 = enabled
fix 5112260 = enabled
fix 5112352 = enabled
fix 5547058 = enabled
fix 5618040 = enabled
fix 5585313 = enabled
fix 5547895 = enabled
fix 5634346 = enabled
fix 5620485 = enabled
fix 5483301 = enabled
fix 5657044 = enabled
fix 5694984 = enabled
fix 5868490 = enabled
fix 5650477 = enabled
fix 5611962 = enabled
fix 4279274 = enabled
fix 5741121 = enabled
fix 5714944 = enabled
fix 5391505 = enabled
fix 5762598 = enabled
fix 5578791 = enabled
fix 5259048 = enabled
fix 5882954 = enabled
fix 2492766 = enabled
fix 5707608 = enabled
fix 5891471 = enabled
fix 5884780 = enabled
fix 5680702 = enabled
fix 5371452 = enabled
fix 5838613 = enabled
fix 5949981 = enabled
fix 5624216 = enabled
fix 5741044 = enabled
fix 5976822 = enabled
fix 6006457 = enabled
fix 5872956 = enabled
fix 5923644 = enabled
fix 5943234 = enabled
fix 5844495 = enabled
fix 4168080 = enabled
fix 6020579 = enabled
fix 5842686 = disabled
fix 5996801 = enabled
fix 5593639 = enabled
fix 6133948 = enabled
fix 3151991 = enabled
fix 6146906 = enabled
fix 6239909 = enabled
fix 6267621 = enabled
fix 5909305 = enabled
fix 6279918 = enabled
fix 6141818 = enabled
fix 6151963 = enabled
fix 6251917 = enabled
fix 6282093 = enabled
fix 6119510 = enabled
fix 6119382 = enabled
fix 3801750 = enabled
fix 5705630 = disabled
fix 5944076 = enabled
fix 5406763 = enabled
fix 6070954 = enabled
fix 6282944 = enabled
fix 6138746 = enabled
fix 6082745 = enabled
fix 3426050 = enabled
fix 599680 = enabled
fix 6062266 = enabled
fix 6087237 = enabled
fix 6122894 = enabled
fix 6377505 = disabled
fix 5893768 = enabled
fix 6163564 = enabled
fix 6073325 = enabled
fix 6188881 = enabled
fix 6007259 = enabled
fix 6239971 = enabled
fix 5284200 = enabled
fix 6042205 = enabled
fix 6051211 = enabled
fix 6434668 = enabled
fix 6438752 = disabled
fix 5936366 = disabled
fix 6439032 = enabled
fix 6438892 = disabled
fix 6006300 = disabled
fix 5947231 = enabled
fix 5416118 = 1
fix 6365442 = 1
fix 6239039 = enabled
fix 6502845 = disabled
fix 6913094 = disabled
fix 6029469 = enabled
fix 5919513 = enabled
fix 6057611 = enabled
fix 6469667 = enabled
fix 6608941 = disabled
fix 6368066 = disabled
fix 6329318 = enabled
fix 6656356 = enabled
fix 4507997 = enabled
fix 6671155 = enabled
fix 6694548 = enabled
fix 6688200 = enabled
fix 6612471 = disabled
fix 6708183 = disabled
fix 6326934 = enabled
fix 6520717 = disabled
fix 6714199 = enabled
fix 6681545 = disabled
fix 6748058 = disabled
fix 6167716 = disabled
fix 6674254 = enabled
fix 6468287 = enabled
fix 6503543 = disabled
fix 6808773 = disabled
fix 6766962 = disabled
fix 6120483 = enabled
fix 6670551 = disabled
fix 6771838 = enabled
fix 6626018 = disabled
fix 6530596 = enabled
fix 6778642 = enabled
fix 6699059 = disabled
fix 6376551 = disabled
fix 6429113 = enabled
fix 6782437 = enabled
fix 6776808 = disabled
fix 6765823 = disabled
fix 6768660 = disabled
fix 6782665 = disabled
fix 6610822 = enabled
fix 6514189 = enabled
fix 6818410 = disabled
fix 6827696 = disabled
fix 6773613 = enabled
fix 5902962 = enabled
fix 6956212 = enabled
fix 3056297 = enabled
fix 6440977 = disabled
fix 6972291 = disabled
fix 6904146 = enabled
fix 6221403 = enabled
fix 5475051 = enabled
fix 6845871 = enabled
fix 5468809 = enabled
fix 6917633 = disabled
fix 4444536 = disabled
fix 6955210 = enabled
fix 6994194 = enabled
fix 6399597 = disabled
fix 6951776 = disabled
fix 5648287 = 3
fix 6987082 = disabled
fix 7132036 = enabled
fix 6980350 = disabled
fix 5199213 = enabled
fix 7138405 = enabled
fix 7148689 = enabled
fix 6820988 = enabled
fix 7032684 = disabled
fix 6617866 = enabled
fix 7155968 = disabled
fix 7127980 = disabled
fix 6982954 = disabled
fix 7241819 = enabled
fix 6897034 = enabled
fix 7236148 = enabled
fix 7298570 = enabled
fix 7249095 = enabled
fix 7314499 = disabled
fix 7324224 = disabled
fix 7289023 = enabled
fix 7237571 = enabled
fix 7116357 = enabled
fix 7345484 = enabled
fix 7375179 = disabled
fix 6430500 = disabled
fix 5897486 = disabled
fix 6774209 = disabled
fix 7306637 = disabled
fix 6451322 = enabled
fix 7208131 = enabled
fix 7388652 = disabled
fix 7127530 = disabled
fix 6751206 = enabled
fix 6669103 = enabled
fix 7430474 = enabled
fix 6990305 = enabled
fix 7043307 = disabled
fix 6921505 = disabled
fix 7388457 = disabled
fix 3120429 = enabled
fix 7452823 = disabled
fix 6838105 = enabled
fix 6769711 = disabled
fix 7170213 = enabled
fix 6528872 = enabled
fix 7295298 = enabled
fix 5922070 = enabled
fix 7259468 = enabled
fix 6418552 = enabled
fix 4619997 = enabled
fix 7524366 = disabled
fix 6942476 = disabled
fix 6418771 = enabled
fix 7375077 = enabled
fix 5400639 = disabled
fix 4570921 = disabled
fix 7426911 = disabled
fix 5099019 = disabled
fix 7528216 = enabled
fix 7521266 = enabled
fix 7385140 = disabled
fix 7576516 = enabled
fix 7573526 = enabled
fix 7576476 = enabled
fix 7165898 = enabled
fix 7263214 = enabled
fix 3320140 = enabled
fix 7555510 = enabled
fix 7613118 = disabled
fix 7597059 = enabled
fix 7558911 = disabled
fix 5520732 = disabled
fix 7679490 = disabled
fix 7449971 = disabled
fix 3628118 = enabled
fix 4370840 = enabled
fix 7281191 = enabled
fix 7519687 = enabled
fix 5029592 = 0
fix 6012093 = 1
fix 6053861 = disabled
fix 6941515 = disabled
fix 7696414 = enabled
fix 7272039 = enabled
fix 7834811 = enabled
fix 7640597 = enabled
fix 7341616 = enabled
fix 7168184 = disabled
fix 399198 = disabled
fix 7831070 = enabled
fix 7676897 = disabled
fix 7414637 = disabled
fix 7585456 = enabled
fix 8202421 = disabled
fix 7658097 = disabled
fix 8251486 = disabled
fix 7132684 = enabled
fix 7512227 = enabled
fix 6972987 = disabled
fix 7199035 = disabled
fix 8243446 = disabled
fix 7650462 = disabled
fix 6720701 = enabled
fix 7592673 = enabled
fix 7718694 = disabled
fix 7534027 = disabled
fix 7708267 = enabled
fix 5716785 = disabled
fix 7356191 = enabled
fix 7679161 = disabled
fix 7597159 = disabled
fix 7499258 = enabled
fix 8328363 = disabled
fix 7452863 = disabled
fix 8284930 = disabled
fix 7298626 = disabled
fix 7657126 = enabled
fix 8371884 = enabled
fix 8318020 = enabled
fix 8255423 = enabled
fix 7135745 = enabled
fix 8356253 = disabled
fix 7534257 = enabled
fix 8323407 = enabled
fix 7539815 = enabled
fix 8289316 = enabled
fix 8447850 = disabled
fix 7675944 = enabled
fix 8355120 = disabled
fix 7176746 = enabled
fix 8442891 = disabled
fix 8373261 = disabled
fix 7679164 = disabled
fix 7670533 = enabled
fix 8408665 = disabled
fix 8491399 = disabled
fix 8348392 = disabled
fix 8348585 = enabled
fix 8508056 = disabled
fix 8335178 = disabled
fix 8515269 = disabled
fix 8247017 = enabled
fix 7325597 = enabled
fix 8531490 = disabled
fix 6163600 = enabled
fix 8589278 = disabled
fix 8557992 = disabled
fix 7556098 = enabled
fix 8580883 = enabled
fix 5892599 = disabled
fix 8609714 = enabled
fix 8514561 = enabled
fix 8619631 = disabled
***************************************
PARAMETERS IN OPT_PARAM HINT
****************************
***************************************
Column Usage Monitoring is ON: tracking level = 1
***************************************
Considering Query Transformations on query block SEL$1 (#0)
**************************
Query transformations (QT)
**************************
CBQT bypassed for query block SEL$1 (#0): Disabled by parameter.
CBQT: Validity checks failed for 5n7ufx7tz1uks.
CSE: Considering common sub-expression elimination in query block SEL
$1 (#0)
*************************
Common Subexpression elimination (CSE)
*************************
CSE: CSE not performed on query block SEL$1 (#0).
OBYE: Considering Order-by Elimination from view SEL$1 (#0)
***************************
Order-by elimination (OBYE)
***************************
OBYE: OBYE performed.
JE: Considering Join Elimination on query block SEL$1 (#0)
*************************
Join Elimination (JE)
*************************
*** 2009-11-04 12:27:14.709
SQL:******* UNPARSED QUERY IS *******
SELECT "XFI"."SERV_PROV_CODE" "SERV_PROV_CODE","XFI"."B1_PER_ID1"
"B1_PER_ID1","XFI"."B1_PER_ID2" "B1_PER_ID2","XFI"."B1_PER_ID3"
"B1_PER_ID3","XFI"."FEEITEM_SEQ_NBR"
"FEEITEM_SEQ_NBR","XFI"."INVOICE_NBR"
"INVOICE_NBR","XFI"."GF_FEE_PERIOD" "GF_FEE_PERIOD","XFI"."GF_FEE"
"GF_FEE","XFI"."GF_DES" "GF_DES","XFI"."GF_UNIT"
"GF_UNIT","XFI"."GF_UDES" "GF_UDES","FINV"."INVOICE_DATE"
"GF_FEE_APPLY_DATE","XFI"."FEEITEM_INVOICE_STATUS"
"FEEITEM_INVOICE_STATUS","XFI"."GF_L1" "GF_L1","XFI"."GF_L2"
"GF_L2","XFI"."GF_L3" "GF_L3","XFI"."X4FEEITEM_INVOICE_UDF1"
"X4FEEITEM_INVOICE_UDF1","XFI"."X4FEEITEM_INVOICE_UDF2"
"X4FEEITEM_INVOICE_UDF2","XFI"."X4FEEITEM_INVOICE_UDF3"
"X4FEEITEM_INVOICE_UDF3","XFI"."X4FEEITEM_INVOICE_UDF4"
"X4FEEITEM_INVOICE_UDF4","XFI"."GF_FEE_SCHEDULE"
"GF_FEE_SCHEDULE","XFI"."FEE_SCHEDULE_VERSION"
"FEE_SCHEDULE_VERSION","XFI"."REC_DATE" "REC_DATE","XFI"."REC_FUL_NAM"
"REC_FUL_NAM","XFI"."REC_STATUS" "REC_STATUS","F4"."GF_COD"
"GF_COD","F4"."GF_PRIORITY" "GF_PRIORITY" FROM
"TEST"."X4FEEITEM_INVOICE" "XFI","TEST"."SETDETAILS"
"SD","TEST"."F4INVOICE" "FINV","TEST"."F4FEEITEM" "F4" WHERE
"SD"."SERV_PROV_CODE"="XFI"."SERV_PROV_CODE" AND
"SD"."B1_PER_ID1"="XFI"."B1_PER_ID1" AND
"SD"."B1_PER_ID2"="XFI"."B1_PER_ID2" AND
"SD"."B1_PER_ID3"="XFI"."B1_PER_ID3" AND
"XFI"."SERV_PROV_CODE"="F4"."SERV_PROV_CODE" AND
"XFI"."B1_PER_ID1"="F4"."B1_PER_ID1" AND
"XFI"."B1_PER_ID2"="F4"."B1_PER_ID2" AND
"XFI"."B1_PER_ID3"="F4"."B1_PER_ID3" AND
"XFI"."FEEITEM_SEQ_NBR"="F4"."FEEITEM_SEQ_NBR" AND
"FINV"."SERV_PROV_CODE"="XFI"."SERV_PROV_CODE" AND
"FINV"."INVOICE_NBR"="XFI"."INVOICE_NBR" AND
"SD"."SERV_PROV_CODE"='SACRAMENTO' AND UPPER("SD"."SET_ID")='SET07'
AND "XFI"."REC_STATUS"='A' AND
"XFI"."FEEITEM_INVOICE_STATUS"='INVOICED' ORDER BY
"FINV"."INVOICE_DATE"
SQL:******* UNPARSED QUERY IS *******
SELECT "XFI"."SERV_PROV_CODE" "SERV_PROV_CODE","XFI"."B1_PER_ID1"
"B1_PER_ID1","XFI"."B1_PER_ID2" "B1_PER_ID2","XFI"."B1_PER_ID3"
"B1_PER_ID3","XFI"."FEEITEM_SEQ_NBR"
"FEEITEM_SEQ_NBR","XFI"."INVOICE_NBR"
"INVOICE_NBR","XFI"."GF_FEE_PERIOD" "GF_FEE_PERIOD","XFI"."GF_FEE"
"GF_FEE","XFI"."GF_DES" "GF_DES","XFI"."GF_UNIT"
"GF_UNIT","XFI"."GF_UDES" "GF_UDES","FINV"."INVOICE_DATE"
"GF_FEE_APPLY_DATE","XFI"."FEEITEM_INVOICE_STATUS"
"FEEITEM_INVOICE_STATUS","XFI"."GF_L1" "GF_L1","XFI"."GF_L2"
"GF_L2","XFI"."GF_L3" "GF_L3","XFI"."X4FEEITEM_INVOICE_UDF1"
"X4FEEITEM_INVOICE_UDF1","XFI"."X4FEEITEM_INVOICE_UDF2"
"X4FEEITEM_INVOICE_UDF2","XFI"."X4FEEITEM_INVOICE_UDF3"
"X4FEEITEM_INVOICE_UDF3","XFI"."X4FEEITEM_INVOICE_UDF4"
"X4FEEITEM_INVOICE_UDF4","XFI"."GF_FEE_SCHEDULE"
"GF_FEE_SCHEDULE","XFI"."FEE_SCHEDULE_VERSION"
"FEE_SCHEDULE_VERSION","XFI"."REC_DATE" "REC_DATE","XFI"."REC_FUL_NAM"
"REC_FUL_NAM","XFI"."REC_STATUS" "REC_STATUS","F4"."GF_COD"
"GF_COD","F4"."GF_PRIORITY" "GF_PRIORITY" FROM
"TEST"."X4FEEITEM_INVOICE" "XFI","TEST"."SETDETAILS"
"SD","TEST"."F4INVOICE" "FINV","TEST"."F4FEEITEM" "F4" WHERE
"SD"."SERV_PROV_CODE"="XFI"."SERV_PROV_CODE" AND
"SD"."B1_PER_ID1"="XFI"."B1_PER_ID1" AND
"SD"."B1_PER_ID2"="XFI"."B1_PER_ID2" AND
"SD"."B1_PER_ID3"="XFI"."B1_PER_ID3" AND
"XFI"."SERV_PROV_CODE"="F4"."SERV_PROV_CODE" AND
"XFI"."B1_PER_ID1"="F4"."B1_PER_ID1" AND
"XFI"."B1_PER_ID2"="F4"."B1_PER_ID2" AND
"XFI"."B1_PER_ID3"="F4"."B1_PER_ID3" AND
"XFI"."FEEITEM_SEQ_NBR"="F4"."FEEITEM_SEQ_NBR" AND
"FINV"."SERV_PROV_CODE"="XFI"."SERV_PROV_CODE" AND
"FINV"."INVOICE_NBR"="XFI"."INVOICE_NBR" AND
"SD"."SERV_PROV_CODE"='SACRAMENTO' AND UPPER("SD"."SET_ID")='SET07'
AND "XFI"."REC_STATUS"='A' AND
"XFI"."FEEITEM_INVOICE_STATUS"='INVOICED' ORDER BY
"FINV"."INVOICE_DATE"
Query block SEL$1 (#0) unchanged
CVM: Considering view merge in query block SEL$1 (#0)
JE: Considering Join Elimination on query block SEL$1 (#0)
*************************
Join Elimination (JE)
*************************
SQL:******* UNPARSED QUERY IS *******
SELECT "XFI"."SERV_PROV_CODE" "SERV_PROV_CODE","XFI"."B1_PER_ID1"
"B1_PER_ID1","XFI"."B1_PER_ID2" "B1_PER_ID2","XFI"."B1_PER_ID3"
"B1_PER_ID3","XFI"."FEEITEM_SEQ_NBR"
"FEEITEM_SEQ_NBR","XFI"."INVOICE_NBR"
"INVOICE_NBR","XFI"."GF_FEE_PERIOD" "GF_FEE_PERIOD","XFI"."GF_FEE"
"GF_FEE","XFI"."GF_DES" "GF_DES","XFI"."GF_UNIT"
"GF_UNIT","XFI"."GF_UDES" "GF_UDES","FINV"."INVOICE_DATE"
"GF_FEE_APPLY_DATE","XFI"."FEEITEM_INVOICE_STATUS"
"FEEITEM_INVOICE_STATUS","XFI"."GF_L1" "GF_L1","XFI"."GF_L2"
"GF_L2","XFI"."GF_L3" "GF_L3","XFI"."X4FEEITEM_INVOICE_UDF1"
"X4FEEITEM_INVOICE_UDF1","XFI"."X4FEEITEM_INVOICE_UDF2"
"X4FEEITEM_INVOICE_UDF2","XFI"."X4FEEITEM_INVOICE_UDF3"
"X4FEEITEM_INVOICE_UDF3","XFI"."X4FEEITEM_INVOICE_UDF4"
"X4FEEITEM_INVOICE_UDF4","XFI"."GF_FEE_SCHEDULE"
"GF_FEE_SCHEDULE","XFI"."FEE_SCHEDULE_VERSION"
"FEE_SCHEDULE_VERSION","XFI"."REC_DATE" "REC_DATE","XFI"."REC_FUL_NAM"
"REC_FUL_NAM","XFI"."REC_STATUS" "REC_STATUS","F4"."GF_COD"
"GF_COD","F4"."GF_PRIORITY" "GF_PRIORITY" FROM
"TEST"."X4FEEITEM_INVOICE" "XFI","TEST"."SETDETAILS"
"SD","TEST"."F4INVOICE" "FINV","TEST"."F4FEEITEM" "F4" WHERE
"SD"."SERV_PROV_CODE"="XFI"."SERV_PROV_CODE" AND
"SD"."B1_PER_ID1"="XFI"."B1_PER_ID1" AND
"SD"."B1_PER_ID2"="XFI"."B1_PER_ID2" AND
"SD"."B1_PER_ID3"="XFI"."B1_PER_ID3" AND
"XFI"."SERV_PROV_CODE"="F4"."SERV_PROV_CODE" AND
"XFI"."B1_PER_ID1"="F4"."B1_PER_ID1" AND
"XFI"."B1_PER_ID2"="F4"."B1_PER_ID2" AND
"XFI"."B1_PER_ID3"="F4"."B1_PER_ID3" AND
"XFI"."FEEITEM_SEQ_NBR"="F4"."FEEITEM_SEQ_NBR" AND
"FINV"."SERV_PROV_CODE"="XFI"."SERV_PROV_CODE" AND
"FINV"."INVOICE_NBR"="XFI"."INVOICE_NBR" AND
"SD"."SERV_PROV_CODE"='SACRAMENTO' AND UPPER("SD"."SET_ID")='SET07'
AND "XFI"."REC_STATUS"='A' AND
"XFI"."FEEITEM_INVOICE_STATUS"='INVOICED' ORDER BY
"FINV"."INVOICE_DATE"
SQL:******* UNPARSED QUERY IS *******
SELECT "XFI"."SERV_PROV_CODE" "SERV_PROV_CODE","XFI"."B1_PER_ID1"
"B1_PER_ID1","XFI"."B1_PER_ID2" "B1_PER_ID2","XFI"."B1_PER_ID3"
"B1_PER_ID3","XFI"."FEEITEM_SEQ_NBR"
"FEEITEM_SEQ_NBR","XFI"."INVOICE_NBR"
"INVOICE_NBR","XFI"."GF_FEE_PERIOD" "GF_FEE_PERIOD","XFI"."GF_FEE"
"GF_FEE","XFI"."GF_DES" "GF_DES","XFI"."GF_UNIT"
"GF_UNIT","XFI"."GF_UDES" "GF_UDES","FINV"."INVOICE_DATE"
"GF_FEE_APPLY_DATE","XFI"."FEEITEM_INVOICE_STATUS"
"FEEITEM_INVOICE_STATUS","XFI"."GF_L1" "GF_L1","XFI"."GF_L2"
"GF_L2","XFI"."GF_L3" "GF_L3","XFI"."X4FEEITEM_INVOICE_UDF1"
"X4FEEITEM_INVOICE_UDF1","XFI"."X4FEEITEM_INVOICE_UDF2"
"X4FEEITEM_INVOICE_UDF2","XFI"."X4FEEITEM_INVOICE_UDF3"
"X4FEEITEM_INVOICE_UDF3","XFI"."X4FEEITEM_INVOICE_UDF4"
"X4FEEITEM_INVOICE_UDF4","XFI"."GF_FEE_SCHEDULE"
"GF_FEE_SCHEDULE","XFI"."FEE_SCHEDULE_VERSION"
"FEE_SCHEDULE_VERSION","XFI"."REC_DATE" "REC_DATE","XFI"."REC_FUL_NAM"
"REC_FUL_NAM","XFI"."REC_STATUS" "REC_STATUS","F4"."GF_COD"
"GF_COD","F4"."GF_PRIORITY" "GF_PRIORITY" FROM
"TEST"."X4FEEITEM_INVOICE" "XFI","TEST"."SETDETAILS"
"SD","TEST"."F4INVOICE" "FINV","TEST"."F4FEEITEM" "F4" WHERE
"SD"."SERV_PROV_CODE"="XFI"."SERV_PROV_CODE" AND
"SD"."B1_PER_ID1"="XFI"."B1_PER_ID1" AND
"SD"."B1_PER_ID2"="XFI"."B1_PER_ID2" AND
"SD"."B1_PER_ID3"="XFI"."B1_PER_ID3" AND
"XFI"."SERV_PROV_CODE"="F4"."SERV_PROV_CODE" AND
"XFI"."B1_PER_ID1"="F4"."B1_PER_ID1" AND
"XFI"."B1_PER_ID2"="F4"."B1_PER_ID2" AND
"XFI"."B1_PER_ID3"="F4"."B1_PER_ID3" AND
"XFI"."FEEITEM_SEQ_NBR"="F4"."FEEITEM_SEQ_NBR" AND
"FINV"."SERV_PROV_CODE"="XFI"."SERV_PROV_CODE" AND
"FINV"."INVOICE_NBR"="XFI"."INVOICE_NBR" AND
"SD"."SERV_PROV_CODE"='SACRAMENTO' AND UPPER("SD"."SET_ID")='SET07'
AND "XFI"."REC_STATUS"='A' AND
"XFI"."FEEITEM_INVOICE_STATUS"='INVOICED' ORDER BY
"FINV"."INVOICE_DATE"
Query block SEL$1 (#0) unchanged
query block SEL$1 (#0) unchanged
Considering Query Transformations on query block SEL$1 (#0)
**************************
Query transformations (QT)
**************************
CBQT bypassed for query block SEL$1 (#0): Disabled by parameter.
CBQT: Validity checks failed for 5n7ufx7tz1uks.
CSE: Considering common sub-expression elimination in query block SEL
$1 (#0)
*************************
Common Subexpression elimination (CSE)
*************************
CSE: CSE not performed on query block SEL$1 (#0).
SU: Considering subquery unnesting in query block SEL$1 (#0)
********************
Subquery Unnest (SU)
********************
SJC: Considering set-join conversion in query block SEL$1 (#0)
*************************
Set-Join Conversion (SJC)
*************************
SJC: not performed
JE: Considering Join Elimination on query block SEL$1 (#0)
*************************
Join Elimination (JE)
*************************
SQL:******* UNPARSED QUERY IS *******
SELECT "XFI"."SERV_PROV_CODE" "SERV_PROV_CODE","XFI"."B1_PER_ID1"
"B1_PER_ID1","XFI"."B1_PER_ID2" "B1_PER_ID2","XFI"."B1_PER_ID3"
"B1_PER_ID3","XFI"."FEEITEM_SEQ_NBR"
"FEEITEM_SEQ_NBR","XFI"."INVOICE_NBR"
"INVOICE_NBR","XFI"."GF_FEE_PERIOD" "GF_FEE_PERIOD","XFI"."GF_FEE"
"GF_FEE","XFI"."GF_DES" "GF_DES","XFI"."GF_UNIT"
"GF_UNIT","XFI"."GF_UDES" "GF_UDES","FINV"."INVOICE_DATE"
"GF_FEE_APPLY_DATE","XFI"."FEEITEM_INVOICE_STATUS"
"FEEITEM_INVOICE_STATUS","XFI"."GF_L1" "GF_L1","XFI"."GF_L2"
"GF_L2","XFI"."GF_L3" "GF_L3","XFI"."X4FEEITEM_INVOICE_UDF1"
"X4FEEITEM_INVOICE_UDF1","XFI"."X4FEEITEM_INVOICE_UDF2"
"X4FEEITEM_INVOICE_UDF2","XFI"."X4FEEITEM_INVOICE_UDF3"
"X4FEEITEM_INVOICE_UDF3","XFI"."X4FEEITEM_INVOICE_UDF4"
"X4FEEITEM_INVOICE_UDF4","XFI"."GF_FEE_SCHEDULE"
"GF_FEE_SCHEDULE","XFI"."FEE_SCHEDULE_VERSION"
"FEE_SCHEDULE_VERSION","XFI"."REC_DATE" "REC_DATE","XFI"."REC_FUL_NAM"
"REC_FUL_NAM","XFI"."REC_STATUS" "REC_STATUS","F4"."GF_COD"
"GF_COD","F4"."GF_PRIORITY" "GF_PRIORITY" FROM
"TEST"."X4FEEITEM_INVOICE" "XFI","TEST"."SETDETAILS"
"SD","TEST"."F4INVOICE" "FINV","TEST"."F4FEEITEM" "F4" WHERE
"SD"."SERV_PROV_CODE"="XFI"."SERV_PROV_CODE" AND
"SD"."B1_PER_ID1"="XFI"."B1_PER_ID1" AND
"SD"."B1_PER_ID2"="XFI"."B1_PER_ID2" AND
"SD"."B1_PER_ID3"="XFI"."B1_PER_ID3" AND
"XFI"."SERV_PROV_CODE"="F4"."SERV_PROV_CODE" AND
"XFI"."B1_PER_ID1"="F4"."B1_PER_ID1" AND
"XFI"."B1_PER_ID2"="F4"."B1_PER_ID2" AND
"XFI"."B1_PER_ID3"="F4"."B1_PER_ID3" AND
"XFI"."FEEITEM_SEQ_NBR"="F4"."FEEITEM_SEQ_NBR" AND
"FINV"."SERV_PROV_CODE"="XFI"."SERV_PROV_CODE" AND
"FINV"."INVOICE_NBR"="XFI"."INVOICE_NBR" AND
"SD"."SERV_PROV_CODE"='SACRAMENTO' AND UPPER("SD"."SET_ID")='SET07'
AND "XFI"."REC_STATUS"='A' AND
"XFI"."FEEITEM_INVOICE_STATUS"='INVOICED' ORDER BY
"FINV"."INVOICE_DATE"
SQL:******* UNPARSED QUERY IS *******
SELECT "XFI"."SERV_PROV_CODE" "SERV_PROV_CODE","XFI"."B1_PER_ID1"
"B1_PER_ID1","XFI"."B1_PER_ID2" "B1_PER_ID2","XFI"."B1_PER_ID3"
"B1_PER_ID3","XFI"."FEEITEM_SEQ_NBR"
"FEEITEM_SEQ_NBR","XFI"."INVOICE_NBR"
"INVOICE_NBR","XFI"."GF_FEE_PERIOD" "GF_FEE_PERIOD","XFI"."GF_FEE"
"GF_FEE","XFI"."GF_DES" "GF_DES","XFI"."GF_UNIT"
"GF_UNIT","XFI"."GF_UDES" "GF_UDES","FINV"."INVOICE_DATE"
"GF_FEE_APPLY_DATE","XFI"."FEEITEM_INVOICE_STATUS"
"FEEITEM_INVOICE_STATUS","XFI"."GF_L1" "GF_L1","XFI"."GF_L2"
"GF_L2","XFI"."GF_L3" "GF_L3","XFI"."X4FEEITEM_INVOICE_UDF1"
"X4FEEITEM_INVOICE_UDF1","XFI"."X4FEEITEM_INVOICE_UDF2"
"X4FEEITEM_INVOICE_UDF2","XFI"."X4FEEITEM_INVOICE_UDF3"
"X4FEEITEM_INVOICE_UDF3","XFI"."X4FEEITEM_INVOICE_UDF4"
"X4FEEITEM_INVOICE_UDF4","XFI"."GF_FEE_SCHEDULE"
"GF_FEE_SCHEDULE","XFI"."FEE_SCHEDULE_VERSION"
"FEE_SCHEDULE_VERSION","XFI"."REC_DATE" "REC_DATE","XFI"."REC_FUL_NAM"
"REC_FUL_NAM","XFI"."REC_STATUS" "REC_STATUS","F4"."GF_COD"
"GF_COD","F4"."GF_PRIORITY" "GF_PRIORITY" FROM
"TEST"."X4FEEITEM_INVOICE" "XFI","TEST"."SETDETAILS"
"SD","TEST"."F4INVOICE" "FINV","TEST"."F4FEEITEM" "F4" WHERE
"SD"."SERV_PROV_CODE"="XFI"."SERV_PROV_CODE" AND
"SD"."B1_PER_ID1"="XFI"."B1_PER_ID1" AND
"SD"."B1_PER_ID2"="XFI"."B1_PER_ID2" AND
"SD"."B1_PER_ID3"="XFI"."B1_PER_ID3" AND
"XFI"."SERV_PROV_CODE"="F4"."SERV_PROV_CODE" AND
"XFI"."B1_PER_ID1"="F4"."B1_PER_ID1" AND
"XFI"."B1_PER_ID2"="F4"."B1_PER_ID2" AND
"XFI"."B1_PER_ID3"="F4"."B1_PER_ID3" AND
"XFI"."FEEITEM_SEQ_NBR"="F4"."FEEITEM_SEQ_NBR" AND
"FINV"."SERV_PROV_CODE"="XFI"."SERV_PROV_CODE" AND
"FINV"."INVOICE_NBR"="XFI"."INVOICE_NBR" AND
"SD"."SERV_PROV_CODE"='SACRAMENTO' AND UPPER("SD"."SET_ID")='SET07'
AND "XFI"."REC_STATUS"='A' AND
"XFI"."FEEITEM_INVOICE_STATUS"='INVOICED' ORDER BY
"FINV"."INVOICE_DATE"
Query block SEL$1 (#0) unchanged
PM: Considering predicate move-around in query block SEL$1 (#0)
**************************
Predicate Move-Around (PM)
**************************
PM: PM bypassed: Outer query contains no views.
PM: PM bypassed: Outer query contains no views.
query block SEL$1 (#0) unchanged
FPD: Considering simple filter push in query block SEL$1 (#0)
"SD"."SERV_PROV_CODE"="XFI"."SERV_PROV_CODE" AND
"SD"."B1_PER_ID1"="XFI"."B1_PER_ID1" AND
"SD"."B1_PER_ID2"="XFI"."B1_PER_ID2" AND
"SD"."B1_PER_ID3"="XFI"."B1_PER_ID3" AND
"XFI"."SERV_PROV_CODE"="F4"."SERV_PROV_CODE" AND
"XFI"."B1_PER_ID1"="F4"."B1_PER_ID1
try to generate transitive predicate from check constraints for query
block SEL$1 (#0)
finally: "SD"."SERV_PROV_CODE"="XFI"."SERV_PROV_CODE" AND
"SD"."B1_PER_ID1"="XFI"."B1_PER_ID1" AND
"SD"."B1_PER_ID2"="XFI"."B1_PER_ID2" AND
"SD"."B1_PER_ID3"="XFI"."B1_PER_ID3" AND
"XFI"."SERV_PROV_CODE"="F4"."SERV_PROV_CODE" AND
"XFI"."B1_PER_ID1"="F4"."B1_PER_ID1
FPD: transitive predicates are generated in query block SEL$1 (#0)
"SD"."SERV_PROV_CODE"="XFI"."SERV_PROV_CODE" AND
"SD"."B1_PER_ID1"="XFI"."B1_PER_ID1" AND
"SD"."B1_PER_ID2"="XFI"."B1_PER_ID2" AND
"SD"."B1_PER_ID3"="XFI"."B1_PER_ID3" AND
"XFI"."SERV_PROV_CODE"="F4"."SERV_PROV_CODE" AND
"XFI"."B1_PER_ID1"="F4"."B1_PER_ID1
apadrv-start sqlid=6493995954418543192
:
call(in-use=9024, alloc=16360), compile(in-use=85684,
alloc=88456), execution(in-use=3448, alloc=4060)
*******************************************
Peeked values of the binds in SQL statement
*******************************************
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "XFI"."SERV_PROV_CODE" "SERV_PROV_CODE","XFI"."B1_PER_ID1"
"B1_PER_ID1","XFI"."B1_PER_ID2" "B1_PER_ID2","XFI"."B1_PER_ID3"
"B1_PER_ID3","XFI"."FEEITEM_SEQ_NBR"
"FEEITEM_SEQ_NBR","XFI"."INVOICE_NBR"
"INVOICE_NBR","XFI"."GF_FEE_PERIOD" "GF_FEE_PERIOD","XFI"."GF_FEE"
"GF_FEE","XFI"."GF_DES" "GF_DES","XFI"."GF_UNIT"
"GF_UNIT","XFI"."GF_UDES" "GF_UDES","FINV"."INVOICE_DATE"
"GF_FEE_APPLY_DATE","XFI"."FEEITEM_INVOICE_STATUS"
"FEEITEM_INVOICE_STATUS","XFI"."GF_L1" "GF_L1","XFI"."GF_L2"
"GF_L2","XFI"."GF_L3" "GF_L3","XFI"."X4FEEITEM_INVOICE_UDF1"
"X4FEEITEM_INVOICE_UDF1","XFI"."X4FEEITEM_INVOICE_UDF2"
"X4FEEITEM_INVOICE_UDF2","XFI"."X4FEEITEM_INVOICE_UDF3"
"X4FEEITEM_INVOICE_UDF3","XFI"."X4FEEITEM_INVOICE_UDF4"
"X4FEEITEM_INVOICE_UDF4","XFI"."GF_FEE_SCHEDULE"
"GF_FEE_SCHEDULE","XFI"."FEE_SCHEDULE_VERSION"
"FEE_SCHEDULE_VERSION","XFI"."REC_DATE" "REC_DATE","XFI"."REC_FUL_NAM"
"REC_FUL_NAM","XFI"."REC_STATUS" "REC_STATUS","F4"."GF_COD"
"GF_COD","F4"."GF_PRIORITY" "GF_PRIORITY" FROM
"TEST"."X4FEEITEM_INVOICE" "XFI","TEST"."SETDETAILS"
"SD","TEST"."F4INVOICE" "FINV","TEST"."F4FEEITEM" "F4" WHERE
"SD"."SERV_PROV_CODE"="XFI"."SERV_PROV_CODE" AND
"SD"."B1_PER_ID1"="XFI"."B1_PER_ID1" AND
"SD"."B1_PER_ID2"="XFI"."B1_PER_ID2" AND
"SD"."B1_PER_ID3"="XFI"."B1_PER_ID3" AND
"XFI"."SERV_PROV_CODE"="F4"."SERV_PROV_CODE" AND
"XFI"."B1_PER_ID1"="F4"."B1_PER_ID1" AND
"XFI"."B1_PER_ID2"="F4"."B1_PER_ID2" AND
"XFI"."B1_PER_ID3"="F4"."B1_PER_ID3" AND
"XFI"."FEEITEM_SEQ_NBR"="F4"."FEEITEM_SEQ_NBR" AND
"FINV"."SERV_PROV_CODE"="XFI"."SERV_PROV_CODE" AND
"FINV"."INVOICE_NBR"="XFI"."INVOICE_NBR" AND
"SD"."SERV_PROV_CODE"='SACRAMENTO' AND UPPER("SD"."SET_ID")='SET07'
AND "XFI"."REC_STATUS"='A' AND
"XFI"."FEEITEM_INVOICE_STATUS"='INVOICED' AND
"XFI"."SERV_PROV_CODE"='SACRAMENTO' AND
"F4"."SERV_PROV_CODE"='SACRAMENTO' AND
"FINV"."SERV_PROV_CODE"='SACRAMENTO' ORDER BY "FINV"."INVOICE_DATE"
*************************
First K Rows: Setup begin
kkoqbc: optimizing query block SEL$1 (#0)
:
call(in-use=9564, alloc=16360), compile(in-use=93204,
alloc=96704), execution(in-use=3640, alloc=4060)
kkoqbc-subheap (create addr=0x6ddabc)
*** 2009-11-04 12:27:16.195
****************
QUERY BLOCK TEXT
****************
SELECT xfi.serv_prov_code,
xfi.b1_per_id1,
xfi.b1_per_id2,
xfi.b1_per_id3,
xfi.feeitem_seq_nbr,
xfi.invoice_nbr,
xfi.gf_fee_period,
xfi.gf_fee,
xfi.gf_des,
xfi.gf_unit,
xfi.gf_udes,
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (optimizer): qb_name=SEL$1 nbfros=4 flg=0
fro(0): flg=0 objn=73740 hint_alias="F4"@"SEL$1"
fro(1): flg=0 objn=73746 hint_alias="FINV"@"SEL$1"
fro(2): flg=0 objn=74784 hint_alias="SD"@"SEL$1"
fro(3): flg=0 objn=74848 hint_alias="XFI"@"SEL$1"
-----------------------------
SYSTEM STATISTICS INFORMATION
-----------------------------
Using NOWORKLOAD Stats
CPUSPEEDNW: 2696 millions instructions/sec (default is 100)
IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
IOSEEKTIM: 10 milliseconds (default is 10)
MBRC: -1 blocks (default is 8)
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: F4FEEITEM Alias: F4
#Rows: 1291140 #Blks: 35597 AvgRowLen: 185.00
Index Stats::
Index: F4FEEITEM_PK Col#: 1 2 3 4 5
LVLS: 2 #LB: 10410 #DK: 1291140 LB/K: 1.00 DB/K: 1.00 CLUF:
515902.00
Index: F4FEEITEM_POS_IX Col#: 1 38
LVLS: 2 #LB: 6304 #DK: 75 LB/K: 84.00 DB/K: 467.00 CLUF:
35079.00
***********************
Table Stats::
Table: F4INVOICE Alias: FINV
#Rows: 305860 #Blks: 3646 AvgRowLen: 72.00
Index Stats::
Index: F4INVOICE_BATCH_DATE_IX Col#: 1 8 12
LVLS: 2 #LB: 1307 #DK: 82901 LB/K: 1.00 DB/K: 1.00 CLUF:
4528.00
Index: F4INVOICE_BATCH_NBR_IX Col#: 1 11
LVLS: 2 #LB: 954 #DK: 8 LB/K: 119.00 DB/K: 456.00 CLUF:
3650.00
Index: F4INVOICE_DATE_IX Col#: 1 8
LVLS: 2 #LB: 1255 #DK: 81356 LB/K: 1.00 DB/K: 1.00 CLUF:
4451.00
Index: F4INVOICE_PK Col#: 1 2
LVLS: 2 #LB: 1294 #DK: 307685 LB/K: 1.00 DB/K: 1.00 CLUF:
109019.00
***********************
Table Stats::
Table: SETDETAILS Alias: SD
#Rows: 390896 #Blks: 3646 AvgRowLen: 74.00
Index Stats::
Index: SETDETAILS_ADDR_IX Col#: 1 14
LVLS: 2 #LB: 1806 #DK: 2 LB/K: 903.00 DB/K: 1819.00 CLUF:
3638.00
Index: SETDETAILS_IX Col#: 1 3 4 5 6
LVLS: 2 #LB: 4247 #DK: 390879 LB/K: 1.00 DB/K: 1.00 CLUF:
10423.00
Index: SETDETAILS_PAR_IX Col#: 12 13
LVLS: 0 #LB: 1 #DK: 6 LB/K: 1.00 DB/K: 1.00 CLUF: 3.00
Index: SETDETAILS_PID_IX Col#: 1 4 5 6
LVLS: 2 #LB: 2185 #DK: 180591 LB/K: 1.00 DB/K: 2.00 CLUF:
371867.00
Index: SETDETAILS_PK Col#: 1 2
LVLS: 2 #LB: 1820 #DK: 390896 LB/K: 1.00 DB/K: 1.00 CLUF:
354197.00
Index: SETDETAILS_SETID1_IX Col#: 1 3
LVLS: 2 #LB: 1652 #DK: 3665 LB/K: 1.00 DB/K: 2.00 CLUF:
7579.00
Index: SETDETAILS_SETID_IX Col#: 1 17
LVLS: 2 #LB: 1652 #DK: 3664 LB/K: 1.00 DB/K: 2.00 CLUF:
7585.00
Index: SETDETAILS_STAT_IX Col#: 1 15
LVLS: 2 #LB: 1806 #DK: 1 LB/K: 1806.00 DB/K: 3637.00 CLUF:
3637.00
***********************
Table Stats::
Table: X4FEEITEM_INVOICE Alias: XFI
#Rows: 1278471 #Blks: 25397 AvgRowLen: 131.00
Index Stats::
Index: X4FEEITEM_INVOICE_NBR_IX Col#: 1 6
LVLS: 2 #LB: 4328 #DK: 303821 LB/K: 1.00 DB/K: 1.00 CLUF:
27474.00
Index: X4FEEITEM_INVOICE_PK Col#: 1 2 3 4 5 6
LVLS: 2 #LB: 12530 #DK: 1278471 LB/K: 1.00 DB/K: 1.00 CLUF:
209916.00
Access path analysis for X4FEEITEM_INVOICE
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for X4FEEITEM_INVOICE[XFI]
Column (#13):
NewDensity:0.006698, OldDensity:0.000000 BktCnt:1278471, PopBktCnt:
1278471, PopValCnt:3, NDV:3
ColGroup (#1, Index) X4FEEITEM_INVOICE_PK
Col#: 1 2 3 4 5 6 CorStregth: 4642883360575.80
ColGroup (#2, Index) X4FEEITEM_INVOICE_NBR_IX
Col#: 1 6 CorStregth: 4.09
ColGroup Usage:: PredCnt: 3 Matches Full: Partial:
Table: X4FEEITEM_INVOICE Alias: XFI
Card: Original: 1278471.000000 Rounded: 1187146 Computed:
1187146.28 Non Adjusted: 1187146.28
Access Path: TableScan
Cost: 6916.07 Resp: 6916.07 Degree: 0
Cost_io: 6880.00 Cost_cpu: 1166930829
Resp_io: 6880.00 Resp_cpu: 1166930829
Access Path: index (RangeScan)
Index: X4FEEITEM_INVOICE_NBR_IX
resc_io: 30352.00 resc_cpu: 1349160084
ix_sel: 0.954312 ix_sel_with_filters: 0.954312
Cost: 3039.37 Resp: 3039.37 Degree: 1
Access Path: index (RangeScan)
Index: X4FEEITEM_INVOICE_PK
resc_io: 212286.00 resc_cpu: 2644792149
ix_sel: 0.954312 ix_sel_with_filters: 0.954312
Cost: 21236.77 Resp: 21236.77 Degree: 1
****** trying bitmap/domain indexes ******
****** finished trying bitmap/domain indexes ******
Best:: AccessPath: IndexRange
Index: X4FEEITEM_INVOICE_NBR_IX
Cost: 3039.37 Degree: 1 Resp: 3039.37 Card: 1187146.28
Bytes: 0
Access path analysis for SETDETAILS
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for SETDETAILS[SD]
Column (#17):
NewDensity:0.000065, OldDensity:0.002105 BktCnt:254, PopBktCnt:
194, PopValCnt:19, NDV:3664
Column (#3):
NewDensity:0.000063, OldDensity:0.001961 BktCnt:254, PopBktCnt:
196, PopValCnt:20, NDV:3665
ColGroup (#8, Index) SETDETAILS_IX
Col#: 1 3 4 5 6 CorStregth: 11963.23
ColGroup (#2, Index) SETDETAILS_PID_IX
Col#: 1 4 5 6 CorStregth: 7.07
ColGroup (#1, Index) SETDETAILS_SETID1_IX
Col#: 1 3 CorStregth: 1.00
ColGroup (#3, Index) SETDETAILS_SETID_IX
Col#: 1 17 CorStregth: 1.00
ColGroup (#5, Index) SETDETAILS_STAT_IX
Col#: 1 15 CorStregth: -1.00
ColGroup (#7, Index) SETDETAILS_PK
Col#: 1 2 CorStregth: -1.00
ColGroup (#6, Index) SETDETAILS_PAR_IX
Col#: 12 13 CorStregth: -1.00
ColGroup (#4, Index) SETDETAILS_ADDR_IX
Col#: 1 14 CorStregth: -1.00
ColGroup Usage:: PredCnt: 2 Matches Full: Partial:
***** Virtual column Adjustment ******
Column name SYS_NC00017$
cost_cpu 150.00
cost_io
179769313486231570814527423731704356798070567525844996598917476803157260780028538760589558632766878171540458953514382464234321326889464182768467546703537516986049910576551282076245490090389328944075868508455133942304583236903222948165808559332123348274797826204144723168738177180919299881250404026184124858368.00
***** End virtual column Adjustment ******
Table: SETDETAILS Alias: SD
Card: Original: 390896.000000 Rounded: 25 Computed: 25.33 Non
Adjusted: 25.33
Access Path: TableScan
Cost: 993.91 Resp: 993.91 Degree: 0
Cost_io: 989.00 Cost_cpu: 158872177
Resp_io: 989.00 Resp_cpu: 158872177
Access Path: index (RangeScan)
Index: SETDETAILS_ADDR_IX
resc_io: 5446.00 resc_cpu: 273320887
ix_sel: 0.999999 ix_sel_with_filters: 0.999999
Cost: 545.44 Resp: 545.44 Degree: 1
Access Path: index (RangeScan)
Index: SETDETAILS_IX
resc_io: 14672.00 resc_cpu: 339023293
ix_sel: 0.999999 ix_sel_with_filters: 0.999999
Cost: 1468.25 Resp: 1468.25 Degree: 1
Access Path: index (RangeScan)
Index: SETDETAILS_PID_IX
resc_io: 374054.00 resc_cpu: 2898340643
ix_sel: 0.999999 ix_sel_with_filters: 0.999999
Cost: 37414.36 Resp: 37414.36 Degree: 1
Access Path: index (RangeScan)
Index: SETDETAILS_PK
resc_io: 356019.00 resc_cpu: 2769905472
ix_sel: 0.999999 ix_sel_with_filters: 0.999999
Cost: 35610.46 Resp: 35610.46 Degree: 1
Access Path: index (RangeScan)
Index: SETDETAILS_SETID1_IX
resc_io: 9233.00 resc_cpu: 300289781
ix_sel: 0.999999 ix_sel_with_filters: 0.999999
Cost: 924.23 Resp: 924.23 Degree: 1
ColGroup Usage:: PredCnt: 2 Matches Full: Partial:
ColGroup Usage:: PredCnt: 2 Matches Full: Partial:
***** Virtual column Adjustment ******
Column name SYS_NC00017$
cost_cpu 150.00
cost_io
179769313486231570814527423731704356798070567525844996598917476803157260780028538760589558632766878171540458953514382464234321326889464182768467546703537516986049910576551282076245490090389328944075868508455133942304583236903222948165808559332123348274797826204144723168738177180919299881250404026184124858368.00
***** End virtual column Adjustment ******
Access Path: index (AllEqRange)
Index: SETDETAILS_SETID_IX
resc_io: 4.00 resc_cpu: 40186
ix_sel: 0.000065 ix_sel_with_filters: 0.000065
Cost: 1.00 Resp: 1.00 Degree: 1
Access Path: index (RangeScan)
Index: SETDETAILS_STAT_IX
resc_io: 5445.00 resc_cpu: 273313766
ix_sel: 0.999999 ix_sel_with_filters: 0.999999
Cost: 545.34 Resp: 545.34 Degree: 1
****** trying bitmap/domain indexes ******
****** finished trying bitmap/domain indexes ******
Best:: AccessPath: IndexRange
Index: SETDETAILS_SETID_IX
Cost: 1.00 Degree: 1 Resp: 1.00 Card: 25.33 Bytes: 0
Access path analysis for F4INVOICE
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for F4INVOICE[FINV]
Table: F4INVOICE Alias: FINV
Card: Original: 305860.000000 Rounded: 248742 Computed:
248742.29 Non Adjusted: 248742.29
Access Path: TableScan
Cost: 992.77 Resp: 992.77 Degree: 0
Cost_io: 989.00 Cost_cpu: 121960650
Resp_io: 989.00 Resp_cpu: 121960650
Access Path: index (RangeScan)
Index: F4INVOICE_BATCH_DATE_IX
resc_io: 4748.00 resc_cpu: 156037217
ix_sel: 0.813255 ix_sel_with_filters: 0.813255
Cost: 475.28 Resp: 475.28 Degree: 1
Access Path: index (RangeScan)
Index: F4INVOICE_BATCH_NBR_IX
resc_io: 3747.00 resc_cpu: 147769406
ix_sel: 0.813255 ix_sel_with_filters: 0.813255
Cost: 375.16 Resp: 375.16 Degree: 1
Access Path: index (RangeScan)
Index: F4INVOICE_DATE_IX
resc_io: 4643.00 resc_cpu: 154714696
ix_sel: 0.813255 ix_sel_with_filters: 0.813255
Cost: 464.78 Resp: 464.78 Degree: 1
Access Path: index (RangeScan)
Index: F4INVOICE_PK
resc_io: 89716.00 resc_cpu: 761518341
ix_sel: 0.813255 ix_sel_with_filters: 0.813255
Cost: 8973.95 Resp: 8973.95 Degree: 1
****** trying bitmap/domain indexes ******
****** finished trying bitmap/domain indexes ******
Best:: AccessPath: IndexRange
Index: F4INVOICE_BATCH_NBR_IX
Cost: 375.16 Degree: 1 Resp: 375.16 Card: 248742.29
Bytes: 0
Access path analysis for F4FEEITEM
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for F4FEEITEM[F4]
Table: F4FEEITEM Alias: F4
Card: Original: 1291140.000000 Rounded: 1231517 Computed:
1231517.00 Non Adjusted: 1231517.00
Access Path: TableScan
Cost: 9692.31 Resp: 9692.31 Degree: 0
Cost_io: 9643.00 Cost_cpu: 1595464860
Resp_io: 9643.00 Resp_cpu: 1595464860
Access Path: index (RangeScan)
Index: F4FEEITEM_PK
resc_io: 502011.00 resc_cpu: 5089807126
ix_sel: 0.953821 ix_sel_with_filters: 0.953821
Cost: 50216.83 Resp: 50216.83 Degree: 1
Access Path: index (RangeScan)
Index: F4FEEITEM_POS_IX
resc_io: 39475.00 resc_cpu: 1795884754
ix_sel: 0.953821 ix_sel_with_filters: 0.953821
Cost: 3953.05 Resp: 3953.05 Degree: 1
****** trying bitmap/domain indexes ******
****** finished trying bitmap/domain indexes ******
Best:: AccessPath: IndexRange
Index: F4FEEITEM_POS_IX
Cost: 3953.05 Degree: 1 Resp: 3953.05 Card: 1231517.00
Bytes: 0
Join ColGroups for X4FEEITEM_INVOICE[XFI] and SETDETAILS[SD] : Using
cdn sanity check
ColGroup (#2, Index) F4FEEITEM_PK
Col#: 1 2 3 4 5 CorStregth: 45040975.51
ColGroup (#1, Index) F4FEEITEM_POS_IX
Col#: 1 38 CorStregth: -1.00
Join ColGroups for X4FEEITEM_INVOICE[XFI] and F4FEEITEM[F4] : Using
cdn sanity check
***************************************
OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***************************************
Considering cardinality-based initial join order.
Permutations for Starting Table :0
Join order[1]: SETDETAILS[SD]#0 F4INVOICE[FINV]#1 X4FEEITEM_INVOICE
[XFI]#2 F4FEEITEM[F4]#3
***************
Now joining: F4INVOICE[FINV]#1
***************
NL Join
Outer table: Card: 25.33 Cost: 1.00 Resp: 1.00 Degree: 1 Bytes:
38
Access path analysis for F4INVOICE
Inner table: F4INVOICE Alias: FINV
Access Path: TableScan
NL Join: Cost: 24783.24 Resp: 24783.24 Degree: 1
Cost_io: 24689.00 Cost_cpu: 3049020275
Resp_io: 24689.00 Resp_cpu: 3049020275
Access Path: index (RangeScan)
Index: F4INVOICE_BATCH_DATE_IX
resc_io: 4748.00 resc_cpu: 156037217
ix_sel: 0.813255 ix_sel_with_filters: 0.813255
NL Join : Cost: 11883.06 Resp: 11883.06 Degree: 1
Cost_io: 11871.00 Cost_cpu: 390097061
Resp_io: 11871.00 Resp_cpu: 390097061
Access Path: index (RangeScan)
Index: F4INVOICE_BATCH_NBR_IX
resc_io: 3747.00 resc_cpu: 147769406
ix_sel: 0.813255 ix_sel_with_filters: 0.813255
NL Join : Cost: 9379.92 Resp: 9379.92 Degree: 1
Cost_io: 9368.50 Cost_cpu: 369427533
Resp_io: 9368.50 Resp_cpu: 369427533
Access Path: index (RangeScan)
Index: F4INVOICE_DATE_IX
resc_io: 4643.00 resc_cpu: 154714696
ix_sel: 0.813255 ix_sel_with_filters: 0.813255
NL Join : Cost: 11620.46 Resp: 11620.46 Degree: 1
Cost_io: 11608.50 Cost_cpu: 386790758
Resp_io: 11608.50 Resp_cpu: 386790758
Access Path: index (RangeScan)
Index: F4INVOICE_PK
resc_io: 89716.00 resc_cpu: 761518341
ix_sel: 0.813255 ix_sel_with_filters: 0.813255
NL Join : Cost: 224349.85 Resp: 224349.85 Degree: 1
Cost_io: 224291.00 Cost_cpu: 1903799871
Resp_io: 224291.00 Resp_cpu: 1903799871
****** trying bitmap/domain indexes ******
****** finished trying bitmap/domain indexes ******
Best NL cost: 9379.92
resc: 9379.92 resc_io: 9368.50 resc_cpu: 369427533
resp: 9379.92 resp_io: 9368.50 resc_cpu: 369427533
Join Card: 6301302.146511 = = outer (25.332653) * inner
(248742.292433) * sel (1.000000)
Join Card - Rounded: 6301302 Computed: 6301302.15
Best:: JoinMethod: NestedLoop
Cost: 9379.92 Degree: 1 Resp: 9379.92 Card: 6301302.15
Bytes: 62
***************
Now joining: X4FEEITEM_INVOICE[XFI]#2
***************
NL Join
Outer table: Card: 6301302.15 Cost: 9379.92 Resp: 9379.92 Degree:
1 Bytes: 62
Access path analysis for X4FEEITEM_INVOICE
Inner table: X4FEEITEM_INVOICE Alias: XFI
Access Path: TableScan
NL Join: Cost: 43571000697.03 Resp: 43571000697.03 Degree: 1
Cost_io: 43342596237.50 Cost_cpu: 7389493697666618
Resp_io: 43342596237.50 Resp_cpu: 7389493697666618
kkofmx: index filter:"SD"."SERV_PROV_CODE"="XFI"."SERV_PROV_CODE"
kkofmx: index filter:"FINV"."SERV_PROV_CODE"="XFI"."SERV_PROV_CODE"
kkofmx: index filter:"SD"."SERV_PROV_CODE"="XFI"."SERV_PROV_CODE"
kkofmx: index filter:"FINV"."SERV_PROV_CODE"="XFI"."SERV_PROV_CODE"
kkofmx: index filter:"FINV"."INVOICE_NBR"="XFI"."INVOICE_NBR"
OPTIMIZER PERCENT INDEX CACHING = 90
Access Path: index (AllEqJoinGuess)
Index: X4FEEITEM_INVOICE_NBR_IX
resc_io: 1.00 resc_cpu: 12613
ix_sel: 0.000004 ix_sel_with_filters: 0.000004
***** Logdef predicate Adjustment ******
Final IO cst 0.00 , CPU cst 0.00
***** End Logdef Adjustment ******
***** Logdef predicate Adjustment ******
Final IO cst 0.00 , CPU cst 0.00
***** End Logdef Adjustment ******
NL Join : Cost: 639755.78 Resp: 639755.78 Degree: 1
Cost_io: 639498.70 Cost_cpu: 8317062188
Resp_io: 639498.70 Resp_cpu: 8317062188
OPTIMIZER PERCENT INDEX CACHING = 90
Access Path: index (RangeScan)
Index: X4FEEITEM_INVOICE_PK
resc_io: 2.00 resc_cpu: 15073
ix_sel: 0.000000 ix_sel_with_filters: 0.000000
***** Logdef predicate Adjustment ******
Final IO cst 0.00 , CPU cst 0.00
***** End Logdef Adjustment ******
***** Logdef predicate Adjustment ******
Final IO cst 0.00 , CPU cst 0.00
***** End Logdef Adjustment ******
***** Logdef predicate Adjustment ******
Final IO cst 0.00 , CPU cst 0.00
***** End Logdef Adjustment ******
NL Join : Cost: 1269933.89 Resp: 1269933.89 Degree: 1
Cost_io: 1269628.90 Cost_cpu: 9867304445
Resp_io: 1269628.90 Resp_cpu: 9867304445
****** trying bitmap/domain indexes ******
****** finished trying bitmap/domain indexes ******
Best NL cost: 639755.78
resc: 639755.78 resc_io: 639498.70 resc_cpu: 8317062188
resp: 639755.78 resp_io: 639498.70 resc_cpu: 8317062188
ColGroup cardinality sanity check: ndv for X4FEEITEM_INVOICE[XFI] =
1278471 SETDETAILS[SD] = 546
Join selectivity using 1 ColGroups: 0.003014 (sel1 = 0.000000, sel2 =
0.000000)
Join Card: 112700.054052 = = outer (6301302.146511) * inner
(1187146.275001) * sel (0.000000)
Join Card - Rounded: 112700 Computed: 112700.05
Outer table: F4INVOICE Alias: FINV
resc: 9379.92 card 6301302.15 bytes: 62 deg: 1 resp: 9379.92
Inner table: X4FEEITEM_INVOICE Alias: XFI
resc: 3039.37 card: 1187146.28 bytes: 131 deg: 1 resp: 3039.37
using dmeth: 2 #groups: 1
SORT ressource Sort statistics
Sort width: 238 Area size: 208896 Max Area size:
41943040
Degree: 1
Blocks to Sort: 60946 Row size: 79 Total Rows:
6301302
Initial runs: 12 Merge passes: 1 IO Cost / pass: 33014
Total IO sort cost: 93960 Total CPU sort cost: 7945775124
Total Temp space used: 913646000
SORT ressource Sort statistics
Sort width: 238 Area size: 208896 Max Area size:
41943040
Degree: 1
Blocks to Sort: 22528 Row size: 155 Total Rows:
1187146
Initial runs: 5 Merge passes: 1 IO Cost / pass: 12204
Total IO sort cost: 34732 Total CPU sort cost: 1666431753
Total Temp space used: 442065000
SM join: Resc: 141408.40 Resp: 141408.40 [multiMatchCost=0.00]
SM Join
SM cost: 141408.40
resc: 141408.40 resc_io: 141095.70 resc_cpu: 10116550418
resp: 141408.40 resp_io: 141095.70 resp_cpu: 10116550418
Outer table: F4INVOICE Alias: FINV
resc: 9379.92 card 6301302.15 bytes: 62 deg: 1 resp: 9379.92
Inner table: X4FEEITEM_INVOICE Alias: XFI
resc: 3039.37 card: 1187146.28 bytes: 131 deg: 1 resp: 3039.37
using dmeth: 2 #groups: 1
Cost per ptn: 30102.90 #ptns: 1
hash_area: 124 (max=10240) buildfrag: 56921 probefrag: 20723
ppasses: 1
Hash join: Resc: 42522.19 Resp: 42522.19 [multiMatchCost=0.00]
Outer table: X4FEEITEM_INVOICE Alias: XFI
resc: 3039.37 card 1187146.28 bytes: 131 deg: 1 resp: 3039.37
Inner table: F4INVOICE Alias: FINV
resc: 9379.92 card: 6301302.15 bytes: 62 deg: 1 resp: 9379.92
using dmeth: 2 #groups: 1
Cost per ptn: 30110.80 #ptns: 1
hash_area: 124 (max=10240) buildfrag: 20723 probefrag: 56921
ppasses: 1
Hash join: Resc: 42530.09 Resp: 42530.09 [multiMatchCost=0.00]
HA Join
HA cost: 42530.09 swapped
resc: 42530.09 resc_io: 42459.70 resc_cpu: 2277382928
resp: 42530.09 resp_io: 42459.70 resp_cpu: 2277382928
Best:: JoinMethod: Hash
Cost: 42530.09 Degree: 1 Resp: 42530.09 Card: 112700.05
Bytes: 193
***************
Now joining: F4FEEITEM[F4]#3
***************
NL Join
Outer table: Card: 112700.05 Cost: 42530.09 Resp: 42530.09
Degree: 1 Bytes: 193
Access path analysis for F4FEEITEM
Inner table: F4FEEITEM Alias: F4
Access Path: TableScan
NL Join: Cost: 1092141187.96 Resp: 1092141187.96 Degree: 1
Cost_io: 1086566725.70 Cost_cpu: 180348728036864
Resp_io: 1086566725.70 Resp_cpu: 180348728036864
kkofmx: index filter:"XFI"."SERV_PROV_CODE"="F4"."SERV_PROV_CODE"
OPTIMIZER PERCENT INDEX CACHING = 90
Access Path: index (UniqueScan)
Index: F4FEEITEM_PK
resc_io: 1.00 resc_cpu: 9201
ix_sel: 0.000001 ix_sel_with_filters: 0.000001
***** Logdef predicate Adjustment ******
Final IO cst 0.00 , CPU cst 0.00
***** End Logdef Adjustment ******
NL Join : Cost: 53803.30 Resp: 53803.30 Degree: 1
Cost_io: 53729.70 Cost_cpu: 2381083157
Resp_io: 53729.70 Resp_cpu: 2381083157
kkofmx: index filter:"XFI"."SERV_PROV_CODE"="F4"."SERV_PROV_CODE"
kkofmx: index filter:"XFI"."SERV_PROV_CODE"="F4"."SERV_PROV_CODE"
OPTIMIZER PERCENT INDEX CACHING = 90
Access Path: index (AllEqUnique)
Index: F4FEEITEM_PK
resc_io: 1.00 resc_cpu: 9201
ix_sel: 0.000000 ix_sel_with_filters: 0.000000
***** Logdef predicate Adjustment ******
Final IO cst 0.00 , CPU cst 0.00
***** End Logdef Adjustment ******
NL Join : Cost: 53803.30 Resp: 53803.30 Degree: 1
Cost_io: 53729.70 Cost_cpu: 2381083157
Resp_io: 53729.70 Resp_cpu: 2381083157
OPTIMIZER PERCENT INDEX CACHING = 90
Access Path: index (RangeScan)
Index: F4FEEITEM_POS_IX
resc_io: 34070.00 resc_cpu: 1757393371
ix_sel: 0.953821 ix_sel_with_filters: 0.953821
***** Logdef predicate Adjustment ******
Final IO cst 0.00 , CPU cst 0.00
***** End Logdef Adjustment ******
NL Join : Cost: 384623615.25 Resp: 384623615.25 Degree: 1
Cost_io: 384011359.70 Cost_cpu: 19808100671844
Resp_io: 384011359.70 Resp_cpu: 19808100671844
****** trying bitmap/domain indexes ******
****** finished trying bitmap/domain indexes ******
Best NL cost: 53803.30
resc: 53803.30 resc_io: 53729.70 resc_cpu: 2381083157
resp: 53803.30 resp_io: 53729.70 resc_cpu: 2381083157
ColGroup cardinality sanity check: ndv for X4FEEITEM_INVOICE[XFI] =
192 F4FEEITEM[F4] = 1291140
Join selectivity using 1 ColGroups: 0.000293 (sel1 = 0.000000, sel2 =
0.000000)
Join Card: 40640899.977307 = = outer (112700.054052) * inner
(1231517.000000) * sel (0.000293)
Join Card - Rounded: 40640900 Computed: 40640899.98
Outer table: X4FEEITEM_INVOICE Alias: XFI
resc: 42530.09 card 112700.05 bytes: 193 deg: 1 resp: 42530.09
Inner table: F4FEEITEM Alias: F4
resc: 3953.05 card: 1231517.00 bytes: 42 deg: 1 resp: 3953.05
using dmeth: 2 #groups: 1
SORT ressource Sort statistics
Sort width: 238 Area size: 208896 Max Area size:
41943040
Degree: 1
Blocks to Sort: 3077 Row size: 223 Total Rows:
112700
Initial runs: 2 Merge passes: 1 IO Cost / pass: 1668
Total IO sort cost: 4745 Total CPU sort cost: 193340637
Total Temp space used: 57713000
SORT ressource Sort statistics
Sort width: 238 Area size: 208896 Max Area size:
41943040
Degree: 1
Blocks to Sort: 8595 Row size: 57 Total Rows: 1231517
Initial runs: 2 Merge passes: 1 IO Cost / pass: 4658
Total IO sort cost: 13253 Total CPU sort cost: 1366595347
Total Temp space used: 148382000
SM join: Resc: 64651.17 Resp: 64651.17 [multiMatchCost=121.81]
SM Join
SM cost: 64651.17
resc: 64651.17 resc_io: 64405.20 resc_cpu: 7957845688
resp: 64651.17 resp_io: 64405.20 resp_cpu: 7957845688
Outer table: X4FEEITEM_INVOICE Alias: XFI
resc: 42530.09 card 112700.05 bytes: 193 deg: 1 resp: 42530.09
Inner table: F4FEEITEM Alias: F4
resc: 3953.05 card: 1231517.00 bytes: 42 deg: 1 resp: 3953.05
using dmeth: 2 #groups: 1
Cost per ptn: 4245.02 #ptns: 1
hash_area: 124 (max=10240) buildfrag: 2821 probefrag: 8118
ppasses: 1
Hash join: Resc: 50849.98 Resp: 50849.98 [multiMatchCost=121.81]
HA Join
HA cost: 50849.98
resc: 50849.98 resc_io: 50642.20 resc_cpu: 6722171156
resp: 50849.98 resp_io: 50642.20 resp_cpu: 6722171156
ORDER BY sort
SORT ressource Sort statistics
Sort width: 238 Area size: 208896 Max Area size:
41943040
Degree: 1
Blocks to Sort: 1338443 Row size: 269 Total Rows:
40640900
Initial runs: 261 Merge passes: 2 IO Cost / pass: 724992
Total IO sort cost: 2788427 Total CPU sort cost:
95755709776
Total Temp space used: 10739696000
Best:: JoinMethod: Hash
Cost: 2842236.72 Degree: 1 Resp: 2842236.72 Card:
40640899.98 Bytes: 235
***********************
Best so far: Table#: 0 cost: 1.0001 card: 25.3327 bytes: 950
*** 2009-11-04 12:27:17.068
Table#: 1 cost: 9379.9188 card: 6301302.1465 bytes:
390680724
Table#: 2 cost: 42530.0924 card: 112700.0541 bytes:
21751100
Table#: 3 cost: 2842236.7249 card: 40640899.9773
bytes: 9550611500
***********************
*********************************
Number of join permutations tried: 1
*********************************
Consider using bloom filter between SD[SETDETAILS] and FINV[F4INVOICE]
kkoBloomFilter: join ndv:0 reduction:1.000000 (limit:0.500000)
rejected because not a hash join
Consider using bloom filter between FINV[F4INVOICE] and XFI
[X4FEEITEM_INVOICE] ,with join inputs swapped
kkoBloomFilter: join (lcdn:6301302 rcdn:1187146 jcdn:112700 limit:
3740283685443)
Computing bloom ndv for creator:FINV[F4INVOICE] ccdn:6301302.1 and
user:XFI[X4FEEITEM_INVOICE] ucdn:1187146.3
kkopqComputeBloomNdv: predicate (bndv:5 ndv:5) and (bndv:5 ndv:5)
kkopqComputeBloomNdv: predicate (bndv:222104 ndv:200076) and (bndv:
248360 ndv:4)
kkopqComputeBloomNdv: pred cnt:4 ndv:6301302 reduction:5
kkoBloomFilter: join ndv:0 reduction:5.307941 (limit:0.500000)
rejected because distinct value ratio
Consider using bloom filter between XFI[X4FEEITEM_INVOICE] and F4
[F4FEEITEM]
kkoBloomFilter: join (lcdn:112700 rcdn:1231517 jcdn:40640900 limit:
69396016233)
Computing bloom ndv for creator:XFI[X4FEEITEM_INVOICE] ccdn:112700.1
and user:F4[F4FEEITEM] ucdn:1231517.0
kkopqComputeBloomNdv: predicate (bndv:5 ndv:5) and (bndv:11 ndv:11)
kkopqComputeBloomNdv: predicate (bndv:165 ndv:4) and (bndv:168 ndv:
168)
kkopqComputeBloomNdv: predicate (bndv:3 ndv:3) and (bndv:3 ndv:3)
kkopqComputeBloomNdv: predicate (bndv:27325 ndv:4) and (bndv:27463 ndv:
10)
kkopqComputeBloomNdv: predicate (bndv:353396 ndv:4) and (bndv:381953
ndv:4)
kkopqComputeBloomNdv: pred cnt:10 ndv:112700 reduction:0
kkoBloomFilter: join ndv:112700 reduction:0.091513 (limit:0.500000)
accepted invalidated
(newjo-save) [2 1 3 0 ]
SORT ressource Sort statistics
Sort width: 238 Area size: 208896 Max Area size:
41943040
Degree: 1
Blocks to Sort: 1338443 Row size: 269 Total Rows:
40640900
Initial runs: 261 Merge passes: 2 IO Cost / pass: 724992
Total IO sort cost: 2788427 Total CPU sort cost:
95755709776
Total Temp space used: 10739696000
Or-Expansion validity checks failed on query block SEL$1 (#0) because
NO_EXPAND hint
Transfer Optimizer annotations for query block SEL$1 (#0)
Final cost for query block SEL$1 (#0) - All Rows Plan:
Best join order: 1
Cost: 2842236.7249 Degree: 1 Card: 40640900.0000 Bytes:
9550611500
Resc: 2842236.7249 Resc_io: 2839069.2000 Resc_cpu: 102477880932
Resp: 2842236.7249 Resp_io: 2839069.2000 Resc_cpu: 102477880932
kkoqbc-subheap (delete addr=0x6ddabc, in-use=165664, alloc=174768)
kkoqbc-end:
:
call(in-use=90568, alloc=285820), compile(in-use=99312,
alloc=100828), execution(in-use=3640, alloc=4060)
kkoqbc: finish optimizing query block SEL$1 (#0)
First K Rows: K = 100.00, N = 40640900.00
First K Rows: Setup end
***********************
kkoqbc: optimizing query block SEL$1 (#0)
:
call(in-use=89196, alloc=285820), compile(in-use=99360,
alloc=100828), execution(in-use=3640, alloc=4060)
kkoqbc-subheap (create addr=0x6ddabc)
****************
QUERY BLOCK TEXT
****************
SELECT xfi.serv_prov_code,
xfi.b1_per_id1,
xfi.b1_per_id2,
xfi.b1_per_id3,
xfi.feeitem_seq_nbr,
xfi.invoice_nbr,
xfi.gf_fee_period,
xfi.gf_fee,
xfi.gf_des,
xfi.gf_unit,
xfi.gf_udes,
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (optimizer): qb_name=SEL$1 nbfros=4 flg=0
fro(0): flg=0 objn=73740 hint_alias="F4"@"SEL$1"
fro(1): flg=0 objn=73746 hint_alias="FINV"@"SEL$1"
fro(2): flg=0 objn=74784 hint_alias="SD"@"SEL$1"
fro(3): flg=0 objn=74848 hint_alias="XFI"@"SEL$1"
-----------------------------
SYSTEM STATISTICS INFORMATION
-----------------------------
Using NOWORKLOAD Stats
CPUSPEEDNW: 2696 millions instructions/sec (default is 100)
IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
IOSEEKTIM: 10 milliseconds (default is 10)
MBRC: -1 blocks (default is 8)
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: F4FEEITEM Alias: F4
#Rows: 1291140 #Blks: 35597 AvgRowLen: 185.00
Index Stats::
Index: F4FEEITEM_PK Col#: 1 2 3 4 5
LVLS: 2 #LB: 10410 #DK: 1291140 LB/K: 1.00 DB/K: 1.00 CLUF:
515902.00
Index: F4FEEITEM_POS_IX Col#: 1 38
LVLS: 2 #LB: 6304 #DK: 75 LB/K: 84.00 DB/K: 467.00 CLUF:
35079.00
***********************
Table Stats::
Table: F4INVOICE Alias: FINV
#Rows: 305860 #Blks: 3646 AvgRowLen: 72.00
Index Stats::
Index: F4INVOICE_BATCH_DATE_IX Col#: 1 8 12
LVLS: 2 #LB: 1307 #DK: 82901 LB/K: 1.00 DB/K: 1.00 CLUF:
4528.00
Index: F4INVOICE_BATCH_NBR_IX Col#: 1 11
LVLS: 2 #LB: 954 #DK: 8 LB/K: 119.00 DB/K: 456.00 CLUF:
3650.00
Index: F4INVOICE_DATE_IX Col#: 1 8
LVLS: 2 #LB: 1255 #DK: 81356 LB/K: 1.00 DB/K: 1.00 CLUF:
4451.00
Index: F4INVOICE_PK Col#: 1 2
LVLS: 2 #LB: 1294 #DK: 307685 LB/K: 1.00 DB/K: 1.00 CLUF:
109019.00
***********************
Table Stats::
Table: SETDETAILS Alias: SD
#Rows: 390896 #Blks: 3646 AvgRowLen: 74.00
Index Stats::
Index: SETDETAILS_ADDR_IX Col#: 1 14
LVLS: 2 #LB: 1806 #DK: 2 LB/K: 903.00 DB/K: 1819.00 CLUF:
3638.00
Index: SETDETAILS_IX Col#: 1 3 4 5 6
LVLS: 2 #LB: 4247 #DK: 390879 LB/K: 1.00 DB/K: 1.00 CLUF:
10423.00
Index: SETDETAILS_PAR_IX Col#: 12 13
LVLS: 0 #LB: 1 #DK: 6 LB/K: 1.00 DB/K: 1.00 CLUF: 3.00
Index: SETDETAILS_PID_IX Col#: 1 4 5 6
LVLS: 2 #LB: 2185 #DK: 180591 LB/K: 1.00 DB/K: 2.00 CLUF:
371867.00
Index: SETDETAILS_PK Col#: 1 2
LVLS: 2 #LB: 1820 #DK: 390896 LB/K: 1.00 DB/K: 1.00 CLUF:
354197.00
Index: SETDETAILS_SETID1_IX Col#: 1 3
LVLS: 2 #LB: 1652 #DK: 3665 LB/K: 1.00 DB/K: 2.00 CLUF:
7579.00
Index: SETDETAILS_SETID_IX Col#: 1 17
LVLS: 2 #LB: 1652 #DK: 3664 LB/K: 1.00 DB/K: 2.00 CLUF:
7585.00
Index: SETDETAILS_STAT_IX Col#: 1 15
LVLS: 2 #LB: 1806 #DK: 1 LB/K: 1806.00 DB/K: 3637.00 CLUF:
3637.00
***********************
Table Stats::
Table: X4FEEITEM_INVOICE Alias: XFI
#Rows: 1278471 #Blks: 25397 AvgRowLen: 131.00
Index Stats::
Index: X4FEEITEM_INVOICE_NBR_IX Col#: 1 6
LVLS: 2 #LB: 4328 #DK: 303821 LB/K: 1.00 DB/K: 1.00 CLUF:
27474.00
Index: X4FEEITEM_INVOICE_PK Col#: 1 2 3 4 5 6
LVLS: 2 #LB: 12530 #DK: 1278471 LB/K: 1.00 DB/K: 1.00 CLUF:
209916.00
Access path analysis for X4FEEITEM_INVOICE
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for X4FEEITEM_INVOICE[XFI]
ColGroup Usage:: PredCnt: 3 Matches Full: Partial:
Table: X4FEEITEM_INVOICE Alias: XFI
Card: Original: 1278471.000000 Rounded: 1187146 Computed:
1187146.28 Non Adjusted: 1187146.28
Access Path: TableScan
Cost: 6916.07 Resp: 6916.07 Degree: 0
Cost_io: 6880.00 Cost_cpu: 1166930829
Resp_io: 6880.00 Resp_cpu: 1166930829
Access Path: index (RangeScan)
Index: X4FEEITEM_INVOICE_NBR_IX
resc_io: 30352.00 resc_cpu: 1349160084
ix_sel: 0.954312 ix_sel_with_filters: 0.954312
Cost: 3039.37 Resp: 3039.37 Degree: 1
Access Path: index (RangeScan)
Index: X4FEEITEM_INVOICE_PK
resc_io: 212286.00 resc_cpu: 2644792149
ix_sel: 0.954312 ix_sel_with_filters: 0.954312
Cost: 21236.77 Resp: 21236.77 Degree: 1
****** trying bitmap/domain indexes ******
****** finished trying bitmap/domain indexes ******
Best:: AccessPath: IndexRange
Index: X4FEEITEM_INVOICE_NBR_IX
Cost: 3039.37 Degree: 1 Resp: 3039.37 Card: 1187146.28
Bytes: 0
Access path analysis for SETDETAILS
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for SETDETAILS[SD]
ColGroup Usage:: PredCnt: 2 Matches Full: Partial:
***** Virtual column Adjustment ******
Column name SYS_NC00017$
cost_cpu 150.00
cost_io
179769313486231570814527423731704356798070567525844996598917476803157260780028538760589558632766878171540458953514382464234321326889464182768467546703537516986049910576551282076245490090389328944075868508455133942304583236903222948165808559332123348274797826204144723168738177180919299881250404026184124858368.00
***** End virtual column Adjustment ******
Table: SETDETAILS Alias: SD
Card: Original: 390896.000000 Rounded: 25 Computed: 25.33 Non
Adjusted: 25.33
Access Path: TableScan
Cost: 993.91 Resp: 993.91 Degree: 0
Cost_io: 989.00 Cost_cpu: 158872177
Resp_io: 989.00 Resp_cpu: 158872177
Access Path: index (RangeScan)
Index: SETDETAILS_ADDR_IX
resc_io: 5446.00 resc_cpu: 273320887
ix_sel: 0.999999 ix_sel_with_filters: 0.999999
Cost: 545.44 Resp: 545.44 Degree: 1
Access Path: index (RangeScan)
Index: SETDETAILS_IX
resc_io: 14672.00 resc_cpu: 339023293
ix_sel: 0.999999 ix_sel_with_filters: 0.999999
Cost: 1468.25 Resp: 1468.25 Degree: 1
Access Path: index (RangeScan)
Index: SETDETAILS_PID_IX
resc_io: 374054.00 resc_cpu: 2898340643
ix_sel: 0.999999 ix_sel_with_filters: 0.999999
Cost: 37414.36 Resp: 37414.36 Degree: 1
Access Path: index (RangeScan)
Index: SETDETAILS_PK
resc_io: 356019.00 resc_cpu: 2769905472
ix_sel: 0.999999 ix_sel_with_filters: 0.999999
Cost: 35610.46 Resp: 35610.46 Degree: 1
Access Path: index (RangeScan)
Index: SETDETAILS_SETID1_IX
resc_io: 9233.00 resc_cpu: 300289781
ix_sel: 0.999999 ix_sel_with_filters: 0.999999
Cost: 924.23 Resp: 924.23 Degree: 1
ColGroup Usage:: PredCnt: 2 Matches Full: Partial:
ColGroup Usage:: PredCnt: 2 Matches Full: Partial:
***** Virtual column Adjustment ******
Column name SYS_NC00017$
cost_cpu 150.00
cost_io
179769313486231570814527423731704356798070567525844996598917476803157260780028538760589558632766878171540458953514382464234321326889464182768467546703537516986049910576551282076245490090389328944075868508455133942304583236903222948165808559332123348274797826204144723168738177180919299881250404026184124858368.00
***** End virtual column Adjustment ******
Access Path: index (AllEqRange)
Index: SETDETAILS_SETID_IX
resc_io: 4.00 resc_cpu: 40186
ix_sel: 0.000065 ix_sel_with_filters: 0.000065
Cost: 1.00 Resp: 1.00 Degree: 1
Access Path: index (RangeScan)
Index: SETDETAILS_STAT_IX
resc_io: 5445.00 resc_cpu: 273313766
ix_sel: 0.999999 ix_sel_with_filters: 0.999999
Cost: 545.34 Resp: 545.34 Degree: 1
****** trying bitmap/domain indexes ******
****** finished trying bitmap/domain indexes ******
Best:: AccessPath: IndexRange
Index: SETDETAILS_SETID_IX
Cost: 1.00 Degree: 1 Resp: 1.00 Card: 25.33 Bytes: 0
Access path analysis for F4INVOICE
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for F4INVOICE[FINV]
Table: F4INVOICE Alias: FINV
Card: Original: 305860.000000 Rounded: 248742 Computed:
248742.29 Non Adjusted: 248742.29
Access Path: TableScan
Cost: 992.77 Resp: 992.77 Degree: 0
Cost_io: 989.00 Cost_cpu: 121960650
Resp_io: 989.00 Resp_cpu: 121960650
Access Path: index (RangeScan)
Index: F4INVOICE_BATCH_DATE_IX
resc_io: 4748.00 resc_cpu: 156037217
ix_sel: 0.813255 ix_sel_with_filters: 0.813255
Cost: 475.28 Resp: 475.28 Degree: 1
Access Path: index (RangeScan)
Index: F4INVOICE_BATCH_NBR_IX
resc_io: 3747.00 resc_cpu: 147769406
ix_sel: 0.813255 ix_sel_with_filters: 0.813255
Cost: 375.16 Resp: 375.16 Degree: 1
Access Path: index (RangeScan)
Index: F4INVOICE_DATE_IX
resc_io: 4643.00 resc_cpu: 154714696
ix_sel: 0.813255 ix_sel_with_filters: 0.813255
Cost: 464.78 Resp: 464.78 Degree: 1
Access Path: index (RangeScan)
Index: F4INVOICE_PK
resc_io: 89716.00 resc_cpu: 761518341
ix_sel: 0.813255 ix_sel_with_filters: 0.813255
Cost: 8973.95 Resp: 8973.95 Degree: 1
****** trying bitmap/domain indexes ******
****** finished trying bitmap/domain indexes ******
******** Begin index join costing ********
****** trying bitmap/domain indexes ******
Access Path: index (IndexOnly)
Index: F4INVOICE_BATCH_DATE_IX
resc_io: 1065.00 resc_cpu: 57471934
ix_sel: 0.813255 ix_sel_with_filters: 0.813255
Cost: 106.68 Resp: 106.68 Degree: 0
SORT ressource Sort statistics
Sort width: 238 Area size: 208896 Max Area size:
41943040
Degree: 1
Blocks to Sort: 640 Row size: 21 Total Rows: 248742
Initial runs: 2 Merge passes: 1 IO Cost / pass: 348
Total IO sort cost: 988 Total CPU sort cost: 248990170
Total Temp space used: 4006000
Bitmap nodes:
Used F4INVOICE_BATCH_DATE_IX
Cost = 1103.505539, sel = 0.813255
****** finished trying bitmap/domain indexes ******
Access Path: index (FullScan)
Index: F4INVOICE_PK
resc_io: 1296.00 resc_cpu: 70766386
ix_sel: 1.000000 ix_sel_with_filters: 1.000000
Cost: 129.82 Resp: 129.82 Degree: 0
******** Cost index join ********
Index join: Joining index F4INVOICE_BATCH_DATE_IX
Index join: Joining index F4INVOICE_PK
Ix HA Join
Outer table: F4INVOICE Alias: FINV
resc: 106.68 card 248742.29 bytes: 29 deg: 1 resp: 106.68
Inner table: <unnamed> Alias:
resc: 129.82 card: 305860.00 bytes: 26 deg: 1 resp: 129.82
using dmeth: 2 #groups: 1
Cost per ptn: 1035.88 #ptns: 1
hash_area: 124 (max=10240) buildfrag: 1245 probefrag: 1419
ppasses: 1
Hash join: Resc: 1272.38 Resp: 1272.38 [multiMatchCost=0.00]
******** Index join cost ********
Cost: 1272.38
******** End index join costing ********
Best:: AccessPath: IndexRange
Index: F4INVOICE_BATCH_NBR_IX
Cost: 375.16 Degree: 1 Resp: 375.16 Card: 248742.29
Bytes: 0
Access path analysis for F4FEEITEM
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for F4FEEITEM[F4]
Table: F4FEEITEM Alias: F4
Card: Original: 1291140.000000 Rounded: 1231517 Computed:
1231517.00 Non Adjusted: 1231517.00
Access Path: TableScan
Cost: 9692.31 Resp: 9692.31 Degree: 0
Cost_io: 9643.00 Cost_cpu: 1595464860
Resp_io: 9643.00 Resp_cpu: 1595464860
Access Path: index (RangeScan)
Index: F4FEEITEM_PK
resc_io: 502011.00 resc_cpu: 5089807126
ix_sel: 0.953821 ix_sel_with_filters: 0.953821
Cost: 50216.83 Resp: 50216.83 Degree: 1
Access Path: index (RangeScan)
Index: F4FEEITEM_POS_IX
resc_io: 39475.00 resc_cpu: 1795884754
ix_sel: 0.953821 ix_sel_with_filters: 0.953821
Cost: 3953.05 Resp: 3953.05 Degree: 1
****** trying bitmap/domain indexes ******
****** finished trying bitmap/domain indexes ******
Best:: AccessPath: IndexRange
Index: F4FEEITEM_POS_IX
Cost: 3953.05 Degree: 1 Resp: 3953.05 Card: 1231517.00
Bytes: 0
Join ColGroups for X4FEEITEM_INVOICE[XFI] and SETDETAILS[SD] : Using
cdn sanity check
Join ColGroups for X4FEEITEM_INVOICE[XFI] and F4FEEITEM[F4] : Using
cdn sanity check
***************************************
OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***************************************
Considering cardinality-based initial join order.
Permutations for Starting Table :0
Join order[1]: SETDETAILS[SD]#0 F4INVOICE[FINV]#1 X4FEEITEM_INVOICE
[XFI]#2 F4FEEITEM[F4]#3
***************
Now joining: F4INVOICE[FINV]#1
***************
NL Join
Outer table: Card: 25.33 Cost: 1.00 Resp: 1.00 Degree: 1 Bytes:
38
Access path analysis for F4INVOICE
Inner table: F4INVOICE Alias: FINV
Access Path: TableScan
NL Join: Cost: 24783.24 Resp: 24783.24 Degree: 1
Cost_io: 24689.00 Cost_cpu: 3049020275
Resp_io: 24689.00 Resp_cpu: 3049020275
Access Path: index (RangeScan)
Index: F4INVOICE_BATCH_DATE_IX
resc_io: 4748.00 resc_cpu: 156037217
ix_sel: 0.813255 ix_sel_with_filters: 0.813255
NL Join : Cost: 11883.06 Resp: 11883.06 Degree: 1
Cost_io: 11871.00 Cost_cpu: 390097061
Resp_io: 11871.00 Resp_cpu: 390097061
Access Path: index (RangeScan)
Index: F4INVOICE_BATCH_NBR_IX
resc_io: 3747.00 resc_cpu: 147769406
ix_sel: 0.813255 ix_sel_with_filters: 0.813255
NL Join : Cost: 9379.92 Resp: 9379.92 Degree: 1
Cost_io: 9368.50 Cost_cpu: 369427533
Resp_io: 9368.50 Resp_cpu: 369427533
Access Path: index (RangeScan)
Index: F4INVOICE_DATE_IX
resc_io: 4643.00 resc_cpu: 154714696
ix_sel: 0.813255 ix_sel_with_filters: 0.813255
NL Join : Cost: 11620.46 Resp: 11620.46 Degree: 1
Cost_io: 11608.50 Cost_cpu: 386790758
Resp_io: 11608.50 Resp_cpu: 386790758
Access Path: index (RangeScan)
Index: F4INVOICE_PK
resc_io: 89716.00 resc_cpu: 761518341
ix_sel: 0.813255 ix_sel_with_filters: 0.813255
NL Join : Cost: 224349.85 Resp: 224349.85 Degree: 1
Cost_io: 224291.00 Cost_cpu: 1903799871
Resp_io: 224291.00 Resp_cpu: 1903799871
****** trying bitmap/domain indexes ******
****** finished trying bitmap/domain indexes ******
Best NL cost: 9379.92
resc: 9379.92 resc_io: 9368.50 resc_cpu: 369427533
resp: 9379.92 resp_io: 9368.50 resc_cpu: 369427533
Join Card: 6301302.146511 = = outer (25.332653) * inner
(248742.292433) * sel (1.000000)
Join Card - Rounded: 6301302 Computed: 6301302.15
Best:: JoinMethod: NestedLoop
Cost: 9379.92 Degree: 1 Resp: 9379.92 Card: 6301302.15
Bytes: 62
***************
Now joining: X4FEEITEM_INVOICE[XFI]#2
***************
NL Join
Outer table: Card: 6301302.15 Cost: 9379.92 Resp: 9379.92 Degree:
1 Bytes: 62
Access path analysis for X4FEEITEM_INVOICE
Inner table: X4FEEITEM_INVOICE Alias: XFI
Access Path: TableScan
NL Join: Cost: 43571000697.03 Resp: 43571000697.03 Degree: 1
Cost_io: 43342596237.50 Cost_cpu: 7389493697666618
Resp_io: 43342596237.50 Resp_cpu: 7389493697666618
kkofmx: index filter:"SD"."SERV_PROV_CODE"="XFI"."SERV_PROV_CODE"
kkofmx: index filter:"FINV"."SERV_PROV_CODE"="XFI"."SERV_PROV_CODE"
kkofmx: index filter:"SD"."SERV_PROV_CODE"="XFI"."SERV_PROV_CODE"
kkofmx: index filter:"FINV"."SERV_PROV_CODE"="XFI"."SERV_PROV_CODE"
kkofmx: index filter:"FINV"."INVOICE_NBR"="XFI"."INVOICE_NBR"
OPTIMIZER PERCENT INDEX CACHING = 90
Access Path: index (AllEqJoinGuess)
Index: X4FEEITEM_INVOICE_NBR_IX
resc_io: 1.00 resc_cpu: 12613
ix_sel: 0.000004 ix_sel_with_filters: 0.000004
***** Logdef predicate Adjustment ******
Final IO cst 0.00 , CPU cst 0.00
***** End Logdef Adjustment ******
***** Logdef predicate Adjustment ******
Final IO cst 0.00 , CPU cst 0.00
***** End Logdef Adjustment ******
NL Join : Cost: 639755.78 Resp: 639755.78 Degree: 1
Cost_io: 639498.70 Cost_cpu: 8317062188
Resp_io: 639498.70 Resp_cpu: 8317062188
OPTIMIZER PERCENT INDEX CACHING = 90
Access Path: index (RangeScan)
Index: X4FEEITEM_INVOICE_PK
resc_io: 2.00 resc_cpu: 15073
ix_sel: 0.000000 ix_sel_with_filters: 0.000000
***** Logdef predicate Adjustment ******
Final IO cst 0.00 , CPU cst 0.00
***** End Logdef Adjustment ******
***** Logdef predicate Adjustment ******
Final IO cst 0.00 , CPU cst 0.00
***** End Logdef Adjustment ******
***** Logdef predicate Adjustment ******
Final IO cst 0.00 , CPU cst 0.00
***** End Logdef Adjustment ******
NL Join : Cost: 1269933.89 Resp: 1269933.89 Degree: 1
Cost_io: 1269628.90 Cost_cpu: 9867304445
Resp_io: 1269628.90 Resp_cpu: 9867304445
****** trying bitmap/domain indexes ******
****** finished trying bitmap/domain indexes ******
Best NL cost: 639755.78
resc: 639755.78 resc_io: 639498.70 resc_cpu: 8317062188
resp: 639755.78 resp_io: 639498.70 resc_cpu: 8317062188
ColGroup cardinality sanity check: ndv for X4FEEITEM_INVOICE[XFI] =
1278471 SETDETAILS[SD] = 546
Join selectivity using 1 ColGroups: 0.003014 (sel1 = 0.000000, sel2 =
0.000000)
Join Card: 112700.054052 = = outer (6301302.146511) * inner
(1187146.275001) * sel (0.000000)
Join Card - Rounded: 112700 Computed: 112700.05
Outer table: F4INVOICE Alias: FINV
resc: 9379.92 card 6301302.15 bytes: 62 deg: 1 resp: 9379.92
Inner table: X4FEEITEM_INVOICE Alias: XFI
resc: 3039.37 card: 1187146.28 bytes: 131 deg: 1 resp: 3039.37
using dmeth: 2 #groups: 1
SORT ressource Sort statistics
Sort width: 238 Area size: 208896 Max Area size:
41943040
Degree: 1
Blocks to Sort: 60946 Row size: 79 Total Rows:
6301302
Initial runs: 12 Merge passes: 1 IO Cost / pass: 33014
Total IO sort cost: 93960 Total CPU sort cost: 7945775124
Total Temp space used: 913646000
SORT ressource Sort statistics
Sort width: 238 Area size: 208896 Max Area size:
41943040
Degree: 1
Blocks to Sort: 22528 Row size: 155 Total Rows:
1187146
Initial runs: 5 Merge passes: 1 IO Cost / pass: 12204
Total IO sort cost: 34732 Total CPU sort cost: 1666431753
Total Temp space used: 442065000
SM join: Resc: 141408.40 Resp: 141408.40 [multiMatchCost=0.00]
SM Join
SM cost: 141408.40
resc: 141408.40 resc_io: 141095.70 resc_cpu: 10116550418
resp: 141408.40 resp_io: 141095.70 resp_cpu: 10116550418
Outer table: F4INVOICE Alias: FINV
resc: 9379.92 card 6301302.15 bytes: 62 deg: 1 resp: 9379.92
Inner table: X4FEEITEM_INVOICE Alias: XFI
resc: 3039.37 card: 1187146.28 bytes: 131 deg: 1 resp: 3039.37
using dmeth: 2 #groups: 1
Cost per ptn: 30102.90 #ptns: 1
hash_area: 124 (max=10240) buildfrag: 56921 probefrag: 20723
ppasses: 1
Hash join: Resc: 42522.19 Resp: 42522.19 [multiMatchCost=0.00]
Outer table: X4FEEITEM_INVOICE Alias: XFI
resc: 3039.37 card 1187146.28 bytes: 131 deg: 1 resp: 3039.37
Inner table: F4INVOICE Alias: FINV
resc: 9379.92 card: 6301302.15 bytes: 62 deg: 1 resp: 9379.92
using dmeth: 2 #groups: 1
Cost per ptn: 30110.80 #ptns: 1
hash_area: 124 (max=10240) buildfrag: 20723 probefrag: 56921
ppasses: 1
Hash join: Resc: 42530.09 Resp: 42530.09 [multiMatchCost=0.00]
HA Join
HA cost: 42530.09 swapped
resc: 42530.09 resc_io: 42459.70 resc_cpu: 2277382928
resp: 42530.09 resp_io: 42459.70 resp_cpu: 2277382928
Best:: JoinMethod: Hash
Cost: 42530.09 Degree: 1 Resp: 42530.09 Card: 112700.05
Bytes: 193
***************
Now joining: F4FEEITEM[F4]#3
***************
NL Join
Outer table: Card: 112700.05 Cost: 42530.09 Resp: 42530.09
Degree: 1 Bytes: 193
Access path analysis for F4FEEITEM
Inner table: F4FEEITEM Alias: F4
Access Path: TableScan
NL Join: Cost: 1092141187.96 Resp: 1092141187.96 Degree: 1
Cost_io: 1086566725.70 Cost_cpu: 180348728036864
Resp_io: 1086566725.70 Resp_cpu: 180348728036864
kkofmx: index filter:"XFI"."SERV_PROV_CODE"="F4"."SERV_PROV_CODE"
OPTIMIZER PERCENT INDEX CACHING = 90
Access Path: index (UniqueScan)
Index: F4FEEITEM_PK
resc_io: 1.00 resc_cpu: 9201
ix_sel: 0.000001 ix_sel_with_filters: 0.000001
***** Logdef predicate Adjustment ******
Final IO cst 0.00 , CPU cst 0.00
***** End Logdef Adjustment ******
NL Join : Cost: 53803.30 Resp: 53803.30 Degree: 1
Cost_io: 53729.70 Cost_cpu: 2381083157
Resp_io: 53729.70 Resp_cpu: 2381083157
kkofmx: index filter:"XFI"."SERV_PROV_CODE"="F4"."SERV_PROV_CODE"
kkofmx: index filter:"XFI"."SERV_PROV_CODE"="F4"."SERV_PROV_CODE"
OPTIMIZER PERCENT INDEX CACHING = 90
Access Path: index (AllEqUnique)
Index: F4FEEITEM_PK
resc_io: 1.00 resc_cpu: 9201
ix_sel: 0.000000 ix_sel_with_filters: 0.000000
***** Logdef predicate Adjustment ******
Final IO cst 0.00 , CPU cst 0.00
***** End Logdef Adjustment ******
NL Join : Cost: 53803.30 Resp: 53803.30 Degree: 1
Cost_io: 53729.70 Cost_cpu: 2381083157
Resp_io: 53729.70 Resp_cpu: 2381083157
OPTIMIZER PERCENT INDEX CACHING = 90
Access Path: index (RangeScan)
Index: F4FEEITEM_POS_IX
resc_io: 34070.00 resc_cpu: 1757393371
ix_sel: 0.953821 ix_sel_with_filters: 0.953821
***** Logdef predicate Adjustment ******
Final IO cst 0.00 , CPU cst 0.00
***** End Logdef Adjustment ******
NL Join : Cost: 384623615.25 Resp: 384623615.25 Degree: 1
Cost_io: 384011359.70 Cost_cpu: 19808100671844
Resp_io: 384011359.70 Resp_cpu: 19808100671844
****** trying bitmap/domain indexes ******
****** finished trying bitmap/domain indexes ******
Best NL cost: 53803.30
resc: 53803.30 resc_io: 53729.70 resc_cpu: 2381083157
resp: 53803.30 resp_io: 53729.70 resc_cpu: 2381083157
ColGroup cardinality sanity check: ndv for X4FEEITEM_INVOICE[XFI] =
192 F4FEEITEM[F4] = 1291140
Join selectivity using 1 ColGroups: 0.000293 (sel1 = 0.000000, sel2 =
0.000000)
Join Card: 40640899.977307 = = outer (112700.054052) * inner
(1231517.000000) * sel (0.000293)
Join Card - Rounded: 40640900 Computed: 40640899.98
Outer table: X4FEEITEM_INVOICE Alias: XFI
resc: 42530.09 card 112700.05 bytes: 193 deg: 1 resp: 42530.09
Inner table: F4FEEITEM Alias: F4
resc: 3953.05 card: 1231517.00 bytes: 42 deg: 1 resp: 3953.05
using dmeth: 2 #groups: 1
SORT ressource Sort statistics
Sort width: 238 Area size: 208896 Max Area size:
41943040
Degree: 1
Blocks to Sort: 3077 Row size: 223 Total Rows:
112700
Initial runs: 2 Merge passes: 1 IO Cost / pass: 1668
Total IO sort cost: 4745 Total CPU sort cost: 193340637
Total Temp space used: 57713000
SORT ressource Sort statistics
Sort width: 238 Area size: 208896 Max Area size:
41943040
Degree: 1
Blocks to Sort: 8595 Row size: 57 Total Rows: 1231517
Initial runs: 2 Merge passes: 1 IO Cost / pass: 4658
Total IO sort cost: 13253 Total CPU sort cost: 1366595347
Total Temp space used: 148382000
SM join: Resc: 64651.17 Resp: 64651.17 [multiMatchCost=121.81]
SM Join
SM cost: 64651.17
resc: 64651.17 resc_io: 64405.20 resc_cpu: 7957845688
resp: 64651.17 resp_io: 64405.20 resp_cpu: 7957845688
Outer table: X4FEEITEM_INVOICE Alias: XFI
resc: 42530.09 card 112700.05 bytes: 193 deg: 1 resp: 42530.09
Inner table: F4FEEITEM Alias: F4
resc: 3953.05 card: 1231517.00 bytes: 42 deg: 1 resp: 3953.05
using dmeth: 2 #groups: 1
Cost per ptn: 4245.02 #ptns: 1
hash_area: 124 (max=10240) buildfrag: 2821 probefrag: 8118
ppasses: 1
Hash join: Resc: 50849.98 Resp: 50849.98 [multiMatchCost=121.81]
HA Join
HA cost: 50849.98
resc: 50849.98 resc_io: 50642.20 resc_cpu: 6722171156
resp: 50849.98 resp_io: 50642.20 resp_cpu: 6722171156
ORDER BY sort
SORT ressource Sort statistics
Sort width: 238 Area size: 208896 Max Area size:
41943040
Degree: 1
Blocks to Sort: 1338443 Row size: 269 Total Rows:
40640900
Initial runs: 261 Merge passes: 2 IO Cost / pass: 724992
Total IO sort cost: 2788427 Total CPU sort cost:
95755709776
Total Temp space used: 10739696000
Best:: JoinMethod: Hash
Cost: 2842236.72 Degree: 1 Resp: 2842236.72 Card:
40640899.98 Bytes: 235
***********************
Best so far: Table#: 0 cost: 1.0001 card: 25.3327 bytes: 950
Table#: 1 cost: 9379.9188 card: 6301302.1465 bytes:
390680724
Table#: 2 cost: 42530.0924 card: 112700.0541 bytes:
21751100
Table#: 3 cost: 2842236.7249 card: 40640899.9773
bytes: 9550611500
***********************
First K Rows: K = 100.00, N = 40640900.00
First K Rows: old pf = -1.0000000, new pf = 0.0394747
Access path analysis for SETDETAILS
***************************************
SINGLE TABLE ACCESS PATH (First K Rows)
Single Table Cardinality Estimation for SETDETAILS[SD]
ColGroup Usage:: PredCnt: 2 Matches Full: Partial:
***** Virtual column Adjustment ******
Column name SYS_NC00017$
cost_cpu 150.00
cost_io
179769313486231570814527423731704356798070567525844996598917476803157260780028538760589558632766878171540458953514382464234321326889464182768467546703537516986049910576551282076245490090389328944075868508455133942304583236903222948165808559332123348274797826204144723168738177180919299881250404026184124858368.00
***** End virtual column Adjustment ******
Table: SETDETAILS Alias: SD
Card: Original: 15431.000000 Rounded: 2 Computed: 1.56 Non
Adjusted: 1.56
Access Path: TableScan
Cost: 40.19 Resp: 40.19 Degree: 0
Cost_io: 40.00 Cost_cpu: 6272226
Resp_io: 40.00 Resp_cpu: 6272226
Access Path: index (RangeScan)
Index: SETDETAILS_ADDR_IX
resc_io: 218.00 resc_cpu: 10811071
ix_sel: 0.999999 ix_sel_with_filters: 0.999999
Cost: 21.83 Resp: 21.83 Degree: 1
Access Path: index (RangeScan)
Index: SETDETAILS_IX
resc_io: 582.00 resc_cpu: 13403275
ix_sel: 0.999999 ix_sel_with_filters: 0.999999
Cost: 58.24 Resp: 58.24 Degree: 1
Access Path: index (RangeScan)
Index: SETDETAILS_PID_IX
resc_io: 14769.00 resc_cpu: 114435144
ix_sel: 0.999999 ix_sel_with_filters: 0.999999
Cost: 1477.25 Resp: 1477.25 Degree: 1
Access Path: index (RangeScan)
Index: SETDETAILS_PK
resc_io: 14056.00 resc_cpu: 109357558
ix_sel: 0.999999 ix_sel_with_filters: 0.999999
Cost: 1405.94 Resp: 1405.94 Degree: 1
Access Path: index (RangeScan)
Index: SETDETAILS_SETID1_IX
resc_io: 368.00 resc_cpu: 11879287
ix_sel: 0.999999 ix_sel_with_filters: 0.999999
Cost: 36.84 Resp: 36.84 Degree: 1
ColGroup Usage:: PredCnt: 2 Matches Full: Partial:
ColGroup Usage:: PredCnt: 2 Matches Full: Partial:
***** Virtual column Adjustment ******
Column name SYS_NC00017$
cost_cpu 150.00
cost_io
179769313486231570814527423731704356798070567525844996598917476803157260780028538760589558632766878171540458953514382464234321326889464182768467546703537516986049910576551282076245490090389328944075868508455133942304583236903222948165808559332123348274797826204144723168738177180919299881250404026184124858368.00
***** End virtual column Adjustment ******
Access Path: index (AllEqRange)
Index: SETDETAILS_SETID_IX
resc_io: 4.00 resc_cpu: 29386
ix_sel: 0.000101 ix_sel_with_filters: 0.000101
Cost: 1.00 Resp: 1.00 Degree: 1
Access Path: index (RangeScan)
Index: SETDETAILS_STAT_IX
resc_io: 218.00 resc_cpu: 10811071
ix_sel: 0.999999 ix_sel_with_filters: 0.999999
Cost: 21.83 Resp: 21.83 Degree: 1
****** trying bitmap/domain indexes ******
****** finished trying bitmap/domain indexes ******
Best:: AccessPath: IndexRange
Index: SETDETAILS_SETID_IX
Cost: 1.00 Degree: 1 Resp: 1.00 Card: 1.56 Bytes: 38
First K Rows: old pf = -1.0000000, new pf = 0.0002251
Access path analysis for F4INVOICE
***************************************
SINGLE TABLE ACCESS PATH (First K Rows)
Single Table Cardinality Estimation for F4INVOICE[FINV]
Table: F4INVOICE Alias: FINV
Card: Original: 69.000000 Rounded: 56 Computed: 56.11 Non
Adjusted: 56.11
Access Path: TableScan
Cost: 2.00 Resp: 2.00 Degree: 0
Cost_io: 2.00 Cost_cpu: 28761
Resp_io: 2.00 Resp_cpu: 28761
Access Path: index (RangeScan)
Index: F4INVOICE_BATCH_DATE_IX
resc_io: 5.00 resc_cpu: 63537
ix_sel: 0.813255 ix_sel_with_filters: 0.813255
Cost: 1.00 Resp: 1.00 Degree: 1
Access Path: index (RangeScan)
Index: F4INVOICE_BATCH_NBR_IX
resc_io: 4.00 resc_cpu: 56416
ix_sel: 0.813255 ix_sel_with_filters: 0.813255
Cost: 1.00 Resp: 1.00 Degree: 1
Access Path: index (RangeScan)
Index: F4INVOICE_DATE_IX
resc_io: 5.00 resc_cpu: 63537
ix_sel: 0.813255 ix_sel_with_filters: 0.813255
Cost: 1.00 Resp: 1.00 Degree: 1
Access Path: index (RangeScan)
Index: F4INVOICE_PK
resc_io: 24.00 resc_cpu: 198845
ix_sel: 0.813255 ix_sel_with_filters: 0.813255
Cost: 2.40 Resp: 2.40 Degree: 1
****** trying bitmap/domain indexes ******
****** finished trying bitmap/domain indexes ******
Best:: AccessPath: IndexRange
Index: F4INVOICE_BATCH_NBR_IX
Cost: 1.00 Degree: 1 Resp: 1.00 Card: 56.11 Bytes: 24
First K Rows: old pf = -1.0000000, new pf = 1.0000000
Access path analysis for X4FEEITEM_INVOICE
***************************************
SINGLE TABLE ACCESS PATH (First K Rows)
Single Table Cardinality Estimation for X4FEEITEM_INVOICE[XFI]
ColGroup Usage:: PredCnt: 3 Matches Full: Partial:
Table: X4FEEITEM_INVOICE Alias: XFI
Card: Original: 1278471.000000 Rounded: 1187146 Computed:
1187146.28 Non Adjusted: 1187146.28
Access Path: TableScan
Cost: 6916.07 Resp: 6916.07 Degree: 0
Cost_io: 6880.00 Cost_cpu: 1166930829
Resp_io: 6880.00 Resp_cpu: 1166930829
Access Path: index (RangeScan)
Index: X4FEEITEM_INVOICE_NBR_IX
resc_io: 30352.00 resc_cpu: 1349160084
ix_sel: 0.954312 ix_sel_with_filters: 0.954312
Cost: 3039.37 Resp: 3039.37 Degree: 1
Access Path: index (RangeScan)
Index: X4FEEITEM_INVOICE_PK
resc_io: 212286.00 resc_cpu: 2644792149
ix_sel: 0.954312 ix_sel_with_filters: 0.954312
Cost: 21236.77 Resp: 21236.77 Degree: 1
****** trying bitmap/domain indexes ******
****** finished trying bitmap/domain indexes ******
Best:: AccessPath: IndexRange
Index: X4FEEITEM_INVOICE_NBR_IX
Cost: 3039.37 Degree: 1 Resp: 3039.37 Card: 1187146.28
Bytes: 131
First K Rows: old pf = -1.0000000, new pf = 0.2773070
Access path analysis for F4FEEITEM
***************************************
SINGLE TABLE ACCESS PATH (First K Rows)
Single Table Cardinality Estimation for F4FEEITEM[F4]
Table: F4FEEITEM Alias: F4
Card: Original: 358043.000000 Rounded: 341509 Computed:
341509.09 Non Adjusted: 341509.09
Access Path: TableScan
Cost: 2688.68 Resp: 2688.68 Degree: 0
Cost_io: 2675.00 Cost_cpu: 442439376
Resp_io: 2675.00 Resp_cpu: 442439376
Access Path: index (RangeScan)
Index: F4FEEITEM_PK
resc_io: 139214.00 resc_cpu: 1411461448
ix_sel: 0.953821 ix_sel_with_filters: 0.953821
Cost: 13925.76 Resp: 13925.76 Degree: 1
Access Path: index (RangeScan)
Index: F4FEEITEM_POS_IX
resc_io: 10950.00 resc_cpu: 498037068
ix_sel: 0.953821 ix_sel_with_filters: 0.953821
Cost: 1096.54 Resp: 1096.54 Degree: 1
****** trying bitmap/domain indexes ******
****** finished trying bitmap/domain indexes ******
Best:: AccessPath: IndexRange
Index: F4FEEITEM_POS_IX
Cost: 1096.54 Degree: 1 Resp: 1096.54 Card: 341509.09
Bytes: 42
First K Rows: unchanged join prefix len = 1
Join order[1]: SETDETAILS[SD]#0 F4INVOICE[FINV]#1 X4FEEITEM_INVOICE
[XFI]#2 F4FEEITEM[F4]#3
***************
Now joining: F4INVOICE[FINV]#1
***************
NL Join
Outer table: Card: 1.56 Cost: 1.00 Resp: 1.00 Degree: 1 Bytes:
38
Access path analysis for F4INVOICE
Inner table: F4INVOICE Alias: FINV
Access Path: TableScan
NL Join: Cost: 3.00 Resp: 3.00 Degree: 1
Cost_io: 3.00 Cost_cpu: 60461
Resp_io: 3.00 Resp_cpu: 60461
Access Path: index (RangeScan)
Index: F4INVOICE_BATCH_DATE_IX
resc_io: 5.00 resc_cpu: 63537
ix_sel: 0.813255 ix_sel_with_filters: 0.813255
NL Join : Cost: 2.00 Resp: 2.00 Degree: 1
Cost_io: 2.00 Cost_cpu: 15646
Resp_io: 2.00 Resp_cpu: 15646
Access Path: index (RangeScan)
Index: F4INVOICE_BATCH_NBR_IX
resc_io: 4.00 resc_cpu: 56416
ix_sel: 0.813255 ix_sel_with_filters: 0.813255
NL Join : Cost: 2.00 Resp: 2.00 Degree: 1
Cost_io: 2.00 Cost_cpu: 14222
Resp_io: 2.00 Resp_cpu: 14222
Access Path: index (RangeScan)
Index: F4INVOICE_DATE_IX
resc_io: 5.00 resc_cpu: 63537
ix_sel: 0.813255 ix_sel_with_filters: 0.813255
NL Join : Cost: 2.00 Resp: 2.00 Degree: 1
Cost_io: 2.00 Cost_cpu: 15646
Resp_io: 2.00 Resp_cpu: 15646
Access Path: index (RangeScan)
Index: F4INVOICE_PK
resc_io: 24.00 resc_cpu: 198845
ix_sel: 0.813255 ix_sel_with_filters: 0.813255
NL Join : Cost: 5.80 Resp: 5.80 Degree: 1
Cost_io: 5.80 Cost_cpu: 42707
Resp_io: 5.80 Resp_cpu: 42707
****** trying bitmap/domain indexes ******
****** finished trying bitmap/domain indexes ******
Best NL cost: 2.00
resc: 2.00 resc_io: 2.00 resc_cpu: 14222
resp: 2.00 resp_io: 2.00 resc_cpu: 14222
Join Card: 87.748644 = = outer (1.563739) * inner (56.114622) * sel
(1.000000)
Join Card - Rounded: 88 Computed: 87.75
Best:: JoinMethod: NestedLoop
Cost: 2.00 Degree: 1 Resp: 2.00 Card: 87.75 Bytes: 62
***************
Now joining: X4FEEITEM_INVOICE[XFI]#2
***************
NL Join
Outer table: Card: 87.75 Cost: 2.00 Resp: 2.00 Degree: 1 Bytes:
62
Access path analysis for X4FEEITEM_INVOICE
Inner table: X4FEEITEM_INVOICE Alias: XFI
Access Path: TableScan
NL Join: Cost: 608488.75 Resp: 608488.75 Degree: 1
Cost_io: 605299.00 Cost_cpu: 103197006349
Resp_io: 605299.00 Resp_cpu: 103197006349
kkofmx: index filter:"SD"."SERV_PROV_CODE"="XFI"."SERV_PROV_CODE"
kkofmx: index filter:"FINV"."SERV_PROV_CODE"="XFI"."SERV_PROV_CODE"
kkofmx: index filter:"SD"."SERV_PROV_CODE"="XFI"."SERV_PROV_CODE"
kkofmx: index filter:"FINV"."SERV_PROV_CODE"="XFI"."SERV_PROV_CODE"
kkofmx: index filter:"FINV"."INVOICE_NBR"="XFI"."INVOICE_NBR"
OPTIMIZER PERCENT INDEX CACHING = 90
Access Path: index (AllEqJoinGuess)
Index: X4FEEITEM_INVOICE_NBR_IX
resc_io: 1.00 resc_cpu: 12613
ix_sel: 0.000004 ix_sel_with_filters: 0.000004
***** Logdef predicate Adjustment ******
Final IO cst 0.00 , CPU cst 0.00
***** End Logdef Adjustment ******
***** Logdef predicate Adjustment ******
Final IO cst 0.00 , CPU cst 0.00
***** End Logdef Adjustment ******
NL Join : Cost: 10.80 Resp: 10.80 Degree: 1
Cost_io: 10.80 Cost_cpu: 125213
Resp_io: 10.80 Resp_cpu: 125213
OPTIMIZER PERCENT INDEX CACHING = 90
Access Path: index (RangeScan)
Index: X4FEEITEM_INVOICE_PK
resc_io: 2.00 resc_cpu: 15073
ix_sel: 0.000000 ix_sel_with_filters: 0.000000
***** Logdef predicate Adjustment ******
Final IO cst 0.00 , CPU cst 0.00
***** End Logdef Adjustment ******
***** Logdef predicate Adjustment ******
Final IO cst 0.00 , CPU cst 0.00
***** End Logdef Adjustment ******
***** Logdef predicate Adjustment ******
Final IO cst 0.00 , CPU cst 0.00
***** End Logdef Adjustment ******
NL Join : Cost: 19.60 Resp: 19.60 Degree: 1
Cost_io: 19.60 Cost_cpu: 146863
Resp_io: 19.60 Resp_cpu: 146863
****** trying bitmap/domain indexes ******
****** finished trying bitmap/domain indexes ******
Best NL cost: 10.80
resc: 10.80 resc_io: 10.80 resc_cpu: 125213
resp: 10.80 resp_io: 10.80 resc_cpu: 125213
ColGroup cardinality sanity check: ndv for X4FEEITEM_INVOICE[XFI] =
1278471 SETDETAILS[SD] = 1120
Join selectivity using 1 ColGroups: 0.001959 (sel1 = 0.000000, sel2 =
0.000000)
Join Card: 1.020111 = = outer (87.748644) * inner (1187146.275001) *
sel (0.000000)
Join Card - Rounded: 1 Computed: 1.02
Outer table: F4INVOICE Alias: FINV
resc: 9379.92 card 6301302.15 bytes: 62 deg: 1 resp: 9379.92
Inner table: X4FEEITEM_INVOICE Alias: XFI
resc: 3039.37 card: 1187146.28 bytes: 131 deg: 1 resp: 3039.37
using dmeth: 2 #groups: 1
SORT ressource Sort statistics
Sort width: 238 Area size: 208896 Max Area size:
41943040
Degree: 1
Blocks to Sort: 60946 Row size: 79 Total Rows:
6301302
Initial runs: 12 Merge passes: 1 IO Cost / pass: 33014
Total IO sort cost: 93960 Total CPU sort cost: 7945775124
Total Temp space used: 913646000
SORT ressource Sort statistics
Sort width: 238 Area size: 208896 Max Area size:
41943040
Degree: 1
Blocks to Sort: 22528 Row size: 155 Total Rows:
1187146
Initial runs: 5 Merge passes: 1 IO Cost / pass: 12204
Total IO sort cost: 34732 Total CPU sort cost: 1666431753
Total Temp space used: 442065000
SM join: Resc: 141408.40 Resp: 141408.40 [multiMatchCost=0.00]
SM Join
SM cost: 141408.40
resc: 141408.40 resc_io: 141095.70 resc_cpu: 10116550418
resp: 141408.40 resp_io: 141095.70 resp_cpu: 10116550418
Outer table: F4INVOICE Alias: FINV
resc: 9379.92 card 6301302.15 bytes: 62 deg: 1 resp: 9379.92
Inner table: X4FEEITEM_INVOICE Alias: XFI
resc: 3039.37 card: 1187146.28 bytes: 131 deg: 1 resp: 3039.37
using dmeth: 2 #groups: 1
Cost per ptn: 30102.90 #ptns: 1
hash_area: 124 (max=10240) buildfrag: 56921 probefrag: 20723
ppasses: 1
Hash join: Resc: 42522.19 Resp: 42522.19 [multiMatchCost=0.00]
Outer table: X4FEEITEM_INVOICE Alias: XFI
resc: 3039.37 card 1187146.28 bytes: 131 deg: 1 resp: 3039.37
Inner table: F4INVOICE Alias: FINV
resc: 2.00 card: 87.75 bytes: 62 deg: 1 resp: 2.00
using dmeth: 2 #groups: 1
Cost per ptn: 8031.63 #ptns: 1
hash_area: 124 (max=10240) buildfrag: 20723 probefrag: 1
ppasses: 1
Hash join: Resc: 11073.00 Resp: 11073.00 [multiMatchCost=0.00]
HA Join
HA cost: 11073.00 swapped
resc: 11073.00 resc_io: 11060.20 resc_cpu: 414215271
resp: 11073.00 resp_io: 11060.20 resp_cpu: 414215271
Best:: JoinMethod: NestedLoop
Cost: 10.80 Degree: 1 Resp: 10.80 Card: 1.02 Bytes: 193
***************
Now joining: F4FEEITEM[F4]#3
***************
NL Join
Outer table: Card: 1.02 Cost: 10.80 Resp: 10.80 Degree: 1 Bytes:
193
Access path analysis for F4FEEITEM
Inner table: F4FEEITEM Alias: F4
Access Path: TableScan
NL Join: Cost: 2699.52 Resp: 2699.52 Degree: 1
Cost_io: 2685.80 Cost_cpu: 443887309
Resp_io: 2685.80 Resp_cpu: 443887309
kkofmx: index filter:"XFI"."SERV_PROV_CODE"="F4"."SERV_PROV_CODE"
OPTIMIZER PERCENT INDEX CACHING = 90
Access Path: index (UniqueScan)
Index: F4FEEITEM_PK
resc_io: 1.00 resc_cpu: 9201
ix_sel: 0.000003 ix_sel_with_filters: 0.000003
***** Logdef predicate Adjustment ******
Final IO cst 0.00 , CPU cst 0.00
***** End Logdef Adjustment ******
NL Join : Cost: 11.80 Resp: 11.80 Degree: 1
Cost_io: 11.80 Cost_cpu: 126134
Resp_io: 11.80 Resp_cpu: 126134
kkofmx: index filter:"XFI"."SERV_PROV_CODE"="F4"."SERV_PROV_CODE"
kkofmx: index filter:"XFI"."SERV_PROV_CODE"="F4"."SERV_PROV_CODE"
OPTIMIZER PERCENT INDEX CACHING = 90
Access Path: index (AllEqUnique)
Index: F4FEEITEM_PK
resc_io: 1.00 resc_cpu: 9201
ix_sel: 0.000000 ix_sel_with_filters: 0.000000
***** Logdef predicate Adjustment ******
Final IO cst 0.00 , CPU cst 0.00
***** End Logdef Adjustment ******
NL Join : Cost: 11.80 Resp: 11.80 Degree: 1
Cost_io: 11.80 Cost_cpu: 126134
Resp_io: 11.80 Resp_cpu: 126134
OPTIMIZER PERCENT INDEX CACHING = 90
Access Path: index (RangeScan)
Index: F4FEEITEM_POS_IX
resc_io: 9451.00 resc_cpu: 487362029
ix_sel: 0.953821 ix_sel_with_filters: 0.953821
***** Logdef predicate Adjustment ******
Final IO cst 0.00 , CPU cst 0.00
***** End Logdef Adjustment ******
NL Join : Cost: 957.41 Resp: 957.41 Degree: 1
Cost_io: 955.90 Cost_cpu: 48861416
Resp_io: 955.90 Resp_cpu: 48861416
****** trying bitmap/domain indexes ******
****** finished trying bitmap/domain indexes ******
Best NL cost: 11.80
resc: 11.80 resc_io: 11.80 resc_cpu: 126134
resp: 11.80 resp_io: 11.80 resc_cpu: 126134
ColGroup cardinality sanity check: ndv for X4FEEITEM_INVOICE[XFI] =
192 F4FEEITEM[F4] = 1291140
Join selectivity using 1 ColGroups: 0.000293 (sel1 = 0.000000, sel2 =
0.000000)
Join Card: 102.011383 = = outer (1.020111) * inner (341509.085948) *
sel (0.000293)
Join Card - Rounded: 102 Computed: 102.01
Outer table: X4FEEITEM_INVOICE Alias: XFI
resc: 42530.09 card 112700.05 bytes: 193 deg: 1 resp: 42530.09
Inner table: F4FEEITEM Alias: F4
resc: 3953.05 card: 1231517.00 bytes: 42 deg: 1 resp: 3953.05
using dmeth: 2 #groups: 1
SORT ressource Sort statistics
Sort width: 238 Area size: 208896 Max Area size:
41943040
Degree: 1
Blocks to Sort: 3077 Row size: 223 Total Rows:
112700
Initial runs: 2 Merge passes: 1 IO Cost / pass: 1668
Total IO sort cost: 4745 Total CPU sort cost: 193340637
Total Temp space used: 57713000
SORT ressource Sort statistics
Sort width: 238 Area size: 208896 Max Area size:
41943040
Degree: 1
Blocks to Sort: 8595 Row size: 57 Total Rows: 1231517
Initial runs: 2 Merge passes: 1 IO Cost / pass: 4658
Total IO sort cost: 13253 Total CPU sort cost: 1366595347
Total Temp space used: 148382000
SM join: Resc: 64529.36 Resp: 64529.36 [multiMatchCost=0.00]
SM Join
SM cost: 64529.36
resc: 64529.36 resc_io: 64405.20 resc_cpu: 4016907388
resp: 64529.36 resp_io: 64405.20 resp_cpu: 4016907388
Outer table: X4FEEITEM_INVOICE Alias: XFI
resc: 42530.09 card 112700.05 bytes: 193 deg: 1 resp: 42530.09
Inner table: F4FEEITEM Alias: F4
resc: 1096.54 card: 341509.09 bytes: 42 deg: 1 resp: 1096.54
using dmeth: 2 #groups: 1
Cost per ptn: 1967.78 #ptns: 1
hash_area: 124 (max=10240) buildfrag: 2821 probefrag: 2252
ppasses: 1
Hash join: Resc: 45594.41 Resp: 45594.41 [multiMatchCost=0.00]
Outer table: F4FEEITEM Alias: F4
resc: 3953.05 card 1231517.00 bytes: 42 deg: 1 resp: 3953.05
Inner table: X4FEEITEM_INVOICE Alias: XFI
resc: 10.80 card: 1.02 bytes: 193 deg: 1 resp: 10.80
using dmeth: 2 #groups: 1
Cost per ptn: 3151.24 #ptns: 1
hash_area: 124 (max=10240) buildfrag: 8118 probefrag: 1 ppasses:
1
Hash join: Resc: 7115.09 Resp: 7115.09 [multiMatchCost=0.00]
HA Join
HA cost: 7115.09 swapped
resc: 7115.09 resc_io: 7102.30 resc_cpu: 413935678
resp: 7115.09 resp_io: 7102.30 resp_cpu: 413935678
ORDER BY sort
First K Rows: switch to Amode plans
Best:: JoinMethod: Hash
Cost: 2842236.72 Degree: 1 Resp: 2842236.72 Card:
40640899.98 Bytes: 235
***********************
Best so far: Table#: 0 cost: 1.0001 card: 25.3327 bytes: 950
Table#: 1 cost: 9379.9188 card: 6301302.1465 bytes:
390680724
Table#: 2 cost: 42530.0924 card: 112700.0541 bytes:
21751100
Table#: 3 cost: 2842236.7249 card: 40640899.9773
bytes: 9550611500
***********************
Join order[2]: SETDETAILS[SD]#0 F4INVOICE[FINV]#1 F4FEEITEM[F4]#3
X4FEEITEM_INVOICE[XFI]#2
***************
Now joining: F4FEEITEM[F4]#3
***************
NL Join
Outer table: Card: 6301302.15 Cost: 9379.92 Resp: 9379.92 Degree:
1 Bytes: 62
Access path analysis for F4FEEITEM
Inner table: F4FEEITEM Alias: F4
Access Path: TableScan
NL Join: Cost: 61060690373.87 Resp: 61060690373.87 Degree: 1
Cost_io: 60749943012.50 Cost_cpu: 10053506280658836
Resp_io: 60749943012.50 Resp_cpu: 10053506280658836
Access Path: index (RangeScan)
Index: F4FEEITEM_PK
resc_io: 502011.00 resc_cpu: 5089807126
ix_sel: 0.953821 ix_sel_with_filters: 0.953821
NL Join : Cost: 316431434957.78 Resp: 316431434957.78 Degree: 1
Cost_io: 316332301200.70 Cost_cpu: 3207241551594518
Resp_io: 316332301200.70 Resp_cpu: 3207241551594518
Access Path: index (RangeScan)
Index: F4FEEITEM_POS_IX
resc_io: 39475.00 resc_cpu: 1795884754
ix_sel: 0.953821 ix_sel_with_filters: 0.953821
NL Join : Cost: 24909377321.35 Resp: 24909377321.35 Degree: 1
Cost_io: 24874399013.50 Cost_cpu: 1131641588642504
Resp_io: 24874399013.50 Resp_cpu: 1131641588642504
****** trying bitmap/domain indexes ******
****** finished trying bitmap/domain indexes ******
Best NL cost: 24909377321.35
resc: 24909377321.35 resc_io: 24874399013.50 resc_cpu:
1131641588642504
resp: 24909377321.35 resp_io: 24874399013.50 resc_cpu:
1131641588642504
Join Card: 7760160715565.263672 = = outer (6301302.146511) * inner
(1231517.000000) * sel (1.000000)
Join Card - Rounded: 7760160715565 Computed: 7760160715565.26
Best:: JoinMethod: NestedLoop
Cost: 24909377321.35 Degree: 1 Resp: 24909377321.35 Card:
7760160715565.26 Bytes: 104
***************
Now joining: X4FEEITEM_INVOICE[XFI]#2
***************
NL Join
Outer table: Card: 7760160715565.26 Cost: 24909377321.35 Resp:
24909377321.35 Degree: 1 Bytes: 104
Access path analysis for X4FEEITEM_INVOICE
Inner table: X4FEEITEM_INVOICE Alias: XFI
Access Path: TableScan
NL Join: Cost: 53658442668552256.00 Resp: 53658442668552256.00
Degree: 1
Cost_io: 53377158666308520.00 Cost_cpu: 9100287998344789229568
Resp_io: 53377158666308520.00 Resp_cpu: 9100287998344789229568
kkofmx: index filter:"SD"."SERV_PROV_CODE"="XFI"."SERV_PROV_CODE"
kkofmx: index filter:"SD"."B1_PER_ID1"="XFI"."B1_PER_ID1"
kkofmx: index filter:"SD"."B1_PER_ID2"="XFI"."B1_PER_ID2"
kkofmx: index filter:"SD"."B1_PER_ID3"="XFI"."B1_PER_ID3"
kkofmx: index filter:"XFI"."SERV_PROV_CODE"="F4"."SERV_PROV_CODE"
kkofmx: index filter:"FINV"."SERV_PROV_CODE"="XFI"."SERV_PROV_CODE"
OPTIMIZER PERCENT INDEX CACHING = 90
Access Path: index (UniqueScan)
Index: X4FEEITEM_INVOICE_PK
resc_io: 1.00 resc_cpu: 8901
ix_sel: 0.000001 ix_sel_with_filters: 0.000001
***** Logdef predicate Adjustment ******
Final IO cst 0.00 , CPU cst 0.00
***** End Logdef Adjustment ******
***** Logdef predicate Adjustment ******
Final IO cst 0.00 , CPU cst 0.00
***** End Logdef Adjustment ******
***** Logdef predicate Adjustment ******
Final IO cst 0.00 , CPU cst 0.00
***** End Logdef Adjustment ******
***** Logdef predicate Adjustment ******
Final IO cst 0.00 , CPU cst 0.00
***** End Logdef Adjustment ******
***** Logdef predicate Adjustment ******
Final IO cst 0.00 , CPU cst 0.00
***** End Logdef Adjustment ******
***** Logdef predicate Adjustment ******
Final IO cst 0.00 , CPU cst 0.00
***** End Logdef Adjustment ******
NL Join : Cost: 801138960185.15 Resp: 801138960185.15 Degree: 1
Cost_io: 800890470570.00 Cost_cpu: 8039302073463704
Resp_io: 800890470570.00 Resp_cpu: 8039302073463704
kkofmx: index filter:"SD"."SERV_PROV_CODE"="XFI"."SERV_PROV_CODE"
kkofmx: index filter:"XFI"."SERV_PROV_CODE"="F4"."SERV_PROV_CODE"
kkofmx: index filter:"FINV"."SERV_PROV_CODE"="XFI"."SERV_PROV_CODE"
kkofmx: index filter:"SD"."SERV_PROV_CODE"="XFI"."SERV_PROV_CODE"
kkofmx: index filter:"SD"."B1_PER_ID1"="XFI"."B1_PER_ID1"
kkofmx: index filter:"SD"."B1_PER_ID2"="XFI"."B1_PER_ID2"
kkofmx: index filter:"SD"."B1_PER_ID3"="XFI"."B1_PER_ID3"
kkofmx: index filter:"XFI"."SERV_PROV_CODE"="F4"."SERV_PROV_CODE"
kkofmx: index filter:"FINV"."SERV_PROV_CODE"="XFI"."SERV_PROV_CODE"
OPTIMIZER PERCENT INDEX CACHING = 90
Access Path: index (AllEqJoinGuess)
Index: X4FEEITEM_INVOICE_NBR_IX
resc_io: 1.00 resc_cpu: 12613
ix_sel: 0.000004 ix_sel_with_filters: 0.000004
***** Logdef predicate Adjustment ******
Final IO cst 0.00 , CPU cst 0.00
***** End Logdef Adjustment ******
***** Logdef predicate Adjustment ******
Final IO cst 0.00 , CPU cst 0.00
***** End Logdef Adjustment ******
***** Logdef predicate Adjustment ******
Final IO cst 0.00 , CPU cst 0.00
***** End Logdef Adjustment ******
NL Join : Cost: 801227978714.05 Resp: 801227978714.05 Degree: 1
Cost_io: 800890470570.00 Cost_cpu: 10919289003780112
Resp_io: 800890470570.00 Resp_cpu: 10919289003780112
OPTIMIZER PERCENT INDEX CACHING = 90
Access Path: index (AllEqUnique)
Index: X4FEEITEM_INVOICE_PK
resc_io: 1.00 resc_cpu: 8901
ix_sel: 0.000000 ix_sel_with_filters: 0.000000
***** Logdef predicate Adjustment ******
Final IO cst 0.00 , CPU cst 0.00
***** End Logdef Adjustment ******
***** Logdef predicate Adjustment ******
Final IO cst 0.00 , CPU cst 0.00
***** End Logdef Adjustment ******
***** Logdef predicate Adjustment ******
Final IO cst 0.00 , CPU cst 0.00
***** End Logdef Adjustment ******
***** Logdef predicate Adjustment ******
Final IO cst 0.00 , CPU cst 0.00
***** End Logdef Adjustment ******
***** Logdef predicate Adjustment ******
Final IO cst 0.00 , CPU cst 0.00
***** End Logdef Adjustment ******
***** Logdef predicate Adjustment ******
Final IO cst 0.00 , CPU cst 0.00
***** End Logdef Adjustment ******
NL Join : Cost: 801138960185.15 Resp: 801138960185.15 Degree: 1
Cost_io: 800890470570.00 Cost_cpu: 8039302073463704
Resp_io: 800890470570.00 Resp_cpu: 8039302073463704
****** trying bitmap/domain indexes ******
****** finished trying bitmap/domain indexes ******
Best NL cost: 801138960185.15
resc: 801138960185.15 resc_io: 800890470570.00 resc_cpu:
8039302073463704
resp: 801138960185.15 resp_io: 800890470570.00 resc_cpu:
8039302073463704
ColGroup cardinality sanity check: ndv for X4FEEITEM_INVOICE[XFI] =
1278471 SETDETAILS[SD] = 1120
ColGroup cardinality sanity check: ndv for X4FEEITEM_INVOICE[XFI] =
1278471 F4FEEITEM[F4] = 1291140
Join selectivity using 2 ColGroups: 0.000001 (sel1 = 0.000000, sel2 =
0.000000)
Join Card: 26416584.985249 = = outer (7760160715565.263672) * inner
(1187146.275001) * sel (0.000000)
Join Card - Rounded: 26416585 Computed: 26416584.99
Outer table: F4FEEITEM Alias: F4
resc: 24909377321.35 card 7760160715565.26 bytes: 104 deg: 1
resp: 24909377321.35
Inner table: X4FEEITEM_INVOICE Alias: XFI
resc: 3039.37 card: 1187146.28 bytes: 131 deg: 1 resp: 3039.37
using dmeth: 2 #groups: 1
SORT ressource Sort statistics
Sort width: 238 Area size: 208896 Max Area size:
41943040
Degree: 1
Blocks to Sort: 2794463989 Row size: 125 Total Rows:
7760160715565
Initial runs: 23127104 Merge passes: 4 IO Cost / pass:
1513667996
Total IO sort cost: 8849135973 Total CPU sort cost:
15142955447201776
Total Temp space used: 1956038328116000
SORT ressource Sort statistics
Sort width: 238 Area size: 208896 Max Area size:
41943040
Degree: 1
Blocks to Sort: 22528 Row size: 155 Total Rows:
1187146
Initial runs: 5 Merge passes: 1 IO Cost / pass: 12204
Total IO sort cost: 34732 Total CPU sort cost: 1666431753
Total Temp space used: 442065000
SM join: Resc: 34226610052.81 Resp: 34226610052.81
[multiMatchCost=0.00]
SM Join
SM cost: 34226610052.81
resc: 34226610052.81 resc_io: 33723572753.70 resc_cpu:
16274598837192042
resp: 34226610052.81 resp_io: 33723572753.70 resp_cpu:
16274598837192042
Outer table: F4FEEITEM Alias: F4
resc: 24909377321.35 card 7760160715565.26 bytes: 104 deg: 1
resp: 24909377321.35
Inner table: X4FEEITEM_INVOICE Alias: XFI
resc: 3039.37 card: 1187146.28 bytes: 131 deg: 1 resp: 3039.37
using dmeth: 2 #groups: 1
Cost per ptn: 42922754857.84 #ptns: 1
hash_area: 124 (max=10240) buildfrag: 109885088258 probefrag:
20723 ppasses: 1
Hash join: Resc: 67832135296.55 Resp: 67832135296.55
[multiMatchCost=77.98]
Outer table: X4FEEITEM_INVOICE Alias: XFI
resc: 3039.37 card 1187146.28 bytes: 131 deg: 1 resp: 3039.37
Inner table: F4FEEITEM Alias: F4
resc: 24909377321.35 card: 7760160715565.26 bytes: 104 deg: 1
resp: 24909377321.35
using dmeth: 2 #groups: 1
Cost per ptn: 42598077416.87 #ptns: 1
hash_area: 124 (max=10240) buildfrag: 20723 probefrag:
109885088258 ppasses: 1
Hash join: Resc: 67507457777.59 Resp: 67507457777.59
[multiMatchCost=0.00]
HA Join
HA cost: 67507457777.59 swapped
resc: 67507457777.59 resc_io: 67410573267.70 resc_cpu:
3134472403492896
resp: 67507457777.59 resp_io: 67410573267.70 resp_cpu:
3134472403492896
ORDER BY sort
SORT ressource Sort statistics
Sort width: 238 Area size: 208896 Max Area size:
41943040
Degree: 1
Blocks to Sort: 869988 Row size: 269 Total Rows:
26416585
Initial runs: 170 Merge passes: 1 IO Cost / pass: 471246
Total IO sort cost: 1341234 Total CPU sort cost:
50800686513
Total Temp space used: 7728751000
Best:: JoinMethod: SortMerge
Cost: 34227952857.03 Degree: 1 Resp: 34227952857.03 Card:
26416584.99 Bytes: 235
***********************
Best so far: Table#: 0 cost: 1.0001 card: 25.3327 bytes: 950
Table#: 1 cost: 9379.9188 card: 6301302.1465 bytes:
390680724
Table#: 3 cost: 24909377321.3532 card:
7760160715565.2637 bytes: 807056714418760
Table#: 2 cost: 34227952857.0291 card: 26416584.9852
bytes: 6207897475
***********************
First K Rows: K = 100.00, N = 26416585.00
First K Rows: old pf = 0.0002251, new pf = 0.0000965
Access path analysis for F4INVOICE
***************************************
SINGLE TABLE ACCESS PATH (First K Rows)
Single Table Cardinality Estimation for F4INVOICE[FINV]
Table: F4INVOICE Alias: FINV
Card: Original: 30.000000 Rounded: 24 Computed: 24.40 Non
Adjusted: 24.40
Access Path: TableScan
Cost: 2.00 Resp: 2.00 Degree: 0
Cost_io: 2.00 Cost_cpu: 16481
Resp_io: 2.00 Resp_cpu: 16481
Access Path: index (RangeScan)
Index: F4INVOICE_BATCH_DATE_IX
resc_io: 4.00 resc_cpu: 40736
ix_sel: 0.813255 ix_sel_with_filters: 0.813255
Cost: 1.00 Resp: 1.00 Degree: 1
Access Path: index (RangeScan)
Index: F4INVOICE_BATCH_NBR_IX
resc_io: 4.00 resc_cpu: 40736
ix_sel: 0.813255 ix_sel_with_filters: 0.813255
Cost: 1.00 Resp: 1.00 Degree: 1
Access Path: index (RangeScan)
Index: F4INVOICE_DATE_IX
resc_io: 4.00 resc_cpu: 40736
ix_sel: 0.813255 ix_sel_with_filters: 0.813255
Cost: 1.00 Resp: 1.00 Degree: 1
Access Path: index (RangeScan)
Index: F4INVOICE_PK
resc_io: 12.00 resc_cpu: 97707
ix_sel: 0.813255 ix_sel_with_filters: 0.813255
Cost: 1.20 Resp: 1.20 Degree: 1
****** trying bitmap/domain indexes ******
****** finished trying bitmap/domain indexes ******
Best:: AccessPath: IndexRange
Index: F4INVOICE_BATCH_DATE_IX
Cost: 1.00 Degree: 1 Resp: 1.00 Card: 24.40 Bytes: 62
First K Rows: old pf = 0.2773070, new pf = 1.0000000
Access path analysis for F4FEEITEM
***************************************
SINGLE TABLE ACCESS PATH (First K Rows)
Single Table Cardinality Estimation for F4FEEITEM[F4]
Table: F4FEEITEM Alias: F4
Card: Original: 1291140.000000 Rounded: 1231517 Computed:
1231517.00 Non Adjusted: 1231517.00
Access Path: TableScan
Cost: 9692.31 Resp: 9692.31 Degree: 0
Cost_io: 9643.00 Cost_cpu: 1595464860
Resp_io: 9643.00 Resp_cpu: 1595464860
Access Path: index (RangeScan)
Index: F4FEEITEM_PK
resc_io: 502011.00 resc_cpu: 5089807126
ix_sel: 0.953821 ix_sel_with_filters: 0.953821
Cost: 50216.83 Resp: 50216.83 Degree: 1
Access Path: index (RangeScan)
Index: F4FEEITEM_POS_IX
resc_io: 39475.00 resc_cpu: 1795884754
ix_sel: 0.953821 ix_sel_with_filters: 0.953821
Cost: 3953.05 Resp: 3953.05 Degree: 1
****** trying bitmap/domain indexes ******
****** finished trying bitmap/domain indexes ******
Best:: AccessPath: IndexRange
Index: F4FEEITEM_POS_IX
Cost: 3953.05 Degree: 1 Resp: 3953.05 Card: 1231517.00
Bytes: 235
First K Rows: unchanged join prefix len = 2
Join order[2]: SETDETAILS[SD]#0 F4INVOICE[FINV]#1 F4FEEITEM[F4]#3
X4FEEITEM_INVOICE[XFI]#2
***************
Now joining: F4INVOICE[FINV]#1
***************
NL Join
Outer table: Card: 1.56 Cost: 1.00 Resp: 1.00 Degree: 1 Bytes:
38
Access path analysis for F4INVOICE
Inner table: F4INVOICE Alias: FINV
Access Path: TableScan
NL Join: Cost: 3.00 Resp: 3.00 Degree: 1
Cost_io: 3.00 Cost_cpu: 35901
Resp_io: 3.00 Resp_cpu: 35901
Access Path: index (RangeScan)
Index: F4INVOICE_BATCH_DATE_IX
resc_io: 4.00 resc_cpu: 40736
ix_sel: 0.813255 ix_sel_with_filters: 0.813255
NL Join : Cost: 2.00 Resp: 2.00 Degree: 1
Cost_io: 2.00 Cost_cpu: 11086
Resp_io: 2.00 Resp_cpu: 11086
Access Path: index (RangeScan)
Index: F4INVOICE_BATCH_NBR_IX
resc_io: 4.00 resc_cpu: 40736
ix_sel: 0.813255 ix_sel_with_filters: 0.813255
NL Join : Cost: 2.00 Resp: 2.00 Degree: 1
Cost_io: 2.00 Cost_cpu: 11086
Resp_io: 2.00 Resp_cpu: 11086
Access Path: index (RangeScan)
Index: F4INVOICE_DATE_IX
resc_io: 4.00 resc_cpu: 40736
ix_sel: 0.813255 ix_sel_with_filters: 0.813255
NL Join : Cost: 2.00 Resp: 2.00 Degree: 1
Cost_io: 2.00 Cost_cpu: 11086
Resp_io: 2.00 Resp_cpu: 11086
Access Path: index (RangeScan)
Index: F4INVOICE_PK
resc_io: 12.00 resc_cpu: 97707
ix_sel: 0.813255 ix_sel_with_filters: 0.813255
NL Join : Cost: 3.40 Resp: 3.40 Degree: 1
Cost_io: 3.40 Cost_cpu: 22480
Resp_io: 3.40 Resp_cpu: 22480
****** trying bitmap/domain indexes ******
****** finished trying bitmap/domain indexes ******
Best NL cost: 2.00
resc: 2.00 resc_io: 2.00 resc_cpu: 11086
resp: 2.00 resp_io: 2.00 resc_cpu: 11086
Join Card: 38.151584 = = outer (1.563739) * inner (24.397662) * sel
(1.000000)
Join Card - Rounded: 38 Computed: 38.15
Best:: JoinMethod: NestedLoop
Cost: 2.00 Degree: 1 Resp: 2.00 Card: 38.15 Bytes: 62
***************
Now joining: F4FEEITEM[F4]#3
***************
NL Join
Outer table: Card: 38.15 Cost: 2.00 Resp: 2.00 Degree: 1 Bytes:
62
Access path analysis for F4FEEITEM
Inner table: F4FEEITEM Alias: F4
Access Path: TableScan
NL Join: Cost: 368229.96 Resp: 368229.96 Degree: 1
Cost_io: 366356.00 Cost_cpu: 60627675754
Resp_io: 366356.00 Resp_cpu: 60627675754
Access Path: index (RangeScan)
Index: F4FEEITEM_PK
resc_io: 502011.00 resc_cpu: 5089807126
ix_sel: 0.953821 ix_sel_with_filters: 0.953821
NL Join : Cost: 1908241.63 Resp: 1908241.63 Degree: 1
Cost_io: 1907643.80 Cost_cpu: 19341278164
Resp_io: 1907643.80 Resp_cpu: 19341278164
Access Path: index (RangeScan)
Index: F4FEEITEM_POS_IX
resc_io: 39475.00 resc_cpu: 1795884754
ix_sel: 0.953821 ix_sel_with_filters: 0.953821
NL Join : Cost: 150217.94 Resp: 150217.94 Degree: 1
Cost_io: 150007.00 Cost_cpu: 6824373151
Resp_io: 150007.00 Resp_cpu: 6824373151
****** trying bitmap/domain indexes ******
****** finished trying bitmap/domain indexes ******
Best NL cost: 150217.94
resc: 150217.94 resc_io: 150007.00 resc_cpu: 6824373151
resp: 150217.94 resp_io: 150007.00 resc_cpu: 6824373151
Join Card: 46984324.706703 = = outer (38.151584) * inner
(1231517.000000) * sel (1.000000)
Join Card - Rounded: 46984325 Computed: 46984324.71
Best:: JoinMethod: NestedLoop
Cost: 150217.94 Degree: 1 Resp: 150217.94 Card: 46984324.71
Bytes: 104
***************
Now joining: X4FEEITEM_INVOICE[XFI]#2
***************
NL Join
Outer table: Card: 46984324.71 Cost: 150217.94 Resp: 150217.94
Degree: 1 Bytes: 104
Access path analysis for X4FEEITEM_INVOICE
Inner table: X4FEEITEM_INVOICE Alias: XFI
Access Path: TableScan
NL Join: Cost: 324878027288.63 Resp: 324878027288.63 Degree: 1
Cost_io: 323174977641.00 Cost_cpu: 55098200200808192
Resp_io: 323174977641.00 Resp_cpu: 55098200200808192
kkofmx: index filter:"SD"."SERV_PROV_CODE"="XFI"."SERV_PROV_CODE"
kkofmx: index filter:"SD"."B1_PER_ID1"="XFI"."B1_PER_ID1"
kkofmx: index filter:"SD"."B1_PER_ID2"="XFI"."B1_PER_ID2"
kkofmx: index filter:"SD"."B1_PER_ID3"="XFI"."B1_PER_ID3"
kkofmx: index filter:"XFI"."SERV_PROV_CODE"="F4"."SERV_PROV_CODE"
kkofmx: index filter:"FINV"."SERV_PROV_CODE"="XFI"."SERV_PROV_CODE"
OPTIMIZER PERCENT INDEX CACHING = 90
Access Path: index (UniqueScan)
Index: X4FEEITEM_INVOICE_PK
resc_io: 1.00 resc_cpu: 8901
ix_sel: 0.000001 ix_sel_with_filters: 0.000001
***** Logdef predicate Adjustment ******
Final IO cst 0.00 , CPU cst 0.00
***** End Logdef Adjustment ******
***** Logdef predicate Adjustment ******
Final IO cst 0.00 , CPU cst 0.00
***** End Logdef Adjustment ******
***** Logdef predicate Adjustment ******
Final IO cst 0.00 , CPU cst 0.00
***** End Logdef Adjustment ******
***** Logdef predicate Adjustment ******
Final IO cst 0.00 , CPU cst 0.00
***** End Logdef Adjustment ******
***** Logdef predicate Adjustment ******
Final IO cst 0.00 , CPU cst 0.00
***** End Logdef Adjustment ******
***** Logdef predicate Adjustment ******
Final IO cst 0.00 , CPU cst 0.00
***** End Logdef Adjustment ******
NL Join : Cost: 4849943.15 Resp: 4849943.15 Degree: 1
Cost_io: 4848439.50 Cost_cpu: 48647188052
Resp_io: 4848439.50 Resp_cpu: 48647188052
kkofmx: index filter:"SD"."SERV_PROV_CODE"="XFI"."SERV_PROV_CODE"
kkofmx: index filter:"XFI"."SERV_PROV_CODE"="F4"."SERV_PROV_CODE"
kkofmx: index filter:"FINV"."SERV_PROV_CODE"="XFI"."SERV_PROV_CODE"
kkofmx: index filter:"SD"."SERV_PROV_CODE"="XFI"."SERV_PROV_CODE"
kkofmx: index filter:"SD"."B1_PER_ID1"="XFI"."B1_PER_ID1"
kkofmx: index filter:"SD"."B1_PER_ID2"="XFI"."B1_PER_ID2"
kkofmx: index filter:"SD"."B1_PER_ID3"="XFI"."B1_PER_ID3"
kkofmx: index filter:"XFI"."SERV_PROV_CODE"="F4"."SERV_PROV_CODE"
kkofmx: index filter:"FINV"."SERV_PROV_CODE"="XFI"."SERV_PROV_CODE"
OPTIMIZER PERCENT INDEX CACHING = 90
Access Path: index (AllEqJoinGuess)
Index: X4FEEITEM_INVOICE_NBR_IX
resc_io: 1.00 resc_cpu: 12613
ix_sel: 0.000004 ix_sel_with_filters: 0.000004
***** Logdef predicate Adjustment ******
Final IO cst 0.00 , CPU cst 0.00
***** End Logdef Adjustment ******
***** Logdef predicate Adjustment ******
Final IO cst 0.00 , CPU cst 0.00
***** End Logdef Adjustment ******
***** Logdef predicate Adjustment ******
Final IO cst 0.00 , CPU cst 0.00
***** End Logdef Adjustment ******
NL Join : Cost: 4850482.12 Resp: 4850482.12 Degree: 1
Cost_io: 4848439.50 Cost_cpu: 66084229228
Resp_io: 4848439.50 Resp_cpu: 66084229228
OPTIMIZER PERCENT INDEX CACHING = 90
Access Path: index (AllEqUnique)
Index: X4FEEITEM_INVOICE_PK
resc_io: 1.00 resc_cpu: 8901
ix_sel: 0.000000 ix_sel_with_filters: 0.000000
***** Logdef predicate Adjustment ******
Final IO cst 0.00 , CPU cst 0.00
***** End Logdef Adjustment ******
***** Logdef predicate Adjustment ******
Final IO cst 0.00 , CPU cst 0.00
***** End Logdef Adjustment ******
***** Logdef predicate Adjustment ******
Final IO cst 0.00 , CPU cst 0.00
***** End Logdef Adjustment ******
***** Logdef predicate Adjustment ******
Final IO cst 0.00 , CPU cst 0.00
***** End Logdef Adjustment ******
***** Logdef predicate Adjustment ******
Final IO cst 0.00 , CPU cst 0.00
***** End Logdef Adjustment ******
***** Logdef predicate Adjustment ******
Final IO cst 0.00 , CPU cst 0.00
***** End Logdef Adjustment ******
NL Join : Cost: 4849943.15 Resp: 4849943.15 Degree: 1
Cost_io: 4848439.50 Cost_cpu: 48647188052
Resp_io: 4848439.50 Resp_cpu: 48647188052
****** trying bitmap/domain indexes ******
****** finished trying bitmap/domain indexes ******
Best NL cost: 4849943.15
resc: 4849943.15 resc_io: 4848439.50 resc_cpu: 48647188052
resp: 4849943.15 resp_io: 4848439.50 resc_cpu: 48647188052
ColGroup cardinality sanity check: ndv for X4FEEITEM_INVOICE[XFI] =
48 SETDETAILS[SD] = 4
ColGroup cardinality sanity check: ndv for X4FEEITEM_INVOICE[XFI] =
19800 F4FEEITEM[F4] = 1291140
Join selectivity using 2 ColGroups: 0.000006 (sel1 = 0.000000, sel2 =
0.000000)
Join Card: 1700.668904 = = outer (46984324.706703) * inner
(1187146.275001) * sel (0.000000)
Join Card - Rounded: 1701 Computed: 1700.67
Outer table: F4FEEITEM Alias: F4
resc: 24909377321.35 card 7760160715565.26 bytes: 104 deg: 1
resp: 24909377321.35
Inner table: X4FEEITEM_INVOICE Alias: XFI
resc: 3039.37 card: 1187146.28 bytes: 131 deg: 1 resp: 3039.37
using dmeth: 2 #groups: 1
SORT ressource Sort statistics
Sort width: 238 Area size: 208896 Max Area size:
41943040
Degree: 1
Blocks to Sort: 2794463989 Row size: 125 Total Rows:
7760160715565
Initial runs: 23127104 Merge passes: 4 IO Cost / pass:
1513667996
Total IO sort cost: 8849135973 Total CPU sort cost:
15142955447201776
Total Temp space used: 1956038328116000
SORT ressource Sort statistics
Sort width: 238 Area size: 208896 Max Area size:
41943040
Degree: 1
Blocks to Sort: 22528 Row size: 155 Total Rows:
1187146
Initial runs: 5 Merge passes: 1 IO Cost / pass: 12204
Total IO sort cost: 34732 Total CPU sort cost: 1666431753
Total Temp space used: 442065000
SM join: Resc: 34226610052.81 Resp: 34226610052.81
[multiMatchCost=0.00]
SM Join
SM cost: 34226610052.81
resc: 34226610052.81 resc_io: 33723572753.70 resc_cpu:
16274598837192042
resp: 34226610052.81 resp_io: 33723572753.70 resp_cpu:
16274598837192042
Outer table: F4FEEITEM Alias: F4
resc: 24909377321.35 card 7760160715565.26 bytes: 104 deg: 1
resp: 24909377321.35
Inner table: X4FEEITEM_INVOICE Alias: XFI
resc: 3039.37 card: 1187146.28 bytes: 131 deg: 1 resp: 3039.37
using dmeth: 2 #groups: 1
Cost per ptn: 42922754857.84 #ptns: 1
hash_area: 124 (max=10240) buildfrag: 109885088258 probefrag:
20723 ppasses: 1
Hash join: Resc: 67832135218.56 Resp: 67832135218.56
[multiMatchCost=0.00]
Outer table: X4FEEITEM_INVOICE Alias: XFI
resc: 3039.37 card 1187146.28 bytes: 131 deg: 1 resp: 3039.37
Inner table: F4FEEITEM Alias: F4
resc: 150217.94 card: 46984324.71 bytes: 104 deg: 1 resp:
150217.94
using dmeth: 2 #groups: 1
Cost per ptn: 265943.45 #ptns: 1
hash_area: 124 (max=10240) buildfrag: 20723 probefrag: 665306
ppasses: 1
Hash join: Resc: 419200.75 Resp: 419200.75 [multiMatchCost=0.00]
HA Join
HA cost: 419200.75 swapped
resc: 419200.75 resc_io: 418602.20 resc_cpu: 19364803171
resp: 419200.75 resp_io: 418602.20 resp_cpu: 19364803171
First K Rows: copy A one plan, tab=X4FEEITEM_INVOICE
ORDER BY sort
First K Rows: switch to Amode plans
Join order aborted: cost > best plan cost
***********************
Join order[3]: SETDETAILS[SD]#0 X4FEEITEM_INVOICE[XFI]#2 F4INVOICE
[FINV]#1 F4FEEITEM[F4]#3
***************
Now joining: X4FEEITEM_INVOICE[XFI]#2
***************
NL Join
Outer table: Card: 25.33 Cost: 1.00 Resp: 1.00 Degree: 1 Bytes:
38
Access path analysis for X4FEEITEM_INVOICE
Inner table: X4FEEITEM_INVOICE Alias: XFI
Access Path: TableScan
NL Join: Cost: 172868.18 Resp: 172868.18 Degree: 1
Cost_io: 171962.00 Cost_cpu: 29317331327
Resp_io: 171962.00 Resp_cpu: 29317331327
kkofmx: index filter:"SD"."SERV_PROV_CODE"="XFI"."SERV_PROV_CODE"
kkofmx: index filter:"SD"."SERV_PROV_CODE"="XFI"."SERV_PROV_CODE"
OPTIMIZER PERCENT INDEX CACHING = 90
Access Path: index (RangeScan)
Index: X4FEEITEM_INVOICE_NBR_IX
resc_io: 26639.00 resc_cpu: 1324363816
ix_sel: 0.954312 ix_sel_with_filters: 0.954312
***** Logdef predicate Adjustment ******
Final IO cst 0.00 , CPU cst 0.00
***** End Logdef Adjustment ******
NL Join : Cost: 66700.84 Resp: 66700.84 Degree: 1
Cost_io: 66598.50 Cost_cpu: 3310913559
Resp_io: 66598.50 Resp_cpu: 3310913559
OPTIMIZER PERCENT INDEX CACHING = 90
Access Path: index (RangeScan)
Index: X4FEEITEM_INVOICE_PK
resc_io: 2.00 resc_cpu: 15082
ix_sel: 0.000000 ix_sel_with_filters: 0.000000
***** Logdef predicate Adjustment ******
Final IO cst 0.00 , CPU cst 0.00
***** End Logdef Adjustment ******
NL Join : Cost: 6.00 Resp: 6.00 Degree: 1
Cost_io: 6.00 Cost_cpu: 41723
Resp_io: 6.00 Resp_cpu: 41723
****** trying bitmap/domain indexes ******
****** finished trying bitmap/domain indexes ******
Best NL cost: 6.00
resc: 6.00 resc_io: 6.00 resc_cpu: 41723
resp: 6.00 resp_io: 6.00 resc_cpu: 41723
ColGroup cardinality sanity check: ndv for X4FEEITEM_INVOICE[XFI] =
1278471 SETDETAILS[SD] = 1120
Join selectivity using 1 ColGroups: 0.001959 (sel1 = 0.000000, sel2 =
0.000000)
Join Card: 58922.727880 = = outer (25.332653) * inner
(1187146.275001) * sel (0.001959)
Join Card - Rounded: 58923 Computed: 58922.73
Outer table: SETDETAILS Alias: SD
resc: 1.00 card 25.33 bytes: 38 deg: 1 resp: 1.00
Inner table: X4FEEITEM_INVOICE Alias: XFI
resc: 3039.37 card: 1187146.28 bytes: 131 deg: 1 resp: 3039.37
using dmeth: 2 #groups: 1
SORT ressource Sort statistics
Sort width: 238 Area size: 208896 Max Area size:
41943040
Degree: 1
Blocks to Sort: 1 Row size: 52 Total Rows: 25
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 32357899
Total Temp space used: 0
SORT ressource Sort statistics
Sort width: 238 Area size: 208896 Max Area size:
41943040
Degree: 1
Blocks to Sort: 22528 Row size: 155 Total Rows:
1187146
Initial runs: 5 Merge passes: 1 IO Cost / pass: 12204
Total IO sort cost: 34732 Total CPU sort cost: 1666431753
Total Temp space used: 442065000
SM join: Resc: 37824.88 Resp: 37824.88 [multiMatchCost=0.00]
SM Join
SM cost: 37824.88
resc: 37824.88 resc_io: 37768.20 resc_cpu: 1833709679
resp: 37824.88 resp_io: 37768.20 resp_cpu: 1833709679
Outer table: SETDETAILS Alias: SD
resc: 1.00 card 25.33 bytes: 38 deg: 1 resp: 1.00
Inner table: X4FEEITEM_INVOICE Alias: XFI
resc: 3039.37 card: 1187146.28 bytes: 131 deg: 1 resp: 3039.37
using dmeth: 2 #groups: 1
Cost per ptn: 4.17 #ptns: 1
hash_area: 124 (max=10240) buildfrag: 1 probefrag: 20723
ppasses: 1
Hash join: Resc: 3044.54 Resp: 3044.54 [multiMatchCost=0.00]
HA Join
HA cost: 3044.54
resc: 3044.54 resc_io: 3036.20 resc_cpu: 269814711
resp: 3044.54 resp_io: 3036.20 resp_cpu: 269814711
Best:: JoinMethod: NestedLoop
Cost: 6.00 Degree: 1 Resp: 6.00 Card: 58922.73 Bytes: 169
***************
Now joining: F4INVOICE[FINV]#1
***************
NL Join
Outer table: Card: 58922.73 Cost: 6.00 Resp: 6.00 Degree: 1
Bytes: 169
Access path analysis for F4INVOICE
Inner table: F4INVOICE Alias: FINV
Access Path: TableScan
NL Join: Cost: 58408219.03 Resp: 58408219.03 Degree: 1
Cost_io: 58184015.00 Cost_cpu: 7253598714095
Resp_io: 58184015.00 Resp_cpu: 7253598714095
kkofmx: index filter:"FINV"."SERV_PROV_CODE"="XFI"."SERV_PROV_CODE"
OPTIMIZER PERCENT INDEX CACHING = 90
Access Path: index (UniqueScan)
Index: F4INVOICE_PK
resc_io: 1.00 resc_cpu: 8461
ix_sel: 0.000003 ix_sel_with_filters: 0.000003
***** Logdef predicate Adjustment ******
Final IO cst 0.00 , CPU cst 0.00
***** End Logdef Adjustment ******
NL Join : Cost: 5899.84 Resp: 5899.84 Degree: 1
Cost_io: 5898.30 Cost_cpu: 49899066
Resp_io: 5898.30 Resp_cpu: 49899066
kkofmx: index filter:"FINV"."SERV_PROV_CODE"="XFI"."SERV_PROV_CODE"
kkofmx: index filter:"FINV"."SERV_PROV_CODE"="XFI"."SERV_PROV_CODE"
kkofmx: index filter:"FINV"."SERV_PROV_CODE"="XFI"."SERV_PROV_CODE"
kkofmx: index filter:"FINV"."SERV_PROV_CODE"="XFI"."SERV_PROV_CODE"
OPTIMIZER PERCENT INDEX CACHING = 90
Access Path: index (RangeScan)
Index: F4INVOICE_BATCH_DATE_IX
resc_io: 3793.00 resc_cpu: 149236242
ix_sel: 0.813255 ix_sel_with_filters: 0.813255
***** Logdef predicate Adjustment ******
Final IO cst 0.00 , CPU cst 0.00
***** End Logdef Adjustment ******
NL Join : Cost: 22376679.88 Resp: 22376679.88 Degree: 1
Cost_io: 22349499.90 Cost_cpu: 879344749989
Resp_io: 22349499.90 Resp_cpu: 879344749989
OPTIMIZER PERCENT INDEX CACHING = 90
Access Path: index (RangeScan)
Index: F4INVOICE_BATCH_NBR_IX
resc_io: 3050.00 resc_cpu: 142805762
ix_sel: 0.813255 ix_sel_with_filters: 0.813255
***** Logdef predicate Adjustment ******
Final IO cst 0.00 , CPU cst 0.00
***** End Logdef Adjustment ******
NL Join : Cost: 17997529.81 Resp: 17997529.81 Degree: 1
Cost_io: 17971521.00 Cost_cpu: 841454433156
Resp_io: 17971521.00 Resp_cpu: 841454433156
OPTIMIZER PERCENT INDEX CACHING = 90
Access Path: index (RangeScan)
Index: F4INVOICE_DATE_IX
resc_io: 3726.00 resc_cpu: 148184335
ix_sel: 0.813255 ix_sel_with_filters: 0.813255
***** Logdef predicate Adjustment ******
Final IO cst 0.00 , CPU cst 0.00
***** End Logdef Adjustment ******
NL Join : Cost: 21981704.20 Resp: 21981704.20 Degree: 1
Cost_io: 21954715.80 Cost_cpu: 873146601436
Resp_io: 21954715.80 Resp_cpu: 873146601436
OPTIMIZER PERCENT INDEX CACHING = 90
Access Path: index (AllEqUnique)
Index: F4INVOICE_PK
resc_io: 1.00 resc_cpu: 8461
ix_sel: 0.000004 ix_sel_with_filters: 0.000004
***** Logdef predicate Adjustment ******
Final IO cst 0.00 , CPU cst 0.00
***** End Logdef Adjustment ******
NL Join : Cost: 5899.84 Resp: 5899.84 Degree: 1
Cost_io: 5898.30 Cost_cpu: 49899066
Resp_io: 5898.30 Resp_cpu: 49899066
****** trying bitmap/domain indexes ******
****** finished trying bitmap/domain indexes ******
Best NL cost: 5899.84
resc: 5899.84 resc_io: 5898.30 resc_cpu: 49899066
resp: 5899.84 resp_io: 5898.30 resc_cpu: 49899066
Join Card: 73255.035134 = = outer (58922.727880) * inner
(248742.292433) * sel (0.000005)
Join Card - Rounded: 73255 Computed: 73255.04
Outer table: X4FEEITEM_INVOICE Alias: XFI
resc: 6.00 card 58922.73 bytes: 169 deg: 1 resp: 6.00
Inner table: F4INVOICE Alias: FINV
resc: 375.16 card: 248742.29 bytes: 24 deg: 1 resp: 375.16
using dmeth: 2 #groups: 1
SORT ressource Sort statistics
Sort width: 238 Area size: 208896 Max Area size:
41943040
Degree: 1
Blocks to Sort: 1414 Row size: 196 Total Rows:
58923
Initial runs: 2 Merge passes: 1 IO Cost / pass: 768
Total IO sort cost: 2182 Total CPU sort cost: 109242447
Total Temp space used: 26829000
SORT ressource Sort statistics
Sort width: 238 Area size: 208896 Max Area size:
41943040
Degree: 1
Blocks to Sort: 1127 Row size: 37 Total Rows: 248742
Initial runs: 2 Merge passes: 1 IO Cost / pass: 612
Total IO sort cost: 1739 Total CPU sort cost: 260983032
Total Temp space used: 18047000
SM join: Resc: 4313.60 Resp: 4313.60 [multiMatchCost=0.00]
SM Join
SM cost: 4313.60
resc: 4313.60 resc_io: 4301.70 resc_cpu: 385044143
resp: 4313.60 resp_io: 4301.70 resp_cpu: 385044143
Outer table: X4FEEITEM_INVOICE Alias: XFI
resc: 6.00 card 58922.73 bytes: 169 deg: 1 resp: 6.00
Inner table: F4INVOICE Alias: FINV
resc: 375.16 card: 248742.29 bytes: 24 deg: 1 resp: 375.16
using dmeth: 2 #groups: 1
Cost per ptn: 930.61 #ptns: 1
hash_area: 124 (max=10240) buildfrag: 1302 probefrag: 1094
ppasses: 1
Hash join: Resc: 1311.77 Resp: 1311.77 [multiMatchCost=0.00]
Outer table: F4INVOICE Alias: FINV
resc: 375.16 card 248742.29 bytes: 24 deg: 1 resp: 375.16
Inner table: X4FEEITEM_INVOICE Alias: XFI
resc: 6.00 card: 58922.73 bytes: 169 deg: 1 resp: 6.00
using dmeth: 2 #groups: 1
Cost per ptn: 930.32 #ptns: 1
hash_area: 124 (max=10240) buildfrag: 1094 probefrag: 1302
ppasses: 1
Hash join: Resc: 1311.52 Resp: 1311.52 [multiMatchCost=0.04]
HA Join
HA cost: 1311.52 swapped
resc: 1311.52 resc_io: 1308.70 resc_cpu: 91353572
resp: 1311.52 resp_io: 1308.70 resp_cpu: 91353572
Best:: JoinMethod: Hash
Cost: 1311.52 Degree: 1 Resp: 1311.52 Card: 73255.04 Bytes:
193
***************
Now joining: F4FEEITEM[F4]#3
***************
NL Join
Outer table: Card: 73255.04 Cost: 1311.52 Resp: 1311.52 Degree:
1 Bytes: 193
Access path analysis for F4FEEITEM
Inner table: F4FEEITEM Alias: F4
Access Path: TableScan
NL Join: Cost: 709865440.24 Resp: 709865440.24 Degree: 1
Cost_io: 706242082.70 Cost_cpu: 117225284278630
Resp_io: 706242082.70 Resp_cpu: 117225284278630
kkofmx: index filter:"XFI"."SERV_PROV_CODE"="F4"."SERV_PROV_CODE"
OPTIMIZER PERCENT INDEX CACHING = 90
Access Path: index (UniqueScan)
Index: F4FEEITEM_PK
resc_io: 1.00 resc_cpu: 9201
ix_sel: 0.000001 ix_sel_with_filters: 0.000001
***** Logdef predicate Adjustment ******
Final IO cst 0.00 , CPU cst 0.00
***** End Logdef Adjustment ******
NL Join : Cost: 8639.11 Resp: 8639.11 Degree: 1
Cost_io: 8634.20 Cost_cpu: 158758721
Resp_io: 8634.20 Resp_cpu: 158758721
kkofmx: index filter:"XFI"."SERV_PROV_CODE"="F4"."SERV_PROV_CODE"
kkofmx: index filter:"XFI"."SERV_PROV_CODE"="F4"."SERV_PROV_CODE"
OPTIMIZER PERCENT INDEX CACHING = 90
Access Path: index (AllEqUnique)
Index: F4FEEITEM_PK
resc_io: 1.00 resc_cpu: 9201
ix_sel: 0.000000 ix_sel_with_filters: 0.000000
***** Logdef predicate Adjustment ******
Final IO cst 0.00 , CPU cst 0.00
***** End Logdef Adjustment ******
NL Join : Cost: 8639.11 Resp: 8639.11 Degree: 1
Cost_io: 8634.20 Cost_cpu: 158758721
Resp_io: 8634.20 Resp_cpu: 158758721
OPTIMIZER PERCENT INDEX CACHING = 90
Access Path: index (RangeScan)
Index: F4FEEITEM_POS_IX
resc_io: 34070.00 resc_cpu: 1757393371
ix_sel: 0.953821 ix_sel_with_filters: 0.953821
***** Logdef predicate Adjustment ******
Final IO cst 0.00 , CPU cst 0.00
***** End Logdef Adjustment ******
NL Join : Cost: 249979016.88 Resp: 249979016.88 Degree: 1
Cost_io: 249581093.70 Cost_cpu: 12873876491367
Resp_io: 249581093.70 Resp_cpu: 12873876491367
****** trying bitmap/domain indexes ******
****** finished trying bitmap/domain indexes ******
Best NL cost: 8639.11
resc: 8639.11 resc_io: 8634.20 resc_cpu: 158758721
resp: 8639.11 resp_io: 8634.20 resc_cpu: 158758721
ColGroup cardinality sanity check: ndv for X4FEEITEM_INVOICE[XFI] =
192 F4FEEITEM[F4] = 1291140
Join selectivity using 1 ColGroups: 0.000293 (sel1 = 0.000000, sel2 =
0.000000)
Join Card: 26416584.985249 = = outer (73255.035134) * inner
(1231517.000000) * sel (0.000293)
Join Card - Rounded: 26416585 Computed: 26416584.99
Outer table: F4INVOICE Alias: FINV
resc: 1311.52 card 73255.04 bytes: 193 deg: 1 resp: 1311.52
Inner table: F4FEEITEM Alias: F4
resc: 3953.05 card: 1231517.00 bytes: 42 deg: 1 resp: 3953.05
using dmeth: 2 #groups: 1
SORT ressource Sort statistics
Sort width: 238 Area size: 208896 Max Area size:
41943040
Degree: 1
Blocks to Sort: 2000 Row size: 223 Total Rows:
73255
Initial runs: 2 Merge passes: 1 IO Cost / pass: 1084
Total IO sort cost: 3084 Total CPU sort cost: 134942411
Total Temp space used: 30024000
SORT ressource Sort statistics
Sort width: 238 Area size: 208896 Max Area size:
41943040
Degree: 1
Blocks to Sort: 8595 Row size: 57 Total Rows: 1231517
Initial runs: 2 Merge passes: 1 IO Cost / pass: 4658
Total IO sort cost: 13253 Total CPU sort cost: 1366595347
Total Temp space used: 148382000
SM join: Resc: 21725.83 Resp: 21725.83 [multiMatchCost=77.85]
SM Join
SM cost: 21725.83
resc: 21725.83 resc_io: 21593.20 resc_cpu: 4290986606
resp: 21725.83 resp_io: 21593.20 resp_cpu: 4290986606
Outer table: F4INVOICE Alias: FINV
resc: 1311.52 card 73255.04 bytes: 193 deg: 1 resp: 1311.52
Inner table: F4FEEITEM Alias: F4
resc: 3953.05 card: 1231517.00 bytes: 42 deg: 1 resp: 3953.05
using dmeth: 2 #groups: 1
Cost per ptn: 3862.71 #ptns: 1
hash_area: 124 (max=10240) buildfrag: 1834 probefrag: 8118
ppasses: 1
Hash join: Resc: 9205.13 Resp: 9205.13 [multiMatchCost=77.85]
HA Join
HA cost: 9205.13
resc: 9205.13 resc_io: 9109.20 resc_cpu: 3103744430
resp: 9205.13 resp_io: 9109.20 resp_cpu: 3103744430
ORDER BY sort
SORT ressource Sort statistics
Sort width: 238 Area size: 208896 Max Area size:
41943040
Degree: 1
Blocks to Sort: 869988 Row size: 269 Total Rows:
26416585
Initial runs: 170 Merge passes: 1 IO Cost / pass: 471246
Total IO sort cost: 1341234 Total CPU sort cost:
50800686513
Total Temp space used: 6980805000
Best:: JoinMethod: NestedLoop
Cost: 1351443.32 Degree: 1 Resp: 1351443.32 Card:
26416584.99 Bytes: 235
***********************
Best so far: Table#: 0 cost: 1.0001 card: 25.3327 bytes: 950
Table#: 2 cost: 6.0013 card: 58922.7279 bytes:
9957987
Table#: 1 cost: 1311.5237 card: 73255.0351 bytes:
14138215
Table#: 3 cost: 1351443.3234 card: 26416584.9852
bytes: 6207897475
***********************
First K Rows: K = 100.00, N = 26416585.00
First K Rows: old pf = 1.0000000, new pf = 0.0004299
Access path analysis for X4FEEITEM_INVOICE
***************************************
SINGLE TABLE ACCESS PATH (First K Rows)
Single Table Cardinality Estimation for X4FEEITEM_INVOICE[XFI]
ColGroup Usage:: PredCnt: 3 Matches Full: Partial:
Table: X4FEEITEM_INVOICE Alias: XFI
Card: Original: 550.000000 Rounded: 511 Computed: 510.71 Non
Adjusted: 510.71
Access Path: TableScan
Cost: 5.02 Resp: 5.02 Degree: 0
Cost_io: 5.00 Cost_cpu: 502555
Resp_io: 5.00 Resp_cpu: 502555
Access Path: index (RangeScan)
Index: X4FEEITEM_INVOICE_NBR_IX
resc_io: 16.00 resc_cpu: 601472
ix_sel: 0.954312 ix_sel_with_filters: 0.954312
Cost: 1.60 Resp: 1.60 Degree: 1
Access Path: index (RangeScan)
Index: X4FEEITEM_INVOICE_PK
resc_io: 95.00 resc_cpu: 1164066
ix_sel: 0.954312 ix_sel_with_filters: 0.954312
Cost: 9.50 Resp: 9.50 Degree: 1
****** trying bitmap/domain indexes ******
****** finished trying bitmap/domain indexes ******
Best:: AccessPath: IndexRange
Index: X4FEEITEM_INVOICE_NBR_IX
Cost: 1.60 Degree: 1 Resp: 1.60 Card: 510.71 Bytes: 339
First K Rows: old pf = 0.0000965, new pf = 0.8043506
Access path analysis for F4INVOICE
***************************************
SINGLE TABLE ACCESS PATH (First K Rows)
Single Table Cardinality Estimation for F4INVOICE[FINV]
Table: F4INVOICE Alias: FINV
Card: Original: 246019.000000 Rounded: 200076 Computed:
200076.28 Non Adjusted: 200076.28
Access Path: TableScan
Cost: 799.03 Resp: 799.03 Degree: 0
Cost_io: 796.00 Cost_cpu: 98101624
Resp_io: 796.00 Resp_cpu: 98101624
Access Path: index (RangeScan)
Index: F4INVOICE_BATCH_DATE_IX
resc_io: 3821.00 resc_cpu: 125522662
ix_sel: 0.813255 ix_sel_with_filters: 0.813255
Cost: 382.49 Resp: 382.49 Degree: 1
Access Path: index (RangeScan)
Index: F4INVOICE_BATCH_NBR_IX
resc_io: 3015.00 resc_cpu: 118866482
ix_sel: 0.813255 ix_sel_with_filters: 0.813255
Cost: 301.87 Resp: 301.87 Degree: 1
Access Path: index (RangeScan)
Index: F4INVOICE_DATE_IX
resc_io: 3737.00 resc_cpu: 124462391
ix_sel: 0.813255 ix_sel_with_filters: 0.813255
Cost: 374.08 Resp: 374.08 Degree: 1
Access Path: index (RangeScan)
Index: F4INVOICE_PK
resc_io: 72164.00 resc_cpu: 612534386
ix_sel: 0.813255 ix_sel_with_filters: 0.813255
Cost: 7218.29 Resp: 7218.29 Degree: 1
****** trying bitmap/domain indexes ******
****** finished trying bitmap/domain indexes ******
Best:: AccessPath: IndexRange
Index: F4INVOICE_BATCH_NBR_IX
Cost: 301.87 Degree: 1 Resp: 301.87 Card: 200076.28
Bytes: 62
First K Rows: old pf = 1.0000000, new pf = 0.2773070
Access path analysis for F4FEEITEM
***************************************
SINGLE TABLE ACCESS PATH (First K Rows)
Single Table Cardinality Estimation for F4FEEITEM[F4]
Table: F4FEEITEM Alias: F4
Card: Original: 358043.000000 Rounded: 341509 Computed:
341509.09 Non Adjusted: 341509.09
Access Path: TableScan
Cost: 2688.68 Resp: 2688.68 Degree: 0
Cost_io: 2675.00 Cost_cpu: 442439376
Resp_io: 2675.00 Resp_cpu: 442439376
Access Path: index (RangeScan)
Index: F4FEEITEM_PK
resc_io: 139214.00 resc_cpu: 1411461448
ix_sel: 0.953821 ix_sel_with_filters: 0.953821
Cost: 13925.76 Resp: 13925.76 Degree: 1
Access Path: index (RangeScan)
Index: F4FEEITEM_POS_IX
resc_io: 10950.00 resc_cpu: 498037068
ix_sel: 0.953821 ix_sel_with_filters: 0.953821
Cost: 1096.54 Resp: 1096.54 Degree: 1
****** trying bitmap/domain indexes ******
****** finished trying bitmap/domain indexes ******
Best:: AccessPath: IndexRange
Index: F4FEEITEM_POS_IX
Cost: 1096.54 Degree: 1 Resp: 1096.54 Card: 341509.09
Bytes: 104
First K Rows: unchanged join prefix len = 1
Join order[3]: SETDETAILS[SD]#0 X4FEEITEM_INVOICE[XFI]#2 F4INVOICE
[FINV]#1 F4FEEITEM[F4]#3
***************
Now joining: X4FEEITEM_INVOICE[XFI]#2
***************
NL Join
Outer table: Card: 1.56 Cost: 1.00 Resp: 1.00 Degree: 1 Bytes:
38
Access path analysis for X4FEEITEM_INVOICE
Inner table: X4FEEITEM_INVOICE Alias: XFI
Access Path: TableScan
NL Join: Cost: 9.03 Resp: 9.03 Degree: 1
Cost_io: 9.00 Cost_cpu: 1013006
Resp_io: 9.00 Resp_cpu: 1013006
kkofmx: index filter:"SD"."SERV_PROV_CODE"="XFI"."SERV_PROV_CODE"
kkofmx: index filter:"SD"."SERV_PROV_CODE"="XFI"."SERV_PROV_CODE"
OPTIMIZER PERCENT INDEX CACHING = 90
Access Path: index (RangeScan)
Index: X4FEEITEM_INVOICE_NBR_IX
resc_io: 13.00 resc_cpu: 580816
ix_sel: 0.954312 ix_sel_with_filters: 0.954312
***** Logdef predicate Adjustment ******
Final IO cst 0.00 , CPU cst 0.00
***** End Logdef Adjustment ******
NL Join : Cost: 3.60 Resp: 3.60 Degree: 1
Cost_io: 3.60 Cost_cpu: 119102
Resp_io: 3.60 Resp_cpu: 119102
OPTIMIZER PERCENT INDEX CACHING = 90
Access Path: index (RangeScan)
Index: X4FEEITEM_INVOICE_PK
resc_io: 2.00 resc_cpu: 15073
ix_sel: 0.000004 ix_sel_with_filters: 0.000004
***** Logdef predicate Adjustment ******
Final IO cst 0.00 , CPU cst 0.00
***** End Logdef Adjustment ******
NL Join : Cost: 2.00 Resp: 2.00 Degree: 1
Cost_io: 2.00 Cost_cpu: 5953
Resp_io: 2.00 Resp_cpu: 5953
****** trying bitmap/domain indexes ******
****** finished trying bitmap/domain indexes ******
Best NL cost: 2.00
resc: 2.00 resc_io: 2.00 resc_cpu: 5953
resp: 2.00 resp_io: 2.00 resc_cpu: 5953
ColGroup cardinality sanity check: ndv for X4FEEITEM_INVOICE[XFI] =
1278471 SETDETAILS[SD] = 4
Join selectivity using 1 ColGroups: 0.007838 (sel1 = 0.000000, sel2 =
0.000000)
Join Card: 6.259204 = = outer (1.563739) * inner (510.711976) * sel
(0.007838)
Join Card - Rounded: 6 Computed: 6.26
Outer table: SETDETAILS Alias: SD
resc: 1.00 card 25.33 bytes: 38 deg: 1 resp: 1.00
Inner table: X4FEEITEM_INVOICE Alias: XFI
resc: 3039.37 card: 1187146.28 bytes: 131 deg: 1 resp: 3039.37
using dmeth: 2 #groups: 1
SORT ressource Sort statistics
Sort width: 238 Area size: 208896 Max Area size:
41943040
Degree: 1
Blocks to Sort: 1 Row size: 52 Total Rows: 25
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 32357899
Total Temp space used: 0
SORT ressource Sort statistics
Sort width: 238 Area size: 208896 Max Area size:
41943040
Degree: 1
Blocks to Sort: 22528 Row size: 155 Total Rows:
1187146
Initial runs: 5 Merge passes: 1 IO Cost / pass: 12204
Total IO sort cost: 34732 Total CPU sort cost: 1666431753
Total Temp space used: 442065000
SM join: Resc: 37824.88 Resp: 37824.88 [multiMatchCost=0.00]
SM Join
SM cost: 37824.88
resc: 37824.88 resc_io: 37768.20 resc_cpu: 1833709679
resp: 37824.88 resp_io: 37768.20 resp_cpu: 1833709679
Outer table: SETDETAILS Alias: SD
resc: 1.00 card 25.33 bytes: 38 deg: 1 resp: 1.00
Inner table: X4FEEITEM_INVOICE Alias: XFI
resc: 1.60 card: 510.71 bytes: 131 deg: 1 resp: 1.60
using dmeth: 2 #groups: 1
Cost per ptn: 0.50 #ptns: 1
hash_area: 124 (max=10240) buildfrag: 1 probefrag: 9 ppasses: 1
Hash join: Resc: 3.10 Resp: 3.10 [multiMatchCost=0.00]
HA Join
HA cost: 3.10
resc: 3.10 resc_io: 2.60 resc_cpu: 16295350
resp: 3.10 resp_io: 2.60 resp_cpu: 16295350
Best:: JoinMethod: NestedLoop
Cost: 2.00 Degree: 1 Resp: 2.00 Card: 6.26 Bytes: 169
.
- Follow-Ups:
- Re: performance issue after upgrade to oracle 11.2.0.1 linux 32 bit.
- From: Jonathan Lewis
- Re: performance issue after upgrade to oracle 11.2.0.1 linux 32 bit.
- References:
- performance issue after upgrade to oracle 11.2.0.1 linux 32 bit.
- From: lsllcm
- Re: performance issue after upgrade to oracle 11.2.0.1 linux 32 bit.
- From: Charles Hooper
- Re: performance issue after upgrade to oracle 11.2.0.1 linux 32 bit.
- From: joel garry
- Re: performance issue after upgrade to oracle 11.2.0.1 linux 32 bit.
- From: Charles Hooper
- Re: performance issue after upgrade to oracle 11.2.0.1 linux 32 bit.
- From: lsllcm
- performance issue after upgrade to oracle 11.2.0.1 linux 32 bit.
- Prev by Date: Re: performance issue after upgrade to oracle 11.2.0.1 linux 32 bit.
- Next by Date: Re: performance issue after upgrade to oracle 11.2.0.1 linux 32 bit.
- Previous by thread: Re: performance issue after upgrade to oracle 11.2.0.1 linux 32 bit.
- Next by thread: Re: performance issue after upgrade to oracle 11.2.0.1 linux 32 bit.
- Index(es):
Relevant Pages
|
