What is the fastest method to connect to an Oracle database?

There are still applications around, which do connect to the database, do some stuff and then disconnect again. I.e. for each and every operation a logon to the database is necessary. This produces unnecessary overhead and is expressed in a high percentage of DB time taken by „connection management call elapsed time“ in the Oracle time model. In case the application cannot be changed and a lot of connections are happening per second, then the question is on what is the fastest method to connect to an Oracle database?

I do want to compare 3 different methods on how connects to the DB are handled and see what is the fastest in terms of connect-time:

Dedicated Server
Shared Server
Database Resident Connection Pooling (DRCP)

For the test I used the following configuration:

Container database cdb0, version 19.19. with pluggable database pdb0 running in a VM on Proxmox.
Client with version 19.16. running on a VM on Proxmox (different physical server than the DB).

REMARK: I do not use technologies, which may increase logon-time (like logon-triggers or logon-auditing) during the tests performed.

Preparation

Setup shared server

For shared server I created a service pdb0_shared in my PDB pdb0:

alter session set container=pdb0;
exec dbms_service.create_service('pdb0_shared','pdb0_shared');
exec dbms_service.start_service('pdb0_shared');

Then I setup 10 shared server processes and 4 dispatchers. This has to be done in the root container:

connect / as sysdba
ALTER SYSTEM SET shared_servers= 10 SCOPE = BOTH ;
ALTER SYSTEM SET max_shared_servers = 100 SCOPE = BOTH;
ALTER SYSTEM SET dispatchers ='(INDEX=1)(PROTOCOL=TCP)(dispatchers=4)(SERVICE=DORNER_LIS)' SCOPE = BOTH;

REMARK: The INDEX=1 means that I do not change the existing dispatcher-setting (e.g. for XDB).

Verify the setup:

$ lsnrctl services listener1
...
Service "pdb0_shared" has 1 instance(s).
  Instance "cdb0", status READY, has 4 handler(s) for this service...
    Handler(s):
      "D004" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: pm-DB-OEL8, pid: 57522>
         (ADDRESS=(PROTOCOL=tcp)(HOST=pm-DB-OEL8)(PORT=32407))
      "D003" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: pm-DB-OEL8, pid: 57520>
         (ADDRESS=(PROTOCOL=tcp)(HOST=pm-DB-OEL8)(PORT=17299))
      "D002" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: pm-DB-OEL8, pid: 57518>
         (ADDRESS=(PROTOCOL=tcp)(HOST=pm-DB-OEL8)(PORT=19185))
      "D001" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: pm-DB-OEL8, pid: 57516>
         (ADDRESS=(PROTOCOL=tcp)(HOST=pm-DB-OEL8)(PORT=15569))
The command completed successfully

$ sqlplus cbleile@pm-DB-OEL8:1531/pdb0_shared:shared

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jan 18 19:29:34 2024
Version 19.16.0.0.0

Copyright (c) 1982, 2022, Oracle.  All rights reserved.

Enter password: 
Last Successful login time: Thu Jan 18 2024 19:17:55 +01:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.19.0.0.0

SQL>

REMARK: Please consider the „:shared“ in the Easy Connect string. Using a service, which has no shared service connect handler results in an error when connecting:

$ sqlplus cbleile@pm-DB-OEL8:1531/pdb0:shared

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jan 18 19:30:40 2024
Version 19.16.0.0.0

Copyright (c) 1982, 2022, Oracle.  All rights reserved.

Enter password: 
ERROR:
ORA-12520: TNS:listener could not find available handler for requested type of server

Setup pooled connections through the DRCP

This is simple with a single command in the root container:

sys@CDB0> EXEC DBMS_CONNECTION_POOL.start_pool;

PL/SQL procedure successfully completed.

For details see
https://oracle-base.com/articles/11g/database-resident-connection-pool-11gr1
https://oracle-base.com/articles/23c/database-resident-connection-pool-enhancements-23c

Testing

I created 3 test scripts for dedicated, shared and pooled connections:

$ cat loop_connect_speed_pdb0_dedicated.sh
#!/bin/bash
# Purpose: Connect to a DB using a dedicated server connect and immediately 
#          disconnet then. Do this n times (parameter 1).
# History: 18.01.2024 CBL: Created
#############################################################################

if [ $# -ne 2 ]
then
   echo "Usage: $0 <number of connections> <listener port>"
   exit 1
fi

NUMBER_OF_CONNECTS=$1
LISTENER_PORT=$2

for (( c=1; c<=$NUMBER_OF_CONNECTS; c++ ))
do
   echo exit | sqlplus -S cbleile/<password>@pm-DB-OEL8:${LISTENER_PORT}/pdb0:dedicated
done


$ cat loop_connect_speed_pdb0_shared.sh
#!/bin/bash
# Purpose: Connect to a DB using a shared server connect and immediately 
#          disconnet then. Do this n times (parameter 1).
# History: 18.01.2024 CBL: Created
#############################################################################

if [ $# -ne 2 ]
then
   echo "Usage: $0 <number of connections> <listener port>"
   exit 1
fi

NUMBER_OF_CONNECTS=$1
LISTENER_PORT=$2

for (( c=1; c<=$NUMBER_OF_CONNECTS; c++ ))
do
   echo exit | sqlplus -S cbleile/<password>@pm-DB-OEL8:${LISTENER_PORT}/pdb0_shared:shared
done


$ cat loop_connect_speed_pdb0_pooled.sh
#!/bin/bash
# Purpose: Connect to a DB using a pooled server connect and immediately 
#          disconnet then. Do this n times (parameter 1).
# History: 18.01.2024 CBL: Created
#############################################################################

if [ $# -ne 2 ]
then
   echo "Usage: $0 <number of connections> <listener port>"
   exit 1
fi

NUMBER_OF_CONNECTS=$1
LISTENER_PORT=$2

for (( c=1; c<=$NUMBER_OF_CONNECTS; c++ ))
do
   echo exit | sqlplus -S cbleile/<password>@pm-DB-OEL8:${LISTENER_PORT}/pdb0:pooled
done

The scripts do 400 connects to the DB (one after the other in the loop) through the listner listening on port 1531. The results for 400 connects to the DB are as follows:

$ time ./loop_connect_speed_pdb0_dedicated.sh 400 1531

real	0m24.489s
user	0m7.418s
sys	0m3.132s

I.e. in average 61ms per connect/disconnect using a dedicated server.

 time ./loop_connect_speed_pdb0_shared.sh 400 1531

real	0m17.399s
user	0m7.460s
sys	0m3.038s

I.e. in average 44ms per connect/disconnect using a shared server.

$ time ./loop_connect_speed_pdb0_pooled.sh 400 1531

real	0m18.235s
user	0m7.413s
sys	0m3.099s

I.e. in average 46ms per connect/disconnect using a pooled server.

I repeated the test several times and results are consistent. I.e. shared server connections are the fastest followed closely by pooled connections and dedicated server connections are obviously the slowest.

REMARK: Please consider that also the disconnect time is part of the measured time.

In above tests I connected 400 times serially, i.e. one after the other. What happens when connects happen concurrently? How does it scale?

Concurrent connects

I developed a second script, which starts e.g. 400 connections 2 or more times in parallel. I.e. with parallel 2 means I did 400 connections in two session, i.e. 800 connections in total using the following scripts:

oracle@19c-dg2:/home/oracle/ [rdbms1916] cat run_loop_pdb0_dedicated.sh
#!/bin/bash
# Purpose: Connect to a DB using a dedicated server connect and immediately 
#          disconnet then. Do this n times (parameter 1) and do this n times in parallel (parameter 2).
# History: 18.01.2024 CBL: Created
#############################################################################

if [ $# -ne 3 ]
then
   echo "Usage: $0 <number of connections> <number of parallel starts> <number of listeners>"
   exit 1
fi

NUMBER_OF_CONNECTS=$1
NUMBER_OF_PARALLEL_STARTS=$2
NUMBER_OF_LISTENERS=$3

for (( c=1; c<=$NUMBER_OF_PARALLEL_STARTS; c++ ))
do
   let "port_nr = $(($c % $NUMBER_OF_LISTENERS)) + 1"
#   echo $port_nr
   ./loop_connect_speed_pdb0_dedicated.sh $NUMBER_OF_CONNECTS 153${port_nr} &
done

# jobs

wait

The scripts for shared and pooled connects look accordingly.

Here the results to connect 1 – 6 times in parallel 400 connections (all going over 1 listener) via dedicated server:

oracle@19c-dg2:/home/oracle/ [rdbms1916] time ./run_loop_pdb0_dedicated.sh 400 1 1

real	0m24.265s
user	0m7.485s
sys	0m3.099s
oracle@19c-dg2:/home/oracle/ [rdbms1916] time ./run_loop_pdb0_dedicated.sh 400 2 1

real	0m24.569s
user	0m14.677s
sys	0m6.273s
oracle@19c-dg2:/home/oracle/ [rdbms1916] time ./run_loop_pdb0_dedicated.sh 400 3 1

real	0m26.511s
user	0m21.708s
sys	0m9.807s
oracle@19c-dg2:/home/oracle/ [rdbms1916] time ./run_loop_pdb0_dedicated.sh 400 4 1

real	0m29.579s
user	0m28.625s
sys	0m13.461s
oracle@19c-dg2:/home/oracle/ [rdbms1916] time ./run_loop_pdb0_dedicated.sh 400 5 1

real	0m33.383s
user	0m35.570s
sys	0m16.990s
oracle@19c-dg2:/home/oracle/ [rdbms1916] time ./run_loop_pdb0_dedicated.sh 400 6 1

real	0m37.954s
user	0m42.894s
sys	0m20.418s

So obviously something is slowing us down the higher concurrent logins get.

Results for shared server connects:

oracle@19c-dg2:/home/oracle/ [rdbms1916] time ./run_loop_pdb0_shared.sh 400 1 1

real	0m17.319s
user	0m7.531s
sys	0m3.028s
oracle@19c-dg2:/home/oracle/ [rdbms1916] time ./run_loop_pdb0_shared.sh 400 2 1

real	0m17.301s
user	0m14.338s
sys	0m6.468s
oracle@19c-dg2:/home/oracle/ [rdbms1916] time ./run_loop_pdb0_shared.sh 400 3 1

real	0m21.004s
user	0m21.426s
sys	0m9.922s
oracle@19c-dg2:/home/oracle/ [rdbms1916] time ./run_loop_pdb0_shared.sh 400 4 1

real	0m25.325s
user	0m28.712s
sys	0m13.222s
oracle@19c-dg2:/home/oracle/ [rdbms1916] time ./run_loop_pdb0_shared.sh 400 5 1

real	0m29.989s
user	0m35.659s
sys	0m16.876s
oracle@19c-dg2:/home/oracle/ [rdbms1916] time ./run_loop_pdb0_shared.sh 400 6 1

real	0m35.180s
user	0m42.738s
sys	0m20.573s

I.e. also a slow down and with higher concurrency the times get closer to dedicated connections. Finally pooled connections:

oracle@19c-dg2:/home/oracle/ [rdbms1916] time ./run_loop_pdb0_pooled.sh 400 1 1

real	0m17.977s
user	0m7.355s
sys	0m3.192s
oracle@19c-dg2:/home/oracle/ [rdbms1916] time ./run_loop_pdb0_pooled.sh 400 2 1

real	0m18.000s
user	0m14.439s
sys	0m6.428s
oracle@19c-dg2:/home/oracle/ [rdbms1916] time ./run_loop_pdb0_pooled.sh 400 3 1

real	0m21.257s
user	0m21.505s
sys	0m9.970s
oracle@19c-dg2:/home/oracle/ [rdbms1916] time ./run_loop_pdb0_pooled.sh 400 4 1

real	0m25.417s
user	0m28.549s
sys	0m13.497s
oracle@19c-dg2:/home/oracle/ [rdbms1916] time ./run_loop_pdb0_pooled.sh 400 5 1

real	0m30.227s
user	0m35.761s
sys	0m16.912s
oracle@19c-dg2:/home/oracle/ [rdbms1916] time ./run_loop_pdb0_pooled.sh 400 6 1

real	0m35.146s
user	0m42.688s
sys	0m20.551s

Here a graph showing the result:

Summary

If it gets to connect time to an Oracle database then shared and pooled server connects are faster than dedicated server. However, changing from dedicated server connections to e.g. shared server connects is only a workaround. The base problem should be addressed: The application should be designed to use a connection pool and re-use connections without doing login/logout for every action or, in case of classical client server, keep the connection open. Please consider that connecting and disconnecting to/from the DB may take a considerable time in your application. The reason why connects do not scale that well when connecting concurrently will be addressed in a future blog.

Clemens' Blogs