[20160513]重温11g DRCP.txt

[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的特性.