Optimizing Oracle and Hibernate performance, how it turned out

Some weeks ago I wrote down some notes for making Oracle work harder and faster with hibernate.

Those notes were collected from several places on the Internet and are supposed to help.

But they didn’t. Not for us.

Good news is that we found the problem of the bad performance and fixed it… it was all caused by foreign key integrity checks.

We got our first clue when the sysadmin detected a lot (say, 12 or so) open cursors for a simple update sentence.

We were using defaults for most of the hibernate settings and even when the update was intended to only change one column, the sql sentence set all the fields in the table for the affected row.

And Oracle fired all the checks.

I’m not sure why Oracle does not optimize this by first checking if the value has changed (if it hasn’t, then the constraints are forcedly valid), but the solution was simple… don’t update more than you need.

I have  a new friend and it is called @org.hibernate.annotations.Entity(dynamicUpdate=true). There is some (extremely brief) documentation on the hibernate annotations reference and javadocs. Of course you can also use it in the hbm files if XML is your thing.

Just in case you did not guess it, this only updates dirty properties of your objects (i.e. those that you updated after retrieving it from the database).

This has potential caveats if another transaction somehow updates your object, since the database state will be different that what you expect. To the best of my understanding this can only happen with detached objects in any reasonable isolation level… and you should reload the state from database in that case.

Well, so that was it… too many constraints on a table and updating more columns than needed. Updating only the affected columns increased performance to where we expected: better than the mysql-based prototype.

About these ads

  1. Oscar Arias

    I faced a similar problem a few months ago using Hibernate with DB2. In adition to the dynamic update option we used @org.hibernate.annotations.Entity(dynamicInsert=true) in order to prevent Hibernate from generating update and insert statements with lots of null values.

  2. Abel,

    Would you mind if I asked you a few questions via email about your experience performance tuning Hibernate and Oracle?

    Cheers,
    Orion

  3. Jan-Marten Spit

    “I’m not sure why Oracle does not optimize this by first checking if the value has changed”

    the answer to that is realy simple. (and important, that’s why i felt inclined to react to this now 4 year old post).

    if a programmer thinks about what the implications of his/her code are, he or she would not ‘update’ a value to the same value ever.

    so if Oracle would ‘check it first’, it would actually be punishing good programmers.






Follow

Get every new post delivered to your Inbox.

%d bloggers like this: