You can lock statistics to prevent them from being updated and keep them stable. For example, when you want to keep the table statistics unchanged after you collect statistics on a table with stable data, you can lock the table statistics.
The DBMS_STATS package provides the following procedures for locking or unlocking statistics:
lock_table_stats: locks statistics on a table.lock_partition_stats: locks statistics on a partition.lock_schema_stats: locks statistics on all tables in a schema.unlock_table_stats: unlocks the statistics of a table.unlock_partition_stats: unlocks statistics on a partition.unlock_schema_stats: unlocks statistics on all tables in a schema.
Note that a native Oracle database supports three types of locks: ALL, DATA, and CACHE, and the OceanBase Database optimizer supports only ALL. If you lock statistics on a table and a partition, you must unlock statistics on the table and partition at the same time.
# Lock the table-level statistics on the T_PART table and statistics on the P0 partition under the TEST user.
call dbms_stats.lock_table_stats('TEST', 'T_PART');
call dbms_stats.lock_partition_stats('TEST', 'T_PART', 'P0');
# Unlock the table-level statistics on the T_PART table and statistics on the P0 partition under the TEST user.
call dbms_stats.unlock_table_stats('TEST', 'T_PART');
call dbms_stats.unlock_partition_stats('TEST', 'T_PART', 'P0');