• 10046 trace

跟踪级别(TRACE LEVEL)

玩转跟踪不了解跟踪级别是不行的，下面介绍 Oracle 可采用的跟踪接口：

Level 0 = No statistics generated
Level 1 = standard trace output including parsing, executes and fetches plus more.
Level 2 = Same as level 1.
Level 4 = Same as level 1 but includes bind information
Level 8 = Same as level 1 but includes wait's information
Level 12 = Same as level 1 but includes binds and waits

1 使用ORADEBUG跟踪当前会话

oradebug 跟踪 SYS 用户的当前 SESSION，如下方式：

sys@MAA> oradebug setmypid
sys@MAA> oradebug unlimit
sys@MAA> oradebug event 10046 trace name context forever, level 12
sys@MAA> exec our code
sys@MAA> oradebug event 10046 trace name context off 
sys@MAA> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/maa/maa/trace/maa_ora_17641.trc 

2 使用ORADEBUG跟踪其他会话

通过 oradebug 非常方便地跟踪其他会话，如下方式：

---- 查出session信息

luocs@MAA> select pid, spid from v$process p, v$session s where p.addr = s.paddr and s.
sid=(select sid from v$mystat where rownum=1); 
 PID SPID 
---------- ------------------------------------------------ 
 25 17678 

----指定跟踪 SESSION 的 SPID(OS process)

sys@MAA> oradebug setospid 17678 
Oracle pid: 25, Unix process pid: 17678, image: oracle@maa3.luocs.com (TNS V1-V3)

----或者指定跟踪 SESSION 的 PID(Oracle process ID)

sys@MAA> oradebug setorapid 25 
Oracle pid: 25, Unix process pid: 17678, image: oracle@maa3.luocs.com (TNS V1-V3)

sys@MAA> oradebug unlimit 
sys@MAA> oradebug event 10046 trace name context forever, level 12
sys@MAA> exec our code  <<在跟踪的会话执行sql
sys@MAA> oradebug event 10046 trace name context off 
sys@MAA> oradebug tracefile_name

tkprof 格式化输出

 tkprof tracefile outfile [explain=user/password] 

• AUTOTRACE

luocs@MAA> set autotrace on
luocs@MAA> set autotrace on explain 
luocs@MAA> set autotrace on statistics 
luocs@MAA> set autotrace traceonly 
luocs@MAA> set autotrace traceonly explain 
luocs@MAA> set autotrace traceonly explain statistics 
luocs@MAA> set autotrace off 

#使用缩略方式，比如
luocs@MAA> set autot trace exp stat

• 10053 EVENTS

luocs@MAA> alter session set events '10053 trace name context forever, level 1'; 
luocs@MAA> alter session set events '10053 trace name context off'; 
sys@MAA> oradebug event 10053 trace name context forever, level 1 
sys@MAA> oradebug event 10053 trace name context off