/u01/app/admin/VMDEV01/udump/vmdev01_ora_31807_112025_vconovalov_89_447.trc Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1 System name: Linux Node name: mdch-vm-qadb Release: 2.6.18-92.el5PAE Version: #1 SMP Tue Jun 10 19:22:41 EDT 2008 Machine: i686 Instance name: VMDEV01 Redo thread mounted by this instance: 1 Oracle process number: 24 Unix process pid: 31807, image: oracle@mdch-vm-qadb *** 2009-10-23 11:21:54.663 *** ACTION NAME:(SQL Window - New) 2009-10-23 11:21:54.663 *** MODULE NAME:(PL/SQL Developer) 2009-10-23 11:21:54.663 *** SERVICE NAME:(VMDEV01) 2009-10-23 11:21:54.663 *** SESSION ID:(89.447) 2009-10-23 11:21:54.663 Registered qb: SEL$1 0xb7f56ad0 (PARSER) signature (): qb_name=SEL$1 nbfros=1 flg=0 fro(0): flg=4 objn=16574 hint_alias="E"@"SEL$1" ************************** Predicate Move-Around (PM) ************************** PM: Considering predicate move-around in SEL$1 (#0). PM: Checking validity of predicate move-around in SEL$1 (#0). CBQT: Validity checks failed for 8nx82d6vyv9t0. CVM: Considering view merge in query block SEL$1 (#0) CBQT: Validity checks failed for 8nx82d6vyv9t0. *************** Subquery Unnest *************** SU: Considering subquery unnesting in query block SEL$1 (#0) ************************* Set-Join Conversion (SJC) ************************* SJC: Considering set-join conversion in SEL$1 (#0). ************************** Predicate Move-Around (PM) ************************** PM: Considering predicate move-around in SEL$1 (#0). PM: Checking validity of predicate move-around in SEL$1 (#0). PM: PM bypassed: Outer query contains no views. FPD: Considering simple filter push in SEL$1 (#0) FPD: Current where clause predicates in SEL$1 (#0) : "E"."MLE_CREATIONDATE">=SYSDATE@! AND "E"."MLE_CREATIONDATE"<=SYSDATE@!+1 kkogcp: try to generate transitive predicate from check constraints for SEL$1 (#0) constraint: "E"."MLE_INTERNATIONAL"=1 OR "E"."MLE_INTERNATIONAL"=0 constraint: "E"."MLE_ECOMMERCE"=1 OR "E"."MLE_ECOMMERCE"=0 constraint: "E"."MLE_STATUS"=1 OR "E"."MLE_STATUS"=0 constraint: "E"."MLE_TYPE"=0 predicates with check contraints: "E"."MLE_CREATIONDATE">=SYSDATE@! AND "E"."MLE_CREATIONDATE"<=SYSDATE@!+1 AND SYSDATE@!+1>=SYSDATE@! AND ("E"."MLE_INTERNATIONAL"=1 OR "E"."MLE_INTERNATIONAL"=0) AND ("E"."MLE_ECOMMERCE"=1 OR "E"."MLE_ECOMMERCE"=0) AND ("E"."MLE_STATUS"=1 OR "E"."MLE_STATUS"=0) AND "E"."MLE_TYPE"=0 after transitive predicate generation: "E"."MLE_CREATIONDATE">=SYSDATE@! AND "E"."MLE_CREATIONDATE"<=SYSDATE@!+1 AND SYSDATE@!<=SYSDATE@!+1 AND ("E"."MLE_INTERNATIONAL"=1 OR "E"."MLE_INTERNATIONAL"=0) AND ("E"."MLE_ECOMMERCE"=1 OR "E"."MLE_ECOMMERCE"=0) AND ("E"."MLE_STATUS"=1 OR "E"."MLE_STATUS"=0) AND "E"."MLE_TYPE"=0 finally: "E"."MLE_CREATIONDATE">=SYSDATE@! AND "E"."MLE_CREATIONDATE"<=SYSDATE@!+1 AND SYSDATE@!<=SYSDATE@!+1 FPD: Following transitive predicates are generated in SEL$1 (#0) : SYSDATE@!<=SYSDATE@!+1 apadrv-start: call(in-use=928, alloc=16360), compile(in-use=50996, alloc=52740) kkoqbc-start : call(in-use=928, alloc=16360), compile(in-use=51596, alloc=52740) kkoqbc-subheap (create addr=0xb7f5bf60) ****************************************** Current SQL statement for this session: --SELECT dbms_debug_jdwp.current_session_id sid, -- dbms_debug_jdwp.current_session_serial serial# --FROM dual; select /* eds16 */ e.* from enquiry e where e.MLE_CREATIONDATE between sysdate and sysdate+1 ******************************************* Legend The following abbreviations are used by optimizer trace. CBQT - cost-based query transformation JPPD - join predicate push-down FPD - filter push-down PM - predicate move-around CVM - complex view merging SPJ - select-project-join SJC - set join conversion SU - subquery unnesting OBYE - order by elimination ST - star transformation qb - query block LB - leaf blocks DK - distinct keys LB/K - average number of leaf blocks per key DB/K - average number of data blocks per key CLUF - clustering factor NDV - number of distinct values Resp - response cost Card - cardinality Resc - resource cost NL - nested loops (join) SM - sort merge (join) HA - hash (join) CPUCSPEED - CPU Speed IOTFRSPEED - I/O transfer speed IOSEEKTIM - I/O seek time SREADTIM - average single block read time MREADTIM - average multiblock read time MBRC - average multiblock read count MAXTHR - maximum I/O system throughput SLAVETHR - average slave I/O throughput dmeth - distribution method 1: no partitioning required 2: value partitioned 4: right is random (round-robin) 512: left is random (round-robin) 8: broadcast right and partition left 16: broadcast left and partition right 32: partition left using partitioning of right 64: partition right using partitioning of left 128: use hash partitioning dimension 256: use range partitioning dimension 2048: use list partitioning dimension 1024: run the join in serial 0: invalid distribution method sel - selectivity ptn - partition ******************************************* Peeked values of the binds in SQL statement ******************************************* *************************************** PARAMETERS USED BY THE OPTIMIZER ******************************** ************************************* PARAMETERS WITH ALTERED VALUES ****************************** ********************************* Bug Fix Control Environment *************************** fix 4611850 = enabled fix 4663804 = enabled fix 4663698 = enabled fix 4545833 = enabled fix 3499674 = disabled fix 4584065 = enabled fix 4602374 = enabled fix 4569940 = enabled fix 4631959 = enabled fix 4519340 = enabled fix 4550003 = enabled fix 4488689 = enabled fix 3118776 = enabled fix 4519016 = enabled fix 4487253 = enabled fix 4556762 = 15 fix 4728348 = enabled fix 4723244 = enabled fix 4554846 = enabled fix 4175830 = enabled fix 4722900 = enabled fix 5094217 = enabled fix 4904890 = enabled fix 4483286 = disabled fix 4969880 = disabled fix 4711525 = enabled fix 4717546 = enabled fix 4904838 = enabled fix 5005866 = enabled fix 4600710 = enabled fix 5129233 = enabled fix 5195882 = enabled fix 5084239 = enabled fix 4595987 = enabled fix 4134994 = enabled fix 5104624 = enabled fix 4908162 = enabled fix 5015557 = enabled fix 5263572 = enabled fix 4483240 = enabled fix 5099909 = enabled fix 5650477 = enabled fix 4273361 = enabled fix 5694984 = enabled fix 5449488 = enabled fix 5236908 = enabled fix 5618040 = enabled fix 5634346 = enabled fix 5220356 = enabled fix 5611962 = enabled fix 5741121 = enabled fix 5547058 = enabled fix 5762598 = enabled fix 5509293 = enabled fix 5396162 = enabled fix 5891471 = enabled fix 4872602 = disabled fix 5882954 = enabled fix 5884780 = enabled fix 5680702 = enabled fix 5240607 = enabled fix 4924149 = enabled fix 4752814 = enabled fix 4583239 = enabled fix 5949981 = enabled fix 5096560 = enabled fix 5838613 = enabled fix 5482831 = enabled fix 5624216 = enabled fix 5976822 = enabled fix 5741044 = enabled fix 5385629 = enabled fix 5705630 = disabled fix 5483301 = enabled fix 6122894 = enabled fix 5842686 = disabled fix 6006300 = disabled fix 6070954 = enabled fix 2492766 = enabled fix 6042205 = enabled fix 5302124 = enabled fix 6051211 = enabled fix 5620485 = enabled fix 4545802 = enabled fix 4716096 = enabled fix 5259048 = enabled fix 6163564 = enabled fix 6082745 = enabled fix 5944076 = enabled fix 4878299 = enabled fix 5288623 = enabled fix 5570494 = enabled fix 5387148 = enabled fix 4605810 = enabled fix 4704779 = enabled fix 5547895 = enabled fix 6188881 = enabled fix 5872956 = enabled fix 4708389 = enabled fix 3151991 = enabled fix 3426050 = enabled fix 599680 = enabled fix 5505157 = enabled fix 5996801 = enabled fix 5765456 = 0 fix 6494943 = enabled fix 6251917 = enabled fix 6087237 = enabled fix 6239971 = enabled fix 6062266 = enabled fix 5520732 = 0 fix 6151963 = enabled fix 4567767 = enabled fix 6007259 = enabled fix 6694548 = enabled ************************************* PARAMETERS WITH DEFAULT VALUES ****************************** optimizer_mode_hinted = false optimizer_features_hinted = 0.0.0 parallel_execution_enabled = true parallel_query_forced_dop = 0 parallel_dml_forced_dop = 0 parallel_ddl_forced_degree = 0 parallel_ddl_forced_instances = 0 _query_rewrite_fudge = 90 optimizer_features_enable = 10.2.0.4 _optimizer_search_limit = 5 cpu_count = 8 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 = 16 _optimizer_max_permutations = 2000 pga_aggregate_target = 403456 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 = 403 KB _smm_max_size = 80691 KB _smm_px_max_size = 201728 KB _cpu_to_io = 0 _optimizer_undo_cost_change = 10.2.0.4 parallel_query_mode = enabled parallel_dml_mode = disabled parallel_ddl_mode = enabled optimizer_mode = all_rows sqlstat_enabled = false _optimizer_percent_parallel = 101 _always_anti_join = choose _always_semi_join = choose _optimizer_mode_force = true _partition_view_enabled = true _always_star_transformation = false _query_rewrite_or_error = false _hash_join_enabled = true cursor_sharing = exact _b_tree_bitmap_plans = true star_transformation_enabled = false _optimizer_cost_model = choose _new_sort_cost_estimate = true _complex_view_merging = true _unnest_subquery = true _eliminate_common_subexpr = true _pred_move_around = true _convert_set_to_join = false _push_join_predicate = true _push_join_union_view = true _fast_full_scan_enabled = true _optim_enhance_nnull_detection = true _parallel_broadcast_enabled = true _px_broadcast_fudge_factor = 100 _ordered_nested_loop = true _no_or_expansion = false optimizer_index_cost_adj = 100 optimizer_index_caching = 0 _system_index_caching = 0 _disable_datalayer_sampling = false query_rewrite_enabled = true query_rewrite_integrity = enforced _query_cost_rewrite = true _query_rewrite_2 = true _query_rewrite_1 = true _query_rewrite_expression = true _query_rewrite_jgmigrate = true _query_rewrite_fpc = true _query_rewrite_drj = true _full_pwise_join_enabled = true _partial_pwise_join_enabled = true _left_nested_loops_random = true _improved_row_length_enabled = true _index_join_enabled = true _enable_type_dep_selectivity = true _improved_outerjoin_card = true _optimizer_adjust_for_nulls = true _optimizer_degree = 0 _use_column_stats_for_function = true _subquery_pruning_enabled = true _subquery_pruning_mv_enabled = false _or_expand_nvl_predicate = true _like_with_bind_as_equality = false _table_scan_cost_plus_one = true _cost_equality_semi_join = true _default_non_equality_sel_check = true _new_initial_join_orders = true _oneside_colstat_for_equijoins = true _optim_peek_user_binds = true _minimal_stats_aggregation = true _force_temptables_for_gsets = false workarea_size_policy = auto _smm_auto_cost_enabled = true _gs_anti_semi_join_allowed = true _optim_new_default_join_sel = true optimizer_dynamic_sampling = 2 _pre_rewrite_push_pred = true _optimizer_new_join_card_computation = true _union_rewrite_for_gs = yes_gset_mvs _generalized_pruning_enabled = true _optim_adjust_for_part_skews = true _force_datefold_trunc = false statistics_level = typical _optimizer_system_stats_usage = true skip_unusable_indexes = true _remove_aggr_subquery = true _optimizer_push_down_distinct = 0 _dml_monitoring_enabled = true _optimizer_undo_changes = false _predicate_elimination_enabled = true _nested_loop_fudge = 100 _project_view_columns = true _local_communication_costing_enabled = true _local_communication_ratio = 50 _query_rewrite_vop_cleanup = true _slave_mapping_enabled = true _optimizer_cost_based_transformation = linear _optimizer_mjc_enabled = true _right_outer_hash_enable = true _spr_push_pred_refspr = true _optimizer_cache_stats = false _optimizer_cbqt_factor = 50 _optimizer_squ_bottomup = true _fic_area_size = 131072 _optimizer_skip_scan_enabled = true _optimizer_cost_filter_pred = false _optimizer_sortmerge_join_enabled = true _optimizer_join_sel_sanity_check = true _mmv_query_rewrite_enabled = true _bt_mmv_query_rewrite_enabled = true _add_stale_mv_to_dependency_list = true _distinct_view_unnesting = false _optimizer_dim_subq_join_sel = true _optimizer_disable_strans_sanity_checks = 0 _optimizer_compute_index_stats = true _push_join_union_view2 = true _optimizer_ignore_hints = false _optimizer_random_plan = 0 _query_rewrite_setopgrw_enable = true _optimizer_correct_sq_selectivity = true _disable_function_based_index = false _optimizer_join_order_control = 3 _optimizer_cartesian_enabled = true _optimizer_starplan_enabled = true _extended_pruning_enabled = true _optimizer_push_pred_cost_based = true _sql_model_unfold_forloops = run_time _enable_dml_lock_escalation = false _bloom_filter_enabled = true _update_bji_ipdml_enabled = 0 _optimizer_extended_cursor_sharing = udo _dm_max_shared_pool_pct = 1 _optimizer_cost_hjsmj_multimatch = true _optimizer_transitivity_retain = true _px_pwg_enabled = true optimizer_secure_view_merging = true _optimizer_join_elimination_enabled = true flashback_table_rpi = non_fbt _optimizer_cbqt_no_size_restriction = true _optimizer_enhanced_filter_push = true _optimizer_filter_pred_pullup = true _rowsrc_trace_level = 0 _simple_view_merging = true _optimizer_rownum_pred_based_fkr = true _optimizer_better_inlist_costing = all _optimizer_self_induced_cache_cost = false _optimizer_min_cache_blocks = 10 _optimizer_or_expansion = depth _optimizer_order_by_elimination_enabled = true _optimizer_outer_to_anti_enabled = true _selfjoin_mv_duplicates = true _dimension_skip_null = true _force_rewrite_enable = false _optimizer_star_tran_in_with_clause = true _optimizer_complex_pred_selectivity = true _optimizer_connect_by_cost_based = true _gby_hash_aggregation_enabled = true _globalindex_pnum_filter_enabled = true _fix_control_key = 0 _optimizer_skip_scan_guess = false _enable_row_shipping = false _row_shipping_threshold = 80 _row_shipping_explain = false _optimizer_rownum_bind_default = 10 _first_k_rows_dynamic_proration = true _px_ual_serial_input = true _optimizer_native_full_outer_join = off _optimizer_star_trans_min_cost = 0 _optimizer_star_trans_min_ratio = 0 _optimizer_fkr_index_cost_bias = 10 _optimizer_connect_by_combine_sw = true _optimizer_use_subheap = true _optimizer_or_expansion_subheap = true _optimizer_sortmerge_join_inequality = true _optimizer_use_histograms = true _optimizer_enable_density_improvements = false ********************************* Bug Fix Control Environment *************************** fix 4611850 = enabled fix 4663804 = enabled fix 4663698 = enabled fix 4545833 = enabled fix 3499674 = disabled fix 4584065 = enabled fix 4602374 = enabled fix 4569940 = enabled fix 4631959 = enabled fix 4519340 = enabled fix 4550003 = enabled fix 4488689 = enabled fix 3118776 = enabled fix 4519016 = enabled fix 4487253 = enabled fix 4556762 = 15 fix 4728348 = enabled fix 4723244 = enabled fix 4554846 = enabled fix 4175830 = enabled fix 4722900 = enabled fix 5094217 = enabled fix 4904890 = enabled fix 4483286 = disabled fix 4969880 = disabled fix 4711525 = enabled fix 4717546 = enabled fix 4904838 = enabled fix 5005866 = enabled fix 4600710 = enabled fix 5129233 = enabled fix 5195882 = enabled fix 5084239 = enabled fix 4595987 = enabled fix 4134994 = enabled fix 5104624 = enabled fix 4908162 = enabled fix 5015557 = enabled fix 5263572 = enabled fix 4483240 = enabled fix 5099909 = enabled fix 5650477 = enabled fix 4273361 = enabled fix 5694984 = enabled fix 5449488 = enabled fix 5236908 = enabled fix 5618040 = enabled fix 5634346 = enabled fix 5220356 = enabled fix 5611962 = enabled fix 5741121 = enabled fix 5547058 = enabled fix 5762598 = enabled fix 5509293 = enabled fix 5396162 = enabled fix 5891471 = enabled fix 4872602 = disabled fix 5882954 = enabled fix 5884780 = enabled fix 5680702 = enabled fix 5240607 = enabled fix 4924149 = enabled fix 4752814 = enabled fix 4583239 = enabled fix 5949981 = enabled fix 5096560 = enabled fix 5838613 = enabled fix 5482831 = enabled fix 5624216 = enabled fix 5976822 = enabled fix 5741044 = enabled fix 5385629 = enabled fix 5705630 = disabled fix 5483301 = enabled fix 6122894 = enabled fix 5842686 = disabled fix 6006300 = disabled fix 6070954 = enabled fix 2492766 = enabled fix 6042205 = enabled fix 5302124 = enabled fix 6051211 = enabled fix 5620485 = enabled fix 4545802 = enabled fix 4716096 = enabled fix 5259048 = enabled fix 6163564 = enabled fix 6082745 = enabled fix 5944076 = enabled fix 4878299 = enabled fix 5288623 = enabled fix 5570494 = enabled fix 5387148 = enabled fix 4605810 = enabled fix 4704779 = enabled fix 5547895 = enabled fix 6188881 = enabled fix 5872956 = enabled fix 4708389 = enabled fix 3151991 = enabled fix 3426050 = enabled fix 599680 = enabled fix 5505157 = enabled fix 5996801 = enabled fix 5765456 = 0 fix 6494943 = enabled fix 6251917 = enabled fix 6087237 = enabled fix 6239971 = enabled fix 6062266 = enabled fix 5520732 = 0 fix 6151963 = enabled fix 4567767 = enabled fix 6007259 = enabled fix 6694548 = enabled *************************************** PARAMETERS IN OPT_PARAM HINT **************************** *************************************** Column Usage Monitoring is ON: tracking level = 1 *************************************** **************** QUERY BLOCK TEXT **************** select /* eds16 */ e.* from enquiry e where e.MLE_CREATIONDATE between sysdate and sysdate+1 ********************* QUERY BLOCK SIGNATURE ********************* qb name was generated signature (optimizer): qb_name=SEL$1 nbfros=1 flg=0 fro(0): flg=0 objn=16574 hint_alias="E"@"SEL$1" ***************************** SYSTEM STATISTICS INFORMATION ***************************** Using NOWORKLOAD Stats CPUSPEED: 665 millions instruction/sec IOTFRSPEED: 4096 bytes per millisecond (default is 4096) IOSEEKTIM: 10 milliseconds (default is 10) *************************************** BASE STATISTICAL INFORMATION *********************** Table Stats:: Table: ENQUIRY Alias: E #Rows: 284029 #Blks: 10097 AvgRowLen: 221.00 Index Stats:: Index: INDX_ENQUIRY_ACQBID Col#: 7 LVLS: 2 #LB: 1060 #DK: 76 LB/K: 13.00 DB/K: 581.00 CLUF: 44190.00 Index: INDX_ENQUIRY_BATCHID Col#: 25 LVLS: 1 #LB: 3 #DK: 29 LB/K: 1.00 DB/K: 1.00 CLUF: 49.00 Index: INDX_ENQUIRY_COUNTRY Col#: 21 LVLS: 2 #LB: 820 #DK: 34 LB/K: 24.00 DB/K: 1257.00 CLUF: 42768.00 Index: INDX_ENQUIRY_CRDATE Col#: 3 LVLS: 2 #LB: 754 #DK: 83719 LB/K: 1.00 DB/K: 1.00 CLUF: 35302.00 Index: INDX_ENQUIRY_LR1 Col#: 23 LVLS: 0 #LB: 0 #DK: 0 LB/K: 0.00 DB/K: 0.00 CLUF: 0.00 Index: INDX_ENQUIRY_LR2 Col#: 24 LVLS: 0 #LB: 0 #DK: 0 LB/K: 0.00 DB/K: 0.00 CLUF: 0.00 Index: INDX_ENQUIRY_MCC Col#: 22 LVLS: 2 #LB: 930 #DK: 360 LB/K: 2.00 DB/K: 499.00 CLUF: 179906.00 Index: INDX_ENQUIRY_MLETYPE Col#: 2 LVLS: 2 #LB: 665 #DK: 1 LB/K: 665.00 DB/K: 9215.00 CLUF: 9215.00 Index: INDX_ENQUIRY_NOM Col#: 41 LVLS: 2 #LB: 665 #DK: 1 LB/K: 665.00 DB/K: 9215.00 CLUF: 9215.00 Index: INDX_ENQUIRY_STATUS Col#: 6 LVLS: 2 #LB: 541 #DK: 2 LB/K: 270.00 DB/K: 4856.00 CLUF: 9713.00 Index: PK_ENQUIRY Col#: 1 LVLS: 1 #LB: 694 #DK: 284029 LB/K: 1.00 DB/K: 1.00 CLUF: 15852.00 *************************************** SINGLE TABLE ACCESS PATH ----------------------------------------- BEGIN Single Table Cardinality Estimation ----------------------------------------- Column (#3): MLE_CREATIONDATE(DATE) AvgLen: 8.00 NDV: 3440 Nulls: 0 Density: 2.8408e-04 Min: 2454753 Max: 2455125 Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 252 Using prorated density: 2.8105e-04 of col #3 as selectivity of out-of-range value pred Table: ENQUIRY Alias: E Card: Original: 284029 Rounded: 81 Computed: 80.69 Non Adjusted: 80.69 ----------------------------------------- END Single Table Cardinality Estimation ----------------------------------------- Access Path: TableScan Cost: 2236.45 Resp: 2236.45 Degree: 0 Cost_io: 2210.00 Cost_cpu: 211180863 Resp_io: 2210.00 Resp_cpu: 211180863 Using prorated density: 2.8105e-04 of col #3 as selectivity of out-of-range value pred Access Path: index (RangeScan) Index: INDX_ENQUIRY_CRDATE resc_io: 13.00 resc_cpu: 184579 ix_sel: 2.8105e-04 ix_sel_with_filters: 2.8105e-04 Cost: 13.02 Resp: 13.02 Degree: 1 Best:: AccessPath: IndexRange Index: INDX_ENQUIRY_CRDATE Cost: 13.02 Degree: 1 Resp: 13.02 Card: 80.69 Bytes: 0 *************************************** OPTIMIZER STATISTICS AND COMPUTATIONS *************************************** GENERAL PLANS *************************************** Considering cardinality-based initial join order. Permutations for Starting Table :0 *********************** Join order[1]: ENQUIRY[E]#0 *********************** Best so far: Table#: 0 cost: 13.0231 card: 80.6865 bytes: 17901 (newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:80000 ********************************* Number of join permutations tried: 1 ********************************* Final - All Rows Plan: Best join order: 1 Cost: 13.0231 Degree: 1 Card: 81.0000 Bytes: 17901 Resc: 13.0231 Resc_io: 13.0000 Resc_cpu: 184579 Resp: 13.0231 Resp_io: 13.0000 Resc_cpu: 184579 kkoipt: Query block SEL$1 (#0) ******* UNPARSED QUERY IS ******* SELECT "E"."MLE_ID" "MLE_ID","E"."MLE_TYPE" "MLE_TYPE","E"."MLE_CREATIONDATE" "MLE_CREATIONDATE","E"."MLE_DELETIONDATE" "MLE_DELETIONDATE","E"."MLE_MODIFICATIONDATE" "MLE_MODIFICATIONDATE","E"."MLE_STATUS" "MLE_STATUS","E"."MLE_ACQ_ACQUIRERBID" "MLE_ACQ_ACQUIRERBID","E"."MLE_TRADINGNAME" "MLE_TRADINGNAME","E"."MLE_LEGALNAME" "MLE_LEGALNAME","E"."MLE_PRIMARYPRINCIPALNAME" "MLE_PRIMARYPRINCIPALNAME","E"."MLE_2NDPRINCIPALNAME" "MLE_2NDPRINCIPALNAME","E"."MLE_3RDPRINCIPALNAME" "MLE_3RDPRINCIPALNAME","E"."MLE_PRIMARYPHONENUMBER" "MLE_PRIMARYPHONENUMBER","E"."MLE_2NDPHONENUMBER" "MLE_2NDPHONENUMBER","E"."MLE_3RDPHONENUMBER" "MLE_3RDPHONENUMBER","E"."MLE_ADDRESSSTREET" "MLE_ADDRESSSTREET","E"."MLE_ADDRESSPOSTCODE" "MLE_ADDRESSPOSTCODE","E"."MLE_ADDRESSCITY" "MLE_ADDRESSCITY","E"."MLE_ADDRESSCOUNTYREGION" "MLE_ADDRESSCOUNTYREGION","E"."MLE_ADDRESSLOCALITYREGION" "MLE_ADDRESSLOCALITYREGION","E"."MLE_CNY_ADDRESSCOUNTRY" "MLE_CNY_ADDRESSCOUNTRY","E"."MLE_MCC_CODE" "MLE_MCC_CODE","E"."MLE_LSR_LISTINGREASON1" "MLE_LSR_LISTINGREASON1","E"."MLE_LSR_LISTINGREASON2" "MLE_LSR_LISTINGREASON2","E"."MLE_BRQ_BATCHID" "MLE_BRQ_BATCHID","E"."MLE_ECOMMERCE" "MLE_ECOMMERCE","E"."MLE_WEBADDRESS" "MLE_WEBADDRESS","E"."MLE_INTERNATIONAL" "MLE_INTERNATIONAL","E"."MLE_FINANCIALINSTITUTIONID" "MLE_FINANCIALINSTITUTIONID","E"."MLE_BANKACCOUNTNUMBER" "MLE_BANKACCOUNTNUMBER","E"."MLE_TAXIDENTIFICATION" "MLE_TAXIDENTIFICATION","E"."MLE_COMPANYIDENTIFICATION" "MLE_COMPANYIDENTIFICATION","E"."MLE_CONTRACTSTARTDATE" "MLE_CONTRACTSTARTDATE","E"."MLE_CONTRACTCLOSEDDATE" "MLE_CONTRACTCLOSEDDATE","E"."MLE_CARDACCEPTORIDENTIFICATION" "MLE_CARDACCEPTORIDENTIFICATION","E"."MLE_IBAN" "MLE_IBAN","E"."MLE_CREATOR_NAME" "MLE_CREATOR_NAME","E"."MLE_CREATOR_LOGIN" "MLE_CREATOR_LOGIN","E"."MLE_ISIMPORTED" "MLE_ISIMPORTED","E"."MLE_HASH" "MLE_HASH","E"."MLE_NUMBEROFMERCHANTS" "MLE_NUMBEROFMERCHANTS" FROM "VMAS"."ENQUIRY" "E" WHERE SYSDATE@!<=SYSDATE@!+1 AND "E"."MLE_CREATIONDATE"<=SYSDATE@!+1 AND "E"."MLE_CREATIONDATE">=SYSDATE@! kkoqbc-subheap (delete addr=0xb7f5bf60, in-use=11868, alloc=12092) kkoqbc-end : call(in-use=88680, alloc=114616), compile(in-use=54940, alloc=60988) apadrv-end: call(in-use=88680, alloc=114616), compile(in-use=55460, alloc=60988) sql_id=8nx82d6vyv9t0. Current SQL statement for this session: --SELECT dbms_debug_jdwp.current_session_id sid, -- dbms_debug_jdwp.current_session_serial serial# --FROM dual; select /* eds16 */ e.* from enquiry e where e.MLE_CREATIONDATE between sysdate and sysdate+1 ============ Plan Table ============ -----------------------------------------------------------+-----------------------------------+ | Id | Operation | Name | Rows | Bytes | Cost | Time | -----------------------------------------------------------+-----------------------------------+ | 0 | SELECT STATEMENT | | | | 13 | | | 1 | FILTER | | | | | | | 2 | TABLE ACCESS BY INDEX ROWID | ENQUIRY | 81 | 17K | 13 | 00:00:01 | | 3 | INDEX RANGE SCAN | INDX_ENQUIRY_CRDATE| 80 | | 3 | 00:00:01 | -----------------------------------------------------------+-----------------------------------+ Predicate Information: ---------------------- 1 - filter(SYSDATE@!<=SYSDATE@!+1) 3 - access("E"."MLE_CREATIONDATE">=SYSDATE@! AND "E"."MLE_CREATIONDATE"<=SYSDATE@!+1) Content of other_xml column =========================== db_version : 10.2.0.4 parse_schema : VMAS_APP plan_hash : 1744578590 Outline Data: /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('10.2.0.4') ALL_ROWS OUTLINE_LEAF(@"SEL$1") INDEX_RS_ASC(@"SEL$1" "E"@"SEL$1" ("ENQUIRY"."MLE_CREATIONDATE")) END_OUTLINE_DATA */ Optimizer environment: optimizer_mode_hinted = false optimizer_features_hinted = 0.0.0 parallel_execution_enabled = true parallel_query_forced_dop = 0 parallel_dml_forced_dop = 0 parallel_ddl_forced_degree = 0 parallel_ddl_forced_instances = 0 _query_rewrite_fudge = 90 optimizer_features_enable = 10.2.0.4 _optimizer_search_limit = 5 cpu_count = 8 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 = 16 _optimizer_max_permutations = 2000 pga_aggregate_target = 403456 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 = 403 KB _smm_max_size = 80691 KB _smm_px_max_size = 201728 KB _cpu_to_io = 0 _optimizer_undo_cost_change = 10.2.0.4 parallel_query_mode = enabled parallel_dml_mode = disabled parallel_ddl_mode = enabled optimizer_mode = all_rows sqlstat_enabled = false _optimizer_percent_parallel = 101 _always_anti_join = choose _always_semi_join = choose _optimizer_mode_force = true _partition_view_enabled = true _always_star_transformation = false _query_rewrite_or_error = false _hash_join_enabled = true cursor_sharing = exact _b_tree_bitmap_plans = true star_transformation_enabled = false _optimizer_cost_model = choose _new_sort_cost_estimate = true _complex_view_merging = true _unnest_subquery = true _eliminate_common_subexpr = true _pred_move_around = true _convert_set_to_join = false _push_join_predicate = true _push_join_union_view = true _fast_full_scan_enabled = true _optim_enhance_nnull_detection = true _parallel_broadcast_enabled = true _px_broadcast_fudge_factor = 100 _ordered_nested_loop = true _no_or_expansion = false optimizer_index_cost_adj = 100 optimizer_index_caching = 0 _system_index_caching = 0 _disable_datalayer_sampling = false query_rewrite_enabled = true query_rewrite_integrity = enforced _query_cost_rewrite = true _query_rewrite_2 = true _query_rewrite_1 = true _query_rewrite_expression = true _query_rewrite_jgmigrate = true _query_rewrite_fpc = true _query_rewrite_drj = true _full_pwise_join_enabled = true _partial_pwise_join_enabled = true _left_nested_loops_random = true _improved_row_length_enabled = true _index_join_enabled = true _enable_type_dep_selectivity = true _improved_outerjoin_card = true _optimizer_adjust_for_nulls = true _optimizer_degree = 0 _use_column_stats_for_function = true _subquery_pruning_enabled = true _subquery_pruning_mv_enabled = false _or_expand_nvl_predicate = true _like_with_bind_as_equality = false _table_scan_cost_plus_one = true _cost_equality_semi_join = true _default_non_equality_sel_check = true _new_initial_join_orders = true _oneside_colstat_for_equijoins = true _optim_peek_user_binds = true _minimal_stats_aggregation = true _force_temptables_for_gsets = false workarea_size_policy = auto _smm_auto_cost_enabled = true _gs_anti_semi_join_allowed = true _optim_new_default_join_sel = true optimizer_dynamic_sampling = 2 _pre_rewrite_push_pred = true _optimizer_new_join_card_computation = true _union_rewrite_for_gs = yes_gset_mvs _generalized_pruning_enabled = true _optim_adjust_for_part_skews = true _force_datefold_trunc = false statistics_level = typical _optimizer_system_stats_usage = true skip_unusable_indexes = true _remove_aggr_subquery = true _optimizer_push_down_distinct = 0 _dml_monitoring_enabled = true _optimizer_undo_changes = false _predicate_elimination_enabled = true _nested_loop_fudge = 100 _project_view_columns = true _local_communication_costing_enabled = true _local_communication_ratio = 50 _query_rewrite_vop_cleanup = true _slave_mapping_enabled = true _optimizer_cost_based_transformation = linear _optimizer_mjc_enabled = true _right_outer_hash_enable = true _spr_push_pred_refspr = true _optimizer_cache_stats = false _optimizer_cbqt_factor = 50 _optimizer_squ_bottomup = true _fic_area_size = 131072 _optimizer_skip_scan_enabled = true _optimizer_cost_filter_pred = false _optimizer_sortmerge_join_enabled = true _optimizer_join_sel_sanity_check = true _mmv_query_rewrite_enabled = true _bt_mmv_query_rewrite_enabled = true _add_stale_mv_to_dependency_list = true _distinct_view_unnesting = false _optimizer_dim_subq_join_sel = true _optimizer_disable_strans_sanity_checks = 0 _optimizer_compute_index_stats = true _push_join_union_view2 = true _optimizer_ignore_hints = false _optimizer_random_plan = 0 _query_rewrite_setopgrw_enable = true _optimizer_correct_sq_selectivity = true _disable_function_based_index = false _optimizer_join_order_control = 3 _optimizer_cartesian_enabled = true _optimizer_starplan_enabled = true _extended_pruning_enabled = true _optimizer_push_pred_cost_based = true _sql_model_unfold_forloops = run_time _enable_dml_lock_escalation = false _bloom_filter_enabled = true _update_bji_ipdml_enabled = 0 _optimizer_extended_cursor_sharing = udo _dm_max_shared_pool_pct = 1 _optimizer_cost_hjsmj_multimatch = true _optimizer_transitivity_retain = true _px_pwg_enabled = true optimizer_secure_view_merging = true _optimizer_join_elimination_enabled = true flashback_table_rpi = non_fbt _optimizer_cbqt_no_size_restriction = true _optimizer_enhanced_filter_push = true _optimizer_filter_pred_pullup = true _rowsrc_trace_level = 0 _simple_view_merging = true _optimizer_rownum_pred_based_fkr = true _optimizer_better_inlist_costing = all _optimizer_self_induced_cache_cost = false _optimizer_min_cache_blocks = 10 _optimizer_or_expansion = depth _optimizer_order_by_elimination_enabled = true _optimizer_outer_to_anti_enabled = true _selfjoin_mv_duplicates = true _dimension_skip_null = true _force_rewrite_enable = false _optimizer_star_tran_in_with_clause = true _optimizer_complex_pred_selectivity = true _optimizer_connect_by_cost_based = true _gby_hash_aggregation_enabled = true _globalindex_pnum_filter_enabled = true _fix_control_key = 0 _optimizer_skip_scan_guess = false _enable_row_shipping = false _row_shipping_threshold = 80 _row_shipping_explain = false _optimizer_rownum_bind_default = 10 _first_k_rows_dynamic_proration = true _px_ual_serial_input = true _optimizer_native_full_outer_join = off _optimizer_star_trans_min_cost = 0 _optimizer_star_trans_min_ratio = 0 _optimizer_fkr_index_cost_bias = 10 _optimizer_connect_by_combine_sw = true _optimizer_use_subheap = true _optimizer_or_expansion_subheap = true _optimizer_sortmerge_join_inequality = true _optimizer_use_histograms = true _optimizer_enable_density_improvements = false ********************************* Bug Fix Control Environment *************************** fix 4611850 = enabled fix 4663804 = enabled fix 4663698 = enabled fix 4545833 = enabled fix 3499674 = disabled fix 4584065 = enabled fix 4602374 = enabled fix 4569940 = enabled fix 4631959 = enabled fix 4519340 = enabled fix 4550003 = enabled fix 4488689 = enabled fix 3118776 = enabled fix 4519016 = enabled fix 4487253 = enabled fix 4556762 = 15 fix 4728348 = enabled fix 4723244 = enabled fix 4554846 = enabled fix 4175830 = enabled fix 4722900 = enabled fix 5094217 = enabled fix 4904890 = enabled fix 4483286 = disabled fix 4969880 = disabled fix 4711525 = enabled fix 4717546 = enabled fix 4904838 = enabled fix 5005866 = enabled fix 4600710 = enabled fix 5129233 = enabled fix 5195882 = enabled fix 5084239 = enabled fix 4595987 = enabled fix 4134994 = enabled fix 5104624 = enabled fix 4908162 = enabled fix 5015557 = enabled fix 5263572 = enabled fix 4483240 = enabled fix 5099909 = enabled fix 5650477 = enabled fix 4273361 = enabled fix 5694984 = enabled fix 5449488 = enabled fix 5236908 = enabled fix 5618040 = enabled fix 5634346 = enabled fix 5220356 = enabled fix 5611962 = enabled fix 5741121 = enabled fix 5547058 = enabled fix 5762598 = enabled fix 5509293 = enabled fix 5396162 = enabled fix 5891471 = enabled fix 4872602 = disabled fix 5882954 = enabled fix 5884780 = enabled fix 5680702 = enabled fix 5240607 = enabled fix 4924149 = enabled fix 4752814 = enabled fix 4583239 = enabled fix 5949981 = enabled fix 5096560 = enabled fix 5838613 = enabled fix 5482831 = enabled fix 5624216 = enabled fix 5976822 = enabled fix 5741044 = enabled fix 5385629 = enabled fix 5705630 = disabled fix 5483301 = enabled fix 6122894 = enabled fix 5842686 = disabled fix 6006300 = disabled fix 6070954 = enabled fix 2492766 = enabled fix 6042205 = enabled fix 5302124 = enabled fix 6051211 = enabled fix 5620485 = enabled fix 4545802 = enabled fix 4716096 = enabled fix 5259048 = enabled fix 6163564 = enabled fix 6082745 = enabled fix 5944076 = enabled fix 4878299 = enabled fix 5288623 = enabled fix 5570494 = enabled fix 5387148 = enabled fix 4605810 = enabled fix 4704779 = enabled fix 5547895 = enabled fix 6188881 = enabled fix 5872956 = enabled fix 4708389 = enabled fix 3151991 = enabled fix 3426050 = enabled fix 599680 = enabled fix 5505157 = enabled fix 5996801 = enabled fix 5765456 = 0 fix 6494943 = enabled fix 6251917 = enabled fix 6087237 = enabled fix 6239971 = enabled fix 6062266 = enabled fix 5520732 = 0 fix 6151963 = enabled fix 4567767 = enabled fix 6007259 = enabled fix 6694548 = enabled Query Block Registry: ********************* SEL$1 0xb7f56ad0 (PARSER) [FINAL] Optimizer State Dump: call(in-use=91420, alloc=114616), compile(in-use=85172, alloc=130356)