How to Lock/Unlock Table Statistics in Oracle

There are a number of cases were you want to lock table statistics and schema statistics for example, if you have a highly volatile tables or intermediate table, where the volume of data changes drastically over a relatively short period of time or if you want a table not be analyzed by automatic statistics job but analyze it later.

 

If table is highly volatile then locking the statistics prevent in execution plan from changing and thus helps in plan stability for some period of time. That is why many guys prefer to unlock the stats, gather the stats and finally lock the stats.

How to check if table stats is locked:

SQL> SELECT stattype_locked FROM dba_tab_statistics WHERE table_name = '&TABLE_NAME' and owner = '&TABLE_OWNER';

If you will try to gather locked table statics, you will get the below error:

SQL> EXEC dbms_stats.gather_table_stats(ownname => ‘SANCS’, tabname => ‘ORDER’ , estimate_percent => dbms_stats.auto_sample_size);

ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at “SYS.DBMS_STATS”, line 10640
ORA-06512: at “SYS.DBMS_STATS”, line 10664
ORA-06512: at line 1

How to Lock Table Statistics?

SQL>exec dbms_stats.lock_table_stats('<schema>', '<Table>');

 Example:
exec dbms_stats.lock_table_stats('SANCS', 'ORDER');

PL/SQL procedure successfully completed.

How to Unlock Table Statistics?

SQL> exec dbms_stats.unlock_table_stats('<schema>', '<Table>');



Example:

SQL> exec dbms_stats.unlock_table_stats('SANCS', 'ORDER');

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_STATS.unlock_schema_stats('APPS');
 PL/SQL procedure successfully completed.