****************
修改t1表
****************
create table t1 as select * from dba_objects;
update t1 set object_id=1 where object_id >1 and object_id <10000;
update t1 set object_id=10000 where object_id >10000 and object_id <20000;
update t1 set object_id=20000 where object_id >20000 and object_id <30000;
update t1 set object_id=30000 where object_id >30000 and object_id <40000;
update t1 set object_id=40000 where object_id >40000 and object_id <50000;
update t1 set object_id=50000 where object_id >50000 and object_id <60000;
update t1 set object_id=60000 where object_id >60000 and object_id <70000;
update t1 set object_id=70000 where object_id >70000 and object_id <80000;
update t1 set object_id=80000 where object_id >80000 and object_id <90000;
SQL> create index t1_object_id on t1(object_id);
Index created.
SQL> create index t2_object_id on t2(object_id);
Index created.
select sum(t1.object_id),sum(t2.object_id)
from t1,t2
where t1.object_id=t2.object_id
and t1.object_id=10000
and t2.object_id=10000;
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1955305233
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 19 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
| 2 | NESTED LOOPS | | 9910 | 79280 | 19 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN| T2_OBJECT_ID | 1 | 5 | 1 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN| T1_OBJECT_ID | 9910 | 29730 | 18 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T2"."OBJECT_ID"=10000)
4 - access("T1"."OBJECT_ID"=10000)
filter("T1"."OBJECT_ID"="T2"."OBJECT_ID")
18 rows selected.
***********************
测试leading T1
***********************
select /*+ use_nl(T1 T2) leading(T1) */ sum(t1.object_id),sum(t2.object_id)
from t1,t2
where t1.object_id=t2.object_id
and t1.object_id=10000
and t2.object_id=10000;
SQL> explain plan for select /*+ use_nl(T1 T2) leading(T1) */ sum(t1.object_id),sum(t2.object_id)
from t1,t2
where t1.object_id=t2.object_id
and t1.object_id=10000
and t2.object_id=10000; 2 3 4 5
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1466620934
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 9798 (1)| 00:01:58 |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
| 2 | NESTED LOOPS | | 9773 | 78184 | 9798 (1)| 00:01:58 |
|* 3 | INDEX RANGE SCAN| T1_OBJECT_ID | 9773 | 29319 | 20 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN| T2_OBJECT_ID | 1 | 5 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T1"."OBJECT_ID"=10000)
4 - access("T2"."OBJECT_ID"=10000)
filter("T1"."OBJECT_ID"="T2"."OBJECT_ID")
18 rows selected.
***********************
测试leading T2
***********************
select /*+ use_nl(T1 T2) leading(T2) */ sum(t1.object_id),sum(t2.object_id)
from t1,t2
where t1.object_id=t2.object_id
and t1.object_id=10000
and t2.object_id=10000;
SQL> explain plan for select /*+ use_nl(T1 T2) leading(T2) */ sum(t1.object_id),sum(t2.object_id)
from t1,t2
where t1.object_id=t2.object_id
and t1.object_id=10000
and t2.object_id=10000; 2 3 4 5
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1955305233
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 19 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
| 2 | NESTED LOOPS | | 9773 | 78184 | 19 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN| T2_OBJECT_ID | 1 | 5 | 1 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN| T1_OBJECT_ID | 9773 | 29319 | 18 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T2"."OBJECT_ID"=10000)
4 - access("T1"."OBJECT_ID"=10000)
filter("T1"."OBJECT_ID"="T2"."OBJECT_ID")
18 rows selected.
**********************
**********************
alter session set events '10053 trace name context forever,level 1';
explain plan select sum(t1.object_id),sum(t2.object_id)
from t1,t2
where t1.object_id=t2.object_id
and t1.object_id=10000
and t2.object_id=10000;
alter session set events '10053 trace name context off';
Trace file /u01/app/oracle/diag/rdbms/yhdb/yhdb1/trace/yhdb1_ora_17830.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/db_1
System name: Linux
Node name: yhdb
Release: 2.6.32-573.el6.x86_64
Version: #1 SMP Wed Jul 1 18:23:37 EDT 2015
Machine: x86_64
VM name: VMWare Version: 6
Instance name: yhdb1
Redo thread mounted by this instance: 1
Oracle process number: 33
Unix process pid: 17830, image: oracle@yhdb (TNS V1-V3)
*** 2018-05-06 20:21:39.463
*** SESSION ID:(162.25) 2018-05-06 20:21:39.463
*** CLIENT ID:() 2018-05-06 20:21:39.463
*** SERVICE NAME:(SYS$USERS) 2018-05-06 20:21:39.463
*** MODULE NAME:(SQL*Plus) 2018-05-06 20:21:39.463
*** ACTION NAME:() 2018-05-06 20:21:39.463
Registered qb: SEL$1 0x86e1de58 (PARSER)
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (): qb_name=SEL$1 nbfros=2 flg=0
fro(0): flg=4 objn=87599 hint_alias="T1"@"SEL$1"
fro(1): flg=4 objn=87600 hint_alias="T2"@"SEL$1"
SPM: statement not found in SMB
SPM: statement not a candidate for auto-capture
**************************
Automatic degree of parallelism (ADOP)
**************************
Automatic degree of parallelism is disabled: Parameter.
PM: Considering predicate move-around in query block SEL$1 (#0)
**************************
Predicate Move-Around (PM)
**************************
OPTIMIZER INFORMATION
******************************************
----- Current SQL Statement for this session (sql_id=6hp0811hnj1zs) -----
explain plan for select sum(t1.object_id),sum(t2.object_id)
from t1,t2
where t1.object_id=t2.object_id
and t1.object_id=10000
and t2.object_id=10000
*******************************************
Legend
The following abbreviations are used by optimizer trace.
CBQT-cost-based query transformation
JPPD - join predicate push-down
OJPPD - old-style (non-cost-based) JPPD
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
OST - old style star transformation
ST - new (cbqt) star transformation
CNT - count(col) to count(*) transformation
JE - Join Elimination
JF - join factorization
CBY - connect by
SLP - select list pruning
DP - distinct placement
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)
CPUSPEED - 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)
128: 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
256: run the join in serial
0: invalid distribution method
sel - selectivity
ptn - partition
***************************************
PARAMETERS USED BY THE OPTIMIZER
********************************
*************************************
PARAMETERS WITH ALTERED VALUES
******************************
Compilation Environment Dump
Bug Fix Control Environment
*************************************
PARAMETERS WITH DEFAULT VALUES
******************************
Compilation Environment Dump
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 = 11.2.0.4
_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 = 8
_optimizer_max_permutations = 2000
pga_aggregate_target = 282624 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 = 282 KB
_smm_max_size_static = 56524 KB
_smm_px_max_size_static = 141312 KB
_cpu_to_io = 0
_optimizer_undo_cost_change = 11.2.0.4
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
_optimizer_null_aware_antijoin = true
_optimizer_extend_jppd_view_types = 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
_px_minus_intersect = true
_fix_control_key = 0
_force_slave_mapping_intra_part_loads = false
_force_tmp_segment_loads = false
_query_mmvrewrite_maxpreds = 10
_query_mmvrewrite_maxintervals = 5
_query_mmvrewrite_maxinlists = 5
_query_mmvrewrite_maxdmaps = 10
_query_mmvrewrite_maxcmaps = 20
_query_mmvrewrite_maxregperm = 512
_query_mmvrewrite_maxqryinlistvals = 500
_disable_parallel_conventional_load = false
_trace_virtual_columns = false
_replace_virtual_columns = true
_virtual_column_overload_allowed = true
_kdt_buffering = true
_first_k_rows_dynamic_proration = true
_optimizer_sortmerge_join_inequality = true
_optimizer_aw_stats_enabled = true
_bloom_pruning_enabled = true
result_cache_mode = MANUAL
_px_ual_serial_input = true
_optimizer_skip_scan_guess = false
_enable_row_shipping = true
_row_shipping_threshold = 80
_row_shipping_explain = false
transaction_isolation_level = read_commited
_optimizer_distinct_elimination = true
_optimizer_multi_level_push_pred = true
_optimizer_group_by_placement = true
_optimizer_rownum_bind_default = 10
_enable_query_rewrite_on_remote_objs = true
_optimizer_extended_cursor_sharing_rel = simple
_optimizer_adaptive_cursor_sharing = true
_direct_path_insert_features = 0
_optimizer_improve_selectivity = true
optimizer_use_pending_statistics = false
_optimizer_enable_density_improvements = true
_optimizer_aw_join_push_enabled = true
_optimizer_connect_by_combine_sw = true
_enable_pmo_ctas = 0
_optimizer_native_full_outer_join = force
_bloom_predicate_enabled = true
_optimizer_enable_extended_stats = true
_is_lock_table_for_ddl_wait_lock = 0
_pivot_implementation_method = choose
optimizer_capture_sql_plan_baselines = false
optimizer_use_sql_plan_baselines = true
_optimizer_star_trans_min_cost = 0
_optimizer_star_trans_min_ratio = 0
_with_subquery = OPTIMIZER
_optimizer_fkr_index_cost_bias = 10
_optimizer_use_subheap = true
parallel_degree_policy = manual
parallel_degree = 0
parallel_min_time_threshold = 10
_parallel_time_unit = 10
_optimizer_or_expansion_subheap = true
_optimizer_free_transformation_heap = true
_optimizer_reuse_cost_annotations = true
_result_cache_auto_size_threshold = 100
_result_cache_auto_time_threshold = 1000
_optimizer_nested_rollup_for_gset = 100
_nlj_batching_enabled = 1
parallel_query_default_dop = 0
is_recur_flags = 0
optimizer_use_invisible_indexes = false
flashback_data_archive_internal_cursor = 0
_optimizer_extended_stats_usage_control = 192
_parallel_syspls_obey_force = true
cell_offload_processing = true
_rdbms_internal_fplib_enabled = false
db_file_multiblock_read_count = 83
_bloom_folding_enabled = true
_mv_generalized_oj_refresh_opt = true
cell_offload_compaction = ADAPTIVE
cell_offload_plan_display = AUTO
_bloom_predicate_pushdown_to_storage = true
_bloom_vector_elements = 0
_bloom_pushing_max = 512
parallel_degree_limit = 65535
parallel_force_local = false
parallel_max_degree = 4
total_cpu_count = 2
_optimizer_coalesce_subqueries = true
_optimizer_fast_pred_transitivity = true
_optimizer_fast_access_pred_analysis = true
_optimizer_unnest_disjunctive_subq = true
_optimizer_unnest_corr_set_subq = true
_optimizer_distinct_agg_transform = true
_aggregation_optimization_settings = 0
_optimizer_connect_by_elim_dups = true
_optimizer_eliminate_filtering_join = true
_connect_by_use_union_all = true
dst_upgrade_insert_conv = true
advanced_queuing_internal_cursor = 0
_optimizer_unnest_all_subqueries = true
parallel_autodop = 0
parallel_ddldml = 0
_parallel_cluster_cache_policy = adaptive
_parallel_scalability = 50
iot_internal_cursor = 0
_optimizer_instance_count = 0
_optimizer_connect_by_cb_whr_only = false
_suppress_scn_chk_for_cqn = nosuppress_1466
_optimizer_join_factorization = true
_optimizer_use_cbqt_star_transformation = true
_optimizer_table_expansion = true
_and_pruning_enabled = true
_deferred_constant_folding_mode = DEFAULT
_optimizer_distinct_placement = true
partition_pruning_internal_cursor = 0
parallel_hinted = none
_sql_compatibility = 0
_optimizer_use_feedback = true
_optimizer_try_st_before_jppd = true
_dml_frequency_tracking = false
_optimizer_interleave_jppd = true
kkb_drop_empty_segments = 0
_px_partition_scan_enabled = true
_px_partition_scan_threshold = 64
_optimizer_false_filter_pred_pullup = true
_bloom_minmax_enabled = true
only_move_row = 0
_optimizer_enable_table_lookup_by_nl = true
deferred_segment_creation = true
_optimizer_filter_pushdown = true
_allow_level_without_connect_by = false
_max_rwgs_groupings = 8192
_optimizer_outer_join_to_inner = true
_optimizer_full_outer_join_to_outer = true
_px_numa_support_enabled = true
total_processor_group_count = 1
_bloom_sm_enabled = false
_bloom_serial_filter = on
_parallel_degree_level = 100
_optimizer_ads_max_table_count = 0
_optimizer_ads_time_limit = 0
_optimizer_ads_use_result_cache = true
parallel_dblink = 0
_optimizer_cluster_by_rowid = false
_optimizer_cluster_by_rowid_control = 3
Bug Fix Control Environment
fix 3834770 = 1
。。。
fix 16554552 = enabled
***************************************
PARAMETERS IN OPT_PARAM HINT
****************************
***************************************
Column Usage Monitoring is ON: tracking level = 1
***************************************
Considering Query Transformations on query block SEL$1 (#0)
**************************
Query transformations (QT)
**************************
JF: Checking validity of join factorization for query block SEL$1 (#0)
JF: Bypassed: not a UNION or UNION-ALL query block.
ST: not valid since star transformation parameter is FALSE
TE: Checking validity of table expansion for query block SEL$1 (#0)
TE: Bypassed: No partitioned table in query block.
CBQT bypassed for query block SEL$1 (#0): no complex view, sub-queries or UNION (ALL) queries.
CBQT: Validity checks failed for 6hp0811hnj1zs.
CSE: Considering common sub-expression elimination in query block SEL$1 (#0)
*************************
Common Subexpression elimination (CSE)
*************************
CSE: CSE not performed on query block SEL$1 (#0).
OBYE: Considering Order-by Elimination from view SEL$1 (#0)
***************************
Order-by elimination (OBYE)
***************************
OBYE: OBYE bypassed: no order by to eliminate.
JE: Considering Join Elimination on query block SEL$1 (#0)
*************************
Join Elimination (JE)
*************************
SQL:******* UNPARSED QUERY IS *******
SELECT SUM("T1"."OBJECT_ID") "SUM(T1.OBJECT_ID)",SUM("T2"."OBJECT_ID") "SUM(T2.OBJECT_ID)" FROM "DAYU"."T1" "T1","DAYU"."T2" "T2" WHERE "T1"."OBJECT_ID"="T2"."OBJECT_ID" AND "T1"."OBJECT_ID"=10000 AND "T2"."OBJECT_ID"=10000
JE: cfro: T1 objn:87600 col#:4 dfro:T2 dcol#:4
JE: cfro: T1 objn:87600 col#:4 dfro:T2 dcol#:4
JE: cfro: T1 objn:87600 col#:4 dfro:T2 dcol#:4
JE: cfro: T2 objn:87599 col#:4 dfro:T1 dcol#:4
JE: cfro: T2 objn:87599 col#:4 dfro:T1 dcol#:4
JE: cfro: T2 objn:87599 col#:4 dfro:T1 dcol#:4
SQL:******* UNPARSED QUERY IS *******
SELECT SUM("T1"."OBJECT_ID") "SUM(T1.OBJECT_ID)",SUM("T2"."OBJECT_ID") "SUM(T2.OBJECT_ID)" FROM "DAYU"."T1" "T1","DAYU"."T2" "T2" WHERE "T1"."OBJECT_ID"="T2"."OBJECT_ID" AND "T1"."OBJECT_ID"=10000 AND "T2"."OBJECT_ID"=10000
Query block SEL$1 (#0) unchanged
CVM: Considering view merge in query block SEL$1 (#0)
OJE: Begin: find best directive for query block SEL$1 (#0)
OJE: End: finding best directive for query block SEL$1 (#0)
CNT: Considering count(col) to count(*) on query block SEL$1 (#0)
*************************
Count(col) to Count(*) (CNT)
*************************
CNT: COUNT() to COUNT(*) not done.
JE: Considering Join Elimination on query block SEL$1 (#0)
*************************
Join Elimination (JE)
*************************
SQL:******* UNPARSED QUERY IS *******
SELECT SUM("T1"."OBJECT_ID") "SUM(T1.OBJECT_ID)",SUM("T2"."OBJECT_ID") "SUM(T2.OBJECT_ID)" FROM "DAYU"."T1" "T1","DAYU"."T2" "T2" WHERE "T1"."OBJECT_ID"="T2"."OBJECT_ID" AND "T1"."OBJECT_ID"=10000 AND "T2"."OBJECT_ID"=10000
JE: cfro: T1 objn:87600 col#:4 dfro:T2 dcol#:4
JE: cfro: T1 objn:87600 col#:4 dfro:T2 dcol#:4
JE: cfro: T1 objn:87600 col#:4 dfro:T2 dcol#:4
JE: cfro: T2 objn:87599 col#:4 dfro:T1 dcol#:4
JE: cfro: T2 objn:87599 col#:4 dfro:T1 dcol#:4
JE: cfro: T2 objn:87599 col#:4 dfro:T1 dcol#:4
SQL:******* UNPARSED QUERY IS *******
SELECT SUM("T1"."OBJECT_ID") "SUM(T1.OBJECT_ID)",SUM("T2"."OBJECT_ID") "SUM(T2.OBJECT_ID)" FROM "DAYU"."T1" "T1","DAYU"."T2" "T2" WHERE "T1"."OBJECT_ID"="T2"."OBJECT_ID" AND "T1"."OBJECT_ID"=10000 AND "T2"."OBJECT_ID"=10000
Query block SEL$1 (#0) unchanged
query block SEL$1 (#0) unchanged
Considering Query Transformations on query block SEL$1 (#0)
**************************
Query transformations (QT)
**************************
JF: Checking validity of join factorization for query block SEL$1 (#0)
JF: Bypassed: not a UNION or UNION-ALL query block.
ST: not valid since star transformation parameter is FALSE
TE: Checking validity of table expansion for query block SEL$1 (#0)
TE: Bypassed: No partitioned table in query block.
CBQT bypassed for query block SEL$1 (#0): no complex view, sub-queries or UNION (ALL) queries.
CBQT: Validity checks failed for 6hp0811hnj1zs.
CSE: Considering common sub-expression elimination in query block SEL$1 (#0)
*************************
Common Subexpression elimination (CSE)
*************************
CSE: CSE not performed on query block SEL$1 (#0).
SU: Considering subquery unnesting in query block SEL$1 (#0)
********************
Subquery Unnest (SU)
********************
SJC: Considering set-join conversion in query block SEL$1 (#0)
*************************
Set-Join Conversion (SJC)
*************************
SJC: not performed
JE: Considering Join Elimination on query block SEL$1 (#0)
*************************
Join Elimination (JE)
*************************
SQL:******* UNPARSED QUERY IS *******
SELECT SUM("T1"."OBJECT_ID") "SUM(T1.OBJECT_ID)",SUM("T2"."OBJECT_ID") "SUM(T2.OBJECT_ID)" FROM "DAYU"."T1" "T1","DAYU"."T2" "T2" WHERE "T1"."OBJECT_ID"="T2"."OBJECT_ID" AND "T1"."OBJECT_ID"=10000 AND "T2"."OBJECT_ID"=10000
JE: cfro: T1 objn:87600 col#:4 dfro:T2 dcol#:4
JE: cfro: T1 objn:87600 col#:4 dfro:T2 dcol#:4
JE: cfro: T1 objn:87600 col#:4 dfro:T2 dcol#:4
JE: cfro: T2 objn:87599 col#:4 dfro:T1 dcol#:4
JE: cfro: T2 objn:87599 col#:4 dfro:T1 dcol#:4
JE: cfro: T2 objn:87599 col#:4 dfro:T1 dcol#:4
SQL:******* UNPARSED QUERY IS *******
SELECT SUM("T1"."OBJECT_ID") "SUM(T1.OBJECT_ID)",SUM("T2"."OBJECT_ID") "SUM(T2.OBJECT_ID)" FROM "DAYU"."T1" "T1","DAYU"."T2" "T2" WHERE "T1"."OBJECT_ID"="T2"."OBJECT_ID" AND "T1"."OBJECT_ID"=10000 AND "T2"."OBJECT_ID"=10000
Query block SEL$1 (#0) unchanged
PM: Considering predicate move-around in query block SEL$1 (#0)
**************************
Predicate Move-Around (PM)
**************************
PM: PM bypassed: Outer query contains no views.
PM: PM bypassed: Outer query contains no views.
query block SEL$1 (#0) unchanged
FPD: Considering simple filter push in query block SEL$1 (#0)
"T1"."OBJECT_ID"="T2"."OBJECT_ID" AND "T1"."OBJECT_ID"=10000 AND "T2"."OBJECT_ID"=10000
try to generate transitive predicate from check constraints for query block SEL$1 (#0)
finally: "T1"."OBJECT_ID"="T2"."OBJECT_ID" AND "T1"."OBJECT_ID"=10000 AND "T2"."OBJECT_ID"=10000
apadrv-start sqlid=7517642510073104376
:
call(in-use=2808, alloc=16344), compile(in-use=66208, alloc=68488), execution(in-use=96688, alloc=97560)
*******************************************
Peeked values of the binds in SQL statement
*******************************************
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT SUM("T1"."OBJECT_ID") "SUM(T1.OBJECT_ID)",SUM("T2"."OBJECT_ID") "SUM(T2.OBJECT_ID)" FROM "DAYU"."T1" "T1","DAYU"."T2" "T2" WHERE "T1"."OBJECT_ID"="T2"."OBJECT_ID" AND "T1"."OBJECT_ID"=10000 AND "T2"."OBJECT_ID"=10000
kkoqbc: optimizing query block SEL$1 (#0)
:
call(in-use=2960, alloc=16344), compile(in-use=67512, alloc=68488), execution(in-use=96688, alloc=97560)
kkoqbc-subheap (create addr=0x7fcea1f56468)
****************
QUERY BLOCK TEXT
****************
select sum(t1.object_id),sum(t2.object_id)
from t1,t2
where t1.object_id=t2.object_id
and t1.object_id=10000
and t2.object_id=10000
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (optimizer): qb_name=SEL$1 nbfros=2 flg=0
fro(0): flg=0 objn=87599 hint_alias="T1"@"SEL$1"
fro(1): flg=0 objn=87600 hint_alias="T2"@"SEL$1"
-----------------------------
SYSTEM STATISTICS INFORMATION
-----------------------------
Using NOWORKLOAD Stats
CPUSPEEDNW: 1322 millions instructions/sec (default is 100)
IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
IOSEEKTIM: 10 milliseconds (default is 10)
MBRC: NO VALUE blocks (default is 8)
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: T2 Alias: T2
#Rows: 86309 #Blks: 1261 AvgRowLen: 98.00 ChainCnt: 0.00
Column (#4): OBJECT_ID(
AvgLen: 5 NDV: 86309 Nulls: 0 Density: 0.000012 Min: 2 Max: 87600
Index Stats::
Index: T2_OBJECT_ID Col#: 4
LVLS: 1 #LB: 191 #DK: 86309 LB/K: 1.00 DB/K: 1.00 CLUF: 1313.00
***********************
Table Stats::
Table: T1 Alias: T1
#Rows: 86310 #Blks: 1261 AvgRowLen: 96.00 ChainCnt: 0.00
Column (#4): OBJECT_ID(
AvgLen: 3 NDV: 9 Nulls: 0 Density: 0.041173 Min: 1 Max: 80000
Histogram: Freq #Bkts: 9 UncompBkts: 5489 EndPtVals: 9
Index Stats::
Index: T1_OBJECT_ID Col#: 4
LVLS: 1 #LB: 169 #DK: 9 LB/K: 18.00 DB/K: 137.00 CLUF: 1233.00
Access path analysis for T1
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for T1[T1]
Table: T1 Alias: T1
Card: Original: 86310.000000 Rounded: 10221 Computed: 10220.71 Non Adjusted: 10220.71
Access Path: TableScan
Cost: 344.98 Resp: 344.98 Degree: 0
Cost_io: 343.00 Cost_cpu: 31420736
Resp_io: 343.00 Resp_cpu: 31420736
Access Path: index (index (FFS))
Index: T1_OBJECT_ID
resc_io: 48.00 resc_cpu: 15876223
ix_sel: 0.000000 ix_sel_with_filters: 1.000000
Access Path: index (FFS)
Cost: 49.00 Resp: 49.00 Degree: 1
Cost_io: 48.00 Cost_cpu: 15876223
Resp_io: 48.00 Resp_cpu: 15876223
Access Path: index (AllEqRange)
Index: T1_OBJECT_ID
resc_io: 21.00 resc_cpu: 2194600
ix_sel: 0.118419 ix_sel_with_filters: 0.118419
Cost: 21.14 Resp: 21.14 Degree: 1
Best:: AccessPath: IndexRange
Index: T1_OBJECT_ID
Cost: 21.14 Degree: 1 Resp: 21.14 Card: 10220.71 Bytes: 0
Access path analysis for T2
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for T2[T2]
Table: T2 Alias: T2
Card: Original: 86309.000000 Rounded: 1 Computed: 1.00 Non Adjusted: 1.00
Access Path: TableScan
Cost: 344.98 Resp: 344.98 Degree: 0
Cost_io: 343.00 Cost_cpu: 31420476
Resp_io: 343.00 Resp_cpu: 31420476
Access Path: index (index (FFS))
Index: T2_OBJECT_ID
resc_io: 53.00 resc_cpu: 16032725
ix_sel: 0.000000 ix_sel_with_filters: 1.000000
Access Path: index (FFS)
Cost: 54.01 Resp: 54.01 Degree: 1
Cost_io: 53.00 Cost_cpu: 16032725
Resp_io: 53.00 Resp_cpu: 16032725
Access Path: index (AllEqRange)
Index: T2_OBJECT_ID
resc_io: 1.00 resc_cpu: 8171
ix_sel: 0.000012 ix_sel_with_filters: 0.000012
Cost: 1.00 Resp: 1.00 Degree: 1
Best:: AccessPath: IndexRange
Index: T2_OBJECT_ID
Cost: 1.00 Degree: 1 Resp: 1.00 Card: 1.00 Bytes: 0
***************************************
OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***************************************
Considering cardinality-based initial join order.
Permutations for Starting Table :0
Join order[1]: T2[T2]#0 T1[T1]#1
***************
Now joining: T1[T1]#1
***************
NL Join
Outer table: Card: 1.00 Cost: 1.00 Resp: 1.00 Degree: 1 Bytes: 5
Access path analysis for T1
Inner table: T1 Alias: T1
Access Path: TableScan
NL Join: Cost: 345.98 Resp: 345.98 Degree: 1
Cost_io: 344.00 Cost_cpu: 31428907
Resp_io: 344.00 Resp_cpu: 31428907
Access Path: index (index (FFS))
Index: T1_OBJECT_ID
resc_io: 48.00 resc_cpu: 15876223
ix_sel: 0.000000 ix_sel_with_filters: 1.000000
Inner table: T1 Alias: T1
Access Path: index (FFS)
NL Join: Cost: 50.00 Resp: 50.00 Degree: 1
Cost_io: 49.00 Cost_cpu: 15884395
Resp_io: 49.00 Resp_cpu: 15884395
kkofmx: index filter:"T1"."OBJECT_ID"="T2"."OBJECT_ID"
Access Path: index (AllEqJoinGuess)
Index: T1_OBJECT_ID
resc_io: 18.00 resc_cpu: 2173236
ix_sel: 0.118419 ix_sel_with_filters: 0.118419
***** Logdef predicate Adjustment ******
Final IO cst 0.00 , CPU cst 0.00
***** End Logdef Adjustment ******
NL Join : Cost: 19.14 Resp: 19.14 Degree: 1
Cost_io: 19.00 Cost_cpu: 2181407
Resp_io: 19.00 Resp_cpu: 2181407
Best NL cost: 19.14
resc: 19.14 resc_io: 19.00 resc_cpu: 2181407
resp: 19.14 resp_io: 19.00 resc_cpu: 2181407
Join Card: 10220.714156 = outer (1.000000) * inner (10220.714156) * sel (1.000000)
Join Card - Rounded: 10221 Computed: 10220.71
Outer table: T2 Alias: T2
resc: 1.00 card 1.00 bytes: 5 deg: 1 resp: 1.00
Inner table: T1 Alias: T1
resc: 21.14 card: 10220.71 bytes: 3 deg: 1 resp: 21.14
using dmeth: 2 #groups: 1
SORT ressource Sort statistics
Sort width: 329 Area size: 288768 Max Area size: 57880576
Degree: 1
Blocks to Sort: 18 Row size: 14 Total Rows: 10221
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 21997032
Total Temp space used: 0
SM join: Resc: 23.53 Resp: 23.53 [multiMatchCost=0.00]
SM Join
SM cost: 23.53
resc: 23.53 resc_io: 22.00 resc_cpu: 24199803
resp: 23.53 resp_io: 22.00 resp_cpu: 24199803
Outer table: T2 Alias: T2
resc: 1.00 card 1.00 bytes: 5 deg: 1 resp: 1.00
Inner table: T1 Alias: T1
resc: 21.14 card: 10220.71 bytes: 3 deg: 1 resp: 21.14
using dmeth: 2 #groups: 1
Cost per ptn: 0.10 #ptns: 1
hash_area: 124 (max=14131) buildfrag: 1 probefrag: 19 ppasses: 1
Hash join: Resc: 22.24 Resp: 22.24 [multiMatchCost=0.00]
HA Join
HA cost: 22.24
resc: 22.24 resc_io: 22.00 resc_cpu: 3825022
resp: 22.24 resp_io: 22.00 resp_cpu: 3825022
Best:: JoinMethod: NestedLoop
Cost: 19.14 Degree: 1 Resp: 19.14 Card: 10220.71 Bytes: 8
***********************
Best so far: Table#: 0 cost: 1.0005 card: 1.0000 bytes: 5
Table#: 1 cost: 19.1375 card: 10220.7142 bytes: 81768
***********************
Join order[2]: T1[T1]#1 T2[T2]#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
*********************************
Consider using bloom filter between T2[T2] and T1[T1] with ??
kkoBloomFilter: join ndv:0 reduction:1.000000 (limit:0.500000) rejected because not a hash join
Enumerating distribution method (advanced)
--- Distribution method for
join between T2[T2](serial) and T1[T1](serial); jm = 12; right side access path = IndexRange
---- NLJ default -> BROADCAST-LEFT
(newjo-save) [0 1 ]
Trying or-Expansion on query block SEL$1 (#0)
Transfer Optimizer annotations for query block SEL$1 (#0)
id=0 frofkks[i] (index start key) predicate="T2"."OBJECT_ID"=10000
id=0 frofkke[i] (index stop key) predicate="T2"."OBJECT_ID"=10000
id=0 frofkks[i] (index start key) predicate="T1"."OBJECT_ID"=10000
id=0 frofkke[i] (index stop key) predicate="T1"."OBJECT_ID"=10000
id=0 froiand (index only filter) predicate="T1"."OBJECT_ID"="T2"."OBJECT_ID"
Final cost for query block SEL$1 (#0) - All Rows Plan:
Best join order: 1
Cost: 19.1375 Degree: 1 Card: 10221.0000 Bytes: 81768
Resc: 19.1375 Resc_io: 19.0000 Resc_cpu: 2181407
Resp: 19.1375 Resp_io: 19.0000 Resc_cpu: 2181407
kkoqbc-subheap (delete addr=0x7fcea1f56468, in-use=41600, alloc=49272)
kkoqbc-end:
:
call(in-use=12576, alloc=82128), compile(in-use=73392, alloc=76520), execution(in-use=104800, alloc=105672)
kkoqbc: finish optimizing query block SEL$1 (#0)
apadrv-end
:
call(in-use=12576, alloc=82128), compile(in-use=74376, alloc=76520), execution(in-use=104800, alloc=105672)
Starting SQL statement dump
user_id=85 user_name=DAYU module=SQL*Plus action=
sql_id=6hp0811hnj1zs plan_hash_value=1955305233 problem_type=3
----- Current SQL Statement for this session (sql_id=6hp0811hnj1zs) -----
explain plan for select sum(t1.object_id),sum(t2.object_id)
from t1,t2
where t1.object_id=t2.object_id
and t1.object_id=10000
and t2.object_id=10000
sql_text_length=149
sql=explain plan for select sum(t1.object_id),sum(t2.object_id)
from t1,t2
where t1.object_id=t2.object_id
and t1.object_id=10000
and t2.object_id=10000
----- Explain Plan Dump -----
----- Plan Table -----
============
Plan Table
============
------------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
------------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 19 | |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
| 2 | NESTED LOOPS | | 10K | 80K | 19 | 00:00:01 |
| 3 | INDEX RANGE SCAN | T2_OBJECT_ID| 1 | 5 | 1 | 00:00:01 |
| 4 | INDEX RANGE SCAN | T1_OBJECT_ID| 10K | 30K | 18 | 00:00:01 |
------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
3 - access("T2"."OBJECT_ID"=10000)
4 - access("T1"."OBJECT_ID"=10000)
4 - filter("T1"."OBJECT_ID"="T2"."OBJECT_ID")
Content of other_xml column
===========================
db_version : 11.2.0.4
parse_schema : DAYU
plan_hash : 1955305233
plan_hash_2 : 203326528
Outline Data:
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX(@"SEL$1" "T2"@"SEL$1" ("T2"."OBJECT_ID"))
INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."OBJECT_ID"))
LEADING(@"SEL$1" "T2"@"SEL$1" "T1"@"SEL$1")
USE_NL(@"SEL$1" "T1"@"SEL$1")
END_OUTLINE_DATA
*/
Optimizer state dump:
Compilation Environment Dump
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 = 11.2.0.4
_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 = 8
_optimizer_max_permutations = 2000
pga_aggregate_target = 282624 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 = 282 KB
_smm_max_size_static = 56524 KB
_smm_px_max_size_static = 141312 KB
_cpu_to_io = 0
_optimizer_undo_cost_change = 11.2.0.4
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
_optimizer_null_aware_antijoin = true
_optimizer_extend_jppd_view_types = 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
_px_minus_intersect = true
_fix_control_key = 0
_force_slave_mapping_intra_part_loads = false
_force_tmp_segment_loads = false
_query_mmvrewrite_maxpreds = 10
_query_mmvrewrite_maxintervals = 5
_query_mmvrewrite_maxinlists = 5
_query_mmvrewrite_maxdmaps = 10
_query_mmvrewrite_maxcmaps = 20
_query_mmvrewrite_maxregperm = 512
_query_mmvrewrite_maxqryinlistvals = 500
_disable_parallel_conventional_load = false
_trace_virtual_columns = false
_replace_virtual_columns = true
_virtual_column_overload_allowed = true
_kdt_buffering = true
_first_k_rows_dynamic_proration = true
_optimizer_sortmerge_join_inequality = true
_optimizer_aw_stats_enabled = true
_bloom_pruning_enabled = true
result_cache_mode = MANUAL
_px_ual_serial_input = true
_optimizer_skip_scan_guess = false
_enable_row_shipping = true
_row_shipping_threshold = 80
_row_shipping_explain = false
transaction_isolation_level = read_commited
_optimizer_distinct_elimination = true
_optimizer_multi_level_push_pred = true
_optimizer_group_by_placement = true
_optimizer_rownum_bind_default = 10
_enable_query_rewrite_on_remote_objs = true
_optimizer_extended_cursor_sharing_rel = simple
_optimizer_adaptive_cursor_sharing = true
_direct_path_insert_features = 0
_optimizer_improve_selectivity = true
optimizer_use_pending_statistics = false
_optimizer_enable_density_improvements = true
_optimizer_aw_join_push_enabled = true
_optimizer_connect_by_combine_sw = true
_enable_pmo_ctas = 0
_optimizer_native_full_outer_join = force
_bloom_predicate_enabled = true
_optimizer_enable_extended_stats = true
_is_lock_table_for_ddl_wait_lock = 0
_pivot_implementation_method = choose
optimizer_capture_sql_plan_baselines = false
optimizer_use_sql_plan_baselines = true
_optimizer_star_trans_min_cost = 0
_optimizer_star_trans_min_ratio = 0
_with_subquery = OPTIMIZER
_optimizer_fkr_index_cost_bias = 10
_optimizer_use_subheap = true
parallel_degree_policy = manual
parallel_degree = 0
parallel_min_time_threshold = 10
_parallel_time_unit = 10
_optimizer_or_expansion_subheap = true
_optimizer_free_transformation_heap = true
_optimizer_reuse_cost_annotations = true
_result_cache_auto_size_threshold = 100
_result_cache_auto_time_threshold = 1000
_optimizer_nested_rollup_for_gset = 100
_nlj_batching_enabled = 1
parallel_query_default_dop = 0
is_recur_flags = 0
optimizer_use_invisible_indexes = false
flashback_data_archive_internal_cursor = 0
_optimizer_extended_stats_usage_control = 192
_parallel_syspls_obey_force = true
cell_offload_processing = true
_rdbms_internal_fplib_enabled = false
db_file_multiblock_read_count = 83
_bloom_folding_enabled = true
_mv_generalized_oj_refresh_opt = true
cell_offload_compaction = ADAPTIVE
cell_offload_plan_display = AUTO
_bloom_predicate_pushdown_to_storage = true
_bloom_vector_elements = 0
_bloom_pushing_max = 512
parallel_degree_limit = 65535
parallel_force_local = false
parallel_max_degree = 4
total_cpu_count = 2
_optimizer_coalesce_subqueries = true
_optimizer_fast_pred_transitivity = true
_optimizer_fast_access_pred_analysis = true
_optimizer_unnest_disjunctive_subq = true
_optimizer_unnest_corr_set_subq = true
_optimizer_distinct_agg_transform = true
_aggregation_optimization_settings = 0
_optimizer_connect_by_elim_dups = true
_optimizer_eliminate_filtering_join = true
_connect_by_use_union_all = true
dst_upgrade_insert_conv = true
advanced_queuing_internal_cursor = 0
_optimizer_unnest_all_subqueries = true
parallel_autodop = 0
parallel_ddldml = 0
_parallel_cluster_cache_policy = adaptive
_parallel_scalability = 50
iot_internal_cursor = 0
_optimizer_instance_count = 0
_optimizer_connect_by_cb_whr_only = false
_suppress_scn_chk_for_cqn = nosuppress_1466
_optimizer_join_factorization = true
_optimizer_use_cbqt_star_transformation = true
_optimizer_table_expansion = true
_and_pruning_enabled = true
_deferred_constant_folding_mode = DEFAULT
_optimizer_distinct_placement = true
partition_pruning_internal_cursor = 0
parallel_hinted = none
_sql_compatibility = 0
_optimizer_use_feedback = true
_optimizer_try_st_before_jppd = true
_dml_frequency_tracking = false
_optimizer_interleave_jppd = true
kkb_drop_empty_segments = 0
_px_partition_scan_enabled = true
_px_partition_scan_threshold = 64
_optimizer_false_filter_pred_pullup = true
_bloom_minmax_enabled = true
only_move_row = 0
_optimizer_enable_table_lookup_by_nl = true
deferred_segment_creation = true
_optimizer_filter_pushdown = true
_allow_level_without_connect_by = false
_max_rwgs_groupings = 8192
_optimizer_outer_join_to_inner = true
_optimizer_full_outer_join_to_outer = true
_px_numa_support_enabled = true
total_processor_group_count = 1
_bloom_sm_enabled = false
_bloom_serial_filter = on
_parallel_degree_level = 100
_optimizer_ads_max_table_count = 0
_optimizer_ads_time_limit = 0
_optimizer_ads_use_result_cache = true
parallel_dblink = 0
_optimizer_cluster_by_rowid = false
_optimizer_cluster_by_rowid_control = 3
Bug Fix Control Environment
fix 3834770 = 1
。。。
fix 16554552 = enabled
Query Block Registry:
SEL$1 0x86e1de58 (PARSER) [FINAL]
:
call(in-use=21496, alloc=82128), compile(in-use=110808, alloc=210392), execution(in-use=245848, alloc=248976)
End of Optimizer State Dump
Dumping Hints
=============
====================== END SQL Statement Dump ======================
