Oracle trace
1 前提
有足够的CPU时间
有够的trace空间
确保trace的文件有足够的磁盘空间
2 级别
system: alter system set sql_trace=true; --很少使用 session: alter session set sql_trace=true;
3 方法
3.1trace当前会话:
alter session set sql_trace=true; --启用当前session trace select count(*) dba_objects; --此SQL操作被追踪 alter session set sql_trace=false; --结束追踪 --查看trace文件 SELECT d.VALUE || ‘/‘ || 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$diag_info where name=‘Diag Trace‘) d ;
3.2 trace其它用户会话:
SQL> select sid,serial#,username from v$session where username is not null; SID SERIAL# USERNAME ---------- ---------- -------------------------------------------------------------------------------------------------------------------------------- 3 62469 SYS 214 20158 C##SAPR3 217 44968 C##SAPR3 395 39248 C##SAPR3 596 21546 SYS execute dbms_system.set_sql_trace_in_session(395,39248,true) ---启用用户session trace execute dbms_system.set_sql_trace_in_session(395,39248,false) ---结束追用户session trace
3.3指定SQL_ID
alter system set events ‘SQL_TRACE [SQL:&&SQL_ID] bind=true,wait=true‘;
4. 使用tkprof 查看
原文：https://www.cnblogs.com/tingxin/p/12839620.html