ITPub博客

首页 > Linux操作系统 > Linux操作系统 > dbms_stats 11g新功能 set_param的改进

dbms_stats 11g新功能 set_param的改进

原创 Linux操作系统 作者:myownstars 时间:2012-06-21 16:48:33 0 删除 编辑

Dbms_statsgather_***_stats中有很多可选项,譬如cascade/estimate_percent,可以手工修改其默认值

10g提供了set_param用于修改,但是只能用于全局修改; 11g则细化了很多,包括table/schema级别

下面来分别验证一下:

1  10g

SQL> select * from v$version;

 

BANNER

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

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi

PL/SQL Release 10.2.0.5.0 - Production

CORE    10.2.0.5.0      Production

TNS for HPUX: Version 10.2.0.5.0 - Production

NLSRTL Version 10.2.0.5.0 - Production

 

Dbms_stats只有set_param

PROCEDURE SET_PARAM

 Argument Name                  Type                    In/Out Default?

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

 PNAME                          VARCHAR2                IN

 PVAL                              VARCHAR2                IN

 

SQL> select SNAME, nvl(SPARE4,SVAL1) as value from optstat_hist_control$ where sname in ('CASCADE','ESTIMATE_PERCENT','DEGREE','METHOD_OPT','NO_INVALIDATE','GRANULARITY');

 

SNAME                          VALUE

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

CASCADE                        DBMS_STATS.AUTO_CASCADE

ESTIMATE_PERCENT     DBMS_STATS.AUTO_SAMPLE_SIZE

DEGREE                         NULL

METHOD_OPT              FOR ALL COLUMNS SIZE AUTO

NO_INVALIDATE            DBMS_STATS.AUTO_INVALIDATE

GRANULARITY               AUTO

 

6 rows selected.

SQL> exec dbms_stats.set_param('ESTIMATE_PERCENT',10);

 

PL/SQL procedure successfully completed.

 

SQL> select SNAME, nvl(SPARE4,SVAL1) as value from optstat_hist_control$ where sname in ('CASCADE','ESTIMATE_PERCENT','DEGREE','METHOD_OPT','NO_INVALIDATE','GRANULARITY');

 

SNAME                          VALUE

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

CASCADE                        DBMS_STATS.AUTO_CASCADE

ESTIMATE_PERCENT               10

DEGREE                         NULL

METHOD_OPT                     FOR ALL COLUMNS SIZE AUTO

NO_INVALIDATE                  DBMS_STATS.AUTO_INVALIDATE

GRANULARITY                    AUTO

 

6 rows selected.

 

SQL> create table t_temp as select owner,object_id from dba_objects;

 

Table created.

 

SQL> exec dbms_stats.gather_table_stats('SYS','T_TEMP');

 

PL/SQL procedure successfully completed.

 

SQL> select num_rows,sample_size,last_analyzed from dba_tables where table_name='T_TEMP';

 

  NUM_ROWS SAMPLE_SIZE LAST_ANALYZ

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

     38510        3851 21-JUN-2012

 

SQL>  exec dbms_stats.set_param('ESTIMATE_PERCENT',100);

 

PL/SQL procedure successfully completed.

 

SQL> exec dbms_stats.gather_table_stats('SYS','T_TEMP');

 

PL/SQL procedure successfully completed.

 

SQL> select num_rows,sample_size,last_analyzed from dba_tables where table_name='T_TEMP';

 

  NUM_ROWS SAMPLE_SIZE LAST_ANALYZ

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

     38270       38270 21-JUN-2012

 

还原成默认值

SQL> exec dbms_stats.set_param('ESTIMATE_PERCENT',null);

 

PL/SQL procedure successfully completed.

 

SQL>  select SNAME, nvl(SPARE4,SVAL1) as value from optstat_hist_control$ where sname in ('CASCADE','ESTIMATE_PERCENT','DEGREE','METHOD_OPT','NO_INVALIDATE','GRANULARITY');

 

SNAME                          VALUE

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

CASCADE                        DBMS_STATS.AUTO_CASCADE

ESTIMATE_PERCENT               DBMS_STATS.AUTO_SAMPLE_SIZE

DEGREE                         NULL

METHOD_OPT                     FOR ALL COLUMNS SIZE AUTO

NO_INVALIDATE                  DBMS_STATS.AUTO_INVALIDATE

GRANULARITY                    AUTO

 

2 11g

SQL> select * from v$version;

 

BANNER

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

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

PL/SQL Release 11.2.0.2.0 - Production

CORE    11.2.0.2.0      Production

TNS for IBM/AIX RISC System/6000: Version 11.2.0.2.0 - Production

NLSRTL Version 11.2.0.2.0 – Production

 

Dbms_stats则提供很多种选择

PROCEDURE SET_DATABASE_PREFS

 Argument Name                  Type                    In/Out Default?

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

 PNAME                          VARCHAR2                IN

 PVALUE                         VARCHAR2                IN

 ADD_SYS                        BOOLEAN                 IN     DEFAULT

PROCEDURE SET_GLOBAL_PREFS

 Argument Name                  Type                    In/Out Default?

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

 PNAME                          VARCHAR2                IN

 PVALUE                         VARCHAR2                IN

PROCEDURE SET_PARAM

 Argument Name                  Type                    In/Out Default?

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

 PNAME                          VARCHAR2                IN

 PVAL                           VARCHAR2                IN

PROCEDURE SET_SCHEMA_PREFS

 Argument Name                  Type                    In/Out Default?

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

 OWNNAME                        VARCHAR2                IN

 PNAME                          VARCHAR2                IN

 PVALUE                         VARCHAR2                IN

PROCEDURE SET_TABLE_PREFS

 Argument Name                  Type                    In/Out Default?

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

 OWNNAME                        VARCHAR2                IN

 TABNAME                        VARCHAR2                IN

 PNAME                          VARCHAR2                IN

 PVALUE                         VARCHAR2                IN

 

 

SQL> create table t_temp as select owner,object_id from dba_objects;

 

Table created.

 

SQL> select num_rows,sample_size from dba_tables where table_name='T_TEMP';

 

  NUM_ROWS SAMPLE_SIZE

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

 

SQL> desc dba_tab_stat_prefs

 Name                                      Null?    Type

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

 OWNER                                     NOT NULL VARCHAR2(30)

 TABLE_NAME                                NOT NULL VARCHAR2(30)

 PREFERENCE_NAME                                    VARCHAR2(30)

 PREFERENCE_VALUE                                   VARCHAR2(1000)

 

SQL> select PREFERENCE_NAME,PREFERENCE_VALUE from dba_tab_stat_prefs where wner='SYS' and TABLE_NAME='T_TEMP';

 

no rows selected

SQL>  exec dbms_stats.set_table_prefs('SYS','T_TEMP','ESTIMATE_PERCENT',100);

 

PL/SQL procedure successfully completed.

 

SQL> select PREFERENCE_NAME,PREFERENCE_VALUE from dba_tab_stat_prefs where wner='SYS' and TABLE_NAME='T_TEMP';

 

PREFERENCE_NAME                PREFERENCE_VALUE

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

ESTIMATE_PERCENT               100

 

SQL> exec dbms_stats.gather_table_stats('SYS','T_TEMP');

 

PL/SQL procedure successfully completed.

 

SQL> select num_rows,sample_size from dba_tables where table_name='T_TEMP';

 

  NUM_ROWS SAMPLE_SIZE

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

     59800       59800

 

 

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

请登录后发表评论 登录
全部评论

注册时间:2010-03-18

  • 博文量
    375
  • 访问量
    3095117