ITPub博客

首页 > Linux操作系统 > Linux操作系统 > [20130730]11G的DRCP特性.txt

[20130730]11G的DRCP特性.txt

原创 Linux操作系统 作者:lfree 时间:2013-07-30 16:53:24 0 删除 编辑
[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;

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-767493/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2455
  • 访问量
    6258612