自动收集统计信息

一、 背景

随着数据不断增删改，数据分布会发生变化，自动统计信息收集会随着数据的变化而动态调整，使得优化器在处理新数据时仍然能够选择最合适的索引、连接顺序和其他执行策略。
当数据量较大或存在许多大表时，收集统计信息需要较长的时间，对业务时间和资源占用造成较大负担，自动收集可以根据预设的维护窗口，在业务低峰时段进行，以降低对实时业务的影响，并合理利用服务器空闲资源。

二、 数据库版本

版本号 1-3-62-2023.11.09-208042-20067-SEC

三、 自动收集统计信息的使用

用户可以通过设置统计信息的收集范围和策略，如：设置触发自动收集的阈值、自动收集最长持续时间等属性，根据不同需求，对自动收集统计信息进行配置。
以下是自动收集统计信息的使用说明：

3.1开启自动收集统计信息

自动收集统计信息功能通过设置AUTO_STAT_OB参数开启。INI参数AUTO_STAT_OBJ是用来监控用户表的增删改行数，参数默认值为0，表示不进行监控；设为1，则监控所有用户表对象；设为2，则监控用户配置过的目标对象。示例如下：
--开启参数
call sp_set_para_value(1,'AUTO_STAT_OBJ',2);
--验证参数
select * from v$dm_ini where para_name='AUTO_STAT_OBJ';

3.2设置自动收集的时间和频率

为便于用户规划自动收集统计信息的工作时间，DM支持用户自行设置自动收集统计信息的工作时间、间隔周期和最长运行时间等信息。当到达设置的工作时间，并满足了收集条件，如：数据变化率已达到预设阈值，自动收集统计信息就会开始工作。这些时间信息通过设置定时器实现。
调用系统函数SP_CREATE_AUTO_STAT_TRIGGER进行自动收集统计信息的触发器创建、调度频率设置、启用\禁用触发器等操作。语法如下：

以下对参数进行说明：
type：指定调度类型。可取值 1、2、3、4、5、6、7、8，缺省为 1。常用参数介绍：
1：按天的频率来执行；
2：按周的频率来执行；
3：在一个月的某一天执行；
freq_interval：与 type 有关，表示不同调度类型下的发生频率，默认为 1。说明如下：
当 type=1 时，表示每几天执行，取值范围为 1~100；
当 type=2 时，表示每几个星期执行，取值范围为 1~100；
当 type=3 时，表示每几个月中的某一天执行，取值范围为 1~100；
freq_sub_interval：与 type 和 freq_interval 有关，表示不同 type 的执行频率，在freq_interval 基础上，继续指定更为精准的频率，缺省为 1。说明如下：
当 type=1 时，这个值无效，系统不做检查；
当 type=2 时，表示某一个星期的星期几执行，可以同时选中七天中的任意几天，取值范围 1~127。DM 数据库系统内部用七位二进制来表示选中的日子，从最低位开始算起，依次表示周日、周一到周五、周六。选中周几，就将该位置 1，否则置 0；
当 type=3 时，表示将在一个月的第几天执行，取值范围 1~31；

freq_minute_interval：开始时间后，当天每隔几分钟再次执行，取值范围为1~1439，缺省为 1439；

starttime：开始时间，缺省为 22:00；

during_start_date：有效日期时间段的开始日期时间，缺省为 1900/1/1；

max_run_duration：收集统计信息触发器最大执行时间，单位秒，0 表示不限制， 缺省为 0；

enable：定时器是否有效，0：无效，1：有效，缺省为 1。

示例：call SP_CREATE_AUTO_STAT_TRIGGER(1,1,0,2,'00:01','2023/12/15',0,1);
此参数组合表示：此定时器有效，从2023-12-15开始，每天0点01分开始执行，每2分钟执行一次，不限制收集时间。

3.3设置收集范围和策略

用户可以定义自动收集统计信息的收集范围和收集策略，例如：设置进行自动收集的数据变化行数阈值、收集的采样率、收集的并行度等。使用DBMS_STATS包SET_TABLE_PREFS方法，可以设置监控对象统计信息的相关属性。语法如下：
PROCEDURE SET_TABLE_PREFS (
OWNNAME VARCHAR(128), 模式名，不区分大小写
TABNAME VARCHAR(128), 表名，不区分大小写
PNAME VARCHAR(128), 属性名，不区分大小写
PVALUE VARCHAR(128) 属性名对应的属性值，不区分大小写
);
其中，PPNAME是属性名，不区分大小写。可以取 STALE_PERCENT、ESTIMATE_PERCENT、
METHOD_OPT、DEGREE、GRANULARITY 、CASCADE、NO_INVALIDATE、PREFERENCE_OVERRIDES_PARAMETER、MONITOR_MODIFICATIONS_FLAG 和
MONITOR_ MODIFICATIONS_RM_PLN_FLAG。
以下对参数进行说明：
STALE_PERCENT：决定表的统计信息过时且需要重收集统计信息时表的行数百分比。缺省值是 10，即一张表有超过 10%的数据改变则可认为统计信息过时，进行收集。
ESTIMATE_PERCENT：收集的百分比，范围为 0.000001~100，默认系统自定。 METHOD_OPT：控制列的统计信息集合和直方图的创建的格式，缺省为 FOR ALL COLUMNS SIZE AUTO。此参数可以配置对特定列进行收集统计信息，如：FOR COLUMNS NAME，则只对特定表的NAME列进行收集统计信息。
DEGREE：收集的并行度，缺省为 1。
PREFERENCE_OVERRIDES_PARAMETER：决定是否用静态属性值覆盖参数的输入值。为 TRUE时，忽略参数的输入值，使用对应的属性值；为 FALSE 时，使用参数的输入值。
MONITOR_MODIFICATIONS_FLAG：决定是否对表进行数据变化监控。为 TRUE 时，监控表的数据变化；为 FALSE 时，不监控表的数据变化。缺省值为 FALSE。
MONITOR_ MODIFICATIONS_RM_PLN_FLAG：决定对表进行数据变化监控时，当数据变化达到该阈值时，会移除该表相关的缓存计划。
示例：设定收集范围为TEST模式下的表T1，数据变化率超过15%收集统计信息。
call dbms_stats.SET_TABLE_PREFS('TEST','T1','STALE_PERCENT',15);
其中使用了STALE_PERCENT参数，设定收集阈值为15%。

四、 查看统计信息收集情况

自动收集统计信息完成后，用户可以通过系统表 SYSSTATTABLEIDU 和动态视图 V$AUTO_STAT_TABLE_IDU 查看监控信息，通过系统表 SYSSTATS 查看统计信息。以下是对这些表的说明：

用户可以通过查询SYSSTATPREFS表获取自动收集已配置的静态参数情况：
SELECT SF_GET_TABLENAME_BY_ID(ID) as Table_name,* FROM SYSSTATPREFS;

查询结果包括在收集范围内的表、已配置的静态参数、修改时间等信息。

查看监控信息：
SELECT SF_GET_TABLENAME_BY_ID(ID) as Table_name,* FROM SYSSTATTABLEIDU;

用户可查询到收集范围内的表上一次收集统计信息时的总行数和增删改的影响行数（只有系统重启后，才会更新）。

查看统计信息：
SELECT SF_GET_TABLENAME_BY_ID(ID) as Table_name,* FROM SYSSTATS;

SYSSTATS记录了系统中的统计信息，可以获取到表的总行数、采样数等信息。用户可以查询此表获取统计信息收集情况。

五、 自动收集统计信息示例

5.1示例

以下示例为完整自动收集统计信息过程：
1、 开启自动统计信息功能，设置为对特定表自动收集统计信息。
call sp_set_para_value(1,'AUTO_STAT_OBJ',2);

2、 准备测试数据：创建TEST用户，并在该模式下创建TAB1-TAB200共计200张表。
CREATE USER "TEST" IDENTIFIED BY "TEST#1234" DEFAULT TABLESPACE "TEST";
GRANT RESOURCE,SOI,VTI,PUBLIC TO TEST;
begin
for rs in(
select 'drop table if exists TEST.TAB'||level||';' as sql1,'create table TEST.TAB'||level||'(a int,b datetime default sysdate);'as sql2 from dual connect by level<=200)
loop
execute immediate rs.sql1;
execute immediate rs.sql2;
end loop;end;

3、 设置自动收集统计信息的范围和策略。此次收集范围为TEST模式下的表TAB1-TAB200，当数据变化率超过15%时进行自动收集统计信息。
begin
for rs in (
select 'call dbms_stats.SET_TABLE_PREFS(''TEST'',''TAB'||level||''',''STALE_PERCENT'',15);'as sql1 from dual connect by level<=200)
loop
execute immediate rs.sql1;
end loop;end;

4、 设置自动收集统计信息的定时器。本次设置为从2024-02-20开始，每天0点01分开始执行，每2分钟执行一次，不限制收集时间。
call SP_CREATE_AUTO_STAT_TRIGGER(1,1,0,2,'00:01','2023/11/03',0,1);

5、 对TEST模式下的表TAB1-TAB200插入20001行数据，使其数据变化率超过15%，触发自动收集统计信息。
begin
for rs in(
select 'insert into TEST.TAB'||level||' select level as a,SYSDATE()as b from dual CONNECT by level<= 20001; commit;' as sql1 from dual connect by level<=200)
loop
execute immediate rs.sql1;
end loop;end;

6、 查看统计信息收集情况。
SELECT SF_GET_TABLENAME_BY_ID(ID) as Table_name,* FROM SYSSTATS;

可查询到设置在收集范围内的表的统计信息，插入的数据触发了自动收集统计信息功能，统计信息已成功更新。用户可以根据需要，自行定义收集策略和收集时间，以应对不同的业务场景。

5.2设置采样率示例

自动收集统计信息功能中，系统默认会按照目标表的数据量，对其采样率进行调整，用户也可以根据实际需求，自行设置统计信息采样率。ESTIMATE_PERCENT属性为收集的百分比，范围为 0.000001~100，默认系统自定，指定自动收集统计信息采样率可以通过设置ESTIMATE_PERCENT参数完成。
以下为ESTIMATE_PERCENT参数调整示例：
现有TEST模式下TAB1表，已在自动收集统计信息范围内，收集策略为：当数据变化率超过15%时进行自动收集统计信息，其他参数保持默认。清空该表后插入100000行数据，查看统计信息情况：
表结构：TEST.TAB1(a int,b datetime default sysdate);
插入100000行数据：
begin
for rs in(
select 'insert into TEST.TAB'||level||' select level as a,SYSDATE()as b from dual CONNECT by level<= 100000; commit;' as sql1 from dual connect by level<=1)
loop
execute immediate rs.sql1;
end loop;
end;
查看统计信息情况：
SELECT SF_GET_TABLENAME_BY_ID(ID) as Table_name,* FROM SYSSTATS;

TAB1表100000行数据量情况下，N_SAMPLE的值约为T_TOTAL的一半，自动收集统计信息默认的采样率约为50%。

对收集TAB1表的收集策略进行调整，通过ESTIMATE_PERCENT参数将其采样率调整为100%。
call dbms_stats.SET_TABLE_PREFS('TEST','TAB1','ESTIMATE_PERCENT',100);
清空TAB1表，再次插入100000行数据，查看统计信息情况。
SELECT SF_GET_TABLENAME_BY_ID(ID) as Table_name,* FROM SYSSTATS;

TAB1表此时的采样率已为100%。

5.3设置并行度示例

当需要对大表进行统计信息收集时，往往需要消耗较长的时间，当机器资源允许的情况下，可以设置自动收集统计信息的并行度，缩短收集的时间，提高效率。
并行度可以通过设置收集策略中的DEGREE 参数进行调整，默认值为1。以下是示例：
现有TEST模式下T1表，已在自动收集统计信息范围内，收集策略为：当数据变化率超过15%时进行自动收集统计信息，其他参数保持默认。清空该表后插入1亿行数据，查看统计信息情况：
表结构：TEST.T1(a int,b datetime default sysdate);
begin
for rs in(
select 'insert into TEST.T'||level||' select level as a,SYSDATE()as b from dual CONNECT by level<= 100000000; commit;' as sql1 from dual connect by level<=1)
loop
execute immediate rs.sql1;
end loop;
end;
查看统计信息情况：
SELECT SF_GET_TABLENAME_BY_ID(ID) as Table_name, ID,COLID,T_FLAG,T_TOTAL,N_SAMPLE,N_DISTINCT,N_NULL,LAST_GATHERED FROM SYSSTATS;

从统计信息中的LAST_GATHERED列可得到本次收集统计信息耗时约为5分钟。

调整并行度DEGREE为8，再次对此表进行收集统计信息，查看统计信息情况。
call dbms_stats.SET_TABLE_PREFS('TEST','T1','DEGREE',8);
SELECT SF_GET_TABLENAME_BY_ID(ID) as Table_name, ID,COLID,T_FLAG,T_TOTAL,N_SAMPLE,N_DISTINCT,N_NULL,LAST_GATHERED FROM SYSSTATS;

提高并行度后，1亿数据量的普通表收集统计信息耗时约为3分钟，提高了收集速度。

5.4对特定列进行收集示例

自动收集统计信息功能支持对表的特定列进行收集统计信息，当仅需要对表的特定列进行收集时，通过设置参数METHOD_OPT进行实现。示例如下：
现有TEST模式下TAB1表，表结构：TEST.TAB1(a int,b datetime default sysdate)，已在自动收集统计信息范围内。收集策略为：当数据变化率超过15%时进行自动收集统计信息，采样率100%，其他参数保持默认。
清空该表后插入10000行数据，查看统计信息情况。
SELECT SF_GET_TABLENAME_BY_ID(ID) as Table_name, ID,COLID,T_FLAG,T_TOTAL,N_SAMPLE,N_DISTINCT,N_NULL,LAST_GATHERED FROM SYSSTATS;

TAB1表的两列都收集了统计信息。

设置参数METHOD_OPT，只对列A收集统计信息。
call dbms_stats.SET_TABLE_PREFS('TEST','TAB1','METHOD_OPT','FOR COLUMNS A');
在表TAB1已有10000行数据情况下，再次插入10000行数据，触发自动收集统计信息，查看统计信息情况。
SELECT SF_GET_TABLENAME_BY_ID(ID) as Table_name, ID,COLID,T_FLAG,T_TOTAL,N_SAMPLE,N_DISTINCT,N_NULL,LAST_GATHERED FROM SYSSTATS;

此时统计信息中只更新了TAB1中的A列的统计信息，另一列统计信息保持不变，说明只对特定列进行了统计信息的收集。

六、 查看收集进度信息

用户如果需要查看自动收集统计信息的进度，或是收集的相关信息，可以通过自定义存储过程SYSDBA.GET_AUTO_STAT_INFO_FUNC实现。自动收集统计信息时，利用该过程将收集过程的信息实时的传出，达到查看收集统计信息进度的功能。
首先，创建一个用户表 AUTO_STAT_INFO，用以保存自动收集过程的相关信息。
create table AUTO_STAT_INFO(
task_id INT, 任务id，同一个任务的task_id相同
total_stat INT, 一次任务需要收集的表的总个数
table_id INT, 收集完成的一个表table的id，每收集完一个
表的统计信息，就会调用一次
sch_name varchar(24), 统计的模式名
table_name varchar(24), 统计的表名
curr_gath_tab_id INT, 当前正在收集的表id。收集完一个表table后，传出数据时，该字段为接下来要收集的表id，也是服务器当前正要或正在收集的表
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, 这次任务的结束时间，未结束为NULL
gather_tbl_start_time DATETIME, 收集完一个表table时，该表收集的开始时间
gather_tbl_end_time DATETIME 收集完一个表table时，该表收集的结束时间，收集失败，则结束时间为NULL
);

创建过程 SYSDBA.GET_AUTO_STAT_INFO_FUNC，接收服务器在自动收集统计信息时的过程信息。并在模块体编写用户代码，可以将过程收集的统计信息写入表AUTO_STAT_INFO 中。
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;

上述GET_AUTO_STAT_INFO_FUNC存储过程可以将收集统计信息时的信息写入到表AUTO_STAT_INFO中，用户可以查看AUTO_STAT_INFO表以获取收集进度信息。
现有表T1-T200，表结构相同，TEST.T1(a int,b datetime default sysdate)，都在自动收集统计信息的范围内，收集策略为：当数据变化率超过15%时进行自动收集统计信息，其他参数保持默认。
以下是对T1-T200的表分别插入10000行，触发自动收集统计信息，查询表AUTO_STAT_INFO得到的相关信息：

可以得到对每个表的收集任务的开始时间、结束时间、已成功收集表数量、已失败收集表数量等信息。用户可以根据这些信息对统计信息的收集进度作出大致的评估。用户也可以自行定义GET_AUTO_STAT_INFO_FUNC存储过程的代码，将需要的信息进行处理，以应对不同的业务场景。

七、 总结

自动收集统计信息可以在空闲时间段进行工作，不占用正常的业务时间和机器资源，能够帮助用户更有效率地完成业务。自动收集统计信息功能较为完备，可以自行设定定时器、收集策略、对象、并行度等参数，能够满足不同的需求。
当然，目前也仍存在可以改进的地方，较为直观的有：缺少可以直接获取自动收集统计信息进度的功能，对收集的进度和具体成功、失败的任务的相关信息无法获取。如果需要查看具体的表是否完成收集或是任务进度，只能通过自行定义存储过程以查看相关信息，但结果不够理想。如果能够提供直观的进度显示和成功收集统计信息的表的相关信息，将对用户带来更大的便利。