Concurrent manager Overview

What is Concurrent manager?

Batch Processing Sub-System

Programs are run as operating system background processes.

These programs may be written using a variety of Oracle tools, programming languages for executables, or OS scripts.

Internal Concurrent Manager (ICM)

 Controls all other concurrent managers.

It administers the startup and shutdown of managers as defined by their work shift, monitors for process failure, and cleans up if a failure occurs.

Standard Manager

As shipped with Oracle Applications will accept and run any concurrent requests.

Conflict Resolution Manager (CRM)

Enforces rules designed to ensure that incompatible concurrent requests do not run in the same conflict domain 

Output Post Processor (OPP)

Concurrent Processing uses the Output Post Processor (OPP) to enforce Post Processing Output post processor run the XML publisher

Concurrent Manager – Tables

FND_CONCURRENT_REQUESTS

Details of user requests, including status, start date, and completion date

FND_CONCURRENT_PROGRAMS

Details of concurrent programs, including execution method, whether the program is constrained, and whether it must be run alone.

FND_CONCURRENT_PROCESSES

Cross-references between concurrent requests and queues, and a history of concurrent manager processes

FND_CONCURRENT_QUEUES

Information about each of the concurrent manager queues.

Concurrent Request Life Cycle:

Pending / Normal:

This request is waiting for next available concurrent manager

Increase number “processes” of the Concurrent Manager

Pending / Standby

The Request is waiting at CRM due to Conflict Request which is already being run.

Running / Normal

This Request is running normal if If the request is taking longer time, enable Database Trace and tkprof and find out expensive DML

Completed / Error

Check the request log file and take action accordingly

Inactive / No Manager

Manager is not available that manager

Managing Concurrent manger

How many concurrent Programs running to the month

select count(*), trunc(ACTUAL_START_DATE) from apps.fnd_concurrent_requests

group by trunc(ACTUAL_START_DATE)order by trunc(ACTUAL_START_DATE);

SQL> select count(*), trunc(ACTUAL_START_DATE)

from apps.fnd_concurrent_requests

group by trunc(ACTUAL_START_DATE)

order by trunc(ACTUAL_START_DATE);

Day to day concurrent Program history

SELECT TO_CHAR(TRUNC(ACTUAL_START_DATE),'DD-MON-YY DY') STARTDATE,

 COUNT(*) COUNT, ROUND(SUM(ACTUAL_COMPLETION_DATE - ACTUAL_START_DATE) * 24, 2) RUNNING_HOURS,

 ROUND(AVG(ACTUAL_COMPLETION_DATE - ACTUAL_START_DATE) * 24, 2) AVG_RUNNING_HOURS,

 ROUND(SUM(ACTUAL_START_DATE - REQUESTED_START_DATE) * 24, 2) PENDING_HOURS,

 ROUND(AVG(ACTUAL_START_DATE - REQUESTED_START_DATE) * 24, 2) AVG_PENDING_HOURS

 FROM APPLSYS.FND_CONCURRENT_PROGRAMS P,APPLSYS.FND_CONCURRENT_REQUESTS R

 WHERE R.PROGRAM_APPLICATION_ID = P.APPLICATION_ID

 AND R.CONCURRENT_PROGRAM_ID = P.CONCURRENT_PROGRAM_ID

 AND R.STATUS_CODE IN ('C','G')

 AND TRUNC(ACTUAL_COMPLETION_DATE) > TRUNC(SYSDATE-6)

 AND TO_CHAR(TRUNC(ACTUAL_START_DATE),'DD-MON-YY DY') IS NOT NULL

 GROUP BY TRUNC(ACTUAL_START_DATE)

 ORDER BY TRUNC(ACTUAL_START_DATE) ASC;

How to check trace enabling concurrent

select a.CONCURRENT_PROGRAM_ID, b.USER_CONCURRENT_PROGRAM_NAME "Program_Name",a.ENABLE_TRACE, a.CONCURRENT_PROGRAM_NAME "Short_Name", a.APPLICATION_ID from apps.fnd_concurrent_programs a, apps.fnd_concurrent_programs_tl b

where a.CONCURRENT_PROGRAM_ID=b.CONCURRENT_PROGRAM_ID and a.ENABLE_TRACE='Y';

How to find long running concurrent program

SELECT a.request_id,frt.responsibility_name

,c.concurrent_program_name || ': ' || ctl.user_concurrent_program_name

, fu.user_name,a.ARGUMENT_TEXT--,a.logfile_name --,a.phase_code,a.status_code

 ,DECODE(a.phase_code,

            'C','Completed','I','Incactive','P','Pending','R','Running') phase,

    DECODE(a.status_code,

            'D','Cancelled','U','Disabled','E','Error','M','No Manager',

            'R','Normal','I','Normal','C','Normal','H','On Hold','W','Paused',

            'B','Resuming','P','Scheduled','Q','Standby','S','Suspended',

            'X','Terminated','T','Terminating','A','Waiting','Z','Waiting',

            'G','Warning','N/A') status

,TO_CHAR(actual_start_date, 'DD-MON-YY HH24:MI') actual_start_date

,TO_CHAR(actual_completion_date, 'DD-MON-YY HH24:MI') actual_completion_date

--,actual_start_date,actual_completion_date

,(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 = 'C'--OR  a.phase_code = 'R' OR  a.phase_code = 'E'

----AND a.status_code = 'C'--OR a.status_code = 'R'

--and  a.status_code = 'E'

AND a.actual_start_date >= sysdate-100  and a.actual_start_date <= sysdate

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

--and a.request_id='5035452';

--and (nvl(actual_completion_date,sysdate)-actual_start_date)*1440 > 20

and ctl.user_concurrent_program_name='PFC Bundle Invoices for Training Services Customers Program'

--and ctl.user_concurrent_program_name like 'PFC%Active%Users'

--and ctl.user_concurrent_program_name like 'PFC%'

--and ctl.user_concurrent_program_name='PFC DH BudgetHolder Commitments By Tasks'

--and ctl.user_concurrent_program_name='PFC DH BudgetHolder Commitments By Tasks'

--ORDER BY duration_in_hours DESC;

--and ctl.user_concurrent_program_name='PFC Populate Timesheet Exception'

--and ctl.user_concurrent_program_name='PFC UAE EMployee Assignments Conversion'

--ORDER BY actual_completion_date DESC;

order by a.request_id desc;

How to clear scheduled concurrent programs

UPDATE fnd_concurrent_requests

SET phase_code = 'C', status_code = 'X'

WHERE status_code IN ('Q','I')

AND requested_start_date > SYSDATE

AND hold_flag = 'N'

Details of Running Concurrent program information

Select substr(Concurrent_Queue_Name,1,12) Manager,fu.user_name,fcp.user_Concurrent_Program_Name full_name,

substr(Fcp.Concurrent_Program_Name,1,35) short_name,fcr.concurrent_program_id "PROGRAM ID",Request_Id Request,

REQUEST_DATE,

to_char(actual_start_date, ' DAY DD-MON-YY HH:MI AM') ACTUAL_START_DATE ,

--to_char(SYSDATE, 'DAY DD-MON-YY HH:MI AM') CURRENT_TIME,

floor(((ACTUAL_START_DATE-request_date)*24*60*60)/3600)|| ' Hrs ' ||

floor((((ACTUAL_START_DATE-request_date)*24*60*60) -

floor(((ACTUAL_START_DATE-request_date)*24*60*60)/3600)*3600)/60)|| ' Mins ' ||

round((((ACTUAL_START_DATE-request_date)*24*60*60) -

floor(((ACTUAL_START_DATE-request_date)*24*60*60)/3600)*3600 -

(floor((((ACTUAL_START_DATE-request_date)*24*60*60) -

floor(((ACTUAL_START_DATE-request_date)*24*60*60)/3600)*3600)/60)*60) ))|| ' Secs ' "DELAYED_START",

floor(((SYSDATE-ACTUAL_START_DATE)*24*60*60)/3600)|| ' Hrs ' ||

floor((((SYSDATE-ACTUAL_START_DATE)*24*60*60) -

floor(((SYSDATE-ACTUAL_START_DATE)*24*60*60)/3600)*3600)/60)|| ' Mins ' ||

round((((SYSDATE-ACTUAL_START_DATE)*24*60*60) -

floor(((SYSDATE-ACTUAL_START_DATE)*24*60*60)/3600)*3600 -

(floor((((SYSDATE-ACTUAL_START_DATE)*24*60*60) -

floor(((SYSDATE-ACTUAL_START_DATE)*24*60*60)/3600)*3600)/60)*60) ))|| ' Secs ' "RUNNING_SINCE",

argument_text,

vs.sid,vs.serial#,vs.sql_id,vs.event,vs.blocking_session,vs.status,vs.osuser,

vs.machine,vs.program,vs.module,vs.action,

--Status_code,

fcr.ofile_size,

fcr.lfile_size

from

apps.Fnd_Concurrent_Queues Fcq,

apps.Fnd_Concurrent_Requests Fcr,

apps.Fnd_Concurrent_Programs_vl Fcp,

apps.Fnd_User Fu,

apps.Fnd_Concurrent_Processes Fpro,

v$session vs,

v$process vp

where

Phase_Code = 'R'

And Status_Code <> 'W'

And Fcr.Controlling_Manager = Concurrent_Process_Id

And (Fcq.Concurrent_Queue_Id = Fpro.Concurrent_Queue_Id   

And Fcq.Application_Id      = Fpro.Queue_Application_Id )

And (Fcr.Concurrent_Program_Id = Fcp.Concurrent_Program_Id

And Fcr.Program_Application_Id = Fcp.Application_Id )    

And Fcr.Requested_By = User_Id                            

And fcr.ORACLE_PROCESS_ID=vp.sPID                         

And vs.PADDR=vp.addr

--AND fcp.user_Concurrent_Program_Name='Inventory transaction worker'

--AND fu.USER_NAME='502525468'

--AND FCR.ACTUAL_START_DATE>SYSDATE-2/24

--AND fcr.argument_text like '3831511256, 3, ,%'

order by fcp.user_Concurrent_Program_Name,ACTUAL_START_DATE desc;