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ł
