Tuesday, 24 October 2017

Query 1: To know the blocking sessions details.

select l1.sid, v1.username, v1.CLIENT_IDENTIFIER, v1.program,v1.module,v1.action,v1.last_call_et,   v1.status,  ' IS BLOCKING',
l2.sid blocked_sid, v1.username blocked_username, v2.CLIENT_IDENTIFIER blocked_CLIENT_IDENTIFIER, v2.program blocked_program,v2.module blocked_module,v2.action blocked_action,v2.last_call_et blocked_last_call_et, v2.status blocked_status
from v$lock l1, v$lock l2, v$session v1, v$session v2
where l1.block =1
and l2.request > 0
and l1.id1=l2.id1
and l1.id2=l2.id2
and  l1.sid = v1.sid
and  l2.sid = v2.sid
order by l1.sid

Query 2: To get the long running concurrent program details.

select distinct q.concurrent_queue_name || ' - ' || target_node qname
,a.request_id "Req Id",  (select user_name from apps.fnd_user where user_id = a.requested_by)requested_by
,(select attribute1 from apps.hr_all_organization_units where organization_id =a.org_id)entity,
(select responsibility_name from apps.fnd_responsibility_vl where responsibility_id =a.responsibility_id)responsibility_name
,decode(a.parent_request_id,-1,null,a.parent_request_id) "Parent"
,a.concurrent_program_id "Prg Id"
,a.phase_code,a.status_code
,nvl(a.os_process_id,b.os_process_id) "OS"
,vs.sid
,vp.spid
,round((nvl(actual_completion_date,sysdate)-actual_start_date)*1440,2) "Time"
,c.concurrent_program_name||' - '||
c2.user_concurrent_program_name "program", a.argument_text ,a.concurrent_program_id,actual_start_date
from applsys.fnd_concurrent_requests a,applsys.fnd_concurrent_processes b
,applsys.fnd_concurrent_queues q
,applsys.fnd_concurrent_programs_tl c2
,applsys.fnd_concurrent_programs c
,gv$session vs
,gv$process vp
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 c2.concurrent_program_id = c.concurrent_program_id
and a.phase_code in ('I','P','R','T')
and b.queue_application_id = q.application_id
and b.concurrent_queue_id = q.concurrent_queue_id
and c2.language = 'US'
and vs.process (+) = b.os_process_id
and vs.paddr = vp.addr (+)
and vs.inst_id = vp.inst_id
--and vs.sid in (3655,1243)
order by 1,2,3

No comments:

Post a Comment