Track and Trace E-Business Suite Concurrent Request - EBS Logs, O/S Process IDs, Locks

 

I often get asked to take a look at an Oracle eBusiness Suite concurrent request to see what it is doing, this can come from a few different angles:

1. What requests are currently running?
2. I have an operating system process that is taking too much CPU - what is it doing?
3. Can you tell me where the request is at? I've clicked on the log but it doesn't show anything!
4. My request is taking too long - can you check for blocking locks?

There are a number of strategies to track and trace where things are at for a running request, these include:

1. Monitor the currently running requests in fnd_concurrent_requests
2. Checking the v$sqlarea to see what SQL statement or PL/SQL is running
3. Tailing the concurrent request log / output file while it is being written to near realtime - prior to request completing
4. Checking for locks blocking the concurrent request

So without further ado, let's take a look at the following sweet query:

set pages 9999 feed on lines 150

col user_concurrent_program_name format a40 head PROGRAM trunc

col elapsed format 9999

col request_id format 9999999 head REQUEST

col user_name format a12

col oracle_process_id format a5 head OSPID

col inst_name format a10

col sql_text format a30

col outfile_tmp format a30

col logfile_tmp format a30

select /*+ ordered */

       fcp.user_concurrent_program_name

,      fcr.request_id

,      round(24*60*( sysdate - actual_start_date )) elapsed

,      fu.user_name

,      fcr.oracle_process_id

,      sess.sid

,      sess.serial#

,      inst.inst_name

,      sa.sql_text

,      cp.plsql_dir || '/' || cp.plsql_out outfile_tmp

,      cp.plsql_dir || '/' || cp.plsql_log logfile_tmp

from   apps.fnd_concurrent_requests fcr

,      apps.fnd_concurrent_programs_tl fcp

,      apps.fnd_concurrent_processes cp

,      apps.fnd_user fu

,      gv$process pro

,      gv$session sess

,      gv$sqlarea sa

,      sys.v_$active_instances inst

where  fcp.concurrent_program_id = fcr.concurrent_program_id

and    fcp.application_id = fcr.program_application_id

and    fcr.controlling_manager = cp.concurrent_process_id

and    fcr.requested_by = fu.user_id (+)

and    fcr.oracle_process_id = pro.spid (+)

and    pro.addr = sess.paddr (+)

and    sess.sql_address = sa.address (+)

and    sess.sql_hash_value = sa.hash_value (+)

and    sess.inst_id = inst.inst_number (+)

and    fcr.phase_code = 'R' /* only running requests */

;

PROGRAM                                   REQUEST ELAPSED USER_NAME    OSPID        SID    SERIAL# INST_NAME  SQL_TEXT                       OUTFILE_TMP                    LOGFILE_TMP

---------------------------------------- -------- ------- ------------ ----- ---------- ---------- ---------- ------------------------------ ------------------------------ ------------------------------

Workflow Background Process               2960551       1 VIRTUATE     24814        130      29699 APPLPROD1  BEGIN WF_ENGINE.BACKGROUNDCONC /usr/tmp/o0068194.tmp          /usr/tmp/l0068194.tmp

                                                                                                              URRENT(:errbuf,:rc,:A0,:A1,:A2

                                                                                                              ,:A3,:A4,:A5); END;

1 row selected.

From the above we can see key information:

• The running Concurrent Request Program Name and Request_ID
• The database node operating system process id (OSPID) so we can monitor usage via top / iostat / vmstat
• The SID / Serial in case we want to kill the session via alter system kill session '130,29699';
• The instance name the database session is running on in a RAC environment
• The currently running SQL text
• The temporary files where concurrent request log/out is being written to via utl_file while running. These files are copied over to the Concurrent Tier $APPLCSF/$APPLOUT and $APPLLOG after completion of the request.

We can break out the above into a few queries and procedures to drill into specific information information from the core EBS tables and DBA v$ views

Read carefully........