Generate and Review Trace for a Concurrent Program

 1. Run the following query:  

select max(log_sequence) from fnd_log_messages 

Make a note this log_seq. 

2. Set the following profile options at the user level: 

  FND: Debug Log Enabled = YES 

  FND: Debug Log Filename = NULL 

  FND: Debug Log Level = STATEMENT 

  FND: Debug Log Module = % 

3. Enable trace for the concurrent program: In System Administrator responsibility Navigate to: Concurrent -> Program -> Define, query up the concurrent program you wish to trace. Make sure that the "enable trace" checkbox is UN-checked.

4. Turn on tracing: Responsibility: System Administrator 

    Navigate: Profiles > System 

    Query Profile Option Concurrent: Allow Debugging 

    Set profile to Yes

5. Submit the Concurrent Request, but before submitting, click Debugging Options, and select for SQL Trace: Trace With Binds And Waits. 

Make a note of your request id.

6. After the request finishes, please retrieve the trace file by running this sql query which will give the exact trace file name, and will prompt you for the request id:

    SELECT 'Request id: '||request_id ,  

'Trace id: '||oracle_Process_id,  

'Trace Flag: '||req.enable_trace,  

'Trace Name:  

'||dest.value||'/'||lower(dbnm.value)||'_ora_'||oracle_process_id||'.trc',  

'Prog. Name: '||prog.user_concurrent_program_name,  

'File Name: '||execname.execution_file_name|| execname.subroutine_name ,  

'Status : '||decode(phase_code,'R','Running')  

||'-'||decode(status_code,'R','Normal'),  

'SID Serial: '||ses.sid||','|| ses.serial#,  

'Module : '||ses.module  

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 req.request_id = &request  

and req.oracle_process_id=proc.spid(+)  

and proc.addr = ses.paddr(+)  

and dest.name='user_dump_dest'  

and dbnm.name='db_name'  

and req.concurrent_program_id = prog.concurrent_program_id  

and req.program_application_id = prog.application_id  

and prog.application_id = execname.application_id  

and prog.executable_id=execname.executable_id;  

7. Run the following sql:   

 select * from fnd_log_messages 

 where log_sequence > &log_seq_noted_above 

 order by log_sequence   

 

8.Upload the data from the above SQL in an xls file. 

9. Convert the trace file into tkprof format, using the following command:

    tkprof <filename.trc> <output_filename_SORT.txt> explain=apps/<password>

10. Review trace,log and tkprof files.