-SYSTEMATIC APPROACH TO FINDOUT DETAILS ON LONG RUNNING REQUESTS-
Step-1
=====
Get the CM Req.ID
Login to DB Node and check for the any locks and blocking sessions first.
##QUERY##
select l1.inst_id,l1.sid, ' IS BLOCKING ' , l2.inst_id,l2.sid from gv$lock l1, gv$lock l2
where l1.block =1 and l2.request > 0
and l1.id1=l2.id1
and l1.id2=l2.id2--);
Step-2
=====
Get the details about the requests using below query.
##QUERY##
set lines 180
set pages 300
col name format a20
col QUEUE for a20
col U_NAME for a20
select fcr.request_id req_id,
substr(fcq.concurrent_queue_name, 1, 20) queue,
to_char(fcr.actual_start_date,'hh24:mi') s_time,
substr(fcr.user_concurrent_program_name, 1, 60) name,
substr(fcr.requestor, 1, 9 ) u_name,
round((sysdate -actual_start_date) *24, 2) elap,
decode(fcr.phase_code,'R','Running','P','Inactive','C','Completed', fcr.phase_code) Phase,
substr(decode( fcr.status_code, 'A', 'WAITING', 'B', 'RESUMING',
'C', 'NORMAL', 'D', 'CANCELLED', 'E', 'ERROR', 'F', 'SCHEDULED',
'G', 'WARNING', 'H', 'ON HOLD', 'I', 'NORMAL', 'M', 'NO MANAGER',
'Q', 'STANDBY', 'R', 'NORMAL', 'S', 'SUSPENDED', 'T', 'TERMINATED',
'U', 'DISABLED', 'W', 'PAUSED', 'X', 'TERMINATED', 'Z', 'WAITING',
'UNKNOWN'), 1, 10)
from
apps.fnd_concurrent_queues fcq,
apps.fnd_concurrent_processes fcp,
apps.fnd_conc_req_summary_v fcr
where fcp.concurrent_queue_id = fcq.concurrent_queue_id
and fcp.queue_application_id = fcq.application_id
and fcr.controlling_manager = fcp.concurrent_process_id
and fcr.request_id = '&RequstID'
order by request_id ;
##QUERY##
select fcr.request_id req_id,
fcr.phase_code||'/'||fcr.status_code sta,
fcr.pid pid,
sess.sid sid,
sess.inst_id inst_id,
fcr.running_time time,
nvl(t.used_urec,0) u_rec,
substrb(fcr.user_desc,1,8) user_desc,
substrb(decode(fcr.pgm_code,
'FNDRSSUB',fcr.pgm_name||'-'||rs.user_request_set_name,
'FNDRSSTG',fcr.pgm_name||'-'||rs.user_request_set_name
||'-'||rss.user_stage_name,
fcr.pgm_name), 1,48) pgm_name
from gv$session sess,
gv$transaction t,
fnd_request_sets_vl rs,
fnd_request_set_stages_vl rss,
(select /*+ ordered
index (r fnd_concurrent_requests_n7)
index (pt fnd_concurrent_programs_tl_u1) */
r.request_id request_id,
r.program_application_id application_id,
c.os_process_id pid,
r.oracle_session_id audsid,
r.concurrent_program_id concurrent_program_id,
p.concurrent_program_name pgm_code,
u.description user_desc,
decode(p.concurrent_program_name,
'FNDRSSUB','SET',
'FNDRSSTG','Set Stage',
pt.user_concurrent_program_name) pgm_name,
r.phase_code phase_code,
r.status_code status_code,
to_char(r.request_date,'yymmdd hh24:mi:ss') request_date, to_char(r.actual_completion_date,'yymmdd hh24:mi:ss') actual_completion_date,
to_char(r.requested_start_date,'yymmdd hh24:mi:ss') requested_start_date,
ceil((nvl(r.actual_completion_date,sysdate)-r.actual_start_date)*1440) running_time,
r.actual_start_date actual_start_date_org,
u.user_id user_id,
u.user_name user_name,
r.argument_text arguments,
decode(p.concurrent_program_name,
'FNDRSSUB',r.argument2,
'FNDRSSTG',r.argument2,
-1) request_set_id,
decode(p.concurrent_program_name,
'FNDRSSTG',r.argument3,
-1) request_set_stage_id
from
fnd_concurrent_requests r,
fnd_concurrent_programs_tl pt,
fnd_concurrent_programs p,
fnd_user u,
fnd_concurrent_processes c
where r.requested_by = u.user_id
and r.program_application_id = pt.application_id
and r.concurrent_program_id = pt.concurrent_program_id
and pt.language = 'US'
and pt.application_id = p.application_id
and pt.concurrent_program_id = p.concurrent_program_id
and r.controlling_manager = c.concurrent_process_id
and r.phase_code = 'R'
and r.status_code = 'R') fcr
where fcr.audsid = sess.audsid(+)
and sess.saddr = t.ses_addr(+)
and fcr.request_set_id = rs.request_set_id(+)
and fcr.request_set_id = rss.request_set_id(+)
and fcr.request_set_stage_id = rss.request_set_stage_id(+)
order by fcr.actual_start_date_org desc, fcr.running_time, fcr.request_id desc;
Step-3
=====
Get the session id from the request ID using below query.
##QUERY##
SELECT ses.sid, ses.serial#
FROM v$session ses, v$process pro
WHERE ses.paddr = pro.addr
AND pro.spid IN (SELECT oracle_process_id FROM fnd_concurrent_requests
WHERE request_id = &request_id);
Step-4
=====
To Check the previous runs of the same program
##QUERY##
set pause off
set pagesize 2000
set linesize 120
set wrap off
column user_concurrent_program_name format a45 noprint
column argument_text format a45 print
column user_name format a15
column start_time format a15
column end_time format a15
column comp_time format 9999.99
select request_id,
user_concurrent_program_name,
to_char(actual_start_date,'DD/MON HH24:MI:SS') START_TIME,
to_char(ACTUAL_COMPLETION_DATE,'DD/MON HH24:MI:SS') END_TIME,
(actual_completion_date-actual_start_date)*24*60 comp_time, argument_text,
user_name,status_code, phase_code
from apps.fnd_concurrent_requests, apps.fnd_concurrent_programs_tl,apps.fnd_user
where
fnd_concurrent_requests.concurrent_program_id=fnd_concurrent_programs_tl.concurrent_program_id
and user_concurrent_program_name like '%Billing and Receipt%'
and fnd_concurrent_programs_tl.language='US'
and requested_by=user_id
order by actual_start_date desc,ACTUAL_COMPLETION_DATE desc;
To View the Child Requests any running under parent concurrent Request
===================================================
set lines 200
col USER_CONCURRENT_PROGRAM_NAME for a40
col PHASE_CODE for a10
col STATUS_CODE for a10
col COMPLETION_TEXT for a20
SELECT sum.request_id,req.PARENT_REQUEST_ID,
sum.user_concurrent_program_name,DECODE(sum.phase_code,'C','Completed',sum.phase_code)
phase_code,DECODE(sum.status_code,'D', 'Cancelled' ,'E', 'Error' , 'G', 'Warning', 'H','On Hold' , 'T', 'Terminating',
'M', 'No Manager' , 'X', 'Terminated', 'C', 'Normal', sum.status_code) status_code, sum.actual_start_date, sum.actual_completion_date, sum.completion_text
FROM apps.fnd_conc_req_summary_v sum, apps.fnd_concurrent_requests req
where req.request_id=sum.request_id and req.PARENT_REQUEST_ID = '&parent_concurrent_request_id';
------------------------------------------------------------------------------------------------------------
From previous runs we can analyse the time taken to execute the program and some other info.
------------------------------------------------------------------------------------------------------------
-TO KNOW THE PENDING CONCURRENT REQUEST-
set pagesize 2000
set linesize 512
column QUEUE_DESCRIPTION format a45
column "CONCURRENT Manager" format a45 HEADING "Concurrent|Manager"
column "Queue ID" format 999999999 HEADING "Concurrent|Queue ID"
column "Apps ID" format 999999 HEADING "Apps|ID"
column NODE format a15
column Phase format a10
column count(1) format 9999999 HEADING "Requests|Count()"
SELECT a.CONCURRENT_QUEUE_ID "Queue ID",
a.QUEUE_APPLICATION_ID "Apps ID",
b.user_CONCURRENT_QUEUE_NAME "Concurrent Manager",
b.TARGET_NODE "NODE",
decode(a.PHASE_CODE, 'P','PENDING',
'R','Running'
) Phase,b.Running_Processes Running,b.Max_Processes Max_processes
,count(1)
FROM apps.FND_CONCURRENT_WORKER_REQUESTS a, apps.fnd_concurrent_queues_vl b
WHERE (a.Phase_Code = 'P' or a.Phase_Code = 'R')
and a.hold_flag != 'Y'
and a.Requested_Start_Date <= SYSDATE
AND ('' IS NULL OR ('' = 'B' AND a.PHASE_CODE = 'R' AND a.STATUS_CODE IN ('I', 'Q')))
and '1' in (0,1,4)
And a.concurrent_queue_id=b.concurrent_queue_id
group by a.CONCURRENT_QUEUE_ID,
a.QUEUE_APPLICATION_ID,
b.user_CONCURRENT_QUEUE_NAME,
b.TARGET_NODE,
a.PHASE_CODE,b.Running_Processes,b.Max_Processes
order by 1;
------------------------------------------------------------------------------------------------------------
Monitor Continuously for Pending Requests
------------------------------------------------------------------------------------------------------------
Comments
Post a Comment