Posts Tagged ‘oracle’
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.
I’ve collected the following bits of information regarding the tunning of Oracle when used with Hibernate performance… it might help someone (and I need to write it down somewhere I won’t loose when moving from a desk to another!).
The following properties should be set:
# See http://martijndashorst.com/blog/2006/11/28/hibernate-31-something-performance-problems-contd/
# NOTE: See http://opensource.atlassian.com/projects/hibernate/browse/HHH-3359
hibernate.jdbc.wrap_result_sets = true
# See http://www.hibernate.org/120.html#A10
hibernate.dbcp.ps.maxIdle = 0
hibernate.c3p0.max_statements = 0
# Everything else comes from http://docs.codehaus.org/display/TRAILS/DatabaseConfigurations
# The Oracle JDBC driver doesn't like prepared statement caching very much.
hibernate.statement_cache.size=0
# or batching with BLOBs very much.
hibernate.jdbc.batch_size=0
I have not tested the performance difference… just collected the information.
Let me know if you know more tricks!
Update: Added a warning about a memory leak in current hibernate, thanks to dfernandez.
Update 2: Statement caching for Oracle can be enabled directly on the datasource implementation. See this article.
This is mostly a reminder for myself (I read somewhere that one needs to write down things to actually make them stick to the brain). But it might also help you.
When reading date-time information from a database (that is, year, day, month… but also hour, minute, second, millisecond), always use ResultSet.getTimestamp(). I repeat. Always.
By some reason, I keep using getDate(). Maybe because a java.sql.Date can store information about time, but the javadoc forbids it:
To conform with the definition of SQL
DATE, the millisecond values wrapped by ajava.sql.Dateinstance must be ‘normalized’ by setting the hours, minutes, seconds, and milliseconds to zero in the particular time zone with which the instance is associated.
To make things worse, some JDBC drivers ignore the javadoc, and will set the date with the time portion when available. An example of this is Oracle 10 jdbc driver. Interestingly, Oracle 9 enforces javadoc behaviour.
So, if you are running away from version 10 and come to version 9 (like I did), be very careful. If you see that suddenly your dates are truncated at midnight, loosing their time component, then your are not properly reading your ResultSet.
By the way… the link to the javadoc is to the getTimestamp(String, Calendar) version on purpose.
It is well known that using column names is generally better than using an index (you don’t care how the select is written, don’t forget to increment counter, the readability of your code is improved…).
But I think that far less people know about the Calendar parameter. It is very useful when your database stores dates for a given (known) timezone, and your application runs in a different one. Passing the right calendar will adjust the date with the time difference and daylight savings (if needed).
So, key points to remember:
- resultset.getDate() reads (sometimes) only date information
- resultset.getTimestamp() reads (always) date and time information
- Don’t assume that changing a JDBC driver will keep your application working flawlessly.
Sometimes (quite randombly) the Oracle 10 JDBC driver will throw an SqlException with the message “OALL8 is in an inconsistent state“.
This, apparently, is a bug on the JDBC code, and shows up when using the driver to connecto to any Oracle version (I was using it agains an 8i version, and I have seen reports against both 9i and 10g).
The easiest solution, when possible, is to use a lower version of the driver (version 9.2.0.8 is working fine for me… I can’t use 8i because of some newer features I use).
If that’s not an option, bear in mind that the error will leave your connection useless, so you’ll need to code some magic to remove it from your pool. Most pools support using a ping query to test a connection, which might be enough. If you test this approach, please let me know.
I’m just guessing now, but due to the random nature of this error, maybe just detecting the message and retrying the same query in a different connection could recover from it.
In this thread, you can get more information about symptoms, workarounds and affected versions. Or you can google for the error code… it’s quite popular.
My profile
amuino on Twitter
My projects
My Open Source
Rambling about... 