Oracle Database DRCP and Node.js: How to enable and use it

I’ve uploaded a video showing how to use Oracle Database Resident Connection Pooling (DRCP) with the Node.js node-oracledb driver.

This is not the video: it’s a random, eye-catching ‘pool’ photo by Lee Jeffs on Unsplash

For some background about connection pooling, see Always Use Connection Pools — and How. As part of that post I give an overview of DRCP:

…if the cluster grows, or connection pools were bigger, or if there are a huge number of connections from other applications all resulting in the database hardware running into memory issues, then there is always the possibility of using Database Resident Connection Pooling (DRCP). As shown in the diagram below, DRCP allows the database server processes that handle connections in the database to be shared as needed across all application processes, including when the application is scaled over multiple machines. Only active application connections need to use a server process. The idle application connections do not need to hold onto database resources, but will be allocated a database server process when they need to do database work. DRCP is most efficient when used in conjunction with application connection pools but also can benefit standalone connections. To use DRCP the application tier needs a simple connection string change and the setting of a connectionClass attribute. Setting that attribute now makes it easier to use DRCP in the future.

Database Resident Connection Pooling

Go watch the video

Here is the script for the video:

/*
 *  Using Database Resident Connection Pooling with Oracle Database 19c
 *  and node-oracledb
 *
 *  Christopher Jones, Jan 2023
 *  christopher.jones@oracle.com
 */
 
/*
 * Connect to the Oracle DB 19c CDB
 */

sqlplus /nolog
connect sys/oracle@localhost/orclcdb as sysdba

/*
 * Is DRCP running?
 */

select status from dba_cpool_info;

/*
 * Configure DRCP
 */

-- execute dbms_connection_pool.stop_pool()
-- execute dbms_connection_pool.restore_defaults()

-- Sample sizes  
execute dbms_connection_pool.alter_param(null, 'MAXSIZE', '100')
execute dbms_connection_pool.alter_param(null, 'MINSIZE', '100')
execute dbms_connection_pool.alter_param(null, 'NUM_CBROK', '4')

 
/*  
 * Check DRCP configuration
 */

select minsize, maxsize, num_cbrok from dba_cpool_info;
  
-- 19.10+
-- SQL> show parameter auth_servers
--
-- NAME                                 TYPE        VALUE
-- ------------------------------------ ----------- ------------------------------
-- max_auth_servers                     integer     25
-- min_auth_servers                     integer     1

-- Sample size
-- alter system set max_auth_servers=50 scope=spfile sid='*';
-- startup force

/*
 * Start DRCP
 */

execute dbms_connection_pool.start_pool()

select status from dba_cpool_info;


/*
 * Get an app, e.g. https://github.com/oracle/node-oracledb/blob/main/examples/webapp.js
 * (and copy demosetup.js and dbconfig.js too)
 *
 * Set the connection class in webapp.js.  This determines sharing of session
 * state information between connections.
 */

...
oracledb.connectionClass = 'MYAPPNAME'
...

/*
 * Run the app after editing dbconfig.js and/or setting credential environment variables.
 * The connection string should request a pooled server.
 */

export NODE_ORACLEDB_USER=cj
export NODE_ORACLEDB_PASSWORD=cj
export NODE_ORACLEDB_CONNECTIONSTRING=localhost/orclpdb1:pooled

node webapp.js

/*
 * Put the app under 'user' load
 */
ab -n 100 -c 4 http://127.0.0.1:7000/3

/*
 * Check the DRCP stats
 */

sqlplus /nolog
connect sys/oracle@localhost/orclcdb as sysdba

set echo off linesize 250 pagesize 1000
col cclass_name format a38
select cclass_name, num_requests, num_hits, num_misses, num_waits, num_authentications from v$cpool_cc_stats;

Resources

DRCP Technical Brief: Extreme Oracle Database Connection Scalability with Database Resident Connection Pooling (DRCP)

Oracle Database 19c DRCP documentation: Using Database Resident Connection Pool

node-oracledb documentation: Database Resident Connection Pooling (DRCP)

Blog post: Always Use Connection Pools — and How

You may also like this video: Getting started with Node.js and Oracle Cloud databases which covers some tips useful for both Cloud and on-premise database. An efficient application means you hold connections for shorter periods, meaning you can reduce connection pool sizes.

If you’re curious about the goings-on of Oracle Developers in their natural habitat, come join us on our public Slack channel!

If you haven’t already done so, you can sign up for an Oracle Cloud Free Tier account today. It’s not necessary to follow along to with this post, if you’re curious about how to get started with OCI, signing up is the first step!