Why you should always use connection pooling with Oracle XE

(Last Updated On: January 29, 2018)

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 test tries to simulate a low latency transaction environment, so the connection is leased for a very short amount 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 <em>processes</em> and <em>sessions</em> 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 cause 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.

If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.

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.

Subscribe to our Newsletter

* indicates required
10 000 readers have found this blog worth following!

If you subscribe to my newsletter, you'll get:
  • A free sample of my Video Course about running Integration tests at warp-speed using Docker and tmpfs
  • 3 chapters from my book, High-Performance Java Persistence, 
  • a 10% discount coupon for my book. 
Get the most out of your persistence layer!

Advertisements

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.