How To Collect 10046 Trace (SQL_TRACE) Diagnostics for Performance Issues

 

 

SQL> show parameter dia

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
diagnostic_dest        string  /u01/app/oracle

 

To gather 10046 trace at the session level:

 

SQL> alter session set tracefile_identifier='10046';

Session altered.

SQL> alter session set timed_statistics=true;

Session altered.

SQL> alter session set statistics_level=all;

Session altered.

SQL> alter session set max_dump_file_size=unlimited;

Session altered.

SQL> alter session set events '10046 trace name context forever,level 12';

Session altered.

 

SQL> select * from dual;  --- the sql to be traced

D
-
X

 

SQL> alter session set events '10046 trace name context off';

Session altered.

 

 

 

-----------------------------

 

Tracing a process after it has started

找出PID和SPID

 

select p.PID,p.SPID,s.SID
from v$process p,v$session s
where s.paddr = p.addr
and s.sid = &SESSION_ID
/

 

connect / as sysdba
oradebug setospid 9834
oradebug unlimit
oradebug event 10046 trace name context forever,level 12

 

or

 

connect / as sysdba
oradebug setorapid 9834
oradebug unlimit
oradebug event 10046 trace name context forever,level 12

 

To disable oradebug tracing once tracing is finished:

oradebug event 10046 trace name context off

 

Instance wide tracing
 --- 会对性能有影响

System-wide tracing can be enabled as follows:

alter system set events '10046 trace name context forever,level 12';

The setting can be disabled in all sessions by using the following command:

alter system set events '10046 trace name context off';

 

 

通过参数

This setting will trace every session in the instance when it is restarted.

event="10046 trace name context forever,level 12"

The setting can be disabled by removing the parameter and restarting the instance or by using an alter system command as follows:

alter system set events '10046 trace name context off';

 

 

通过触发器

CREATE OR REPLACE TRIGGER SYS.set_trace
AFTER LOGON ON DATABASE
WHEN (USER like '&USERNAME')
DECLARE
lcommand varchar(200);
BEGIN
EXECUTE IMMEDIATE 'alter session set statistics_level=ALL';
EXECUTE IMMEDIATE 'alter session set max_dump_file_size=UNLIMITED';
EXECUTE IMMEDIATE 'alter session set events ''10046 trace name context forever, level 12''';
END set_trace;
  /