[20130924]12c dbms_stats包的一些缺省参数.txt

[20130924]12c dbms_stats包的一些缺省参数.txt

11G下：

SQL> @ver

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

SELECT DBMS_STATS.get_param ('AUTOSTATS_TARGET') AUTOSTATS_TARGET,

       DBMS_STATS.get_param ('CONCURRENT') CONCURRENT,

       DBMS_STATS.get_param ('CASCADE') CASCADE,

       DBMS_STATS.get_param ('DEGREE') DEGREE,

       DBMS_STATS.get_param ('ESTIMATE_PERCENT') estimate_percent,

       DBMS_STATS.get_param ('METHOD_OPT') method_opt,

       DBMS_STATS.get_param ('NO_INVALIDATE') no_invalidate,

       DBMS_STATS.get_param ('GRANULARITY') granularity,

       --DBMS_STATS.get_param ('OPTIONS') options,

       --DBMS_STATS.get_param ('GLOBAL_TEMP_TABLE_STATS') GLOBAL_TEMP_TABLE_STATS,

       DBMS_STATS.get_param ('INCREMENTAL') INCREMENTAL,

       --DBMS_STATS.get_param ('INCREMENTAL_LEVEL') INCREMENTAL_LEVEL,

       --DBMS_STATS.get_param ('INCREMENTAL_STALENESS') INCREMENTAL_STALENESS,

       DBMS_STATS.get_param ('PUBLISH') PUBLISH,

       DBMS_STATS.get_param ('STALE_PERCENT') STALE_PERCENT

  FROM DUAL;

Single Record View

As of: 2013-9-24 9:55:46

AUTOSTATS_TARGET:  AUTO

CONCURRENT:        FALSE

CASCADE:           DBMS_STATS.AUTO_CASCADE

DEGREE:            NULL

ESTIMATE_PERCENT:  DBMS_STATS.AUTO_SAMPLE_SIZE

METHOD_OPT:        FOR ALL COLUMNS SIZE AUTO

NO_INVALIDATE:     DBMS_STATS.AUTO_INVALIDATE

GRANULARITY:       AUTO

INCREMENTAL:       FALSE

PUBLISH:           TRUE

STALE_PERCENT:     10

12C增加了如下参数options,GLOBAL_TEMP_TABLE_STATS,INCREMENTAL_LEVEL,INCREMENTAL_STALENESS：
--   options - further specification of which objects to gather statistics for
--      'GATHER' - gather statistics on all objects in the schema
--      'GATHER AUTO' - gather all necessary statistics automatically. Oracle
--        implicitly determines which objects need new statistics.

/* Formatted on 2013/9/19 21:00:09 (QP5 v5.227.12220.39754) */

SELECT DBMS_STATS.get_param ('AUTOSTATS_TARGET') AUTOSTATS_TARGET,

       DBMS_STATS.get_param ('CONCURRENT') CONCYRRENT,

       DBMS_STATS.get_param ('CASCADE') CASCADE,

       DBMS_STATS.get_param ('DEGREE') DEGREE,

       DBMS_STATS.get_param ('ESTIMATE_PERCENT') estimate_percent,

       DBMS_STATS.get_param ('METHOD_OPT') method_opt,

       DBMS_STATS.get_param ('NO_INVALIDATE') no_invalidate,

       DBMS_STATS.get_param ('GRANULARITY') granularity,

       DBMS_STATS.get_param ('OPTIONS') options,

       DBMS_STATS.get_param ('GLOBAL_TEMP_TABLE_STATS') GLOBAL_TEMP_TABLE_STATS,

       DBMS_STATS.get_param ('INCREMENTAL') INCREMENTAL,

       DBMS_STATS.get_param ('INCREMENTAL_LEVEL') INCREMENTAL_LEVEL,

       DBMS_STATS.get_param ('INCREMENTAL_STALENESS') INCREMENTAL_STALENESS,

       DBMS_STATS.get_param ('PUBLISH') PUBLISH,

       DBMS_STATS.get_param ('STALE_PERCENT') STALE_PERCENT

  FROM DUAL;

Record View

As of: 2013/9/19 21:00:59

AUTOSTATS_TARGET:         AUTO

CONCYRRENT:               OFF

CASCADE:                  DBMS_STATS.AUTO_CASCADE

DEGREE:                   NULL

ESTIMATE_PERCENT:         DBMS_STATS.AUTO_SAMPLE_SIZE

METHOD_OPT:               FOR ALL COLUMNS SIZE AUTO

NO_INVALIDATE:            DBMS_STATS.AUTO_INVALIDATE

GRANULARITY:              AUTO

OPTIONS:                  GATHER

GLOBAL_TEMP_TABLE_STATS:  SESSION

INCREMENTAL:              FALSE

INCREMENTAL_LEVEL:        PARTITION

INCREMENTAL_STALENESS:    

PUBLISH:                  TRUE

STALE_PERCENT:            10