DBA Hub

📋Steps in this guide1/3

Database Resident Connection Pool (DRCP) Enhancements in Oracle Database 23ai/26ai

Oracle database 23ai/26ai enhanced the Database Resident Connection Pool (DRCP) functionality to include multiple named pools and implicit connection pooling.

oracle 23configurationintermediate
by OracleDba
17 views
1

Multiple Named Connection Pools

In previous versions of the database the Database Resident Connection Pool (DRCP) functionality only supported a single default pool. In Oracle 23ai/26ai we can create multiple named connection pools, each with a different configuration, allowing use to tailor each pool for a specific application if we need to. We connect to the root container and display the available pools using the view. We can see only the default pool is present. We create a new pool using the procedure in the package. Most of the parameters have default values, so we can create a new connection pool just by providing the pool name. We can see all the default values in the view. The procedure allows us to configure an individual pool parameter, while the procedure allows us to configure all pool parameters in one call. The default settings are restored using the procedure. The pool parameters that are currently supported are listed below. We start and stop a connection pool using the and procedures. The parameter defaults to the default pool. When we are using the default pool we can connect by adding "pooled" to the end of the EZConnect URL. To connect to a named pool we need to specify the as and the in the connect description. Here is an example of such a "tnsnames.ora" file entry. This allows us to connect to the named pool using a TNS alias. Once we connect we can see our session is pooled. We can look at the , and views to check statistics for the pool usage. We remove a pool using the following commands.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
conn / as sysdba

column connection_pool format a30

select connection_pool,
       status
from   dba_cpool_info;

CONNECTION_POOL                STATUS
------------------------------ ----------------
SYS_DEFAULT_CONNECTION_POOL    INACTIVE

SQL>

PROCEDURE ADD_POOL
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 POOL_NAME                      VARCHAR2                IN
 MINSIZE                        BINARY_INTEGER          IN     DEFAULT
 MAXSIZE                        BINARY_INTEGER          IN     DEFAULT
 INCRSIZE                       BINARY_INTEGER          IN     DEFAULT
 SESSION_CACHED_CURSORS         BINARY_INTEGER          IN     DEFAULT
 INACTIVITY_TIMEOUT             BINARY_INTEGER          IN     DEFAULT
 MAX_THINK_TIME                 BINARY_INTEGER          IN     DEFAULT
 MAX_USE_SESSION                BINARY_INTEGER          IN     DEFAULT
 MAX_LIFETIME_SESSION           BINARY_INTEGER          IN     DEFAULT
 MAX_TXN_THINK_TIME             BINARY_INTEGER          IN     DEFAULT

exec sys.dbms_connection_pool.add_pool('test_pool_1');


select *
from   dba_cpool_info
where  connection_pool = 'TEST_POOL_1';

CONNECTION_POOL                STATUS              MINSIZE    MAXSIZE   INCRSIZE
------------------------------ ---------------- ---------- ---------- ----------
SESSION_CACHED_CURSORS INACTIVITY_TIMEOUT MAX_THINK_TIME MAX_USE_SESSION
---------------------- ------------------ -------------- ---------------
MAX_LIFETIME_SESSION  NUM_CBROK MAXCONN_CBROK MAX_TXN_THINK_TIME     CON_ID
-------------------- ---------- ------------- ------------------ ----------
TEST_POOL_1                    INACTIVE                  0         40          2
                    20                300            120          500000
               86400          1         40000                  0          1


SQL>

PROCEDURE CONFIGURE_POOL
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 POOL_NAME                      VARCHAR2                IN     DEFAULT
 MINSIZE                        BINARY_INTEGER          IN     DEFAULT
 MAXSIZE                        BINARY_INTEGER          IN     DEFAULT
 INCRSIZE                       BINARY_INTEGER          IN     DEFAULT
 SESSION_CACHED_CURSORS         BINARY_INTEGER          IN     DEFAULT
 INACTIVITY_TIMEOUT             BINARY_INTEGER          IN     DEFAULT
 MAX_THINK_TIME                 BINARY_INTEGER          IN     DEFAULT
 MAX_USE_SESSION                BINARY_INTEGER          IN     DEFAULT
 MAX_LIFETIME_SESSION           BINARY_INTEGER          IN     DEFAULT
 MAX_TXN_THINK_TIME             BINARY_INTEGER          IN     DEFAULT

exec sys.dbms_connection_pool.start_pool('test_pool_1');


select connection_pool,
       status
from   dba_cpool_info;

CONNECTION_POOL                STATUS
------------------------------ ----------------
SYS_DEFAULT_CONNECTION_POOL    INACTIVE
TEST_POOL_1                    ACTIVE

SQL>


exec sys.dbms_connection_pool.stop_pool('test_pool_1');


select connection_pool,
       status
from   dba_cpool_info;

CONNECTION_POOL                STATUS
------------------------------ ----------------
SYS_DEFAULT_CONNECTION_POOL    INACTIVE
TEST_POOL_1                    INACTIVE

SQL>

conn testuser1/testuser1@//localhost:1521/freepdb1:pooled

TEST_POOL_1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = FREEPDB1)
      (SERVER = POOLED)
      (POOL_NAME = TEST_POOL_1)
    )
  )

SQL> conn testuser1/testuser1@test_pool_1
Connected.
SQL>

conn / as sysdba

select server
from   v$session
where  username = 'TESTUSER1';

SERVER
---------
POOLED

SQL>

exec sys.dbms_connection_pool.stop_pool('test_pool_1');
exec sys.dbms_connection_pool.remove_pool('test_pool_1');
2

PDB-Level Connection Pools

By default connection pools are managed at the root container level, but this behaviour can be changed by setting the parameter to true, as demonstrated below. By default PDB-level connection pools are not available. An attempt to create one will result in the following error. Before we can use PDB-level connection pools we must set some parameters in the root container. Once the parameter is set to true we can no longer manage connection pools from the root container. Once connected to the PDB we can manage the pools as described in the previous section. To clean up and revert to the previous state we need to remove the pool and reset the parameter.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
SQL> exec sys.dbms_connection_pool.add_pool('test_pool_1');
BEGIN sys.dbms_connection_pool.add_pool('test_pool_1'); END;

*
ERROR at line 1:
ORA-56515: DRCP: Operation not allowed from a Pluggable Database
ORA-06512: at "SYS.DBMS_CONNECTION_POOL", line 3
ORA-06512: at line 1

SQL>

conn / as sysdba
alter system set connection_brokers='((type=pooled)(brokers=1)(connections=40000))';
alter system set enable_per_pdb_drcp=true scope=spfile;
shutdown immediate;
startup;

conn / as sysdba
exec sys.dbms_connection_pool.add_pool('test_pool_1');

BEGIN sys.dbms_connection_pool.add_pool('test_pool_1'); END;

*
ERROR at line 1:
ORA-56615: DRCP: Operation not allowed from the ROOT
ORA-06512: at "SYS.DBMS_CONNECTION_POOL", line 3
ORA-06512: at line 1


SQL>

conn sys/SysPassword1@localhost:1521/freepdb1 as sysdba

exec sys.dbms_connection_pool.add_pool('test_pool_1');


select connection_pool,
       status
from   dba_cpool_info;

CONNECTION_POOL                STATUS
------------------------------ ----------------
SYS_DEFAULT_CONNECTION_POOL    ACTIVE
TEST_POOL_1                    INACTIVE

SQL>

exec sys.dbms_connection_pool.stop_pool('test_pool_1');
exec sys.dbms_connection_pool.remove_pool('test_pool_1');

conn / as sysdba
alter system set enable_per_pdb_drcp=false scope=spfile;
shutdown immediate;
startup;
3

Implicit Connection Pooling

Implicit connection pooling gives an additional level of control over database resident connection pools by indicating time boundaries. Setting the to or indicates at which point a connection can be released back to the connection pool. - : The connection is released back to the connection pool when the session is implicitly stateless. - : The connection is released back to the connection pool when a transaction ends. To be considered implicitly stateless, a session must satify all these conditions. If any of these conditions are not met, the session is considered implicitly stateful. - All the open cursors have been fetched through to completion. - No active transactions. - No temporary LOBs. - No global temporary tables containing rows. - No open private temporary tables. Our choice of pool boundary will depend on how our application handles sessions. For applications where sessions are unlikely to be implicitly stateless, but we want to force the release back to the pool, then a pool boundary of transaction is the best option. If we know our sessions are mostly implicitly stateless, then the statement pool boundary is fine. We add the setting to our previous "tnsnames.ora" file entry to enable implicit connection pooling with a boundary of . We connect in the same way we did before. For more information see: Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
TEST_POOL_1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = FREEPDB1)
      (SERVER = POOLED)
      (POOL_NAME = TEST_POOL_1)
      (POOL_BOUNDARY=TRANSACTION)
    )
  )

SQL> conn testuser1/testuser1@test_pool_1
Connected.
SQL>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!