Re: A brief doc on 10046 tracing
- From: Robyn <robyn.sands@xxxxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 20 Jul 2005 12:00:40 -0400
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@xxxxxxxxx> 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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
