Oracle ODP. NET Connection Pool

Database Connection Pool

A connection pool is a cache of database connections that request a database connection to a connection pool whenever the application needs to connect to the database, and the connection pool is responsible for the creation and destruction of the specific database connection. The database connection in the connection pool is cached for a period of time, and subsequent connection requests first use the database connection in the cache, and if there are idle connections in the cache, they are used directly and are created when there is no idle connection. This improves overall performance by increasing the additional resources that are consumed as a result of database connection creation.

. NET Connect Oracle

. NET connects the Oracle database with 3 main libraries:

1. . NET comes with the. NET Framework Data Provider for Oracle
2. Devart provided by Dotconnect for Oracle
3. Oracle Data Provider for. Net/odp.net provided by Oracle

Currently, the Xasset system uses the 3rd type, Odp.net provided by Oracle. Odp. NET provides a set of classes that implement the ADO-related interfaces, and provides interfaces that use some of the advanced features of the Oracle database, such as Rac,xml DB, BULK INSERT, and so on. Relative to. NET has also improved a lot of library performance.

Odp. NET is now part of Oracle ODAC (Oracle Data Access Component). The ODAC component needs to be installed before use, and Oracle provides an xcopy-and executable-mode for download and installation. See Xasset System Release Notes or official documentation for specific installation methods.

Odp. NET has the following types of database connection string formats:

1. Using TNS

This approach requires the configuration of TNS, which typically requires a user ID and password (the integrated authentication method can omit the user ID and password). The exact string format is as follows:

Data SOURCE=TORCL; User Id=myusername; Password=mypassword;

1. Odp.net way of not relying on Tnsnames.ora

This approach does not require TNS to be implemented in a way that configures the connection string in TNS directly into the data source node. The connection string provides the server address, port, instance, and other information directly, in the following format:

Data source= (description= (address_list= (address= (protocol=tcp) (Host=myhost))) (Port=myport (SERVER =dedicated) (SERVICE_NAME=MYORACLESID));

User Id=myusername; Password=mypassword;

1. How to use EZ Connect

The EZ Connect method does not require special configuration, but requires the Sqlnet.ora in the database service to enable the method, add names.directory_path= (TNSNames, ezconnect) enabled, the specific connection string format is as follows:

Data Source=username/[email Protected]//instancename;

Odp. NET connection pooling and related functions

Odp. NET provides the ability to connect pools. Of course we can choose to use or not to use, whether the Enable connection pooling feature is determined by the database connection string. The connection parameter provides pooling this parameter, if the value is true, the connection pooling is enabled, otherwise it is not enabled, and the default is to enable connection pooling when the parameter is not supplied.

Database connection parameters In addition to the standard parameters mentioned above data source,user Id,password, connection pooling parameters related to several main: Connection Lifetime, Connection Timeout, DECR pool Size, Incr pool size, Max pool size, Min pool size, Pooling, Validate Connection.

When connection pooling is enabled, the process of getting a database connection is roughly as follows:

If the connection pool does not exist (such as when the application first accesses the database), Odp.net creates the connection pool and initializes the database connection by the value set by the parameter "Min Pool Size" (default 1). This value specifies the minimum number of connections in the connection pool, regardless of whether the connection pool has at least one database connection with that value set. Until the connection pool is destroyed.

L If the connection pool exists, check for available connections.

U if there is a connection available, the connection is taken out of the pool and returned to the application.

N if "Validate Connection" is enabled, the connection pool will go to the database server to verify that the connection is valid.

U if there are no available connections, the connection pool checks whether the number of connections in the current pool has reached the specified maximum "Max Pool Size" (default 100).

n If the maximum limit has been reached, the request waits for a time of "Connection Timeout" (the default of 15 seconds) to check if there is a connection returned to the pool during this time period.

• If a connection is returned to the pool, the connection is returned directly to the requesting application.
• If there is still no connection available within the specified time, the "Connection Request timed out" exception is thrown.

n if the upper limit is not reached, the connection pool will actively create a connection to the value specified by the INCR pool Size (default 5) and return one of the applications to the request.

When the application obtains data from the database and calls Close ()/dispose (), the connection pool checks whether the lifetime of the connection exceeds the "Connection Lifetime" (default 0, not checked).

If the lifetime of the connection exceeds the defined value, the connection is actually closed. This shutdown refers to the physical connection of the application's connection pool to the database server shutting down.

The check does not occur until the connection is returned to the pool, if it is not checked in use.

Of course, if the connection is actually closed, the connection will not close when the number of connections in the pool is less than the value specified by Min pool Size.

Connection pooling closes unused connections in the pool every 3 minutes, as specified by the value set by "Decr pool Size" (default 1) for each shutdown.

Oracle ODP. NET Connection Pool