Undocumented Oracle PreparedStatement optimization

We just get a 20% response time gain on a 600+ lines query under Oracle. Our DBA noticed that queries were faster when launched from SQLDeveloper than from our JEE application using the JDBC Oracler 11g driver. We looked at the queries as they actually arrived to the Oracle engine and they where under the form : SELECT... WHERE col1 like ':myvar1' OR col2 LIKE ':myvar2' AND col3 IN (:item1,:myvar2,...) and not 'SELECT... WHERE col1 LIKE ':1' OR col2 LIKE ':2' AND col3 IN (:3,:4,...) like usual when using PreparedStatement the regular way.

Indeed, every PreparedStatement documentation I'm aware of, beginning with the one from Sun states that we have to use '?' to represent bind variables in queries. These '?' are replaced by ':1', ':2', '3' ... by the JDBC driver. So the database has no way to now in our case that :2 and :4 have the same value. This information is lost.

We discovered that we can use PrepareStatement by providing queries with named bind variables instead of '?'. Of course, we still have to set the right value using the setXXX(int position,value) setters for every bind variable occurrence in the query. Then, queries arrive to Oracle like when using SQLDeveloper, with named bind variables.

OK but what's the deal with all this ?

I'm not sure but I think that this optimization may allow Oracle optimizer to be cleverer, especially for queries with redundant parts. It is especially good for queries with duplicated sub SELECT with IN condition containing all the same list of items. Maybe Oracle create on-the fly WITH clauses or similar optimizations in this case ?

Note that this optimization may only work with Oracle and is probably only useful for very large or redundant queries. I don't recommend it in most cases. AFAIK, neither Hibernate nor Spring-JDBC implements this optimization.