为什么忘记commit也会造成select查询的性能问题

栏目: 数据库 · 发布时间: 6年前

内容简介:今天遇到一个很有意思的问题,一个开发人员反馈在测试服务器ORACLE数据库执行的一条简单SQL语句非常缓慢,他写的一个SQL没有返回任何数据,但是耗费了几分钟的时间。让我检查分析一下原因,分析解决过后,发现事情的真相有点让人哭笑不得,但是也是非常有意思的。我们先简单构造一下类似的案例,当然只是简单模拟。

今天遇到一个很有意思的问题,一个开发人员反馈在测试服务器ORACLE数据库执行的一条简单 SQL 语句非常缓慢,他写的一个SQL没有返回任何数据,但是耗费了几分钟的时间。让我检查分析一下原因,分析解决过后,发现事情的真相有点让人哭笑不得,但是也是非常有意思的。我们先简单构造一下类似的案例,当然只是简单模拟。

假设一个同事A,创建了一个表并初始化了数据(实际环境数据量较大,有1G多的数据),但是他忘记提交了。我们简单模拟如下:


为什么忘记commit也会造成select查询的性能问题

另外一个同事B对这个表做一些简单查询操作,但是他不知道同事A的没有提交INSERT语句,如下所示,查询时间用了大概5秒多(这个因为构造的数据量不是非常大的缘故。实际场景耗费了几分钟)


为什么忘记commit也会造成select查询的性能问题

当时是在SQL Developer工具里面分析SQL的执行计划,并没有注意到redo size非常大的情况。刚开始怀疑是统计信息不准确导致,手工收集了一下该表的统计信息,执行的时间和执行计划依然如此,没有任何变化。 如果我们使用SQL*Plus,查看执行计划,就会看到redo size异常大,你就会有所察觉(见后面分析)


因为ORACLE里面的写不阻塞读,所以不可能是因为SQL阻塞的缘故,然后我想查看这个表到底有多少记录,结果亮瞎了我的眼睛,记录数为0,但是空间用掉了852 个数据块


为什么忘记commit也会造成select查询的性能问题

于是我使用Tom大师的show_space脚本检查、确认该表的空间使用情况,如下所示,该表确实使用852个数据块。


为什么忘记commit也会造成select查询的性能问题

分析到这里,那么肯定是遇到了插入数据操作,却没有提交的缘故。用下面脚本检查发现一个会话ID为883的对这个表有一个ROW级排他锁,而且会话还有一个事务排他锁,那么可以肯定这个会话执行了DML操作,但是没有提交。


为什么忘记commit也会造成select查询的性能问题

我们在会话里面提交后,然后重新执行这个SQL,你会发现执行计划里面redo size为0,这是因为redo size表示DML生成的redo log的大小,其实从上面的执行计划分析redo size异常,就应该了解到一个七七八八了,因为一个正常的SELECT查询是不会在redo log里面生成相关信息的。那么肯定是遇到了DML操作,但是没有提交。

为什么忘记commit也会造成select查询的性能问题

分析到这里,我们已经知道事情的前因后果了,解决也很容易,找到那个会话的信息,然后定位到哪个同事,让其提交即可解决。但是,为什么没有提交与提交过后的差距那么大呢?是什么原因呢? 我们可以在这个案例,提交前与提交后跟踪执行的SQL语句,如下所示。


提交前上面SQL生成的跟踪文件为scm2_ora_8444.trc,我们使用TKPROF格式化如下: tkprof scm2_ora_8444.trc out_uncommit.txt 如下所示

为什么忘记commit也会造成select查询的性能问题

提交后,在另外一个会话执行上面的SQL,然后格式化跟踪文件如下所示:

为什么忘记commit也会造成select查询的性能问题

我们发现提交前与提交后两者的物理读、一致性读有较大差别(尤其是一致性读相差3倍多)。这个主要是因为ORACLE的一致性读需要构造cr块,产生了大量的逻辑读的缘故。相关理论与概念如下:

为什么要一致性读,为了保持数据的一致性。如果一个事务需要修改数据块中数据,会先在回滚段中保存一份修改前数据和SCN的数据块,然后再更新Buffer Cache中的数据块的数据及其SCN,并标识其为“脏”数据。

当其他进程读取数据块时,会先比较数据块上的SCN和进程自己的SCN。如果数据块上的SCN小于等于进程本身的SCN,则直接读取数据块上的数据;

如果数据块上的SCN大于进程本身的SCN,则会从回滚段中找出修改前的数据块读取数据。通常,普通查询都是一致性读。

一致性读什么时候需要cr块呢,那就是select语句在发现所查询的时间点对应的scn,与数据块当前所的scn不一致的时候。构造cr块的时候,首先去data buffer中去找包含数据库前镜像的undo块,如果有直接取出构建CR块,这时候是逻辑读,产生逻辑IO;但是data buffer将undo信息写出后,就没有需要的undo信息,就会去undo段找所需要的前镜像的undo信息,这时候从磁盘上读出block到buffer中,这时候产生物理读(物理IO)


以上所述就是小编给大家介绍的《为什么忘记commit也会造成select查询的性能问题》,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对 码农网 的支持!

查看所有标签

猜你喜欢:

本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们

App研发录:架构设计、Crash分析和竞品技术分析

App研发录:架构设计、Crash分析和竞品技术分析

包建强 / 机械工业出版社 / 2015-10-21 / CNY 59.00

本书是作者多年App开发的经验总结,从App架构的角度,重点总结了Android应用开发中常见的实用技巧和疑难问题解决方法,为打造高质量App提供有价值的实践指导,迅速提升应用开发能力和解决疑难问题的能力。本书涉及的问题有:Android基础建设、网络底层框架设计、缓存、网络流量优化、制定编程规范、模块化拆分、Crash异常的捕获与分析、持续集成、代码混淆、App竞品技术分析、项目管理和团队建设等......一起来看看 《App研发录:架构设计、Crash分析和竞品技术分析》 这本书的介绍吧!

CSS 压缩/解压工具
CSS 压缩/解压工具

在线压缩/解压 CSS 代码

XML、JSON 在线转换
XML、JSON 在线转换

在线XML、JSON转换工具

html转js在线工具
html转js在线工具

html转js在线工具