如何使用DBMS_STATS对统计信息进行不同的处理 - 云原生数据库 PolarDB

PolarDB PostgreSQL版（兼容Oracle）提供了DBMS_STATS包，用于收集数据库运行过程中的统计信息，提供现有统计信息的备份，并支持将备份统计项进行还原。您可以自定义备份统计信息的时间点，根据需求修改表的统计信息，从而辅助优化器生成期望中的执行计划。

前提条件

如您需要使用DBMS_STATS包相关功能，请联系我们处理。

准备测试数据

说明

测试数据仅适用于本文中的操作示例。

创建Schema，名为dbms_stats_schema。
```
CREATE SCHEMA dbms_stats_schema;
```

创建表，名为dbms_stats_test。

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

DBMS_STATS.GATHER_SCHEMA_STATS

该函数用于收集和备份Schema级的统计信息。

语法

DBMS_STATS.GATHER_SCHEMA_STATS (
        ownname          VARCHAR2
    );

参数

参数	描述	是否必填
ownname	待分析的Schema名称。	是

示例

收集和备份Schema级的统计信息，示例如下：

CALL DBMS_STATS.GATHER_SCHEMA_STATS('dbms_stats_schema');

查询统计信息的备份情况，查询示例如下：

SELECT * FROM polar_dbms_stats.backup_history;

DBMS_STATS.GATHER_TABLE_STATS

该函数用于收集和备份表级的统计信息。

语法

DBMS_STATS.GATHER_TABLE_STATS (
        ownname          VARCHAR2, 
        tabname          VARCHAR2
    );

参数

参数	描述	是否必填
ownname	待分析的表所属的Schema。	是
tabname	待分析的表名称。	是

示例

收集并备份表级的统计信息，示例如下：

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

查询统计信息备份情况，查询示例如下：

SELECT * FROM polar_dbms_stats.backup_history;

DBMS_STATS.GATHER_DATABASE_STATS

该函数用于收集和备份数据库级的统计信息。

语法

DBMS_STATS.GATHER_DATABASE_STATS ();

示例

收集并备份数据库级的统计信息，示例如下：

CALL DBMS_STATS.GATHER_DATABASE_STATS();

查询统计信息备份情况，查询示例如下：

SELECT * FROM polar_dbms_stats.backup_history;

DBMS_STATS.GATHER_INDEX_STATS

该函数用于收集和备份索引的统计信息。

语法

DBMS_STATS.GATHER_INDEX_STATS (
        ownname          VARCHAR2, 
        indname          VARCHAR2
    );

参数

参数	描述	是否必填
ownname	待分析的索引所属的Schema。	是
indname	待分析的索引名称。	是

示例

收集并备份索引级的统计信息，示例如下：

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

查询统计信息备份情况，查询示例如下：

SELECT * FROM polar_dbms_stats.backup_history;

DBMS_STATS.GATHER_COLUMN_STATS

该函数用于收集和备份指定列的统计信息。

语法

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

参数

参数	描述	是否必填
ownname	待分析的列所属的Schema。	是
tablename	待分析的列所属的表。	是
attname	待分析的列名称。	是

示例

收集并备份指定列的统计信息，示例如下：

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

该函数用于还原指定Schema的统计信息。

语法

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

参数

参数	描述	是否必填
ownname	待还原的Schema名称。	是
as_of_timestamp	待还原的时间点。	是

示例

还原指定Schema的统计信息，示例如下：

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

该函数用于还原数据库的统计信息。

语法

DBMS_STATS.RESTORE_DATABASE_STATS (
        as_of_timestamp        TIMESTAMP WITH TIME ZONE
    );

参数

参数	描述	是否必填
as_of_timestamp	待还原的时间点。	是

示例

还原数据库统计信息，示例如下：

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

该函数用于还原指定表的统计信息。

语法

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

参数

参数	描述	是否必填
ownname	待还原的表所属的Schema。	是
tabname	待还原的表名称。	是
as_of_timestamp	待还原的时间点。	是

示例

还原指定表统计信息，示例如下：

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

该函数用于还原指定列的统计信息。

语法

DBMS_STATS.RESTORE_COLUMN_STATS (
        ownname         TEXT,
        tablename       TEXT,
        attname         TEXT,
        as_of_timestamp timestamp with time zone
    );

参数

参数	描述	是否必填
ownname	待还原的列所属的Schema。	是
tabname	待还原的列所属的表。	是
attname	待还原的列名。	是
as_of_timestamp	待还原的时间点。	是

示例

还原指定列统计信息，示例如下：

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

该函数用于删除指定时间点之前的统计信息备份。

语法

DBMS_STATS.PURGE_STATS (
        before_timestamp timestamp
    );

参数

参数	描述	是否必填
before_timestamp	指定的删除时间点。	是

示例

删除指定时间点之前的统计信息备份，示例如下：

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

DBMS_STATS.SET_TABLE_STATS

该函数用于设置指定表的统计信息。

说明

在进行set_table_stats前，如果没有采集相关表的统计信息，需要先执行gather_schema_stats或者gather_table_stats。

语法

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

参数

参数	描述	是否必填
ownname	待设置的表所属的Schema。	是
tabname	待设置的表名称。	是
numrows	设置的表行数。	是
numblks	设置的表块数。	是

示例

设置表的统计信息，示例如下：

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

DBMS_STATS.GET_TABLE_STATS

该函数用于获取指定表的统计信息。

语法

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

参数

参数	描述	是否必填
ownname	待获取的表所属的Schema。	是
tabname	待获取的表名称。	是
numrows	获取的表行数。	是
numblks	获取的表块数。	是

示例

获取表的统计信息，示例如下：

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

该函数用于设置索引的统计信息。

语法

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

参数

参数	描述	是否必填
ownname	待设置的索引所属的Schema。	是
indname	待设置的索引名称。	是
numrows	设置的索引行数。	是
numblks	设置的索引块数。	是

示例

设置索引的统计信息，示例如下：

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

DBMS_STATS.GET_INDEX_STATS

该函数用于获取指定索引的统计信息。

语法

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

参数

参数	描述	是否必填
ownname	待获取的索引所属的Schema。	是
tabname	待获取的索引名称。	是
numrows	获取的索引行数。	是
numblks	获取的索引块数。	是

示例

获取索引的统计信息，示例如下：

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

该函数用于获取指定列的统计信息。

语法

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

参数

参数	描述	是否必填
ownname	待获取的列所属的Schema。	是
tabname	待获取的列所属的表。	是
colname	待获取的列名称。	是
distcnt	获取的列中不同值的数量。	是
nullcnt	获取的列空值数量。	是
avgclen	获取的列平均长度。	是

示例

获取列的统计信息，示例如下：

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

该函数用于锁定正在使用的表统计信息。

语法

DBMS_STATS.LOCK_TABLE_STATS (
        ownname       VARCHAR2, 
        tabname       VARCHAR2 
    );

参数

参数	描述	是否必填
ownname	待锁定的表所属的Schema。	是
tabname	待锁定的表名称。	是

示例

锁定正在使用的表的统计信息，示例如下：

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

该函数用于解锁正在使用的表统计信息。

语法

DBMS_STATS.UNLOCK_TABLE_STATS (
        ownname       VARCHAR2, 
        tabname       VARCHAR2 
    );

参数

参数	描述	是否必填
ownname	待解锁的表所属的Schema。	是
tabname	待解锁的表名称。	是

示例

解锁正在使用的表的统计信息，示例如下：

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

该函数用于锁定正在使用的Schema的统计信息。

语法

DBMS_STATS.LOCK_SCHEMA_STATS (
        ownname       VARCHAR2
    );

参数

参数	描述	是否必填
ownname	待锁定的Schema名称。	是

示例

锁定正在使用的Schema的统计信息，示例如下：

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

该函数用于解锁正在使用的Schema的统计信息。

语法

DBMS_STATS.UNLOCK_SCHEMA_STATS (
        ownname       VARCHAR2
    );

参数

参数	描述	是否必填
ownname	待解锁的Schema名称。	是

示例

解锁正在使用的Schema的统计信息，示例如下：

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

该函数用于锁定正在使用的列的统计信息。

语法

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

参数

参数	描述	是否必填
ownname	待锁定的列所属的Schema。	是
tabname	待锁定的列所属的表。	是
attname	待锁定的列名称。	是

示例

锁定正在使用的列的统计信息，示例如下：

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

DBMS_STATS.UNLOCK_COLUMN_STATS

该函数用于解锁正在使用的列的统计信息。

语法

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

参数

参数	描述	是否必填
ownname	待解锁的列所属的Schema。	是
tabname	待解锁的列所属的表。	是
attname	待解锁的列名称。	是

示例

解锁正在使用的列的统计信息，示例如下：

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

DBMS_STATS.DELETE_TABLE_STATS

该函数用于删除已有的表统计信息。

语法

DBMS_STATS.DELETE_TABLE_STATS (
        ownname       VARCHAR2, 
        tabname       VARCHAR2
    );

参数

参数	描述	是否必填
ownname	待删除的表所属的Schema。	是
tabname	待删除的表名称。	是

示例

删除已有的表统计信息，示例如下：

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

该函数用于删除已有的列统计信息。

语法

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

参数

参数	描述	是否必填
ownname	待删除的列所属的Schema。	是
tabname	待删除的列所属的表。	是
attname	待删除的列名称。	是

示例

删除已有的列统计信息，示例如下：

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

DBMS_STATS.DELETE_SCHEMA_STATS

该函数用于删除已有的Schema对象统计信息。

语法

DBMS_STATS.DELETE_SCHEMA_STATS (
        ownname       VARCHAR2 
    );

参数

参数	描述	是否必填
ownname	待删除的Schema名称。	是

示例

删除已有的Schema统计信息，示例如下：

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

该函数用于删除已有的索引对象统计信息。

语法

DBMS_STATS.DELETE_INDEX_STATS (
        ownname       VARCHAR2,
        indname       VARCHAR2 
    );

参数

参数	描述	是否必填
ownname	待删除的索引所属的Schema。	是
indname	待删除的索引名称。	是

示例

删除已有的索引统计信息，示例如下：

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.%';