微信号:OraNews

介绍:分享数据库技术、新闻与信息,尤其是和Oracle数据库相关的内容,文章内容来自原创、专栏作者投稿或读者投稿.

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

2017-05-11 06:46 张洪涛

编辑手记:懂业务,懂系统逻辑,你才能做一个更好的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_TypeSN,可直接跳过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.



本文来自云和恩墨大讲堂朋友张洪涛先生的投稿,在此表示感谢。

富士康 数据库工程师  张洪涛

福利

恩墨学院上次推出的Spark课程将于5月13日正式开课,欢迎广大朋友报名一起提升!另:今天看到这篇文章的同学有福啦!

现在报名立即送出《Spark大数据分析实战》一本,想要的同学快点报名哟!报名后联系云和恩墨小助手(sunx5126)领取书籍。数量有限,先到先得。

报名请点击以下链接:(原文链接直达)

https://jinshuju.net/f/t5Fvf9


加入"云和恩墨大讲堂"微信群,参与讨论学习

搜索 盖国强(Eygle)微信号:eyygle,或者扫描下面二维码,备注:云和恩墨大讲堂,即可入群。每周与千人共享免费技术分享,与讲师在线讨论。


关注公众号,获得后续精彩分享

 
数据和云 更多文章 SQL 使用like &#39;%ABC&#39; 和 like&nbs oracle 12.2 local temp的原理和实现 极致之处,精彩无限 - 优化了一半的SQL YH5:Extended RAC 双活解决方案 YH1:Oracle Data Guard知识库
猜您喜欢 9点1氪:意大利科学家发明未来之镜;特斯拉 Model 3 将于3月31日生产,定价低达3.5万美元 依赖管理工具漫谈--从Maven,Gradle到Go 今晚公开课:精准测试--搜狗输入法覆盖率工具介绍 用graphite diamond做监控 流利说@WWDC 2016