We are currently facing a problem conserning the way hibernate generates sql for our hql queries and the way the database manages these queries.
When a list is binded
Code:
queryStr.append(" AND doc.idDms IN (:ids_dms)");
query.setParameterList(ids_dms, myList);
The sql generated is pretty much like
Code:
...iddms in (?,?,?,?,?....)
The number of binded values corresponds to the number of elements in the list.
When using Oracle, the shared pool is filled with query with exactly de same structure but
with a different number of paramaters. From an Oracle point of view these queries are all different.
As a concequence we have:
-hard parsing of all the query and no use of cached information (execution plans, optimizations...)
-shared pool memory increase since multiple version of the same query and additionnal information are stored
My question is there:
-Is there a solution to avoid what is a problem from database point of view?
-Is there best practice concerning the way lists should be managed so that it does not become a problem from database point of view?
I found some information about a solution using jdbc, but nothing with hibernate
http://knol.google.com/k/oracle-passing ... d-variable
Login
FAQ
Search