ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 10053事件初探.TXT

10053事件初探.TXT

原创 Linux操作系统 作者:to_be_dba 时间:2013-07-07 12:07:15 0 删除 编辑

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/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 日期格式的转换
请登录后发表评论 登录
全部评论

注册时间:2011-11-23

  • 博文量
    148
  • 访问量
    390666