[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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20210506]完善tix指令碼.txt指令碼
- [20210506]]關於ORA-01450.txt
- [20200220]windows設定keepalive引數.txtWindows
- python預設引數的使用注意Python
- 04-預設引數
- C++預設引數C++
- TypeScript 函式可選引數和預設引數TypeScript函式
- es6中的引數預設值
- 檢視JVM預設配置引數JVM
- vue事件帶預設引數，怎麼傳遞其他引數Vue事件
- 3.3.2 函式的預設引數和佔位引數 函式過載函式
- webapi 設定swagger上請求引數的預設值WebAPISwagger
- [20180413]bash 位置引數.txt
- 檢視JVM預設引數及微調JVM啟動引數JVM
- python疑問5：位置引數，預設引數，可變引數，關鍵字引數，命名關鍵字引數區別Python
- c語言中預設引數的兩種型別C語言型別
- c#基礎-5.變長引數和引數預設值C#
- [20200620]expdp impdp exclude引數.txt
- springmvc引數設定預設值，多地址請求SpringMVC
- C++ 預設引數和佔位符C++
- 【ES6基礎】預設引數值
- [20210506]RAC crsctl status ... -v 獲取last started or status changes資訊.txtAST
- Retrofit統一新增post請求的預設引數
- [20190917]oracle引數deferred屬性.txtOracle
- [20200620]IMPDP TRANSFORM引數再探究.txtORM
- [20210826]核心引數kernel.sem.txt
- [20210209]修改CPU_COUNT引數.txt
- dbms_stats(zt)
- dbms_stats(轉)
- 預設引數和關鍵字參數列面上最大的區別是？
- 全網最適合入門的物件導向程式設計教程：48 Python函式方法與介面-位置引數、預設引數、可變引數和關鍵字引數物件程式設計Python函式
- [20190409]latch get 引數where and why.txt
- [20191204]hugepage相關引數含義.txt
- [20190417]隱含引數_SPIN_COUNT.txt
- [20180308]測試ARG_MAX引數.txt
- [20220913]hugepage相關引數含義.txt
- [20210310]db_lost_write_protect引數.txt
- [譯] ES6：理解引數預設值的實現細節