Scenario: Concurrent Requests are in Running Normal state for longer time than normal duration of the Program.
Analysis:
Step 1: Navigate to System Administrator Responsibility : Concurrent Manager --> Administer
Identify the Concurrent Requests which are in Running Status.
Get the Database Session Details of the currently running concurrent requests using the below Query:
SELECT DISTINCT a.request_id,C.INST_ID, d.sid, d.serial# ,d.osuser,d.process , c.SPID ,d.inst_id
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
gv$process c,
gv$session d
WHERE a.controlling_manager = b.concurrent_process_id
AND c.pid = b.oracle_process_id
AND b.session_id=d.audsid
AND a.phase_code = 'R' and a.status_coDe='R';
Step 2: Verify if the database sessions are active/Inactive at the database level using the below query.
select inst_id,sid,serial#,program,module,status,last_call_et,sql_id from gv$session where sid=&sid;
-- sid value to be taken from output of Sql Query in Step 1
If Database session is INACTIVE And Running no sql for more than an Hour, we can Terminate the Concurrent Requests.
If the Database session is ACTIVE and has an SQL_ID attached with it, Need to check on tuning the sql being run by the database session.
I will cover more details about SQL Tuning in another post.