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 in advance, 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_statsto lock the statistics on a table.Use the stored procedure
lock_partition_statsto lock the statistics on a partition.Use the stored procedure
lock_schema_statsto 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.
Parameter |
Description |
|---|---|
| ownname | The name of the database where the table is located. |
| tabname | The name of the table. |
| partname | The name of the partition. |
| stattype | The lock type. Three types are supported:
|
Example
Lock the table-level statistics of the t_part table and the statistics of the p0 partition for 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_statsto unlock the statistics on a table.Use the stored procedure
unlock_partition_statsto unlock statistics on a partition.Use the stored procedure
unlock_schema_statsto 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.
Parameter |
Description |
|---|---|
| ownname | The name of the database where the table is located. |
| tabname | The name of the table. |
| partname | The name of the partition. |
| stattype | The lock type. Three types are supported:
|
Notice
For a table, if both table-level and partition-level statistics are locked, they must be unlocked together when unlocking the table.
Example
Unlock the table-level statistics of the t_part table and the statistics of the p0 partition for user test.
CALL dbms_stats.unlock_table_stats('test', 't_part');
CALL dbms_stats.unlock_partition_stats('test', 't_part', 'p0');