Azure环境下Db2结合Hibernate的查询性能问题排查请求

问题场景：
一条多表左连接的复杂SQL，在Db2编辑器（Squirrel）中执行速度很快，但通过应用执行时耗时长达3分钟。SQL语句如下：

select * from CPP.ItemSearchField itemsearch0_ 
left outer join CPP.Item item1_ on itemsearch0_.id=item1_.id 
left outer join CPP.Category category2_ on item1_.category_id=category2_.id 
left outer join CPP.SecurityGroup securitygr3_ on category2_.securityGroupId=securitygr3_.id 
left outer join CPP.SecurityGroup securitygr4_ on securitygr3_.parentId=securitygr4_.id 
left outer join CPP.ItemClassifier itemclassi5_ on item1_.itemClassifier_id=itemclassi5_.id 
left outer join CPP.Person person6_ on itemclassi5_.owner_id=person6_.id 
left outer join CPP.Account account7_ on person6_.id=account7_.id 
left outer join CPP.Person person8_ on account7_.id=person8_.id 
left outer join CPP.ItemClassifier itemclassi9_ on itemclassi5_.parentId=itemclassi9_.id 
left outer join CPP.ItemGroup itemgroup10_ on item1_.ItemGroupFK=itemgroup10_.id 
left outer join CPP.ReportingPeriod reportingp11_ on item1_.reportingPeriod_id=reportingp11_.id 
left outer join CPP.ReportingPeriodType reportingp12_ on reportingp11_.reportingPeriodTypeId=reportingp12_.id 
left outer join CPP.Attachment attachment13_ on item1_.id=attachment13_.itemFk 
where itemsearch0_.id=?

核心排查方向与解决方法 #

• 对比执行计划：编辑器和应用生成的执行计划可能不同。在应用中开启Db2执行计划日志，将其与Squirrel中生成的执行计划对比，重点检查itemsearch0_.id=?参数绑定是否导致索引失效，或优化器选择了低效的连接方式。
• 检查连接属性差异：
  • 隔离级别：应用可能使用了更严格的隔离级别（如可重复读），导致锁等待或额外资源消耗，可尝试调整为读已提交。
  • 预编译设置：编辑器可能启用了预编译优化，而应用未开启，需确保应用连接配置中开启预编译选项。
• 优化结果集处理：
  • 避免select *，只查询业务需要的字段，减少数据传输量和后续对象转换开销。
  • 若使用ORM框架，检查是否存在不必要的关联加载，可改为懒加载或手动控制关联查询。
• 更新数据库统计信息：Db2优化器依赖最新的统计信息生成高效计划，执行以下命令更新关联表的统计信息：

  RUNSTATS ON TABLE CPP.ItemSearchField AND INDEXES ALL;
  RUNSTATS ON TABLE CPP.Item AND INDEXES ALL;
  -- 对所有关联表执行上述命令
  

• 排查连接池配置：检查应用的数据库连接池参数，比如最大连接数是否不足、空闲连接超时是否合理，避免因等待连接导致的耗时。
• 直接执行原生SQL：如果应用使用ORM框架，尝试在应用中直接执行原生SQL，对比耗时，排查是否是ORM框架的额外处理导致性能问题。

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