[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.