-- Which Workflow processes are generating the most events?
SELECT
event_name,
event_key,
COUNT(*) AS event_count,
MAX(event_date) AS latest_event
FROM wf_events
WHERE event_date >= TRUNC(SYSDATE) + (18/24)
GROUP BY event_name, event_key
ORDER BY event_count DESC
FETCH FIRST 20 ROWS ONLY;
-- Check WF_EVENT listener queue backlog
SELECT
qt.queue_table,
q.name,
q.status,
DBMS_AQADM.queue_depth(q.name) AS queue_depth
FROM dba_queues q
JOIN dba_queue_tables qt ON q.queue_table = qt.queue_table
WHERE q.name IN ('WF_EVENT_T', 'WF_EVENT_Q');
-- Find expensive WF_EVENT queries
SELECT
sql_id,
parsing_schema_name,
executions,
elapsed_time / 1e6 AS elapsed_sec,
ROUND(elapsed_time / executions / 1e3, 2) AS ms_per_exec,
sql_text
FROM v$sqlarea
WHERE LOWER(sql_text) LIKE '%wf_event%'
AND executions > 0
ORDER BY elapsed_time DESC
FETCH FIRST 10 ROWS ONLY;
-- Check WF_EVENT listener latency
SELECT
event_filter_guid,
event_name,
COUNT(*) AS processed_count,
MAX(event_date) AS last_processed
FROM wf_event_subscriptions
WHERE event_date >= TRUNC(SYSDATE) + (18/24)
GROUP BY event_filter_guid, event_name
ORDER BY processed_count DESC;
-- Which Workflow processes are generating the most events?
SELECT
event_name,
event_key,
COUNT(*) AS event_count,
MAX(event_date) AS latest_event
FROM wf_events
WHERE event_date >= TRUNC(SYSDATE) + (18/24)
GROUP BY event_name, event_key
ORDER BY event_count DESC
FETCH FIRST 20 ROWS ONLY;
-- Check WF_EVENT listener queue backlog
SELECT
qt.queue_table,
q.name,
q.status,
DBMS_AQADM.queue_depth(q.name) AS queue_depth
FROM dba_queues q
JOIN dba_queue_tables qt ON q.queue_table = qt.queue_table
WHERE q.name IN ('WF_EVENT_T', 'WF_EVENT_Q');
-- Find expensive WF_EVENT queries
SELECT
sql_id,
parsing_schema_name,
executions,
elapsed_time / 1e6 AS elapsed_sec,
ROUND(elapsed_time / executions / 1e3, 2) AS ms_per_exec,
sql_text
FROM v$sqlarea
WHERE LOWER(sql_text) LIKE '%wf_event%'
AND executions > 0
ORDER BY elapsed_time DESC
FETCH FIRST 10 ROWS ONLY;
-- Check WF_EVENT listener latency
SELECT
event_filter_guid,
event_name,
COUNT(*) AS processed_count,
MAX(event_date) AS last_processed
FROM wf_event_subscriptions
WHERE event_date >= TRUNC(SYSDATE) + (18/24)
GROUP BY event_filter_guid, event_name
ORDER BY processed_count DESC;
-- Trace where setNavigationParama is being called FROM
SELECT
sql_id,
parsing_schema_name,
executions,
ROUND(elapsed_time / 1e6 / executions, 2) AS avg_sec_per_exec,
SUBSTR(sql_text, 1, 100) AS sql_snippet
FROM v$sqlarea
WHERE sql_id = '0bu1jgc94rg3fj'
OR LOWER(sql_text) LIKE '%setnavigationparam%';
-- Find what's IN THE CALL STACK for this SQL
SELECT
sql_id,
plan_hash_value,
depth,
operation,
options,
object_name,
object_type,
cardinality,
bytes
FROM v$sql_plan
WHERE sql_id = '0bu1jgc94rg3fj'
ORDER BY id;
-- Get the actual SQL text
SELECT
sql_fulltext
FROM v$sql
WHERE sql_id = '0bu1jgc94rg3fj'
FETCH FIRST 1 ROW ONLY;
-- Find what's calling setNavigationParams
SELECT
sql_id,
parsing_schema_name,
executions,
SUBSTR(sql_text, 1, 200) AS sql_caller
FROM v$sqlarea
WHERE LOWER(sql_text) LIKE '%setnavigationparam%'
OR (sql_id != '0bu1jgc94rg3fj'
AND LOWER(sql_text) LIKE '%wf_event%'
AND executions > 1000)
ORDER BY executions DESC
FETCH FIRST 20 ROWS ONLY;
-- Check concurrent request log for WF jobs
SELECT
request_id,
program_application_id,
concurrent_program_id,
phase_code,
status_code,
actual_start_date,
actual_completion_date,
argument_text
FROM fnd_concurrent_requests
WHERE program_application_id IN (
SELECT application_id FROM fnd_application WHERE application_short_name IN ('WF', 'FUN')
)
AND actual_start_date >= TRUNC(SYSDATE - 7)
AND status_code IN ('R', 'C', 'E') -- Running, Complete, Error
ORDER BY request_id DESC
FETCH FIRST 30 ROWS ONLY;
-- Check for stuck/errored requests
SELECT
request_id,
user_concurrent_program_name,
phase_code,
status_code,
actual_start_date,
ROUND((SYSDATE - actual_start_date) * 86400, 0) AS age_seconds,
logfile_node_name,
logfile_path,
logfile_filename
FROM fnd_concurrent_requests_vl
WHERE status_code IN ('R', 'E')
AND actual_start_date >= TRUNC(SYSDATE - 1)
ORDER BY actual_start_date DESC;
-- Find what's calling setNavigationParams
SELECT
sql_id,
parsing_schema_name,
executions,
SUBSTR(sql_text, 1, 200) AS sql_caller
FROM v$sqlarea
WHERE LOWER(sql_text) LIKE '%setnavigationparam%'
OR (sql_id != '0bu1jgc94rg3fj'
AND LOWER(sql_text) LIKE '%wf_event%'
AND executions > 1000)
ORDER BY executions DESC
FETCH FIRST 20 ROWS ONLY;
-- Check concurrent request log for WF jobs
SELECT
request_id,
program_application_id,
concurrent_program_id,
phase_code,
status_code,
actual_start_date,
actual_completion_date,
argument_text
FROM fnd_concurrent_requests
WHERE program_application_id IN (
SELECT application_id FROM fnd_application WHERE application_short_name IN ('WF', 'FUN')
)
AND actual_start_date >= TRUNC(SYSDATE - 7)
AND status_code IN ('R', 'C', 'E') -- Running, Complete, Error
ORDER BY request_id DESC
FETCH FIRST 30 ROWS ONLY;
-- Check for stuck/errored requests
SELECT
request_id,
user_concurrent_program_name,
phase_code,
status_code,
actual_start_date,
ROUND((SYSDATE - actual_start_date) * 86400, 0) AS age_seconds,
logfile_node_name,
logfile_path,
logfile_filename
FROM fnd_concurrent_requests_vl
WHERE status_code IN ('R', 'E')
AND actual_start_date >= TRUNC(SYSDATE - 1)
ORDER BY actual_start_date DESC;
SELECT
request_id,
user_concurrent_program_name,
concurrent_program_name,
program_application_id,
concurrent_program_id,
actual_start_date,
actual_completion_date,
ROUND((actual_completion_date - actual_start_date) * 86400, 0) AS duration_sec,
phase_code,
status_code
FROM fnd_concurrent_requests_vl
WHERE actual_start_date >= TRUNC(SYSDATE - 2)
AND (status_code IN ('R', 'E', 'I')
OR actual_start_date BETWEEN TO_DATE('2026-07-25', 'YYYY-MM-DD') AND TO_DATE('2026-07-28', 'YYYY-MM-DD'))
ORDER BY actual_start_date DESC
FETCH FIRST 50 ROWS ONLY;
SELECT
owner,
name,
type
FROM dba_source
WHERE owner = 'APPS'
AND (LOWER(text) LIKE '%WF_ITEM_ACTIVITY_STATUSES%'
OR LOWER(text) LIKE '%OJMSTEXT%')
GROUP BY owner, name, type
ORDER BY name;
-- Find sessions currently executing the loop
SELECT
sid,
serial#,
username,
program,
status,
sql_id,
ROUND((SYSDATE - logon_time) * 86400, 0) AS session_age_sec,
event
FROM v$session
WHERE username = 'APPS'
AND status = 'ACTIVE'
AND (sql_id IN ('5z0s7q1a976mp', '8qf0gsw8scbr', 'a4gzfqk1c25n',
'daphqlqsnymv8', '0bu1jgc94rg3fj')
OR ROUND((SYSDATE - logon_time) * 86400) > 3600);
SELECT
s.sid,
s.serial#,
s.sql_id,
s.prev_sql_id,
s.status,
s.username,
s.module,
s.action,
s.client_identifier,
s.client_info,
s.program,
s.machine,
s.process AS client_os_pid,
p.spid AS database_os_pid,
s.logon_time
FROM gv$session s
LEFT JOIN gv$process p
ON p.addr = s.paddr
AND p.inst_id = s.inst_id
WHERE s.sql_id IN (
'5z0s7q1a976mp',
'8qf0gsw8scbr',
'a4gzfqk1c25n',
'daphqlqsnymv8',
'0bu1jgc94rg3fj'
)
OR s.prev_sql_id IN (
'5z0s7q1a976mp',
'8qf0gsw8scbr',
'a4gzfqk1c25n',
'daphqlqsnymv8',
'0bu1jgc94rg3fj'
)
ORDER BY s.inst_id, s.sid;
SELECT
request_id,
user_concurrent_program_name,
concurrent_program_name,
actual_start_date,
actual_completion_date,
ROUND((actual_completion_date - actual_start_date) * 86400, 0) AS duration_sec,
phase_code,
status_code,
user_name
FROM fnd_concurrent_requests_vl
WHERE actual_start_date BETWEEN TO_DATE('2026-07-24', 'YYYY-MM-DD') AND TO_DATE('2026-07-28 16:47', 'YYYY-MM-DD HH24:MI')
ORDER BY actual_start_date DESC
FETCH FIRST 50 ROWS ONLY;
SELECT
request_id,
user_concurrent_program_name,
concurrent_program_name,
actual_start_date,
actual_completion_date,
ROUND((actual_completion_date - actual_start_date) * 86400, 0) AS duration_sec,
phase_code,
status_code,
user_name
FROM fnd_concurrent_requests_vl
WHERE actual_start_date BETWEEN TO_DATE('2026-07-24', 'YYYY-MM-DD') AND TO_DATE('2026-07-28 16:47', 'YYYY-MM-DD HH24:MI')
ORDER BY actual_start_date DESC
FETCH FIRST 50 ROWS ONLY;
-- Find which Workflow event subscription is triggered by WSHPSRS
SELECT
event_filter_guid,
event_name,
owner_name,
owner_tag,
function_name,
status
FROM wf_event_subscriptions
WHERE LOWER(event_name) LIKE '%pick%'
OR LOWER(event_name) LIKE '%ship%'
OR LOWER(function_name) LIKE '%navigation%'
ORDER BY event_name;
