全表扫描却产生大量db file sequential read一例

编辑手记:一条看似简单的SQL,执行时间异常惊人,明明是全表扫描,却在undo 表空间产生大量的单块读导致db file sequential read等待事件。今天老熊带你揭开重重迷雾。

开发人员在进行新系统上线前的数据校验测试时,发现一条手工执行的SQL执行了超过1小时还没有返回结果。SQL很简单:

下面是这条SQL的真实的执行计划:

很显然,在这个表上建billing_nbr和start_date的复合索引,这条SQL就能很快执行完(实际上最后也建了索引)。但是这里我们要探讨的是,为什么这么一条简单的SQL语句,执行了超过1小时还没有结果。

正常运行评估

MOBILE_CALL_1204_OLD这张表的大小约为12GB,以系统的IO能力,正常情况下不会执行这么长的时间。简单地看了一下,系统的CPU以及IO压力都不高。假设单进程全表扫描表,每秒扫描50MB大小(这实际上是一个很保守的扫描速度了),那么只需要245秒就可以完成扫描。

下面来诊断一下SQL为什么会这么不正常地慢。

看看会话的等待(以下会用到Oracle大牛Tanel Poder的脚本):

明明是全表扫描的SQL,为什么99%以上的等待时间是db file sequential read,即单块读?!

多执行几次waitprof脚本,得到的结果是一致的(注意这里的数据,特别是平均等待时间并不一定是准确的值,这里重点关注的是等待时间的分布)。

那么SQL执行计划为全表扫描(或索引快速全扫描)的时候,在运行时会有哪些情况实际上是单块读?我目前能想到的有:

  1. db_file_multiblock_read_count参数设置为1
  2. 表或索引的大部分块在buffer cache中,少量不连续的块在磁盘上。
  3. 一些特殊的块,比如段头
  4. 行链接的块
  5. LOB列的索引块和cache的LOB块(虽然10046事件看不到lob索引和cache的lob的读等待,但客观上是存在的。)

那么在这条SQL语句产生的大量单块读,又是属于什么情况呢?我们来看看单块读更细节的情况:

多次执行同样的SQL,发现绝大部分的单块读发生在3、353-355这四个文件上,我们来看看这4个文件是什么:

原来是UNDO表空间。那么另一个疑问就会来了,为什么在UNDO上产生了如此之多的单块读?

首先要肯定的是,这条简单的查询语句,是进行的一致性读。那么在进行一致性读的过程中,会有两个动作会涉及到读UNDO块,延迟块清除和构建CR块。

下面我们用另一个脚本来查看会话当时的状况:

上面的结果是5秒左右的会话采样数据。再一次提醒,涉及到时间,特别要精确到毫秒的,不一定很精确,我们主要是看数据之间的对比。

从上面的数据来看,会话请求了382次IO请求,单块读和多块读一共耗时4219.17ms(4.17s+49.17ms),平均每次IO耗时11ms。这个单次IO速度对这套系统的要求来说相对较慢,但也不是慢得很离谱。data blocks consistent reads - undo records applied这个统计值表示进行一致性读时,回滚的UNDO记录条数。

初步分析

对比这个统计值可以很明显地看出,这条SQL在执行时,为了得到一致性读,产生了大量的UNDO记录回滚。那么很显然,在这条SQL语句开始执行的时候,表上有很大的事务还没有提交。当然还有另一种可能是SQL在执行之后有新的很大的事务(不过这种可能性较小一些,因为那样的话这条SQL可能比较快就执行完了)。

询问发测试的人员,称没有什么大事务运行过,耳听为虚,眼见为实:

这张表目前没有事务,但是曾经update了超过1.6亿条记录。最后一次DML的时间正是这条执行很慢的SQL开始运行之后的时间(这里不能说明最后一次事务量很大,也不能说明最后一次修改对SQL造成了很大影响,但是这里证明了这张表最近的确是修改过,并不是像测试人员说的那样没有修改过)。

实际上对于这张表要做的操作,我之前是类似的表上是有看过的。这张表的总行数有上亿条,而这张表由于进行数据的人工处理,需要update掉绝大部分的行,update时使用并行处理。

那么这个问题到,从时间顺序上来讲,应该如下:

  1. 在表上有很大的事务,但是还没有提交。
  2. 问题SQL开始执行查询。
  3. 事务提交。
  4. 在检查SQL性能问题时,表上已经没有事务。

由于update量很大,那么UNDO占用的空间也很大,但是可能由于其他活动的影响,很多UNDO块已经刷出内存,这样在问题SQL执行时,大量的块需要将块回滚到之前的状态(虽然事务开始于查询SQL,但是是在查询SQL开始之后才提交的,一致性读的SCN比较是根据SQL开始的SCN与事务提交SCN比较的,而不是跟事务的开始SCN比较),这样需要访问到大量的UNDO块,但是UNDO块很多已经不在内存中,就不得不从磁盘读入。

对于大事务,特别是更新或DELETE数千万记录的大事务,在生产系统上尽量避免单条SQL一次性做。这造成的影响特别大,比如:

  • 事务可能意外中断,回滚时间很长,事务恢复时过高的并行度可能引起负载增加。
  • 表中大量的行长时间被锁住。
  • 如果事务意外中断,长时间的回滚(恢复)过程中,可能严重影响SQL性能(因为查询时需要回滚块)。
  • 事务还未提交时,影响SQL性能,比如本文中提到的情况。
  • 消耗过多UNDO空间。
  • 对于DELETE大事务,有些版本的oracle在空闲空间查找上会有问题,导致在INSERT数据时,查找空间导致过长的时间。
  • 对于RAC数据库,由于一致性读的代价更大,所以大事务的危害更大。

那么,现在我们可以知道,全表扫描过程还会产生单块读的情况有,读UNDO块。 对于这条SQL,要解决其速度慢的问题,有两种方案:

  1. 在表上建个索引,如果类似的SQL还要多次执行,这是最佳方案。
  2. 取消SQL,重新执行。因为已经没有事务在运行,重新执行只是会产生事务清除,但不会回滚UNDO记录来构建一致性读块。

继续回到问题,从统计数据来看,每秒只构建了少量的一致性读块(CR block created,table scan blocks gotten这两个值均为2),每秒的table scan rows gotten值为98.4,通过dump数据块可以发现块上的行数基本上在49行左右,所以一致性读块数和行数是匹配的。session logical reads每秒为97.6,由于每回滚一条undo记录都要记录一次逻辑读,这个值跟每秒获取的行数也是匹配的(误差值很小),与data blocks consistent reads - undo records applied的值也是很接近的。

问题到这儿,产生了一个疑问,就是单块读较多(超过70),因此可以推测,平均每个undo块只回滚了不到2条的undo记录,同时同一数据块上各行对应的undo记录很分散,分散到了多个undo块中,通常应该是聚集在同一个块或相邻块中,这一点非常奇怪,不过现在已经没有这个环境(undo块已经被其他事务重用),不能继续深入地分析这个问题,就留着一个疑问,欢迎探讨(一个可能的解释是块是由多个并发事务修改的,对于这个案例,不会是这种情况,因为在数据块的dump中没有过多ITL,另外更不太可能是一个块更新了多次,因为表实在很大,在短时间内不可能在表上发生很多次这样的大事务)。

在最后,我特别要提到,在生产系统上,特别是OLTP类型的系统上,尽量避免大事务。

原文发布于微信公众号 - 数据和云(OraNews)

原文发表时间:2017-05-05

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏大内老A

谈谈分布式事务之一:SOA需要怎样的事务控制方式

在一个基于SOA架构的分布式系统体系中,服务(Service)成为了基本的功能提供单元,无论与业务流程无关的基础功能,还是具体的业务逻辑,均实现在相应的服务之中...

1957
来自专栏我是攻城师

如何使用Jekyll+GitHub Pages搭建个人博客站点

3817
来自专栏Python小屋

使用Python开发会聊天的智能小机器人

本文重点演示使用socket实现TCP通信以及字典和集合的用法,客户端发来信息之后,服务端会尽量猜测客户端要表达的真正意思,然后选择合适的内容进行回复。 服务...

33612
来自专栏信安之路

从0开始编写信息收集器

我们都知道在信息收集是整个渗透测试中无可或缺的一环,那我们老是需要一类一类信息去查询非常耗费时间,(人生苦短,我用 python)那这时我就想做一个信息收集器,...

740
来自专栏王磊的博客

聊聊excel生成图片的几种方式

目录     I:需求。    II:实现思路。     III:实现方式。     IV:优缺点分析。     V:结论。     VI:wps安装与配置。 ...

33911
来自专栏phodal

你不再需要动态网页——编辑-发布-开发分离

尽管没有特别的动力去构建一个全新的CMS,但是我还是愿意去撰文一篇来书写如何去做这样的事——编辑-发布-开发分离模式是如何工作的。微服务是我们对于复杂应用的一种...

1999
来自专栏友弟技术工作室

Python 资源大全中文版

GitHub 上有一个 Awesome - XXX 系列的资源整理,资源非常丰富,涉及面非常广。awesome-python 是 vinta 发起维护的 Pyt...

3515
来自专栏java一日一条

Java EE7和Maven工程入门(1)

在日常工作中,我经常需要解决许多简单的或者是复杂的Maven/Java EE工程结构的问题。为了找到解决办法,我经常要拿项目的结构做实验,在不同应用服务器上对部...

241
来自专栏养码场

找工作太麻烦?造个机器人吧!

这是基于go语言编写的一款boss直聘机器人软件(牛人版)。附上Python版本, 无需配置Go环境, 我会提供windows和macos的可执行程序。不喜勿喷...

684
来自专栏张善友的专栏

SQL Server 2008基于策略的管理

基于策略的管理(Policy Based Management),使DBA们可以制定管理策略,并将这些策略应用到服务器、数据库以及数据环境中的其他对象上去。经过...

1869

扫码关注云+社区