Database Resident Connection Pooling (DRCP) hands-on

Preamble

Database Resident Connection Pooling (DRCP) is not more than a connection pool inside the database. Connection pooling is usually a feature provided by middle tier processes like I have already done in a pure Java program or in one of the well know servlet containers / application servers like Tomcat/JBoss or else.

So why implementing this connection pooling directly inside the database ? First to be able to use for application where it is not available, typical example provided by Oracle is web application where you often need small and quick database access to retrieve information. Ultimately we can also imagine clubbing multiple middle tier pools inside the same DRCP pool.

The clear added value is a decrease in database memory footprint as well as a decrease in CPU consumption. Each client process is using CPU ! One also very interesting added value is to keep the session open and your web application will not have the burden of creating a session each time it needs to connect to the database.

Each connection to the pool is handle by a Connection Broker that then delegate the client process to the chosen server process. Once the client disconnect or after a timeout the server process is released to the pool:

drcp01
drcp01

In 19.10 they have added two new initialization parameters to control number of processes to handle session authentication (different from the number of minimum and maximum sessions inside the pool):

SQL> show parameter min_auth_servers
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
min_auth_servers                     integer     1
SQL> show parameter max_auth_servers
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_auth_servers                     integer     25

In 19.11 they have added DRCP_DEDICATED_OPT parameter to have the pool behave like a dedicated server (so as a one-to-one client/server) in case you are below the maximum number of pool connection. This is again a trade between performance and resources consumption (the V$AUTHPOOL_STATS view is NOT existing, probably a bug):

SQL> show parameter drcp_dedicated_opt
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
drcp_dedicated_opt                   string      YES

My testing has been done on a 19.12 Oracle database running on a bare metal server with 12 cores and 64GB of memory.

Database Resident Connection Pooling creation

I have started to create my pool directly in the pluggable database I’m using but the message is clear, you must create the pool in the root container:

SQL> EXEC dbms_connection_pool.start_pool(pool_name => 'pool01');
BEGIN dbms_connection_pool.start_pool(pool_name => 'pool01'); END;
 
*
ERROR AT line 1:
ORA-56515: DRCP: Operation NOT allowed FROM a Pluggable DATABASE
ORA-06512: AT "SYS.DBMS_CONNECTION_POOL", line 4
ORA-06512: AT line 1

You also cannot (yet) change the pool name and only the default name (SYS_DEFAULT_CONNECTION_POOL) is accepted. As there is a parameter pool_name I have no doubt that one day we will be able to create multiple pools. So currently all you pooled database connection will go in same pool, cannot split them and create a bigger pool for a more important application:

SQL> EXEC dbms_connection_pool.start_pool(pool_name => 'pool01');
BEGIN dbms_connection_pool.start_pool(pool_name => 'pool01'); END;
 
*
ERROR AT line 1:
ORA-56501: DRCP: Pool startup failed
ORA-56500: DRCP: Pool NOT found
ORA-06512: AT "SYS.DBMS_CONNECTION_POOL", line 4
ORA-06512: AT line 1

Before (or after) starting the pool you can modify its parameters with either DBMS_CONNECTION_POOL.ALTER_PARAM or DBMS_CONNECTION_POOL.CONFIGURE_POOL:

SQL> exec dbms_connection_pool.configure_pool(pool_name => 'SYS_DEFAULT_CONNECTION_POOL', minsize => 1, maxsize => 10);
 
PL/SQL procedure successfully completed.
 
SQL> exec dbms_connection_pool.alter_param(pool_name => 'SYS_DEFAULT_CONNECTION_POOL', param_name => 'incrsize', param_value => 1);
 
PL/SQL procedure successfully completed.

Thne start the pool with:

SQL> EXEC dbms_connection_pool.start_pool(pool_name => 'SYS_DEFAULT_CONNECTION_POOL');
 
PL/SQL PROCEDURE successfully completed.
SQL> SET lines 200
SQL> col connection_pool FOR a30
SQL> SELECT connection_pool, status, minsize, maxsize, incrsize FROM dba_cpool_info;
 
CONNECTION_POOL                STATUS              MINSIZE    MAXSIZE   INCRSIZE
------------------------------ ---------------- ---------- ---------- ----------
SYS_DEFAULT_CONNECTION_POOL    ACTIVE                    1         10          1

At Linux level it will create background processes, called connection broker to handle client request and free server processes:

[oracle@server01 ~]$ ps -ef | grep ora_l00 | grep -v grep
oracle   10516     1  0 15:24 ?        00:00:00 ora_l000_orcl
oracle   10522     1  0 15:24 ?        00:00:00 ora_l001_orcl

Database Resident Connection Pooling testing

Even if the pool has been created in the root container you can obviously use it to connect to your pluggable database. You can either use easy connect string or define an TNS string in your tnsnames.ora:

pdb1_pool =
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)
                      (HOST=server01.domain.com)
                      (PORT=1531))
                      (CONNECT_DATA=(SERVICE_NAME=pdb1)
                      (SERVER=POOLED)))

To test it I have used the Oracle client I have on my Windows desktop as well as my own account (YJAQUIER) and a test account (TEST01):

PS C:\Users\yjaquier> sqlplus yjaquier/secure_password@server01.domain.com:1531/pdb1:pooled
 
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Oct 6 15:58:54 2021
Version 19.3.0.0.0
 
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
 
Last Successful login time: Wed Oct 06 2021 15:37:45 +02:00
 
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0
 
SQL>

Or

PS C:\Users\yjaquier> tnsping pdb1_pool
 
TNS Ping Utility for 64-bit Windows: Version 19.0.0.0.0 - Production on 12-OCT-2021 15:06:55
 
Copyright (c) 1997, 2019, Oracle.  All rights reserved.
 
Used parameter files:
C:\app\client\product\19.0.0\client_1\network\admin\sqlnet.ora
 
 
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp) (HOST=server01.domain.com) (PORT=1531)) (CONNECT_DATA=(SERVICE_NAME=pdb1) (SERVER=POOLED)))
OK (110 msec)
PS C:\Users\yjaquier> sqlplus test01/test01@pdb1_pool
 
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Oct 12 15:06:59 2021
Version 19.3.0.0.0
 
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
 
Last Successful login time: Tue Oct 12 2021 14:57:58 +02:00
 
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0
 
SQL>

Finally you have few views to follow connections and their respective pool usage (feel free to describe them to see all available columns):

SQL> set lines 200
SQL> col username for a15
SQL> col cclass_name for a15
SQL> select username, cclass_name, process_id, program, machine from v$cpool_conn_info;
 
USERNAME        CCLASS_NAME     PROCESS_ID               PROGRAM                                          MACHINE
--------------- --------------- ------------------------ ------------------------------------------------ ----------------------------------------------------------------
YJAQUIER        YJAQUIER.SHARED 21904:9308               sqlplus.exe                                      COMPANY\DESKTOP007
TEST01          TEST01.SHARED   9860:2396                sqlplus.exe                                      COMPANY\DESKTOP007
 
SQL> col pool_name for a30
SQL> select pool_name, num_open_servers, num_busy_servers, num_requests from v$cpool_stats;
 
POOL_NAME                      NUM_OPEN_SERVERS NUM_BUSY_SERVERS NUM_REQUESTS
------------------------------ ---------------- ---------------- ------------
SYS_DEFAULT_CONNECTION_POOL                   3                0            7
 
SQL> select * from v$cpool_cc_info;
 
POOL_NAME                      CCLASS_NAME         CON_ID
------------------------------ --------------- ----------
SYS_DEFAULT_CONNECTION_POOL    YJAQUIER.SHARED          0
SYS_DEFAULT_CONNECTION_POOL    TEST01.SHARED            0
 
SQL> select cclass_name, num_requests, num_hits, num_misses from v$cpool_cc_stats;
 
CCLASS_NAME     NUM_REQUESTS   NUM_HITS NUM_MISSES
--------------- ------------ ---------- ----------
YJAQUIER.SHARED            5          0          5
TEST01.SHARED              4          0          4

Simply stop the pool with:

SQL> EXEC dbms_connection_pool.stop_pool(pool_name => 'SYS_DEFAULT_CONNECTION_POOL');
 
PL/SQL PROCEDURE successfully completed.

References

About Post Author

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>