Identifying a Running Process
Session ID :
Session ID is the Identification number of the Process at the Database Side. The value for this field can be obtained by querying the V$SESSION view. This is given by the field SID.
Process ID and SPID:
The Process ID or the PID is the number of the Process at the OS level and the SPID is the identification number for the Process at the Application level. These values can be obtained by querying the V$PROCESS view.
Request ID:
Request ID is the ID of the Concurrent request or the Program that has been submitted at the Application tier. This field is denoted by the REQUEST_ID field and can be obtained by querying the FND_CONCURRENT_REQUESTS view.
PHASE_CODE and STATUS_CODE :
These two columns gives the value of the Phase and the Status Code of the concurrent request that is running in the Application end. Some of the different Status Code and Phase code and their meanings are as follows:
Phase code
C - Completed
C - Completed
P - pending
R - Running
Status code
D - Cancelled
C - Normal
Q - standby
W - paused
We can get the Oracle Process ID and the OS Process ID for a particular request that is running in the Application end by querying the FND_CONCURRENT_REQUESTS View also. The fields ORACLE_PROCESS_ID , and OS_PROCESS_ID represent the same in the FND_CONCURRENT_REQUESTS dictionary view.
E.g. In order to kill a particular request that is running, we can obtain the Process ID and other details using the below mentioned SQL query .. :
SQL> select Phase_code, status_code,REQUEST_ID,ORACLE_ID, ORACLE_PROCESS_ID, OS_PROCESS_ID from fnd_concurrent_requests where REQUEST_ID=;
Script 2
=========
This sql will :
* Shows all running and pending requests. (except those that are pending on Conflict resolution manager);
* Can handle RAC and multiple Concurrent nodes, does not meter which node you run the select from;
* Sorts the requests by putting the running requests in the front and pending requests are sorted the same order they will be picked up for execution by concurrent managers.
The meaning of columns is:
* REQ_ID – request ID;
* PREQ_ID – parent request ID, important if you need understand relation of request set stages to request sets;
* PROG_NAME – Short name of the concurrent program
* REQ_NAME - full name of concurrent program, request set or request set stage;
* P, S - Phase and Status codes, you can decode these using FND_LOOKUPS;
* USER_NAME - does not need explanation ;)
* PRIO – request priorit;
* CP_PROCESS - Concurrent node hostname and process ID for the OS process handling the execution of the request (format “HOST:PID”);
* DB_SID - Instance name and SID of oracle session executing the requests (format “INSTANCE_NAME:SID”);
* DB_PROCESS - DB host and process id for the corresponding Oracle DB session (format “HOST:PID”);
* STAT - DB session status A-Active, I-Inactive;
* EVENT, SW – Event end seconds in wait fields from v$session_wait view;
* ACTUAL_START_DATE - time when request started execution;
* ARGUMENT_TEXT – Parameters of the request.
And here goes the select, you are welcome to ask for improvements and suggestions. I’ll also be happy to hear some feedback if you found it useful or too buggy to use (you know you have to test in your test environment before running in production, don’t you?):
select r.request_id req_id,
r.parent_request_id preq_id,
ps.concurrent_program_name prog_name,
p.user_concurrent_program_name || case
when concurrent_program_name = 'FNDRSSUB' then
(select ': ' || rs.user_request_set_name
from fnd_request_sets_tl rs
where rs.application_id = to_number(argument1)
and rs.request_set_id = to_number(argument2)
and rs.language = 'US')
end || case
when concurrent_program_name = 'FNDRSSTG' then
(select ': ' || rss.user_stage_name
from fnd_request_set_stages_tl rss
where rss.set_application_id = to_number(argument1)
and rss.request_set_id = to_number(argument2)
and rss.request_set_stage_id = to_number(argument3)
and rss.language = 'US')
end req_name,
r.phase_code p,
r.status_code s,
u.user_name,
r.priority PRIO,
(select node_name || ':'
from fnd_concurrent_processes cp
where concurrent_process_id = r.controlling_manager) ||
r.os_process_id cp_process,
gi.INSTANCE_NAME || ':' || ss.sid db_sid,
gi.HOST_NAME || ':' || pp.spid db_process,
decode(ss.status, 'ACTIVE', 'A', 'INACTIVE', 'I', ss.status) STAT,
w.event,
w.seconds_in_wait sw,
r.actual_start_date,
argument_text
from fnd_user u,
fnd_concurrent_requests r,
fnd_concurrent_programs_tl p,
fnd_concurrent_programs ps,
gv$session ss,
gv$process pp,
gv$session_wait w,
gv$instance gi
where 1 = 1
and r.requested_by = u.user_id
and (r.phase_code = 'R' or r.status_code = 'I')
And r.Requested_Start_Date <= Sysdate
and p.concurrent_program_id = r.concurrent_program_id
and ps.concurrent_program_id = r.concurrent_program_id
and p.language = 'US'
and ss.audsid(+) = r.oracle_session_id
and r.hold_flag = 'N'
and pp.inst_id(+) = ss.inst_id
and pp.addr(+) = ss.paddr
and w.INST_ID(+) = ss.inst_id
and w.sid(+) = ss.sid
and gi.inst_id(+) = ss.inst_id
order by decode(r.phase_code, 'R', 0, 1),
NVL(R.priority, 999999999),
R.Priority_Request_ID,
R.Request_ID;
Script 3
=========This script will find the DB node name, session sid
select q.concurrent_queue_name,
cp.node_name || ‘:’ || cp.os_process_id CP_NODE_PID,
cp.db_instance || ‘:’ || s.sid || ‘,’ || s.SERIAL# DB_NODE_SID_SERIAL#,
p.spid DB_NODE_PID
from fnd_concurrent_queues q,
fnd_concurrent_processes cp,
gv$session s,
gv$process p
where q.concurrent_queue_name like ‘STANDARD%’
and cp.concurrent_queue_id = q.concurrent_queue_id
and cp.process_status_code = ‘A’
and s.AUDSID = cp.session_id
and s.INST_ID = cp.instance_number
and p.INST_ID = cp.instance_number
and p.addr = s.paddr;
This was for tracing a concurrent manager that’s running already, in the post I refer to queries that are executed once at the startup of concurrent manager. I found those using DB “AFTER LOGON” trigger like this:
CREATE OR REPLACE TRIGGER SYS.TRACE_ON_LOGON
AFTER LOGON ON DATABASE
BEGIN
SYS.dbms_support.start_trace(TRUE, TRUE);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
1) Enable the trigger
2) Start concurrent managers
3) Disable the trigger
4) use the select above to find the DB process ID for the concurrent manager of interest (the trace file name will contain this ID)
Script 4
===================
select distinct
s.inst_id,
s.status,
s.username,
s.sid,
s.serial#,
s.schemaname,
s.osuser,
f.user_name,
p.spid,
f.request_id,
s.process,
s.module,
s.action,
s.machine,
s.program,
u.value || '/' || lower(n.value) || '_ora_' || p.spid || '.trc' Trace_File,
t.sql_text
--
from gv$session s,
gv$process p,
gv$sql t,
(select u.user_name, r.* from apps.fnd_concurrent_requests r, apps.fnd_user u where r.requested_by = u.user_id and phase_code = 'R') f,
(select inst_id, value from gv$parameter where name = 'user_dump_dest') u,
(select inst_id, value from gv$parameter where name = 'instance_name') n
--
where s.paddr = p.addr
and s.inst_id = p.inst_id
and s.sql_address = t.address(+)
and s.inst_id = t.inst_id(+)
and p.spid = f.oracle_process_id(+)
and s.inst_id = u.inst_id
and s.inst_id = n.inst_id
;
1 comment:
very useful queries...
thanks for providing these queries.
Post a Comment