/u01/app/oracle/admin/orcl/udump/orcl_ora_18565.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: ocp.mycorpdomain.com Release: 2.6.9-42.ELsmp Version: #1 SMP Wed Jul 12 23:27:17 EDT 2006 Machine: i686 Instance name: orcl Redo thread mounted by this instance: 1 Oracle process number: 15 Unix process pid: 18565, image: oracle@ocp.mycorpdomain.com (TNS V1-V3) *** 2011-03-15 18:46:51.058 *** ACTION NAME:() 2011-03-15 18:46:51.051 *** MODULE NAME:(SQL*Plus) 2011-03-15 18:46:51.051 *** SERVICE NAME:(SYS$USERS) 2011-03-15 18:46:51.051 *** SESSION ID:(159.9) 2011-03-15 18:46:51.051 Registered qb: SEL$1 0xb72efe60 (PARSER) signature (): qb_name=SEL$1 nbfros=2 flg=0 fro(0): flg=4 objn=52813 hint_alias="T1"@"SEL$1" fro(1): flg=5 objn=52793 hint_alias="V1"@"SEL$1" Registered qb: SEL$2 0xb72ef03c (PARSER) signature (): qb_name=SEL$2 nbfros=2 flg=0 fro(0): flg=4 objn=52814 hint_alias="T2"@"SEL$2" fro(1): flg=4 objn=52815 hint_alias="T3"@"SEL$2" ************************** 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 passed for a46qsa3huppqh. voptcojrj: logp:0xb72ef628 voptcojrj:"T1"."N"=1 rejected voptcojrj:"T1"."ID"="V1"."ID2_1"(+) rejected Query block (0xb72ef03c) before join elimination: SQL:******* UNPARSED QUERY IS ******* SELECT "T2"."ID" "ID2_1","T2"."NAME" "NAME2" FROM SCOTT."T_PRED2" "T2",SCOTT."T_PRED3" "T3" WHERE "T2"."ID"="T3"."ID" AND "T2"."ID2"="T3"."ID2" Query block (0xb72ef03c) unchanged Query block (0xb72efe60) before join elimination: SQL:******* UNPARSED QUERY IS ******* SELECT "T1"."NAME" "NAME","V1"."NAME2" "NAME2","V1"."NAME2" "NAME2" FROM "SCOTT"."T_PRED1" "T1", (SELECT "T2"."ID" "ID2_1","T2"."NAME" "NAME2" FROM SCOTT."T_PRED2" "T2",SCOTT."T_PRED3" "T3" WHERE "T2"."ID"="T3"."ID" AND "T2"."ID2"="T3"."ID2") "V1" WHERE "T1"."N"=1 AND "T1"."ID"="V1"."ID2_1"(+) Query block (0xb72efe60) unchanged apadrv-start: call(in-use=2004, alloc=16360), compile(in-use=40260, alloc=41816) ****************************************** Current SQL statement for this session: select /*+ gather_plan_statistics cost_based */ t1.name, v1.name2 , v1.name2 from t_pred1 t1, v_pred v1 where t1.n = 1 and t1.id = v1.id2_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 ****************************** ************************************* 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 = 2 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 = 16384 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 = 128 KB _smm_max_size = 3276 KB _smm_px_max_size = 8192 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 = 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 _gby_hash_aggregation_enabled = true *************************************** PARAMETERS IN OPT_PARAM HINT **************************** *************************************** Column Usage Monitoring is ON: tracking level = 1 *************************************** ******************************** COST-BASED QUERY TRANSFORMATIONS ******************************** FPD: Considering simple filter push (pre rewrite) in SEL$1 (#0) FPD: Current where clause predicates in SEL$1 (#0) : "T1"."N"=1 AND "T1"."ID"="V1"."ID2_1"(+) kkogcp: try to generate transitive predicate from check constraints for SEL$1 (#0) predicates with check contraints: "T1"."N"=1 AND "T1"."ID"="V1"."ID2_1"(+) after transitive predicate generation: "T1"."N"=1 AND "T1"."ID"="V1"."ID2_1"(+) finally: "T1"."N"=1 AND "T1"."ID"="V1"."ID2_1"(+) FPD: Considering simple filter push (pre rewrite) in SEL$2 (#0) FPD: Current where clause predicates in SEL$2 (#0) : "T2"."ID"="T3"."ID" AND "T2"."ID2"="T3"."ID2" Registered qb: SEL$1 0xb72e9364 (COPY SEL$1) signature(): NULL Registered qb: SEL$2 0xb72e6ba8 (COPY SEL$2) signature(): NULL ***************************** Cost-Based Subquery Unnesting ***************************** SU: No subqueries to consider in query block SEL$2 (#2). SU: No subqueries to consider in query block SEL$1 (#1). ******************************* Cost-Based Complex View Merging ******************************* CVM: Finding query blocks in SEL$1 (#1) that are valid to merge. voptcojrj: logp:0xb72e537c voptcojrj:"T1"."N"=1 rejected voptcojrj:"T1"."ID"="V1"."ID2_1"(+) rejected CVM: Checking validity of merging SEL$2 (#2) Query block (0xb72e6ba8) before join elimination: SQL:******* UNPARSED QUERY IS ******* SELECT /*+ */ "T2"."ID" "ID2_1","T2"."NAME" "NAME2" FROM "SCOTT"."T_PRED2" "T2","SCOTT"."T_PRED3" "T3" WHERE "T2"."ID"="T3"."ID" AND "T2"."ID2"="T3"."ID2" Query block (0xb72e6ba8) unchanged CVM: CVM bypassed: View on right side of outer join contains more than one table. Query block (0xb72e9364) before join elimination: SQL:******* UNPARSED QUERY IS ******* SELECT /*+ */ "T1"."NAME" "NAME","V1"."NAME2" "NAME2","V1"."NAME2" "NAME2" FROM "SCOTT"."T_PRED1" "T1", (SELECT /*+ */ "T2"."ID" "ID2_1","T2"."NAME" "NAME2" FROM "SCOTT"."T_PRED2" "T2","SCOTT"."T_PRED3" "T3" WHERE "T2"."ID"="T3"."ID" AND "T2"."ID2"="T3"."ID2") "V1" WHERE "T1"."N"=1 AND "T1"."ID"="V1"."ID2_1"(+) Query block (0xb72e9364) unchanged ************************* Set-Join Conversion (SJC) ************************* SJC: Considering set-join conversion in SEL$1 (#1). ************************* Set-Join Conversion (SJC) ************************* SJC: Considering set-join conversion in SEL$2 (#2). voptcojrj: logp:0xb72ef628 voptcojrj:"T1"."N"=1 rejected voptcojrj:"T1"."ID"="V1"."ID2_1"(+) rejected Query block (0xb72ef03c) before join elimination: SQL:******* UNPARSED QUERY IS ******* SELECT "T2"."ID" "ID2_1","T2"."NAME" "NAME2" FROM SCOTT."T_PRED2" "T2",SCOTT."T_PRED3" "T3" WHERE "T2"."ID"="T3"."ID" AND "T2"."ID2"="T3"."ID2" Query block (0xb72ef03c) unchanged Query block (0xb72efe60) before join elimination: SQL:******* UNPARSED QUERY IS ******* SELECT "T1"."NAME" "NAME","V1"."NAME2" "NAME2","V1"."NAME2" "NAME2" FROM "SCOTT"."T_PRED1" "T1", (SELECT "T2"."ID" "ID2_1","T2"."NAME" "NAME2" FROM SCOTT."T_PRED2" "T2",SCOTT."T_PRED3" "T3" WHERE "T2"."ID"="T3"."ID" AND "T2"."ID2"="T3"."ID2") "V1" WHERE "T1"."N"=1 AND "T1"."ID"="V1"."ID2_1"(+) Query block (0xb72efe60) unchanged ************************** Predicate Move-Around (PM) ************************** PM: Considering predicate move-around in SEL$1 (#1). PM: Checking validity of predicate move-around in SEL$1 (#1). PM: PM bypassed: View on the right side of semi, anti, or non-group outer join. PM: Passed validity checks. *********************************** Cost-Based Filter Predicate Pull-Up *********************************** *********************************** Cost-Based Join Predicate Push-down *********************************** JPPD: Checking validity of push-down in query block SEL$2 (#2) JPPD: No view found to push predicate into. JPPD: Checking validity of push-down in query block SEL$1 (#1) JPPD: Checking validity of push-down from SEL$1 (#1) to SEL$2 (#2) JPPD: Passed validity checks: push-down from SEL$1 (#1) to SEL$2 (#2) JPPD: Pushdown from SEL$1 (#1) passed validity checks. JPPD: Using search type: linear JPPD: Considering join predicate push-down on query block SEL$1 (#1) JPPD: Starting iteration 1, state space = (2) : (0) JPPD: Performing join predicate push-down (no transformation phase) From SEL$1 (#1) to SEL$2 (#2) FPD: Considering simple filter push in SEL$1 (#1) FPD: Current where clause predicates in SEL$1 (#1) : "T1"."N"=1 AND "T1"."ID"="V1"."ID2_1"(+) kkogcp: try to generate transitive predicate from check constraints for SEL$1 (#1) predicates with check contraints: "T1"."N"=1 AND "T1"."ID"="V1"."ID2_1"(+) after transitive predicate generation: "T1"."N"=1 AND "T1"."ID"="V1"."ID2_1"(+) finally: "T1"."N"=1 AND "T1"."ID"="V1"."ID2_1"(+) FPD: Considering simple filter push in SEL$2 (#2) FPD: Current where clause predicates in SEL$2 (#2) : "T2"."ID"="T3"."ID" AND "T2"."ID2"="T3"."ID2" kkogcp: try to generate transitive predicate from check constraints for SEL$2 (#2) predicates with check contraints: "T2"."ID"="T3"."ID" AND "T2"."ID2"="T3"."ID2" after transitive predicate generation: "T2"."ID"="T3"."ID" AND "T2"."ID2"="T3"."ID2" finally: "T2"."ID"="T3"."ID" AND "T2"."ID2"="T3"."ID2" JPPD: Costing transformed query. kkoqbc-start : call(in-use=3388, alloc=16360), compile(in-use=130992, alloc=132288) **************** QUERY BLOCK TEXT **************** Not available. ********************* QUERY BLOCK SIGNATURE ********************* qb name was generated signature (optimizer): qb_name=SEL$2 nbfros=2 flg=0 fro(0): flg=0 objn=52814 hint_alias="T2"@"SEL$2" fro(1): flg=0 objn=52815 hint_alias="T3"@"SEL$2" ***************************** SYSTEM STATISTICS INFORMATION ***************************** Using NOWORKLOAD Stats CPUSPEED: 999 millions instruction/sec IOTFRSPEED: 4096 bytes per millisecond (default is 4096) IOSEEKTIM: 10 milliseconds (default is 10) *************************************** BASE STATISTICAL INFORMATION *********************** Table Stats:: Table: T_PRED3 Alias: T3 #Rows: 20000 #Blks: 111 AvgRowLen: 31.00 Column (#1): ID(NUMBER) AvgLen: 5.00 NDV: 20000 Nulls: 0 Density: 5.0000e-05 Min: 1 Max: 20000 Column (#2): ID2(NUMBER) AvgLen: 5.00 NDV: 20000 Nulls: 0 Density: 5.0000e-05 Min: 1 Max: 20000 Index Stats:: Index: T_PRED3_IDX Col#: 1 2 LVLS: 1 #LB: 56 #DK: 20000 LB/K: 1.00 DB/K: 1.00 CLUF: 102.00 *********************** Table Stats:: Table: T_PRED2 Alias: T2 #Rows: 20000 #Blks: 111 AvgRowLen: 31.00 Column (#1): ID(NUMBER) AvgLen: 5.00 NDV: 20000 Nulls: 0 Density: 5.0000e-05 Min: 1 Max: 20000 Column (#2): ID2(NUMBER) AvgLen: 5.00 NDV: 20000 Nulls: 0 Density: 5.0000e-05 Min: 1 Max: 20000 Index Stats:: Index: T_PRED2_IDX Col#: 1 2 LVLS: 1 #LB: 56 #DK: 20000 LB/K: 1.00 DB/K: 1.00 CLUF: 102.00 *************************************** SINGLE TABLE ACCESS PATH Table: T_PRED2 Alias: T2 Card: Original: 20000 Rounded: 20000 Computed: 20000.00 Non Adjusted: 20000.00 Access Path: TableScan Cost: 26.38 Resp: 26.38 Degree: 0 Cost_io: 26.00 Cost_cpu: 4590480 Resp_io: 26.00 Resp_cpu: 4590480 Best:: AccessPath: TableScan Cost: 26.38 Degree: 1 Resp: 26.38 Card: 20000.00 Bytes: 0 *************************************** SINGLE TABLE ACCESS PATH Table: T_PRED3 Alias: T3 Card: Original: 20000 Rounded: 20000 Computed: 20000.00 Non Adjusted: 20000.00 Access Path: TableScan Cost: 26.35 Resp: 26.35 Degree: 0 Cost_io: 26.00 Cost_cpu: 4190480 Resp_io: 26.00 Resp_cpu: 4190480 Access Path: index (index (FFS)) Index: T_PRED3_IDX resc_io: 14.00 resc_cpu: 2798801 ix_sel: 0.0000e+00 ix_sel_with_filters: 1 Access Path: index (FFS) Cost: 14.23 Resp: 14.23 Degree: 1 Cost_io: 14.00 Cost_cpu: 2798801 Resp_io: 14.00 Resp_cpu: 2798801 Access Path: index (FullScan) Index: T_PRED3_IDX resc_io: 57.00 resc_cpu: 4405922 ix_sel: 1 ix_sel_with_filters: 1 Cost: 57.37 Resp: 57.37 Degree: 1 Best:: AccessPath: IndexFFS Index: T_PRED3_IDX Cost: 14.23 Degree: 1 Resp: 14.23 Card: 20000.00 Bytes: 0 Multi-column join key card: 20000 #cols: 2 table: T_PRED2 Multi-column join key card: 20000 #cols: 2 table: T_PRED3 *************************************** OPTIMIZER STATISTICS AND COMPUTATIONS *************************************** GENERAL PLANS *************************************** Considering cardinality-based initial join order. *********************** Join order[1]: T_PRED2[T2]#0 T_PRED3[T3]#1 *************** Now joining: T_PRED3[T3]#1 *************** NL Join Outer table: Card: 20000.00 Cost: 26.38 Resp: 26.38 Degree: 1 Bytes: 31 Inner table: T_PRED3 Alias: T3 Access Path: TableScan NL Join: Cost: 492646.10 Resp: 492646.10 Degree: 0 Cost_io: 485652.00 Cost_cpu: 83814187280 Resp_io: 485652.00 Resp_cpu: 83814187280 Access Path: index (index (FFS)) Index: T_PRED3_IDX resc_io: 12.25 resc_cpu: 2798801 ix_sel: 0.0000e+00 ix_sel_with_filters: 1 Inner table: T_PRED3 Alias: T3 Access Path: index (FFS) NL Join: Cost: 249698.45 Resp: 249698.45 Degree: 0 Cost_io: 245027.00 Cost_cpu: 55980603280 Resp_io: 245027.00 Resp_cpu: 55980603280 Access Path: index (AllEqJoinGuess) Index: T_PRED3_IDX resc_io: 1.00 resc_cpu: 8171 ix_sel: 5.0000e-05 ix_sel_with_filters: 5.0000e-05 NL Join: Cost: 20040.02 Resp: 20040.02 Degree: 1 Cost_io: 20026.00 Cost_cpu: 168019280 Resp_io: 20026.00 Resp_cpu: 168019280 Best NL cost: 20040.02 resc: 20040.02 resc_io: 20026.00 resc_cpu: 168019280 resp: 20040.02 resp_io: 20026.00 resp_cpu: 168019280 Using multi-column join key sanity check for table T_PRED2 Revised join sel:5.0000e-05 = 2.5000e-09 * (1/20000.00) * (1/2.5000e-09) Join Card: 20000.00 = outer (20000.00) * inner (20000.00) * sel (5.0000e-05) Join Card - Rounded: 20000 Computed: 20000.00 SM Join Outer table: resc: 26.38 card 20000.00 bytes: 31 deg: 1 resp: 26.38 Inner table: T_PRED3 Alias: T3 resc: 14.23 card: 20000.00 bytes: 10 deg: 1 resp: 14.23 using dmeth: 2 #groups: 1 SORT resource Sort statistics Sort width: 17 Area size: 131072 Max Area size: 3354624 Degree: 1 Blocks to Sort: 111 Row size: 45 Total Rows: 20000 Initial runs: 2 Merge passes: 1 IO Cost / pass: 62 Total IO sort cost: 173 Total CPU sort cost: 27591568 Total Temp space used: 1795000 SORT resource Sort statistics Sort width: 17 Area size: 131072 Max Area size: 3354624 Degree: 1 Blocks to Sort: 52 Row size: 21 Total Rows: 20000 Initial runs: 2 Merge passes: 1 IO Cost / pass: 30 Total IO sort cost: 82 Total CPU sort cost: 26138634 Total Temp space used: 828000 SM join: Resc: 300.10 Resp: 300.10 [multiMatchCost=0.00] SM cost: 300.10 resc: 300.10 resc_io: 295.00 resc_cpu: 61119482 resp: 300.10 resp_io: 295.00 resp_cpu: 61119482 SM Join (with index on outer) Access Path: index (FullScan) Index: T_PRED2_IDX resc_io: 159.00 resc_cpu: 8932309 ix_sel: 1 ix_sel_with_filters: 1 Cost: 159.75 Resp: 159.75 Degree: 1 Outer table: resc: 159.75 card 20000.00 bytes: 31 deg: 1 resp: 159.75 Inner table: T_PRED3 Alias: T3 resc: 14.23 card: 20000.00 bytes: 10 deg: 1 resp: 14.23 using dmeth: 2 #groups: 1 SORT resource Sort statistics Sort width: 17 Area size: 131072 Max Area size: 3354624 Degree: 1 Blocks to Sort: 52 Row size: 21 Total Rows: 20000 Initial runs: 2 Merge passes: 1 IO Cost / pass: 30 Total IO sort cost: 82 Total CPU sort cost: 26138634 Total Temp space used: 828000 SM join: Resc: 258.16 Resp: 258.16 [multiMatchCost=0.00] HA Join Outer table: resc: 26.38 card 20000.00 bytes: 31 deg: 1 resp: 26.38 Inner table: T_PRED3 Alias: T3 resc: 14.23 card: 20000.00 bytes: 10 deg: 1 resp: 14.23 using dmeth: 2 #groups: 1 Cost per ptn: 0.92 #ptns: 1 hash_area: 0 (max=0) Hash join: Resc: 41.53 Resp: 41.53 [multiMatchCost=0.00] HA Join (swap) Outer table: resc: 14.23 card 20000.00 bytes: 10 deg: 1 resp: 14.23 Inner table: T_PRED2 Alias: T2 resc: 26.38 card: 20000.00 bytes: 31 deg: 1 resp: 26.38 using dmeth: 2 #groups: 1 Cost per ptn: 0.92 #ptns: 1 hash_area: 0 (max=0) Hash join: Resc: 41.53 Resp: 41.53 [multiMatchCost=0.00] HA cost: 41.53 resc: 41.53 resc_io: 40.00 resc_cpu: 18381054 resp: 41.53 resp_io: 40.00 resp_cpu: 18381054 Best:: JoinMethod: Hash Cost: 41.53 Degree: 1 Resp: 41.53 Card: 20000.00 Bytes: 41 *********************** Best so far: Table#: 0 cost: 26.3831 card: 20000.0000 bytes: 620000 Table#: 1 cost: 41.5339 card: 20000.0000 bytes: 820000 *********************** Join order[2]: T_PRED3[T3]#1 T_PRED2[T2]#0 *************** Now joining: T_PRED2[T2]#0 *************** NL Join Outer table: Card: 20000.00 Cost: 14.23 Resp: 14.23 Degree: 1 Bytes: 10 Inner table: T_PRED2 Alias: T2 Access Path: TableScan NL Join: Cost: 493301.54 Resp: 493301.54 Degree: 0 Cost_io: 485640.00 Cost_cpu: 91812395601 Resp_io: 485640.00 Resp_cpu: 91812395601 Access Path: index (AllEqJoinGuess) Index: T_PRED2_IDX resc_io: 2.00 resc_cpu: 15483 ix_sel: 5.0000e-05 ix_sel_with_filters: 5.0000e-05 NL Join (ordered): Cost: 40040.07 Resp: 40040.07 Degree: 1 Cost_io: 40014.00 Cost_cpu: 312456401 Resp_io: 40014.00 Resp_cpu: 312456401 Best NL cost: 40040.07 resc: 40040.07 resc_io: 40014.00 resc_cpu: 312456401 resp: 40040.07 resp_io: 40014.00 resp_cpu: 312456401 Using multi-column join key sanity check for table T_PRED2 Revised join sel:5.0000e-05 = 2.5000e-09 * (1/20000.00) * (1/2.5000e-09) Join Card: 20000.00 = outer (20000.00) * inner (20000.00) * sel (5.0000e-05) Join Card - Rounded: 20000 Computed: 20000.00 SM Join Outer table: resc: 14.23 card 20000.00 bytes: 10 deg: 1 resp: 14.23 Inner table: T_PRED2 Alias: T2 resc: 26.38 card: 20000.00 bytes: 31 deg: 1 resp: 26.38 using dmeth: 2 #groups: 1 SORT resource Sort statistics Sort width: 17 Area size: 131072 Max Area size: 3354624 Degree: 1 Blocks to Sort: 52 Row size: 21 Total Rows: 20000 Initial runs: 2 Merge passes: 1 IO Cost / pass: 30 Total IO sort cost: 82 Total CPU sort cost: 26138634 Total Temp space used: 828000 SORT resource Sort statistics Sort width: 17 Area size: 131072 Max Area size: 3354624 Degree: 1 Blocks to Sort: 111 Row size: 45 Total Rows: 20000 Initial runs: 2 Merge passes: 1 IO Cost / pass: 62 Total IO sort cost: 173 Total CPU sort cost: 27591568 Total Temp space used: 1795000 SM join: Resc: 300.10 Resp: 300.10 [multiMatchCost=0.00] SM cost: 300.10 resc: 300.10 resc_io: 295.00 resc_cpu: 61119482 resp: 300.10 resp_io: 295.00 resp_cpu: 61119482 SM Join (with index on outer) Access Path: index (FullScan) Index: T_PRED3_IDX resc_io: 57.00 resc_cpu: 4405922 ix_sel: 1 ix_sel_with_filters: 1 Cost: 57.37 Resp: 57.37 Degree: 1 Outer table: resc: 57.37 card 20000.00 bytes: 10 deg: 1 resp: 57.37 Inner table: T_PRED2 Alias: T2 resc: 26.38 card: 20000.00 bytes: 31 deg: 1 resp: 26.38 using dmeth: 2 #groups: 1 SORT resource Sort statistics Sort width: 17 Area size: 131072 Max Area size: 3354624 Degree: 1 Blocks to Sort: 111 Row size: 45 Total Rows: 20000 Initial runs: 2 Merge passes: 1 IO Cost / pass: 62 Total IO sort cost: 173 Total CPU sort cost: 27591568 Total Temp space used: 1795000 SM join: Resc: 259.05 Resp: 259.05 [multiMatchCost=0.00] HA Join Outer table: resc: 14.23 card 20000.00 bytes: 10 deg: 1 resp: 14.23 Inner table: T_PRED2 Alias: T2 resc: 26.38 card: 20000.00 bytes: 31 deg: 1 resp: 26.38 using dmeth: 2 #groups: 1 Cost per ptn: 0.92 #ptns: 1 hash_area: 0 (max=0) Hash join: Resc: 41.53 Resp: 41.53 [multiMatchCost=0.00] HA cost: 41.53 resc: 41.53 resc_io: 40.00 resc_cpu: 18381054 resp: 41.53 resp_io: 40.00 resp_cpu: 18381054 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: 41.5339 Degree: 1 Card: 20000.0000 Bytes: 820000 Resc: 41.5339 Resc_io: 40.0000 Resc_cpu: 18381054 Resp: 41.5339 Resp_io: 40.0000 Resc_cpu: 18381054 kkoipt: Query block SEL$2 (#2) ******* UNPARSED QUERY IS ******* SELECT /*+ */ "T2"."ID" "ID2_1","T2"."NAME" "NAME2" FROM "SCOTT"."T_PRED2" "T2","SCOTT"."T_PRED3" "T3" WHERE "T2"."ID"="T3"."ID" AND "T2"."ID2"="T3"."ID2" kkoqbc-end : call(in-use=37128, alloc=49112), compile(in-use=132096, alloc=132288) kkoqbc-start : call(in-use=37128, alloc=49112), compile(in-use=132140, alloc=132288) **************** QUERY BLOCK TEXT **************** Not available. ********************* QUERY BLOCK SIGNATURE ********************* qb name was generated signature (optimizer): qb_name=SEL$1 nbfros=2 flg=0 fro(0): flg=0 objn=52813 hint_alias="T1"@"SEL$1" fro(1): flg=1 objn=52793 hint_alias="V1"@"SEL$1" ***************************** SYSTEM STATISTICS INFORMATION ***************************** Using NOWORKLOAD Stats CPUSPEED: 999 millions instruction/sec IOTFRSPEED: 4096 bytes per millisecond (default is 4096) IOSEEKTIM: 10 milliseconds (default is 10) *************************************** BASE STATISTICAL INFORMATION *********************** Table Stats:: Table: V_PRED Alias: V1 (NOT ANALYZED) #Rows: 0 #Blks: 0 AvgRowLen: 0.00 *********************** Table Stats:: Table: T_PRED1 Alias: T1 #Rows: 100 #Blks: 4 AvgRowLen: 14.00 Column (#1): ID(NUMBER) AvgLen: 3.00 NDV: 100 Nulls: 0 Density: 0.01 Min: 1 Max: 100 *************************************** SINGLE TABLE ACCESS PATH Column (#2): N(NUMBER) AvgLen: 3.00 NDV: 5 Nulls: 0 Density: 0.2 Min: 1 Max: 5 Table: T_PRED1 Alias: T1 Card: Original: 100 Rounded: 20 Computed: 20.00 Non Adjusted: 20.00 Access Path: TableScan Cost: 3.00 Resp: 3.00 Degree: 0 Cost_io: 3.00 Cost_cpu: 50886 Resp_io: 3.00 Resp_cpu: 50886 Best:: AccessPath: TableScan Cost: 3.00 Degree: 1 Resp: 3.00 Card: 20.00 Bytes: 0 *************************************** OPTIMIZER STATISTICS AND COMPUTATIONS *************************************** GENERAL PLANS *************************************** Considering cardinality-based initial join order. *********************** Join order[1]: T_PRED1[T1]#0 V_PRED[V1]#1 *************** Now joining: V_PRED[V1]#1 *************** NL Join Outer table: Card: 20.00 Cost: 3.00 Resp: 3.00 Degree: 1 Bytes: 14 Inner table: V_PRED Alias: V1 Access Path: TableScan NL Join: Cost: 833.68 Resp: 833.68 Degree: 0 Cost_io: 803.00 Cost_cpu: 367671975 Resp_io: 803.00 Resp_cpu: 367671975 Best NL cost: 833.68 resc: 833.68 resc_io: 803.00 resc_cpu: 367671975 resp: 833.68 resp_io: 803.00 resp_cpu: 367671975 Column (#1): ID2_1(NUMBER) AvgLen: 5.00 NDV: 20000 Nulls: 0 Density: 5.0000e-05 Min: 1 Max: 20000 Outer Join Card: 20.00 = max ( outer (20.00), (outer (20.00) * inner (20000.00) * sel (5.0000e-05) ) Join Card - Rounded: 20 Computed: 20.00 SM Join Outer table: resc: 3.00 card 20.00 bytes: 14 deg: 1 resp: 3.00 Inner table: V_PRED Alias: V1 resc: 41.53 card: 20000.00 bytes: 47 deg: 1 resp: 41.53 using dmeth: 2 #groups: 1 SORT resource Sort statistics Sort width: 17 Area size: 131072 Max Area size: 3354624 Degree: 1 Blocks to Sort: 1 Row size: 26 Total Rows: 20 Initial runs: 1 Merge passes: 0 IO Cost / pass: 0 Total IO sort cost: 0 Total CPU sort cost: 11987442 Total Temp space used: 0 SORT resource Sort statistics Sort width: 17 Area size: 131072 Max Area size: 3354624 Degree: 1 Blocks to Sort: 152 Row size: 62 Total Rows: 20000 Initial runs: 2 Merge passes: 1 IO Cost / pass: 84 Total IO sort cost: 236 Total CPU sort cost: 28601234 Total Temp space used: 2434000 SM join: Resc: 283.93 Resp: 283.93 [multiMatchCost=0.00] SM cost: 283.93 resc: 283.93 resc_io: 279.00 resc_cpu: 59020617 resp: 283.93 resp_io: 279.00 resp_cpu: 59020617 HA Join Outer table: resc: 3.00 card 20.00 bytes: 14 deg: 1 resp: 3.00 Inner table: V_PRED Alias: V1 resc: 41.53 card: 20000.00 bytes: 47 deg: 1 resp: 41.53 using dmeth: 2 #groups: 1 Cost per ptn: 0.67 #ptns: 1 hash_area: 0 (max=0) Hash join: Resc: 45.21 Resp: 45.21 [multiMatchCost=0.00] HA cost: 45.21 resc: 45.21 resc_io: 43.00 resc_cpu: 26426714 resp: 45.21 resp_io: 43.00 resp_cpu: 26426714 Best:: JoinMethod: Hash Cost: 45.21 Degree: 1 Resp: 45.21 Card: 20.00 Bytes: 61 *********************** Best so far: Table#: 0 cost: 3.0042 card: 20.0000 bytes: 280 Table#: 1 cost: 45.2052 card: 20.0000 bytes: 1220 (newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:2000 ********************************* Number of join permutations tried: 1 ********************************* Final - All Rows Plan: Best join order: 1 Cost: 45.2052 Degree: 1 Card: 20.0000 Bytes: 1220 Resc: 45.2052 Resc_io: 43.0000 Resc_cpu: 26426714 Resp: 45.2052 Resp_io: 43.0000 Resc_cpu: 26426714 kkoipt: Query block SEL$1 (#1) ******* UNPARSED QUERY IS ******* SELECT /*+ */ "T1"."NAME" "NAME","V1"."NAME2" "NAME2","V1"."NAME2" "NAME2" FROM "SCOTT"."T_PRED1" "T1", (SELECT /*+ */ "T2"."ID" "ID2_1","T2"."NAME" "NAME2" FROM "SCOTT"."T_PRED2" "T2","SCOTT"."T_PRED3" "T3" WHERE "T2"."ID"="T3"."ID" AND "T2"."ID2"="T3"."ID2") "V1" WHERE "T1"."N"=1 AND "T1"."ID"="V1"."ID2_1"(+) kkoqbc-end : call(in-use=66372, alloc=81864), compile(in-use=132624, alloc=135864) CBQT: Saved costed qb# 2 (SEL$2) JPPD: Updated best state, Cost = 45.21 JPPD: Starting iteration 2, state space = (2) : (1) JPPD: Performing join predicate push-down (candidate phase) From SEL$1 (#1) to SEL$2 (#2) JPPD: Pushing predicate "T1"."ID"="V1"."ID2_1"(+) From SEL$1 (#1) to SEL$2 (#2) Registered qb: SEL$639F1A6F 0xb72042cc (PUSHED PREDICATE SEL$2; SEL$1; "V1"@"SEL$1") signature (): qb_name=SEL$639F1A6F nbfros=2 flg=0 fro(0): flg=0 objn=52814 hint_alias="T2"@"SEL$2" fro(1): flg=0 objn=52815 hint_alias="T3"@"SEL$2" FPD: Considering simple filter push in SEL$1 (#1) FPD: Current where clause predicates in SEL$1 (#1) : "T1"."N"=1 kkogcp: try to generate transitive predicate from check constraints for SEL$1 (#1) predicates with check contraints: "T1"."N"=1 after transitive predicate generation: "T1"."N"=1 finally: "T1"."N"=1 FPD: Considering simple filter push in SEL$639F1A6F (#2) FPD: Current where clause predicates in SEL$639F1A6F (#2) : "T2"."ID"="T3"."ID" AND "T2"."ID2"="T3"."ID2" AND :B1="T2"."ID" kkogcp: try to generate transitive predicate from check constraints for SEL$639F1A6F (#2) predicates with check contraints: "T2"."ID"="T3"."ID" AND "T2"."ID2"="T3"."ID2" AND "T2"."ID"=:B1 AND "T3"."ID"=:B2 after transitive predicate generation: "T2"."ID"="T3"."ID" AND "T2"."ID2"="T3"."ID2" AND "T2"."ID"=:B1 AND "T3"."ID"=:B2 finally: "T2"."ID"="T3"."ID" AND "T2"."ID2"="T3"."ID2" AND "T2"."ID"=:B1 AND "T3"."ID"=:B2 FPD: Following transitive predicates are generated in SEL$639F1A6F (#2) : "T3"."ID"=:B1 JPPD: Costing transformed query. kkoqbc-start : call(in-use=66372, alloc=81864), compile(in-use=145040, alloc=148236) **************** QUERY BLOCK TEXT **************** Not available. ********************* QUERY BLOCK SIGNATURE ********************* qb name was generated signature (optimizer): qb_name=SEL$639F1A6F nbfros=2 flg=0 fro(0): flg=0 objn=52814 hint_alias="T2"@"SEL$2" fro(1): flg=0 objn=52815 hint_alias="T3"@"SEL$2" ***************************** SYSTEM STATISTICS INFORMATION ***************************** Using NOWORKLOAD Stats CPUSPEED: 999 millions instruction/sec IOTFRSPEED: 4096 bytes per millisecond (default is 4096) IOSEEKTIM: 10 milliseconds (default is 10) *************************************** BASE STATISTICAL INFORMATION *********************** Table Stats:: Table: T_PRED3 Alias: T3 #Rows: 20000 #Blks: 111 AvgRowLen: 31.00 Column (#1): ID(NUMBER) AvgLen: 5.00 NDV: 20000 Nulls: 0 Density: 5.0000e-05 Min: 1 Max: 20000 Column (#2): ID2(NUMBER) AvgLen: 5.00 NDV: 20000 Nulls: 0 Density: 5.0000e-05 Min: 1 Max: 20000 Index Stats:: Index: T_PRED3_IDX Col#: 1 2 LVLS: 1 #LB: 56 #DK: 20000 LB/K: 1.00 DB/K: 1.00 CLUF: 102.00 *********************** Table Stats:: Table: T_PRED2 Alias: T2 #Rows: 20000 #Blks: 111 AvgRowLen: 31.00 Column (#2): ID2(NUMBER) AvgLen: 5.00 NDV: 20000 Nulls: 0 Density: 5.0000e-05 Min: 1 Max: 20000 Index Stats:: Index: T_PRED2_IDX Col#: 1 2 LVLS: 1 #LB: 56 #DK: 20000 LB/K: 1.00 DB/K: 1.00 CLUF: 102.00 *************************************** SINGLE TABLE ACCESS PATH Column (#1): ID(NUMBER) AvgLen: 5.00 NDV: 20000 Nulls: 0 Density: 5.0000e-05 Min: 1 Max: 20000 Table: T_PRED2 Alias: T2 Card: Original: 20000 Rounded: 1 Computed: 1.00 Non Adjusted: 1.00 Access Path: TableScan Cost: 26.40 Resp: 26.40 Degree: 0 Cost_io: 26.00 Cost_cpu: 4790520 Resp_io: 26.00 Resp_cpu: 4790520 Access Path: index (skip-scan) SS sel: 5.0000e-05 ANDV (#skips): 1 SS io: 1.00 vs. index scan io: 1.00 Skip Scan rejected Access Path: index (RangeScan) Index: T_PRED2_IDX resc_io: 3.00 resc_cpu: 21754 ix_sel: 5.0000e-05 ix_sel_with_filters: 5.0000e-05 Cost: 3.00 Resp: 3.00 Degree: 1 Best:: AccessPath: IndexRange Index: T_PRED2_IDX Cost: 3.00 Degree: 1 Resp: 3.00 Card: 1.00 Bytes: 0 *************************************** SINGLE TABLE ACCESS PATH Table: T_PRED3 Alias: T3 Card: Original: 20000 Rounded: 1 Computed: 1.00 Non Adjusted: 1.00 Access Path: TableScan Cost: 26.40 Resp: 26.40 Degree: 0 Cost_io: 26.00 Cost_cpu: 4790500 Resp_io: 26.00 Resp_cpu: 4790500 Access Path: index (index (FFS)) Index: T_PRED3_IDX resc_io: 14.00 resc_cpu: 3798801 ix_sel: 0.0000e+00 ix_sel_with_filters: 1 Access Path: index (FFS) Cost: 14.32 Resp: 14.32 Degree: 1 Cost_io: 14.00 Cost_cpu: 3798801 Resp_io: 14.00 Resp_cpu: 3798801 Access Path: index (skip-scan) SS sel: 5.0000e-05 ANDV (#skips): 1 SS io: 1.00 vs. index scan io: 1.00 Skip Scan rejected Access Path: index (IndexOnly) Index: T_PRED3_IDX resc_io: 2.00 resc_cpu: 14443 ix_sel: 5.0000e-05 ix_sel_with_filters: 5.0000e-05 Cost: 2.00 Resp: 2.00 Degree: 1 Best:: AccessPath: IndexRange Index: T_PRED3_IDX Cost: 2.00 Degree: 1 Resp: 2.00 Card: 1.00 Bytes: 0 Multi-column join key card: 20000 #cols: 2 table: T_PRED2 Multi-column join key card: 20000 #cols: 2 table: T_PRED3 *************************************** OPTIMIZER STATISTICS AND COMPUTATIONS *************************************** GENERAL PLANS *************************************** Considering cardinality-based initial join order. *********************** Join order[1]: T_PRED2[T2]#0 T_PRED3[T3]#1 *************** Now joining: T_PRED3[T3]#1 *************** NL Join Outer table: Card: 1.00 Cost: 3.00 Resp: 3.00 Degree: 1 Bytes: 31 Inner table: T_PRED3 Alias: T3 Access Path: TableScan NL Join: Cost: 29.43 Resp: 29.43 Degree: 0 Cost_io: 29.00 Cost_cpu: 5212234 Resp_io: 29.00 Resp_cpu: 5212234 Access Path: index (index (FFS)) Index: T_PRED3_IDX resc_io: 14.00 resc_cpu: 3798801 ix_sel: 0.0000e+00 ix_sel_with_filters: 1 Inner table: T_PRED3 Alias: T3 Access Path: index (FFS) NL Join: Cost: 17.32 Resp: 17.32 Degree: 0 Cost_io: 17.00 Cost_cpu: 3820555 Resp_io: 17.00 Resp_cpu: 3820555 kkofmx: index filter:"T2"."ID"="T3"."ID" AND "T2"."ID2"="T3"."ID2" AND "T2"."ID"=:B1 AND "T3"."ID"=:B2 Access Path: index (AllEqJoinGuess) Index: T_PRED3_IDX resc_io: 1.00 resc_cpu: 8171 ix_sel: 5.0000e-05 ix_sel_with_filters: 5.0000e-05 NL Join: Cost: 4.00 Resp: 4.00 Degree: 1 Cost_io: 4.00 Cost_cpu: 29926 Resp_io: 4.00 Resp_cpu: 29926 Best NL cost: 4.00 resc: 4.00 resc_io: 4.00 resc_cpu: 29926 resp: 4.00 resp_io: 4.00 resp_cpu: 29926 Using multi-column join key sanity check for table T_PRED2 Revised join sel:5.0000e-05 = 1 * (1/20000.00) * (1/1) Join Card: 0.00 = outer (1.00) * inner (1.00) * sel (5.0000e-05) Join Card - Rounded: 1 Computed: 0.00 SM Join Outer table: resc: 3.00 card 1.00 bytes: 31 deg: 1 resp: 3.00 Inner table: T_PRED3 Alias: T3 resc: 2.00 card: 1.00 bytes: 10 deg: 1 resp: 2.00 using dmeth: 2 #groups: 1 SORT resource Sort statistics Sort width: 17 Area size: 131072 Max Area size: 3354624 Degree: 1 Blocks to Sort: 1 Row size: 45 Total Rows: 1 Initial runs: 1 Merge passes: 0 IO Cost / pass: 0 Total IO sort cost: 0 Total CPU sort cost: 11983548 Total Temp space used: 0 SORT resource Sort statistics Sort width: 17 Area size: 131072 Max Area size: 3354624 Degree: 1 Blocks to Sort: 1 Row size: 21 Total Rows: 1 Initial runs: 1 Merge passes: 0 IO Cost / pass: 0 Total IO sort cost: 0 Total CPU sort cost: 11983548 Total Temp space used: 0 SM join: Resc: 7.00 Resp: 7.00 [multiMatchCost=0.00] SM cost: 7.00 resc: 7.00 resc_io: 5.00 resc_cpu: 24003293 resp: 7.00 resp_io: 5.00 resp_cpu: 24003293 HA Join Outer table: resc: 3.00 card 1.00 bytes: 31 deg: 1 resp: 3.00 Inner table: T_PRED3 Alias: T3 resc: 2.00 card: 1.00 bytes: 10 deg: 1 resp: 2.00 using dmeth: 2 #groups: 1 Cost per ptn: 0.50 #ptns: 1 hash_area: 0 (max=0) Hash join: Resc: 5.50 Resp: 5.50 [multiMatchCost=0.00] HA cost: 5.50 resc: 5.50 resc_io: 5.00 resc_cpu: 6028221 resp: 5.50 resp_io: 5.00 resp_cpu: 6028221 Best:: JoinMethod: NestedLoop Cost: 4.00 Degree: 1 Resp: 4.00 Card: 0.00 Bytes: 41 *********************** Best so far: Table#: 0 cost: 3.0018 card: 1.0000 bytes: 31 Table#: 1 cost: 4.0025 card: 0.0001 bytes: 41 *********************** Join order[2]: T_PRED3[T3]#1 T_PRED2[T2]#0 *************** Now joining: T_PRED2[T2]#0 *************** NL Join Outer table: Card: 1.00 Cost: 2.00 Resp: 2.00 Degree: 1 Bytes: 10 Inner table: T_PRED2 Alias: T2 Access Path: TableScan NL Join: Cost: 28.43 Resp: 28.43 Degree: 0 Cost_io: 28.00 Cost_cpu: 5204943 Resp_io: 28.00 Resp_cpu: 5204943 kkofmx: index filter:"T2"."ID"="T3"."ID" AND "T2"."ID2"="T3"."ID2" AND "T2"."ID"=:B1 AND "T3"."ID"=:B2 Access Path: index (AllEqJoinGuess) Index: T_PRED2_IDX resc_io: 2.00 resc_cpu: 15483 ix_sel: 5.0000e-05 ix_sel_with_filters: 5.0000e-05 NL Join: Cost: 4.00 Resp: 4.00 Degree: 1 Cost_io: 4.00 Cost_cpu: 29926 Resp_io: 4.00 Resp_cpu: 29926 Best NL cost: 4.00 resc: 4.00 resc_io: 4.00 resc_cpu: 29926 resp: 4.00 resp_io: 4.00 resp_cpu: 29926 Using multi-column join key sanity check for table T_PRED2 Revised join sel:5.0000e-05 = 1 * (1/20000.00) * (1/1) Join Card: 0.00 = outer (1.00) * inner (1.00) * sel (5.0000e-05) Join Card - Rounded: 1 Computed: 0.00 SM Join Outer table: resc: 2.00 card 1.00 bytes: 10 deg: 1 resp: 2.00 Inner table: T_PRED2 Alias: T2 resc: 3.00 card: 1.00 bytes: 31 deg: 1 resp: 3.00 using dmeth: 2 #groups: 1 SORT resource Sort statistics Sort width: 17 Area size: 131072 Max Area size: 3354624 Degree: 1 Blocks to Sort: 1 Row size: 45 Total Rows: 1 Initial runs: 1 Merge passes: 0 IO Cost / pass: 0 Total IO sort cost: 0 Total CPU sort cost: 11983548 Total Temp space used: 0 SM join: Resc: 6.00 Resp: 6.00 [multiMatchCost=0.00] SM cost: 6.00 resc: 6.00 resc_io: 5.00 resc_cpu: 12019745 resp: 6.00 resp_io: 5.00 resp_cpu: 12019745 HA Join Outer table: resc: 2.00 card 1.00 bytes: 10 deg: 1 resp: 2.00 Inner table: T_PRED2 Alias: T2 resc: 3.00 card: 1.00 bytes: 31 deg: 1 resp: 3.00 using dmeth: 2 #groups: 1 Cost per ptn: 0.50 #ptns: 1 hash_area: 0 (max=0) Hash join: Resc: 5.50 Resp: 5.50 [multiMatchCost=0.00] HA cost: 5.50 resc: 5.50 resc_io: 5.00 resc_cpu: 6028221 resp: 5.50 resp_io: 5.00 resp_cpu: 6028221 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: 4.0025 Degree: 1 Card: 1.0000 Bytes: 41 Resc: 4.0025 Resc_io: 4.0000 Resc_cpu: 29926 Resp: 4.0025 Resp_io: 4.0000 Resc_cpu: 29926 kkoipt: Query block SEL$639F1A6F (#2) ******* UNPARSED QUERY IS ******* SELECT /*+ */ "T2"."ID" "ID2_1","T2"."NAME" "NAME2" FROM "SCOTT"."T_PRED2" "T2","SCOTT"."T_PRED3" "T3" WHERE "T2"."ID"="T3"."ID" AND "T2"."ID2"="T3"."ID2" AND "T2"."ID"="T1"."ID" AND "T3"."ID"="T1"."ID" kkoqbc-end : call(in-use=102956, alloc=114616), compile(in-use=145592, alloc=148236) kkoqbc-start : call(in-use=102956, alloc=114616), compile(in-use=145636, alloc=148236) **************** QUERY BLOCK TEXT **************** Not available. ********************* QUERY BLOCK SIGNATURE ********************* qb name was generated signature (optimizer): qb_name=SEL$1 nbfros=2 flg=0 fro(0): flg=0 objn=52813 hint_alias="T1"@"SEL$1" fro(1): flg=1 objn=52793 hint_alias="V1"@"SEL$1" ***************************** SYSTEM STATISTICS INFORMATION ***************************** Using NOWORKLOAD Stats CPUSPEED: 999 millions instruction/sec IOTFRSPEED: 4096 bytes per millisecond (default is 4096) IOSEEKTIM: 10 milliseconds (default is 10) *************************************** BASE STATISTICAL INFORMATION *********************** Table Stats:: Table: V_PRED Alias: V1 (NOT ANALYZED) #Rows: 0 #Blks: 0 AvgRowLen: 0.00 *********************** Table Stats:: Table: T_PRED1 Alias: T1 #Rows: 100 #Blks: 4 AvgRowLen: 14.00 *************************************** SINGLE TABLE ACCESS PATH Column (#2): N(NUMBER) AvgLen: 3.00 NDV: 5 Nulls: 0 Density: 0.2 Min: 1 Max: 5 Table: T_PRED1 Alias: T1 Card: Original: 100 Rounded: 20 Computed: 20.00 Non Adjusted: 20.00 Access Path: TableScan Cost: 3.00 Resp: 3.00 Degree: 0 Cost_io: 3.00 Cost_cpu: 50886 Resp_io: 3.00 Resp_cpu: 50886 Best:: AccessPath: TableScan Cost: 3.00 Degree: 1 Resp: 3.00 Card: 20.00 Bytes: 0 *************************************** OPTIMIZER STATISTICS AND COMPUTATIONS *************************************** GENERAL PLANS *************************************** Considering cardinality-based initial join order. *********************** Join order[1]: T_PRED1[T1]#1 V_PRED[V1]#0 *************** Now joining: V_PRED[V1]#0 *************** NL Join Outer table: Card: 20.00 Cost: 3.00 Resp: 3.00 Degree: 1 Bytes: 14 Inner table: V_PRED Alias: V1 Access Path: TableScan NL Join: Cost: 83.05 Resp: 83.05 Degree: 0 Cost_io: 83.00 Cost_cpu: 649401 Resp_io: 83.00 Resp_cpu: 649401 Best NL cost: 83.05 resc: 83.05 resc_io: 83.00 resc_cpu: 649401 resp: 83.05 resp_io: 83.00 resp_cpu: 649401 Outer Join Card: 20.00 = max ( outer (20.00), (outer (20.00) * inner (20000.00) * sel (5.0000e-05) ) Join cardinality for HJ/SMJ (no post filters): 400000.00, outer: 20.00, inner: 20000.00, sel: 1 Join Card - Rounded: 20 Computed: 20.00 Best:: JoinMethod: NestedLoop Cost: 83.05 Degree: 1 Resp: 83.05 Card: 20.00 Bytes: 52 *********************** Best so far: Table#: 1 cost: 3.0042 card: 20.0000 bytes: 280 Table#: 0 cost: 83.0542 card: 20.0000 bytes: 1040 (newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:2000 ********************************* Number of join permutations tried: 1 ********************************* Final - All Rows Plan: Best join order: 1 Cost: 83.0542 Degree: 1 Card: 20.0000 Bytes: 1040 Resc: 83.0542 Resc_io: 83.0000 Resc_cpu: 649401 Resp: 83.0542 Resp_io: 83.0000 Resc_cpu: 649401 kkoipt: Query block SEL$1 (#1) ******* UNPARSED QUERY IS ******* SELECT /*+ */ "T1"."NAME" "NAME","V1"."NAME2" "NAME2","V1"."NAME2" "NAME2" FROM "SCOTT"."T_PRED1" "T1", (SELECT /*+ */ "T2"."ID" "ID2_1","T2"."NAME" "NAME2" FROM "SCOTT"."T_PRED2" "T2","SCOTT"."T_PRED3" "T3" WHERE "T2"."ID"="T3"."ID" AND "T2"."ID2"="T3"."ID2" AND "T2"."ID"="T1"."ID" AND "T3"."ID"="T1"."ID") "V1" WHERE "T1"."N"=1 kkoqbc-end : call(in-use=132596, alloc=147368), compile(in-use=146608, alloc=148236) CBQT: Saved costed qb# 2 (SEL$639F1A6F) JPPD: New cost worse than best so far, Cost = 83.05 JPPD: Will not use JPPD from query block SEL$1 (#1). JPPD: Applying transformation directives JPPD: Checking validity of push-down in query block SEL$2 (#2) JPPD: No view found to push predicate into. JPPD: Checking validity of push-down in query block SEL$1 (#1) JPPD: Pushdown from SEL$1 (#1) passed validity checks. JPPD: Performing join predicate push-down (no transformation phase) From SEL$1 (#1) to SEL$2 (#2) FPD: Considering simple filter push in SEL$1 (#1) FPD: Current where clause predicates in SEL$1 (#1) : "T1"."N"=1 AND "T1"."ID"="V1"."ID2_1"(+) kkogcp: try to generate transitive predicate from check constraints for SEL$1 (#1) predicates with check contraints: "T1"."N"=1 AND "T1"."ID"="V1"."ID2_1"(+) after transitive predicate generation: "T1"."N"=1 AND "T1"."ID"="V1"."ID2_1"(+) finally: "T1"."N"=1 AND "T1"."ID"="V1"."ID2_1"(+) FPD: Considering simple filter push in SEL$2 (#2) FPD: Current where clause predicates in SEL$2 (#2) : "T2"."ID"="T3"."ID" AND "T2"."ID2"="T3"."ID2" kkogcp: try to generate transitive predicate from check constraints for SEL$2 (#2) predicates with check contraints: "T2"."ID"="T3"."ID" AND "T2"."ID2"="T3"."ID2" after transitive predicate generation: "T2"."ID"="T3"."ID" AND "T2"."ID2"="T3"."ID2" finally: "T2"."ID"="T3"."ID" AND "T2"."ID2"="T3"."ID2" kkoqbc-start : call(in-use=132604, alloc=147368), compile(in-use=100312, alloc=148236) **************** QUERY BLOCK TEXT **************** select /*+ gather_plan_statistics cost_based */ t1.name, v1.name2 , v1.name2 from t_pred1 t1, v_pred v1 where t1.n = 1 and t1.id = v1.id2_1(+) ********************* QUERY BLOCK SIGNATURE ********************* qb name was generated signature (optimizer): qb_name=SEL$2 nbfros=2 flg=0 fro(0): flg=0 objn=52814 hint_alias="T2"@"SEL$2" fro(1): flg=0 objn=52815 hint_alias="T3"@"SEL$2" ***************************** SYSTEM STATISTICS INFORMATION ***************************** Using NOWORKLOAD Stats CPUSPEED: 999 millions instruction/sec IOTFRSPEED: 4096 bytes per millisecond (default is 4096) IOSEEKTIM: 10 milliseconds (default is 10) *************************************** BASE STATISTICAL INFORMATION *********************** Table Stats:: Table: T_PRED3 Alias: T3 #Rows: 20000 #Blks: 111 AvgRowLen: 31.00 Column (#1): ID(NUMBER) AvgLen: 5.00 NDV: 20000 Nulls: 0 Density: 5.0000e-05 Min: 1 Max: 20000 Column (#2): ID2(NUMBER) AvgLen: 5.00 NDV: 20000 Nulls: 0 Density: 5.0000e-05 Min: 1 Max: 20000 Index Stats:: Index: T_PRED3_IDX Col#: 1 2 LVLS: 1 #LB: 56 #DK: 20000 LB/K: 1.00 DB/K: 1.00 CLUF: 102.00 *********************** Table Stats:: Table: T_PRED2 Alias: T2 #Rows: 20000 #Blks: 111 AvgRowLen: 31.00 Column (#1): ID(NUMBER) AvgLen: 5.00 NDV: 20000 Nulls: 0 Density: 5.0000e-05 Min: 1 Max: 20000 Column (#2): ID2(NUMBER) AvgLen: 5.00 NDV: 20000 Nulls: 0 Density: 5.0000e-05 Min: 1 Max: 20000 Index Stats:: Index: T_PRED2_IDX Col#: 1 2 LVLS: 1 #LB: 56 #DK: 20000 LB/K: 1.00 DB/K: 1.00 CLUF: 102.00 *************************************** SINGLE TABLE ACCESS PATH Table: T_PRED2 Alias: T2 Card: Original: 20000 Rounded: 20000 Computed: 20000.00 Non Adjusted: 20000.00 Access Path: TableScan Cost: 26.38 Resp: 26.38 Degree: 0 Cost_io: 26.00 Cost_cpu: 4590480 Resp_io: 26.00 Resp_cpu: 4590480 Best:: AccessPath: TableScan Cost: 26.38 Degree: 1 Resp: 26.38 Card: 20000.00 Bytes: 0 *************************************** SINGLE TABLE ACCESS PATH Table: T_PRED3 Alias: T3 Card: Original: 20000 Rounded: 20000 Computed: 20000.00 Non Adjusted: 20000.00 Access Path: TableScan Cost: 26.35 Resp: 26.35 Degree: 0 Cost_io: 26.00 Cost_cpu: 4190480 Resp_io: 26.00 Resp_cpu: 4190480 Access Path: index (index (FFS)) Index: T_PRED3_IDX resc_io: 14.00 resc_cpu: 2798801 ix_sel: 0.0000e+00 ix_sel_with_filters: 1 Access Path: index (FFS) Cost: 14.23 Resp: 14.23 Degree: 1 Cost_io: 14.00 Cost_cpu: 2798801 Resp_io: 14.00 Resp_cpu: 2798801 Access Path: index (FullScan) Index: T_PRED3_IDX resc_io: 57.00 resc_cpu: 4405922 ix_sel: 1 ix_sel_with_filters: 1 Cost: 57.37 Resp: 57.37 Degree: 1 Best:: AccessPath: IndexFFS Index: T_PRED3_IDX Cost: 14.23 Degree: 1 Resp: 14.23 Card: 20000.00 Bytes: 0 Multi-column join key card: 20000 #cols: 2 table: T_PRED2 Multi-column join key card: 20000 #cols: 2 table: T_PRED3 *************************************** OPTIMIZER STATISTICS AND COMPUTATIONS *************************************** GENERAL PLANS *************************************** Considering cardinality-based initial join order. *********************** Join order[1]: T_PRED2[T2]#0 T_PRED3[T3]#1 *************** Now joining: T_PRED3[T3]#1 *************** NL Join Outer table: Card: 20000.00 Cost: 26.38 Resp: 26.38 Degree: 1 Bytes: 31 Inner table: T_PRED3 Alias: T3 Access Path: TableScan NL Join: Cost: 492646.10 Resp: 492646.10 Degree: 0 Cost_io: 485652.00 Cost_cpu: 83814187280 Resp_io: 485652.00 Resp_cpu: 83814187280 Access Path: index (index (FFS)) Index: T_PRED3_IDX resc_io: 12.25 resc_cpu: 2798801 ix_sel: 0.0000e+00 ix_sel_with_filters: 1 Inner table: T_PRED3 Alias: T3 Access Path: index (FFS) NL Join: Cost: 249698.45 Resp: 249698.45 Degree: 0 Cost_io: 245027.00 Cost_cpu: 55980603280 Resp_io: 245027.00 Resp_cpu: 55980603280 Access Path: index (AllEqJoinGuess) Index: T_PRED3_IDX resc_io: 1.00 resc_cpu: 8171 ix_sel: 5.0000e-05 ix_sel_with_filters: 5.0000e-05 NL Join: Cost: 20040.02 Resp: 20040.02 Degree: 1 Cost_io: 20026.00 Cost_cpu: 168019280 Resp_io: 20026.00 Resp_cpu: 168019280 Best NL cost: 20040.02 resc: 20040.02 resc_io: 20026.00 resc_cpu: 168019280 resp: 20040.02 resp_io: 20026.00 resp_cpu: 168019280 Using multi-column join key sanity check for table T_PRED2 Revised join sel:5.0000e-05 = 2.5000e-09 * (1/20000.00) * (1/2.5000e-09) Join Card: 20000.00 = outer (20000.00) * inner (20000.00) * sel (5.0000e-05) Join Card - Rounded: 20000 Computed: 20000.00 SM Join Outer table: resc: 26.38 card 20000.00 bytes: 31 deg: 1 resp: 26.38 Inner table: T_PRED3 Alias: T3 resc: 14.23 card: 20000.00 bytes: 10 deg: 1 resp: 14.23 using dmeth: 2 #groups: 1 SORT resource Sort statistics Sort width: 17 Area size: 131072 Max Area size: 3354624 Degree: 1 Blocks to Sort: 111 Row size: 45 Total Rows: 20000 Initial runs: 2 Merge passes: 1 IO Cost / pass: 62 Total IO sort cost: 173 Total CPU sort cost: 27591568 Total Temp space used: 1795000 SORT resource Sort statistics Sort width: 17 Area size: 131072 Max Area size: 3354624 Degree: 1 Blocks to Sort: 52 Row size: 21 Total Rows: 20000 Initial runs: 2 Merge passes: 1 IO Cost / pass: 30 Total IO sort cost: 82 Total CPU sort cost: 26138634 Total Temp space used: 828000 SM join: Resc: 300.10 Resp: 300.10 [multiMatchCost=0.00] SM cost: 300.10 resc: 300.10 resc_io: 295.00 resc_cpu: 61119482 resp: 300.10 resp_io: 295.00 resp_cpu: 61119482 SM Join (with index on outer) Access Path: index (FullScan) Index: T_PRED2_IDX resc_io: 159.00 resc_cpu: 8932309 ix_sel: 1 ix_sel_with_filters: 1 Cost: 159.75 Resp: 159.75 Degree: 1 Outer table: resc: 159.75 card 20000.00 bytes: 31 deg: 1 resp: 159.75 Inner table: T_PRED3 Alias: T3 resc: 14.23 card: 20000.00 bytes: 10 deg: 1 resp: 14.23 using dmeth: 2 #groups: 1 SORT resource Sort statistics Sort width: 17 Area size: 131072 Max Area size: 3354624 Degree: 1 Blocks to Sort: 52 Row size: 21 Total Rows: 20000 Initial runs: 2 Merge passes: 1 IO Cost / pass: 30 Total IO sort cost: 82 Total CPU sort cost: 26138634 Total Temp space used: 828000 SM join: Resc: 258.16 Resp: 258.16 [multiMatchCost=0.00] HA Join Outer table: resc: 26.38 card 20000.00 bytes: 31 deg: 1 resp: 26.38 Inner table: T_PRED3 Alias: T3 resc: 14.23 card: 20000.00 bytes: 10 deg: 1 resp: 14.23 using dmeth: 2 #groups: 1 Cost per ptn: 0.92 #ptns: 1 hash_area: 0 (max=0) Hash join: Resc: 41.53 Resp: 41.53 [multiMatchCost=0.00] HA Join (swap) Outer table: resc: 14.23 card 20000.00 bytes: 10 deg: 1 resp: 14.23 Inner table: T_PRED2 Alias: T2 resc: 26.38 card: 20000.00 bytes: 31 deg: 1 resp: 26.38 using dmeth: 2 #groups: 1 Cost per ptn: 0.92 #ptns: 1 hash_area: 0 (max=0) Hash join: Resc: 41.53 Resp: 41.53 [multiMatchCost=0.00] HA cost: 41.53 resc: 41.53 resc_io: 40.00 resc_cpu: 18381054 resp: 41.53 resp_io: 40.00 resp_cpu: 18381054 Best:: JoinMethod: Hash Cost: 41.53 Degree: 1 Resp: 41.53 Card: 20000.00 Bytes: 41 *********************** Best so far: Table#: 0 cost: 26.3831 card: 20000.0000 bytes: 620000 Table#: 1 cost: 41.5339 card: 20000.0000 bytes: 820000 *********************** Join order[2]: T_PRED3[T3]#1 T_PRED2[T2]#0 *************** Now joining: T_PRED2[T2]#0 *************** NL Join Outer table: Card: 20000.00 Cost: 14.23 Resp: 14.23 Degree: 1 Bytes: 10 Inner table: T_PRED2 Alias: T2 Access Path: TableScan NL Join: Cost: 493301.54 Resp: 493301.54 Degree: 0 Cost_io: 485640.00 Cost_cpu: 91812395601 Resp_io: 485640.00 Resp_cpu: 91812395601 Access Path: index (AllEqJoinGuess) Index: T_PRED2_IDX resc_io: 2.00 resc_cpu: 15483 ix_sel: 5.0000e-05 ix_sel_with_filters: 5.0000e-05 NL Join (ordered): Cost: 40040.07 Resp: 40040.07 Degree: 1 Cost_io: 40014.00 Cost_cpu: 312456401 Resp_io: 40014.00 Resp_cpu: 312456401 Best NL cost: 40040.07 resc: 40040.07 resc_io: 40014.00 resc_cpu: 312456401 resp: 40040.07 resp_io: 40014.00 resp_cpu: 312456401 Using multi-column join key sanity check for table T_PRED2 Revised join sel:5.0000e-05 = 2.5000e-09 * (1/20000.00) * (1/2.5000e-09) Join Card: 20000.00 = outer (20000.00) * inner (20000.00) * sel (5.0000e-05) Join Card - Rounded: 20000 Computed: 20000.00 SM Join Outer table: resc: 14.23 card 20000.00 bytes: 10 deg: 1 resp: 14.23 Inner table: T_PRED2 Alias: T2 resc: 26.38 card: 20000.00 bytes: 31 deg: 1 resp: 26.38 using dmeth: 2 #groups: 1 SORT resource Sort statistics Sort width: 17 Area size: 131072 Max Area size: 3354624 Degree: 1 Blocks to Sort: 52 Row size: 21 Total Rows: 20000 Initial runs: 2 Merge passes: 1 IO Cost / pass: 30 Total IO sort cost: 82 Total CPU sort cost: 26138634 Total Temp space used: 828000 SORT resource Sort statistics Sort width: 17 Area size: 131072 Max Area size: 3354624 Degree: 1 Blocks to Sort: 111 Row size: 45 Total Rows: 20000 Initial runs: 2 Merge passes: 1 IO Cost / pass: 62 Total IO sort cost: 173 Total CPU sort cost: 27591568 Total Temp space used: 1795000 SM join: Resc: 300.10 Resp: 300.10 [multiMatchCost=0.00] SM cost: 300.10 resc: 300.10 resc_io: 295.00 resc_cpu: 61119482 resp: 300.10 resp_io: 295.00 resp_cpu: 61119482 SM Join (with index on outer) Access Path: index (FullScan) Index: T_PRED3_IDX resc_io: 57.00 resc_cpu: 4405922 ix_sel: 1 ix_sel_with_filters: 1 Cost: 57.37 Resp: 57.37 Degree: 1 Outer table: resc: 57.37 card 20000.00 bytes: 10 deg: 1 resp: 57.37 Inner table: T_PRED2 Alias: T2 resc: 26.38 card: 20000.00 bytes: 31 deg: 1 resp: 26.38 using dmeth: 2 #groups: 1 SORT resource Sort statistics Sort width: 17 Area size: 131072 Max Area size: 3354624 Degree: 1 Blocks to Sort: 111 Row size: 45 Total Rows: 20000 Initial runs: 2 Merge passes: 1 IO Cost / pass: 62 Total IO sort cost: 173 Total CPU sort cost: 27591568 Total Temp space used: 1795000 SM join: Resc: 259.05 Resp: 259.05 [multiMatchCost=0.00] HA Join Outer table: resc: 14.23 card 20000.00 bytes: 10 deg: 1 resp: 14.23 Inner table: T_PRED2 Alias: T2 resc: 26.38 card: 20000.00 bytes: 31 deg: 1 resp: 26.38 using dmeth: 2 #groups: 1 Cost per ptn: 0.92 #ptns: 1 hash_area: 0 (max=0) Hash join: Resc: 41.53 Resp: 41.53 [multiMatchCost=0.00] HA cost: 41.53 resc: 41.53 resc_io: 40.00 resc_cpu: 18381054 resp: 41.53 resp_io: 40.00 resp_cpu: 18381054 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: 41.5339 Degree: 1 Card: 20000.0000 Bytes: 820000 Resc: 41.5339 Resc_io: 40.0000 Resc_cpu: 18381054 Resp: 41.5339 Resp_io: 40.0000 Resc_cpu: 18381054 kkoipt: Query block SEL$2 (#2) ******* UNPARSED QUERY IS ******* SELECT "T2"."ID" "ID2_1","T2"."NAME" "NAME2" FROM SCOTT."T_PRED2" "T2",SCOTT."T_PRED3" "T3" WHERE "T2"."ID2"="T3"."ID2" AND "T2"."ID"="T3"."ID" kkoqbc-end : call(in-use=164848, alloc=180120), compile(in-use=102052, alloc=148236) kkoqbc-start : call(in-use=164848, alloc=180120), compile(in-use=102096, alloc=148236) **************** QUERY BLOCK TEXT **************** select /*+ gather_plan_statistics cost_based */ t1.name, v1.name2 , v1.name2 from t_pred1 t1, v_pred v1 where t1.n = 1 and t1.id = v1.id2_1(+) ********************* QUERY BLOCK SIGNATURE ********************* qb name was generated signature (optimizer): qb_name=SEL$1 nbfros=2 flg=0 fro(0): flg=0 objn=52813 hint_alias="T1"@"SEL$1" fro(1): flg=1 objn=52793 hint_alias="V1"@"SEL$1" ***************************** SYSTEM STATISTICS INFORMATION ***************************** Using NOWORKLOAD Stats CPUSPEED: 999 millions instruction/sec IOTFRSPEED: 4096 bytes per millisecond (default is 4096) IOSEEKTIM: 10 milliseconds (default is 10) *************************************** BASE STATISTICAL INFORMATION *********************** Table Stats:: Table: V_PRED Alias: V1 (NOT ANALYZED) #Rows: 0 #Blks: 0 AvgRowLen: 0.00 *********************** Table Stats:: Table: T_PRED1 Alias: T1 #Rows: 100 #Blks: 4 AvgRowLen: 14.00 Column (#1): ID(NUMBER) AvgLen: 3.00 NDV: 100 Nulls: 0 Density: 0.01 Min: 1 Max: 100 *************************************** SINGLE TABLE ACCESS PATH Column (#2): N(NUMBER) AvgLen: 3.00 NDV: 5 Nulls: 0 Density: 0.2 Min: 1 Max: 5 Table: T_PRED1 Alias: T1 Card: Original: 100 Rounded: 20 Computed: 20.00 Non Adjusted: 20.00 Access Path: TableScan Cost: 3.00 Resp: 3.00 Degree: 0 Cost_io: 3.00 Cost_cpu: 50886 Resp_io: 3.00 Resp_cpu: 50886 Best:: AccessPath: TableScan Cost: 3.00 Degree: 1 Resp: 3.00 Card: 20.00 Bytes: 0 *************************************** OPTIMIZER STATISTICS AND COMPUTATIONS *************************************** GENERAL PLANS *************************************** Considering cardinality-based initial join order. *********************** Join order[1]: T_PRED1[T1]#0 V_PRED[V1]#1 *************** Now joining: V_PRED[V1]#1 *************** NL Join Outer table: Card: 20.00 Cost: 3.00 Resp: 3.00 Degree: 1 Bytes: 14 Inner table: V_PRED Alias: V1 Access Path: TableScan NL Join: Cost: 833.68 Resp: 833.68 Degree: 0 Cost_io: 803.00 Cost_cpu: 367671975 Resp_io: 803.00 Resp_cpu: 367671975 Best NL cost: 833.68 resc: 833.68 resc_io: 803.00 resc_cpu: 367671975 resp: 833.68 resp_io: 803.00 resp_cpu: 367671975 Column (#1): ID2_1(NUMBER) AvgLen: 5.00 NDV: 20000 Nulls: 0 Density: 5.0000e-05 Min: 1 Max: 20000 Outer Join Card: 20.00 = max ( outer (20.00), (outer (20.00) * inner (20000.00) * sel (5.0000e-05) ) Join Card - Rounded: 20 Computed: 20.00 SM Join Outer table: resc: 3.00 card 20.00 bytes: 14 deg: 1 resp: 3.00 Inner table: V_PRED Alias: V1 resc: 41.53 card: 20000.00 bytes: 30 deg: 1 resp: 41.53 using dmeth: 2 #groups: 1 SORT resource Sort statistics Sort width: 17 Area size: 131072 Max Area size: 3354624 Degree: 1 Blocks to Sort: 1 Row size: 26 Total Rows: 20 Initial runs: 1 Merge passes: 0 IO Cost / pass: 0 Total IO sort cost: 0 Total CPU sort cost: 11987442 Total Temp space used: 0 SORT resource Sort statistics Sort width: 17 Area size: 131072 Max Area size: 3354624 Degree: 1 Blocks to Sort: 106 Row size: 43 Total Rows: 20000 Initial runs: 2 Merge passes: 1 IO Cost / pass: 58 Total IO sort cost: 164 Total CPU sort cost: 27468438 Total Temp space used: 1631000 SM join: Resc: 211.83 Resp: 211.83 [multiMatchCost=0.00] SM cost: 211.83 resc: 211.83 resc_io: 207.00 resc_cpu: 57887821 resp: 211.83 resp_io: 207.00 resp_cpu: 57887821 HA Join Outer table: resc: 3.00 card 20.00 bytes: 14 deg: 1 resp: 3.00 Inner table: V_PRED Alias: V1 resc: 41.53 card: 20000.00 bytes: 30 deg: 1 resp: 41.53 using dmeth: 2 #groups: 1 Cost per ptn: 0.67 #ptns: 1 hash_area: 0 (max=0) Hash join: Resc: 45.21 Resp: 45.21 [multiMatchCost=0.00] HA cost: 45.21 resc: 45.21 resc_io: 43.00 resc_cpu: 26426714 resp: 45.21 resp_io: 43.00 resp_cpu: 26426714 Best:: JoinMethod: Hash Cost: 45.21 Degree: 1 Resp: 45.21 Card: 20.00 Bytes: 44 *********************** Best so far: Table#: 0 cost: 3.0042 card: 20.0000 bytes: 280 Table#: 1 cost: 45.2052 card: 20.0000 bytes: 880 (newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:2000 ********************************* Number of join permutations tried: 1 ********************************* Final - All Rows Plan: Best join order: 1 Cost: 45.2052 Degree: 1 Card: 20.0000 Bytes: 880 Resc: 45.2052 Resc_io: 43.0000 Resc_cpu: 26426714 Resp: 45.2052 Resp_io: 43.0000 Resc_cpu: 26426714 kkoipt: Query block SEL$1 (#1) ******* UNPARSED QUERY IS ******* SELECT "T1"."NAME" "NAME","V1"."NAME2" "NAME2","V1"."NAME2" "NAME2" FROM "SCOTT"."T_PRED1" "T1", (SELECT "T2"."ID" "ID2_1","T2"."NAME" "NAME2" FROM SCOTT."T_PRED2" "T2",SCOTT."T_PRED3" "T3" WHERE "T2"."ID2"="T3"."ID2" AND "T2"."ID"="T3"."ID") "V1" WHERE "T1"."N"=1 AND "T1"."ID"="V1"."ID2_1"(+) kkoqbc-end : call(in-use=194092, alloc=212872), compile(in-use=103072, alloc=148236) apadrv-end: call(in-use=194092, alloc=212872), compile(in-use=104300, alloc=148236) sql_id=a46qsa3huppqh. Current SQL statement for this session: select /*+ gather_plan_statistics cost_based */ t1.name, v1.name2 , v1.name2 from t_pred1 t1, v_pred v1 where t1.n = 1 and t1.id = v1.id2_1(+) ============ Plan Table ============ ----------------------------------------------+-----------------------------------+ | Id | Operation | Name | Rows | Bytes | Cost | Time | ----------------------------------------------+-----------------------------------+ | 0 | SELECT STATEMENT | | | | 45 | | | 1 | HASH JOIN OUTER | | 20 | 880 | 45 | 00:00:01 | | 2 | TABLE ACCESS FULL | T_PRED1 | 20 | 280 | 3 | 00:00:01 | | 3 | VIEW | V_PRED | 20K | 586K | 42 | 00:00:01 | | 4 | HASH JOIN | | 20K | 801K | 42 | 00:00:01 | | 5 | INDEX FAST FULL SCAN | T_PRED3_IDX| 20K | 195K | 14 | 00:00:01 | | 6 | TABLE ACCESS FULL | T_PRED2 | 20K | 605K | 26 | 00:00:01 | ----------------------------------------------+-----------------------------------+ Predicate Information: ---------------------- 1 - access("T1"."ID"="V1"."ID2_1") 2 - filter("T1"."N"=1) 4 - access("T2"."ID"="T3"."ID" AND "T2"."ID2"="T3"."ID2") Content of other_xml column =========================== db_version : 10.2.0.1 parse_schema : SCOTT plan_hash : 3337020919 Outline Data: /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('10.2.0.1') ALL_ROWS OUTLINE_LEAF(@"SEL$2") OUTLINE_LEAF(@"SEL$1") OUTLINE(@"SEL$2") OUTLINE(@"SEL$1") FULL(@"SEL$1" "T1"@"SEL$1") NO_ACCESS(@"SEL$1" "V1"@"SEL$1") LEADING(@"SEL$1" "T1"@"SEL$1" "V1"@"SEL$1") USE_HASH(@"SEL$1" "V1"@"SEL$1") FULL(@"SEL$2" "T2"@"SEL$2") INDEX_FFS(@"SEL$2" "T3"@"SEL$2" ("T_PRED3"."ID" "T_PRED3"."ID2")) LEADING(@"SEL$2" "T2"@"SEL$2" "T3"@"SEL$2") USE_HASH(@"SEL$2" "T3"@"SEL$2") SWAP_JOIN_INPUTS(@"SEL$2" "T3"@"SEL$2") 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 = 2 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 = 16384 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 = 128 KB _smm_max_size = 3276 KB _smm_px_max_size = 8192 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 = 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 _gby_hash_aggregation_enabled = true Query Block Registry: ********************* SEL$2 0xb72ef03c (PARSER) [FINAL] SEL$639F1A6F 0xb72042cc (PUSHED PREDICATE SEL$2; SEL$1; "V1"@"SEL$1") SEL$1 0xb72efe60 (PARSER) [FINAL] SEL$639F1A6F 0xb72042cc (PUSHED PREDICATE SEL$2; SEL$1; "V1"@"SEL$1") Optimizer State Dump: call(in-use=206256, alloc=229248), compile(in-use=127772, alloc=173100) Dumping Hints ============= atom_hint=(@=0x293baa14 err=0 resol=0 used=1 token=821 org=1 lvl=1 txt=GATHER_PLAN_STATISTICS ()) Registered qb: MISC$1 0xb729ac90 (PARSER) signature(): NULL ************************** Predicate Move-Around (PM) ************************** PM: Considering predicate move-around in MISC$1 (#0). PM: Checking validity of predicate move-around in MISC$1 (#0). CBQT: Validity checks failed for d9u7st7vtby9c. CVM: Considering view merge in query block MISC$1 (#0) CBQT: Validity checks failed for d9u7st7vtby9c. *************** Subquery Unnest *************** SU: Considering subquery unnesting in query block MISC$1 (#0) ************************* Set-Join Conversion (SJC) ************************* SJC: Considering set-join conversion in MISC$1 (#0). ************************** Predicate Move-Around (PM) ************************** PM: Considering predicate move-around in MISC$1 (#0). PM: Checking validity of predicate move-around in MISC$1 (#0). PM: PM bypassed: Outer query contains no views. FPD: Considering simple filter push in MISC$1 (#0) FPD: Current where clause predicates in MISC$1 (#0) : apadrv-start: call(in-use=30788, alloc=30788), compile(in-use=29716, alloc=30380) sql_id=d9u7st7vtby9c. Current SQL statement for this session: alter session set events '10053 trace name context off' ============ Plan Table ============ ---------------------------------+-----------------------------------+ | Id | Operation | Name | Rows | Bytes | Cost | Time | ---------------------------------+-----------------------------------+ | 0 | DDL STATEMENT | | | | 0 | | ---------------------------------+-----------------------------------+ Predicate Information: ---------------------- 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 = 2 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 = 16384 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 = 128 KB _smm_max_size = 3276 KB _smm_px_max_size = 8192 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 = 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 _gby_hash_aggregation_enabled = true Query Block Registry: ********************* MISC$1 0xb729ac90 (PARSER) [FINAL] Optimizer State Dump: call(in-use=84552, alloc=84552), compile(in-use=38936, alloc=82496)