Improvement of set_param, a new function of dbms_stats 11g

There are many optional gather _ *** _ stats in Dbms_stats. For example, cascade/estimate_percent, You can manually modify its default value.
10G provides set_param for modification, but it can only be used for global modification. 11G provides a lot of details, including the table/schema level.
The following is a separate verification:
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 only has 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-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-jun-2012
 
Restore to default value
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 provides many options
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