Sometimes the automatic statistics job should treat a table differently: gather more often, or not at all. DBMS_STATS preferences change how statistics are gathered for one table, and locking freezes a table's statistics so no job can replace them, which keeps a known good plan stable.
Code for This Guide
The main examples are in the examples/pkg-performance folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
dbms_stats.set_table_prefs(ownname, tabname, pname, pvalue); dbms_stats.get_prefs(pname [, ownname, tabname]) dbms_stats.lock_table_stats(ownname, tabname); dbms_stats.unlock_table_stats(ownname, tabname);
Common preferences are STALE_PERCENT, ESTIMATE_PERCENT, METHOD_OPT, and INCREMENTAL.
The example uses the FLIGHT_COPY table from How to Gather Statistics with DBMS_STATS.
A Preference and a Lock
Example:
begin
dbms_stats.set_table_prefs(user, 'FLIGHT_COPY', 'STALE_PERCENT', '5');
dbms_stats.lock_table_stats(user, 'FLIGHT_COPY'); -- keep them as they are
end;
/
select dbms_stats.get_prefs('STALE_PERCENT', user, 'FLIGHT_COPY') as stale_percent,
dbms_stats.get_prefs('STALE_PERCENT') as database_default
from dual;
select stattype_locked from user_tab_statistics where table_name = 'FLIGHT_COPY';
exec dbms_stats.gather_table_stats(user, 'FLIGHT_COPY')Output:
PL/SQL procedure successfully completed. STALE_PERCENT DATABASE_DEFAULT ________________ ___________________ 5 10 STATTYPE_LOCKED __________________ ALL BEGIN dbms_stats.gather_table_stats(user, 'FLIGHT_COPY'); END; * ERROR at line 1: ORA-20005: object statistics are locked (stattype = ALL) ORA-06512: at "SYS.DBMS_STATS", line 45301 ORA-06512: at "SYS.DBMS_STATS", line 44675 ORA-06512: at "SYS.DBMS_STATS", line 9279 ORA-06512: at "SYS.DBMS_STATS", line 10408 ORA-06512: at "SYS.DBMS_STATS", line 43815 ORA-06512: at "SYS.DBMS_STATS", line 44823 ORA-06512: at "SYS.DBMS_STATS", line 45282 ORA-06512: at line 1
FLIGHT_COPY now counts as stale after 5 percent of changes instead of the database default of 10. Its statistics are locked, STATTYPE_LOCKED shows ALL, and gathering fails with ORA-20005. The example drops the table afterward.
Things to Know
- The automatic job skips locked tables silently; only explicit gathering raises the error.
- FORCE => TRUE gathers statistics on a locked table anyway.
- Lock statistics of tables whose contents swing widely, such as staging tables, after gathering them at a representative moment.
Related Guides
Conclusion
DBMS_STATS table preferences tune how one table's statistics are gathered, and locking keeps them from changing at all. Use preferences for tables that change quickly and locks for tables whose good plan must not change.
