Hibernate参数化sql查询慢和活动oracle会话

Question

几天来，我一直在为针对Oracle数据库查询hibernate而苦苦挣扎。类似这样的东西被用来将记录提供给网格。

SELECT 
    fields
FROM 
    tables and JoinedTables
WHERE
        Field1  >= :value1
    AND Field2 = :value2
    AND Field3 = :value3
Order By MaintTable.Id Desc

在Spring + Hibernate 4.2方法中使用此方法。

SQLQuery query = (SQLQuery) session.createSQLQuery(querySql)
                                .addEntity(CertificateViewEnt.class)
                                .setParameter("value1", firstCertificateRecordDate)
                                .setParameter("value2", certType.toUpperCase())
                                .setParameter("value3", deleted? 1:0);      

每个筛选的字段都被正确地编入索引，并在Maintable.Id后代上创建一个函数索引，以提高性能。

起初，我认为是会话/连接池没有得到正确的管理，所以我更改为StatelessSession并添加了这个session.close()：

query.setCacheable(false)
              .setTimeout(30)
              .setReadOnly(true);
...
...
//Pagination
query.setMaxResults(rows);
query.setFirstResult(HelperMethod(page, rows));

result = (List<CertificateViewEnt>) query.list(); 
session.close();
return result;

它没有解决这个问题。查询运行了几次ok，但由于一些未知的原因，使用以前已经成功运行的值，挂起，离开在甲骨文(status=ACTIVE)中打开的会话，并在超时时失败。在任何SQL客户端上对Oracle运行相同的查询，在所有可能的params组合中运行数十次，每次执行性能极高的查询(大约400 at )，每次执行10条记录。

在阅读了一些文章之后，Link1 [Slow performance on Hibernate + Java but fast when I use TOAD with the same native Oracle query Link2：[query hangs oracle 10g ]

我认为Hibernate会使用糟糕的QueryPlan，并决定使用绑定参数删除所有过滤器，而且也没有解决问题，尽管它稍微好了一点。一段时间后，移动到其他页面时挂起，如第1页、第2页、第3页、第4页、…页

在这之后，我怀疑Hibernate的方法生成的SQL

query.setMaxResults(rows)
query.setFirstResult(SomeHelperMethod(page, rows));

因为在日志中看到它们作为绑定参数传递给Oracle。

       ...
       Order By Certificado.Id Desc ) row_
       where rownum <= ?)
  where rownum_ > ?

我在追踪日志上也看到了这个

2015-09-15 14:09:53 TRACE QueryPlanCache:200 - Located native-sql query plan in cache (SELECT /*+ INDEX(

这是：

2015-09-15 14:09:53 TRACE BasicBinder:84 - binding parameter [2] as [VARCHAR] - E
2015-09-15 14:09:53 DEBUG Loader:2031 - bindNamedParameters() 0 -> deleted [3]
2015-09-15 14:09:53 TRACE BasicBinder:84 - binding parameter [3] as [INTEGER] - 0
2015-09-15 14:09:53 TRACE Loader:1931 - Bound [7] parameters total
/*
SLOW here !!!  Around 3 secs when query runs in ~0,300 secs via SQL client.
And ACTIVE sessions are left running in Oracle.
*/
2015-09-15 14:09:56 TRACE JdbcCoordinatorImpl:397 - Registering result set [org.apache.commons.dbcp.DelegatingResultSet@f0c620]
2015-09-15 14:09:56 TRACE Loader:943 - Processing result set

最后，我不得不放弃所有Hibernate绑定参数，实现自定义计算分页，并编写了所有SQL来检索页面行，并且它正在正确运行和管理db会话。

所以，我的问题是：，，hibernate是在什么情况下阻止查询在数据库上运行？绑定参数查询是否存在已知的问题？

当我有绑定参数时，我真的不喜欢编写所有的SQL代码并强制硬解析这个SQL。

关于环境的一些注意事项: Tomcat和Oracle在同一个主机上。因此，网络连接不是问题所在。

Hibernate版本4.2.15最终

该表在dev数据库中有大约300 k recs (在生产中有1，5m)，一次显示10，20，50 recs的页面，按主键Desc (生成的序列)排序。

希望一些Hibernate专家能在这方面帮助我，这样我仍然可以信任大型数据库项目上的Hibernate查询。提前谢谢。

Answer 1

我不知道这是否是您的问题，但是Oracle在解析查询时会查看bind变量值，然后保存查询计划以供将来执行，这样每次使用一组新的绑定变量运行查询时，它都不必继续解析查询。但是每隔一段时间，查询就会被重新解析。如果在解析过程中碰巧传递了一些不寻常的绑定变量值，则存储和使用一个错误的计划。这是绑定变量的诅咒。它们减少了解析，但当再次解析查询时，它们可以在非典型绑定变量值上翻转计划。暗示会有帮助。我们使用SQL锁定具有倾向于更改计划的绑定变量的查询计划。有时，您可以自定义何时以及如何收集优化器统计信息，以便创建一个好的计划，而不管向绑定变量传递哪些值。

不管怎么说，这是我一直看到的，可能是，也可能不是你的问题。

鲍比