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

前提条件

  • 已安装polar_dbms_stats插件，安装命令如下：
    CREATE EXTENSION polar_dbms_stats;
  • polar_dbms_stats.use_locked_stats参数的状态为on。
    说明 您可以通过如下命令查看该参数的状态：
    SHOW polar_dbms_stats.use_locked_stats;

准备测试数据

说明 测试数据仅适用本文中的操作示例。
  1. 创建Schema，名为dbms_stats_schema。
    CREATE SCHEMA dbms_stats_schema;
  2. 创建表，名为dbms_stats_test。
    CREATE TABLE dbms_stats_schema.dbms_stats_test(id int);
  3. 创建索引。
    CREATE INDEX dbms_stats_index on dbms_stats_schema.dbms_stats_test(id);
  4. 插入数据。
    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.%';