转载

闪回区报警引发的性能问题分析(r11笔记第11天)

自从有了Zabbix+Orabbix,很多监控都有了一种可控的方式，当然对于报警处理来说，报警是表象，很可能通过表象暴露出来的是一些更深层次的问题。这不又来一个，不看不知道，一看让自己着实吓了一跳。

首先是一个报警信息，可以看到是闪回区超过了报警的阈值，为了尽可能提前发现问题，我把阈值设置为了70%，和Oracle默认的80%有一些差别。

ZABBIX-监控系统:
------------------------------------
报警内容: archive_area_usage
------------------------------------
报警级别: PROBLEM
------------------------------------
监控项目: archive_area_usage：ARCHIVEDLOG-->73.5-->
------------------------------------
报警时间:2016.12.13-06:53:15这个问题可以使用脚本，而且之前确实已经用脚本分析过了。感兴趣移步这里。闪回区空间不足引发的SQL问题分析（r10笔记第32天）

不过我们换一种全新的解读方式，就是通过图表来看，基本也能够定位出问题的方向。

这是一个当天抓取到的闪回区使用率的图表，可以看到闪回区在早上的时间段使用率攀升。

闪回区报警引发的性能问题分析(r11笔记第11天)

那么这个问题该怎么进一步解读呢，我们可以看看是否是一个周期性的问题，下面是一周内的闪回区使用对比图，那么从我这边得到的消息是近期也没有其它的应用变更，这个图表看起来就不大正常了，似乎没有什么特定的规律可循。

闪回区报警引发的性能问题分析(r11笔记第11天)

而且通过上面的图表很可能会得出一个错误的结论，怎么理解呢，我们得到一个月的闪回区使用情况，就会发现这种规律来。闪回区的变化其实还是有一定的规律可循的。

闪回区报警引发的性能问题分析(r11笔记第11天)

好了，到了这里我就不秀图了，开始甩开膀子查问题了。

首先得到的是一个近期的归档情况，可以看到确实是周期性的，而且归档的频率在问题发生时极高，按照这个频率，每个日志成员1G的大小，这得切换近300次。这个量级确实太惊人了。

闪回区报警引发的性能问题分析(r11笔记第11天)

如此多的归档，充分说明存在大量的数据变化，而这类操作update的可能性最高，而且具有周期性，那么狠可能是JOB相关。当然这个问题肯定会使得DB time升高。

所以我们继续拿脚本得到下面的数据，就是查看问题时间段内的快照数据，看看哪些SQL占用了大量的DB time

   SNAP_ID SQL_ID        EXECUTIONS_DELTA ELAPSED_TI PER_TOTAL
---------- ------------- ---------------- ---------- ----------
     78491 75ubgcf0pdrkr                0 1802s      40%
     78491 882jz57wm9cj7                0 1802s      40%
     78491 0cn4gzcn4j0fb                1 161s       3%
     78491 cq4a88vu6232h                1 155s       3%
     78491 79zffzf7unqdm                1 129s       2%

可以看到前2个语句执行时间有些长了。这是一个半小时的快照，两个语句竟然一直在运行。

当然拿到语句也全然不费功夫，就是下面的语句。

第1个是可以看出是一个存储过程。

SQL_FULLTEXT
----------------------------------------------------------------------------------------------------
BEGIN proc_update_cardinfo(); END;

第二个可以看出是一个update语句，两者简单关联一下，发现还是有点联系。SQL_FULLTEXT
----------------------------------------------------------------------------------------------------
UPDATE CARDINFO 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'

印证起来就很容易了，很快定位出这个update语句就是出自这个存储过程。

这个语句除了看起来稍微有些臃肿外，暂时没有发现其它的问题。实际上这两个表数据量分别在亿级，千万级，如此一来就很容易出现性能问题了，当然我就引申出一个方法，那就是不要拘泥于这一个语句来做优化而是纵观这个需求全盘来考虑。

我在纸上看着存储过程的实现方式画了一下流程图，发现比我想象的要复杂的多。表cardinfo上存在多个触发器，任何的DML都会有一套数据的维护规则，而且这个表的数据变更源头是来自另外一个数据库，通过DB Link的方式去取得增量数据，然后在这个基础上结合当前库的千万级大表做更新。

大体的逻辑如下，我使用伪代码表示

begin
cursor cur is select * from test@db_link where xxxxxx;
for i in cur loop
merge into cardinfo
using(xxxxxx)
on (xxx.id=xx.id)
when matched then update
when not matched then insert
commit
end loop;
update cardinfo set col2=(xxxx) where drawed='Y';
update cardinfo set col3=(xxxx) where drawed='Y';
end;

当然我感觉到有一些潜在问题，但是还是需要确认，于是和开发同事进行了沟通，首先得和他们说明这个问题，第二我来帮他们看看是否有改进空间，第三他们需要告诉我一些基本的逻辑，所以这个过程开发同学其实还是蛮配合的。

大体的业务逻辑了解了下，再看这个实现也能够基本理解了。当然业务的内容说太细不合适，说太少又不理解，我就简单举一个例子，就好比在玩游戏的时候会有一些奖券之类的东西，你如果抽到了就可以使用，也可以忽略，如果使用那就是一个激活的过程，激活之后这些奖券可能会相应提升你的等级，这个存储过程就是做这个事情的。

这个update语句的执行计划信息如下：

闪回区报警引发的性能问题分析(r11笔记第11天)
可以看出这是一个看起来优化空间很大的问题，需要动用大量的优化技巧。目前已经在处理的过程中，对于性能问题的验证和测试，都会有一些值得借鉴的地方。容我好好测试一下，分享出来。