从逻辑入手优化数据库性能

作者介绍

谢浩

现任职于云和恩墨,具有多年oracle数据库企业级运维经验,擅长结合业务、硬件系统制定各种项目方案。

在oracle性能优化主要包括:数据架构优化、逻辑优化、sql优化、数据库运行参数优化。本文从两个实际案例入手介绍从逻辑优化的方向优化数据库性能。

案例一:“过度紧张”的性能监控语句

出现的问题

某客户出现系统缓慢,经检查oracle数据库主机cpu占用在问题时段达到95%以上,物理IO总量超过往常同时段的2倍。

问题分析

Awr中cpu占用、IO排名第一的sql如下:

Selct count(1) from IOM_P_SVM;

该sql来自存储过程P_IOM_POST,对IOM_P_SVM表进行全表扫描,其cpu占用、物理IO超出第二名5倍以上。

检查发现该表上没有建立任何索引,进一步检查涉及该表的其他sql,主要包含:

Delete from IOM_P_SVM where stat_id=:1;

该sql同样对表进行全表扫描。经过与应用厂商核实,IOM_P_SVM表的stat_id字段不可能为空。至此,在stat_id字段上建立索引是比较“标准”的优化手段,但经过详细分析并不匹配客户实际业务情况。

IOM_P_SVM表实际上是业务模块A与业务模块B之间的“接口表”。接口表是一种应用系统中常见的设计模式。模块A向接口表内不断插入待处理的数据,模块B定时将其中状态为“未处理”的数据(对应stat_id为“I”)取出进行后续工作,处理完成后再从接口表内delete掉刚刚处理的部分数据,如此往复。

这种方法在模块B出现故障时会造成接口表的数据积压及水位线的升高。

红框内的代码,实际上是程序开发人员为防止在模块B出现问题时出现接口表积压的问题而采取的措施,一旦积压数据超过8000行,就降低插入的速度。

此类接口表及对应的逻辑具有较高的“数据变更/数据查询比”,该比例是建立索引时需要考虑的因素之一,如果某个表对数据的变更操作(增删改)与对该表内数据进行查询的比例超过1:1就要结合insert、delete、update、select的各种sql考虑建立索引带来的查询性能提升与维护索引的代价孰轻孰重。而此类接口表中的数据在正常情况下会经历:1次插入、1次更新、1次select查询、1次删除。同时,如果模块A、B都正常工作,接口表的体积应当极小,数据插入后很快就被模块B处理完成并删除。因此接口表上一般都不会建立索引。

优化方案

以上代码使用变量iv_count进行计数,每循环1000次进行积压量判断,因此在7001至8000范围内必然会有一次积压量查询被发起。

修改后该sql造成的压力将下降至现有水平的1/1000。

案例二:通过rowid减小事务量

出现的问题

某客户批处理和统计上报应用出现缓慢现象,经检查问题sql如下:

经过与业务人员了解,使用rownum进行限制的目的是减少批处理工作的“批量”大小,但是却要对目标表进行多次full table scan,造成大量物理IO。

同时客户另一统计类应用系统出现大量Deq Credit: send blkd等待事件,甚至出现parallel语句hang住无法运行的情况,经过oracle原厂、主机os厂商、第三方厂商多次会诊,仍无法解决。但得出的结论都是过多的运用并行,但不适用并行很难在业务规定时间内完成数据加工。

优化方案

针对以上问题,我方提出了适用rowid对表进行分割以减小事务体积的优化方法。部分核心代码如下:

该方案通过构造rowid的方式将表进行分割,代替之前适用rownum分割的方式,因此在达到相同目的的情况下仅需要对原表扫描一次,大幅度减小事务量。详细代码点击【阅读全文】

该方案的基础是mos上给出的通过rowid分割大表的sql

需要注意的问题:

1、需要通过存储过程等方法自动生成分割后的sql,以便应用系统调用(附件中已包含完整存储过程代码)

2、如果where子句中使用了rowid,那么parallel hint将失效,也就是说通过rowid分割生成的sql只能单线程执行。

3、Insert语句分割后如果添加append关键字,由于append插入会产生互锁,因此只能串行执行,而append关键字是nologging的必要条件。因此如果目标sql是insert语句需要权衡拆分、并行、nologging之间对性能的影响。

4、最后一个分片需要单独处理,由于程序不是静态,因此需要考虑在分割工作开始以后新插入的业务数据。

该方案还可以结合oracle多线程部分替代parallel,具体方法是将待处理的分割sql包装到单独的存储过程中,在主存储过程中通过job调用,这样就达到了人工控制多线程的效果。具体代码将结合附件进行讲解。

总结

在数据库优化工作中,往往把注意力集中在参数、sql两个方面,但是根据行业内大量经验证明,数据库架构、程序逻辑实现对性能的影响也是很大的,因此我们在优化一个sql的时候可以先考虑“这个sql可以不可以少执行?”“这个sql访问的数据能不能少一些?”而往往这些被忽略甚至主观认为不可能的因素,却能对我们的优化工作带来很大的帮助。

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

原文发表时间:2017-09-26

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

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏云加新鲜事儿

腾讯云李海翔:数据库的并发控制技术深度探索

在2017第八届中国数据库技术大会(DTCC2017)上,腾讯云数据库专家工程师李海翔作为内核专场主持人和专家组成员,代表腾讯云进行了题为《数据库的并发控制技术...

4130
来自专栏机器学习和数学

[编程经验] Pandas中比较好用的几个方法

话说我现在好久不做深度学习的东西了,做了一段时间是的NLP,以为可以去尝试各种高大上的算法,然而现在还并没有,反而觉得更像是做数据挖掘的。。平时遇到的比较多的问...

3285
来自专栏程序员的SOD蜜

抛砖引玉:使用二进制位操作,解决铁道部火车票的数据查询和存储问题,超轻量级的解决方案

    又到节假日,园子里面不少高人再次对12306网站的各种问题的各种分析和提出各种解决方案,我也看了这些讨论文章,出于也是一个买票难的“码农”,也来献计献言...

2485
来自专栏Albert陈凯

Scala学习路线

这是一篇为公司内部”scala热情workshop”活动准备的文章,面向Scala初学者,目的在于帮助大家能尽早就建立起对Scala的整体认识,少走弯路。当然由...

3225
来自专栏牛客网

阿里Android客户端一面经历

猝不及防的空降,表示电影刚开场、等了两个星期的一面应该是简历面吧,终于打过来了。讲道理并不敢拒面,天知道下次什么时候打过来。

611
来自专栏landv

C#本质论第四版-1,抄书才能看下去,不然两三眼就看完了,一摞书都成了摆设。抄下了记忆更深刻

1893
来自专栏微信公众号:Java团长

什么才是Java的基础知识?

近日里,很多人邀请我回答各种j2ee开发的初级问题,我无一都强调java初学者要先扎实自己的基础知识,那什么才是java的基础知识?又怎么样才算掌握了java的...

371
来自专栏一个会写诗的程序员的博客

函数式编程与面向对象编程[5]:编程的本质函数式编程与面向对象编程[5]:编程的本质编程的本质

函数式程序员在洞察问题方面会遵循一个奇特的路线。他们首先会问一些似有禅机的问题。例如,在设计一个交互式程序时,他们会问:什么是交互?在实现 基于元胞自动机的生命...

512
来自专栏喔家ArchiSelf

IOT语义互操作性之本体论

这个系列文章描述了一个单一的语义数据模型来支持物联网和建筑、企业和消费者的数据转换。 这种模型必须简单可扩展, 以便能够在各行业领域之间实现插件化和互操作性。 ...

875
来自专栏CSDN技术头条

关系型数据库是如何运作的(上)

一说到关系型数据库,我总感觉缺了点什么。如果你尝试透过“关系型数据库是如何运作的”的关键词句来进行搜索,其搜索结果是少量的而且内容是简短的。难道说是由于它已经太...

1898

扫描关注云+社区