一个不懂业务的DBA不是好的DBA

编辑手记:懂业务,懂系统逻辑,你才能做一个更好的DBA。

在数据库巡检中发现一个MES生产信息数据库中一个存储过程中一条SQL单次逻辑读为2100,且执行很频繁,占数据库整体逻辑读70%。SQL本意是查询特定条码在C_LABEL_DESC_T条码基本信息表中有无维护,查询结果只为1或0。

SELECTCOUNT( * ) INTO count_ll3 FROM C_LABEL_DESC_T WHERE label_type ='CARTON' ANDLENGTH(START_BARCODE)=LENGTH(DATA) AND START_BARCODE <=DATA AND END_BARCODE>=DATA ANDROWNUM<2 AND model_name LIKE'%-W';

SQL执行计划的ACCESS Predicate使用了 END_BARCODE>=:DATA条件,END_BARCODE为VARCHAR2类型,对其进行>=范围查询效率不佳才产生了单次2100的逻辑读。

先看一下数据分布,LABEL_TYPE值为’CARTON’的记录占绝大多数,没法对LABEL_TYPE字段加索引来解决问题。CBO这条路已经走不通,只有从应用层面下手优化。

SQL> SELECT label_type, COUNT ( * ) FROM C_LABEL_DESC_T GROUP BY label_type ORDER BY 2 DESC; LABEL_TYPE COUNT(*) ----------------- ---------- CARTON 16489 SN 1161

1、可否降低执行频率?

如果可以降低存储过程执行频率1个数量级,那就是最简单的解决问题办法。

开发人员回应此条SQL在生产线的包装工站被调用,生产线上扫描每个主板SN或箱号SN时都会调用,DATA变量值即为传入的SN,存储过程执行频率无法降低。

2、第一次改写

典型生产线OLTP系统中查询历史信息概率很低,代入存储过程的的SN一般情况下都是最近几天才维护进条码规则表中。C_LABEL_DESC_T表中的WORK_DATE字段与开发人员确认就是条码规则条目的维护时间。

分析一下WORK_DATE值的数据分布:

SQL> SELECTTRUNC ( (SELECT COUNT ( * ) FROM C_LABEL_DESC_T WHEREWORK_DATE > SYSDATE - 3) / (SELECT COUNT (* ) FROM C_LABEL_DESC_T) * 100, 1) percent FROM DUAL; PERCENT ---------- 8.5

可以看出此表中绝大部分都是历史数据,最近3天维护的只占8.5%。

估计代入存储过程的SN为最近3天维护进条码规则表的概率有95%,不是最近3天维护的概率只有约5%。对DATE类型字段查询3天内数据就可利用到WORK_DATE字段索引了,应比对VARCHAR2字段进行范围查询效率更高。

依据此思路进行第一次改写存储过程如下:

/*判断此条码是否维护进最近三天维护的条码规则内*/

SELECTCOUNT( * ) INTO count_ll3 FROM C_LABEL_DESC_T WHERE label_type ='CARTON' ANDLENGTH(START_BARCODE)=LENGTH(data) AND START_BARCODE <=DATA AND END_BARCODe >=DATA ANDROWNUM<2 AND model_name LIKE'%-W' AND work_date >=SYSDATE-3;

/*如最近三天维护的条码规则中未查到,再查询3天以前数据*/

IF count_ll3 =0 THEN SELECTCOUNT( * ) INTO count_ll3 FROM C_LABEL_DESC_T WHERE label_type ='CARTON' AND LENGTH(START_BARCODE)=LENGTH(data) AND START_BARCODE <=DATA AND END_BARCODe >=DATA ANDROWNUM<2 AND model_name LIKE'%-W' AND work_date <SYSDATE-3; ENDIF;

改写后再进行观察DB整体逻辑读有降低,但下降比例只有个位数,这个存储过程的逻辑读在AWR报告中仍占Top 1,问题到底出在那里?

与开发人员沟通得知DATA变量的值虽然有可能是主板SN或箱号SN,但为主板SN的概率比箱号SN大的多(一箱会放上百片主板)。如果DATA变量值是主板SN,必全部走完以上PL/SQL块中所有逻辑,再得出count_ll3为0,我们预期中的Performance Improvement就落空了。

3、第二次改写

主板SN全部保存在R_WIP_TRACKING表中的SERIAL_NUMBER字段,并且为主键,如果我们先判断DATA变量值是否为主板SN,并对count_ll3赋值,后面的判断逻辑就不需执行。

依此思路再次进行改写:

/*先判断DATA变量值是否为主板SN*/

SELECTCOUNT( * ) INTO count_ll3 FROM R_WIP_TRACKING WHERE SHIPPING_SN =DATA;

/*如果传入值为主板SN,那即可确认对应的Label_Type为SN,可直接跳过IF块*/

IF count_ll3 =0 THEN

/*再判断此条码是否维护进最近三天维护的条码规则内*/

SELECTCOUNT( * ) INTO count_ll3 FROM C_LABEL_DESC_T WHERE label_type ='CARTON' ANDLENGTH(START_BARCODE)=LENGTH(data) AND START_BARCODE <=DATA AND END_BARCODe >=DATA ANDROWNUM<2 AND model_name LIKE'%-W' AND work_date >=SYSDATE-3;

/*如最近三天维护的条码规则中未查到,再查询3天以前数据*/

IF count_ll3 =0 THEN SELECTCOUNT( * ) INTO count_ll3 FROM C_LABEL_DESC_T WHERE label_type ='CARTON' ANDLENGTH(START_BARCODE)=LENGTH(data) AND START_BARCODE <=DATA AND END_BARCODe >=DATA ANDROWNUM<2 AND model_name LIKE'%-W' AND work_date <SYSDATE-3; ENDIF; ENDIF;

改写后,在GridControl与AWR报告中此存储过程的执行信息彻底不见,数据库整体逻辑读降低70%,问题得到圆满解决。

SQL优化的过程也是DBA对系统逻辑与业务流程的熟悉过程,掌握前两者优化再加上一点耐心方可游刃有余。

The End.

原文发布于微信公众号 - 数据和云(OraNews)

原文发表时间:2017-05-11

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏*坤的Blog

mysql生成百万级数量测试数据

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

MYSQL索引条件下推的简单测试

自MySQL 5.6开始,在索引方面有了一些改进,比如索引条件下推(Index condition pushdown,ICP),严格来说属于优化器层面的改进。 ...

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

关于收缩数据文件的尝试(r5笔记第34天)

在数据库中对于数据文件都是提前规划,不够就加的情况,很少会留意到其实有些数据文件那么大,其实条件允许也是可以收缩收缩的。 这种情况在本地测试环境中尤为突出,本来...

3436
来自专栏idba

死锁案例之三

一 前言 死锁其实是一个很有意思也很有挑战的技术问题,大概每个DBA和部分开发朋友都会在工作过程中遇见。关于死锁我会持续写一个系列的案例分析,希望能...

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

一个SQL语句引发的ORA-00600错误排查(二)(r9笔记第65天)

继昨天一个SQL语句导致的ORA-00600错误之后,我给出了背景和初步的分析结果,今天来给出我的结论,当然说明原因不是我的本意,还有反思。 首先语句类似这样的...

3477
来自专栏性能与架构

这个sql为什么没有用到索引

用户users 表中对 create_time 字段建有索引 现在查询某个时间段的用户,通过explain发现下面这个sql 没有用到索引 explain ...

3235
来自专栏java一日一条

MySQL 性能优化的最佳 20+ 条经验

今天,数据库的操作越来越成为整个应用的性能瓶颈了,这点对于Web应用尤其明显。关于数据库的性能,这并不只是DBA才需要担心的事,而这更是我 们程序员需要去关注的...

783
来自专栏java一日一条

MySQL 性能优化的最佳 20+ 条经验

今天,数据库的操作越来越成为整个应用的性能瓶颈了,这点对于Web应用尤其明显。关于数据库的性能,这并不只是DBA才需要担心的事,而这更是我 们程序员需要去关注的...

382
来自专栏帘卷西风的专栏

创建角色随机名字(mysql抽取随机记录)和mysql游标的使用

1、现在创建游戏角色的时候,基本上都是支持角色名字随机的,以前此功能在客户端用代码实现,然后向服务器请求并验证,后来发现有时候连续几次都失败,所以改成在服务器...

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

分区表的一个持续改进方案(r9笔记第53天)

今天看到一个同事发了一封邮件,是关于分区的,他说目前某个表的分区需要添加,为了保险起见,让我先添加三年的。这里折射出几个问题。 1.如果没有这位开发同学提醒,我...

2884

扫码关注云+社区