/usr/local/oracle/admin/prod/udump/prod1_ora_20991.trc Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production With the Partitioning, Real Application Clusters, OLAP and Data Mining options ORACLE_HOME = /usr/local/oracle/product/10.2.0 System name: Linux Node name: brvbsproda Release: 2.6.9-22.ELsmp Version: #1 SMP Mon Sep 19 18:00:54 EDT 2005 Machine: x86_64 Instance name: prod1 Redo thread mounted by this instance: 1 Oracle process number: 82 Unix process pid: 20991, image: oracleprod1@brvbsproda *** 2008-04-11 15:16:08.797 *** ACTION NAME:() 2008-04-11 15:16:08.797 *** MODULE NAME:(SQL*Plus) 2008-04-11 15:16:08.797 *** SERVICE NAME:(SYS$USERS) 2008-04-11 15:16:08.797 *** SESSION ID:(216.40405) 2008-04-11 15:16:08.797 Registered qb: SEL$1 0x92463740 (PARSER) signature (): qb_name=SEL$1 nbfros=2 flg=0 fro(0): flg=4 objn=2335998 hint_alias="MVT"@"SEL$1" fro(1): flg=4 objn=2336056 hint_alias="TREDRESS"@"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 1avdvyv4c6snk. Query block (0x192463740) before join elimination: SQL:******* UNPARSED QUERY IS ******* SELECT /*+ INDEX ("MVT" "FK_T_MOUVEMENTSTOCK11") */ "MVT"."ID_MOUVEMENTSTOCK" "ID_MOUVEMENTSTOCK","MVT"."ID_SOUSSTOCK" "ID_SOUSSTOCK","MVT"."ID_LIGNERECEPTION" "ID_LIGNERECEPTION","MVT"."ID_TYPEMOUVEMENTSTOCK" "ID_TYPEMOUVEMENTSTOCK","MVT"."QUANTITE" "QUANTITE","MVT"."ID_BONCLIENT" "ID_BONCLIENT","MVT"."ID_RECEPTION" "ID_RECEPTION","MVT"."ID_LIGNERETOURFOURNISSEUR" "ID_LIGNERETOURFOURNISSEUR","MVT"."COMMENTAIRE" "COMMENTAIRE","MVT"."ESTVALIDE" "ESTVALIDE","MVT"."ID_REDRESSEMENT" "ID_REDRESSEMENT","MVT"."DATEMOUVEMENTSTOCK" "DATEMOUVEMENTSTOCK","MVT"."ESTRECTIFIE" "ESTRECTIFIE","MVT"."PMPMOUVEMENT" "PMPMOUVEMENT","MVT"."PMPAPRESMOUVEMENT" "PMPAPRESMOUVEMENT","MVT"."ID_PRODUIT" "ID_PRODUIT","MVT"."QUANTITESTOCKAVANT" "QUANTITESTOCKAVANT","MVT"."PMPAVANTMOUVEMENT" "PMPAVANTMOUVEMENT","MVT"."ID_AGENCE" "ID_AGENCE","MVT"."PRIXTRANSACTION" "PRIXTRANSACTION","MVT"."ID_FACTUREFOURNISSEUR" "ID_FACTUREFOURNISSEUR" FROM "SAMSE"."T_REDRESSEMENT" "TREDRESS","SAMSE"."T_MOUVEMENTSTOCK" "MVT" WHERE "MVT"."ID_REDRESSEMENT"="TREDRESS"."ID_REDRESSEMENT" AND "TREDRESS"."DATEDERNIEREMODIFICATION">=TO_DATE('01/03/08','DD/MM/YY') AND "TREDRESS"."DATEDERNIEREMODIFICATION"=TO_DATE('01/03/08','DD/MM/YY') AND "TREDRESS"."DATEDERNIEREMODIFICATION"=TO_DATE('01/03/08','DD/MM/YY') AND "TREDRESS"."DATEDERNIEREMODIFICATION"=TO_DATE('01/03/08','DD/MM/YY') AND "TREDRESS"."DATEDERNIEREMODIFICATION"TO_DATE('01/03/08','DD/MM/YY') after transitive predicate generation: "MVT"."ID_REDRESSEMENT"="TREDRESS"."ID_REDRESSEMENT" AND "TREDRESS"."DATEDERNIEREMODIFICATION">=TO_DATE('01/03/08','DD/MM/YY') AND "TREDRESS"."DATEDERNIEREMODIFICATION"TO_DATE('01/03/08','DD/MM/YY') finally: "MVT"."ID_REDRESSEMENT"="TREDRESS"."ID_REDRESSEMENT" AND "TREDRESS"."DATEDERNIEREMODIFICATION">=TO_DATE('01/03/08','DD/MM/YY') AND "TREDRESS"."DATEDERNIEREMODIFICATION"TO_DATE('01/03/08','DD/MM/YY') FPD: Following transitive predicates are generated in SEL$1 (#0) : TO_DATE('01/03/08','DD/MM/YY')TO_DATE('01/03/08','DD/MM/YY') apadrv-start: call(in-use=1328, alloc=16344), compile(in-use=40744, alloc=44736) kkoqbc-start : call(in-use=1336, alloc=16344), compile(in-use=41896, alloc=44736) ****************************************** Current SQL statement for this session: explain plan for select /*+ index(mvt,FK_T_MOUVEMENTSTOCK11) */ mvt.* from samse.t_redressement tredress,samse.t_mouvementstock mvt where mvt.id_redressement=tredress.id_redressement and tredress.datedernieremodification >= TO_DATE ('01/03/08', 'DD/MM/YY') AND tredress.datedernieremodification < (TO_DATE ('31/03/08', 'DD/MM/YY') + 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 ****************************** _pga_max_size = 209700 KB ********************************* 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 ************************************* 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.3 _optimizer_search_limit = 5 cpu_count = 4 active_instance_count = 3 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 = 1048576 KB _query_rewrite_maxdisjunct = 257 _smm_auto_min_io_size = 56 KB _smm_auto_max_io_size = 248 KB _smm_min_size = 1024 KB _smm_max_size = 104850 KB _smm_px_max_size = 524288 KB _cpu_to_io = 0 _optimizer_undo_cost_change = 10.2.0.3 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 _optimizer_native_full_outer_join = off ********************************* 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 *************************************** PARAMETERS IN OPT_PARAM HINT **************************** *************************************** Column Usage Monitoring is ON: tracking level = 1 *************************************** **************** QUERY BLOCK TEXT **************** select /*+ index(mvt,FK_T_MOUVEMENTSTOCK11) */ mvt.* from samse.t_redressement tredress,samse.t_mouvementstock mvt where mvt.id_redressement=tredress.id_redressement and tredress.datedernieremodification >= TO_DATE ('01/03/08', 'DD/MM/YY') AND tredress.datedernieremodification < (TO_DATE ('31/03/08', 'DD/MM/YY') + 1) ********************* QUERY BLOCK SIGNATURE ********************* qb name was generated signature (optimizer): qb_name=SEL$1 nbfros=2 flg=0 fro(0): flg=0 objn=2335998 hint_alias="MVT"@"SEL$1" fro(1): flg=0 objn=2336056 hint_alias="TREDRESS"@"SEL$1" ***************************** SYSTEM STATISTICS INFORMATION ***************************** Using NOWORKLOAD Stats CPUSPEED: 714 millions instruction/sec IOTFRSPEED: 4096 bytes per millisecond (default is 4096) IOSEEKTIM: 10 milliseconds (default is 10) *************************************** BASE STATISTICAL INFORMATION *********************** Table Stats:: Table: T_MOUVEMENTSTOCK Alias: MVT #Rows: 19027751 #Blks: 193359 AvgRowLen: 68.00 Column (#11): ID_REDRESSEMENT(NUMBER) AvgLen: 2.00 NDV: 1080855 Nulls: 17637262 Density: 9.2519e-07 Min: 1 Max: 3417795 Index Stats:: Index: FK_T_MOUVEMENTSTOCK11 Col#: 11 LVLS: 2 #LB: 4263 #DK: 1080855 LB/K: 1.00 DB/K: 1.00 CLUF: 631213.00 User hint to use this index Index: FK_T_MOUVEMENTSTOCK12 Col#: 12 LVLS: 2 #LB: 73642 #DK: 5925766 LB/K: 1.00 DB/K: 1.00 CLUF: 5880558.00 Index: FK_T_MOUVEMENTSTOCK13 Col#: 19 LVLS: 2 #LB: 43787 #DK: 99 LB/K: 442.00 DB/K: 38741.00 CLUF: 3835396.00 Index: FK_T_MOUVEMENTSTOCK2 Col#: 2 LVLS: 2 #LB: 63253 #DK: 1691733 LB/K: 1.00 DB/K: 10.00 CLUF: 18402793.00 Index: FK_T_MOUVEMENTSTOCK3 Col#: 3 LVLS: 2 #LB: 8610 #DK: 2441263 LB/K: 1.00 DB/K: 1.00 CLUF: 1228239.00 Index: FK_T_MOUVEMENTSTOCK4 Col#: 4 LVLS: 2 #LB: 46934 #DK: 19 LB/K: 2470.00 DB/K: 43991.00 CLUF: 835841.00 Index: FK_T_MOUVEMENTSTOCK6 Col#: 6 LVLS: 2 #LB: 40827 #DK: 4902334 LB/K: 1.00 DB/K: 1.00 CLUF: 4574540.00 Index: FK_T_MOUVEMENTSTOCK7 Col#: 7 LVLS: 2 #LB: 8277 #DK: 673396 LB/K: 1.00 DB/K: 1.00 CLUF: 694164.00 Index: FK_T_MOUVEMENTSTOCK8 Col#: 8 LVLS: 1 #LB: 182 #DK: 71665 LB/K: 1.00 DB/K: 1.00 CLUF: 39505.00 Index: FK_T_MOUVEMENTSTOCK_14 Col#: 21 LVLS: 1 #LB: 115 #DK: 20691 LB/K: 1.00 DB/K: 1.00 CLUF: 21045.00 Index: PK_T_MOUVEMENTSTOCK Col#: 1 LVLS: 2 #LB: 57028 #DK: 19027751 LB/K: 1.00 DB/K: 1.00 CLUF: 5862269.00 Index: T_MOUVEMENTSTOCK_IDX1 Col#: 16 19 LVLS: 2 #LB: 52591 #DK: 407577 LB/K: 1.00 DB/K: 16.00 CLUF: 6910114.00 *********************** Table Stats:: Table: T_REDRESSEMENT Alias: TREDRESS #Rows: 1111683 #Blks: 12276 AvgRowLen: 68.00 Column (#1): ID_REDRESSEMENT(NUMBER) AvgLen: 6.00 NDV: 1111683 Nulls: 0 Density: 8.9954e-07 Min: 1 Max: 3417795 Index Stats:: Index: FK_T_REDRESSEMENT10 Col#: 12 LVLS: 2 #LB: 531 #DK: 18317 LB/K: 1.00 DB/K: 1.00 CLUF: 21845.00 Index: FK_T_REDRESSEMENT3 Col#: 2 LVLS: 2 #LB: 1782 #DK: 438161 LB/K: 1.00 DB/K: 1.00 CLUF: 509801.00 Index: FK_T_REDRESSEMENT4 Col#: 3 LVLS: 2 #LB: 2395 #DK: 633084 LB/K: 1.00 DB/K: 1.00 CLUF: 408396.00 Index: FK_T_REDRESSEMENT6 Col#: 5 LVLS: 2 #LB: 2280 #DK: 12 LB/K: 190.00 DB/K: 5267.00 CLUF: 63207.00 Index: FK_T_REDRESSEMENT8 Col#: 7 LVLS: 2 #LB: 2599 #DK: 978 LB/K: 2.00 DB/K: 247.00 CLUF: 241701.00 Index: FK_T_REDRESSEMENT9 Col#: 8 LVLS: 2 #LB: 2599 #DK: 978 LB/K: 2.00 DB/K: 247.00 CLUF: 241913.00 Index: PK_T_REDRESSEMENT Col#: 1 LVLS: 2 #LB: 2406 #DK: 1111683 LB/K: 1.00 DB/K: 1.00 CLUF: 376981.00 *************************************** SINGLE TABLE ACCESS PATH Column (#10): DATEDERNIEREMODIFICATION(DATE) AvgLen: 8.00 NDV: 611244 Nulls: 0 Density: 1.6360e-06 Min: 2453099 Max: 2454568 Table: T_REDRESSEMENT Alias: TREDRESS Card: Original: 1111683 Rounded: 23464 Computed: 23464.21 Non Adjusted: 23464.21 Access Path: TableScan Cost: 3447.42 Resp: 3447.42 Degree: 0 Cost_io: 3327.00 Cost_cpu: 1031750570 Resp_io: 3327.00 Resp_cpu: 1031750570 Best:: AccessPath: TableScan Cost: 3447.42 Degree: 1 Resp: 3447.42 Card: 23464.21 Bytes: 0 *************************************** SINGLE TABLE ACCESS PATH Table: T_MOUVEMENTSTOCK Alias: MVT Card: Original: 19027751 Rounded: 1390489 Computed: 1390489.00 Non Adjusted: 1390489.00 kkofmx: index filter:"MVT"."ID_REDRESSEMENT" IS NOT NULL Access Path: index (FullScan) Index: FK_T_MOUVEMENTSTOCK11 resc_io: 635478.00 resc_cpu: 5596194978 ix_sel: 1 ix_sel_with_filters: 1 Cost: 636131.17 Resp: 636131.17 Degree: 1 Best:: AccessPath: IndexRange Index: FK_T_MOUVEMENTSTOCK11 Cost: 636131.17 Degree: 1 Resp: 636131.17 Card: 1390489.00 Bytes: 0 *************************************** OPTIMIZER STATISTICS AND COMPUTATIONS *************************************** GENERAL PLANS *************************************** Considering cardinality-based initial join order. Permutations for Starting Table :0 *********************** Join order[1]: T_REDRESSEMENT[TREDRESS]#0 T_MOUVEMENTSTOCK[MVT]#1 *************** Now joining: T_MOUVEMENTSTOCK[MVT]#1 *************** NL Join Outer table: Card: 23464.21 Cost: 3447.42 Resp: 3447.42 Degree: 1 Bytes: 14 kkofmx: index filter:"MVT"."ID_REDRESSEMENT" IS NOT NULL Access Path: index (AllEqJoinGuess) Index: FK_T_MOUVEMENTSTOCK11 resc_io: 10.00 resc_cpu: 85916 ix_sel: 1.2661e-05 ix_sel_with_filters: 1.2661e-05 NL Join: Cost: 233112.18 Resp: 233112.18 Degree: 1 Cost_io: 232761.57 Cost_cpu: 3003954067 Resp_io: 232761.57 Resp_cpu: 3003954067 Best NL cost: 233112.18 resc: 233112.18 resc_io: 232761.57 resc_cpu: 3003954067 resp: 233112.18 resp_io: 232761.57 resp_cpu: 3003954067 Join Card: 30186.03 = outer (23464.21) * inner (1390489.00) * sel (9.2519e-07) Join Card - Rounded: 30186 Computed: 30186.03 SM Join Outer table: resc: 3447.42 card 23464.21 bytes: 14 deg: 1 resp: 3447.42 Inner table: T_MOUVEMENTSTOCK Alias: MVT resc: 636131.17 card: 1390489.00 bytes: 68 deg: 1 resp: 636131.17 using dmeth: 2 #groups: 1 SORT resource Sort statistics Sort width: 612 Area size: 1048576 Max Area size: 107366400 Degree: 1 Blocks to Sort: 75 Row size: 26 Total Rows: 23464 Initial runs: 1 Merge passes: 0 IO Cost / pass: 0 Total IO sort cost: 0 Total CPU sort cost: 23915767 Total Temp space used: 0 SORT resource Sort statistics Sort width: 612 Area size: 1048576 Max Area size: 107366400 Degree: 1 Blocks to Sort: 14471 Row size: 85 Total Rows: 1390489 Initial runs: 2 Merge passes: 1 IO Cost / pass: 7840 Total IO sort cost: 22311 Total CPU sort cost: 1643395944 Total Temp space used: 325477000 SM join: Resc: 662084.20 Resp: 662084.20 [multiMatchCost=0.00] SM cost: 662084.20 resc: 662084.20 resc_io: 661116.00 resc_cpu: 8295257258 resp: 662084.20 resp_io: 661116.00 resp_cpu: 8295257258 SM Join (with index on outer) Access Path: index (FullScan) Index: PK_T_REDRESSEMENT resc_io: 379389.00 resc_cpu: 3868460372 ix_sel: 1 ix_sel_with_filters: 1 Cost: 379840.51 Resp: 379840.51 Degree: 1 Outer table: resc: 379840.51 card 23464.21 bytes: 14 deg: 1 resp: 379840.51 Inner table: T_MOUVEMENTSTOCK Alias: MVT resc: 636131.17 card: 1390489.00 bytes: 68 deg: 1 resp: 636131.17 using dmeth: 2 #groups: 1 SORT resource Sort statistics Sort width: 612 Area size: 1048576 Max Area size: 107366400 Degree: 1 Blocks to Sort: 14471 Row size: 85 Total Rows: 1390489 Initial runs: 2 Merge passes: 1 IO Cost / pass: 7840 Total IO sort cost: 22311 Total CPU sort cost: 1643395944 Total Temp space used: 325477000 SM join: Resc: 1038474.50 Resp: 1038474.50 [multiMatchCost=0.00] HA Join Outer table: resc: 3447.42 card 23464.21 bytes: 14 deg: 1 resp: 3447.42 Inner table: T_MOUVEMENTSTOCK Alias: MVT resc: 636131.17 card: 1390489.00 bytes: 68 deg: 1 resp: 636131.17 using dmeth: 2 #groups: 1 Cost per ptn: 17.14 #ptns: 1 hash_area: 256 (max=26213) Hash join: Resc: 639595.73 Resp: 639595.73 [multiMatchCost=0.00] HA cost: 639595.73 resc: 639595.73 resc_io: 638805.00 resc_cpu: 6774797919 resp: 639595.73 resp_io: 638805.00 resp_cpu: 6774797919 Best:: JoinMethod: NestedLoop Cost: 233112.18 Degree: 1 Resp: 233112.18 Card: 30186.03 Bytes: 82 *********************** Best so far: Table#: 0 cost: 3447.4227 card: 23464.2069 bytes: 328496 Table#: 1 cost: 233112.1844 card: 30186.0301 bytes: 2475252 *********************** Join order[2]: T_MOUVEMENTSTOCK[MVT]#1 T_REDRESSEMENT[TREDRESS]#0 Join order aborted: cost > best plan cost *********************** (newjo-stop-1) k:0, spcnt:0, perm:2, maxperm:2000 ********************************* Number of join permutations tried: 2 ********************************* (newjo-save) [1 0 ] Final - All Rows Plan: Best join order: 1 Cost: 233112.1844 Degree: 1 Card: 30186.0000 Bytes: 2475252 Resc: 233112.1844 Resc_io: 232761.5723 Resc_cpu: 3003954067 Resp: 233112.1844 Resp_io: 232761.5723 Resc_cpu: 3003954067 kkoipt: Query block SEL$1 (#0) ******* UNPARSED QUERY IS ******* SELECT /*+ INDEX ("MVT" "FK_T_MOUVEMENTSTOCK11") */ "MVT"."ID_MOUVEMENTSTOCK" "ID_MOUVEMENTSTOCK","MVT"."ID_SOUSSTOCK" "ID_SOUSSTOCK","MVT"."ID_LIGNERECEPTION" "ID_LIGNERECEPTION","MVT"."ID_TYPEMOUVEMENTSTOCK" "ID_TYPEMOUVEMENTSTOCK","MVT"."QUANTITE" "QUANTITE","MVT"."ID_BONCLIENT" "ID_BONCLIENT","MVT"."ID_RECEPTION" "ID_RECEPTION","MVT"."ID_LIGNERETOURFOURNISSEUR" "ID_LIGNERETOURFOURNISSEUR","MVT"."COMMENTAIRE" "COMMENTAIRE","MVT"."ESTVALIDE" "ESTVALIDE","MVT"."ID_REDRESSEMENT" "ID_REDRESSEMENT","MVT"."DATEMOUVEMENTSTOCK" "DATEMOUVEMENTSTOCK","MVT"."ESTRECTIFIE" "ESTRECTIFIE","MVT"."PMPMOUVEMENT" "PMPMOUVEMENT","MVT"."PMPAPRESMOUVEMENT" "PMPAPRESMOUVEMENT","MVT"."ID_PRODUIT" "ID_PRODUIT","MVT"."QUANTITESTOCKAVANT" "QUANTITESTOCKAVANT","MVT"."PMPAVANTMOUVEMENT" "PMPAVANTMOUVEMENT","MVT"."ID_AGENCE" "ID_AGENCE","MVT"."PRIXTRANSACTION" "PRIXTRANSACTION","MVT"."ID_FACTUREFOURNISSEUR" "ID_FACTUREFOURNISSEUR" FROM "SAMSE"."T_REDRESSEMENT" "TREDRESS","SAMSE"."T_MOUVEMENTSTOCK" "MVT" WHERE TO_DATE('01/03/08','DD/MM/YY')=TO_DATE('01/03/08','DD/MM/YY') AND "TREDRESS"."DATEDERNIEREMODIFICATION"= TO_DATE ('01/03/08', 'DD/MM/YY') AND tredress.datedernieremodification < (TO_DATE ('31/03/08', 'DD/MM/YY') + 1) ============ Plan Table ============ -------------------------------------------------------------+-----------------------------------+ | Id | Operation | Name | Rows | Bytes | Cost | Time | -------------------------------------------------------------+-----------------------------------+ | 0 | SELECT STATEMENT | | | | 228K | | | 1 | FILTER | | | | | | | 2 | TABLE ACCESS BY INDEX ROWID | T_MOUVEMENTSTOCK | 1 | 68 | 10 | 00:00:01 | | 3 | NESTED LOOPS | | 29K | 2417K | 228K | 00:47:38 | | 4 | TABLE ACCESS FULL | T_REDRESSEMENT | 23K | 321K | 3447 | 00:00:42 | | 5 | INDEX RANGE SCAN | FK_T_MOUVEMENTSTOCK11| 18 | | 2 | 00:00:01 | -------------------------------------------------------------+-----------------------------------+ Predicate Information: ---------------------- 1 - filter(TO_DATE('01/03/08','DD/MM/YY')=TO_DATE('01/03/08','DD/MM/YY') AND "TREDRESS"."DATEDERNIEREMODIFICATION"