TIP #38 : Tracing session with 10046 event

Using event 10046 is a common way to gather extended information about sessions which mostly is useful got performance analysis.
Here are different tips on how to enable it and how to read its output.

Enabling trace for session.

1. alter session set timed_statistics = true;
2. alter session set statistics_level=all;
3. alter session set max_dump_file_size = unlimited;
4. alter session set events '10046 trace name context forever,level 12' ; 12 will trace wait_events and bind variables;

Disabling trace for a session.

1. alter session set events '10046 trace name context off';

Tracing existing process

• Find SID of that session
• Find its OS pid from the following (This does not work for Windows)
• select p.PID,p.SPID,s.SID from v$process p,v$session s where s.paddr = p.addr and s.sid = &SESSION_ID;
• oradebug setospid spid??? (spid from above query)
• oradebug unlimit
• oradebug event 10046 trace name context forever,level 12;

OR

• Find SID of that session
• Find its Orable pid from the following (This does work for Windows)
  select p.PID,p.SPID,s.SID from v$process p,v$session s where s.paddr = p.addr and s.sid = &SESSION_ID;
• oradebug setorapid pid??? (pid from above query)
• oradebug unlimit
• oradebug event 10046 trace name context forever,level 12;

Disable Tracing of existing session

• oradebug event 10046 trace name context off

Enabling Trace on user login with trigger

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;

Enabling trace on whole instance.

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

Disabling trace on whole instance

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

How to interpret trace file

Trace file is created in user_dump_dest.There is TKPROF traditional way to make trace file more readable but what I `d like are the following 2 tools which create HTML report.

• ORASRP (oracledba.ru/orasrp) : OpenSource , available in Windows and Linux
• TRCANLZR (Metalink note:224270.1) : Creates stored-procedure and bunch of tables in Oracle database, Available on any platform which oracle is available.