How To Trace a Concurrent Request And Generate TKPROF File [ID 453527.1]

Solution

1. Generate Trace File

Enable Tracing For The Concurrent Manager  Program

Select the Enable Trace Checkbox 

Note : Checking the Trace Check box on the Concurrent Program gives an Event 10046
Level 8 trace. So even if the trace is set for Binds and Waits on the Submission form. once the
concurrent program is encountered in the trace it will reset to level 8 so no binds
will be present in the trace after that point.

If you check this base flag it will always override the Debug Rule that you select after. So if checks Enable Trace it overrides what the Debug Rule is.

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

If the Debug option is greyed out & not updateable set the profile Concurrent: Allow Debugging to Yes

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

prompt  
accept request prompt 'Please enter the concurrent request id for the appropriate concurrent program:'  
prompt  

column traceid format a8  
column tracename format a80  
column user_concurrent_program_name format a40  
column execname format a15  
column enable_trace format a12  
set lines 80  
set pages 22  
set head off 

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_application_id = execname.application_id
and prog.executable_id=execname.executable_id; 

 To check the timeline of the request :

SELECT request_id, TO_CHAR( request_date, 'DD-MON-YYYY HH24:MI:SS' )
request_date, TO_CHAR( requested_start_date,'DD-MON-YYYY HH24:MI:SS' )
requested_start_date, TO_CHAR( actual_start_date, 'DD-MON-YYYY HH24:MI:SS' )
actual_start_date, TO_CHAR( actual_completion_date, 'DD-MON-YYYY HH24:MI:SS' )
actual_completion_date, TO_CHAR( sysdate, 'DD-MON-YYYY HH24:MI:SS' )
current_date, ROUND( ( NVL( actual_completion_date, sysdate ) - actual_start_date ) * 24, 2 ) duration
FROM fnd_concurrent_requests
WHERE request_id = TO_NUMBER('&p_request_id');

 

You may produce a complete report reference of the request ID using the Note:187504.1 bde_request.sql Process and Session info for one Concurrent Request(11.5)

3. TKPROF Trace File

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

$tkprof raw_trace_file.trc output_file explain=apps/apps 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:               This 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 sys=no explain=apps/ sort='(prsela,exeela,fchela)' print=10

 
Note : On Database versions prior to 11g use the following query to identify the directory where the raw trace file is saved.

select value from v$parameter where name = 'user_dump_dest';

On database version 11g use the following query:

select value from v$diag_info where name = 'Diag Trace';

 

4. Debug based on fnd_log_messages

If you need to debug further to get the detailed error messages raised from the program.

To do this ask the customer to execute the following steps,

 1) Log into system admin responsibility and set the following profile options:

 FND: Debug Log Enabled - Yes
 FND: Debug Log Level - Statement
 FND: Debug Log Module - %
 FND: Message Level Threshold to low level

 [ set at user level ]
 
 2) Run the below select in sqlplus:

 SELECT MAX(log_sequence) FROM fnd_log_messages;

 
 Take the max of log sequence and use it as log_seq1 in step#5
 
 3) Try to reproduce the issue with above user for which fnd profile options are set.

 4) Run the below select in sqlplus:
 

 SELECT MAX(log_sequence) FROM fnd_log_messages;

 
 Take the max of log sequence and use it as log_seq2 in step #5.
 
 5) Run following select to get the log messages:

 SELECT LOG_SEQUENCE, MESSAGE_TEXT, USER_ID FROM fnd_log_messages WHERE
 log_sequence > log_seq1-1 and log_sequence < log_seq2+1 ;

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15225049/viewspace-720604/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/15225049/viewspace-720604/