SQL> alter session set events '10053 trace name context forever,level 1';
Session altered.
SQL> explain plan for select t1.* from t,t1 where t.x<100 and t.x=t1.x;
Explained.
SQL> alter session set events '10053 trace name context off';
SYS.DBMS_SYSTEM.SET_EV (<sid>, <serial#>, 10053, {1|2}, '')
关闭：
SYS.DBMS_SYSTEM.SET_EV (<sid>, <serial#>, 10053,0, '')
**************************
Predicate Move-Around (PM)**************************
PM: Considering predicate move-around in SEL$1 (#0).
PM: Checking validity of predicate move-around in SEL$1 (#0).
CBQT: Validity checks failed for f5x7vjx0tm31y.
Query block (7000004cbff84c0) before join elimination:
SQL:******* UNPARSED QUERY IS *******
SELECT "T1"."X" "X","T1"."NAME" "NAME" FROM "AIKI"."T" "T","AIKI"."T1" "T1" WHERE "T"."X"<100 AND "T"."X"="T1"."X"
Query block (7000004cbff84c0) unchanged
CVM: Considering view merge in query block SEL$1 (#0)
Query block (7000004cbff84c0) before join elimination:
SQL:******* UNPARSED QUERY IS *******
SELECT "T1"."X" "X","T1"."NAME" "NAME" FROM "AIKI"."T" "T","AIKI"."T1" "T1" WHERE "T"."X"<100 AND "T"."X"="T1"."X"
Query block (7000004cbff84c0) unchanged
CBQT: Validity checks failed for f5x7vjx0tm31y.
***************
Subquery Unnest
***************
SU: Considering subquery unnesting in query block SEL$1 (#0)
*************************
Set-Join Conversion (SJC)
*************************
SJC: Considering set-join conversion in SEL$1 (#0).
**************************
Predicate Move-Around (PM)
**************************
PM: Considering predicate move-around in SEL$1 (#0).
PM: Checking validity of predicate move-around in SEL$1 (#0).
PM: PM bypassed: Outer query contains no views.
FPD: Considering simple filter push in SEL$1 (#0)
FPD: Current where clause predicates in SEL$1 (#0) :
"T"."X"<100 AND "T"."X"="T1"."X"
kkogcp: try to generate transitive predicate from check constraints for SEL$1 (#0)
predicates with check contraints: "T"."X"<100 AND "T"."X"="T1"."X" AND "T1"."X"<100
after transitive predicate generation: "T"."X"<100 AND "T"."X"="T1"."X" AND "T1"."X"<100
finally: "T"."X"<100 AND "T"."X"="T1"."X" AND "T1"."X"<100
FPD: Following transitive predicates are generated in SEL$1 (#0) :
"T1"."X"<100
apadrv-start: call(in-use=1064, alloc=16344), compile(in-use=35216, alloc=36536)
kkoqbc-start
: call(in-use=1072, alloc=16344), compile(in-use=36432, alloc=36536)
kkoqbc-subheap (create addr=11049bba8)
******************************************
Current SQL statement for this session:
explain plan for select t1.* from t,t1 where t.x<100 and t.x=t1.x
*******************************************
Legend
The following abbreviations are used by optimizer trace.
CBQT - cost-based query transformation
JPPD - join predicate push-down
FPD - filter push-down
PM - predicate move-around
CVM - complex view merging
SPJ - select-project-join
SJC - set join conversion
SU - subquery unnesting
OBYE - order by elimination
ST - star transformation
qb - query block
LB - leaf blocks
DK - distinct keys
LB/K - average number of leaf blocks per key
DB/K - average number of data blocks per key
CLUF - clustering factor
NDV - number of distinct values
Resp - response cost
Card - cardinality
Resc - resource cost
NL - nested loops (join)
SM - sort merge (join)
HA - hash (join)
CPUCSPEED - CPU Speed
IOTFRSPEED - I/O transfer speed
IOSEEKTIM - I/O seek time
SREADTIM - average single block read time
MREADTIM - average multiblock read time
MBRC - average multiblock read count
MAXTHR - maximum I/O system throughput
SLAVETHR - average slave I/O throughput
dmeth - distribution method
1: no partitioning required
2: value partitioned
4: right is random (round-robin)
512: left is random (round-robin)
8: broadcast right and partition left
16: broadcast left and partition right
32: partition left using partitioning of right
64: partition right using partitioning of left
128: use hash partitioning dimension
256: use range partitioning dimension
2048: use list partitioning dimension
1024: run the join in serial
0: invalid distribution method
sel - selectivity
ptn - partition
*******************************************
Peeked values of the binds in SQL statement
*******************************************
***************************************
PARAMETERS USED BY THE OPTIMIZER
********************************
*************************************
PARAMETERS WITH ALTERED VALUES
******************************
_pga_max_size = 1340000 KB
cursor_sharing = similar
optimizer_index_cost_adj = 31
*********************************
Bug Fix Control Environment
***************************
fix 4611850 = enabled
fix 4663804 = enabled
fix 4663698 = enabled
fix 4545833 = enabled
fix 3499674 = disabled
fix 4584065 = enabled
fix 4602374 = enabled
fix 4569940 = enabled
fix 4631959 = enabled
fix 4519340 = enabled
fix 4550003 = enabled
fix 4488689 = enabled
fix 3118776 = enabled
fix 4519016 = enabled
fix 4487253 = enabled
fix 4556762 = 15
fix 4728348 = enabled
fix 4723244 = enabled
fix 4554846 = enabled
fix 4175830 = enabled
fix 4722900 = enabled
fix 5094217 = enabled
fix 4904890 = enabled
fix 4483286 = disabled
fix 4969880 = disabled
fix 4711525 = enabled
fix 4717546 = enabled
fix 4904838 = enabled
fix 5005866 = enabled
fix 4600710 = enabled
fix 5129233 = enabled
fix 5195882 = enabled
fix 5084239 = enabled
fix 4595987 = enabled
fix 4134994 = enabled
fix 5104624 = enabled
fix 4908162 = enabled
fix 5015557 = enabled
fix 5263572 = enabled
fix 4483240 = enabled
fix 5099909 = enabled
fix 5650477 = enabled
fix 4273361 = enabled
fix 5694984 = enabled
fix 5449488 = enabled
fix 5236908 = enabled
fix 5618040 = enabled
fix 5634346 = enabled
fix 5220356 = enabled
fix 5611962 = enabled
fix 5741121 = enabled
fix 5547058 = enabled
fix 5762598 = enabled
fix 5509293 = enabled
fix 5396162 = enabled
fix 5891471 = enabled
fix 4872602 = disabled
fix 5882954 = enabled
fix 5884780 = enabled
fix 5680702 = enabled
fix 5240607 = enabled
fix 4924149 = enabled
fix 4752814 = enabled
fix 4583239 = enabled
fix 5949981 = enabled
fix 5096560 = enabled
fix 5838613 = enabled
fix 5482831 = enabled
fix 5624216 = enabled
fix 5976822 = enabled
fix 5741044 = enabled
fix 5385629 = enabled
fix 5705630 = disabled
fix 5483301 = enabled
fix 6122894 = enabled
fix 5842686 = disabled
fix 6006300 = disabled
fix 6070954 = enabled
fix 2492766 = enabled
fix 6042205 = enabled
fix 5302124 = enabled
fix 6051211 = enabled
fix 5620485 = enabled
fix 4545802 = enabled
fix 4716096 = enabled
fix 5259048 = enabled
fix 6163564 = enabled
fix 6082745 = enabled
fix 5944076 = enabled
fix 4878299 = enabled
fix 5288623 = enabled
fix 5570494 = enabled
fix 5387148 = enabled
fix 4605810 = enabled
fix 4704779 = enabled
fix 5547895 = enabled
fix 6188881 = enabled
fix 5872956 = enabled
fix 4708389 = enabled
fix 3151991 = enabled
fix 3426050 = enabled
fix 599680 = enabled
fix 5505157 = enabled
fix 5996801 = enabled
fix 5765456 = 0
fix 6494943 = enabled
fix 6251917 = enabled
fix 6087237 = enabled
fix 6239971 = enabled
fix 6062266 = enabled
fix 5520732 = 0
fix 6151963 = enabled
fix 4567767 = enabled
fix 6007259 = enabled
fix 6694548 = enabled
*************************************
PARAMETERS WITH DEFAULT VALUES
******************************
optimizer_mode_hinted = false
optimizer_features_hinted = 0.0.0
parallel_execution_enabled = true
parallel_query_forced_dop = 0
parallel_dml_forced_dop = 0
parallel_ddl_forced_degree = 0
parallel_ddl_forced_instances = 0
_query_rewrite_fudge = 90
optimizer_features_enable = 10.2.0.4
_optimizer_search_limit = 5
cpu_count = 16
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 = 16384
_sort_multiblock_read_count = 2
_hash_multiblock_io_count = 0
_db_file_optimizer_read_count = 25
_optimizer_max_permutations = 2000
pga_aggregate_target = 6700032 KB
_query_rewrite_maxdisjunct = 257
_smm_auto_min_io_size = 48 KB
_smm_auto_max_io_size = 240 KB
_smm_min_size = 1024 KB
_smm_max_size = 670000 KB
_smm_px_max_size = 3350016 KB
_cpu_to_io = 0
_optimizer_undo_cost_change = 10.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
_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_caching = 0
_system_index_caching = 0
_disable_datalayer_sampling = false
query_rewrite_enabled = true
query_rewrite_integrity = enforced
_query_cost_rewrite = true
_query_rewrite_2 = true
_query_rewrite_1 = true
_query_rewrite_expression = true
_query_rewrite_jgmigrate = true
_query_rewrite_fpc = true
_query_rewrite_drj = true
_full_pwise_join_enabled = true
_partial_pwise_join_enabled = true
_left_nested_loops_random = true
_improved_row_length_enabled = true
_index_join_enabled = true
_enable_type_dep_selectivity = true
_improved_outerjoin_card = true
_optimizer_adjust_for_nulls = true
_optimizer_degree = 0
_use_column_stats_for_function = true
_subquery_pruning_enabled = true
_subquery_pruning_mv_enabled = false
_or_expand_nvl_predicate = true
_like_with_bind_as_equality = false
_table_scan_cost_plus_one = true
_cost_equality_semi_join = true
_default_non_equality_sel_check = true
_new_initial_join_orders = true
_oneside_colstat_for_equijoins = true
_optim_peek_user_binds = true
_minimal_stats_aggregation = true
_force_temptables_for_gsets = false
workarea_size_policy = auto
_smm_auto_cost_enabled = true
_gs_anti_semi_join_allowed = true
_optim_new_default_join_sel = true
optimizer_dynamic_sampling = 2
_pre_rewrite_push_pred = true
_optimizer_new_join_card_computation = true
_union_rewrite_for_gs = yes_gset_mvs
_generalized_pruning_enabled = true
_optim_adjust_for_part_skews = true
_force_datefold_trunc = false
statistics_level = typical
_optimizer_system_stats_usage = true
skip_unusable_indexes = true
_remove_aggr_subquery = true
_optimizer_push_down_distinct = 0
_dml_monitoring_enabled = true
_optimizer_undo_changes = false
_predicate_elimination_enabled = true
_nested_loop_fudge = 100
_project_view_columns = true
_local_communication_costing_enabled = true
_local_communication_ratio = 50
_query_rewrite_vop_cleanup = true
_slave_mapping_enabled = true
_optimizer_cost_based_transformation = linear
_optimizer_mjc_enabled = true
_right_outer_hash_enable = true
_spr_push_pred_refspr = true
_optimizer_cache_stats = false
_optimizer_cbqt_factor = 50
_optimizer_squ_bottomup = true
_fic_area_size = 131072
_optimizer_skip_scan_enabled = true
_optimizer_cost_filter_pred = false
_optimizer_sortmerge_join_enabled = true
_optimizer_join_sel_sanity_check = true
_mmv_query_rewrite_enabled = true
_bt_mmv_query_rewrite_enabled = true
_add_stale_mv_to_dependency_list = true
_distinct_view_unnesting = false
_optimizer_dim_subq_join_sel = true
_optimizer_disable_strans_sanity_checks = 0
_optimizer_compute_index_stats = true
_push_join_union_view2 = true
_optimizer_ignore_hints = false
_optimizer_random_plan = 0
_query_rewrite_setopgrw_enable = true
_optimizer_correct_sq_selectivity = true
_disable_function_based_index = false
_optimizer_join_order_control = 3
_optimizer_cartesian_enabled = true
_optimizer_starplan_enabled = true
_extended_pruning_enabled = true
_optimizer_push_pred_cost_based = true
_sql_model_unfold_forloops = run_time
_enable_dml_lock_escalation = false
_bloom_filter_enabled = true
_update_bji_ipdml_enabled = 0
_optimizer_extended_cursor_sharing = udo
_dm_max_shared_pool_pct = 1
_optimizer_cost_hjsmj_multimatch = true
_optimizer_transitivity_retain = true
_px_pwg_enabled = true
optimizer_secure_view_merging = true
_optimizer_join_elimination_enabled = true
flashback_table_rpi = non_fbt
_optimizer_cbqt_no_size_restriction = true
_optimizer_enhanced_filter_push = true
_optimizer_filter_pred_pullup = true
_rowsrc_trace_level = 0
_simple_view_merging = true
_optimizer_rownum_pred_based_fkr = true
_optimizer_better_inlist_costing = all
_optimizer_self_induced_cache_cost = false
_optimizer_min_cache_blocks = 10
_optimizer_or_expansion = depth
_optimizer_order_by_elimination_enabled = true
_optimizer_outer_to_anti_enabled = true
_selfjoin_mv_duplicates = true
_dimension_skip_null = true
_force_rewrite_enable = false
_optimizer_star_tran_in_with_clause = true
_optimizer_complex_pred_selectivity = true
_optimizer_connect_by_cost_based = true
_gby_hash_aggregation_enabled = true
_globalindex_pnum_filter_enabled = true
_fix_control_key = 0
_optimizer_skip_scan_guess = false
_enable_row_shipping = false
_row_shipping_threshold = 80
_row_shipping_explain = false
_optimizer_rownum_bind_default = 10
_first_k_rows_dynamic_proration = true
_px_ual_serial_input = true
_optimizer_native_full_outer_join = off
_optimizer_star_trans_min_cost = 0
_optimizer_star_trans_min_ratio = 0
_optimizer_fkr_index_cost_bias = 10
_optimizer_connect_by_combine_sw = true
_optimizer_use_subheap = true
_optimizer_or_expansion_subheap = true
_optimizer_sortmerge_join_inequality = true
_optimizer_use_histograms = true
_optimizer_enable_density_improvements = false
*********************************
Bug Fix Control Environment
***************************
fix 4611850 = enabled
fix 4663804 = enabled
fix 4663698 = enabled
fix 4545833 = enabled
fix 3499674 = disabled
fix 4584065 = enabled
fix 4602374 = enabled
fix 4569940 = enabled
fix 4631959 = enabled
fix 4519340 = enabled
fix 4550003 = enabled
fix 4488689 = enabled
fix 3118776 = enabled
fix 4519016 = enabled
fix 4487253 = enabled
fix 4556762 = 15
fix 4728348 = enabled
fix 4723244 = enabled
fix 4554846 = enabled
fix 4175830 = enabled
fix 4722900 = enabled
fix 5094217 = enabled
fix 4904890 = enabled
fix 4483286 = disabled
fix 4969880 = disabled
fix 4711525 = enabled
fix 4717546 = enabled
fix 4904838 = enabled
fix 5005866 = enabled
fix 4600710 = enabled
fix 5129233 = enabled
fix 5195882 = enabled
fix 5084239 = enabled
fix 4595987 = enabled
fix 4134994 = enabled
fix 5104624 = enabled
fix 4908162 = enabled
fix 5015557 = enabled
fix 5263572 = enabled
fix 4483240 = enabled
fix 5099909 = enabled
fix 5650477 = enabled
fix 4273361 = enabled
fix 5694984 = enabled
fix 5449488 = enabled
fix 5236908 = enabled
fix 5618040 = enabled
fix 5634346 = enabled
fix 5220356 = enabled
fix 5611962 = enabled
fix 5741121 = enabled
fix 5547058 = enabled
fix 5762598 = enabled
fix 5509293 = enabled
fix 5396162 = enabled
fix 5891471 = enabled
fix 4872602 = disabled
fix 5882954 = enabled
fix 5884780 = enabled
fix 5680702 = enabled
fix 5240607 = enabled
fix 4924149 = enabled
fix 4752814 = enabled
fix 4583239 = enabled
fix 5949981 = enabled
fix 5096560 = enabled
fix 5838613 = enabled
fix 5482831 = enabled
fix 5624216 = enabled
fix 5976822 = enabled
fix 5741044 = enabled
fix 5385629 = enabled
fix 5705630 = disabled
fix 5483301 = enabled
fix 6122894 = enabled
fix 5842686 = disabled
fix 6006300 = disabled
fix 6070954 = enabled
fix 2492766 = enabled
fix 6042205 = enabled
fix 5302124 = enabled
fix 6051211 = enabled
fix 5620485 = enabled
fix 4545802 = enabled
fix 4716096 = enabled
fix 5259048 = enabled
fix 6163564 = enabled
fix 6082745 = enabled
fix 5944076 = enabled
fix 4878299 = enabled
fix 5288623 = enabled
fix 5570494 = enabled
fix 5387148 = enabled
fix 4605810 = enabled
fix 4704779 = enabled
fix 5547895 = enabled
fix 6188881 = enabled
fix 5872956 = enabled
fix 4708389 = enabled
fix 3151991 = enabled
fix 3426050 = enabled
fix 599680 = enabled
fix 5505157 = enabled
fix 5996801 = enabled
fix 5765456 = 0
fix 6494943 = enabled
fix 6251917 = enabled
fix 6087237 = enabled
fix 6239971 = enabled
fix 6062266 = enabled
fix 5520732 = 0
fix 6151963 = enabled
fix 4567767 = enabled
fix 6007259 = enabled
fix 6694548 = enabled
***************************************
PARAMETERS IN OPT_PARAM HINT
****************************
***************************************
Column Usage Monitoring is ON: tracking level = 1
***************************************
****************
QUERY BLOCK TEXT
****************
select t1.* from t,t1 where t.x<100 and t.x=t1.x
*********************
QUERY BLOCK SIGNATURE
*********************
qb name was generated
signature (optimizer): qb_name=SEL$1 nbfros=2 flg=0
fro(0): flg=0 objn=1896491 hint_alias="T"@"SEL$1"
fro(1): flg=0 objn=1896493 hint_alias="T1"@"SEL$1"
*****************************
SYSTEM STATISTICS INFORMATION
*****************************
Using NOWORKLOAD Stats
CPUSPEED: 1190 millions instruction/sec
IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
IOSEEKTIM: 10 milliseconds (default is 10)
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: T1 Alias: T1 (NOT ANALYZED)
#Rows: 1964 #Blks: 12 AvgRowLen: 100.00
Column (#1): X(NUMBER) NO STATISTICS (using defaults)
AvgLen: 13.00 NDV: 61 Nulls: 0 Density: 0.016293
***********************
Table Stats::
Table: T Alias: T
#Rows: 365354 #Blks: 286 AvgRowLen: 4.00
Column (#1): X(NUMBER)
AvgLen: 5.00 NDV: 365354 Nulls: 0 Density: 2.7371e-06 Min: 33 Max: 364916
Index Stats::
Index: IND_T Col#: 1
LVLS: 1 #LB: 401 #DK: 364949 LB/K: 1.00 DB/K: 1.00 CLUF: 276.00
***************************************
SINGLE TABLE ACCESS PATH
-----------------------------------------
BEGIN Single Table Cardinality Estimation
-----------------------------------------
Table: T Alias: T
Card: Original: 365354 Rounded: 67 Computed: 67.09 Non Adjusted: 67.09
-----------------------------------------
END Single Table Cardinality Estimation
-----------------------------------------
Access Path: TableScan
Cost: 96.55 Resp: 96.55 Degree: 0
Cost_io: 92.00 Cost_cpu: 75857264
Resp_io: 92.00 Resp_cpu: 75857264
Access Path: index (index (FFS))
Index: IND_T
resc_io: 128.00 resc_cpu: 65948225
ix_sel: 0.0000e+00 ix_sel_with_filters: 1
Access Path: index (FFS)
Cost: 131.96 Resp: 131.96 Degree: 1
Cost_io: 128.00 Cost_cpu: 65948225
Resp_io: 128.00 Resp_cpu: 65948225
Access Path: index (IndexOnly)
Index: IND_T
resc_io: 2.00 resc_cpu: 33086
ix_sel: 1.8382e-04 ix_sel_with_filters: 1.8382e-04
Cost: 1.00 Resp: 1.00 Degree: 1
Best:: AccessPath: IndexRange Index: IND_T
Cost: 1.00 Degree: 1 Resp: 1.00 Card: 67.09 Bytes: 0
***************************************
SINGLE TABLE ACCESS PATH
-----------------------------------------
BEGIN Single Table Cardinality Estimation
-----------------------------------------
*** 2012-03-12 09:20:26.444
** Performing dynamic sampling initial checks. **
** Dynamic sampling initial checks returning TRUE (level = 2).
** Dynamic sampling updated table stats.: blocks=12
*** 2012-03-12 09:20:26.444
** 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
*** 2012-03-12 09:20:26.461
** Executed dynamic sampling query:
level : 2
sample pct. : 100.000000
actual sample size : 9999
filtered sample card. : 99
orig. card. : 1964
block cnt. table stat. : 12
block cnt. for sampling: 12
max. sample block cnt. : 64
sample block cnt. : 12
ndv C3 : 9999
scaled : 9999.00
nulls C4 : 0
scaled : 0.00
min. sel. est. : 0.05000000
** Dynamic sampling col. stats.:
Column (#1): X(NUMBER) Part#: 0
AvgLen: 22.00 NDV: 9999 Nulls: 0 Density: 1.0001e-04
** 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 Rounded: 99 Computed: 99.00 Non Adjusted: 99.00
-----------------------------------------
END Single Table Cardinality Estimation
-----------------------------------------
Access Path: TableScan
Cost: 6.13 Resp: 6.13 Degree: 0
Cost_io: 6.00 Cost_cpu: 2118695
Resp_io: 6.00 Resp_cpu: 2118695
Best:: AccessPath: TableScan
Cost: 6.13 Degree: 1 Resp: 6.13 Card: 99.00 Bytes: 0
***************************************
OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***************************************
Considering cardinality-based initial join order.
Permutations for Starting Table :0
***********************
Join order[1]: T[T]#0 T1[T1]#1
***************
Now joining: T1[T1]#1
***************
NL Join
Outer table: Card: 67.09 Cost: 1.00 Resp: 1.00 Degree: 1 Bytes: 4
Inner table: T1 Alias: T1
Access Path: TableScan
NL Join: Cost: 263.52 Resp: 263.52 Degree: 1
Cost_io: 255.00 Cost_cpu: 141962792
Resp_io: 255.00 Resp_cpu: 141962792
Best NL cost: 263.52
resc: 263.52 resc_io: 255.00 resc_cpu: 141962792
resp: 263.52 resp_io: 255.00 resp_cpu: 141962792
Join Card: 67.09 = outer (67.09) * inner (99.00) * sel (0.010101)
Join Card - Rounded: 67 Computed: 67.09
SM Join
Outer table:
resc: 1.00 card 67.09 bytes: 4 deg: 1 resp: 1.00
Inner table: T1 Alias: T1
resc: 6.13 card: 99.00 bytes: 17 deg: 1 resp: 6.13
using dmeth: 2 #groups: 1
SORT resource Sort statistics
Sort width: 3924 Area size: 1048576 Max Area size: 686080000
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: 16683383
Total Temp space used: 0
SM join: Resc: 8.13 Resp: 8.13 [multiMatchCost=0.00]
SM cost: 8.13
resc: 8.13 resc_io: 7.00 resc_cpu: 18812334
resp: 8.13 resp_io: 7.00 resp_cpu: 18812334
HA Join
Outer table:
resc: 1.00 card 67.09 bytes: 4 deg: 1 resp: 1.00
Inner table: T1 Alias: T1
resc: 6.13 card: 99.00 bytes: 17 deg: 1 resp: 6.13
using dmeth: 2 #groups: 1
Cost per ptn: 0.50 #ptns: 1
hash_area: 0 (max=128) Hash join: Resc: 7.63 Resp: 7.63 [multiMatchCost=0.00]
HA cost: 7.63
resc: 7.63 resc_io: 7.00 resc_cpu: 10475808
resp: 7.63 resp_io: 7.00 resp_cpu: 10475808
Best:: JoinMethod: Hash
Cost: 7.63 Degree: 1 Resp: 7.63 Card: 67.09 Bytes: 21
***********************
Best so far: Table#: 0 cost: 1.0006 card: 67.0865 bytes: 268
Table#: 1 cost: 7.6290 card: 67.0865 bytes: 1407
***********************
Join order[2]: T1[T1]#1 T[T]#0
***************
Now joining: T[T]#0
***************
NL Join
Outer table: Card: 99.00 Cost: 6.13 Resp: 6.13 Degree: 1 Bytes: 17
Inner table: T Alias: T
Access Path: TableScan
NL Join: Cost: 9357.07 Resp: 9357.07 Degree: 1
Cost_io: 8906.00 Cost_cpu: 7511987799
Resp_io: 8906.00 Resp_cpu: 7511987799
Access Path: index (index (FFS))
Index: IND_T
resc_io: 126.05 resc_cpu: 65948225
ix_sel: 0.0000e+00 ix_sel_with_filters: 1
Inner table: T Alias: T
Access Path: index (FFS)
NL Join: Cost: 12877.16 Resp: 12877.16 Degree: 1
Cost_io: 12485.00 Cost_cpu: 6530992958
Resp_io: 12485.00 Resp_cpu: 6530992958
kkofmx: index filter:"T"."X"<100
Access Path: index (AllEqJoinGuess)
Index: IND_T
resc_io: 1.00 resc_cpu: 10793
ix_sel: 2.7401e-06 ix_sel_with_filters: 5.0314e-10
NL Join: Cost: 36.84 Resp: 36.84 Degree: 1
Cost_io: 36.69 Cost_cpu: 2451463
Resp_io: 36.69 Resp_cpu: 2451463
Best NL cost: 36.84
resc: 36.84 resc_io: 36.69 resc_cpu: 2451463
resp: 36.84 resp_io: 36.69 resp_cpu: 2451463
Join Card: 67.09 = outer (99.00) * inner (67.09) * sel (0.010101)
Join Card - Rounded: 67 Computed: 67.09
SM Join
Outer table:
resc: 6.13 card 99.00 bytes: 17 deg: 1 resp: 6.13
Inner table: T Alias: T
resc: 1.00 card: 67.09 bytes: 4 deg: 1 resp: 1.00
using dmeth: 2 #groups: 1
SORT resource Sort statistics
Sort width: 3924 Area size: 1048576 Max Area size: 686080000
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: 16683383
Total Temp space used: 0
SORT resource Sort statistics
Sort width: 3924 Area size: 1048576 Max Area size: 686080000
Degree: 1
Blocks to Sort: 1 Row size: 15 Total Rows: 67
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 16672125
Total Temp space used: 0
SM join: Resc: 9.13 Resp: 9.13 [multiMatchCost=0.00]
SM cost: 9.13
resc: 9.13 resc_io: 7.00 resc_cpu: 35484459
resp: 9.13 resp_io: 7.00 resp_cpu: 35484459
HA Join
Outer table:
resc: 6.13 card 99.00 bytes: 17 deg: 1 resp: 6.13
Inner table: T Alias: T
resc: 1.00 card: 67.09 bytes: 4 deg: 1 resp: 1.00
using dmeth: 2 #groups: 1
Cost per ptn: 0.50 #ptns: 1
hash_area: 0 (max=128) Hash join: Resc: 7.63 Resp: 7.63 [multiMatchCost=0.00]
HA cost: 7.63
resc: 7.63 resc_io: 7.00 resc_cpu: 10477408
resp: 7.63 resp_io: 7.00 resp_cpu: 10477408
Join order aborted: cost > best plan cost
***********************
(newjo-stop-1) k:0, spcnt:0, perm:2, maxperm:2000
*********************************
Number of join permutations tried: 2
*********************************
(newjo-save) [1 0 ]
Final - All Rows Plan: Best join order: 1
Cost: 7.6290 Degree: 1 Card: 67.0000 Bytes: 1407
Resc: 7.6290 Resc_io: 7.0000 Resc_cpu: 10475808
Resp: 7.6290 Resp_io: 7.0000 Resc_cpu: 10475808
kkoipt: Query block SEL$1 (#0)
******* UNPARSED QUERY IS *******
SELECT "T1"."X" "X","T1"."NAME" "NAME" FROM "AIKI"."T" "T","AIKI"."T1" "T1" WHERE "T"."X"<100 AND "T"."X"="T1"."X" AND "T1"."X"<100
kkoqbc-subheap (delete addr=11049bba8, in-use=24376, alloc=26624)
kkoqbc-end
: call(in-use=20376, alloc=65448), compile(in-use=39680, alloc=40552)
apadrv-end: call(in-use=20376, alloc=65448), compile(in-use=40624, alloc=44568)
sql_id=f5x7vjx0tm31y.
Current SQL statement for this session:
explain plan for select t1.* from t,t1 where t.x<100 and t.x=t1.x
============
Plan Table
============
--------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
--------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 8 | |
| 1 | HASH JOIN | | 67 | 1407 | 8 | 00:00:01 |
| 2 | INDEX RANGE SCAN | IND_T | 67 | 268 | 1 | 00:00:01 |
| 3 | TABLE ACCESS FULL | T1 | 99 | 1683 | 6 | 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 : 10.2.0.4
parse_schema : AIKI
dynamic_sampling: yes
plan_hash : 1857101565
Outline Data:
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
OPT_PARAM('optimizer_index_cost_adj' 31)
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 environment:
optimizer_mode_hinted = false
optimizer_features_hinted = 0.0.0
parallel_execution_enabled = true
parallel_query_forced_dop = 0
parallel_dml_forced_dop = 0
parallel_ddl_forced_degree = 0
parallel_ddl_forced_instances = 0
_query_rewrite_fudge = 90
optimizer_features_enable = 10.2.0.4
_optimizer_search_limit = 5
cpu_count = 16
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 = 16384
_sort_multiblock_read_count = 2
_hash_multiblock_io_count = 0
_db_file_optimizer_read_count = 25
_optimizer_max_permutations = 2000
pga_aggregate_target = 6700032 KB
_pga_max_size = 1340000 KB
_query_rewrite_maxdisjunct = 257
_smm_auto_min_io_size = 48 KB
_smm_auto_max_io_size = 240 KB
_smm_min_size = 1024 KB
_smm_max_size = 670000 KB
_smm_px_max_size = 3350016 KB
_cpu_to_io = 0
_optimizer_undo_cost_change = 10.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 = similar
_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 = 31
optimizer_index_caching = 0
_system_index_caching = 0
_disable_datalayer_sampling = false
query_rewrite_enabled = true
query_rewrite_integrity = enforced
_query_cost_rewrite = true
_query_rewrite_2 = true
_query_rewrite_1 = true
_query_rewrite_expression = true
_query_rewrite_jgmigrate = true
_query_rewrite_fpc = true
_query_rewrite_drj = true
_full_pwise_join_enabled = true
_partial_pwise_join_enabled = true
_left_nested_loops_random = true
_improved_row_length_enabled = true
_index_join_enabled = true
_enable_type_dep_selectivity = true
_improved_outerjoin_card = true
_optimizer_adjust_for_nulls = true
_optimizer_degree = 0
_use_column_stats_for_function = true
_subquery_pruning_enabled = true
_subquery_pruning_mv_enabled = false
_or_expand_nvl_predicate = true
_like_with_bind_as_equality = false
_table_scan_cost_plus_one = true
_cost_equality_semi_join = true
_default_non_equality_sel_check = true
_new_initial_join_orders = true
_oneside_colstat_for_equijoins = true
_optim_peek_user_binds = true
_minimal_stats_aggregation = true
_force_temptables_for_gsets = false
workarea_size_policy = auto
_smm_auto_cost_enabled = true
_gs_anti_semi_join_allowed = true
_optim_new_default_join_sel = true
optimizer_dynamic_sampling = 2
_pre_rewrite_push_pred = true
_optimizer_new_join_card_computation = true
_union_rewrite_for_gs = yes_gset_mvs
_generalized_pruning_enabled = true
_optim_adjust_for_part_skews = true
_force_datefold_trunc = false
statistics_level = typical
_optimizer_system_stats_usage = true
skip_unusable_indexes = true
_remove_aggr_subquery = true
_optimizer_push_down_distinct = 0
_dml_monitoring_enabled = true
_optimizer_undo_changes = false
_predicate_elimination_enabled = true
_nested_loop_fudge = 100
_project_view_columns = true
_local_communication_costing_enabled = true
_local_communication_ratio = 50
_query_rewrite_vop_cleanup = true
_slave_mapping_enabled = true
_optimizer_cost_based_transformation = linear
_optimizer_mjc_enabled = true
_right_outer_hash_enable = true
_spr_push_pred_refspr = true
_optimizer_cache_stats = false
_optimizer_cbqt_factor = 50
_optimizer_squ_bottomup = true
_fic_area_size = 131072
_optimizer_skip_scan_enabled = true
_optimizer_cost_filter_pred = false
_optimizer_sortmerge_join_enabled = true
_optimizer_join_sel_sanity_check = true
_mmv_query_rewrite_enabled = true
_bt_mmv_query_rewrite_enabled = true
_add_stale_mv_to_dependency_list = true
_distinct_view_unnesting = false
_optimizer_dim_subq_join_sel = true
_optimizer_disable_strans_sanity_checks = 0
_optimizer_compute_index_stats = true
_push_join_union_view2 = true
_optimizer_ignore_hints = false
_optimizer_random_plan = 0
_query_rewrite_setopgrw_enable = true
_optimizer_correct_sq_selectivity = true
_disable_function_based_index = false
_optimizer_join_order_control = 3
_optimizer_cartesian_enabled = true
_optimizer_starplan_enabled = true
_extended_pruning_enabled = true
_optimizer_push_pred_cost_based = true
_sql_model_unfold_forloops = run_time
_enable_dml_lock_escalation = false
_bloom_filter_enabled = true
_update_bji_ipdml_enabled = 0
_optimizer_extended_cursor_sharing = udo
_dm_max_shared_pool_pct = 1
_optimizer_cost_hjsmj_multimatch = true
_optimizer_transitivity_retain = true
_px_pwg_enabled = true
optimizer_secure_view_merging = true
_optimizer_join_elimination_enabled = true
flashback_table_rpi = non_fbt
_optimizer_cbqt_no_size_restriction = true
_optimizer_enhanced_filter_push = true
_optimizer_filter_pred_pullup = true
_rowsrc_trace_level = 0
_simple_view_merging = true
_optimizer_rownum_pred_based_fkr = true
_optimizer_better_inlist_costing = all
_optimizer_self_induced_cache_cost = false
_optimizer_min_cache_blocks = 10
_optimizer_or_expansion = depth
_optimizer_order_by_elimination_enabled = true
_optimizer_outer_to_anti_enabled = true
_selfjoin_mv_duplicates = true
_dimension_skip_null = true
_force_rewrite_enable = false
_optimizer_star_tran_in_with_clause = true
_optimizer_complex_pred_selectivity = true
_optimizer_connect_by_cost_based = true
_gby_hash_aggregation_enabled = true
_globalindex_pnum_filter_enabled = true
_fix_control_key = 0
_optimizer_skip_scan_guess = false
_enable_row_shipping = false
_row_shipping_threshold = 80
_row_shipping_explain = false
_optimizer_rownum_bind_default = 10
_first_k_rows_dynamic_proration = true
_px_ual_serial_input = true
_optimizer_native_full_outer_join = off
_optimizer_star_trans_min_cost = 0
_optimizer_star_trans_min_ratio = 0
_optimizer_fkr_index_cost_bias = 10
_optimizer_connect_by_combine_sw = true
_optimizer_use_subheap = true
_optimizer_or_expansion_subheap = true
_optimizer_sortmerge_join_inequality = true
_optimizer_use_histograms = true
_optimizer_enable_density_improvements = false
*********************************
Bug Fix Control Environment
***************************
fix 4611850 = enabled
fix 4663804 = enabled
fix 4663698 = enabled
fix 4545833 = enabled
fix 3499674 = disabled
fix 4584065 = enabled
fix 4602374 = enabled
fix 4569940 = enabled
fix 4631959 = enabled
fix 4519340 = enabled
fix 4550003 = enabled
fix 4488689 = enabled
fix 3118776 = enabled
fix 4519016 = enabled
fix 4487253 = enabled
fix 4556762 = 15
fix 4728348 = enabled
fix 4723244 = enabled
fix 4554846 = enabled
fix 4175830 = enabled
fix 4722900 = enabled
fix 5094217 = enabled
fix 4904890 = enabled
fix 4483286 = disabled
fix 4969880 = disabled
fix 4711525 = enabled
fix 4717546 = enabled
fix 4904838 = enabled
fix 5005866 = enabled
fix 4600710 = enabled
fix 5129233 = enabled
fix 5195882 = enabled
fix 5084239 = enabled
fix 4595987 = enabled
fix 4134994 = enabled
fix 5104624 = enabled
fix 4908162 = enabled
fix 5015557 = enabled
fix 5263572 = enabled
fix 4483240 = enabled
fix 5099909 = enabled
fix 5650477 = enabled
fix 4273361 = enabled
fix 5694984 = enabled
fix 5449488 = enabled
fix 5236908 = enabled
fix 5618040 = enabled
fix 5634346 = enabled
fix 5220356 = enabled
fix 5611962 = enabled
fix 5741121 = enabled
fix 5547058 = enabled
fix 5762598 = enabled
fix 5509293 = enabled
fix 5396162 = enabled
fix 5891471 = enabled
fix 4872602 = disabled
fix 5882954 = enabled
fix 5884780 = enabled
fix 5680702 = enabled
fix 5240607 = enabled
fix 4924149 = enabled
fix 4752814 = enabled
fix 4583239 = enabled
fix 5949981 = enabled
fix 5096560 = enabled
fix 5838613 = enabled
fix 5482831 = enabled
fix 5624216 = enabled
fix 5976822 = enabled
fix 5741044 = enabled
fix 5385629 = enabled
fix 5705630 = disabled
fix 5483301 = enabled
fix 6122894 = enabled
fix 5842686 = disabled
fix 6006300 = disabled
fix 6070954 = enabled
fix 2492766 = enabled
fix 6042205 = enabled
fix 5302124 = enabled
fix 6051211 = enabled
fix 5620485 = enabled
fix 4545802 = enabled
fix 4716096 = enabled
fix 5259048 = enabled
fix 6163564 = enabled
fix 6082745 = enabled
fix 5944076 = enabled
fix 4878299 = enabled
fix 5288623 = enabled
fix 5570494 = enabled
fix 5387148 = enabled
fix 4605810 = enabled
fix 4704779 = enabled
fix 5547895 = enabled
fix 6188881 = enabled
fix 5872956 = enabled
fix 4708389 = enabled
fix 3151991 = enabled
fix 3426050 = enabled
fix 599680 = enabled
fix 5505157 = enabled
fix 5996801 = enabled
fix 5765456 = 0
fix 6494943 = enabled
fix 6251917 = enabled
fix 6087237 = enabled
fix 6239971 = enabled
fix 6062266 = enabled
fix 5520732 = 0
fix 6151963 = enabled
fix 4567767 = enabled
fix 6007259 = enabled
fix 6694548 = enabled
Query Block Registry:
*********************
SEL$1 0xcbff84c0 (PARSER) [FINAL]
Optimizer State Dump: call(in-use=29056, alloc=65448), compile(in-use=73312, alloc=122328)
扫一扫
114
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
