Over a million developers have joined DZone.

Why You Should Always Use Connection Pooling with Oracle XE

DZone's Guide to

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
Free Resource

Download the Guide to Open Source Database Selection: MySQL vs. MariaDB and see how the side-by-side comparison of must-have features will ease the journey. Brought to you in partnership with MariaDB.

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 and that’s causing a lot of connection establishing overhead on both the driver as on the server side.

If you enjoyed this article, I bet you are going to love my book as well.

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.

Interested in reducing database costs by moving from Oracle Enterprise to open source subscription?  Read the total cost of ownership (TCO) analysis. Brought to you in partnership with MariaDB.

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

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

Opinions expressed by DZone contributors are their own.


Dev Resources & Solutions Straight to Your Inbox

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 }}