一个不懂业务的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对系统逻辑与业务流程的熟悉过程，掌握前两者优化再加上一点耐心方可游刃有余