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

作者介绍

谢浩

现任职于云和恩墨，具有多年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访问的数据能不能少一些？”而往往这些被忽略甚至主观认为不可能的因素，却能对我们的优化工作带来很大的帮助。