缺省参数oracle,[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