获取当前会话的trace信息

查看当前会话的SID和SERIAL#

SQL>  select SID,SERIAL# from v$session where sid = (select sid from v$mystat where rownum = 1);  

       SID    SERIAL#
---------- ----------
        82        599

启动trace

SQL> execute dbms_system.set_sql_trace_in_session(82,599,true);   

PL/SQL procedure successfully completed.

完成后，停止trace。

SQL> execute dbms_system.set_sql_trace_in_session(82,599,false);

PL/SQL procedure successfully completed.

查看trace文件路径

SQL> select a.TRACEFILE from v$process a where a.ADDR in (select paddr from v$session where sid=82);

TRACEFILE
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/racdb/racdb1/trace/racdb1_ora_15427.trc

查看trace文件内容

[root@rac1 ~]# tail -f /u01/app/oracle/diag/rdbms/racdb/racdb1/trace/racdb1_ora_15427.trc
STAT #140524433493112 id=7 cnt=86345 pid=5 pos=2 obj=0 op='HASH JOIN  (cr=1068 pr=0 pw=0 time=57943 us cost=294 size=9143030 card=86255)'
STAT #140524433493112 id=8 cnt=87 pid=7 pos=1 obj=47 op='INDEX FULL SCAN I_USER2 (cr=1 pr=0 pw=0 time=7 us cost=1 size=1914 card=87)'
STAT #140524433493112 id=9 cnt=86345 pid=7 pos=2 obj=18 op='TABLE ACCESS FULL OBJ$ (cr=1067 pr=0 pw=0 time=36404 us cost=292 size=7252056 card=86334)'
STAT #140524433493112 id=10 cnt=0 pid=4 pos=2 obj=0 op='NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us cost=2 size=29 card=1)'
STAT #140524433493112 id=11 cnt=0 pid=10 pos=1 obj=47 op='INDEX SKIP SCAN I_USER2 (cr=0 pr=0 pw=0 time=0 us cost=1 size=20 card=1)'
STAT #140524433493112 id=12 cnt=0 pid=10 pos=2 obj=39 op='INDEX RANGE SCAN I_OBJ4 (cr=0 pr=0 pw=0 time=0 us cost=1 size=9 card=1)'
STAT #140524433493112 id=13 cnt=0 pid=3 pos=2 obj=0 op='NESTED LOOPS  (cr=1 pr=0 pw=0 time=45 us cost=1 size=17 card=1)'
STAT #140524433493112 id=14 cnt=0 pid=13 pos=1 obj=138 op='INDEX FULL SCAN I_LINK1 (cr=1 pr=0 pw=0 time=44 us cost=0 size=13 card=1)'
STAT #140524433493112 id=15 cnt=0 pid=13 pos=2 obj=47 op='INDEX RANGE SCAN I_USER2 (cr=0 pr=0 pw=0 time=0 us cost=1 size=4 card=1)'
FETCH #140524433493112:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=1250653294,tim=1609743664086145