DM8 增量收集分区表统计信息

方法一：
第一步，打开监控
设置 INI 参数 AUTO_STAT_OBJ 为 1 或 2。
1：对所有表进行监控；
2：只对用户通过 DBMS_STATS.SET_TABLE_PREFS 设置过 STALE_PERCENT 属性的表对象进行监控。
如果 AUTO_STAT_OBJ=2，需进一步使用 DBMS_STATS.SET_TABLE_PREFS 设置 STALE_PERCENT 属性。

用AUTO_STAT_OBJ=2打开对T表的监控。

SP_SET_PARA_VALUE(1,'AUTO_STAT_OBJ',2);

第二步，构造测试分区表和数据

create table t1_del(c1 int, c2 int)
PARTITION BY LIST("c1")
(
PARTITION  "prt1000"  VALUES('1000'),
PARTITION  "prt2000"  VALUES('2000'), 
PARTITION  "prt3000"  VALUES('3000')
);
insert into t1_del select 1000,1000 from dual connect by level <= 10000;
insert into t1_del select 2000,2000 from dual connect by level <= 10000;
insert into t1_del select 3000,3000 from dual connect by level <= 10000;
commit;

第三步，设置测试分区表静态属性

DBMS_STATS.SET_TABLE_PREFS('SYSDBA','T1_DEL','STALE_PERCENT',15);--对修改行数占总行数的比达到STALE_PERCENT要求的对象和总行数为0的对象收集统计信息
select dbms_stats.GET_PREFS('STALE_PERCENT','SYSDBA','T1_DEL');

DBMS_STATS.SET_TABLE_PREFS ('SYSDBA','T1_DEL','INCREMENTAL','TRUE');--只对存在数据变动的分区子表收集统计信息
select dbms_stats.GET_PREFS('INCREMENTAL','SYSDBA','T1_DEL');

第四步，初次收集测试分区表统计信息

BEGIN
 dbms_stats.gather_table_stats(
   ownname=>'SYSDBA', --模式名
   tabname=>'T1_DEL', --表名
   GRANULARITY => 'AUTO',--ALL、GLOBAL、PARTITION、SUBPARTITION
   cascade=>TRUE,  --是否收集索引信息，TRUE 或 FALSE。缺省为 TRUE。
   estimate_percent=>100,--采样比
   method_opt=>'FOR ALL COLUMNS SIZE AUTO',--每列收集直方图
   no_invalidate=>FALSE,--是否失效旧执行计划,false 游标失效
   degree=>3);--并行度
END;

第五步，查看测试分区表统计信息

SELECT SCH.NAME AS SCHEMA_NAME,
       TAB.NAME AS TABLE_NAME,
       ST.LAST_GATHERED
  FROM SYSSTATS ST,
       SYSOBJECTS TAB,
       SYSOBJECTS SCH
 WHERE SCH.ID = TAB.SCHID
   AND ST.ID = TAB.ID
   AND SCH.NAME = 'SYSDBA'
   AND T_FLAG='T'
   AND TAB.NAME IN ('T1_DEL_PRT1000', 
                    'T1_DEL_PRT2000', 
                    'T1_DEL_PRT3000',
                    'T1_DEL_PRT4000');--PRT4000分区不存在

第六步，设置自动收集统计信息
使用 SP_CREATE_AUTO_STAT_TRIGGER 设置触发器

SP_CREATE_AUTO_STAT_TRIGGER(1, 1, 1, 1,'16:20', '2025/9/29',0,1);--创建
SP_CREATE_AUTO_STAT_TRIGGER(1, 1, 1, 1,'16:50', '2025/6/29',0,2);--删除

第七步，对测试分区表进行增删改操作

alter table t1_del add partition "prt4000" values(4000);--新增分区

insert into t1_del select 3000,3000 from dual connect by level <= 10000;--prt3000新增数据

insert into t1_del select 4000,4000 from dual connect by level <= 10000;--新分区新增数据

delete t1_del_prt2000 where rowid in(
select rowid from t1_del_prt2000  limit 6000); --prt2000 删除部分数据
commit;

第八步，监控统计信息收集过程
首先，创建一个用户表 AUTO_STAT_INFO，用以保存自动收集过程的相关信息。

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);

其次，创建过程 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

        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 SCH.NAME AS SCHEMA_NAME,
       TAB.NAME AS TABLE_NAME,
       ST.LAST_GATHERED
  FROM SYSSTATS ST,
       SYSOBJECTS TAB,
       SYSOBJECTS SCH
 WHERE SCH.ID = TAB.SCHID
   AND ST.ID = TAB.ID
   AND SCH.NAME = 'SYSDBA'
   AND T_FLAG='T'
   AND TAB.NAME IN ('T1_DEL_PRT1000', 
                    'T1_DEL_PRT2000', 
                    'T1_DEL_PRT3000',
                    'T1_DEL_PRT4000');

T1_DEL_PRT1000 数据没有变动，所以PRT1000分区统计信息没有更新；
T1_DEL_PRT2000 删除了分区60%的数据，所以自动增量更新统计信息；
T1_DEL_PRT3000 新增了分区100%的数据，所以自动增量更新统计信息；
T1_DEL_PRT4000 新增分区，所以自动增量更新统计信息；

查看自动收集过程的相关信息

select * from AUTO_STAT_INFO;

方法二：

-- 启用表级监控（动态生效）
ALTER SYSTEM SET 'MONITOR_MODIFICATIONS' = 2 BOTH;
-- 设置监控表
DBMS_STATS.SET_TABLE_PREFS('SYSDBA', 'T1_DEL', 'MONITOR_MODIFICATIONS_FLAG', 'TRUE');
-- 设置刷新间隔（默认3600秒）
ALTER SYSTEM SET 'MON_CHECK_INTERVAL' = 5 BOTH;-- 每5秒刷新一次

--通过监控监本判断，结合dbms_stats工具 + 调度job，实现增量收集分区表统计信息。
SELECT OWNER OWNNAME,
       'PARTITION' OBJTYPE,
       TABLE_NAME OBJNAME,
       CASE 
       WHEN NVL(STATS_TOTAL_ROWS, 0) = 0 AND NVL(TOTAL_ROWS, 0) > 0 THEN '新增分区' 
       WHEN NVL(STATS_TOTAL_ROWS, 0) = 0 AND NVL(TOTAL_ROWS, 0) = 0 THEN '(不在监控范围)' 
       WHEN NVL(STATS_TOTAL_ROWS, 0) = 0 AND NVL(TOTAL_ROWS, 0) < 0 THEN '错误: 初始0行无法删除' 
       ELSE CONCAT(ROUND((NVL(TOTAL_ROWS, 0) * 100.0) / NULLIF(NVL(STATS_TOTAL_ROWS, 0) , 0), 2),'%')
       END AS CHANGE_PERCENTAGE
  FROM ( SELECT SCH.NAME AS OWNER,
                 TAB.NAME AS TABLE_NAME,
                 SM.INSERTS-SM.DELETES AS TOTAL_ROWS,
                 ST.T_TOTAL AS STATS_TOTAL_ROWS
            FROM SYSOBJECTS TAB
       LEFT JOIN SYSSTATS ST 
              ON ST.ID = TAB.ID
       LEFT JOIN SYSOBJECTS SCH 
              ON SCH.ID = TAB.SCHID
       LEFT JOIN SYSMODIFICATIONS SM 
              ON SM.ID=TAB.ID
           WHERE SCH.NAME = 'SYSDBA' AND TAB.SUBTYPE$='UTAB' 
           AND TAB.PID>0 AND (T_FLAG='T' OR T_FLAG IS NULL) )

DM8 增量收集分区表统计信息

作者