/u02/admin/orcl/udump/orcl_ora_3156.trc Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1 System name: Linux Node name: fserver Release: 2.6.16-hardened-r3 Version: #2 SMP Thu Apr 27 04:00:40 Local time zone must be set--see zic Machine: i686 Instance name: orcl Redo thread mounted by this instance: 1 Oracle process number: 14 Unix process pid: 3156, image: oracle@fserver (TNS V1-V3) *** 2006-05-18 22:14:35.924 *** ACTION NAME:() 2006-05-18 22:14:35.908 *** MODULE NAME:(SQL*Plus) 2006-05-18 22:14:35.908 *** SERVICE NAME:(SYS$USERS) 2006-05-18 22:14:35.908 *** SESSION ID:(149.483) 2006-05-18 22:14:35.908 Registered qb: SEL$1 0xaa39b688 (PARSER) signature (): qb_name=SEL$1 nbfros=1 flg=0 fro(0): flg=4 objn=118780 hint_alias="TI_OFFER_726"@"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 44kb1djhswktx. CVM: Considering view merge in query block SEL$1 (#0) CBQT: Validity checks failed for 44kb1djhswktx. *************** 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) : 1=1 AND "TI_OFFER_726"."DURATION">=7 AND "TI_OFFER_726"."DURATION"<=10 AND "TI_OFFER_726"."REAL_PRICE">=0 AND "TI_OFFER_726"."REAL_PRICE"<=99999 AND "TI_OFFER_726"."DEPARTURE">='18-MAY-06' AND "TI_OFFER_726"."DEPARTURE"<='27-MAY-06' AND ("TI_OFFER_726"."MEAL"=2 OR "TI_OFFER_726"."MEAL"=3 OR "TI_OFFER_726"."MEAL"=4) AND ("TI_OFFER_726"."ALLOCCAT"=7 OR "TI_OFFER_726"."ALLOCCAT"=6 OR "TI_OFFER_726"."ALLOCCAT"=5) kkogcp: try to generate transitive predicate from check constraints for SEL$1 (#0) predicates with check contraints: 1=1 AND "TI_OFFER_726"."DURATION">=7 AND "TI_OFFER_726"."DURATION"<=10 AND "TI_OFFER_726"."REAL_PRICE">=0 AND "TI_OFFER_726"."REAL_PRICE"<=99999 AND "TI_OFFER_726"."DEPARTURE">='18-MAY-06' AND "TI_OFFER_726"."DEPARTURE"<='27-MAY-06' AND ("TI_OFFER_726"."MEAL"=2 OR "TI_OFFER_726"."MEAL"=3 OR "TI_OFFER_726"."MEAL"=4) AND ("TI_OFFER_726"."ALLOCCAT"=7 OR "TI_OFFER_726"."ALLOCCAT"=6 OR "TI_OFFER_726"."ALLOCCAT"=5) AND 10>=7 AND 99999>=0 AND TO_DATE('27-MAY-06')>=TO_DATE('18-MAY-06') after transitive predicate generation: 1=1 AND "TI_OFFER_726"."DURATION">=7 AND "TI_OFFER_726"."DURATION"<=10 AND "TI_OFFER_726"."REAL_PRICE">=0 AND "TI_OFFER_726"."REAL_PRICE"<=99999 AND "TI_OFFER_726"."DEPARTURE">='18-MAY-06' AND "TI_OFFER_726"."DEPARTURE"<='27-MAY-06' AND ("TI_OFFER_726"."MEAL"=2 OR "TI_OFFER_726"."MEAL"=3 OR "TI_OFFER_726"."MEAL"=4) AND ("TI_OFFER_726"."ALLOCCAT"=7 OR "TI_OFFER_726"."ALLOCCAT"=6 OR "TI_OFFER_726"."ALLOCCAT"=5) AND 7<=10 AND 0<=99999 AND TO_DATE('18-MAY-06')<=TO_DATE('27-MAY-06') finally: 1=1 AND "TI_OFFER_726"."DURATION">=7 AND "TI_OFFER_726"."DURATION"<=10 AND "TI_OFFER_726"."REAL_PRICE">=0 AND "TI_OFFER_726"."REAL_PRICE"<=99999 AND "TI_OFFER_726"."DEPARTURE">='18-MAY-06' AND "TI_OFFER_726"."DEPARTURE"<='27-MAY-06' AND ("TI_OFFER_726"."MEAL"=2 OR "TI_OFFER_726"."MEAL"=3 OR "TI_OFFER_726"."MEAL"=4) AND ("TI_OFFER_726"."ALLOCCAT"=7 OR "TI_OFFER_726"."ALLOCCAT"=6 OR "TI_OFFER_726"."ALLOCCAT"=5) AND 7<=10 AND 0<=99999 AND TO_DATE('18-MAY-06')<=TO_DATE('27-MAY-06') FPD: Following transitive predicates are generated in SEL$1 (#0) : 7<=10 AND 0<=99999 AND TO_DATE('18-MAY-06')<=TO_DATE('27-MAY-06') apadrv-start: call(in-use=792, alloc=218808), compile(in-use=33088, alloc=36696) kkoqbc-start : call(in-use=792, alloc=218808), compile(in-use=33668, alloc=36696) ****************************************** Current SQL statement for this session: explain plan for SELECT /* ZAPROS */ * FROM ti_offer_726 WHERE 1=1 AND duration BETWEEN 7 AND 10 and real_price between 0 and 99999 AND departure BETWEEN '18-MAY-06' AND '27-MAY-06' AND meal IN (2,3,4) AND alloccat IN (7,6,5) ******************************************* 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 ****************************** optimizer_index_cost_adj = 15 optimizer_index_caching = 70 ************************************* 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.1 _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 = 32 _optimizer_max_permutations = 2000 pga_aggregate_target = 793600 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 = 793 KB _smm_max_size = 102400 KB _smm_px_max_size = 396800 KB _cpu_to_io = 0 _optimizer_undo_cost_change = 10.2.0.1 parallel_query_mode = enabled parallel_dml_mode = disabled parallel_ddl_mode = enabled optimizer_mode = all_rows sqlstat_enabled = false _optimizer_percent_parallel = 101 _always_anti_join = choose _always_semi_join = choose _optimizer_mode_force = true _partition_view_enabled = true _always_star_transformation = false _query_rewrite_or_error = false _hash_join_enabled = true cursor_sharing = exact _b_tree_bitmap_plans = true star_transformation_enabled = false _optimizer_cost_model = choose _new_sort_cost_estimate = true _complex_view_merging = true _unnest_subquery = true _eliminate_common_subexpr = true _pred_move_around = true _convert_set_to_join = false _push_join_predicate = true _push_join_union_view = true _fast_full_scan_enabled = true _optim_enhance_nnull_detection = true _parallel_broadcast_enabled = true _px_broadcast_fudge_factor = 100 _ordered_nested_loop = true _no_or_expansion = false _system_index_caching = 0 _disable_datalayer_sampling = false query_rewrite_enabled = true query_rewrite_integrity = enforced _query_cost_rewrite = true _query_rewrite_2 = true _query_rewrite_1 = true _query_rewrite_expression = true _query_rewrite_jgmigrate = true _query_rewrite_fpc = true _query_rewrite_drj = true _full_pwise_join_enabled = true _partial_pwise_join_enabled = true _left_nested_loops_random = true _improved_row_length_enabled = true _index_join_enabled = true _enable_type_dep_selectivity = true _improved_outerjoin_card = true _optimizer_adjust_for_nulls = true _optimizer_degree = 0 _use_column_stats_for_function = true _subquery_pruning_enabled = true _subquery_pruning_mv_enabled = false _or_expand_nvl_predicate = true _like_with_bind_as_equality = false _table_scan_cost_plus_one = true _cost_equality_semi_join = true _default_non_equality_sel_check = true _new_initial_join_orders = true _oneside_colstat_for_equijoins = true _optim_peek_user_binds = true _minimal_stats_aggregation = true _force_temptables_for_gsets = false workarea_size_policy = auto _smm_auto_cost_enabled = true _gs_anti_semi_join_allowed = true _optim_new_default_join_sel = true optimizer_dynamic_sampling = 2 _pre_rewrite_push_pred = true _optimizer_new_join_card_computation = true _union_rewrite_for_gs = yes_gset_mvs _generalized_pruning_enabled = true _optim_adjust_for_part_skews = true _force_datefold_trunc = false statistics_level = typical _optimizer_system_stats_usage = true skip_unusable_indexes = true _remove_aggr_subquery = true _optimizer_push_down_distinct = 0 _dml_monitoring_enabled = true _optimizer_undo_changes = false _predicate_elimination_enabled = true _nested_loop_fudge = 100 _project_view_columns = true _local_communication_costing_enabled = true _local_communication_ratio = 50 _query_rewrite_vop_cleanup = true _slave_mapping_enabled = true _optimizer_cost_based_transformation = linear _optimizer_mjc_enabled = true _right_outer_hash_enable = true _spr_push_pred_refspr = true _optimizer_cache_stats = false _optimizer_cbqt_factor = 50 _optimizer_squ_bottomup = true _fic_area_size = 131072 _optimizer_skip_scan_enabled = true _optimizer_cost_filter_pred = false _optimizer_sortmerge_join_enabled = true _optimizer_join_sel_sanity_check = true _mmv_query_rewrite_enabled = 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 _gby_hash_aggregation_enabled = true *************************************** PARAMETERS IN OPT_PARAM HINT **************************** *************************************** Column Usage Monitoring is ON: tracking level = 1 *************************************** **************** QUERY BLOCK TEXT **************** SELECT /* ZAPROS */ * FROM ti_offer_726 WHERE 1=1 AND duration BETWEEN 7 AND 10 and real_price between 0 and 99999 AND departure BETWEEN '18-MAY-06' AND '27-MAY-06' AND meal IN (2,3,4) AND alloccat IN (7,6,5) ********************* QUERY BLOCK SIGNATURE ********************* qb name was generated signature (optimizer): qb_name=SEL$1 nbfros=1 flg=0 fro(0): flg=0 objn=118780 hint_alias="TI_OFFER_726"@"SEL$1" ***************************** SYSTEM STATISTICS INFORMATION ***************************** Using NOWORKLOAD Stats CPUSPEED: 525 millions instruction/sec IOTFRSPEED: 4096 bytes per millisecond (default is 4096) IOSEEKTIM: 10 milliseconds (default is 10) *************************************** BASE STATISTICAL INFORMATION *********************** Table Stats:: Table: TI_OFFER_726 Alias: TI_OFFER_726 #Rows: 2495933 #Blks: 31891 AvgRowLen: 88.00 Index Stats:: Index: TI_OFFER_726_ALLOCATION Col#: 9 LVLS: 2 #LB: 5132 #DK: 636 LB/K: 8.00 DB/K: 257.00 CLUF: 163624.00 Index: TI_OFFER_726_ALLOCCAT Col#: 10 LVLS: 1 #LB: 86 #DK: 9 LB/K: 9.00 DB/K: 19.00 CLUF: 174.00 Index: TI_OFFER_726_ALLSPO Col#: 20 LVLS: 2 #LB: 5439 #DK: 1269 LB/K: 4.00 DB/K: 49.00 CLUF: 63230.00 Index: TI_OFFER_726_BIGIND Col#: 4 6 5 12 10 11 23 LVLS: 2 #LB: 13466 #DK: 2114762 LB/K: 1.00 DB/K: 1.00 CLUF: 2222144.00 Index: TI_OFFER_726_CHILDTYPE Col#: 23 LVLS: 1 #LB: 181 #DK: 12 LB/K: 15.00 DB/K: 30.00 CLUF: 362.00 Index: TI_OFFER_726_DEPARTURE Col#: 4 LVLS: 1 #LB: 225 #DK: 30 LB/K: 7.00 DB/K: 15.00 CLUF: 450.00 Index: TI_OFFER_726_DURATION Col#: 6 LVLS: 1 #LB: 319 #DK: 20 LB/K: 15.00 DB/K: 31.00 CLUF: 638.00 Index: TI_OFFER_726_DUR_DEF Col#: 26 LVLS: 1 #LB: 72 #DK: 2 LB/K: 36.00 DB/K: 71.00 CLUF: 143.00 Index: TI_OFFER_726_ID_PK Col#: 7 LVLS: 2 #LB: 5488 #DK: 2442731 LB/K: 1.00 DB/K: 1.00 CLUF: 150365.00 Index: TI_OFFER_726_MEAL Col#: 12 LVLS: 1 #LB: 78 #DK: 8 LB/K: 9.00 DB/K: 19.00 CLUF: 159.00 Index: TI_OFFER_726_OPERATOR Col#: 8 LVLS: 1 #LB: 76 #DK: 45 LB/K: 1.00 DB/K: 3.00 CLUF: 165.00 Index: TI_OFFER_726_REAL_PRICE Col#: 5 LVLS: 2 #LB: 5394 #DK: 2806 LB/K: 1.00 DB/K: 529.00 CLUF: 1486295.00 Index: TI_OFFER_726_RESORT Col#: 11 LVLS: 1 #LB: 85 #DK: 15 LB/K: 5.00 DB/K: 11.00 CLUF: 176.00 *************************************** SINGLE TABLE ACCESS PATH Column (#12): MEAL(NUMBER) AvgLen: 3.00 NDV: 7 Nulls: 0 Density: 2.0033e-07 Min: 1 Max: 15 Histogram: Freq #Bkts: 7 UncompBkts: 5485 EndPtVals: 7 Column (#10): ALLOCCAT(NUMBER) AvgLen: 3.00 NDV: 8 Nulls: 0 Density: 2.0033e-07 Min: 7 Max: 19 Histogram: Freq #Bkts: 8 UncompBkts: 5485 EndPtVals: 8 Column (#6): DURATION(NUMBER) AvgLen: 3.00 NDV: 20 Nulls: 0 Density: 2.0033e-07 Min: 2 Max: 21 Histogram: Freq #Bkts: 20 UncompBkts: 5485 EndPtVals: 20 Column (#5): REAL_PRICE(NUMBER) AvgLen: 4.00 NDV: 2806 Nulls: 0 Density: 5.8789e-04 Min: 261 Max: 24504 Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 255 Column (#4): DEPARTURE(DATE) AvgLen: 8.00 NDV: 30 Nulls: 0 Density: 2.0033e-07 Min: 2453858 Max: 2453887 Histogram: Freq #Bkts: 30 UncompBkts: 5485 EndPtVals: 30 Table: TI_OFFER_726 Alias: TI_OFFER_726 Card: Original: 2495933 Rounded: 316 Computed: 316.21 Non Adjusted: 316.21 Access Path: TableScan Cost: 6381.08 Resp: 6381.08 Degree: 0 Cost_io: 6147.00 Cost_cpu: 1473698226 Resp_io: 6147.00 Resp_cpu: 1473698226 kkofmx: index filter: ("TI_OFFER_726"."MEAL"=2 OR "TI_OFFER_726"."MEAL"=3 OR "TI_OFFER_726"."MEAL"=4) AND ("TI_OFFER_726"."ALLOCCAT"=5 OR "TI_OFFER_726"."ALLOCCAT"=6 OR "TI_OFFER_726"."ALLOCCAT"=7) AND TO_DATE('18-MAY-06')<=TO_DATE('27-MAY-06') AND "TI_OFFER_726"."DURATION">=7 AND "TI_OFFER_726"."DURATION"<=10 AND "TI_OFFER_726"."REAL_PRICE">=0 AND "TI_OFFER_726"."REAL_PRICE"<=99999 AND "TI_OFFER_726"."DEPARTURE">='18-MAY-06' AND "TI_OFFER_726"."DEPARTURE"<='27-MAY-06' kkofmx: index filter: ("TI_OFFER_726"."ALLOCCAT"=5 OR "TI_OFFER_726"."ALLOCCAT"=6 OR "TI_OFFER_726"."ALLOCCAT"=7) AND TO_DATE('18-MAY-06')<=TO_DATE('27-MAY-06') AND "TI_OFFER_726"."DURATION">=7 AND "TI_OFFER_726"."DURATION"<=10 AND "TI_OFFER_726"."REAL_PRICE">=0 AND "TI_OFFER_726"."REAL_PRICE"<=99999 AND "TI_OFFER_726"."DEPARTURE">='18-MAY-06' AND "TI_OFFER_726"."DEPARTURE"<='27-MAY-06' kkofmx: index filter:"TI_OFFER_726"."DURATION">=7 AND "TI_OFFER_726"."DURATION"<=10 AND "TI_OFFER_726"."REAL_PRICE">=0 AND "TI_OFFER_726"."REAL_PRICE"<=99999 AND "TI_OFFER_726"."DEPARTURE">='18-MAY-06' AND "TI_OFFER_726"."DEPARTURE"<='27-MAY-06' kkofmx: index filter:"TI_OFFER_726"."DURATION"<=10 AND "TI_OFFER_726"."REAL_PRICE">=0 AND "TI_OFFER_726"."REAL_PRICE"<=99999 AND "TI_OFFER_726"."DEPARTURE">='18-MAY-06' AND "TI_OFFER_726"."DEPARTURE"<='27-MAY-06' kkofmx: index filter:"TI_OFFER_726"."REAL_PRICE">=0 AND "TI_OFFER_726"."REAL_PRICE"<=99999 AND "TI_OFFER_726"."DEPARTURE">='18-MAY-06' AND "TI_OFFER_726"."DEPARTURE"<='27-MAY-06' kkofmx: index filter:"TI_OFFER_726"."REAL_PRICE"<=99999 AND "TI_OFFER_726"."DEPARTURE">='18-MAY-06' AND "TI_OFFER_726"."DEPARTURE"<='27-MAY-06' Access Path: index (skip-scan) SS sel: 0.074713 ANDV (#skips): 158001 SS io: 158001.11 vs. index scan io: 4896.00 Skip Scan rejected Access Path: index (RangeScan) Index: TI_OFFER_726_BIGIND resc_io: 5180.00 resc_cpu: 221341119 ix_sel: 0.36354 ix_sel_with_filters: 1.2669e-04 Cost: 785.60 Resp: 785.60 Degree: 1 Access Path: index (RangeScan) Index: TI_OFFER_726_REAL_PRICE resc_io: 1491691.00 resc_cpu: 13097453218 ix_sel: 1 ix_sel_with_filters: 1 Cost: 224065.71 Resp: 224065.71 Degree: 1 ****** trying bitmap/domain indexes ****** Best:: AccessPath: IndexRange Index: TI_OFFER_726_BIGIND Cost: 785.60 Degree: 1 Resp: 785.60 Card: 316.21 Bytes: 0 *************************************** OPTIMIZER STATISTICS AND COMPUTATIONS *************************************** GENERAL PLANS *************************************** Considering cardinality-based initial join order. *********************** Join order[1]: TI_OFFER_726[TI_OFFER_726]#0 *********************** Best so far: Table#: 0 cost: 785.5953 card: 316.2142 bytes: 27808 (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: 785.5953 Degree: 1 Card: 316.0000 Bytes: 27808 Resc: 785.5953 Resc_io: 777.0000 Resc_cpu: 54113761 Resp: 785.5953 Resp_io: 777.0000 Resc_cpu: 54113761 kkoipt: Query block SEL$1 (#0) ******* UNPARSED QUERY IS ******* SELECT "TI_OFFER_726"."CITY" "CITY","TI_OFFER_726"."COUNTRY" "COUNTRY","TI_OFFER_726"."ROOMSIZE" "ROOMSIZE","TI_OFFER_726"."DEPARTURE" "DEPARTURE","TI_OFFER_726"."REAL_PRICE" "REAL_PRICE","TI_OFFER_726"."DURATION" "DURATION","TI_OFFER_726"."ID" "ID","TI_OFFER_726"."OPERATOR" "OPERATOR","TI_OFFER_726"."ALLOCATION" "ALLOCATION","TI_OFFER_726"."ALLOCCAT" "ALLOCCAT","TI_OFFER_726"."RESORT" "RESORT","TI_OFFER_726"."MEAL" "MEAL","TI_OFFER_726"."ROOMTYPE" "ROOMTYPE","TI_OFFER_726"."ROOMVIEW" "ROOMVIEW","TI_OFFER_726"."PRICE" "PRICE","TI_OFFER_726"."CH1FROM" "CH1FROM","TI_OFFER_726"."CH1TO" "CH1TO","TI_OFFER_726"."CH2FROM" "CH2FROM","TI_OFFER_726"."CH2TO" "CH2TO","TI_OFFER_726"."ALLSPO" "ALLSPO","TI_OFFER_726"."STOP" "STOP","TI_OFFER_726"."PARTITION_NUM" "PARTITION_NUM","TI_OFFER_726"."CHILDTYPE" "CHILDTYPE","TI_OFFER_726"."PRICE_HELPER" "PRICE_HELPER","TI_OFFER_726"."DURATION_TYPE" "DURATION_TYPE" FROM "TIUSER"."TI_OFFER_726" "TI_OFFER_726" WHERE TO_DATE('18-MAY-06')<=TO_DATE('27-MAY-06') AND "TI_OFFER_726"."DEPARTURE"<='27-MAY-06' AND "TI_OFFER_726"."DEPARTURE">='18-MAY-06' AND "TI_OFFER_726"."DURATION"<=10 AND ("TI_OFFER_726"."ALLOCCAT"=5 OR "TI_OFFER_726"."ALLOCCAT"=6 OR "TI_OFFER_726"."ALLOCCAT"=7) AND ("TI_OFFER_726"."MEAL"=2 OR "TI_OFFER_726"."MEAL"=3 OR "TI_OFFER_726"."MEAL"=4) AND "TI_OFFER_726"."DURATION">=7 AND "TI_OFFER_726"."REAL_PRICE">=0 AND "TI_OFFER_726"."REAL_PRICE"<=99999 kkoqbc-end : call(in-use=78576, alloc=218808), compile(in-use=36008, alloc=36696) apadrv-end: call(in-use=78576, alloc=218808), compile(in-use=36528, alloc=36696) sql_id=44kb1djhswktx. Current SQL statement for this session: explain plan for SELECT /* ZAPROS */ * FROM ti_offer_726 WHERE 1=1 AND duration BETWEEN 7 AND 10 and real_price between 0 and 99999 AND departure BETWEEN '18-MAY-06' AND '27-MAY-06' AND meal IN (2,3,4) AND alloccat IN (7,6,5) ============ Plan Table ============ -----------------------------------------------------------+-----------------------------------+ | Id | Operation | Name | Rows | Bytes | Cost | Time | -----------------------------------------------------------+-----------------------------------+ | 0 | SELECT STATEMENT | | | | 786 | | | 1 | FILTER | | | | | | | 2 | TABLE ACCESS BY INDEX ROWID | TI_OFFER_726 | 316 | 27K | 786 | 00:00:10 | | 3 | INDEX RANGE SCAN | TI_OFFER_726_BIGIND| 316 | | 743 | 00:00:09 | -----------------------------------------------------------+-----------------------------------+ Predicate Information: ---------------------- 1 - filter(TO_DATE('18-MAY-06')<=TO_DATE('27-MAY-06')) 3 - access("DEPARTURE">='18-MAY-06' AND "DURATION">=7 AND "REAL_PRICE">=0 AND "DEPARTURE"<='27-MAY-06' AND "DURATION"<=10 AND "REAL_PRICE"<=99999) 3 - filter(("DURATION"<=10 AND INTERNAL_FUNCTION("ALLOCCAT") AND INTERNAL_FUNCTION("MEAL") AND "DURATION">=7 AND "REAL_PRICE">=0 AND "REAL_PRICE"<=99999)) Content of other_xml column =========================== db_version : 10.2.0.1 parse_schema : TIUSER plan_hash : 558334835 Outline Data: /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('10.2.0.1') OPT_PARAM('optimizer_index_cost_adj' 15) OPT_PARAM('optimizer_index_caching' 70) ALL_ROWS OUTLINE_LEAF(@"SEL$1") INDEX(@"SEL$1" "TI_OFFER_726"@"SEL$1" ("TI_OFFER_726"."DEPARTURE" "TI_OFFER_726"."DURATION" "TI_OFFER_726"."REAL_PRICE" "TI_OFFER_726"."MEAL" "TI_OFFER_726"."ALLOCCAT" "TI_OFFER_726"."RESORT" "TI_OFFER_726"."CHILDTYPE")) 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.1 _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 = 32 _optimizer_max_permutations = 2000 pga_aggregate_target = 793600 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 = 793 KB _smm_max_size = 102400 KB _smm_px_max_size = 396800 KB _cpu_to_io = 0 _optimizer_undo_cost_change = 10.2.0.1 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 = 15 optimizer_index_caching = 70 _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 _gby_hash_aggregation_enabled = true Query Block Registry: ********************* SEL$1 0xaa39b688 (PARSER) [FINAL] Optimizer State Dump: call(in-use=82420, alloc=218808), compile(in-use=69688, alloc=118660)