你的Oracle是不是这个时间发生的故障？

时间：2020-12-27 00:41:42

背景

在我的运维Oracle工作中，经常有客户遇到这个问题，而且一直都有，也可以说基本在每个客户那里都遇到过。所以希望这篇文章可以帮助到一些朋友。

描述

如果你的系统发生问题了，请注意这两个特殊时间：

  1. 周一至周五工作日每天晚上22:00之后持续4小时。
  2. 周六日从凌晨06:00一直持续20个小时。

有经验的朋友看到这里就知道我想说什么了，是的我是想说Oracle auto task带来的问题。虽然是老生常谈，不过刚刚又遇到了这个问题，就写出来给大家提个醒！

分析

Oracle auto task 包含三个作业：

  • 「auto optimizer stats collection：」(自动优化器统计信息搜集)

一条SQL要在数据库中执行，必须先有执行计划，执行计划的准确与否取决于统计信息的准确度。经常遇到的性能问题就是执行计划不对。Oracle为了保证表及相关对象统计信息的准确性，安排了这个作业，在固定的「窗口」定期执行。当一个表的数据变化量超过10%便会被记录，在自动采集统计信息时被「自动」收集。

会带来的问题：新收集的统计信息会导致执行计划改变，经常遇到第二天刚上班，系统变得特别慢，分析后就是SQL执行计划改变，最好的解决做法是绑定历史执行计划。

  • 「sql tuning advisor：」

Oracle每天会将占数据库资源较多的一些SQL放在一个优化集中，在固定的「窗口」中进行分析，「自动」对其生成优化指导，但是并不做任何实际的变更。不过生成的指导是可以被使用的，需要人工确认并实施。

会带来的问题：因为是每天执行的作业，所以每天都可能再分析相同的SQL，导致资源浪费，更可能占用大量的I/O资源，影响正常的业务SQL。

  • 「auto space advisor ：」

如果一个表被使用的久了就会有碎片，碎片就是一个表在物理磁盘的存储中会有很多空洞、行链接等，碎片越多就会导致数据库性能下降的越厉害，所以Oracle设置了再固定「窗口」内「自动」分析碎片的任务，让我们可以快速的定位哪些碎片大的对象可以被回收。仅生成指导，不实际变更。

会带来的问题：通常如果遇到被分析的对象特别大，分析过程中会通过创建临时表的形式对比可节省空间，将会带来TEMP表空间使用率100%报警，也会生成大量的REDO导致归档空间报警，也会带来大量的I/O资源使用，影响正常业务。

任务关键字：dbms_space.auto_space_advisor_job_proc

「综上所述」

  • auto optimizer stats collection：自动统计信息搜集作业，发生实际操作。
  • auto space advisor ：空间优化指导，只生成指导建议，不做实际操作。
  • sql tuning advisor：SQL优化指导，只生成指导建议，不做实际操作。

「有两个关键信息：」

1.窗口：

Oracle设置了两个类型时间窗口运行auto task作业，这个窗口内会执行上面提到的三个作业。这两类时间窗口为，周一至周五工作日每天晚上22:00~次日02:00 持续4小时，周六日从凌晨06:00~次日凌晨02:00一直持续20个小时。如果遇到情况复杂、体量较大的系统时，通常会遇到任务执行失败的情况，这也是为什么Oracle在周末设计20个小时的窗口的原因，希望用更长的窗口完成任务。但是一些周六、日有交易的系统，其实会受到很大的性能影响，甚至我遇到过在周六、周日20个小时的窗口内以每4小时的频率重复执行以上作业的情况。

2.自动：

Oracle在很早就开始布局自动化、从10g 、11g auto task，到18c auto index，再到19c Machine Learning，Oracle在自动化的道路上一直不懈努力。自动化的后面其实是数据库在后台自动的分析了很多数据，有时候接受自动化的代价可能需要接受风险。比如自动化的统计信息收集，会遇到SQL执行计划的突然改变，影响实时交易。自动化的SQL调优建议、段空间建议会带来数据库资源的争用，带来空间告警，甚至影响业务。有得有舍。

建议

  • 常见的建议是关闭auto space advisor 、sql tuning advisor。仅保留auto optimizer stats collection这样带来的风险最低。
  • 如果你的系统周六、日也有在线业务，比如工厂加工流水线、实时交易类系统，也建议将周六、周日的窗口修改时间短一些，毕竟20个小时的窗口期的隐患还是很大的。我比较喜欢将周六、周日的窗口修改为和工作日一样的22点开始4个小时窗口。
  • 如果系统体量特别大，我也会额外注意超大表的统计信息收集，有时需要屏蔽掉较大的表的作业，使用手动的方式处理。
  • 也有的客户为追求绝对的稳定，会关闭auto space advisor 、sql tuning advisor、auto optimizer stats collection三个作业，这种情况下维护统计信息作业变得比较麻烦，强力建议在系统上线之初进行一次统计信息收集，后期在表有重大变化时进行统计信息维护。

举个栗子

一个朋友找过来的问题，windows机器，数据库异常关机，一般异常宕机都会看看报错代码，还没来得及看代码，先看到了cdmp目录生成时间，工作日22:00，立刻引起警觉，很可能是auto task导致的问题。你的Oracle是不是这个时间发生的故障？

然后查一查日志验证了我的猜想你的Oracle是不是这个时间发生的故障？SYS_AUTO_SQL_TUNING_TASK 带来了内存溢出的问题，导致系统宕机。破案以后我关闭了sql tuning advisor，顺带手优化了auto space advisor 。不过也发现这个系统使用了AMM管理，我给改成了ASMM管理，这是另外的一概念，有机会写一篇文章解释解释。

附件

分享一些和auto task相关的常用SQL 自动收集作业状态：







SQL> select client_name,status,WINDOW_GROUP from dba_autotask_client;CLIENT_NAME                                                      STATUS   WINDOW_GROUP---------------------------------------------------------------- -------- --------------------auto optimizer stats collection                          DISABLED ORA$AT_WGRP_OSauto space advisor                                              ENABLED  ORA$AT_WGRP_SAsql tuning advisor                                               ENABLED  ORA$AT_WGRP_SQ

关闭某个任务








BEGINdbms_auto_task_admin.disable(client_name => 'auto space advisor',operation   => NULL,window_name => NULL);END;/

查看窗口信息
















select t1.window_name, t1.repeat_interval, t1.durationfrom dba_scheduler_windows t1, dba_scheduler_wingroup_members t2where t1.window_name = t2.window_nameand t2.window_group_name in('MAINTENANCE_WINDOW_GROUP', 'BSLN_MAINTAIN_STATS_SCHED');
WINDOW_NAME          REPEAT_INTERVAL                                                                                      DURATION-------------------- ---------------------------------------------------------------------------------------------------- -------------------------MONDAY_WINDOW            freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0                                                +000 04:00:00TUESDAY_WINDOW            freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0                                                +000 04:00:00WEDNESDAY_WINDOW     freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0                                                +000 04:00:00THURSDAY_WINDOW        freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0                                                +000 04:00:00FRIDAY_WINDOW              freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0                                                +000 04:00:00SATURDAY_WINDOW       freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0                                                 +000 20:00:00SUNDAY_WINDOW           freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0                                                 +000 20:00:00

修改窗口属性：

















修改持续时间BEGINdbms_scheduler.set_attribute(name      => '"SYS"."THURSDAY_WINDOW"',attribute => 'DURATION',value     => numtodsinterval(12, 'hour'));END;/       
BEGINDBMS_SCHEDULER.SET_ATTRIBUTE(name => '"SYS"."FRIDAY_WINDOW"',attribute => 'REPEAT_INTERVAL',value => 'FREQ=WEEKLY;BYDAY=FRI;BYHOUR=16;BYMINUTE=0;BYSECOND=0');END;/



相关文章