DBMS_STATS - PolarDB - Alibaba Cloud Documentation Center

The DBMS_STATS package helps you collect the statistics for database running, back up generated statistics, and restore statistics. You can specify a custom point in time for statistics backup and modify table statistics as needed. This way, the optimizer can generate accurate execution plans.

Prerequisites

The polar_dbms_stats.use_locked_stats parameter is set to ON.

You can execute the following statement to query the parameter value:

SHOW polar_dbms_stats.use_locked_stats;

You can execute the following statement to set the parameter:

SET polar_dbms_stats.use_locked_stats = ON;   -- Set the parameter to ON.
SET polar_dbms_stats.use_locked_stats = OFF;  -- Set the parameter to OFF.

List of DBMS_STATS functions

Function	Description
GATHER_SCHEMA_STATS Procedure	Collects and backs up schema statistics.
GATHER_TABLE_STATS Procedure	Collects and backs up table statistics.
GATHER_DATABASE_STATS Procedure	Collects and backs up database statistics.
GATHER_INDEX_STATS Procedure	Collects and backs up index statistics.
GATHER_COLUMN_STATS Procedure	Collects and backs up the statistics for a specified column.
RESTORE_SCHEMA_STATS Procedure	Restores the statistics for a specified schema.
RESTORE_DATABASE_STATS Procedure	Restores the statistics for a specified database.
RESTORE_TABLE_STATS Procedure	Restores the statistics for a specified table.
RESTORE_COLUMN_STATS Procedure	Restores the statistics for a specified column.
PURGE_STATS Procedure	Deletes the backup statistics that are generated before a specified point in time.
SET_TABLE_STATS Procedure	Configures the statistics for a specified table.
GET_TABLE_STATS Procedure	Obtains the statistics for a specified table.
SET_INDEX_STATS Procedure	Configures the statistics for a specified index.
GET_INDEX_STATS Procedure	Obtains the statistics for a specified index.
GET_COLUMN_STATS Procedure	Obtains the statistics for a specified column.
LOCK_TABLE_STATS Procedure	Locks the statistics for the table that is being used.
UNLOCK_TABLE_STATS Procedure	Unlocks the statistics for the table that is being used.
LOCK_SCHEMA_STATS Procedure	Locks the statistics for the schema that is being used.
UNLOCK_SCHEMA_STATS Procedure	Unlocks the statistics for the schema that is being used.
LOCK_COLUMN_STATS Procedure	Locks the statistics for the column that is being used.
UNLOCK_COLUMN_STATS Procedure	Unlocks the statistics for the column that is being used.
DELETE_TABLE_STATS Procedure	Deletes the existing table statistics.
DELETE_COLUMN_STATS Procedure	Deletes the existing column statistics.
DELETE_SCHEMA_STATS Procedure	Deletes the existing schema statistics.
DELETE_INDEX_STATS Procedure	Deletes the existing index statistics.

Prepare test data

The test data applies only to the examples that are provided in this topic.

Create a schema named dbms_stats_schema.

CREATE SCHEMA dbms_stats_schema;

Create a table named dbms_stats_test.

CREATE TABLE dbms_stats_schema.dbms_stats_test(id int);

Create indexes.

CREATE INDEX dbms_stats_index on dbms_stats_schema.dbms_stats_test(id);

Insert data into the table.

INSERT INTO dbms_stats_schema.dbms_stats_test values (generate_series(1,10000));

DBMS_STATS.GATHER_SCHEMA_STATS

This function is used to collect and back up schema statistics.

Syntax

DBMS_STATS.GATHER_SCHEMA_STATS (
 ownname VARCHAR2
 );

Parameters

Parameter	Description
ownname	The name of the schema you want to analyze.

Examples

Collect and back up schema statistics. Example:

CALL DBMS_STATS.GATHER_SCHEMA_STATS('dbms_stats_schema');

Query the backup status of schema statistics. Example:

SELECT * FROM polar_dbms_stats.backup_history;

DBMS_STATS.GATHER_TABLE_STATS

This function is used to collect and back up table statistics.

Syntax

DBMS_STATS.GATHER_TABLE_STATS (
 ownname VARCHAR2, 
 tabname VARCHAR2
 );

Parameters

Parameter	Description
ownname	The schema of the table you want to analyze.
tabname	The name of the table you want to analyze.

Examples

Collect and back up table statistics. Example:

CALL DBMS_STATS.GATHER_TABLE_STATS('dbms_stats_schema', 'dbms_stats_test');

Query the backup status of column statistics. Example:

SELECT * FROM polar_dbms_stats.backup_history;

DBMS_STATS.GATHER_DATABASE_STATS

This function is used to collect and back up database statistics.

Syntax

DBMS_STATS.GATHER_DATABASE_STATS ();

Examples

Collect and back up database statistics. Example:

CALL DBMS_STATS.GATHER_DATABASE_STATS();

Query the backup status of column statistics. Example:

SELECT * FROM polar_dbms_stats.backup_history;

DBMS_STATS.GATHER_INDEX_STATS

This function is used to collect and back up index statistics.

Syntax

DBMS_STATS.GATHER_INDEX_STATS (
 ownname VARCHAR2, 
 indname VARCHAR2
 );

Parameters

Parameter	Description
ownname	The schema of the index you want to analyze.
indname	The name of the index you want to analyze.

Examples

Collect and back up index statistics. Example:

CALL DBMS_STATS.GATHER_INDEX_STATS('dbms_stats_schema', 'dbms_stats_index');

Query the backup status of column statistics. Example:

SELECT * FROM polar_dbms_stats.backup_history;

DBMS_STATS.GATHER_COLUMN_STATS

This function is used to collect and back up the statistics for a specified column.

Syntax

DBMS_STATS.GATHER_COLUMN_STATS (
 ownname VARCHAR2,
 tablename VARCHAR2,
 attname TEXT
 );

Parameters

Parameter	Description
ownname	The schema of the column you want to analyze.
tablename	The name of the table that contains the column you want to analyze.
attname	The name of the column you want to analyze.

Examples

Collect and back up the statistics for a specified column. Example:

CALL DBMS_STATS.GATHER_COLUMN_STATS('dbms_stats_schema', 'dbms_stats_test', 'id');

Query the backup status of column statistics. Example:

SELECT * FROM polar_dbms_stats.backup_history;

DBMS_STATS.RESTORE_SCHEMA_STATS

This function is used to restore the statistics for a specified schema.

Syntax

DBMS_STATS.RESTORE_SCHEMA_STATS (
 ownname VARCHAR2, 
 as_of_timestamp TIMESTAMP WITH TIME ZONE
 );

Parameters

Parameter	Description
ownname	The name of the schema you want to restore.
as_of_timestamp	The point in time to which you want to restore statistics.

Examples

Restore the statistics for a specified schema. Example:

SELECT DBMS_STATS.RESTORE_SCHEMA_STATS('dbms_stats_schema',time) FROM polar_dbms_stats.backup_history WHERE unit='s';

Query the restored statistics. Example:

SELECT count(*) FROM polar_dbms_stats.relation_stats_locked WHERE relname LIKE 'dbms_stats_schema%';

DBMS_STATS.RESTORE_DATABASE_STATS

This function is used to restore database statistics.

Syntax

DBMS_STATS.RESTORE_DATABASE_STATS (
 as_of_timestamp TIMESTAMP WITH TIME ZONE
 );

Parameters

Parameter	Description
as_of_timestamp	The point in time to which you want to restore statistics.

Examples

Restore database statistics. Example:

SELECT DBMS_STATS.RESTORE_DATABASE_STATS(time) FROM polar_dbms_stats.backup_history WHERE unit='d';

Query the restored statistics. Example:

SELECT count(*) FROM polar_dbms_stats.relation_stats_locked WHERE relname LIKE 'dbms_stats_schema%';

DBMS_STATS.RESTORE_TABLE_STATS

This function is used to restore the statistics for a specified table.

Syntax

DBMS_STATS.RESTORE_TABLE_STATS (
 ownname VARCHAR2, 
 tabname VARCHAR2, 
 as_of_timestamp TIMESTAMP WITH TIME ZONE
 );

Parameters

Parameter	Description
ownname	The schema of the table you want to restore.
tabname	The name of the table you want to restore.
as_of_timestamp	The point in time to which you want to restore statistics.

Examples

Restore the statistics for a specified table. Example:

SELECT DBMS_STATS.RESTORE_TABLE_STATS('dbms_stats_schema', 'dbms_stats_test', time) FROM polar_dbms_stats.backup_history WHERE unit='t';

Query the restored statistics. Example:

SELECT count(*) FROM polar_dbms_stats.relation_stats_locked WHERE relname LIKE 'dbms_stats_schema%';

DBMS_STATS.RESTORE_COLUMN_STATS

This function is used to restore the statistics for a specified column.

Syntax

DBMS_STATS.RESTORE_COLUMN_STATS (
 ownname TEXT,
 tablename TEXT,
 attname TEXT,
 as_of_timestamp timestamp with time zone
 );

Parameters

Parameter	Description
ownname	The schema of the column you want to restore.
tabname	The name of the table that contains the column you want to restore.
attname	The name of the column you want to restore.
as_of_timestamp	The point in time to which you want to restore statistics.

Examples

Restore the statistics for a specified column. Example:

SELECT DBMS_STATS.RESTORE_COLUMN_STATS('dbms_stats_schema', 'dbms_stats_test', 'id', time) FROM polar_dbms_stats.backup_history WHERE unit='c';

Query the restored statistics. Example:

SELECT count(*) FROM polar_dbms_stats.relation_stats_locked WHERE relname LIKE 'dbms_stats_schema%';

DBMS_STATS.PURGE_STATS

This function is used to delete the backup statistics that are generated before a specified point in time.

Syntax

DBMS_STATS.PURGE_STATS (
 before_timestamp timestamp
 );

Parameters

Parameter	Description
before_timestamp	The specified point in time. The system deletes the statistics that are generated before the specified point in time.

Examples

Delete the backup statistics that are generated before a specified point in time. Example:

SELECT DBMS_STATS.PURGE_STATS(time) FROM polar_dbms_stats.backup_history WHERE unit='c';

DBMS_STATS.SET_TABLE_STATS

This function is used to configure statistics for a specified table.

Note

Before you call the SET_TABLE_STATS function to configure statistics for a table, make sure that the table statistics are collected. If the table statistics are not collected, call the GATHER_SCHEMA_STATS or GATHER_TABLE_STATS function to collect the statistics.

Syntax

DBMS_STATS.SET_TABLE_STATS (
 ownname VARCHAR2, 
 tabname VARCHAR2, 
 numrows NUMBER DEFAULT NULL, 
 numblks NUMBER DEFAULT NULL
 );

Parameters

Parameter	Description
ownname	The schema of the table you want to configure.
tabname	The name of the table you want to configure.
numrows	Optional. The number of rows for the table.
numblks	Optional. The number of blocks for the table.

Examples

Configure table statistics. Example:

CALL DBMS_STATS.SET_TABLE_STATS('dbms_stats_schema', 'dbms_stats_test', 1234, 4321);

DBMS_STATS.GET_TABLE_STATS

This function is used to query the statistics for a specified table.

Syntax

DBMS_STATS.GET_TABLE_STATS (
 ownname VARCHAR2, 
 tabname VARCHAR2, 
 numrows OUT NUMBER, 
 numblks OUT NUMBER
 );

Parameters

Parameter	Description
ownname	The schema of the table you want to query.
tabname	The name of the table you want to query.
numrows	The number of rows you want to query from the table.
numblks	The number of blocks you want to query from the table.

Examples

Query table statistics. Example:

DECLARE
 numrows integer;
 numblks integer;
BEGIN
 CALL DBMS_STATS.GET_TABLE_STATS('dbms_stats_schema', 'dbms_stats_test', numrows, numblks);
 raise notice '%', numrows;
 raise notice '%', numblks;
END;

DBMS_STATS.SET_INDEX_STATS

This function is used to configure index statistics.

Syntax

DBMS_STATS.SET_INDEX_STATS (
 ownname VARCHAR2,
 indname VARCHAR2,
 numrows NUMBER DEFAULT NULL, 
 numblks NUMBER DEFAULT NULL
 );

Parameters

Parameter	Description
ownname	The schema of the index you want to configure.
indname	The name of the index you want to configure.
numrows	Optional. The number of rows you want to configure for the index.
numblks	Optional. The number of blocks you want to configure for the index.

Examples

Configure index statistics. Example:

CALL DBMS_STATS.SET_INDEX_STATS('dbms_stats_schema', 'dbms_stats_index', 2345, 5432);

DBMS_STATS.GET_INDEX_STATS

This function is used to query the statistics for a specified index.

Syntax

DBMS_STATS.GET_INDEX_STATS (
 ownname VARCHAR2, 
 tabname VARCHAR2, 
 numrows OUT NUMBER, 
 numblks OUT NUMBER
 );

Parameters

Parameter	Description
ownname	The schema of the index you want to query.
tabname	The name of the index you want to query.
numrows	The number of rows you want to query from the index.
numblks	The number of blocks you want to query from the index.

Examples

Query index statistics. Example:

DECLARE
 numrows integer;
 numblks integer;
BEGIN
 CALL DBMS_STATS.GET_INDEX_STATS('dbms_stats_schema', 'dbms_stats_index', numrows, numblks);
 raise notice '%', numrows;
 raise notice '%', numblks;
END;

DBMS_STATS.GET_COLUMN_STATS

This function is used to query the statistics for a specified column.

Syntax

DBMS_STATS.GET_COLUMN_STATS (
 ownname VARCHAR2, 
 tabname VARCHAR2, 
 colname VARCHAR2, 
 distcnt OUT NUMBER, 
 nullcnt OUT NUMBER, 
 avgclen OUT NUMBER
 );

Parameters

Parameter	Description
ownname	The schema of the column you want to query.
tabname	The name of the table that contains the column you want to query.
colname	The name of the column you want to query.
distcnt	The number of distinct values in the column you want to query.
nullcnt	The number of empty values in the column you want to query.
avgclen	The average length of the column you want to query.

Examples

Query column statistics. Example:

DECLARE
 distcnt integer;
 nullcnt integer;
 avgclen integer;
BEGIN
 CALL DBMS_STATS.GET_COLUMN_STATS('dbms_stats_schema', 'dbms_stats_test', 'id', distcnt, nullcnt, avgclen);
 raise notice '%', distcnt;
 raise notice '%', nullcnt;
 raise notice '%', avgclen;
END;

DBMS_STATS.LOCK_TABLE_STATS

This function is used to lock the statistics for the table that is being used.

Syntax

DBMS_STATS.LOCK_TABLE_STATS (
 ownname VARCHAR2, 
 tabname VARCHAR2 
 );

Parameters

Parameter	Description
ownname	The schema of the table you want to lock.
tabname	The name of the table you want to lock.

Examples

Lock the statistics for the table that is being used. Example:

CALL DBMS_STATS.LOCK_TABLE_STATS('dbms_stats_schema', 'dbms_stats_test');

Query the locked statistics. Example:

SELECT count(*) FROM polar_dbms_stats.relation_stats_locked WHERE relname = 'dbms_stats_schema.dbms_stats_test';

DBMS_STATS.UNLOCK_TABLE_STATS

This function is used to unlock the statistics for the table that is being used.

Syntax

DBMS_STATS.UNLOCK_TABLE_STATS (
 ownname VARCHAR2, 
 tabname VARCHAR2 
 );

Parameters

Parameter	Description
ownname	The schema of the table you want to unlock.
tabname	The name of the table you want to unlock.

Examples

Unlock the statistics for the table that is being used. Example:

CALL DBMS_STATS.UNLOCK_TABLE_STATS('dbms_stats_schema', 'dbms_stats_test');

Query the locked statistics. Example:

SELECT count(*) FROM polar_dbms_stats.relation_stats_locked WHERE relname = 'dbms_stats_schema.dbms_stats_test';

DBMS_STATS.LOCK_SCHEMA_STATS

This function is used to lock the statistics for the schema that is being used.

Syntax

DBMS_STATS.LOCK_SCHEMA_STATS (
 ownname VARCHAR2
 );

Parameters

Parameter	Description
ownname	The name of the schema you want to lock.

Examples

Lock the statistics for the schema that is being used. Example:

CALL DBMS_STATS.LOCK_SCHEMA_STATS('dbms_stats_schema');

Query the locked statistics. Example:

SELECT count(*) FROM polar_dbms_stats.relation_stats_locked WHERE relname LIKE 'dbms_stats_schema.%';

DBMS_STATS.UNLOCK_SCHEMA_STATS

This function is used to unlock the statistics for the schema that is being used.

Syntax

DBMS_STATS.UNLOCK_SCHEMA_STATS (
 ownname VARCHAR2
 );

Parameters

Parameter	Description
ownname	The name of the schema you want to unlock.

Examples

Unlock the statistics for the schema that is being used. Example:

CALL DBMS_STATS.UNLOCK_SCHEMA_STATS('dbms_stats_schema');

Query the locked statistics. Example:

SELECT count(*) FROM polar_dbms_stats.relation_stats_locked WHERE relname LIKE 'dbms_stats_schema.%';

DBMS_STATS.LOCK_COLUMN_STATS

This function is used to lock the statistics for the column that is being used.

Syntax

DBMS_STATS.LOCK_COLUMN_STATS (
 ownname VARCHAR2, 
 tabname VARCHAR2, 
 attname VARCHAR2
 );

Parameters

Parameter	Description
ownname	The schema of the column you want to lock.
tabname	The name of the table that contains the column you want to lock.
attname	The name of the column you want to lock.

Examples

Lock the statistics for the column that is being used. Example:

CALL DBMS_STATS.LOCK_COLUMN_STATS('dbms_stats_schema', 'dbms_stats_test', 'id');

DBMS_STATS.UNLOCK_COLUMN_STATS

This function is used to unlock the statistics for the column that is being used.

Syntax

DBMS_STATS.UNLOCK_COLUMN_STATS (
 ownname VARCHAR2, 
 tabname VARCHAR2, 
 attname VARCHAR2
 );

Parameters

Parameter	Description
ownname	The schema of the column you want to unlock.
tabname	The name of the table that contains the column you want to unlock.
attname	The name of the column you want to unlock.

Examples

Unlock the statistics for the column that is being used. Example:

CALL DBMS_STATS.UNLOCK_COLUMN_STATS('dbms_stats_schema', 'dbms_stats_test', 'id');

DBMS_STATS.DELETE_TABLE_STATS

This function is used to delete the existing table statistics.

Syntax

DBMS_STATS.DELETE_TABLE_STATS (
 ownname VARCHAR2, 
 tabname VARCHAR2
 );

Parameters

Parameter	Description
ownname	The schema of the table you want to delete.
tabname	The name of the table you want to delete.

Examples

Delete the existing table statistics. Example:

CALL DBMS_STATS.DELETE_TABLE_STATS('dbms_stats_schema', 'dbms_stats_test');

Query table statistics after the existing table statistics are deleted. Example:

SELECT count(*) FROM polar_dbms_stats.relation_stats_backup WHERE relname = 'dbms_stats_schema.dbms_stats_test';

DBMS_STATS.DELETE_COLUMN_STATS

This function is used to delete the existing column statistics.

Syntax

DBMS_STATS.DELETE_COLUMN_STATS (
 ownname VARCHAR2, 
 tabname VARCHAR2,
 attname VARCHAR2
 );

Parameters

Parameter	Description
ownname	The schema of the column you want to delete.
tabname	The name of the table that contains the column you want to delete.
attname	The name of the column you want to delete.

Examples

Delete the existing column statistics. Example:

CALL DBMS_STATS.DELETE_COLUMN_STATS('dbms_stats_schema', 'dbms_stats_test', 'id');

DBMS_STATS.DELETE_SCHEMA_STATS

This function is used to delete the existing schema statistics.

Syntax

DBMS_STATS.DELETE_SCHEMA_STATS (
 ownname VARCHAR2 
 );

Parameters

Parameter	Description
ownname	The name of the schema you want to delete.

Examples

Delete the existing schema statistics. Example:

CALL DBMS_STATS.DELETE_SCHEMA_STATS('dbms_stats_schema');

Query index statistics after the existing index statistics are deleted. Example:

SELECT count(*) FROM polar_dbms_stats.relation_stats_backup WHERE relname LIKE 'dbms_stats_schema.%';

DBMS_STATS.DELETE_INDEX_STATS

This function is used to delete the existing index statistics.

Syntax

DBMS_STATS.DELETE_INDEX_STATS (
 ownname VARCHAR2,
 indname VARCHAR2 
 );

Parameters

Parameter	Description
ownname	The schema of the index you want to delete.
indname	The name of the index you want to delete.

Examples

Delete the existing index statistics. Example:

CALL DBMS_STATS.DELETE_INDEX_STATS('dbms_stats_schema', 'dbms_stats_index');

Query index statistics after the existing index statistics are deleted. Example:

SELECT count(*) FROM polar_dbms_stats.relation_stats_backup WHERE relname LIKE 'dbms_stats_schema.%';