在数据库巡检中发现一个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对系统逻辑与业务流程的熟悉过程,掌握前两者优化再加上一点耐心方可游刃有余