Tracing

A trace can help determine things like why a particular action is not occurring, why a page is not rendering, why an error is occurring, why a button does not appear or to collect timing information for performance issues.  It is often required by Support when logging a bug. Obtain a trace using the following steps:

Note: New trace files are created for a login session when trace mode is first turned on for that session. If you subsequently turn trace mode off and back on again, the 2nd set of  tracing information will be appended to the existing trace files and so the trace files will contain information for multiple activities. For this reason, when tracing multiple issues, log out of the application, close the browser completely and then log back in for each issue being traced.

1. For the user that wishes to enable trace mode, set the profile option FND: Diagnostic to Yes at the user level.  When the profile is Yes, a Diagnostics link appears in the top right corner of each HTML page. The link provides access to turn trace on and off. The user may need to log off and back in to re-cache the profile option change.
2. Click the Diagnostics link.
3. Select the Set Trace Level option in the Diagnostic list of values and click the Go button.
4. Select one of the following trace options. After selecting, click the Save button to begin tracing.
   
   • For most non-performance issues, select the Trace with binds option.
   • For performance issues, select the Trace with binds and waits option.
     
5. Perform. the actions you want to trace.
6. Turn trace mode off by repeating steps 2 and 3. Then select option Disable Trace and click the Save button. Several Trace Id's are displayed on the screen after clicking Save.  Make note of these as they will help identify the trace files on the server.
7. Locate the trace files on the server. The files will be found in the directory obtained by the following query. Use the Trace Id's to locate the exact trace files. Upload all trace files to the SR.
   
   select value from v$parameter where name = 'user_dump_dest';

我们在使用sql_trace生成了trace文件后，要找到对应的trace文件是一件比较麻烦的事情，下面两个sql就是解决这个问题的。

当前用户：
SELECT d.value||'/'||lower(rtrim(i.instance, chr(0)))||'_ora_'||p.spid||'.trc' trace_file_name
from ( select p.spid from v$mystat m,v$session s, v$process p
      where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p,
    ( select t.instance from v$thread t,v$parameter v
      where v.name = 'thread' and (v.value = 0 or t.thread# = to_number(v.value))) i,
    ( select value from v$parameter where name = 'user_dump_dest') d


2.如果是查询其他用户session的跟踪文件，则根据用户的sid和#serial使用如下查询：

SELECT d.value||'/'||lower(rtrim(i.instance, chr(0)))||'_ora_'||p.spid||'.trc' trace_file_name

from

    ( select p.spid from v$session s, v$process p

      where s.sid=’’ and s. SERIAL#='' and p.addr = s.paddr) p,

    ( select t.instance from v$thread t,v$parameter v

      where v.name = 'thread' and (v.value = 0 or t.thread# = to_number(v.value))) i,

    ( select value from v$parameter where name = 'user_dump_dest') d