达梦数据库的DBMS_STATS包

达梦数据库的DBMS_STATS包

基础信息
OS版本：
Red Hat Enterprise Linux Server release 7.9 (Maipo)
DB版本：
DM Database Server 64 V8
DB Version: 0x7000c
03134284132-20240115-215128-20081

达梦数据库（DM Database）提供了 DBMS_STATS 包，这个包主要用于管理和维护数据库的统计信息。通过该包，您可以收集、删除、锁定和解锁表和索引的统计信息，从而帮助优化器生成更高效的查询执行计划。

DBMS_STATS 包的常用存储过程和函数

1. GATHER_TABLE_STATS：收集指定表及其索引的统计信息。
2. GATHER_INDEX_STATS：收集指定索引的统计信息。
3. GATHER_SCHEMA_STATS：收集指定模式下所有表和索引的统计信息。
4. DELETE_TABLE_STATS：删除指定表及其索引的统计信息。
5. DELETE_SCHEMA_STATS：删除指定模式下所有表和索引的统计信息。
6. TABLE_STATS_SHOW：查看指定表的统计信息。

使用示例

1. 收集指定表的统计信息

DBMS_STATS.GATHER_TABLE_STATS(
    OWNNAME => 'SCHEMA_NAME',  -- 模式名称
    TABNAME => 'TABLE_NAME',   -- 表名称
    CASCADE => TRUE,           -- 是否收集与表相关的索引的统计信息
    ESTIMATE_PERCENT => 100    -- 采样百分比，100 表示全表
);

示例：收集 DMTEST 模式下 T1 表的统计信息

SQL> DBMS_STATS.GATHER_TABLE_STATS(
2       OWNNAME => 'DMTEST',
3       TABNAME => 'T1',
4       CASCADE => TRUE,
5       ESTIMATE_PERCENT => 100
6   );
DMSQL 过程已成功完成
已用时间: 22.637(毫秒). 执行号:811.

2. 收集指定索引的统计信息

DBMS_STATS.GATHER_INDEX_STATS(
    OWNNAME => 'SCHEMA_NAME',  -- 模式名称
    INDNAME => 'INDEX_NAME'    -- 索引名称
);

示例：收集 DMTEST 模式下 IDX_T1_1 索引的统计信息

SQL> DBMS_STATS.GATHER_INDEX_STATS(
2       OWNNAME => 'DMTEST',
3       INDNAME => 'IDX_T1_1'
4   );
DMSQL 过程已成功完成
已用时间: 19.804(毫秒). 执行号:813.

3. 收集指定模式下所有表和索引的统计信息

DBMS_STATS.GATHER_SCHEMA_STATS(
    OWNNAME => 'SCHEMA_NAME',  -- 模式名称
    ESTIMATE_PERCENT => 100    -- 采样百分比，100 表示全表
);

示例：收集 DMTEST 模式下所有表和索引的统计信息

SQL> DBMS_STATS.GATHER_SCHEMA_STATS(
2       OWNNAME => 'DMTEST',
3       ESTIMATE_PERCENT => 100
4   );
DMSQL 过程已成功完成
已用时间: 30.627(毫秒). 执行号:814.

4. 删除指定表的统计信息

DBMS_STATS.DELETE_TABLE_STATS(
    OWNNAME => 'SCHEMA_NAME',  -- 模式名称
    TABNAME => 'TABLE_NAME'    -- 表名称
);

示例：删除 DMTEST 模式下 T1 表的统计信息

SQL> DBMS_STATS.DELETE_TABLE_STATS(
2       OWNNAME => 'DMTEST',
3       TABNAME => 'T1'
4   );
DMSQL 过程已成功完成
已用时间: 5.937(毫秒). 执行号:815.

5. 删除指定模式下所有表的统计信息

DBMS_STATS.DELETE_SCHEMA_STATS(
    OWNNAME => 'SCHEMA_NAME'  -- 模式名称
);

示例：删除 DMTEST 模式下所有表的统计信息

SQL> DBMS_STATS.DELETE_SCHEMA_STATS(
2       OWNNAME => 'DMTEST'
3   );
DMSQL 过程已成功完成
已用时间: 3.111(毫秒). 执行号:816.

6. 查看指定表的统计信息

DBMS_STATS.TABLE_STATS_SHOW(
    OWNNAME => 'SCHEMA_NAME'，  -- 模式名称
    TABNAME => 'TABLE_NAME'     -- 表名称
);

示例：查看DMTEST.T1表的统计信息

SQL> DBMS_STATS.TABLE_STATS_SHOW(
2       OWNNAME => 'DMTEST'，  
3       TABNAME => 'T1'     
4   );

行号     NUM_ROWS             LEAF_BLOCKS          LEAF_USED_BLOCKS    
---------- -------------------- -------------------- --------------------
1          10000                48                   48

已用时间: 0.337(毫秒). 执行号:611.

定期收集统计信息

为了确保优化器能够使用最新和最准确的统计信息，建议定期收集统计信息。可以通过达梦数据库的任务调度功能，安排定期任务来自动收集统计信息。

创建定时任务

BEGIN
    DBMS_SCHEDULER.CREATE_JOB(
        JOB_NAME         => 'GATHER_STATS_JOB',
        JOB_TYPE         => 'PLSQL_BLOCK',
        JOB_ACTION       => 'BEGIN DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME => ''PUBLIC'', ESTIMATE_PERCENT => 100); END;',
        START_DATE       => SYSDATE,
        REPEAT_INTERVAL  => 'FREQ=DAILY; BYHOUR=2; BYMINUTE=0; BYSECOND=0',
        ENABLED          => TRUE
    );
END;

上述定时任务将在每天凌晨 2 点运行，自动收集 PUBLIC 模式下所有表和索引的统计信息。

通过使用 DBMS_STATS 包，达梦数据库管理员可以有效地管理统计信息，优化查询执行计划，提高数据库性能。

DBMS_STATS的更多使用方法参考官方文档《DM8系统包使用手册.pdf》