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

作者介绍

谢浩

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

相关文章

来自专栏Android机动车

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

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

2482
来自专栏数据和云

创新,才能不被淘汰-机器学习时代,运维将何去何从?

我们曾经分享过一篇文章,云时代的DBA,何去何从?,在文中我们讨论了Oracle最近几年重点转而向云的变革,它全力以赴在做的一件事情就是把所有的产品和服务转移到...

2796
来自专栏AI科技评论

真正从零开始,TensorFlow详细图文安装入门教程!

GAIR 今年夏天,雷锋网将在深圳举办一场盛况空前的“全球人工智能与机器人创新大会”(简称GAIR)。大会现场,谷歌,DeepMind,Uber,微软等巨头的人...

39814
来自专栏纯洁的微笑

微服务架构—服务降级

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

1542
来自专栏数据和云

实践实战:在PoC中的Oracle 12c优化器参数推荐

最近,Oracle数据库优化器的产品经理 Nigel Bayliss 发布了一篇文档,介绍:Setting up the Oracle Optimizer fo...

964
来自专栏FreeBuf

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

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

2946
来自专栏大数据架构

超大规模 Spark 集群灰度发布 CI CD

目前主流的代码管理工具有,Github、Gitlab等。本文所介绍的内容中,所有代码均托管于私有的 Gitlab 中。

2103
来自专栏散尽浮华

Linux系统下CPU使用(load average)梳理

在平时的运维工作中,当一台服务器的性能出现问题时,通常会去看当前的CPU使用情况,尤其是看下CPU的负载情况(load average)。对一般的系统来说,根据...

7116
来自专栏FreeBuf

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

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

2308
来自专栏恰同学骚年

谈谈对于企业级系统架构的理解

在我们刚开始学习架构的时候,首先会想到分层的概念,分层架构比较经典的是三层架构,那么,什么是三层架构呢?它包括表现层,业务层,数据访问层;而对于一个新手来说,从...

1192

扫码关注云+社区