Skip to content

Instantly share code, notes, and snippets.

@oraclelearner

oraclelearner/gist:1583dc1ff3350fcb9c104d3d77064efc Secret

Created June 3, 2022 07:15
Show Gist options
  • Save oraclelearner/1583dc1ff3350fcb9c104d3d77064efc to your computer and use it in GitHub Desktop.
Save oraclelearner/1583dc1ff3350fcb9c104d3d77064efc to your computer and use it in GitHub Desktop.
Download ZIP
Raw
gistfile1.txt
$ cat DB11_ora_315892_trace4031.trc
Trace file /u01/app/oracle/diag/rdbms/DB1_XXXDB11/trace/DB11_ora_315892_trace4031.trc
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.11.0.0.0
Build label: RDBMS_19.11.0.0.0DBRU_LINUX.X64_210412
ORACLE_HOME: /u01/app/oracle/product/19.11.0.0.210420
System name: Linux
Node name: XXXXXXX.city1.comp1.com
Release: 4.1.12-124.30.1.el7uek.x86_64
Version: #2 SMP Thu Aug 8 01:38:50 GMT 2019
Machine: x86_64
Storage: Exadata
Instance name: DB11
Redo thread mounted by this instance: 1
Oracle process number: 426
Unix process pid: 315892, image: oracle@XXXXXXX.city1.comp1.com (TNS V1-V3)
*** 2022-06-02T23:45:36.605767-04:00
*** SESSION ID:(968.24201) 2022-06-02T23:45:36.605788-04:00
*** CLIENT ID:() 2022-06-02T23:45:36.605794-04:00
*** SERVICE NAME:(SYS$USERS) 2022-06-02T23:45:36.605800-04:00
*** MODULE NAME:(sqlplus@XXXXXXX.city1.comp1.com (TNS V1-V3) 2022-06-02T23:45:36.605815-04:00
*** ACTION NAME:() 2022-06-02T23:45:36.605820-04:00
*** CLIENT DRIVER:(SQL*PLUS) 2022-06-02T23:45:36.605825-04:00
DDE: Problem Key 'ORA 4031' was flood controlled (0x4) (incident: 183412)
ORA-04031: unable to allocate 4096 bytes of shared memory ("shared pool","TAB_PART_IDX13","pacdHds_kkpaco","kksgaAlloc: firstN")
LibraryHandle: Address=0x8bd3805b8 Hash=a1687c00 LockMode=S PinMode=S LoadLockMode=0 Status=VALD Subpool=7
ObjectName: Name=SCHEMA1.TAB_PART_IDX13
FullHashValue=14e79c9fc59c5ffbf0c806bca1687c00 Namespace=INDEX(04) Type=INDEX(01) ContainerId=0 ContainerUid=0 Identifier=1133798 OwnerIdn=128
Registered qb: MISC$1 0x8ecf6718 (PARSER)
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature(): NULL
SPM: disallowed: statement type not supported
kkoctx(): choosing CBO since optimizer mode set to all/first_rows
**************************
Automatic degree of parallelism (AUTODOP)
**************************
Automatic degree of parallelism is disabled: SQL property restriction (bootstrap, stmt type, remote cursor).
PM: Considering predicate move-around in query block MISC$1 (#0)
**************************
Predicate Move-Around (PM)
**************************
OPTIMIZER INFORMATION
******************************************
----- Current SQL Statement for this session (sql_id=9babjv8yq8ru3) -----
BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;
*******************************************
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
parallel_threads_per_cpu = 2
_pga_max_size = 2097152 KB
parallel_max_degree = 288
deferred_segment_creation = false
total_processor_group_count = 4
Bug Fix Control Environment
fix 27268249 = disabled *
use approximate ndv for computing leaf blocks and distinct keys
*************************************
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 = 72
active_instance_count = 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 = 8
_optimizer_max_permutations = 2000
pga_aggregate_target = 20480000 KB
_query_rewrite_maxdisjunct = 257
_smm_auto_min_io_size = 120 KB
_smm_auto_max_io_size = 1016 KB
_smm_min_size = 1024 KB
_smm_max_size_static = 1048576 KB
_smm_px_max_size_static = 10240000 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
_fix_control_key = -571469988
_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 = 128
_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
total_cpu_count = 144
_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
_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 = true
_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 = false
_optimizer_gather_stats_on_conventional_dml = false
_optimizer_use_stats_on_conventional_config = 0
_skip_pset_col_chk = 0
_nls_binary = true
_optimizer_quarantine_sql = true
_optimizer_gather_stats_on_conventional_config = 0
_nls_forced_binary = 0
_utl32k_mv_query = false
optimizer_session_type = NORMAL
BlockChain_ledger_infrastructure = 0
_cell_offload_vector_groupby_external = true
_bigdata_offload_flag = false
container_data = ALL
optimizer_real_time_statistics = false
_update_global_index_from_part_exchange = 0
***************************************
PARAMETERS IN OPT_PARAM HINT
****************************
***************************************
Column Usage Monitoring is ON: tracking level = 53
***************************************
Considering Query Transformations on query block MISC$1 (#0)
**************************
Query transformations (QT)
**************************
CBQT bypassed for query block MISC$1 (#0): statement type.
CBQT: Validity checks failed for 9babjv8yq8ru3.
CSE: Considering common sub-expression elimination in query block MISC$1 (#0)
*************************
Common Subexpression elimination (CSE)
*************************
CSE: CSE not performed on query block MISC$1 (#0).
OBYE: Considering Order-by Elimination from view MISC$1 (#0)
***************************
Order-by elimination (OBYE)
***************************
OBYE: OBYE bypassed: no order by to eliminate.
CVM: Considering view merge in query block MISC$1 (#0)
OJE: Begin: find best directive for query block MISC$1 (#0)
OJE: End: finding best directive for query block MISC$1 (#0)
query block MISC$1 (#0) unchanged
Considering Query Transformations on query block MISC$1 (#0)
**************************
Query transformations (QT)
**************************
CBQT bypassed for query block MISC$1 (#0): statement type.
CBQT: Validity checks failed for 9babjv8yq8ru3.
CSE: Considering common sub-expression elimination in query block MISC$1 (#0)
*************************
Common Subexpression elimination (CSE)
*************************
CSE: CSE not performed on query block MISC$1 (#0).
AAT: Considering Approximate Aggregate Transformation on query block MISC$1 (#0)
*******************************************
Approximate Aggregate Transformation (AAT)
*******************************************
AAT: no exact aggregates transformed
SQE: Trying SQ elimination.
SQE: invalid SQL command******* UNPARSED QUERY IS *******
SELECT 0
SU: Considering subquery unnesting in query block MISC$1 (#0)
********************
Subquery Unnest (SU)
********************
SU: Considering bottom-up subquery unnesting
SJC: Considering set-join conversion in query block MISC$1 (#0)
*************************
Set-Join Conversion (SJC)
*************************
SJC: not performed
DCL: Checking validity of group-by elimination MISC$1 (#0)
DCL: Result of group-by elimination: Invalid
PM: Considering predicate move-around in query block MISC$1 (#0)
**************************
Predicate Move-Around (PM)
**************************
PM: PM bypassed: Outer query contains no views.
PM: PM bypassed: Outer query contains no views.
isReduceGrByValid: Group By Validation (Failed).
isReduceGrByValid: Group By Validation (Failed).
SQT: SQT bypassed: non select statement.
query block MISC$1 (#0) unchanged
oracle@XXXXXXX.city1.comp1.com [/u01/app/oracle/diag/rdbms/DB1_XXXDB11/trace]
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment