|
🖥️ Environment Details :
Test Environment: All observations and performance metrics presented in this document were collected and validated on the above environment configuration. 📌 Disclaimer: The observations and performance metrics presented in this document are based on testing conducted in the current environment configuration and workload conditions. Results were obtained from controlled lab validations and are intended for reference purposes only. Actual performance may vary depending on factors such as application workload characteristics, session concurrency, infrastructure sizing, database configuration, network conditions, and overall runtime environment. |
Let's first understand the requirement or need for DRCP (Database Resident Connection Pooling). In Dedicated Server Mode, the database creates one dedicated server process for every client connection. In environments where applications maintain a large number of open connections, this can lead to excessive resource consumption on the database server. For example, suppose there are 16 application servers, each maintaining 500 database connections. This results in a total of 8000 connections. In a dedicated server configuration, the database creates 8000 dedicated server processes, one for each client connection(user process). However, not all application connections are actively used at the same time. If only around 200 application processes are active, the remaining 7800 server processes remain idle while still consuming database resources such as memory and process overhead. This is where DRCP becomes useful. Instead of allocating a separate dedicated server process for each client connection, DRCP maintains a shared pool of server processes on the database server. For example, instead of creating 8000 dedicated server processes for 8000 client connections, the database may require only around 200 pooled server processes. This significantly reduces the number of idle server processes, optimizes database memory utilization, and improves overall scalability and resource efficiency of the database server. |
What is DRCP (Database Resident Connection Pooling) ? Database Resident Connection Pooling (DRCP) is a centralized pool of reusable server processes on the database server that enables multiple applications to efficiently share a limited number of pooled connections, resulting in lower memory consumption, better resource utilization, and improved scalability for environments with a large number of concurrent connections. When is DRCP used? 1) When your application has a large number of database connections, but only a few connections are active at a time. 2) When the application needs to support high connections with minimal memory usage on the database server. 3) When applications use short-lived connections. 4) When the application frequently opens and closes database connections. 5) When better scalability is required without increasing the number of database server processes. 6) When connection pooling at the application tier is not sufficient or available. |
Let us first understand the key components used in DRCP. ⚙️Connection Broker (CB): A Connection Broker (CB) is an Oracle background process that manages and coordinates client connections with pooled server processes in DRCP. It acts as a mediater between client applications and the database server pool, efficiently assigning available pooled server processes to incoming client connection requests. 🔐Authentication Servers: Connection Broker (CB) initially authenticates the connection requests received from the client by using a reserved set of DRCP processes referred to as Authentication Servers. Please note that approx. 5% of the current pooled servers are reserved for authentication. How does DRCP work ? - A client application requests a database connection. - The Oracle Listener forwards the connection request to the Connection Broker. - The Connection Broker authenticates the client using Authentication Servers within the DRCP pool. - After successful authentication, the broker maintains a persistent connection with the client application. - When the client starts a database operation, the Connection Broker assigns an available pooled server process from the free pool. - The assigned pooled server process handles all database interactions for that client session. - Once the transaction is completed or the session is closed, the pooled server process is released back to the free pool for reuse. - The client connection remains associated with the Connection Broker until the application disconnects, enabling efficient reuse of pooled server resources. |
Let's simulate the test case "wihout DRCP" and "with DRCP".
Step by step implementation of DRCP :
Step 1: Check the database configuration parameters from existing DB. NAME VALUE --------------------- ------- sga_target 200G sga_max_size 200G pga_aggregate_limit 0 pga_aggregate_target 50G processes 15000 sessions 22656 SQL> alter system set local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=10.20.30.100)(PORT=1530))'; System altered. SQL> alter system register; System altered. [oracle@oradbserver1 admin]$ cat listener.ora LISTENER_PR = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.20.30.100)(PORT = 1530)) ) ) SID_LIST_LISTENER_PR = (SID_LIST = (SID_DESC = (ORACLE_HOME = /u01/dbhome_19c) (SID_NAME = PR) ) ) [oracle@oradbserver1 admin]$ lsnrctl status LISTENER_PR LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 20-MAY-2026 01:36:45 Copyright (c) 1991, 2024, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.20.30.100)(PORT=1530))) STATUS of the LISTENER ------------------------ Alias LISTENER_PR Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production Start Date 20-MAY-2026 01:36:40 Uptime 0 days 0 hr. 0 min. 5 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/dbhome_19c/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/oradbserver1/listener_pr/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.20.30.100)(PORT=1530))) Services Summary... Service "PR" has 1 instance(s). Instance "PR", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully [oracle@oradbserver1 ~]$ lsnrctl services LISTENER_PR LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 20-MAY-2026 01:38:45 Copyright (c) 1991, 2024, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.20.30.100)(PORT=1530))) Services Summary... Service "PR" has 2 instance(s). Instance "PR", status UNKNOWN, has 1 handler(s) for this service... Handler(s): "DEDICATED" established:0 refused:0 LOCAL SERVER Instance "PR", status READY, has 1 handler(s) for this service... Handler(s): "DEDICATED" established:0 refused:0 state:ready LOCAL SERVER The command completed successfully #Check the sqlplus connectivity. [oracle@oradbserver1 ~]$ sqlplus NO_DRCP/test123@//10.20.30.100:1530/PR SQL*Plus: Release 19.0.0.0.0 - Production on Wed May 20 01:39:49 2026 Version 19.26.0.0.0 Copyright (c) 1982, 2024, Oracle. All rights reserved. Last Successful login time: Wed May 20 2026 01:21:18 +05:30 Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.26.0.0.0 SQL> def DEFINE _DATE = "20260520" (CHAR) DEFINE _CONNECT_IDENTIFIER = "//10.20.30.100:1530/PR" (CHAR) DEFINE _USER = "NO_DRCP" (CHAR) DEFINE _PRIVILEGE = "" (CHAR) DEFINE _SQLPLUS_RELEASE = "1926000000" (CHAR) DEFINE _EDITOR = "vi" (CHAR) DEFINE _O_VERSION = "Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.26.0.0.0" (CHAR) DEFINE _O_RELEASE = "1926000000" (CHAR) SQL> exit Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.26.0.0.0 |
Step 2: Create two users "NO_DRCP" and "DRCP" to simulate the test cases. SQL> create user NO_DRCP identified by test123; User created. SQL> create user DRCP identified by test123; User created. SQL> grant create session,connect to NO_DRCP,DRCP; Grant succeeded. SQL> grant execute on DBMS_LOCK to NO_DRCP,DRCP; Grant succeeded. Step 3: Create a customized profile and assign that profile to created users. SQL> create profile DRCP_PROFILE limit 2 COMPOSITE_LIMIT UNLIMITED 3 SESSIONS_PER_USER UNLIMITED 4 CPU_PER_SESSION UNLIMITED 5 CPU_PER_CALL UNLIMITED 6 LOGICAL_READS_PER_SESSION UNLIMITED 7 LOGICAL_READS_PER_CALL UNLIMITED 8 IDLE_TIME 30 9 CONNECT_TIME UNLIMITED 10 PRIVATE_SGA UNLIMITED 11 FAILED_LOGIN_ATTEMPTS UNLIMITED 12 PASSWORD_LIFE_TIME UNLIMITED 13 PASSWORD_REUSE_TIME UNLIMITED 14 PASSWORD_REUSE_MAX UNLIMITED 15 PASSWORD_VERIFY_FUNCTION NULL 16 PASSWORD_LOCK_TIME UNLIMITED 17 PASSWORD_GRACE_TIME 432000/86400 18 INACTIVE_ACCOUNT_TIME 10368000/86400 19 PASSWORD_ROLLOVER_TIME -1/86400 20 ; Profile created. SQL> alter user NO_DRCP profile DRCP_PROFILE; User altered. SQL> alter user DRCP profile DRCP_PROFILE; User altered. SQL> col USERNAME for a16 SQL> col PROFILE for a16 SQL> select username,profile from dba_users where username in('NO_DRCP','DRCP'); USERNAME PROFILE ---------------- ---------------- NO_DRCP DRCP_PROFILE DRCP DRCP_PROFILE |
Step 4: Prepare a shell script to invoke SQL*Plus connections in a loop for both the NO_DRCP and DRCP users. Script for NO_DRCP : [oracle@oradbserver1 ~]$ vi no_drcp.sh #!/bin/bash for i in {1..10000} do ( sqlplus -L -s /nolog <<EOF connect NO_DRCP/test123@//10.20.30.100:1530/PR set heading off set feedback off begin dbms_lock.sleep(120); end; / exit; EOF ) & done Script for DRCP : [oracle@oradbserver1 ~]$ vi drcp.sh #!/bin/bash for i in {1..10000} do ( sqlplus -L -s /nolog <<EOF connect NO_DRCP/test123@//10.20.30.100:1530/PR:POOLED set heading off set feedback off begin dbms_lock.sleep(120); end; / exit; EOF ) & done |
Step 5: Prepare a shell script for monitoring both without DRCP and with DRCP activities. #Below scripts will monitor the netstat PORT utilization and also the services in LISTENER_PR. The netstat script will lists the total count of TIME_WAIT and ESTABLISHED connections on port 1530. [oracle@oradbserver1 oracle]$ vi netstat.sh while true do datef=`date +%d%b%Y` sh netstat_1530.sh >>/u01/app/oracle/netstat_1530_${datef}.log sleep 1 done [oracle@oradbserver1 oracle]$ vi netstat_1530.sh date echo "" echo "Total tcp connections on port 1530 are : `netstat -an | grep 1530 |wc -l`" echo "" echo "Total TIME_WAIT connections on port 1530 are : `netstat -an | grep 1530 | grep -i TIME_WAIT | wc -l`" echo "" echo "Total ESTABLISHED connections on port 1530 are : `netstat -an | grep 1530 | grep -i ESTABLISHED |wc -l`" echo "" echo "" echo "" lsnrctl services LISTENER_PR #The below script will display the total count of DEDICATED and POOLED server processes. [oracle@oradbserver1 oracle]$ cat drcp_count.sh export ORACLE_SID=PR export ORACLE_HOME=/u01/dbhome_19c export PATH=$ORACLE_HOME/bin:$PATH while true do datef=`date +%d%b%Y` sqlplus -s / as sysdba@PR @/u01/app/oracle/count.sql <<EOF >>/u01/app/oracle/drcp_count_${datef}.log exit EOF sleep 1 done [oracle@oradbserver1 oracle]$ cat /u01/app/oracle/count.sql set lines 300 pages 3000 col username for a15 col status for a15 select to_char(sysdate,'DD-MON-YY HH24:MI:SS') sdt,INST_ID,username,status,count(*) from gv$session where username='NO_DRCP' group by INST_ID,username,status order by 1; select to_char(sysdate,'DD-MON-YY HH24:MI:SS') sdt,count(*) Total_Processes from v$process; select to_char(sysdate,'DD-MON-YY HH24:MI:SS') sdt,server,count(*) from v$session group by server; select to_char(sysdate,'DD-MON-YY HH24:MI:SS') sdt,round(sum(pga_used_mem)/1024/1024) pga_mb from v$process; |
Step 6: Manually create an AWR begin snapshot to compare the database performance analysis before and after enabling DRCP and also, run the schell script "no_drcp.sh". 01:41:06 SQL> EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot; PL/SQL procedure successfully completed. #Invoke the shell script "no_drcp.sh". [oracle@oradbserver1 ~]$ sh no_drcp.sh Now again create an AWR end snapshot after shell script execution. 01:45:05 SQL> EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot; PL/SQL procedure successfully completed. Now Let's check the monitoring scripts and observe the memory and process utilization on DB server. Without DRCP: [oracle@oradbserver1 oracle]$ vi netstat_1530_20May2026.log Wed May 20 01:40:40 IST 2026 Total tcp connections on port 1530 are : 4 Total TIME_WAIT connections on port 1530 are : 1 Total ESTABLISHED connections on port 1530 are : 2 Wed May 20 01:40:41 IST 2026 Total tcp connections on port 1530 are : 5 Total TIME_WAIT connections on port 1530 are : 2 Total ESTABLISHED connections on port 1530 are : 2 Wed May 20 01:40:42 IST 2026 Total tcp connections on port 1530 are : 6 Total TIME_WAIT connections on port 1530 are : 3 Total ESTABLISHED connections on port 1530 are : 2 Wed May 20 01:40:43 IST 2026 Total tcp connections on port 1530 are : 8 Total TIME_WAIT connections on port 1530 are : 5 Total ESTABLISHED connections on port 1530 are : 2 ............. ............. Wed May 20 01:41:38 IST 2026 Total tcp connections on port 1530 are : 59 Total TIME_WAIT connections on port 1530 are : 56 Total ESTABLISHED connections on port 1530 are : 2 Wed May 20 01:41:39 IST 2026 Total tcp connections on port 1530 are : 6443 Total TIME_WAIT connections on port 1530 are : 46 Total ESTABLISHED connections on port 1530 are : 1463 Wed May 20 01:41:40 IST 2026 Total tcp connections on port 1530 are : 11691 Total TIME_WAIT connections on port 1530 are : 41 Total ESTABLISHED connections on port 1530 are : 4215 Wed May 20 01:41:41 IST 2026 Total tcp connections on port 1530 are : 12641 Total TIME_WAIT connections on port 1530 are : 42 Total ESTABLISHED connections on port 1530 are : 6759 Wed May 20 01:41:45 IST 2026 Total tcp connections on port 1530 are : 14707 Total TIME_WAIT connections on port 1530 are : 39 Total ESTABLISHED connections on port 1530 are : 10549 Wed May 20 01:41:47 IST 2026 Total tcp connections on port 1530 are : 15775 Total TIME_WAIT connections on port 1530 are : 40 Total ESTABLISHED connections on port 1530 are : 13074 ............. ............. Wed May 20 01:43:27 IST 2026 Total tcp connections on port 1530 are : 19407 Total TIME_WAIT connections on port 1530 are : 70 Total ESTABLISHED connections on port 1530 are : 19332 Wed May 20 01:43:28 IST 2026 Total tcp connections on port 1530 are : 19408 Total TIME_WAIT connections on port 1530 are : 71 Total ESTABLISHED connections on port 1530 are : 19332 Wed May 20 01:43:30 IST 2026 Total tcp connections on port 1530 are : 19409 Total TIME_WAIT connections on port 1530 are : 72 Total ESTABLISHED connections on port 1530 are : 19332 Wed May 20 01:43:31 IST 2026 Total tcp connections on port 1530 are : 19408 Total TIME_WAIT connections on port 1530 are : 71 Total ESTABLISHED connections on port 1530 are : 19332 Wed May 20 01:43:32 IST 2026 Total tcp connections on port 1530 are : 19409 Total TIME_WAIT connections on port 1530 are : 72 Total ESTABLISHED connections on port 1530 are : 19332 Wed May 20 01:43:34 IST 2026 Total tcp connections on port 1530 are : 19410 Total TIME_WAIT connections on port 1530 are : 73 Total ESTABLISHED connections on port 1530 are : 19332 Wed May 20 01:43:35 IST 2026 Total tcp connections on port 1530 are : 19406 Total TIME_WAIT connections on port 1530 are : 69 Total ESTABLISHED connections on port 1530 are : 19332 Wed May 20 01:43:36 IST 2026 Total tcp connections on port 1530 are : 19407 Total TIME_WAIT connections on port 1530 are : 70 Total ESTABLISHED connections on port 1530 are : 19332 From the above NETSTAT output, you can see that a total of 19,332 connections have been established on port 1530. Let’s check the established connections on the LISTENER. Filter out the data from the file "netstat_1530_20May2026.log". Wed May 20 01:40:40 IST 2026 LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 20-MAY-2026 01:40:40 Copyright (c) 1991, 2024, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.20.30.100)(PORT=1530))) Services Summary... Service "PR" has 2 instance(s). Instance "PR", status UNKNOWN, has 1 handler(s) for this service... Handler(s): "DEDICATED" established:0 refused:0 LOCAL SERVER Instance "PR", status READY, has 1 handler(s) for this service... Handler(s): "DEDICATED" established:1 refused:0 state:ready LOCAL SERVER The command completed successfully Wed May 20 01:41:35 IST 2026 "DEDICATED" established:1 refused:0 state:ready Wed May 20 01:41:36 IST 2026 "DEDICATED" established:1 refused:0 state:ready Wed May 20 01:41:37 IST 2026 "DEDICATED" established:1 refused:0 state:ready Wed May 20 01:41:38 IST 2026 "DEDICATED" established:1 refused:0 state:ready Wed May 20 01:41:39 IST 2026 "DEDICATED" established:725 refused:0 state:ready Wed May 20 01:41:40 IST 2026 "DEDICATED" established:1612 refused:0 state:ready Wed May 20 01:41:41 IST 2026 "DEDICATED" established:4159 refused:0 state:ready Wed May 20 01:41:45 IST 2026 "DEDICATED" established:4793 refused:0 state:ready Wed May 20 01:41:47 IST 2026 "DEDICATED" established:5770 refused:0 state:ready Wed May 20 01:41:48 IST 2026 "DEDICATED" established:6270 refused:0 state:ready Wed May 20 01:41:49 IST 2026 "DEDICATED" established:6715 refused:0 state:ready Wed May 20 01:41:50 IST 2026 "DEDICATED" established:6715 refused:0 state:ready Wed May 20 01:41:52 IST 2026 "DEDICATED" established:6768 refused:0 state:ready Wed May 20 01:41:53 IST 2026 "DEDICATED" established:7006 refused:0 state:ready Wed May 20 01:41:54 IST 2026 "DEDICATED" established:7684 refused:0 state:ready Wed May 20 01:41:56 IST 2026 "DEDICATED" established:8201 refused:0 state:ready Wed May 20 01:41:57 IST 2026 "DEDICATED" established:8201 refused:0 state:ready Wed May 20 01:41:58 IST 2026 "DEDICATED" established:8201 refused:0 state:ready Wed May 20 01:42:00 IST 2026 "DEDICATED" established:8201 refused:0 state:ready Wed May 20 01:42:01 IST 2026 "DEDICATED" established:8201 refused:0 state:ready Wed May 20 01:42:02 IST 2026 "DEDICATED" established:8201 refused:0 state:ready Wed May 20 01:42:03 IST 2026 "DEDICATED" established:8201 refused:0 state:ready Wed May 20 01:42:05 IST 2026 "DEDICATED" established:8201 refused:0 state:ready Wed May 20 01:42:06 IST 2026 "DEDICATED" established:8201 refused:0 state:ready Wed May 20 01:42:07 IST 2026 "DEDICATED" established:8377 refused:0 state:ready Wed May 20 01:42:09 IST 2026 "DEDICATED" established:8377 refused:0 state:ready Wed May 20 01:42:10 IST 2026 "DEDICATED" established:8646 refused:0 state:ready Wed May 20 01:42:11 IST 2026 "DEDICATED" established:9468 refused:0 state:ready Wed May 20 01:42:13 IST 2026 "DEDICATED" established:9468 refused:0 state:ready Wed May 20 01:42:14 IST 2026 "DEDICATED" established:9600 refused:0 state:ready Wed May 20 01:42:15 IST 2026 "DEDICATED" established:9600 refused:0 state:ready Wed May 20 01:42:17 IST 2026 "DEDICATED" established:9665 refused:0 state:ready Wed May 20 01:42:18 IST 2026 "DEDICATED" established:9665 refused:0 state:ready Wed May 20 01:42:19 IST 2026 "DEDICATED" established:9665 refused:0 state:ready Wed May 20 01:42:21 IST 2026 "DEDICATED" established:9665 refused:0 state:ready Wed May 20 01:42:22 IST 2026 "DEDICATED" established:9665 refused:0 state:ready Wed May 20 01:42:23 IST 2026 "DEDICATED" established:9665 refused:0 state:ready Wed May 20 01:42:25 IST 2026 "DEDICATED" established:9665 refused:0 state:ready Wed May 20 01:42:26 IST 2026 "DEDICATED" established:9665 refused:0 state:ready Wed May 20 01:42:28 IST 2026 "DEDICATED" established:9665 refused:0 state:ready Wed May 20 01:42:29 IST 2026 "DEDICATED" established:9665 refused:0 state:ready Wed May 20 01:42:30 IST 2026 "DEDICATED" established:9665 refused:0 state:ready Wed May 20 01:42:32 IST 2026 "DEDICATED" established:9665 refused:0 state:ready Wed May 20 01:42:33 IST 2026 "DEDICATED" established:9666 refused:0 state:ready Wed May 20 01:42:34 IST 2026 "DEDICATED" established:9666 refused:0 state:ready Wed May 20 01:42:36 IST 2026 "DEDICATED" established:9666 refused:0 state:ready Wed May 20 01:42:37 IST 2026 "DEDICATED" established:9666 refused:0 state:ready ............ ............ Wed May 20 01:43:35 IST 2026 "DEDICATED" established:9666 refused:0 state:ready Wed May 20 01:43:36 IST 2026 "DEDICATED" established:9666 refused:0 state:ready Wed May 20 01:43:38 IST 2026 "DEDICATED" established:9666 refused:0 state:ready Wed May 20 01:43:39 IST 2026 "DEDICATED" established:9666 refused:0 state:ready Wed May 20 01:43:40 IST 2026 "DEDICATED" established:9666 refused:0 state:ready From the above "lsnrctl services LISTENER_PR" output, it has been observed that a total of 9,666 connections have been established. At PORT 1530, approximately 19,332 connections (9,666 × 2) considering both the client and server processes have been established. Let's check out the file "drcp_count_20May2026.log" that will display data from DB. no rows selected SDT TOTAL_PROCESSES ------------------ --------------- 20-MAY-26 01:41:08 949 SDT SERVER COUNT(*) ------------------ --------- ---------- 20-MAY-26 01:41:08 DEDICATED 180 SDT PGA_MB ------------------ ---------- 20-MAY-26 01:41:08 1683 no rows selected SDT TOTAL_PROCESSES ------------------ --------------- 20-MAY-26 01:41:09 950 SDT SERVER COUNT(*) ------------------ --------- ---------- 20-MAY-26 01:41:09 DEDICATED 181 SDT PGA_MB ------------------ ---------- 20-MAY-26 01:41:09 1685 no rows selected SDT TOTAL_PROCESSES ------------------ --------------- 20-MAY-26 01:41:11 950 SDT SERVER COUNT(*) ------------------ --------- ---------- 20-MAY-26 01:41:11 DEDICATED 181 SDT PGA_MB ------------------ ---------- 20-MAY-26 01:41:11 1688 no rows selected SDT TOTAL_PROCESSES ------------------ --------------- 20-MAY-26 01:41:12 950 SDT SERVER COUNT(*) ------------------ --------- ---------- 20-MAY-26 01:41:12 DEDICATED 181 SDT PGA_MB ------------------ ---------- 20-MAY-26 01:41:12 1686 no rows selected SDT TOTAL_PROCESSES ------------------ --------------- 20-MAY-26 01:41:13 951 SDT SERVER COUNT(*) ------------------ --------- ---------- 20-MAY-26 01:41:13 DEDICATED 182 SDT PGA_MB ------------------ ---------- 20-MAY-26 01:41:13 1688 no rows selected SDT TOTAL_PROCESSES ------------------ --------------- 20-MAY-26 01:41:38 955 SDT SERVER COUNT(*) ------------------ --------- ---------- 20-MAY-26 01:41:38 DEDICATED 186 SDT PGA_MB ------------------ ---------- 20-MAY-26 01:41:38 1693 SDT INST_ID USERNAME STATUS COUNT(*) ------------------ ---------- ---------- ------- -------- 20-MAY-26 01:41:39 1 NO_DRCP ACTIVE 451 SDT TOTAL_PROCESSES ------------------ --------------- 20-MAY-26 01:41:39 1414 SDT SERVER COUNT(*) ------------------ --------- ---------- 20-MAY-26 01:41:39 DEDICATED 645 SDT PGA_MB ------------------ ---------- 20-MAY-26 01:41:39 3031 SDT INST_ID USERNAME STATUS COUNT(*) ------------------ ---------- ---------- ------- -------- 20-MAY-26 01:41:40 1 NO_DRCP ACTIVE 1092 20-MAY-26 01:41:40 1 NO_DRCP INACTIVE 1 SDT TOTAL_PROCESSES ------------------ --------------- 20-MAY-26 01:41:40 2061 SDT SERVER COUNT(*) ------------------ --------- ---------- 20-MAY-26 01:41:40 DEDICATED 1289 SDT PGA_MB ------------------ ---------- 20-MAY-26 01:41:40 4916 SDT INST_ID USERNAME STATUS COUNT(*) ------------------ ---------- ---------- ------- -------- 20-MAY-26 01:41:41 1 NO_DRCP ACTIVE 1838 SDT TOTAL_PROCESSES ------------------ --------------- 20-MAY-26 01:41:41 2798 SDT SERVER COUNT(*) ------------------ --------- ---------- 20-MAY-26 01:41:41 DEDICATED 2029 SDT PGA_MB ------------------ ---------- 20-MAY-26 01:41:41 7077 SDT INST_ID USERNAME STATUS COUNT(*) ------------------ ---------- ---------- ------- -------- 20-MAY-26 01:41:42 1 NO_DRCP ACTIVE 2561 20-MAY-26 01:41:42 1 NO_DRCP INACTIVE 1 SDT TOTAL_PROCESSES ------------------ --------------- 20-MAY-26 01:41:42 3528 SDT SERVER COUNT(*) ------------------ --------- ---------- 20-MAY-26 01:41:42 DEDICATED 2757 SDT PGA_MB ------------------ ---------- 20-MAY-26 01:41:42 9209 SDT INST_ID USERNAME STATUS COUNT(*) ------------------ ---------- ---------- ------- -------- 20-MAY-26 01:41:43 1 NO_DRCP ACTIVE 3242 SDT TOTAL_PROCESSES ------------------ --------------- 20-MAY-26 01:41:43 4314 SDT SERVER COUNT(*) ------------------ --------- ---------- 20-MAY-26 01:41:43 DEDICATED 3544 SDT PGA_MB ------------------ ---------- 20-MAY-26 01:41:43 11470 SDT INST_ID USERNAME STATUS COUNT(*) ------------------ ---------- ---------- ------- -------- 20-MAY-26 01:41:44 1 NO_DRCP ACTIVE 4093 SDT TOTAL_PROCESSES ------------------ --------------- 20-MAY-26 01:41:44 5062 SDT SERVER COUNT(*) ------------------ --------- ---------- 20-MAY-26 01:41:44 DEDICATED 4291 SDT PGA_MB ------------------ ---------- 20-MAY-26 01:41:44 13698 SDT INST_ID USERNAME STATUS COUNT(*) ------------------ ---------- ---------- ------- -------- 20-MAY-26 01:41:45 1 NO_DRCP ACTIVE 4512 SDT TOTAL_PROCESSES ------------------ --------------- 20-MAY-26 01:41:45 5468 SDT SERVER COUNT(*) ------------------ --------- ---------- 20-MAY-26 01:41:45 DEDICATED 4699 SDT PGA_MB ------------------ ---------- 20-MAY-26 01:41:45 14873 SDT INST_ID USERNAME STATUS COUNT(*) ------------------ ---------- ---------- ------- -------- 20-MAY-26 01:41:46 1 NO_DRCP ACTIVE 5177 20-MAY-26 01:41:46 1 NO_DRCP INACTIVE 1 SDT TOTAL_PROCESSES ------------------ --------------- 20-MAY-26 01:41:46 6147 SDT SERVER COUNT(*) ------------------ --------- ---------- 20-MAY-26 01:41:46 DEDICATED 5377 SDT PGA_MB ------------------ ---------- 20-MAY-26 01:41:46 16868 ................ ................ SDT INST_ID USERNAME STATUS COUNT(*) ------------------ ---------- ---------- ------- -------- 20-MAY-26 01:42:33 1 NO_DRCP ACTIVE 9664 SDT TOTAL_PROCESSES ------------------ --------------- 20-MAY-26 01:42:33 10619 SDT SERVER COUNT(*) ------------------ --------- ---------- 20-MAY-26 01:42:33 DEDICATED 9850 SDT PGA_MB ------------------ ---------- 20-MAY-26 01:42:33 29833 SDT INST_ID USERNAME STATUS COUNT(*) ------------------ ---------- ---------- ------- -------- 20-MAY-26 01:42:34 1 NO_DRCP ACTIVE 9665 SDT TOTAL_PROCESSES ------------------ --------------- 20-MAY-26 01:42:34 10620 SDT SERVER COUNT(*) ------------------ --------- ---------- 20-MAY-26 01:42:34 DEDICATED 9851 SDT PGA_MB ------------------ ---------- 20-MAY-26 01:42:34 29829 SDT INST_ID USERNAME STATUS COUNT(*) ------------------ ---------- ---------- ------- -------- 20-MAY-26 01:42:35 1 NO_DRCP ACTIVE 9665 SDT TOTAL_PROCESSES ------------------ --------------- 20-MAY-26 01:42:35 10620 SDT SERVER COUNT(*) ------------------ --------- ---------- 20-MAY-26 01:42:35 DEDICATED 9851 SDT PGA_MB ------------------ ---------- 20-MAY-26 01:42:35 29823 The database activity snapshot captured at 20-MAY-26 01:42:35 highlights the following observations: - Total active NO_DRCP sessions: 9,665 - Total database processes: 10,620 - Total dedicated server processes: 9,851 - Total PGA memory consumption: 29,823 MB From the above observations, it is evident that approximately 9,000 dedicated server processes have been created to handle around 9,000 user processes. As a result, the overall memory consumption on the database server has increased to approximately 29 GB. |
Let's observe the pattern after configuring DRCP in the database.
Step 7: Now it is time to configure DRCP in the database. #Use the following command to configure DRCP with minimum configuration parameters. SQL> BEGIN dbms_connection_pool.configure_pool( minsize => 50, maxsize => 200, incrsize => 20, session_cached_cursors => 100 ); END; PL/SQL procedure successfully completed. #Use the below command to start the pool. SQL> execute dbms_connection_pool.start_pool() PL/SQL procedure successfully completed. Let's first check sqlplus connectivity using DRCP. [oracle@oradbserver1 ~]$ sqlplus DRCP/test123@//10.20.30.100:1530/PR:POOLED SQL*Plus: Release 19.0.0.0.0 - Production on Wed May 20 02:26:05 2026 Version 19.26.0.0.0 Copyright (c) 1982, 2024, Oracle. All rights reserved. Last Successful login time: Wed May 20 2026 02:25:25 +05:30 Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.26.0.0.0 SQL> def DEFINE _DATE = "20260520" (CHAR) DEFINE _CONNECT_IDENTIFIER = "//10.20.30.100:1530/PR:POOLED" (CHAR) DEFINE _USER = "DRCP" (CHAR) DEFINE _PRIVILEGE = "" (CHAR) DEFINE _SQLPLUS_RELEASE = "1926000000" (CHAR) DEFINE _EDITOR = "vi" (CHAR) DEFINE _O_VERSION = "Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.26.0.0.0" (CHAR) DEFINE _O_RELEASE = "1926000000" (CHAR) SQL> SQL> exit Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.26.0.0.0 Important parameters of DRCP: 1) MINSIZE : Minimum number of pooled servers in the pool. (Default : 4) 2) MAXSIZE : Maximum number of pooled servers allowed in the pool. (Default : 40).If this MAXSIZE limit is reached, then connection requests wait until a server becomes free. 3) INCRSIZE : Specifies the number of pooled servers to add when no servers are available for connections and the pool has not yet reached its maximum size. (Default : 2) 4) SESSION_CACHED_CURSORS : Specifies the number of frequently used SQL statements that are cached on the server for each pooled session. This parameter in DRCP overrides the DB instance SESSION_CACHED_CURSORS parameter. (Default : 20) 5) INACTIVITY_TIMEOUT : Specifies the duration, in seconds, that a free server remains in the pool. After this time, the free server process is terminated. (Default : 300 secs) 6) MAX_THINK_TIME : This specifies the maximum idle time in seconds.(Default : 120 secs) 7) MAX_USE_SESSION : Specifies the maximum number of times a server can be allocated and released from the pool before it is restarted.(Default : 500000) 8) MAX_LIFETIME_SESSION : Specifies the lifetime of a pooled server in seconds before it is restarted. (Default : 86400 seconds) Important Query to check and monitor DRCP stats: SQL> col CONNECTION_POOL for a30 SQL> col CCLASS_NAME for a30 SQL> col MACHINE for a35 #Query to get configuration information about the connection pool SQL> SELECT connection_pool, status, maxsize FROM dba_cpool_info; # Query to get DRCP statistics. SQL> SELECT num_requests, num_hits, num_misses, num_waits FROM v$cpool_stats; #Query to get the connection class level statistics for the pool. SQL> SELECT cclass_name, num_requests, num_hits, num_misses FROM v$cpool_cc_stats; #Query to get the connection information about each connection to the connection broker. SQL> SELECT cclass_name, machine FROM v$cpool_conn_info; #Execute above queries and get the required output. SQL> col CONNECTION_POOL for a30 SQL> SELECT connection_pool, status, maxsize FROM dba_cpool_info; CONNECTION_POOL STATUS MAXSIZE ------------------------------ ---------------- ---------- SYS_DEFAULT_CONNECTION_POOL ACTIVE 200 SQL> SELECT num_requests, num_hits, num_misses, num_waits FROM v$cpool_stats; NUM_REQUESTS NUM_HITS NUM_MISSES NUM_WAITS ------------ ---------- ---------- ---------- 0 0 0 0 SQL> SELECT cclass_name, num_requests, num_hits, num_misses FROM v$cpool_cc_stats; no rows selected SQL> SELECT cclass_name, machine FROM v$cpool_conn_info; no rows selected |
Step 7: Now create AWR begin snap for DRCP connection pooling and run the "drcp.sh" script. 01:57:50 SQL> EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot; PL/SQL procedure successfully completed. #Run the "drcp.sh" script to initiate 10000 connections to the database. [oracle@oradbserver1 ~]$ sh drcp.sh [oracle@oradbserver1 ~]$ #Let's monitor the DRCP stats. You can see the DRCP statistics here. The 'CCLASS_NAME' is shown as 'DRCP.SHARED' along with the machine name. SQL> @drcpstat SQL> CCLASS_NAME NUM_REQUESTS NUM_HITS NUM_MISSES ---------------- ------------ ---------- ---------- DRCP.SHARED 190 0 190 CCLASS_NAME MACHINE ---------------- ----------------------------------- DRCP.SHARED oradbserver1.mock.uat DRCP.SHARED oradbserver1.mock.uat DRCP.SHARED oradbserver1.mock.uat DRCP.SHARED oradbserver1.mock.uat DRCP.SHARED oradbserver1.mock.uat DRCP.SHARED oradbserver1.mock.uat DRCP.SHARED oradbserver1.mock.uat DRCP.SHARED oradbserver1.mock.uat DRCP.SHARED oradbserver1.mock.uat DRCP.SHARED oradbserver1.mock.uat DRCP.SHARED oradbserver1.mock.uat DRCP.SHARED oradbserver1.mock.uat DRCP.SHARED oradbserver1.mock.uat DRCP.SHARED oradbserver1.mock.uat DRCP.SHARED oradbserver1.mock.uat DRCP.SHARED oradbserver1.mock.uat DRCP.SHARED oradbserver1.mock.uat DRCP.SHARED oradbserver1.mock.uat DRCP.SHARED oradbserver1.mock.uat DRCP.SHARED oradbserver1.mock.uat DRCP.SHARED oradbserver1.mock.uat DRCP.SHARED oradbserver1.mock.uat DRCP.SHARED oradbserver1.mock.uat DRCP.SHARED oradbserver1.mock.uat DRCP.SHARED oradbserver1.mock.uat ........... ........... DRCP.SHARED oradbserver1.mock.uat DRCP.SHARED oradbserver1.mock.uat DRCP.SHARED oradbserver1.mock.uat DRCP.SHARED oradbserver1.mock.uat DRCP.SHARED oradbserver1.mock.uat DRCP.SHARED oradbserver1.mock.uat DRCP.SHARED oradbserver1.mock.uat DRCP.SHARED oradbserver1.mock.uat DRCP.SHARED oradbserver1.mock.uat DRCP.SHARED oradbserver1.mock.uat DRCP.SHARED oradbserver1.mock.uat DRCP.SHARED oradbserver1.mock.uat DRCP.SHARED oradbserver1.mock.uat DRCP.SHARED oradbserver1.mock.uat DRCP.SHARED oradbserver1.mock.uat 10000 rows selected. #Let's generate the AWR end snapshot and capture the AWR report. 02:02:02 SQL> EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot; PL/SQL procedure successfully completed. |
Step 8: Now it's time to capture AWR reports of generated AWR snaps and compare the results. Without DRCP: Begin Snap:127 End Snap:128 SQL> @?/rdbms/admin/awrrpt.sql With DRCP: Begin Snap:129 End Snap:130 SQL> @?/rdbms/admin/awrrpt.sql SQL> @?/rdbms/admin/awrdrpt.sql |
Step 9: Now Let's check the monitoring scripts and observe the memory and process utilization on DB server. With DRCP: [oracle@oradbserver1 oracle]$ vi netstat_1530_20May2026.log Wed May 20 01:56:34 IST 2026 Total tcp connections on port 1530 are : 3 Total TIME_WAIT connections on port 1530 are : 0 Total ESTABLISHED connections on port 1530 are : 2 Wed May 20 01:56:35 IST 2026 Total tcp connections on port 1530 are : 4 Total TIME_WAIT connections on port 1530 are : 1 Total ESTABLISHED connections on port 1530 are : 2 Wed May 20 01:56:36 IST 2026 Total tcp connections on port 1530 are : 5 Total TIME_WAIT connections on port 1530 are : 2 Total ESTABLISHED connections on port 1530 are : 2 Wed May 20 01:56:37 IST 2026 Total tcp connections on port 1530 are : 6 Total TIME_WAIT connections on port 1530 are : 3 Total ESTABLISHED connections on port 1530 are : 2 ......... Total tcp connections on port 1530 are : 14 Total TIME_WAIT connections on port 1530 are : 11 Total ESTABLISHED connections on port 1530 are : 2 Wed May 20 01:56:45 IST 2026 Total tcp connections on port 1530 are : 15 Total TIME_WAIT connections on port 1530 are : 12 Total ESTABLISHED connections on port 1530 are : 2 Wed May 20 01:56:46 IST 2026 Total tcp connections on port 1530 are : 16 Total TIME_WAIT connections on port 1530 are : 13 Total ESTABLISHED connections on port 1530 are : 2 Wed May 20 01:56:47 IST 2026 Total tcp connections on port 1530 are : 17 Total TIME_WAIT connections on port 1530 are : 14 Total ESTABLISHED connections on port 1530 are : 2 Wed May 20 01:56:49 IST 2026 Total tcp connections on port 1530 are : 18 Total TIME_WAIT connections on port 1530 are : 15 Total ESTABLISHED connections on port 1530 are : 2 Wed May 20 01:56:50 IST 2026 Total tcp connections on port 1530 are : 19 Total TIME_WAIT connections on port 1530 are : 16 Total ESTABLISHED connections on port 1530 are : 2 Wed May 20 01:56:51 IST 2026 Total tcp connections on port 1530 are : 20 Total TIME_WAIT connections on port 1530 are : 17 Total ESTABLISHED connections on port 1530 are : 2 Wed May 20 01:56:52 IST 2026 Total tcp connections on port 1530 are : 21 Total TIME_WAIT connections on port 1530 are : 18 Total ESTABLISHED connections on port 1530 are : 2 Wed May 20 01:56:53 IST 2026 Total tcp connections on port 1530 are : 22 Total TIME_WAIT connections on port 1530 are : 19 Total ESTABLISHED connections on port 1530 are : 2 Wed May 20 01:56:54 IST 2026 Total tcp connections on port 1530 are : 23 Total TIME_WAIT connections on port 1530 are : 20 Total ESTABLISHED connections on port 1530 are : 2 ......... Wed May 20 01:58:26 IST 2026 Total tcp connections on port 1530 are : 62 Total TIME_WAIT connections on port 1530 are : 59 Total ESTABLISHED connections on port 1530 are : 2 Wed May 20 01:58:27 IST 2026 Total tcp connections on port 1530 are : 63 Total TIME_WAIT connections on port 1530 are : 60 Total ESTABLISHED connections on port 1530 are : 184 Wed May 20 01:58:28 IST 2026 Total tcp connections on port 1530 are : 12561 Total TIME_WAIT connections on port 1530 are : 43 Total ESTABLISHED connections on port 1530 are : 15023 Wed May 20 01:58:29 IST 2026 Total tcp connections on port 1530 are : 20043 Total TIME_WAIT connections on port 1530 are : 40 Total ESTABLISHED connections on port 1530 are : 20002 Wed May 20 01:58:31 IST 2026 Total tcp connections on port 1530 are : 20045 Total TIME_WAIT connections on port 1530 are : 42 Total ESTABLISHED connections on port 1530 are : 20002 Wed May 20 01:58:32 IST 2026 Total tcp connections on port 1530 are : 20043 Total TIME_WAIT connections on port 1530 are : 40 Total ESTABLISHED connections on port 1530 are : 20002 Wed May 20 01:58:33 IST 2026 Total tcp connections on port 1530 are : 20044 Total TIME_WAIT connections on port 1530 are : 41 Total ESTABLISHED connections on port 1530 are : 20002 ....... Wed May 20 02:00:44 IST 2026 Total tcp connections on port 1530 are : 19872 Total TIME_WAIT connections on port 1530 are : 269 Total ESTABLISHED connections on port 1530 are : 19602 Wed May 20 02:00:45 IST 2026 Total tcp connections on port 1530 are : 19873 Total TIME_WAIT connections on port 1530 are : 270 Total ESTABLISHED connections on port 1530 are : 19602 Wed May 20 02:00:46 IST 2026 Total tcp connections on port 1530 are : 19874 Total TIME_WAIT connections on port 1530 are : 271 Total ESTABLISHED connections on port 1530 are : 19602 Wed May 20 02:00:47 IST 2026 Total tcp connections on port 1530 are : 19871 Total TIME_WAIT connections on port 1530 are : 268 Total ESTABLISHED connections on port 1530 are : 19602 Wed May 20 02:00:49 IST 2026 Total tcp connections on port 1530 are : 19873 Total TIME_WAIT connections on port 1530 are : 270 Total ESTABLISHED connections on port 1530 are : 19602 Wed May 20 02:00:50 IST 2026 Total tcp connections on port 1530 are : 19874 Total TIME_WAIT connections on port 1530 are : 271 Total ESTABLISHED connections on port 1530 are : 19602 Wed May 20 01:57:02 IST 2026 "N000" established:0 refused:0 current:0 max:40000 state:ready CMON <machine: oradbserver1.mock.uat, pid: 261100> Wed May 20 01:57:03 IST 2026 "N000" established:0 refused:0 current:0 max:40000 state:ready CMON <machine: oradbserver1.mock.uat, pid: 261100> Wed May 20 01:57:04 IST 2026 "N000" established:0 refused:0 current:0 max:40000 state:ready CMON <machine: oradbserver1.mock.uat, pid: 261100> Wed May 20 01:57:05 IST 2026 "N000" established:0 refused:0 current:0 max:40000 state:ready CMON <machine: oradbserver1.mock.uat, pid: 261100> Wed May 20 01:57:06 IST 2026 "N000" established:0 refused:0 current:0 max:40000 state:ready CMON <machine: oradbserver1.mock.uat, pid: 261100> Wed May 20 01:57:07 IST 2026 "N000" established:0 refused:0 current:0 max:40000 state:ready CMON <machine: oradbserver1.mock.uat, pid: 261100> Wed May 20 01:57:08 IST 2026 "N000" established:0 refused:0 current:0 max:40000 state:ready CMON <machine: oradbserver1.mock.uat, pid: 261100> Wed May 20 01:57:09 IST 2026 "N000" established:0 refused:0 current:0 max:40000 state:ready CMON <machine: oradbserver1.mock.uat, pid: 261100> Wed May 20 01:57:10 IST 2026 "N000" established:0 refused:0 current:0 max:40000 state:ready CMON <machine: oradbserver1.mock.uat, pid: 261100> Wed May 20 01:57:12 IST 2026 "N000" established:0 refused:0 current:0 max:40000 state:ready CMON <machine: oradbserver1.mock.uat, pid: 261100> Wed May 20 01:57:13 IST 2026 "N000" established:0 refused:0 current:0 max:40000 state:ready CMON <machine: oradbserver1.mock.uat, pid: 261100> ........... Wed May 20 01:58:25 IST 2026 "N000" established:0 refused:0 current:0 max:40000 state:ready CMON <machine: oradbserver1.mock.uat, pid: 261100> Wed May 20 01:58:26 IST 2026 "N000" established:0 refused:0 current:0 max:40000 state:ready CMON <machine: oradbserver1.mock.uat, pid: 261100> Wed May 20 01:58:27 IST 2026 "N000" established:276 refused:0 current:278 max:40000 state:ready CMON <machine: oradbserver1.mock.uat, pid: 261100> Wed May 20 01:58:28 IST 2026 "N000" established:8160 refused:0 current:8162 max:40000 state:ready CMON <machine: oradbserver1.mock.uat, pid: 261100> Wed May 20 01:58:29 IST 2026 "N000" established:10000 refused:0 current:9999 max:40000 state:ready CMON <machine: oradbserver1.mock.uat, pid: 261100> Wed May 20 01:58:31 IST 2026 "N000" established:10000 refused:0 current:9999 max:40000 state:ready CMON <machine: oradbserver1.mock.uat, pid: 261100> Wed May 20 01:58:32 IST 2026 "N000" established:10000 refused:0 current:10000 max:40000 state:ready CMON <machine: oradbserver1.mock.uat, pid: 261100> Wed May 20 01:58:33 IST 2026 "N000" established:10000 refused:0 current:10000 max:40000 state:ready CMON <machine: oradbserver1.mock.uat, pid: 261100> Wed May 20 01:58:34 IST 2026 "N000" established:10000 refused:0 current:10000 max:40000 state:ready CMON <machine: oradbserver1.mock.uat, pid: 261100> Wed May 20 01:58:35 IST 2026 "N000" established:10000 refused:0 current:10000 max:40000 state:ready CMON <machine: oradbserver1.mock.uat, pid: 261100> Wed May 20 01:58:37 IST 2026 "N000" established:10000 refused:0 current:10000 max:40000 state:ready CMON <machine: oradbserver1.mock.uat, pid: 261100> Wed May 20 01:58:38 IST 2026 "N000" established:10000 refused:0 current:10000 max:40000 state:ready CMON <machine: oradbserver1.mock.uat, pid: 261100> Wed May 20 01:58:39 IST 2026 "N000" established:10000 refused:0 current:10000 max:40000 state:ready CMON <machine: oradbserver1.mock.uat, pid: 261100> Wed May 20 01:58:40 IST 2026 "N000" established:10000 refused:0 current:10000 max:40000 state:ready CMON <machine: oradbserver1.mock.uat, pid: 261100> Wed May 20 01:58:41 IST 2026 "N000" established:10000 refused:0 current:10000 max:40000 state:ready CMON <machine: oradbserver1.mock.uat, pid: 261100> Wed May 20 01:58:43 IST 2026 "N000" established:10000 refused:0 current:10000 max:40000 state:ready CMON <machine: oradbserver1.mock.uat, pid: 261100> Wed May 20 01:58:44 IST 2026 "N000" established:10000 refused:0 current:10000 max:40000 state:ready CMON <machine: oradbserver1.mock.uat, pid: 261100> Wed May 20 01:58:45 IST 2026 "N000" established:10000 refused:0 current:10000 max:40000 state:ready CMON <machine: oradbserver1.mock.uat, pid: 261100> Wed May 20 01:58:46 IST 2026 "N000" established:10000 refused:0 current:10000 max:40000 state:ready CMON <machine: oradbserver1.mock.uat, pid: 261100> Wed May 20 01:58:47 IST 2026 "N000" established:10000 refused:0 current:10000 max:40000 state:ready CMON <machine: oradbserver1.mock.uat, pid: 261100> Wed May 20 01:58:49 IST 2026 "N000" established:10000 refused:0 current:10000 max:40000 state:ready CMON <machine: oradbserver1.mock.uat, pid: 261100> Wed May 20 01:58:50 IST 2026 "N000" established:10000 refused:0 current:10000 max:40000 state:ready CMON <machine: oradbserver1.mock.uat, pid: 261100> Wed May 20 01:58:51 IST 2026 "N000" established:10000 refused:0 current:10000 max:40000 state:ready CMON <machine: oradbserver1.mock.uat, pid: 261100> Wed May 20 01:58:52 IST 2026 "N000" established:10000 refused:0 current:10000 max:40000 state:ready CMON <machine: oradbserver1.mock.uat, pid: 261100> Wed May 20 01:58:53 IST 2026 "N000" established:10000 refused:0 current:10000 max:40000 state:ready CMON <machine: oradbserver1.mock.uat, pid: 261100> Wed May 20 01:58:55 IST 2026 "N000" established:10000 refused:0 current:10000 max:40000 state:ready CMON <machine: oradbserver1.mock.uat, pid: 261100> Wed May 20 01:58:56 IST 2026 "N000" established:10000 refused:0 current:10000 max:40000 state:ready CMON <machine: oradbserver1.mock.uat, pid: 261100> Wed May 20 01:58:57 IST 2026 "N000" established:10000 refused:0 current:10000 max:40000 state:ready CMON <machine: oradbserver1.mock.uat, pid: 261100> Wed May 20 01:58:58 IST 2026 "N000" established:10000 refused:0 current:10000 max:40000 state:ready CMON <machine: oradbserver1.mock.uat, pid: 261100> Wed May 20 01:58:59 IST 2026 "N000" established:10000 refused:0 current:10000 max:40000 state:ready CMON <machine: oradbserver1.mock.uat, pid: 261100> Wed May 20 01:59:01 IST 2026 "N000" established:10000 refused:0 current:10000 max:40000 state:ready CMON <machine: oradbserver1.mock.uat, pid: 261100> Wed May 20 01:59:02 IST 2026 "N000" established:10000 refused:0 current:10000 max:40000 state:ready CMON <machine: oradbserver1.mock.uat, pid: 261100> Wed May 20 01:59:03 IST 2026 "N000" established:10000 refused:0 current:10000 max:40000 state:ready CMON <machine: oradbserver1.mock.uat, pid: 261100> Wed May 20 01:59:04 IST 2026 "N000" established:10000 refused:0 current:10000 max:40000 state:ready CMON <machine: oradbserver1.mock.uat, pid: 261100> Wed May 20 01:59:05 IST 2026 "N000" established:10000 refused:0 current:10000 max:40000 state:ready CMON <machine: oradbserver1.mock.uat, pid: 261100> Wed May 20 01:59:07 IST 2026 "N000" established:10000 refused:0 current:10000 max:40000 state:ready CMON <machine: oradbserver1.mock.uat, pid: 261100> Wed May 20 01:59:08 IST 2026 "N000" established:10000 refused:0 current:10000 max:40000 state:ready CMON <machine: oradbserver1.mock.uat, pid: 261100> Wed May 20 01:59:09 IST 2026 "N000" established:10000 refused:0 current:10000 max:40000 state:ready CMON <machine: oradbserver1.mock.uat, pid: 261100> Wed May 20 01:59:10 IST 2026 "N000" established:10000 refused:0 current:10000 max:40000 state:ready CMON <machine: oradbserver1.mock.uat, pid: 261100> Wed May 20 01:59:11 IST 2026 "N000" established:10000 refused:0 current:10000 max:40000 state:ready CMON <machine: oradbserver1.mock.uat, pid: 261100> Wed May 20 01:59:13 IST 2026 "N000" established:10000 refused:0 current:10000 max:40000 state:ready CMON <machine: oradbserver1.mock.uat, pid: 261100> Wed May 20 01:59:14 IST 2026 "N000" established:10000 refused:0 current:10000 max:40000 state:ready CMON <machine: oradbserver1.mock.uat, pid: 261100> Wed May 20 01:59:15 IST 2026 "N000" established:10000 refused:0 current:10000 max:40000 state:ready CMON <machine: oradbserver1.mock.uat, pid: 261100> Wed May 20 01:59:16 IST 2026 "N000" established:10000 refused:0 current:10000 max:40000 state:ready CMON <machine: oradbserver1.mock.uat, pid: 261100> Wed May 20 01:59:17 IST 2026 "N000" established:10000 refused:0 current:10000 max:40000 state:ready CMON <machine: oradbserver1.mock.uat, pid: 261100> Wed May 20 01:59:19 IST 2026 "N000" established:10000 refused:0 current:10000 max:40000 state:ready CMON <machine: oradbserver1.mock.uat, pid: 261100> Wed May 20 01:59:20 IST 2026 "N000" established:10000 refused:0 current:10000 max:40000 state:ready CMON <machine: oradbserver1.mock.uat, pid: 261100> Wed May 20 01:59:21 IST 2026 "N000" established:10000 refused:0 current:10000 max:40000 state:ready CMON <machine: oradbserver1.mock.uat, pid: 261100> Wed May 20 01:59:22 IST 2026 "N000" established:10000 refused:0 current:10000 max:40000 state:ready CMON <machine: oradbserver1.mock.uat, pid: 261100> Wed May 20 01:59:23 IST 2026 "N000" established:10000 refused:0 current:10000 max:40000 state:ready CMON <machine: oradbserver1.mock.uat, pid: 261100> Wed May 20 01:59:25 IST 2026 "N000" established:10000 refused:0 current:10000 max:40000 state:ready CMON <machine: oradbserver1.mock.uat, pid: 261100> Wed May 20 01:59:26 IST 2026 "N000" established:10000 refused:0 current:10000 max:40000 state:ready CMON <machine: oradbserver1.mock.uat, pid: 261100> Wed May 20 01:59:27 IST 2026 "N000" established:10000 refused:0 current:10000 max:40000 state:ready CMON <machine: oradbserver1.mock.uat, pid: 261100> ....... Wed May 20 02:01:50 IST 2026 "N000" established:10000 refused:0 current:10000 max:40000 state:ready CMON <machine: oradbserver1.mock.uat, pid: 261100> Wed May 20 02:01:51 IST 2026 "N000" established:10000 refused:0 current:10000 max:40000 state:ready CMON <machine: oradbserver1.mock.uat, pid: 261100> Wed May 20 02:01:52 IST 2026 "N000" established:10000 refused:0 current:10000 max:40000 state:ready CMON <machine: oradbserver1.mock.uat, pid: 261100> Wed May 20 02:01:53 IST 2026 "N000" established:10000 refused:0 current:10000 max:40000 state:ready CMON <machine: oradbserver1.mock.uat, pid: 261100> Wed May 20 02:01:54 IST 2026 "N000" established:10000 refused:0 current:10000 max:40000 state:ready CMON <machine: oradbserver1.mock.uat, pid: 261100> Wed May 20 02:01:56 IST 2026 "N000" established:10000 refused:0 current:10000 max:40000 state:ready CMON <machine: oradbserver1.mock.uat, pid: 261100> Wed May 20 02:01:57 IST 2026 "N000" established:10000 refused:0 current:10000 max:40000 state:ready CMON <machine: oradbserver1.mock.uat, pid: 261100> Wed May 20 02:01:58 IST 2026 "N000" established:10000 refused:0 current:10000 max:40000 state:ready CMON <machine: oradbserver1.mock.uat, pid: 261100> Wed May 20 02:01:59 IST 2026 "N000" established:10000 refused:0 current:10000 max:40000 state:ready CMON <machine: oradbserver1.mock.uat, pid: 261100> Wed May 20 02:02:00 IST 2026 "N000" established:10000 refused:0 current:10000 max:40000 state:ready CMON <machine: oradbserver1.mock.uat, pid: 261100> Wed May 20 02:02:02 IST 2026 "N000" established:10000 refused:0 current:10000 max:40000 state:ready CMON <machine: oradbserver1.mock.uat, pid: 261100> Wed May 20 02:02:03 IST 2026 "N000" established:10000 refused:0 current:10000 max:40000 state:ready CMON <machine: oradbserver1.mock.uat, pid: 261100> Wed May 20 02:02:04 IST 2026 "N000" established:10000 refused:0 current:10000 max:40000 state:ready CMON <machine: oradbserver1.mock.uat, pid: 261100> Wed May 20 02:02:05 IST 2026 "N000" established:10000 refused:0 current:10000 max:40000 state:ready CMON <machine: oradbserver1.mock.uat, pid: 261100> Wed May 20 02:02:06 IST 2026 "N000" established:10000 refused:0 current:10000 max:40000 state:ready CMON <machine: oradbserver1.mock.uat, pid: 261100> Wed May 20 02:02:08 IST 2026 "N000" established:10000 refused:0 current:10000 max:40000 state:ready CMON <machine: oradbserver1.mock.uat, pid: 261100> Wed May 20 02:02:09 IST 2026 "N000" established:10000 refused:0 current:10000 max:40000 state:ready CMON <machine: oradbserver1.mock.uat, pid: 261100> Wed May 20 02:02:10 IST 2026 "N000" established:10000 refused:0 current:10000 max:40000 state:ready CMON <machine: oradbserver1.mock.uat, pid: 261100> Wed May 20 02:02:11 IST 2026 "N000" established:10000 refused:0 current:10000 max:40000 state:ready CMON <machine: oradbserver1.mock.uat, pid: 261100> Wed May 20 02:02:12 IST 2026 "N000" established:10000 refused:0 current:10000 max:40000 state:ready CMON <machine: oradbserver1.mock.uat, pid: 261100> Wed May 20 02:02:14 IST 2026 "N000" established:10000 refused:0 current:10000 max:40000 state:ready CMON <machine: oradbserver1.mock.uat, pid: 261100> Wed May 20 02:02:15 IST 2026 "N000" established:10000 refused:0 current:10000 max:40000 state:ready CMON <machine: oradbserver1.mock.uat, pid: 261100> Wed May 20 02:02:16 IST 2026 "N000" established:10000 refused:0 current:10000 max:40000 state:ready CMON <machine: oradbserver1.mock.uat, pid: 261100> Wed May 20 02:02:17 IST 2026 "N000" established:10000 refused:0 current:10000 max:40000 state:ready CMON <machine: oradbserver1.mock.uat, pid: 261100> Wed May 20 02:02:18 IST 2026 "N000" established:10000 refused:0 current:10000 max:40000 state:ready CMON <machine: oradbserver1.mock.uat, pid: 261100> Wed May 20 02:02:20 IST 2026 "N000" established:10000 refused:0 current:10000 max:40000 state:ready CMON <machine: oradbserver1.mock.uat, pid: 261100> Wed May 20 02:02:21 IST 2026 "N000" established:10000 refused:0 current:10000 max:40000 state:ready CMON <machine: oradbserver1.mock.uat, pid: 261100> Wed May 20 02:02:22 IST 2026 "N000" established:10000 refused:0 current:10000 max:40000 state:ready CMON <machine: oradbserver1.mock.uat, pid: 261100> Wed May 20 02:02:23 IST 2026 "N000" established:10000 refused:0 current:10000 max:40000 state:ready CMON <machine: oradbserver1.mock.uat, pid: 261100> Wed May 20 02:02:24 IST 2026 "N000" established:10000 refused:0 current:10000 max:40000 state:ready CMON <machine: oradbserver1.mock.uat, pid: 261100> SDT TOTAL_PROCESSES ------------------ --------------- 20-MAY-26 01:57:50 1002 SDT SERVER COUNT(*) ------------------ --------- ---------- 20-MAY-26 01:57:50 DEDICATED 181 SDT PGA_MB ------------------ ---------- 20-MAY-26 01:57:50 1742 SDT TOTAL_PROCESSES ------------------ --------------- 20-MAY-26 01:57:51 1002 SDT SERVER COUNT(*) ------------------ --------- ---------- 20-MAY-26 01:57:51 DEDICATED 181 SDT PGA_MB ------------------ ---------- 20-MAY-26 01:57:51 1743 SDT TOTAL_PROCESSES ------------------ --------------- 20-MAY-26 01:57:52 1002 SDT SERVER COUNT(*) ------------------ --------- ---------- 20-MAY-26 01:57:52 DEDICATED 181 SDT PGA_MB ------------------ ---------- 20-MAY-26 01:57:52 1743 SDT TOTAL_PROCESSES ------------------ --------------- 20-MAY-26 01:57:53 1003 SDT SERVER COUNT(*) ------------------ --------- ---------- 20-MAY-26 01:57:53 DEDICATED 182 SDT PGA_MB ------------------ ---------- 20-MAY-26 01:57:53 1777 SDT TOTAL_PROCESSES ------------------ --------------- 20-MAY-26 01:57:54 1003 SDT SERVER COUNT(*) ------------------ --------- ---------- 20-MAY-26 01:57:54 DEDICATED 182 SDT PGA_MB ------------------ ---------- 20-MAY-26 01:57:54 1776 SDT TOTAL_PROCESSES ------------------ --------------- 20-MAY-26 01:57:55 1004 SDT SERVER COUNT(*) ------------------ --------- ---------- 20-MAY-26 01:57:55 DEDICATED 183 SDT PGA_MB ------------------ ---------- 20-MAY-26 01:57:55 1777 SDT TOTAL_PROCESSES ------------------ --------------- 20-MAY-26 01:57:56 1004 SDT SERVER COUNT(*) ------------------ --------- ---------- 20-MAY-26 01:57:56 DEDICATED 183 SDT PGA_MB ------------------ ---------- 20-MAY-26 01:57:56 1777 SDT TOTAL_PROCESSES ------------------ --------------- 20-MAY-26 01:57:57 1004 SDT SERVER COUNT(*) ------------------ --------- ---------- 20-MAY-26 01:57:57 DEDICATED 183 SDT PGA_MB ------------------ ---------- 20-MAY-26 01:57:57 1777 .............. SDT TOTAL_PROCESSES ------------------ --------------- 20-MAY-26 01:59:07 1183 SDT SERVER COUNT(*) ------------------ --------- ---------- 20-MAY-26 01:59:07 POOLED 200 20-MAY-26 01:59:07 DEDICATED 188 SDT PGA_MB ------------------ ---------- 20-MAY-26 01:59:07 2152 SDT TOTAL_PROCESSES ------------------ --------------- 20-MAY-26 01:59:08 1182 SDT SERVER COUNT(*) ------------------ --------- ---------- 20-MAY-26 01:59:08 POOLED 200 20-MAY-26 01:59:08 DEDICATED 187 SDT PGA_MB ------------------ ---------- 20-MAY-26 01:59:08 2150 SDT TOTAL_PROCESSES ------------------ --------------- 20-MAY-26 01:59:09 1182 SDT SERVER COUNT(*) ------------------ --------- ---------- 20-MAY-26 01:59:09 POOLED 200 20-MAY-26 01:59:09 DEDICATED 187 SDT PGA_MB ------------------ ---------- 20-MAY-26 01:59:09 2150 SDT TOTAL_PROCESSES ------------------ --------------- 20-MAY-26 01:59:10 1182 SDT SERVER COUNT(*) ------------------ --------- ---------- 20-MAY-26 01:59:10 POOLED 200 20-MAY-26 01:59:10 DEDICATED 187 SDT PGA_MB ------------------ ---------- 20-MAY-26 01:59:10 2150 SDT TOTAL_PROCESSES ------------------ --------------- 20-MAY-26 01:59:11 1182 SDT SERVER COUNT(*) ------------------ --------- ---------- 20-MAY-26 01:59:11 POOLED 200 20-MAY-26 01:59:11 DEDICATED 187 SDT PGA_MB ------------------ ---------- 20-MAY-26 01:59:11 2150 ............ SDT TOTAL_PROCESSES ------------------ --------------- 20-MAY-26 02:01:03 1180 SDT SERVER COUNT(*) ------------------ --------- ---------- 20-MAY-26 02:01:03 POOLED 200 20-MAY-26 02:01:03 DEDICATED 185 SDT PGA_MB ------------------ ---------- 20-MAY-26 02:01:03 2148 SDT TOTAL_PROCESSES ------------------ --------------- 20-MAY-26 02:01:04 1180 SDT SERVER COUNT(*) ------------------ --------- ---------- 20-MAY-26 02:01:04 POOLED 200 20-MAY-26 02:01:04 DEDICATED 185 SDT PGA_MB ------------------ ---------- 20-MAY-26 02:01:04 2148 SDT TOTAL_PROCESSES ------------------ --------------- 20-MAY-26 02:01:05 1180 SDT SERVER COUNT(*) ------------------ --------- ---------- 20-MAY-26 02:01:05 POOLED 200 20-MAY-26 02:01:05 DEDICATED 185 SDT PGA_MB ------------------ ---------- 20-MAY-26 02:01:05 2148 SDT TOTAL_PROCESSES ------------------ --------------- 20-MAY-26 02:01:06 1180 SDT SERVER COUNT(*) ------------------ --------- ---------- 20-MAY-26 02:01:06 POOLED 200 20-MAY-26 02:01:06 DEDICATED 185 SDT PGA_MB ------------------ ---------- 20-MAY-26 02:01:06 2148 SDT TOTAL_PROCESSES ------------------ --------------- 20-MAY-26 02:01:07 1180 SDT SERVER COUNT(*) ------------------ --------- ---------- 20-MAY-26 02:01:07 POOLED 200 20-MAY-26 02:01:07 DEDICATED 185 SDT PGA_MB ------------------ ---------- 20-MAY-26 02:01:07 2148 SDT TOTAL_PROCESSES ------------------ --------------- 20-MAY-26 02:01:08 1180 SDT SERVER COUNT(*) ------------------ --------- ---------- 20-MAY-26 02:01:08 POOLED 200 20-MAY-26 02:01:08 DEDICATED 185 SDT PGA_MB ------------------ ---------- 20-MAY-26 02:01:08 2148 SDT TOTAL_PROCESSES ------------------ --------------- 20-MAY-26 02:01:09 1180 SDT SERVER COUNT(*) ------------------ --------- ---------- 20-MAY-26 02:01:09 POOLED 200 20-MAY-26 02:01:09 DEDICATED 185 SDT PGA_MB ------------------ ---------- 20-MAY-26 02:01:09 2148 ............. SDT TOTAL_PROCESSES ------------------ --------------- 20-MAY-26 02:02:10 1181 SDT SERVER COUNT(*) ------------------ --------- ---------- 20-MAY-26 02:02:10 POOLED 200 20-MAY-26 02:02:10 DEDICATED 186 SDT PGA_MB ------------------ ---------- 20-MAY-26 02:02:10 2153 SDT TOTAL_PROCESSES ------------------ --------------- 20-MAY-26 02:02:11 1181 SDT SERVER COUNT(*) ------------------ --------- ---------- 20-MAY-26 02:02:11 POOLED 200 20-MAY-26 02:02:11 DEDICATED 186 SDT PGA_MB ------------------ ---------- 20-MAY-26 02:02:11 2153 SDT TOTAL_PROCESSES ------------------ --------------- 20-MAY-26 02:02:12 1181 SDT SERVER COUNT(*) ------------------ --------- ---------- 20-MAY-26 02:02:12 POOLED 200 20-MAY-26 02:02:12 DEDICATED 186 SDT PGA_MB ------------------ ---------- 20-MAY-26 02:02:12 2153 SDT TOTAL_PROCESSES ------------------ --------------- 20-MAY-26 02:02:13 1182 SDT SERVER COUNT(*) ------------------ --------- ---------- 20-MAY-26 02:02:13 POOLED 200 20-MAY-26 02:02:13 DEDICATED 187 SDT PGA_MB ------------------ ---------- 20-MAY-26 02:02:13 2155 SDT TOTAL_PROCESSES ------------------ --------------- 20-MAY-26 02:02:14 1182 SDT SERVER COUNT(*) ------------------ --------- ---------- 20-MAY-26 02:02:14 POOLED 200 20-MAY-26 02:02:14 DEDICATED 187 SDT PGA_MB ------------------ ---------- 20-MAY-26 02:02:14 2155 SDT TOTAL_PROCESSES ------------------ --------------- 20-MAY-26 02:02:15 1182 SDT SERVER COUNT(*) ------------------ --------- ---------- 20-MAY-26 02:02:15 POOLED 200 20-MAY-26 02:02:15 DEDICATED 187 SDT PGA_MB ------------------ ---------- 20-MAY-26 02:02:15 2155 SDT TOTAL_PROCESSES ------------------ --------------- 20-MAY-26 02:02:16 1183 SDT SERVER COUNT(*) ------------------ --------- ---------- 20-MAY-26 02:02:16 POOLED 200 20-MAY-26 02:02:16 DEDICATED 188 SDT PGA_MB ------------------ ---------- 20-MAY-26 02:02:16 2156 SDT TOTAL_PROCESSES ------------------ --------------- 20-MAY-26 02:02:18 1183 SDT SERVER COUNT(*) ------------------ --------- ---------- 20-MAY-26 02:02:18 POOLED 200 20-MAY-26 02:02:18 DEDICATED 188 SDT PGA_MB ------------------ ---------- 20-MAY-26 02:02:18 2156 SDT TOTAL_PROCESSES ------------------ --------------- 20-MAY-26 02:02:19 1183 SDT SERVER COUNT(*) ------------------ --------- ---------- 20-MAY-26 02:02:19 POOLED 200 20-MAY-26 02:02:19 DEDICATED 188 SDT PGA_MB ------------------ ---------- 20-MAY-26 02:02:19 2156 SDT TOTAL_PROCESSES ------------------ --------------- 20-MAY-26 02:02:20 1184 SDT SERVER COUNT(*) ------------------ --------- ---------- 20-MAY-26 02:02:20 POOLED 200 20-MAY-26 02:02:20 DEDICATED 189 SDT PGA_MB ------------------ ---------- 20-MAY-26 02:02:20 2158 SDT TOTAL_PROCESSES ------------------ --------------- 20-MAY-26 02:02:21 1184 SDT SERVER COUNT(*) ------------------ --------- ---------- 20-MAY-26 02:02:21 POOLED 200 20-MAY-26 02:02:21 DEDICATED 189 SDT PGA_MB ------------------ ---------- 20-MAY-26 02:02:21 2158 SDT TOTAL_PROCESSES ------------------ --------------- 20-MAY-26 02:02:22 1184 SDT SERVER COUNT(*) ------------------ --------- ---------- 20-MAY-26 02:02:22 POOLED 200 20-MAY-26 02:02:22 DEDICATED 189 SDT PGA_MB ------------------ ---------- 20-MAY-26 02:02:22 2158 SDT TOTAL_PROCESSES ------------------ --------------- 20-MAY-26 02:02:23 1184 SDT SERVER COUNT(*) ------------------ --------- ---------- 20-MAY-26 02:02:23 POOLED 200 20-MAY-26 02:02:23 DEDICATED 189 SDT PGA_MB ------------------ ---------- 20-MAY-26 02:02:23 2158 SDT TOTAL_PROCESSES ------------------ --------------- 20-MAY-26 02:02:24 1184 SDT SERVER COUNT(*) ------------------ --------- ---------- 20-MAY-26 02:02:24 POOLED 200 20-MAY-26 02:02:24 DEDICATED 189 SDT PGA_MB ------------------ ---------- 20-MAY-26 02:02:24 2158 SDT TOTAL_PROCESSES ------------------ --------------- 20-MAY-26 02:02:25 1184 SDT SERVER COUNT(*) ------------------ --------- ---------- 20-MAY-26 02:02:25 POOLED 200 20-MAY-26 02:02:25 DEDICATED 189 SDT PGA_MB ------------------ ---------- 20-MAY-26 02:02:25 2158 SDT TOTAL_PROCESSES ------------------ --------------- 20-MAY-26 02:02:26 1184 SDT SERVER COUNT(*) ------------------ --------- ---------- 20-MAY-26 02:02:26 POOLED 200 20-MAY-26 02:02:26 DEDICATED 189 SDT PGA_MB ------------------ ---------- 20-MAY-26 02:02:26 2158 SDT TOTAL_PROCESSES ------------------ --------------- 20-MAY-26 02:02:27 1184 SDT SERVER COUNT(*) ------------------ --------- ---------- 20-MAY-26 02:02:27 POOLED 200 20-MAY-26 02:02:27 DEDICATED 189 SDT PGA_MB ------------------ ---------- 20-MAY-26 02:02:27 2158 SDT TOTAL_PROCESSES ------------------ --------------- 20-MAY-26 02:02:28 1184 SDT SERVER COUNT(*) ------------------ --------- ---------- 20-MAY-26 02:02:28 POOLED 200 20-MAY-26 02:02:28 DEDICATED 189 SDT PGA_MB ------------------ ---------- 20-MAY-26 02:02:28 2158 SDT TOTAL_PROCESSES ------------------ --------------- 20-MAY-26 02:02:29 1184 SDT SERVER COUNT(*) ------------------ --------- ---------- 20-MAY-26 02:02:29 POOLED 200 20-MAY-26 02:02:29 DEDICATED 189 SDT PGA_MB ------------------ ---------- 20-MAY-26 02:02:29 2158 SDT TOTAL_PROCESSES ------------------ --------------- 20-MAY-26 02:02:30 1184 SDT SERVER COUNT(*) ------------------ --------- ---------- 20-MAY-26 02:02:30 POOLED 200 20-MAY-26 02:02:30 DEDICATED 189 SDT PGA_MB ------------------ ---------- 20-MAY-26 02:02:30 2158 SDT TOTAL_PROCESSES ------------------ --------------- 20-MAY-26 02:02:31 1184 SDT SERVER COUNT(*) ------------------ --------- ---------- 20-MAY-26 02:02:31 POOLED 200 20-MAY-26 02:02:31 DEDICATED 189 SDT PGA_MB ------------------ ---------- 20-MAY-26 02:02:31 2158 SDT TOTAL_PROCESSES ------------------ --------------- 20-MAY-26 02:02:32 1184 SDT SERVER COUNT(*) ------------------ --------- ---------- 20-MAY-26 02:02:32 POOLED 200 20-MAY-26 02:02:32 DEDICATED 189 SDT PGA_MB ------------------ ---------- 20-MAY-26 02:02:32 2158 SDT TOTAL_PROCESSES ------------------ --------------- 20-MAY-26 02:02:33 1184 SDT SERVER COUNT(*) ------------------ --------- ---------- 20-MAY-26 02:02:33 POOLED 200 20-MAY-26 02:02:33 DEDICATED 189 SDT PGA_MB ------------------ ---------- 20-MAY-26 02:02:33 2158 SDT TOTAL_PROCESSES ------------------ --------------- 20-MAY-26 02:02:34 1184 SDT SERVER COUNT(*) ------------------ --------- ---------- 20-MAY-26 02:02:34 POOLED 200 20-MAY-26 02:02:34 DEDICATED 189 SDT PGA_MB ------------------ ---------- 20-MAY-26 02:02:34 2158 SDT TOTAL_PROCESSES ------------------ --------------- 20-MAY-26 02:02:35 1184 SDT SERVER COUNT(*) ------------------ --------- ---------- 20-MAY-26 02:02:35 POOLED 200 20-MAY-26 02:02:35 DEDICATED 189 SDT PGA_MB ------------------ ---------- 20-MAY-26 02:02:35 2158 SDT TOTAL_PROCESSES ------------------ --------------- 20-MAY-26 02:02:36 1184 SDT SERVER COUNT(*) ------------------ --------- ---------- 20-MAY-26 02:02:36 POOLED 200 20-MAY-26 02:02:36 DEDICATED 189 SDT PGA_MB ------------------ ---------- 20-MAY-26 02:02:36 2158 .........     📌Note : Please note that the below observations are based on the current configuration, workload pattern, and my lab/testing results conducted in the existing environment setup. Actual performance may vary depending on application workload, session concurrency, infrastructure sizing, database configuration, and runtime conditions. 📊 Key Observations: Dedicated (Without DRCP) vs DRCP (Pooled): The below comparison highlights the effectiveness of Database Resident Connection Pooling (DRCP) in optimizing database connection management and improving overall resource efficiency. 🔴 Dedicated (Without DRCP): ⚠️ High PGA Memory Consumption: - PGA Memory reached approximately 29.5 GB. - Memory usage increased rapidly with growing sessions. ⚠️Higher Resource Utilization: - Dedicated sessions consumed. - More PGA memory. - More server processes. ⚠️ Less Efficient Scalability: - Each connection maintained it's own dedicated resources. - Memory overhead increased significantly under load. ⚠️ Process Overhead: - Large number of dedicated processes created. - Increased process management overhead. 🟢 DRCP (Pooled): ⚡ Significant PGA Memory Optimization: - PGA Memory stabilized around ~200 MB. - Approximate memory reduction: ~93%. ⚡ Stable Memory Utilization: - Memory usage remained consistent and controlled. - No aggressive spikes observed. ⚡ Better Connection Management: - DRCP reused pooled server processes efficiently. - Reduced unnecessary process creation. ⚡ Improved Scalability: - Supported increasing sessions with minimal memory growth. - Efficient for high-concurrency workloads. ⚡Lower Infrastructure Cost: - Reduced server memory requirements. - Better database consolidation efficiency. Step 10: Now Let's check the AWR reports generated during both "Without" and "With" DRCP simulation and observe the differences.  📌Observations from AWR Metrics Comparison (Dedicated vs DRCP Pooled) - (Load Profile): ✔️ DB Time(s) reduced by 85.7%, indicating faster database processing. ✔️ DB CPU(s) reduced by 85.7%, lowering overall CPU consumption. ✔️ User Calls(s) reduced by 66.3%, minimizing connection overhead. ✔️ SQL Parses(s) reduced by 32.6%, improving session reuse efficiency. ✔️ SQL Executes(s) reduced by 33.5%, reducing execution overhead. ✔️ Hard Parses(s) reduced by 16.7%, lowering SQL compilation costs. ➖ Logon activity(s) remained nearly unchanged, ensuring a comparable workload.   📌Observations from AWR Metrics Comparison (Dedicated vs DRCP Pooled) - (Time Model Stats): ✔️ Connection Management Call Elapsed Time Per Second reduced by 78.8% (42.96s → 9.11s), significantly lowering connection establishment overhead. ✔️ DRCP completed connection management operations 4.7× faster than Dedicated Connections. ✔️ Reduced connection setup time improves application responsiveness and scalability. ✔️ Lower connection overhead enables more efficient utilization of database resources. 🏆 Conclusion: DRCP significantly minimizes connection management latency, reducing elapsed time from 42.96 seconds to 9.11 seconds and delivering 78.8% faster connection handling.  📌Observations from AWR Metrics Comparison (Dedicated vs DRCP Pooled) - (PGA Memory Stats): ✔️ PGA memory growth reduced by 95.4% with DRCP (10,254.2 MB → 471.8 MB increase). ✔️ Without DRCP, PGA usage grew by 10.25 GB during the workload (2,988.5 MB → 13,242.7 MB). ✔️ With DRCP, PGA usage remained nearly stable, increasing by only 471.8 MB (4,047.7 MB → 4,519.5 MB). ✔️ DRCP significantly reduced memory consumption by reusing pooled database sessions. ✔️ Lower PGA growth improves scalability and allows more concurrent connections with the same server resources. 🏆 Conclusion: DRCP dramatically reduced PGA memory growth from 10.25 GB to 471.8 MB, delivering a 95.4% reduction in memory overhead while maintaining workload execution.  🏁 Final Conclusion: 🔴 Dedicated (Without DRCP): - Consumes significantly higher memory. - Creates more process overhead. - Less efficient under high concurrency. 🟢 DRCP (Pooled): - Dramatically reduces PGA memory usage. - Optimizes process utilization. - Improves scalability and database efficiency. ✅ DRCP (Pooled) clearly provides superior memory optimization, scalability, and resource efficiency compared to traditional Dedicated connections(Without DRCP). 📢 Join My WhatsApp Group👉- Oracle DBA++ Stay updated with the latest Oracle tips, blog posts, and database insights. |
Thanks for reading this post ! Please comment if you like this post ! Click on FOLLOW to get next blog updates !
