[20130730]11G的DRCP特性.txt

[20130730]11G的DRCP特性.txt

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.


--参考以上链接做一些测试：

SQL> @ver

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

--以sys用户启动。
SQL> execute dbms_connection_pool.start_pool;
PL/SQL procedure successfully completed.

$ ps -ef | grep -e ora_l0 -e ora_n0 | grep -v grep
503      26281     1  0 15:49 ?        00:00:00 ora_n000_test
503      27178     1  0 16:04 ?        00:00:00 ora_l000_test
503      27180     1  0 16:04 ?        00:00:00 ora_l001_test
503      27182     1  0 16:04 ?        00:00:00 ora_l002_test
503      27184     1  0 16:04 ?        00:00:00 ora_l003_test

ora_n000_XXX =>    Connection Broker Process
ora_l000_XXX => Pooled Server Process(Handles client requests in Database Resident Connection Pooling)

--使用ezconnect建立OK。
sqlplus scott/xxxx@192.168.100.XXX:1521/test.com:pooled

--也修改tnsnames.ora文件，加入如下：
testpool =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.XXX)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = test.com)
          (SERVER=pooled)
          ~~~~~~~~~~~~~~~
    )
  )

--按照文档介绍，If a session remains idle for longer than the inactivity_timeout it is killed to free up space in the connection pool.

SQL> exec print_table('select * from  dba_cpool_info');

CONNECTION_POOL               : SYS_DEFAULT_CONNECTION_POOL

STATUS                        : ACTIVE

MINSIZE                       : 4

MAXSIZE                       : 40

INCRSIZE                      : 2

SESSION_CACHED_CURSORS        : 20

INACTIVITY_TIMEOUT            : 300

MAX_THINK_TIME                : 120

MAX_USE_SESSION               : 500000

MAX_LIFETIME_SESSION          : 86400

NUM_CBROK                     : 1

MAXCONN_CBROK                 : 40000

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

PL/SQL procedure successfully completed.

--INACTIVITY_TIMEOUT=300秒，如果300秒就会退出。

--做一个事务，等待300秒看看：
SQL> create table t ( a number);
Table created.

SQL> insert into t values (1);
1 row created.

SQL> select sysdate from dual ;
select sysdate from dual
            *
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 27182
Session ID: 13 Serial number: 349

--重新登录，发现断开连接时使用的是rollback。
SQL> select * from t;
no rows selected


--可以修改参数，例子:
exec dbms_connection_pool.alter_param( POOL_NAME=>'SYS_DEFAULT_CONNECTION_POOL', PARAM_NAME=>'INACTIVITY_TIMEOUT', PARAM_VALUE=>'1000');

SQL> exec scott.print_table('select * from  dba_cpool_info');

CONNECTION_POOL               : SYS_DEFAULT_CONNECTION_POOL

STATUS                        : ACTIVE

MINSIZE                       : 4

MAXSIZE                       : 40

INCRSIZE                      : 2

SESSION_CACHED_CURSORS        : 20

INACTIVITY_TIMEOUT            : 1000

MAX_THINK_TIME                : 120

MAX_USE_SESSION               : 500000

MAX_LIFETIME_SESSION          : 86400

NUM_CBROK                     : 1

MAXCONN_CBROK                 : 40000

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

PL/SQL procedure successfully completed.


--停止drcp使用。
SQL> execute dbms_connection_pool.stop_pool;