DBMS_STATS - PolarDB - Alibaba Cloud Documentation Center

PolarDB for PostgreSQL (Compatible with Oracle) provides the DBMS_STATS package to help 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

Prepare test data

Note

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 an index.

CREATE INDEX dbms_stats_index on dbms_stats_schema.dbms_stats_test(id);

Insert data.

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	Required
ownname	Specifies the name of the schema you want to analyze.	Yes

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	Required
ownname	Specifies the schema of the table you want to analyze.	Yes
tabname	Specifies the name of the table you want to analyze.	Yes

Examples

Collect and back up table statistics. Example:

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

Query the backup 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 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	Required
ownname	Specifies the schema of the index you want to analyze.	Yes
indname	Specifies the name of the index you want to analyze.	Yes

Examples

Collect and back up index statistics. Example:

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

Query the backup 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	Required
ownname	Specifies the schema of the column you want to analyze.	Yes
tablename	Specifies the name of the table that contains the column you want to analyze.	Yes
attname	Specifies the name of the column you want to analyze.	Yes

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	Required
ownname	Specifies the name of the schema you want to restore.	Yes
as_of_timestamp	Specifies the point in time to which you want to restore statistics.	Yes

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	Required
as_of_timestamp	Specifies the point in time to which you want to restore statistics.	Yes

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	Required
ownname	Specifies the schema of the table you want to restore.	Yes
tabname	Specifies the name of the table you want to restore.	Yes
as_of_timestamp	Specifies the point in time to which you want to restore statistics.	Yes

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	Required
ownname	Specifies the schema of the column you want to restore.	Yes
tabname	Specifies the name of the table that contains the column you want to restore.	Yes
attname	Specifies the name of the column you want to restore.	Yes
as_of_timestamp	Specifies the point in time to which you want to restore statistics.	Yes

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	Required
before_timestamp	Specifies the specified point in time. The system deletes the statistics that are generated before the specified point in time.	Yes

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	Required
ownname	Specifies the schema of the table you want to configure.	Yes
tabname	Specifies the name of the table you want to configure.	Yes
numrows	Specifies the number of rows for the table.	Yes
numblks	Specifies the number of blocks for the table.	Yes

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	Required
ownname	Specifies the schema of the table you want to query.	Yes
tabname	Specifies the name of the table you want to query.	Yes
numrows	Specifies the number of rows you want to query from the table.	Yes
numblks	Specifies the number of blocks you want to query from the table.	Yes

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	Required
ownname	Specifies the schema of the index you want to configure.	Yes
indname	Specifies the name of the index you want to configure.	Yes
numrows	Specifies the number of rows you want to configure for the index.	Yes
numblks	Specifies the number of blocks you want to configure for the index.	Yes

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	Required
ownname	Specifies the schema of the index you want to query.	Yes
tabname	Specifies the name of the index you want to query.	Yes
numrows	Specifies the number of rows you want to query from the index.	Yes
numblks	Specifies the number of blocks you want to query from the index.	Yes

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	Required
ownname	Specifies the schema of the column you want to query.	Yes
tabname	Specifies the name of the table that contains the column you want to query.	Yes
colname	Specifies the name of the column you want to query.	Yes
distcnt	Specifies the number of distinct values in the column you want to query.	Yes
nullcnt	Specifies the number of empty values in the column you want to query.	Yes
avgclen	Specifies the average length of the column you want to query.	Yes

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	Required
ownname	Specifies the schema of the table you want to lock.	Yes
tabname	Specifies the name of the table you want to lock.	Yes

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	Required
ownname	Specifies the schema of the table you want to unlock.	Yes
tabname	Specifies the name of the table you want to unlock.	Yes

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	Required
ownname	Specifies the name of the schema you want to lock.	Yes

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	Required
ownname	Specifies the name of the schema you want to unlock.	Yes

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	Required
ownname	Specifies the schema of the column you want to lock.	Yes
tabname	Specifies the name of the table that contains the column you want to lock.	Yes
attname	Specifies the name of the column you want to lock.	Yes

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	Required
ownname	Specifies the schema of the column you want to unlock.	Yes
tabname	Specifies the name of the table that contains the column you want to unlock.	Yes
attname	Specifies the name of the column you want to unlock.	Yes

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	Required
ownname	Specifies the schema of the table you want to delete.	Yes
tabname	Specifies the name of the table you want to delete.	Yes

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	Required
ownname	Specifies the schema of the column you want to delete.	Yes
tabname	Specifies the name of the table that contains the column you want to delete.	Yes
attname	Specifies the name of the column you want to delete.	Yes

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	Required
ownname	Specifies the name of the schema you want to delete.	Yes

Examples

Delete the existing schema statistics. Example:

CALL DBMS_STATS.DELETE_SCHEMA_STATS('dbms_stats_schema');

Query schema statistics after the existing schema 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	Required
ownname	Specifies the schema of the index you want to delete.	Yes
indname	Specifies the name of the index you want to delete.	Yes

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.%';