Connection pooling and Oracle.

24 Comments

• 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?
  

• Don Hi,
  
  
  
  Did you monitor Oracle v$session ?
  

• 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
  

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

• 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.
  

• 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
  

• 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
  

• 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
  

• 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
  

• 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)
  

• 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.
  

• 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.
  

• 
  
  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).
  
  
  
  

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

• Is it ok to set my pooling =NO
  

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

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

• 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.
  
  
  

• 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.
  

• Solution:
  1. DO NOT use system.data.oracleclient.oracleconnection, instead use oracle.dataaccess.client.oracleconnection.
  2. call ClearPool after Close
  

• 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???
  

• "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.
  

• Hi,
  
  Connection pooling intializing the database connection for invalid database credentials.
  
  Probm : I am reseting the user login password in the oracle database, but the connection pooling intializing the database connection for new password as well as old password.
  
  The problem with connection pooling because it just checking singature of requested connection string with existing connection string in the connection pooler and initializing database connection without validating the user credentials.
  
  pls tell me how to drop the existing connection object which has invalid credentials in connection pooler. this is creating problem to me.
  
  My connection string like this :
  
  datasrc=MFPROD.WORLD;enlist=true;lifetime=10;maxsize=100;minsize=1;incsize=5;decsize=1;timeout=15;dbapriv=;validcon=false;stmtcache=0;stmtcachepurge=false;metapool=true;gridrac=false;userid=TEST_COMPOWNER;passwd=MADHU5;
  

• stwierdziwszy oszustwo, stawał się straszny. unrest Rycerz ostrożnie odwinął zamotany na
  nadgarstku struna. Nie pragnął, aby potwór urwała mu rękę.
  W samą porę. Branie było na początku delikatn.