Database management changes to Oracle connection pool information

The Oracle database used in recent projects is built with multiple tablespaces on the server for use by different systems, while two systems are in use, a project being developed at the time of the test run, the connection pool is full, not connected to the problem, To this end, I checked how to modify the Oracle Connection pool configuration, the special record for reference.

Currently Oracle only supports one connection pool, pool name is "Sys_default_connection_pool", and the management of connection pooling information is also a package "Dbms_connection_pool".

First look at the package description:

sql> desc Dbms_connection_pool





Element Type





---------------- ---------





Alter_param PROCEDURE





Configure_pool PROCEDURE





restore_defaults PROCEDURE





Start_pool PROCEDURE





Stop_pool PROCEDURE





   

There are 5 stored procedures inside the package. The default Oracle is to include a default connection pool Sys_default_connection_pool, but is not open, need to display the open connection pool, the first step of course is to open the connection pool:

exec dbms_connection_pool.   Start_pool (' Sys_default_connection_pool ');

This operation only needs to be done once, the next time the database restarts, the connection pool will automatically open.

After you open the connection pool, you can query through the system view Dba_cpool_info:

sql> Select Connection_pool,status from Dba_cpool_info;





Connection_pool STATUS------------------------------------------------------------------------------------- -----------





Sys_default_connection_pool ACTIVE


   

When the connection pool is started, you can pass the Dbms_connection_pool. Configure_pool to view the associated configuration items for the connection pool.

sql> desc Dbms_connection_pool. Configure_pool





Parameter Type Mode Default?





---------------------- -------------- ---- --------





pool_name VARCHAR2 in Y





minsize Binary_integer in Y





MAXSIZE Binary_integer in Y





incrsize Binary_integer in Y





session_cached_cursors Binary_integer in Y





inactivity_timeout Binary_integer in Y





max_think_time Binary_integer in Y





max_use_session Binary_integer in Y





max_lifetime_session Binary_integer in Y





   

Parameter description:

Parameter description

MinSize the minimum number of pooled servers in the pool, the default is 4.
MAXSIZE the maximum number of pooled servers in the pool, the default is 40.
Incrsize This parameter is when a client application needs to be connected, and when pooled servers is unavailable, each pool increases the number of pooled servers.

Session_cached_cursors the number of session cursors cached on each pooled servers, the default is 20.
Inactivity_timeout pooled server is in the idle state for the maximum time in seconds, exceeding this time, the server will be stopped. The default is 300.

Max_think_time after a client obtains a pooled server from pool, if no database calls are committed within max_think_time time, the pooled server is freed and the client connection is stopped. The default is 30, per second.

The number of times Max_use_session pooled server can taken and release on pool, by default, is 5000.
Max_lifetime_session the time, in seconds, to live for a pooled server in the pool. Thedefault value is 3600. The life value of a pooled server in pool.

Note: The number of pooled servers cannot be lower than minsize.

You can use Dbms_connection_pool. Configure_pool or Dbms_connection_pool. Alter_param the settings of the connection pool.

First take a look at the parameter information:

sql> desc Dbms_connection_pool. Alter_param





Parameter Type Mode Default?





----------- -------- ---- --------





pool_name VARCHAR2 in Y





param_name VARCHAR2 in





Param_value VARCHAR2 in

sql> exec dbms_connection_pool. Alter_param (', ' minsize ', ' 10 ');





Pl/sql procedure successfully completed





sql> exec dbms_connection_pool. Alter_param (', ' maxsize ', ' 100 ');





Pl/sql procedure successfully completed





 

Because there is only one connection pool, the value of the first parameter can be omitted.

Several system views are more useful in the system:

Dba_cpool_info This view contains the state of the connection pool

V$cpool_stats This view contains statistics about the connection pool

V$cpool_cc_stats This view contains the pool's connection type level statistics

After the modification is successful, you can query the following connection pool information:

sql> Select Connection_pool, status,minsize,maxsize from Dba_cpool_info;





connection_pool STATUS minsize MAXSIZE





-------------------------------------------------------------------------------- ---------------- ---------- - ---------





Sys_default_connection_pool ACTIVE 10 100





   

To this end, the connection pool settings and related modifications have been completed.