performance issue after upgrade to oracle 11.2.0.1 linux 32 bit.
in [Oracle]
|
From: Charles Hooper on 3 Nov 2009 13:11 On Nov 3, 12:44 pm, joel garry <joel-ga...(a)home.com> wrote: > On Nov 3, 8:22 am, Charles Hooper <hooperc2...(a)yahoo.com> wrote: > > > On Nov 3, 10:29 am, lsllcm <lsl...(a)gmail.com> wrote: > > > Note also that it is possible that the object statistics and optimizer > > parameters (FIRST_ROWS_n, for example) differ between the Oracle > > versions. > > > David's suggestion of a 10053 trace is a good one - just keep in mind > > that such traces only appear during a hard parse. > > A bit of a stretch, but consider this: rownum is assigned after the > predicate is processed, but before sorting is done. "Bug 6438892 : > Suboptimal plan with ROWNUM predicate and NESTED LOOPS." is in the > 11.1.0.6 patch set, bug not published. So maybe there is some > mysterious optimizer bug here, set off by the order by even though > rownum is not originally a predicate. Try patching beyond the base > release and see if the problem is still there. > > jg > -- The OP is experiencing problems in 11.2.0.1, which does not exhibit the ROWNUM bug that you mentioned, based on my testing. You might be suggesting - what if the fix of that bug caused another bug? Interesting, quite possible. I noticed the large number of NESTED LOOPS in his posted plan also, which is one of the reasons why I mentioned FIRST_ROWS_n (OPTIMIZER_MODE). It might even be the case that the OPTIMIZER_MODE is set to FIRST_ROWS (or maybe even CHOOSE), rather than FIRST_ROWS_1, FIRST_ROWS_10, FIRST_ROWS_100, ALL_ROWS, etc. lsllcm, please try setting OPTIMIZER_FEATURES_ENABLE to 11.1.0.6 in Oracle 11.2.0.1 and re-execute the SQL statement - note that this will force a hard parse. How does the performance compare? If the performance is better, set OPTIMIZER_FEATURES_ENABLE to 11.2.0.1, add a couple extra spaces in the SQL statement, and try your test again. Is the performance the same as it was on 11.1.0.6 and 10.2.0.4? Charles Hooper IT Manager/Oracle DBA K&M Machine-Fabricating, Inc.
From: joel garry on 3 Nov 2009 19:38 On Nov 3, 10:11 am, Charles Hooper <hooperc2...(a)yahoo.com> wrote: > On Nov 3, 12:44 pm, joel garry <joel-ga...(a)home.com> wrote: > > > > > On Nov 3, 8:22 am, Charles Hooper <hooperc2...(a)yahoo.com> wrote: > > > > On Nov 3, 10:29 am, lsllcm <lsl...(a)gmail.com> wrote: > > > > Note also that it is possible that the object statistics and optimizer > > > parameters (FIRST_ROWS_n, for example) differ between the Oracle > > > versions. > > > > David's suggestion of a 10053 trace is a good one - just keep in mind > > > that such traces only appear during a hard parse. > > > A bit of a stretch, but consider this: rownum is assigned after the > > predicate is processed, but before sorting is done. "Bug 6438892 : > > Suboptimal plan with ROWNUM predicate and NESTED LOOPS." is in the > > 11.1.0.6 patch set, bug not published. So maybe there is some > > mysterious optimizer bug here, set off by the order by even though > > rownum is not originally a predicate. Try patching beyond the base > > release and see if the problem is still there. > > > jg > > -- > > The OP is experiencing problems in 11.2.0.1, which does not exhibit > the ROWNUM bug that you mentioned, based on my testing. You might be > suggesting - what if the fix of that bug caused another bug? > Interesting, quite possible. Uh yeah, that's the ticket. (OK, I admit, somewhere along the line I bugeyed the 11.2.0.1 to 11.1...) > > I noticed the large number of NESTED LOOPS in his posted plan also, > which is one of the reasons why I mentioned FIRST_ROWS_n > (OPTIMIZER_MODE). It might even be the case that the OPTIMIZER_MODE > is set to FIRST_ROWS (or maybe even CHOOSE), rather than FIRST_ROWS_1, > FIRST_ROWS_10, FIRST_ROWS_100, ALL_ROWS, etc. > > lsllcm, please try setting OPTIMIZER_FEATURES_ENABLE to 11.1.0.6 in > Oracle 11.2.0.1 and re-execute the SQL statement - note that this will > force a hard parse. How does the performance compare? If the > performance is better, set OPTIMIZER_FEATURES_ENABLE to 11.2.0.1, add > a couple extra spaces in the SQL statement, and try your test again. > Is the performance the same as it was on 11.1.0.6 and 10.2.0.4? That may be informative. jg -- @home.com is bogus. http://users.rcn.com/eslowry/inexcus.htm
From: lsllcm on 3 Nov 2009 20:46 On Nov 4, 2:11 am, Charles Hooper <hooperc2...(a)yahoo.com> wrote: > On Nov 3, 12:44 pm, joel garry <joel-ga...(a)home.com> wrote: > > > > > > > On Nov 3, 8:22 am, Charles Hooper <hooperc2...(a)yahoo.com> wrote: > > > > On Nov 3, 10:29 am, lsllcm <lsl...(a)gmail.com> wrote: > > > > Note also that it is possible that the object statistics and optimizer > > > parameters (FIRST_ROWS_n, for example) differ between the Oracle > > > versions. > > > > David's suggestion of a 10053 trace is a good one - just keep in mind > > > that such traces only appear during a hard parse. > > > A bit of a stretch, but consider this: rownum is assigned after the > > predicate is processed, but before sorting is done. "Bug 6438892 : > > Suboptimal plan with ROWNUM predicate and NESTED LOOPS." is in the > > 11.1.0.6 patch set, bug not published. So maybe there is some > > mysterious optimizer bug here, set off by the order by even though > > rownum is not originally a predicate. Try patching beyond the base > > release and see if the problem is still there. > > > jg > > -- > > The OP is experiencing problems in 11.2.0.1, which does not exhibit > the ROWNUM bug that you mentioned, based on my testing. You might be > suggesting - what if the fix of that bug caused another bug? > Interesting, quite possible. > > I noticed the large number of NESTED LOOPS in his posted plan also, > which is one of the reasons why I mentioned FIRST_ROWS_n > (OPTIMIZER_MODE). It might even be the case that the OPTIMIZER_MODE > is set to FIRST_ROWS (or maybe even CHOOSE), rather than FIRST_ROWS_1, > FIRST_ROWS_10, FIRST_ROWS_100, ALL_ROWS, etc. > > lsllcm, please try setting OPTIMIZER_FEATURES_ENABLE to 11.1.0.6 in > Oracle 11.2.0.1 and re-execute the SQL statement - note that this will > force a hard parse. How does the performance compare? If the > performance is better, set OPTIMIZER_FEATURES_ENABLE to 11.2.0.1, add > a couple extra spaces in the SQL statement, and try your test again. > Is the performance the same as it was on 11.1.0.6 and 10.2.0.4? > > Charles Hooper > IT Manager/Oracle DBA > K&M Machine-Fabricating, Inc.- Hide quoted text - > > - Show quoted text - Thank you All at first. I have done one quick test after set OPTIMIZER_FEATURES_ENABLE to 11.1.0.6 again, the performance is same as the value to 11.2.0.1. I will double test it again and get 10053 trace file. Thanks
From: lsllcm on 4 Nov 2009 07:01 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(a)localhost.localdomain *** 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: F4INVOIC
From: lsllcm on 4 Nov 2009 07:02
part-2 ===================================================== *************** Now joining: F4INVOICE[FINV]#1 *************** NL Join Outer table: Card: 6.26 Cost: 2.00 Resp: 2.00 Degree: 1 Bytes: 169 Access path analysis for F4INVOICE Inner table: F4INVOICE Alias: FINV Access Path: TableScan NL Join: Cost: 4788.36 Resp: 4788.36 Degree: 1 Cost_io: 4770.00 Cost_cpu: 594128854 Resp_io: 4770.00 Resp_cpu: 594128854 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.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: 3.00 Resp: 3.00 Degree: 1 Cost_io: 3.00 Cost_cpu: 11030 Resp_io: 3.00 Resp_cpu: 11030 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: 3052.00 resc_cpu: 120046275 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: 1835.43 Resp: 1835.43 Degree: 1 Cost_io: 1833.20 Cost_cpu: 72033718 Resp_io: 1833.20 Resp_cpu: 72033718 OPTIMIZER PERCENT INDEX CACHING = 90 Access Path: index (RangeScan) Index: F4INVOICE_BATCH_NBR_IX resc_io: 2454.00 resc_cpu: 114871354 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: 1476.53 Resp: 1476.53 Degree: 1 Cost_io: 1474.40 Cost_cpu: 68928765 Resp_io: 1474.40 Resp_cpu: 68928765 OPTIMIZER PERCENT INDEX CACHING = 90 Access Path: index (RangeScan) Index: F4INVOICE_DATE_IX resc_io: 2999.00 resc_cpu: 119206769 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: 1803.61 Resp: 1803.61 Degree: 1 Cost_io: 1801.40 Cost_cpu: 71530014 Resp_io: 1801.40 Resp_cpu: 71530014 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: 3.00 Resp: 3.00 Degree: 1 Cost_io: 3.00 Cost_cpu: 11030 Resp_io: 3.00 Resp_cpu: 11030 ****** trying bitmap/domain indexes ****** ****** finished trying bitmap/domain indexes ****** Best NL cost: 3.00 resc: 3.00 resc_io: 3.00 resc_cpu: 11030 resp: 3.00 resp_io: 3.00 resc_cpu: 11030 Join Card: 6.259212 = = outer (6.259204) * inner (200076.276865) * sel (0.000005) Join Card - Rounded: 6 Computed: 6.26 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: 301.87 card: 200076.28 bytes: 24 deg: 1 resp: 301.87 using dmeth: 2 #groups: 1 Cost per ptn: 847.29 #ptns: 1 hash_area: 124 (max=10240) buildfrag: 1302 probefrag: 880 ppasses: 1 Hash join: Resc: 1155.16 Resp: 1155.16 [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: 2.00 card: 6.26 bytes: 169 deg: 1 resp: 2.00 using dmeth: 2 #groups: 1 Cost per ptn: 426.79 #ptns: 1 hash_area: 124 (max=10240) buildfrag: 1094 probefrag: 1 ppasses: 1 Hash join: Resc: 803.95 Resp: 803.95 [multiMatchCost=0.00] HA Join HA cost: 803.95 swapped resc: 803.95 resc_io: 801.70 resc_cpu: 72763912 resp: 803.95 resp_io: 801.70 resp_cpu: 72763912 Best:: JoinMethod: NestedLoop Cost: 3.00 Degree: 1 Resp: 3.00 Card: 6.26 Bytes: 193 *************** Now joining: F4FEEITEM[F4]#3 *************** NL Join Outer table: Card: 6.26 Cost: 3.00 Resp: 3.00 Degree: 1 Bytes: 193 Access path analysis for F4FEEITEM Inner table: F4FEEITEM Alias: F4 Access Path: TableScan NL Join: Cost: 16128.30 Resp: 16128.30 Degree: 1 Cost_io: 16046.00 Cost_cpu: 2662583604 Resp_io: 16046.00 Resp_cpu: 2662583604 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: 4.00 Resp: 4.00 Degree: 1 Cost_io: 4.00 Cost_cpu: 16551 Resp_io: 4.00 Resp_cpu: 16551 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: 4.00 Resp: 4.00 Degree: 1 Cost_io: 4.00 Cost_cpu: 16551 Resp_io: 4.00 Resp_cpu: 16551 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: 5682.64 Resp: 5682.64 Degree: 1 Cost_io: 5673.60 Cost_cpu: 292428248 Resp_io: 5673.60 Resp_cpu: 292428248 ****** trying bitmap/domain indexes ****** ****** finished trying bitmap/domain indexes ****** Best NL cost: 4.00 resc: 4.00 resc_io: 4.00 resc_cpu: 16551 resp: 4.00 resp_io: 4.00 resc_cpu: 16551 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: 625.922744 = = outer (6.259212) * inner (341509.085948) * sel (0.000293) Join Card - Rounded: 626 Computed: 625.92 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: 21647.99 Resp: 21647.99 [multiMatchCost=0.00] SM Join SM cost: 21647.99 resc: 21647.99 resc_io: 21593.20 resc_cpu: 1772479806 resp: 21647.99 resp_io: 21593.20 resp_cpu: 1772479806 Outer table: F4INVOICE Alias: FINV resc: 1311.52 card 73255.04 bytes: 193 deg: 1 resp: 1311.52 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: 1585.47 #ptns: 1 hash_area: 124 (max=10240) buildfrag: 1834 probefrag: 2252 ppasses: 1 Hash join: Resc: 3993.53 Resp: 3993.53 [multiMatchCost=0.00] HA Join HA cost: 3993.53 resc: 3993.53 resc_io: 3985.70 resc_cpu: 253386280 resp: 3993.53 resp_io: 3985.70 resp_cpu: 253386280 ORDER BY sort First K Rows: switch to Amode plans 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 *********************** Join order[4]: SETDETAILS[SD]#0 X4FEEITEM_INVOICE[XFI]#2 F4FEEITEM [F4]#3 F4INVOICE[FINV]#1 *************** Now joining: F4FEEITEM[F4]#3 *************** NL Join Outer table: Card: 58922.73 Cost: 6.00 Resp: 6.00 Degree: 1 Bytes: 169 Access path analysis for F4FEEITEM Inner table: F4FEEITEM Alias: F4 Access Path: TableScan NL Join: Cost: 570982520.22 Resp: 570982520.22 Degree: 1 Cost_io: 568068058.00 Cost_cpu: 94290629250968 Resp_io: 568068058.00 Resp_cpu: 94290629250968 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: 5899.98 Resp: 5899.98 Degree: 1 Cost_io: 5898.30 Cost_cpu: 54259368 Resp_io: 5898.30 Resp_cpu: 54259368 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: 5899.98 Resp: 5899.98 Degree: 1 Cost_io: 5898.30 Cost_cpu: 54259368 Resp_io: 5898.30 Resp_cpu: 54259368 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: 201070736.09 Resp: 201070736.09 Degree: 1 Cost_io: 200750667.00 Cost_cpu: 10355089000488 Resp_io: 200750667.00 Resp_cpu: 10355089000488 ****** trying bitmap/domain indexes ****** ****** finished trying bitmap/domain indexes ****** Best NL cost: 5899.98 resc: 5899.98 resc_io: 5898.30 resc_cpu: 54259368 resp: 5899.98 resp_io: 5898.30 resc_cpu: 54259368 ColGroup cardinality sanity check: ndv for X4FEEITEM_INVOICE[XFI] = 192 F4FEEITEM[F4] = 1291140 Join selectivity using 1 ColGroups: 0.000001 (sel1 = 0.000000, sel2 = 0.000000) Join Card: 58922.727880 = = outer (58922.727880) * inner (1231517.000000) * sel (0.000001) Join Card - Rounded: 58923 Computed: 58922.73 Outer table: X4FEEITEM_INVOICE Alias: XFI resc: 6.00 card 58922.73 bytes: 169 deg: 1 resp: 6.00 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: 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: 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: 19439.67 Resp: 19439.67 [multiMatchCost=0.00] SM Join SM cost: 19439.67 resc: 19439.67 resc_io: 19388.50 resc_cpu: 1655467992 resp: 19439.67 resp_io: 19388.50 resp_cpu: 1655467992 Outer table: X4FEEITEM_INVOICE Alias: XFI resc: 6.00 card 58922.73 bytes: 169 deg: 1 resp: 6.00 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: 3656.58 #ptns: 1 hash_area: 124 (max=10240) buildfrag: 1302 probefrag: 8118 ppasses: 1 Hash join: Resc: 7615.63 Resp: 7615.63 [multiMatchCost=0.00] HA Join HA cost: 7615.63 resc: 7615.63 resc_io: 7600.50 resc_cpu: 489593477 resp: 7615.63 resp_io: 7600.50 resp_cpu: 489593477 Best:: JoinMethod: NestedLoop Cost: 5899.98 Degree: 1 Resp: 5899.98 Card: 58922.73 Bytes: 211 *************** Now joining: F4INVOICE[FINV]#1 *************** NL Join Outer table: Card: 58922.73 Cost: 5899.98 Resp: 5899.98 Degree: 1 Bytes: 211 Access path analysis for F4INVOICE Inner table: F4INVOICE Alias: FINV Access Path: TableScan NL Join: Cost: 58414113.01 Resp: 58414113.01 Degree: 1 Cost_io: 58189907.30 Cost_cpu: 7253652931740 Resp_io: 58189907.30 Resp_cpu: 7253652931740 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: 11793.82 Resp: 11793.82 Degree: 1 Cost_io: 11790.60 Cost_cpu: 104116711 Resp_io: 11790.60 Resp_cpu: 104116711 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: 22382573.85 Resp: 22382573.85 Degree: 1 Cost_io: 22355392.20 Cost_cpu: 879398967633 Resp_io: 22355392.20 Resp_cpu: 879398967633 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: 18003423.79 Resp: 18003423.79 Degree: 1 Cost_io: 17977413.30 Cost_cpu: 841508650801 Resp_io: 17977413.30 Resp_cpu: 841508650801 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: 21987598.17 Resp: 21987598.17 Degree: 1 Cost_io: 21960608.10 Cost_cpu: 873200819081 Resp_io: 21960608.10 Resp_cpu: 873200819081 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: 11793.82 Resp: 11793.82 Degree: 1 Cost_io: 11790.60 Cost_cpu: 104116711 Resp_io: 11790.60 Resp_cpu: 104116711 ****** trying bitmap/domain indexes ****** ****** finished trying bitmap/domain indexes ****** Best NL cost: 11793.82 resc: 11793.82 resc_io: 11790.60 resc_cpu: 104116711 resp: 11793.82 resp_io: 11790.60 resc_cpu: 104116711 Join Card: 73255.035134 = = outer (58922.727880) * inner (248742.292433) * sel (0.000005) Join Card - Rounded: 73255 Computed: 73255.04 Outer table: F4FEEITEM Alias: F4 resc: 5899.98 card 58922.73 bytes: 211 deg: 1 resp: 5899.98 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: 1753 Row size: 243 Total Rows: 58923 Initial runs: 2 Merge passes: 1 IO Cost / pass: 952 Total IO sort cost: 2705 Total CPU sort cost: 117590661 Total Temp space used: 27599000 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: 10730.84 Resp: 10730.84 [multiMatchCost=0.00] SM Join SM cost: 10730.84 resc: 10730.84 resc_io: 10717.00 resc_cpu: 447610002 resp: 10730.84 resp_io: 10717.00 resp_cpu: 447610002 Outer table: F4FEEITEM Alias: F4 resc: 5899.98 card 58922.73 bytes: 211 deg: 1 resp: 5899.98 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: 1047.65 #ptns: 1 hash_area: 124 (max=10240) buildfrag: 1604 probefrag: 1094 ppasses: 1 Hash join: Resc: 7322.79 Resp: 7322.79 [multiMatchCost=0.00] Outer table: F4INVOICE Alias: FINV resc: 375.16 card 248742.29 bytes: 24 deg: 1 resp: 375.16 Inner table: F4FEEITEM Alias: F4 resc: 5899.98 card: 58922.73 bytes: 211 deg: 1 resp: 5899.98 using dmeth: 2 #groups: 1 Cost per ptn: 1047.36 #ptns: 1 hash_area: 124 (max=10240) buildfrag: 1094 probefrag: 1604 ppasses: 1 Hash join: Resc: 7322.54 Resp: 7322.54 [multiMatchCost=0.04] HA Join HA cost: 7322.54 swapped resc: 7322.54 resc_io: 7318.00 resc_cpu: 146810157 resp: 7322.54 resp_io: 7318.00 resp_cpu: 146810157 ORDER BY sort SORT ressource Sort statistics Sort width: 238 Area size: 208896 Max Area size: 41943040 Degree: 1 Blocks to Sort: 2413 Row size: 269 Total Rows: 73255 Initial runs: 2 Merge passes: 1 IO Cost / pass: 1308 Total IO sort cost: 3721 Total CPU sort cost: 145112949 Total Temp space used: 18768000 Best:: JoinMethod: Hash Cost: 11048.02 Degree: 1 Resp: 11048.02 Card: 73255.04 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#: 3 cost: 5899.9771 card: 58922.7279 bytes: 12432753 Table#: 1 cost: 11048.0232 card: 73255.0351 bytes: 17214925 *********************** First K Rows: K = 100.00, N = 73255.00 First K Rows: old pf = 0.0004299, new pf = 0.0356842 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: 45622.000000 Rounded: 42363 Computed: 42363.09 Non Adjusted: 42363.09 Access Path: TableScan Cost: 248.29 Resp: 248.29 Degree: 0 Cost_io: 247.00 Cost_cpu: 41646782 Resp_io: 247.00 Resp_cpu: 41646782 Access Path: index (RangeScan) Index: X4FEEITEM_INVOICE_NBR_IX resc_io: 1087.00 resc_cpu: 48172582 ix_sel: 0.954312 ix_sel_with_filters: 0.954312 Cost: 108.85 Resp: 108.85 Degree: 1 Access Path: index (RangeScan) Index: X4FEEITEM_INVOICE_PK resc_io: 7579.00 resc_cpu: 94404970 ix_sel: 0.954312 ix_sel_with_filters: 0.954312 Cost: 758.19 Resp: 758.19 Degree: 1 ****** trying bitmap/domain indexes ****** ****** finished trying bitmap/domain indexes ****** Best:: AccessPath: IndexRange Index: X4FEEITEM_INVOICE_NBR_IX Cost: 108.85 Degree: 1 Resp: 108.85 Card: 42363.09 Bytes: 169 First K Rows: old pf = 0.2773070, new pf = 0.9879518 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: 1275585.000000 Rounded: 1216680 Computed: 1216680.31 Non Adjusted: 1216680.31 Access Path: TableScan Cost: 9575.72 Resp: 9575.72 Degree: 0 Cost_io: 9527.00 Cost_cpu: 1576249323 Resp_io: 9527.00 Resp_cpu: 1576249323 Access Path: index (RangeScan) Index: F4FEEITEM_PK resc_io: 495964.00 resc_cpu: 5028495498 ix_sel: 0.953821 ix_sel_with_filters: 0.953821 Cost: 49611.94 Resp: 49611.94 Degree: 1 Access Path: index (RangeScan) Index: F4FEEITEM_POS_IX resc_io: 39001.00 resc_cpu: 1774260911 ix_sel: 0.953821 ix_sel_with_filters: 0.953821 Cost: 3905.58 Resp: 3905.58 Degree: 1 ****** trying bitmap/domain indexes ****** ****** finished trying bitmap/domain indexes ****** Best:: AccessPath: IndexRange Index: F4FEEITEM_POS_IX Cost: 3905.58 Degree: 1 Resp: 3905.58 Card: 1216680.31 Bytes: 235 First K Rows: old pf = 0.8043506, new pf = 1.0000000 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: 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: 193 First K Rows: unchanged join prefix len = 2 Join order[4]: SETDETAILS[SD]#0 X4FEEITEM_INVOICE[XFI]#2 F4FEEITEM [F4]#3 F4INVOICE[FINV]#1 *************** 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: 496.59 Resp: 496.59 Degree: 1 Cost_io: 494.00 Cost_cpu: 83707754 Resp_io: 494.00 Resp_cpu: 83707754 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: 954.00 resc_cpu: 47284154 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: 192.09 Resp: 192.09 Degree: 1 Cost_io: 191.80 Cost_cpu: 9459769 Resp_io: 191.80 Resp_cpu: 9459769 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 ****** 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: 519.195260 = = outer (1.563739) * inner (42363.094163) * sel (0.007838) Join Card - Rounded: 519 Computed: 519.20 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: 108.85 card: 42363.09 bytes: 131 deg: 1 resp: 108.85 using dmeth: 2 #groups: 1 Cost per ptn: 0.63 #ptns: 1 hash_area: 124 (max=10240) buildfrag: 1 probefrag: 740 ppasses: 1 Hash join: Resc: 110.48 Resp: 110.48 [multiMatchCost=0.00] HA Join HA cost: 110.48 resc: 110.48 resc_io: 109.70 resc_cpu: 25237661 resp: 110.48 resp_io: 109.70 resp_cpu: 25237661 Best:: JoinMethod: NestedLoop Cost: 2.00 Degree: 1 Resp: 2.00 Card: 519.20 Bytes: 169 *************** Now joining: F4FEEITEM[F4]#3 *************** NL Join Outer table: Card: 519.20 Cost: 2.00 Resp: 2.00 Degree: 1 Bytes: 169 Access path analysis for F4FEEITEM Inner table: F4FEEITEM Alias: F4 Access Path: TableScan NL Join: Cost: 4968807.71 Resp: 4968807.71 Degree: 1 Cost_io: 4943446.00 Cost_cpu: 820519140377 Resp_io: 4943446.00 Resp_cpu: 820519140377 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: 53.91 Resp: 53.91 Degree: 1 Cost_io: 53.90 Cost_cpu: 483508 Resp_io: 53.90 Resp_cpu: 483508 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: 53.91 Resp: 53.91 Degree: 1 Cost_io: 53.90 Cost_cpu: 483508 Resp_io: 53.90 Resp_cpu: 483508 OPTIMIZER PERCENT INDEX CACHING = 90 Access Path: index (RangeScan) Index: F4FEEITEM_POS_IX resc_io: 33659.00 resc_cpu: 1736218179 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: 1749689.33 Resp: 1749689.33 Degree: 1 Cost_io: 1746904.10 Cost_cpu: 90109729441 Resp_io: 1746904.10 Resp_cpu: 90109729441 ****** trying bitmap/domain indexes ****** ****** finished trying bitmap/domain indexes ****** Best NL cost: 53.91 resc: 53.91 resc_io: 53.90 resc_cpu: 483508 resp: 53.91 resp_io: 53.90 resc_cpu: 483508 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: 184971.994232 = = outer (519.195260) * inner (1216680.307670) * sel (0.000293) Join Card - Rounded: 184972 Computed: 184971.99 Outer table: X4FEEITEM_INVOICE Alias: XFI resc: 6.00 card 58922.73 bytes: 169 deg: 1 resp: 6.00 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: 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: 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: 19439.67 Resp: 19439.67 [multiMatchCost=0.00] SM Join SM cost: 19439.67 resc: 19439.67 resc_io: 19388.50 resc_cpu: 1655467992 resp: 19439.67 resp_io: 19388.50 resp_cpu: 1655467992 Outer table: X4FEEITEM_INVOICE Alias: XFI resc: 6.00 card 58922.73 bytes: 169 deg: 1 resp: 6.00 Inner table: F4FEEITEM Alias: F4 resc: 3905.58 card: 1216680.31 bytes: 42 deg: 1 resp: 3905.58 using dmeth: 2 #groups: 1 Cost per ptn: 3619.48 #ptns: 1 hash_area: 124 (max=10240) buildfrag: 1302 probefrag: 8021 ppasses: 1 Hash join: Resc: 7531.06 Resp: 7531.06 [multiMatchCost=0.00] HA Join HA cost: 7531.06 resc: 7531.06 resc_io: 7516.10 resc_cpu: 484065755 resp: 7531.06 resp_io: 7516.10 resp_cpu: 484065755 Best:: JoinMethod: NestedLoop Cost: 53.91 Degree: 1 Resp: 53.91 Card: 184971.99 Bytes: 211 *************** Now joining: F4INVOICE[FINV]#1 *************** NL Join Outer table: Card: 184971.99 Cost: 53.91 Resp: 53.91 Degree: 1 Bytes: 211 Access path analysis for F4INVOICE Inner table: F4INVOICE Alias: FINV Access Path: TableScan NL Join: Cost: 183356022.71 Resp: 183356022.71 Degree: 1 Cost_io: 182652197.90 Cost_cpu: 22770610493621 Resp_io: 182652197.90 Resp_cpu: 22770610493621 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: 18555.95 Resp: 18555.95 Degree: 1 Cost_io: 18551.10 Cost_cpu: 156996456 Resp_io: 18551.10 Resp_cpu: 156996456 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: 70245257.32 Resp: 70245257.32 Degree: 1 Cost_io: 70159933.50 Cost_cpu: 2760453097551 Resp_io: 70159933.50 Resp_cpu: 2760453097551 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: 56498161.18 Resp: 56498161.18 Degree: 1 Cost_io: 56416513.90 Cost_cpu: 2641507224374 Resp_io: 56416513.90 Resp_cpu: 2641507224374 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: 69005343.50 Resp: 69005343.50 Degree: 1 Cost_io: 68920621.10 Cost_cpu: 2740995773009 Resp_io: 68920621.10 Resp_cpu: 2740995773009 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: 18555.95 Resp: 18555.95 Degree: 1 Cost_io: 18551.10 Cost_cpu: 156996456 Resp_io: 18551.10 Resp_cpu: 156996456 ****** trying bitmap/domain indexes ****** ****** finished trying bitmap/domain indexes ****** Best NL cost: 18555.95 resc: 18555.95 resc_io: 18551.10 resc_cpu: 156996456 resp: 18555.95 resp_io: 18551.10 resc_cpu: 156996456 Join Card: 229964.402933 = = outer (184971.994232) * inner (248742.292433) * sel (0.000005) Join Card - Rounded: 229964 Computed: 229964.40 Outer table: F4FEEITEM Alias: F4 resc: 5899.98 card 58922.73 bytes: 211 deg: 1 resp: 5899.98 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: 1753 Row size: 243 Total Rows: 58923 Initial runs: 2 Merge passes: 1 IO Cost / pass: 952 Total IO sort cost: 2705 Total CPU sort cost: 117590661 Total Temp space used: 27599000 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: 10730.84 Resp: 10730.84 [multiMatchCost=0.00] SM Join SM cost: 10730.84 resc: 10730.84 resc_io: 10717.00 resc_cpu: 447610002 resp: 10730.84 resp_io: 10717.00 resp_cpu: 447610002 Outer table: F4FEEITEM Alias: F4 resc: 5899.98 card 58922.73 bytes: 211 deg: 1 resp: 5899.98 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: 1047.65 #ptns: 1 hash_area: 124 (max=10240) buildfrag: 1604 probefrag: 1094 ppasses: 1 Hash join: Resc: 7322.79 Resp: 7322.79 [multiMatchCost=0.00] Outer table: F4INVOICE Alias: FINV resc: 375.16 card 248742.29 bytes: 24 deg: 1 resp: 375.16 Inner table: F4FEEITEM Alias: F4 resc: 53.91 card: 184971.99 bytes: 211 deg: 1 resp: 53.91 using dmeth: 2 #groups: 1 Cost per ptn: 2377.57 #ptns: 1 hash_area: 124 (max=10240) buildfrag: 1094 probefrag: 5036 ppasses: 1 Hash join: Resc: 2806.78 Resp: 2806.78 [multiMatchCost=0.14] HA Join HA cost: 2806.78 swapped resc: 2806.78 resc_io: 2802.60 resc_cpu: 135389711 resp: 2806.78 resp_io: 2802.60 resp_cpu: 135389711 First K Rows: copy A one plan, tab=F4INVOICE ORDER BY sort First K Rows: switch to Amode plans Best:: JoinMethod: Hash Cost: 11048.02 Degree: 1 Resp: 11048.02 Card: 73255.04 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#: 3 cost: 5899.9771 card: 58922.7279 bytes: 12432753 Table#: 1 cost: 11048.0232 card: 73255.0351 bytes: 17214925 *********************** Join order[5]: SETDETAILS[SD]#0 F4FEEITEM[F4]#3 F4INVOICE[FINV]#1 X4FEEITEM_INVOICE[XFI]#2 *************** Now joining: F4FEEITEM[F4]#3 *************** NL Join Outer table: Card: 25.33 Cost: 1.00 Resp: 1.00 Degree: 1 Bytes: 38 Access path analysis for F4FEEITEM Inner table: F4FEEITEM Alias: F4 Access Path: TableScan NL Join: Cost: 242256.87 Resp: 242256.87 Degree: 1 Cost_io: 241024.00 Cost_cpu: 39886625511 Resp_io: 241024.00 Resp_cpu: 39886625511 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: 1255421.81 Resp: 1255421.81 Degree: 1 Cost_io: 1255028.50 Cost_cpu: 12724521833 Resp_io: 1255028.50 Resp_cpu: 12724521833 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: 98827.27 Resp: 98827.27 Degree: 1 Cost_io: 98688.50 Cost_cpu: 4489715904 Resp_io: 98688.50 Resp_cpu: 4489715904 ****** trying bitmap/domain indexes ****** ****** finished trying bitmap/domain indexes ****** Best NL cost: 98827.27 resc: 98827.27 resc_io: 98688.50 resc_cpu: 4489715904 resp: 98827.27 resp_io: 98688.50 resc_cpu: 4489715904 Join Card: 31197592.655682 = = outer (25.332653) * inner (1231517.000000) * sel (1.000000) Join Card - Rounded: 31197593 Computed: 31197592.66 Best:: JoinMethod: NestedLoop Cost: 98827.27 Degree: 1 Resp: 98827.27 Card: 31197592.66 Bytes: 80 *************** Now joining: F4INVOICE[FINV]#1 *************** NL Join Outer table: Card: 31197592.66 Cost: 98827.27 Resp: 98827.27 Degree: 1 Bytes: 80 Access path analysis for F4INVOICE Inner table: F4INVOICE Alias: FINV Access Path: TableScan NL Join: Cost: 30924028352.03 Resp: 30924028352.03 Degree: 1 Cost_io: 30806421878.50 Cost_cpu: 3804883217918776 Resp_io: 30806421878.50 Resp_cpu: 3804883217918776 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: 14827762611.55 Resp: 14827762611.55 Degree: 1 Cost_io: 14812715844.90 Cost_cpu: 486803048972143 Resp_io: 14812715844.90 Resp_cpu: 486803048972143 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: 11704086289.36 Resp: 11704086289.36 Degree: 1 Cost_io: 11689836785.60 Cost_cpu: 461009467341556 Resp_io: 11689836785.60 Resp_cpu: 461009467341556 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: 14500060354.67 Resp: 14500060354.67 Degree: 1 Cost_io: 14485141118.40 Cost_cpu: 482677101158996 Resp_io: 14485141118.40 Resp_cpu: 482677101158996 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: 279965857199.73 Resp: 279965857199.73 Degree: 1 Cost_io: 279892424047.30 Cost_cpu: 2375758416296015 Resp_io: 279892424047.30 Resp_cpu: 2375758416296015 ****** trying bitmap/domain indexes ****** ****** finished trying bitmap/domain indexes ****** Best NL cost: 11704086289.36 resc: 11704086289.36 resc_io: 11689836785.60 resc_cpu: 461009467341556 resp: 11704086289.36 resp_io: 11689836785.60 resc_cpu: 461009467341556 Join Card: 7760160715565.263672 = = outer (31197592.655682) * inner (248742.292433) * sel (1.000000) Join Card - Rounded: 7760160715565 Computed: 7760160715565.26 Best:: JoinMethod: NestedLoop Cost: 11704086289.36 Degree: 1 Resp: 11704086289.36 Card: 7760160715565.26 Bytes: 104 *************** Now joining: X4FEEITEM_INVOICE[XFI]#2 *************** NL Join Outer table: Card: 7760160715565.26 Cost: 11704086289.36 Resp: 11704086289.36 Degree: 1 Bytes: 104 Access path analysis for X4FEEITEM_INVOICE Inner table: X4FEEITEM_INVOICE Alias: XFI Access Path: TableScan NL Join: Cost: 53658429463261216.00 Resp: 53658429463261216.00 Degree: 1 Cost_io: 53377145481746288.00 Cost_cpu: 9100287327712668811264 Resp_io: 53377145481746288.00 Resp_cpu: 9100287327712668811264 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: 787933669153.16 Resp: 787933669153.16 Degree: 1 Cost_io: 787705908342.10 Cost_cpu: 7368669952162757 Resp_io: 787705908342.10 Resp_cpu: 7368669952162757 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: 788022687682.06 Resp: 788022687682.06 Degree: 1 Cost_io: 787705908342.10 Cost_cpu: 10248656882479164 Resp_io: 787705908342.10 Resp_cpu: 10248656882479164 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: 787933669153.16 Resp: 787933669153.16 Degree: 1 Cost_io: 787705908342.10 Cost_cpu: 7368669952162757 Resp_io: 787705908342.10 Resp_cpu: 7368669952162757 ****** trying bitmap/domain indexes ****** ****** finished trying bitmap/domain indexes ****** Best NL cost: 787933669153.16 resc: 787933669153.16 resc_io: 787705908342.10 resc_cpu: 7368669952162757 resp: 787933669153.16 resp_io: 787705908342.10 resc_cpu: 7368669952162757 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: F4INVOICE Alias: FINV resc: 11704086289.36 card 7760160715565.26 bytes: 104 deg: 1 resp: 11704086289.36 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: 1816321324639000 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: 21021319020.82 Resp: 21021319020.82 [multiMatchCost=0.00] SM Join SM cost: 21021319020.82 resc: 21021319020.82 resc_io: 20539010525.80 resc_cpu: 15603966715891094 resp: 21021319020.82 resp_io: 20539010525.80 resp_cpu: 15603966715891094 Outer table: F4INVOICE Alias: FINV resc: 11704086289.36 card 7760160715565.26 bytes: 104 deg: 1 resp: 11704086289.36 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: 54626844264.55 Resp: 54626844264.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: F4INVOICE Alias: FINV resc: 11704086289.36 card: 7760160715565.26 bytes: 104 deg: 1 resp: 11704086289.36 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: 54302166745.60 Resp: 54302166745.60 [multiMatchCost=0.00] HA Join HA cost: 54302166745.60 swapped resc: 54302166745.60 resc_io: 54226011039.80 resc_cpu: 2463840282191949 resp: 54302166745.60 resp_io: 54226011039.80 resp_cpu: 2463840282191949 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: 21022661825.04 Degree: 1 Resp: 21022661825.04 Card: 26416584.99 Bytes: 235 *********************** Best so far: Table#: 0 cost: 1.0001 card: 25.3327 bytes: 950 Table#: 3 cost: 98827.2742 card: 31197592.6557 bytes: 2495807440 Table#: 1 cost: 11704086289.3596 card: 7760160715565.2637 bytes: 807056714418760 Table#: 2 cost: 21022661825.0356 card: 26416584.9852 bytes: 6207897475 *********************** First K Rows: K = 100.00, N = 26416585.00 First K Rows: old pf = 0.9879518, new pf = 0.0000966 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: 125.000000 Rounded: 119 Computed: 119.23 Non Adjusted: 119.23 Access Path: TableScan Cost: 3.00 Resp: 3.00 Degree: 0 Cost_io: 3.00 Cost_cpu: 158206 Resp_io: 3.00 Resp_cpu: 158206 Access Path: index (RangeScan) Index: F4FEEITEM_PK resc_io: 52.00 resc_cpu: 517915 ix_sel: 0.953821 ix_sel_with_filters: 0.953821 Cost: 5.20 Resp: 5.20 Degree: 1 Access Path: index (RangeScan) Index: F4FEEITEM_POS_IX resc_io: 7.00 resc_cpu: 197450 ix_sel: 0.953821 ix_sel_with_filters: 0.953821 Cost: 1.00 Resp: 1.00 Degree: 1 ****** trying bitmap/domain indexes ****** ****** finished trying bitmap/domain indexes ****** Best:: AccessPath: IndexRange Index: F4FEEITEM_POS_IX Cost: 1.00 Degree: 1 Resp: 1.00 Card: 119.23 Bytes: 211 First K Rows: old pf = 0.0356842, 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: 169 First K Rows: unchanged join prefix len = 1 Join order[5]: SETDETAILS[SD]#0 F4FEEITEM[F4]#3 F4INVOICE[FINV]#1 X4FEEITEM_INVOICE[XFI]#2 *************** Now joining: F4FEEITEM[F4]#3 *************** NL Join Outer table: Card: 1.56 Cost: 1.00 Resp: 1.00 Degree: 1 Bytes: 38 Access path analysis for F4FEEITEM Inner table: F4FEEITEM Alias: F4 Access Path: TableScan NL Join: Cost: 5.01 Resp: 5.01 Degree: 1 Cost_io: 5.00 Cost_cpu: 319350 Resp_io: 5.00 Resp_cpu: 319350 Access Path: index (RangeScan) Index: F4FEEITEM_PK resc_io: 52.00 resc_cpu: 517915 ix_sel: 0.953821 ix_sel_with_filters: 0.953821 NL Join : Cost: 11.40 Resp: 11.40 Degree: 1 Cost_io: 11.40 Cost_cpu: 106522 Resp_io: 11.40 Resp_cpu: 106522 Access Path: index (RangeScan) Index: F4FEEITEM_POS_IX resc_io: 7.00 resc_cpu: 197450 ix_sel: 0.953821 ix_sel_with_filters: 0.953821 NL Join : Cost: 2.40 Resp: 2.40 Degree: 1 Cost_io: 2.40 Cost_cpu: 42429 Resp_io: 2.40 Resp_cpu: 42429 ****** trying bitmap/domain indexes ****** ****** finished trying bitmap/domain indexes ****** Best NL cost: 2.40 resc: 2.40 resc_io: 2.40 resc_cpu: 42429 resp: 2.40 resp_io: 2.40 resc_cpu: 42429 Join Card: 186.441017 = = outer (1.563739) * inner (119.227679) * sel (1.000000) Join Card - Rounded: 186 Computed: 186.44 Best:: JoinMethod: NestedLoop Cost: 2.40 Degree: 1 Resp: 2.40 Card: 186.44 Bytes: 80 *************** Now joining: F4INVOICE[FINV]#1 *************** NL Join Outer table: Card: 186.44 Cost: 2.40 Resp: 2.40 Degree: 1 Bytes: 80 Access path analysis for F4INVOICE Inner table: F4INVOICE Alias: FINV Access Path: TableScan NL Join: Cost: 184372.57 Resp: 184372.57 Degree: 1 Cost_io: 183671.40 Cost_cpu: 22684723373 Resp_io: 183671.40 Resp_cpu: 22684723373 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: 88404.91 Resp: 88404.91 Degree: 1 Cost_io: 88315.20 Cost_cpu: 2902334667 Resp_io: 88315.20 Resp_cpu: 2902334667 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: 69781.56 Resp: 69781.56 Degree: 1 Cost_io: 69696.60 Cost_cpu: 2748553374 Resp_io: 69696.60 Resp_cpu: 2748553374 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: 86451.15 Resp: 86451.15 Degree: 1 Cost_io: 86362.20 Cost_cpu: 2877735773 Resp_io: 86362.20 Resp_cpu: 2877735773 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: 1669157.81 Resp: 1669157.81 Degree: 1 Cost_io: 1668720.00 Cost_cpu: 14164283572 Resp_io: 1668720.00 Resp_cpu: 14164283572 ****** trying bitmap/domain indexes ****** ****** finished trying bitmap/domain indexes ****** Best NL cost: 69781.56 resc: 69781.56 resc_io: 69696.60 resc_cpu: 2748553374 resp: 69781.56 resp_io: 69696.60 resc_cpu: 2748553374 Join Card: 46375765.972939 = = outer (186.441017) * inner (248742.292433) * sel (1.000000) Join Card - Rounded: 46375766 Computed: 46375765.97 Join order aborted: cost > best plan cost *********************** Join order[6]: SETDETAILS[SD]#0 F4FEEITEM[F4]#3 X4FEEITEM_INVOICE [XFI]#2 F4INVOICE[FINV]#1 *************** Now joining: X4FEEITEM_INVOICE[XFI]#2 *************** NL Join Outer table: Card: 31197592.66 Cost: 98827.27 Resp: 98827.27 Degree: 1 Bytes: 80 Access path analysis for X4FEEITEM_INVOICE Inner table: X4FEEITEM_INVOICE Alias: XFI Access Path: TableScan NL Join: Cost: 215719017499.73 Resp: 215719017499.73 Degree: 1 Cost_io: 214588192491.50 Cost_cpu: 36585206298900480 Resp_io: 214588192491.50 Resp_cpu: 36585206298900480 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:"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" 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 ****** ***** Logdef predicate Adjustment ****** Final IO cst 0.00 , CPU cst 0.00 ***** End Logdef Adjustment ****** NL Join : Cost: 83235074875.00 Resp: 83235074875.00 Degree: 1 Cost_io: 83107366681.20 Cost_cpu: 4131700822208196 Resp_io: 83107366681.20 Resp_cpu: 4131700822208196 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 ****** ***** 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: 6339799.35 Resp: 6339799.35 Degree: 1 Cost_io: 6338207.10 Cost_cpu: 51513473461 Resp_io: 6338207.10 Resp_cpu: 51513473461 ****** trying bitmap/domain indexes ****** ****** finished trying bitmap/domain indexes ****** Best NL cost: 6339799.35 resc: 6339799.35 resc_io: 6338207.10 resc_cpu: 51513473461 resp: 6339799.35 resp_io: 6338207.10 resc_cpu: 51513473461 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: 21248194.691027 = = outer (31197592.655682) * inner (1187146.275001) * sel (0.000001) Join Card - Rounded: 21248195 Computed: 21248194.69 Outer table: F4FEEITEM Alias: F4 resc: 98827.27 card 31197592.66 bytes: 80 deg: 1 resp: 98827.27 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: 374311 Row size: 98 Total Rows: 31197593 Initial runs: 73 Merge passes: 1 IO Cost / pass: 202754 Total IO sort cost: 577065 Total CPU sort cost: 44242302437 Total Temp space used: 6310405000 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: 715082.65 Resp: 715082.65 [multiMatchCost=0.00] SM Join SM cost: 715082.65 resc: 715082.65 resc_io: 713520.70 resc_cpu: 50533366102 resp: 715082.65 resp_io: 713520.70 resp_cpu: 50533366102 Outer table: F4FEEITEM Alias: F4 resc: 98827.27 card 31197592.66 bytes: 80 deg: 1 resp: 98827.27 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: 143846.54 #ptns: 1 hash_area: 124 (max=10240) buildfrag: 350364 probefrag: 20723 ppasses: 1 Hash join: Resc: 245775.19 Resp: 245775.19 [multiMatchCost=62.01] Outer table: X4FEEITEM_INVOICE Alias: XFI r |