More 10046 tracing

(For standard 10046 tracing, see the trace.sql code listing)

Instance wide tracing

This setting will trace every session that is created after the parameter is set. Existing sessions will not be traced.

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

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

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

Remember to set max_dump_file size and timed_statistics beforehand if necessary.

Instance wide tracing via a login trigger

Used for limiting traces to e.g. a particular user:

CREATE OR REPLACE TRIGGER SYS.set_trace
AFTER LOGON ON DATABASE
WHEN (USER like  '&USERNAME')
BEGIN
  EXECUTE IMMEDIATE 'alter session set timed_statistics=TRUE'; 
  EXECUTE IMMEDIATE 'alter session set max_dump_file_size=UNLIMITED';
  EXECUTE IMMEDIATE 'alter session set events ''10046 trace name context forever, level 8''';
END set_trace;
/

From Metalink Note 376442.1

Or, for limiting traces to e.g. a particular program:

conn / as sysdba
CREATE OR REPLACE TRIGGER SYS.set_trace
AFTER LOGON ON DATABASE
WHEN (USER != 'SYS')
DECLARE
   v_exe       v$session.program%TYPE;
BEGIN

   SELECT upper(program)
     INTO v_exe
     FROM v$session s, v$mystat m
    WHERE m.sid = s.sid
     AND  rownum < 2 ;

  IF v_exe in ('MYPROG.EXE','MYPROGRAM.EXE')
  THEN
    EXECUTE IMMEDIATE 'alter session set events ''10046 trace name context forever, level 8''';
  END IF ;

END set_trace;
/

Level 12 trace - for bind variables

I tend to use level 12 traces only very rarely.

The syntax for doing so is the same as a level 8 trace, but with a 12 instead of an 8. For example:

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

One drawback of level 12 traces is that they don't report the bind values in tkprof. For that you either have to look at the raw trace file itself, or use the below Trace Analyzer tool instead of tkprof.

Trace Analyzer

Trace Analyzer is an improved version of tkprof. It generates an html report rather than plan text, with a lot of useful analysis information that tkprof doesn't have. And for level 12 traces, it reports the bind values.

Sample output is in file trcanlzr_84029.html.

The drawback of it is that it takes a bit of time to install. And html output files have to be ftp'd around to be viewed. But if you're going to be doing a lot of performance work on a database, or for level 12 traces, then trace analyzer is well worth the effort.

Installation and usage instructions are:

Download the source code from Metalink Note 224270.1, and ftp it to the database server.
Install it into your database. The install is quite slow, which can be annoying:
```
unzip trca.zip
cd trca/install
sqlplus "/ as sysdba"
SQL> @tacreate
```

Then, instead of tkprof, use:

cd trca/sqlt/run
sqlplus /
SQL> @trcanlzr myfilename.trc

More detailed Trace Analyzer instructions are in file instructions.txt.
Dave Moore has a good brief overview article of Trace Analyzer.