Friday, July 31, 2026
Wednesday, July 29, 2026
workdlow
-- 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;
SELECT TRUNC(creation_date, 'YYYY') AS creation_year,
file_content_type,
COUNT(*) AS row_count
FROM fnd_lobs
WHERE program_name IS NULL
AND expiration_date IS NULL
GROUP BY TRUNC(creation_date, 'YYYY'),
file_content_type
ORDER BY creation_year DESC, row_count DESC;
Monday, July 27, 2026
arc
1. Check archive generation by hour
SET LINES 200
SET PAGES 100
SELECT TO_CHAR(first_time,'DD-MON-YYYY HH24') AS archive_hour,
COUNT(*) AS archive_count,
ROUND(SUM(blocks * block_size) / 1024 / 1024 / 1024, 2) AS size_gb
FROM v$archived_log
WHERE first_time >= SYSDATE - 3
AND dest_id = 1
GROUP BY TO_CHAR(first_time,'DD-MON-YYYY HH24')
ORDER BY archive_hour;
2. Check daily archive generation
SELECT TO_CHAR(first_time,'DD-MON-YYYY') AS archive_date,
COUNT(*) AS log_count,
ROUND(SUM(blocks * block_size) / 1024 / 1024 / 1024, 2) AS total_gb
FROM v$archived_log
WHERE first_time >= SYSDATE - 7
AND dest_id = 1
GROUP BY TO_CHAR(first_time,'DD-MON-YYYY')
ORDER BY MIN(first_time);
3. Find recently growing objects in APPS_TS_TX_IDX
SET LINES 220
SET PAGES 100
COLUMN owner FORMAT A20
COLUMN segment_name FORMAT A45
COLUMN partition_name FORMAT A30
COLUMN segment_type FORMAT A20
SELECT *
FROM (
SELECT owner,
segment_name,
partition_name,
segment_type,
ROUND(bytes / 1024 / 1024 / 1024, 2) AS size_gb
FROM dba_segments
WHERE tablespace_name = 'APPS_TS_TX_IDX'
ORDER BY bytes DESC
)
WHERE ROWNUM <= 30;
4. Check recent extent allocation
This helps identify objects that received new extents recently, provided auditing or historical views are available:
SELECT owner,
segment_name,
segment_type,
partition_name,
COUNT(*) AS extent_count,
ROUND(SUM(bytes) / 1024 / 1024 / 1024, 2) AS allocated_gb
FROM dba_extents
WHERE tablespace_name = 'APPS_TS_TX_IDX'
GROUP BY owner,
segment_name,
segment_type,
partition_name
ORDER BY allocated_gb DESC
FETCH FIRST 30 ROWS ONLY;
5. Check active sessions generating redo
Run the first query, wait 10–15 minutes, and run it again. Sessions with the largest increase are likely generating the redo.
SELECT s.sid,
s.serial#,
s.username,
s.program,
s.module,
s.action,
s.sql_id,
ROUND(st.value / 1024 / 1024, 2) AS redo_mb
FROM v$session s
JOIN v$sesstat st
ON st.sid = s.sid
JOIN v$statname sn
ON sn.statistic# = st.statistic#
WHERE sn.name = 'redo size'
AND s.username IS NOT NULL
ORDER BY st.value DESC
FETCH FIRST 30 ROWS ONLY;
6. Check EBS concurrent requests running during the issue
SELECT r.request_id,
p.user_concurrent_program_name,
r.phase_code,
r.status_code,
r.actual_start_date,
r.actual_completion_date,
ROUND((NVL(r.actual_completion_date, SYSDATE)
- r.actual_start_date) * 24, 2) AS runtime_hours,
r.oracle_process_id
FROM apps.fnd_concurrent_requests r
JOIN apps.fnd_concurrent_programs_vl p
ON p.concurrent_program_id = r.concurrent_program_id
AND p.application_id = r.program_application_id
WHERE r.actual_start_date >= SYSDATE - 2
ORDER BY r.actual_start_date DESC;
7. Check tablespace usage
SELECT df.tablespace_name,
ROUND(df.total_gb, 2) AS total_gb,
ROUND(df.total_gb - fs.free_gb, 2) AS used_gb,
ROUND(fs.free_gb, 2) AS free_gb,
ROUND((df.total_gb - fs.free_gb) / df.total_gb * 100, 2) AS used_pct
FROM (
SELECT tablespace_name,
SUM(bytes) / 1024 / 1024 / 1024 AS total_gb
FROM dba_data_files
WHERE tablespace_name = 'APPS_TS_TX_IDX'
GROUP BY tablespace_name
) df
JOIN (
SELECT tablespace_name,
SUM(bytes) / 1024 / 1024 / 1024 AS free_gb
FROM dba_free_space
WHERE tablespace_name = 'APPS_TS_TX_IDX'
GROUP BY tablespace_name
) fs
ON fs.tablespace_name = df.tablespace_name;
Likely causes include a bulk data load, index rebuild, CREATE INDEX, heavy updates or deletes, statistics collection with index maintenance, clone/post-clone processing, interface imports, or a repeatedly failing concurrent program. The archive-
What to check next
1. Is debug logging enabled?
Check the profile options:
SELECT profile_option_name,
profile_option_value
FROM apps.fnd_profile_option_values v,
apps.fnd_profile_options p
WHERE v.profile_option_id = p.profile_option_id
AND p.profile_option_name LIKE 'AFLOG%';
Or from the EBS front end:
FND: Debug Log Enabled
FND: Debug Log Level
If enabled at Statement or Procedure, it can generate very large volumes of log records.
2. Which module is writing to FND_LOG_MESSAGES?
SELECT module,
COUNT(*) cnt
FROM applsys.fnd_log_messages
WHERE timestamp >= SYSDATE - 3
GROUP BY module
ORDER BY cnt DESC;
3. Which workflow events are busiest?
SELECT event_name,
COUNT(*)
FROM apps.wf_events
GROUP BY event_name
ORDER BY COUNT(*) DESC;
(If WF_EVENTS does not contain the required data, we can query the relevant runtime workflow tables instead.)
COL profile_level FOR A15
COL profile_option_value FOR A20
SELECT
DECODE(level_id,
10001,'SITE',
10002,'APPLICATION',
10003,'RESPONSIBILITY',
10004,'USER',
10005,'SERVER',
TO_CHAR(level_id)) profile_level,
level_value,
profile_option_value
FROM apps.fnd_profile_option_values v,
apps.fnd_profile_options p
WHERE v.profile_option_id = p.profile_option_id
AND p.profile_option_name = 'AFLOG_ENABLED';
Subscribe to:
Posts (Atom)