Kevin Kempf's Blog

July 29, 2011

Oracle Database Resident Connection Pooling (DRCP)

Filed under: 11g, 11i — kkempf @ 9:34 am

This better work!

What is Oracle DRCP?

In a nutshell, it’s a pool of pre-allocated connections which can be invoked by a special call in the tnsnames.ora file.  In this manner, you “pay up front” once, and subsequently the “cost” of doing a connection to the database is dramatically reduced later.   A simple example is a web page.  By having a pool of connections already available, and recycling timed out connections, you can ensure your performance will be consistent regardless of volume (when the pool is properly tuned). I recommend this pageas a setup guide; it’s what I used.

A Bad Day

So we upgraded our main production server form 32gb of RAM to 64gb.  Happy day, right?  I mean I get to double my SGA, things should be smoking fast now right?  Kind of. The database would have been fine if only the 11i front end accessed it.  Unfortunately, we have a bolt-on mobile supply chain solution called Highjump, and it’s the software bane of my existence.  It pretty much constantly connects to the database via ODBC, in fact it connects and disconnects many times just to perform a simple action through the interface tables.  We have about 60 Highjump users in the company, it’s in use pretty much 24×7. The problem came in when the host CPU spiked after the memory upgrade.  Analyzing the nature of the CPU use, it wasn’t easy to explain, but it was clear that no conventional query was spinning the CPU.  There was just a mystery gap between DB time and CPU time:

Description Latest Collection Reference Collection
DB Time (seconds) 6626.19 5146.15
CPU Time (sec) 329.78 236.72
User I/O Wait Time (seconds) 2.88 29.98
Application Wait Time (seconds) 0.42 0.93
Cluster Wait Time (seconds) 0.00 0.00
Concurrency Wait Time (seconds) 6.11 2.88

So it turns out, the actual issue was connection creation. Doubling the SGA seems to have effectively made the creation of a dedicated server process connection about twice as expensive as it used to be. While this we were aware of this before, it wasn’t really problematic. At this point, however, it was killing our whole ERP.

Oh Behave!

This solution could be used to make any poorly written application (which connects to Oracle) behave, in addition to classic web server type examples.  So here’s a thumbnail sketch of the fix, and then I’ll go into detail.

  1. Start the Database Resident Connection Pool on the RDBMS
  2. Configure the DRCP on the RDBMS
  3. Install & Configure the client side
  4. Confirm it’s working as expected
  5. Since DRCP is now a requirement for operation, put a custom metric in EM to ensure the DRCP is running

Starting DRCP on the database

This part is really simple.  As sys just type EXECUTE DBMS_CONNECTION_POOL.START_POOL(); and you’re done.  My understanding is that once enabled (and not explicitly stopped) this pool will start with all subsequent database startups.  Trust but verify, right?  I mean Oracle has never lied to me before.

Within about 2-3 minutes of creating the pool the first time (with minimum-type parameters) the listener registered the service automagically.  If you have a problem getting it to show up, it seems you might try to register the local listener:

alter system set local_listener=’DEV’;
alter system register;
show parameter local_listener;

Anyways, type lsnrctl services SID:

lsnrctl services PROD

“N000” established:0 refused:0 current:0 max:40000 state:ready

CMON <machine: hostname.domainname.com, pid: 29590>

(ADDRESS=(PROTOCOL=tcp)(HOST=hostname.domainname.com)(PORT=33161))

You will see this n000 background process on the OS, as well:

ps -ef|grep n000

oraprod  11191     1  0 Jul27 ?        Ss     0:07 ora_n000_PROD

Incidentally, to stop the pool, you issue EXECUTE DBMS_CONNECTION_POOL.STOP_POOL();

Configuring the DRCP on the RDBMS

This was a lot of conjecture, and in my case I chose to error on the side of conservative, allocating too much up front and cutting it back later. Straight out of Oracle’s DBA Guide, the following parameters are configurable

MINSIZE The minimum number of pooled servers in the pool. The default value is 4.
MAXSIZE The maximum number of pooled servers in the pool. The default value is 40.
INCRSIZE The number of pooled servers by which the pool is incremented if servers are unavailable when a client application request is received. The default value is 3.
SESSION_CACHED_CURSORS The number of session cursors to cache in each pooled server session. The default value is 20.
INACTIVITY_TIMEOUT The maximum time, in seconds, the pooled server can stay idle in the pool. After this time, the server is terminated. The default value is 300.This parameter does not apply if the pool is at MINSIZE.
MAX_THINK_TIME The maximum time of inactivity, in seconds, for a client after it obtains a pooled server from the pool. After obtaining a pooled server from the pool, if the client application does not issue a database call for the time specified by MAX_THINK_TIME, the pooled server is freed and the client connection is terminated. The default value is 30.
MAX_USE_SESSION The number of times a pooled server can be taken and released to the pool. The default value is 5000.
MAX_LIFETIME_SESSION The time, in seconds, to live for a pooled server in the pool. The default value is 3600.
NUM_CBROK The number of Connection Brokers that are created to handle client requests. The default value is 1.Creating multiple Connection Broker processes helps distribute the load of client connection requests if there are a large number of client applications.
MAXCONN_CBROK The maximum number of connections that each Connection Broker can handle.The default value is 40000. But if the maximum connections allowed by the platform on which the database is installed is lesser than the default value, this value overrides the value set using MAXCONN_CBROK. Set the per-process file descriptor limit of the operating system sufficiently high so that it supports the number of connections specified by MAXCONN_CBROK.

All of these are your best guess, at least at first. To change a parameter, use: EXECUTE DBMS_CONNECTION_POOL.ALTER_PARAM (”,’MINSIZE’,’10’); and to revert to defaults use: EXECUTE DBMS_CONNECTION_POOL.RESTORE_DEFAULTS(); Parameters persist through pool shutdown.

Install and Configure the Client Side

DRCP was introduced in RDBMS 11.1.0.6.  Therefore, it shouldn’t be a big surprise that you need client 11.1.0.6 or better installed to take advantage of this feature.  Once installed, all you need do is adjust your tnsnames.ora file a bit:

Old

PROD =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = myhost.mydomain.com)(PORT = 1521))

(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = PROD) ) )

New

PROD =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = myhost.mydomain.com)(PORT = 1521))

(CONNECT_DATA = (SERVER = POOLED) (SERVICE_NAME = PROD) ) )

Yup, that’s it.  Simply change SERVER=DEDICATED|SHARED to SERVER=POOLED and you’re done.

Confirm it’s working as expected

Well this is actually the fun part.  There’s several ways to confirm your connection is in the new pooled connection.

First, log into the database from the 11.1.0.6+ client with the new tnsnames.ora file specifying server=pooled

1. Sit there and do nothing after logging in.  Assuming you didn’t change the parameter MAX_THINK_TIME, in 30 seconds (more or less if you changed it) you will be disconnected:

SQL*Plus: Release 11.2.0.1.0 Production on Thu Jul 28 14:56:34 2011

Copyright (c) 1982, 2009, Oracle.  All rights reserved. Enter

password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 – 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options

(wait 30 seconds)

SQL> select * from dual;

select * from dual * ERROR at line 1:

ORA-03113: end-of-file on communication channel Process

ID: 13184 Session ID: 801 Serial number: 54251

2. Check out the listener on the database before and after you make a connection

lsnrctl services PROD (look for N000 current connections)

Service “PROD” has 2 instance(s).

Instance “PROD”, status UNKNOWN, has 1 handler(s) for this service…

Handler(s): “DEDICATED” established:2 refused:0

LOCAL SERVER

Instance “PROD”, status READY, has 2 handler(s) for this service…

Handler(s): “N000” established:1 refused:0 current:1 max:40000 state:ready

CMON <machine: myhost.mydomain.com, pid: 6241>

(ADDRESS=(PROTOCOL=tcp)(HOST=myhost.mydomain.com)(PORT=44404))

“DEDICATED” established:5062 refused:0 state:ready

LOCAL SERVER

3. Log in, do one query, and wait for INACTIVITY_TIMEOUT seconds to kill you.  Assuming you didn’t change the parameter INACTIVITY_TIMEOUT, in 5 minutes (more or less if you changed it) you will be disconnected:

SQL*Plus: Release 11.2.0.1.0 Production on Thu Jul 28 15:03:54 2011

Copyright (c) 1982, 2009, Oracle.  All rights reserved. Enter

password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 – 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from dual 2  ;

D

X

(wait 5 minutes and try it again)

SQL> /

select * from dual * ERROR at line 1:

ORA-03113: end-of-file on communication channel

Process ID: 13184 Session ID: 742 Serial number: 36743

Use the data dictionary views to see what’s going on (straight from the Oracle doc)

DBA_CPOOL_INFO
Contains information about the connection pool such as the pool status, the maximum and minimum number of connections, and timeout for idle sessions.
V$CPOOL_STATS
Contains pool statistics such as the number of session requests, number of times a session that matches the request was found in the pool, and total wait time for a session request.
V$CPOOL_CC_STATS
Contains connection class level statistics for the pool.

Put a Custom Metric in Enterprise Manager

There’s really nothing exotic here, but I thought I’d show the screenshot of my setup

Checking for Active status on the connection pool

How DRCP Saved my bacon

As you can see by the command below, before 24 hours had passed, the DRCP had serviced just shy of 30,000 connections (7000+ per broker).

Before implementing DRCP, but with the memory added and the SGA at 48gb,  we were able to confirm that a dedicated server process connection took between 1 and 1.5 seconds each to establish with sqlplus.  After DRCP, connections take, effectively, no time.

Kentucky windage, admittedly, but it can’t be too far off: assume a savings of 1 second per connection (which is all CPU) and we’re at over 8 hours of CPU time saved from serving connections in under 24 hours.

lsnrctl services PROD

Service “PROD” has 2 instance(s).
Instance “PROD”, status UNKNOWN, has 1 handler(s) for this service…
Handler(s):
“DEDICATED” established:1 refused:0
LOCAL SERVER
Instance “PROD”, status READY, has 5 handler(s) for this service…
Handler(s):
“DEDICATED” established:132211 refused:0 state:ready
LOCAL SERVER
“N000” established:7539 refused:0 current:13 max:40000 state:ready
CMON <machine:myhost.mydomain.com, pid: 29590>
(ADDRESS=(PROTOCOL=tcp)(HOST=myhost.mydomain.com)(PORT=33161))
“N003” established:7250 refused:0 current:12 max:40000 state:ready
CMON <machine: myhost.mydomain.com, pid: 11203>
(ADDRESS=(PROTOCOL=tcp)(HOST=myhost.mydomain.com)(PORT=59497))
“N002” established:7241 refused:0 current:16 max:40000 state:ready
CMON <machine: myhost.mydomain.com, pid: 11197>
(ADDRESS=(PROTOCOL=tcp)(HOST=myhost.mydomain.com)(PORT=55356))
“N001” established:7266 refused:0 current:11 max:40000 state:ready
CMON <machine: myhost.mydomain.com, pid: 11191>
(ADDRESS=(PROTOCOL=tcp)(HOST=myhost.mydomain.com)(PORT=48000))

Advertisements

Blog at WordPress.com.