案例:troubleshooting high buffer gets in insert into … values …

本案例来自北区同事的分享,客户是exadata,11.2.0.4版本,故障现象是很多平时一条非常简单的insert语句,会消耗了大量逻辑读。应用性能严重下降。

以sql_id:gcwv8p1fhdq5y为例

sql为insert into xxx values(XXX,XXX,XXX…)

该sql历史执行消耗为:

可以看到该sql性能非常不稳定,正常情况下几十个逻辑读即可,但是异常时段每次执行逻辑读非常高。如果我碰到这个case,第一感觉可能是存在大事务或者并发事务,由于data blocks consistent reads – undo records applied过高,导致逻辑读异常增加。但是通常对于这条insert的一个block中的情况,不可能逻辑读增长到如此程度。或者是触发器?

通过异常时段的awr发现,有一条删除recyclebin$的sql的逻辑读,占据了awr snap逻辑读的90%,并且执行次数和单次执行的逻辑读都很高。

该sql是清除回收站的sql,当表空间除开回收站的free空间不足时,会触发该sql去回收回收站对象占用的空间。那么问题就比较清楚了,很可能就是insert的时候表空间不足,并且回收站占用空间过大,触发了递归了该sql,回收recyclebin,并且递归sql的逻辑读应该也算在了insert sql的逻辑读上,所以我们看到insert的逻辑读如此之高。

可以看到delete recyclebin$的sql性能也比较差,单词执行9w多逻辑读。并且执行次数非常多,其实从执行次数也可以看出,回收站每个对象占用的空间其实比较小,但是对象很多。清理一个对象并不能支撑多久。recyclebin$的segment大小撑到了700多M。查看该执行计划发现还是一个全表扫描。。。

一看这个sql就知道统计信息也是错的,那么大的逻辑读,cost才611。。。

delete的where条件是bo(base object),并且该列是没有索引的。这个可能是oracle设计缺陷。没有想到recyclebin$会被撑到那么大?

解决方法:

其他建议:

为了避免此类故障再次发生

  • 如果可以关闭recyclebin
  • 如果非要开启,那么需要监控recyclebin$里的对象占用的空间占据dba_free_space中的比例,如果比例过高,就要在低峰期的手动清理回收站。

over!

 

此条目发表在Oracle, Oracle troubleshooting分类目录,贴了标签。将固定链接加入收藏夹。

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注