dbms_stats 11g新功能 set_param的改进
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
评论暂时关闭