1、在数据库端确认版本:
SQL> select product,version from product_component_version where product like ‘%Database%’;
PRODUCT VERSION
Oracle Database 11g Enterprise Edition 11.2.0.4.0
2、查看现有连接池情况,Oracle 11g有一个默认的连接池对象,通过 dba_cpool_info 可以看到连接池信息:
SQL> col CONNECTION_POOL format a30
SQL> select CONNECTION_POOL,STATUS,MINSIZE,MAXSIZE from dba_cpool_info;
CONNECTION_POOL STATUS MINSIZE MAXSIZE
SYS_DEFAULT_CONNECTION_POOL INACTIVE 4 40
SQL>
默认连接池:sys_default_connection_pool,状态是inactive。
3、使用dbms_connection_pool包,启动连接池:
SQL> exec dbms_connection_pool.start_pool();
PL/SQL procedure successfully completed.
SQL>
4、查看连接池状态:
SQL> select CONNECTION_POOL,STATUS,MINSIZE,MAXSIZE from dba_cpool_info;
CONNECTION_POOL STATUS MINSIZE MAXSIZE
SYS_DEFAULT_CONNECTION_POOL ACTIVE 4 40
SQL>
查看进程情况:
[oracle@VM_0_4_centos ~]$ ps -ef|grep ora_n
oracle 14896 1 0 23:06 ? 00:00:00 ora_n000_xkdb
ora_n000_sid 就是Connection Broker进程,负责连接管理。
[oracle@VM_0_4_centos ~]$ ps -ef|grep ora_l
oracle 14898 1 0 23:06 ? 00:00:00 ora_l000_xkdb
oracle 14900 1 0 23:06 ? 00:00:00 ora_l001_xkdb
oracle 14902 1 0 23:06 ? 00:00:00 ora_l002_xkdb
oracle 14904 1 0 23:06 ? 00:00:00 ora_l003_xkdb
ora_l00x_sid 是连接池中的连接的Server Process,初始启动个数由MINSIZE参数决定
5、应用端tns配置:
xkdbdrcp =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = POOLED) –将常见的SERVER = DEDICATED修改为SERVER = POOLED,表明连接使用连接池;
(SERVICE_NAME = xkdb)
)
)
而后通过user/passwd@tns即可使用连接池连接数据库。
6、跟踪会话的连接情况:
select pname, spid, username, program from v
processwhereaddrin(selectpaddrfromv
p
r
o
c
e
s
s
w
h
e
r
e
a
d
d
r
i
n
(
s
e
l
e
c
t
p
a
d
d
r
f
r
o
m
v
session where sid = :1);
查看进程情况和使用连接池的进程名称
一旦连接数超过了minsize,就会继续创建新的连接池连接供使用。