BUG: ConnectionLifetime property ? (Does not work ?....)

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
goethals.f
Posts: 60
Joined: Mon 18 May 2009 13:17

BUG: ConnectionLifetime property ? (Does not work ?....)

Post by goethals.f » Tue 10 Aug 2010 11:48

Hello,

I have a question about using the pooling facilities from Oracle Dotconnect, when using in Direct==False mode (using OCI call's)


We should like to have the following situation:

A number of different C# DLL's are created, and within each DLL's there is the needed logic to connect to the Oracle DB and fetch the info. Each DLL's covers a different Business and connects to DB using different userID etc...
These DLL's could be seen as a library of functionalities towards the DB itself

When developing a specific application, we alwasy have an application specific connection to the DB + should use a number of those library DLL's which do the neede stuff towards the DB.

for creating those connections in the library DLL's we have the following options

*) use pooling=False: But performance could be problem, if repetitive access to same DLL is done in the application (open - close - open ...)

*) use pooling=True
--> I tried setting "ConnectionLifetime=10" (as property in OracleConnectionStringBuilder, and toString() for getting full ConnectionString)
--> But when diagnosing via DbMonitor, I see that when connection.close(), it is returned to the pool, but the real "Disconnect" in DBMonitor happens only after around 5 MINUTES (also session on DB side is only removed after those minutes)
--> I also tried the value 420 (7 minutes), but again after 5 minutes the connection was removed from the pool !


Could it be that the 'ConnectionLifetime' property is not taken into account, or do I need some more properties to solve my problem ? (all other of OracleConnectionStringBuilder are left default)


Does anyone have some ideas, and/or example where dotConnect pooling is used and a lifetime of 1 minute or so ?

Thx
Fred


PS: I also tried using the OCI-pooling parameters, but then diagnose is only possible on DB-side

PS2: I try to connect to Oracle 10.2.0.4, with Oracle10 client, and Dotconnect version 5.70.152.0


Edited: added "Dotconnect version 5.70.152.0"

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Wed 11 Aug 2010 13:00

Here is a description of the Connection Lifetime parameter of connection string from our documentation:
When a connection is returned to the pool, its creation time is compared with the current time, and the connection is destroyed if that time span (in seconds) exceeds the value specified by Connection Lifetime. The default value is 0.

Please check the settings of your Oracle Client and Oracle Server: http://www.devnewsgroups.net/adonet/t41 ... -time.aspx. Probably, the "5 minutes" timeout is set there.

goethals.f
Posts: 60
Joined: Mon 18 May 2009 13:17

Post by goethals.f » Wed 11 Aug 2010 13:44

Hello,


Just to be sure about how it should work according to the devart docs. When returning to the pool, do you really compare against the time of creation (the time of first .open())

or

do you compare against the last time the connection is used (by a SQL-command etc...)



*) When using 'Pooling=True' (and all other default).

When closing the connection it returns to the pool --> it should be really closed directly, because time between create and close is always >0 and default-Lifetime==0 --> so the connection should be distroyed according the docs ?

Or does Lifetime==0 means, no destroying at all ?


*) when a connection sits in the pool, does dotconnect checks on a regular base is the lifetime is exceeded, or does it only happen when connection is returned to the pool and afterwards no more ?

So in this case, the connection will only be destroyed if lifetime is exceeded at moment of close, in all other cases it will be destroyed based on the server (default) settings. Am I correct ?

Reading some info from oracle: it looks like the pooling-services has a clean-up default interval of 3 minutes. (http://download.oracle.com/docs/cd/B193 ... ecting.htm)


If I now really understand everything correct, the concept of keep the oracle connection X-minutes open after the LAST usage (or the real time of .Close() called, independant of the original creation time is not really possible if lifetime-interval are calculated on the real creation-time.
Maybe simple not possible with Oracle (independant of usage of Dotconnect or not)


For now I will use the current situation - and the (server-default) pooling of 5 minutes. Which is fine for me, the only intention was to possible shorten this time (not to extend the interval)


Thx
Fred

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Fri 13 Aug 2010 15:02

1. When a connection is returned to the pool, its creation time (the time of first .open()) is compared with the current time.

2. The default value of Connection Lifetime is 0 (connection always returns to pool).

3. Yes, you are right. Connection Lifetime is checked only when connection is returned to the pool.
Reading some info from oracle: it looks like the pooling-services has a clean-up default interval of 3 minutes. (http://download.oracle.com/docs/cd/B193 ... ecting.htm)
You are referencing here to ODP.NET provider pooling description.

dotConnect for Oracle also has internal check of connections which reside in pool. But interval of these checks can not be changed via APIs of dotConnect for Oracle.

Post Reply