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
