How to Trace a Concurrent Request And Generate TKPROF File

 Enable Tracing For The Concurrent Manager  Program

    Responsibility: System Administrator
    Navigate: Concurrent > Program > Define
    Query Concurrent Program
    Select the Enable Trace Checkbox

Turn On Tracing

    Responsibility: System Administrator
    Navigate: Profiles > System
    Query Profile Option Concurrent: Allow Debugging
    Set profile to Yes

 Run Concurrent Program With Tracing Turned On

    Logon to the Responsibility that runs the Concurrent Program
     In the Submit Request Screen click on Debug Options (B)
    Select the Checkbox for SQL Trace

 2. Find Trace File Name

  Run the following SQL to find out the Raw trace name and location for the concurrent program.  The SQL prompts the user for the request id

SELECT
    req.request_id
    ,req.logfile_node_name node
    ,req.oracle_Process_id
    ,req.enable_trace
    ,dest.VALUE||'/'||LOWER(dbnm.VALUE)||'_ora_'||oracle_process_id||'.trc' trace_filename
    ,prog.user_concurrent_program_name
    ,execname.execution_file_name
    ,execname.subroutine_name
    ,phase_code
    ,status_code
    ,ses.SID
    ,ses.serial#
    ,ses.module
    ,ses.machine
    FROM
    fnd_concurrent_requests req
    ,v$session ses
    ,v$process proc
    ,v$parameter dest
    ,v$parameter dbnm
    ,fnd_concurrent_programs_vl prog
    ,fnd_executables execname
    WHERE 1=1
    AND req.request_id = &request --Request ID

/opt/oracle/enable/admin/ENBL1/udump  --> trace file location

3. TKPROF Trace File

Once you have obtained the Raw trace file you need to format the file using TKPROF.

$tkprof enbl2_ora_23852_837166_CR1814778.trc /home/vk837166/837166_CR1814778 explain=apps/nobel4pce sort=(exeela,fchela) sys=no
Where: raw_trace_file.trc: Name of trace file

output_file: tkprof out file

explain: This option provides the explain plan for the sql                      statements

sort: his provides the sort criteria in which all sql statements will be sorted.  This will bring the bad sql at the top of the outputfile.

sys=no:Disables sql statements issued by user SYS

Another example: To get (TKPROF) sorted by longest running queries first and limits the results to the “Top 10? long running queries





$ tkprof <filename.trc> <output_filename> sys=no explain=apps/<password> sort=’(prsela,exeela,fchela)’ print=10