Hibernate参数化的sql查询缓慢且活跃的oracle会话 [英] Hibernate parameterized sql query slow and active oracle sessions

问题描述

我一直在用针对Oracle数据库的休眠查询来挣扎好几天。
这样的东西就是用来向网格提供记录的。

  SELECT 
字段
 FROM 
表和连接表
 WHERE 
 Field1> =：value1 
 AND Field2 =：value2 
 AND Field3 =：value3 
 Order By MaintTable .Id Desc 
  

在Spring Java + Hibernate 4.2方法中使用这种方法。
$ b

  SQLQuery query =（SQLQuery）session.createSQLQuery（querySql）
 .addEntity（CertificateViewEnt.class）
 .setParameter（value1 ，firstCertificateRecordDate）
 .setParameter（value2，certType.toUpperCase（））
 .setParameter（value3，deleted？1：0）; 
  

每个过滤字段都被正确编制索引并创建一个Maintable.Id后代的函数索引来提高性能。 / p>

起初我以为会话/连接池没有正确管理，所以我改为 StatelessSession 并添加session.close（）：

  query.setCacheable（false）
 .setTimeout（30）
 .setReadOnly（true）; 
 ... 
 ... 
 //分页
 query.setMaxResults（rows）; 
 query.setFirstResult（HelperMethod（page，rows））; 
 
 result =（List< CertificateViewEnt>）query.list（）; 
 session.close（）; 
返回结果; 
  

它没有解决它。
查询运行好几次，但由于某些未知原因，并且使用先前已成功运行的值挂起，会话在Oracle中保持打开状态（status = ACTIVE），并在超时时失败。
在任何SQL客户端上针对Oracle运行的相同查询，以及所有可能的params组合的数十次执行，性能极高，大约400ms，一次处理10条记录。

阅读了一些文章后，
Link1 [
Link2：[>查询挂起oracle 10g

我怀疑不好的QueryPlan被Hibernate使用，并决定使用绑定参数删除所有过滤器，但也没有解决，尽管它稍微好一点。过了一会儿，当转移到其他页面（如第1页，2,3,4，...）时，它们被挂起。

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

  query.setMaxResults（rows）
 query.setFirstResult（SomeHelperMethod（page，rows））; 
  

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

  ... 
 Order By Certificado.Id Desc）row_ 
其中rownum <=？）
其中rownum_ > ？ 
  

我也在跟踪日志中看到了这一点

  2015-09-15 14:09:53 TRACE QueryPlanCache：200  - 位于缓存中的原生sql查询计划（SELECT / * + INDEX（
  
 
 
 
以及： 
 
 
  2015-09 -15 14:09:53 TRACE BasicBinder：84  - 绑定参数[2]为[VARCHAR]  -  E 
 2015-09-15 14:09:53 DEBUG加载器：2031  -  bindNamedParameters（）0  - >已删除[3] 
 2015-09-15 14:09:53 TRACE BasicBinder：84  - 绑定参数[3]为[INTEGER]  -  0 
 2015-09-15 14:09:53 TRACE加载程序： 1931  - 绑定[7]参数总计
 / * 
在这里慢!!!查询在~0,300秒内通过SQL客户端运行时大约需要3秒
并且ACTIVE会话仍在Oracle中运行。 
 * / 
 2015-09-15 14:09:56 TRACE JdbcCoordinatorImpl：397  - 注册结果集[org.apache.commons.dbcp.DelegatingResultSet@f0c620] 
 2015-09-15 14:09:56 TRACE加载程序：943  - 处理结果集
  
最后，我不得不ab并在所有Hibernate绑定参数并实现自定义计算分页并编写所有SQL来检索页面行，并正确运行和管理数据库会话。
所以，我的问题是：            
是否有任何已知的绑定参数查询问题？
 
 
 
我不太想写所有的SQL代码并强制解析这个SQL，当我绑定参数。
 
 
有关环境的一些说明：
 Tomcat和Oracle位于同一主机上。因此，网络连接不是问题。
 
  Hibernate 4.2.15 final  
 
 
该表有大约300k recs in dev数据库（生产量为1,5M），每次显示10,20,50个recs页面，按主键Desc排序（生成序列）
 
 
希望一些Hibernate专家可以帮助我，这样我仍然可以信任大型数据库项目上的Hibernate查询。 
在此先感谢。
解决方案
我不知道这是你的问题，但Oracle窥视绑定变量解析查询时的值，然后保存查询计划以备将来执行，以便每次使用一组新的绑定变量运行查询时不必继续解析查询。但是每隔一段时间查询都会被重新解析。如果在解析过程中碰巧传递了一些不常见的绑定变量值，那么就会存储并使用一个不合理的计划。这是绑定变量的诅咒。它们减少了解析，但是当再次分析查询时，可以针对非典型绑定变量值翻转计划。提示可以提供帮助。我们使用SQL配置文件来锁定查询计划，并使用绑定变量来改变计划。有时，您可以自定义何时以及如何收集优化器统计信息，以便创建一个良好的计划，而不管传递给绑定变量的值是什么。
 
 
 
无论如何，所有的时间，可能会或可能不会是你的问题。 
 
  Bobby 
 
I've been struggling for several days with a query on hibernate against an Oracle database.
Something like this that is is used to feed records to a grid.
SELECT 
    fields
FROM 
    tables and JoinedTables
WHERE
        Field1  >= :value1
    AND Field2 = :value2
    AND Field3 = :value3
Order By MaintTable.Id Desc
Using this approach in a Spring Java + Hibernate 4.2 method.
SQLQuery query = (SQLQuery) session.createSQLQuery(querySql)
                                .addEntity(CertificateViewEnt.class)
                                .setParameter("value1", firstCertificateRecordDate)
                                .setParameter("value2", certType.toUpperCase())
                                .setParameter("value3", deleted? 1:0);      
Every filtered field is correctly indexed and created an Function Index on Maintable.Id Descendent to improve performance.

At first I thought it was session/connection pool not being correctly managed, so I changed to StatelessSession and add this 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;
It didn't solved it.
Query runs a couples of times ok, but for some unknown reason, and using values that were already run previously with success, hangs, leaves session opened in Oracle (status=ACTIVE) and fails on timeout.
The same query being run against Oracle on any SQL client and dozens of times with all possible combinations of params executes with extreme performance, around 400ms, for 10 records at a time.

After reading some articles here and there, 
Link1 [Slow performance on Hibernate + Java but fast when I use TOAD with the same native Oracle query
Link2: [query hangs oracle 10g

I supected of poorly QueryPlan being used by Hibernate and decided to remove all filters using bound parameters and also didn't solved, though it was a little bit better. After a while hanged when moving to other pages like Page 1, 2,3,4, …

After all that, I suspected of the SQL generated by Hibernate's methods 
query.setMaxResults(rows)
query.setFirstResult(SomeHelperMethod(page, rows));
Because saw in log that they were being passed as bind parameters to Oracle.
       ...
       Order By Certificado.Id Desc ) row_
       where rownum <= ?)
  where rownum_ > ?
I also saw this in the Trace Log
2015-09-15 14:09:53 TRACE QueryPlanCache:200 - Located native-sql query plan in cache (SELECT /*+ INDEX(
and this:
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
Finally I had to abandon all Hibernate bind params and implemented custom calculation pagination and wrote all SQL to retrieve the page rows and it's running and managing db sessions correctly.

So, My Question is: 
What Is hibernate doing behing the scenes that prevents the query to run as it runs against the database ?
Is there any known problem with bind parameter queries ?

I don't really like to be writing all the SQL code and forcing hard-parsing this SQL, when I have bind parameters.

Some notes on environment:
Tomcat and Oracle are on the same host. So network connection is not the problem

Hibernate version 4.2.15 final

The table has around 300k recs in dev database(1,5M on Production) and shows pages of 10, 20, 50 recs at a time, sorted by Primary Key Desc (Sequence generated)

Hope some Hibernate experts can help me on this so that I can still trust Hibernate queries on large database projects.
Thanks in advance.
解决方案
I don't know if this is your issue, but Oracle peeks at bind variable values when parsing a query and then saves the query plan for future executions so it doesn't have to keep parsing the query each time it is run with a new set of bind variables. But every once and a while the query is re-parsed. If some unusual bind variable values happen to be passed during a parse then a bad plan is stored and used. It is kind of the curse of bind variables. They reduce parsing but can flip the plan around on atypical bind variable values when queries are parsed again. Hints can help. We use SQL Profiles to lock in plans of queries with bind variables that tend to change plans. Sometimes you can customize when and how optimizer statistics are gathered so that a good plan is created regardless of what values are passed into the bind variables.

Anyway, its something I see all the time and may or may not be your issue.

Bobby

                        
这篇关于Hibernate参数化的sql查询缓慢且活跃的oracle会话的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！