{"id":533,"date":"2008-12-23T21:55:47","date_gmt":"2008-12-23T19:55:47","guid":{"rendered":"http:\/\/www.ora-solutions.net\/web\/?p=533"},"modified":"2010-10-25T23:01:43","modified_gmt":"2010-10-25T21:01:43","slug":"jdbc-pool-with-implicit-connection-cache","status":"publish","type":"post","link":"https:\/\/www.ora-solutions.net\/web\/2008\/12\/23\/jdbc-pool-with-implicit-connection-cache\/","title":{"rendered":"JDBC Pool with Implicit Connection Cache"},"content":{"rendered":"<p>I have finally found the chance to make my first steps in Java. Instead of &#8220;Hello World&#8221;, I wanted to test the performance effect of using or NOT using a connection pool for a Java application.<\/p>\n<p>I have prepared 2 Java Testcases:<\/p>\n<ul>\n<li> ConnWithIcc.java: 999 Select statements with JDBC Pooling via Implicit Connection Cache<\/li>\n<li>Conn WithougIcc.java: 999 Select statements withough JDBC Pooling<\/li>\n<\/ul>\n<p><strong>ConnWithIcc.java:<\/strong><\/p>\n<pre lang=\"java\" line=\"1\">\r\nimport java.sql.*;\r\nimport java.util.*;\r\nimport oracle.jdbc.pool.*;\r\nimport oracle.jdbc.*;\r\n\r\nclass ConnWithIcc\r\n{\r\n  public static void main(String args[]) throws InterruptedException\r\n  {\r\n    ResultSet rset = null;\r\n    Connection conn = null;\r\n    Statement stmt = null;\r\n\r\n    try\r\n    {\r\n      \/\/ create a DataSource\r\n      OracleDataSource ods = new OracleDataSource();\r\n      ods.setURL(\"jdbc:oracle:thin:@\/\/ora-vm1:1521\/ICC\");\r\n      ods.setUser(\"scott\");\r\n      ods.setPassword(\"tiger\");\r\n      \/\/ set cache properties\r\n      java.util.Properties prop = new java.util.Properties();\r\n      prop.setProperty(\"MinLimit\", \"10\");\r\n      prop.setProperty(\"MaxLimit\", \"10\");\r\n\r\n      \/\/ set DataSource properties\r\n      ods.setConnectionCachingEnabled(true); \/\/ be sure set to true\r\n      ods.setConnectionCacheProperties (prop);\r\n      ods.setConnectionCacheName(\"ImplicitCache01\"); \/\/ this cache's name\r\n\r\n      for (int i=1; i < 1000; i++)\r\n      {\r\n         \/\/System.out.println(\"Establishing connection \" +i);\r\n\r\n         \/\/ get the connection\r\n         conn = ods.getConnection();\r\n         \/\/System.out.println(\"Connected.\\nPrinting query results ...\\n\");\r\n         \/\/ Create a stmt\r\n         stmt = conn.createStatement();\r\n         \/\/ execute the query\r\n         \/\/ rset = stmt.executeQuery( \"select 1 from dual where \" + i + \" = \" + i );\r\n         rset = stmt.executeQuery( \"select 1 from dual\");\r\n         while (rset.next())\r\n         {\r\n            int dualnr = rset.getInt ( 1 );\r\n         }\r\n         conn.close();\r\n         rset.close();\r\n         stmt.close();\r\n\r\n\r\n\/*\r\n         try\r\n         {\r\n            Thread.currentThread().sleep(1000);\r\n         }\r\n         catch(InterruptedException ie)\r\n         {\r\n               System.err.println (\"error message: \" + ie.getMessage() );\r\n               ie.printStackTrace();\r\n               Runtime.getRuntime().exit(1);\r\n\r\n         }\r\n         \/\/System.out.print(\"\\f\");\r\n*\/\r\n\r\n      }\r\n   }\r\n   catch (SQLException e)\r\n   {\r\n      \/\/ handle the exception properly - in this case, we just\r\n      \/\/ print a message and stack trace and exit the application\r\n      System.err.println (\"error message: \" + e.getMessage() );\r\n      e.printStackTrace();\r\n      Runtime.getRuntime().exit(1);\r\n   }\r\n   finally\r\n   {\r\n      \/\/ close the result set, the stmt and connection.\r\n      \/\/ ignore any exceptions since we are in the\r\n      \/\/ finally clause.\r\n      try\r\n      {\r\n         if( rset != null )\r\n           rset.close();\r\n         if( stmt != null )\r\n           stmt.close();\r\n         if( conn != null )\r\n           conn.close();\r\n      }\r\n      catch ( SQLException ignored ) {ignored.printStackTrace(); }\r\n   }\r\n  }\r\n}\r\n<\/pre>\n<p><strong>ConnWithoutIcc.java:<\/strong><\/p>\n<pre lang=\"java\" line=\"1\">\r\nimport java.sql.*;\r\nimport java.util.*;\r\nimport oracle.jdbc.pool.*;\r\nimport oracle.jdbc.*;\r\n\r\nclass ConnWithoutIcc\r\n{\r\n  public static void main(String args[]) throws InterruptedException\r\n  {\r\n    ResultSet rset = null;\r\n    Connection conn = null;\r\n    Statement stmt = null;\r\n\r\n    try\r\n    {\r\n      \/\/ create a DataSource\r\n      OracleDataSource ods = new OracleDataSource();\r\n      ods.setURL(\"jdbc:oracle:thin:@\/\/ora-vm1:1521\/NOICC\");\r\n      ods.setUser(\"scott\");\r\n      ods.setPassword(\"tiger\");\r\n\r\n      \/\/ set cache properties\r\n      \/\/java.util.Properties prop = new java.util.Properties();\r\n      \/\/prop.setProperty(\"MinLimit\", \"10\");\r\n      \/\/prop.setProperty(\"MaxLimit\", \"10\");\r\n\r\n      \/\/ set DataSource properties\r\n      \/\/ods.setConnectionCachingEnabled(true); \/\/ be sure set to true\r\n      \/\/ods.setConnectionCacheProperties (prop);\r\n     \/\/ ods.setConnectionCacheName(\"ImplicitCache01\"); \/\/ this cache's name\r\n\r\n\r\n\r\n      for (int i=1; i < 1000; i++)\r\n      {\r\n         \/\/System.out.println(\"Establishing connection \" +i);\r\n\r\n         \/\/ get the connection\r\n         conn = ods.getConnection();\r\n         \/\/System.out.println(\"Connected.\\nPrinting query results ...\\n\");\r\n         \/\/ Create a stmt\r\n         stmt = conn.createStatement();\r\n         \/\/ execute the query\r\n         \/\/ rset = stmt.executeQuery( \"select 1 from dual where \" + i + \" = \" + i );\r\n         rset = stmt.executeQuery( \"select 1 from dual\");\r\n\r\n         while (rset.next())\r\n         {\r\n            int dualnr = rset.getInt ( 1 );\r\n         }\r\n\r\n\r\n\/*\r\n         try\r\n         {\r\n            Thread.currentThread().sleep(1000);\r\n         }\r\n         catch(InterruptedException ie)\r\n         {\r\n               System.err.println (\"error message: \" + ie.getMessage() );\r\n               ie.printStackTrace();\r\n               Runtime.getRuntime().exit(1);\r\n\r\n         }\r\n*\/\r\n         \/\/System.out.print(\"\\f\");\r\n         conn.close();\r\n         rset.close();\r\n         stmt.close();\r\n\r\n      }\r\n   }\r\n   catch (SQLException e)\r\n   {\r\n      \/\/ handle the exception properly - in this case, we just\r\n      \/\/ print a message and stack trace and exit the application\r\n      System.err.println (\"error message: \" + e.getMessage() );\r\n      e.printStackTrace();\r\n      Runtime.getRuntime().exit(1);\r\n   }\r\n   finally\r\n   {\r\n      \/\/ close the result set, the stmt and connection.\r\n      \/\/ ignore any exceptions since we are in the\r\n      \/\/ finally clause.\r\n      try\r\n      {\r\n         if( rset != null )\r\n           rset.close();\r\n         if( stmt != null )\r\n           stmt.close();\r\n         if( conn != null )\r\n           conn.close();\r\n      }\r\n      catch ( SQLException ignored ) {ignored.printStackTrace(); }\r\n   }\r\n  }\r\n}\r\n<\/pre>\n<p>The first step is of course to put the java executable in the path and set the classpath environment variable:<\/p>\n<pre lang=\"bash\" line=\"1\">\r\n[oracle@ora-vm1 ICC]$ echo $PATH\r\n\/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\r\n\r\n[oracle@ora-vm1 ICC]$ echo $CLASSPATH\r\n.:\/u01\/app\/oracle\/product\/10.2.0\/jdbc\/lib\/ojdbc14.jar:\/u01\/app\/oracle\/product\/10.2.0\/jdbc\/lib\/orai18n.jar\r\n<\/pre>\n<p>Next, you have to compile the java files into class files:<\/p>\n<pre lang=\"bash\" line=\"1\">\r\n[oracle@ora-vm1 ICC]$ javac ConnWithoutIcc.java\r\n[oracle@ora-vm1 ICC]$ javac ConnWithIcc.java\r\n<\/pre>\n<p>Finally, we can execute both classes and measure the performance difference:<\/p>\n<pre lang=\"bash\" line=\"1\">\r\n[oracle@ora-vm1 ICC]$ time java ConnWithIcc\r\n\r\nreal    0m1.501s\r\nuser    0m0.446s\r\nsys     0m0.484s\r\n\r\n[oracle@ora-vm1 ICC]$ time java ConnWithoutIcc\r\n\r\nreal    0m53.653s\r\nuser    0m1.528s\r\nsys     0m2.561s\r\n<\/pre>\n<p>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\u00b4s is completely utilized by 999 establishing and tear down database connections.<\/p>\n<blockquote>\n<pre>\r\n[oracle@ora-vm1 ICC]$ sar -u 5 100\r\nLinux 2.6.18-92.1.18.el5 (ora-vm1.intra)        12\/23\/2008\r\n\r\n12:06:48 PM       CPU     %user     %nice   %system   %iowait    %steal     %idle\r\n12:06:53 PM       all      0.30      0.00      1.90      2.30      0.00     95.51\r\n12:06:58 PM       all      0.10      0.00      1.80      1.60      0.00     96.50\r\n12:07:03 PM       all      7.51      0.00     15.92      0.70      0.00     75.88\r\n12:07:08 PM       all     12.90      0.00     42.20      0.10      0.00     44.80\r\n12:07:13 PM       all     13.60      0.00     40.80      0.50      0.00     45.10\r\n12:07:18 PM       all     13.00      0.00     41.80      0.60      0.00     44.60\r\n12:07:23 PM       all     14.09      0.00     40.16      0.40      0.00     45.35\r\n12:07:28 PM       all     13.09      0.00     41.66      0.50      0.00     44.76\r\n12:07:33 PM       all     13.00      0.00     41.40      0.50      0.00     45.10\r\n12:07:38 PM       all     14.79      0.00     40.66      0.20      0.00     44.36\r\n12:07:43 PM       all     15.02      0.00     39.84      0.30      0.00     44.84\r\n12:07:48 PM       all     13.10      0.00     41.90      0.60      0.00     44.40\r\n12:07:53 PM       all     14.99      0.00     40.06      0.70      0.00     44.26\r\n12:07:58 PM       all      4.20      0.00     12.91      0.60      0.00     82.28\r\n12:08:03 PM       all      0.30      0.00      1.50      1.00      0.00     97.20\r\n<\/pre>\n<\/blockquote>\n","protected":false},"excerpt":{"rendered":"<p>I have finally found the chance to make my first steps in Java. Instead of &#8220;Hello World&#8221;, 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: [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[13,52,5],"tags":[],"class_list":["post-533","post","type-post","status-publish","format-standard","hentry","category-10g","category-java","category-oracle-database"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/posts\/533","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/comments?post=533"}],"version-history":[{"count":8,"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/posts\/533\/revisions"}],"predecessor-version":[{"id":918,"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/posts\/533\/revisions\/918"}],"wp:attachment":[{"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/media?parent=533"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/categories?post=533"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/tags?post=533"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}