PolarDB O Edition provides the DBMS_STATS package to collect the statistics of running databases, back up current statistics, and restore statistics. You can configure a custom time point for backup statistics and modify table statistics as needed. This helps the optimizer generate sound 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 status of the polar_dbms_stats.use_locked_stats parameter is on.
    Note
    • You can run the following command to query the status of the parameter:
      SHOW polar_dbms_stats.use_locked_stats;
    • If the status of the parameter is off,Submit a ticket to contact Technical Support.

Prepare test data

Note The test data applies to only the examples used 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 status of table 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 database 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 status of index statistics. Example:

SELECT * FROM polar_dbms_stats.backup_history;

DBMS_STATS.GATHER_COLUMN_STATS

This function is used to collect and back up 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 statistics of 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 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 time point to which you want to restore statistics. Yes

Examples

Restore 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 time point 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 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 time point to which you want to restore statistics. Yes

Examples

Restore 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 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 time point to which you want to restore statistics. Yes

Examples

Restore 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 backup statistics generated before a specified time point.

Syntax

DBMS_STATS.PURGE_STATS (
        before_timestamp timestamp
    );

Parameters

Parameter Description Required
before_timestamp The specified time point before which the backup statistics are deleted. Yes

Examples

Delete backup statistics generated before a specified time point. 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.

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 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;
CALL;

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 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;
CALL;

DBMS_STATS.GET_COLUMN_STATS

This function is used to query 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;
CALL;

DBMS_STATS.LOCK_TABLE_STATS

This function is used to lock statistics of a table in use.

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 statistics of a table in use. 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 statistics of a table in use.

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 statistics of a table in use. 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 statistics of a schema in use.

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 statistics of a schema in use. 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 statistics of a schema in use.

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 statistics of a schema in use. 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 statistics of a column in use.

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 statistics of a column in use. 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 statistics of a column in use.

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 statistics of a column in use. 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. %';