Oracle 8i JDBC thin/oci performance

From: Lothar Joeckel <ljoeckel_at_iname.com>
Date: Fri, 06 Aug 1999 01:23:57 +0200
Message-ID: <37AA1D0D.8DA99807@iname.com>

I've compared the two Oracle jdbc drivers 'thin' and 'oci' to find the differences in performance. In that comparision in found that the JDBC/OCI
driver beheaves very strange in a way that it tooks longer and longer to access the database.
Please look here at the results of a small test program. The program simply runs 20000 times a query against the EMP table. The time shown in the tables means that each single query has an average access time of n.nn milliseconds.

Count OCI ms. THIN ms.

1000  3.06    5.05
2000  3.17    5.08
3000  3.28    5.07
4000  3.40    5.07
5000  3.53    5.07
6000  3.65    5.07
7000  3.77    5.07
8000  3.89    5.07
9000  4.01    5.07
10000 4.14    5.07
11000 4.26    5.07
12000 4.39    5.07
13000 4.51    5.07
14000 4.64    5.07
15000 4.77    5.07
16000 4.90    5.07
17000 5.03    5.07
18000 5.16    5.08
19000 5.28    5.08
20000 5.41    5.08

The 'THIN' driver works fully as expected. A very constant access time. Surprisingly the 'OCI' drivers start's with 3.06ms per Query and ends up at 5.41ms which is 77% over the base value of 3.06.

Any comments to this?
Regards
Lothar Joeckel

• The Source ==== import java.sql.*; class Employee { public static void main (String args []) throws SQLException { String type = "THIN"; String connectString = null; DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

    try {

        type = args[0].toUpperCase();
} catch (ArrayIndexOutOfBoundsException e) {

        System.err.println("Usage: java Employee <jdbc|oci>");
        System.exit(1);

}

    if (type.equals("OCI")) {

        connectString = "jdbc:oracle:oci8:@SPARC";
} else {

        connectString = "jdbc:oracle:thin:@sparc:1521:SPARC";
}

    System.out.println("Using " + type + " driver");

    Connection conn = DriverManager.getConnection (connectString, "scott", "tiger");

    long t = System.currentTimeMillis();     for (int i = 1; i < 20001; i++) {

        Statement stmt = conn.createStatement();
        ResultSet rset = stmt.executeQuery("select ENAME from EMP");
        rset.next();
        if (i % 1000 == 0) {
            double avg = (double) ((double)(System.currentTimeMillis() -

t
/ i);

            System.out.println("i=" + i + " avg=" + avg);
        }
        rset.close();
        stmt.close();

}

 }
}

Received on Thu Aug 05 1999 - 18:23:57 CDT