Oracle 11g DRCP: Database Resident Connection Pooling - second attempt

In my last post I wrote about unsuccessful attempt to check functionality of DRCP: Database Resident Connection Pooling. Now it's time for success story :) The problem with previous situation was, that Oracle was not fully functional. And that was because I manually set, that i have all proper versions of linux packages, what was not true.
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 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.194.111)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = DB11G)(SERVER=POOLED)
    )
  )

And tried to connect to pool

[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@db11gpool
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

This time successful login.
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:

begin
 DBMS_CONNECTION_POOL.ALTER_PARAM ('','MINSIZE','1');
 DBMS_CONNECTION_POOL.ALTER_PARAM ('','MAXSIZE','4');
end;
/

After 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:

• 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
  

So, we have to have at least 1 authentication server in our pool. We should be aware of this when we setup small number of MAXSIZE of 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ł