Oracle 19.3 案例参考：通过 10053 跟踪获取的 SQL 执行计划诊断信息_oracle _optimizer_use_stats_on_conventional_dml

墨天轮原文链接：https://www.modb.pro/db/22835?ywm

这是一个测试案例的说明文件，是引用参考

注意，这是一个测试案例的说明文件，是引用参考：

Trace file /oradb/diag/rdbms/eygle/eygle/trace/eygle_ora_29019.trc
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
Build label:    RDBMS_19.3.0.0.0DBRU_LINUX.X64_190417
ORACLE_HOME:    /oradb/19.3
System name:	Linux
Node name:	localhost.localdomain
Release:	3.10.0-862.el7.x86_64
Version:	#1 SMP Wed Mar 21 18:14:51 EDT 2018
Machine:	x86_64
Instance name: eygle
Redo thread mounted by this instance: 1
Oracle process number: 51
Unix process pid: 29019, image: oracle@localhost.localdomain (TNS V1-V3)
 
 
*** 2020-03-20T14:34:05.759352+08:00
*** SESSION ID:(424.48891) 2020-03-20T14:34:05.759649+08:00
*** CLIENT ID:() 2020-03-20T14:34:05.759659+08:00
*** SERVICE NAME:(SYS$USERS) 2020-03-20T14:34:05.759664+08:00
*** MODULE NAME:(sqlplus@localhost.localdomain (TNS V1-V3)) 2020-03-20T14:34:05.759668+08:00
*** ACTION NAME:() 2020-03-20T14:34:05.759673+08:00
*** CLIENT DRIVER:(SQL*PLUS) 2020-03-20T14:34:05.759676+08:00
 
Registered qb: SEL$1 0x755b98b0 (PARSER)
---------------------
QUERY BLOCK SIGNATURE
---------------------
  signature (): qb_name=SEL$1 nbfros=2 flg=0
    fro(0): flg=4 objn=73130 hint_alias="ENMO"@"SEL$1"
    fro(1): flg=4 objn=73131 hint_alias="TECH"@"SEL$1"
 
SPM: statement not found in SMB
SPM: capture of plan baseline is OFF
 
**************************
Automatic degree of parallelism (AUTODOP)
**************************
Automatic degree of parallelism is disabled: Parameter.
kkopqSetForceParallelProperties: Hint:no
Query: compute:yes forced:no forceDop:0
Global Manual Dop: 1 - Rounded?: no
 
PM: Considering predicate move-around in query block SEL$1 (#0)
**************************
Predicate Move-Around (PM)
**************************
OPTIMIZER INFORMATION
 
******************************************
----- Current SQL Statement for this session (sql_id=9fp7chxjxjwst) -----
SELECT COUNT(OBJECT_NAME || ', ' || OBJECT_TYPE), COUNT(EDITION_NAME) FROM ENMO, TECH WHERE ID = OBJECT
_ID
*******************************************
Legend
The following abbreviations are used by optimizer trace.
CBQT - cost-based query transformation
JPPD - join predicate push-down
OJPPD - old-style (non-cost-based) JPPD
FPD - filter push-down
PM - predicate move-around
CVM - complex view merging
SPJ - select-project-join
SJC - set join conversion
SU - subquery unnesting
OBYE - order by elimination
OST - old style star transformation
ST - new (cbqt) star transformation
CNT - count(col) to count(*) transformation
JE - Join Elimination
JF - join factorization
CBY - connect by
SLP - select list pruning
DP - distinct placement
VT - vector transformation
AAT - Approximate Aggregate Transformation
ORE - CBQT OR-Expansion
LORE - Legacy OR-Expansion
qb - query block
LB - leaf blocks
DK - distinct keys
LB/K - average number of leaf blocks per key
DB/K - average number of data blocks per key
CLUF - clustering factor
NDV - number of distinct values
Resp - response cost
Card - cardinality
Resc - resource cost
NL - nested loops (join)
SM - sort merge (join)
HA - hash (join)
CPUSPEED - CPU Speed 
IOTFRSPEED - I/O transfer speed
IOSEEKTIM - I/O seek time
SREADTIM - average single block read time
MREADTIM - average multiblock read time
MBRC - average multiblock read count
MAXTHR - maximum I/O system throughput
SLAVETHR - average slave I/O throughput
dmeth - distribution method
  1: no partitioning required
  2: value partitioned
  4: right is random (round-robin)
  128: left is random (round-robin)
  8: broadcast right and partition left
  16: broadcast left and partition right
  32: partition left using partitioning of right
  64: partition right using partitioning of left
  256: run the join in serial
  0: invalid distribution method
sel - selectivity
ptn - partition
AP - adaptive plans
***************************************
PARAMETERS USED BY THE OPTIMIZER
********************************
  *************************************
  PARAMETERS WITH ALTERED VALUES
  ******************************
Compilation Environment Dump
_fix_control_key                    = -866096202
Bug Fix Control Environment
 
 
  *************************************
  PARAMETERS WITH DEFAULT VALUES
  ******************************
Compilation Environment Dump
optimizer_mode_hinted               = false
optimizer_features_hinted           = 0.0.0
parallel_execution_enabled          = true
parallel_query_forced_dop           = 0
parallel_dml_forced_dop             = 0
parallel_ddl_forced_degree          = 0
parallel_ddl_forced_instances       = 0
_query_rewrite_fudge                = 90
optimizer_features_enable           = 19.1.0
_optimizer_search_limit             = 5
cpu_count                           = 1
active_instance_count               = 1
parallel_threads_per_cpu            = 1
hash_area_size                      = 131072
bitmap_merge_area_size              = 1048576
sort_area_size                      = 65536
sort_area_retained_size             = 0
_sort_elimination_cost_ratio        = 0
_optimizer_block_size               = 8192
_sort_multiblock_read_count         = 2
_hash_multiblock_io_count           = 0
_db_file_optimizer_read_count       = 8
_optimizer_max_permutations         = 2000
pga_aggregate_target                = 303104 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                       = 303 KB
_smm_max_size_static                = 60620 KB
_smm_px_max_size_static             = 151552 KB
_cpu_to_io                          = 0
_optimizer_undo_cost_change         = 19.1.0
parallel_query_mode                 = enabled
_enable_parallel_dml                = 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                  = false
_full_pwise_join_enabled            = true
_partial_pwise_join_enabled         = true
_left_nested_loops_random           = true
_improved_row_length_enabled        = true
_index_join_enabled                 = true
_enable_type_dep_selectivity        = true
_improved_outerjoin_card            = true
_optimizer_adjust_for_nulls         = true
_optimizer_degree                   = 0
_use_column_stats_for_function      = true
_subquery_pruning_enabled           = true
_subquery_pruning_mv_enabled        = false
_or_expand_nvl_predicate            = true
_like_with_bind_as_equality         = false
_table_scan_cost_plus_one           = true
_cost_equality_semi_join            = true
_default_non_equality_sel_check     = true
_new_initial_join_orders            = true
_oneside_colstat_for_equijoins      = true
_optim_peek_user_binds              = true
_minimal_stats_aggregation          = true
_force_temptables_for_gsets         = false
workarea_size_policy                = auto
_smm_auto_cost_enabled              = true
_gs_anti_semi_join_allowed          = true
_optim_new_default_join_sel         = true
optimizer_dynamic_sampling          = 2
_pre_rewrite_push_pred              = true
_optimizer_new_join_card_computation = true
_union_rewrite_for_gs               = yes_gset_mvs
_generalized_pruning_enabled        = true
_optim_adjust_for_part_skews        = true
_force_datefold_trunc               = false
statistics_level                    = typical
_optimizer_system_stats_usage       = true
skip_unusable_indexes               = true
_remove_aggr_subquery               = true
_optimizer_push_down_distinct       = 0
_dml_monitoring_enabled             = true
_optimizer_undo_changes             = false
_predicate_elimination_enabled      = true
_nested_loop_fudge                  = 100
_project_view_columns               = true
_local_communication_costing_enabled = true
_local_communication_ratio          = 50
_query_rewrite_vop_cleanup          = true
_slave_mapping_enabled              = true
_optimizer_cost_based_transformation = linear
_optimizer_mjc_enabled              = true
_right_outer_hash_enable            = true
_spr_push_pred_refspr               = true
_optimizer_cache_stats              = false
_optimizer_cbqt_factor              = 50
_optimizer_squ_bottomup             = true
_fic_area_size                      = 131072
_optimizer_skip_scan_enabled        = true
_optimizer_cost_filter_pred         = false
_optimizer_sortmerge_join_enabled   = true
_optimizer_join_sel_sanity_check    = true
_mmv_query_rewrite_enabled          = true
_bt_mmv_query_rewrite_enabled       = true
_add_stale_mv_to_dependency_list    = true
_distinct_view_unnesting            = false
_optimizer_dim_subq_join_sel        = true
_optimizer_disable_strans_sanity_checks = 0
_optimizer_compute_index_stats      = true
_push_join_union_view2              = true
optimizer_ignore_hints              = false
_optimizer_random_plan              = 0
_query_rewrite_setopgrw_enable      = true
_optimizer_correct_sq_selectivity   = true
_disable_function_based_index       = false
_optimizer_join_order_control       = 3
_optimizer_cartesian_enabled        = true
_optimizer_starplan_enabled         = true
_extended_pruning_enabled           = true
_optimizer_push_pred_cost_based     = true
_optimizer_null_aware_antijoin      = true
_optimizer_extend_jppd_view_types   = true
_sql_model_unfold_forloops          = run_time
_enable_dml_lock_escalation         = false
_bloom_filter_enabled               = true
_update_bji_ipdml_enabled           = 0
_optimizer_extended_cursor_sharing  = udo
_dm_max_shared_pool_pct             = 1
_optimizer_cost_hjsmj_multimatch    = true
_optimizer_transitivity_retain      = true
_px_pwg_enabled                     = true
optimizer_secure_view_merging       = true
_optimizer_join_elimination_enabled = true
flashback_table_rpi                 = non_fbt
_optimizer_cbqt_no_size_restriction = true
_optimizer_enhanced_filter_push     = true
_optimizer_filter_pred_pullup       = true
_rowsrc_trace_level                 = 0
_simple_view_merging                = true
_optimizer_rownum_pred_based_fkr    = true
_optimizer_better_inlist_costing    = all
_optimizer_self_induced_cache_cost  = false
_optimizer_min_cache_blocks         = 10
_optimizer_or_expansion             = depth
_optimizer_order_by_elimination_enabled = true
_optimizer_outer_to_anti_enabled    = true
_selfjoin_mv_duplicates             = true
_dimension_skip_null                = true
_force_rewrite_enable               = false
_optimizer_star_tran_in_with_clause = true
_optimizer_complex_pred_selectivity = true
_optimizer_connect_by_cost_based    = true
_gby_hash_aggregation_enabled       = true
_globalindex_pnum_filter_enabled    = true
_px_minus_intersect                 = true
_force_slave_mapping_intra_part_loads = false
_force_tmp_segment_loads            = false
_query_mmvrewrite_maxpreds          = 10
_query_mmvrewrite_maxintervals      = 5
_query_mmvrewrite_maxinlists        = 5
_query_mmvrewrite_maxdmaps          = 10
_query_mmvrewrite_maxcmaps          = 20
_query_mmvrewrite_maxregperm        = 512
_query_mmvrewrite_maxqryinlistvals  = 500
_disable_parallel_conventional_load = false
_trace_virtual_columns              = false
_replace_virtual_columns            = true
_virtual_column_overload_allowed    = true
_kdt_buffering                      = true
_first_k_rows_dynamic_proration     = true
_optimizer_sortmerge_join_inequality = true
_optimizer_aw_stats_enabled         = true
_bloom_pruning_enabled              = true
result_cache_mode                   = MANUAL
_px_ual_serial_input                = true
_optimizer_skip_scan_guess          = false
_enable_row_shipping                = true
_row_shipping_threshold             = 100
_row_shipping_explain               = false
transaction_isolation_level         = read_commited
_optimizer_distinct_elimination     = true
_optimizer_multi_level_push_pred    = true
_optimizer_group_by_placement       = true
_optimizer_rownum_bind_default      = 10
_enable_query_rewrite_on_remote_objs = true
_optimizer_extended_cursor_sharing_rel = simple
_optimizer_adaptive_cursor_sharing  = true
_direct_path_insert_features        = 0
_optimizer_improve_selectivity      = true
optimizer_use_pending_statistics    = false
_optimizer_enable_density_improvements = true
_optimizer_aw_join_push_enabled     = true
_optimizer_connect_by_combine_sw    = true
_enable_pmo_ctas                    = 0
_optimizer_native_full_outer_join   = force
_bloom_predicate_enabled            = true
_optimizer_enable_extended_stats    = true
_is_lock_table_for_ddl_wait_lock    = 0
_pivot_implementation_method        = choose
optimizer_capture_sql_plan_baselines = false
optimizer_use_sql_plan_baselines    = true
_optimizer_star_trans_min_cost      = 0
_optimizer_star_trans_min_ratio     = 0
_with_subquery                      = OPTIMIZER
_optimizer_fkr_index_cost_bias      = 10
_optimizer_use_subheap              = true
parallel_degree_policy              = manual
parallel_degree                     = 0
parallel_min_time_threshold         = 10
_parallel_time_unit                 = 10
_optimizer_or_expansion_subheap     = true
_optimizer_free_transformation_heap = true
_optimizer_reuse_cost_annotations   = true
_result_cache_auto_size_threshold   = 100
_result_cache_auto_time_threshold   = 1000
_optimizer_nested_rollup_for_gset   = 100
_nlj_batching_enabled               = 1
parallel_query_default_dop          = 0
is_recur_flags                      = 0
optimizer_use_invisible_indexes     = false
flashback_data_archive_internal_cursor = 0
_optimizer_extended_stats_usage_control = 192
_parallel_syspls_obey_force         = true
cell_offload_processing             = true
_rdbms_internal_fplib_enabled       = false
db_file_multiblock_read_count       = 75
_bloom_folding_enabled              = true
_mv_generalized_oj_refresh_opt      = true
cell_offload_compaction             = ADAPTIVE
cell_offload_plan_display           = AUTO
_bloom_predicate_offload            = true
_bloom_filter_size                  = 0
_bloom_pushing_max                  = 512
parallel_degree_limit               = 65535
parallel_force_local                = false
parallel_max_degree                 = 1
total_cpu_count                     = 1
_optimizer_coalesce_subqueries      = true
_optimizer_fast_pred_transitivity   = true
_optimizer_fast_access_pred_analysis = true
_optimizer_unnest_disjunctive_subq  = true
_optimizer_unnest_corr_set_subq     = true
_optimizer_distinct_agg_transform   = true
_aggregation_optimization_settings  = 0
_optimizer_connect_by_elim_dups     = true
_optimizer_eliminate_filtering_join = true
_connect_by_use_union_all           = true
dst_upgrade_insert_conv             = true
advanced_queuing_internal_cursor    = 0
_optimizer_unnest_all_subqueries    = true
parallel_autodop                    = 0
parallel_ddldml                     = 0
_parallel_cluster_cache_policy      = adaptive
_parallel_scalability               = 50
iot_internal_cursor                 = 0
_optimizer_instance_count           = 0
_optimizer_connect_by_cb_whr_only   = false
_suppress_scn_chk_for_cqn           = nosuppress_1466
_optimizer_join_factorization       = true
_optimizer_use_cbqt_star_transformation = true
_optimizer_table_expansion          = true
_and_pruning_enabled                = true
_deferred_constant_folding_mode     = DEFAULT
_optimizer_distinct_placement       = true
partition_pruning_internal_cursor   = 0
parallel_hinted                     = none
_sql_compatibility                  = 0
_optimizer_use_feedback             = true
_optimizer_try_st_before_jppd       = true
_dml_frequency_tracking             = false
_optimizer_interleave_jppd          = true
kkb_drop_empty_segments             = 0
_px_partition_scan_enabled          = true
_px_partition_scan_threshold        = 64
_optimizer_false_filter_pred_pullup = true
_bloom_minmax_enabled               = true
only_move_row                       = 0
_optimizer_enable_table_lookup_by_nl = true
parallel_execution_message_size     = 16384
_px_loc_msg_cost                    = 1000
_px_net_msg_cost                    = 10000
_optimizer_generate_transitive_pred = true
_optimizer_cube_join_enabled        = true
_optimizer_filter_pushdown          = true
deferred_segment_creation           = true
_optimizer_outer_join_to_inner      = true
_allow_level_without_connect_by     = false
_max_rwgs_groupings                 = 8192
_optimizer_hybrid_fpwj_enabled      = true
_px_replication_enabled             = true
ilm_filter                          = 0
_optimizer_partial_join_eval        = true
_px_concurrent                      = true
_px_object_sampling_enabled         = true
_px_back_to_parallel                = OFF
_optimizer_unnest_scalar_sq         = true
_optimizer_full_outer_join_to_outer = true
_px_filter_parallelized             = true
_px_filter_skew_handling            = true
_zonemap_use_enabled                = true
_zonemap_control                    = 0
_optimizer_multi_table_outerjoin    = true
_px_groupby_pushdown                = force
_partition_advisor_srs_active       = true
_optimizer_ansi_join_lateral_enhance = true
_px_parallelize_expression          = true
_fast_index_maintenance             = true
_optimizer_ansi_rearchitecture      = true
_optimizer_gather_stats_on_load     = true
ilm_access_tracking                 = 0
ilm_dml_timestamp                   = 0
_px_adaptive_dist_method            = choose
_px_adaptive_dist_method_threshold  = 0
_optimizer_batch_table_access_by_rowid = true
optimizer_adaptive_reporting_only   = false
_optimizer_ads_max_table_count      = 0
_optimizer_ads_time_limit           = 0
_optimizer_ads_use_result_cache     = true
_px_wif_dfo_declumping              = choose
_px_wif_extend_distribution_keys    = true
_px_join_skew_handling              = true
_px_join_skew_ratio                 = 10
_px_join_skew_minfreq               = 30
CLI_internal_cursor                 = 0
_parallel_fault_tolerance_enabled   = false
_parallel_fault_tolerance_threshold = 3
_px_partial_rollup_pushdown         = adaptive
_px_single_server_enabled           = true
_optimizer_dsdir_usage_control      = 0
_px_cpu_autodop_enabled             = true
_px_cpu_process_bandwidth           = 200
_sql_hvshare_threshold              = 0
_px_tq_rowhvs                       = true
_optimizer_use_gtt_session_stats    = true
_optimizer_proc_rate_level          = basic
_px_hybrid_TSM_HWMB_load            = true
_optimizer_use_histograms           = true
PMO_altidx_rebuild                  = 0
_cell_offload_expressions           = true
_cell_materialize_virtual_columns   = true
_cell_materialize_all_expressions   = false
_rowsets_enabled                    = true
_rowsets_target_maxsize             = 524288
_rowsets_max_rows                   = 256
_use_hidden_partitions              = 0
_px_load_monitor_threshold          = 10000
_px_numa_support_enabled            = false
total_processor_group_count         = 1
_adaptive_window_consolidator_enabled = true
_optimizer_strans_adaptive_pruning  = true
_bloom_rm_filter                    = false
_optimizer_null_accepting_semijoin  = true
_long_varchar_allow_IOT             = 0
_parallel_ctas_enabled              = true
_cell_offload_complex_processing    = true
_optimizer_performance_feedback     = off
_optimizer_proc_rate_source         = DEFAULT
_hashops_prefetch_size              = 4
_cell_offload_sys_context           = true
_multi_commit_global_index_maint    = 0
_stat_aggs_one_pass_algorithm       = false
_dbg_scan                           = 0
_oltp_comp_dbg_scan                 = 0
_arch_comp_dbg_scan                 = 0
_optimizer_gather_feedback          = true
_upddel_dba_hash_mask_bits          = 0
_px_pwmr_enabled                    = true
_px_cdb_view_enabled                = true
_bloom_sm_enabled                   = true
_optimizer_cluster_by_rowid         = true
_optimizer_cluster_by_rowid_control = 129
_partition_cdb_view_enabled         = true
_common_data_view_enabled           = true
_pred_push_cdb_view_enabled         = true
_rowsets_cdb_view_enabled           = true
_distinct_agg_optimization_gsets    = choose
_array_cdb_view_enabled             = true
_optimizer_adaptive_plan_control    = 0
_optimizer_adaptive_random_seed     = 0
_optimizer_cbqt_or_expansion        = on
_inmemory_dbg_scan                  = 0
_gby_vector_aggregation_enabled     = true
_optimizer_vector_transformation    = true
_optimizer_vector_fact_dim_ratio    = 10
_key_vector_max_size                = 0
_key_vector_predicate_enabled       = true
_key_vector_predicate_threshold     = 0
_vector_operations_control          = 0
_optimizer_vector_min_fact_rows     = 10000000
parallel_dblink                     = 0
_px_scalable_invdist                = true
_key_vector_offload                 = predicate
_optimizer_aggr_groupby_elim        = true
_optimizer_reduce_groupby_key       = true
_vector_serialize_temp_threshold    = 1000
_always_vector_transformation       = false
_optimizer_cluster_by_rowid_batched = true
_object_link_fixed_enabled          = true
optimizer_inmemory_aware            = true
_optimizer_inmemory_table_expansion = true
_optimizer_inmemory_gen_pushable_preds = true
_optimizer_inmemory_autodop         = true
_optimizer_inmemory_access_path     = true
_optimizer_inmemory_bloom_filter    = true
_parallel_inmemory_min_time_threshold = 1
_parallel_inmemory_time_unit        = 1
_rc_sys_obj_enabled                 = true
_optimizer_nlj_hj_adaptive_join     = true
_indexable_con_id                   = true
_bloom_serial_filter                = on
inmemory_force                      = default
inmemory_query                      = enable
_inmemory_query_scan                = true
_inmemory_query_fetch_by_rowid      = false
_inmemory_pruning                   = on
_px_autodop_pq_overhead             = true
_px_external_table_default_stats    = true
_optimizer_key_vector_aggr_factor   = 75
_optimizer_vector_cost_adj          = 100
_cdb_cross_container                = 65535
_cdb_view_parallel_degree           = 65535
_optimizer_hll_entry                = 4096
_px_cdb_view_join_enabled           = true
inmemory_size                       = 0
_external_table_smart_scan          = hadoop_only
_optimizer_inmemory_minmax_pruning  = true
_approx_cnt_distinct_gby_pushdown   = choose
_approx_cnt_distinct_optimization   = 0
_optimizer_ads_use_partial_results  = true
_query_execution_time_limit         = 0
_optimizer_inmemory_cluster_aware_dop = true
_optimizer_db_blocks_buffers        = 0 KB
_query_rewrite_use_on_query_computation = true
_px_scalable_invdist_mcol           = true
_optimizer_bushy_join               = off
_optimizer_bushy_fact_dim_ratio     = 20
_optimizer_bushy_fact_min_size      = 100000
_optimizer_bushy_cost_factor        = 100
_rmt_for_table_redef_enabled        = true
_optimizer_eliminate_subquery       = true
_sqlexec_cache_aware_hash_join_enabled = true
_zonemap_usage_tracking             = true
_sqlexec_hash_based_distagg_enabled = false
_sqlexec_disable_hash_based_distagg_tiv = false
_sqlexec_hash_based_distagg_ssf_enabled = true
_sqlexec_distagg_optimization_settings = 0
approx_for_aggregation              = false
approx_for_count_distinct           = false
_optimizer_union_all_gsets          = true
_rowsets_use_encoding               = true
_rowsets_max_enc_rows               = 64
_optimizer_enhanced_join_elimination = true
_optimizer_multicol_join_elimination = true
_key_vector_create_pushdown_threshold = 20000
_optimizer_enable_plsql_stats       = true
_recursive_with_parallel            = true
_recursive_with_branch_iterations   = 7
_px_dist_agg_partial_rollup_pushdown = adaptive
_px_adaptive_dist_bypass_enabled    = true
_enable_view_pdb                    = true
_optimizer_key_vector_pruning_enabled = true
_pwise_distinct_enabled             = true
_recursive_with_using_temp_table    = false
_partition_read_only                = true
_sql_alias_scope                    = true
_cdb_view_no_skip_migrate           = false
_approx_perc_sampling_err_rate      = 2
_sqlexec_encoding_aware_hj_enabled  = true
rim_node_exist                      = 0
_enable_containers_subquery         = true
_force_containers_subquery          = false
_cell_offload_vector_groupby        = true
_vector_encoding_mode               = manual
_ds_xt_split_count                  = 1
_ds_sampling_method                 = PROGRESSIVE
_optimizer_ads_use_spd_cache        = true
_optimizer_use_table_scanrate       = HADOOP_ONLY
_optimizer_use_xt_rowid             = true
_xt_sampling_scan_granules          = on
_recursive_with_control             = 0
_sqlexec_use_rwo_aware_expr_analysis = true
_optimizer_band_join_aware          = true
_optimizer_vector_base_dim_fact_factor = 200
approx_for_percentile               = none
_approx_percentile_optimization     = 0
_projection_pushdown                = true
_px_object_sampling                 = 200
_optimizer_adaptive_plans_continuous = false
_optimizer_adaptive_plans_iterative = false
_ds_enable_view_sampling            = true
_optimizer_generate_ptf_implied_preds = true
_optimizer_inmemory_use_stored_stats = AUTO
_cdb_special_old_xplan              = true
uniaud_internal_cursor              = 0
_kd_dbg_control                     = 0
_mv_access_compute_fresh_data       = on
_bloom_filter_ratio                 = 35
_optimizer_control_shard_qry_processing = 65528
containers_parallel_degree          = 65535
sql_from_coordinator                = 0
_xt_sampling_scan_granules_min_granules = 1
_in_memory_cdt                      = LIMITED
_in_memory_cdt_maxpx                = 4
_px_partition_load_dist_threshold   = 64
_px_adaptive_dist_bypass_optimization = 1
_optimizer_interleave_or_expansion  = true
optimizer_adaptive_plans            = true
optimizer_adaptive_statistics       = false
_optimizer_use_feedback_for_join    = false
_optimizer_ads_for_pq               = false
_px_join_skewed_values_count        = 0
optimizer_ignore_parallel_hints     = false
parallel_min_degree                 = 1
_px_nlj_bcast_rr_threshold          = 10
_optimizer_gather_stats_on_load_all = false
_optimizer_gather_stats_on_load_hist = false
_optimizer_allow_all_access_paths   = true
_key_vector_double_enabled          = true
_key_vector_timestamp_enabled       = true
_optimizer_answering_query_using_stats = false
_disable_dblink_optim               = true
_cell_offload_hybrid_processing     = true
_read_optimized_table_lookup        = true
_optimizer_key_vector_payload       = true
_optimizer_vector_fact_payload_ratio = 20
_bloom_pruning_setops_enabled       = true
_bloom_filter_setops_enabled        = true
_key_vector_shared_dgk_ht           = true
_px_pwise_wif_enabled               = true
_sqlexec_reorder_wif_enabled        = true
_px_partition_skew_threshold        = 80
_sqlexec_pwiseops_with_sqlfuncs_enabled = true
_sqlexec_pwiseops_with_binds_enabled = true
_px_shared_hash_join                = false
_px_reuse_server_groups             = multi
_px_join_skew_null_handling         = true
_px_join_skew_use_histogram         = true
_px_join_skew_sampling_time_limit   = 50
_px_join_skew_sampling_percent      = 0
_cdb_view_no_skip_restricted        = false
_inmemory_external_table            = true
_sqlexec_use_kgghash3               = true
_cell_offload_vector_groupby_force  = false
_hcs_enable_pred_push               = true
parallel_dop_doubled                = 0
_optimizer_gather_stats_on_load_index = true
_con_map_sql_enforcement            = true
_uniq_cons_sql_enforcement          = true
_ref_cons_sql_enforcement           = true
_is_lock_table_for_split_merge      = 0
_cell_offload_vector_groupby_fact_key = true
_px_scalable_gby_invdist            = true
_px_dynamic_granules                = true
_px_dynamic_granules_adjust         = 10
_px_hybrid_partition_execution_enabled = true
_px_hybrid_partition_skew_threshold = 10
_optimizer_key_vector_use_max_size  = 1048576
_cell_offload_vector_groupby_withnojoin = true
_key_vector_join_pushdown_enabled   = true
_cell_offload_grand_total           = true
_optimizer_key_vector_payload_dim_aggs = false
_optimizer_use_auto_indexes         = AUTO
_optimizer_use_stats_on_conventional_dml = true
_optimizer_gather_stats_on_conventional_dml = true
_optimizer_use_stats_on_conventional_config = 0
_skip_pset_col_chk                  = 0
_nls_binary                         = false
_optimizer_quarantine_sql           = true
_optimizer_gather_stats_on_conventional_config = 0
_nls_forced_binary                  = 0
_utl32k_mv_query                    = false
_optimizer_auto_index_allow         = AUTO
 
  ***************************************
  PARAMETERS IN OPT_PARAM HINT
  ****************************
***************************************
Column Usage Monitoring is ON: tracking level = 53
***************************************
 
Considering Query Transformations on query block SEL$1 (#0)
**************************
Query transformations (QT)
**************************
JF: Checking validity of join factorization for query block SEL$1 (#0)
JF: Bypassed: not a UNION or UNION-ALL query block.
ST: not valid since star transformation parameter is FALSE
TE: Checking validity of table expansion for query block SEL$1 (#0)
TE: Bypassed: No partitioned table in query block.
ORE: Checking validity of OR Expansion for query block SEL$1 (#0)
ORE: Predicate chain before QB validity check - SEL$1
"TECH"."ID"="ENMO"."OBJECT_ID"
ORE: Predicate chain after QB validity check - SEL$1
"TECH"."ID"="ENMO"."OBJECT_ID"
ORE: bypassed - No valid predicate for OR expansion.
VT: Initial VT validity check for query block SEL$1 (#0)
VT: Bypassed: inmemory is disabled.
BJ: Checking validity for bushy join for query block SEL$1 (#0)
invalid because Not enabled by hint/parameter
BJ: Bypassed: Not enabled by hint/parameter.
CBQT bypassed for query block SEL$1 (#0): no complex view, sub-queries or UNION (ALL) queries.
CBQT: Validity checks failed for 9fp7chxjxjwst.
CSE: Considering common sub-expression elimination in query block SEL$1 (#0)
*************************
Common Subexpression elimination (CSE)
*************************
CSE:     CSE not performed on query block SEL$1 (#0).
OBYE:   Considering Order-by Elimination from view SEL$1 (#0)
***************************
Order-by elimination (OBYE)
***************************
OBYE:     OBYE bypassed: no order by to eliminate.
JE:[V2]   Considering Join Elimination on query block SEL$1 (#0)
*************************
Join Elimination [JE:R 12.2]    
*************************
SQL:******* UNPARSED QUERY IS *******
SELECT COUNT("ENMO"."OBJECT_NAME"||', '||"ENMO"."OBJECT_TYPE") "COUNT(OBJECT_NAME||','||OBJECT_TYPE)",C
OUNT("ENMO"."EDITION_NAME") "COUNT(EDITION_NAME)" FROM "SYS"."ENMO" "ENMO","SYS"."TECH" "TECH" WHERE "T
ECH"."ID"="ENMO"."OBJECT_ID"
JE:[V2] Query block (0x7efe755b98b0) before join elimination:
SQL:******* UNPARSED QUERY IS *******
SELECT COUNT("ENMO"."OBJECT_NAME"||', '||"ENMO"."OBJECT_TYPE") "COUNT(OBJECT_NAME||','||OBJECT_TYPE)",C
OUNT("ENMO"."EDITION_NAME") "COUNT(EDITION_NAME)" FROM "SYS"."ENMO" "ENMO","SYS"."TECH" "TECH" WHERE "T
ECH"."ID"="ENMO"."OBJECT_ID"
JE:[V2]: Try to eliminate TECH by ref. join elim using NTSID
JE:[V2]: Cannot eliminate TECH by ref. join elim - no constr. based join pred(s)
JE:[V2]: Try to eliminate ENMO by ref. join elim using NTSID
JE:[V2]: Cannot eliminate ENMO by ref. join elim - no constr. based join pred(s)
JE:[V2] Query block (0x7efe755b98b0) after join elimination:
SQL:******* UNPARSED QUERY IS *******
SELECT COUNT("ENMO"."OBJECT_NAME"||', '||"ENMO"."OBJECT_TYPE") "COUNT(OBJECT_NAME||','||OBJECT_TYPE)",C
OUNT("ENMO"."EDITION_NAME") "COUNT(EDITION_NAME)" FROM "SYS"."ENMO" "ENMO","SYS"."TECH" "TECH" WHERE "T
ECH"."ID"="ENMO"."OBJECT_ID"
 
CVM: Considering view merge in query block SEL$1 (#0)
OJE: Begin: find best directive for query block SEL$1 (#0)
OJE: End: finding best directive for query block SEL$1 (#0)
CNT:   Considering count(col) to count(*) on query block SEL$1 (#0)
*************************
Count(col) to Count(*) (CNT)
*************************
CNT:     COUNT() to COUNT(*) not done.
JE:[V2]   Considering Join Elimination on query block SEL$1 (#0)
*************************
Join Elimination [JE:R 12.2]    
*************************
SQL:******* UNPARSED QUERY IS *******
SELECT COUNT("ENMO"."OBJECT_NAME"||', '||"ENMO"."OBJECT_TYPE") "COUNT(OBJECT_NAME||','||OBJECT_TYPE)",C
OUNT("ENMO"."EDITION_NAME") "COUNT(EDITION_NAME)" FROM "SYS"."ENMO" "ENMO","SYS"."TECH" "TECH" WHERE "T
ECH"."ID"="ENMO"."OBJECT_ID"
JE:[V2] Query block (0x7efe755b98b0) before join elimination:
SQL:******* UNPARSED QUERY IS *******
SELECT COUNT("ENMO"."OBJECT_NAME"||', '||"ENMO"."OBJECT_TYPE") "COUNT(OBJECT_NAME||','||OBJECT_TYPE)",C
OUNT("ENMO"."EDITION_NAME") "COUNT(EDITION_NAME)" FROM "SYS"."ENMO" "ENMO","SYS"."TECH" "TECH" WHERE "T
ECH"."ID"="ENMO"."OBJECT_ID"
JE:[V2]: Try to eliminate TECH by ref. join elim using NTSID
JE:[V2]: Cannot eliminate TECH by ref. join elim - no constr. based join pred(s)
JE:[V2]: Try to eliminate ENMO by ref. join elim using NTSID
JE:[V2]: Cannot eliminate ENMO by ref. join elim - no constr. based join pred(s)
JE:[V2] Query block (0x7efe755b98b0) after join elimination:
SQL:******* UNPARSED QUERY IS *******
SELECT COUNT("ENMO"."OBJECT_NAME"||', '||"ENMO"."OBJECT_TYPE") "COUNT(OBJECT_NAME||','||OBJECT_TYPE)",C
OUNT("ENMO"."EDITION_NAME") "COUNT(EDITION_NAME)" FROM "SYS"."ENMO" "ENMO","SYS"."TECH" "TECH" WHERE "T
ECH"."ID"="ENMO"."OBJECT_ID"