生产背景：某些情况下，业务表在某一时段会删除部分数据行，导致相关表的执行计划产生偏移。需要通过统计信息收集才能避免该问题发生。

1. 创建任务表
   首先需要创建一个任务表来存储需要收集统计信息的对象信息。

--  创建任务表
CREATE TABLE stats_collection_tasks (
    owner          VARCHAR2(30) NOT NULL,       -- 对象属主
    table_name     VARCHAR2(30) NOT NULL,       -- 表名
    collect_time   VARCHAR2(5)  NOT NULL,       -- 收集时间(HH24:MI格式)
    job_name       VARCHAR2(100),               -- JOB名称
    status         VARCHAR2(20) DEFAULT 'PENDING', -- 收集状态(PENDING/RUNNING/COMPLETED/FAILED)
    job_created    CHAR(1) DEFAULT 'Y' CHECK (job_created IN ('Y','N')), -- 作业是否已创建
    last_collected TIMESTAMP,                   -- 上次收集时间
    enabled        CHAR(1) DEFAULT 'Y' CHECK (enabled IN ('Y','N')) -- 是否启用
);

-- 创建约束和索引提高查询效率
CREATE INDEX idx_stats_tasks_time ON stats_collection_tasks(collect_time, enabled) tablespace TS_001;
CREATE INDEX idx_stats_owner_tname ON stats_collection_tasks(owner, table_name) tablespace TS_001;

ALTER TABLE "SYSDBA"."stats_collection_tasks" add constraint "uni_key"  unique("owner","table_name") using index tablespace "TS_001";

2. 创建收集统计信息的存储过程

创建任务表，包含属主，对象表，统计信息收集时间，JOB名，收集完成状态，job是否已创建。
每日查询任务表里的对象，统计信息采集时间，创建相对应的job。
job名包含（属主_表名_收集日期）
job开始时，删除昨天的收集job
每个job执行一次，工作内容是收集表的统计信息
根据任务表里的统计信息收集时间开启job

--  创建删除旧作业的存储过程
CREATE OR REPLACE PROCEDURE cleanup_old_jobs AS
BEGIN
    -- 删除昨天创建的且已完成的作业
    FOR job_rec IN (
        SELECT job_name 
        FROM stats_collection_tasks 
        WHERE job_created = 'Y' 
        OR (status = 'COMPLETED'
        AND TRUNC(last_collected) = TRUNC(SYSDATE)-1)
    ) LOOP
        BEGIN
            SP_DROP_JOB(job_rec.job_name);
            UPDATE stats_collection_tasks 
            SET job_created = 'N' ,
            status ='PENDING'
            WHERE job_name = job_rec.job_name;
        EXCEPTION
            WHEN OTHERS THEN
                DBMS_OUTPUT.PUT_LINE('删除作业失败: ' || job_rec.job_name || ' - ' || SQLERRM);
        END;
    END LOOP;
    COMMIT;
END cleanup_old_jobs;
/


-- 创建主存储过程
CREATE OR REPLACE PROCEDURE create_daily_stats_jobs AS
    v_job_name VARCHAR2(100);
    v_start_time TIMESTAMP;
BEGIN
    -- 先清理旧作业
    cleanup_old_jobs;

    -- 为每个任务创建新作业
    FOR task_rec IN (
        SELECT task_id, owner, table_name, collect_time 
        FROM stats_collection_tasks 
        WHERE enabled = 'Y'
        AND job_created ='N'
        AND (status IS NULL OR status IN ('PENDING', 'FAILED'))
    ) LOOP
        -- 生成作业名:属主_表名_收集日期
        v_job_name := 'STATS_' || task_rec.owner || '_' || 
                     task_rec.table_name || '_' || 
                     TO_CHAR(SYSDATE, 'YYYYMMDD');
        
        -- 计算今天的收集时间
        v_start_time := TO_TIMESTAMP(
            TO_CHAR(SYSDATE, 'YYYY-MM-DD') || ' ' || task_rec.collect_time,
            'YYYY-MM-DD HH24:MI'
        );
        
        -- 如果今天的时间已过，则设置为明天同一时间
        IF v_start_time < SYSTIMESTAMP THEN
            v_start_time := v_start_time + INTERVAL '1' DAY;
            v_job_name := 'STATS_' || task_rec.owner || '_' || 
                     task_rec.table_name || '_' || 
                     TO_CHAR(SYSDATE+1, 'YYYYMMDD');
        END IF;
        
        
        -- 创建一次性作业
        SP_CREATE_JOB(v_job_name,1,0,'',0,0,'',0,'');
        update stats_collection_tasks 
            set job_created='Y',
            job_name = v_job_name
            where owner = task_rec.owner
            and table_name = task_rec.table_name;
        SP_JOB_CONFIG_START(v_job_name);
        SP_ADD_JOB_STEP(v_job_name, '一次性执行', 0, 
                      'BEGIN ' ||
                      'UPDATE stats_collection_tasks ' ||
                      'SET job_name = ''' || v_job_name || ''',' ||
                      'status = ''RUNNING'' ' ||
                      'WHERE owner =  ''' || task_rec.owner || ''' ' ||
                      'AND table_name = ''' || task_rec.table_name|| ''' ;' ||
                      'BEGIN ' ||
                      'DBMS_STATS.GATHER_TABLE_STATS(' ||
                      'ownname => ''' || task_rec.owner || ''',' ||
                      'tabname => ''' || task_rec.table_name || ''',' ||
                      'estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,' ||
                      'degree => DBMS_STATS.AUTO_DEGREE,' ||
                      'cascade => TRUE); ' ||
                      'UPDATE stats_collection_tasks ' ||
                      'SET status = ''COMPLETED'', ' ||
                      'last_collected = SYSTIMESTAMP ' ||
                      'WHERE owner =  ''' || task_rec.owner || ''' ' ||
                      'AND table_name = ''' || task_rec.table_name|| ''' ;' ||
                      'EXCEPTION WHEN OTHERS THEN ' ||
                      'UPDATE stats_collection_tasks ' ||
                      'SET status = ''FAILED'' ' ||
                      'WHERE owner =  ''' || task_rec.owner || ''' ' ||
                      'AND table_name = ''' || task_rec.table_name|| ''' ;' ||
                      'END; ' ||
                      'COMMIT;' ||
                      'END;', 
            0, 0, 0, 0, NULL, 0);
        SP_ADD_JOB_SCHEDULE(v_job_name, '一次性执行', 1, 0, 0, 0, 0, NULL, NULL,v_start_time, NULL, '');
        SP_JOB_CONFIG_COMMIT(v_job_name);

    END LOOP;
    COMMIT;
END create_daily_stats_jobs;
/

3. 创建主调度作业
   每日午夜调度一次，JOB执行create_daily_stats_jobs。创建今天的相关表的统计信息收集任务。

4.使用说明
向stats_collection_tasks表中插入需要收集统计信息的对象信息，例如：
INSERT INTO stats_collection_tasks (owner, table_name, collect_time)
VALUES (‘SYSDBA’, ‘T1_DEL’, ‘17:00’);