[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')