10053事件初探.TXT
pl/sql developer中執行以下語句，用於分析10053事件：
set time on
alter session set events '10053 trace name context forever, level 12';
select job,dname from dept,emp where dept.deptno=emp.deptno;
update emp set ename=ename||'a';
delete from emp where empno in (select empno from dept where dept.dname='RESEARCH');
commit;
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7876, 'ADAMS', 'CLERK', 7788, to_date('23-05-1987', 'dd-mm-yyyy'), 1100, null, 20);
commit;
alter session set events '10053 trace name context off';
delete from emp where empno=7876;
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Dump file d:\admin\test\udump\test_ora_952.trc
Sun Jul 07 10:30:13 2013
ORACLE V10.2.0.1.0 - Production vsnsta=0
vsnsql=14 vsnxtr=3
Oracle Database 10g Release 10.2.0.1.0 - Production
Windows XP Version V5.1 Service Pack 2
CPU : 2 - type 586
Process Affinity : 0x00000000
Memory (Avail/Total): Ph:900M/2047M, Ph+PgF:2377M/3939M, VA:1242M/2047M
Instance name: test
Redo thread mounted by this instance: 1
Oracle process number: 21
Windows thread id: 952, image: ORACLE.EXE (SHAD)
*** ACTION NAME:(Command Window - New) 2013-07-07 10:30:13.968
*** MODULE NAME:(PL/SQL Developer) 2013-07-07 10:30:13.968
*** SERVICE NAME:(test) 2013-07-07 10:30:13.968
*** SESSION ID:(159.681) 2013-07-07 10:30:13.968
Registered qb: SEL$1 0x9d5a5e0 (PARSER)
signature (): qb_name=SEL$1 nbfros=2 flg=0
fro(0): flg=4 bjn=51149 hint_alias="DEPT"@"SEL$1"
fro(1): flg=4 bjn=51151 hint_alias="EMP"@"SEL$1"
**************************
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 4hrmnf43pxzkq.
CVM: Considering view merge in query block SEL$1 (#0)
Query block (09D5A5E0) before join elimination:
SQL:******* UNPARSED QUERY IS *******
SELECT "EMP"."JOB" "JOB","DEPT"."DNAME" "DNAME" FROM "SCOTT"."DEPT" "DEPT","SCOTT"."EMP" "EMP" WHERE "DEPT"."DEPTNO"="EMP"."DEPTNO"
Query block (09D5A5E0) unchanged
CBQT: Validity checks failed for 4hrmnf43pxzkq.
***************
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) :
"DEPT"."DEPTNO"="EMP"."DEPTNO"
kkogcp: try to generate transitive predicate from check constraints for SEL$1 (#0)
predicates with check contraints: "DEPT"."DEPTNO"="EMP"."DEPTNO"
after transitive predicate generation: "DEPT"."DEPTNO"="EMP"."DEPTNO"
finally: "DEPT"."DEPTNO"="EMP"."DEPTNO"
apadrv-start: call(in-use=284, alloc=0), compile(in-use=33952, alloc=0)
kkoqbc-start
: call(in-use=288, alloc=0), compile(in-use=34736, alloc=0)
******************************************
Current SQL statement for this session:
select job,dname from dept,emp where dept.deptno=emp.deptno
*******************************************
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
******************************
*************************************
PARAMETERS WITH DEFAULT VALUES
******************************
--該部分內容是所有的語句執行相關的引數，篇幅較大，後面再出現類似資訊時，用省略號代替。
optimizer_mode_hinted = false
optimizer_features_hinted = 0.0.0
parallel_execution_enabled = false
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.1
_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 = 16
_optimizer_max_permutations = 2000
pga_aggregate_target = 198656 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 = 198 KB
_smm_max_size = 39731 KB
_smm_px_max_size = 99328 KB
_cpu_to_io = 0
_optimizer_undo_cost_change = 10.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
_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
_gby_hash_aggregation_enabled = true
***************************************
PARAMETERS IN OPT_PARAM HINT
****************************
***************************************
Column Usage Monitoring is ON: tracking level = 1
***************************************
****************
QUERY BLOCK TEXT
****************
select job,dname from dept,emp where dept.deptno=emp.deptno
*********************
QUERY BLOCK SIGNATURE
*********************
qb name was generated
signature (optimizer): qb_name=SEL$1 nbfros=2 flg=0
fro(0): flg=0 bjn=51149 hint_alias="DEPT"@"SEL$1"
fro(1): flg=0 bjn=51151 hint_alias="EMP"@"SEL$1"
*****************************
SYSTEM STATISTICS INFORMATION
*****************************
Using NOWORKLOAD Stats
CPUSPEED: 485 millions instruction/sec
IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
IOSEEKTIM: 10 milliseconds (default is 10)
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: EMP Alias: EMP
#Rows: 7 #Blks: 5 AvgRowLen: 35.00
Column (#8): DEPTNO(NUMBER)
AvgLen: 3.00 NDV: 2 Nulls: 0 Density: 0.071429 Min: 10 Max: 20
Histogram: Freq #Bkts: 2 UncompBkts: 7 EndPtVals: 2
Index Stats::
Index: IDX_EMP2 Col#: 6
LVLS: 0 #LB: 1 #DK: 7 LB/K: 1.00 DB/K: 1.00 CLUF: 1.00
Index: PK_EMP Col#: 1
LVLS: 0 #LB: 1 #DK: 7 LB/K: 1.00 DB/K: 1.00 CLUF: 1.00
***********************
Table Stats::
Table: DEPT Alias: DEPT
#Rows: 4 #Blks: 5 AvgRowLen: 20.00
Column (#1): DEPTNO(NUMBER)
AvgLen: 3.00 NDV: 4 Nulls: 0 Density: 0.25 Min: 10 Max: 40
Index Stats::
Index: PK_DEPT Col#: 1
LVLS: 0 #LB: 1 #DK: 4 LB/K: 1.00 DB/K: 1.00 CLUF: 1.00
***************************************
SINGLE TABLE ACCESS PATH
Table: DEPT Alias: DEPT
Card: Original: 4 Rounded: 4 Computed: 4.00 Non Adjusted: 4.00
Access Path: TableScan
Cost: 3.01 Resp: 3.01 Degree: 0
Cost_io: 3.00 Cost_cpu: 36287
Resp_io: 3.00 Resp_cpu: 36287
Best:: AccessPath: TableScan
Cost: 3.01 Degree: 1 Resp: 3.01 Card: 4.00 Bytes: 0
***************************************
SINGLE TABLE ACCESS PATH
Table: EMP Alias: EMP
Card: Original: 7 Rounded: 7 Computed: 7.00 Non Adjusted: 7.00
Access Path: TableScan
Cost: 3.01 Resp: 3.01 Degree: 0
Cost_io: 3.00 Cost_cpu: 37637
Resp_io: 3.00 Resp_cpu: 37637
Best:: AccessPath: TableScan
Cost: 3.01 Degree: 1 Resp: 3.01 Card: 7.00 Bytes: 0
***************************************
OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***************************************
Considering cardinality-based initial join order.
***********************
Join order[1]: DEPT[DEPT]#0 EMP[EMP]#1
***************
Now joining: EMP[EMP]#1
***************
NL Join
Outer table: Card: 4.00 Cost: 3.01 Resp: 3.01 Degree: 1 Bytes: 13
Inner table: EMP Alias: EMP
Access Path: TableScan
NL Join: Cost: 9.03 Resp: 9.03 Degree: 0
Cost_io: 9.00 Cost_cpu: 186836
Resp_io: 9.00 Resp_cpu: 186836
Best NL cost: 9.03
resc: 9.03 resc_io: 9.00 resc_cpu: 186836
resp: 9.03 resp_io: 9.00 resp_cpu: 186836
Join Card: 7.00 = outer (4.00) * inner (7.00) * sel (0.25)
Join Card - Rounded: 7 Computed: 7.00
SM Join
Outer table:
resc: 3.01 card 4.00 bytes: 13 deg: 1 resp: 3.01
Inner table: EMP Alias: EMP
resc: 3.01 card: 7.00 bytes: 11 deg: 1 resp: 3.01
using dmeth: 2 #groups: 1
SORT resource Sort statistics
Sort width: 231 Area size: 202752 Max Area size: 40684544
Degree: 1
Blocks to Sort: 1 Row size: 25 Total Rows: 4
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 5820060
Total Temp space used: 0
SORT resource Sort statistics
Sort width: 231 Area size: 202752 Max Area size: 40684544
Degree: 1
Blocks to Sort: 1 Row size: 23 Total Rows: 7
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 5820585
Total Temp space used: 0
SM join: Resc: 8.01 Resp: 8.01 [multiMatchCost=0.00]
SM cost: 8.01
resc: 8.01 resc_io: 6.00 resc_cpu: 11714569
resp: 8.01 resp_io: 6.00 resp_cpu: 11714569
SM Join (with index on outer)
Access Path: index (FullScan)
Index: PK_DEPT
resc_io: 2.00 resc_cpu: 15723
ix_sel: 1 ix_sel_with_filters: 1
Cost: 2.00 Resp: 2.00 Degree: 1
Outer table:
resc: 2.00 card 4.00 bytes: 13 deg: 1 resp: 2.00
Inner table: EMP Alias: EMP
resc: 3.01 card: 7.00 bytes: 11 deg: 1 resp: 3.01
using dmeth: 2 #groups: 1
SORT resource Sort statistics
Sort width: 231 Area size: 202752 Max Area size: 40684544
Degree: 1
Blocks to Sort: 1 Row size: 23 Total Rows: 7
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 5820585
Total Temp space used: 0
SM join: Resc: 6.01 Resp: 6.01 [multiMatchCost=0.00]
HA Join
Outer table:
resc: 3.01 card 4.00 bytes: 13 deg: 1 resp: 3.01
Inner table: EMP Alias: EMP
resc: 3.01 card: 7.00 bytes: 11 deg: 1 resp: 3.01
using dmeth: 2 #groups: 1
Cost per ptn: 0.50 #ptns: 1
hash_area: 0 (max=0) Hash join: Resc: 6.51 Resp: 6.51 [multiMatchCost=0.00]
HA cost: 6.51
resc: 6.51 resc_io: 6.00 resc_cpu: 2985074
resp: 6.51 resp_io: 6.00 resp_cpu: 2985074
Best:: JoinMethod: SortMerge
Cost: 6.01 Degree: 1 Resp: 6.01 Card: 7.00 Bytes: 24
***********************
Best so far: Table#: 0 cost: 2.0027 card: 4.0000 bytes: 52
Table#: 1 cost: 6.0093 card: 7.0000 bytes: 168
***********************
Join order[2]: EMP[EMP]#1 DEPT[DEPT]#0
***************
Now joining: DEPT[DEPT]#0
***************
NL Join
Outer table: Card: 7.00 Cost: 3.01 Resp: 3.01 Degree: 1 Bytes: 11
Inner table: DEPT Alias: DEPT
Access Path: TableScan
NL Join: Cost: 12.05 Resp: 12.05 Degree: 0
Cost_io: 12.00 Cost_cpu: 291648
Resp_io: 12.00 Resp_cpu: 291648
Access Path: index (UniqueScan)
Index: PK_DEPT
resc_io: 1.00 resc_cpu: 8341
ix_sel: 0.25 ix_sel_with_filters: 0.25
NL Join: Cost: 4.02 Resp: 4.02 Degree: 1
Cost_io: 4.00 Cost_cpu: 96027
Resp_io: 4.00 Resp_cpu: 96027
Access Path: index (AllEqUnique)
Index: PK_DEPT
resc_io: 1.00 resc_cpu: 8341
ix_sel: 0.25 ix_sel_with_filters: 0.25
NL Join: Cost: 4.02 Resp: 4.02 Degree: 1
Cost_io: 4.00 Cost_cpu: 96027
Resp_io: 4.00 Resp_cpu: 96027
Best NL cost: 4.02
resc: 4.02 resc_io: 4.00 resc_cpu: 96027
resp: 4.02 resp_io: 4.00 resp_cpu: 96027
Join Card: 7.00 = outer (7.00) * inner (4.00) * sel (0.25)
Join Card - Rounded: 7 Computed: 7.00
SM Join
Outer table:
resc: 3.01 card 7.00 bytes: 11 deg: 1 resp: 3.01
Inner table: DEPT Alias: DEPT
resc: 3.01 card: 4.00 bytes: 13 deg: 1 resp: 3.01
using dmeth: 2 #groups: 1
SORT resource Sort statistics
Sort width: 231 Area size: 202752 Max Area size: 40684544
Degree: 1
Blocks to Sort: 1 Row size: 23 Total Rows: 7
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 5820585
Total Temp space used: 0
SORT resource Sort statistics
Sort width: 231 Area size: 202752 Max Area size: 40684544
Degree: 1
Blocks to Sort: 1 Row size: 25 Total Rows: 4
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 5820060
Total Temp space used: 0
SM join: Resc: 8.01 Resp: 8.01 [multiMatchCost=0.00]
SM cost: 8.01
resc: 8.01 resc_io: 6.00 resc_cpu: 11714569
resp: 8.01 resp_io: 6.00 resp_cpu: 11714569
HA Join
Outer table:
resc: 3.01 card 7.00 bytes: 11 deg: 1 resp: 3.01
Inner table: DEPT Alias: DEPT
resc: 3.01 card: 4.00 bytes: 13 deg: 1 resp: 3.01
using dmeth: 2 #groups: 1
Cost per ptn: 0.50 #ptns: 1
hash_area: 0 (max=0) Hash join: Resc: 6.51 Resp: 6.51 [multiMatchCost=0.00]
HA cost: 6.51
resc: 6.51 resc_io: 6.00 resc_cpu: 2985524
resp: 6.51 resp_io: 6.00 resp_cpu: 2985524
Best:: JoinMethod: NestedLoop
Cost: 4.02 Degree: 1 Resp: 4.02 Card: 7.00 Bytes: 24
***********************
Best so far: Table#: 1 cost: 3.0065 card: 7.0000 bytes: 77
Table#: 0 cost: 4.0165 card: 7.0000 bytes: 168
(newjo-stop-1) k:0, spcnt:0, perm:2, maxperm:2000
*********************************
Number of join permutations tried: 2
*********************************
(newjo-save) [0 1 ]
Final - All Rows Plan: Best join order: 2
Cost: 4.0165 Degree: 1 Card: 7.0000 Bytes: 168
Resc: 4.0165 Resc_io: 4.0000 Resc_cpu: 96027
Resp: 4.0165 Resp_io: 4.0000 Resc_cpu: 96027
kkoipt: Query block SEL$1 (#0)
******* UNPARSED QUERY IS *******
SELECT "EMP"."JOB" "JOB","DEPT"."DNAME" "DNAME" FROM "SCOTT"."DEPT" "DEPT","SCOTT"."EMP" "EMP" WHERE "DEPT"."DEPTNO"="EMP"."DEPTNO"
kkoqbc-end
: call(in-use=31192, alloc=0), compile(in-use=35732, alloc=0)
apadrv-end: call(in-use=31192, alloc=0), compile(in-use=36320, alloc=0)
sql_id=4hrmnf43pxzkq.
Current SQL statement for this session:
select job,dname from dept,emp where dept.deptno=emp.deptno
============
Plan Table
============
------------------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
------------------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 4 | |
| 1 | NESTED LOOPS | | 7 | 168 | 4 | 00:00:01 |
| 2 | TABLE ACCESS FULL | EMP | 7 | 77 | 3 | 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 13 | 1 | 00:00:01 |
| 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 | |
------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
4 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
Content of other_xml column
===========================
db_version : 10.2.0.1
parse_schema : SCOTT
plan_hash : 351108634
Outline Data:
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "EMP"@"SEL$1")
INDEX(@"SEL$1" "DEPT"@"SEL$1" ("DEPT"."DEPTNO"))
LEADING(@"SEL$1" "EMP"@"SEL$1" "DEPT"@"SEL$1")
USE_NL(@"SEL$1" "DEPT"@"SEL$1")
END_OUTLINE_DATA
*/
Optimizer environment:
optimizer_mode_hinted = false
optimizer_features_hinted = 0.0.0
parallel_execution_enabled = false
……
……
_optimizer_complex_pred_selectivity = true
_gby_hash_aggregation_enabled = true
Query Block Registry:
*********************
SEL$1 0x9d5a5e0 (PARSER) [FINAL]
Optimizer State Dump: call(in-use=36060, alloc=0), compile(in-use=55304, alloc=0)
Registered qb: UPD$1 0x9d5ac90 (PARSER)
signature (): qb_name=UPD$1 nbfros=1 flg=0
fro(0): flg=4 bjn=51151 hint_alias="EMP"@"UPD$1"
**************************
Predicate Move-Around (PM)
**************************
PM: Considering predicate move-around in UPD$1 (#0).
PM: Checking validity of predicate move-around in UPD$1 (#0).
CBQT: Validity checks failed for 22ca3x3fup9gu.
CVM: Considering view merge in query block UPD$1 (#0)
CBQT: Validity checks failed for 22ca3x3fup9gu.
***************
Subquery Unnest
***************
SU: Considering subquery unnesting in query block UPD$1 (#0)
*************************
Set-Join Conversion (SJC)
*************************
SJC: Considering set-join conversion in UPD$1 (#0).
**************************
Predicate Move-Around (PM)
**************************
PM: Considering predicate move-around in UPD$1 (#0).
PM: Checking validity of predicate move-around in UPD$1 (#0).
PM: PM bypassed: Outer query contains no views.
FPD: Considering simple filter push in UPD$1 (#0)
FPD: Current where clause predicates in UPD$1 (#0) :
apadrv-start: call(in-use=220, alloc=0), compile(in-use=31792, alloc=0)
kkoqbc-start
: call(in-use=220, alloc=0), compile(in-use=32396, alloc=0)
******************************************
Current SQL statement for this session:
update emp set ename=ename||'a'
*******************************************
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
******************************
*************************************
PARAMETERS WITH DEFAULT VALUES
******************************
optimizer_mode_hinted = false
optimizer_features_hinted = 0.0.0
……
……
_optimizer_complex_pred_selectivity = true
_gby_hash_aggregation_enabled = true
***************************************
PARAMETERS IN OPT_PARAM HINT
****************************
***************************************
Column Usage Monitoring is ON: tracking level = 1
***************************************
****************
QUERY BLOCK TEXT
****************
Not available.
*********************
QUERY BLOCK SIGNATURE
*********************
qb name was generated
signature (optimizer): qb_name=UPD$1 nbfros=1 flg=0
fro(0): flg=0 bjn=51151 hint_alias="EMP"@"UPD$1"
*****************************
SYSTEM STATISTICS INFORMATION
*****************************
Using NOWORKLOAD Stats
CPUSPEED: 485 millions instruction/sec
IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
IOSEEKTIM: 10 milliseconds (default is 10)
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: EMP Alias: EMP
#Rows: 7 #Blks: 5 AvgRowLen: 35.00
Index Stats::
Index: IDX_EMP2 Col#: 6
LVLS: 0 #LB: 1 #DK: 7 LB/K: 1.00 DB/K: 1.00 CLUF: 1.00
Index: PK_EMP Col#: 1
LVLS: 0 #LB: 1 #DK: 7 LB/K: 1.00 DB/K: 1.00 CLUF: 1.00
***************************************
SINGLE TABLE ACCESS PATH
Table: EMP Alias: EMP
Card: Original: 7 Rounded: 7 Computed: 7.00 Non Adjusted: 7.00
Access Path: TableScan
Cost: 3.01 Resp: 3.01 Degree: 0
Cost_io: 3.00 Cost_cpu: 36797
Resp_io: 3.00 Resp_cpu: 36797
Best:: AccessPath: TableScan
Cost: 3.01 Degree: 1 Resp: 3.01 Card: 7.00 Bytes: 0
***************************************
OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***************************************
Considering cardinality-based initial join order.
***********************
Join order[1]: EMP[EMP]#0
***********************
Best so far: Table#: 0 cost: 3.0063 card: 7.0000 bytes: 42
(newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:80000
*********************************
Number of join permutations tried: 1
*********************************
Final - All Rows Plan: Best join order: 1
Cost: 3.0063 Degree: 1 Card: 7.0000 Bytes: 42
Resc: 3.0063 Resc_io: 3.0000 Resc_cpu: 36797
Resp: 3.0063 Resp_io: 3.0000 Resc_cpu: 36797
kkoipt: Query block UPD$1 (#0)
******* UNPARSED QUERY IS *******
SELECT 0 FROM "SCOTT"."EMP" "EMP"
kkoqbc-end
: call(in-use=15332, alloc=0), compile(in-use=32832, alloc=0)
apadrv-end: call(in-use=15332, alloc=0), compile(in-use=33392, alloc=0)
sql_id=22ca3x3fup9gu.
Current SQL statement for this session:
update emp set ename=ename||'a'
============
Plan Table
============
--------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
--------------------------------------+-----------------------------------+
| 0 | UPDATE STATEMENT | | | | 3 | |
| 1 | UPDATE | EMP | | | | |
| 2 | TABLE ACCESS FULL | EMP | 7 | 42 | 3 | 00:00:01 |
--------------------------------------+-----------------------------------+
Predicate Information:
----------------------
Content of other_xml column
===========================
db_version : 10.2.0.1
parse_schema : SCOTT
plan_hash : 1494045816
Outline Data:
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"UPD$1")
FULL(@"UPD$1" "EMP"@"UPD$1")
END_OUTLINE_DATA
*/
Optimizer environment:
optimizer_mode_hinted = false
optimizer_features_hinted = 0.0.0
……
……
_optimizer_star_tran_in_with_clause = true
_optimizer_complex_pred_selectivity = true
_gby_hash_aggregation_enabled = true
Query Block Registry:
*********************
UPD$1 0x9d5ac90 (PARSER) [FINAL]
Optimizer State Dump: call(in-use=16808, alloc=0), compile(in-use=49920, alloc=0)
Registered qb: DEL$1 0x9d5ac90 (PARSER)
signature (): qb_name=DEL$1 nbfros=1 flg=0
fro(0): flg=4 bjn=51151 hint_alias="EMP"@"DEL$1"
Registered qb: SEL$1 0x9d5a420 (PARSER)
signature (): qb_name=SEL$1 nbfros=1 flg=0
fro(0): flg=4 bjn=51149 hint_alias="DEPT"@"SEL$1"
**************************
Predicate Move-Around (PM)
**************************
PM: Considering predicate move-around in DEL$1 (#0).
PM: Checking validity of predicate move-around in DEL$1 (#0).
CBQT: Validity checks passed for 63fxar6x9jm64.
apadrv-start: call(in-use=340, alloc=0), compile(in-use=34512, alloc=0)
******************************************
Current SQL statement for this session:
delete from emp where empno in (select empno from dept where dept.dname='RESEARCH')
*******************************************
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
******************************
*************************************
PARAMETERS WITH DEFAULT VALUES
******************************
optimizer_mode_hinted = false
optimizer_features_hinted = 0.0.0
……
……
_optimizer_star_tran_in_with_clause = true
_optimizer_complex_pred_selectivity = true
_gby_hash_aggregation_enabled = true
***************************************
PARAMETERS IN OPT_PARAM HINT
****************************
***************************************
Column Usage Monitoring is ON: tracking level = 1
***************************************
********************************
COST-BASED QUERY TRANSFORMATIONS
********************************
FPD: Considering simple filter push (pre rewrite) in DEL$1 (#0)
FPD: Current where clause predicates in DEL$1 (#0) :
"EMP"."EMPNO"=ANY (SELECT "EMP"."EMPNO" FROM "DEPT" "DEPT")
Registered qb: DEL$1 0x9d551cc (COPY DEL$1)
signature(): NULL
Registered qb: SEL$1 0x9d524c4 (COPY SEL$1)
signature(): NULL
*****************************
Cost-Based Subquery Unnesting
*****************************
SU: No subqueries to consider in query block SEL$1 (#2).
SU: Considering subquery unnesting in query block DEL$1 (#1)
SU: Performing unnesting that does not require costing.
SU: Considering subquery unnest on DEL$1 (#1).
SU: Checking validity of unnesting subquery SEL$1 (#2)
SU: SU bypassed: Invalid correlated predicates.
SU: Validity checks failed.
*******************************
Cost-Based Complex View Merging
*******************************
CVM: Finding query blocks in DEL$1 (#1) that are valid to merge.
*************************
Set-Join Conversion (SJC)
*************************
SJC: Considering set-join conversion in DEL$1 (#1).
SJC: Considering set-join conversion in SEL$1 (#2).
**************************
Predicate Move-Around (PM)
**************************
PM: Considering predicate move-around in DEL$1 (#1).
PM: Checking validity of predicate move-around in DEL$1 (#1).
PM: PM bypassed: Outer query contains no views.
***********************************
Cost-Based Filter Predicate Pull-Up
***********************************
***********************************
Cost-Based Join Predicate Push-down
***********************************
JPPD: Checking validity of push-down in query block SEL$1 (#2)
JPPD: No view found to push predicate into.
JPPD: Checking validity of push-down in query block DEL$1 (#1)
JPPD: No view found to push predicate into.
JPPD: Applying transformation directives
JPPD: Checking validity of push-down in query block SEL$1 (#2)
JPPD: No view found to push predicate into.
JPPD: Checking validity of push-down in query block DEL$1 (#1)
JPPD: No view found to push predicate into.
FPD: Considering simple filter push in DEL$1 (#1)
FPD: Current where clause predicates in DEL$1 (#1) :
EXISTS (SELECT 0 FROM "DEPT" "DEPT")
FPD: Considering simple filter push in SEL$1 (#2)
FPD: Current where clause predicates in SEL$1 (#2) :
:B1=:B2 AND "DEPT"."DNAME"='RESEARCH'
kkogcp: try to generate transitive predicate from check constraints for SEL$1 (#2)
predicates with check contraints: :B1=:B2 AND "DEPT"."DNAME"='RESEARCH'
after transitive predicate generation: :B1=:B2 AND "DEPT"."DNAME"='RESEARCH'
finally: :B1=:B2 AND "DEPT"."DNAME"='RESEARCH'
kkogcp: try to generate transitive predicate from check constraints for DEL$1 (#1)
predicates with check contraints: EXISTS (SELECT 0 FROM "DEPT" "DEPT")
after transitive predicate generation: EXISTS (SELECT 0 FROM "DEPT" "DEPT")
finally: EXISTS (SELECT 0 FROM "DEPT" "DEPT")
kkoqbc-start
: call(in-use=9300, alloc=0), compile(in-use=55348, alloc=0)
****************
QUERY BLOCK TEXT
****************
select empno from dept where dept.dname='RESEARCH'
*********************
QUERY BLOCK SIGNATURE
*********************
qb name was generated
signature (optimizer): qb_name=SEL$1 nbfros=1 flg=0
fro(0): flg=0 bjn=51149 hint_alias="DEPT"@"SEL$1"
*****************************
SYSTEM STATISTICS INFORMATION
*****************************
Using NOWORKLOAD Stats
CPUSPEED: 485 millions instruction/sec
IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
IOSEEKTIM: 10 milliseconds (default is 10)
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: DEPT Alias: DEPT
#Rows: 4 #Blks: 5 AvgRowLen: 20.00
Index Stats::
Index: PK_DEPT Col#: 1
LVLS: 0 #LB: 1 #DK: 4 LB/K: 1.00 DB/K: 1.00 CLUF: 1.00
***************************************
SINGLE TABLE ACCESS PATH
Column (#2): DNAME(VARCHAR2)
AvgLen: 10.00 NDV: 4 Nulls: 0 Density: 0.25
Table: DEPT Alias: DEPT
Card: Original: 4 Rounded: 1 Computed: 1.00 Non Adjusted: 1.00
Access Path: TableScan
Cost: 3.01 Resp: 3.01 Degree: 0
Cost_io: 3.00 Cost_cpu: 36487
Resp_io: 3.00 Resp_cpu: 36487
Best:: AccessPath: TableScan
Cost: 3.01 Degree: 1 Resp: 3.01 Card: 1.00 Bytes: 0
***************************************
OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***************************************
Considering cardinality-based initial join order.
***********************
Join order[1]: DEPT[DEPT]#0
***********************
Best so far: Table#: 0 cost: 3.0063 card: 1.0000 bytes: 10
(newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:80000
*********************************
Number of join permutations tried: 1
*********************************
Final - First Rows Plan: Best join order: 1
Cost: 3.0063 Degree: 1 Card: 1.0000 Bytes: 10
Resc: 3.0063 Resc_io: 3.0000 Resc_cpu: 36487
Resp: 3.0063 Resp_io: 3.0000 Resc_cpu: 36487
kkoipt: Query block SEL$1 (#2)
******* UNPARSED QUERY IS *******
SELECT 0 FROM "SCOTT"."DEPT" "DEPT" WHERE "SYS_ALIAS_1"."EMPNO"="SYS_ALIAS_1"."EMPNO" AND "DEPT"."DNAME"='RESEARCH'
kkoqbc-end
: call(in-use=24704, alloc=0), compile(in-use=55756, alloc=0)
kkoqbc-start
: call(in-use=24704, alloc=0), compile(in-use=55800, alloc=0)
****************
QUERY BLOCK TEXT
****************
Not available.
*********************
QUERY BLOCK SIGNATURE
*********************
qb name was generated
signature (optimizer): qb_name=DEL$1 nbfros=1 flg=0
fro(0): flg=0 bjn=51151 hint_alias="EMP"@"DEL$1"
*****************************
SYSTEM STATISTICS INFORMATION
*****************************
Using NOWORKLOAD Stats
CPUSPEED: 485 millions instruction/sec
IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
IOSEEKTIM: 10 milliseconds (default is 10)
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: EMP Alias: SYS_ALIAS_1
#Rows: 7 #Blks: 5 AvgRowLen: 35.00
Index Stats::
Index: IDX_EMP2 Col#: 6
LVLS: 0 #LB: 1 #DK: 7 LB/K: 1.00 DB/K: 1.00 CLUF: 1.00
Index: PK_EMP Col#: 1
LVLS: 0 #LB: 1 #DK: 7 LB/K: 1.00 DB/K: 1.00 CLUF: 1.00
***************************************
SINGLE TABLE ACCESS PATH
Column (#1): EMPNO(NUMBER)
AvgLen: 4.00 NDV: 7 Nulls: 0 Density: 0.14286 Min: 7369 Max: 7934
Table: EMP Alias: SYS_ALIAS_1
Card: Original: 7 Rounded: 7 Computed: 7.00 Non Adjusted: 7.00
Access Path: TableScan
Cost: 3.01 Resp: 3.01 Degree: 0
Cost_io: 3.00 Cost_cpu: 37777
Resp_io: 3.00 Resp_cpu: 37777
Best:: AccessPath: TableScan
Cost: 3.01 Degree: 1 Resp: 3.01 Card: 7.00 Bytes: 0
***************************************
OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***************************************
Considering cardinality-based initial join order.
***********************
Join order[1]: EMP[SYS_ALIAS_1]#0
***********************
Best so far: Table#: 0 cost: 3.0065 card: 7.0000 bytes: 77
(newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:80000
*********************************
Number of join permutations tried: 1
*********************************
Final adjusted join cardinality: 1, sq. fil. factor: 7
Final - All Rows Plan: Best join order: 1
Cost: 13.5284 Degree: 1 Card: 1.0000 Bytes: 77
Resc: 13.5284 Resc_io: 13.5000 Resc_cpu: 165482
Resp: 13.5284 Resp_io: 13.5000 Resc_cpu: 165482
kkoipt: Query block DEL$1 (#1)
******* UNPARSED QUERY IS *******
SELECT 0 FROM "SCOTT"."EMP" "SYS_ALIAS_1" WHERE EXISTS (SELECT 0 FROM "SCOTT"."DEPT" "DEPT" WHERE "SYS_ALIAS_1"."EMPNO"="SYS_ALIAS_1"."EMPNO" AND "DEPT"."DNAME"='RESEARCH')
kkoqbc-end
: call(in-use=43260, alloc=0), compile(in-use=56256, alloc=0)
apadrv-end: call(in-use=43260, alloc=0), compile(in-use=57036, alloc=0)
sql_id=63fxar6x9jm64.
Current SQL statement for this session:
delete from emp where empno in (select empno from dept where dept.dname='RESEARCH')
============
Plan Table
============
----------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
----------------------------------------+-----------------------------------+
| 0 | DELETE STATEMENT | | | | 14 | |
| 1 | DELETE | EMP | | | | |
| 2 | FILTER | | | | | |
| 3 | TABLE ACCESS FULL | EMP | 7 | 77 | 3 | 00:00:01 |
| 4 | FILTER | | | | | |
| 5 | TABLE ACCESS FULL | DEPT | 1 | 10 | 3 | 00:00:01 |
----------------------------------------+-----------------------------------+
Predicate Information:
----------------------
2 - filter( IS NOT NULL)
4 - filter(:B1=:B2)
5 - filter("DEPT"."DNAME"='RESEARCH')
Content of other_xml column
===========================
db_version : 10.2.0.1
parse_schema : SCOTT
plan_hash : 3342411297
Outline Data:
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
OUTLINE_LEAF(@"DEL$1")
OUTLINE(@"SEL$1")
OUTLINE(@"DEL$1")
FULL(@"DEL$1" "EMP"@"DEL$1")
FULL(@"SEL$1" "DEPT"@"SEL$1")
END_OUTLINE_DATA
*/
Optimizer environment:
optimizer_mode_hinted = false
optimizer_features_hinted = 0.0.0
……
……
_optimizer_star_tran_in_with_clause = true
_optimizer_complex_pred_selectivity = true
_gby_hash_aggregation_enabled = true
Query Block Registry:
*********************
SEL$1 0x9d5a420 (PARSER) [FINAL]
DEL$1 0x9d5ac90 (PARSER) [FINAL]
Optimizer State Dump: call(in-use=48012, alloc=0), compile(in-use=75744, alloc=0)
Registered qb: MISC$1 0x9d5ac90 (PARSER)
signature(): NULL
**************************
Predicate Move-Around (PM)
**************************
PM: Considering predicate move-around in MISC$1 (#0).
PM: Checking validity of predicate move-around in MISC$1 (#0).
CBQT: Validity checks failed for bv2g23x77u6nv.
CVM: Considering view merge in query block MISC$1 (#0)
CBQT: Validity checks failed for bv2g23x77u6nv.
***************
Subquery Unnest
***************
SU: Considering subquery unnesting in query block MISC$1 (#0)
*************************
Set-Join Conversion (SJC)
*************************
SJC: Considering set-join conversion in MISC$1 (#0).
**************************
Predicate Move-Around (PM)
**************************
PM: Considering predicate move-around in MISC$1 (#0).
PM: Checking validity of predicate move-around in MISC$1 (#0).
PM: PM bypassed: Outer query contains no views.
FPD: Considering simple filter push in MISC$1 (#0)
FPD: Current where clause predicates in MISC$1 (#0) :
apadrv-start: call(in-use=30788, alloc=0), compile(in-use=29724, alloc=0)
sql_id=bv2g23x77u6nv.
Current SQL statement for this session:
alter session set events '10053 trace name context off'
============
Plan Table
============
---------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
---------------------------------+-----------------------------------+
| 0 | DDL STATEMENT | | | | 0 | |
---------------------------------+-----------------------------------+
Predicate Information:
----------------------
Optimizer environment:
optimizer_mode_hinted = false
optimizer_features_hinted = 0.0.0
……
……
_optimizer_complex_pred_selectivity = true
_gby_hash_aggregation_enabled = true
Query Block Registry:
*********************
MISC$1 0x9d5ac90 (PARSER) [FINAL]
Optimizer State Dump: call(in-use=84552, alloc=0), compile(in-use=38964, alloc=0)
我們知道，oracle在拿到我們提供的語句後，並不是直接執行。
而是首先進行語法、語義的分析，然後查詢許可權，
再通過查詢優化器產生儘可能多的備選執行計劃，並對其進行成本比較，選擇最優的執行計劃。
查詢優化器對於局進行查詢轉換，包括：
檢視合併
謂詞前推
子查詢解巢狀
物化檢視的改寫
從統計資訊中獲得到：
可選擇性
集的勢
成本
然後對應不同的連線順序（SMJ、HJ、NL……），綜合得到最優的執行計劃。
從10053事件跟蹤檔案中可以看到，在我們輸入一個語句後，oracle得到了哪些備選的執行計劃，
各個執行計劃的成本如何，以及最終選擇了哪個執行計劃。
當oracle選擇了錯誤的執行計劃時（這是我們經常說到的，其實絕大多數情況下是我們為其提供的統計資訊不夠完整、“新鮮”），
應該先通過執行計劃\10046分析出那幾個步驟比較可疑，然後藉助10053事件去探查內部原因。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26451536/viewspace-765685/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 10053事件事件
- 【10053 事件】10053事件的跟蹤檔案解析事件
- Oracle 10053 事件Oracle事件
- ORACLEE 10053 事件Oracle事件
- Oracle 10053事件Oracle事件
- 解析10053事件事件
- 深入解析10053事件事件
- 10046 事件 與 10053 事件事件
- 【最佳化】10053事件事件
- ORACLE 深入解析10053事件Oracle事件
- 深入解析10053事件(ZT)事件
- oracle 10053診斷事件Oracle事件
- oracle診斷事件及深入解析10053事件Oracle事件
- 10053事件處理步驟事件
- oracle中的10053事件命令Oracle事件
- 【原創】ORACLE 深入解析10053事件Oracle事件
- JS事件迴圈EventLoop初探JS事件OOP
- 透過10053事件分析一個SQL執行計劃事件SQL
- 使用10053事件跟蹤CBO優化器決策（上）事件優化
- 使用10053事件跟蹤CBO優化器決策（下）事件優化
- 藉助10053跟蹤事件理解SQL執行過程事件SQL
- Apache ZooKeeper - 事件監聽機制初探Apache事件
- [20111220]Capturing 10053 trace files.txtAPT
- C#視窗事件處理初探 (轉)C#事件
- [VSIX]build事件初探之執行順序UI事件
- histogram與10053(zt)Histogram
- 【效能】解讀10053檔案
- Index Range Scan成本與10053Index
- django中出現 錯誤 Errno 10053Django
- Index Range Scan成本 Histogram 和 10053IndexHistogram
- [20191203]大量resmgrcpu quantum等待事件.txt事件
- [20130409]Data file init write等待事件.txt事件
- sql_trace、10046、10053、tkprofSQL
- [20220518]enq FU - contention等待事件.txtENQ事件
- [20161228]奇怪log file sync等待事件.txt事件
- [20160720]檢視了解oracle跟蹤事件.txtOracle事件
- [20130904]等待事件wait for a undo record.txt事件AI
- [20150520]使用gdb檢視等待事件.txt事件