Stats:- Change statistics stale percentage in 11g

Object statistics will become stale whenever there is an dml operations performed on those objects, that means new/deletion of rows, adding columns, updating columns may change the row count, number of blocks, index entries etc. This staleness of object statistics has been determined or hardcoded to 10% of the modifications to the objects(tables etc), for which the same will be tracked in dba_tab_modifications (base table mon_mod$) and visible in dba_tab_statistics column stale_stats.

Just some basics of dba_tab_modifications,

Until 9i, one has to put the object in monitoring and the dml changes to the object will be captured
	ALTER TABLE table MONITORING; 
	ALTER TABLE table NOMONITORING;

From Oracle 10.1 and above table modification statistics:
	are enabled if STATISTICS_LEVEL is TYPICAL or ALL
	are disabled if STATISTICS_LEVEL is BASIC
	are collected in the SGA
	are automatically flushed to SYS.MON_MODS$
	       by SMON background process 
	       every 15 minutes to SYS.MON_MODS$ 
	if a significant number of rows has changed (possibly 10%)
	can be manually flushed to SYS.MON_MOD$ using:
	       DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO
	are reported in DBA_TAB_MODIFICATIONS
	modification in dba_tab_statistics, mark stale_stats column "yes"

Back to post now,
Oracle automatically determines which object need statistics which does not by crosschecking the stale_stats for those objects. If objects stats were stale i.e more than 10% it will collect the stats and does not collect for rest.

DBMS_STATS.gather_database_procedure arguments:- 
DBMS_STATS.GATHER_DATABASE_STATS (
   estimate_percent NUMBER   DEFAULT to_estimate_percent_type 
                                                (get_param('ESTIMATE_PERCENT')),
   block_sample     BOOLEAN  DEFAULT FALSE,
   method_opt       VARCHAR2 DEFAULT get_param('METHOD_OPT'),
   degree           NUMBER   DEFAULT to_degree_type(get_param('DEGREE')),
   granularity      VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'), 
   cascade          BOOLEAN  DEFAULT to_cascade_type(get_param('CASCADE')),
   stattab          VARCHAR2 DEFAULT NULL, 
   statid           VARCHAR2 DEFAULT NULL,
   options          VARCHAR2 DEFAULT 'GATHER',
   objlist          OUT      ObjectTab,
   statown          VARCHAR2 DEFAULT NULL,
   gather_sys       BOOLEAN  DEFAULT TRUE,
   no_invalidate    BOOLEAN  DEFAULT to_no_invalidate_type (
                                     get_param('NO_INVALIDATE')),
   obj_filter_list ObjectTab DEFAULT NULL);

Further read, Just for your understanding, dbms_stats.gather_***_stats(options parameter)

GATHER - gather statistics for all objects 
GATHER AUTO - gather statistics for all objects needing new statistics
GATHER STALE - gather statistics for all objects having stale statistics
GATHER EMPTY- gather statistics for all objects having no statistics
LIST AUTO - list objects needing new statistics
LIST STALE - list objects having stale statistics
LIST EMPTY - list objects having stale statistics

So, it is not good for some objects as even the <10% changes / staleness may be very large for very large tables and optimizer may choose inefficient plans. To fix this we have to manually collect the stats as Oracle skips those objects while automatic stats job runs until 10g.

In 11g, If you are into such situation where you want to change the staleness threshold for particular object, we have an option to modify or determine the stale percent of the statistics for database/schema/object level.

From the documentation,

The automatic statistics-gathering job uses the DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC procedure, which uses the same default parameter values as the other DBMS_STATS.GATHER_*_STATS procedures. The defaults are sufficient in most cases. However, it is occasionally necessary to change the default value of one of the statistics gathering parameters, which can be accomplished by using the DBMS_STATS.SET_*_PREF procedures. Parameter values should be changed at the smallest scope possible, ideally on a per-object bases. For example, if you want to change the staleness threshold for a specific table, so its statistics are considered stale when only 5% of the rows in the table have changed rather than the default 10%, you can change the STALE_PERCENT table preference for that one table using the DBMS_STATS.SET _TABLE_PREFS procedure. By changing the default value at the smallest scope you limit the amount of non-default parameter values that need to be manually managed.

Changing stale percent:-

SQL> DBMS_STAT.set_table_prefs('SH','SALES','STALE_PERCENT',5);
STALE_PERCENT - This value determines the percentage of rows in a table that have to change before the statistics on that table are deemed stale and should be regathered. The default value is 10%.

Which

changes the stale_percent to 5

for this table and when stats job runs it will not look for 10% rather it looks for 5% staleness and collect the stats for this table.

You can also set the stale_percent at database level and schema level using dbms_stats.set_database_prefs & dbms_stats.set_schema_prefs subprograms.

Hope this helps.

-Thanks
Geek DBA