Hibernate parameterized sql query slow and active oracle sessions

Question

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.

Answer 1

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