Oracle Database Resident Connection Pooling

How to use DRCP
DRCP defaults
Pooling effectiveness
Observations
Views on DRCP

How to use DRCP

To enable the database resident connection pool, the following needs to be run as SYSDBA

exec DBMS_CONNECTION_POOL.start_pool

Then one needs to change the client TNSNAMES.ORA, to replace the (SERVER = DEDICATED) with (SERVER = POOLED), and then it works.

If required, it can then be stopped with

exec DBMS_CONNECTION_POOL.stop_pool

Using the DRCP I found my connections kept getting disconnected, so I have adopted the following confuration:

BEGIN
  DBMS_CONNECTION_POOL.configure_pool(minsize              => 1
                                     ,maxsize              => 20
                                     ,incrsize             => 1
                                     ,inactivity_timeout   => 3600
                                     ,max_think_time       => 1800
                                     ,max_lifetime_session => 7200
                                     );
END;
/

The defaults for the above are

Parameter Default Value Comment

MINSIZE 4 Often times I will only be using one connection for a user, so having four in place seemed like a waste of memory to me
MAXSIZE 40
INCRSIZE 3 For my purposes I only want one more connection if there isn't a spare one when I try to connect
SESSION_CACHED_CURSORS 20
INACTIVITY_TIMEOUT 300 I increased this, as I rarely reuse a connection within 5 minutes
MAX_THINK_TIME 30 As most of my connections are SQL*Plus of SQL*Developer, I definitely wanted more that 30 seconds think time!
MAX_USE_SESSION 5000
MAX_LIFETIME_SESSION 3600

Parameter	Default Value	Comment
MINSIZE	4	Often times I will only be using one connection for a user, so having four in place seemed like a waste of memory to me
MAXSIZE	40
INCRSIZE	3	For my purposes I only want one more connection if there isn't a spare one when I try to connect
SESSION_CACHED_CURSORS	20
INACTIVITY_TIMEOUT	300	I increased this, as I rarely reuse a connection within 5 minutes
MAX_THINK_TIME	30	As most of my connections are SQLPlus of SQLDeveloper, I definitely wanted more that 30 seconds think time!
MAX_USE_SESSION	5000
MAX_LIFETIME_SESSION	3600

Pooling effectiveness

Testing the effectiveness of the connection pooling, I wrote a script to connect 100 times to a database, and perform a simple SELECT sysdate FROM dual;. The script connected to an Oracle 11.2.0.1 instance and an Oracle 12.1.0.2 instance, first using a dedicated connection, and then using a DRCP pooled connection.

Database Version	Average connect and sysdate query time (seconds)
Database Version	Dedicated Connection	DRCP Pooled Connection
11.2.0.1	0.164	0.099
12.1.0.2	0.177	0.116

For reference the script used to generate the above, and sample output, is at the bottom of this page.

One thing to note with the above, is the pooled connections from SQL*Plus were seen as "Purity NEW" connections, I couldn't figure out how to make SQL*Plus connect with a purity of "SELF", so more benefit is likely to be had from DRCP using clients connecting with a purity of SELF.

Observations

It is wise to create a new TNS name for the pooled connection, as there are some things that pooled connections can not be used for, and I have encountered problems with them.

Attempts to shut the database down using a pooled connection will be met with

ORA-56605: DRCP: Session switching and migration not allowed
ORA-56513: DRCP: Cannot perform requested operation using pooled connection

I have also found that after a period of hammering the database (with thousands of connections), attempts to use the pooled connection started to result in

ORA-03135: connection lost contact
Process ID: 0
Session ID: 0 Serial number: 0

I suspect that the connection problem would have cleared on it's own when the appropriate timeout had occurred, but I resolved this for my purposes by restarting DRCP:

exec DBMS_CONNECTION_POOL.stop_pool
exec DBMS_CONNECTION_POOL.start_pool

Views on DRCP

The following views can be used to see how DRCP is configured, and what it is up to:

DBA_CPOOL_INFO - shows DRCP pool configuration
V$CPOOL_STATS and V$CPOOL_CC_STATS - show pool usage information
V$CPOOL_CONN_INFO - shows individual connection details

Pooling effectiveness test script

Script:

#!/bin/bash
#

for repeat in 1 2 3 4 5 6 7 8 9 10
do
  for connection in ora11 ora11p o12102nc o12102ncp
  do
    echo Connection $connection
    (time for i in 1 2 3 4 5 6 7 8 9 10
    do
      echo "SELECT sysdate FROM dual;" |sqlplus -s rik/rik@$connection 2>&1 >/dev/null
    done) 2>&1 |sed 's/  */ /g' |awk -F" |      |m|s" '/real/ {print $3}' 
  done
done | awk '
  BEGIN {
    current_conn = "none";
    num_conn = 0;
    ora11_count = 0;
    ora11p_count = 0;
    o12102nc_count = 0;
    o12102ncp_count = 0;
  }

  $1 == "Connection" { current_conn = $2 }

  $1 != "Connection"  &&  current_conn == "ora11" { ora11[ora11_count++] = $1 }
  $1 != "Connection"  &&  current_conn == "ora11p" { ora11p[ora11p_count++] = $1 }
  $1 != "Connection"  &&  current_conn == "o12102nc" { o12102nc[o12102nc_count++] = $1 }
  $1 != "Connection"  &&  current_conn == "o12102ncp" { o12102ncp[o12102ncp_count++] = $1 }

  function avg(array, count) {
    total = 0;
    for (i=0; i<count; i++) total += array[i];
    return (total / count);
  }

  END {
    printf("%10.10s %10.10s %10.10s %10.10s\n", "ora11", "ora11p", "o12102nc", "o12102ncp");
    printf("---------- ---------- ---------- ----------\n");
    for (i=0; i<ora11_count; i++) {
      printf("%10f %10f %10f %10f\n", ora11[i], ora11p[i], o12102nc[i], o12102ncp[i]);
    }
    printf("---------- ---------- ---------- ----------\n");
    printf("%10f %10f %10f %10f\n", avg(ora11, ora11_count), avg(ora11p, ora11p_count), avg(o12102nc, o12102nc_count), avg(o12102ncp, o12102ncp_count));
  }
'

Sample script output: