Rambling about…

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.