Pooling not working as I expect

[aozkesek]

Hi,

Here is my development environment&tool;
* delphi pascal 7.0
* oracle 9i client
* odac 5.7 net
* windows xp

I wrote basic test program as you can see bottom.

What I'm doing with this test program is,
* create 20 thread
* each thread has own ora-session object
* each ora-session has same parameter with enabled pooling.
* within thread procedure,
** get new session and connect,
** sleep 3 sec,
** disconnect,
** sleep 1 sec,
** reconnect and disconnect.
* wait for finish first 20 thread.
* do all this with another new 20 thread

It takes 1 or 1,5 sec for every single first 20 session connect time is acceptable because of empty pool.

Unfortunately, it also takes same time for next 20 session connect time. I was not expect that the last 20 thread session connect time is as long as first ones.

I'm not sure that where I mistake or what I miss.

I suspected the ODAC, then I downloaded and tested same test program with new trial version of ODAC. Nothing changed, I got similar connect time result.

I also wrote exactly the similar test program with c# .Net from Microsoft and ODP.NET from Oracle. It took 0 -zero- or 1 msec to connect to db within last 20 thread as expected.

thanks for your time,

program tester;

{$APPTYPE CONSOLE}

uses
Windows,
SysUtils,
DBAccess,
Ora,
OraError,
OraCall,
OraClasses;

function ftNow: string;
begin
result := FormatDateTime('(hh:nn:ss.zzz) ', now);
end;

function oraSession(id: string): TOraSession;
begin
result := TOraSession.Create(nil);
result.Options.UseOCI7 := false;
result.PoolingOptions.MaxPoolSize := 20;
result.PoolingOptions.MinPoolSize := 10;
result.PoolingOptions.ConnectionLifetime := 20;
result.Username := 'blabla';
result.Password := 'blabla';
result.Server := 'blabla';
result.ConnectMode := cmNormal;
result.ThreadSafety := true;
result.Pooling := true;
writeln(id + ftNow + ' connecting...');
result.connect;
writeln(id + ftNow + ' connected.');
end;

function tProc(dHandle: Pointer): integer;
var
id: string;
os: TOraSession;
begin
id := Format('tProc_%d ', [integer(dHandle^)]);
os := oraSession(id);
Sleep(3000);
os.Disconnect;
Sleep(1000);
writeln(id + ftNow + ' connecting...');
os.connect;
writeln(id + ftNow + ' connected.');
os.Disconnect;
result := 0;
end;

var
tId: cardinal;
tHandle: array[1..20] of integer;
tData: array[1..20] of integer;
i: integer;
os: TOraSession;
begin

Writeln('main begin.');

os := oraSession('');
os.disconnect;

for i:=1 to 20 do
begin
tData := i;
tHandle := BeginThread(nil, 0, @tProc, @tData, 0, tId);
end;

WaitForMultipleObjects(20, @tHandle, true, 30000 );

for i:=1 to 20 do
begin
tData := i + 20;
tHandle := BeginThread(nil, 0, @tProc, @tData, 0, tId);
end;

WaitForMultipleObjects(20, @tHandle, true, 30000 );

end.

[AlexP]

Hello,

You have set a too small value for ConnectionLifetime (=20), this property is set in milliseconds. Therefore, in your case, connections are removed from the pool in 20 milliseconds and then recreated. To solve the problem, you should increase the value for this property

[aozkesek]

Hello,

I appreciate that your comment AlexP.

It worked fine now.

[AlexP]

Hello,

Glad to see that the problem was solved. If you have any other questions, feel free to contact us.