java - Hibernate 参数化 sql 查询缓慢且活跃的 oracle session

Question

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

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

在 Spring Java + 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 Descendent 上创建函数索引以提高性能。

一开始以为是session/connection pool没有被正确管理，所以改成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;

没有解决。查询运行几次正常，但由于某些未知原因，并且使用之前已成功运行的值，挂起，使 session 在 Oracle 中打开(状态= Activity )并因超时而失败。在任何 SQL 客户端上针对 Oracle 运行相同的查询，并使用所有可能的参数组合执行数十次，以极高的性能执行，大约 400 毫秒，一次处理 10 条记录。

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

我怀疑 Hibernate 使用的 QueryPlan 很糟糕，因此决定删除所有使用绑定(bind)参数的过滤器，但也没有解决，尽管它稍微好一点。移动到第 1、2、3、4 页等其他页面时挂了一会儿，......

毕竟，我怀疑Hibernate的方法生成的SQL

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

因为在日志中看到它们作为绑定(bind)参数传递给 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 绑定(bind)参数并实现自定义计算分页并编写所有 SQL 来检索页面行并且它正在运行并正确管理数据库 session 。

那么，我的问题是: hibernate 在阻止查询在针对数据库运行时运行的场景中做了什么？绑定(bind)参数查询是否存在任何已知问题？

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

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

Hibernate 4.2.15 最终版

该表在开发数据库中有大约 30 万条记录(生产中有 1.5 万条记录)，一次显示 10、20、50 条记录的页面，按主键降序排序(生成的序列)

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

Answer 1

我不知道这是否是您的问题，但 Oracle 在解析查询时会查看绑定(bind)变量值，然后保存查询计划以供将来执行，因此它不必在每次运行时都继续解析查询使用一组新的绑定(bind)变量。但是每隔一段时间就会重新解析查询。如果在解析过程中碰巧传递了一些不寻常的绑定(bind)变量值，则会存储和使用错误的计划。这是一种绑定(bind)变量的诅咒。它们减少了解析，但可以在再次解析查询时根据非典型的绑定(bind)变量值翻转计划。提示可以提供帮助。我们使用 SQL 配置文件来锁定带有绑定(bind)变量的查询计划，这些变量往往会改变计划。有时您可以自定义收集优化器统计信息的时间和方式，以便无论将什么值传递到绑定(bind)变量都可以创建一个好的计划。

无论如何，这是我一直看到的事情，可能是也可能不是你的问题。

鲍比