Skip Headers
Oracle® Database Administrator's Guide
11g Release 2 (11.2)

Part Number E17120-11
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

Configuring Database Resident Connection Pooling

The database server is preconfigured to allow database resident connection pooling. However, you must explicitly enable this feature by starting the connection pool.

This section contains the following topics:

Enabling Database Resident Connection Pooling

Oracle Database includes a default connection pool called SYS_DEFAULT_CONNECTION_POOL. By default, this pool is created, but not started. To enable database resident connection pooling, you must explicitly start the connection pool.

To enable database resident connection pooling:

  1. Start the database resident connection pool, as described in "Starting the Database Resident Connection Pool".

  2. Route the client connection requests to the connection pool, as described in "Routing Client Connection Requests to the Connection Pool".

Starting the Database Resident Connection Pool

To start the connection pool, use the following steps:

  1. Start SQL*Plus and connect to the database as the SYS user.

  2. Issue the following command:

    SQL> EXECUTE DBMS_CONNECTION_POOL.START_POOL();
    

Once started, the connection pool remains in this state until it is explicitly stopped. The connection pool is automatically restarted when the database instance is restarted if the pool was active at the time of instance shutdown.

In an Oracle Real Application Clusters (Oracle RAC) environment, you can use any instance to manage the connection pool. Any changes you make to the pool configuration are applicable on all Oracle RAC instances.

Routing Client Connection Requests to the Connection Pool

In the client application, the connect string must specify the connect type as POOLED.

The following example shows an easy connect string that enables clients to connect to a database resident connection pool:

examplehost.company.com:1521/books.company.com:POOLED

The following example shows a TNS connect descriptor that enables clients to connect to a database resident connection pool:

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp) (HOST=myhost)
       (PORT=1521))(CONNECT_DATA=(SERVICE_NAME=sales)
       (SERVER=POOLED)))

Disabling Database Resident Connection Pooling

To disable database resident connection pooling, you must explicitly stop the connection pool. Use the following steps:

  1. Start SQL*Plus and connect to the database as the SYS user.

  2. Issue the following command:

    SQL> EXECUTE DBMS_CONNECTION_POOL.STOP_POOL();
    

    See Also:

    Oracle Database PL/SQL Packages and Types Reference for more information on the DBMS_CONNECTION_POOL package.

Note:

The operation of disabling the database resident connection pool can be completed only when all client requests that have been handed off to a server are completed.

Configuring the Connection Pool for Database Resident Connection Pooling

The connection pool is configured using default parameter values. You can use the procedures in the DBMS_CONNECTION_POOL package to configure the connection pool according to your usage. In an Oracle Real Application Clusters (Oracle RAC) environment, the configuration parameters are applicable to each Oracle RAC instance.

Table 5-2 lists the parameters that you can configure for the connection pool.

Table 5-2 Configuration Parameters for Database Resident Connection Pooling

Parameter Name Description

MINSIZE

The minimum number of pooled servers in the pool. The default value is 4.

MAXSIZE

The maximum number of pooled servers in the pool. The default value is 40.

The connection pool reserves 5% of the pooled servers for authentication, and at least one pooled server is always reserved for authentication. When setting this parameter, ensure that there are enough pooled servers for both authentication and connections.

INCRSIZE

The number of pooled servers by which the pool is incremented if servers are unavailable when a client application request is received. The default value is 2.

SESSION_CACHED_CURSORS

The number of session cursors to cache in each pooled server session. The default value is 20.

INACTIVITY_TIMEOUT

The maximum time, in seconds, the pooled server can stay idle in the pool. After this time, the server is terminated. The default value is 300.

This parameter does not apply if the pool is at MINSIZE.

MAX_THINK_TIME

The maximum time of inactivity, in seconds, for a client after it obtains a pooled server from the pool. After obtaining a pooled server from the pool, if the client application does not issue a database call for the time specified by MAX_THINK_TIME, the pooled server is freed and the client connection is terminated. The default value is 120.

MAX_USE_SESSION

The number of times a pooled server can be taken and released to the pool. The default value is 500000.

MAX_LIFETIME_SESSION

The time, in seconds, to live for a pooled server in the pool. The default value is 86400.

NUM_CBROK

The number of Connection Brokers that are created to handle client requests. The default value is 1.

Creating multiple Connection Broker processes helps distribute the load of client connection requests if there are a large number of client applications.

MAXCONN_CBROK

The maximum number of connections that each Connection Broker can handle.

The default value is 40000. But if the maximum connections allowed by the platform on which the database is installed is lesser than the default value, this value overrides the value set using MAXCONN_CBROK.

Set the per-process file descriptor limit of the operating system sufficiently high so that it supports the number of connections specified by MAXCONN_CBROK.


Using the CONFIGURE_POOL Procedure

The CONFIGURE_POOL procedure of the DBMS_CONNECTION_POOL package enables you to configure the connection pool with advanced options. This procedure is usually used when you must modify all the parameters of the connection pool.

Using the ALTER_PARAM Procedure

The ALTER_PARAM procedure of the DBMS_CONNECTION_POOL package enables you to alter a specific configuration parameter without affecting other parameters.For example, the following command changes the minimum number of pooled servers used:

SQL> EXECUTE DBMS_CONNECTION_POOL.ALTER_PARAM ('','MINSIZE','10');

The following example, changes the maximum number of connections that each connection broker can handle to 50000.

SQL> EXECUTE DBMS_CONNECTION_POOL.ALTER_PARAM ('','MAXCONN_CBROK','50000');

Before you execute this command, ensure that the maximum number of connections allowed by the platform on which your database is installed is not less than the value you set for MAXCONN_CBROK.

For example, in Linux, the following entry in the /etc/security/limits.conf file indicates that the maximum number of connections allowed for the user test_user is 30000.

test_user HARD NOFILE 30000

To set the maximum number of connections that each connection broker can allow to 50000, first change the value in the limits.conf file to a value not less than 50000.

Restoring the Connection Pool Default Settings

If you have made changes to the connection pool parameters, but you want to revert to the default pool settings, use the RESTORE_DEFAULT procedure of the DBMS_CONNECTION_POOL package. The command to restore the connection pool to its default settings is:

SQL> EXECUTE DBMS_CONNECTION_POOL.RESTORE_DEFAULTS();

See Also:

Oracle Database PL/SQL Packages and Types Reference for more information on the DBMS_CONNECTION_POOL package.

Data Dictionary Views for Database Resident Connection Pooling

Table 5-3 lists the data dictionary views that provide information about database resident connection pooling. Use these views to obtain information about your connection pool and to monitor the performance of database resident connection pooling.

Table 5-3 Data Dictionary Views for Database Resident Connection Pooling

View Description

DBA_CPOOL_INFO

Contains information about the connection pool such as the pool status, the maximum and minimum number of connections, and timeout for idle sessions.

V$CPOOL_CONN_INFO

Contains information about each connection to the connection broker.

V$CPOOL_STATS

Contains pool statistics such as the number of session requests, number of times a session that matches the request was found in the pool, and total wait time for a session request.

V$CPOOL_CC_STATS

Contains connection class level statistics for the pool.


See Also:

Oracle Database Reference for more information about these views.