Oracle Database Discussions

Hi there,

I have a more general question about a specific Oracle behaviour.

I update a feature from within an application. The application doesn't respond and I finally have to terminate it. I checked Oracle whether a query is running long using the following statement:

select s.username,s.sid,s.serial#,s.last_call_et/60 mins_running,q.sql_text from v$session s
join v$sqltext_with_newlines q
on s.sql_address = q.address
where status='ACTIVE'
and type <>'BACKGROUND'
and last_call_et> 60
order by sid,serial#,q.piece


The result of the above query is:

WITH CONNECTION AS ( SELECT * FROM WW_CONN C WHERE (C.FID_FROM I
N (SELECT FID FROM WW_LINE WHERE FID_ATTR=:B1 ) AND C.F_CLASS_ID
FROM =22) OR (C.FIDTO IN (SELECT FID FROM WW_LINE WHERE FID_AT
TR=:B1 ) AND C.F_CLASS_ID_TO =22) ) SELECT MIN(P.FID_ATTR) AS FI
D_FROM FROM CONNECTION C, WW_POINT P WHERE (P.FID = C.FID_FROM A
ND C.F_CLASS_ID_FROM = 32 AND C.FLOW = 1) OR (P.FID = C.FID_TO A
ND C.F_CLASS_ID_TO = 32 AND C.FLOW = 2)

I have a different tool which shows me the binding parameter values. So I know that the value for :B1 is 5011 - the id of the feature being updated. This query runs for 20 mins and longer before it eventually stops. The update process involves multiple sql statements - so this one is not doing the update but is part of the process.

Here is the bit I do not understand: when I run the query in SQL Developer with value 5011 for :B1 it takes 0.5 secs to return a result.

Why is it, that the sql statement takes so long when issued by the application but takes less than a second when I run it manually?

I sent a dump of the data to the application vendor who is not able to reproduce the issue in their environment. Could someone explain to me what happens here or give me some keywords for further research?

We are using 11gR2, 64bit.

Many thanks,
Rob