优化案例4：绑定变量引发性能问题

DM技术交流QQ群：940124259

1. 问题描述

遇到一个极其简单的单表排序查询SQL，已经优化一阵子，该有的索引已经补建，何啥性能仍得不到解决？（依旧执行时间4秒多）
这时候不能马虎，得深入分析问题，跟踪它真实的执行计划，试着从SQL日志和SQL缓存中寻找线索。
从SQL日志筛选出来，执行时间平均4秒多，很消耗整体数据库的资源，因为它执行地非常高频。
询问该单位的开发人员，才知道这个功能是短信通知推送，每隔5秒进行一次查询，将未处理的消息推送出去。
通过观察SQL特点带有绑定变量falg=?，结合实际功能含义，则大多数时候（99%）传值’0’， 表示未处理的状态值，再查看唯一键值0的分布很少（0-80条），有索引的情况下不应该这么慢。
接下来进入分析和解决过程环节。

---

2. 解决方法

2.1 SQL日志发掘

起先怀疑类型隐式转换引起走不了索引，特地从SQL日志中去看参数类型绑定，没啥异常。

2.2 SQL缓存计划

从v$sql_plan动态视图里头捞出计划地址，再dump事件打印真实计划，再次确认应用程序跑的SQL执行计划是否正确。

 -- 先找出当前库中缓存的目标SQL的SQL_ID序号
  select * from v$sqltext where cmd_type= 'SELECT' and sql_text like '%falg=?%' ;

从图中可知sql_id = 1 就是目标SQL，侧面说明这个SQL在全库最为明显的高频SQL（n_exec = 811101执行次数）。

-- 通过sql_id搞到执行计划的地址
select * from v$sql_plan where sql_id = 1;

-- plndump事件dump当前正在运行的真实计划
select to number(0x00007F947200E78，XXXXXXXXXXXXXXXXXXXXXXXXXXX'); -- 0x00007F947200E78 --> 十进制 140365019483768
alter session set events 'inmediate trace name pindump level 140365019483768' ; -- dump计划从跟踪文件中查看（数据库服务器trace目录）

看到计划估算出来的行数，“不信”，专门去执行一遍看看，falg=‘0’ 数据分布少得可怜，明显估算错误。

尝试重新收集下索引和这两列的统计信息，再查看执行计划。

calldbms stats.gather table stats (
'CQ', 'ISHB MESSAGE INFO',  null,
method opt=>'for columns falg,create time size auto',
cascade=>true,
estimate_percent=>100
);

stat 100 on index CQ.IDX_C_SHB_MESSAGE_INFO_FALG_CREATE_TIME;

统计信息仍然无解（记住：带falg=?查看执行计划，另直接替换成falg=‘0’），细心的人可能会发现SLCT2估算出来的数字接近于(~1135867 / 2 = 567933.5)，竟然给和谐平均计算。

2.3 HINT注入

HINT注入实属无奈之举，本想喊开发人员把绑定变量改成常量字面写法，但他找不对源代码位置，才会选择这一方法。
其实还有一招的，已经正式运行环境，没法修改参数直接重启。

HINT注入使用，请参考《DM8 SQL使用手册》附录 3 系统存储过程和函数 17小节

SF_INJECT_HINT(
'select shbsmsmsgi0_.id as id77_, shbsmsmsgi0_.area_code as area2_77_, shbsmsmsgi0_.create_time as create3_77_, shbsmsmsgi0_.create_user_id as create4_77_, shbsmsmsgi0_.create_user_name as create5_77_, shbsmsmsgi0_.data_type as data6_77_, shbsmsmsgi0_.falg as falg77_, shbsmsmsgi0_.mobiles as mobiles77_, shbsmsmsgi0_.smscontent as smscontent77_, shbsmsmsgi0_.update_time as update10_77_, shbsmsmsgi0_.update_user_id as update11_77_, shbsmsmsgi0_.update_user_name as update12_77_, shbsmsmsgi0_.VERSION as VERSION77_ from SHB_MESSAGE_INFO shbsmsmsgi0_ where shbsmsmsgi0_.falg=? order by shbsmsmsgi0_.create_time',
'INDEX(shbsmsmsgi0_ IDX_C_SHB_MESSAGE_INFO_FALG_CREATE_TIME)',
'FALG_ADJUST_INDEX','FALG_ADJUST_INDEX',
    TRUE        
);

hint注入index提示，强制指定索引后，该SQL立马变快，已经缩短到1秒以内，执行计划也变成预期计划。

查看SQL日志，发现SQL已经能在200毫秒左右返回结果，已经大大解决每间隔5秒的执行效率，避免SQL执行任务堆积，提高TPS。

---

3. 问题总结

SQL放在管理工具执行，并且跨了网络，但执行耗时极短（10毫秒内），SQL日志记录应用程序访问消耗时间200毫秒，多出20倍的时间，推测应用程序那边jdbc或者代码逻辑写得有些问题，遗留问题暂时交给开发人员。
原来的老版本DM7 ，关于绑定变量窥探属于静态参数，修改后必须重启生效，莫得法在白天运行期间直接改了重启。
奉劝伙伴们选用DM8新版本，别再继续使用老古董，遇到类似绑定变量引发的性能问题的SQL小心一点，实在不行，你们改成常量SQL吧。