[20140812]oracle12c dbms_stats的預設引數
[20140812]oracle 12c. dbms_stats的預設引數.txt
--昨天,別人講我寫的連結依舊漏掉寶dbms_stats的預設引數,今天看了一下,確實如此,寫一個比較全面的,
--實際上這些值儲存在SYS.OPTSTAT_HIST_CONTROL$中.
--連結:
http://blog.itpub.net/267265/viewspace-773277/
直接查詢它就ok了.
SYS@test> select sname, sval1,spare1, spare4 from sys.OPTSTAT_HIST_CONTROL$ order by sname;
SNAME | SVAL1| SPARE1|SPARE4
------------------------------|----------|----------|----------------------------------------
APPROXIMATE_NDV | | 1|TRUE
AUTOSTATS_TARGET | | 1|AUTO
CASCADE | | 1|DBMS_STATS.AUTO_CASCADE
CONCURRENT | | 1|OFF
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
GLOBAL_TEMP_TABLE_STATS | | 1|SESSION
GRANULARITY | | 1|AUTO
INCREMENTAL | | 1|FALSE
INCREMENTAL_INTERNAL_CONTROL | | 1|TRUE
INCREMENTAL_LEVEL | | 1|PARTITION
INCREMENTAL_STALENESS | | 1|
JOB_OVERHEAD | | 1|-1
JOB_OVERHEAD_PERC | | 1|1
METHOD_OPT | | 1|FOR ALL COLUMNS SIZE AUTO
MON_MODS_ALL_UPD_TIME | | |
NO_INVALIDATE | | 1|DBMS_STATS.AUTO_INVALIDATE
OPTIONS | | 1|GATHER
PUBLISH | | 1|TRUE
SKIP_TIME | | |
SPD_RETENTION_WEEKS | | 1|53
STALE_PERCENT | | 1|10
STATS_RETENTION | 31| 1|
SYS_FLAGS | | |1
TABLE_CACHED_BLOCKS | | 1|1
TRACE | | 1|0
30 rows selected.
--注:STATS_RETENTION的預設值保持在SVAL1.SPARE1=1 表示SPARE4的值是oracle的預設引數,沒有改動過.
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;
select
DBMS_STATS.get_prefs ('APPROXIMATE_NDV') APPROXIMATE_NDV,
DBMS_STATS.get_prefs ('AUTOSTATS_TARGET') AUTOSTATS_TARGET,
DBMS_STATS.get_prefs ('CASCADE') CASCADE,
DBMS_STATS.get_prefs ('CONCURRENT') CONCURRENT,
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 ('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 ('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 ('PUBLISH') PUBLISH,
DBMS_STATS.get_prefs ('SKIP_TIME') SKIP_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 ('SYS_FLAGS') SYS_FLAGS,
DBMS_STATS.get_prefs ('TABLE_CACHED_BLOCKS') TABLE_CACHED_BLOCKS,
DBMS_STATS.get_prefs ('TRACE') TRACE,
1 from dual
32 rows selected.
--執行如下:
select
DBMS_STATS.get_prefs ('APPROXIMATE_NDV') APPROXIMATE_NDV,
DBMS_STATS.get_prefs ('AUTOSTATS_TARGET') AUTOSTATS_TARGET,
DBMS_STATS.get_prefs ('CASCADE') CASCADE,
DBMS_STATS.get_prefs ('CONCURRENT') CONCURRENT,
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 ('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 ('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 ('PUBLISH') PUBLISH,
DBMS_STATS.get_prefs ('SKIP_TIME') SKIP_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 ('SYS_FLAGS') SYS_FLAGS,
DBMS_STATS.get_prefs ('TABLE_CACHED_BLOCKS') TABLE_CACHED_BLOCKS,
DBMS_STATS.get_prefs ('TRACE') TRACE,
1 from dual
Record View
As of: 2014/8/11 22:01:54
APPROXIMATE_NDV: TRUE
AUTOSTATS_TARGET: AUTO
CASCADE: DBMS_STATS.AUTO_CASCADE
CONCURRENT: OFF
DEBUG: 0
DEGREE: NULL
ENABLE_HYBRID_HISTOGRAMS: 3
ENABLE_TOP_FREQ_HISTOGRAMS: 3
ESTIMATE_PERCENT: DBMS_STATS.AUTO_SAMPLE_SIZE
GATHER_AUTO: AFTER_LOAD
GLOBAL_TEMP_TABLE_STATS: SESSION
GRANULARITY: AUTO
INCREMENTAL: FALSE
INCREMENTAL_INTERNAL_CONTROL: TRUE
INCREMENTAL_LEVEL: PARTITION
INCREMENTAL_STALENESS:
JOB_OVERHEAD: -1
JOB_OVERHEAD_PERC: 1
METHOD_OPT: FOR ALL COLUMNS SIZE AUTO
MON_MODS_ALL_UPD_TIME:
NO_INVALIDATE: DBMS_STATS.AUTO_INVALIDATE
OPTIONS: GATHER
PUBLISH: TRUE
SKIP_TIME:
SPD_RETENTION_WEEKS: 53
STALE_PERCENT: 10
STATS_RETENTION:
SYS_FLAGS: 1
TABLE_CACHED_BLOCKS: 1
TRACE: 0
1: 1
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-1249558/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20210506]oracle19c dbms_stats的預設引數.txtOracle
- oracle dbms_stats(no_invalidate引數)Oracle
- [zt] 統計分析工具dbms_stats 預設引數在 9i、10g中的變化
- C++預設引數C++
- 【C++】引數的預設值C++
- Python預設引數的坑Python
- Python中的預設引數Python
- python預設引數的使用注意Python
- Python中的預設引數值Python
- TypeScript 函式可選引數和預設引數TypeScript函式
- 函式定義、函式的引數、函式的預設引數函式
- 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語言中預設引數的兩種型別C語言型別
- c#基礎-5.變長引數和引數預設值C#
- python疑問5：位置引數，預設引數，可變引數，關鍵字引數，命名關鍵字引數區別Python
- 【ES6基礎】預設引數值
- C++ 預設引數和佔位符C++
- ES6特性之：引數預設值
- 【引數】DB_nK_CACHE_SIZE引數設定與資料庫預設塊大小之間的限制資料庫
- webapi 設定swagger上請求引數的預設值WebAPISwagger
- Oracle12c版本中未歸檔隱藏引數Oracle
- Retrofit統一新增post請求的預設引數
- Swift學習筆記（三十一）——引數的預設值Swift筆記
- springmvc引數設定預設值，多地址請求SpringMVC
- 輕鬆學習 JavaScript (3)：函式中的預設引數JavaScript函式