在数据库实际运维中，统计信息的准确度直接影响到执行计划准确度，因此在没有开启动态统计信息的情况下可以使用监控表自动收集的功能，当表变化达到设定的阈值时，在指定的时间就会对该表进行统计信息收集，下面简单介绍一下基于DML监控下自动收集统计信息的功能

参数介绍

相关参数
MONITOR_MODIFICATIONS
0 动态，系统级 是否监控用户表对象的数据变化情况，包括增删改导致的修改行数、TRUNCATE表操作以及最新修改时间等。0：不监控；1：对所有表进行监控；2：只对用户执行过SET_TABLE_PREFS的表进行监控。
DMDPC环境下不支持动态修改该参数

MON_CHECK_INTERVAL
3600 动态，系统级 将用户表对象的数据变化情况记录到系统表的时间间隔，单位为秒，即每过设置的秒数便将用户表对象的数据变化情况记录到系统表中。仅当MONITOR_MODIFICATIONS非0时生效

AUTO_STAT_OBJ
0 动态，系统级 是否监控增删改导致的修改行数和TRUNCATE表操作。0：不监控；1：对所有用户表进行监控；2：只对用户通过DBMS_STATS. SET_TABLE_PREFS设置过STALE_PERCENT属性的用户表进行监控。
DMDPC环境下不支持动态修改该参数；DMDSC环境下仅允许控制节点动态修改该参数

系统包方法介绍

DBMS_STATS.SET_TABLE_PREFS
该过程设置普通用户表的静态属性值。
语法如下：
PROCEDURE SET_TABLE_PREFS (
OWNNAME VARCHAR(128),
TABNAME VARCHAR(128),
PPNAME VARCHAR(128),
PVALUE VARCHAR(128)
);
参数详解
OWNNAME
模式名，不区分大小写
TABNAME
表名，不区分大小写。
PPNAME
属性名，不区分大小写。可以取 STALE_PERCENT 、ESTIMATE_PERCENT、METHOD_OPT、DEGREE、GRANULARITY、CASCADE、NO_INVALIDATE、PREFERENCE_OVERRIDES_PARAMETER、MONITOR_MODIFICATIONS_FLAG、MONITOR_MODIFICATIONS_RM_PLN_FLAG 和 MONITOR_MODIFICATIONS_REGATHER_COL。

STALE_PERCENT 决定表的统计信息过时且需要重收集统计信息时表的行数百分比。有效取值范围是非负数，缺省值是 10，即一张表有超过 10% 的数据改变则可认为统计信息过时。取值范围[0,100]，该值置为 0 时，则认为需要将该表的 STALE_PERCENT 属性值置为缺省值。

PREFERENCE_OVERRIDES_PARAMETER 决定是否用静态属性值覆盖参数的输入值。为 TRUE 时，忽略参数的输入值，使用对应的属性值；为 FALSE 时，使用参数的输入值。

MONITOR_MODIFICATIONS_FLAG 决定是否对表进行数据变化监控。为 TRUE 时，监控表的数据变化；为 FALSE 时，不监控表的数据变化。默认值为 FALSE。

MONITOR_MODIFICATIONS_RM_PLN_FLAG 决定对表进行数据变化监控时，当数据变化达到该阈值时，会移除该表相关的缓存计划，同时按照指定方式重新收集该表的统计信息。若后续未指定 MONITOR_MODIFICATIONS_REGATHER_COL，则重新对该表所有存在频率直方图统计信息的列和索引收集统计信息；若后续指定了 MONITOR_MODIFICATIONS_REGATHER_COL，则只对指定列及包含它的索引收集统计信息，同一张表上可指定多个不同的列。必须先指定 MONITOR_MODIFICATIONS_RM_PLN_FLAG，才可以指定 MONITOR_MODIFICATIONS_REGATHER_COL。

其他属性值范围可以参考 GATHER_TABLE_STATS 中的参数描述。

PVALUE

系统过程介绍
SP_CREATE_AUTO_STAT_TRIGGER

定义：
SP_CREATE_AUTO_STAT_TRIGGER(
type int,
freq_interval int,
freq_sub_interval int,
freq_minute_interval int,
starttime varchar(128),
during_start_date varchar(128),
max_run_duration int,
enable int
)
功能说明：
INI 参数 AUTO_STAT_OBJ 开启时，启用自动收集统计信息功能。
参数说明：
type：指定调度类型。可取值 1、2、3、4、5、6、7、8，缺省为 1，不同取值意义分别介绍如下：
1：按天的频率来执行；
2：按周的频率来执行；
3：在一个月的某一天执行；
4：在一个月的第一周第几天执行；
5：在一个月的第二周的第几天执行；
6：在一个月的第三周的第几天执行；
7：在一个月的第四周的第几天执行；
8：在一个月的最后一周的第几天执行。
freq_interval：与 type 有关，表示不同调度类型下的发生频率，默认为 1。具体说明如下：
当 type=1 时，表示每几天执行，取值范围为 1~100；
当 type=2 时，表示每几个星期执行，取值范围为 1~100；
当 type=3 时，表示每几个月中的某一天执行，取值范围为 1~100；
当 type=4 时，表示每几个月的第一周执行，取值范围为 1~100；
当 type=5 时，表示每几个月的第二周执行，取值范围为 1~100；
当 type=6 时，表示每几个月的第三周执行，取值范围为 1~100；
当 type=7 时，表示每几个月的第四周执行，取值范围为 1~100；
当 type=8 时，表示每几个月的最后一周执行，取值范围为 1~100。
freq_sub_interval：与 type 和 freq_interval 有关，表示不同 type 的执行频率，在 freq_interval 基础上，继续指定更为精准的频率，缺省为 1。具体说明如下：
当 type=1 时，这个值无效，系统不做检查；
当 type=2 时，表示某一个星期的星期几执行，可以同时选中七天中的任意几天，取值范围 1~127。具体可参考如下规则：因为每周有七天，所以 DM 数据库系统内部用七位二进制来表示选中的日子，从最低位开始算起，依次表示周日、周一到周五、周六。选中周几，就将该位置 1，否则置 0。例如，选中周二和周六，7 位二进制就是 1000100，转化成十进制就是 68，所以 FREQ_SUB_INTERVAL 取值 68；
当 type=3 时，表示将在一个月的第几天执行，取值范围 1~31；
当 type 为 4、5、6、7 或 8 时，都表示将在某一周内第几天执行，取值范围 1~7，分别表示从周一到周日。
freq_minute_interval：开始时间后，当天每隔几分钟再次执行，取值范围为 1~1439，缺省为 1439。
starttime：开始时间，缺省为 22:00。
during_start_date：有效日期时间段的开始日期时间，只有当前时间大于该参数值时，该定时器才有效，缺省为 1900/1/1。
max_run_duration：收集统计信息触发器最大执行时间，单位秒，0 表示不限制，缺省为 0。
enable：定时器的操作。0：使触发器无效；1：使触发器有效；2：删除触发器。缺省为 1。
SYSDBA.GET_AUTO_STAT_INFO_FUNC

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 /用户自定义如何使用统计信息的代码/ END; /

功能说明：
支持对自动收集统计信息的过程进行监控。
通过创建确定的过程 SYSDBA.GET_AUTO_STAT_INFO_FUNC，用以接收服务器在自动收集统计信息时的相关信息。该过程的模块体为开放式，用户可以根据自己的需求来编写如何使用统计信息的代码。
参数说明：
task_id： 任务 id，同一个任务的 task_id 相同。
total_stat： 一次任务需要收集的表的总个数。
table_id： 收集完成的一个表 table 的 id，每收集完一个表的统计信息，就会调用一次 SYSDBA.GET_AUTO_STAT_INFO_FUNC，传出一次数据，用户可自定义该过程，自定义处理接收到的数据。
sch_name： 对应 table_id 的模式名。
table_name： 对应 table_id 的表名。
curr_gath_tab_id： 当前正在收集的表 id。收集完一个表 table 后，传出数据时，该字段为接下来要收集的表 id，也是服务器当前正要或正在收集的表。
curr_gath_sch_name： 对应 curr_gath_tab_id 的模式名。
curr_gath_tab_name： 对应 curr_gath_tab_id 的表名。
success_stat： 截止到目前这次收集任务一共成功收集了多少张表。
fail_stat： 截止到目前这次收集任务一共失败收集了多少张表。
task_start_time： 这次任务的开始时间。
task_end_time： 这次任务的结束时间，未结束为 NULL。
gather_tbl_start_time： 收集完一个表 table 时，该表收集的开始时间。
gather_tbl_end_time： 收集完一个表 table 时，该表收集的结束时间，收集失败，则结束时间为 NULL。

举例说明
现对业务表TEST.TABLEA和TEST.TABLEB设置自动收集统计信息，要求该表数据变化超过15%时认定统计信息失效，自动收集的开始时间为23:30，每天执行最多一次，所有表统计信息收集过程时间总和不超过5个小时。并可以对自动收集的过程进行监控。

设置参数及属性
SP_SET_PARA_VALUE(1,‘AUTO_STAT_OBJ’,2);–仅对
SP_SET_PARA_VALUE(1,‘MONITOR_MODIFICATIONS’,2);–仅对用户执行过 SET_TABLE_PREFS 的表进行监控。

DBMS_STATS.SET_TABLE_PREFS(‘TEST’,‘TESTA’,‘MONITOR_MODIFICATIONS_FLAG’,‘TRUE’);–决定是否对表进行数据变化监控

DBMS_STATS.SET_TABLE_PREFS(‘TEST’,‘TESTB’,‘MONITOR_MODIFICATIONS_FLAG’,‘TRUE’);–决定是否对表进行数据变化监控

DBMS_STATS.SET_TABLE_PREFS(‘TEST’,‘TESTA’,‘STALE_PERCENT’,20);–设置过时百分比为20

DBMS_STATS.SET_TABLE_PREFS(‘TEST’,‘TESTA’,‘STALE_PERCENT’,20);–设置过时百分比为20
设置自动收集
SP_CREATE_AUTO_STAT_TRIGGER(
type =>1,–按天
freq_interval =>1,–每1天
freq_sub_interval =>127,–按天时无意义
freq_minute_interval =>2,–每隔多久再次执行 单位分钟
starttime =>‘23:30’,–开始时间
during_start_date =>‘2025/5/1’, --有效日期时间段的开始日期时间
max_run_duration =>18000,–收集统计信息触发器最大执行时间 单位秒
enable =>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
--//下面是用户自定义的代码，将 SYSDBA.GET_AUTO_STAT_INFO_FUNC 过程的信息插入到用户表AUTO_STAT_INFO 中
 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;


--查看自动收集过程
select * from AUTO_STAT_INFO;