Friday, January 1, 2010

Monitoring and troubleshooting CM request in Database

Identifying a Running ProcessSession 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 .. :

* 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.

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?):

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:

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)