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. ..
No comments:
Post a Comment