ITPub博客

首页 > Linux操作系统 > Linux操作系统 > [20130924]12c dbms_stats包的一些缺省参数.txt

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

原创 Linux操作系统 作者:lfree 时间:2013-09-24 09:59:05 0 删除 编辑
[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

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-773277/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2594
  • 访问量
    6370128