[Hibernate Query] Slow performance with Oracle as compared to MySQL

[Abhijit Salunkhe]

Hi Pros,

We are using Hibernate for the persistent layer of our Java based web application.It has been observed that the application response time is significantly slow when using Oracle as compared to using MySQL.

For Example.
Particular transaction (used as a abstract term) say transactionX takes approximately 18 Sec with MySQL. Same transationX when fired using Oracle as a database completes in approximately 55 Sec.

Is there any known issue with hibernate which must be causing this performance issue with Oracle?

We fired generated SQL statements directly from SQL editor. Our initial investigation directs towards use of joins, but not sure if this is the real culprit.

Also, In configurations we found one difference,

For MySQL

<property name="jdbc.batch_size">30</property>

For Oracle

<property name="jdbc.batch_size">5</property>

Thanks in advance,
Abhijit Salunkhe

[Mark Spritzler]

Many factors can lead to this, and it is not necessarily Hibernate that is the cause.

Main things I would ask is what is the explain plan you get from Oracle, that would tell you a lot about the query.

Maybe you don't have indexes in Oracle that you had in MySQL. Or that you had no indexes in MySQL, that you now need in Oracle. Ask your Oracle DBA to analyze your query.

Are you eager loading too much data?

There are many possibilities here, but I recommend what I wrote above first.

Mark