Statement outline lets you bind optimizer hints or index hints to specific SQL statements, preventing execution plan drift without modifying application code. Use it proactively to lock plans for critical queries before they regress, or reactively to fix queries that changed behavior after a statistics update or engine upgrade.
With statement outline, you can:
Lock execution plans for critical queries so they don't change when statistics update or the engine upgrades.
Apply optimizer hints or index hints to any SQL statement by matching its digest — no query rewrite required.
Preview, inspect, and remove outlines without restarting the instance.
Tip: To confirm that an outline is active, runEXPLAINon the target query and checkSHOW WARNINGSfor the applied hint. Alternatively, callCALL dbms_outln.preview_outline()to check which outlines match a query before you apply it.
Prerequisites
Before you begin, ensure that your RDS instance runs one of the following MySQL engine versions:
MySQL 8.0
MySQL 5.7
How it works
When AliSQL receives a query, it computes the query's digest and checks the mysql.outline table for a matching entry. If a match is found and the outline is enabled, AliSQL injects the stored hint into the execution plan. The outline table is created automatically on instance startup.
Statement outline supports two hint types:
| Hint type | Applicable scopes | Reference |
|---|---|---|
| Optimizer hint | Global-level, table-level, index-level, and join-order | Optimizer Hints |
| Index hint | Table level, with optional FOR JOIN, FOR ORDER BY, or FOR GROUP BY scope | Index Hints |
Outline table schema
AliSQL stores all outlines in the mysql.outline table. The DDL is:
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`)
) /*!50100 TABLESPACE `mysql` */ ENGINE=InnoDB
DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0 COMMENT='Statement outline'Column descriptions:
| Column | Description |
|---|---|
Id | Auto-incremented ID for each outline entry. |
Schema_name | Database name. |
Digest | 64-byte hash string derived from Digest_text. |
Digest_text | Normalized form of the SQL statement. |
Type | Hint type: OPTIMIZER for optimizer hints; USE INDEX, FORCE INDEX, or IGNORE INDEX for index hints. |
Scope | Applies only to index hints. Valid values: FOR JOIN, FOR ORDER BY, FOR GROUP BY. An empty string means the hint applies to all contexts. |
State | Y to enable the outline; N to disable it. |
Position | For optimizer hints: the sequential number of the query block keyword (starting from 1). For index hints: the sequential number of the table in the query (starting from 1). |
Hint | For optimizer hints: the full hint string, for example /*+ MAX_EXECUTION_TIME(1000) */. For index hints: a comma-separated list of index names, for example ind_1,ind_2. |
Manage statement outlines
DBMS_OUTLN provides five stored procedures for managing outlines. Call each procedure with CALL dbms_outln.<procedure>().
Add an optimizer hint
CALL dbms_outln.add_optimizer_outline('<Schema_name>', '<Digest>', <query_block>, '<hint>', '<query>');Provide either Digest or query — leave the other blank. If you provide a query string, DBMS_OUTLN computes Digest and Digest_text automatically.
Example: Apply a 1-second execution time limit to a specific query.
CALL dbms_outln.add_optimizer_outline("outline_db", '', 1, '/*+ MAX_EXECUTION_TIME(1000) */',
"select * from t1 where id = 1");Add an index hint
CALL dbms_outln.add_index_outline('<Schema_name>', '<Digest>', <Position>, '<Type>', '<Hint>', '<Scope>', '<Query>');Provide either Digest or Query — leave the other blank. If you provide a query string, DBMS_OUTLN computes Digest and Digest_text automatically.
Example: Force the query to use ind_1 on t1.
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 matched outlines
Check which outlines match a query before the query runs — useful for manual validation.
CALL dbms_outln.preview_outline('<Schema_name>', '<Query>');Note: Themax_digest_lengthparameter controls how much of the SQL statement is hashed. If a query matches the wrong outline, increasemax_digest_length.
Example:
CALL dbms_outln.preview_outline('outline_db', "select * from t1 where t1.col1 = 1 and t1.col2 = 'xpchild'");Expected output:
+------------+------------------------------------------------------------------+------------+------------+-------+---------------------+
| SCHEMA | DIGEST | BLOCK_TYPE | BLOCK_NAME | BLOCK | HINT |
+------------+------------------------------------------------------------------+------------+------------+-------+---------------------+
| outline_db | b4369611be7ab2d27c85897632576a04bc08f50b928a1d735b62d0a140628c4c | TABLE | t1 | 1 | USE INDEX (`ind_1`) |
+------------+------------------------------------------------------------------+------------+------------+-------+---------------------+
1 row in set (0.00 sec)Show active outlines
List all outlines currently loaded in memory, along with hit and overflow counts.
CALL dbms_outln.show_outline();Example output:
+------+------------+------------------------------------------------------------------+-----------+-------+------+-------------------------------------------------------+------+----------+-------------------------------------------------------------------------------------+
| ID | SCHEMA | DIGEST | TYPE | SCOPE | POS | HINT | HIT | OVERFLOW | DIGEST_TEXT |
+------+------------+------------------------------------------------------------------+-----------+-------+------+-------------------------------------------------------+------+----------+-------------------------------------------------------------------------------------+
| 33 | outline_db | 36bebc61fce7e32b93926aec3fdd790dad5d895107e2d8d3848d1c60b74bcde6 | OPTIMIZER | | 1 | /*+ SET_VAR(foreign_key_checks=OFF) */ | 1 | 0 | SELECT * FROM `t1` WHERE `id` = ? |
| 32 | outline_db | 36bebc61fce7e32b93926aec3fdd790dad5d895107e2d8d3848d1c60b74bcde6 | OPTIMIZER | | 1 | /*+ MAX_EXECUTION_TIME(1000) */ | 2 | 0 | SELECT * FROM `t1` WHERE `id` = ? |
| 34 | outline_db | d4dcef634a4a664518e5fb8a21c6ce9b79fccb44b773e86431eb67840975b649 | OPTIMIZER | | 1 | /*+ BNL(t1,t2) */ | 1 | 0 | SELECT `t1` . `id` , `t2` . `id` FROM `t1` , `t2` |
| 35 | outline_db | 5a726a609b6fbfb76bb8f9d2a24af913a2b9d07f015f2ee1f6f2d12dfad72e6f | OPTIMIZER | | 2 | /*+ QB_NAME(subq1) */ | 2 | 0 | SELECT * FROM `t1` WHERE `t1` . `col1` IN ( SELECT `col1` FROM `t2` ) |
| 36 | outline_db | 5a726a609b6fbfb76bb8f9d2a24af913a2b9d07f015f2ee1f6f2d12dfad72e6f | OPTIMIZER | | 1 | /*+ SEMIJOIN(@subq1 MATERIALIZATION, DUPSWEEDOUT) */ | 2 | 0 | SELECT * FROM `t1` WHERE `t1` . `col1` IN ( SELECT `col1` FROM `t2` ) |
| 30 | outline_db | b4369611be7ab2d27c85897632576a04bc08f50b928a1d735b62d0a140628c4c | USE INDEX | | 1 | ind_1 | 3 | 0 | SELECT * FROM `t1` WHERE `t1` . `col1` = ? AND `t1` . `col2` = ? |
| 31 | outline_db | 33c71541754093f78a1f2108795cfb45f8b15ec5d6bff76884f4461fb7f33419 | USE INDEX | | 2 | ind_2 | 1 | 0 | SELECT * FROM `t1` , `t2` WHERE `t1` . `col1` = `t2` . `col1` AND `t2` . `col2` = ? |
+------+------------+------------------------------------------------------------------+-----------+-------+------+-------------------------------------------------------+------+----------+-------------------------------------------------------------------------------------+
7 rows in set (0.00 sec)The HIT column shows how many times the outline matched its target query block or table. The OVERFLOW column shows how many times the target was not found.
Delete an outline
Remove an outline from memory or the outline table by its Id.
CALL dbms_outln.del_outline(<Id>);Example:
CALL dbms_outln.del_outline(32);If the outline does not exist, the system raises a warning. Run SHOW WARNINGS to see the details.
CALL dbms_outln.del_outline(1000);
-- Query OK, 0 rows affected, 2 warnings (0.00 sec)
SHOW WARNINGS;Expected output:
+---------+------+----------------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------------+
| Warning | 7521 | Statement outline 1000 is not found in table |
| Warning | 7521 | Statement outline 1000 is not found in cache |
+---------+------+----------------------------------------------+
2 rows in set (0.00 sec)Reload outlines after a manual edit
If you modify the mysql.outline table directly, call flush_outline to reload the changes into memory.
CALL dbms_outln.flush_outline();Example: Update the position of outline 18, then flush.
UPDATE mysql.outline SET Position = 1 WHERE Id = 18;
-- Query OK, 1 row affected (0.00 sec)
CALL dbms_outln.flush_outline();
-- Query OK, 0 rows affected (0.01 sec)Verify that an outline is active
Use either method to confirm the outline is applied.
Method 1: preview_outline
CALL dbms_outln.preview_outline('outline_db', "select * from t1 where t1.col1 = 1 and t1.col2 = 'xpchild'");A non-empty result means the query matches an active outline:
+------------+------------------------------------------------------------------+------------+------------+-------+---------------------+
| SCHEMA | DIGEST | BLOCK_TYPE | BLOCK_NAME | BLOCK | HINT |
+------------+------------------------------------------------------------------+------------+------------+-------+---------------------+
| outline_db | b4369611be7ab2d27c85897632576a04bc08f50b928a1d735b62d0a140628c4c | TABLE | t1 | 1 | USE INDEX (`ind_1`) |
+------------+------------------------------------------------------------------+------------+------------+-------+---------------------+
1 row in set (0.01 sec)Method 2: EXPLAIN + SHOW WARNINGS
EXPLAIN SELECT * FROM t1 WHERE t1.col1 = 1 AND t1.col2 = 'xpchild';+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | t1 | NULL | ref | ind_1 | ind_1 | 5 | const | 1 | 100.00 | Using where |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)Then check the rewritten query in SHOW WARNINGS to confirm the hint was injected:
SHOW WARNINGS;+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1003 | /* select#1 */ select `outline_db`.`t1`.`id` AS `id`,`outline_db`.`t1`.`col1` AS `col1`,`outline_db`.`t1`.`col2` AS `col2` from `outline_db`.`t1` USE INDEX (`ind_1`) where ((`outline_db`.`t1`.`col1` = 1) and (`outline_db`.`t1`.`col2` = 'xpchild')) |
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)The USE INDEX (ind_1) in the rewritten query confirms the outline is active.