Hi,

I have a problem with Oracle performance when using jdbc driver for version 10 or 11. My Oracle version is 10.2.0.3. Generally the case is as follows:
I have a server that performs all Hibernate operations and a client application (using Tomcat) that calls some functions from that server. The problematic request is a query on 2 tables (given as sql generated by Hibernate):

select distinct "5 columns from both tables"
from A, B
where
A.B_ID=B.ID and (A.E_ID in ("950 identifiers")) and B.L_ID=2 and (
B.AT_ID in ("23 identifiers"));

Table A has 550 000 rows and table B has 130 000 rows. When I use jdbc driver for Oracle version 9.x such a query completes in about half a second. The problem arises when I change the jdbc driver to 10.2.0.3 or any other for Oracle 10. Then the mentioned query completes in about 600 seconds which is about 1200 times slower. The only difference between these configurations is a different jdbc driver. The problem is that when I use the newer driver CPU usage on Oracle machine rises to 100%.

Can somebody help me with this problem? Maybe someone had similar situation?

Hibernate version:

3.1.3 / 3.2.6 no difference after changing Hibernate version

hibernate.cfg.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
"conf/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
<session-factory>
<!--### Query Language ###-->
<!--define query language constants / function names-->
<property name="hibernate.query.substitutions">true 1, false 0, yes
'Y', no 'N'</property>

<property name="hibernate.show_sql">false</property>

<!--### C3P0 Connection Pool###-->
<property name="hibernate.c3p0.acquire_increment">1</property>
<property name="hibernate.c3p0.idle_test_period">200</property> <!-- seconds -->
<property name="hibernate.c3p0.max_size">100</property>
<property name="hibernate.c3p0.max_statements">2000</property>
<property name="hibernate.c3p0.min_size">5</property>
<property name="hibernate.c3p0.timeout">100</property> <!-- seconds -->

<!--### Miscellaneous Settings ###-->
<!--set the maximum JDBC 2 batch size
(a nonzero value enables batching)-->
<property name="hibernate.jdbc.batch_size">0</property>
<!--use JDBC batching for versioned data-->
<property name="hibernate.jdbc.batch_versioned_data">true</property>
<!--use streams when writing binary types to / from JDBC-->
<property name="hibernate.jdbc.use_streams_for_binary">true</property>
<!--set the maximum depth of the outer join fetch tree-->
<property name="hibernate.max_fetch_depth">1</property>
<!--enable CGLIB reflection optimizer (enabled by default)-->
<property name="hibernate.bytecode.use_reflection_optimizer">
false</property>

<property name="hibernate.jdbc.wrap_result_sets">true</property>

<!--use factory for new query translator-->
<property name="hibernate.query.factory_class">
org.hibernate.hql.ast.ASTQueryTranslatorFactory</property>

<!--### Second-level Cache ###
-->
<!--set a prefix for cache region names-->
<property name="hibernate.cache.region_prefix">
hibernate.dlibra.server</property>
<!--enable the query cache-->
<property name="hibernate.cache.use_query_cache">true</property>
<!--choose a cache implementation-->
<property name="hibernate.cache.provider_class">
org.hibernate.cache.EhCacheProvider</property>
</session-factory>
</hibernate-configuration>