Mastering Oracle EBS Concurrent Requests: Your Ultimate SQL Query Toolkit
As an Oracle E-Business Suite administrator or developer, managing concurrent requests is a fundamental part of your daily routine. From troubleshooting long-running programs to auditing user activity, having a powerful set of SQL queries can save you a significant amount of time and effort.
This post provides a comprehensive toolkit of essential SQL queries to help you monitor, troubleshoot, and analyze concurrent requests and other critical system data in your Oracle EBS environment.
1. Finding Concurrent Request Information
The queries below are your go-to tools for getting basic information about concurrent requests. You can use these to find out which manager ran a specific request, what program is associated with a request, or to see a summary of a request's status.
How to Determine Which Manager Ran a Specific Concurrent Request
This query helps you pinpoint the concurrent manager responsible for a particular request, which is useful for troubleshooting queue-related issues.
col USER_CONCURRENT_QUEUE_NAME for a100
select b.USER_CONCURRENT_QUEUE_NAME
from fnd_concurrent_processes a,
fnd_concurrent_queues_vl b,
fnd_concurrent_requests c
where a.CONCURRENT_QUEUE_ID = b.CONCURRENT_QUEUE_ID
and a.CONCURRENT_PROCESS_ID = c.controlling_manager
and c.request_id = ‘&conc_reqid’;
To Find Concurrent Program Name, Phase, and Status for a Given Request ID
This is a quick way to get the essential details of a completed or running request, including the program name and its current phase and status.
SELECT request_id,
user_concurrent_program_name,
DECODE(phase_code, 'C', 'Completed', phase_code) phase_code,
DECODE(status_code, 'D', 'Cancelled',
'E', 'Error',
'G', 'Warning',
'H', 'On Hold',
'T', 'Terminating',
'M', 'No Manager',
'X', 'Terminated',
'C', 'Normal', status_code) status_code,
to_char(actual_start_date, 'dd-mon-yy:hh24:mi:ss') Start_Date,
to_char(actual_completion_date, 'dd-mon-yy:hh24:mi:ss'),
completion_text
FROM apps.fnd_conc_req_summary_v
WHERE request_id = '&req_id'
ORDER BY 6 DESC;
To Find Child Requests
If a program spawns other requests (a parent-child relationship), this query helps you find all the requests submitted by a parent.
set lines 200
col USER_CONCURRENT_PROGRAM_NAME for a40
col PHASE_CODE for a10
col STATUS_CODE for a10
col COMPLETION_TEXT for a20
SELECT sum.request_id,
req.PARENT_REQUEST_ID,
sum.user_concurrent_program_name,
DECODE (sum.phase_code, 'C', 'Completed', sum.phase_code) phase_code,
DECODE(sum.status_code, 'D', 'Cancelled' ,
'E', 'Error' ,
'G', 'Warning',
'H', 'On Hold' ,
'T', 'Terminating',
'M', 'No Manager' ,
'X', 'Terminated',
'C', 'Normal', sum.status_code) status_code,
sum.actual_start_date,
sum.actual_completion_date,
sum.completion_text
FROM apps.fnd_conc_req_summary_v sum,
apps.fnd_concurrent_requests req
where req.request_id=sum.request_id
and req.PARENT_REQUEST_ID = '&parent_concurrent_request_id';
2. Troubleshooting Long-Running and Errored Requests
These queries are essential for performance tuning and troubleshooting. They help you identify sessions that are taking too long, find the underlying SQL, and pinpoint requests that have failed.
Long-Running Concurrent Requests
This is a powerful query that provides a comprehensive overview of currently running requests, including session information (SPID), and their runtime duration.
SELECT a.request_id,
a.oracle_process_id "SPID",
frt.responsibility_name,
c.concurrent_program_name || ': ' || ctl.user_concurrent_program_name,
a.description,
a.ARGUMENT_TEXT,
b.node_name,
b.db_instance,
a.logfile_name,
a.logfile_node_name,
a.outfile_name,
q.concurrent_queue_name,
a.phase_code,
a.status_code,
a.completion_text,
actual_start_date,
actual_completion_date,
fu.user_name,
(nvl(actual_completion_date,sysdate)-actual_start_date)*1440 mins,
(SELECT avg(nvl(a2.actual_completion_date-a2.actual_start_date,0))*1440 avg_run_time
FROM APPLSYS.fnd_Concurrent_requests a2,
APPLSYS.fnd_concurrent_programs c2
WHERE c2.concurrent_program_id = c.concurrent_program_id
AND a2.concurrent_program_id = c2.concurrent_program_id
AND a2.program_application_id = c2.application_id
AND a2.phase_code || '' = 'C') avg_mins,
round((actual_completion_date - requested_start_date),2) * 24 duration_in_hours
FROM APPLSYS.fnd_Concurrent_requests a,
APPLSYS.fnd_concurrent_processes b,
applsys.fnd_concurrent_queues q,
APPLSYS.fnd_concurrent_programs c,
APPLSYS.fnd_concurrent_programs_tl ctl,
apps.fnd_user fu,
apps.FND_RESPONSIBILITY_TL frt
WHERE a.controlling_manager = b.concurrent_process_id
AND a.concurrent_program_id = c.concurrent_program_id
AND a.program_application_id = c.application_id
AND a.phase_code = 'R'
AND a.status_code = 'R'
AND b.queue_application_id = q.application_id
AND b.concurrent_queue_id = q.concurrent_queue_id
AND ctl.concurrent_program_id = c.concurrent_program_id
AND a.requested_by = fu.user_id
AND a.responsibility_id = frt.responsibility_id
ORDER BY a.actual_start_date DESC;
To Find the SQL Query for a Given Concurrent Request SID
Once you have the Session ID (SID) from the previous query, you can use this to find the specific SQL statement the request is currently executing.
select sid,sql_text
from gv$session ses, gv$sqlarea sql
where ses.sql_hash_value = sql.hash_value(+)
and ses.sql_address = sql.address(+)
and ses.sid='&oracle_sid'
/
Oracle Concurrent Request Error Script
This query quickly lists all concurrent requests that have errored out in the last two days, helping you identify and resolve issues.
SELECT a.request_id "Req Id",
a.phase_code,
a.status_code,
actual_start_date,
actual_completion_date,
c.concurrent_program_name || ': ' || ctl.user_concurrent_program_name "program"
FROM APPLSYS.fnd_Concurrent_requests a,
APPLSYS.fnd_concurrent_processes b,
applsys.fnd_concurrent_queues q,
APPLSYS.fnd_concurrent_programs c,
APPLSYS.fnd_concurrent_programs_tl ctl
WHERE a.controlling_manager = b.concurrent_process_id
AND a.concurrent_program_id = c.concurrent_program_id
AND a.program_application_id = c.application_id
AND a.status_code = 'E'
AND a.phase_code = 'C'
AND actual_start_date > sysdate - 2
AND b.queue_application_id = q.application_id
AND b.concurrent_queue_id = q.concurrent_queue_id
AND ctl.concurrent_program_id = c.concurrent_program_id
AND ctl.LANGUAGE = 'US'
ORDER BY 5 DESC;
3. Monitoring and Performance Tuning
These queries help you monitor the overall health of your concurrent processing system by checking manager status, pending requests, and average run times.
Concurrent Manager Status for a Given SID
Use this to get detailed session information for a specific concurrent manager session.
col MODULE for a20
col OSUSER for a10
col USERNAME for a10
set num 10
col MACHINE for a20
set lines 200
col SCHEMANAME for a10
select s.sid,
s.serial#,
p.spid os_pid,
s.status,
s.osuser,
s.username,
s.MACHINE,
s.MODULE,
s.SCHEMANAME,
s.action
from gv$session s, gv$process p
WHERE s.paddr = p.addr
and s.sid = '&oracle_sid';
Checking Which Manager Will Execute a Program
This query helps you understand the concurrent manager specialization rules by showing which manager is set to run a specific program.
SELECT user_concurrent_program_name,
user_concurrent_queue_name
FROM apps.fnd_concurrent_programs_tl cp,
apps.fnd_concurrent_queue_content cqc,
apps.fnd_concurrent_queues_tl cq
WHERE cqc.type_application_id(+) = cp.application_id
AND cqc.type_id(+) = cp.concurrent_program_id
AND cqc.type_code(+) = 'P'
AND cqc.include_flag(+) = 'I'
AND cp.LANGUAGE = 'US'
AND cp.user_concurrent_program_name = '&USER_CONCURRENT_PROGRAM_NAME'
AND NVL (cqc.concurrent_queue_id, 0) = cq.concurrent_queue_id
AND NVL (cqc.queue_application_id, 0) = cq.application_id
AND cq.LANGUAGE = 'US'
To See All Pending / Running Requests per Manager
This gives you a quick snapshot of the workload for each concurrent manager.
SELECT request_id,
phase_code,
status_code,
user_name,
user_concurrent_queue_name
FROM apps.fnd_concurrent_worker_requests cwr,
apps.fnd_concurrent_queues_tl cq,
apps.fnd_user fu
WHERE (cwr.phase_code = 'P' OR cwr.phase_code = 'R')
AND cwr.hold_flag != 'Y'
AND cwr.requested_start_date <= SYSDATE
AND cwr.concurrent_queue_id = cq.concurrent_queue_id
AND cwr.queue_application_id = cq.application_id
AND cq.LANGUAGE = 'US'
AND cwr.requested_by = fu.user_id
ORDER BY 5
4. Administering Oracle EBS
These queries provide a broader view of your Oracle EBS environment, from user management to applied patches.
To Find Out If a Module is Installed
This query lists all installed Oracle EBS products along with their patch levels.
SELECT a.application_name,
a.product_code,
DECODE (b.status, 'I', 'Installed', 'S', 'Shared', 'N/A') status,
patch_level
FROM apps.fnd_application_vl a,
apps.fnd_product_installations b
WHERE a.application_id = b.application_id
and b.status='I'
order by product_code asc;
Script to Reset Oracle Apps Front-End User Password
This is an essential administrative script for resetting a user's password from the backend using PL/SQL.
DECLARE
flag_value BOOLEAN;
BEGIN
flag_value := fnd_user_pkg.changepassword(username=> 'NKUMAR2', newpassword => 'welcome1');
IF flag_value THEN
DBMS_OUTPUT.PUT_LINE('The password reset successfully');
ELSE
DBMS_OUTPUT.PUT_LINE('The password reset has failed');
END IF;
END;
/
COMMIT;
How to Check the System Administrator Responsibility for Users
This query quickly identifies which users have been assigned the "System Administrator" responsibility and for what time frame.
set lines 2000
set pages 5000
col USER_NAME for a45
Select b.user_name,
c.responsibility_name,
a.START_DATE,
a.END_DATE
from fnd_user_resp_groups_direct a,
fnd_user b,
fnd_responsibility_tl c
where a.user_id = b.user_id
and a.responsibility_id = c.responsibility_id
and c.responsibility_name='System Administrator';
This collection of queries should serve as a powerful resource for any Oracle EBS professional. Bookmark this page and refer to it whenever you need to dive into the details of your concurrent processing environment.
No comments:
Post a Comment