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

作者介绍

谢浩

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

相关文章

来自专栏一个爱瞎折腾的程序猿

sqlserver使用存储过程跟踪SQL

USE [master] GO /****** Object: StoredProcedure [dbo].[sp_perfworkload_trace_s...

2890
来自专栏转载gongluck的CSDN博客

cocos2dx 打灰机

#include "GamePlane.h" #include "PlaneSprite.h" #include "BulletNode.h" #include...

7246
来自专栏C#

DotNet加密方式解析--非对称加密

    新年新气象,也希望新年可以挣大钱。不管今年年底会不会跟去年一样,满怀抱负却又壮志未酬。(不过没事,我已为各位卜上一卦,卦象显示各位都能挣钱...)...

5988
来自专栏Ceph对象存储方案

Luminous版本PG 分布调优

Luminous版本开始新增的balancer模块在PG分布优化方面效果非常明显,操作也非常简便,强烈推荐各位在集群上线之前进行这一操作,能够极大的提升整个集群...

3675
来自专栏java 成神之路

使用 NIO 实现 echo 服务器

5607
来自专栏杨龙飞前端

scrollto 到指定位置

2964
来自专栏张善友的专栏

Miguel de Icaza 细说 Mix 07大会上的Silverlight和DLR

Mono之父Miguel de Icaza 详细报道微软Mix 07大会上的Silverlight和DLR ,上面还谈到了Mono and Silverligh...

3007
来自专栏张善友的专栏

Silverlight + Model-View-ViewModel (MVVM)

     早在2005年,John Gossman写了一篇关于Model-View-ViewModel模式的博文,这种模式被他所在的微软的项目组用来创建Expr...

3308
来自专栏魂祭心

原 canvas绘制clock

5154
来自专栏张善友的专栏

Mix 10 上的asp.net mvc 2的相关Session

Beyond File | New Company: From Cheesy Sample to Social Platform Scott Hansel...

2787

扫码关注云+社区