Problems using TOracleSessionPool

[Primvs]

Hi!

I'm having major problem using TOracleSessionPool. I created Homogen. Session pool for Max. 25 Sessions.
It is being used in a service. Each time I get a request I acquire Session for this request using following code:

function TdmdData.aquireOraSession: TOracleSession;
begin
  Result := Nil;
  Try
    FCS.Acquire;
    Try
      Result  := TOracleSession.Create(Nil);
      Result.ThreadSafe := False;
      Result.AutoCommit := True;
      Result.Pool       := oraPool;
      Result.Pooling    := spInternal;
      Result.Logon;
    Except
      On E:Exception Do Begin
        logEvent(EventSourceApp,'aquireOraSession: '+E.Message,EVENTLOG_ERROR_TYPE,1,0);
        Raise;
      End;
    End;
  Finally
    FCS.Release;
  End;

(FCS is Critical section)

The problem is, that when I get a time consuming request (OK, basically any request, it's just that I noticed it with time consuming request), new requests get stuck in "Result.Logon;" line, until the first request is completed.
Can you help me?

Primvs

 

[Marco Kalter]

What exactly does a "request" do? I'm curious how it could could possibly block other logon operations.

 

[Primvs]

Originally posted by Marco Kalter:
What exactly does a "request" do? I'm curious how it could could possibly block other logon operations.

Hey!

I'm writting a CMS service. It has only one command (Request). It gets an XML parameter which contains a command (getSearchResults/saveStatistics/...) and parameters for executing that command.

Request:
- first acquires session - using function I've sent before
- parses XML parameter to extract a command and command parameters
- calls a function for that command
This functions are working with database. None contains anything out of ordinary- usually select querries, which in some cases are very time consuming (like for example search query). Now I do use DBMS function for searching in CLOB in this one, but things also get stuck for other "larger" commands that don't use DBMS.
Basic procedure in these functions:
get command parameters
build SQL querry
set variables (if query has any)
Open DataSet
Create Result XML from querry results

- releases sesssion.

OK, now I've been debugging stuff a bit. And the thing is that while other incomming commands were stuck in LogOn the command that "caused" this effect was in Open statement for time consuming querries. That is

odsContent.Open;

where odsContent is TOracleDataSet.

The Oracle Database version I'm working on is 8.1.7.

Regards,
Primoz Bartolj
SRC.SI

 

[Primvs]

Forgot to add something:

Session I'm working on in command functions is the one acquired with

function TdmdData.aquireOraSession

and released with

procedure TdmdData.releaseOraSession(ASession: TOracleSession);

It is stored in same object that contains these command functions (

TWWW_CMS = class(TAutoObject, IWWW_CMS)

).

 

[Marco Kalter]

I will check out the demo project you sent me.

 

[Primvs]

Hi. I added following line in my project initialization:

OracleCI.UseSingleEnvironment:= False;

And things are now running normally. Is this the correct procedure for using DOA in multithreaded applications?

Greetings, Primoz

 

[Marco Kalter]

It is an optimization in 4.0 that should have been transparent, but apparently can cause some stability issues. It will be removed in 4.0.6.