自动收集统计信息实现方式有多种，我们以下的脚本主要依靠DBMS_STATS.GATHER_TABLE_STATS这个系统包函数来实现，这个是针对每张表来做统计信息的更新，那么就要考虑到新增的表的统计信息的收集，就是需要把系统里面的表都写入一张表，然后每天定时读取这些表，采用DBMS_STATS.GATHER_TABLE_STATS来更新统计信息，参考脚本如下：
--SYSDBA用户创建

--DROP TABLE MEDDLE_TABLES;
CREATE TABLE MEDDLE_TABLES(
TID INT IDENTITY(1,1),
SCH_NAME VARCHAR(100),
TABLE_NAME VARCHAR(100),
IS_STATS INT
);

--用于每天加入新增表
CREATE OR REPLACE PROCEDURE GET_MEDDLE_TABLE()
AS
CNT INT;
BEGIN
--获取新增表写入到记录表
INSERT INTO MEDDLE_TABLES(SCH_NAME,TABLE_NAME,IS_STATS) SELECT OWNER,TABLE_NAME,0 FROM ALL_TABLES WHERE OWNER IN ('MEDDLE_LCBA','MEDDLE_DZJZZZ') AND TABLE_NAME NOT LIKE 'BAK%'
AND (OWNER,TABLE_NAME) NOT IN (SELECT SCH_NAME,TABLE_NAME FROM MEDDLE_TABLES);
--如果全部都更新过了，就将更新标记重新调整为0
SELECT COUNT(*) INTO CNT FROM MEDDLE_TABLES WHERE IS_STATS=0;
IF CNT = 0 THEN
UPDATE MEDDLE_TABLES SET IS_STATS = 0;
END IF;
END;

--CALL GET_MEDDLE_TABLE();

--SELECT * FROM MEDDLE_TABLES;

--用于执行统计信息更新
CREATE OR REPLACE PROCEDURE STAT_MEDDLE_TABLE()
AS
V_SCH_NAME VARCHAR(100);
V_TABLE_NAME VARCHAR(100);
BEGIN
--获取最新一个未更新的表进行更新
SELECT TOP 1 SCH_NAME,TABLE_NAME INTO V_SCH_NAME,V_TABLE_NAME FROM MEDDLE_TABLES WHERE IS_STATS=0;
UPDATE MEDDLE_TABLES SET IS_STATS = -1 WHERE SCH_NAME=V_SCH_NAME AND TABLE_NAME=V_TABLE_NAME;
commit;
DBMS_STATS.GATHER_TABLE_STATS(V_SCH_NAME,V_TABLE_NAME,null,100,FALSE,'FOR ALL COLUMNS SIZE AUTO');
--统计信息更新完成后更新记录表中状态
UPDATE MEDDLE_TABLES SET IS_STATS = 1 WHERE SCH_NAME=V_SCH_NAME AND TABLE_NAME=V_TABLE_NAME;
commit;
END;

--CALL STAT_MEDDLE_TABLE();

--创建定时作业，每天凌晨0点10份调度一次，记录新增的表和调整整体更新状态
call SP_CREATE_JOB('GET_MEDDLE_TABLE',1,0,'',0,0,'',0,'每天收集一次新增的表');
call SP_JOB_CONFIG_START('GET_MEDDLE_TABLE');
call SP_ADD_JOB_STEP('GET_MEDDLE_TABLE', 'GET_MEDDLE_TABLE', 0, 'CALL GET_MEDDLE_TABLE();', 1, 2, 0, 0, NULL, 0);
call SP_ADD_JOB_SCHEDULE('GET_MEDDLE_TABLE', 'GET_MEDDLE_TABLE', 1, 1, 1, 0, 0, '23:10:10', NULL, '2020-04-09 10:35:24', NULL, '');
call SP_JOB_CONFIG_COMMIT('GET_MEDDLE_TABLE');

--每天24点10分到早上7点10分，每隔5分钟执行一次调度，每次调度更新一个表的统计信息
call SP_CREATE_JOB('STAT_MEDDLE_TABLE',1,0,'',0,0,'',0,'每天凌晨到到早上进行表统计信息更新');
call SP_JOB_CONFIG_START('STAT_MEDDLE_TABLE');
call SP_ADD_JOB_STEP('STAT_MEDDLE_TABLE', 'STAT_MEDDLE_TABLE1', 0, 'CALL STAT_MEDDLE_TABLE();', 1, 2, 0, 0, NULL, 0);
call SP_ADD_JOB_STEP('STAT_MEDDLE_TABLE', 'STAT_MEDDLE_TABLE2', 0, 'CALL STAT_MEDDLE_TABLE();', 1, 2, 0, 0, NULL, 0);
call SP_ADD_JOB_STEP('STAT_MEDDLE_TABLE', 'STAT_MEDDLE_TABLE3', 0, 'CALL STAT_MEDDLE_TABLE();', 1, 2, 0, 0, NULL, 0);
call SP_ADD_JOB_STEP('STAT_MEDDLE_TABLE', 'STAT_MEDDLE_TABLE4', 0, 'CALL STAT_MEDDLE_TABLE();', 1, 2, 0, 0, NULL, 0);
call SP_ADD_JOB_STEP('STAT_MEDDLE_TABLE', 'STAT_MEDDLE_TABLE5', 0, 'CALL STAT_MEDDLE_TABLE();', 1, 2, 0, 0, NULL, 0);
call SP_ADD_JOB_SCHEDULE('STAT_MEDDLE_TABLE', 'STAT_MEDDLE_TABLE', 1, 1, 1, 0, 5, '00:10:10', '07:10:10', '2020-04-09 10:40:55', NULL, '');
call SP_JOB_CONFIG_COMMIT('STAT_MEDDLE_TABLE');

--添加自动收集统计信息的任务（每天1点收集全库统计信息）
call SP_CREATE_JOB('statistics',1,0,'',0,0,'',0,'');
call SP_JOB_CONFIG_START('statistics');
call SP_ADD_JOB_STEP('statistics', 'statistics1', 0, 'begin
for rs in (select ''sf_set_SESSION_para_value(''''HAGR_HASH_SIZE'''',(select cast(
case when max(table_rowcount(owner,table_name))<=(select max_value from v$dm_ini
where para_Name=''''HAGR_HASH_SIZE'''') and max(table_rowcount(owner,table_name))>=(
select min_value from v$dm_ini where para_Name=''''HAGR_HASH_SIZE'''') then
max(table_rowcount(owner,table_name)) when max(table_rowcount(owner,table_name))<(
select min_value from v$dm_ini where para_Name=''''HAGR_HASH_SIZE'''') then
(select min_value from v$dm_ini where para_Name=''''HAGR_HASH_SIZE'''') else
(select max_value from v$dm_ini where para_Name=''''HAGR_HASH_SIZE'''') end as bigint)
from dba_tables where owner=''''''||NAME||''''''));''
sql1,''DBMS_STATS.GATHER_SCHEMA_STATS(''''''||NAME||'''''',100,TRUE,''''FOR ALL COLUMNS SIZE AUTO'''');''
sql2
from SYS.SYSOBJECTS where TYPE$=''SCH'' ) loop
execute immediate rs.sql1;
execute immediate rs.sql2;
end loop;
end;', 0, 0, 0, 0, NULL, 0);
call SP_ADD_JOB_SCHEDULE('statistics', 'statistics1', 1, 2, 1, 64, 0, '01:00:00', NULL, '2021-06-09 22:54:37', NULL, '');
call SP_JOB_CONFIG_COMMIT('statistics');

是这种吗，看下是否满足所需呢