全表扫描却产生大量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 条评论
登录 后参与评论

相关文章

来自专栏杨建荣的学习笔记

这样分析一个死锁问题

之前也列举了几期的MySQL死锁问题,光有操作演练,还缺少一些自己的分析,所以我就打算补充一下。 首先对于死锁问题,我们分析的背景是基于MySQL事...

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

Mysql大表优化方案

原文版权 ? 当MySQL单表记录数过大时,增删改查性能都会急剧下降,可以参考以下步骤来优化: 单表优化 除非单表数据未来会一直不断上涨,否则不要一开始就考虑拆...

4017
来自专栏Linyb极客之路

MySQL锁机制及优化

总的来说,MySQL各存储引擎使用了三种类型(级别)的锁定机制:行级锁定,页级锁定和表级锁定。下面我们先分析一下MySQL这三种锁定的特点和各自的优劣所在。

1602
来自专栏Java架构沉思录

MySQL在并发场景下的优化手段

对于数据库系统来说在多用户并发条件下提高并发性的同时又要保证数据的一致性一直是数据库系统追求的目标,既要满足大量并发访问的需求又必须保证在此条件下数据的安全,为...

1202
来自专栏数据和云

Oracle 12.2 新特性:在线的修改数据表为分区表

众所周知,分区技术是Oracle应用最为广泛的一个选件,并且历史悠久,然而在过去的版本中,如果我们要将一个现有的数据表转换为分区表,则是相当复杂的一个过程,而在...

3635
来自专栏美团技术团队

数据库运维必读的10个问题

本文中的问题精选自上期【你问我答】——数据库专题中读者的提问。【你问我答】是由美团点评技术团队推出的线上问答服务,你在工作学习中遇到的各种技术问题,都可以通过我...

35211
来自专栏数据和云

基于裸数据的异地数据库性能诊断与优化

作者介绍 杨江, 6年Oracle工作经验,4年Oracle数据库专业服务经验,擅长性能优化、性能问题诊断、故障排查、GOLDENGATE。 影响数据库性能的因...

2855
来自专栏大数据平台TBDS

spark sql简单查询千亿级库表导致的问题

根据常理判断,简单的 select * limit 不会造成内存溢出的。因此,我们用hive原生sql查询,发现不存在这个问题。

3812
来自专栏数据和云

常识之外:全表扫描为何产生大量 db file sequential read 单块读?

编辑手记:在理解Oracle技术细节时,我们不仅应该读懂概念,还要能够通过测试验证细节,理解那些『功夫在诗外』的部分,例如全表扫描和单块读。 开发人员在进行新系...

3539
来自专栏FreeBuf

企业安全建设之路:端口扫描(下)

0x00、前言 在企业安全建设过程当中,我们也不断在思考,做一个什么样的端口扫描才能企业业务需求。同时,伴随着企业私有云、混合云以及公有云业务部署环境的不断变...

46210

扫码关注云+社区