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.
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; /
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 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:
unzip trca.zip cd trca/install sqlplus "/ as sysdba" SQL> @tacreate
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.