Oracle 11g 自动收集统计信息导致 IO 过高优化方案

Oracle 11g 自动收集统计信息导致 IO 过高优化方案

Oracle 11g 默认开启了自动统计信息收集任务（Auto Stats Collection），这虽然能保持优化器数据的准确性，但在业务高峰期往往会造成显著的 I/O 压力。如果监控发现数据库在特定时段出现 IO 飙升，我们通常可以根据实际情况调整任务的执行时间、持续时间，或者直接暂停相关窗口。

调整维护窗口配置

我们可以通过 DBMS_SCHEDULER 包来修改系统维护窗口的行为。以下操作演示了如何禁用特定窗口、调整执行时长以及设定新的开始时间。

1. 停止当前任务

首先，我们需要暂时禁用默认的维护窗口，防止它在原定时间触发。

BEGIN
    DBMS_SCHEDULER.DISABLE(
        name => '"SYS"."FRIDAY_WINDOW"',
        force => TRUE);
END;
/

2. 修改任务持续时间

为了避免长时间占用资源，建议限制统计收集的最大运行时间。这里设置为 240 分钟（单位是分钟）。

BEGIN
    DBMS_SCHEDULER.SET_ATTRIBUTE(
        name => '"SYS"."FRIDAY_WINDOW"',
        attribute => 'DURATION',
        value => numtodsinterval(240, 'minute'));
END;
/

3. 调整任务开始时间

将任务迁移到业务低峰期，例如周一凌晨 1 点。注意这里的 REPEAT_INTERVAL 格式。

BEGIN
    DBMS_SCHEDULER.SET_ATTRIBUTE(
        name => '"SYS"."FRIDAY_WINDOW"',
        attribute => 'REPEAT_INTERVAL',
        value => 'FREQ=WEEKLY;BYDAY=MON;BYHOUR=1;BYMINUTE=0;BYSECOND=0');
END;
/

4. 重新启用任务

配置完成后，记得重新启用窗口以生效。

BEGIN
    DBMS_SCHEDULER.ENABLE(
        name => '"SYS"."FRIDAY_WINDOW"');
END;
/

验证与监控

修改后，建议查询视图确认窗口状态是否已更新，并检查历史执行情况以确保任务按预期运行。

查看窗口配置详情：

SELECT t1.window_name,
       t1.repeat_interval,
       t1.duration
FROM   dba_scheduler_windows t1,
       dba_scheduler_wingroup_members t2
WHERE  t1.window_name = t2.window_name
AND    t2.window_group_name IN ('MAINTENANCE_WINDOW_GROUP', 'BSLN_MAINTAIN_STATS_SCHED');

查看自动任务客户端的历史执行记录：

SELECT client_name,
       window_name,
       jobs_created,
       jobs_started,
       jobs_completed
FROM   dba_autotask_client_history
WHERE  client_name LIKE '%stats%'
ORDER BY window_start_time;

注意事项

• 权限要求：执行上述操作通常需要 SYSDBA 或拥有 ADMINISTER DATABASE TRIGGER 权限的用户。
• 业务影响：调整统计信息收集策略可能会影响 SQL 执行计划的稳定性，建议在测试环境验证后再应用到生产库。
• 窗口名称：不同版本的 Oracle 或不同的安装配置下，维护窗口的名称可能略有差异，请根据实际 dba_scheduler_windows 中的名称进行调整。

通过合理调度这些后台任务，我们可以在保证数据准确性的同时，显著降低对在线业务的 I/O 干扰。