Implicit Connection Pooling when connections overload your database

13 min readJun 18, 2024

When a long-running application keeps a database connection open for the app lifetime, it is likely that the connection is often idle and not being used to execute SQL statements. However this holds the connection’s server process and session memory open on the database host, ultimately limiting the number of other users who can connect. When re-architecting to use an Oracle driver connection pool isn’t possible, a simple connection string change can make use of Oracle Database 23ai “Implicit Connection Pooling” to share the database host resources. This reduces the memory required, and allows the database to be more scalable.

My previous blog post DRCP helps apps that don’t use a Connection Pool showed an example which connected and disconnected frequently but didn’t use connection pooling. It explained how the app could benefit from Database Resident Connection Pooling (DRCP). This is because those frequent application disconnection calls let the database know when it is safe to reuse database server processes for other application users.

The following example differs because a disconnection call happens only when a long-running application terminates. Pure DRCP won’t help because there is no connection-usage “boundary” to indicate when to reuse the database server processes. Luckily, Oracle Database 23ai “Implicit Connection Pooling” can figure it out.

Let’s look at a demo. The following simple Python app long_run.py opens a connection and then loops. In each iteration a query is executed and there is a sleep simulating user "think time" between queries. The connection is closed when the app finishes (handled by the end of the with context block). This simulates a long-running app that holds onto a connection even when not executing a SQL statement:

# long_run.py

import os
import time

import oracledb
import sample_env  # Contains credentials, see python-oracledb samples

# Number of queries to execute in the life of this script
NUMSQLS = globals().get("NUMSQLS", 0)

# Number of seconds to wait between executing SQL statements
SLEEPTIME = globals().get("SLEEPTIME", 0)

# The name of the end "user", unique per execution of this script
APPUSERNAME = globals().get("APPUSERNAME", None)

sql = """select unique sid||'-'||serial# as sidser,
                       current_timestamp as ct
         from v$session_connect_info
         where sid = sys_context('USERENV', 'SID')"""

with oracledb.connect(
    user=sample_env.get_main_user(),
    password=sample_env.get_main_password(),
    dsn=sample_env.get_connect_string(),
) as connection:
    for i in range(NUMSQLS):
        with connection.cursor() as cursor:
            for s, d in cursor.execute(sql):
                print(f"{APPUSERNAME} sid-ser {s} at time {d}")
        time.sleep(SLEEPTIME)

The application query gets the current time and the Session Identifier/Serial Number — this pair shows the database server process session in use. Each iteration’s query will output a line like:

User01 sid-ser 407-62786 at time 2024-06-11 20:29:34.632666

The connection string is a simple one like localhost/orclpdb1 . There is no DRCP involved.

A helper script, runner.py, is used to simulate a number of users concurrently running long_run.py. The helper script invokes long_run.py 10 times (set in "NUMUSERS") via the use of threads. Each invocation of long_run.py is passed a unique "user name" e.g. User01 (via "APPUSERNAME"), the number of SQL statements to execute ("NUMSQLS"), and the sleep time ("SLEEPTIME") between executions. The sleep time emulates the application user being idle and not executing SQL statements. The helper script is:

# runner.py

import threading
import time

import sample_env  # Contains credentials, see python-oracledb samples

NUMUSERS = 10      # number of times to concurrently invoke long_run.py

def start_app(tn):
    app_globals = {
        "APPUSERNAME": "User{:02d}".format(tn + 1),  # Name of the "user" running long_run.py
        "NUMSQLS": 5,     # Number of queries to execute in the life of long_run.py
        "SLEEPTIME": 5,   # Number of seconds to sleep between SQL statements
    }
    exec(open("long_run.py").read(), app_globals)

def start_workload():
    thread = []
    for i in range(NUMUSERS):
        t = threading.Thread(target=start_app, args=(i,))
        t.start()
        thread.append(t)

    for i in range(NUMUSERS):
        thread[i].join()

if __name__ == "__main__":

    print(f"Using connection string: {sample_env.get_connect_string()}")

    start = time.time()
    start_workload()
    elapsed = time.time() - start
    print("All done!")
    print("Time {:04.2f} seconds".format(elapsed))

As coded, each long_run.py process will run 5 queries with a sleep time of 5 seconds between each query. (In practice, your applications may stay open much longer). Add in a small amount of time for overheads, connecting, and executing statements gives a total expected end-to-end time of bit more than 25 seconds:

$ python3 runner.py
Using connection string: localhost/orclpdb1
User01 sid-ser 407-62786 at time 2024-06-11 20:29:34.632666
User03 sid-ser 26-48119  at time 2024-06-11 20:29:34.802162
User02 sid-ser 172-37948 at time 2024-06-11 20:29:34.973973
User04 sid-ser 272-26116 at time 2024-06-11 20:29:35.146129
User05 sid-ser 398-8361  at time 2024-06-11 20:29:35.315068
User07 sid-ser 33-58745  at time 2024-06-11 20:29:35.485322
User08 sid-ser 154-64891 at time 2024-06-11 20:29:35.667774
User09 sid-ser 268-40439 at time 2024-06-11 20:29:35.837789
User10 sid-ser 408-62081 at time 2024-06-11 20:29:36.013276
User06 sid-ser 10-45069  at time 2024-06-11 20:29:36.189829
User01 sid-ser 407-62786 at time 2024-06-11 20:29:39.653528
User03 sid-ser 26-48119  at time 2024-06-11 20:29:39.821690
User02 sid-ser 172-37948 at time 2024-06-11 20:29:39.990345
User04 sid-ser 272-26116 at time 2024-06-11 20:29:40.162383
User05 sid-ser 398-8361  at time 2024-06-11 20:29:40.330363
User07 sid-ser 33-58745  at time 2024-06-11 20:29:40.503150
User08 sid-ser 154-64891 at time 2024-06-11 20:29:40.686834
User09 sid-ser 268-40439 at time 2024-06-11 20:29:40.854444
User10 sid-ser 408-62081 at time 2024-06-11 20:29:41.029533
User06 sid-ser 10-45069  at time 2024-06-11 20:29:41.206820
User01 sid-ser 407-62786 at time 2024-06-11 20:29:44.666896
User03 sid-ser 26-48119  at time 2024-06-11 20:29:44.835387
User02 sid-ser 172-37948 at time 2024-06-11 20:29:45.005330
User04 sid-ser 272-26116 at time 2024-06-11 20:29:45.177302
User05 sid-ser 398-8361  at time 2024-06-11 20:29:45.343248
User07 sid-ser 33-58745  at time 2024-06-11 20:29:45.520129
User08 sid-ser 154-64891 at time 2024-06-11 20:29:45.704843
User09 sid-ser 268-40439 at time 2024-06-11 20:29:45.868719
User10 sid-ser 408-62081 at time 2024-06-11 20:29:46.046426
User06 sid-ser 10-45069  at time 2024-06-11 20:29:46.225250
User01 sid-ser 407-62786 at time 2024-06-11 20:29:49.679691
User03 sid-ser 26-48119  at time 2024-06-11 20:29:49.848716
User02 sid-ser 172-37948 at time 2024-06-11 20:29:50.021498
User04 sid-ser 272-26116 at time 2024-06-11 20:29:50.191881
User05 sid-ser 398-8361  at time 2024-06-11 20:29:50.359139
User07 sid-ser 33-58745  at time 2024-06-11 20:29:50.542895
User08 sid-ser 154-64891 at time 2024-06-11 20:29:50.715070
User09 sid-ser 268-40439 at time 2024-06-11 20:29:50.880684
User10 sid-ser 408-62081 at time 2024-06-11 20:29:51.062274
User06 sid-ser 10-45069  at time 2024-06-11 20:29:51.245149
User01 sid-ser 407-62786 at time 2024-06-11 20:29:54.694733
User03 sid-ser 26-48119  at time 2024-06-11 20:29:54.866731
User02 sid-ser 172-37948 at time 2024-06-11 20:29:55.038168
User04 sid-ser 272-26116 at time 2024-06-11 20:29:55.206117
User05 sid-ser 398-8361  at time 2024-06-11 20:29:55.372266
User07 sid-ser 33-58745  at time 2024-06-11 20:29:55.560076
User08 sid-ser 154-64891 at time 2024-06-11 20:29:55.730851
User09 sid-ser 268-40439 at time 2024-06-11 20:29:55.899642
User10 sid-ser 408-62081 at time 2024-06-11 20:29:56.078866
User06 sid-ser 10-45069  at time 2024-06-11 20:29:56.260751
All done!
Time 26.85 seconds

The important point is that each of the 10 processes will open a connection and keep it open for the duration the long_run.py app run. This is shown by each user script always executing its queries with the same session identifier/serial number combination. For example User01 always uses 407-62786. This is 10 connections open all the time, each of which has a corresponding server process and session memory consuming database host resources.

Oracle Database 23ai Implicit Connection Pooling

Implicit Connection Pooling is an Oracle Database 23ai feature that makes use of Database Resident Connection Pooling to share database server processes and session memory. It is supported by the popular Oracle Database drivers, including python-oracledb, node-oracledb and JDBC.

Implicit Connection Pooling is suitable for applications that hold onto connections when not doing database work. This includes applications which implement their own connection pool solution holding the underlying connections to the database open for the life of the pool.

Implicit Connection Pooling works by transparently recognizing the boundary when an application isn’t using an open connection. It then lets another application connection use the server process and session memory of the first (currently idle) connection. When the first application subsequently initiates another database request, a free database server process is allocated back to it and the application continues without knowing about the temporary ‘theft’ of the server process. No application changes are needed. The database’s resources are shared, allowing greater scalability.

Implicit Connection Pooling differs from pure DRCP because the mapping and unmapping of database server processes is done implicitly by Oracle. With pure DRCP, the mapping and unmapping only happens when the application initiates get-connection and close-connection calls.

Because long_run.py only has one open/close pair (the close is internally done at the end of the with context), it is an ideal candidate for Implicit Connection Pooling. All that is needed is to start DRCP in the database, change the connection string to use a DRCP pooled server, and additionally set a POOL_BOUNDARY parameter to the connection string. The application code is not modified. Re-running the demo gives the output:

$ python3 runner.py
Using connection string: localhost/orclpdb1:pooled?pool_boundary=statement
User01 sid-ser 399-25678 at time 2024-06-11 20:28:46.803668
User02 sid-ser 399-25678 at time 2024-06-11 20:28:46.830118
User05 sid-ser 399-25678 at time 2024-06-11 20:28:46.840759
User04 sid-ser 399-25678 at time 2024-06-11 20:28:46.867337
User03 sid-ser 152-55977 at time 2024-06-11 20:28:46.908656
User08 sid-ser 152-55977 at time 2024-06-11 20:28:46.978751
User09 sid-ser 152-55977 at time 2024-06-11 20:28:47.045773
User06 sid-ser 152-55977 at time 2024-06-11 20:28:47.106757
User10 sid-ser 152-55977 at time 2024-06-11 20:28:47.169244
User07 sid-ser 152-55977 at time 2024-06-11 20:28:47.229357
User01 sid-ser 152-55977 at time 2024-06-11 20:28:51.844053
User02 sid-ser 399-25678 at time 2024-06-11 20:28:51.844449
User05 sid-ser 152-55977 at time 2024-06-11 20:28:51.855879
User04 sid-ser 152-55977 at time 2024-06-11 20:28:51.883601
User03 sid-ser 152-55977 at time 2024-06-11 20:28:51.940661
User08 sid-ser 152-55977 at time 2024-06-11 20:28:51.990124
User09 sid-ser 152-55977 at time 2024-06-11 20:28:52.058879
User06 sid-ser 152-55977 at time 2024-06-11 20:28:52.120180
User10 sid-ser 152-55977 at time 2024-06-11 20:28:52.182709
User07 sid-ser 152-55977 at time 2024-06-11 20:28:52.243295
User01 sid-ser 152-55977 at time 2024-06-11 20:28:56.874489
User02 sid-ser 399-25678 at time 2024-06-11 20:28:56.874487
User04 sid-ser 399-25678 at time 2024-06-11 20:28:56.895112
User03 sid-ser 399-25678 at time 2024-06-11 20:28:56.954245
User05 sid-ser 277-27334 at time 2024-06-11 20:28:56.969050
User08 sid-ser 277-27334 at time 2024-06-11 20:28:56.997923
User09 sid-ser 277-27334 at time 2024-06-11 20:28:57.072845
User06 sid-ser 277-27334 at time 2024-06-11 20:28:57.131504
User10 sid-ser 277-27334 at time 2024-06-11 20:28:57.195656
User07 sid-ser 277-27334 at time 2024-06-11 20:28:57.257700
User01 sid-ser 399-25678 at time 2024-06-11 20:29:01.903889
User02 sid-ser 277-27334 at time 2024-06-11 20:29:01.903889
User04 sid-ser 152-55977 at time 2024-06-11 20:29:01.910282
User03 sid-ser 152-55977 at time 2024-06-11 20:29:01.968523
User05 sid-ser 152-55977 at time 2024-06-11 20:29:01.997715
User08 sid-ser 152-55977 at time 2024-06-11 20:29:02.011130
User09 sid-ser 152-55977 at time 2024-06-11 20:29:02.086866
User06 sid-ser 152-55977 at time 2024-06-11 20:29:02.139241
User10 sid-ser 152-55977 at time 2024-06-11 20:29:02.209716
User07 sid-ser 152-55977 at time 2024-06-11 20:29:02.271144
User01 sid-ser 152-55977 at time 2024-06-11 20:29:06.940760
User02 sid-ser 277-27334 at time 2024-06-11 20:29:06.940760
User04 sid-ser 399-25678 at time 2024-06-11 20:29:06.940760
User03 sid-ser 399-25678 at time 2024-06-11 20:29:06.982961
User05 sid-ser 399-25678 at time 2024-06-11 20:29:07.010853
User08 sid-ser 399-25678 at time 2024-06-11 20:29:07.023677
User09 sid-ser 399-25678 at time 2024-06-11 20:29:07.102189
User06 sid-ser 399-25678 at time 2024-06-11 20:29:07.152508
User10 sid-ser 399-25678 at time 2024-06-11 20:29:07.223001
User07 sid-ser 399-25678 at time 2024-06-11 20:29:07.284733
All done!
Time 25.96 seconds

This shows server process and session memory being reused across different users’ queries, for example the first two lines have the same session identifier/serial number:

User01 sid-ser 399-25678 at time 2024-06-11 20:28:46.803668
User02 sid-ser 399-25678 at time 2024-06-11 20:28:46.830118

If you crunch the output, you can see that in this run only three servers are used for all queries being executed. The session identifier/serial number pairs are:

152-55977
277-27334
399-25678

This is many fewer than the ten servers needed without Implicit Connection Pooling. Your absolute results will be different, depending on factors like timing.

The total time difference between the two runs is close but in this small test isn’t significant for comparison. In general, because Implicit Connection Pooling shares resources and the Oracle stack has to do some extra work, it’s possible the total time will be slower when moving to Implicit Connection Pooling — if the difference is even accurately measurable. This would depend on how frequently applications execute SQL. However, the benefit is that the database tier uses less memory due to the lower number of server processes, so it may be more efficient — and it certainly can handle more user connections from other applications.

Configuring Implicit Connection Pooling

DRCP provides the configurable pool of database server processes used by Implicit Connection Pooling. DRCP needs to be enabled, monitored, and tuned in the database, see the technical brief Extreme Oracle Database Connection Scalability with Database Resident Connection Pooling.

On the application side, your connection string simply needs to request a DRCP pooled server, e.g. with “:pooled" or "(SERVER=POOLED)". It additionally needs to contain a new POOL_BOUNDARY parameter. The connection string used in this blog post was an Easy Connect string localhost/orclpdb1:pooled?pool_boundary=statement. In a tnsnames.ora file, an equivalent connect descriptor would contain "...(POOL_BOUNDARY=STATEMENT)..."

The POOL_BOUNDARY parameter can have the value STATEMENT or TRANSACTION:

STATEMENT: a connection is released back to the DRCP connection pool when the connection is implicitly stateless, i.e. when there are no active cursors in the connection (all the rows of the cursors have been internally fetched), no active transactions, no temporary tables, and no temporary LOBs.
TRANSACTION: a connection is released back to the DRCP connection pool when a commit or rollback is initiated by the app. It is recommended to not enable any driver “autocommit” setting when using Implicit Connection Pooling. If you do, then you will be unable to fetch any data that requires multiple round-trips to the database, such as streaming LOB data.

In line with standard DRCP recommendations for security, you should add a POOL_CONNECTION_CLASS parameter to the connection string, using the same value for all applications that are alike, for example:

localhost/orclpdb1:pooled?pool_boundary=statement&pool_connection_class=myappname

The DRCP “purity” used by Implicit Connection Pooling defaults to SELF, which allows reuse of the server process session memory. Adding the connection string parameter POOL_PURITY=NEW will change this and cause each use of a connection to recreate the session memory.

You can optionally create a PL/SQL package ORA_CPOOL_STATE with procedures to get and reset connection session state, see the Oracle Call Interface documentation.

When not to use Implicit Connection Pooling

If you can restructure your application to use an Oracle-provided driver connection pool, this can be the most efficient outcome. A driver connection pool provides resource sharing, and provides Oracle Database high availability features. Using a driver connection pool (such as the python-oracledb connection pool or node-oracledb connection pool) is often best suited for multi-user applications, but even single user applications that infrequently use connections can benefit from them. The blog post Desktop applications with node-oracledb and electron shows one such scenario. If an application is efficiently using an Oracle application connection pool, and getting and releasing connections frequently, then Implicit Connection Pooling provides no benefit.

If your application can’t be changed to use an Oracle driver connection pool, but the app opens and closes connections frequently, then you may be able to benefit from DRCP directly without enabling Implicit Connection Pooling. See my blog post DRCP helps apps that don’t use a Connection Pool.

At an application level, there are some cases where Implicit Connection Pooling isn’t a good match. As you can see from the example in this post, the session identifier and serial number that a “user” saw for each of their SQL statements varied during the life of that user’s script. So if your app relies on the values not changing, then Implicit Connection Pooling isn’t a good fit.

Another example of a fit that may have issues, is using a statement boundary of TRANSACTION when multiple cursors are in use by the application, or when you are streaming LOBs. In this scenario any application commit can invalidate any open cursors, and also prevent LOB streaming.

You should thoroughly test your application when using Implicit Connection Pooling to ensure that the internal reuse of database servers and sessions does not cause any problems.

Conclusion

Implicit Connection Pooling allows applications that hold connections open for a long time to share their database server processes and session memory. This reduces memory pressure on the database host, and makes the overall system more scalable. Implicit Connection Pooling can be helpful for apps that open single connections, or that implement their own connection pooling solution instead of using an Oracle-provided driver connection pool. It is supported by the popular Oracle Database drivers.

This post discussed how Implicit Connection Pooling makes use of DRCP. It can additionally make use of PRCP, which is available when your system uses Oracle Connection Manager in Traffic Director Mode.

For a video on Implicit Connection Pooling see Effortless Connection Management with Implicit Pooling in Oracle Database 23c by my colleague Sharad Chandran R.

References

Video: Effortless Connection Management with Implicit Pooling in Oracle Database 23c (video).
Python-oracledb Documentation: Implicit Connection Pooling.
Node-oracledb documentation: Implicit Connection Pooling.
Blog post: Always Use Connection Pools — and How.
Blog post: DRCP helps apps that don’t use a Connection Pool
Technical Brief: Extreme Oracle Database Connection Scalability with Database Resident Connection Pooling (DRCP).
Technical Brief: CMAN-TDM — An Oracle Database connection proxy for scalable and highly available applications.