Search Oracle Blogs

List of Blogs maintained by Paweł Barut.

Tuesday, September 18, 2007

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
and Disconnect
1000 times
226 sec 154 sec 31%
Insert one row,
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ł


Anonymous said...

DRCP is equivalent to traditional client-side pooling in terms of connection state management and does *not* initialize state (Pl/Sql package variables, CONTEXT etc). DRCP only closes open cursors and commits open transactions at the time of "connection release".

What you observed might have been the effect of an optimization. When the total number of connections to the pool (Connection Broker) is less than the pool's max size, connections are not actually "released" i.e made free, though the client has released it. They are still tied to the same client. So, every new client (connection) request would get a fresh connection for itself.

Real pooling (sharing of connections) starts after the pool's max size is reached, when more client connections to the Broker are vying for fewer connections in the pool.

You could check statistics for sharing in v$cpool_stats.

BTW, the Connection Broker is the nxxx process. There is a small percentage (around 5%) of pooled servers(Lxxx processes) that are reserved for authentication. The L003 that you observe might be that. They do not have sessions.

"The views expressed on this blog are my own and do not necessarily reflect the views of Oracle"

Paweł Barut said...

Thanks for information and pointing my errors.
Probably I put my thesis too early.
For my test I use only sqlplus with script. Might be my observations are because I use sqlplus, not directly OCI. I've to check it deeper than.

Anonymous said...

Since SQLPlus currently cannot specify the PURITY of a session, it always gets a NEW session. More information can be found here:

"The views expressed on this blog are my own and do not necessarily reflect the views of Oracle"

Paweł Barut said...

I did some corrections. Removed text is stroked out, new text is underlined.


Copyright © Paweł Barut
Printing from DOS to USB Printer