[20130730]11G的DRCP特性.txt

[20130730]11G的DRCP特性.txt



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;