oracle trace文件

1 查询trace路径

SELECT VALUE  FROM V$PARAMETER WHERE NAME = 'user_dump_dest';

2 find对应的trace file，oracle版本不同，查询方法不同

oracle 10g:

 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;

oracle 11g:

select tracefile from v$process where addr in (select paddr from v$session where sid in (select sid from v$mystat));

为了省事，可以设置一个名称

alter session set tracefile_identifier='test';

3 开启trace

SQL> alter session set sql_trace=true

4 执行SQL，然后使用tkprof工具转换trc文件，这样比较好看

  tkprof t0fwa_ora_13265_test.trc test.txt