Concurrent manger/program related scripts


---------------------------
Concurrent Program Details:
---------------------------

set head off
    set verify off
    set echo off
    set pages 1500
    set linesize 132
    break on row skip 1

    prompt Checking for Concurrent program details run by this process id
    prompt **************************************************************
    prompt
    prompt
    select /*+ CHOOSE*/
      'Node Name..............................: ' || q.node_name                          || chr(10) ||
      'Req id.................................: ' || Request_Id                           || chr(10) ||
      'Requestor..............................: ' || User_Name                            || chr(10) ||
      'Manager................................: ' || Q.User_Concurrent_Queue_Name         || chr(10) ||
      'Status code............................: ' || decode(status_code,'A', 'Waiting','B',
      'Resuming','D', 'Cancelled','E', 'Error', 'G', 'Warning', 'H',
      'On Hold', 'R', 'Normal','S', 'Suspended', 'T', 'Terminating',
                      'W', 'Paused', 'X','Terminated', status_code)                                       || chr(10) ||
      'Phase code.............................: '  || decode(phase_code, 'C', 'Completed',
      'I', 'Inactive', 'P', 'Pending', 'R', 'Running', phase_code)                        || chr(10) ||
      'Priority...............................: ' || Fcr.priority                         || chr(10) ||
      'Program................................: ' || Fcp.User_Concurrent_Program_Name     || chr(10) ||
      'Time so far ...........................: ' || trunc((sysdate-Fcr.actual_start_date)*24*60,2)||' min'|| chr(10) ||
      'Avg execution time in  30 days.........: ' ||  trunc(AVG_TIME,2)  ||' min'          || chr(10) ||
      'Max execution time in 30 days..........: ' ||  trunc(MAX_TIME,2)  ||' min'         || chr(10) ||
      'Fastest execution Time in 30 days......: ' ||trunc( MIN_TIME,2) || ' min'          || chr(10) ||
      'Number of executions in last 30 days...: ' ||occurance                             || chr(10) ||
      'ClientPID..............................: ' || Fcr.OS_PROCESS_ID                    || chr(10) ||
      'ServerPID..............................: ' || Fcr.ORACLE_PROCESS_ID     || chr(10) ||
      'Arguments passed to the program .......: ' ||Fcr.argument_text
                     from apps.Fnd_Concurrent_Requests Fcr,
    apps.Fnd_Concurrent_Programs_vl Fcp,
    apps.Fnd_Oracle_Userid O,
    apps.Fnd_Concurrent_Processes P,
    apps.Fnd_Concurrent_Queues_vl Q,
    apps.Fnd_User,(select
    concurrent_program_id
    ,count(concurrent_program_id) occurance
    ,min(actual_completion_date-actual_start_date)*24*60 MIN_TIME
    ,max(actual_completion_date-actual_start_date)*24*60 MAX_TIME
    , avg(actual_completion_date-actual_start_date)*24*60 AVG_TIME
    from apps.fnd_concurrent_requests
    where status_code='C' and phase_code='C'
    and trunc(actual_start_date)>trunc(sysdate-30)
    group by concurrent_program_id
    having max(actual_completion_date-actual_start_date)*24*60 >5) CREQ
    where Controlling_Manager = Concurrent_Process_ID
    and ( P.Concurrent_Queue_ID=Q.Concurrent_Queue_ID AND P.Queue_Application_ID = Q.Application_ID )
    and O.Oracle_Id = Fcr.Oracle_Id and Request_Id in ('&REQID')
    and( Fcr.Program_Application_Id=Fcp.Application_Id
    and  Fcr.Concurrent_Program_Id=Fcp.Concurrent_Program_Id)
    and Requested_By = User_Id
    and Phase_Code = 'R' and status_code in ('R','T')
    --adding joins with new
    and Fcr.Concurrent_Program_Id=CREQ.Concurrent_Program_Id(+)
                                          Order By   Q.User_Concurrent_Queue_Name,q.node_name, Actual_Start_Date,Request_Id;



--------------------------------------------------------------------------------------------------------------------

Concurrent Program Running Normal:
----------------------------------

REM Script provides the request details corresponding to any Process-id
        REM which can be acquired from the top sessions
        set head on
        set lines 180
        set pages 1000
        set verify off
        undef spid
        column req_id format 99999999999
        column OPID format a10
        column PPID format a8
        column SPID format a8
        column ST_CD format a1+

        column ph_cd format a1
        column CNAME format a30
        column event format a15
        column user_name format a10
        column program format a8
        column serial# format 999999
        column sid format 9999
        column username format a8
        select a.request_id "REQ_ID",a.oracle_process_id "OPID",a.os_process_id "PPID",
        e.user_concurrent_program_name "CNAME",
        f.user_name,a.status_code "ST_CD",a.phase_code "PH_CD", b.username,b.sid,
        b.serial#,b.program,g.event,
        to_char(a.ACTUAL_START_DATE,'MON-DD-HH-MI-SS') START_DATE,
        to_char(a.ACTUAL_COMPLETION_DATE,'MON-DD-HH-MI-SS') COMPL_DATE
        from apps.fnd_concurrent_requests a,(select c.username,c.sid,c.serial#,
                                c.program,d.spid from v$session c, v$process d
                                where c.paddr=d.addr) b,
                                apps.fnd_concurrent_programs_tl e,
                                apps.fnd_user f,
                                v$session_wait g
                                where a.oracle_process_id=b.spid
                                and a.concurrent_program_id=e.concurrent_program_id
                                and e.language='US'
                                and a.requested_by=f.user_id
                                and b.sid=g.sid
                                and a.status_code='R'
                                and a.phase_code='R';
-----------------------------------------------------------------------------------------------
Checking Concurrent Program 								
------------------------------
set pagesize 200 feedback on head on
set linesize 300
Col User_name   form a15        TRUNC
col Time        form a4         TRUNC
col Program     form a54        TRUNC
col program_id  form 999999999          TRUNC
col status      form a17        TRUNC
col Scheduled   form a16        TRUNC
 

select
  /*+ choose */
 U.USER_NAME User_name,
  fcr.request_id request_id,
  to_char(round((nvl(fcr.actual_completion_date,sysdate)-fcr.actual_start_date)*                                                                                        1440)) Time,
  c.concurrent_program_name||' - ' ||
  substr(fcrv.program,1,45) PROGRAM,
  fcr.CONCURRENT_PROGRAM_ID     Program_id,
  fcr.PARENT_REQUEST_ID Parent_id,
  to_char(fcrv.REQUESTED_START_DATE,'DD-MON-RR HH24:MI') Scheduled,
  DECODE(fcr.phase_code ,
        'C','Completed',
        'I','Inactive',
        'P','Pending',
        'R','Running') || ' - ' ||
  DECODE(fcr.status_code,
        'A','Waiting',
        'B','Resuming',
        'C','Normal',
        'D','Cancelled',
        'E','Error',
        'G','Warning',
        'H','On Hold',
        'I','Normal',
        'M','No Manager',
        'P','Scheduled',
        'Q','Standby',
        'R','Normal',
        'S','Suspended',
        'T','Terminating',
        'U','Disabled',
        'W','Paused',
        'X','Terminated',
        'Z',' Waiting') Status
from
  fnd_concurrent_requests fcr,
  fnd_concurrent_programs c,
  fnd_conc_req_summary_v fcrv,
  fnd_user      U
where
  fcr.concurrent_program_id = c.concurrent_program_id
  and fcr.program_application_id = c.application_id
  and fcr.CONCURRENT_PROGRAM_ID = fcrv.CONCURRENT_PROGRAM_ID
  and fcr.REQUEST_ID = fcrv.REQUEST_ID
  and fcrv.REQUESTED_START_DATE < sysdate+10/1440
  and fcr.phase_code in ('P')
  and fcr.HOLD_FLAG != 'Y'
  and fcr.requested_by = U.user_id
order by 1, 2 asc
/

---------------------------------------------------------------------------------
Concurrent program details using spid:
--------------------------------------

set lines 180
set pages 1000
set verify off
undef spid
column req_id format 9999999999
set head on
column OPID format a10
column PPID format a8
column ST_CD format a1
column ph_cd format a1
column CNAME format a15
column event format a15
column user_name format a10
column program format a8
column serial# format 999999
column sid format 9999
          col hrs_running format 999.999
column username format a8
select a.request_id "REQ_ID",a.oracle_process_id "OPID",a.os_process_id "PPID",
e.user_concurrent_program_name "CNAME",
f.user_name,a.status_code "ST_CD",a.phase_code "PH_CD", b.username,b.sid,
b.serial#,b.program,g.event,
to_char(a.ACTUAL_START_DATE,'MON-DD-HH-MI-SS') START_DATE,
to_char(a.ACTUAL_COMPLETION_DATE,'MON-DD-HH-MI-SS') COMPL_DATE,
             (sysdate-actual_start_date)*24 Hrs_Running
from apps.fnd_concurrent_requests a,(select c.username,c.sid,c.serial#,
                        c.program,d.spid from v$session c, v$process d
                        where c.paddr=d.addr
                        and d.spid=&spid) b,
                        apps.fnd_concurrent_programs_tl e,
                        apps.fnd_user f,
                        v$session_wait g
                        where a.oracle_process_id=b.spid
                        and a.concurrent_program_id=e.concurrent_program_id
                        and e.language='US'
                        and a.requested_by=f.user_id
                        and b.sid=g.sid
                        and a.status_code!='C'
order by a.ACTUAL_COMPLETION_DATE desc;

---------------------------------------------------------------------------------

Concurrent Program Name:
------------------------

set linesize 152
set pages 1000
col username for a10
col status for a10
col phase for a10
col PNAME for a15
col mins for 99999
col start_date for a18
col end_date for a18

col request_id for 99999999999
select
b.user_name username,
request_id as REQ_ID,
a.controlling_manager cm,
decode(a.phase_code,
'C','Complete',
'I','Inactive',
'P','Pending',
'R','Running',
'Unknown') phase_code ,
decode(a.status_code,
'C','Normal',
'D','Cancelled',
'E','Error',
'F','Scheduled',
'G','Warning',
'I','Normal',
'M','No Manager',
'Q','Standby',
'R','Normal',
'S','Suspended',
'T','Terminating',
'U','Disabled',
'W','Paused',
'Z','Waiting'
) status_code ,
a.USER_CONCURRENT_PROGRAM_NAME as PNAME,
to_char(a.REQUESTED_START_DATE,'DD-MON-YY HH24-MI-SS') START_DATE,
to_char(a.ACTUAL_COMPLETION_DATE,'DD-MON-YY HH24-MI-SS') END_DATE,
(a.ACTUAL_COMPLETION_DATE - a.REQUESTED_START_DATE)*24*60 "mins"
from apps.fnd_conc_req_summary_v a,apps.fnd_user b
where
-- status_code in ('E') and
upper(a.USER_CONCURRENT_PROGRAM_NAME) like upper('%&str%') and
a.REQUESTED_BY=b.user_id
and rownum<100order by ACTUAL_COMPLETION_DATE;

---------------------------------------------------------------------------------
Steps  to be performed before starting  Concurrent Manager: 
----------------------------------------------------------

Before starting Concurrent Manager, execute the following 2 scripts. Both the scripts 
should      return no rows selected. If they return any rows, please contact Lead (Kesava)  
immediately.      

SELECT
concurrent_queue_name manager,
concurrent_process_id pid,

decode (process_status_code,
'A','Active',
'C','Connecting',
'D','Deactiviating',

'G','Awaiting Discovery',
'K','Terminated',
'M','Migrating',
'P','Suspended',
'R','Running',

'S','Deactivated',
'T','Terminating',
'U','Unreachable',
'Z','Initializing') cm_process_status

FROM fnd_concurrent_queues fcq, fnd_concurrent_processes fcp
WHERE process_status_code not in ('K','S') and
fcq.concurrent_queue_id = fcp.concurrent_queue_id AND
fcq.application_id = fcp.queue_application_id;
 
SELECT concurrent_queue_name manager,

decode (control_code,
'A','Activating',
'B','Activated',
'D','Deactivating',
'E','Deactivated',

'N','Target node/queue unavailable',
'O','Suspending concurrent manager',
'P','Suspended',

'Q','Resuming concurrent manager',
'R','Restarting',
'T','Terminating',

'U','Updating environment information',
'V','Verifying',
'X','Terminated'
) control_code

FROM fnd_concurrent_queues
WHERE control_code not in ('E', 'R', 'X')
AND control_code IS NOT NULL;


(ii) This is the most important point. And an addition to current process we are following. 
While starting CM.. Immediately in another session start monitoring the log file in 
$APPLCSF/$APPLLOG/SID_MMDD.mgr that is getting updated. Log file name would be part of 
output to adcmctl.sh start command. This is to know immediately if there are issues in starting 
Concurrent Manager.
---------------------------------------------------------------------------------
To find all concurrent programs that are executed in last 24 Hours and have taken more than 10 mins to complete. 

---------------------------------------------------------------------------------
select
fcr.request_id,fcp.concurrent_program_name,fct.user_concurrent_program_name,fap.application_short_name,fap.basepath,
fex.executable_name, fex.execution_file_path,fex.execution_file_name,
DECODE (fex.execution_method_code,
'I', 'PL/SQL Stored Procedure',
'H', 'Host',
'S', 'Immediate',
'J', 'Java Stored Procedure',
'K', 'Java concurrent program',
'M', 'Multi Language Function',
'P', 'Oracle reports',
'B', 'Request Set Stage Function',
'A', 'Spawned',
'L', 'SQL*Loader',
'Q', 'SQL*Plus',
'E', 'Pearl concurrent Programm',
'Unkown Type'
) EXECUTION_TYPE,
fcu.user_name,to_char(fcr.request_date,'DD-MON-RR HH24:MI'),to_char(fcr.actual_start_date,'DD-MON-RR HH24:MI'),to_char(fcr.actual_completion_date,'DD-MON-RR HH24:MI'),
decode (fcr.phase_code, 'C', 'Completed',
                                        'I', 'Inactive',
                                        'P', 'Pending',
                                        'R', 'Running'
                                        ) "Phase",
decode ( fcr.status_code, 'A', 'Waiting',
                                          'B', 'Resuming',
                                          'C', 'Normal',
                                          'D', 'Cancelled',
                                          'E', 'Errors',
                                          'G', 'Warning',
                                          'H', 'On Hold',
                                          'I', 'Normal',
                                          'M', 'No Manager',
                                          'P', 'Scheduled',
                                          'Q', 'Standby',
                                          'R', 'Normal',
                                          'S', 'Suspended',
                                          'T', 'Terminating',
                                          'U', 'Disabled',
                                          'W', 'Paused',
                                          'X', 'Terminated') "Status",
trunc( fcr.actual_completion_date - fcr.actual_start_date  ) "Days",
trunc( mod( (fcr.actual_completion_date - fcr.actual_start_date) * 24, 24 ) )  "Hours",
trunc( mod( (fcr.actual_completion_date - fcr.actual_start_date) * 24 * 60, 60 ) )  "Minutes",
trunc( mod( (fcr.actual_completion_date - fcr.actual_start_date) * 24 * 60 * 60, 60 ) ) "Seconds"
from
APPLSYS.fnd_concurrent_requests fcr,
APPLSYS.fnd_concurrent_programs fcp,
APPLSYS.fnd_concurrent_programs_tl fct,
APPLSYS.fnd_user fcu,
APPLSYS.fnd_executables fex,
applsys.fnd_application fap
where
fcp.concurrent_program_name not in ('FNDOAMCOL') and
fcp.concurrent_program_id=fcr.CONCURRENT_PROGRAM_ID and
fcp.concurrent_program_id=fct.CONCURRENT_PROGRAM_ID and
fcr.REQUESTED_BY=fcu.user_id and
fex.executable_id = fcp.executable_id and
fcp.application_id = fap.application_id and
fcr.actual_start_date > (sysdate - 1) and
trunc( mod( (fcr.actual_completion_date - fcr.actual_start_date) * 24 * 60, 60 ) ) > 10
order by fcr.actual_start_date , fcr.actual_completion_date asc; 

-----------------------------------------------------------------------------------------------------
	  

2 comments:

  1. Nice posts with scripts, use full stuff for every one thanks for the post, And you can also check for any issues and tips and trouble shooting related to appsdba 11i and R12 on http://www.appstier.blogspot.in/

    ReplyDelete
  2. Regards
    Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
    LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
    Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, SQL, PL/SQL, D2K at training@oracleappstechnical.com | +91 - 9581017828.

    ReplyDelete

Note: Only a member of this blog may post a comment.