Now I've installed Oracle Enterprise Linux v 5.0 and Oracle 11g over it, with some help of Tim instructions.
I've added entry in TNSNAMES.ORA on linux box:
DB11GPOOL =And tried to connect to pool
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.194.111)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = DB11G)(SERVER=POOLED)
)
)
[oracle@localhost admin]$ sqlplus pooltest/pool@db11gpool
SQL*Plus: Release 11.1.0.6.0 - Production on Wed Aug 29 01:44:53 2007
Copyright (c) 1982, 2007, Oracle. All rights reserved.
ERROR:
ORA-12520: TNS:listener could not find available handler for requested type of server
So I've started connection pooling (as SYS at Oracle):
exec DBMS_CONNECTION_POOL.START_POOL();And tried again:
[oracle@localhost admin]$ sqlplus pooltest/pool@db11gpoolThis time successful login.
SQL*Plus: Release 11.1.0.6.0 - Production on Wed Aug 29 02:02:37 2007
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select 1 from dual;
1
----------
1
Now I've left this session untouched for some time (more then 300 second - default time-out for pool)
SQL> select 1 from dual;
select 1 from dual
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 2821
Session ID: 125 Serial number: 21
As you can see, session was timed-out by pool, and disconnected.
Ok, lets change parameters for pool to observer behaviour, when max number of session is reached.
Execute it as SYS:
beginAfter I set it, I'was able to have 3 (not 4) simultaneous sessions using Connection pool. Whenever I start 4-th or 5-th session it is waiting for connection. You do not get any error, session just hung up. When one of active session is released (closed or timed-out) then one of waiting sessions is being connected. When I was looking for explanation, I found out one that in view V$CPOOL_STATS:
DBMS_CONNECTION_POOL.ALTER_PARAM ('','MINSIZE','1');
DBMS_CONNECTION_POOL.ALTER_PARAM ('','MAXSIZE','4');
end;
/
-
NUM_OPEN_SERVERS=4 - Total number of
busy and free servers in the pool (including authentication servers)
-
NUM_BUSY_SERVERS=3 - Total number of
busy servers in the pool (not including authentication servers)
-
NUM_AUTH_SERVERS=1 - Number of
authentication servers in the pool
When I first read about database connection pooling I was thinking that when you start using all connections in pool, than next attempt to connect will open new normal session. But it will not be good. Mechanism of queues is better, because client will not overload database server. Might be it's not good for clients, as they might wait long time. On the other hand in well written application sessions should be short and closed immediately when no more needed in request.
More tests of DRCP in next posts...
Cheers, Paweł
