Introduction
Need for want of lock statistics in a oracle table in some cases, for example
1. Don’t want a table to be analyzed by schedule statistics job but want to analyze it later or at higher estimate
2. Dont want to generate the statistics for the table for performance reason
3. Don’t want server to spend time generate statistics when the table data doesn’t change
There could be many more case where we want to lock statistics
Error:
ORA-20005: object statistics are locked (stattype = ALL)
While running sql tuning advisor for a sql_id We found one table has stale statistics.
While running gather stats for that table got below error.
SQL> BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS (
3 ownname => ‘FMS’,
4 tabname => ‘HOMES_RGP_REQUEST_LINES’,
5 cascade => TRUE, —-
6 method_opt=>’FOR ALL INDEXED COLUMNS SIZE SKEWONLY’,
7 granularity => ‘ALL’,
8 estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE,
9 degree => CEIL(DBMS_STATS.AUTO_DEGREE/2),
10 END;
11 /
BEGIN
*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at “FMS.HOMES_RGP_REQUEST_LINES”, line 34634
ORA-06512: at line 2
STATTYPE_LOCKED =ALL ——> stats are locked for the table
STATTYPE_LOCKED = NULL – > stats are not locked
Cause:
SQL> select owner,table_name,STATTYPE_LOCKED from dba_tab_statistics where table_name=’FMS’ and owner=’HOMES_RGP_REQUEST_LINES’;
OWNER TABLE_NAME STATTYPE_LOCKED
———- ——————— ————————
FMS HOMES_RGP_REQUEST_LINES ALL
If stats are locked for a table or schema, then gathering stats will throw ORA-20005 error
STATTYPE_LOCKED – ALL stats are locked for the table
STATTYPE_LOCKED – NULL stats are not locked
Workaround
Unlock stats and Run gather stats again
EXEC DBMS_STATS.unlock_table_stats(‘FMS’,’HOMES_RGP_REQUEST_LINES’);
BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS (
3 ownname => ‘FMS’,
4 tabname => ‘HOMES_RGP_REQUEST_LINES’,
5 cascade => TRUE, —-
6 method_opt=>’FOR ALL INDEXED COLUMNS SIZE SKEWONLY’,
7 granularity => ‘ALL’,
8 estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE,
9 degree => CEIL(DBMS_STATS.AUTO_DEGREE/2),
10 END;
11 /
PL/SQL procedure successfully completed.
