Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

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

Read why times series is the fastest growing database category.

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.

If you enjoy reading this article, you might want to subscribe to my newsletter and get a discount for my book as well.

Vlad Mihalcea&apos;s Newsletter


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.

Learn how to get 20x more performance than Elastic by moving to a Time Series database.

Topics:
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.

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

{{ parent.tldr }}

{{ parent.urlSource.name }}