/u10/app/oracle/admin/db2rac1/udump/db2rac11_ora_23563_J3.trc

*** TRACE DUMP CONTINUED FROM FILE /u10/app/oracle/admin/db2rac1/udump/db2rac11_ora_23563_J2.trc ***

Registered qb: SEL$1 0x25311424 (PARSER)

signature (): qb_name=SEL$1 nbfros=1 flg=0

fro(0): flg=4 objn=51989 hint_alias="DEPT"@"SEL$1"

Registered qb: SEL$2 0x2531102c (PARSER)

signature (): qb_name=SEL$2 nbfros=1 flg=0

fro(0): flg=4 objn=51987 hint_alias="OUTER"@"SEL$2"

Registered qb: SEL$3 0x2530a8c8 (PARSER)

signature (): qb_name=SEL$3 nbfros=1 flg=0

fro(0): flg=4 objn=51987 hint_alias="INNER"@"SEL$3"

**************************

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 1w7uy3mdggx1f.

apadrv-start: call(in-use=520, alloc=16360), compile(in-use=32684, alloc=36696)

******************************************

Current SQL statement for this session:

explain plan for

select *

from dept

where exists (

select 'X'

from emp outer

where outer.sal =

(select /* no_unnest */

max(inner.sal)

from emp inner

where inner.dept_no >= outer.dept_no

) and outer.dept_no=dept.dept_no

)

*******************************************

Legend

The following abbreviations are used by optimizer trace.

CBQT - cost-based query transformation

JPPD - join predicate push-down

FPD - filter push-down

PM - predicate move-around

CVM - complex view merging

SPJ - select-project-join

SJC - set join conversion

SU - subquery unnesting

OBYE - order by elimination

ST - star transformation

qb - query block

LB - leaf blocks

DK - distinct keys

LB/K - average number of leaf blocks per key

DB/K - average number of data blocks per key

CLUF - clustering factor

NDV - number of distinct values

Resp - response cost

Card - cardinality

Resc - resource cost

NL - nested loops (join)

SM - sort merge (join)

HA - hash (join)

CPUCSPEED - CPU Speed

IOTFRSPEED - I/O transfer speed

IOSEEKTIM - I/O seek time

SREADTIM - average single block read time

MREADTIM - average multiblock read time

MBRC - average multiblock read count

MAXTHR - maximum I/O system throughput

SLAVETHR - average slave I/O throughput

dmeth - distribution method

1: no partitioning required

2: value partitioned

4: right is random (round-robin)

512: left is random (round-robin)

8: broadcast right and partition left

16: broadcast left and partition right

32: partition left using partitioning of right

64: partition right using partitioning of left

128: use hash partitioning dimension

256: use range partitioning dimension

2048: use list partitioning dimension

1024: run the join in serial

0: invalid distribution method

sel - selectivity

ptn - partition

*******************************************

Peeked values of the binds in SQL statement

*******************************************

***************************************

PARAMETERS USED BY THE OPTIMIZER

********************************

*************************************

PARAMETERS WITH ALTERED VALUES

******************************

*********************************

Bug Fix Control Environment

***************************

fix 4611850 = enabled

fix 4663804 = enabled

fix 4663698 = enabled

fix 4545833 = enabled

fix 3499674 = disabled

fix 4584065 = enabled

fix 4602374 = enabled

fix 4569940 = enabled

fix 4631959 = enabled

fix 4519340 = enabled

fix 4550003 = enabled

fix 4488689 = enabled

fix 3118776 = enabled

fix 4519016 = enabled

fix 4487253 = enabled

fix 4556762 = 15

fix 4728348 = enabled

fix 4723244 = enabled

fix 4554846 = enabled

fix 4175830 = enabled

*************************************

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.2

_optimizer_search_limit = 5

cpu_count = 1

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.2

parallel_query_mode = enabled

parallel_dml_mode = disabled

parallel_ddl_mode = enabled

optimizer_mode = all_rows

sqlstat_enabled = false

_optimizer_percent_parallel = 101

_always_anti_join = choose

_always_semi_join = choose

_optimizer_mode_force = true

_partition_view_enabled = true

_always_star_transformation = false

_query_rewrite_or_error = false

_hash_join_enabled = true

cursor_sharing = exact

_b_tree_bitmap_plans = true

star_transformation_enabled = false

_optimizer_cost_model = choose

_new_sort_cost_estimate = true

_complex_view_merging = true

_unnest_subquery = true

_eliminate_common_subexpr = true

_pred_move_around = true

_convert_set_to_join = false

_push_join_predicate = true

_push_join_union_view = true

_fast_full_scan_enabled = true

_optim_enhance_nnull_detection = true

_parallel_broadcast_enabled = true

_px_broadcast_fudge_factor = 100

_ordered_nested_loop = true

_no_or_expansion = false

optimizer_index_cost_adj = 100

optimizer_index_caching = 0

_system_index_caching = 0

_disable_datalayer_sampling = false

query_rewrite_enabled = true

query_rewrite_integrity = enforced

_query_cost_rewrite = true

_query_rewrite_2 = true

_query_rewrite_1 = true

_query_rewrite_expression = true

_query_rewrite_jgmigrate = true

_query_rewrite_fpc = true

_query_rewrite_drj = true

_full_pwise_join_enabled = true

_partial_pwise_join_enabled = true

_left_nested_loops_random = true

_improved_row_length_enabled = true

_index_join_enabled = true

_enable_type_dep_selectivity = true

_improved_outerjoin_card = true

_optimizer_adjust_for_nulls = true

_optimizer_degree = 0

_use_column_stats_for_function = true

_subquery_pruning_enabled = true

_subquery_pruning_mv_enabled = false

_or_expand_nvl_predicate = true

_like_with_bind_as_equality = false

_table_scan_cost_plus_one = true

_cost_equality_semi_join = true

_default_non_equality_sel_check = true

_new_initial_join_orders = true

_oneside_colstat_for_equijoins = true

_optim_peek_user_binds = true

_minimal_stats_aggregation = true

_force_temptables_for_gsets = false

workarea_size_policy = auto

_smm_auto_cost_enabled = true

_gs_anti_semi_join_allowed = true

_optim_new_default_join_sel = true

optimizer_dynamic_sampling = 2

_pre_rewrite_push_pred = true

_optimizer_new_join_card_computation = true

_union_rewrite_for_gs = yes_gset_mvs

_generalized_pruning_enabled = true

_optim_adjust_for_part_skews = true

_force_datefold_trunc = false

statistics_level = typical

_optimizer_system_stats_usage = true

skip_unusable_indexes = true

_remove_aggr_subquery = true

_optimizer_push_down_distinct = 0

_dml_monitoring_enabled = true

_optimizer_undo_changes = false

_predicate_elimination_enabled = true

_nested_loop_fudge = 100

_project_view_columns = true

_local_communication_costing_enabled = true

_local_communication_ratio = 50

_query_rewrite_vop_cleanup = true

_slave_mapping_enabled = true

_optimizer_cost_based_transformation = linear

_optimizer_mjc_enabled = true

_right_outer_hash_enable = true

_spr_push_pred_refspr = true

_optimizer_cache_stats = false

_optimizer_cbqt_factor = 50

_optimizer_squ_bottomup = true

_fic_area_size = 131072

_optimizer_skip_scan_enabled = true

_optimizer_cost_filter_pred = false

_optimizer_sortmerge_join_enabled = true

_optimizer_join_sel_sanity_check = true

_mmv_query_rewrite_enabled = true

_bt_mmv_query_rewrite_enabled = true

_add_stale_mv_to_dependency_list = true

_distinct_view_unnesting = false

_optimizer_dim_subq_join_sel = true

_optimizer_disable_strans_sanity_checks = 0

_optimizer_compute_index_stats = true

_push_join_union_view2 = true

_optimizer_ignore_hints = false

_optimizer_random_plan = 0

_query_rewrite_setopgrw_enable = true

_optimizer_correct_sq_selectivity = true

_disable_function_based_index = false

_optimizer_join_order_control = 3

_optimizer_cartesian_enabled = true

_optimizer_starplan_enabled = true

_extended_pruning_enabled = true

_optimizer_push_pred_cost_based = true

_sql_model_unfold_forloops = run_time

_enable_dml_lock_escalation = false

_bloom_filter_enabled = true

_update_bji_ipdml_enabled = 0

_optimizer_extended_cursor_sharing = udo

_dm_max_shared_pool_pct = 1

_optimizer_cost_hjsmj_multimatch = true

_optimizer_transitivity_retain = true

_px_pwg_enabled = true

optimizer_secure_view_merging = true

_optimizer_join_elimination_enabled = true

flashback_table_rpi = non_fbt

_optimizer_cbqt_no_size_restriction = true

_optimizer_enhanced_filter_push = true

_optimizer_filter_pred_pullup = true

_rowsrc_trace_level = 0

_simple_view_merging = true

_optimizer_rownum_pred_based_fkr = true

_optimizer_better_inlist_costing = all

_optimizer_self_induced_cache_cost = false

_optimizer_min_cache_blocks = 10

_optimizer_or_expansion = depth

_optimizer_order_by_elimination_enabled = true

_optimizer_outer_to_anti_enabled = true

_selfjoin_mv_duplicates = true

_dimension_skip_null = true

_force_rewrite_enable = false

_optimizer_star_tran_in_with_clause = true

_optimizer_complex_pred_selectivity = true

_optimizer_connect_by_cost_based = true

_gby_hash_aggregation_enabled = true

_globalindex_pnum_filter_enabled = true

_fix_control_key = 0

_optimizer_skip_scan_guess = false

_enable_row_shipping = false

*********************************

Bug Fix Control Environment

***************************

fix 4611850 = enabled

fix 4663804 = enabled

fix 4663698 = enabled

fix 4545833 = enabled

fix 3499674 = disabled

fix 4584065 = enabled

fix 4602374 = enabled

fix 4569940 = enabled

fix 4631959 = enabled

fix 4519340 = enabled

fix 4550003 = enabled

fix 4488689 = enabled

fix 3118776 = enabled

fix 4519016 = enabled

fix 4487253 = enabled

fix 4556762 = 15

fix 4728348 = enabled

fix 4723244 = enabled

fix 4554846 = enabled

fix 4175830 = enabled

***************************************

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) :

EXISTS (SELECT 'X' FROM "EMP" "OUTER")

Registered qb: SEL$1 0x2530881c (COPY SEL$1)

signature(): NULL

Registered qb: SEL$2 0x25307cc8 (COPY SEL$2)

signature(): NULL

Registered qb: SEL$3 0x25304e58 (COPY SEL$3)

signature(): NULL

*****************************

Cost-Based Subquery Unnesting

*****************************

SU: No subqueries to consider in query block SEL$3 (#3).

SU: Considering subquery unnesting in query block SEL$2 (#2)

SU: Performing unnesting that does not require costing.

SU: Considering subquery unnest on SEL$2 (#2).

SU: Checking validity of unnesting subquery SEL$3 (#3)

SU: Passed validity checks, but requires costing.

SU: Using search type: exhaustive

SU: Starting iteration 1, state space = (3) : (1)

CBQT: Looking for costed query block SEL$3, key = 7

CBQT: Could not find stored query block.

SU: Unnesting subquery SEL$3 (#3)

Registered qb: SEL$5D0CF27A 0x252fa9e8 (QUERY BLOCK TABLES CHANGED SEL$3)

signature (): qb_name=SEL$5D0CF27A nbfros=2 flg=0

fro(0): flg=0 objn=51987 hint_alias="INNER"@"SEL$3"

fro(1): flg=4 objn=51987 hint_alias="OUTER"@"SEL$5D0CF27A"

Registered qb: SEL$12EAFFF1 0x252fa9e8 (SUBQ INTO VIEW FOR COMPLEX UNNEST SEL$5D0CF27A)

signature (): qb_name=SEL$12EAFFF1 nbfros=2 flg=0

fro(0): flg=0 objn=51987 hint_alias="INNER"@"SEL$3"

fro(1): flg=0 objn=51987 hint_alias="OUTER"@"SEL$5D0CF27A"

Registered qb: SEL$4111C18D 0x252fb7b0 (VIEW ADDED SEL$2)

signature (): qb_name=SEL$4111C18D nbfros=2 flg=0

fro(0): flg=0 objn=51987 hint_alias="OUTER"@"SEL$2"

fro(1): flg=5 objn=0 hint_alias="VW_SQ_1"@"SEL$4111C18D"

Registered qb: SEL$EEC73E22 0x252fb7b0 (SUBQUERY UNNEST SEL$4111C18D; SEL$3)

signature (): qb_name=SEL$EEC73E22 nbfros=2 flg=0

fro(0): flg=0 objn=51987 hint_alias="OUTER"@"SEL$2"

fro(1): flg=1 objn=0 hint_alias="VW_SQ_1"@"SEL$4111C18D"

FPD: Considering simple filter push in SEL$EEC73E22 (#2)

FPD: Current where clause predicates in SEL$EEC73E22 (#2) :

"OUTER"."SAL"="VW_SQ_1"."VW_COL_1" AND "VW_SQ_1"."ROWID"="OUTER".ROWID AND "OUTER"."DEPT_NO"=:B1

kkogcp: try to generate transitive predicate from check constraints for SEL$EEC73E22 (#2)

predicates with check contraints: "OUTER"."SAL"="VW_SQ_1"."VW_COL_1" AND "VW_SQ_1"."ROWID"="OUTER".ROWID AND "OUTER"."DEPT_NO"=:B1

after transitive predicate generation: "OUTER"."SAL"="VW_SQ_1"."VW_COL_1" AND "VW_SQ_1"."ROWID"="OUTER".ROWID AND "OUTER"."DEPT_NO"=:B1

finally: "OUTER"."SAL"="VW_SQ_1"."VW_COL_1" AND "VW_SQ_1"."ROWID"="OUTER".ROWID AND "OUTER"."DEPT_NO"=:B1

FPD: Considering simple filter push in SEL$12EAFFF1 (#3)

FPD: Current where clause predicates in SEL$12EAFFF1 (#3) :

"INNER"."DEPT_NO">="OUTER"."DEPT_NO"

kkogcp: try to generate transitive predicate from check constraints for SEL$12EAFFF1 (#3)

predicates with check contraints: "INNER"."DEPT_NO">="OUTER"."DEPT_NO"

after transitive predicate generation: "INNER"."DEPT_NO">="OUTER"."DEPT_NO"

finally: "INNER"."DEPT_NO">="OUTER"."DEPT_NO"

SU: Costing transformed query.

kkoqbc-start

: call(in-use=520, alloc=16360), compile(in-use=89416, alloc=89840)

****************

QUERY BLOCK TEXT

****************

Not available.

*********************

QUERY BLOCK SIGNATURE

*********************

qb name was generated

signature (optimizer): qb_name=SEL$12EAFFF1 nbfros=2 flg=0

fro(0): flg=0 objn=51987 hint_alias="INNER"@"SEL$3"

fro(1): flg=0 objn=51987 hint_alias="OUTER"@"SEL$5D0CF27A"

*****************************

SYSTEM STATISTICS INFORMATION

*****************************

Using NOWORKLOAD Stats

CPUSPEED: 188 millions instruction/sec

IOTFRSPEED: 4096 bytes per millisecond (default is 4096)

IOSEEKTIM: 10 milliseconds (default is 10)

***************************************

BASE STATISTICAL INFORMATION

***********************

Table Stats::

Table: EMP Alias: INNER

#Rows: 20000 #Blks: 244 AvgRowLen: 72.00

Index Stats::

Index: E_PK Col#: 3

LVLS: 1 #LB: 37 #DK: 20000 LB/K: 1.00 DB/K: 1.00 CLUF: 216.00

***********************

Table Stats::

Table: EMP Alias: OUTER

#Rows: 20000 #Blks: 244 AvgRowLen: 72.00

Index Stats::

Index: E_PK Col#: 3

LVLS: 1 #LB: 37 #DK: 20000 LB/K: 1.00 DB/K: 1.00 CLUF: 216.00

***************************************

SINGLE TABLE ACCESS PATH

Table: EMP Alias: OUTER

Card: Original: 20000 Rounded: 20000 Computed: 20000.00 Non Adjusted: 20000.00

Access Path: TableScan

Cost: 57.10 Resp: 57.10 Degree: 0

Cost_io: 55.00 Cost_cpu: 4737631

Resp_io: 55.00 Resp_cpu: 4737631

Best:: AccessPath: TableScan

Cost: 57.10 Degree: 1 Resp: 57.10 Card: 20000.00 Bytes: 0

***************************************

SINGLE TABLE ACCESS PATH

Table: EMP Alias: INNER

Card: Original: 20000 Rounded: 20000 Computed: 20000.00 Non Adjusted: 20000.00

Access Path: TableScan

Cost: 57.27 Resp: 57.27 Degree: 0

Cost_io: 55.00 Cost_cpu: 5137631

Resp_io: 55.00 Resp_cpu: 5137631

Best:: AccessPath: TableScan

Cost: 57.27 Degree: 1 Resp: 57.27 Card: 20000.00 Bytes: 0

***************************************

OPTIMIZER STATISTICS AND COMPUTATIONS

***************************************

GENERAL PLANS

***************************************

Considering cardinality-based initial join order.

Permutations for Starting Table :0

***********************

Join order[1]: EMP[OUTER]#0 EMP[INNER]#1

***************

Now joining: EMP[INNER]#1

***************

NL Join

Outer table: Card: 20000.00 Cost: 57.10 Resp: 57.10 Degree: 1 Bytes: 27

Inner table: EMP Alias: INNER

Access Path: TableScan

NL Join: Cost: 1113011.63 Resp: 1113011.63 Degree: 0

Cost_io: 1067556.00 Cost_cpu: 102757364831

Resp_io: 1067556.00 Resp_cpu: 102757364831

Best NL cost: 1113011.63

resc: 1113011.63 resc_io: 1067556.00 resc_cpu: 102757364831

resp: 1113011.63 resp_io: 1067556.00 resp_cpu: 102757364831

Join Card: 20000000.00 = outer (20000.00) * inner (20000.00) * sel (0.05)

Join Card - Rounded: 20000000 Computed: 20000000.00

SM Join

Outer table:

resc: 57.10 card 20000.00 bytes: 27 deg: 1 resp: 57.10

Inner table: EMP Alias: INNER

resc: 57.27 card: 20000.00 bytes: 8 deg: 1 resp: 57.27

using dmeth: 2 #groups: 1

SORT resource Sort statistics

Sort width: 17 Area size: 131072 Max Area size: 3354624

Degree: 1

Blocks to Sort: 98 Row size: 40 Total Rows: 20000

Initial runs: 2 Merge passes: 1 IO Cost / pass: 54

Total IO sort cost: 152 Total CPU sort cost: 17548490

Total Temp space used: 1631000

SORT resource Sort statistics

Sort width: 17 Area size: 131072 Max Area size: 3354624

Degree: 1

Blocks to Sort: 47 Row size: 19 Total Rows: 20000

Initial runs: 2 Merge passes: 1 IO Cost / pass: 26

Total IO sort cost: 73 Total CPU sort cost: 16292564

Total Temp space used: 664000

SM join: Resc: 1238.17 Resp: 1238.17 [multiMatchCost=883.83]

SM cost: 1238.17

resc: 1238.17 resc_io: 335.00 resc_cpu: 2041716316

resp: 1238.17 resp_io: 335.00 resp_cpu: 2041716316

GROUP BY sort

GROUP BY cardinality: 1.00, TABLE cardinality: 20000000.00

SORT resource Sort statistics

Sort width: 17 Area size: 131072 Max Area size: 3354624

Degree: 1

Blocks to Sort: 119981 Row size: 49 Total Rows: 20000000

Initial runs: 1 Merge passes: 0 IO Cost / pass: 0

Total IO sort cost: 0 Total CPU sort cost: 21856876289

Total Temp space used: 0

Best:: JoinMethod: SortMerge

Cost: 10906.75 Degree: 1 Resp: 10906.75 Card: 20000000.00 Bytes: 35

***********************

Best so far: Table#: 0 cost: 57.0957 card: 20000.0000 bytes: 540000

Table#: 1 cost: 10906.7544 card: 20000000.0000 bytes: 700000000

***********************

Join order[2]: EMP[INNER]#1 EMP[OUTER]#0

***************

Now joining: EMP[OUTER]#0

***************

NL Join

Outer table: Card: 20000.00 Cost: 57.27 Resp: 57.27 Degree: 1 Bytes: 8

Inner table: EMP Alias: OUTER

Access Path: TableScan

NL Join: Cost: 1109472.94 Resp: 1109472.94 Degree: 0

Cost_io: 1067556.00 Cost_cpu: 94757764831

Resp_io: 1067556.00 Resp_cpu: 94757764831

Best NL cost: 1109472.94

resc: 1109472.94 resc_io: 1067556.00 resc_cpu: 94757764831

resp: 1109472.94 resp_io: 1067556.00 resp_cpu: 94757764831

Join Card: 20000000.00 = outer (20000.00) * inner (20000.00) * sel (0.05)

Join Card - Rounded: 20000000 Computed: 20000000.00

SM Join

Outer table:

resc: 57.27 card 20000.00 bytes: 8 deg: 1 resp: 57.27

Inner table: EMP Alias: OUTER

resc: 57.10 card: 20000.00 bytes: 27 deg: 1 resp: 57.10

using dmeth: 2 #groups: 1

SORT resource Sort statistics

Sort width: 17 Area size: 131072 Max Area size: 3354624

Degree: 1

Blocks to Sort: 47 Row size: 19 Total Rows: 20000

Initial runs: 2 Merge passes: 1 IO Cost / pass: 26

Total IO sort cost: 73 Total CPU sort cost: 16292564

Total Temp space used: 664000

SORT resource Sort statistics

Sort width: 17 Area size: 131072 Max Area size: 3354624

Degree: 1

Blocks to Sort: 98 Row size: 40 Total Rows: 20000

Initial runs: 2 Merge passes: 1 IO Cost / pass: 54

Total IO sort cost: 152 Total CPU sort cost: 17548490

Total Temp space used: 1631000

SM join: Resc: 1238.17 Resp: 1238.17 [multiMatchCost=883.83]

SM cost: 1238.17

resc: 1238.17 resc_io: 335.00 resc_cpu: 2041716316

resp: 1238.17 resp_io: 335.00 resp_cpu: 2041716316

GROUP BY sort

GROUP BY cardinality: 1.00, TABLE cardinality: 20000000.00

SORT resource Sort statistics

Sort width: 17 Area size: 131072 Max Area size: 3354624

Degree: 1

Blocks to Sort: 119981 Row size: 49 Total Rows: 20000000

Initial runs: 1 Merge passes: 0 IO Cost / pass: 0

Total IO sort cost: 0 Total CPU sort cost: 21856876289

Total Temp space used: 0

Join order aborted: cost > best plan cost

***********************

(newjo-stop-1) k:0, spcnt:0, perm:2, maxperm:2000

*********************************

Number of join permutations tried: 2

*********************************

(newjo-save) [1 0 ]

SORT resource Sort statistics

Sort width: 17 Area size: 131072 Max Area size: 3354624

Degree: 1

Blocks to Sort: 119981 Row size: 49 Total Rows: 20000000

Initial runs: 293 Merge passes: 3 IO Cost / pass: 64992

Total IO sort cost: 314957 Total CPU sort cost: 27766180501

Total Temp space used: 1771267000

Final - All Rows Plan: Best join order: 1

Cost: 10906.7544 Degree: 1 Card: 20000000.0000 Bytes: 700000000

Resc: 10906.7544 Resc_io: 335.0000 Resc_cpu: 23898592605

Resp: 10906.7544 Resp_io: 335.0000 Resc_cpu: 23898592605

kkoipt: Query block SEL$12EAFFF1 (#3)

******* UNPARSED QUERY IS *******

SELECT /*+ */ MAX("INNER"."SAL") "VW_COL_1","OUTER".ROWID "ROWID" FROM "JEREMY"."EMP" "OUTER","JEREMY"."EMP" "INNER" WHERE "INNER"."DEPT_NO">="OUTER"."DEPT_NO" GROUP BY "OUTER".ROWID

kkoqbc-end

: call(in-use=31984, alloc=49112), compile(in-use=90280, alloc=93896)

kkoqbc-start

: call(in-use=31984, alloc=49112), compile(in-use=90500, alloc=93896)

****************

QUERY BLOCK TEXT

****************

Not available.

*********************

QUERY BLOCK SIGNATURE

*********************

qb name was generated

signature (optimizer): qb_name=SEL$EEC73E22 nbfros=2 flg=0

fro(0): flg=0 objn=51987 hint_alias="OUTER"@"SEL$2"

fro(1): flg=1 objn=0 hint_alias="VW_SQ_1"@"SEL$4111C18D"

*****************************

SYSTEM STATISTICS INFORMATION

*****************************

Using NOWORKLOAD Stats

CPUSPEED: 188 millions instruction/sec

IOTFRSPEED: 4096 bytes per millisecond (default is 4096)

IOSEEKTIM: 10 milliseconds (default is 10)

***************************************

BASE STATISTICAL INFORMATION

***********************

Table Stats::

Table: EMP Alias: OUTER

#Rows: 20000 #Blks: 244 AvgRowLen: 72.00

Index Stats::

Index: E_PK Col#: 3

LVLS: 1 #LB: 37 #DK: 20000 LB/K: 1.00 DB/K: 1.00 CLUF: 216.00

***********************

Table Stats::

Table: VW_SQ_1 Alias: VW_SQ_1 NO STATISTICS

***************************************

SINGLE TABLE ACCESS PATH

Column (#1): DEPT_NO(NUMBER)

AvgLen: 3.00 NDV: 6 Nulls: 0 Density: 0.16667 Min: 0 Max: 5

Table: EMP Alias: OUTER

Card: Original: 20000 Rounded: 3333 Computed: 3333.33 Non Adjusted: 3333.33

Access Path: TableScan

Cost: 57.57 Resp: 57.57 Degree: 0

Cost_io: 55.00 Cost_cpu: 5804291

Resp_io: 55.00 Resp_cpu: 5804291

Best:: AccessPath: TableScan

Cost: 57.57 Degree: 1 Resp: 57.57 Card: 3333.33 Bytes: 0

Multi-column join key card: 20000 #cols: 2 table: EMP

***************************************

OPTIMIZER STATISTICS AND COMPUTATIONS