Hi Developers,
I am a .Net developer of a Large Online Retailling Company. I would like to have your help on a Connection Pool issue.
Recently we have developed a Web Application on ODP .Net to work with Oracle Database 10g. To gain the performance, connection pool enable is a must. Everything work fine throughout the development period and the performance is Great, however while the Web Application is deployed to the production, sooner or later (a few minutes or a few days) all the Connections in the connection pool will be unavailable and the ODP .NET keeps throwing:
"The Exception: Oracle.DataAccess.Client.OracleException Connection request timed out at ...
Oracle.DataAccess.Client.OracleConnection.Open()
..."
It seems to me that all the Connections in the Connection Pool are busy, as a result once the "Connection timeout" period is over, the OracleConnection.Open() throws this Exception.
However, when I look into the sessions information at the Database, all the Sessions (Max Pool Size=40, for instance) are in Wait Events - SQL*Net message from client for a long long time.
I can just find a reason for this: In the Web Application, every concurrent incoming requests is handled by a separating thread concurrently. For some pages, it may hit a long query in the database. For some reason, those pages will be Timeout and IIS 6.0 will call "Thread.Abort()" to stop the corresponding thread. I can catch Exception "System.Threading.ThreadAbortException: Thread was being aborted. at Oracle.DataAccess.Client.OpsSql.ExecuteReader( ... " at the DataAccess Layer.
To reproduce the problem in a simplier program, I have created a long-running query aginst the all_objects table in DB and started 20 threads for it. At the middle of each Thread Executing, I issue the Thread.Abort(). After all threads are aborted and all OracleDataReader, OracleCommand, OracleParameters and OracleConnection are disposed in the finally block, I tried to run the query but it will fail to obtain a connection from the connection pool.
If I set "pooling = false", there will be no problem.
The testing program can be downloaded at:
http://ki-gallery.dyndns.org/download/ThreadAbortTest.zip
in which there are a .Net solution, a plsql script "pkg_test_thread_abort.plsql" for the stored procedure being called against the "all_objects" table and a screenshot "Database_Sessions_Status.jpg" about the idle sessions in the database. Please try to use Anti-Virus program to scan the zip package
I thank you for your help !
Regards,
Alex
I am a .Net developer of a Large Online Retailling Company. I would like to have your help on a Connection Pool issue.
Recently we have developed a Web Application on ODP .Net to work with Oracle Database 10g. To gain the performance, connection pool enable is a must. Everything work fine throughout the development period and the performance is Great, however while the Web Application is deployed to the production, sooner or later (a few minutes or a few days) all the Connections in the connection pool will be unavailable and the ODP .NET keeps throwing:
"The Exception: Oracle.DataAccess.Client.OracleException Connection request timed out at ...
Oracle.DataAccess.Client.OracleConnection.Open()
..."
It seems to me that all the Connections in the Connection Pool are busy, as a result once the "Connection timeout" period is over, the OracleConnection.Open() throws this Exception.
However, when I look into the sessions information at the Database, all the Sessions (Max Pool Size=40, for instance) are in Wait Events - SQL*Net message from client for a long long time.
I can just find a reason for this: In the Web Application, every concurrent incoming requests is handled by a separating thread concurrently. For some pages, it may hit a long query in the database. For some reason, those pages will be Timeout and IIS 6.0 will call "Thread.Abort()" to stop the corresponding thread. I can catch Exception "System.Threading.ThreadAbortException: Thread was being aborted. at Oracle.DataAccess.Client.OpsSql.ExecuteReader( ... " at the DataAccess Layer.
To reproduce the problem in a simplier program, I have created a long-running query aginst the all_objects table in DB and started 20 threads for it. At the middle of each Thread Executing, I issue the Thread.Abort(). After all threads are aborted and all OracleDataReader, OracleCommand, OracleParameters and OracleConnection are disposed in the finally block, I tried to run the query but it will fail to obtain a connection from the connection pool.
If I set "pooling = false", there will be no problem.
The testing program can be downloaded at:
http://ki-gallery.dyndns.org/download/ThreadAbortTest.zip
in which there are a .Net solution, a plsql script "pkg_test_thread_abort.plsql" for the stored procedure being called against the "all_objects" table and a screenshot "Database_Sessions_Status.jpg" about the idle sessions in the database. Please try to use Anti-Virus program to scan the zip package
I thank you for your help !
Regards,
Alex