Table of contents
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:
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
