SET_TABLE_PREFS Procedure

Starting in 11g, there are three new arguments to the SET_TABLE_PREFS procedure, designed to allow the DBA more control over the freshness of their statistics:

• STALE_PERCENT - overrides the one-size-fits-all value of 10%.
• INCREMENTAL - Incremental statistics gathering for partition.
• PUBLISH - Allows the DBA to test new statistics before publishing them to the data dictionary.

This is an important 11g new feature because the DBA can now control the quality of optimizer statistics at the table level, thereby improving the behavior of the SQL optimizer to always choose the "best" execution plan for any query.

DBMS_STATS.SET_TABLE_PREFS

Oracle also has a feature ("PUBLISH, false") feature in DBMS_STATS.SET TABLE_PREFS that allows you to collect CBO statistics and use them for testing without "publishing" them into the production environment.

For example:

You can change the PUBLISH setting for the customers table in the SH schema, execute the statement:

EXEC DBMS_STATS.SET_TABLE_PREFS('SH', 'CUSTOMERS', 'PUBLISH', 'FALSE');

When Oracle automatically enables statistics collection, the default "staleness" threshold of 10% can now be changed with the DBMS_STATS.SET_TABLE_PREFS procedure:

EXEC DBMS_STATS.SET_TABLE_PREFS('HR', EMPS', 'STALE_PERCENT', '15%')

You can also expand upon this functionality at greater levels of breadth using these news procedures:

• DBMS_STATS.SET_SCHEMA_PREFS - This is like RUNNINGSET_TABLE_PREFS, but for all objects in the schema
• DBMS_STATS.SET_DATABASE_PREFS - This runs the SET_TABLE_PREFS for all of the schemas in the current database.
• DBMS_STATS.SET_GLOBAL_PREFS - This runs SET_TABLE_PREFS for all databases in the environment.