<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="performance-schema-prepared-statements-instances-table">
      </a>
      29.12.6.4 The prepared_statements_instances Table
     </h4>
    </div>
   </div>
  </div>
  <p>
   The Performance Schema provides instrumentation for prepared
          statements, for which there are two protocols:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      The binary protocol. This is accessed through the MySQL C
              API and maps onto underlying server commands as shown in
              the following table.
     </p>
     <div class="informaltable">
      <table summary="How the binary protocol accessed through the MySQL C API maps onto underlying server commands.">
       <colgroup>
        <col style="width: 50%"/>
        <col style="width: 50%"/>
       </colgroup>
       <thead>
        <tr>
         <th>
          C API Function
         </th>
         <th>
          Corresponding Server Command
         </th>
        </tr>
       </thead>
       <tbody>
        <tr>
         <td>
          <a class="ulink" href="/doc/c-api/8.4/en/mysql-stmt-prepare.html" target="_top">
           <code class="literal">
            mysql_stmt_prepare()
           </code>
          </a>
         </td>
         <td>
          <code class="literal">
           COM_STMT_PREPARE
          </code>
         </td>
        </tr>
        <tr>
         <td>
          <a class="ulink" href="/doc/c-api/8.4/en/mysql-stmt-execute.html" target="_top">
           <code class="literal">
            mysql_stmt_execute()
           </code>
          </a>
         </td>
         <td>
          <code class="literal">
           COM_STMT_EXECUTE
          </code>
         </td>
        </tr>
        <tr>
         <td>
          <a class="ulink" href="/doc/c-api/8.4/en/mysql-stmt-close.html" target="_top">
           <code class="literal">
            mysql_stmt_close()
           </code>
          </a>
         </td>
         <td>
          <code class="literal">
           COM_STMT_CLOSE
          </code>
         </td>
        </tr>
       </tbody>
      </table>
     </div>
    </li>
    <li class="listitem">
     <p>
      The text protocol. This is accessed using SQL statements
              and maps onto underlying server commands as shown in the
              following table.
     </p>
     <div class="informaltable">
      <table summary="How the text protocol accessed using SQL statements maps onto underlying server commands.">
       <colgroup>
        <col style="width: 50%"/>
        <col style="width: 50%"/>
       </colgroup>
       <thead>
        <tr>
         <th>
          SQL Statement
         </th>
         <th>
          Corresponding Server Command
         </th>
        </tr>
       </thead>
       <tbody>
        <tr>
         <td>
          <a class="link" href="prepare.html" title="15.5.1 PREPARE Statement">
           <code class="literal">
            PREPARE
           </code>
          </a>
         </td>
         <td>
          <code class="literal">
           SQLCOM_PREPARE
          </code>
         </td>
        </tr>
        <tr>
         <td>
          <a class="link" href="execute.html" title="15.5.2 EXECUTE Statement">
           <code class="literal">
            EXECUTE
           </code>
          </a>
         </td>
         <td>
          <code class="literal">
           SQLCOM_EXECUTE
          </code>
         </td>
        </tr>
        <tr>
         <td>
          <a class="link" href="deallocate-prepare.html" title="15.5.3 DEALLOCATE PREPARE Statement">
           <code class="literal">
            DEALLOCATE PREPARE
           </code>
          </a>
          ,
          <a class="link" href="deallocate-prepare.html" title="15.5.3 DEALLOCATE PREPARE Statement">
           <code class="literal">
            DROP
                    PREPARE
           </code>
          </a>
         </td>
         <td>
          <code class="literal">
           SQLCOM_DEALLOCATE PREPARE
          </code>
         </td>
        </tr>
       </tbody>
      </table>
     </div>
    </li>
   </ul>
  </div>
  <p>
   Performance Schema prepared statement instrumentation covers
          both protocols. The following discussion refers to the server
          commands rather than the C API functions or SQL statements.
  </p>
  <p>
   Information about prepared statements is available in the
   <a class="link" href="performance-schema-prepared-statements-instances-table.html" title="29.12.6.4 The prepared_statements_instances Table">
    <code class="literal">
     prepared_statements_instances
    </code>
   </a>
   table. This table enables inspection of prepared statements
          used in the server and provides aggregated statistics about
          them. To control the size of this table, set the
   <a class="link" href="performance-schema-system-variables.html#sysvar_performance_schema_max_prepared_statements_instances">
    <code class="literal">
     performance_schema_max_prepared_statements_instances
    </code>
   </a>
   system variable at server startup.
  </p>
  <p>
   Collection of prepared statement information depends on the
          statement instruments shown in the following table. These
          instruments are enabled by default. To modify them, update the
   <a class="link" href="performance-schema-setup-instruments-table.html" title="29.12.2.3 The setup_instruments Table">
    <code class="literal">
     setup_instruments
    </code>
   </a>
   table.
  </p>
  <div class="informaltable">
   <table summary="Collection of prepared statement information depends on the statement instruments shown in this table.">
    <colgroup>
     <col style="width: 50%"/>
     <col style="width: 50%"/>
    </colgroup>
    <thead>
     <tr>
      <th>
       Instrument
      </th>
      <th>
       Server Command
      </th>
     </tr>
    </thead>
    <tbody>
     <tr>
      <td>
       <code class="literal">
        statement/com/Prepare
       </code>
      </td>
      <td>
       <code class="literal">
        COM_STMT_PREPARE
       </code>
      </td>
     </tr>
     <tr>
      <td>
       <code class="literal">
        statement/com/Execute
       </code>
      </td>
      <td>
       <code class="literal">
        COM_STMT_EXECUTE
       </code>
      </td>
     </tr>
     <tr>
      <td>
       <code class="literal">
        statement/sql/prepare_sql
       </code>
      </td>
      <td>
       <code class="literal">
        SQLCOM_PREPARE
       </code>
      </td>
     </tr>
     <tr>
      <td>
       <code class="literal">
        statement/sql/execute_sql
       </code>
      </td>
      <td>
       <code class="literal">
        SQLCOM_EXECUTE
       </code>
      </td>
     </tr>
    </tbody>
   </table>
  </div>
  <p>
   The Performance Schema manages the contents of the
   <a class="link" href="performance-schema-prepared-statements-instances-table.html" title="29.12.6.4 The prepared_statements_instances Table">
    <code class="literal">
     prepared_statements_instances
    </code>
   </a>
   table as follows:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Statement preparation
     </p>
     <p>
      A
      <code class="literal">
       COM_STMT_PREPARE
      </code>
      or
      <code class="literal">
       SQLCOM_PREPARE
      </code>
      command creates a
              prepared statement in the server. If the statement is
              successfully instrumented, a new row is added to the
      <a class="link" href="performance-schema-prepared-statements-instances-table.html" title="29.12.6.4 The prepared_statements_instances Table">
       <code class="literal">
        prepared_statements_instances
       </code>
      </a>
      table. If the statement cannot be instrumented,
      <a class="link" href="performance-schema-status-variables.html#statvar_Performance_schema_prepared_statements_lost">
       <code class="literal">
        Performance_schema_prepared_statements_lost
       </code>
      </a>
      status variable is incremented.
     </p>
    </li>
    <li class="listitem">
     <p>
      Prepared statement execution
     </p>
     <p>
      Execution of a
      <code class="literal">
       COM_STMT_EXECUTE
      </code>
      or
      <code class="literal">
       SQLCOM_PREPARE
      </code>
      command for an
              instrumented prepared statement instance updates the
              corresponding
      <a class="link" href="performance-schema-prepared-statements-instances-table.html" title="29.12.6.4 The prepared_statements_instances Table">
       <code class="literal">
        prepared_statements_instances
       </code>
      </a>
      table row.
     </p>
    </li>
    <li class="listitem">
     <p>
      Prepared statement deallocation
     </p>
     <p>
      Execution of a
      <code class="literal">
       COM_STMT_CLOSE
      </code>
      or
      <code class="literal">
       SQLCOM_DEALLOCATE_PREPARE
      </code>
      command for
              an instrumented prepared statement instance removes the
              corresponding
      <a class="link" href="performance-schema-prepared-statements-instances-table.html" title="29.12.6.4 The prepared_statements_instances Table">
       <code class="literal">
        prepared_statements_instances
       </code>
      </a>
      table row. To avoid resource leaks, removal occurs even if
              the prepared statement instruments described previously
              are disabled.
     </p>
    </li>
   </ul>
  </div>
  <p>
   The
   <a class="link" href="performance-schema-prepared-statements-instances-table.html" title="29.12.6.4 The prepared_statements_instances Table">
    <code class="literal">
     prepared_statements_instances
    </code>
   </a>
   table has these columns:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <code class="literal">
       OBJECT_INSTANCE_BEGIN
      </code>
     </p>
     <p>
      The address in memory of the instrumented prepared
              statement.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       STATEMENT_ID
      </code>
     </p>
     <p>
      The internal statement ID assigned by the server. The text
              and binary protocols both use statement IDs.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       STATEMENT_NAME
      </code>
     </p>
     <p>
      For the binary protocol, this column is
      <code class="literal">
       NULL
      </code>
      . For the text protocol, this
              column is the external statement name assigned by the
              user. For example, for the following SQL statement, the
              name of the prepared statement is
      <code class="literal">
       stmt
      </code>
      :
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa23577929"><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">PREPARE</span> stmt <span class="token keyword">FROM</span> <span class="token string">'SELECT 1'</span><span class="token punctuation">;</span></code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       SQL_TEXT
      </code>
     </p>
     <p>
      The prepared statement text, with
      <code class="literal">
       ?
      </code>
      placeholder markers.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       OWNER_THREAD_ID
      </code>
      ,
      <code class="literal">
       OWNER_EVENT_ID
      </code>
     </p>
     <p>
      These columns indicate the event that created the prepared
              statement.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       OWNER_OBJECT_TYPE
      </code>
      ,
      <code class="literal">
       OWNER_OBJECT_SCHEMA
      </code>
      ,
      <code class="literal">
       OWNER_OBJECT_NAME
      </code>
     </p>
     <p>
      For a prepared statement created by a client session,
              these columns are
      <code class="literal">
       NULL
      </code>
      . For a prepared
              statement created by a stored program, these columns point
              to the stored program. A typical user error is forgetting
              to deallocate prepared statements. These columns can be
              used to find stored programs that leak prepared
              statements:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa15995294"><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">SELECT</span>
  OWNER_OBJECT_TYPE<span class="token punctuation">,</span> OWNER_OBJECT_SCHEMA<span class="token punctuation">,</span> OWNER_OBJECT_NAME<span class="token punctuation">,</span>
  STATEMENT_NAME<span class="token punctuation">,</span> SQL_TEXT
<span class="token keyword">FROM</span> performance_schema<span class="token punctuation">.</span>prepared_statements_instances
<span class="token keyword">WHERE</span> OWNER_OBJECT_TYPE <span class="token operator">IS</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">;</span></code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      The query execution engine. The value is either
      <code class="literal">
       PRIMARY
      </code>
      or
      <code class="literal">
       SECONDARY
      </code>
      . For use with HeatWave Service and
              HeatWave, where the
      <code class="literal">
       PRIMARY
      </code>
      engine is
      <code class="literal">
       InnoDB
      </code>
      and the
      <code class="literal">
       SECONDARY
      </code>
      engine is HeatWave
              (
      <code class="literal">
       RAPID
      </code>
      ). For MySQL Community Edition Server, MySQL Enterprise Edition Server
              (on-premise), and HeatWave Service without HeatWave, the value is
              always
      <code class="literal">
       PRIMARY
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       TIMER_PREPARE
      </code>
     </p>
     <p>
      The time spent executing the statement preparation itself.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       COUNT_REPREPARE
      </code>
     </p>
     <p>
      The number of times the statement was reprepared
              internally (see
      <a class="xref" href="statement-caching.html" title="10.10.3 Caching of Prepared Statements and Stored Programs">
       Section 10.10.3, “Caching of Prepared Statements and Stored Programs”
      </a>
      ).
              Timing statistics for repreparation are not available
              because it is counted as part of statement execution, not
              as a separate operation.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       COUNT_EXECUTE
      </code>
      ,
      <code class="literal">
       SUM_TIMER_EXECUTE
      </code>
      ,
      <code class="literal">
       MIN_TIMER_EXECUTE
      </code>
      ,
      <code class="literal">
       AVG_TIMER_EXECUTE
      </code>
      ,
      <code class="literal">
       MAX_TIMER_EXECUTE
      </code>
     </p>
     <p>
      Aggregated statistics for executions of the prepared
              statement.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       SUM_
       <em class="replaceable">
        <code>
         xxx
        </code>
       </em>
      </code>
     </p>
     <p>
      The remaining
      <code class="literal">
       SUM_
       <em class="replaceable">
        <code>
         xxx
        </code>
       </em>
      </code>
      columns are the same as for the statement summary tables
              (see
      <a class="xref" href="performance-schema-statement-summary-tables.html" title="29.12.20.3 Statement Summary Tables">
       Section 29.12.20.3, “Statement Summary Tables”
      </a>
      ).
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       MAX_CONTROLLED_MEMORY
      </code>
     </p>
     <p>
      Reports the maximum amount of controlled memory used by a
              prepared statement during execution.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       MAX_TOTAL_MEMORY
      </code>
     </p>
     <p>
      Reports the maximum amount of memory used by a prepared
              statement during execution.
     </p>
    </li>
   </ul>
  </div>
  <p>
   The
   <a class="link" href="performance-schema-prepared-statements-instances-table.html" title="29.12.6.4 The prepared_statements_instances Table">
    <code class="literal">
     prepared_statements_instances
    </code>
   </a>
   table has these indexes:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Primary key on (
      <code class="literal">
       OBJECT_INSTANCE_BEGIN
      </code>
      )
     </p>
    </li>
    <li class="listitem">
     <p>
      Index on (
      <code class="literal">
       STATEMENT_ID
      </code>
      )
     </p>
    </li>
    <li class="listitem">
     <p>
      Index on (
      <code class="literal">
       STATEMENT_NAME
      </code>
      )
     </p>
    </li>
    <li class="listitem">
     <p>
      Index on (
      <code class="literal">
       OWNER_THREAD_ID
      </code>
      ,
      <code class="literal">
       OWNER_EVENT_ID
      </code>
      )
     </p>
    </li>
    <li class="listitem">
     <p>
      Index on (
      <code class="literal">
       OWNER_OBJECT_TYPE
      </code>
      ,
      <code class="literal">
       OWNER_OBJECT_SCHEMA
      </code>
      ,
      <code class="literal">
       OWNER_OBJECT_NAME
      </code>
      )
     </p>
    </li>
   </ul>
  </div>
  <p>
   <a class="link" href="truncate-table.html" title="15.1.37 TRUNCATE TABLE Statement">
    <code class="literal">
     TRUNCATE TABLE
    </code>
   </a>
   resets the
          statistics columns of the
   <a class="link" href="performance-schema-prepared-statements-instances-table.html" title="29.12.6.4 The prepared_statements_instances Table">
    <code class="literal">
     prepared_statements_instances
    </code>
   </a>
   table.
  </p>
 </div>
 <br/>
</div>
