闪回区空间不足引发的SQL问题分析

共计 2078 个字符，预计需要花费 6 分钟才能阅读完成。

有一天上班的时候，收到一封报警邮件。
ZABBIX-监控系统 :
————————————
报警内容 : archive_area_usage
————————————
报警级别 : PROBLEM
————————————
监控项目 : archive_area_usage：ARCHIVED LOG–>70.25–>
————————————
报警时间 :2016.09.20-08:52:47 可以看出是闪回区快满了，当然我设置了阈值 70%，比 Oracle 默认的 80% 要更低一些，希望尽可能早的发现这些潜在的问题。
碰到这个问题，让我有些奇怪。
现在服务器端都有默认的 crontab 来设置定期删除过期的归档，怎么闪回区还会这么快就满了呢。这类问题的原因相对来说复杂一些，如果说从数据库层面来看，如果在 10gR2 的版本中，可能出现这种情况，那就是有些命令的兼容性问题导致，如果是系统层面可能就是就是存储路径失效，比如 nfs 挂载点失效等导致。
目前这个数据库是 11gR2，存储都是本地磁盘。
我们来看看 crontab 的设置，可以看出是每个小时会运行，触发的频率较高，如果每天触发一次，如果存在这个问题可能还能理解，为什么在这种频率下删除归档依旧闪回区空间不足？
$ crontab -l
*/50 * * * *  . $HOME/.bash_profile;$HOME/dbadmin/scripts/rman_trun_arch.sh
我们来看看脚本的内容。我贴出关键的部分。
可以看出归档的删除过期归档，保留时间是 10 个小时之内，其实已经算是很短的了。保留近半天的归档而已。
rman target / <<EOF
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;
crosscheck archivelog all;
delete noprompt expired archivelog all;
delete noprompt archivelog until time “sysdate-10/24”;
exit
EOF
如此频率下怎么还会有这类问题。看看当前闪回区的情况。

可以看到已经存在 300 多个归档。
这问题确实有意思了，有大量的归档，有频繁的删除策略，但是闪回区还报错。
我们来换个姿势看这个问题，就是查看归档频率。

这个脚本的强大的之处就在于可以查看近 2 周的归档频率，通过这种方式就可以看出这个问题其实是一个周期性的。在周二会定期出现，只是之前没有引起重视而已。
可以看到每个小时的归档频率极高，按照这种情况，6 个小时就会积累 300 多个归档，一个归档日志成员是 1G 来算，那么这个归档量就很大了。
一个统计库怎么这么忙，这是一个问题，我们来看看数据库的负载情况。

可以看到在早间的时候数据库的负载还是有很大的提升。
那么这个时间段内是否有 SQL 引起的如此的变化，比如一个 AWR 报告，比如一个脚本就能够定位。
当然抓到罪魁祸首是关键，我是使用脚本来做，抓到了下面的语句。发现了不少负载高的查询语句。

进一步定位，发现都有千丝万缕的关键，那就是其中一个存储过程调用，会调用里面的一些 SQL 语句。
最终发现 SQL 语句是这样的形式
SQL_FULLTEXT
—————————————————————————————————-
UPDATE TESTINFO A SET A.MAX_LEVEL = NVL((SELECT USER_CLASS FROM ROLE_CLASS_INFO B WHERE A.GROUPID =
B.GROUP_ID AND B.CN_GUID = A.ROLE_GUID), A.MAX_LEVEL) WHERE DRAWED = ‘Y’

看这个语句其实逻辑也不复杂，但是如果查看数据量就会发现这个工作量真是太大了，两个表都是亿级的数据量。

按照过滤条件，数据量 2 亿，过滤得到 4 千万，都不是小数目，所以全表看来也是一种方案。
SQL> select DRAWED,count(*)from test.testinfo group by DRAWED;
D  COUNT(*)
– ———-
Y  43807108
N  216762221
Elapsed: 00:00:36.17 
但是显然这里还是存在一些需要确认的地方，这个语句本该不需运行，至少不应该在统计层面来保证数据的业务逻辑一致性，应该在 OLTP 系统中就应该保证，所以我的努力方向就是取消这个 JOB，这种优化才是最有效的。

更多 Oracle 相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12

本文永久更新链接地址：http://www.linuxidc.com/Linux/2016-09/135531.htm