[20160513]重温11g DRCP.txt
--以前做过一次测试,再也没有使用过.
[20130730]11G的DRCP特性.txt => http://blog.itpub.net/267265/viewspace-767493/
http://www.oracle-base.com/articles/11g/DatabaseResidentConnectionPool_11gR1.php
Database Resident Connection Pool (DRCP) in Oracle Database 11g Release 1
The database resident connection pool (DRCP) reduces the resource requirements of applications that currently don't
support connection pooling, either because it is not supported by the application infrastructure, or it has not been
implemented. The pool is managed using the DBMS_CONNECTION_POOL package. Although the package appears to support
multiple connection pools, the document states that it currently only supports the default pool name
(SYS_DEFAULT_CONNECTION_POOL).
The DRCP is started and stopped using the START_POOL and STOP_POOL procedures respectively.
DRCP对应的应用需求是"短会话、高并发"的应用场景。所以DRCP服务的连接必然是短时间交互。Inactivity_timeout参数就是设置这个
timeout值。如果会话连接到这个连接之后，超过一定时间没有inactive交互，Oracle会自动将其断开。Server Process被释放回连接池。
配置connection pool，我们可以使用dbms_connection_pool方法configure_pool。
也可以使用dbms_connection_pool的方法alter_param.
-- 感觉我们现在的一些应用使用php连接数据库,应该使用这种模式.而且我们会话就是读取数据,不存在dml语句,感觉比较合适.
1.重复测试环境:
SYS@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SYS@book> select * from dba_cpool_info;
CONNECTION_POOL STATUS MINSIZE MAXSIZE INCRSIZE SESSION_CACHED_CURSORS INACTIVITY_TIMEOUT MAX_THINK_TIME MAX_USE_SESSION MAX_LIFETIME_SESSION NUM_CBROK MAXCONN_CBROK
--------------------------- -------- ------- ---------- ---------- ---------------------- ------------------ -------------- --------------- -------------------- ---------- -------------
SYS_DEFAULT_CONNECTION_POOL INACTIVE 4 40 2 20 300 120 500000 86400 1 40000
--当前没有激活!访问这个视图dba_cpool_info本质是查询sys.cpool$.
SYS@book> exec dbms_connection_pool.start_pool;
PL/SQL procedure successfully completed.
$ ps -ef | grep ora_[nl]0
oracle 21436 1 0 09:06 ? 00:00:00 ora_n000_book
oracle 21516 1 0 09:20 ? 00:00:00 ora_l000_book
oracle 21518 1 0 09:20 ? 00:00:00 ora_l001_book
oracle 21520 1 0 09:20 ? 00:00:00 ora_l002_book
oracle 21522 1 0 09:20 ? 00:00:00 ora_l003_book
--可以发现同时启动了一些进程.
ora_n000_XXX => Connection Broker Process
ora_l000_XXX => Pooled Server Process(Handles client requests in Database Resident Connection Pooling)
2.测试:
--启动2个会话,使用ezconnect模式:
$ rlsql scott/book@192.168.100.78:1521/book:pooled
SQL*Plus: Release 11.2.0.4.0 Production on Fri May 13 09:23:30 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SCOTT@192.168.100.78:1521/book:pooled> @ &r/spid
SID SERIAL# SPID PID P_SERIAL# C50
---------- ---------- ------ ------- ---------- --------------------------------------------------
123 429 21518 35 199 alter system kill session '123,429' immediate;
$ rlsql scott/book@192.168.100.78:1521/book:pooled
SQL*Plus: Release 11.2.0.4.0 Production on Fri May 13 09:24:17 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SCOTT@192.168.100.78:1521/book:pooled> @ &r/spid
SID SERIAL# SPID PID P_SERIAL# C50
---------- ---------- ------ ------- ---------- --------------------------------------------------
112 2511 21516 34 132 alter system kill session '112,2511' immediate;
--再启动多个会话:
]$ ps -ef | grep ora_[ln]0
oracle 21436 1 0 09:06 ? 00:00:00 ora_n000_book
oracle 21516 1 0 09:20 ? 00:00:00 ora_l000_book
oracle 21518 1 0 09:20 ? 00:00:00 ora_l001_book
oracle 21520 1 0 09:20 ? 00:00:00 ora_l002_book
oracle 21522 1 0 09:20 ? 00:00:00 ora_l003_book
oracle 21608 1 0 09:25 ? 00:00:00 ora_l004_book
oracle 21610 1 0 09:25 ? 00:00:00 ora_l005_book
oracle 21612 1 0 09:25 ? 00:00:00 ora_l006_book
--可以发现ora_l00?的进程数量再增加.退出后等一会:
--实际上如果等INACTIVITY_TIMEOUT=300秒,没有操作.自动退出.
SYS@book> exec dbms_connection_pool.configure_pool(minsize => 1,maxsize =>3,incrsize => 1,inactivity_timeout =>60);
PL/SQL procedure successfully completed.
SYS@book> select * from dba_cpool_info;
CONNECTION_POOL STATUS MINSIZE MAXSIZE INCRSIZE SESSION_CACHED_CURSORS INACTIVITY_TIMEOUT MAX_THINK_TIME MAX_USE_SESSION MAX_LIFETIME_SESSION NUM_CBROK MAXCONN_CBROK
------------------------------ ---------------- ---------- ---------- ---------- ---------------------- ------------------ -------------- --------------- -------------------- ---------- -------------
SYS_DEFAULT_CONNECTION_POOL ACTIVE 1 3 1 20 60 120 500000 86400 1 40000
--这样开启3个会话,第3个会话会挂起.除非其中1个会话退出,第3个会话才能进入.
--使用如下命令还原:
SYS@book> exec dbms_connection_pool.RESTORE_DEFAULTS;
PL/SQL procedure successfully completed.
SYS@book> select * from dba_cpool_info;
CONNECTION_POOL STATUS MINSIZE MAXSIZE INCRSIZE SESSION_CACHED_CURSORS INACTIVITY_TIMEOUT MAX_THINK_TIME MAX_USE_SESSION MAX_LIFETIME_SESSION NUM_CBROK MAXCONN_CBROK
------------------------------ ---------------- ---------- ---------- ---------- ---------------------- ------------------ -------------- --------------- -------------------- ---------- -------------
SYS_DEFAULT_CONNECTION_POOL ACTIVE 4 40 2 20 300 120 500000 86400 1 40000
--生产系统应该根据自己的需要调整这些参数,例子:
exec dbms_connection_pool.configure_pool(minsize => 10,maxsize =>80,incrsize => 4);
--停止DRCP:
SYS@book> exec dbms_connection_pool.stop_pool;
PL/SQL procedure successfully completed.
$ ps -ef | grep ora_[ln]0
oracle 21436 1 0 09:06 ? 00:00:00 ora_n000_book
--ora_n000还存在,等一会消失.我的测试等3分钟上下.
--注意client端也要11g才行!否则无法使用drcp的特性.