Python Connector for Oracle Connection pooling - Python Connector for Oracle

Connection pooling

The connector supports connection pooling, which improves performance and scalability by reusing existing database connections. Instead of repeatedly opening and closing connections, the connector retrieves a connection from a pre-established pool, reducing the time and resources required to establish new connections. This is especially beneficial in scenarios that involve frequent connect/disconnect operations.

When you call the close() method on a connection object, the connection remains alive and is returned to the pool. When a new connection is created using the connect() method, the module retrieves a connection from the pool—unless the pooler has already detected the connection as severed and marked it as invalid. If the pool is empty or lacks a valid connection, a new one is established.

Enable connection pooling

To enable connection pooling, set the value of the connection_pool.enabled module attribute to True.

The connection_pool.enabled attribute is global. If pooling is enabled, all new connections are pooled. Pooling can be disabled for a particular connection using the DisablePooling connection string parameter.

my_connection = devart.oracle.connect("Direct=True;Host=your_server;ServiceName=your_service_name;UserName=your_username;Password=your_password",DisablePooling=True)

Configure connection pooling

You can configure connection pool behavior using the following attributes:

• connection_pool.min_size
• connection_pool.max_size
• connection_pool.lifetime
• connection_pool.validate

For more information about the attributes, see the connection pool class.

The following example sets the attributes for the default connection pool, which implicitly has ID 0.

devart.oracle.connection_pool.min_size = 0
devart.oracle.connection_pool.max_size = 1000
devart.oracle.connection_pool.lifetime = 60000
devart.oracle.connection_pool.validate = True
devart.oracle.connection_pool.enabled = True

Multiple connection pools

You can define multiple connection pools with different settings. To define settings for a connection pool with a particular ID, use the syntax connection_pool[pool_id: int], where pool_id is the ID of the pool. You can also pass the PoolId connection string parameter to specify which connection pool needs to be used for a particular connection.

devart.oracle.connection_pool[42].max_size = 100
devart.oracle.connection_pool[42].lifetime = 120000
devart.oracle.connection_pool.enabled = True
my_connection = devart.oracle.connect("Direct=True;Host=your_server;ServiceName=your_service_name;UserName=your_username;Password=your_password",PoolId=42)

Important: Connections are placed in the same pool when all parameters in the connection string are identical. If the parameters differ, connections are placed in separate pools—even if they share the same pool ID. The connector creates a new pool when a connection uses an existing pool ID but has different connection parameters.

Connection validation

Database connections in a pool are validated every 30 seconds to ensure that a broken connection isn't returned from the pool when a connection object is constructed. Invalid connections are destroyed. The connection pooler also validates connections when they are added or released back into the pool (for example, when you call the connection.close() method).

If you set the validate attribute to True, connections are also validated when they are drawn from the pool. In the event of a network issue, all connections to the database may become broken. Therefore, if a fatal error is detected in one connection from the pool, the pooler validates all connections in the pool.

Idle timeout

The pooler removes a connection from the pool after it's been idle for approximately 4 minutes. If no new connections are added to the pool during this time, it becomes empty to save the resources. If you set the min_size attribute to a non-zero value, the pool doesn't destroy all idle connections and become empty unless the remaining connections are marked as invalid.

Maximum pool size

The max_size pool attribute limits the number of connections that can be stored in a pool at the same time. When the maximum number of connections in a pool is reached, all future database connections are destroyed once the connection object releases them.

Connection lifetime

You can limit the connection lifetime using the lifetime attribute. When a connection object is closed, and a database connection is returned to the pool, the creation time of the connection is compared with the current time. If this timespan exceeds the lifetime value, the connection is destroyed. This technique serves for load balancing.