[20210506]oracle19c dbms_stats的缺省参数.txt

[20210506]oracle19c dbms_stats的缺省参数.txt 1.环境： SYS@127.0.0.1:17101/dyhis> @ prxx ============================== PORT_STRING                   : x86_64/Linux 2.4.xx VERSION                       : 19.0.0.0.0 BANNER                        : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production BANNER_FULL                   : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.9.0.0.0 BANNER_LEGACY                 : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production CON_ID                        : 0 PL/SQL procedure successfully completed. 2.测试： --//dbms_stats的缺省参数保存在sys.OPTSTAT_HIST_CONTROL$表中。 SYS@127.0.0.1:17101/dyhis> column SPARE4 format a30 SYS@127.0.0.1:17101/dyhis> select sname, sval1,spare1, spare4 from sys.OPTSTAT_HIST_CONTROL$ order by sname; SNAME                               SVAL1     SPARE1 SPARE4 ------------------------------ ---------- ---------- ------------------------------ ANDV_ALGO_INTERNAL_OBSERVE                         1 FALSE APPROXIMATE_NDV                                    1 TRUE APPROXIMATE_NDV_ALGORITHM                          1 REPEAT OR HYPERLOGLOG AUTOSTATS_TARGET                                   1 AUTO AUTO_STAT_EXTENSIONS                               1 OFF AUTO_TASK_INTERVAL                                 1 900 AUTO_TASK_MAX_RUN_TIME                             1 3600 AUTO_TASK_STATUS                                   1 OFF CASCADE                                            1 DBMS_STATS.AUTO_CASCADE CONCURRENT                                         1 OFF COORDINATOR_TRIGGER_SHARD                          1 FALSE DEBUG                                              1 0 DEGREE                                             1 NULL ENABLE_HYBRID_HISTOGRAMS                           1 3 ENABLE_TOP_FREQ_HISTOGRAMS                         1 3 ESTIMATE_PERCENT                                   1 DBMS_STATS.AUTO_SAMPLE_SIZE GATHER_AUTO                                        1 AFTER_LOAD GATHER_SCAN_RATE                                   1 HADOOP_ONLY GLOBAL_TEMP_TABLE_STATS                            1 SESSION GRANULARITY                                        1 AUTO INCREMENTAL                                        1 FALSE INCREMENTAL_INTERNAL_CONTROL                       1 TRUE INCREMENTAL_LEVEL                                  1 PARTITION INCREMENTAL_STALENESS                              1 ALLOW_MIXED_FORMAT JOB_OVERHEAD                                       1 -1 JOB_OVERHEAD_PERC                                  1 1 MAINTAIN_STATISTICS_STATUS                         1 FALSE METHOD_OPT                                         1 FOR ALL COLUMNS SIZE AUTO MON_MODS_ALL_UPD_TIME NO_INVALIDATE                                      1 DBMS_STATS.AUTO_INVALIDATE OPTIONS                                            1 GATHER PREFERENCE_OVERRIDES_PARAMETER                     1 FALSE PUBLISH                                            1 TRUE ROOT_TRIGGER_PDB                                   1 FALSE SCAN_RATE                                          1 0 SKIP_TIME SNAPSHOT_UPD_TIME SPD_RETENTION_WEEKS                                1 53 STALE_PERCENT                                      1 10 STATS_RETENTION                        31          1 STAT_CATEGORY                                      1 OBJECT_STATS, REALTIME_STATS SYS_FLAGS                                            1 TABLE_CACHED_BLOCKS                                1 1 TRACE                                              1 0 WAIT_TIME_TO_UPDATE_STATS                          1 15 45 rows selected. --//注:STATS_RETENTION的缺省值保持在SVAL1,SPARE1=1 表示SPARE4的值是oracle的缺省参数,没有改动过. --//如果与以前12c比较，你可以发现有增加许多。12c仅仅返回32行。 select 'select ' a from dual union all select * from (select 'DBMS_STATS.get_prefs ('''||sname||''') '||sname||',' a  from sys.OPTSTAT_HIST_CONTROL$ order by sname) union all select '1 from dual ' from dual; A --------------------------------------------------------------------------------------- select DBMS_STATS.get_prefs ('ANDV_ALGO_INTERNAL_OBSERVE') ANDV_ALGO_INTERNAL_OBSERVE, DBMS_STATS.get_prefs ('APPROXIMATE_NDV') APPROXIMATE_NDV, DBMS_STATS.get_prefs ('APPROXIMATE_NDV_ALGORITHM') APPROXIMATE_NDV_ALGORITHM, DBMS_STATS.get_prefs ('AUTOSTATS_TARGET') AUTOSTATS_TARGET, DBMS_STATS.get_prefs ('AUTO_STAT_EXTENSIONS') AUTO_STAT_EXTENSIONS, DBMS_STATS.get_prefs ('AUTO_TASK_INTERVAL') AUTO_TASK_INTERVAL, DBMS_STATS.get_prefs ('AUTO_TASK_MAX_RUN_TIME') AUTO_TASK_MAX_RUN_TIME, DBMS_STATS.get_prefs ('AUTO_TASK_STATUS') AUTO_TASK_STATUS, DBMS_STATS.get_prefs ('CASCADE') CASCADE, DBMS_STATS.get_prefs ('CONCURRENT') CONCURRENT, DBMS_STATS.get_prefs ('COORDINATOR_TRIGGER_SHARD') COORDINATOR_TRIGGER_SHARD, DBMS_STATS.get_prefs ('DEBUG') DEBUG, DBMS_STATS.get_prefs ('DEGREE') DEGREE, DBMS_STATS.get_prefs ('ENABLE_HYBRID_HISTOGRAMS') ENABLE_HYBRID_HISTOGRAMS, DBMS_STATS.get_prefs ('ENABLE_TOP_FREQ_HISTOGRAMS') ENABLE_TOP_FREQ_HISTOGRAMS, DBMS_STATS.get_prefs ('ESTIMATE_PERCENT') ESTIMATE_PERCENT, DBMS_STATS.get_prefs ('GATHER_AUTO') GATHER_AUTO, DBMS_STATS.get_prefs ('GATHER_SCAN_RATE') GATHER_SCAN_RATE, DBMS_STATS.get_prefs ('GLOBAL_TEMP_TABLE_STATS') GLOBAL_TEMP_TABLE_STATS, DBMS_STATS.get_prefs ('GRANULARITY') GRANULARITY, DBMS_STATS.get_prefs ('INCREMENTAL') INCREMENTAL, DBMS_STATS.get_prefs ('INCREMENTAL_INTERNAL_CONTROL') INCREMENTAL_INTERNAL_CONTROL, DBMS_STATS.get_prefs ('INCREMENTAL_LEVEL') INCREMENTAL_LEVEL, DBMS_STATS.get_prefs ('INCREMENTAL_STALENESS') INCREMENTAL_STALENESS, DBMS_STATS.get_prefs ('JOB_OVERHEAD') JOB_OVERHEAD, DBMS_STATS.get_prefs ('JOB_OVERHEAD_PERC') JOB_OVERHEAD_PERC, DBMS_STATS.get_prefs ('MAINTAIN_STATISTICS_STATUS') MAINTAIN_STATISTICS_STATUS, DBMS_STATS.get_prefs ('METHOD_OPT') METHOD_OPT, DBMS_STATS.get_prefs ('MON_MODS_ALL_UPD_TIME') MON_MODS_ALL_UPD_TIME, DBMS_STATS.get_prefs ('NO_INVALIDATE') NO_INVALIDATE, DBMS_STATS.get_prefs ('OPTIONS') OPTIONS, DBMS_STATS.get_prefs ('PREFERENCE_OVERRIDES_PARAMETER') PREFERENCE_OVERRIDES_PARAMETER, DBMS_STATS.get_prefs ('PUBLISH') PUBLISH, DBMS_STATS.get_prefs ('ROOT_TRIGGER_PDB') ROOT_TRIGGER_PDB, DBMS_STATS.get_prefs ('SCAN_RATE') SCAN_RATE, DBMS_STATS.get_prefs ('SKIP_TIME') SKIP_TIME, DBMS_STATS.get_prefs ('SNAPSHOT_UPD_TIME') SNAPSHOT_UPD_TIME, DBMS_STATS.get_prefs ('SPD_RETENTION_WEEKS') SPD_RETENTION_WEEKS, DBMS_STATS.get_prefs ('STALE_PERCENT') STALE_PERCENT, DBMS_STATS.get_prefs ('STATS_RETENTION') STATS_RETENTION, DBMS_STATS.get_prefs ('STAT_CATEGORY') STAT_CATEGORY, DBMS_STATS.get_prefs ('SYS_FLAGS') SYS_FLAGS, DBMS_STATS.get_prefs ('TABLE_CACHED_BLOCKS') TABLE_CACHED_BLOCKS, DBMS_STATS.get_prefs ('TRACE') TRACE, DBMS_STATS.get_prefs ('WAIT_TIME_TO_UPDATE_STATS') WAIT_TIME_TO_UPDATE_STATS, 1 from dual 47 rows selected. --//保存输出，执行如下： SYS@127.0.0.1:17101/dyhis> @ prxx ============================== ANDV_ALGO_INTERNAL_OBSERVE    : FALSE APPROXIMATE_NDV               : TRUE APPROXIMATE_NDV_ALGORITHM     : REPEAT OR HYPERLOGLOG AUTOSTATS_TARGET              : AUTO AUTO_STAT_EXTENSIONS          : OFF AUTO_TASK_INTERVAL            : 900 AUTO_TASK_MAX_RUN_TIME        : 3600 AUTO_TASK_STATUS              : OFF CASCADE                       : DBMS_STATS.AUTO_CASCADE CONCURRENT                    : OFF COORDINATOR_TRIGGER_SHARD     : FALSE DEBUG                         : 0 DEGREE                        : NULL ENABLE_HYBRID_HISTOGRAMS      : 3 ENABLE_TOP_FREQ_HISTOGRAMS    : 3 ESTIMATE_PERCENT              : DBMS_STATS.AUTO_SAMPLE_SIZE GATHER_AUTO                   : AFTER_LOAD GATHER_SCAN_RATE              : HADOOP_ONLY GLOBAL_TEMP_TABLE_STATS       : SESSION GRANULARITY                   : AUTO INCREMENTAL                   : FALSE INCREMENTAL_INTERNAL_CONTROL  : TRUE INCREMENTAL_LEVEL             : PARTITION INCREMENTAL_STALENESS         : ALLOW_MIXED_FORMAT JOB_OVERHEAD                  : -1 JOB_OVERHEAD_PERC             : 1 MAINTAIN_STATISTICS_STATUS    : FALSE METHOD_OPT                    : FOR ALL COLUMNS SIZE AUTO MON_MODS_ALL_UPD_TIME         : NO_INVALIDATE                 : DBMS_STATS.AUTO_INVALIDATE OPTIONS                       : GATHER PREFERENCE_OVERRIDES_PARAMETER: FALSE PUBLISH                       : TRUE ROOT_TRIGGER_PDB              : FALSE SCAN_RATE                     : 0 SKIP_TIME                     : SNAPSHOT_UPD_TIME             : SPD_RETENTION_WEEKS           : 53 STALE_PERCENT                 : 10 STATS_RETENTION               : STAT_CATEGORY                 : OBJECT_STATS, REALTIME_STATS SYS_FLAGS                     : 1 TABLE_CACHED_BLOCKS           : 1 TRACE                         : 0 WAIT_TIME_TO_UPDATE_STATS     : 15 1                             : 1 PL/SQL procedure successfully completed. --//比如AUTO_TASK_STATUS,不过缺省是关闭的。 To Enable high frequency stats collection EXEC DBMS_STATS.SET_GLOBAL_PREFS('AUTO_TASK_STATUS','ON’); For, Maximum Run duration of each run, EXEC DBMS_STATS.SET_GLOBAL_PREFS('AUTO_TASK_MAX_RUN_TIME','600’); To Specify the frequency of auto stats collection in seconds EXEC DBMS_STATS.SET_GLOBAL_PREFS('AUTO_TASK_INTERVAL','240')