[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')
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2770929/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20140812]oracle12c dbms_stats的預設引數Oracle
- oracle dbms_stats(no_invalidate引數)Oracle
- [zt] 統計分析工具dbms_stats 預設引數在 9i、10g中的變化
- 【C++】引數的預設值C++
- Python預設引數的坑Python
- Python中的預設引數Python
- python預設引數的使用注意Python
- Python中的預設引數值Python
- TypeScript 函式可選引數和預設引數TypeScript函式
- [20210506]完善tix指令碼.txt指令碼
- 函式定義、函式的引數、函式的預設引數函式
- ES6 筆記 Rest 引數和 預設引數筆記REST
- Swift語言中為外部引數設定預設值可變引數常量引數變數引數輸入輸出引數Swift變數
- 檢視JVM預設配置引數JVM
- 陷阱！python引數預設值Python
- Python 函式(預設引數）Python函式
- javascript為函式設定預設引數JavaScript函式
- AIX7.1 VMO 引數預設設定AI
- vue事件帶預設引數，怎麼傳遞其他引數Vue事件
- 深入解析 ES6：Rest 引數和引數預設值REST
- 3.3.2 函式的預設引數和佔位引數 函式過載函式
- C語言預設引數值的實現C語言
- 檢視JVM預設引數及微調JVM啟動引數JVM
- javascript給函式引數設定預設值JavaScript函式
- reverse index的like測試和dbms_stats具體引數意義Index
- c#基礎-5.變長引數和引數預設值C#
- c語言中預設引數的兩種型別C語言型別
- python疑問5：位置引數，預設引數，可變引數，關鍵字引數，命名關鍵字引數區別Python
- 【ES6基礎】預設引數值
- C++ 預設引數和佔位符C++
- ES6特性之：引數預設值
- 【引數】DB_nK_CACHE_SIZE引數設定與資料庫預設塊大小之間的限制資料庫
- Retrofit統一新增post請求的預設引數
- Swift學習筆記（三十一）——引數的預設值Swift筆記
- springmvc引數設定預設值，多地址請求SpringMVC
- 輕鬆學習 JavaScript (3)：函式中的預設引數JavaScript函式
- Python函式引數預設值的陷阱和原理深究Python函式
- MySQL 5.5 和 5.6 預設引數值的差異總結MySql