PolarDB for Oracle provides the DBMS_STATS package to help you collect the statistics about 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 plug-in is installed. You can run the following command to install the plug-in:
    CREATE EXTENSION polar_dbms_stats;
  • The value of the polar_dbms_stats.use_locked_stats parameter is on.
    Note
    • You can run the following command to query the parameter value:
      SHOW polar_dbms_stats.use_locked_stats;
    • If the parameter value is off, submit a ticket to contact technical support to change the parameter value.

Prepare test data

Note The test data applies only to the examples that are provided in this topic.
  1. Create a schema named dbms_stats_schema.
    CREATE SCHEMA dbms_stats_schema;
  2. Create a table named dbms_stats_test.
    CREATE TABLE dbms_stats_schema.dbms_stats_test(id int);
  3. Create an index.
    CREATE INDEX dbms_stats_index on dbms_stats_schema.dbms_stats_test(id);
  4. 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 The name of the schema that 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 The schema of the table that you want to analyze. Yes
tabname The name of the table that 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 The schema of the index that you want to analyze. Yes
indname The name of the index that 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 of a specified column.

Syntax

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

Parameters

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

Examples

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

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

Query the backup statistics. Example:

SELECT * FROM polar_dbms_stats.backup_history;

DBMS_STATS.RESTORE_SCHEMA_STATS

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

Syntax

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

Parameters

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

Examples

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

Examples

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

Examples

Restore the statistics of 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 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 The schema of the table that you want to configure. Yes
tabname The name of the table that you want to configure. Yes
numrows The number of table rows that you want to configure. Yes
numblks The number of table blocks that you want to configure. 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 of a specified table.

Syntax

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

Parameters

Parameter Description Required
ownname The schema of the table that you want to query. Yes
tabname The name of the table that you want to query. Yes
numrows The number of table rows that you want to query. Yes
numblks The number of table blocks that you want to query. 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 The schema of the index that you want to configure. Yes
indname The name of the index that you want to configure. Yes
numrows The number of rows in the index table that you want to configure. Yes
numblks The number of blocks in the index table that you want to configure. 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 of a specified index.

Syntax

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

Parameters

Parameter Description Required
ownname The schema of the index that you want to query. Yes
tabname The name of the index that you want to query. Yes
numrows The number of rows in the index table that you want to query. Yes
numblks The number of blocks in the index table that you want to query. 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 of 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 The schema of the column that you want to query. Yes
tabname The table of the column that you want to query. Yes
colname The name of the column that you want to query. Yes
distcnt The number of distinct values in the column that you want to query. Yes
nullcnt The number of null values in the column that you want to query. Yes
avgclen The average length of the column that 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 of the table that is being used.

Syntax

DBMS_STATS.LOCK_TABLE_STATS (
        ownname       VARCHAR2, 
        tabname       VARCHAR2 
    );

Parameters

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

Examples

Lock the statistics of 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 of the table that is being used.

Syntax

DBMS_STATS.UNLOCK_TABLE_STATS (
        ownname       VARCHAR2, 
        tabname       VARCHAR2 
    );

Parameters

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

Examples

Unlock the statistics of 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 of the schema that is being used.

Syntax

DBMS_STATS.LOCK_SCHEMA_STATS (
        ownname       VARCHAR2
    );

Parameters

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

Examples

Lock the statistics of 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 of the schema that is being used.

Syntax

DBMS_STATS.UNLOCK_SCHEMA_STATS (
        ownname       VARCHAR2
    );

Parameters

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

Examples

Unlock the statistics of 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 of the column that is being used.

Syntax

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

Parameters

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

Examples

Lock the statistics of 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 of the column that is being used.

Syntax

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

Parameters

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

Examples

Unlock the statistics of 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 The schema of the table that you want to delete. Yes
tabname The name of the table that 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 The schema of the column that you want to delete. Yes
tabname The table of the column that you want to delete. Yes
attname The name of the column that 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 The name of the schema that 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 The schema of the index that you want to delete. Yes
indname The name of the index that 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.%';