Oracle 11g DRCP: Functionality Test

This post is continuation of my series of post about Database Resident Connection Pooling.
Let start with session and processes. My test shows that Pooled Servers are shown in v$session view only when there is user connected, that means session is busy. For inactive servers only process in v$process view is reported. To identify this processes we can run query like that:

select * from v$process
where program like '%(L0%)';

If our Connection pooling is started, at least one process will be reported. This is so called Connection Broker process that is authenticating connections and assigning them to another server for processing. According to my tests Connection Broker this process usually have highest number in brackets (ex. L003 if you set up max number of processes to 4). To see busy servers run this query:

select * from v$session
where program like '%(L0%)';

Note that Connection Broker is never reported in v$session view.

In next test I've tested if global variables in packages are stored between connections. My test confirms that packages are always initialized after new connection is taken from pool. That's very good, and is what I was expecting. This is advantage over traditional connection pooling at client side like in Java, where environment is totally shared between consecutive use of the same connection. (This is true when only process if reused, when PURITY=NEW)

My next test was concerning usage of context. I've created sample CONTEXT, initialized it in session and reconnected. I've ensured that session was assigned to the same process (pooled session) and read context. My test also confirmed that context is reinitialized between connections. In traditional connection pooling it will not happen. Also when using PHP and persistent connection you have to take care of clearing global variables and context after acquiring session from pool (or before giving it back to pool). Database Resident Connection Pooling will make programmers life easier, at least on that field. (This is true when only process if reused, when PURITY=NEW)

My last test for today was performance test. Test was run on rather poor desktop machine with single processor.

Testcase	Normal Connection	Pooled Connection	%Improvement
Connect and Disconnect 1000 times	226 sec	154 sec	31%
Connect, Insert one row, Disconnect 1000 times	309 sec	178 sec	42%

This results are very promising. First we see 30% improvement of time to connect to DB. What is more important we can see 40% less time on repeating the same simple statement after establishing each connection. The only explanation is that Pooled connection can reuse already parsed statement. That is good news if we think about scalability.

You may be also interested in Oracle Official White-paper on DRCP.

At the end I would like to thank Christopher Jones as he was involved in DRCP development. I've mailed with him few times last year about problems with PHP-Oracle connectivity and lack of connection pooling.

Cheers, Paweł