达梦数据库统计信息自动收集功能

开启全库自动统计信息收集

开启自动收集统计信息参数

SP_SET_PARA_VALUE(1,'AUTO_STAT_OBJ',1);
SP_SET_PARA_VALUE(1,'MONITOR_MODIFICATIONS',1);

创建辅助表和存储过程

create table AUTO_STAT_INFO(
task_id INT,
total_stat INT,
table_id INT, 
sch_name varchar(24), 
table_name varchar(24),
curr_gath_tab_id INT, 
curr_gath_sch_name varchar(24), 
curr_gath_tab_name varchar(24),
success_stat INT,
fail_stat INT,
task_start_time DATETIME, 
task_end_time DATETIME,
gather_tbl_start_time DATETIME,
gather_tbl_end_time DATETIME
);
CREATE OR REPLACE PROCEDURE  SYSDBA.GET_AUTO_STAT_INFO_FUNC(task_id INT,total_stat INT,table_id INT, sch_name varchar(24), table_name varchar(24),curr_gath_tab_id INT, curr_gath_sch_name varchar(24), curr_gath_tab_name varchar(24),success_stat INT,fail_stat INT,task_start_time DATETIME, task_end_time DATETIME,gather_tbl_start_time DATETIME,gather_tbl_end_time DATETIME) as
BEGIN
    INSERT INTO AUTO_STAT_INFO VALUES(task_id,total_stat,table_id, sch_name,table_name,curr_gath_tab_id, curr_gath_sch_name, curr_gath_tab_name,success_stat ,fail_stat,task_start_time,task_end_time,gather_tbl_start_time,gather_tbl_end_time);
    commit;
EXCEPTION
    WHEN OTHERS THEN
        NULL;
END;

开启统计信息自动收集任务

SP_CREATE_AUTO_STAT_TRIGGER(1, 1, 1, 1,'10:24', '2024/10/24',0,1);

创建测试表，插入数据

--创建测试表，插入数据
CREATE TABLE T(ID INT);
insert into t SELECT 1 from dual;
commit; 

查询系统表，验证统计信息收集情况

--观察自动统计信息收集情况
SELECT * FROM  SYSSTATS;