Use the DBMS_STATS package to manage statistics - PolarDB

DBMS_STATS lets you gather optimizer statistics, back them up at a point in time, restore them from a backup, lock them to prevent automatic updates, and manually override them when needed. Accurate statistics help the query optimizer generate efficient execution plans.

Prerequisites

Before you begin, make sure that:

The polar_dbms_stats.use_locked_stats parameter is set to ON.

To check the current value, run:

SHOW polar_dbms_stats.use_locked_stats;

To set the parameter, run:

SET polar_dbms_stats.use_locked_stats = ON;   -- Enable
SET polar_dbms_stats.use_locked_stats = OFF;  -- Disable

Procedures

DBMS_STATS provides procedures organized into five groups based on what they do. Each group operates at a different scope — column, table, index, schema, or database.

Procedure	Description	Scope
`GATHER_COLUMN_STATS`	Gathers and backs up statistics for a column	Column
`GATHER_TABLE_STATS`	Gathers and backs up table statistics	Table
`GATHER_INDEX_STATS`	Gathers and backs up index statistics	Index
`GATHER_SCHEMA_STATS`	Gathers and backs up statistics for all objects in a schema	Schema
`GATHER_DATABASE_STATS`	Gathers and backs up statistics for all objects in the database	Database
`RESTORE_COLUMN_STATS`	Restores column statistics to a point in time	Column
`RESTORE_TABLE_STATS`	Restores table statistics to a point in time	Table
`RESTORE_SCHEMA_STATS`	Restores statistics for all objects in a schema to a point in time	Schema
`RESTORE_DATABASE_STATS`	Restores statistics for all objects in the database to a point in time	Database
`PURGE_STATS`	Deletes backup statistics older than a specified timestamp	—
`GET_COLUMN_STATS`	Returns statistics for a column	Column
`GET_TABLE_STATS`	Returns statistics for a table	Table
`GET_INDEX_STATS`	Returns statistics for an index	Index
`SET_TABLE_STATS`	Sets statistics for a table manually	Table
`SET_INDEX_STATS`	Sets statistics for an index manually	Index
`LOCK_COLUMN_STATS`	Locks column statistics to prevent automatic updates	Column
`UNLOCK_COLUMN_STATS`	Unlocks column statistics	Column
`LOCK_TABLE_STATS`	Locks table statistics to prevent automatic updates	Table
`UNLOCK_TABLE_STATS`	Unlocks table statistics	Table
`LOCK_SCHEMA_STATS`	Locks statistics for all objects in a schema	Schema
`UNLOCK_SCHEMA_STATS`	Unlocks statistics for all objects in a schema	Schema
`DELETE_COLUMN_STATS`	Deletes existing column statistics	Column
`DELETE_TABLE_STATS`	Deletes existing table statistics	Table
`DELETE_INDEX_STATS`	Deletes existing index statistics	Index
`DELETE_SCHEMA_STATS`	Deletes existing schema statistics	Schema

Set up test data

The examples in this topic use the following schema, table, and index. Run these statements to create the test objects before trying any example.

Create a schema:
```
CREATE SCHEMA dbms_stats_schema;
```

Create a table:

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 10,000 rows:

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

DBMS_STATS.GATHER_SCHEMA_STATS

Gathers statistics for all objects in a schema and writes a backup to polar_dbms_stats.backup_history.

Syntax

DBMS_STATS.GATHER_SCHEMA_STATS (
  ownname VARCHAR2
);

Parameters

Parameter	Description
`ownname`	Name of the schema to analyze

Example

-- Gather statistics for the schema
CALL DBMS_STATS.GATHER_SCHEMA_STATS('dbms_stats_schema');

-- Verify the backup was created
SELECT * FROM polar_dbms_stats.backup_history;

DBMS_STATS.GATHER_TABLE_STATS

Gathers statistics for a table and writes a backup to polar_dbms_stats.backup_history.

Syntax

DBMS_STATS.GATHER_TABLE_STATS (
  ownname VARCHAR2,
  tabname VARCHAR2
);

Parameters

Parameter	Description
`ownname`	Schema of the table to analyze
`tabname`	Name of the table to analyze

Example

-- Gather statistics for the table
CALL DBMS_STATS.GATHER_TABLE_STATS('dbms_stats_schema', 'dbms_stats_test');

-- Verify the backup was created
SELECT * FROM polar_dbms_stats.backup_history;

DBMS_STATS.GATHER_DATABASE_STATS

Gathers statistics for all objects in the database and writes a backup to polar_dbms_stats.backup_history.

Syntax

DBMS_STATS.GATHER_DATABASE_STATS ();

Example

-- Gather statistics for the entire database
CALL DBMS_STATS.GATHER_DATABASE_STATS();

-- Verify the backup was created
SELECT * FROM polar_dbms_stats.backup_history;

DBMS_STATS.GATHER_INDEX_STATS

Gathers statistics for an index and writes a backup to polar_dbms_stats.backup_history.

Syntax

DBMS_STATS.GATHER_INDEX_STATS (
  ownname VARCHAR2,
  indname VARCHAR2
);

Parameters

Parameter	Description
`ownname`	Schema of the index to analyze
`indname`	Name of the index to analyze

Example

-- Gather statistics for the index
CALL DBMS_STATS.GATHER_INDEX_STATS('dbms_stats_schema', 'dbms_stats_index');

-- Verify the backup was created
SELECT * FROM polar_dbms_stats.backup_history;

DBMS_STATS.GATHER_COLUMN_STATS

Gathers statistics for a specific column and writes a backup to polar_dbms_stats.backup_history.

Syntax

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

Parameters

Parameter	Description
`ownname`	Schema of the column to analyze
`tablename`	Name of the table that contains the column
`attname`	Name of the column to analyze

Example

-- Gather statistics for the column
CALL DBMS_STATS.GATHER_COLUMN_STATS('dbms_stats_schema', 'dbms_stats_test', 'id');

-- Verify the backup was created
SELECT * FROM polar_dbms_stats.backup_history;

DBMS_STATS.RESTORE_SCHEMA_STATS

Restores statistics for all objects in a schema to the state they were in at a specified point in time. Restored statistics are written to polar_dbms_stats.relation_stats_locked.

Syntax

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

Parameters

Parameter	Description
`ownname`	Name of the schema to restore
`as_of_timestamp`	Point in time to restore statistics to

Example

-- Restore schema statistics using a timestamp from the backup history
SELECT DBMS_STATS.RESTORE_SCHEMA_STATS('dbms_stats_schema', time)
FROM polar_dbms_stats.backup_history
WHERE unit = 's';

-- Verify the restored statistics
SELECT count(*) FROM polar_dbms_stats.relation_stats_locked
WHERE relname LIKE 'dbms_stats_schema%';

DBMS_STATS.RESTORE_DATABASE_STATS

Restores statistics for all objects in the database to the state they were in at a specified point in time.

Syntax

DBMS_STATS.RESTORE_DATABASE_STATS (
  as_of_timestamp  TIMESTAMP WITH TIME ZONE
);

Parameters

Parameter	Description
`as_of_timestamp`	Point in time to restore statistics to

Example

-- Restore database statistics using a timestamp from the backup history
SELECT DBMS_STATS.RESTORE_DATABASE_STATS(time)
FROM polar_dbms_stats.backup_history
WHERE unit = 'd';

-- Verify the restored statistics
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 the state they were in at a specified point in time.

Syntax

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

Parameters

Parameter	Description
`ownname`	Schema of the table to restore
`tabname`	Name of the table to restore
`as_of_timestamp`	Point in time to restore statistics to

Example

-- Restore table statistics using a timestamp from the backup history
SELECT DBMS_STATS.RESTORE_TABLE_STATS('dbms_stats_schema', 'dbms_stats_test', time)
FROM polar_dbms_stats.backup_history
WHERE unit = 't';

-- Verify the restored statistics
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 the state they were in at a specified point in time.

Syntax

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

Parameters

Parameter	Description
`ownname`	Schema of the column to restore
`tablename`	Name of the table that contains the column
`attname`	Name of the column to restore
`as_of_timestamp`	Point in time to restore statistics to

Example

-- Restore column statistics using a timestamp from the backup history
SELECT DBMS_STATS.RESTORE_COLUMN_STATS('dbms_stats_schema', 'dbms_stats_test', 'id', time)
FROM polar_dbms_stats.backup_history
WHERE unit = 'c';

-- Verify the restored statistics
SELECT count(*) FROM polar_dbms_stats.relation_stats_locked
WHERE relname LIKE 'dbms_stats_schema%';

DBMS_STATS.PURGE_STATS

Deletes backup statistics that were created before a specified timestamp. Use this procedure to free up storage used by old backups.

Syntax

DBMS_STATS.PURGE_STATS (
  before_timestamp  TIMESTAMP
);

Parameters

Parameter	Description
`before_timestamp`	Backup statistics created before this timestamp are deleted

Example

-- Delete backup statistics older than a timestamp from the backup history
SELECT DBMS_STATS.PURGE_STATS(time)
FROM polar_dbms_stats.backup_history
WHERE unit = 'c';

DBMS_STATS.SET_TABLE_STATS

Sets row count and block count statistics for a table manually. This overrides the values gathered by GATHER_TABLE_STATS or GATHER_SCHEMA_STATS.

Important

Gather table statistics before calling SET_TABLE_STATS. If no statistics exist for the table, 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	Description
`ownname`	Schema of the table
`tabname`	Name of the table
`numrows`	(Optional) Number of rows to set
`numblks`	(Optional) Number of blocks to set

Example

-- Set table statistics manually
CALL DBMS_STATS.SET_TABLE_STATS('dbms_stats_schema', 'dbms_stats_test', 1234, 4321);

DBMS_STATS.GET_TABLE_STATS

Returns the current row count and block count statistics for a table.

Syntax

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

Parameters

Parameter	Description
`ownname`	Schema of the table
`tabname`	Name of the table
`numrows`	OUT: number of rows
`numblks`	OUT: number of blocks

Example

DECLARE
  numrows integer;
  numblks integer;
BEGIN
  CALL DBMS_STATS.GET_TABLE_STATS('dbms_stats_schema', 'dbms_stats_test', numrows, numblks);
  RAISE NOTICE 'numrows: %', numrows;
  RAISE NOTICE 'numblks: %', numblks;
END;

DBMS_STATS.SET_INDEX_STATS

Sets row count and block count statistics for an index manually.

Syntax

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

Parameters

Parameter	Description
`ownname`	Schema of the index
`indname`	Name of the index
`numrows`	(Optional) Number of rows to set
`numblks`	(Optional) Number of blocks to set

Example

-- Set index statistics manually
CALL DBMS_STATS.SET_INDEX_STATS('dbms_stats_schema', 'dbms_stats_index', 2345, 5432);

DBMS_STATS.GET_INDEX_STATS

Returns the current row count and block count statistics for an index.

Syntax

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

Parameters

Parameter	Description
`ownname`	Schema of the index
`tabname`	Name of the index
`numrows`	OUT: number of rows
`numblks`	OUT: number of blocks

Example

DECLARE
  numrows integer;
  numblks integer;
BEGIN
  CALL DBMS_STATS.GET_INDEX_STATS('dbms_stats_schema', 'dbms_stats_index', numrows, numblks);
  RAISE NOTICE 'numrows: %', numrows;
  RAISE NOTICE 'numblks: %', numblks;
END;

DBMS_STATS.GET_COLUMN_STATS

Returns distinct value count, null 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	Description
`ownname`	Schema of the column
`tabname`	Name of the table that contains the column
`colname`	Name of the column
`distcnt`	OUT: number of distinct values
`nullcnt`	OUT: number of null values
`avgclen`	OUT: average column length

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: %', distcnt;
  RAISE NOTICE 'nullcnt: %', nullcnt;
  RAISE NOTICE 'avgclen: %', avgclen;
END;

DBMS_STATS.LOCK_TABLE_STATS

Locks statistics for a table to prevent automatic updates. Locked statistics are written to polar_dbms_stats.relation_stats_locked.

Syntax

DBMS_STATS.LOCK_TABLE_STATS (
  ownname  VARCHAR2,
  tabname  VARCHAR2
);

Parameters

Parameter	Description
`ownname`	Schema of the table to lock
`tabname`	Name of the table to lock

Example

-- Lock table statistics
CALL DBMS_STATS.LOCK_TABLE_STATS('dbms_stats_schema', 'dbms_stats_test');

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

DBMS_STATS.UNLOCK_TABLE_STATS

Unlocks statistics for a table, allowing automatic updates to resume.

Syntax

DBMS_STATS.UNLOCK_TABLE_STATS (
  ownname  VARCHAR2,
  tabname  VARCHAR2
);

Parameters

Parameter	Description
`ownname`	Schema of the table to unlock
`tabname`	Name of the table to unlock

Example

-- Unlock table statistics
CALL DBMS_STATS.UNLOCK_TABLE_STATS('dbms_stats_schema', 'dbms_stats_test');

-- Verify the lock is removed
SELECT count(*) FROM polar_dbms_stats.relation_stats_locked
WHERE relname = 'dbms_stats_schema.dbms_stats_test';

DBMS_STATS.LOCK_SCHEMA_STATS

Locks statistics for all objects in a schema to prevent automatic updates.

Syntax

DBMS_STATS.LOCK_SCHEMA_STATS (
  ownname  VARCHAR2
);

Parameters

Parameter	Description
`ownname`	Name of the schema to lock

Example

-- Lock schema statistics
CALL DBMS_STATS.LOCK_SCHEMA_STATS('dbms_stats_schema');

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

DBMS_STATS.UNLOCK_SCHEMA_STATS

Unlocks statistics for all objects in a schema, allowing automatic updates to resume.

Syntax

DBMS_STATS.UNLOCK_SCHEMA_STATS (
  ownname  VARCHAR2
);

Parameters

Parameter	Description
`ownname`	Name of the schema to unlock

Example

-- Unlock schema statistics
CALL DBMS_STATS.UNLOCK_SCHEMA_STATS('dbms_stats_schema');

-- Verify the locks are removed
SELECT count(*) FROM polar_dbms_stats.relation_stats_locked
WHERE relname LIKE 'dbms_stats_schema.%';

DBMS_STATS.LOCK_COLUMN_STATS

Locks statistics for a column to prevent automatic updates.

Syntax

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

Parameters

Parameter	Description
`ownname`	Schema of the column to lock
`tabname`	Name of the table that contains the column
`attname`	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 statistics for a column, allowing automatic updates to resume.

Syntax

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

Parameters

Parameter	Description
`ownname`	Schema of the column to unlock
`tabname`	Name of the table that contains the column
`attname`	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 existing statistics for a table.

Syntax

DBMS_STATS.DELETE_TABLE_STATS (
  ownname  VARCHAR2,
  tabname  VARCHAR2
);

Parameters

Parameter	Description
`ownname`	Schema of the table
`tabname`	Name of the table

Example

-- Delete table statistics
CALL DBMS_STATS.DELETE_TABLE_STATS('dbms_stats_schema', 'dbms_stats_test');

-- Verify the statistics are deleted
SELECT count(*) FROM polar_dbms_stats.relation_stats_backup
WHERE relname = 'dbms_stats_schema.dbms_stats_test';

DBMS_STATS.DELETE_COLUMN_STATS

Deletes existing statistics for a column.

Syntax

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

Parameters

Parameter	Description
`ownname`	Schema of the column
`tabname`	Name of the table that contains the column
`attname`	Name of the column

Example

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

DBMS_STATS.DELETE_SCHEMA_STATS

Deletes existing statistics for all objects in a schema.

Syntax

DBMS_STATS.DELETE_SCHEMA_STATS (
  ownname  VARCHAR2
);

Parameters

Parameter	Description
`ownname`	Name of the schema

Example

-- Delete schema statistics
CALL DBMS_STATS.DELETE_SCHEMA_STATS('dbms_stats_schema');

-- Verify the statistics are deleted
SELECT count(*) FROM polar_dbms_stats.relation_stats_backup
WHERE relname LIKE 'dbms_stats_schema.%';

DBMS_STATS.DELETE_INDEX_STATS

Deletes existing statistics for an index.

Syntax

DBMS_STATS.DELETE_INDEX_STATS (
  ownname  VARCHAR2,
  indname  VARCHAR2
);

Parameters

Parameter	Description
`ownname`	Schema of the index
`indname`	Name of the index

Example

-- Delete index statistics
CALL DBMS_STATS.DELETE_INDEX_STATS('dbms_stats_schema', 'dbms_stats_index');

-- Verify the statistics are deleted
SELECT count(*) FROM polar_dbms_stats.relation_stats_backup
WHERE relname LIKE 'dbms_stats_schema.%';