Help Center/ TaurusDB/ Kernel/ Query Optimization/ Execution Plan Management/ Statement Outline
Updated on 2026-01-15 GMT+08:00
View PDF
Share

Statement Outline

Scenarios

During the runtime of a MySQL instance, the execution plan of a SQL statement may change frequently, causing database instability. To resolve the issue, TaurusDB provides the Statement Outline function, which uses MySQL optimizer and index hints to stabilize plan execution. TaurusDB also provides a group of management interfaces (dbms_outln package) for easy use.

Prerequisites

The kernel version of your TaurusDB instance must be 2.0.42.230600 or later. For details about how to check the kernel version, see How Can I Check the Version of a TaurusDB Instance?

Precautions

  • Statement Outline is disabled by default. To enable it, see Enabling Statement Outline.
  • If Statement Outline is disabled, the performance is not affected. If there are a large number of rules after Statement Outline is enabled, the performance deteriorates.

Description

Statement Outline supports the optimizer hints and index hints of MySQL 8.0.

  • Optimizer hints

    Optimizer hints are classified into Global-Level Hint, Table-Level Hint, Index-Level Hint and Join-Order Hints based on the scope (query blocks) and Hint objects. For details, see Optimizer Hints.

  • Index hints

    Index hints provide the optimizer with information about how to select indexes during query processing without changing the optimizer's policy. There are three common index hints: USE INDEX hint, IGNORE INDEX hint, and FORCE INDEX hint. For details, see Index Hints.

Enabling Statement Outline

  1. Log in to the TaurusDB console.
  2. On the Instances page, click the instance name to go to the Basic Information page.
  3. In the navigation pane, choose Parameters.
  4. Search for rds_opt_outline_enabled in the search box and change its value to ON.

    Table 1 Parameter description

    Parameter

    Description

    rds_opt_outline_enabled

    Controls whether to enable Statement Outline.

    • ON: Statement Outline is enabled.
    • OFF: Statement Outline is disabled.

  5. Click Save.

Statement Outline Table Description

TaurusDB has a built-in system table (outline) to store hints. This table is automatically created when the system is started. The SQL statements for creating the table are as follows.

CREATE TABLE `mysql`.`outline` (
  `Id` bigint(20) NOT NULL AUTO_INCREMENT,
  `Schema_name` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `Digest` varchar(64) COLLATE utf8_bin NOT NULL,
  `Digest_text` longtext COLLATE utf8_bin,
  `Type` enum('IGNORE INDEX','USE INDEX','FORCE INDEX','OPTIMIZER') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `Scope` enum('','FOR JOIN','FOR ORDER BY','FOR GROUP BY') CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT '',
  `State` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'Y',
  `Position` bigint(20) NOT NULL,
  `Hint` text COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB
 DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0 COMMENT='Statement outline'

For details about the parameter description, see the following table.

Table 2 Parameter description

Parameter

Description

Id

ID of the outline table.

Schema_name

Database name.

Digest

64-byte hash string calculated from Digest_text during the hash calculation.

Digest_text

Digest of the SQL statement.

Type

In optimizer hints, the value is OPTIMIZER.

In index hints, the value can be USE INDEX, FORCE INDEX, or IGNORE INDEX.

Scope

This field is required only for index hints. Its value can be:

  • FOR GROUP BY
  • FOR ORDER BY
  • FOR JOIN
  • An empty string
    NOTE:

    If this field is set to an empty string, it indicates all types of index hints.

State

Whether Statement Outline is enabled. Its value can be:

  • N
  • Y (default value)

Position
  • Optimizer hints

    Sequence number of the keyword in query blocks on which the hint is applied. Its value starts from 1. All optimizer hints must be applied to the query block.

  • Index hints

    Sequence number of the table on which the hint is applied. Its value starts from 1.

Hint

  • Optimizer hints

    A complete hint string, for example, /*+ MAX_EXECUTION_TIME(1000) */

  • Index hints

    A list of index names, for example, ind_1,ind_2

Statement Outline Management

There are six local storage rules to manage Statement Outline.

add_optimizer_outline

  • Function

    Adds optimizer hints.

  • Syntax 
    dbms_outln.add_optimizer_outline(Schema_name,Digest,Query_block,Hint,Query);

    You can set either Digest or Query (original SQL statement). If you set Query, DBMS_OUTLN calculates Digest and Digest_text. You are advised to set Query directly.

  • Parameters
    Table 3 Parameter description

    Parameter

    Mandatory

    Type

    Description

    Schema_name

    Yes

    VARCHAR

    Name of the database that the statement belongs to.

    This parameter can be set to NULL or left blank. If it is, the statement cannot be matched.

    Digest

    No

    VARCHAR

    Hash value of the statement.

    You can set either this parameter or Query. If you do not want to set it to a specific value, set it to an empty string.

    Query_block

    Yes

    INT

    Position of the object that the hint applies to.

    The value must be greater than or equal to 1.

    Hint

    Yes

    VARCHAR

    Hint name.

    Query

    No

    VARCHAR

    SQL statement.

    • You can set either this parameter or Digest. If you do not want to set it to a specific value, set it to an empty string ('').
    • If both of them are set, check whether Digest and Query match. If they do not match, the parameter verification fails and the execution fails.
  • Example

add_index_outline

  • Function

    Adds index hints.

  • Syntax 
    dbms_outln.add_index_outline(Schema_name,Digest,Position,Type,Hint,Scope,Query);

    You can set either Digest or Query (original SQL statement). If you set Query, DBMS_OUTLN calculates Digest and Digest_text. You are advised to set Query directly.

  • Parameters
    Table 4 Parameter description

    Parameter

    Mandatory

    Type

    Description

    Schema_name

    Yes

    VARCHAR

    Name of the database that the statement belongs to.

    This parameter can be set to NULL or left blank. If it is, the statement cannot be matched.

    Digest

    No

    VARCHAR

    Hash value of the statement.

    You can set either this parameter or Query. If you do not want to set it to a specific value, set it to an empty string ('').

    Position

    Yes

    INT

    Position of the table that the index hint applies to in the statement.

    The value must be greater than or equal to 1.

    Type

    Yes

    ENUM

    Hint type. Its value can be:

    • OPTIMIZER
    • USE INDEX
    • FORCE INDEX
    • IGNORE INDEX

    Hint

    Yes

    VARCHAR

    Hint name or index name set. Use commas (,) to separate multiple index names.

    Scope

    Yes

    ENUM

    Hint scope. Its value can be:

    • FOR GROUP BY
    • FOR ORDER BY
    • FOR JOIN
    • An empty string

    Query

    No

    VARCHAR

    SQL statement.

    • You can set either this parameter or Digest. If you do not want to set it to a specific value, set it to an empty string ('').
    • If both of them are set, check whether Digest and Query match. If they do not match, the parameter verification fails and the execution fails.
  • Example 
    call dbms_outln.add_index_outline('outline_db', '', 1, 'USE INDEX', 'ind_1', '',"select * from t1 where t1.col1 =1 and t1.col2 ='xpchild'");

preview_outline

  • Function

    Queries the status of the SQL statement matching the statement outline, which can be used for manual verification.

  • Syntax 
    dbms_outln.preview_outline(Schema_name,Query);
  • Parameters
    Table 5 Parameter description

    Parameter

    Mandatory

    Type

    Description

    Schema_name

    Yes

    VARCHAR

    Database name.

    Query

    Yes

    VARCHAR

    SQL statement.
  • Example

show_outline

  • Function

    Displays the in-memory hit rate of the statement outline.

  • Syntax 
    dbms_outln.show_outline();
  • Example

    HIT and OVERFLOW description

    1. HIT indicates the number of times that the statement outline finds the destination query block or table.
    2. OVERFLOW indicates the number of times that statement outline does not find the destination query block or table.

del_outline

  • Function

    Deletes a statement outline from the memory and table.

  • Syntax 
    dbms_outln.del_outline(id);
  • Parameter
    Table 6 Parameter description

    Parameter

    Mandatory

    Type

    Description

    id

    Yes

    INT

    Statement outline ID, which is the value in the id column in the mysql.outline table. The value cannot be left blank.
  • Example

    If the statement outline to be deleted is not found, the system will report a warning. You can run the show warnings; command to view the warning content.

flush_outline

  • Function

    If you modify the statement outline in the outline table, you need to make the statement outline take effect again.

  • Syntax 
    dbms_outln.flush_outline();
  • Example 
    update mysql.outline set Position = 1 where Id = 18;
call dbms_outln.flush_outline();

Function Verification

To check whether the statement outline takes effect, perform the following steps:

  • Use the preview_outline interface.

  • Run the EXPLAIN command.

Parent Topic: Execution Plan Management