Useful Queries for Checking Concurrent Requests and Programs
As an Apps DBA we have to monitor the Concurrent jobs status and details frequently,This can be done with the help of the below queries.
-- Concurrent Program Queries for Apps DBA:
-- Query 1:For checking the locks in concurrent jobs
SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess,inst_id,id1, id2, lmode, request, type FROM gV$LOCK
WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM gV$LOCK WHERE request>0) ORDER BY id1,request;
-- Query 2:For checking the concurrent programs running currently with Details of Processed time-- and Start Date
SELECT DISTINCT c.USER_CONCURRENT_PROGRAM_NAME,round(((sysdate-a.actual_start_date)*24*60*60/60),2) AS Process_time,
a.request_id,a.parent_request_id,a.request_date,a.actual_start_date,a.actual_completion_date,(a.actual_completion_date-a.request_date)*24*60*60 AS end_to_end,
(a.actual_start_date-a.request_date)*24*60*60 AS lag_time,d.user_name, a.phase_code,a.status_code,a.argument_text,a.priority
FROM apps.fnd_concurrent_requests a,apps.fnd_concurrent_programs b,apps.FND_CONCURRENT_PROGRAMS_TL c,apps.fnd_user d
WHERE a.concurrent_program_id=b.concurrent_program_id AND b.concurrent_program_id=c.concurrent_program_id AND
a.requested_by=d.user_id AND status_code='R' order by Process_time desc;
-- Query 3:For checking last run of a Concurrent Program along with Processed time
-- Useful to find the Details of Concurrent programs which run daily and comparison purpose
SELECT DISTINCT c.USER_CONCURRENT_PROGRAM_NAME,
round(((a.actual_completion_date-a.actual_start_date)*24*60*60/60),2) AS Process_time,
a.request_id,a.parent_request_id,To_Char(a.request_date,'DD-MON-YY HH24:MI:SS'),To_Char(a.actual_start_date,'DD-MON-YY HH24:MI:SS'),
To_Char(a.actual_completion_date,'DD-MON-YY HH24:MI:SS'), (a.actual_completion_date-a.request_date)*24*60*60 AS end_to_end,
(a.actual_start_date-a.request_date)*24*60*60 AS lag_time,
d.user_name, a.phase_code,a.status_code,a.argument_text,a.priority
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_programs b ,
apps.FND_CONCURRENT_PROGRAMS_TL c,
apps.fnd_user d
WHERE a.concurrent_program_id= b.concurrent_program_id AND
b.concurrent_program_id=c.concurrent_program_id AND
a.requested_by =d.user_id AND
-- trunc(a.actual_completion_date) = '24-AUG-2005'
c.USER_CONCURRENT_PROGRAM_NAME='Incentive Compensation Analytics - ODI' -- and argument_text like '%, , , , ,%';
-- and status_code!='C'
-- Query 4:For Checking the last run of concurrent Program.
- Use below query to check all the concurrent request running which may refer given package
-- This is very useful check before compiling any package on given instance.
-- The query can be modified as per requirement.
-- Remove FND_CONCURRENT_REQUESTS table and joins to check all program dependent on given package.
SELECT
FCR.REQUEST_ID
,FCPV.USER_CONCURRENT_PROGRAM_NAME
,FCPV.CONCURRENT_PROGRAM_NAME
,FCPV.CONCURRENT_PROGRAM_ID
,FCR.STATUS_CODE
,FCR.PHASE_CODE
FROM FND_CONCURRENT_PROGRAMS_VL FCPV
,FND_EXECUTABLES FE
,SYS.DBA_DEPENDENCIES DD
,FND_CONCURRENT_REQUESTS FCR
WHERE FCPV.EXECUTABLE_ID = FE.EXECUTABLE_ID
AND FE.EXECUTION_METHOD_CODE = 'I'
AND SUBSTR(FE.EXECUTION_FILE_NAME,1,INSTR(FE.EXECUTION_FILE_NAME, '.', 1, 1) - 1) = UPPER(DD.NAME)
AND DD.REFERENCED_TYPE IN ('VIEW', 'TABLE', 'TRIGGER', 'PACKAGE') -- add as required
--AND referenced_owner = 'XXCUS'
AND DD.REFERENCED_NAME = UPPER('&Package_name')
AND FCR.CONCURRENT_PROGRAM_ID = FCPV.CONCURRENT_PROGRAM_ID
AND fcr.phase_code NOT IN ( 'C','P');
-- Query 5:The following query will display the time taken to execute the concurrent Programs
--for a particular user with the latest concurrent programs sorted in least time taken
-- to complete the request.
SELECT
f.request_id ,
pt.user_concurrent_program_name user_conc_program_name,
f.actual_start_date start_on,
f.actual_completion_date end_on,
floor(((f.actual_completion_date-f.actual_start_date)
*24*60*60)/3600)
|| ' HOURS ' ||
floor((((f.actual_completion_date-f.actual_start_date)
*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)
*24*60*60)/3600)*3600)/60)
|| ' MINUTES ' ||
round((((f.actual_completion_date-f.actual_start_date)
*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)
*24*60*60)/3600)*3600 -
(floor((((f.actual_completion_date-f.actual_start_date)
*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)
*24*60*60)/3600)*3600)/60)*60) ))
|| ' SECS ' time_difference,
p.concurrent_program_name concurrent_program_name,
decode(f.phase_code,'R','Running','C','Complete',f.phase_code) Phase,
f.status_code
from apps.fnd_concurrent_programs p,
apps.fnd_concurrent_programs_tl pt,
apps.fnd_concurrent_requests f
where f.concurrent_program_id = p.concurrent_program_id
and f.program_application_id = p.application_id
and f.concurrent_program_id = pt.concurrent_program_id
and f.program_application_id = pt.application_id
AND pt.language = USERENV('Lang')
and f.actual_start_date is not null
order by
f.actual_start_date desc;
--Query 6: By using the below Query we can get sid,serial#,spid of the concurrent Request..
SELECT a.request_id, d.sid, d.serial# , c.SPID
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
v$process c,
v$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';
--Query 7: By using below Concurrent Manager and Program rules...
--Gives Detail of the Concurrent_queue_name and User_concurrent_program_name
SELECT b.concurrent_queue_name, c.user_concurrent_program_name
FROM FND_CONCURRENT_QUEUE_CONTENT a, fnd_concurrent_queues b, fnd_concurrent_programs_vl c
WHERE a.queue_application_id = 283
and a.concurrent_queue_id = b.concurrent_queue_id
and a.type_id = c.concurrent_program_id
order by decode(INCLUDE_FLAG, 'I', 1, 2), type_code;
--Query 8: Gives Details of Running Concurrent jobs
SELECT DISTINCT c.USER_CONCURRENT_PROGRAM_NAME,
round(((sysdate-a.actual_start_date)*24*60*60/60),2) AS Process_time,
a.request_id,a.parent_request_id,a.request_date,a.actual_start_date,a.actual_completion_date,
(a.actual_completion_date-a.request_date)*24*60*60 AS end_to_end,
(a.actual_start_date-a.request_date)*24*60*60 AS lag_time,
d.user_name, a.phase_code,a.status_code,a.argument_text,a.priority
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_programs b ,
apps.FND_CONCURRENT_PROGRAMS_TL c,
apps.fnd_user d
WHERE a.concurrent_program_id=b.concurrent_program_id AND
b.concurrent_program_id=c.concurrent_program_id AND
a.requested_by=d.user_id AND
status_code='R' order by Process_time desc;
-- Query 9: Gives detail of Concurrent job completed and pending
SELECT
FCR.REQUEST_ID
,FCPV.USER_CONCURRENT_PROGRAM_NAME
,FCPV.CONCURRENT_PROGRAM_NAME
,FCPV.CONCURRENT_PROGRAM_ID
,FCR.STATUS_CODE
,FCR.PHASE_CODE
FROM FND_CONCURRENT_PROGRAMS_VL FCPV
,FND_EXECUTABLES FE
,SYS.DBA_DEPENDENCIES DD
,FND_CONCURRENT_REQUESTS FCR
WHERE FCPV.EXECUTABLE_ID = FE.EXECUTABLE_ID
AND FE.EXECUTION_METHOD_CODE = 'I'
AND SUBSTR(FE.EXECUTION_FILE_NAME,1,INSTR(FE.EXECUTION_FILE_NAME, '.', 1, 1) - 1) = UPPER(DD.NAME)
AND DD.REFERENCED_TYPE IN ('VIEW', 'TABLE', 'TRIGGER', 'PACKAGE') -- add as required
--AND referenced_owner = 'XXCUS'
AND DD.REFERENCED_NAME = UPPER('&Package_name')
AND FCR.CONCURRENT_PROGRAM_ID = FCPV.CONCURRENT_PROGRAM_ID
AND fcr.phase_code NOT IN ( 'C','P');
-- Query 10:Gives Detail of Running and Completed Concurrent jobs with Start date and end date
-- Latest one at the top
select
f.request_id ,
pt.user_concurrent_program_name user_conc_program_name,
f.actual_start_date start_on,
f.actual_completion_date end_on,
floor(((f.actual_completion_date-f.actual_start_date)
*24*60*60)/3600)
|| ' HOURS ' ||
floor((((f.actual_completion_date-f.actual_start_date)
*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)
*24*60*60)/3600)*3600)/60)
|| ' MINUTES ' ||
round((((f.actual_completion_date-f.actual_start_date)
*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)
*24*60*60)/3600)*3600 -
(floor((((f.actual_completion_date-f.actual_start_date)
*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)
*24*60*60)/3600)*3600)/60)*60) ))
|| ' SECS ' time_difference,
p.concurrent_program_name concurrent_program_name,
decode(f.phase_code,'R','Running','C','Complete',f.phase_code) Phase,
f.status_code
from apps.fnd_concurrent_programs p,
apps.fnd_concurrent_programs_tl pt,
apps.fnd_concurrent_requests f
where f.concurrent_program_id = p.concurrent_program_id
and f.program_application_id = p.application_id
and f.concurrent_program_id = pt.concurrent_program_id
and f.program_application_id = pt.application_id
AND pt.language = USERENV('Lang')
and f.actual_start_date is not null
order by
f.actual_start_date desc;
--- Query 11 wait events details related with Concurrent programs
SELECT s.saddr, s.SID, s.serial#, s.audsid, s.paddr, s.user#, s.username,
s.command, s.ownerid, s.taddr, s.lockwait, s.status, s.server,
s.schema#, s.schemaname, s.osuser, s.process, s.machine, s.terminal,
UPPER (s.program) program, s.TYPE, s.sql_address, s.sql_hash_value,
s.sql_id, s.sql_child_number, s.sql_exec_start, s.sql_exec_id,
s.prev_sql_addr, s.prev_hash_value, s.prev_sql_id,
s.prev_child_number, s.prev_exec_start, s.prev_exec_id,
s.plsql_entry_object_id, s.plsql_entry_subprogram_id,
s.plsql_object_id, s.plsql_subprogram_id, s.module, s.module_hash,
s.action, s.action_hash, s.client_info, s.fixed_table_sequence,
s.row_wait_obj#, s.row_wait_file#, s.row_wait_block#,
s.row_wait_row#, s.logon_time, s.last_call_et, s.pdml_enabled,
s.failover_type, s.failover_method, s.failed_over,
s.resource_consumer_group, s.pdml_status, s.pddl_status, s.pq_status,
s.current_queue_duration, s.client_identifier,
s.blocking_session_status, s.blocking_instance, s.blocking_session,
s.seq#, s.event#, s.event, s.p1text, s.p1, s.p1raw, s.p2text, s.p2,
s.p2raw, s.p3text, s.p3, s.p3raw, s.wait_class_id, s.wait_class#,
s.wait_class, s.wait_time, s.seconds_in_wait, s.state,
s.wait_time_micro, s.time_remaining_micro,
s.time_since_last_wait_micro, s.service_name, s.sql_trace,
s.sql_trace_waits, s.sql_trace_binds, s.sql_trace_plan_stats,
s.session_edition_id, s.creator_addr, s.creator_serial#
FROM v$session s
WHERE ( (s.username IS NOT NULL)
AND (NVL (s.osuser, 'x') <> 'SYSTEM')
AND (s.TYPE <> 'BACKGROUND') AND STATUS='ACTIVE'
)
ORDER BY "PROGRAM";
-- Query 12 To find the pid of the Concurrent job and kill it.
Select a.inst_id, sid, b.spid
from gv$session a, gv$process b,apps.fnd_concurrent_requests c where a.paddr = b.addr and request_ID ='31689665'
AND a.inst_id = b.inst_id and c.os_process_id = a.process;
-- Query 13:To find the Database SID of the Concurrent job
-- We need our concurrent request ID as an input.
-- c.SPID= is the operating system process id
-- d.sid= is the Oracle process id
SQL> column process heading "FNDLIBR PID"
SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.SPID
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
v$process c,
v$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';
No comments:
Post a Comment