Archive for the ‘jdbc’ Category
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.
# or batching with BLOBs very much.
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.
It is focused on simplying the most usual cryptographic tasks (like encryption and hashing) with a plus: it is designed with easy integration in mind.
If using Hibernate, you can declaratively configure encryption for your persistent entities, and it is also easy to manage through spring.
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 a
java.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 220.127.116.11 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.