我们在查看一条sql语句的执行计划时，只看到了CBO最终告诉我们的执行计划结果，但是却不知道CBO为什么要这么做。特别是当执行计划明显失真时，我们一定非常想搞清楚到底是什么地方导致CBO作出了这样一个错误的执行计划。10053事件给我们提供了一种这样的方式，它允许我们深入到CBO的内部，去看看CBO是如何工作的，它究竟是按照什么样的依据得出最终的执行计划的。
SQL> conn u1/u1
已连接。
SQL> create table t as select rownum x from dba_objects;
表已创建。
SQL> create index ind_t on t(x);
索引已创建。
SQL> exec dbms_stats.gather_table_stats(user,'t',cascade=>true);
PL/SQL 过程已成功完成。
SQL> create table t1 as select x,'T1' name from t where x < 10000;
表已创建。
SQL> alter session set events '10053 trace name context forever,level 1';
会话已更改。
SQL> explain plan for select t1.* from t,t1 where t.x < 100 and t.x = t1.x;
已解释。
SQL> alter session set events '10053 trace name context off';
会话已更改。
Trace file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_3127.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1
System name: Linux
Node name: linux
Release: 2.6.18-164.el5
Version: #1 SMP Tue Aug 18 15:51:54 EDT 2009
Machine: i686
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 27
Unix process pid: 3127, image: oracle@linux (TNS V1-V3)
*** 2011-12-17 14:38:30.574
*** SESSION ID:(36.165) 2011-12-17 14:38:30.574
*** CLIENT ID:() 2011-12-17 14:38:30.574
*** SERVICE NAME:(SYS$USERS) 2011-12-17 14:38:30.574
*** MODULE NAME:(SQL*Plus) 2011-12-17 14:38:30.574
*** ACTION NAME:() 2011-12-17 14:38:30.574
Registered qb: SEL$1 0x2739f8f8 (PARSER)
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (): qb_name=SEL$1 nbfros=2 flg=0
fro(0): flg=4 objn=73325 hint_alias="T"@"SEL$1"
fro(1): flg=4 objn=73327 hint_alias="T1"@"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) --从这里开始进入了10053的trace信息部分。
**************************
OPTIMIZER INFORMATION
******************************************
----- Current SQL Statement for this session (sql_id=fr9667v88dwbt) -----
explain plan for select t1.* from t,t1 where t.x < 100 and t.x = t1.x --这里可以看见我们执行的sql语句。
*******************************************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
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.1
_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 = 8
_optimizer_max_permutations = 2000
pga_aggregate_target = 76800 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 = 15360 KB
_smm_px_max_size = 38400 KB
_cpu_to_io = 0
_optimizer_undo_cost_change = 11.2.0.1
parallel_query_mode = enabled
parallel_dml_mode = disabled
parallel_ddl_mode = enabled
optimizer_mode = all_rows
sqlstat_enabled = false
_optimizer_percent_parallel = 101
_always_anti_join = choose
_always_semi_join = choose
_optimizer_mode_force = true
_partition_view_enabled = true
_always_star_transformation = false
_query_rewrite_or_error = false
_hash_join_enabled = true
cursor_sharing = exact
_b_tree_bitmap_plans = true
star_transformation_enabled = false
_optimizer_cost_model = choose
_new_sort_cost_estimate = true
_complex_view_merging = true
_unnest_subquery = true
_eliminate_common_subexpr = true
_pred_move_around = true
_convert_set_to_join = false
_push_join_predicate = true
_push_join_union_view = true
_fast_full_scan_enabled = true
_optim_enhance_nnull_detection = true
_parallel_broadcast_enabled = true
_px_broadcast_fudge_factor = 100
_ordered_nested_loop = true
_no_or_expansion = false
optimizer_index_cost_adj = 100
optimizer_index_caching = 0
_system_index_caching = 0
_disable_datalayer_sampling = false
query_rewrite_enabled = true
query_rewrite_integrity = enforced
_query_cost_rewrite = true
_query_rewrite_2 = true
_query_rewrite_1 = true
_query_rewrite_expression = true
_query_rewrite_jgmigrate = true
_query_rewrite_fpc = true
_query_rewrite_drj = true
_full_pwise_join_enabled = true
_partial_pwise_join_enabled = true
_left_nested_loops_random = true
_improved_row_length_enabled = true
_index_join_enabled = true
_enable_type_dep_selectivity = true
_improved_outerjoin_card = true
_optimizer_adjust_for_nulls = true
_optimizer_degree = 0
_use_column_stats_for_function = true
_subquery_pruning_enabled = true
_subquery_pruning_mv_enabled = false
_or_expand_nvl_predicate = true
_like_with_bind_as_equality = false
_table_scan_cost_plus_one = true
_cost_equality_semi_join = true
_default_non_equality_sel_check = true
_new_initial_join_orders = true
_oneside_colstat_for_equijoins = true
_optim_peek_user_binds = true
_minimal_stats_aggregation = true
_force_temptables_for_gsets = false
workarea_size_policy = auto
_smm_auto_cost_enabled = true
_gs_anti_semi_join_allowed = true
_optim_new_default_join_sel = true
optimizer_dynamic_sampling = 2
_pre_rewrite_push_pred = true
_optimizer_new_join_card_computation = true
_union_rewrite_for_gs = yes_gset_mvs
_generalized_pruning_enabled = true
_optim_adjust_for_part_skews = true
_force_datefold_trunc = false
statistics_level = typical
_optimizer_system_stats_usage = true
skip_unusable_indexes = true
_remove_aggr_subquery = true
_optimizer_push_down_distinct = 0
_dml_monitoring_enabled = true
_optimizer_undo_changes = false
_predicate_elimination_enabled = true
_nested_loop_fudge = 100
_project_view_columns = true
_local_communication_costing_enabled = true
_local_communication_ratio = 50
_query_rewrite_vop_cleanup = true
_slave_mapping_enabled = true
_optimizer_cost_based_transformation = linear
_optimizer_mjc_enabled = true
_right_outer_hash_enable = true
_spr_push_pred_refspr = true
_optimizer_cache_stats = false
_optimizer_cbqt_factor = 50
_optimizer_squ_bottomup = true
_fic_area_size = 131072
_optimizer_skip_scan_enabled = true
_optimizer_cost_filter_pred = false
_optimizer_sortmerge_join_enabled = true
_optimizer_join_sel_sanity_check = true
_mmv_query_rewrite_enabled = true
_bt_mmv_query_rewrite_enabled = true
_add_stale_mv_to_dependency_list = true
_distinct_view_unnesting = false
_optimizer_dim_subq_join_sel = true
_optimizer_disable_strans_sanity_checks = 0
_optimizer_compute_index_stats = true
_push_join_union_view2 = true
_optimizer_ignore_hints = false
_optimizer_random_plan = 0
_query_rewrite_setopgrw_enable = true
_optimizer_correct_sq_selectivity = true
_disable_function_based_index = false
_optimizer_join_order_control = 3
_optimizer_cartesian_enabled = true
_optimizer_starplan_enabled = true
_extended_pruning_enabled = true
_optimizer_push_pred_cost_based = true
_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_maxmergedcmaps = 50
_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 = 224
_parallel_syspls_obey_force = true
cell_offload_processing = true
_rdbms_internal_fplib_enabled = false
db_file_multiblock_read_count = 40
_bloom_folding_enabled = true
_mv_generalized_oj_refresh_opt = true
cell_offload_compaction = ADAPTIVE
parallel_degree_limit = 65535
parallel_force_local = false
parallel_max_degree = 2
total_cpu_count = 1
cell_offload_plan_display = AUTO
_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
_bloom_predicate_pushdown_to_storage = true
_bloom_vector_elements = 0
_bloom_pushing_max = 524288
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
Bug Fix Control Environment
fix 3834770 = 1
fix 3746511 = enabled
fix 4519016 = enabled
fix 3118776 = 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 fr9667v88dwbt.
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 "T1"."X" "X","T1"."NAME" "NAME" FROM "U1"."T" "T","U1"."T1" "T1" WHERE "T"."X"<100 AND "T"."X"="T1"."X"
SQL:******* UNPARSED QUERY IS *******
SELECT "T1"."X" "X","T1"."NAME" "NAME" FROM "U1"."T" "T","U1"."T1" "T1" WHERE "T"."X"<100 AND "T"."X"="T1"."X"
Query block SEL$1 (#0) unchanged
CVM: Considering view merge in query block SEL$1 (#0)
JE: Considering Join Elimination on query block SEL$1 (#0)
*************************
Join Elimination (JE)
*************************
SQL:******* UNPARSED QUERY IS *******
SELECT "T1"."X" "X","T1"."NAME" "NAME" FROM "U1"."T" "T","U1"."T1" "T1" WHERE "T"."X"<100 AND "T"."X"="T1"."X"
SQL:******* UNPARSED QUERY IS *******
SELECT "T1"."X" "X","T1"."NAME" "NAME" FROM "U1"."T" "T","U1"."T1" "T1" WHERE "T"."X"<100 AND "T"."X"="T1"."X"
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 fr9667v88dwbt.
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 "T1"."X" "X","T1"."NAME" "NAME" FROM "U1"."T" "T","U1"."T1" "T1" WHERE "T"."X"<100 AND "T"."X"="T1"."X"
SQL:******* UNPARSED QUERY IS *******
SELECT "T1"."X" "X","T1"."NAME" "NAME" FROM "U1"."T" "T","U1"."T1" "T1" WHERE "T"."X"<100 AND "T"."X"="T1"."X"
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)
"T"."X"<100 AND "T"."X"="T1"."X"
try to generate transitive predicate from check constraints for query block SEL$1 (#0)
finally: "T"."X"<100 AND "T"."X"="T1"."X" AND "T1"."X"<100 --注意这里
FPD: transitive predicates are generated in query block SEL$1 (#0)
"T"."X"<100 AND "T"."X"="T1"."X" AND "T1"."X"<100
apadrv-start sqlid=16979914468182389113
:
call(in-use=1248, alloc=16360), compile(in-use=60584, alloc=60948), execution(in-use=77100, alloc=77552)"T"."X"<100 AND "T"."X"="T1"."X"
"T"."X"<100 AND "T"."X"="T1"."X" AND "T1"."X"<100*******************************************
Peeked values of the binds in SQL statement
*******************************************
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "T1"."X" "X","T1"."NAME" "NAME" FROM "U1"."T" "T","U1"."T1" "T1" WHERE "T"."X"<100 AND "T"."X"="T1"."X" AND "T1"."X"<100 --这里是解析完后最终执行的sql
kkoqbc: optimizing query block SEL$1 (#0)
:
call(in-use=1364, alloc=16360), compile(in-use=61404, alloc=65004), execution(in-use=81176, alloc=81628)
kkoqbc-subheap (create addr=0x54fb0c)
****************
QUERY BLOCK TEXT
****************
select t1.* from t,t1 where t.x < 100 and t.x = t1.x
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (optimizer): qb_name=SEL$1 nbfros=2 flg=0
fro(0): flg=0 objn=73325 hint_alias="T"@"SEL$1"
fro(1): flg=0 objn=73327 hint_alias="T1"@"SEL$1"
-----------------------------
SYSTEM STATISTICS INFORMATION
-----------------------------
Using NOWORKLOAD Stats
CPUSPEEDNW: 3462 millions instructions/sec (default is 100)
IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
IOSEEKTIM: 10 milliseconds (default is 10)
MBRC: -1 blocks (default is 8)
***************************************BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: T1 Alias: T1 (NOT ANALYZED)
#Rows: 1716 #Blks: 21 AvgRowLen: 100.00
***********************
Table Stats::
Table: T Alias: T
#Rows: 71913 #Blks: 120 AvgRowLen: 5.00
Index Stats::
Index: IND_T Col#: 1
LVLS: 1 #LB: 159 #DK: 71913 LB/K: 1.00 DB/K: 1.00 CLUF: 110.00
Access path analysis for T
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for T[T]
Table: T Alias: T
Card: Original: 71913.000000 Rounded: 99 Computed: 99.00 Non Adjusted: 99.00
Access Path: TableScan
Cost: 34.37 Resp: 34.37 Degree: 0
Cost_io: 34.00 Cost_cpu: 15237173
Resp_io: 34.00 Resp_cpu: 15237173
Access Path: index (index (FFS)) --Index fast full scan
Index: IND_T
resc_io: 45.00 resc_cpu: 13357519
ix_sel: 0.000000 ix_sel_with_filters: 1.000000
Access Path: index (FFS)
Cost: 45.32 Resp: 45.32 Degree: 1
Cost_io: 45.00 Cost_cpu: 13357519
Resp_io: 45.00 Resp_cpu: 13357519
Access Path: index (IndexOnly)
Index: IND_T
resc_io: 2.00 resc_cpu: 34243
ix_sel: 0.001377 ix_sel_with_filters: 0.001377
Cost: 2.00 Resp: 2.00 Degree: 1
Best:: AccessPath: IndexRange --最后结论，选择单独访问索引的方式。
Index: IND_T
Cost: 2.00 Degree: 1 Resp: 2.00 Card: 99.00 Bytes: 0Card: Original: 71913.000000 源记录数，也就是操作数据的输入记录数，在这里就是表的实际记录数71913。
Rounded: 99 输出的记录树，CBO计算出通过条件过滤，预计得到的记录数。我们知道表T安装条件小于100的记录数为99。这里估算出得也是99。
通过这一部分信息我们可以看到对于表T，CBO认为可能使用下面3中方式来访问数据。
Access Path: TableScan Cost: 34.37
Access Path: index (FFS) Cost: 45.32
Access Path: index (IndexOnly) Cost: 2.00
很显然，单独访问索引的方式是代价最低的，所以CBO得出结论，对于表T上的查询，选择单独访问索引的方式。
Access path analysis for T1
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for T1[T1]
*** 2011-12-17 14:38:30.600
** Performing dynamic sampling initial checks. **
** Dynamic sampling initial checks returning TRUE (level = 2).
** Dynamic sampling updated table stats.: blocks=21
*** 2011-12-17 14:38:30.600
** Generated dynamic sampling query:
query text :
SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),0), NVL(SUM(C2),0), COUNT(DISTINCT C3), NVL(SUM(CASE WHEN C3 IS NULL THEN 1 ELSE 0 END),0) FROM (SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("T1") FULL("T1") NO_PARALLEL_INDEX("T1") */ 1 AS C1, CASE WHEN "T1"."X"<100 THEN 1 ELSE 0 END AS C2, "T1"."X" AS C3 FROM "T1" "T1") SAMPLESUB
*** 2011-12-17 14:38:30.660
** Executed dynamic sampling query: --因为T1表没有执行统计分析，可以看见这里采用了动态采样，level 2.
level : 2
sample pct. : 100.000000
actual sample size : 9999
filtered sample card. : 99
orig. card. : 1716
block cnt. table stat. : 21
block cnt. for sampling: 21
max. sample block cnt. : 64
sample block cnt. : 21
ndv C3 : 9999
scaled : 9999.00
nulls C4 : 0
scaled : 0.00
min. sel. est. : 0.05000000
** Dynamic sampling col. stats.:
Column (#1): X( Part#: 0
AvgLen: 22 NDV: 9999 Nulls: 0 Density: 0.000100
** Using dynamic sampling NULLs estimates.
** Using dynamic sampling NDV estimates.
Scaled NDVs using cardinality = 9999.
** Using dynamic sampling card. : 9999
** Dynamic sampling updated table card.
** Using single table dynamic sel. est. : 0.00990099
Table: T1 Alias: T1
Card: Original: 9999.000000 Rounded: 99 Computed: 99.00 Non Adjusted: 99.00 --这里CBO估算出的值也和实际值一样。
Access Path: TableScan --因为T1表没有索引，这里只有一种访问方式。
Cost: 7.05 Resp: 7.05 Degree: 0
Cost_io: 7.00 Cost_cpu: 2151330
Resp_io: 7.00 Resp_cpu: 2151330
Best:: AccessPath: TableScan
Cost: 7.05 Degree: 1 Resp: 7.05 Card: 99.00 Bytes: 0T关联T1
NESTED LOOPS JOIN
SORT MERGE JOIN
HASH JOIN
T1管理T
NESTED LOOPS JOIN
SORT MERGE JOIN
HASH JOINOPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***************************************
Considering cardinality-based initial join order.
Permutations for Starting Table :0
Join order[1]: T1[T1]#0 T[T]#1 --order[1]T1关联T
***************
Now joining: T[T]#1
***************
NL Join
Outer table: Card: 99.00 Cost: 7.05 Resp: 7.05 Degree: 1 Bytes: 17
Access path analysis for T
Inner table: T Alias: T
Access Path: TableScan
NL Join: Cost: 3262.36 Resp: 3262.36 Degree: 1
Cost_io: 3226.00 Cost_cpu: 1510631437
Resp_io: 3226.00 Resp_cpu: 1510631437
Access Path: index (index (FFS))
Index: IND_T
resc_io: 43.08 resc_cpu: 13357519
ix_sel: 0.000000 ix_sel_with_filters: 1.000000
Inner table: T Alias: T
Access Path: index (FFS)
NL Join: Cost: 4303.89 Resp: 4303.89 Degree: 1
Cost_io: 4272.00 Cost_cpu: 1324545707
Resp_io: 4272.00 Resp_cpu: 1324545707
kkofmx: index filter:"T"."X"<100
Access Path: index (AllEqJoinGuess)
Index: IND_T
resc_io: 1.00 resc_cpu: 8171
ix_sel: 0.000014 ix_sel_with_filters: 0.000000
***** Logdef predicate Adjustment ******
Final IO cst 0.00 , CPU cst 50.00
***** End Logdef Adjustment ******
NL Join : Cost: 106.07 Resp: 106.07 Degree: 1
Cost_io: 106.00 Cost_cpu: 2965253
Resp_io: 106.00 Resp_cpu: 2965253
Best NL cost: 106.07 --nested loops join方式最好的代价。
resc: 106.07 resc_io: 106.00 resc_cpu: 2965253 --串行
resp: 106.07 resp_io: 106.00 resc_cpu: 2965253 --并行
Join Card: 98.011363 = = outer (99.000000) * inner (99.001377) * sel (0.010000)
Join Card - Rounded: 98 Computed: 98.01
Outer table: T1 Alias: T1
resc: 7.05 card 99.00 bytes: 17 deg: 1 resp: 7.05
Inner table: T Alias: T
resc: 2.00 card: 99.00 bytes: 5 deg: 1 resp: 2.00
using dmeth: 2 #groups: 1
SORT ressource Sort statistics
Sort width: 88 Area size: 131072 Max Area size: 15728640
Degree: 1
Blocks to Sort: 1 Row size: 29 Total Rows: 99
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 41570781
Total Temp space used: 0
SORT ressource Sort statistics
Sort width: 88 Area size: 131072 Max Area size: 15728640
Degree: 1
Blocks to Sort: 1 Row size: 16 Total Rows: 99
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 41570781
Total Temp space used: 0
SM join: Resc: 11.05 Resp: 11.05 [multiMatchCost=0.00]
SM Join
SM cost: 11.05 --sort merge join的代价。
resc: 11.05 resc_io: 9.00 resc_cpu: 85327135 --串行
resp: 11.05 resp_io: 9.00 resp_cpu: 85327135 --并行
Outer table: T1 Alias: T1
resc: 7.05 card 99.00 bytes: 17 deg: 1 resp: 7.05
Inner table: T Alias: T
resc: 2.00 card: 99.00 bytes: 5 deg: 1 resp: 2.00
using dmeth: 2 #groups: 1
Cost per ptn: 0.50 #ptns: 1
hash_area: 124 (max=3840) buildfrag: 1 probefrag: 1 ppasses: 1
Hash join: Resc: 9.55 Resp: 9.55 [multiMatchCost=0.00]
HA Join
HA cost: 9.55 --hash join的代价。
resc: 9.55 resc_io: 9.00 resc_cpu: 22980929 --串行
resp: 9.55 resp_io: 9.00 resp_cpu: 22980929 --并行
Best:: JoinMethod: Hash --order[1]T1关联T hash join的代价最小，所以最后选择hash join。
Cost: 9.55 Degree: 1 Resp: 9.55 Card: 98.01 Bytes: 22
***********************
Best so far: Table#: 0 cost: 7.0518 card: 99.0000 bytes: 1683
Table#: 1 cost: 9.5532 card: 98.0114 bytes: 2156
***********************
Join order[2]: T[T]#1 T1[T1]#0 --order[2]T关联T1
***************
Now joining: T1[T1]#0
***************
NL Join
Outer table: Card: 99.00 Cost: 2.00 Resp: 2.00 Degree: 1 Bytes: 5
Access path analysis for T1
Inner table: T1 Alias: T1
Access Path: TableScan
NL Join: Cost: 572.13 Resp: 572.13 Degree: 1
Cost_io: 567.00 Cost_cpu: 213015937
Resp_io: 567.00 Resp_cpu: 213015937
Best NL cost: 572.13 --nested loops join方式最好的代价。
resc: 572.13 resc_io: 567.00 resc_cpu: 213015937 --串行
resp: 572.13 resp_io: 567.00 resc_cpu: 213015937 --并行
Join Card: 98.011363 = = outer (99.001377) * inner (99.000000) * sel (0.010000)
Join Card - Rounded: 98 Computed: 98.01
Outer table: T Alias: T
resc: 2.00 card 99.00 bytes: 5 deg: 1 resp: 2.00
Inner table: T1 Alias: T1
resc: 7.05 card: 99.00 bytes: 17 deg: 1 resp: 7.05
using dmeth: 2 #groups: 1
SORT ressource Sort statistics
Sort width: 88 Area size: 131072 Max Area size: 15728640
Degree: 1
Blocks to Sort: 1 Row size: 29 Total Rows: 99
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 41570781
Total Temp space used: 0
SM join: Resc: 10.05 Resp: 10.05 [multiMatchCost=0.00]
SM Join
SM cost: 10.05 --sort merge join的代价。
resc: 10.05 resc_io: 9.00 resc_cpu: 43756354 --串行
resp: 10.05 resp_io: 9.00 resp_cpu: 43756354 --并行
Outer table: T Alias: T
resc: 2.00 card 99.00 bytes: 5 deg: 1 resp: 2.00
Inner table: T1 Alias: T1
resc: 7.05 card: 99.00 bytes: 17 deg: 1 resp: 7.05
using dmeth: 2 #groups: 1
Cost per ptn: 0.50 #ptns: 1
hash_area: 124 (max=3840) buildfrag: 1 probefrag: 1 ppasses: 1
Hash join: Resc: 9.55 Resp: 9.55 [multiMatchCost=0.00]
HA Join
HA cost: 9.55 --hash join的代价。
resc: 9.55 resc_io: 9.00 resc_cpu: 22980929 --串行
resp: 9.55 resp_io: 9.00 resp_cpu: 22980929 --并行
Best:: JoinMethod: Hash --order[2]T关联T1 hash join的代价最小，所以最后选择hash join。
Cost: 9.55 Degree: 1 Resp: 9.55 Card: 98.01 Bytes: 22
***********************
Best so far: Table#: 1 cost: 2.0008 card: 99.0014 bytes: 495
Table#: 0 cost: 9.5532 card: 98.0114 bytes: 2156
***********************
(newjo-stop-1) k:0, spcnt:0, perm:2, maxperm:2000
*********************************
Number of join permutations tried: 2
*********************************
Consider using bloom filter between T[T] and T1[T1]
kkoBloomFilter: join (lcdn:99 rcdn:99 jcdn:98 limit:4901)
Computing bloom ndv for creator:T[T] ccdn:99.0 and user:T1[T1] ucdn:99.0
kkopqComputeBloomNdv: predicate (bndv:71913 ndv:100) and (bndv:9999 ndv:99)
kkopqComputeBloomNdv: pred cnt:2 ndv:99 reduction:1
kkoBloomFilter: join ndv:0 reduction:1.000014 (limit:0.500000) rejected because distinct value ratio
(newjo-save) [1 0 ]
Trying or-Expansion on query block SEL$1 (#0)
Transfer Optimizer annotations for query block SEL$1 (#0)
id=0 frofkke[i] (index stop key) predicate="T"."X"<100
id=0 frofkksm[i] (sort-merge/hash) predicate="T"."X"="T1"."X"
id=0 frosand (sort-merge/hash) predicate="T"."X"="T1"."X"
id=0 frofand predicate="T1"."X"<100
Final cost for query block SEL$1 (#0) - All Rows Plan:
Best join order: 2 --这里是最后确定的管理顺序：order[2]T关联T1
Cost: 9.5532 Degree: 1 Card: 98.0000 Bytes: 2156
Resc: 9.5532 Resc_io: 9.0000 Resc_cpu: 22980929
Resp: 9.5532 Resp_io: 9.0000 Resc_cpu: 22980929
kkoqbc-subheap (delete addr=0x54fb0c, in-use=22908, alloc=23628)
kkoqbc-end:
:
call(in-use=13352, alloc=49168), compile(in-use=63052, alloc=65004), execution(in-use=81656, alloc=85704)
kkoqbc: finish optimizing query block SEL$1 (#0)
apadrv-end
:
call(in-use=13352, alloc=49168), compile(in-use=63748, alloc=65004), execution(in-use=81656, alloc=85704)
Starting SQL statement dump
user_id=84 user_name=U1 module=SQL*Plus action=
sql_id=fr9667v88dwbt plan_hash_value=948419965 problem_type=3
----- Current SQL Statement for this session (sql_id=fr9667v88dwbt) -----
explain plan for select t1.* from t,t1 where t.x < 100 and t.x = t1.x
sql_text_length=70
sql=explain plan for select t1.* from t,t1 where t.x < 100 and t.x = t1.x
----- Explain Plan Dump -----
----- Plan Table -----T1[T1]#0 T[T]#1 --order[1]T1关联T
Best NL cost: 106.07 --nested loops join方式最好的代价。
resc: 106.07 resc_io: 106.00 resc_cpu: 2965253 --串行
resp: 106.07 resp_io: 106.00 resc_cpu: 2965253 --并行
SM cost: 11.05 --sort merge join的代价。
resc: 11.05 resc_io: 9.00 resc_cpu: 85327135 --串行
resp: 11.05 resp_io: 9.00 resp_cpu: 85327135 --并行
HA cost: 9.55 --hash join的代价。
resc: 9.55 resc_io: 9.00 resc_cpu: 22980929 --串行
resp: 9.55 resp_io: 9.00 resp_cpu: 22980929 --并行
T[T]#1 T1[T1]#0 --order[2]T关联T1
Best NL cost: 572.13 --nested loops join方式最好的代价。
resc: 572.13 resc_io: 567.00 resc_cpu: 213015937 --串行
resp: 572.13 resp_io: 567.00 resc_cpu: 213015937 --并行
SM cost: 10.05 --sort merge join的代价。
resc: 10.05 resc_io: 9.00 resc_cpu: 43756354 --串行
resp: 10.05 resp_io: 9.00 resp_cpu: 43756354 --并行
HA cost: 9.55 --hash join的代价。
resc: 9.55 resc_io: 9.00 resc_cpu: 22980929 --串行
resp: 9.55 resp_io: 9.00 resp_cpu: 22980929 --并行============
Plan Table
============
--------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
--------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 10 | |
| 1 | HASH JOIN | | 98 | 2156 | 10 | 00:00:01 |
| 2 | INDEX RANGE SCAN | IND_T | 99 | 495 | 2 | 00:00:01 |
| 3 | TABLE ACCESS FULL | T1 | 99 | 1683 | 7 | 00:00:01 |
--------------------------------------+-----------------------------------+
Predicate Information:
----------------------
1 - access("T"."X"="T1"."X")
2 - access("T"."X"<100)
3 - filter("T1"."X"<100)Content of other_xml column
===========================
db_version : 11.2.0.1
parse_schema : U1
dynamic_sampling: 2
plan_hash : 948419965
plan_hash_2 : 4127279571
Outline Data:
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
DB_VERSION('11.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX(@"SEL$1" "T"@"SEL$1" ("T"."X"))
FULL(@"SEL$1" "T1"@"SEL$1")
LEADING(@"SEL$1" "T"@"SEL$1" "T1"@"SEL$1")
USE_HASH(@"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.1
_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 = 8
_optimizer_max_permutations = 2000
pga_aggregate_target = 76800 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 = 15360 KB
_smm_px_max_size = 38400 KB
_cpu_to_io = 0
_optimizer_undo_cost_change = 11.2.0.1
parallel_query_mode = enabled
parallel_dml_mode = disabled
parallel_ddl_mode = enabled
optimizer_mode = all_rows
sqlstat_enabled = false
_optimizer_percent_parallel = 101
_always_anti_join = choose
_always_semi_join = choose
_optimizer_mode_force = true
_partition_view_enabled = true
_always_star_transformation = false
_query_rewrite_or_error = false
_hash_join_enabled = true
cursor_sharing = exact
_b_tree_bitmap_plans = true
star_transformation_enabled = false
_optimizer_cost_model = choose
_new_sort_cost_estimate = true
_complex_view_merging = true
_unnest_subquery = true
_eliminate_common_subexpr = true
_pred_move_around = true
_convert_set_to_join = false
_push_join_predicate = true
_push_join_union_view = true
_fast_full_scan_enabled = true
_optim_enhance_nnull_detection = true
_parallel_broadcast_enabled = true
_px_broadcast_fudge_factor = 100
_ordered_nested_loop = true
_no_or_expansion = false
optimizer_index_cost_adj = 100
optimizer_index_caching = 0
_system_index_caching = 0
_disable_datalayer_sampling = false
query_rewrite_enabled = true
query_rewrite_integrity = enforced
_query_cost_rewrite = true
_query_rewrite_2 = true
_query_rewrite_1 = true
_query_rewrite_expression = true
_query_rewrite_jgmigrate = true
_query_rewrite_fpc = true
_query_rewrite_drj = true
_full_pwise_join_enabled = true
_partial_pwise_join_enabled = true
_left_nested_loops_random = true
_improved_row_length_enabled = true
_index_join_enabled = true
_enable_type_dep_selectivity = true
_improved_outerjoin_card = true
_optimizer_adjust_for_nulls = true
_optimizer_degree = 0
_use_column_stats_for_function = true
_subquery_pruning_enabled = true
_subquery_pruning_mv_enabled = false
_or_expand_nvl_predicate = true
_like_with_bind_as_equality = false
_table_scan_cost_plus_one = true
_cost_equality_semi_join = true
_default_non_equality_sel_check = true
_new_initial_join_orders = true
_oneside_colstat_for_equijoins = true
_optim_peek_user_binds = true
_minimal_stats_aggregation = true
_force_temptables_for_gsets = false
workarea_size_policy = auto
_smm_auto_cost_enabled = true
_gs_anti_semi_join_allowed = true
_optim_new_default_join_sel = true
optimizer_dynamic_sampling = 2
_pre_rewrite_push_pred = true
_optimizer_new_join_card_computation = true
_union_rewrite_for_gs = yes_gset_mvs
_generalized_pruning_enabled = true
_optim_adjust_for_part_skews = true
_force_datefold_trunc = false
statistics_level = typical
_optimizer_system_stats_usage = true
skip_unusable_indexes = true
_remove_aggr_subquery = true
_optimizer_push_down_distinct = 0
_dml_monitoring_enabled = true
_optimizer_undo_changes = false
_predicate_elimination_enabled = true
_nested_loop_fudge = 100
_project_view_columns = true
_local_communication_costing_enabled = true
_local_communication_ratio = 50
_query_rewrite_vop_cleanup = true
_slave_mapping_enabled = true
_optimizer_cost_based_transformation = linear
_optimizer_mjc_enabled = true
_right_outer_hash_enable = true
_spr_push_pred_refspr = true
_optimizer_cache_stats = false
_optimizer_cbqt_factor = 50
_optimizer_squ_bottomup = true
_fic_area_size = 131072
_optimizer_skip_scan_enabled = true
_optimizer_cost_filter_pred = false
_optimizer_sortmerge_join_enabled = true
_optimizer_join_sel_sanity_check = true
_mmv_query_rewrite_enabled = true
_bt_mmv_query_rewrite_enabled = true
_add_stale_mv_to_dependency_list = true
_distinct_view_unnesting = false
_optimizer_dim_subq_join_sel = true
_optimizer_disable_strans_sanity_checks = 0
_optimizer_compute_index_stats = true
_push_join_union_view2 = true
_optimizer_ignore_hints = false
_optimizer_random_plan = 0
_query_rewrite_setopgrw_enable = true
_optimizer_correct_sq_selectivity = true
_disable_function_based_index = false
_optimizer_join_order_control = 3
_optimizer_cartesian_enabled = true
_optimizer_starplan_enabled = true
_extended_pruning_enabled = true
_optimizer_push_pred_cost_based = true
_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_maxmergedcmaps = 50
_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 = 224
_parallel_syspls_obey_force = true
cell_offload_processing = true
_rdbms_internal_fplib_enabled = false
db_file_multiblock_read_count = 40
_bloom_folding_enabled = true
_mv_generalized_oj_refresh_opt = true
cell_offload_compaction = ADAPTIVE
parallel_degree_limit = 65535
parallel_force_local = false
parallel_max_degree = 2
total_cpu_count = 1
cell_offload_plan_display = AUTO
_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
_bloom_predicate_pushdown_to_storage = true
_bloom_vector_elements = 0
_bloom_pushing_max = 524288
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
Bug Fix Control Environment
fix 3834770 = 1
fix 3746511 = enabled
fix 4519016 = enabled
fix 3118776 = enabled
fix 7556098 = enabled
... ...
fix 8580883 = enabled
fix 5892599 = disabled
fix 8609714 = enabled
fix 8514561 = enabled
fix 8619631 = disabled
Query Block Registry:
SEL$1 0x2739f8f8 (PARSER) [FINAL]
:
call(in-use=19408, alloc=49168), compile(in-use=90196, alloc=187356), execution(in-use=208012, alloc=208656)
End of Optimizer State Dump
Dumping Hints
=============
====================== END SQL Statement Dump ======================
2139
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
