/home/oracle/admin/orcl/udump/orcl_ora_13351.trc Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production With the Partitioning, OLAP and Data Mining options ORACLE_HOME = /home/oracle/product/10.2.0/db_1 System name: Linux Node name: latte.perc.com Release: 2.6.18-164.el5 Version: #1 SMP Thu Sep 3 03:28:30 EDT 2009 Machine: x86_64 Instance name: orcl Redo thread mounted by this instance: 1 Oracle process number: 19 Unix process pid: 13351, image: oracle@latte.perc.com (TNS V1-V3) *** ACTION NAME:() 2012-02-24 08:55:37.829 *** MODULE NAME:(SQL*Plus) 2012-02-24 08:55:37.829 *** SERVICE NAME:(SYS$USERS) 2012-02-24 08:55:37.829 *** SESSION ID:(148.8651) 2012-02-24 08:55:37.829 Freeing element 0x1baf103c0 from entry 0x1ba3b63f8, freelist 0x1be45c620 Freeing element 0x1bae7c3a0 from entry 0x1ba3b6d58, freelist 0x1be45cb60 Registered qb: SEL$1 0xb83f73e8 (PARSER) signature (): qb_name=SEL$1 nbfros=2 flg=0 fro(0): flg=4 objn=60774 hint_alias="LINEITEM"@"SEL$1" fro(1): flg=4 objn=60772 hint_alias="ORDERS"@"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 52a0skswvaqvk. CVM: Considering view merge in query block SEL$1 (#0) Query block (0x1b83f73e8) before join elimination: SQL:******* UNPARSED QUERY IS ******* SELECT COUNT(*) "COUNT(*)" FROM "DSS"."ORDERS" "ORDERS","DSS"."LINEITEM" "LINEITEM" WHERE "ORDERS"."O_ORDERKEY"="LINEITEM"."L_ORDERKEY" kkqvIsPrimaryForeignKey cfro:ORDERS objn:60774 col#:1 dfro:LINEITEM dcol#:1 kkqvIsPrimaryForeignKey cfro:LINEITEM objn:60772 col#:1 dfro:ORDERS dcol#:1 Query block (0x1b83f73e8) unchanged CBQT: Validity checks failed for 52a0skswvaqvk. *************** Subquery Unnest *************** SU: Considering subquery unnesting in query block SEL$1 (#0) ************************* Set-Join Conversion (SJC) ************************* SJC: Considering set-join conversion in SEL$1 (#0). ************************** Predicate Move-Around (PM) ************************** PM: Considering predicate move-around in SEL$1 (#0). PM: Checking validity of predicate move-around in SEL$1 (#0). PM: PM bypassed: Outer query contains no views. FPD: Considering simple filter push in SEL$1 (#0) FPD: Current where clause predicates in SEL$1 (#0) : "ORDERS"."O_ORDERKEY"="LINEITEM"."L_ORDERKEY" kkogcp: try to generate transitive predicate from check constraints for SEL$1 (#0) predicates with check contraints: "ORDERS"."O_ORDERKEY"="LINEITEM"."L_ORDERKEY" after transitive predicate generation: "ORDERS"."O_ORDERKEY"="LINEITEM"."L_ORDERKEY" finally: "ORDERS"."O_ORDERKEY"="LINEITEM"."L_ORDERKEY" apadrv-start: call(in-use=536, alloc=16344), compile(in-use=35688, alloc=36536) kkoqbc-start : call(in-use=544, alloc=16344), compile(in-use=36848, alloc=40552) ****************************************** Current SQL statement for this session: explain plan for select count(*) from orders, lineitem where o_orderkey = l_orderkey ******************************************* 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 ****************************** statistics_level = all ************************************* PARAMETERS WITH DEFAULT VALUES ****************************** optimizer_mode_hinted = false optimizer_features_hinted = 0.0.0 parallel_execution_enabled = true parallel_query_forced_dop = 0 parallel_dml_forced_dop = 0 parallel_ddl_forced_degree = 0 parallel_ddl_forced_instances = 0 _query_rewrite_fudge = 90 optimizer_features_enable = 10.2.0.1 _optimizer_search_limit = 5 cpu_count = 4 active_instance_count = 1 parallel_threads_per_cpu = 2 hash_area_size = 131072 bitmap_merge_area_size = 1048576 sort_area_size = 65536 sort_area_retained_size = 0 _sort_elimination_cost_ratio = 0 _optimizer_block_size = 8192 _sort_multiblock_read_count = 2 _hash_multiblock_io_count = 0 _db_file_optimizer_read_count = 16 _optimizer_max_permutations = 2000 pga_aggregate_target = 10240 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 = 2048 KB _smm_px_max_size = 5120 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 _optimizer_system_stats_usage = true skip_unusable_indexes = true _remove_aggr_subquery = true _optimizer_push_down_distinct = 0 _dml_monitoring_enabled = true _optimizer_undo_changes = false _predicate_elimination_enabled = true _nested_loop_fudge = 100 _project_view_columns = true _local_communication_costing_enabled = true _local_communication_ratio = 50 _query_rewrite_vop_cleanup = true _slave_mapping_enabled = true _optimizer_cost_based_transformation = linear _optimizer_mjc_enabled = true _right_outer_hash_enable = true _spr_push_pred_refspr = true _optimizer_cache_stats = false _optimizer_cbqt_factor = 50 _optimizer_squ_bottomup = true _fic_area_size = 131072 _optimizer_skip_scan_enabled = true _optimizer_cost_filter_pred = false _optimizer_sortmerge_join_enabled = true _optimizer_join_sel_sanity_check = true _mmv_query_rewrite_enabled = true _bt_mmv_query_rewrite_enabled = true _add_stale_mv_to_dependency_list = true _distinct_view_unnesting = false _optimizer_dim_subq_join_sel = true _optimizer_disable_strans_sanity_checks = 0 _optimizer_compute_index_stats = true _push_join_union_view2 = true _optimizer_ignore_hints = false _optimizer_random_plan = 0 _query_rewrite_setopgrw_enable = true _optimizer_correct_sq_selectivity = true _disable_function_based_index = false _optimizer_join_order_control = 3 _optimizer_cartesian_enabled = true _optimizer_starplan_enabled = true _extended_pruning_enabled = true _optimizer_push_pred_cost_based = true _sql_model_unfold_forloops = run_time _enable_dml_lock_escalation = false _bloom_filter_enabled = true _update_bji_ipdml_enabled = 0 _optimizer_extended_cursor_sharing = udo _dm_max_shared_pool_pct = 1 _optimizer_cost_hjsmj_multimatch = true _optimizer_transitivity_retain = true _px_pwg_enabled = true optimizer_secure_view_merging = true _optimizer_join_elimination_enabled = true flashback_table_rpi = non_fbt _optimizer_cbqt_no_size_restriction = true _optimizer_enhanced_filter_push = true _optimizer_filter_pred_pullup = true _rowsrc_trace_level = 0 _simple_view_merging = true _optimizer_rownum_pred_based_fkr = true _optimizer_better_inlist_costing = all _optimizer_self_induced_cache_cost = false _optimizer_min_cache_blocks = 10 _optimizer_or_expansion = depth _optimizer_order_by_elimination_enabled = true _optimizer_outer_to_anti_enabled = true _selfjoin_mv_duplicates = true _dimension_skip_null = true _force_rewrite_enable = false _optimizer_star_tran_in_with_clause = true _optimizer_complex_pred_selectivity = true _gby_hash_aggregation_enabled = true *************************************** PARAMETERS IN OPT_PARAM HINT **************************** *************************************** Column Usage Monitoring is ON: tracking level = 1 *************************************** **************** QUERY BLOCK TEXT **************** select count(*) from orders, lineitem where o_orderkey = l_orderkey ********************* QUERY BLOCK SIGNATURE ********************* qb name was generated signature (optimizer): qb_name=SEL$1 nbfros=2 flg=0 fro(0): flg=0 objn=60774 hint_alias="LINEITEM"@"SEL$1" fro(1): flg=0 objn=60772 hint_alias="ORDERS"@"SEL$1" ***************************** SYSTEM STATISTICS INFORMATION ***************************** Using NOWORKLOAD Stats CPUSPEED: 1597 millions instruction/sec IOTFRSPEED: 4096 bytes per millisecond (default is 4096) IOSEEKTIM: 13 milliseconds (default is 10) *************************************** BASE STATISTICAL INFORMATION *********************** Table Stats:: Table: LINEITEM Alias: LINEITEM #Rows: 6001215 #Blks: 109048 AvgRowLen: 124.00 Column (#1): L_ORDERKEY(NUMBER) AvgLen: 6.00 NDV: 1500000 Nulls: 0 Density: 6.6667e-07 Min: 1 Max: 6000000 Index Stats:: Index: LINEITEM_PRTK Col#: 2 LVLS: 2 #LB: 12931 #DK: 197757 LB/K: 1.00 DB/K: 29.00 CLUF: 5812082.00 Index: LINEITEM_SI Col#: 14 LVLS: 2 #LB: 29439 #DK: 4 LB/K: 7359.00 DB/K: 109769.00 CLUF: 439077.00 Index: LINEITEM_SM Col#: 15 LVLS: 2 #LB: 18648 #DK: 7 LB/K: 2664.00 DB/K: 112004.00 CLUF: 784030.00 Index: PK_LINEITEM Col#: 1 4 LVLS: 2 #LB: 15440 #DK: 5879916 LB/K: 1.00 DB/K: 1.00 CLUF: 120630.00 *********************** Table Stats:: Table: ORDERS Alias: ORDERS #Rows: 1500000 #Blks: 24244 AvgRowLen: 110.00 Column (#1): O_ORDERKEY(NUMBER) AvgLen: 6.00 NDV: 1500000 Nulls: 0 Density: 6.6667e-07 Min: 163 Max: 5998114 Index Stats:: Index: PK_ORDERS Col#: 1 LVLS: 2 #LB: 3307 #DK: 1500000 LB/K: 1.00 DB/K: 1.00 CLUF: 24045.00 *************************************** SINGLE TABLE ACCESS PATH Table: ORDERS Alias: ORDERS Card: Original: 1500000 Rounded: 1500000 Computed: 1500000.00 Non Adjusted: 1500000.00 Access Path: TableScan Cost: 4663.15 Resp: 4663.15 Degree: 0 Cost_io: 4646.00 Cost_cpu: 397652191 Resp_io: 4646.00 Resp_cpu: 397652191 Access Path: index (index (FFS)) Index: PK_ORDERS resc_io: 635.00 resc_cpu: 203550602 ix_sel: 0.0000e+00 ix_sel_with_filters: 1 Access Path: index (FFS) Cost: 643.78 Resp: 643.78 Degree: 1 Cost_io: 635.00 Cost_cpu: 203550602 Resp_io: 635.00 Resp_cpu: 203550602 Access Path: index (FullScan) Index: PK_ORDERS resc_io: 3309.00 resc_cpu: 323564845 ix_sel: 1 ix_sel_with_filters: 1 Cost: 3322.95 Resp: 3322.95 Degree: 1 Best:: AccessPath: IndexFFS Index: PK_ORDERS Cost: 643.78 Degree: 1 Resp: 643.78 Card: 1500000.00 Bytes: 0 *************************************** SINGLE TABLE ACCESS PATH Table: LINEITEM Alias: LINEITEM Card: Original: 6001215 Rounded: 6001215 Computed: 6001215.00 Non Adjusted: 6001215.00 Access Path: TableScan Cost: 20965.30 Resp: 20965.30 Degree: 0 Cost_io: 20893.00 Cost_cpu: 1676761039 Resp_io: 20893.00 Resp_cpu: 1676761039 Access Path: index (index (FFS)) Index: PK_LINEITEM resc_io: 2959.00 resc_cpu: 815544954 ix_sel: 0.0000e+00 ix_sel_with_filters: 1 Access Path: index (FFS) Cost: 2994.16 Resp: 2994.16 Degree: 1 Cost_io: 2959.00 Cost_cpu: 815544954 Resp_io: 2959.00 Resp_cpu: 815544954 Access Path: index (FullScan) Index: PK_LINEITEM resc_io: 15442.00 resc_cpu: 1285952476 ix_sel: 1 ix_sel_with_filters: 1 Cost: 15497.45 Resp: 15497.45 Degree: 1 ******** Begin index join costing ******** ****** trying bitmap/domain indexes ****** Access Path: index (FullScan) Index: PK_LINEITEM resc_io: 15442.00 resc_cpu: 1285952476 ix_sel: 1 ix_sel_with_filters: 1 Cost: 15497.45 Resp: 15497.45 Degree: 0 Access Path: index (FullScan) Index: PK_LINEITEM resc_io: 15442.00 resc_cpu: 1285952476 ix_sel: 1 ix_sel_with_filters: 1 Cost: 15497.45 Resp: 15497.45 Degree: 0 ******** End index join costing ******** Best:: AccessPath: IndexFFS Index: PK_LINEITEM Cost: 2994.16 Degree: 1 Resp: 2994.16 Card: 6001215.00 Bytes: 0 *************************************** OPTIMIZER STATISTICS AND COMPUTATIONS *************************************** GENERAL PLANS *************************************** Considering cardinality-based initial join order. *********************** Join order[1]: ORDERS[ORDERS]#0 LINEITEM[LINEITEM]#1 *************** Now joining: LINEITEM[LINEITEM]#1 *************** NL Join Outer table: Card: 1500000.00 Cost: 643.78 Resp: 643.78 Degree: 1 Bytes: 6 Inner table: LINEITEM Alias: LINEITEM Access Path: TableScan NL Join: Cost: 31445391014.49 Resp: 31445391014.49 Degree: 0 Cost_io: 31336943325.00 Cost_cpu: 2515141762230602 Resp_io: 31336943325.00 Resp_cpu: 2515141762230602 Access Path: index (index (FFS)) Index: PK_LINEITEM resc_io: 2957.98 resc_cpu: 815544954 ix_sel: 0.0000e+00 ix_sel_with_filters: 1 Inner table: LINEITEM Alias: LINEITEM Access Path: index (FFS) NL Join: Cost: 4489714714.86 Resp: 4489714714.86 Degree: 0 Cost_io: 4436967799.00 Cost_cpu: 1223317633950602 Resp_io: 4436967799.00 Resp_cpu: 1223317633950602 Access Path: index (IndexOnly) Index: PK_LINEITEM resc_io: 2.00 resc_cpu: 15893 ix_sel: 6.6667e-07 ix_sel_with_filters: 6.6667e-07 NL Join (ordered): Cost: 1517494.68 Resp: 1517494.68 Degree: 1 Cost_io: 1516458.00 Cost_cpu: 24042870602 Resp_io: 1516458.00 Resp_cpu: 24042870602 Best NL cost: 1517494.68 resc: 1517494.68 resc_io: 1516458.00 resc_cpu: 24042870602 resp: 1517494.68 resp_io: 1516458.00 resp_cpu: 24042870602 Join Card: 6001215.00 = outer (1500000.00) * inner (6001215.00) * sel (6.6667e-07) Join Card - Rounded: 6001215 Computed: 6001215.00 SM Join Outer table: resc: 643.78 card 1500000.00 bytes: 6 deg: 1 resp: 643.78 Inner table: LINEITEM Alias: LINEITEM resc: 2994.16 card: 6001215.00 bytes: 6 deg: 1 resp: 2994.16 using dmeth: 2 #groups: 1 SORT resource Sort statistics Sort width: 10 Area size: 131072 Max Area size: 2097152 Degree: 1 Blocks to Sort: 3122 Row size: 17 Total Rows: 1500000 Initial runs: 13 Merge passes: 2 IO Cost / pass: 1534 Total IO sort cost: 6190 Total CPU sort cost: 1525060897 Total Temp space used: 36152000 SORT resource Sort statistics Sort width: 10 Area size: 131072 Max Area size: 2097152 Degree: 1 Blocks to Sort: 12491 Row size: 17 Total Rows: 6001215 Initial runs: 49 Merge passes: 2 IO Cost / pass: 6134 Total IO sort cost: 24759 Total CPU sort cost: 6572744138 Total Temp space used: 144614000 SM join: Resc: 34936.10 Resp: 34936.10 [multiMatchCost=0.00] SM cost: 34936.10 resc: 34936.10 resc_io: 34543.00 resc_cpu: 9116900591 resp: 34936.10 resp_io: 34543.00 resp_cpu: 9116900591 SM Join (with index on outer) Access Path: index (FullScan) Index: PK_ORDERS resc_io: 3309.00 resc_cpu: 323564845 ix_sel: 1 ix_sel_with_filters: 1 Cost: 3322.95 Resp: 3322.95 Degree: 1 Outer table: resc: 3322.95 card 1500000.00 bytes: 6 deg: 1 resp: 3322.95 Inner table: LINEITEM Alias: LINEITEM resc: 2994.16 card: 6001215.00 bytes: 6 deg: 1 resp: 2994.16 using dmeth: 2 #groups: 1 SORT resource Sort statistics Sort width: 10 Area size: 131072 Max Area size: 2097152 Degree: 1 Blocks to Sort: 12491 Row size: 17 Total Rows: 6001215 Initial runs: 49 Merge passes: 2 IO Cost / pass: 6134 Total IO sort cost: 24759 Total CPU sort cost: 6572744138 Total Temp space used: 144614000 SM join: Resc: 31359.52 Resp: 31359.52 [multiMatchCost=0.00] HA Join Outer table: resc: 643.78 card 1500000.00 bytes: 6 deg: 1 resp: 643.78 Inner table: LINEITEM Alias: LINEITEM resc: 2994.16 card: 6001215.00 bytes: 6 deg: 1 resp: 2994.16 using dmeth: 2 #groups: 1 Cost per ptn: 8665.88 #ptns: 1 hash_area: 32 (max=512) Hash join: Resc: 12303.83 Resp: 12303.83 [multiMatchCost=0.00] HA cost: 12303.83 resc: 12303.83 resc_io: 12195.00 resc_cpu: 2523919972 resp: 12303.83 resp_io: 12195.00 resp_cpu: 2523919972 Best:: JoinMethod: Hash Cost: 12303.83 Degree: 1 Resp: 12303.83 Card: 6001215.00 Bytes: 12 *********************** Best so far: Table#: 0 cost: 643.7767 card: 1500000.0000 bytes: 9000000 Table#: 1 cost: 12303.8262 card: 6001215.0000 bytes: 72014580 *********************** Join order[2]: LINEITEM[LINEITEM]#1 ORDERS[ORDERS]#0 *************** Now joining: ORDERS[ORDERS]#0 *************** NL Join Outer table: Card: 6001215.00 Cost: 2994.16 Resp: 2994.16 Degree: 1 Bytes: 6 Inner table: ORDERS Alias: ORDERS Access Path: TableScan NL Join: Cost: 27976374778.51 Resp: 27976374778.51 Degree: 0 Cost_io: 27873478291.00 Cost_cpu: 2386397111117456 Resp_io: 27873478291.00 Resp_cpu: 2386397111117456 Access Path: index (index (FFS)) Index: PK_ORDERS resc_io: 633.55 resc_cpu: 203550602 ix_sel: 0.0000e+00 ix_sel_with_filters: 1 Inner table: ORDERS Alias: ORDERS Access Path: index (FFS) NL Join: Cost: 3854751895.27 Resp: 3854751895.27 Degree: 0 Cost_io: 3802081121.00 Cost_cpu: 1221551742006481 Resp_io: 3802081121.00 Resp_cpu: 1221551742006481 Access Path: index (UniqueScan) Index: PK_ORDERS resc_io: 1.00 resc_cpu: 15293 ix_sel: 6.6667e-07 ix_sel_with_filters: 6.6667e-07 NL Join (ordered): Cost: 10714.35 Resp: 10714.35 Degree: 1 Cost_io: 6722.00 Cost_cpu: 92591405803 Resp_io: 6722.00 Resp_cpu: 92591405803 Access Path: index (AllEqUnique) Index: PK_ORDERS resc_io: 1.00 resc_cpu: 15293 ix_sel: 6.6667e-07 ix_sel_with_filters: 6.6667e-07 NL Join (ordered): Cost: 10714.35 Resp: 10714.35 Degree: 1 Cost_io: 6722.00 Cost_cpu: 92591405803 Resp_io: 6722.00 Resp_cpu: 92591405803 Best NL cost: 10714.35 resc: 10714.35 resc_io: 6722.00 resc_cpu: 92591405803 resp: 10714.35 resp_io: 6722.00 resp_cpu: 92591405803 Join Card: 6001215.00 = outer (6001215.00) * inner (1500000.00) * sel (6.6667e-07) Join Card - Rounded: 6001215 Computed: 6001215.00 SM Join Outer table: resc: 2994.16 card 6001215.00 bytes: 6 deg: 1 resp: 2994.16 Inner table: ORDERS Alias: ORDERS resc: 643.78 card: 1500000.00 bytes: 6 deg: 1 resp: 643.78 using dmeth: 2 #groups: 1 SORT resource Sort statistics Sort width: 10 Area size: 131072 Max Area size: 2097152 Degree: 1 Blocks to Sort: 12491 Row size: 17 Total Rows: 6001215 Initial runs: 49 Merge passes: 2 IO Cost / pass: 6134 Total IO sort cost: 24759 Total CPU sort cost: 6572744138 Total Temp space used: 144614000 SORT resource Sort statistics Sort width: 10 Area size: 131072 Max Area size: 2097152 Degree: 1 Blocks to Sort: 3122 Row size: 17 Total Rows: 1500000 Initial runs: 13 Merge passes: 2 IO Cost / pass: 1534 Total IO sort cost: 6190 Total CPU sort cost: 1525060897 Total Temp space used: 36152000 SM join: Resc: 34936.10 Resp: 34936.10 [multiMatchCost=0.00] SM cost: 34936.10 resc: 34936.10 resc_io: 34543.00 resc_cpu: 9116900591 resp: 34936.10 resp_io: 34543.00 resp_cpu: 9116900591 HA Join Outer table: resc: 2994.16 card 6001215.00 bytes: 6 deg: 1 resp: 2994.16 Inner table: ORDERS Alias: ORDERS resc: 643.78 card: 1500000.00 bytes: 6 deg: 1 resp: 643.78 using dmeth: 2 #groups: 1 Cost per ptn: 8656.18 #ptns: 1 hash_area: 32 (max=512) Hash join: Resc: 12313.53 Resp: 12313.53 [multiMatchCost=19.41] HA Join (swap) Outer table: resc: 643.78 card 1500000.00 bytes: 6 deg: 1 resp: 643.78 Inner table: LINEITEM Alias: LINEITEM resc: 2994.16 card: 6001215.00 bytes: 6 deg: 1 resp: 2994.16 using dmeth: 2 #groups: 1 Cost per ptn: 8665.88 #ptns: 1 hash_area: 32 (max=512) Hash join: Resc: 12303.83 Resp: 12303.83 [multiMatchCost=0.00] HA cost: 12303.83 resc: 12303.83 resc_io: 12195.00 resc_cpu: 2523919972 resp: 12303.83 resp_io: 12195.00 resp_cpu: 2523919972 Best:: JoinMethod: NestedLoop Cost: 10714.35 Degree: 1 Resp: 10714.35 Card: 6001215.00 Bytes: 12 *********************** Best so far: Table#: 1 cost: 2994.1646 card: 6001215.0000 bytes: 36007290 Table#: 0 cost: 10714.3491 card: 6001215.0000 bytes: 72014580 (newjo-stop-1) k:0, spcnt:0, perm:2, maxperm:2000 ********************************* Number of join permutations tried: 2 ********************************* (newjo-save) [0 1 ] Final - All Rows Plan: Best join order: 2 Cost: 10714.3491 Degree: 1 Card: 6001215.0000 Bytes: 72014580 Resc: 10714.3491 Resc_io: 6722.0000 Resc_cpu: 92591405803 Resp: 10714.3491 Resp_io: 6722.0000 Resc_cpu: 92591405803 kkoipt: Query block SEL$1 (#0) ******* UNPARSED QUERY IS ******* SELECT COUNT(*) "COUNT(*)" FROM "DSS"."ORDERS" "ORDERS","DSS"."LINEITEM" "LINEITEM" WHERE "ORDERS"."O_ORDERKEY"="LINEITEM"."L_ORDERKEY" kkoqbc-end : call(in-use=39416, alloc=65448), compile(in-use=38456, alloc=40552) apadrv-end: call(in-use=39416, alloc=65448), compile(in-use=39312, alloc=40552) sql_id=52a0skswvaqvk. Current SQL statement for this session: explain plan for select count(*) from orders, lineitem where o_orderkey = l_orderkey ============ Plan Table ============ ---------------------------------------------+-----------------------------------+ | Id | Operation | Name | Rows | Bytes | Cost | Time | ---------------------------------------------+-----------------------------------+ | 0 | SELECT STATEMENT | | | | 10K | | | 1 | SORT AGGREGATE | | 1 | 12 | | | | 2 | NESTED LOOPS | | 5861K | 69M | 10K | 00:03:36 | | 3 | INDEX FAST FULL SCAN | PK_LINEITEM| 5861K | 34M | 2994 | 00:00:44 | | 4 | INDEX UNIQUE SCAN | PK_ORDERS | 1 | 6 | 1 | 00:00:01 | ---------------------------------------------+-----------------------------------+ Predicate Information: ---------------------- 4 - access("O_ORDERKEY"="L_ORDERKEY") Content of other_xml column =========================== db_version : 10.2.0.1 parse_schema : DSS plan_hash : 2594599139 Outline Data: /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('10.2.0.1') ALL_ROWS OUTLINE_LEAF(@"SEL$1") INDEX_FFS(@"SEL$1" "LINEITEM"@"SEL$1" ("LINEITEM"."L_ORDERKEY" "LINEITEM"."L_LINENUMBER")) INDEX(@"SEL$1" "ORDERS"@"SEL$1" ("ORDERS"."O_ORDERKEY")) LEADING(@"SEL$1" "LINEITEM"@"SEL$1" "ORDERS"@"SEL$1") USE_NL(@"SEL$1" "ORDERS"@"SEL$1") END_OUTLINE_DATA */ Optimizer environment: optimizer_mode_hinted = false optimizer_features_hinted = 0.0.0 parallel_execution_enabled = true parallel_query_forced_dop = 0 parallel_dml_forced_dop = 0 parallel_ddl_forced_degree = 0 parallel_ddl_forced_instances = 0 _query_rewrite_fudge = 90 optimizer_features_enable = 10.2.0.1 _optimizer_search_limit = 5 cpu_count = 4 active_instance_count = 1 parallel_threads_per_cpu = 2 hash_area_size = 131072 bitmap_merge_area_size = 1048576 sort_area_size = 65536 sort_area_retained_size = 0 _sort_elimination_cost_ratio = 0 _optimizer_block_size = 8192 _sort_multiblock_read_count = 2 _hash_multiblock_io_count = 0 _db_file_optimizer_read_count = 16 _optimizer_max_permutations = 2000 pga_aggregate_target = 10240 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 = 2048 KB _smm_px_max_size = 5120 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 = all _optimizer_system_stats_usage = true skip_unusable_indexes = true _remove_aggr_subquery = true _optimizer_push_down_distinct = 0 _dml_monitoring_enabled = true _optimizer_undo_changes = false _predicate_elimination_enabled = true _nested_loop_fudge = 100 _project_view_columns = true _local_communication_costing_enabled = true _local_communication_ratio = 50 _query_rewrite_vop_cleanup = true _slave_mapping_enabled = true _optimizer_cost_based_transformation = linear _optimizer_mjc_enabled = true _right_outer_hash_enable = true _spr_push_pred_refspr = true _optimizer_cache_stats = false _optimizer_cbqt_factor = 50 _optimizer_squ_bottomup = true _fic_area_size = 131072 _optimizer_skip_scan_enabled = true _optimizer_cost_filter_pred = false _optimizer_sortmerge_join_enabled = true _optimizer_join_sel_sanity_check = true _mmv_query_rewrite_enabled = true _bt_mmv_query_rewrite_enabled = true _add_stale_mv_to_dependency_list = true _distinct_view_unnesting = false _optimizer_dim_subq_join_sel = true _optimizer_disable_strans_sanity_checks = 0 _optimizer_compute_index_stats = true _push_join_union_view2 = true _optimizer_ignore_hints = false _optimizer_random_plan = 0 _query_rewrite_setopgrw_enable = true _optimizer_correct_sq_selectivity = true _disable_function_based_index = false _optimizer_join_order_control = 3 _optimizer_cartesian_enabled = true _optimizer_starplan_enabled = true _extended_pruning_enabled = true _optimizer_push_pred_cost_based = true _sql_model_unfold_forloops = run_time _enable_dml_lock_escalation = false _bloom_filter_enabled = true _update_bji_ipdml_enabled = 0 _optimizer_extended_cursor_sharing = udo _dm_max_shared_pool_pct = 1 _optimizer_cost_hjsmj_multimatch = true _optimizer_transitivity_retain = true _px_pwg_enabled = true optimizer_secure_view_merging = true _optimizer_join_elimination_enabled = true flashback_table_rpi = non_fbt _optimizer_cbqt_no_size_restriction = true _optimizer_enhanced_filter_push = true _optimizer_filter_pred_pullup = true _rowsrc_trace_level = 0 _simple_view_merging = true _optimizer_rownum_pred_based_fkr = true _optimizer_better_inlist_costing = all _optimizer_self_induced_cache_cost = false _optimizer_min_cache_blocks = 10 _optimizer_or_expansion = depth _optimizer_order_by_elimination_enabled = true _optimizer_outer_to_anti_enabled = true _selfjoin_mv_duplicates = true _dimension_skip_null = true _force_rewrite_enable = false _optimizer_star_tran_in_with_clause = true _optimizer_complex_pred_selectivity = true _gby_hash_aggregation_enabled = true Query Block Registry: ********************* SEL$1 0xb83f73e8 (PARSER) [FINAL] Optimizer State Dump: call(in-use=47512, alloc=65448), compile(in-use=70976, alloc=122824)