<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="tuning-trace-purging">
      </a>
      10.15.4 Tuning Trace Purging
     </h3>
    </div>
   </div>
  </div>
  <p>
   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.
  </p>
  <p>
   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
   <a class="link" href="server-system-variables.html#sysvar_optimizer_trace_offset">
    <code class="literal">
     optimizer_trace_offset
    </code>
   </a>
   and
   <a class="link" href="server-system-variables.html#sysvar_optimizer_trace_limit">
    <code class="literal">
     optimizer_trace_limit
    </code>
   </a>
   system
      variables, like this:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa96188187"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SET</span> optimizer_trace_offset<span class="token operator">=</span><span class="token keyword"><em class="replaceable">offset</em></span><span class="token punctuation">,</span> optimizer_trace_limit<span class="token operator">=</span><span class="token keyword"><em class="replaceable">limit</em></span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   <em class="replaceable">
    <code>
     offset
    </code>
   </em>
   is a signed integer (default
   <code class="literal">
    -1
   </code>
   );
   <em class="replaceable">
    <code>
     limit
    </code>
   </em>
   is a
      positive integer (default
   <code class="literal">
    1
   </code>
   ). Such a
   <a class="link" href="set.html" title="13.3.6 The SET Type">
    <code class="literal">
     SET
    </code>
   </a>
   statement has the following
      effects:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      All traces previously stored are cleared from memory.
     </p>
    </li>
    <li class="listitem">
     <p>
      A subsequent
      <a class="link" href="select.html" title="15.2.13 SELECT Statement">
       <code class="literal">
        SELECT
       </code>
      </a>
      from the
      <a class="link" href="information-schema-optimizer-trace-table.html" title="28.3.19 The INFORMATION_SCHEMA OPTIMIZER_TRACE Table">
       <code class="literal">
        OPTIMIZER_TRACE
       </code>
      </a>
      table returns the
          first
      <em class="replaceable">
       <code>
        limit
       </code>
      </em>
      traces of the
      <em class="replaceable">
       <code>
        offset
       </code>
      </em>
      oldest stored traces (if
      <em class="replaceable">
       <code>
        offset
       </code>
      </em>
      &gt;= 0), or the first
      <em class="replaceable">
       <code>
        limit
       </code>
      </em>
      traces of the
      <em class="replaceable">
       <code>
        -offset
       </code>
      </em>
      newest stored traces (if
      <em class="replaceable">
       <code>
        offset
       </code>
      </em>
      &lt; 0).
     </p>
    </li>
   </ul>
  </div>
  <p>
   Examples:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <code class="literal">
       SET optimizer_trace_offset=-1,
          optimizer_trace_limit=1
      </code>
      : The most recent trace is
          shown (the default).
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       SET optimizer_trace_offset=-2,
          optimizer_trace_limit=1
      </code>
      : The next-to-last trace is
          shown.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       SET optimizer_trace_offset=-5,
          optimizer_trace_limit=5
      </code>
      : The last five traces are
          shown.
     </p>
    </li>
   </ul>
  </div>
  <p>
   Negative values for
   <em class="replaceable">
    <code>
     offset
    </code>
   </em>
   can thus
      prove useful when the substatements of interest are the last few
      in a stored routine. For example:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa63729201"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SET</span> optimizer_trace_offset<span class="token operator">=</span><span class="token operator">-</span><span class="token number">5</span><span class="token punctuation">,</span> optimizer_trace_limit<span class="token operator">=</span><span class="token number">5</span><span class="token punctuation">;</span>

<span class="token keyword">CALL</span> stored_routine<span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token comment" spellcheck="true"># more than 5 substatements in this routine</span>

<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> information_schema<span class="token punctuation">.</span>OPTIMIZER_TRACE<span class="token punctuation">;</span> <span class="token comment" spellcheck="true"># see only the last 5 traces</span></code></pre>
  </div>
  <p>
   A positive
   <em class="replaceable">
    <code>
     offset
    </code>
   </em>
   can be useful when
      one knows that the interesting substatements are the first few in
      a stored routine.
  </p>
  <p>
   The more accurately these two variables are set, the less memory
      is used. For example,
   <code class="literal">
    SET optimizer_trace_offset=0,
      optimizer_trace_limit=5
   </code>
   requires sufficient memory to
      store five traces, so if only the three first are needed, is is
      better to use
   <code class="literal">
    SET optimizer_trace_offset=0,
      optimizer_trace_limit=3
   </code>
   , since tracing stops after
   <em class="replaceable">
    <code>
     limit
    </code>
   </em>
   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
   <em class="replaceable">
    <code>
     offset
    </code>
   </em>
   and
   <em class="replaceable">
    <code>
     limit
    </code>
   </em>
   can restrict tracing to, for
      example, a single iteration of the loop. This also decreases the
      impact of tracing on execution speed.
  </p>
  <p>
   If
   <em class="replaceable">
    <code>
     offset
    </code>
   </em>
   is greater than or equal to
      0, only
   <em class="replaceable">
    <code>
     limit
    </code>
   </em>
   traces are kept in
      memory. If
   <em class="replaceable">
    <code>
     offset
    </code>
   </em>
   is less than 0, that
      is not true: instead,
   <em class="replaceable">
    <code>
     -offset
    </code>
   </em>
   traces
      are kept in memory. Even if
   <em class="replaceable">
    <code>
     limit
    </code>
   </em>
   is
      smaller than
   <em class="replaceable">
    <code>
     -offset
    </code>
   </em>
   , 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
   <span class="quote">
    “
    <span class="quote">
     window
    </span>
    ”
   </span>
   moves as more statements execute.
  </p>
  <p>
   Using
   <code class="literal">
    optimizer_trace_offset
   </code>
   and
   <code class="literal">
    optimizer_trace_limit
   </code>
   , 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
   <code class="literal">
    SELECT * FROM OPTIMIZER_TRACE
      LIMIT
    <em class="replaceable">
     <code>
      limit
     </code>
    </em>
    OFFSET
    <em class="replaceable">
     <code>
      offset
     </code>
    </em>
   </code>
   , which saves almost
      nothing.
  </p>
 </div>
 <br/>
</div>
