Wednesday, August 29, 2012

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.

No comments: