dbms_stats 11g新功能 set_param的改进

Dbms_stats的gather_***_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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/15480802/viewspace-733586/