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

作者介绍

谢浩

现任职于云和恩墨,具有多年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 条评论
登录 后参与评论

相关文章

来自专栏FreeBuf

Black Hat Europe 2017:安全专家发现5款最流行编程语言中的漏洞

没有不漏的锅,如果底层的编程语言如果出现问题,顶层的应用程序还能幸免于难吗? 这周在 Black Hat Europe 2017 安全会议上,一名安全研究员公开...

24580
来自专栏Android机动车

Google 最新模拟器重磅来袭!秒开并还原到之前工作状态!

12月18日,Google 官方Quick Boot博客的发布,给我们带来了最新的Android模拟器,其中最突出的特点技术 快速启动。声称可以在 6 秒之内便...

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

运维平台中的集群管理功能设计

我在一次沙龙上说过,做一件事情和做一件具体的事情差别很大。 和很多人交流的时候,其实我是希望他做一件事情,把这件事情负责起来,但是从他们惯有的思维来看,他们希...

40490
来自专栏后端技术探索

淘宝高并发订单的数据库方案

周末参加了@淘宝技术嘉年华 主办的技术沙龙, 感觉收获颇丰,非常感谢淘宝人的分享。这里我把淘宝下单高并发解决方案的个人理解分享一下。我不是淘宝技术人员,本文只是...

35020
来自专栏贾老师の博客

"微信后台技术揭秘"系列读书笔记

31440
来自专栏匠心独运的博客

在实践中使用ShardingJdbc组件的正确姿势(一)

在互联网时代,随着业务数量的暴增和应用规模的不断扩大,无论是oracle还是mysql这样子的关系型数据库,都会面临服务器CPU、磁盘IO和内存的各种瓶颈问题。...

35310
来自专栏纯洁的微笑

微服务架构—服务降级

什么是服务降级?当服务器压力剧增的情况下,根据实际业务情况及流量,对一些服务和页面有策略的不处理或换种简单的方式处理,从而释放服务器资源以保证核心交易正常运作或...

23920
来自专栏FreeBuf

2017 OWASP Top 10十大安全漏洞候选出炉,你怎么看?

OWASP(开放式Web应用程序安全项目)近日公布2017 OWASP Top10(十大安全漏洞列表),增加了2个新分类。 背景介绍 OWASP项目最具权威的...

46160
来自专栏新版本

日志易2.0版本重磅发布! Docker日志接入、ML操作界面等你体验

关注公众号:日志易,回复 ‘我要看案例’ ,案例即可快速阅览。

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

时间序列数据库InfluxDB初探(r12笔记第74天)

性能监控中的很多数据都是根据时间维度来生成的,就算是很少的几台服务器,如果设置了大量的监控项,每天的数据量也是很客观的,再加上是成千上万的服务器,这个量级就...

35770

扫码关注云+社区

领取腾讯云代金券