Queries on Concurrent Manager Long Running Request and its Diag.
1.How to Determine Which Manager Ran a Specific Concurrent Request?
col USER_CONCURRENT_QUEUE_NAME for a100
select b.USER_CONCURRENT_QUEUE_NAME from fnd_concurrent_processes a,
fnd_concurrent_queues_vl b, fnd_concurrent_requests c
where a.CONCURRENT_QUEUE_ID = b.CONCURRENT_QUEUE_ID
and a.CONCURRENT_PROCESS_ID = c.controlling_manager
and c.request_id = '&conc_reqid';
2.Concurrent manager status for a given sid?
col MODULE for a20
col OSUSER for a10
col USERNAME for a10
set num 10
col MACHINE for a20
set lines 200
col SCHEMANAME for a10
select s.sid,s.serial#,p.spid os_pid,s.status, s.osuser,s.username, s.MACHINE,s.MODULE,
s.SCHEMANAME,
s.action from gv$session s, gv$process p WHERE s.paddr = p.addr and s.sid = '&oracle_sid';
3. Find out request id from Oracle_Process Id:
select REQUEST_ID,ORACLE_PROCESS_ID,OS_PROCESS_Id from apps.fnd_concurrent_requests where
ORACLE_PROCESS_ID='&a';
4.To find sid,serial# for a given concurrent request id?
set lines 200
SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.SPID ,d.inst_id
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
gv$process c,
gv$session d
WHERE a.controlling_manager = b.concurrent_process_id
AND c.pid = b.oracle_process_id
AND b.session_id=d.audsid
AND a.request_id = &Request_ID
AND a.phase_code = 'R';
5.To find concurrent program name,phase code,status code for a given request id?
SELECT request_id, user_concurrent_program_name, DECODE(phase_code,'C','Completed',phase_code)
phase_code, DECODE(status_code,'D', 'Cancelled' ,
'E', 'Error' , 'G', 'Warning', 'H','On Hold' , 'T', 'Terminating', 'M', 'No Manager' , 'X',
'Terminated', 'C', 'Normal', status_code) status_code, to_char(actual_start_date,'dd-mon-
yy:hh24:mi:ss') Start_Date, to_char(actual_completion_date,'dd-mon-yy:hh24:mi:ss'),
completion_text FROM apps.fnd_conc_req_summary_v WHERE request_id = '&req_id' ORDER BY 6 DESC;
6.To find the sql query for a given concurrent request sid?
select sid,sql_text from gv$session ses, gv$sqlarea sql where
ses.sql_hash_value = sql.hash_value(+) and ses.sql_address = sql.address(+) and
ses.sid='&oracle_sid'
/
7. To find child requests
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';
8. Cancelling Concurrent request :
update fnd_concurrent_requests
set status_code='D', phase_code='C'
where request_id=&req_id;
9. Kill sessions program wise
select 'ALTER SYSTEM KILL SESSION '''||sid||','||serial#||''' immediate;' from v$session where
MODULE like '';
10 .Concurrent Request running by SID
SELECT a.request_id,
d.sid as Oracle_SID,
d.serial#,
d.osuser,
d.process,
c.SPID as OS_Process_ID
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
gv$process c,
gv$session d
WHERE a.controlling_manager = b.concurrent_process_id
AND c.pid = b.oracle_process_id
AND b.session_id=d.audsid
AND d.sid = &SID;
11. Find out request id from Oracle_Process Id:
select REQUEST_ID,ORACLE_PROCESS_ID,OS_PROCESS_Id from fnd_concurrent_requests where
ORACLE_PROCESS_ID='&a';
12. Oracle Concurrent Request Error Script (requests which were error ed out)
SELECT a.request_id "Req Id"
,a.phase_code,a.status_code
, actual_start_date
, actual_completion_date
,c.concurrent_program_name || ': ' || ctl.user_concurrent_program_name "program"
FROM APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_processes b
,applsys.fnd_concurrent_queues q
,APPLSYS.fnd_concurrent_programs c
,APPLSYS.fnd_concurrent_programs_tl ctl
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 a.status_code = 'E'
AND a.phase_code = 'C'
AND actual_start_date > sysdate - 2
AND b.queue_application_id = q.application_id
AND b.concurrent_queue_id = q.concurrent_queue_id
AND ctl.concurrent_program_id = c.concurrent_program_id
AND ctl.LANGUAGE = 'US'
ORDER BY 5 DESC;
13. Request submitted by User
SELECT
user_concurrent_program_name,
request_date,
request_id,
phase_code,
status_code
FROM
fnd_concurrent_requests fcr,
fnd_concurrent_programs_tl fcp,
fnd_responsibility_tl fr,
fnd_user fu
WHERE
fcr.CONCURRENT_PROGRAM_ID = fcp.concurrent_program_id
and fcr.responsibility_id = fr.responsibility_id
and fcr.requested_by = fu.user_id
and user_name = '&user'
AND actual_start_date > sysdate - 1
ORDER BY REQUEST_DATE Asc;
14.Concurrent Program enable with trace
col User_Program_Name for a40
col Last_Updated_By for a30
col DESCRIPTION for a30
SELECT A.CONCURRENT_PROGRAM_NAME "Program_Name",
SUBSTR(A.USER_CONCURRENT_PROGRAM_NAME,1,40) "User_Program_Name",
SUBSTR(B.USER_NAME,1,15) "Last_Updated_By",
SUBSTR(B.DESCRIPTION,1,25) DESCRIPTION
FROM APPS.FND_CONCURRENT_PROGRAMS_VL A, APPLSYS.FND_USER B
WHERE A.ENABLE_TRACE='Y'
AND A.LAST_UPDATED_BY=B.USER_ID
-----------------------------------------------------------------------------
## QUERY TO FIND CURRENTLY LONG RUNNING REQUEST FOR MORE THAN 1 HOUR ##
-----------------------------------------------------------------------------
SELECT fcr.request_id, fcr.actual_start_date, floor((SYSDATE - actual_start_date)*24) || ' HOURS ' || mod(floor((SYSDATE- actual_start_date)*24*60),60) || ' MINS ' "Duration",
fcpt.user_concurrent_program_name,fu.user_name,fcr.argument_text,fcr.oracle_process_id,fcr.oracle_session_id,fcr.os_process_id,fcr.root_request_id
FROM apps.fnd_concurrent_requests fcr,apps.fnd_concurrent_programs_tl fcpt,
apps.fnd_user fu
WHERE
fcr.concurrent_program_id = fcpt.concurrent_program_id
and
fcr.requested_by=fu.user_id
and
actual_start_date LIKE SYSDATE
AND (SYSDATE - actual_start_date) * 24 > 1
AND status_code ='R'
AND phase_code ='R';
-------------------------------------------------------------------------
## To check Program Past Runtime
## Need Program Name as input # if you know Exact Request Name ##
--------------------------------------------------------------------------
SELECT fcr.request_id, fcr.actual_start_date, fcr.actual_completion_date,
floor((actual_completion_date - actual_start_date)*24) || ' HOURS ' || mod(floor((actual_completion_date- actual_start_date)*24*60),60) || ' MINS ' "Duration",
fcpt.user_concurrent_program_name, fu.user_name
FROM apps.fnd_concurrent_requests fcr,apps.fnd_concurrent_programs_tl fcpt,
apps.fnd_user fu
WHERE
fcr.concurrent_program_id = fcpt.concurrent_program_id
and
fcr.requested_by=fu.user_id
and
fcpt.user_concurrent_program_name='&program_name';
----------------------------------------------------------------
## To check Program Past Runtime , need Program Name as input
## if you know via Wild Character Search ##
-----------------------------------------------------------------
SELECT fcr.request_id, fcr.actual_start_date, fcr.actual_completion_date,
floor((actual_completion_date - actual_start_date)*24) || ' HOURS ' || mod(floor((actual_completion_date- actual_start_date)*24*60),60) || ' MINS ' "Duration",
fcpt.user_concurrent_program_name, fu.user_name
FROM apps.fnd_concurrent_requests fcr,apps.fnd_concurrent_programs_tl fcpt,
apps.fnd_user fu
WHERE
fcr.concurrent_program_id = fcpt.concurrent_program_id
and
fcr.requested_by=fu.user_id
and
fcpt.user_concurrent_program_name like '&program_name%';
-------------------------------------------------------------------
## To find Detailed Request that are currently Running ##
## This will help get to what amoung those long request doing ##
-------------------------------------------------------------------
SELECT a.request_id
,a.oracle_process_id "SPID"
,frt.responsibility_name
,c.concurrent_program_name || ': ' || ctl.user_concurrent_program_name
,a.description
,a.ARGUMENT_TEXT
,b.node_name
,b.db_instance
,a.logfile_name
,a.logfile_node_name
,q.concurrent_queue_name
,a.phase_code,a.status_code, a.completion_text
, actual_start_date
, actual_completion_date
, fu.user_name
,(nvl(actual_completion_date,sysdate)-actual_start_date)*1440 mins
,(SELECT avg(nvl(a2.actual_completion_date-a2.actual_start_date,0))*1440 avg_run_time
FROM APPLSYS.fnd_Concurrent_requests a2,
APPLSYS.fnd_concurrent_programs c2
WHERE c2.concurrent_program_id = c.concurrent_program_id
AND a2.concurrent_program_id = c2.concurrent_program_id
AND a2.program_application_id = c2.application_id
AND a2.phase_code || '' = 'C') avg_mins
,round((actual_completion_date - requested_start_date),2) * 24 duration_in_hours
FROM APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_processes b
,applsys.fnd_concurrent_queues q
,APPLSYS.fnd_concurrent_programs c
,APPLSYS.fnd_concurrent_programs_tl ctl
,apps.fnd_user fu
,apps.FND_RESPONSIBILITY_TL frt
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 a.phase_code = 'R'
AND a.status_code = 'R'
AND b.queue_application_id = q.application_id
AND b.concurrent_queue_id = q.concurrent_queue_id
AND ctl.concurrent_program_id = c.concurrent_program_id
AND a.requested_by = fu.user_id
AND a.responsibility_id = frt.responsibility_id
ORDER BY a.actual_start_date DESC;
------------------------------------------------------------------------------
## Followed by Take SPID from 1st Query and run 2nd Query ##
## Gives detail of That Session doing at Backend Database, the SQL Query running its waits ##
-----------------------------------------------------------------------------
SELECT b.sid, b.serial#, a.spid, b.program, b.osuser, b.machine,
b.TYPE, b.event, b.action, b.p1text, b.p2text, b.p3text, b.state, c.sql_text,b.logon_time
FROM v$process a, gv$session b, v$sqltext c
WHERE a.addr=b.paddr
AND b.sql_hash_value = c.hash_value
AND b.STATUS = 'ACTIVE'
AND a.spid = '13947'
ORDER BY a.spid, c.piece;
--------------------------------------------------------------------------------
Concurrent History Details
---------------------------------------------------------------------------------
select a.request_id "rqst_id"
,a.concurrent_program_id "conc_pgm_id"
,a.status_code||decode(a.resubmit_interval, null, null, '*') "stat_cd"
,b.os_process_id "appl_os_pid"
,v.spid "DB_OS_vspid"
,v.Inst_id "Instance"
,v.sid "vsid"
,v.action
,v.serial# "vserial#"
,a.priority "pri"
,substr(d.concurrent_queue_name,1,4) "que"
,(nvl(actual_completion_date,sysdate)-actual_start_date)*1440 "time"
--,round(f.time_avg,2) "avgtime"
,e.user_name "usr"
,decode(sign(a.requested_start_date - a.request_date),1,
round(((sysdate-requested_start_date) * 1440),0),
round(((sysdate-request_date) * 1440),0)) "walltime"
,c.concurrent_program_name||' - '||ct.user_concurrent_program_name "program"
,a.phase_code "Phase"
,a.status_code "Status"
from APPLSYS.fnd_concurrent_requests a
,APPLSYS.fnd_concurrent_processes b
,APPLSYS.fnd_concurrent_programs c
,APPLSYS.fnd_concurrent_programs_tl ct
,APPLSYS.fnd_concurrent_queues d
,APPLSYS.fnd_user e
--,APPLSYS.fcr_stats f
,(select s.inst_id,s.sid, s.serial#, p.spid,s.action from gv$session s, v$process p where s.paddr = p.addr) v
where a.controlling_manager = b.concurrent_process_id
and a.concurrent_program_id = c.concurrent_program_id
--and a.concurrent_program_id = f.concurrent_program_id(+)
--and a.program_application_id = f.application_id(+)
and a.program_application_id = c.application_id
and a.concurrent_program_id = ct.concurrent_program_id
and a.program_application_id = ct.application_id
and b.concurrent_queue_id = d.concurrent_queue_id
and a.requested_by = e.user_id
and a.phase_code in ('R','T')
and a.oracle_process_id=v.spid(+)
order by 10 asc
No comments:
Post a Comment