Oracle E-Business Suite 12.2: Concurrent Request Performance Troubleshooting and Essential Diagnostic SQL
When an Oracle E-Business Suite concurrent request runs much longer than expected, appears to hang, or suddenly performs worse than earlier runs, the safest approach is to investigate in layers. First confirm the request state, then map it to its database session, inspect the SQL and wait event, check for blocking, and enable tracing only when the read-only evidence is insufficient.
This guide provides a production-oriented workflow for Oracle EBS 12.2 with an Oracle 19c database. It also includes frequently used SQL and UNIX commands for patch, file-version, statistics, profile-option, and executable-level checks.
1. Investigation Flow for a Slow or Hanging Concurrent Request
- Confirm the request phase, status, start time, and database process ID.
- Map the operating-system process to the Oracle session.
- Identify the current or most recently executed SQL.
- Review the session wait event and elapsed wait time.
- Check whether another session is blocking the request.
- Compare the runtime with previous executions of the same program.
- Capture a targeted trace only when required.
2. Check the Current Concurrent Request Status
The following query returns the program name, phase, status, timestamps, and Oracle process ID for one or more request IDs.
SELECT r.request_id,
cp.user_concurrent_program_name,
phase.meaning AS request_phase,
status.meaning AS request_status,
TO_CHAR(r.request_date, 'DD-MON-YYYY HH24:MI:SS') AS request_date,
TO_CHAR(r.actual_start_date, 'DD-MON-YYYY HH24:MI:SS') AS actual_start_date,
TO_CHAR(r.actual_completion_date, 'DD-MON-YYYY HH24:MI:SS') AS actual_completion_date,
r.oracle_process_id
FROM apps.fnd_concurrent_requests r
JOIN apps.fnd_concurrent_programs_tl cp
ON cp.application_id = r.program_application_id
AND cp.concurrent_program_id = r.concurrent_program_id
AND cp.language = 'US'
JOIN apps.fnd_lookups phase
ON phase.lookup_type = 'CP_PHASE_CODE'
AND phase.lookup_code = r.phase_code
JOIN apps.fnd_lookups status
ON status.lookup_type = 'CP_STATUS_CODE'
AND status.lookup_code = r.status_code
WHERE r.request_id IN (&request_id1, &request_id2)
ORDER BY r.request_id;3. Map the Request to Its Database Session and SQL
On Oracle 19c, use SQL_ID and PREV_SQL_ID instead of joining only through the legacy SQL address. A request may be between SQL calls, so the previous SQL ID is also useful.
SELECT r.request_id,
s.sid,
s.serial#,
p.spid AS os_process_id,
s.status AS session_status,
s.module,
s.action,
s.sql_id,
s.prev_sql_id,
q.sql_text
FROM apps.fnd_concurrent_requests r
JOIN v$process p
ON p.spid = TRIM(r.oracle_process_id)
JOIN v$session s
ON s.paddr = p.addr
LEFT JOIN v$sql q
ON q.sql_id = COALESCE(s.sql_id, s.prev_sql_id)
AND q.child_number = (
SELECT MIN(q2.child_number)
FROM v$sql q2
WHERE q2.sql_id = COALESCE(s.sql_id, s.prev_sql_id)
)
WHERE r.request_id IN (&request_id1, &request_id2)
ORDER BY r.request_id;If the request is no longer running, its database session may already have disconnected. In that case, use AWR/ASH if licensed and retained, or correlate the request timestamps with archived diagnostic data.
4. Review the Current Wait Event
V$SESSION provides the current wait information and is preferred over the older V$SESSION_WAIT view.
SELECT r.request_id,
s.sid,
s.serial#,
s.event,
s.wait_class,
s.state,
s.seconds_in_wait,
s.blocking_session_status,
s.blocking_instance,
s.blocking_session
FROM apps.fnd_concurrent_requests r
JOIN v$process p
ON p.spid = TRIM(r.oracle_process_id)
JOIN v$session s
ON s.paddr = p.addr
WHERE r.request_id IN (&request_id1, &request_id2)
ORDER BY r.request_id;A wait event is not automatically a problem. Interpret it with the wait class, duration, request behavior, SQL plan, and workload. For example, an idle wait is normally expected, while a sustained concurrency or user-I/O wait may need deeper investigation.
5. Identify Blocking Sessions
This session-based query is more useful than checking a table name alone because it identifies the waiting request and the blocking database session directly.
SELECT r.request_id,
s.sid AS waiting_sid,
s.serial# AS waiting_serial,
s.event,
s.seconds_in_wait,
s.blocking_instance,
s.blocking_session,
bs.serial# AS blocking_serial,
bs.username AS blocking_username,
bs.module AS blocking_module,
bs.sql_id AS blocking_sql_id
FROM apps.fnd_concurrent_requests r
JOIN v$process p
ON p.spid = TRIM(r.oracle_process_id)
JOIN v$session s
ON s.paddr = p.addr
LEFT JOIN gv$session bs
ON bs.inst_id = s.blocking_instance
AND bs.sid = s.blocking_session
WHERE r.request_id = &request_id;Do not terminate a blocking session solely because it appears in this output. First identify its owner, transaction, business function, and rollback impact.
6. Compare Runtime with Previous Executions
Historical runtime helps determine whether degradation was gradual, intermittent, or sudden.
SELECT cp.user_concurrent_program_name,
r.request_id,
r.actual_start_date,
r.actual_completion_date,
ROUND((r.actual_completion_date - r.actual_start_date) * 86400) AS runtime_seconds,
phase.meaning AS request_phase,
status.meaning AS request_status
FROM apps.fnd_concurrent_requests r
JOIN apps.fnd_concurrent_programs_tl cp
ON cp.application_id = r.program_application_id
AND cp.concurrent_program_id = r.concurrent_program_id
AND cp.language = 'US'
JOIN apps.fnd_lookups phase
ON phase.lookup_type = 'CP_PHASE_CODE'
AND phase.lookup_code = r.phase_code
JOIN apps.fnd_lookups status
ON status.lookup_type = 'CP_STATUS_CODE'
AND status.lookup_code = r.status_code
WHERE cp.user_concurrent_program_name = '&concurrent_program_name'
AND r.actual_start_date >= SYSDATE - &history_days
ORDER BY r.actual_start_date DESC;7. Tracing a Concurrent Request
Option A: Enable Trace on the Concurrent Program
Navigate to:
System Administrator → Concurrent → Program → Define
Query the program and select Enable Trace. Submit a controlled test request, collect the trace, and disable the option after testing so later requests are not traced unintentionally.
Find the Trace File on Oracle 19c
USER_DUMP_DEST is obsolete for modern ADR-managed databases. Use the diagnostic destination or query the session trace file directly.
SELECT value
FROM v$diag_info
WHERE name = 'Diag Trace';SELECT r.request_id,
s.sid,
s.serial#,
p.spid AS os_process_id,
p.tracefile,
s.module,
s.sql_id
FROM apps.fnd_concurrent_requests r
JOIN v$process p
ON p.spid = TRIM(r.oracle_process_id)
JOIN v$session s
ON s.paddr = p.addr
WHERE r.request_id = &request_id;Option B: Initialization SQL Statement – Custom
For a single user and controlled reproduction, temporarily set the profile option Initialization SQL Statement – Custom at user level:
BEGIN
EXECUTE IMMEDIATE q'[ALTER SESSION SET TRACEFILE_IDENTIFIER = 'SR_NUMBER']';
EXECUTE IMMEDIATE q'[ALTER SESSION SET MAX_DUMP_FILE_SIZE = UNLIMITED]';
EXECUTE IMMEDIATE q'[ALTER SESSION SET EVENTS '10046 trace name context forever, level 12']';
END;Run only the affected activity, then restore the profile to its previous value immediately. Level 12 captures SQL waits and bind values and can generate substantial output; protect sensitive trace data accordingly.
Option C: Trace a Reproducible SQL*Plus Test
ALTER SESSION SET statistics_level = ALL;
ALTER SESSION SET tracefile_identifier = 'ORGPERF';
ALTER SESSION SET events '10046 trace name context forever, level 12';
-- Execute only the problematic SQL here.
ALTER SESSION SET events '10046 trace name context off';Format the Trace with TKPROF
tkprof input_trace.trc output_trace.txt sort=exeela,fchela,prsela sys=no8. Patch and File-Version Diagnostics
Identify the Patch That Delivered a Specific File Version
This read-only SQL returns the patching history for a particular EBS file. It correlates the file with its recorded version, translation level, patch, driver, patch run, APPL_TOP, and application date. Enter the EBS applications-system name for &SID and supply the filename in uppercase for &file_name_in_caps.
SELECT atp.name AS appl_top_name,
DECODE(f.app_short_name,
'DUMMY', NULL,
'SQLAP', 'AP',
'SQLGL', 'GL',
'OFA', 'FA',
f.app_short_name) AS product,
DECODE(f.subdir, 'DUMMY', NULL, f.subdir) AS directory_name,
f.filename,
fv.version
|| DECODE(fv.translation_level,
0, NULL,
':' || TO_CHAR(fv.translation_level)) AS file_version,
TO_CHAR(pr.end_date, 'DD-MM-YYYY HH24:MI:SS') AS date_applied,
ap.patch_name AS patch_id,
ap.applied_patch_id,
pr.end_date,
fv.version_segment1,
fv.version_segment2,
fv.version_segment3,
fv.version_segment4,
fv.version_segment5,
fv.version_segment6,
fv.version_segment7,
fv.version_segment8,
fv.version_segment9,
fv.version_segment10,
fv.translation_level,
pr.patch_run_id,
pr.patch_top,
pr.patch_action_options,
TO_CHAR(pr.start_date, 'DD-MM-YYYY HH24:MI:SS') AS patch_start_date,
pr.program_run_id,
pr.session_id,
pd.patch_driver_id,
pd.driver_file_name,
pd.platform
FROM ad_appl_tops atp,
ad_applied_patches ap,
ad_patch_drivers pd,
ad_patch_runs pr,
ad_patch_run_bugs prb,
ad_file_versions fv,
ad_patch_run_bug_actions prba,
ad_files f
WHERE f.file_id = prba.file_id
AND prba.executed_flag = 'Y'
AND prba.patch_run_bug_id = prb.patch_run_bug_id
AND pr.appl_top_id = atp.appl_top_id
AND prb.patch_run_id = pr.patch_run_id
AND pr.patch_driver_id = pd.patch_driver_id
AND pd.applied_patch_id = ap.applied_patch_id
AND prba.patch_file_version_id = fv.file_version_id
AND UPPER(atp.applications_system_name) = UPPER('&SID')
AND UPPER(f.filename) IN ('&file_name_in_caps')
GROUP BY f.app_short_name,
f.subdir,
f.filename,
atp.name,
fv.version,
fv.version_segment1,
fv.version_segment2,
fv.version_segment3,
fv.version_segment4,
fv.version_segment5,
fv.version_segment6,
fv.version_segment7,
fv.version_segment8,
fv.version_segment9,
fv.version_segment10,
fv.translation_level,
ap.patch_name,
pr.end_date,
ap.applied_patch_id,
pr.patch_run_id,
pr.patch_top,
pr.patch_action_options,
pr.start_date,
pr.program_run_id,
pr.session_id,
pd.patch_driver_id,
pd.driver_file_name,
pd.platform
ORDER BY f.app_short_name,
f.subdir,
atp.name,
fv.version_segment1 DESC,
fv.version_segment2 DESC,
fv.version_segment3 DESC,
fv.version_segment4 DESC,
fv.version_segment5 DESC,
fv.version_segment6 DESC,
fv.version_segment7 DESC,
fv.version_segment8 DESC,
fv.version_segment9 DESC,
fv.version_segment10 DESC,
fv.translation_level DESC,
pr.end_date DESC;FNDLIBR or AFCPRUN.SQL, and enter it in uppercase. If the same filename exists in multiple products or directories, use the returned product and directory columns to identify the correct record. This query reports the history stored in the AD patch tables; validate the deployed file on the relevant run and patch file systems when investigating an EBS 12.2 discrepancy.
Check Whether an EBS Patch Is Recorded
SELECT bug_number, creation_date
FROM apps.ad_bugs
WHERE bug_number = '&bug_number';
SELECT patch_name, applied_patch_id, creation_date
FROM apps.ad_applied_patches
WHERE patch_name = '&patch_name';For EBS 12.2 online patching, also correlate the result with the relevant ADOP session and patch records. A row in one table alone may not describe the complete patching-cycle outcome.
Check the EBS Release
SELECT release_name
FROM apps.fnd_product_groups;Check Database Component Versions
SELECT comp_name, version, status
FROM dba_registry
ORDER BY comp_name;Inspect Package Header and Selected Source Lines
SELECT owner, name, type, line, text
FROM all_source
WHERE owner = UPPER('&owner')
AND name = UPPER('&package_name')
AND line <= 10
ORDER BY type, line;
SELECT owner, name, type, line, text
FROM all_source
WHERE owner = UPPER('&owner')
AND name = UPPER('&package_name')
AND line BETWEEN &line_from AND &line_to
ORDER BY type, line;9. Index and Statistics Checks
List Index Columns
SELECT ic.index_owner,
ic.index_name,
ic.column_position,
ic.column_name,
i.status,
i.last_analyzed
FROM dba_ind_columns ic
JOIN dba_indexes i
ON i.owner = ic.index_owner
AND i.index_name = ic.index_name
WHERE ic.table_owner = UPPER('&table_owner')
AND ic.table_name = UPPER('&table_name')
ORDER BY ic.index_name, ic.column_position;Check When Table Statistics Were Gathered
SELECT owner, table_name, num_rows, stale_stats, last_analyzed
FROM dba_tab_statistics
WHERE owner = UPPER('&table_owner')
AND table_name = UPPER('&table_name');Gather EBS Table Statistics
EXEC apps.fnd_stats.gather_table_stats('&schema_name', '&table_name');This is a state-changing operation. Confirm the correct FND_STATS signature for your EBS release, estimate the impact, and schedule it through change control.
10. Retrieve Profile Option Values
The following query resolves site, application, responsibility, user, server, and organization-level values without assuming that the display name is unique across languages.
SELECT po.profile_option_name,
pot.user_profile_option_name,
pov.level_id,
pov.level_value,
pov.level_value2,
pov.profile_option_value
FROM apps.fnd_profile_options po
JOIN apps.fnd_profile_options_tl pot
ON pot.profile_option_name = po.profile_option_name
AND pot.application_id = po.application_id
AND pot.language = 'US'
JOIN apps.fnd_profile_option_values pov
ON pov.profile_option_id = po.profile_option_id
AND pov.application_id = po.application_id
WHERE UPPER(pot.user_profile_option_name) =
UPPER('&user_profile_option_name')
ORDER BY pov.level_id, pov.level_value;11. Materialized View Refresh
BEGIN
DBMS_MVIEW.REFRESH(
list => '&schema_name.&materialized_view_name',
method => '&refresh_method'
);
END;
/A refresh can be resource-intensive and may lock or modify the materialized view. Validate the refresh method and run it only in an approved window.
12. Useful UNIX Commands
Find a File Version Embedded in an Executable
strings -a <executable_name> | grep -i '<file_name>' | grep '\$Header'List All Embedded File Headers
strings -a <executable_name> | grep '\$Header' > executable_versions.txtCheck Soft and Hard Resource Limits
ulimit -aS
ulimit -aHCompare an ODF Object with the Database
adodfcmp odffile=<file_name> \
userid=apps \
mode=views \
logfile=/tmp/adodfcmp.log \
touser=apps \
priv_schema=system \
changedb=nAllow the utility to prompt for passwords; do not place database passwords in commands, scripts, screenshots, or shell history.
13. Relinking: Use Only Through an Approved Change
Relinking is not a diagnostic read-only action. Source the correct run-edition environment, stop the affected service or process as required, take backups, review the product-specific procedure, and validate afterward.
adrelink.sh force=y ranlib=y "<product_short_name>"
adrelink.sh force=y ranlib=y "<product_short_name> <executable_name>"Recommended Evidence to Capture for an RCA
- Request ID, program name, parameters, phase, and status.
- Expected runtime and actual runtime.
- SID, serial number, OS process ID, SQL ID, and execution plan.
- Wait event, blocking-session details, and object involved.
- CPU, memory, I/O, and load during the incident window.
- Recent statistics, patches, configuration changes, and data-volume growth.
- Request log/output and any targeted trace or TKPROF report.
- Comparison with previous successful executions.
Conclusion
A slow concurrent request should not be diagnosed from a single query or wait event. Build a time-correlated evidence chain from the EBS request, Oracle session, SQL, execution plan, waits, blockers, host utilization, and historical runtime. Start with read-only checks, keep tracing narrowly scoped, and use change control for statistics gathering, refreshes, relinking, or session termination.
Suggested Blogger labels: Oracle EBS 12.2, Apps DBA, Concurrent Manager, Performance Tuning, SQL, Oracle 19c, Troubleshooting
No comments:
Post a Comment