Locking Stats
To prevent statistics being overwritten, you can lock the stats at schema, table or partition level.
EXEC DBMS_STATS.lock_schema_stats('SCOTT');
EXEC DBMS_STATS.lock_table_stats('SCOTT', 'EMP');
EXEC DBMS_STATS.lock_partition_stats('SCOTT', 'EMP', 'EMP_PART1');
If you need to replace the stats, they must be unlocked.
EXEC DBMS_STATS.unlock_schema_stats('SCOTT');
EXEC DBMS_STATS.unlock_table_stats('SCOTT', 'EMP');
EXEC DBMS_STATS.unlock_partition_stats('SCOTT', 'EMP', 'EMP_PART1');
Locking stats can be very useful to prevent automated jobs from changing them. This is especially useful with tables used for ETL processes. If the stats are gathered when the tables are empty, they will not reflect the real quantity of data during the load process. Instead, either gather stats each time the data is loaded, or gather them once on a full table and lock them.
Transfering Stats
It is possible to transfer statistics between servers allowing consistent execution plans between servers with varying amounts of data. First the statistics must be collected into a statistics table. In the following examples the statistics for the APPSCHEMA user are collected into a new table, STATS_TABLE, which is owned by DBASCHEMA.
EXEC DBMS_STATS.create_stat_table('DBASCHEMA','STATS_TABLE');
EXEC DBMS_STATS.export_schema_stats('APPSCHEMA','STATS_TABLE',NULL,'DBASCHEMA');
This table can then be transfered to another server using your preferred method (Export/Import, SQL*Plus COPY etc.) and the stats imported into the data dictionary as follows.
EXEC DBMS_STATS.import_schema_stats('APPSCHEMA','STATS_TABLE',NULL,'DBASCHEMA');
EXEC DBMS_STATS.drop_stat_table('DBASCHEMA','STATS_TABLE');
Setting Preferences
Since Oracle 10g, many of the default values of parameters for the DBMS_STATS procedures have changed from being hard coded to using preferences. In Oracle 10g, these preferences could be altered using the SET_PARAM procedure.
EXEC DBMS_STATS.set_param('DEGREE', '5');
In 11g, the SET_PARAM procedure was deprecated in favor of a layered approach to preferences. The four levels of preferences are amended with the following procedures.
SET_GLOBAL_PREFS: Used to set global preferences, including some specific to the automatic stats collection job.SET_DATABASE_PREFS: Sets preferences for the whole database.SET_SCHEMA_PREFS: Sets preferences for a specific schema.SET_TABLE_PREFS: Sets preferences for a specific table.
The available preferences are listed below, along with the available scope (G=Global, D=Database, S=Schema, T=Table).
| Preference | Description | Default (11gR2) | Scope | Version |
|---|---|---|---|---|
| CASCADE | Determines if index stats should be gathered for the current table (TRUE, FALSE, AUTO_CASCADE). | DBMS_STATS.AUTO_CASCADE | G, D, S, T | 10gR1+ |
| DEGREE | Degree of parallelism (integer or DEFAULT_DEGREE). | DBMS_STATS.DEFAULT_DEGREE | G, D, S, T | 10gR1+ |
| ESTIMATE_PERCENT | Percentage of rows to sample when gathering stats (0.000001-100 or AUTO_SAMPLE_SIZE). | DBMS_STATS.AUTO_SAMPLE_SIZE | G, D, S, T | 10gR1+ |
| METHOD_OPT | Controls column statistics collection and histogram creation. | FOR ALL COLUMNS SIZE AUTO | G, D, S, T | 10gR1+ |
| NO_INVALIDATE | Determines if dependent cursors should be invalidated as a result of new stats on objects (TRUE, FALSE or AUTO_INVALIDATE). | DBMS_STATS.AUTO_INVALIDATE | G, D, S, T | 10gR1+ |
| AUTOSTATS_TARGET | Determines which objects have stats gathered (ALL, ORACLE, AUTO). | AUTO | G | 10gR2+ |
| GRANULARITY | The granularity of stats to be collected on partitioned objects (ALL, AUTO, DEFAULT, GLOBAL, ‘GLOBAL AND PARTITION’, PARTITION, SUBPARTITION). | AUTO | G, D, S, T | 10gR2+ |
| PUBLISH | Determines if gathered stats should be published immediately or left in a pending state (TRUE, FALSE). | TRUE | G, D, S, T | 11gR2+ |
| INCREMENTAL | Determines whether incremental stats will be used for global statistics on partitioned objects, rather than generated using table scans (TRUE, FALSE). | FALSE | G, D, S, T | 11gR2+ |
| CONCURRENT | Should objects statistics be gathered on multiple objects at once, or one at a time (MANUAL, AUTOMATIC, ALL, OFF). | OFF | G | 12cR1+ |
| GLOBAL_TEMP_TABLE_STATS | Should stats on global temporary tables be session-specific or shared between sessions (SHARED, SESSION). | SESSION | G, D, S | 12cR1+ |
| INCREMENTAL_LEVEL | Which level of synopses should be collected for incremental partitioned statistics (TABLE, PARTITION). | PARTITION | G, D, S, T | 12cR1+ |
| INCREMENTAL_STALENESS | How is staleness of partition statistics determined (USE_STALE_PERCENT, USE_LOCKED_STATS, NULL). | NULL | G, D, S, T | 12cR1+ |
| TABLE_CACHED_BLOCKS | The number of blocks cached in the buffer cache during calculation of index cluster factor. Jonathan Lewis recommends “16” as a sensible value. | 1 | G, D, S, T | 12cR1+ |
| OPTIONS | Used for the OPTIONS parameter of the GATHER_TABLE_STATS procedure (GATHER, GATHER AUTO). | GATHER | G, D, S, T | 12cR1+ |
The following shows their basic usage.
EXEC DBMS_STATS.set_global_prefs('AUTOSTATS_TARGET', 'AUTO');
EXEC DBMS_STATS.set_database_prefs('STALE_PERCENT', '15');
EXEC DBMS_STATS.set_schema_prefs('SCOTT','DEGREE', '5');
EXEC DBMS_STATS.set_table_prefs('SCOTT', 'EMP', 'CASCADE', 'FALSE');
Global preferences can be reset and the other layers of preferences deleted using the following procedures.
EXEC DBMS_STATS.reset_global_pref_defaults;
EXEC DBMS_STATS.delete_database_prefs('CASCADE');
EXEC DBMS_STATS.delete_schema_prefs('SCOTT','DEGREE');
EXEC DBMS_STATS.delete_table_prefs('SCOTT', 'EMP', 'CASCADE');
ref: https://oracle-base.com/articles/misc/cost-based-optimizer-and-database-statistics