[20200819]cofep.sql脚本对比版本参数变化.txt

[20200819]cofep.sql脚本对比版本参数变化.txt --//Tanel Poder大师提供一个脚本对比版本Optimizer Features参数变化.这样比较好了解oracle升级后一些参数做了那些改进. --//脚本如下: -- Compare Optimizer Features Enable Parameter values -- By Tanel Poder ( http://www.tanelpoder.com ) --   Requires opt_param_matrix table to be created (using tools/optimizer/optimizer_features_matrix.sql) --   Requires Oracle 11g due PIVOT clause (but you can rewrite this SQL in earlier versions)` col pd_name head NAME for a50 col pd_value head VALUE for a30 column pd_descr heading DESCRIPTION format a70 word_wrap prompt Compare Optimizer_Features_Enable Parameter differences prompt for values &1 and &2 select m.*, n.ksppdesc pd_descr from (     select *     from opt_param_matrix     pivot(         max(substr(value,1,20))         for opt_features_enabled in ('&1','&2')     )     where "'&1'" != "'&2'" ) m , sys.x$ksppi n , sys.x$ksppcv c where     n.indx=c.indx and n.ksppinm = m.parameter / --//Compare Optimizer Features Enable Parameter values =>cofep , 不好记忆^_^. --//在测试中,遇到一点点小问题,做一个记录,也许以后工作需要. SCOTT@test01p> @ ver1 PORT_STRING                    VERSION        BANNER                                                                               CON_ID ------------------------------ -------------- -------------------------------------------------------------------------------- ---------- IBMPC/WIN_NT64-9.1.0           12.2.0.1.0     Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0 SYS@test> @tpt/cofep 12.2.0.1 10.2.0.4 Compare Optimizer_Features_Enable Parameter differences for values 12.2.0.1 and 10.2.0.4 old   7:         for opt_features_enabled in ('&1','&2') new   7:         for opt_features_enabled in ('12.2.0.1','10.2.0.4') old   9:     where "'&1'" != "'&2'" new   9:     where "'12.2.0.1'" != "'10.2.0.4'"     from opt_param_matrix          * ERROR at line 4: ORA-00942: table or view does not exist --//仔细看原脚本,可以发现opt_param_matrix需要自己手工建立. SYS@test> @ tpt/tools/optimizer/optimizer_features_matrix.sql display a matrix of optimizer parameters which change when changing optimizer_features_enabled... Table created. DECLARE * ERROR at line 1: ORA-12899: value too large for column "SYS"."OPT_PARAM_MATRIX"."PARAMETER" (actual: 56, maximum: 55) ORA-06512: at line 6 ORA-06512: at line 6 To test, run: @cofep.sql 10.2.0.1 10.2.0.4 --//修改脚本optimizer_features_matrix.sql: CREATE TABLE opt_param_matrix(     opt_features_enabled VARCHAR2(100) NOT NULL   , parameter            VARCHAR2(56) NOT NULL   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~   , value                VARCHAR2(1000) ); SYS@test> drop table opt_param_matrix purge ; Table dropped. SYS@test> @ tpt/tools/optimizer/optimizer_features_matrix.sql display a matrix of optimizer parameters which change when changing optimizer_features_enabled... Table created. PL/SQL procedure successfully completed. To test, run: @cofep.sql 10.2.0.1 10.2.0.4 --//测试: SYS@test> @ tpt/cofep 11.2.0.4 12.2.0.1 Compare Optimizer_Features_Enable Parameter differences for values 11.2.0.4 and 12.2.0.1 old   7:         for opt_features_enabled in ('&1','&2') new   7:         for opt_features_enabled in ('11.2.0.4','12.2.0.1') old   9:     where "'&1'" != "'&2'" new   9:     where "'11.2.0.4'" != "'12.2.0.1'" PARAMETER                                '11.2.0.4'        '12.2.0.1'   DESCRIPTION ---------------------------------------- ----------------- ------------ --------------------------------------------------------------------- _optimizer_partial_join_eval             FALSE             TRUE         partial join evaluation parameter _optimizer_multi_table_outerjoin         FALSE             TRUE         allows multiple tables on the left of outerjoin _px_partial_rollup_pushdown              OFF               ADAPTIVE     perform partial rollup pushdown for parallel execution _optimizer_reduce_groupby_key            FALSE             TRUE         group-by key reduction _optimizer_cluster_by_rowid_batched      FALSE             TRUE         enable/disable the cluster by rowid batching feature _optimizer_inmemory_cluster_aware_dop    FALSE             TRUE         Affinitize DOP for inmemory objects _query_rewrite_use_on_query_computation  FALSE             TRUE         query rewrite use on query computation _pwise_distinct_enabled                  FALSE             TRUE         enable partition wise distinct _vector_encoding_mode                    OFF               MANUAL       enable vector encoding(OFF/MANUAL/AUTO) _px_concurrent                           FALSE             TRUE         enables pq with concurrent execution of serial inputs _px_filter_skew_handling                 FALSE             TRUE         enable correlated filter parallelization to handle skew _optimizer_ads_use_spd_cache             FALSE             TRUE         use Sql Plan Directives for caching ADS queries _px_wif_extend_distribution_keys         FALSE             TRUE         extend TQ data redistribution keys for window functions _optimizer_use_gtt_session_stats         FALSE             TRUE         use GTT session private statistics _optimizer_nlj_hj_adaptive_join          FALSE             TRUE         allow adaptive NL Hash joins _optimizer_ads_use_partial_results       FALSE             TRUE         Use partial results of ADS queries _sqlexec_hash_based_distagg_ssf_enabled  FALSE             TRUE         enable hash based distinct aggregation for single set gby queries _recursive_with_parallel                 FALSE             TRUE         Enable/disable parallelization of Recursive With _recursive_with_branch_iterations        1                 7            Expected number of iterations of the recurive branch of RW/CBY _xt_sampling_scan_granules               OFF               ON           Granule Sampling for Block Sampling of External Tables _optimizer_band_join_aware               FALSE             TRUE         enable the detection of band join by the optimizer _optimizer_inmemory_use_stored_stats     NEVER             AUTO         optimizer use stored statistics for in-memory tables _key_vector_create_pushdown_threshold    0                 20000        minimum grouping keys for key vector create pushdown _optimizer_unnest_scalar_sq              FALSE             TRUE         enables unnesting of of scalar subquery _ds_xt_split_count                       0                 1            Dynamic Sampling Service: split count for external tables _ds_sampling_method                      NO_QUALITY_METRIC PROGRESSIVE  Dynamic sampling method used _optimizer_ansi_join_lateral_enhance     FALSE             TRUE         optimization of left/full ansi-joins and lateral views _optimizer_proc_rate_level               OFF               BASIC        control the level of processing rates _adaptive_window_consolidator_enabled    FALSE             TRUE         enable/disable adaptive window consolidator PX plan _optimizer_cluster_by_rowid              FALSE             TRUE         enable/disable the cluster by rowid feature _distinct_agg_optimization_gsets         OFF               CHOOSE       Use Distinct Aggregate Optimization for Grouping Sets _optimizer_inmemory_autodop              FALSE             TRUE         optimizer autoDOP costing for in-memory _optimizer_inmemory_access_path          FALSE             TRUE         optimizer access path costing for in-memory _px_external_table_default_stats         FALSE             TRUE         the external table default stats collection enable/disable _optimizer_inmemory_minmax_pruning       FALSE             TRUE         controls use of min/max pruning for costing in-memory tables _mv_access_compute_fresh_data            OFF               ON           mv access compute fresh data _optimizer_multicol_join_elimination     FALSE             TRUE         eliminate multi-column key based joins _px_dist_agg_partial_rollup_pushdown     OFF               ADAPTIVE     perform distinct agg partial rollup pushdown for px execution _optimizer_vector_transformation         FALSE             TRUE         perform vector transform _optimizer_key_vector_pruning_enabled    FALSE             TRUE         enables or disables key vector partition pruning _bloom_filter_ratio                      30                35           bloom filter filtering ratio _optimizer_control_shard_qry_processing  65535             65534        control shard query processing optimizer_features_enable                11.2.0.4          12.2.0.1     optimizer plan compatibility parameter _px_parallelize_expression               FALSE             TRUE         enables or disables expression evaluation parallelization _optimizer_gather_stats_on_load          FALSE             TRUE         enable/disable online statistics gathering _optimizer_null_accepting_semijoin       FALSE             TRUE         enables null-accepting semijoin _px_replication_enabled                  FALSE             TRUE         enables or disables replication of small table scans _px_wif_dfo_declumping                   OFF               CHOOSE       NDV-aware DFO clumping of multiple window sorts _px_single_server_enabled                FALSE             TRUE         allow single-slave dfo in parallel query _optimizer_cluster_by_rowid_control      3                 129          internal control for cluster by rowid feature mode _optimizer_cbqt_or_expansion             OFF               ON           enables cost based OR expansion _optimizer_inmemory_bloom_filter         FALSE             TRUE         controls serial bloom filter for in-memory tables _optimizer_union_all_gsets               FALSE             TRUE         Use Union All Optimization for Grouping Sets _optimizer_enhanced_join_elimination     FALSE             TRUE         Enhanced(12.2) join elimination _optimizer_interleave_or_expansion       FALSE             TRUE         interleave OR Expansion during CBQT _optimizer_undo_cost_change              11.2.0.4          12.2.0.1     optimizer undo cost change _ds_enable_view_sampling                 FALSE             TRUE         Use sampling for views in Dynamic Sampling _optimizer_ansi_rearchitecture           FALSE             TRUE         re-architecture of ANSI left, right, and full outer joins _optimizer_cube_join_enabled             FALSE             TRUE         enable cube join _px_join_skew_handling                   FALSE             TRUE         enables skew handling for parallel joins _px_adaptive_dist_method                 OFF               CHOOSE       determines the behavior of adaptive distribution methods _optimizer_strans_adaptive_pruning       FALSE             TRUE         allow adaptive pruning of star transformation bitmap trees _optimizer_aggr_groupby_elim             FALSE             TRUE         group-by and aggregation elimination _px_groupby_pushdown                     CHOOSE            FORCE        perform group-by pushdown for parallel query _px_scalable_invdist_mcol                FALSE             TRUE         enable/disable px plan for percentile functions on multiple columns _optimizer_eliminate_subquery            FALSE             TRUE         consider elimination of subquery optimization _gby_vector_aggregation_enabled          FALSE             TRUE         enable group-by and aggregation using vector scheme _optimizer_hybrid_fpwj_enabled           FALSE             TRUE         enable hybrid full partition-wise join when TRUE _px_object_sampling_enabled              FALSE             TRUE         use base object sampling when possible for range distribution _px_filter_parallelized                  FALSE             TRUE         enables or disables correlated filter parallelization _optimizer_batch_table_access_by_rowid   FALSE             TRUE         enable table access by ROWID IO batching _px_cpu_autodop_enabled                  FALSE             TRUE         enables or disables auto dop cpu computation _optimizer_inmemory_table_expansion      FALSE             TRUE         optimizer in-memory awareness for table expansion _optimizer_inmemory_gen_pushable_preds   FALSE             TRUE         optimizer generate pushable predicates for in-memory _optimizer_enable_plsql_stats            FALSE             TRUE         Use statistics of plsql functions _optimizer_use_table_scanrate            OFF               HADOOP_ONLY  Use Table Specific Scan Rate _optimizer_use_xt_rowid                  FALSE             TRUE         Use external table rowid _optimizer_vector_base_dim_fact_factor   0                 200          cost based vector transform base dimension to base fact ratio 78 rows selected.