ORA-01023 error

[hsteigstra]

Following a previous post, I now get an error captured with Oracle Monitor:

ORA-01023: Cursor context not found (Invalid cursor number)

It occurs when I run several client applications simultaneously requesting the same query in an Apache module. It occurs after several hundreds successful requests. The SQL is complex, but correct and presents always the same data. All three clients freeze then with a time-out, no other error message on the client side.
My thoughts are that it has to do with multi-threading aspects.
Any idea what causes this problem?
The SQL statement is copied below this message.

Kind regards, Herman

SELECT
decodelng_id,0,scm_name0,scm_name1) Scheme,
y.srv_id, c.ctr_id, s.scm_id, ctr_name, c.ptp_id, srv_nexternsample nExtern,
srv_startdate StartDate, srv_closingdate CloseDate, scm_mca mca,
lay_id, scm_UseInternalSample Intern, ctr_creatdate, scm_shortname,
decode(e.prs_id,null,0,1) HasSurvey_Status,
decode(q.scm_id,null,0,1) HasSample_Question,
decode(srv_onhold,1,1,decode(svs_validated,0,1,0)) UnValidated,
AnyResultBool(c.scm_id, c.srv_id, c.ptp_id, c.ctr_id) HasResults,
c.prs_id, ctr_validated, PersonName(c.prs_id) Person_validated,
decode(u.prs_id_rp, p.prs_id,1,0) MayValidate,
decode(y.skz_id,null,0,1) SurveyValidated,
decode(dep_id,null,0,1) HasDeepen,
decode(pe.prs_id,null, ps.prs_email, pe.prs_email) Email,
decode(pe.prs_id,null, PersonName(sd.prs_id), PersonName(e.prs_id)) SampleSender,
s.sct_id, svs_evaluation, svs_deepen, srv_onhold, srv_close_deepen
FROM ClusterDef c, Survey y, Subscription u, Location L, Person p, Scheme s,
Survey_status e, Sample_question q, Deepen_survey d,
Scheme_description sd, Person ps, Person pe
WHERE
c.ptp_id=tp_id AND
p.prs_id=rs_id AND
y.scm_id=c.scm_id AND
y.srv_id=c.srv_id AND
L.ptp_id=c.ptp_id AND
u.loc_id=L.loc_id AND
u.scm_id=c.scm_id AND
sbs_startdatey.srv_startdate) AND
scm_valid = 1 AND
s.scm_id=c.scm_id AND
e.scm_id(+)=y.scm_id AND
e.srv_id(+)=y.srv_id AND
q.scm_id(+)=y.scm_id AND
q.srv_id(+)=y.srv_id AND
d.scm_id(+)=y.scm_id AND
d.srv_id(+)=y.srv_id AND
sd.scm_id(+)=s.scm_id AND
ps.prs_id(+)=sd.prs_id AND
pe.prs_id(+)=e.prs_id AND
(srv_closingdate>sysdate-(365*2+10) OR
y.srv_id=(SELECT max(srv_id) FROM Survey WHERE scm_id=u.scm_id AND srv_closingdatesysdate-28 AND
NOT EXISTS (SELECT 1 FROM Survey WHERE scm_id=x.scm_id AND srv_id

 

[Marco Kalter]

You mentioned in your previous post that you are using session pooling. Does it help if you disable pooling?

 

[hsteigstra]

Just turned off Pooling (spNone), same result.
Oracle Monitor not started, just the freezing of Apache.

 

[hsteigstra]

Some additional information:
Tried to turn off TheadSafe but that does not help.
Using Oracle Monitor the last crash displayed dramatically increase of responses for executions and logoffs: 7.5 or 15 sec. instead of apx. 0.1 sec

 

[Marco Kalter]

I'm not sure what could cause this. I assume that with session pooling turned off, all session, query, and dataset instances are restricted to a thread, and therefore all threads run in complete isolation. Could you confirm this?

 

[hsteigstra]

Yes, that is my opinion too. I just was not sure what function the "ThreadSafe" parameter has. I presume I can leave it to false, as the threading is done by Apache itself and each instance of the ApacheModule will get its own thread. There is no parallel action within the the SOAP module.

Today I met another error, I have had it before, but did not make a note:
ORA-03106: fatal two-task communication protocol error
Same testing procedure. Explanations of this error point to network or SQL*net or incompatibility problems.

 

[Marco Kalter]

The ThreadSafe property is only necessary when multiple queries will be performed by multiple threads on the same session. These queries will then be serialized. Like you say, this will not be necessary here.

The two-task error could indeed indicate a compatibility issue. Which Oracle Client version and Oracle Server version are you using?

 

[hsteigstra]

I am not on location, so I can only tell that we use Oracle 10g. I presume client and server are installed from the same CD.

Question about the thread safe parameter: what if I set it and it would not be necessary as all clients have their own thread. Does it cause overhead?

 

[Marco Kalter]

There will be some overhead, since for each database access function it has to enter and leave a TCriticalSection, or the Oracle Call Interface will have to do something similar internally.

It will most likely not be too significant though.