I have a hibernate2.0.1 - struts1.1b- tomcat4.1.18 application that works well when it connects to a postgresql7.3 database, using the hibernate connection pool.
The same application is so slow on Oracle 8i that it is not usable. I am timing the loading of objects from Hibernate queries and found that this is were the problem lies. My objects contain lists, sets and bags, and iterating through them takes milliseconds in postgresql, seconds or even minutes with Oracle.
I am using in both cases exactly the same code everywhere, except for the hibernate.connection dialect, url, password and username details.

I checked that the raw SQL statements are executed with a reasonable speed on Oracle.

I've tried unsuccessfullly to:
- use c3p0 connection pooling
- replace cglib.jar with latest version (cglib-full-2.0-RC2)

nothing has worked so far and I would be grateful for any help.

Same data amount ?

Are the databases the same distance from the JVM? There are a million things that could cause this so I would try to boil it down to a simple test that you can run over either database. If the results are still skewed, I would use a profiler to determine exactly what is taking the extra time. If you can't profile the application, you could put in timing code but it may take a while to narrow down the bottleneck.

I ran into a similar problems quite a few times and the cause varied widely. The last time it turned out that the Coyote connector was about 7 times slower than the HTTP connector on the hardware I was running on. That was with Hiperion during a load test, which is likely to be similar to Hibernate in the way it uses resources. I would have never found this without a good profiling tool.

> Same data amount ?

yes. I am testing on identical datasets. I am getting the same results, only I have to wait half a minute on the Oracle implementation, when the response time from posgresql is immediate.

I've measured the time that is taken for individual loading of data, either through Hibernate OQL queries or iterating through a Hibernate Collection, using calls to System.currentTimeMillis() immediately before and after each statement, and found that any of these queries take between 15-50 times longer with Oracle compared with PostgreSQL.

Executing the SQL queries returned by the Hibernate log is fast.

I've also tested using plain JDBC to fetch data, and that is fast on the Oracle implementation. My problem is that the application has to use Oracle for production, so I am really desperate for a solution to my problem. I very much appreciate any help or hint you can provide.

upgrading to hibernate 2.1 seems to have done the trick. Oracle implementation is now as fast as PostgreSQL.

many thanks to all who offered help!