JDBC Pool with Implicit Connection Cache

By | December 23rd, 2008 | Category: 10g, Java, Oracle Database | No Comments »

I have finally found the chance to make my first steps in Java. Instead of “Hello World”, I wanted to test the performance effect of using or NOT using a connection pool for a Java application.

I have prepared 2 Java Testcases:

  • ConnWithIcc.java: 999 Select statements with JDBC Pooling via Implicit Connection Cache
  • Conn WithougIcc.java: 999 Select statements withough JDBC Pooling

ConnWithIcc.java:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94

import java.sql.*;
import java.util.*;
import oracle.jdbc.pool.*;
import oracle.jdbc.*;
 
class ConnWithIcc
{
  public static void main(String args[]) throws InterruptedException
  {
    ResultSet rset = null;
    Connection conn = null;
    Statement stmt = null;
 
    try
    {
      // create a DataSource
      OracleDataSource ods = new OracleDataSource();
      ods.setURL("jdbc:oracle:thin:@//ora-vm1:1521/ICC");
      ods.setUser("scott");
      ods.setPassword("tiger");
      // set cache properties
      java.util.Properties prop = new java.util.Properties();
      prop.setProperty("MinLimit", "10");
      prop.setProperty("MaxLimit", "10");
 
      // set DataSource properties
      ods.setConnectionCachingEnabled(true); // be sure set to true
      ods.setConnectionCacheProperties (prop);
      ods.setConnectionCacheName("ImplicitCache01"); // this cache's name
 
      for (int i=1; i < 1000; i++)
      {
         //System.out.println("Establishing connection " +i);
 
         // get the connection
         conn = ods.getConnection();
         //System.out.println("Connected.\nPrinting query results ...\n");
         // Create a stmt
         stmt = conn.createStatement();
         // execute the query
         // rset = stmt.executeQuery( "select 1 from dual where " + i + " = " + i );
         rset = stmt.executeQuery( "select 1 from dual");
         while (rset.next())
         {
            int dualnr = rset.getInt ( 1 );
         }
         conn.close();
         rset.close();
         stmt.close();
 
 
/*
         try
         {
            Thread.currentThread().sleep(1000);
         }
         catch(InterruptedException ie)
         {
               System.err.println ("error message: " + ie.getMessage() );
               ie.printStackTrace();
               Runtime.getRuntime().exit(1);
 
         }
         //System.out.print("\f");
*/
 
      }
   }
   catch (SQLException e)
   {
      // handle the exception properly - in this case, we just
      // print a message and stack trace and exit the application
      System.err.println ("error message: " + e.getMessage() );
      e.printStackTrace();
      Runtime.getRuntime().exit(1);
   }
   finally
   {
      // close the result set, the stmt and connection.
      // ignore any exceptions since we are in the
      // finally clause.
      try
      {
         if( rset != null )
           rset.close();
         if( stmt != null )
           stmt.close();
         if( conn != null )
           conn.close();
      }
      catch ( SQLException ignored ) {ignored.printStackTrace(); }
   }
  }
}

ConnWithoutIcc.java:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98

import java.sql.*;
import java.util.*;
import oracle.jdbc.pool.*;
import oracle.jdbc.*;
 
class ConnWithoutIcc
{
  public static void main(String args[]) throws InterruptedException
  {
    ResultSet rset = null;
    Connection conn = null;
    Statement stmt = null;
 
    try
    {
      // create a DataSource
      OracleDataSource ods = new OracleDataSource();
      ods.setURL("jdbc:oracle:thin:@//ora-vm1:1521/NOICC");
      ods.setUser("scott");
      ods.setPassword("tiger");
 
      // set cache properties
      //java.util.Properties prop = new java.util.Properties();
      //prop.setProperty("MinLimit", "10");
      //prop.setProperty("MaxLimit", "10");
 
      // set DataSource properties
      //ods.setConnectionCachingEnabled(true); // be sure set to true
      //ods.setConnectionCacheProperties (prop);
     // ods.setConnectionCacheName("ImplicitCache01"); // this cache's name
 
 
 
      for (int i=1; i < 1000; i++)
      {
         //System.out.println("Establishing connection " +i);
 
         // get the connection
         conn = ods.getConnection();
         //System.out.println("Connected.\nPrinting query results ...\n");
         // Create a stmt
         stmt = conn.createStatement();
         // execute the query
         // rset = stmt.executeQuery( "select 1 from dual where " + i + " = " + i );
         rset = stmt.executeQuery( "select 1 from dual");
 
         while (rset.next())
         {
            int dualnr = rset.getInt ( 1 );
         }
 
 
/*
         try
         {
            Thread.currentThread().sleep(1000);
         }
         catch(InterruptedException ie)
         {
               System.err.println ("error message: " + ie.getMessage() );
               ie.printStackTrace();
               Runtime.getRuntime().exit(1);
 
         }
*/
         //System.out.print("\f");
         conn.close();
         rset.close();
         stmt.close();
 
      }
   }
   catch (SQLException e)
   {
      // handle the exception properly - in this case, we just
      // print a message and stack trace and exit the application
      System.err.println ("error message: " + e.getMessage() );
      e.printStackTrace();
      Runtime.getRuntime().exit(1);
   }
   finally
   {
      // close the result set, the stmt and connection.
      // ignore any exceptions since we are in the
      // finally clause.
      try
      {
         if( rset != null )
           rset.close();
         if( stmt != null )
           stmt.close();
         if( conn != null )
           conn.close();
      }
      catch ( SQLException ignored ) {ignored.printStackTrace(); }
   }
  }
}

The first step is of course to put the java executable in the path and set the classpath environment variable:

1
2
3
4
5

[oracle@ora-vm1 ICC]$ echo $PATH
/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/home/oracle/bin:/u01/app/oracle/product/10.2.0/bin:/u01/app/oracle/product/10.2.0/jre/1.4.2/bin:/u01/app/oracle/product/10.2.0/jdk/bin
 
[oracle@ora-vm1 ICC]$ echo $CLASSPATH
.:/u01/app/oracle/product/10.2.0/jdbc/lib/ojdbc14.jar:/u01/app/oracle/product/10.2.0/jdbc/lib/orai18n.jar

Next, you have to compile the java files into class files:

1
2

[oracle@ora-vm1 ICC]$ javac ConnWithoutIcc.java
[oracle@ora-vm1 ICC]$ javac ConnWithIcc.java

Finally, we can execute both classes and measure the performance difference:

1
2
3
4
5
6
7
8
9
10
11

[oracle@ora-vm1 ICC]$ time java ConnWithIcc
 
real    0m1.501s
user    0m0.446s
sys     0m0.484s
 
[oracle@ora-vm1 ICC]$ time java ConnWithoutIcc
 
real    0m53.653s
user    0m1.528s
sys     0m2.561s

So, the 999 “select 1 from dual” statements take about 1.5 seconds with a jdbc connection pool and take almost 1 minute without a connection pool! Moreover, if you divide 999 statements by 53.6 seconds, you get an average of 18.6 connections per second. You can easily see that one of my 2 core´s is completely utilized by 999 establishing and tear down database connections.

[oracle@ora-vm1 ICC]$ sar -u 5 100
Linux 2.6.18-92.1.18.el5 (ora-vm1.intra)        12/23/2008

12:06:48 PM       CPU     %user     %nice   %system   %iowait    %steal     %idle
12:06:53 PM       all      0.30      0.00      1.90      2.30      0.00     95.51
12:06:58 PM       all      0.10      0.00      1.80      1.60      0.00     96.50
12:07:03 PM       all      7.51      0.00     15.92      0.70      0.00     75.88
12:07:08 PM       all     12.90      0.00     42.20      0.10      0.00     44.80
12:07:13 PM       all     13.60      0.00     40.80      0.50      0.00     45.10
12:07:18 PM       all     13.00      0.00     41.80      0.60      0.00     44.60
12:07:23 PM       all     14.09      0.00     40.16      0.40      0.00     45.35
12:07:28 PM       all     13.09      0.00     41.66      0.50      0.00     44.76
12:07:33 PM       all     13.00      0.00     41.40      0.50      0.00     45.10
12:07:38 PM       all     14.79      0.00     40.66      0.20      0.00     44.36
12:07:43 PM       all     15.02      0.00     39.84      0.30      0.00     44.84
12:07:48 PM       all     13.10      0.00     41.90      0.60      0.00     44.40
12:07:53 PM       all     14.99      0.00     40.06      0.70      0.00     44.26
12:07:58 PM       all      4.20      0.00     12.91      0.60      0.00     82.28
12:08:03 PM       all      0.30      0.00      1.50      1.00      0.00     97.20

Leave Comment