Locking and unlocking

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

For example, if you choose not to collect statistics for 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 of the table, you can also lock the statistics in advance.

Locking

You can lock statistics by using the following methods:

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

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

• Use the stored procedure lock_schema_stats to lock the statistics of 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 of table t_part and statistics of 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');

Unlocking

You can unlock statistics by using the following methods:

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

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

• Use the stored procedure unlock_schema_stats to unlock statistics of 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.

Notice

If you have locked both the table-level statistics and partition-level statistics of the same table, you must unlock the table-level statistics and partition-level statistics of the table at the same time.

Example

Unlock the table-level statistics of table t_part and statistics of 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');