Workflow problem 01.

Problem - A custom oracle workflow which use to take 15 min to complete; started giving ‘Page not found’ while end-user try to expedite it.

Resolution:-

It took me long time to investigate as at system level every thing was fine and other workflow were completing successfully with in time. Looking at a recent code change in the workflow, I even got old code deployed and retested but problem remain the same.

Having exhausted all the option I thought of putting a trace on oracle session which were running oracle workflow code.

I found out SID of workflow using OEM and fed it to below pl/sql…

-----------------------
DECLARE

ln_high_val NUMBER;

ln_max_val NUMBER;

ln_value NUMBER;

ln_sid NUMBER;

ln_serial NUMBER;

lv_sqltxt VARCHAR2(2000);

BEGIN

SELECT vs.SID INTO ln_sid
FROM fnd_concurrent_requests fcr, v$session vs
WHERE fcr.request_id = &Request_id
AND vs.process = fcr.os_process_id
AND fcr.oracle_session_id = vs.audsid;

dbms_output.put_line (ln_sid);

for i in 1..10000 loop

dbms_lock.sleep(2);


SELECT a.VALUE,q.sql_text,s.serial#
INTO ln_value,lv_sqltxt,ln_serial
FROM v$sesstat a, v$statname b, v$session s, v$sqlarea q
WHERE a.statistic# = b.statistic#
AND s.SID = a.SID
AND b.NAME = 'opened cursors current'
AND s.SID = ln_sid
AND s.username = 'APPS'
AND q.hash_value = s.sql_hash_value
AND s.sql_address = q.address;

dbms_output.put_line (ln_value);

INSERT INTO xxrpa.open_cursor_session
VALUES (ln_value, ln_sid,ln_serial,lv_sqltxt);


SELECT MAX (a.VALUE), p.VALUE
INTO ln_high_val, ln_max_val
FROM v$sesstat a, v$statname b, v$parameter p
WHERE a.statistic# = b.statistic#
AND b.NAME = 'opened cursors current'
AND p.NAME = 'open_cursors'
GROUP BY p.VALUE;

INSERT INTO xxrpa.open_cursor
VALUES (ln_high_val, ln_max_val);

COMMIT ;

END LOOP;
END;
/
---------------------------------

To my surprise workflow was running infinitely even after page not found at work flow status monitor, which revels that workflow status monitor was just showing a time-out.

Afterwards a deep analysis of system data told us that ; somebody have changed the flag status in a table and it was the cause wfl was fetching all the parties rather then one. ..