Worst practices when using Oracle JDBC Drivers – great presentation by Douglas Surber

I just came out of – well actually, as I am writing this down I am still in it – a very good session on the Oracle JDBC drivers by Douglas Surber, principal developer for Oracle JDBC Drivers. In a very powerful delivery, Douglas discussed the ten worst practices – and their proper counterparts – with the use of the Oracle JDBC drivers. I will try to sum them up as I understand them. One thing that is very clear to me is that a good understanding of the JDBC drivers – and recent changes in them – can be very useful to put it mildly for improving our applications.

Another very important conclusion: the versions of the Oracle JDBC driver and the Oracle database are not tied together. Oracle JDBC Driver 10gR2 can be used with any supported database version so there is no good reason for not using the latest version of the JDBC drivers, even for organisations working with 8i or 9i databases .....

(Note: 30 minutes onwards, Doug is still happily sharing his expertise, telling tales from the depths of the JDBC arena, with a small audience hanging on to his every word.) 

10. Using Oracle SQL type for simple types

Long time ago these types were created to improve performance (represent exactly what comes of the wire). Now instead you should use standard Java JDBC types! Most Oracle extensions are from the time that JDBC was much smaller and more limited. Oracle now optimizes for JDBC, not for its own extensions if there is a choice. Whenever JDBC will do the job (today) the Oracle extension will eventually go away.

Exception: NUMBER. Oracle representation is a huge (38 byte) type. If that precision really matters – you can use oracle.jdbc.Number. But it hurts performance! Most are not at that ragged edge of representation.

Bringing the number into Java is much faster than using the Oracle Number.

9. Do not use integer (named) arguments

conn.prepareStatement(" select * from tab where id =:1");
ps.setInt(1,n) 

instead:

conn.prepareStatement(" select * from tab where id =?");
ps.setInt(1,n)

or:

 

Conn.prepareStatement(" select * from tab where id =:1");
ps.setAtName("1" ,n)
 

which is much worse in terms of performance.

8. Do not concatenate conditions to SQL statement – instead use bind parameters
In other words: do not use

stmt=conn.CreateStatement() 
stmt.Execute(String + String)

Instead: preparedStatement() with  bind parameters

SQL Injection – where the server mistakes data for code
Breaks the benefit of caching – and therefore hurts performance!

7. Do not code your own CustomDatum classes for mapping complex types from SQL

Do not use CustomDatum at all (deprecated since 9i, 6 years ago). Use OraData instead. Better yet: do not code them yourself, use JPublisher to create Oracle object classes.

6. Do not use LOBs carelessly

LOBs are designed for MBs or GBs. Performance optimized for big suckers. To work with LOB:

* Update set empty_clob()
* Select lob for update
* (getClob.characterStream)
* Write to stream
* commit and close

Three round-trips. This is justified for large (Mb or larger). For small Strings, this is a huge overhead.

In the 10.2 driver, we can treat Clob as if they were strings, with code like:

ps = conn.createPreparedStatement( "update tab set lob =?");
ps.setString(1,"Bob")

The actual way this is done internally depends on the size of String set on the bind-parameter.

Better performance can be squeezed out of this for String between 4000 bytes and 32Kb, using a bit of PL/SQL:

ps = conn.prepareStatement("begin \n
update tab set lob= ? where id = ?;\n
End;")
Ps.setString(1, StringUpTo32K); 

See Data Inteface section in the JDBC developer’s guide for more details. Question: Can you treat the lob value that is selected from a table as String too?

5. Do not use old versions of the drivers – the 10gR2 drivers are supported with the 8i database!!
Even with an 8i database, you can use 10g Drivers. The 10g drivers are much much faster! So always use the newest driver. 10.2 driver is supported with 8.1.7! No excuse for using old drivers

XMLType only works on the OCI driver. JDBC moves the bits (as a Big, Streaming type) and turns it over to the C code.

4. Do not share a connection between multiple threads at the same time
For example initialize a Service object with a connection then use that object in two threads simultaneously. Non reproducible errors may result.
Several known bugs, fixing would hammer performance – so no fix!

Solution: serialize use of connections – use Service object in synchronized block.

3. Do not use DriverManager or Simple DataSources in high volume high performance application

Conn= DriverManager.getConnection()

Try to be backed by a Connection Cache: the Oracle implicit Connection Cache (in 10g drivers).

Props.setProperty("ConnectionCacheEnabled", true);
Ds = new OracleDataSource(props)
Conn = ds.getConnection()

A very powerful connection cache.

Vital with RAC. Rapid failover – not wait for TCP time out, client side load balancing based on feedback from database cluster.

When done with the statement, close it! (this returns it to the cache, that frees up a lot of memory). The cache does not tell the database though – takes a roundtrip.

2. Do not use your own statement cache

(is Gavin King in the room?? Hibernate statement cache does not work with Oracle JDBC)
We have a cache built into the driver. We know what’s happening!  We assume you are using our statement cache. We traded off memory for performance – much more memory used by the drivers that buys a lot of performance.

Conn.setStatementCacheSize(100) - tune this number! (number of different, frequently used statements)
Conn.setImplicitCacheEnable(true)
Ps.conn.prepateStatement(query) 

We do not close cursors – too expensive to recreate (hence the memory usage).

Statement cache does do a very loose parse: we need to know whether a statement is an update, merge, select or insert. It compares statements by comparing the literal strings when deciding whether an existing statement can be reused.

1. Do not use driver package

import oracle.jdbc.driver

This was deprecated in 9iR1, 6 years ago. With the latest drivers, this will not compile or run. We are supposed to do: import oracle.jdbc.*

Future

Support for J2SE 5 and 6 in new jars Ojdbc5.jar and Ojdbc6.jar. The current 10.2 drivers are all compiled with JDK 1.4. Release 11 Drivers version of jar compiled with 5; we also support 6 (released by Sun anyday). Desupport 1.2, 1.3, 1.4 (if you need 1.4, stay on 10.2.0.3, supported for 7 years from now).

Big news with JDK 1.6 J2SE 6: support for JDBC 4.0. This means the following features:

  • SQLXML
  • Rowid as standard use
  • NChar & NClob (end of setFormOfUse)
  • Wrapper interface
  • SQLException Hierarchy (8 or 9 subclasses) – SQLRecoverableException  (it is worth trying again, we lost contact with the database)
  • Conn,createClob, createBlob, createArray
  • The Oracle Universal Connection Pool which will manage any type of Connec
    tion (jdbc, jca, ldap), for
    ny database and any app server, also for stand alone deployment (BPEL, Toplink)
  • oraclele.jdbc.driver is not in 11 drivers
  • next release of the drivers: date and timestamp both map to java,sql.Timestamp: in 91 en 10g drivers, SQL TIMESTAMP maps to java,sql.Timestamp and SQL DATE maps to java.sql.Date

14 Comments

  1. Joe August 7, 2007
  2. oranus January 27, 2007
  3. Ames Tiedeman October 30, 2006
  4. llanerobass October 29, 2006
  5. Marco Gralike October 29, 2006
  6. llanerobass October 26, 2006
  7. llanerobass October 26, 2006
  8. Jan Kettenis October 26, 2006
  9. JJ Walker October 25, 2006
  10. Lucas Jellema October 25, 2006
  11. Aino Andriessen October 25, 2006
  12. Jeroen van Wilgenburg October 25, 2006
  13. Francois October 25, 2006
  14. Anonymous October 25, 2006