Asp.net connection pool to the Oracle database (zt)

Keywords: ASP. NET, Oracle, database connection pool, performance bottleneck

Abstract: This article introduces the performance bottleneck caused by connection pool problems in connecting Oracle to Asp.net and the solution through a case I have experienced.

1. Problem Discovery

In the second half of 2006, I participated in the development of a project at the Qilu Petrochemical station in linshu, Shandong Province. Another project of the company, contract management system, is in the late stage of implementation. This project adopts the B/S architecture developed by. NET and uses oracle as the background database. The client finds that the response of the service system is slow when there are many online users. (most users report that they have been waiting after clicking the logon button on the logon interface, the system does not respond ). Check the server usage. The CPU and hard disk access are not busy, and the memory usage is not very high. No deadlocks are found when you connect to the database, but there are many sessions, about 110. It is suspected that the values of the database processes and sessions are low (the default values are 170 and 150 when not installed ). However, expansion of these two values still does not solve the problem. When I found this problem for the second time, I noticed that although the setting value is low, the two value limits were not reached. At the same time, I also noticed that the number of sessions connecting the system to the database is exactly 100, I think of the database connection pool.

2. Related Materials

After reading the help of vs.net 2003
MS-help: // Ms. vscc.2003/ms. msdnqtr.2003feb. 2052/cpguide/html/cpconconnectionpoolingforsqlservernetdataprovider.htm. The basic content is as follows:

Use the connection string keyword to control the connection pool

The connectionstring attribute of the sqlconnection object supports connection string key/value pairs, which can be used to adjust the logic of the connection pool.

The following table describes the connectionstring values that can be used to adjust the connection pool behavior.

Name	Default Value	Description
Connection lifetime	0	When the connection is returned to the pool, the creation time of the connection is compared with the current time. If the interval exceedsConnection lifetimeThe specified value (in seconds) destroys the connection. In clustering configuration, you can use it to force load balancing between the running server and the online server. If the value is zero (0), the pool connection has the maximum timeout period.
Connection Reset	'True'	Determine whether to reset the database connection when removing it from the pool. For Microsoft SQL Server 7.0, if it is setFalseIt will avoid an additional round-trip process when obtaining the connection, but it must be noted that the connection status (such as the database context) will not be reset.
Enlist	'True'	WhenTrueIf the transaction context exists, the pool manager automatically registers the connection in the current transaction context of the Creation thread.
Max pool size	100	The maximum number of connections allowed in the pool.
Min pool size	0	The minimum number of connections maintained in the pool.
Pooling	'True'	WhenTrueThe connection will be retrieved from the corresponding pool, or the connection will be created and added to the corresponding pool when necessary.

 

It can be seen that ASP. NET connects to the database, the database connection pool is enabled by default, and the maximum number of connections allowed in the pool is 100 by default. It can be basically determined that the connection pool is full when access to the system is blocked.

If all connections in the connection pool are occupied, the system will enable new connections outside the connection pool. This statement has doubts about the size of the problem.

3. Confirm the problem

To verify ASP. net connection to the Oracle database, the connection pool reaches the maximum number of connections, the new connection needs to wait for the connection in the connection pool to release resources, I wrote a test page, when loading the page, first, open 10 connections and wait 20 seconds before closing these connections. Then use the test machine to open two test threads to access the test page, and then open the test page in the browser to use another page with the same connection string, it is found that the latter can be connected to the database only after the page accessed by the test thread is processed.

4. Problem Solving

Find the crux of the problem. We added the Max pool size for the database connection string of our system and set it to 200 as needed. At the same time, the Oracle server's session and process (progresses) values are modified to meet the needs of session connection.

5 questions

As mentioned above, if all connections in the connection pool are occupied, a new connection will be created. However, when the default connection pool size is used and the maximum number of connections is set to 20, all connections in the connection pool are occupied. New connection requests do not enable new connection sessions, wait for the connection in the connection pool to be released.

However, when the maximum number of connections in the connection pool is set to 200, the system connections have once exceeded the upper limit of 200, reaching more than 210. Therefore, it can be determined that a new connection session is created out of the connection pool for a connection request that exceeds the limit, which complies with the information described in this document. If you have any questions about this, please explain your point of view.

6 supplement

After the connection string is modified, the original connection pool will be rebuilt. Under what circumstances can this feature be applied?

Trackback: http://tb.blog.csdn.net/TrackBack.aspx? Postid = 1358594