DBA_Oracle Erp并发程序运行状态查询和监控（案例）

2014-08-09 Created By BaoXinjian

Looking on how to check long running concurrent request in Oracle Apps 11i or R12? Here’s the overview of the SQL query script to detect the session information of each program.First you need to get the listing of running concurrent request in Oracle Apps 11i or R12. You can use the SQL query script as below to obtain the list of running request.

1. SELECT a.request_id  
2. ,a.oracle_process_id "SPID"  
3. ,frt.responsibility_name  
4. ,c.concurrent_program_name || ‘: ‘ || ctl.user_concurrent_program_name  
5. ,a.description  
6. ,a.ARGUMENT_TEXT  
7. ,b.node_name  
8. ,b.db_instance  
9. ,a.logfile_name  
10. ,a.logfile_node_name  
11. ,a.outfile_name  
12. ,q.concurrent_queue_name  
13. ,a.phase_code,a.status_code, a.completion_text  
14. , actual_start_date  
15. , actual_completion_date  
16. , fu.user_name  
17. ,(nvl(actual_completion_date,sysdate)-actual_start_date)*1440 mins  
18. ,(SELECT avg(nvl(a2.actual_completion_date-a2.actual_start_date,0))*1440 avg_run_time  
19. FROM APPLSYS.fnd_Concurrent_requests a2,  
20. APPLSYS.fnd_concurrent_programs c2  
21. WHERE c2.concurrent_program_id = c.concurrent_program_id  
22. AND a2.concurrent_program_id = c2.concurrent_program_id  
23. AND a2.program_application_id = c2.application_id  
24. AND a2.phase_code || ‘‘ = ‘C‘) avg_mins  
25. ,round((actual_completion_date - requested_start_date),2) * 24 duration_in_hours  
26. FROM APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_processes b  
27. ,applsys.fnd_concurrent_queues q  
28. ,APPLSYS.fnd_concurrent_programs c  
29. ,APPLSYS.fnd_concurrent_programs_tl ctl  
30. ,apps.fnd_user fu  
31. ,apps.FND_RESPONSIBILITY_TL frt  
32. WHERE a.controlling_manager = b.concurrent_process_id  
33. AND a.concurrent_program_id = c.concurrent_program_id  
34. AND a.program_application_id = c.application_id  
35. AND a.phase_code = ‘R‘  
36. AND a.status_code = ‘R‘  
37. AND b.queue_application_id = q.application_id  
38. AND b.concurrent_queue_id = q.concurrent_queue_id  
39. AND ctl.concurrent_program_id = c.concurrent_program_id  
40. AND a.requested_by = fu.user_id  
41. AND a.responsibility_id = frt.responsibility_id  
42. ORDER BY a.actual_start_date DESC  

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
,a.outfile_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

You can see the request id and other relevant information from the result.

Based on the SPID associated to each running request, query the v$session or v$session_longops table to see what is the request id doing in the backend.

1. SELECT b.sid, b.serial#, a.spid, b.program, b.osuser, b.machine,  
2. b.TYPE, b.event, b.action, b.p1text, b.p2text, b.p3text, b.state, c.sql_text,b.logon_time  
3. FROM v$process a, v$session b, v$sqltext c  
4. WHERE a.addr=b.paddr  
5. AND b.sql_hash_value = c.hash_value  
6. AND b.STATUS = ‘ACTIVE‘  
7. AND a.spid = ‘11696‘  
8. ORDER BY a.spid, c.piece  

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, v$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 = ‘11696‘
ORDER BY a.spid, c.piece

Replace v$session with gv$session if the database is running on RAC environment. Enable or set trace if you wish to know more details on the session.

Thanks and Regards