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.
