How to Lock, Unlock, Transfer Statistics in oracle

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.


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.


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).

PreferenceDescriptionDefault (11gR2)ScopeVersion
CASCADEDetermines if index stats should be gathered for the current table (TRUE, FALSE, AUTO_CASCADE).DBMS_STATS.AUTO_CASCADEG, D, S, T10gR1+
DEGREEDegree of parallelism (integer or DEFAULT_DEGREE).DBMS_STATS.DEFAULT_DEGREEG, D, S, T10gR1+
ESTIMATE_PERCENTPercentage of rows to sample when gathering stats (0.000001-100 or AUTO_SAMPLE_SIZE).DBMS_STATS.AUTO_SAMPLE_SIZEG, D, S, T10gR1+
METHOD_OPTControls column statistics collection and histogram creation.FOR ALL COLUMNS SIZE AUTOG, D, S, T10gR1+
NO_INVALIDATEDetermines if dependent cursors should be invalidated as a result of new stats on objects (TRUE, FALSE or AUTO_INVALIDATE).DBMS_STATS.AUTO_INVALIDATEG, D, S, T10gR1+
AUTOSTATS_TARGETDetermines which objects have stats gathered (ALL, ORACLE, AUTO).AUTOG10gR2+
GRANULARITYThe granularity of stats to be collected on partitioned objects (ALL, AUTO, DEFAULT, GLOBAL, ‘GLOBAL AND PARTITION’, PARTITION, SUBPARTITION).AUTOG, D, S, T10gR2+
PUBLISHDetermines if gathered stats should be published immediately or left in a pending state (TRUE, FALSE).TRUEG, D, S, T11gR2+
INCREMENTALDetermines whether incremental stats will be used for global statistics on partitioned objects, rather than generated using table scans (TRUE, FALSE).FALSEG, D, S, T11gR2+
CONCURRENTShould objects statistics be gathered on multiple objects at once, or one at a time (MANUAL, AUTOMATIC, ALL, OFF).OFFG12cR1+
GLOBAL_TEMP_TABLE_STATSShould stats on global temporary tables be session-specific or shared between sessions (SHARED, SESSION).SESSIONG, D, S12cR1+
INCREMENTAL_LEVELWhich level of synopses should be collected for incremental partitioned statistics (TABLE, PARTITION).PARTITIONG, D, S, T12cR1+
INCREMENTAL_STALENESSHow is staleness of partition statistics determined (USE_STALE_PERCENT, USE_LOCKED_STATS, NULL).NULLG, D, S, T12cR1+
TABLE_CACHED_BLOCKSThe number of blocks cached in the buffer cache during calculation of index cluster factor. Jonathan Lewis recommends “16” as a sensible value.1G, D, S, T12cR1+
OPTIONSUsed for the OPTIONS parameter of the GATHER_TABLE_STATS procedure (GATHER, GATHER AUTO).GATHERG, D, S, T12cR1+

The following shows their basic usage.

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');