Oracle数据库SQL查询无结果且执行缓慢问题求助

这种情况我在处理Oracle性能问题时碰到过好几次，咱们一步步拆解排查，应该能快速解决：

1. 先解决执行计划生成失败的问题 #

你提到查看执行计划时出现错误，这本身就是个关键信号——正常情况下哪怕查询跑得慢，执行计划也能正常生成。这个错误大概率和数据字典异常或者表/索引的统计信息失效有关，先把这个基础问题搞定：

• 重新收集目标表的全量统计信息，这是Oracle性能调优的常规操作：

  exec dbms_stats.gather_table_stats(ownname => 'SIEBEL', tabname => 'S_SRV_REQ', estimate_percent => dbms_stats.auto_sample_size, cascade => true);
  

  执行完后再尝试查看执行计划，应该就能正常生成了。这时候你就能清楚看到Oracle是选择走created的索引，还是全表扫描，以及那两个过滤条件对执行路径的影响。

2. 分析两个过滤条件导致慢查的原因 #

去掉X_MBL_AREA_LIC is not null和sr_cat_type_cd <> 'Trouble Ticket'后查询正常，说明这两个条件让Oracle的执行计划出现了偏差，可能的原因有这些：

• 列统计信息缺失/失效：Oracle如果不知道X_MBL_AREA_LIC的非空值占比、sr_cat_type_cd中等于'Trouble Ticket'的记录数，就会选错执行路径。刚才的全表统计应该能覆盖这个，如果还是不行，可以单独收集这两个列的统计：

  exec dbms_stats.gather_table_stats(ownname => 'SIEBEL', tabname => 'S_SRV_REQ', colname => 'X_MBL_AREA_LIC,sr_cat_type_cd', estimate_percent => dbms_stats.auto_sample_size);
  

• trunc(created)导致索引失效：划重点！你用了trunc(created)函数包装索引列，Oracle默认不会走这个单列索引！测试环境数据量小，全表扫描也能快速返回，但生产环境数据量大就直接卡住了。解决办法有两个：
  • 改写查询条件，避免用函数：把日期条件改成created >= to_date('25-Sep-2017', 'DD-Mon-YYYY') and created < to_date('26-Sep-2017', 'DD-Mon-YYYY')，这样就能直接用到created的索引了。
  • 创建函数基于索引：如果必须保留trunc(created)的写法，可以创建专门的索引：

    create index idx_s_srv_req_trunc_created on siebel.s_srv_req(trunc(created));
    

• 缺少复合索引：如果结合日期和两个过滤条件后，需要过滤大量数据，可以考虑创建复合索引来优化：

  create index idx_s_srv_req_created_cat_area on siebel.s_srv_req(created, sr_cat_type_cd, X_MBL_AREA_LIC);
  

  注意创建索引前要评估表的大小和日常DML频率，避免影响业务写入性能。

3. 对比测试环境与生产环境的差异 #

既然测试环境正常，一定要排查两者的差异：

• 生产环境2017-09-25这天的数据量是不是远大于测试环境？
• 生产环境中X_MBL_AREA_LIC非空记录、sr_cat_type_cd <> 'Trouble Ticket'的记录占比，是不是和测试环境完全不同？比如生产环境里符合条件的记录几乎是全表，导致Oracle选错全表扫描。
• 数据库参数差异：比如optimizer_mode（是all_rows还是first_rows）、optimizer_index_cost_adj等参数，会不会影响执行计划的选择？

4. 临时应急方案 #

如果现在需要快速让查询跑起来，可以用索引提示强制Oracle走created的索引（替换成你实际的索引名）：

select /*+ index(s_srv_req 你的created列索引名) */ count(*)
from siebel.s_srv_req
where trunc(created)>='25-Sep-2017'
and trunc(created)<='25-Sep-2017'
and X_MBL_AREA_LIC is not null
and sr_cat_type_cd <> 'Trouble Ticket';

内容的提问来源于stack exchange，提问作者Amal