在Hibernate + Java上性能下降，但在相同的本机Oracle查询中使用TOAD时速度很快 [英] Slow performance on Hibernate + Java but fast when I use TOAD with the same native Oracle query

问题描述

我在Oracle上发现了一个hibernate和本机查询的性能问题。当我用TOAD上的几个参数执行一个复杂的SQL查询时，我以毫秒为单位得到结果。但是，当我使用Hibernate执行相同的查询时，这个时间会大大增加（最多4秒或甚至更多）。

我的SQL查询相当复杂，返回一个唯一值（因此，问题与安装类所需的时间无关），它包含几个参数格式'：nameParameter'。该查询存储在一个字符串中。例如，

  String myNamedNativeQuery =从表格中选择count（*）+ 
where column1 =：nameParameter1 + 
和column2 =：nameParameter2; 
 //实际上我的句子要复杂得多！ 
  

当我在TOAD上执行该句子时，它会在几个毫秒内解析出来。但是在Hibernate中使用这个语句

  SQLQuery query = session.createSQLQuery（myNamedNativeQuery）; 
 
 query.setParameter（nameParameter1，value1）; 
 query.setParameter（nameParameter2，value2）; 
 
 query.uniqueResult（）; 
  

需要几秒钟才能得到相同的结果。

我意识到，如果直接在本机查询中替换参数，然后使用Hibernate执行该句子，则时间会急剧减少。它会是这样的：

  String strQuery = session.getNamedQuery（myNamedNativeQuery）。getQueryString（）; 
 
 myNamedNativeQuery = myNamedNativeQuery.replace（nameParameter1，value1）; 
 myNamedNativeQuery = myNamedNativeQuery.replace（nameParameter2，value2）; 
 
 SQLQuery query = session.createSQLQuery（myNamedNativeQuery）; 
 query.uniqueResult（）; 
  

任何人都知道发生了什么？

预先感谢。

PS：Oracle版本是9i和Hibernate 3.2

解决方案

我认为这段代码发生了什么：

  SQLQuery query = session.createSQLQuery（myNamedNativeQuery）; 
 query.setParameter（nameParameter1，value1）; 
 query.setParameter（nameParameter2，value2）; 
 query.uniqueResult（）; 
  

是这样的：

at line 1 ：查询计划是根据您的命名参数的一些预期值创建的。

在第4行：查询使用value1和value2执行，但这些值不是对于在第1行详细描述的查询计划来说，很好的值，所以数据库对实际值执行了一个非常不合适的计划，并且需要很长时间。

为什么？

查看的源代码HibernateSessionImpl.createSQLQuery（...）我发现了这行代码：

$ $ $ $ $ $ $ $ $ $ $ $ $ ，
factory.getQueryPlanCache（）。getSQLParameterMetadata（sql）
）;

调用 getQueryPlanCache（） parameterMetaData。我认为这个元数据不够好。

I've detected a performance problem with hibernate and native queries on Oracle. When I execute a complex SQL query with several parameters on TOAD I get the result in miliseconds. However, when I execute the same query using Hibernate this time is incremented hugely (up to four seconds or even more).

My SQL query is rather complex, return an unique value (so, the problem is not related with the time necessary to instation classes) and it contains several parameters with the the format ':nameParameter'. This query is stored in a String. For example,

String myNamedNativeQuery = "select count(*) from tables "+
                            "where column1 = :nameParameter1 "+ 
                            "and column2 = :nameParameter2";  
                            //actually my sentence is much more complex!!

When I execute the sentence on TOAD it is resolved in few miliseconds. But using this sentence with Hibernate

SQLQuery query = session.createSQLQuery("myNamedNativeQuery");

query.setParameter(nameParameter1, value1);
query.setParameter(nameParameter2, value2);

query.uniqueResult(); 

are necessary several seconds to get the same result.

I realized if I replaced the parameters directly on the native query and then I execute the sentence using Hibernate the time decreases drastically. It would be something like that:

String strQuery = session.getNamedQuery("myNamedNativeQuery").getQueryString();

myNamedNativeQuery = myNamedNativeQuery.replace("nameParameter1", value1);
myNamedNativeQuery = myNamedNativeQuery.replace("nameParameter2", value2);

SQLQuery query = session.createSQLQuery("myNamedNativeQuery");
query.uniqueResult(); 

Anybody knows what's happening??

Thanks in advance.

PS: The Oracle version is 9i and Hibernate 3.2

解决方案

I think what's happening with this code :

SQLQuery query = session.createSQLQuery("myNamedNativeQuery");
query.setParameter(nameParameter1, value1);
query.setParameter(nameParameter2, value2);
query.uniqueResult(); 

is this:

at line 1 : a query plan is created based on some expected values for your named parameters.

at line 4 : the query is executed with value1 and value2, but those values are not "good values" for the query plan that was elaborate at line 1 and so, the database is executing a very inappropriate plan for the actual values and it takes a lot of time.

Why ?

Looking at the source code of HibernateSessionImpl.createSQLQuery(...) I found this line of code:

SQLQueryImpl query = new SQLQueryImpl(
                sql,
                        this,
                        factory.getQueryPlanCache().getSQLParameterMetadata( sql )
        );

which is calling getQueryPlanCache() with some parameterMetaData. I assume that this metadata is not good enough.

这篇关于在Hibernate + Java上性能下降，但在相同的本机Oracle查询中使用TOAD时速度很快的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！