How to troubleshoot Long Running Concurrent Request in EBS 12.2

 The following points need to answer before jumping into troubleshooting:

• Are there any recent code changes done in the concurrent program?

          Check long_running_sid and verify the recent code change of last_ddl_timestamp after a 

           discussion with the developer.

• Was this running long in the last few run as well, or was this time only?

           ==> Check the program history.

• How much time does it take to complete?

          Once again program history can give us some idea.

• Are these jobs fetching higher data compared to the last run

          ===> Confirm on data change . Query Plan change will indicate this issue.

• Does this job runs at any specific time or it can be run anytime

          ===> The nature of the job can help to understand the job's nature.

• Does this job fetch data using DB Link

         ====> Check whether the DB link is involved or not.

The below-given approach would help to conclude the gray areas.

1. Check the load of the server using the top command
2.  Verify the mount space using df -hP
3.  Check the tablespace size
4. Verify the status of the concurrent request using the query
5. select request_id ,phase_code, status_code from fnd_concurrent_requests where request_id ='**';
6.  Check which CM is running this request. 

SELECT request_id, user_concurrent_program_name, status_code, phase_code, completion_text FROM apps.fnd_conc_req_summary_v WHERE request_id =&req_id ;

•   Confirm the Actual and target of the CM from the above output
•   Confirm the Concurrent Manager Status and queue
•   Check the SID of the concurrent request using the query:

SELECT a.request_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 = .oracle_process_id
AND b.session_id=d.audsid AND a.request_id =&req_id AND a.phase_code = 'R';

Query to get the row fetched using the SID from the above step.

column name format a30 word_wrapped
column value format 999,999,999,999

select b.name, a.value value from v$sesstat a, v$statname b where a.statistic# = b.statistic# and sid =&sid and a.value != 0 and b.name like '%row%' /

col sid format 999999
col username format a20
col osuser format a15
select a.sid, a.serial#,a.username, a.osuser, b.spid from v$session a, v$process b where .paddr= b.addr and a.sid='&sid' order by a.sid;

Check the Database session status and what it is running

set echo off
set linesize 132
set verify off
set feedback off
set serveroutput on;
declare
   SID number        := 0 ;
   SERIAL number       := 0 ;
   username varchar(20)   := '';
   Status varchar(8)     := '';
   machine varchar(10)   := '';
   terminal  varchar(25)  := '';
   program  varchar(30)   := '';
   Module varchar(30)    := '';
   Action varchar(20)    := '';
   sql_hash_value number  := 0 ;
   logontime varchar(30)   := '';
   last_call_et number    := 0 ;
   proc number        := 0 ;
   spid number        := 0 ;
   event varchar(30)     := '';
   state varchar(30)     := '';
   sql_text varchar(2000)  := '';
cursor cur1 is
select a.sid sid,
   a.serial# serial,
   a.username username,
   a.status status ,
   a.machine machine,
   a.terminal terminal,
   a.program program,
   a.module module,
   a.action action,
   a.sql_hash_value sql_hash_value,
   to_char(a.logon_time,'DD-Mon-YYYY HH:MI:SS') logontime,
   a.last_call_et last_call_et,
   a.process proc,
   b.spid spid,
   sw.event event,
   sw.state state
from  gv$session a, gv$process b, gv$session_wait sw
where a.paddr=b.addr and a.inst_id=b.inst_id
   and a.sid='&1'
   and a.inst_id=sw.inst_id
   and a.sid=sw.sid;
begin
 DBMS_OUTPUT.PUT_LINE('-----------------------------------------------------------------');
 DBMS_OUTPUT.PUT_LINE(' Database session detail for the shadow process ');
 DBMS_OUTPUT.PUT_LINE('-----------------------------------------------------------------');
for m in cur1
loop
DBMS_OUTPUT.ENABLE(50000);
  DBMS_OUTPUT.PUT_LINE(' ');
  DBMS_OUTPUT.PUT_LINE( 'SID............ : ' || m.sid );
  DBMS_OUTPUT.PUT_LINE('SERIAL#........ : ' || m.serial  );
  DBMS_OUTPUT.PUT_LINE('USERNAME....... : ' || m.username  );
  DBMS_OUTPUT.PUT_LINE('STATUS......... : ' || m.status   );
  DBMS_OUTPUT.PUT_LINE( 'Machine........ : ' || m.machine );
  DBMS_OUTPUT.PUT_LINE( 'Terminal....... : ' || m.terminal);
  DBMS_OUTPUT.PUT_LINE( 'Program........ : ' || m.program );
  DBMS_OUTPUT.PUT_LINE('Module......... : ' || m.module );
  DBMS_OUTPUT.PUT_LINE( 'Action......... : ' || m.action );
  DBMS_OUTPUT.PUT_LINE('SQL Hash Value. : ' || m.sql_hash_value );
  DBMS_OUTPUT.PUT_LINE( 'Logon Time..... : ' || m.logontime );
  DBMS_OUTPUT.PUT_LINE( 'Last Call Et... : ' || m.last_call_et );
  DBMS_OUTPUT.PUT_LINE( 'Process ID..... : ' || m.proc );
  DBMS_OUTPUT.PUT_LINE( 'SPID........... : ' || m.spid );
  DBMS_OUTPUT.PUT_LINE('Session Waiting for event:'||m.event );
  DBMS_OUTPUT.PUT_LINE('Session state ...........:'||m.state);
dbms_output.put_line('SQL_TEXT is..........:');
for rec in ( select sql_text from v$session s,v$sqltext v where
  s.sql_hash_value=v.hash_value and
  s.sql_address=v.address and s.sid=m.sid order by piece)
loop
dbms_output.put_line(rec.sql_text);
end loop;
  DBMS_OUTPUT.PUT_LINE('--------------------------------------------------------------------');
  DBMS_OUTPUT.PUT_LINE(' ');
end loop;
end;
/

• Check which sql id it is running, you can get these details using the above steps.
• One should check the sql_id history and can run sql advisory against the sql_id. And check recommendations based on the sql advisory 
• Check the stale value of the tables which is used by the concurrent program.

    select table_name, stale_stats, last_analyzed from dba_tab_statistics where   stale_stats='YES';

May have to run the gather stats for those tables which are having stale values. The same can also be monitored through EM.

Industry best practices will help to achieve optimal performance of CM jobs.

Optimize the Concurrent Manager

• Manage CM Jobs Lifecycle: 50% of performance tuning is in the business!

– Review long-running auto-resubmitted jobs to ensure that they are still required

– Review jobs that have a huge variance in run time to ensure all users are using selective parameters

– Don’t purge jobs that the users then have to run again

– Trim the fat: Review and eliminate concurrent jobs that are not required and/or are not being used by users.

• Manage CM Managers: More managers/workers != more throughput

– Avoid enabling an excessive number of standard or specialized managers.

– A common guideline is between 1-2 target processes per CPU, but this needs to be balanced with resources required by online activity

– Utilize Parallel Concurrent Processing (PCP) to leverage the Cluster (RAC) 

Manage CM Schedules:

– Use specialization rules and work shifts to bind specific jobs to specific time windows

– Avoid scheduling resource-intensive batch requests during peak user/online activity

– Reschedule some programs to run when the concurrent managers have available capacity

– Define Workload Management Strategy based on job average duration and system usage profile

• Manage CM State Data:

– Purge the FND tables on a regular basis using the “Purge Concurrent Request and Manager Data” program

– High throughput: Keep *.out and *.log on fast disks. Use separate disks if throughput seems slow, especially on NFS Filers

– Truncate the reports.log file in the log directory. Watch for 2GB limit on any output files

   (MOS ID 842850.1)

   

Manage CM State Data:

– Double-check the NFS parameters as per MOS ID 1375769.1 and verify if any further tuning can be applied. 

  Mainly the read and write sizes should be set to at least 64KB

– Double check the concurrent manager jobs activities and load on the system. Each concurrent

  the program has a parameter, SHELF_LIFE, which can be used to specify how long the logs and output

  from the program should be kept.

– Optimize log directory access time by reducing the “inode” file entries. In Oracle E-Business Suite

  R12.2, there is a new environment variable, APPLLDM, which was introduced to specify whether log

  and out files are stored in a single directory for all Oracle E-Business Suite products, or in one

  subdirectory per product

– Check Managing Concurrent Manager Log and Out Directories 

  (MOS ID 1616827.1)   

  
  Optimize the Concurrent Manager: Workload Management Strategy

   Avoid Short Sleep Times

– Bind specific jobs to specific time windows

– Profile Option: Concurrent: Active Request Limit can be used to restrict the number of concurrent requests

  that may be run simultaneously by each user

– Specialize if there are too many jobs in a specific category

  Conflict Resolution Manager Sleep Time:

  

– To maximize throughput for jobs that spawn parallel workers (i.e. Auto Invoice, Payroll), consider

  reducing the sleep time of the Conflict Resolution Manager (CRM)

– Default is the 60s, consider 5 or 10 seconds

• Concurrent Manager Sleep Time:

– Define dedicated queues for short and frequent requests and increase the sleep times for managers

  which do not require near real-time job execution

– Reducing sleep time to a very low value may cause excessive CPU utilization

• Transaction Manager Sleep Time:

– Set the profile “Concurrent: Wait for Available TM” to 1 (second) to minimize TM latency. The profile

  sets the total time to wait for a TM before the switchover to the next available TM

– Set Sleep time on Transaction Managers to 30 minutes. this avoids constant polls to check for

  shutdown requests.