[This tip was excerpted from the new Wrox Press book Oracle 9i Java Programming.]
Performance optimization is a big topic; big enough to fill an entire book of information on how to make Oracle and Java perform better. In this and subsequent tips, we will look at just a few ways to improve the performance of your Java programs. This time we'll take a look at JDBC driver performance.
Recall that the JDBC specification defines four types of drivers:
- Type 1 - Connects to a database through the ODBC API
- Type 2 - Connects to a database through a native code library
- Type 3 - Connects to a database through a middleware proxy
- Type 4 - Connects to a database through 100% Java code
In some cases, you will not have a choice over which driver to use. If your application needs to run on a multitude of client platforms, and you cannot control the client's platform, you will need to use a Type 4 driver. Using the Oracle Thin (Type 4) driver means the driver can be used on any platform because it is 100 percent Java and it can be deployed with the application. If you are writing Java stored procedures or Java code such as EJBs that will deployed in the database, you will use the KPRB (internal) Oracle driver.
At other times, you may have a choice. If you are developing an application that will be deployed to client platforms, and you have the opportunity to install Oracle client software on every platform, then you can choose to use either the OCI (Type 2) or thin driver. However, JDBC books and examples you find on the Internet tend to use the thin driver exclusively. I have recommended to other developers that they use the OCI driver whenever they have the choice, and the thin driver only when they need to. In this section of the chapter, we will provide some concrete data that supports this choice of OCI over thin.
For the series of benchmarks in this section, the tests were run on two different client-server architectures. Each set of tests is run with a Java client running on a Sun server, and connecting to an Oracle 8.1.7 database running on the same machine. The programs were run on the same machine where the database resides in an attempt to mitigate network latencies.
The tests were also executed with a client and server on different machines so we can see how the tests perform over a 10-base-T network. The client was a Windows 2000 machine and the same Sun machine as above was the server. The specifications for the both machines are as follows: For the server, we had: Sun Ultra 60, 2 450 MHz CPU's, 512 MB RAM, Solaris 8, and JDK 1.3.1_01. For the client, we had: Dell Workstation, 1 500 MHz Pentium 3, 384 MB RAM, Windows 2000 Workstation, and JDK 1.3.1_01. [Complete details about the tests are found in the book.]
A number of different tests were performed on each architecture. The first set of tests looked at writing LOB data to the database. The write test times how fast a BLOB (from memory) can be written to a new database row ten times. The BLOB is a memory-based byte array materialized as an InputStream. By using a BLOB from memory, we remove any uncertainty resulting from trying to read a BLOB from some source such as a file. Garbage collection is disabled and the JVM is started with the same amount of heap memory in an attempt to remove any potential variables. Each lob size was tested with the thin driver and the OCI driver.
The results: On the Sun server, there is some performance gain when using the OCI driver. The OCI driver is faster at getting a connection; both drivers perform the same when preparing the statement; and then again the OCI driver is usually faster at writing the data. With the Windows platform as the client, the differences between BLOB writes are not as significant, but there is still a noticeable difference between the two drivers in the amount of time it takes to get a connection to the database.
The next set of tests looked at reading BLOB data. The read test times how fast a BLOB can be read from a database row. Each test was executed using the thin and OCI8 drivers ten times for four different BLOB sizes. The BLOB is just read -- nothing is done with the contents. Again, garbage collection is disabled and the JVM has 32MB of memory. Here is how the table was initially populated.
The results: The results for the read test are similar to those for the write test. The OCI driver is always faster at getting a connection. Both drivers are the same when it comes to creating a Statement object. The OCI driver is generally faster than the thin driver at reading LOB data from the database.
The final test involved reading and writing data to the EMP table. For this set of tests we also used batch updates to see how that affected performance.
The results: For the Sun server, in general, the OCI driver is faster than the Thin driver. (There does appear to be an anomaly with a batch size of 5.) Also, as expected, we see that as batch size increases, execution time decreases. In other words, a batch size of 40 is more efficient than a batch size of 1.
In summary, we've shown here that there really is a difference between the two drivers. In almost every test case explored, there is a statistically significant performance difference between the OCI and the thin drivers. When you have a choice, and you need the fastest performance, you should choose the OCI driver. Because the OCI driver uses native code libraries, it executes faster than the thin driver.