By default, each new trace overwrites the previous trace. Thus, if a statement contains substatements (such as invoking stored procedures, stored functions, or triggers), the topmost statement and substatements each generate one trace, but at the end of execution, the trace for only the last substatement is visible.
A user who wants to see the trace of a different substatement can
enable or disable tracing for the desired substatement, but this
requires editing the routine code, which may not always be
possible. Another solution is to tune trace purging. This is done
by setting the
optimizer_trace_offset
and
optimizer_trace_limit
system
variables, like this:
SET optimizer_trace_offset=offset, optimizer_trace_limit=limit;
offset
is a signed integer (default
-1
); limit
is a
positive integer (default 1
). Such a
SET
statement has the following
effects:
All traces previously stored are cleared from memory.
A subsequent
SELECT
from theOPTIMIZER_TRACE
table returns the firstlimit
traces of theoffset
oldest stored traces (ifoffset
>= 0), or the firstlimit
traces of the-offset
newest stored traces (ifoffset
< 0).
Examples:
SET optimizer_trace_offset=-1, optimizer_trace_limit=1
: The most recent trace is shown (the default).SET optimizer_trace_offset=-2, optimizer_trace_limit=1
: The next-to-last trace is shown.SET optimizer_trace_offset=-5, optimizer_trace_limit=5
: The last five traces are shown.
Negative values for offset
can thus
prove useful when the substatements of interest are the last few
in a stored routine. For example:
SET optimizer_trace_offset=-5, optimizer_trace_limit=5;
CALL stored_routine(); # more than 5 substatements in this routine
SELECT * FROM information_schema.OPTIMIZER_TRACE; # see only the last 5 traces
A positive offset
can be useful when
one knows that the interesting substatements are the first few in
a stored routine.
The more accurately these two variables are set, the less memory
is used. For example, SET optimizer_trace_offset=0,
optimizer_trace_limit=5
requires sufficient memory to
store five traces, so if only the three first are needed, is is
better to use SET optimizer_trace_offset=0,
optimizer_trace_limit=3
, since tracing stops after
limit
traces. A stored routine may have
a loop which executes many substatements and thus generates many
traces, which can use a lot of memory; in such cases, choosing
appropriate values for offset
and
limit
can restrict tracing to, for
example, a single iteration of the loop. This also decreases the
impact of tracing on execution speed.
If offset
is greater than or equal to
0, only limit
traces are kept in
memory. If offset
is less than 0, that
is not true: instead, -offset
traces
are kept in memory. Even if limit
is
smaller than -offset
, excluding the
last statement, the last statement must still be traced because it
will be within the limit after executing one more statement. Since
an offset less than 0 is counted from the end, the
“window” moves as more statements execute.
Using optimizer_trace_offset
and
optimizer_trace_limit
, which are restrictions
at the trace producer level, provide better (greater) speed and
(less) memory usage than setting offsets or limits at the trace
consumer (SQL) level with SELECT * FROM OPTIMIZER_TRACE
LIMIT
, which saves almost
nothing.
limit
OFFSET
offset