The DBMS_CONNECTION_POOL package provides an interface to manage Database Resident Connection Pool.
This chapter contains the following topic:
Table 37-1 DBMS_CONNECTION_POOL Package Subprograms
| Subprogram | Description |
|---|---|
|
Alters a specific configuration parameter as a standalone unit and does not affect other parameters |
|
|
Configures the pool with advanced options |
|
|
Starts the pool for operations. It is only after this call that the pool could be used by connection clients for creating sessions |
|
|
Stops the pool and makes it unavailable for the registered connection clients |
|
|
Restores the pool to default settings |
This procedure alters a specific configuration parameter as a standalone unit and does not affect other parameters.
DBMS_CONNECTION_POOL.ALTER_PARAM ( pool_name IN VARCHAR2 DEFAULT 'SYS_DEFAULT_CONNECTION_POOL', param_name IN VARCHAR2, param_value IN VARCHAR2);
This procedure configures the pool with advanced options.
DBMS_CONNECTION_POOL.CONFIGURE_POOL ( pool_name IN VARCHAR2 DEFAULT 'SYS_DEFAULT_CONNECTION_POOL', minsize IN NUMBER DEFAULT 4, maxsize IN NUMBER DEFAULT 40, incrsize IN NUMBER DEFAULT 2, session_cached_cursors IN NUMBER DEFAULT 20, inactivity_timeout IN NUMBER DEFAULT 300, max_think_time IN NUMBER DEFAULT 120, max_use_session IN NUMBER DEFAULT 500000, max_lifetime_session IN NUMBER DEFAULT 86400, num_cbrok IN NUMBER DEFAULT 1, maxconn_cbrok IN NUMBER DEFAULT 40000);
Table 37-4 CONFIGURE_POOL Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Pool to be configured. Currently only the default pool name is supported. |
|
|
Minimum number of pooled servers in the pool |
|
|
Maximum allowed pooled servers in the pool |
|
|
Pool would increment by this number of pooled server when pooled server are unavailable at application request time |
|
|
Turn on |
|
|
|
|
|
Maximum time of inactivity by the client after getting a session from the pool. If the client does not issue a database call after grabbing a server from the pool, the client will be forced to relinquish control of the pooled server and will get an |
|
|
Maximum number of times a connection can be taken and released to the pool |
|
|
|
|
|
Number of brokers to be started at pool start |
|
|
Maximum number of connections for each broker |
All expressions of time are in seconds
All of the parameters should be set based on statistical request patterns.
minsize should be set keeping in mind that it puts a lower bound on server resource consumption. This is to prevent the timeout from dragging the pool too low, because of a brief period of inactivity.
maxsize should be set keeping in mind that it puts an upper bound on concurrency and response-times and also server resource consumption.
session_cached_cursors is typically set to the number of most frequently used statements. It occupies cursor resource on the server
In doubt, do not set the increment and inactivity_timeout. The pool will have reasonable defaults.
max_use_session and max_lifetime_session allow for software rejuvenation or defensive approaches to potential bugs, leaks, accumulations, and like problems, by getting brand new sessions once in a while.
The connection pool reserves 5% of the pooled servers for authentication, and at least one pooled server is always reserved for authentication. When setting the maxsize parameter, ensure that there are enough pooled servers for both authentication and connections.
This procedure starts the pool for operations. It is only after this call that the pool could be used by connection classes for creating sessions.
DBMS_CONNECTION_POOL.START_POOL ( pool_name IN VARCHAR2 DEFAULT 'SYS_DEFAULT_CONNECTION_POOL');
This procedure stops the pool and makes it unavailable for the registered connection classes.
DBMS_CONNECTION_POOL.STOP_POOL ( pool_name IN VARCHAR2 DEFAULT 'SYS_DEFAULT_CONNECTION_POOL');