Introduction
Oracle Express Edition is the free version of Oracle Enterprise Edition and its smaller size makes it very convenient for testing various Oracle functionalities.
According to Oracle documentation, the Express Edition can use at most one CPU and 1 GB of RAM, but in reality there are other limitations that are not always obvious.
Database connection handling anomaly
The following tests tries to simulate a low latency transaction environment, so the connection is leased for a very short ammount of time:
private void simulateLowLatencyTransactions(
DataSource dataSource, int waitMillis)
throws SQLException {
for (int i = 0; i < callCount; i++) {
try {
try (Connection connection =
dataSource.getConnection()) {
//Let's assume we are running a
//short-lived transaction
sleep(waitMillis);
}
} catch (SQLException e) {
LOGGER.error("Exception on iteration " + i, e);
}
}
}This test works fine until lowering the waiting time beyond a certain threshold value, in which case the database sporadically starts throwing the following exception:
ERROR [main]: c.v.b.h.j.c.OracleConnectionCallTest - Exception on iteration 111
java.sql.SQLException: Listener refused the connection with the following error:
ORA-12516, TNS:listener could not find available handler with matching protocol stack
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:489) ~[ojdbc6-11.2.0.4.jar:11.2.0.4.0]
at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:553) ~[ojdbc6-11.2.0.4.jar:11.2.0.4.0]
at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:254) ~[ojdbc6-11.2.0.4.jar:11.2.0.4.0]
at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32) ~[ojdbc6-11.2.0.4.jar:11.2.0.4.0]
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:528) ~[ojdbc6-11.2.0.4.jar:11.2.0.4.0]
at oracle.jdbc.pool.OracleDataSource.getPhysicalConnection(OracleDataSource.java:280) ~[ojdbc6-11.2.0.4.jar:11.2.0.4.0]
at oracle.jdbc.pool.OracleDataSource.getConnection(OracleDataSource.java:207) ~[ojdbc6-11.2.0.4.jar:11.2.0.4.0]
at oracle.jdbc.pool.OracleDataSource.getConnection(OracleDataSource.java:157) ~[ojdbc6-11.2.0.4.jar:11.2.0.4.0]
at com.vladmihalcea.book.high_performance_java_persistence.jdbc.connection.OracleConnectionCallTest.simulateLowLatencyTransactions(OracleConnectionCallTest.java:50) [test-classes/:na]
at com.vladmihalcea.book.high_performance_java_persistence.jdbc.connection.OracleConnectionCallTest.testConnections(OracleConnectionCallTest.java:40) [test-classes/:na]Although the code is single-threaded, Oracle starts complaining that the connection request listener cannot find a process handler for serving the incoming request.
This assumption can be proven by raising the processes and sessions parameters to a higher value:
alter system set processes=1000 scope=spfile; alter system set sessions=1000 scope=spfile;
With these new settings, the code runs fine and no exception is issued. Although increasing the processes and sessions limits makes the issue go away, this solution is only a work-around and it only raises the connection threshold instead of addressing the root cause.
One possible explanation is given by this IBM troubleshooting note, suggesting that the connection listener might not be instantly notified by the connection closing events. This might causes the connection listener to wrongly assert the actual connection count and to assume that the maximum number of processes has already been reached.
On Oracle 11g Enterprise Edition this issue is not replicable.
The fix
Astute readers will notice the problem when looking on the exception stack-trace. The OracleDataSource doesn’t offer any connection pooling mechanism and that’s causing a lot of connection establishing overhead on both the driver as on the server side.
Using a connection pool fixes this issue because connections are reused instead of being established on demand. The connection pool dramatically reduces the connection acquire time, which also leads to lower transaction latencies and better throughput.
| Reference: | Why you should always use connection pooling with Oracle XE from our JCG partner Vlad Mihalcea at the Vlad Mihalcea’s Blog blog. |
