Re: A brief doc on 10046 tracing

Yikes !!! Sorry all - let's try a plain text ...

Oracle 10046 Trace

Oracle is capable of providing detailed instrumentation data that can be used to locate an error, to evaluate bind variables or to identify and resolve a performance issue. This information can be accessed via a 10046 trace. A 10046 deep trace (level 12) shows exactly how the database is processing a query, the events the system is waiting on and how much time it spends waiting for a specific event. By locating the specific process or query that is "too slow" and capturing a trace file, it becomes possible to determine the improvement that will have the most significant impact on performance. By recapturing the tracing at different phases of the tuning process, it is possible to clearly identify and quantify how the process has improved.

When implementing a 10046 trace, the optimal approach will capture only the query or subquery that needs to be evaluated. The goal is to ensure that only the resources being used by that critical process are reflected in the trace file. When possible, the best approach is open a new session, turn the trace on, run the query and deactivate trace in one script. This prevents misleading wait times from being recorded in the trace file. It is possible to capture and trace a long running active session; the trace file may be incomplete but it is likely the problem event will be captured.

The basic trace process is shown below. Sample commands will follow.

"	Start a new session
"	Set Timed Statistics to TRUE 
"	Set max_dump_file_size to UNLIMITED
"	Set the name of the trace file to include an easily found value
(your name is usually a good option)
"	Turn on trace with Event 10046 at the desired level (1,4,8 or 12)
"	Perform your tracing event. Click on a button on a form, run a sql
statement, etc.
"	Turn trace off as soon as control returns back to the user
"	Search the raw trace file for error information if tracing a failed process
"	Execute Tkprof using raw trace file as input for performance issues

To trace a long running session that is already in process, find the sid and serial# of the process. These values can then be used to set a 10046 event from another session. The commands to trace an existing session should be executed by a DBA. Request assistance if you do not have this level of access.

Trace Levels

Level 1 Default trace level. This level traces all activities until the trace session is stopped.

Level 4 Provides level 1 tracing and displays the entered value for all bind variables. Bind variables are the values that the user enters. The code displays these bind variables as: b1, etc. When level 4 is activated, the substituted value for the bind variable is displayed in the trace file. A level 4 trace will be optimal for error tracing.

Level 8 Provides level 1 tracing and displays a list of all database wait events. Database wait events list the reasons if the elapsed time is greater than the CPU time in the tkprof report.

Level 12 Provides level 1 tracing in addition to both bind variable substitution and database wait events. A level 12 trace is used for performance issues.

Sample Commands

Tracing a statement for bind variable issues or errors

• Setup Tracing alter session set timed_statistics=true; alter session set max_dump_file_size=unlimited; alter session set tracefile_identifier='robyn';
• Activate the Trace alter session set events '10046 trace name context forever, level 4'; /* code to be traced goes here */ alter session set events '10046 trace name context off';

Tracing a statement for performance issues

• Setup Tracing alter session set timed_statistics=true; alter session set max_dump_file_size=unlimited; alter session set tracefile_identifier='robyn';
• Activate the Trace alter session set events '10046 trace name context forever, level 12'; /* code to be traced goes here */ alter session set events '10046 trace name context off';

Tracing an already active, long running session. (DBA only)

Find v$session.sid and v$session.serial# for session to be traced and set timed_statistics and max_dump_file_size as follows:

exec dbms_system.SET_BOOL_PARAM_IN_SESSION (

	sid => ##,
	serial# => ####,
	parnam => 'timed_statistics',
	bval => true)
exec dbms_system.SET_INT_PARAM_IN_SESSION (
	sid => ##,
	serial# => ####,
	parnam => 'max_dump_file_size',
	bval => 2147483647)

• Activate Trace, Option 1 (Be very careful! It is possible to enter dangerous values between the parenthesis.) exec dbms_system.set_ev( sid => ##, serial => ####, event => 10046, level => 12, null => '') /* Wait for executing code to complete during this time window */ exec dbms_system.set_ev( sid => ##, serial => ####, event => 10046, level => 0, null => '')
• Activate Trace, Option 2 (Less dangerous, but does not provide timing for waits and binds.) exec dbms_support.start_trace_in_session( sid => ##, serial => ####, waits => true, binds => true) /* code to be traced executes during this time window */ exec dbms_support.stop_trace_in_session( sid => ##, serial => ####)

Do not use dbms_system.set_sql_trace_in_session for extended tracing. It does not provide timing for waits and binds.

On 7/20/05, Robyn <robyn.sands_at_gmail.com> wrote:
> Hello all,
>
> I've been asked to provide a short doc on 10046 tracing for DBAs and
> developers. So, at the risk of imposing, the text of my first draft is
> below. I plan on including a copy of Oracle Note 39817.1 as an
> appendix as well as some sample trace files from various systems. I
> will also be suggesting that interested parties purchase Cary's book.
> (I'm not sharing mine with this crowd - it may not come back.)
>
> Any suggestions, feedback, etc would be appreciated. I'm the only one
> here that knows how to do this, and I need to keep it short and
> simple, but clear to the uninitiated. If any one would prefer that I
> send the doc directly to them as an attachment, please let me know.
>
> Thanks in advance,
>
> Robyn

--
http://www.freelists.org/webpage/oracle-l