Configuring Statistics Preference

It's very common for DBAs to gather database statistics during performance issues. Sometimes, the parameters used to gather these statistics can be very effective for a specific table or a set of tables.

There is a way to configure these statistics preferences for a table, a schema, or even an entire database to be default.

As we can imagine, the rule is very simple:

• Table preference: This is the highest priority. If it does not exist, other preferences will be used.
• Schema preference: This is second in the priority chain. If there is no table preference, the schema preference will be applied.
• Database preference: This is third in the priority chain. If neither table nor schema preferences are set, the database preference will be used.
• Default value: This will be used if no other preferences are set.

Table preference

Here is how to check the current statistics preference of a specific table:

To change a table's statistic preference:

SQL> begin DBMS_STATS.SET_TABLE_PREFS(USERNAME, 'TABLE_NAME', 'INCREMENTAL', 'FALSE'); end
/

Schema preference

Here is how to check the current statistics preference of an entire schema:

To change a schema's statistic preference:

SQL> begin DBMS_STATS.SET_SCHEMA_PREFS(USERNAME, 'STALE_PERCENT', '8'); end;
/

Database preference

Here is how to check the current statistics preference of your database:

To change a database's statistic preference:

SQL> begin DBMS_STATS.SET_GLOBAL_PREFS('METHOD_OPT', 'FOR ALL COLUMNS SIZE REPEAT'); end;
/