love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客

Phone:18180207355 提供专业Oracle/MySQL/PostgreSQL数据恢复、性能优化、迁移升级、紧急救援等服务

关于Disk file operation IO的一个案例分析

本站文章除注明转载外,均为本站原创: 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客

本文链接地址: 关于Disk file operation IO的一个案例分析

最近某银行新核心上线,我司同事连续奋战了大半年,真不容易。在此为大家的辛苦付出点赞。上线后,用户的EM监控突然来了一个告警;

关于Disk file operation IO的一个案例分析插图

最开始以为是个简单的问题,因此就简单查询了一下ash数据,发现主要是一个SQL语句上:

该SQL的代码如下:

从代码来看,这是一个跟审计相关的SQL语句。第一直觉是这个SQL执行效率可能不高;但是其出现的等待事件是IO相关。因此用户非常关系是不是系统IO有问题?如何判断IO是否有问题呢?其实我们可以通过脚本发现查询相关event的平均IO等待事件时间即可:

如果说上述信息还不足以证明的话,那么其实可以查看awr报告的直方图信息:

关于Disk file operation IO的一个案例分析插图(1)

我们知道Oracle为了确保安全性,实现的是日志写优先原则;每一笔事务的提交,Oracle lgwr进程都必须将对应的日志刷新到磁盘;因此可以通过lgwr 进程的写操作来判断数据库主机到存储之间的链路和存储IO是否正常。很明显,从上述图表来看,lgwr file parallel write没有超过8ms的等待。因此可以排除链路和存储的问题。

最开始由于怀疑是SQL问题,尝试收集了几个表的统计信息之后,运行了一下SQL,都在秒级能够返回结果,以为解决了问题。没想到,第二天监控又爆出来了。仍然是相同的问题告警。

这是我再次回馈到分析这个sql的执行计划,该执行计划如下所示:

 

再次回归到分析SQL执行计划,我们发现该SQL的视图中访问的几个表在之前都进行了检查,数据量非常小,而且执行计划本身并没有问题。唯一的可能疑点是对于X$XML_AUDIT_TRAIL  的访问。在之前的分析中,由于检查数据库审计设置,发现已关闭了数据库审计,因此忽略了这个疑点。

通过进行单次多次测试发现,基于X$XML_AUDIT_TRAIL的访问时非常慢的;而且每次访问都会出现Disk file operation IO等待。如下所示:

而上述的d0k3ahnmyd2a2 则是我的测试SQL语句:

从验证来看,看来问题出现这个xml的表上。我们知道Disk file operation IO等待表示的对于文件的操作,比如open,close等。而且前面查询event的operation type都是8,表明确实在等待IO。那么这里的IO是不是物理IO呢?

从上面的SQL report来看,其实该SQL并没有产生物理读。都没有产生物理IO,为什么是IO相关的等待呢?这就奇怪了。。。

那么唯一的解释就是该SQL可能会访问系统的文件,因为用到了xml。联想到这一层,那么怎么验证这个猜想呢?这里我分别开启2个crt窗口进行测试跟踪即可:

++Session 1

可以看到该SQL查询花费了8.2秒。

+++Session2

从上的跟踪信息来看;我们的测试SQL语句执行时间为8.2秒,其中从_poll函数开始到close(12)操作截止,就消耗了8.19秒。其中getdirent64函数操作就消耗了8.14秒。该函数表现获取文件系统目录和文件信息;同时从close的句柄操作来看,主要是慢在/adump目录的操作上。这也难怪上述SQL执行为什么会这么慢了。而且每次执行必然会出现Disk file operation IO等待。

进一步检查/adump目录发现,该目录下的小文件居然高达263万个。

到这里问题基本上就非常清晰了。只要清理这个目录的垃圾文件即可。

那么问题就来了,数据库都已经关闭了审计,为什么还会产生这么多文件呢?实际上大概估算一下,该系统从去年安装部署后至今,大概也就200多天;也就是说该目录平均每天增加1万多个小文件。其实这是因为Oracle数据库默认情况下仍然会审计sys的登陆操作。其他业务用户的审计操作均已关闭。

换句话讲,也就是说该系统的sys用户登陆每天高达1w+次。这是什么神操作呢 ?

最好检查crontab发现是用户有一个清理日志的脚本,忘记注释了,而该脚本几乎是每秒登陆一次(实际上大概是2-3秒)。也就就解释了为什么每天产生12000个左右的文件了。

Leave a Reply

You must be logged in to post a comment.