Managing statistics with DBMS_STATS - PolarDB - Alibaba Cloud Documentation Center

Prerequisites

Contact us to use the DBMS_STATS package features.

Prepare test data

Note

This test data applies only to the operation examples 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 gathers and backs up schema-level statistics.

Syntax

DBMS_STATS.GATHER_SCHEMA_STATS (
        ownname          VARCHAR2
    );

Parameters

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

Example

Gather and back up schema-level statistics:

CALL DBMS_STATS.GATHER_SCHEMA_STATS('dbms_stats_schema');

Query the statistics backup history:

SELECT * FROM polar_dbms_stats.backup_history;

DBMS_STATS.GATHER_TABLE_STATS

Use this function to collect and back up table-level statistics.

Syntax

DBMS_STATS.GATHER_TABLE_STATS (
        ownname          VARCHAR2, 
        tabname          VARCHAR2
    );

Parameters

Parameter	Description	Required
ownname	The schema that owns the target table.	Yes
tabname	The name of the target table.	Yes

Example

Collect and back up table-level statistics:

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

Query the statistics backup history:

SELECT * FROM polar_dbms_stats.backup_history;

DBMS_STATS.GATHER_DATABASE_STATS

This function collects and backs up database statistics.

Syntax

DBMS_STATS.GATHER_DATABASE_STATS ();

Example

Collect and back up database statistics:

CALL DBMS_STATS.GATHER_DATABASE_STATS();

Query the statistics backup history:

SELECT * FROM polar_dbms_stats.backup_history;

DBMS_STATS.GATHER_INDEX_STATS

This function gathers and backs up index statistics.

Syntax

DBMS_STATS.GATHER_INDEX_STATS (
        ownname          VARCHAR2, 
        indname          VARCHAR2
    );

Parameters

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

Example

To collect and back up index-level statistics, run the following statement:

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

To query the statistics backup history, run the following query:

SELECT * FROM polar_dbms_stats.backup_history;

DBMS_STATS.GATHER_COLUMN_STATS

This function collects and backs up statistics for a specified column.

Syntax

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

Parameters

Parameter	Description	Required
ownname	The name of the schema that contains the table.	Yes
tablename	The name of the table that contains the column.	Yes
attname	The name of the column for which to gather statistics.	Yes

Example

Collect and back up statistics for a specified column:

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

To query the statistics backup history, run the following query:

SELECT * FROM polar_dbms_stats.backup_history;

DBMS_STATS.RESTORE_SCHEMA_STATS

This function restores 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 schema for which to restore statistics.	Yes
as_of_timestamp	The point in time to restore the statistics to.	Yes

Example

Restore statistics for a specified schema:

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

Query the restored statistics:

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

DBMS_STATS.RESTORE_DATABASE_STATS

This function restores database statistics.

Syntax

DBMS_STATS.RESTORE_DATABASE_STATS (
        as_of_timestamp        TIMESTAMP WITH TIME ZONE
    );

Parameters

Parameter	Description	Required
as_of_timestamp	The timestamp that specifies the restoration point.	Yes

Example

The following example restores database statistics:

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

The following example queries the restored statistics:

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

DBMS_STATS.RESTORE_TABLE_STATS

This function restores 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	The name of the schema that contains the table.	Yes
tabname	The name of the table.	Yes
as_of_timestamp	The timestamp to restore the statistics to.	Yes

Example

The following example restores statistics for a table:

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

To query the statistics after restoring them, run the following query:

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

DBMS_STATS.RESTORE_COLUMN_STATS

Restores 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	The name of the schema.	Yes
tabname	The name of the table.	Yes
attname	The name of the column.	Yes
as_of_timestamp	The timestamp to restore the statistics to.	Yes

Example

The following query restores statistics for a specified column:

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

To verify the restored statistics, run the following query:

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

DBMS_STATS.PURGE_STATS

Purges statistics backups created before a specified timestamp.

Syntax

DBMS_STATS.PURGE_STATS (
        before_timestamp timestamp
    );

Parameters

Parameter	Description	Required
before_timestamp	The cutoff timestamp for purging statistics backups.	Yes

Example

Purge statistics backups created before a specific timestamp:

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

DBMS_STATS.SET_TABLE_STATS

Sets the statistics for a specified table.

Note

If statistics have not been gathered for the specified table, run gather_schema_stats or gather_table_stats before calling set_table_stats.

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 that contains the table.	Yes
tabname	The name of the table.	Yes
numrows	The number of rows in the table.	No
numblks	The number of blocks in the table.	No

Example

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

DBMS_STATS.GET_TABLE_STATS

Retrieves 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	The name of the schema that contains the table.	Yes
tabname	The table name.	Yes
numrows	The number of rows in the table.	Yes
numblks	The number of blocks in the table.	Yes

Example

The following example retrieves table statistics:

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 sets the statistics for an index.

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 that contains the index.	Yes
indname	The index name.	Yes
numrows	The number of rows in the index.	No
numblks	The number of blocks in the index.	No

Example

The following example sets statistics for an index:

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

DBMS_STATS.GET_INDEX_STATS

Retrieves 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	The schema that owns the index.	Yes
tabname	The name of the index.	Yes
numrows	The number of rows in the index.	Yes
numblks	The number of data blocks in the index.	Yes

Example

This example shows how to retrieve statistics for an index:

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 retrieves statistics for a specific 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 name of the schema.	Yes
tabname	The name of the table.	Yes
colname	The name of the column.	Yes
distcnt	The number of distinct values in the column.	Yes
nullcnt	The number of null values in the column.	Yes
avgclen	The average length of the column.	Yes

Example

The following example shows how to retrieve statistics for a column:

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 table statistics for a specified table.

Syntax

DBMS_STATS.LOCK_TABLE_STATS (
        ownname       VARCHAR2, 
        tabname       VARCHAR2 
    );

Parameters

Parameter	Description	Required
ownname	The name of the table's schema.	Yes
tabname	The table name.	Yes

Example

To lock table statistics, run the following command:

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

To view the locked statistics, run the following statement:

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

DBMS_STATS.UNLOCK_TABLE_STATS

This procedure unlocks a table's statistics.

Syntax

DBMS_STATS.UNLOCK_TABLE_STATS (
        ownname       VARCHAR2, 
        tabname       VARCHAR2 
    );

Parameters

Parameter	Description	Required
ownname	The schema containing the table.	Yes
tabname	The name of the table.	Yes

Example

This example unlocks a table's statistics:

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

Run the following query to check for locked statistics:

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

DBMS_STATS.LOCK_SCHEMA_STATS

This function locks the statistics for a specified schema.

Syntax

DBMS_STATS.LOCK_SCHEMA_STATS (
        ownname       VARCHAR2
    );

Parameters

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

Example

To lock the statistics for a schema:

CALL DBMS_STATS.LOCK_SCHEMA_STATS('dbms_stats_schema');

To check the locked statistics, run the following query:

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

DBMS_STATS.UNLOCK_SCHEMA_STATS

This function unlocks a specified schema's statistics.

Syntax

DBMS_STATS.UNLOCK_SCHEMA_STATS (
        ownname       VARCHAR2
    );

Parameters

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

Example

To unlock a schema's statistics:

CALL DBMS_STATS.UNLOCK_SCHEMA_STATS('dbms_stats_schema');

To check for locked statistics, run the following query:

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

DBMS_STATS.LOCK_COLUMN_STATS

This function locks the statistics for a specified column.

syntax

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

Parameters

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

Example

This example locks the statistics for a column:

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

DBMS_STATS.UNLOCK_COLUMN_STATS

This function unlocks the statistics for a specified column.

Syntax

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

Parameters

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

Example

The following example unlocks the statistics for a specified column:

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

DBMS_STATS.DELETE_TABLE_STATS

This procedure deletes table statistics.

Syntax

DBMS_STATS.DELETE_TABLE_STATS (
        ownname       VARCHAR2, 
        tabname       VARCHAR2
    );

Parameters

Parameter	Description	Required
ownname	The schema that owns the table.	Yes
tabname	The table name.	Yes

Example

Delete table statistics:

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

Check the table statistics after deletion:

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

DBMS_STATS.DELETE_COLUMN_STATS

This procedure deletes column statistics.

Syntax

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

Parameters

Parameter	Description	Required
ownname	The name of the schema that contains the column.	Yes
tabname	The name of the table that contains the column.	Yes
attname	The name of the column to delete statistics for.	Yes

Example

The following example deletes column statistics:

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

DBMS_STATS.DELETE_SCHEMA_STATS

This procedure deletes existing statistics for a specified schema.

Syntax

DBMS_STATS.DELETE_SCHEMA_STATS (
        ownname       VARCHAR2 
    );

Parameters

Parameter	Description	Required
ownname	The name of the schema whose statistics are to be deleted.	Yes

Example

The following example deletes schema statistics:

CALL DBMS_STATS.DELETE_SCHEMA_STATS('dbms_stats_schema');

To verify that the statistics were deleted, run the following query:

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

DBMS_STATS.DELETE_INDEX_STATS

Deletes the statistics for an index.

Syntax

DBMS_STATS.DELETE_INDEX_STATS (
        ownname       VARCHAR2,
        indname       VARCHAR2 
    );

Parameters

Parameter	Description	Required
ownname	The schema that owns the index.	Yes
indname	The index name.	Yes

Example

Delete index statistics:

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

Query the statistics after deletion:

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