Slow performance on Hibernate + Java but fast when I use TOAD with the same native Oracle query

Question

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

Answer 1

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.