[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.
