How to resolve long running concurrent request in oracle apps
Here we will be discuss about how to resolve Long running concurrent requests in oracle apps. Long running concurrent requests can impact the system performance so we need to keep monitor on the concurrent requests which are keep running from log time but to identify the long running concurrent requests is not the task , We also need to identify the reason for this. here below I will share the most common ways to resolve long running concurrent request in oracle apps.
Common Methods to resolve long running concurrent request in oracle apps
1. There could be the reason , Your concurrent request report query is not fine tune. so we need to generate the TK proof for the Concurrent Program and analyze the all parts of the report where this report is taking so much time so that we can work on that part of the report to more fine tune this report query.
2.We need to check is there any database lock happened for this Concurrent request or session id.
3.We Can kill the session for this report and then manually complete this Concurrent request to be completed.
Find Long Running concurrent Requests with Session and Serial Id.
select distinct vp.spid "SPID",qt.user_concurrent_queue_name "Queue Name"
,c2.user_concurrent_program_name "Concurrent Program Name"
,a.request_id "Request Id"
,decode(a.parent_request_id,-1,NULL,a.parent_request_id) "Parent Req"
,FLV.meaning
,FLVS.meaning
,a.argument_text
,vs.inst_id
,vs.sid "SID"
,vs.serial# "Serial#"
,TO_CHAR(actual_start_date, 'DD-MON-YYYY HH24:MI:SS') "Start Date"
,round((nvl(actual_completion_date,sysdate)-actual_start_date)*1440,2) "TimeSpent"
,u.user_name
from APPS.fnd_Concurrent_requests a,APPS.fnd_concurrent_processes b
,APPS.fnd_concurrent_queues q
,APPS.fnd_concurrent_queues_tl qt
,APPS.fnd_concurrent_programs_tl c2
,APPS.fnd_concurrent_programs c
,APPS.FND_LOOKUP_VALUES FLV
,APPS.FND_LOOKUP_VALUES FLVS
,APPS.FND_USER u
,gv$session vs
,gv$process vp
where a.controlling_manager = b.concurrent_process_id
and a.concurrent_program_id = c.concurrent_program_id
and u.user_id=a.requested_by
and a.phase_code=FLV.Lookup_Code
and FLV.Lookup_Type='CP_PHASE_CODE'
and round((nvl(actual_completion_date,sysdate)-actual_start_date)*1440,2) > :P_TIME_LIMIT
and FLV.language='US'
and a.status_code=FLVS.Lookup_Code
and FLVS.Lookup_Type='CP_STATUS_CODE'
and FLVS.language='US'
and FLVS.view_application_id=0
and b.queue_application_id = q.application_id
and b.concurrent_queue_id = q.concurrent_queue_id
and q.application_id = qt.application_id
and qt.language='US'
and q.concurrent_queue_id = qt.concurrent_queue_id
and c2.language = 'US'
and a.oracle_process_id = vp.spid
and vs.paddr = vp.addr (+)
and a.status_code='R'
and vs.inst_id=vp.inst_id
and a.program_application_id = c.application_id
and c2.concurrent_program_id = c.concurrent_program_id
and a.phase_code in ('I','P','R','T')
order by 1
,c2.user_concurrent_program_name "Concurrent Program Name"
,a.request_id "Request Id"
,decode(a.parent_request_id,-1,NULL,a.parent_request_id) "Parent Req"
,FLV.meaning
,FLVS.meaning
,a.argument_text
,vs.inst_id
,vs.sid "SID"
,vs.serial# "Serial#"
,TO_CHAR(actual_start_date, 'DD-MON-YYYY HH24:MI:SS') "Start Date"
,round((nvl(actual_completion_date,sysdate)-actual_start_date)*1440,2) "TimeSpent"
,u.user_name
from APPS.fnd_Concurrent_requests a,APPS.fnd_concurrent_processes b
,APPS.fnd_concurrent_queues q
,APPS.fnd_concurrent_queues_tl qt
,APPS.fnd_concurrent_programs_tl c2
,APPS.fnd_concurrent_programs c
,APPS.FND_LOOKUP_VALUES FLV
,APPS.FND_LOOKUP_VALUES FLVS
,APPS.FND_USER u
,gv$session vs
,gv$process vp
where a.controlling_manager = b.concurrent_process_id
and a.concurrent_program_id = c.concurrent_program_id
and u.user_id=a.requested_by
and a.phase_code=FLV.Lookup_Code
and FLV.Lookup_Type='CP_PHASE_CODE'
and round((nvl(actual_completion_date,sysdate)-actual_start_date)*1440,2) > :P_TIME_LIMIT
and FLV.language='US'
and a.status_code=FLVS.Lookup_Code
and FLVS.Lookup_Type='CP_STATUS_CODE'
and FLVS.language='US'
and FLVS.view_application_id=0
and b.queue_application_id = q.application_id
and b.concurrent_queue_id = q.concurrent_queue_id
and q.application_id = qt.application_id
and qt.language='US'
and q.concurrent_queue_id = qt.concurrent_queue_id
and c2.language = 'US'
and a.oracle_process_id = vp.spid
and vs.paddr = vp.addr (+)
and a.status_code='R'
and vs.inst_id=vp.inst_id
and a.program_application_id = c.application_id
and c2.concurrent_program_id = c.concurrent_program_id
and a.phase_code in ('I','P','R','T')
order by 1
--ALTER SYSTEM KILL SESSION 'SID, serial#' IMMEDIATE;
ALTER SYSTEM KILL SESSION '523, 4965' IMMEDIATE;
ALTER SYSTEM KILL SESSION '523, 4965' IMMEDIATE;
update fnd_concurrent_requests
set status_code='X',
set status_code='X',
phase_code='C'
where request_id=:P_CONCURRENT_REQUEST_ID
where request_id=:P_CONCURRENT_REQUEST_ID
0 comments:
Post a Comment