获得执行计划方法五-10053事件

10053事件进行对优化器的跟踪，trace 文件将展示影响优化器说呢工程执行计划的各种参数。

10053事件有两个级别：
 Level 2：2级是1级的一个子集，它包含以下内容：

Column statistics 

Single Access Paths 

Join Costs  

Table Joins Considered

Join Methods Considered (NL/MS/HA)

Level 1： 1级比2级更详细，它包含2级的所有内容，在加如下内容：

Parameters used by the optimizer

Index statistics

开启10053事件
   ALTER SESSION SET EVENTS='10053 trace name context forever, level 1';
   ALTER SESSION SET EVENTS='10053 trace name context forever, level 2';
   
关闭10053事件

ALTER SESSION SET EVENTS '10053 trace name context off';

另一种写法

alter session set  "_optimizer_trace"=all;

alter session set  "_optimizer_trace"=none;

SQL>  ALTER SESSION SET EVENTS='10053 trace name context forever, level 1';

Session altered.

SQL> select * from scott.emp ;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7369 SMITH      CLERK           7902 17-DEC-80        800
        20

      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300
        30

      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500
        30


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7566 JONES      MANAGER         7839 02-APR-81       2975
        20

      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400
        30

      7698 BLAKE      MANAGER         7839 01-MAY-81       2850
        30


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7782 CLARK      MANAGER         7839 09-JUN-81       2450
        10

      7788 SCOTT      ANALYST         7566 19-APR-87       3000
        20

      7839 KING       PRESIDENT            17-NOV-81       5000
        10


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0
        30

      7876 ADAMS      CLERK           7788 23-MAY-87       1100
        20

      7900 JAMES      CLERK           7698 03-DEC-81        950
        30


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7902 FORD       ANALYST         7566 03-DEC-81       3000
        20

      7934 MILLER     CLERK           7782 23-JAN-82       1300
        10


14 rows selected.

SQL> ALTER SESSION SET EVENTS '10053 trace name context off';

Session altered.

SQL> select  d.value || '/' || lower(rtrim(i.instance, chr(0))) || '_ora_' || p.spid || '.trc' trace_file_name
  2     from (select p.spid
  3             from sys.v$mystat m, sys.v$session s, sys.v$process p
  4            where m.statistic# = 1
  5              and s.sid = m.sid
  6              and p.addr = s.paddr) p,
  7          (select t.instance
  8             from sys.v$thread t, sys.v$parameter v
  9            where v.name = 'thread'
 10              and (v.value = 0 or t.thread# = to_number(v.value))) i,
 11          (select value from sys.v$parameter where name = 'user_dump_dest') d
 12   /

TRACE_FILE_NAME
--------------------------------------------------------------------------------
/u01/oracle/admin/orcl/udump/orcl_ora_4359.trc

SQL> !
[oracle@sunblaze ~]$ cat /u01/oracle/admin/orcl/udump/orcl_ora_4359.trc
/u01/oracle/admin/orcl/udump/orcl_ora_4359.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /u01/oracle/product/OraHome
System name:    Linux
Node name:      sunblaze.com
Release:        2.6.9-42.ELsmp
Version:        #1 SMP Wed Jul 12 23:27:17 EDT 2006
Machine:        i686
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 18
Unix process pid: 4359, image: oracle@sunblaze.com (TNS V1-V3)

*** 2013-04-05 18:21:02.098
*** SERVICE NAME:(SYS$USERS) 2013-04-05 18:21:02.098
*** SESSION ID:(149.3) 2013-04-05 18:21:02.098
Registered qb: SEL$1 0xb72baae4 (PARSER)
  signature (): qb_name=SEL$1 nbfros=1 flg=0
    fro(0): flg=4 objn=51148 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 9mhzzknczbj6p.
CVM: Considering view merge in query block SEL$1 (#0)
CBQT: Validity checks failed for 9mhzzknczbj6p.
***************
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) :
         apadrv-start: call(in-use=172, alloc=16360), compile(in-use=34084, alloc=38000)
kkoqbc-start
            : call(in-use=172, alloc=16360), compile(in-use=34664, alloc=38000)
******************************************
Current SQL statement for this session:
select * from scott.emp
*******************************************
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          = true
  parallel_query_forced_dop           = 0
  parallel_dml_forced_dop             = 0
  parallel_ddl_forced_degree          = 0
  parallel_ddl_forced_instances       = 0
  _query_rewrite_fudge                = 90
  optimizer_features_enable           = 10.2.0.1
  _optimizer_search_limit             = 5
  cpu_count                           = 1
  active_instance_count               = 1
  parallel_threads_per_cpu            = 2
  hash_area_size                      = 131072
  bitmap_merge_area_size              = 1048576
  sort_area_size                      = 65536
  sort_area_retained_size             = 0
  _sort_elimination_cost_ratio        = 0
  _optimizer_block_size               = 8192
  _sort_multiblock_read_count         = 2
  _hash_multiblock_io_count           = 0
  _db_file_optimizer_read_count       = 16
  _optimizer_max_permutations         = 2000
  pga_aggregate_target                = 93184 KB
  _pga_max_size                       = 204800 KB
  _query_rewrite_maxdisjunct          = 257
  _smm_auto_min_io_size               = 56 KB
  _smm_auto_max_io_size               = 248 KB
  _smm_min_size                       = 128 KB
  _smm_max_size                       = 18636 KB
  _smm_px_max_size                    = 46592 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 * from scott.emp
*********************
QUERY BLOCK SIGNATURE
*********************
qb name was generated
signature (optimizer): qb_name=SEL$1 nbfros=1 flg=0
  fro(0): flg=0 objn=51148 hint_alias="EMP"@"SEL$1"
*****************************
SYSTEM STATISTICS INFORMATION
*****************************
  Using NOWORKLOAD Stats
  CPUSPEED: 587 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: 14  #Blks:  5  AvgRowLen:  37.00
Index Stats::
  Index: PK_EMP  Col#: 1
    LVLS: 0  #LB: 1  #DK: 14  LB/K: 1.00  DB/K: 1.00  CLUF: 1.00
***************************************
SINGLE TABLE ACCESS PATH
  Table: EMP  Alias: EMP     
    Card: Original: 14  Rounded: 14  Computed: 14.00  Non Adjusted: 14.00
  Access Path: TableScan
    Cost:  3.01  Resp: 3.01  Degree: 0
      Cost_io: 3.00  Cost_cpu: 39667
      Resp_io: 3.00  Resp_cpu: 39667
  Best:: AccessPath: TableScan
         Cost: 3.01  Degree: 1  Resp: 3.01  Card: 14.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.0056  card: 14.0000  bytes: 518
(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.0056  Degree: 1  Card: 14.0000  Bytes: 518
  Resc: 3.0056  Resc_io: 3.0000  Resc_cpu: 39667
  Resp: 3.0056  Resp_io: 3.0000  Resc_cpu: 39667
kkoipt: Query block SEL$1 (#0)
******* UNPARSED QUERY IS *******
SELECT "EMP"."EMPNO" "EMPNO","EMP"."ENAME" "ENAME","EMP"."JOB" "JOB","EMP"."MGR" "MGR","EMP"."HIREDATE" "HIREDATE","EMP"."SAL" "SAL","EMP"."COMM" "COMM","EMP"."DEPTNO" "DEPTNO" FROM "SCOTT"."EMP" "EMP"
kkoqbc-end
          : call(in-use=25760, alloc=32736), compile(in-use=35064, alloc=38000)
apadrv-end: call(in-use=25760, alloc=32736), compile(in-use=35584, alloc=38000)
 
sql_id=9mhzzknczbj6p.
Current SQL statement for this session:
select * from scott.emp
 
============
Plan Table
============
-------------------------------------+-----------------------------------+
| Id  | Operation          | Name    | Rows  | Bytes | Cost  | Time      |
-------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT   |         |       |       |     3 |           |
| 1   |  TABLE ACCESS FULL | EMP     |    14 |   518 |     3 |  00:00:01 |
-------------------------------------+-----------------------------------+
Predicate Information:
----------------------
 
Content of other_xml column
===========================
  db_version     : 10.2.0.1
  parse_schema   : SYS
  plan_hash      : 3956160932
  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")
    END_OUTLINE_DATA
  */
 
Optimizer environment:
  optimizer_mode_hinted               = false
  optimizer_features_hinted           = 0.0.0
  parallel_execution_enabled          = true
  parallel_query_forced_dop           = 0
  parallel_dml_forced_dop             = 0
  parallel_ddl_forced_degree          = 0
  parallel_ddl_forced_instances       = 0
  _query_rewrite_fudge                = 90
  optimizer_features_enable           = 10.2.0.1
  _optimizer_search_limit             = 5
  cpu_count                           = 1
  active_instance_count               = 1
  parallel_threads_per_cpu            = 2
  hash_area_size                      = 131072
  bitmap_merge_area_size              = 1048576
  sort_area_size                      = 65536
  sort_area_retained_size             = 0
  _sort_elimination_cost_ratio        = 0
  _optimizer_block_size               = 8192
  _sort_multiblock_read_count         = 2
  _hash_multiblock_io_count           = 0
  _db_file_optimizer_read_count       = 16
  _optimizer_max_permutations         = 2000
  pga_aggregate_target                = 93184 KB
  _pga_max_size                       = 204800 KB
  _query_rewrite_maxdisjunct          = 257
  _smm_auto_min_io_size               = 56 KB
  _smm_auto_max_io_size               = 248 KB
  _smm_min_size                       = 128 KB
  _smm_max_size                       = 18636 KB
  _smm_px_max_size                    = 46592 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
Query Block Registry:
*********************
SEL$1 0xb72baae4 (PARSER) [FINAL]
Optimizer State Dump: call(in-use=27196, alloc=49112), compile(in-use=53008, alloc=101400)
Registered qb: MISC$1 0xb72bac90 (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 07x9zn1dx277j.
CVM: Considering view merge in query block MISC$1 (#0)
CBQT: Validity checks failed for 07x9zn1dx277j.
***************
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=31096, alloc=31096), compile(in-use=29696, alloc=30688)
 
sql_id=07x9zn1dx277j.
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
  parallel_execution_enabled          = true
  parallel_query_forced_dop           = 0
  parallel_dml_forced_dop             = 0
  parallel_ddl_forced_degree          = 0
  parallel_ddl_forced_instances       = 0
  _query_rewrite_fudge                = 90
  optimizer_features_enable           = 10.2.0.1
  _optimizer_search_limit             = 5
  cpu_count                           = 1
  active_instance_count               = 1
  parallel_threads_per_cpu            = 2
  hash_area_size                      = 131072
  bitmap_merge_area_size              = 1048576
  sort_area_size                      = 65536
  sort_area_retained_size             = 0
  _sort_elimination_cost_ratio        = 0
  _optimizer_block_size               = 8192
  _sort_multiblock_read_count         = 2
  _hash_multiblock_io_count           = 0
  _db_file_optimizer_read_count       = 16
  _optimizer_max_permutations         = 2000
  pga_aggregate_target                = 93184 KB
  _pga_max_size                       = 204800 KB
  _query_rewrite_maxdisjunct          = 257
  _smm_auto_min_io_size               = 56 KB
  _smm_auto_max_io_size               = 248 KB
  _smm_min_size                       = 128 KB
  _smm_max_size                       = 18636 KB
  _smm_px_max_size                    = 46592 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
Query Block Registry:
*********************
MISC$1 0xb72bac90 (PARSER) [FINAL]
Optimizer State Dump: call(in-use=85756, alloc=85756), compile(in-use=38944, alloc=83700)