We had a need to understand the overhead of creating connections versus using a pool. Connection pooling is most beneficial when using very short lived database sessions. If the associated database request takes longer than a few seconds, we needed to understand what positive impact pooling would have. In other words, if the request takes a minute, a second to open the connection is less impactful, reducing the benefit of pooling.<\/p>\n
Short of running a complete regression test, we came up with what is below to drive conversation…<\/p>\n
Request = http:\/\/host:port\/threadtest.jsp?count=50&pool=false<\/p>\n
Drop this in the autodeploy directory\u2026<\/p>\n
\r\n<%@ page import=\"java.sql.*\"%>\r\n<%@ page import=\"javax.sql.*\"%>\r\n<%@ page import=\"javax.naming.*\"%>\r\n\r\n<%\r\nclass TestConns implements Runnable{\r\n boolean pool;\r\n weblogic.logging.NonCatalogLogger ncl = new weblogic.logging.NonCatalogLogger(\"Thread Test\");\r\n TestConns(boolean pool) {\r\n this.pool = pool;\r\n Thread t = new Thread(this);\r\n t.start();\r\n }\r\n public void run() {\r\n ncl.info(\"pool = \" + pool + \" start\");\r\n try {\r\n if (this.pool) {\r\n InitialContext ic = new InitialContext();\r\n DataSource ds = (DataSource)ic.lookup(\"jdbc\/mdsrep\");\r\n Connection conn = ds.getConnection();\r\n conn.close();\r\n }\r\n else {\r\n try {\r\n Connection conn = DriverManager.getConnection(\"jdbc:oracle:thin:user\/password@dbhost:dbport\/dbservicename.domainname\");\r\n conn.close();\r\n }\r\n catch (Exception e3) {\r\n ncl.error(e3.getMessage());\r\n return;\r\n }\r\n }\r\n }\r\n catch (Exception e) {\r\n ncl.error(e.getMessage());\r\n }\r\n ncl.info(\"pool = \" + pool + \" end\");\r\n }\r\n}\r\nfor (int i = 1; i <= Integer.parseInt(request.getParameter(\"count\")); i++) {\r\n TestConns t = new TestConns(Boolean.parseBoolean(request.getParameter(\"pool\")));\r\n}\r\n%>\r\n<\/pre>\n\u2026and you will see log entries such as the following when requesting the page\u2026<\/p>\n
\r\n\/u01\/app\/oracle\/product\/fmw: find . -mmin -1 -type f | xargs egrep 'pool' | grep 12:24 | tail -1\r\n.\/user_projects\/domains\/assortplan\/servers\/AdminServer\/logs\/AdminServer.log00020:#### <> <> <673ae030-d2ab-456d-9ffa-2adfa36aa033-000002c8> <1475771052021> \r\n\/u01\/app\/oracle\/product\/fmw:\r\n<\/pre>\nWe saw 247 milliseconds per physical connection…<\/p>\n
\r\n\/u01\/app\/oracle\/product\/fmw: find . -mmin -30 -type f | xargs egrep '12:24.*Thread Test' | awk '{gsub(\"<\",\"\",$0);gsub(\">\",\"\",$0);if ($20 ~ \"start\") {s[$12]=$15} else {t[$12]=$15}} END {for (i in t) {if (s[i] != \"\") {print i,t[i]-s[i]}}}' | sort | awk '{s+=$2} END {print s\/NR}'\r\n247.537\r\n<\/pre>\n…and 5 milliseconds from a pool…<\/p>\n
\r\n\/u01\/app\/oracle\/product\/fmw: find . -mmin -30 -type f | xargs egrep ' 1:24.*Thread Test' | awk '{gsub(\"<\",\"\",$0);gsub(\">\",\"\",$0);if ($20 ~ \"start\") {s[$12]=$15} else {t[$12]=$15}} END {for (i in t) {if (s[i] != \"\") {print i,t[i]-s[i]}}}' | sort | awk '{s+=$2} END {print s\/NR}'\r\n5.58\r\ncmhlcarchapp02:oracle:\/u01\/app\/oracle\/product\/fmw:\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"We had a need to understand the overhead of creating connections versus using a pool. Connection pooling is most beneficial when using very short lived database sessions. If the associated database request takes longer than a few seconds, we needed…<\/p>\n