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

作者介绍

谢浩

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

相关文章

来自专栏大魏分享(微信公众号:david-share)

AIX 下磁盘 I/O 性能分析

磁盘 I/O 的概念 I/O的概念,从字义来理解就是输入输出。操作系统从上层到底层,各个层次之间均存在 I/O。比如,CPU 有 I/O,内存有 I/O, VM...

2919
来自专栏逸鹏说道

.Net中的AOP系列之构建一个汽车租赁应用(上)

开始一个新项目 没有AOP的生活 变更的代价 使用AOP重构 本系列的源码本人已托管于Coding上:点击查看。 本系列的实验环境:VS 2013 Update...

3528
来自专栏函数式编程语言及工具

FunDA(0)- Functional Data Access accessible to all

   大数据、多核CPU驱动了函数式编程模式的兴起。因为函数式编程更适合多线程、复杂、安全的大型软件编程。但是,对许多有应用软件开发经验的编程者来说,函数式编程...

20110
来自专栏乐沙弥的世界

Oracle活动会话历史(ASH)及报告解读

对于数据库运行期间的各种状态的实时监控以及相关性能数据捕获对于解决性能问题,提高整体业务系统运行效率是至关重要的。在Oracle数据库中,实时捕获相关性能数据是...

1141
来自专栏跨界架构师

分布式系统中的必备良药 —— 全局唯一单据号生成

  我们作为一个软件系统,肯定到处充满着各种单据,也必然需要有各种单据号与之对应。比如:电商行业的订单号、支付流水号、退款单号等等。SCM的采购单号、进货单号、...

962
来自专栏祝威廉

Elasticsearch 漫谈

之前在研究ElasticSearch的时候,发现竟然已经有七篇文章了。这些文章通常都是遇到了问题,于是去研读相关代码,试图搞清楚里面的机制,顺带记录下来而成文的...

1214
来自专栏精讲JAVA

key / value 数据库的选型

这个项目有很多 key/value 数据(约 100 GB)需要使用,使用时基本是只读的,偶尔更新时才会批量导入,且可以忍受短暂的停机导入。我一想 TiKV 和...

1163
来自专栏Java技术

面试必备:什么是一致性Hash算法?

最近有小伙伴跑过来问什么是Hash一致性算法,说面试的时候被问到了,因为不了解,所以就没有回答上,问我有没有相应的学习资料推荐,当时上班,没时间回复,晚上回去了...

831
来自专栏斑斓

利用聚合概念指导MongoDB的Schema设计

在我们的项目中,为了能够保存分析报表以及用户设置的报表查询条件,我们将这些信息视为报表元数据存储在MongoDB中。要存储的元数据包括:

662
来自专栏瓜大三哥

SPI 接口协议的Verilog HDL 实现

1.串行外设接口SPI(Serial Peripheral Interface)是一种由Motorola 公司推出的一种同步串行接口,得到了广泛应用。SPI 接...

2167

扫码关注云+社区