Optimize Query Plans with DBMS_STATS in PolarDB - PolarDB

The DBMS_STATS package lets you collect, back up, restore, lock, and delete optimizer statistics in PolarDB for PostgreSQL (Compatible with Oracle). Accurate statistics help the query optimizer choose efficient execution plans.

Note

To use DBMS_STATS, contact us to enable access first.

Set up test data

Note

The following test data is used only by the examples in this topic.

Run the following statements to create a schema, table, and index, then insert 10,000 rows:

CREATE SCHEMA dbms_stats_schema;
CREATE TABLE dbms_stats_schema.dbms_stats_test(id int);
CREATE INDEX dbms_stats_index ON dbms_stats_schema.dbms_stats_test(id);
INSERT INTO dbms_stats_schema.dbms_stats_test VALUES (generate_series(1,10000));

Function summary

DBMS_STATS provides functions in four categories:

Category	Functions
Gather	`GATHER_DATABASE_STATS`, `GATHER_SCHEMA_STATS`, `GATHER_TABLE_STATS`, `GATHER_INDEX_STATS`, `GATHER_COLUMN_STATS`
Restore	`RESTORE_DATABASE_STATS`, `RESTORE_SCHEMA_STATS`, `RESTORE_TABLE_STATS`, `RESTORE_COLUMN_STATS`
Lock/Unlock	`LOCK_TABLE_STATS`, `UNLOCK_TABLE_STATS`, `LOCK_SCHEMA_STATS`, `UNLOCK_SCHEMA_STATS`, `LOCK_COLUMN_STATS`, `UNLOCK_COLUMN_STATS`
Set/Get/Delete	`SET_TABLE_STATS`, `GET_TABLE_STATS`, `SET_INDEX_STATS`, `GET_INDEX_STATS`, `GET_COLUMN_STATS`, `DELETE_TABLE_STATS`, `DELETE_COLUMN_STATS`, `DELETE_SCHEMA_STATS`, `DELETE_INDEX_STATS`, `PURGE_STATS`

All gather operations write results to polar_dbms_stats.backup_history. All restore and lock operations write to polar_dbms_stats.relation_stats_locked.

DBMS_STATS.GATHER_SCHEMA_STATS

Collects and backs up statistics for all tables and indexes in a schema.

Syntax

DBMS_STATS.GATHER_SCHEMA_STATS (
    ownname    VARCHAR2
);

Parameters

Parameter	Type	Required	Description
`ownname`	VARCHAR2	Yes	The name of the schema to analyze.

Example

Collect statistics for dbms_stats_schema, then verify the backup:

CALL DBMS_STATS.GATHER_SCHEMA_STATS('dbms_stats_schema');

SELECT * FROM polar_dbms_stats.backup_history;

DBMS_STATS.GATHER_TABLE_STATS

Collects and backs up statistics for a single table.

Syntax

DBMS_STATS.GATHER_TABLE_STATS (
    ownname    VARCHAR2,
    tabname    VARCHAR2
);

Parameters

Parameter	Type	Required	Description
`ownname`	VARCHAR2	Yes	The schema that owns the table.
`tabname`	VARCHAR2	Yes	The name of the table to analyze.

Example

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

SELECT * FROM polar_dbms_stats.backup_history;

DBMS_STATS.GATHER_DATABASE_STATS

Collects and backs up statistics for all objects in the database.

Syntax

DBMS_STATS.GATHER_DATABASE_STATS ();

Example

CALL DBMS_STATS.GATHER_DATABASE_STATS();

SELECT * FROM polar_dbms_stats.backup_history;

DBMS_STATS.GATHER_INDEX_STATS

Collects and backs up statistics for an index.

Syntax

DBMS_STATS.GATHER_INDEX_STATS (
    ownname    VARCHAR2,
    indname    VARCHAR2
);

Parameters

Parameter	Type	Required	Description
`ownname`	VARCHAR2	Yes	The schema that owns the index.
`indname`	VARCHAR2	Yes	The name of the index to analyze.

Example

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

SELECT * FROM polar_dbms_stats.backup_history;

DBMS_STATS.GATHER_COLUMN_STATS

Collects and backs up statistics for a single column.

Syntax

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

Parameters

Parameter	Type	Required	Description
`ownname`	VARCHAR2	Yes	The schema that owns the table.
`tablename`	VARCHAR2	Yes	The name of the table that contains the column.
`attname`	TEXT	Yes	The name of the column to analyze.

Example

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

SELECT * FROM polar_dbms_stats.backup_history;

DBMS_STATS.RESTORE_SCHEMA_STATS

Restores statistics for all objects in a schema to a previous point in time.

Syntax

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

Parameters

Parameter	Type	Required	Description
`ownname`	VARCHAR2	Yes	The name of the schema to restore.
`as_of_timestamp`	TIMESTAMP WITH TIME ZONE	Yes	The point in time to restore statistics to.

Example

Restore schema statistics using a timestamp from the backup history, then verify the result:

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

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

DBMS_STATS.RESTORE_DATABASE_STATS

Restores statistics for all database objects to a previous point in time.

Syntax

DBMS_STATS.RESTORE_DATABASE_STATS (
    as_of_timestamp    TIMESTAMP WITH TIME ZONE
);

Parameters

Parameter	Type	Required	Description
`as_of_timestamp`	TIMESTAMP WITH TIME ZONE	Yes	The point in time to restore statistics to.

Example

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

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

DBMS_STATS.RESTORE_TABLE_STATS

Restores statistics for a table to a previous point in time.

Syntax

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

Parameters

Parameter	Type	Required	Description
`ownname`	VARCHAR2	Yes	The schema that owns the table.
`tabname`	VARCHAR2	Yes	The name of the table to restore.
`as_of_timestamp`	TIMESTAMP WITH TIME ZONE	Yes	The point in time to restore statistics to.

Example

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

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

DBMS_STATS.RESTORE_COLUMN_STATS

Restores statistics for a column to a previous point in time.

Syntax

DBMS_STATS.RESTORE_COLUMN_STATS (
    ownname             TEXT,
    tablename           TEXT,
    attname             TEXT,
    as_of_timestamp     TIMESTAMP WITH TIME ZONE
);

Parameters

Parameter	Type	Required	Description
`ownname`	TEXT	Yes	The schema that owns the table.
`tablename`	TEXT	Yes	The name of the table that contains the column.
`attname`	TEXT	Yes	The name of the column to restore.
`as_of_timestamp`	TIMESTAMP WITH TIME ZONE	Yes	The point in time to restore statistics to.

Example

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

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

DBMS_STATS.PURGE_STATS

Deletes all backup statistics generated before a specified point in time.

Syntax

DBMS_STATS.PURGE_STATS (
    before_timestamp    TIMESTAMP
);

Parameters

Parameter	Type	Required	Description
`before_timestamp`	TIMESTAMP	Yes	Statistics generated before this timestamp are deleted.

Example

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

DBMS_STATS.SET_TABLE_STATS

Manually sets the row count and block count for a table.

Note

Collect table statistics before calling this function. If statistics have not been collected, call GATHER_TABLE_STATS or GATHER_SCHEMA_STATS first.

Syntax

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

Parameters

Parameter	Type	Required	Description
`ownname`	VARCHAR2	Yes	The schema that owns the table.
`tabname`	VARCHAR2	Yes	The name of the table.
`numrows`	NUMBER	Yes	The row count to set for the table.
`numblks`	NUMBER	Yes	The block count to set for the table.

Example

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

DBMS_STATS.GET_TABLE_STATS

Retrieves the current row count and block count for a table.

Syntax

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

Parameters

Parameter	Type	Required	Description
`ownname`	VARCHAR2	Yes	The schema that owns the table.
`tabname`	VARCHAR2	Yes	The name of the table.
`numrows`	OUT NUMBER	Yes	Returns the row count of the table.
`numblks`	OUT NUMBER	Yes	Returns the block count of the table.

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

Manually sets the row count and block count for an index.

Syntax

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

Parameters

Parameter	Type	Required	Description
`ownname`	VARCHAR2	Yes	The schema that owns the index.
`indname`	VARCHAR2	Yes	The name of the index.
`numrows`	NUMBER	Yes	The row count to set for the index.
`numblks`	NUMBER	Yes	The block count to set for the index.

Example

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

DBMS_STATS.GET_INDEX_STATS

Retrieves the current row count and block count for an index.

Syntax

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

Parameters

Parameter	Type	Required	Description
`ownname`	VARCHAR2	Yes	The schema that owns the index.
`tabname`	VARCHAR2	Yes	The name of the index to query.
`numrows`	OUT NUMBER	Yes	Returns the row count of the index.
`numblks`	OUT NUMBER	Yes	Returns the block count of the index.

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

Retrieves the distinct value count, null value count, and average length for a column.

Syntax

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

Parameters

Parameter	Type	Required	Description
`ownname`	VARCHAR2	Yes	The schema that owns the table.
`tabname`	VARCHAR2	Yes	The name of the table.
`colname`	VARCHAR2	Yes	The name of the column.
`distcnt`	OUT NUMBER	Yes	Returns the number of distinct values in the column.
`nullcnt`	OUT NUMBER	Yes	Returns the number of null values in the column.
`avgclen`	OUT NUMBER	Yes	Returns the average length of the column.

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

Locks the statistics for a table that is being used.

Syntax

DBMS_STATS.LOCK_TABLE_STATS (
    ownname    VARCHAR2,
    tabname    VARCHAR2
);

Parameters

Parameter	Type	Required	Description
`ownname`	VARCHAR2	Yes	The schema that owns the table.
`tabname`	VARCHAR2	Yes	The name of the table to lock.

Example

Lock the table statistics, then verify the lock:

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

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

DBMS_STATS.UNLOCK_TABLE_STATS

Unlocks the statistics for a table that is being used.

Syntax

DBMS_STATS.UNLOCK_TABLE_STATS (
    ownname    VARCHAR2,
    tabname    VARCHAR2
);

Parameters

Parameter	Type	Required	Description
`ownname`	VARCHAR2	Yes	The schema that owns the table.
`tabname`	VARCHAR2	Yes	The name of the table to unlock.

Example

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

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

DBMS_STATS.LOCK_SCHEMA_STATS

Locks the statistics for all objects in a schema.

Syntax

DBMS_STATS.LOCK_SCHEMA_STATS (
    ownname    VARCHAR2
);

Parameters

Parameter	Type	Required	Description
`ownname`	VARCHAR2	Yes	The name of the schema to lock.

Example

CALL DBMS_STATS.LOCK_SCHEMA_STATS('dbms_stats_schema');

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

DBMS_STATS.UNLOCK_SCHEMA_STATS

Unlocks the statistics for all objects in a schema.

Syntax

DBMS_STATS.UNLOCK_SCHEMA_STATS (
    ownname    VARCHAR2
);

Parameters

Parameter	Type	Required	Description
`ownname`	VARCHAR2	Yes	The name of the schema to unlock.

Example

CALL DBMS_STATS.UNLOCK_SCHEMA_STATS('dbms_stats_schema');

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

DBMS_STATS.LOCK_COLUMN_STATS

Locks the statistics for a specific column.

Syntax

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

Parameters

Parameter	Type	Required	Description
`ownname`	VARCHAR2	Yes	The schema that owns the table.
`tabname`	VARCHAR2	Yes	The name of the table that contains the column.
`attname`	VARCHAR2	Yes	The name of the column to lock.

Example

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

DBMS_STATS.UNLOCK_COLUMN_STATS

Unlocks the statistics for a specific column.

Syntax

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

Parameters

Parameter	Type	Required	Description
`ownname`	VARCHAR2	Yes	The schema that owns the table.
`tabname`	VARCHAR2	Yes	The name of the table that contains the column.
`attname`	VARCHAR2	Yes	The name of the column to unlock.

Example

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

DBMS_STATS.DELETE_TABLE_STATS

Deletes the current statistics for a table.

Syntax

DBMS_STATS.DELETE_TABLE_STATS (
    ownname    VARCHAR2,
    tabname    VARCHAR2
);

Parameters

Parameter	Type	Required	Description
`ownname`	VARCHAR2	Yes	The schema that owns the table.
`tabname`	VARCHAR2	Yes	The name of the table.

Example

Delete table statistics, then verify the deletion:

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

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

DBMS_STATS.DELETE_COLUMN_STATS

Deletes the current statistics for a column.

Syntax

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

Parameters

Parameter	Type	Required	Description
`ownname`	VARCHAR2	Yes	The schema that owns the table.
`tabname`	VARCHAR2	Yes	The name of the table that contains the column.
`attname`	VARCHAR2	Yes	The name of the column.

Example

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

DBMS_STATS.DELETE_SCHEMA_STATS

Deletes statistics for all objects in a schema.

Syntax

DBMS_STATS.DELETE_SCHEMA_STATS (
    ownname    VARCHAR2
);

Parameters

Parameter	Type	Required	Description
`ownname`	VARCHAR2	Yes	The name of the schema.

Example

CALL DBMS_STATS.DELETE_SCHEMA_STATS('dbms_stats_schema');

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

DBMS_STATS.DELETE_INDEX_STATS

Deletes the current statistics for an index.

Syntax

DBMS_STATS.DELETE_INDEX_STATS (
    ownname    VARCHAR2,
    indname    VARCHAR2
);

Parameters

Parameter	Type	Required	Description
`ownname`	VARCHAR2	Yes	The schema that owns the index.
`indname`	VARCHAR2	Yes	The name of the index.

Example

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

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