Over a million developers have joined DZone.

Why You Should Always Use Connection Pooling with Oracle XE

In the Oracle Express Edition, a connection pool fixes latency issues because connections are reused instead of being established on demand.

· Database Zone

Build fast, scale big with MongoDB Atlas, a hosted service for the leading NoSQL database. Try it now! Brought to you in partnership with MongoDB.

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
        } 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-]
    at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:553) ~[ojdbc6-]
    at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:254) ~[ojdbc6-]
    at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32) ~[ojdbc6-]
    at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:528) ~[ojdbc6-]
    at oracle.jdbc.pool.OracleDataSource.getPhysicalConnection(OracleDataSource.java:280) ~[ojdbc6-]
    at oracle.jdbc.pool.OracleDataSource.getConnection(OracleDataSource.java:207) ~[ojdbc6-]
    at oracle.jdbc.pool.OracleDataSource.getConnection(OracleDataSource.java:157) ~[ojdbc6-]
    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.

This article is part of the research I’m undergoing for my High-Performance Java Persistence book. If you enjoyed it reading it, you might also subscribe to follow my progress.

Now it's easier than ever to get started with MongoDB, the database that allows startups and enterprises alike to rapidly build planet-scale apps. Introducing MongoDB Atlas, the official hosted service for the database on AWS. Try it now! Brought to you in partnership with MongoDB.

oracle,rdbms,relational databases,latency,oracle xe,connection pooling

Published at DZone with permission of Vlad Mihalcea, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

The best of DZone straight to your inbox.

Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}