Natty Gur

# re: Connection pooling and Oracle.

Shouldn't oracle terminate the connection once it reaches the timeout? If the connection isn't getting killed then there's got to be something going on to keep it open, perhaps a pulse or a lifeline activity. Which is entirely possible. Just curious, did you by any chance try tracing oracle to see what was happening with those connections?

# re: Connection pooling and Oracle.

Natty,

This doesn't seem to be the case using the System.Data.OracleClient namespace included in 1.1. There is a known bug in the 1.0 release, and there is a hotfix to correct that. But I use the 1.1 version, and haven't run into any pooling problems.

Here's the link to the 1.0 KB article: http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q330126

DonXML Demsak

# re: Connection pooling and Oracle.

Don Hi,

Did you monitor Oracle v$session ?

# re: Connection pooling and Oracle.

Yep. Check out the KB article. We were experiencing the exact same problem that you described before migrating to .Net 1.1. Once we did that, no more orphaned connections. We could have just applied the hotfix, but it was a good excuse to move to 1.1.

Don

# re: Connection pooling and Oracle.

We are already working with 1.1 but nothing helps. by the way our DAL objects works under DLLHOST boundary ...

# re: Connection pooling and Oracle.

I also have the same problem working with 1.1 with the system.data.OracleClient namespace. Just downloaded the Oracle provided namespace and will be testing shortly.

# re: Connection pooling and Oracle.

Hi
i am using ODP .NET but still i face the same problem ie., some conenctions are left orphan. They stay in inactive mode oracle db..
I am using ODP .NET runtime version (v1.0.3705)
and version (9.2.0.401)
Is it a bug in it? Please suggest.
Thanks.
-Imayakumar

# re: Connection pooling and Oracle.

Hi
i am using ODP .NET but still i face the same problem ie., some conenctions are left orphan. They stay in inactive mode oracle db..
I am using ODP .NET runtime version (v1.0.3705)
and version (9.2.0.401)
Is it a bug in it? Please suggest.
Thanks.
-Imayakumar

# re: Connection pooling and Oracle.

Hi
i am using ODP .NET but still i face the same problem ie., some conenctions are left orphan. They stay in inactive mode oracle db..
I am using ODP .NET runtime version (v1.0.3705)
and version (9.2.0.401)
Is it a bug in it? Please suggest.
Thanks.
-Imayakumar

# Aparently SOLVED!

I have used the string above, and i think it is ok now! No more unnecessary pooled connections.

"Data Source=TNS_LISTENER;User=usuario;Password=senha;Enlist=no;Persist Security Info=no;Pooling=yes;Connection Lifetime=20;Max Pool Size=20;Min Pool Size=5"

If you don't wanna pooling connection, set pooling=0 and bypass the pool size values...

By the way, pooling is a great feature to optimize connections with remote servers. It cuts down repeated handshaking between webserver and dataserver.

Any greats, suggestions or corretions, please mail me to wanderson(at)bhtec(dot)com(dot)br.

Thanks Natty!
Wans

# re: Connection pooling and Oracle.

Can anyone please confirm the solution for this problem.We have been exepriencing same issue.Using Net 1.1 /Oracle client 9.0/ODP.Net and once in a day we get connection time out and everything crables from then on ..untill box recycles asp.net process (w3...exe)

# re: Connection pooling and Oracle.

I am experiencing the same problem as Mike. The connections don't seem to be released from the pool whether I call dispose(), close(), or both.

I set up a simple test where the max pool size is 5, open up six browsers that each try to connect to the db. The first 5 connect fine and then the sixth waits. Even if I disconnect one (or more) or the connected ones during this time, it still doesn't connect.

I don't think that close() or dispise() are actually closing the connection.

# re: Connection pooling and Oracle.

<A href="http://weblogs.asp.net/ngur/archive/2004/05/09/128765.aspx">see my post :</A>

# re: Connection pooling and Oracle.

I have an application system which access Oracle DB. This application system has 2 parts: an online application and a client-server application.

Both accessing and interacting with the same Oracle DB.

Now, alot of times, the online application either got "hanged" or kept loading (as it is accessing the DB to pull out data or doing some validations) and / or showed a DB error page.

My engineer went to check the problem and reported it is due to connection pool size. Used to be 200 and now he set to 500.

However, my end-user felt that it couldnt be as the number of internal users (about 100) are the same, how could there be a run-out of connections? Even if the online application is catered for external web users, she could not believe that there are 200 connections been made at the same time, causing the application to "hang" or failed.

Can anyone advise? It's rather urgent.

# re: Connection pooling and Oracle.

I've got many replies to that post. So I decide to try making it clearer.

If you see that Oracle holds Connections of your application with invalid status check:



1) That you are using Oracle ODP.NET provider (version 102 or higher) and not Microsoft provider for oracle. This bug solved just in ODP.NET.

2) Ensure that you close all your open connections by closing or disposing OracleConnection.

3) Pay attention to those two connection string options: "Decr pool size" and "Incr pool size". "Decr pool size" set the Number of connections that are closed when an excessive amount of established connections are unused. "Decr pool size" default value is 1. ". "Incr pool size" set the Number of connections established when all connections in pool are used. "Incr pool size" defult value is 5. As you can see those default values can cause the pool to reach it maximum connection pool size default value (Default - 100).

# re: Connection pooling and Oracle.

So, what is a suggestion for numbers that we need to specify in "Decr pool size" and "Incr pool size" ?

# re: Connection pooling and Oracle.

Is it ok to set my pooling =NO

# re: Connection pooling and Oracle.

What's the default value fo "min pool size"?

# re: Connection pooling and Oracle.

look up clearpool() and clearallpools - not to sure if supported in earleir versions of ODP

# re: Connection pooling and Oracle.

Found this a while ago:

To solve this problem you must set in connection string some parameters:

OracleConnection con = new OracleConnection();

con.ConnectionString = "User Id=scott;Password=tiger;Data Source=oracle;" +

        "Min Pool Size=10;Connection Lifetime=120;Connection Timeout=60;" +

        "Incr Pool Size=5; Decr Pool Size=2;Max Pool Size=30;" +

        "Validate Connection = true";

con.Open();

In this example it sets up a pool of 10 connections to start with. Once

those 10 connections are in use it will increase the pool by 5 more

connections. The connection pool will only increase until the max pool

size is reached, which in this case is 30 connections. Once that is reached

then the connection timeout will be used to tell the application how long

to wait for a new connection to become available before it terminates

the request. The connection lifetime is only used to tell the application

how long to wait while trying to obtain a new connection from the connection

pool. If the connections are not in use the connection pool will be

decremented by 2 every 3 minutes. Since the validate connection is set to true

it will also test out the connection before it places it back into the available

connection pool.

# re: Connection pooling and Oracle.

Problem resolution :

We also had the same probem and we were using .net 2.0 and .net provider for oracle.

we solved this by explicitly closing the connection ,command and the reader ( this is important...even if you set the command behaviour to closeconnection ..reader still keeps the conn open with oracle somehow)

Even though we were using the Using block ,oracle was still  keeping the connections open .

Also try to change the max number of connections in the pool to something like 300 the default is 100..as suggested in previous post..but we could not use incr /decr   pool size as it was errored out saying icr pool size is invalid keyword or something

so our code looked something like :

using (OracleConnection cn = new OracleConnection(this.ConnectionString))

           {

               string sql = "something here ";

               OracleCommand cmd = new OracleCommand(sql, cn);

               cmd.CommandType = CommandType.Text;

               cmd.Parameters.Add(":id", OracleType.Number).Value = Id;

               cn.Open();

               IDataReader reader = ExecuteReader(cmd);

               if (reader.Read())

                     ''get data fromm reader data ;

               else

                     ''do else part

                 reader.Dispose();

                 cmd.Dispose();

                 cn.Dispose();

                 return returnObj;

           }

# re: Connection pooling and Oracle.

using the afforesaid connection string I am getting the following error.

System.ArgumentException: Keyword not supported: 'incr pool size'

Can you please help? I have to use the pooling and destroy the inactive connections as well.

# re: Connection pooling and Oracle.

Solution:

1. DO NOT use system.data.oracleclient.oracleconnection, instead use oracle.dataaccess.client.oracleconnection.

2. call ClearPool after Close

# re: Connection pooling and Oracle.

Question:

We are using Oracle 11+ version with ODP.net for connection pooling. The impersonate option is not working. It is always returning the first login.

Eg: If we login first time with vUserId then next login with xUserID, still it is returning the vUserID.  Any thoughts???

# re: Connection pooling and Oracle.

"The connection lifetime is only used to tell the application

how long to wait while trying to obtain a new connection from the connection pool"

That statement is incorrect. Connection lifetime simply refers to the amount of time that the connection is in use from the time the application requests it to the time the call finishes.  If the call takes longer than the connection lifetime value, the connection will be destroyed when it is returned to the pool, BUT ONLY if the number of connections in the pool is equal to or greater than the minimum pool size.

Also - the statement by Franz about clearing the pool after close is crazy and will impact performance massively as you'd be re-establishing ALL connections in the pool after every call which would perform worse than not using pooling at all!

Raise the decr pool size number to something like 3 or even 5 if you have a high minimum number of connections.

The pool will be decremented every 3 minutes.