Lock and unlock statistics

Statistics locking prevents the statistics from being updated, thus guaranteeing the stability of the statistics.

For example, if you choose not to collect statistics on a table, you can lock the statistics, so that when the plan is generated, the statistics are obtained by other means, such as dynamic sampling. If the data in a table remains stable, and you do not need to frequently collect statistics on the table, you can also lock the statistics in advance.

Lock statistics

You can lock statistics by using the following methods:

• Use the stored procedure lock_table_stats to lock the statistics on a table.

• Use the stored procedure lock_partition_stats to lock the statistics on a partition.

• Use the stored procedure lock_schema_stats to lock the statistics on all tables in a schema.

The stored procedures are defined as follows:

PROCEDURE lock_table_stats (
    ownname          VARCHAR2,
    tabname          VARCHAR2,
    stattype         VARCHAR2 DEFAULT 'ALL'
  );

PROCEDURE lock_partition_stats (
    ownname          VARCHAR2,
    tabname          VARCHAR2,
    partname         VARCHAR2
  );

PROCEDURE lock_schema_stats(
    ownname          VARCHAR2,
    STATTYPE         VARCHAR2 DEFAULT 'ALL'
  );

The following table describes the parameters.

Example

Lock table-level statistics on table t_part and statistics on partition p0 under the user test.

CALL dbms_stats.lock_table_stats('test', 't_part');
CALL dbms_stats.lock_partition_stats('test', 't_part', 'p0');

Unlock statistics

You can unlock statistics by using the following methods:

• Use the stored procedure unlock_table_stats to unlock the statistics on a table.

• Use the stored procedure unlock_partition_stats to unlock statistics on a partition.

• Use the stored procedure unlock_schema_stats to unlock statistics on all tables in a schema.

The stored procedures are defined as follows:

PROCEDURE unlock_table_stats (
    ownname          VARCHAR2,
    tabname          VARCHAR2,
    stattype         VARCHAR2 DEFAULT 'ALL'
  );

PROCEDURE unlock_partition_stats (
    ownname          VARCHAR2,
    tabname          VARCHAR2,
    partname         VARCHAR2
  );

PROCEDURE unlock_schema_stats(
    ownname          VARCHAR2,
    STATTYPE         VARCHAR2 DEFAULT 'ALL'
  );

The following table describes the parameters.

Example

Unlock the table-level statistics on table t_part and statistics on partition p0 under the user test.

CALL dbms_stats.unlock_table_stats('test', 't_part');
CALL dbms_stats.unlock_partition_stats('test', 't_part', 'p0');