/u10/app/oracle/admin/db2rac1/udump/db2rac11_ora_7103

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

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

Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production

With the Partitioning, Real Application Clusters, OLAP and Data Mining options

ORACLE_HOME = /u10/app/oracle/product/10.2.0/db_1

System name: Linux

Node name: rh4lab15.lab.ardentperf.com

Release: 2.6.9-22.EL

Version: #1 Mon Sep 19 18:20:28 EDT 2005

Machine: i686

Instance name: db2rac11

Redo thread mounted by this instance: 1

Oracle process number: 26

Unix process pid: 7103, image: oracle@rh4lab15.lab.ardentperf.com (TNS V1-V3)

*** 2007-07-10 11:50:48.804

*** ACTION NAME:() 2007-07-10 11:50:48.804

*** MODULE NAME:(SQL*Plus) 2007-07-10 11:50:48.804

*** SERVICE NAME:(SYS$USERS) 2007-07-10 11:50:48.804

*** SESSION ID:(137.6698) 2007-07-10 11:50:48.804

Registered qb: SEL$1 0x28e1b1d4 (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 0x28e1adc8 (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 0x28baf91c (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 50gnt46vr1t3d.

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

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

Current SQL statement for this session:

explain plan for

select *

from dept

where exists (

select /*+ no_unnest */ '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 /*+ NO_UNNEST */ 'X' FROM "EMP" "OUTER")

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

signature(): NULL

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

signature(): NULL

Registered qb: SEL$3 0x251b5af4 (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: SU bypassed: No unnest hint.

SU: SU bypassed: Failed basic validity checks.

SU: Validity checks failed.

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

SU: Performing unnesting that does not require costing.

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

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

SU: SU bypassed: No unnest hint.

SU: Validity checks failed.

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

Cost-Based Complex View Merging

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

CVM: Finding query blocks in SEL$1 (#1) that are valid to merge.

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

Set-Join Conversion (SJC)

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

SJC: Considering set-join conversion in SEL$1 (#1).

SJC: Considering set-join conversion in SEL$2 (#2).

SJC: Considering set-join conversion in SEL$3 (#3).

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

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: Outer query contains no views.

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

Cost-Based Filter Predicate Pull-Up

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

Cost-Based Join Predicate Push-down

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

JPPD: Checking validity of push-down in query block SEL$3 (#3)