---------------------------
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;
-----------------------------------------------------------------------------------------------------
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.