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
![Implementing an FTP server in PL/SQL]({"aperture":"0","credit":"","camera":"","caption":"","created_timestamp":"0","copyright":"http:\/\/seattletimes.nwsource.com\/ABPub\/2012\/08\/02\/2018836700.jpg","focal_length":"0","iso":"0","shutter_speed":"0","title":"","orientation":"0"} "Implementing an FTP server in PL/SQL")
7.1.6 won’t work really well imo.
Good stuffs !
I’m an author of ETL product and Oracle is the only JDBC driver which caused redesign of my application JDBC access layer.
When I evaluate sources of other projects I always see the comments like this:
Some driver’s( guess who? – Ora…) throw exception here!!!
Regarding the statement cache. My project also use it because not all JDBC drivers support this feature. But my statement cache is configurable and can be replaced with a native driver solution. But I’m not sure if Oracle driver implements caching correctly 😉
Will ORCL wipe RHAT off the map now?
Will customers chose Oracle as the vendor of choice?
Comments?
Seems to be impossible: upgrading Oracle is out of the budget for my client 🙁
@llanerobass
You should at least upgrade to 7.3.4. Oracle 7.2 and 8.0 were the worse Oracle releases I worked with. Oracle 7.2 was also very quick replaced by version Oracle 7.3.
Oracle 7.2 is the version that marked the first SQL*Net V2(.1) drivers coming into existance and was backwards compatible with SQL*Net version 1.
So I can imagine that you will have problems with the OCI calls. The 7.3 version was compatible with the first Oracle 8 SQL*Net drivers ( SQL*Net version 2.2) so you would have a far better chance that this version would work against your JDBC drivers.
If you try to connect to an Oracle 7.2.3 DB using the newest version of the JDBC drivers, you’ll get an ORA-01010 error: “invalid OCI operation”.
And what about Oracle 7.2.3 & Oracle 7.1.6 versions? I’ve been trying to use the lastest Oracle JDBC drivers over those versions and it fails… Even worst, I need to use JDBC over two different Oracle DBs: a 9i DB and a 7.2.3 DB (the lastest Oracle JDBC driver do not work over the 7.2.3 DB so I needed to load dynamically two versions of the Oracle JDBC driver… What a thing to do!)
Could you be more specific with point 9. It is not clear if the first or second alternative is the best. I read that you should not use the first but the second or last instead, of which the last is not preferred because of performance.
You seem to be switching between what to do and what not to do on each of these. Very confusing. You need to be more consistent or people will end up doing the wrong things.
For example, number 10 is phrased as a bad practice. Number 1. says “Do not use your own statement cache”. So you are saying it’s a bad practice to not use my own statement cache? That’s not what the description says.
WTF?
Dear Anonymous,
Thanks for your contribution to this discussion, I am sure. However, I would like to ask two things of you:
– could you please add your name to your contribution? If you feel inclined to making such strong statements, at least have the courtesy to make yourself be known
– could you please substantiate your comments on the Oracle JDBC drivers? I am not saying you are wrong in the statements you are making – though I by no means support them – but I feel that some prove or corroborating sources would be in order. So please write another comment in which you go into more detail about your personal experiences with the Oracle JDBC drivers that have made you voice your opinion.
I am afraid that anonymous comments are not the way to go on our weblog. If you for whatever reason choose to not come forward, I will have to remove your comment from the blog.
best regards,
Lucas
I can’t wait for the SQLXML and XMLType support. However, you can use the XMLType with the thin driver, although it’s use limited :
to select :
oracle.xdb.XMLType xml = XMLType.createXML(ors.getOPAQUE(1));
oracle.xml.parser.v2.XMLDocument = (XMLDocument)xml.getDOM();
to save :
XMLType xml = new XMLType(conn, doc);
OraclePreparedStatement pstmt = (OraclePreparedStatement) conn.prepareStatement(“insert into xmltable values (?)”);
pstmt.setObject(1, xml);
int nInserted = pstmt.executeUpdate();
Are you saying that String named arguments are ok to use or do we have to switch to the question mark notation? We’re using the Spring NamedParameterJdbcTemplate in our project. Working with named parameters is much safer than working with the question marks. And we don’t want to switch back to the question mark notation 😉
Nice information ;o)
Thanks
I find it highly amusing that Oracle would be pontificating about JDBC best practices when their JDBC drivers have consistently been amongst the worst in the industry (worst in virtually any dimension you care to name – spec compliance, performance, backwards compatibility etc. etc.).
About the most charitable thing I can say about the Oracle JDBC drivers is that they’re an excellent cautionary tale for other JDBC driver development teams.