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 slow when using Oracle as a database 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

For Oracle



Thanks in advance;
Abhijit Salunkhe

Hi,

I did also notice a severe performance-impact when switching from mysql to oracle.
Reason was that while auto-generating the tables in mysql there is an automatic index-generation on all foreign-key fields, which is not done in oracle, so i had to create these indexes (which make sense in most cases) myself.
So: check your indexes and if there are some missing on the oracle side try creating them.

Thanks for your input, this is surely going to help me proceed further.

I will try it today and let you know if this was the actual reason.

Thanks once again,
Keep up the good work :)

Simple question first - with MySql were you using MyIsam or Innodb as its storage engine? MyIsam isn't transactional so there would be none of the concurrency overhead involved with Oracle.

The difference in jdbc.batch_size might be relevant depending on how many inserts your slow transactions are doing.

This variable defines how many records hibernate will attempt to insert at once when doing batch inserts. For example if you have a typical batch insert loop like the one below, hibernate will flush a batch of INSERT statements to the database every batch_size iterations. So in your oracle configuration you might be getting slower performance because you're sending 30/5 = 6 times as many batches than you were for MySQL which could give a performance hit if you're saving 1000s of records.



Also, with MySQL there is a special flag you can set in the jdbc driver: &rewriteBatchedStatements=true. If set then the driver converts each batch of INSERTs into a single high optimised mysql-specific bulk insert statement before sending the sql to the database.

I.e. instead of sending a batch of individual inserts like:


it will send a single bulk insert statement:


For very large numbers of inserts this can make a big difference . I'm not sure if there are similar features available for the oracle jdbc driver (?).

(NB. if the entities you are inserting having auto-generated ids then the above probably doesn't apply - hibernate can't batch the inserts since there are problems obtaining the generated ids from batch inserts.)

...or something completely different could be happening :-/ It's very difficult to say without more information about your particular transactions/enties - could you give a bit more details/code examples of the slow operations?


Stephen