Help Center/ TaurusDB/ Kernel/ Query Optimization/ Execution Plan Management/ Statement Outline

Updated on 2026-11-03 GMT+08:00

Statement Outline

Scenarios

During TaurusDB instance operation, the execution plans of SQL statements may change frequently, resulting in unstable database performance. TaurusDB provides the Statement Outline feature, which uses MySQL Optimizer Hints and Index Hints to stabilize execution plans and provides management interfaces through the dbms_outln package. Statement Outline supports fixing execution plans for user tables, user views, system tables, and system views.

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

Log in to the TaurusDB console.
On the Instances page, click the instance name to go to the Basic Information page.
In the navigation pane, choose Parameters.

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.

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 Position indicates the query block. Because all Optimizer Hints must apply to a query block, Position starts from 1 and indicates the position of the keyword in the SQL statement to which the hint applies. Index Hints Position indicates the position of a table. It also starts from 1 and specifies the table to which the hint applies.
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

**Table 5** Parameter description
Parameter	Mandatory	Type	Description
Schema_name	Yes	VARCHAR	Database name.
Query	Yes	VARCHAR	SQL statement.

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.

Examples

Example 1: Pinning the Execution Plan for Access to the mysql.db System Table

By default, the following query statement uses the User index in its execution plan.

mysql> EXPLAIN SELECT Host, Db, User FROM mysql.db WHERE User = "root"\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: db
   partitions: NULL
         type: ref
possible_keys: PRIMARY,User
          key: User
      key_len: 96
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

Use Statement Outline to fix the execution plan and force the use of the primary key index (PRIMARY).

mysql> CALL dbms_outln.add_index_outline('', '', 1, 'FORCE INDEX', 'PRIMARY', '', 'SELECT Host, Db, User FROM mysql.db WHERE User = "root"');
Query OK, 0 rows affected (0.01 sec)

Check whether the Statement Outline rule has taken effect.

mysql> call dbms_outln.show_outline()\G
*************************** 1. row ***************************
         ID: 1
     SCHEMA: 
     DIGEST: deca3340e9cdb939599fd68aaf3ba61c5d71b8fccab4f0406386ae006f05349d
       TYPE: FORCE INDEX
      SCOPE: 
        POS: 1
       HINT: PRIMARY
        HIT: 0
   OVERFLOW: 0
DIGEST_TEXT: SELECT HOST , `Db` , SYSTEM_USER FROM `mysql` . `db` WHERE SYSTEM_USER = ?
1 row in set (0.01 sec)

mysql> CALL dbms_outln.preview_outline('', 'SELECT Host, Db, User FROM mysql.db WHERE User = "root"')\G
*************************** 1. row ***************************
    SCHEMA: 
    DIGEST: deca3340e9cdb939599fd68aaf3ba61c5d71b8fccab4f0406386ae006f05349d
BLOCK_TYPE: TABLE
BLOCK_NAME: db
     BLOCK: 1
      HINT: FORCE INDEX (PRIMARY)
1 row in set (0.00 sec)

Run the application SQL statement and verify that the execution plan now uses the primary key index (PRIMARY).

mysql> EXPLAIN SELECT Host, Db, User FROM mysql.db WHERE User = "root"\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: db
   partitions: NULL
         type: index
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 543
          ref: NULL
         rows: 2
     filtered: 50.00
        Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)

Delete the Statement Outline rule.

mysql> call dbms_outln.del_outline(1);
Query OK, 0 rows affected (0.01 sec)

mysql> call dbms_outln.show_outline();
Empty set (0.01 sec)

Example 2: Pinning the Execution Plan for Access to the INFORMATION_SCHEMA.TABLES System View

By default, the following query statement uses the table join order cat → sch → tbl.

mysql> EXPLAIN SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, TABLE_COMMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = "mysql" AND TABLE_NAME = "user"\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: cat
   partitions: NULL
         type: index
possible_keys: PRIMARY
          key: name
      key_len: 194
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using index
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: sch
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY,catalog_id
          key: catalog_id
      key_len: 202
          ref: mysql.cat.id,const
         rows: 1
     filtered: 100.00
        Extra: Using index
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: tbl
   partitions: NULL
         type: eq_ref
possible_keys: schema_id
          key: schema_id
      key_len: 202
          ref: mysql.sch.id,const
         rows: 1
     filtered: 100.00
        Extra: Using where
*************************** 4. row ***************************
           id: 1
  select_type: SIMPLE
        table: col
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: mysql.tbl.collation_id
         rows: 1
     filtered: 100.00
        Extra: Using index
*************************** 5. row ***************************
           id: 1
  select_type: SIMPLE
        table: ts
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: mysql.tbl.tablespace_id
         rows: 1
     filtered: 100.00
        Extra: Using index
*************************** 6. row ***************************
           id: 1
  select_type: SIMPLE
        table: stat
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 388
          ref: const,const
         rows: 1
     filtered: 100.00
        Extra: Using index
6 rows in set, 1 warning (0.02 sec)

Use Statement Outline to fix the execution plan and change the table join order to cat → tbl → sch.

mysql> CALL dbms_outln.add_optimizer_outline('', '', 1,
    ->   '/*+ JOIN_ORDER(@`select#1` cat,tbl,sch) */',
    ->   'SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, TABLE_COMMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = "mysql" AND TABLE_NAME = "user"');
Query OK, 0 rows affected (0.01 sec)