PolarDB:Statement Outline

Add an optimizer hint outline

CALL dbms_outln.add_optimizer_outline('<Schema_name>', '<Hint>', '<Query>');

When Query contains double-quoted strings, enclose the entire Query in double quotes and use single quotes inside, or vice versa. An outline created with single quotes in Query matches the SQL regardless of whether you run it with single or double quotes.

Example: Pin an execution time limit to a query:

Original SQL:

SELECT * FROM t1 WHERE name = "Tom";

Normalize quotes for the outline:

SELECT * FROM t1 WHERE name = 'Tom';

Add the outline:

CALL dbms_outln.add_optimizer_outline("", "/*+ max_execution_time(1000) */", "SELECT * FROM t1 WHERE name='Tom'");

Add an index hint outline

CALL dbms_outln.add_index_outline('<Schema_name>', '<Digest>', <Position>, '<Type>', '<Hint>', '<Scope>', '<Query>');

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 an outline

preview_outline shows which outlines match a given query without executing the query. Use this to validate an outline before relying on it in production.

CALL dbms_outln.preview_outline('<Schema_name>', '<Query>');

Example:

CALL dbms_outln.preview_outline('outline_db', "SELECT * FROM t1 WHERE t1.col1 = 1 AND t1.col2 = 'xpchild'");

Output:

+------------+------------------------------------------------------------------+------------+------------+-------+---------------------+
| 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)

View active outlines

show_outline lists all outlines currently loaded in memory, along with hit and overflow counts.

CALL dbms_outln.show_outline();

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` = ? |
+------+------------+------------------------------------------------------------------+-----------+-------+------+-------------------------------------------------------+------+----------+-------------------------------------------------------------------------------------+

• HIT: the number of times this outline was matched and applied.

• OVERFLOW: the number of times the outline could not find the target query block or table in the statement.

Delete an outline

CALL dbms_outln.del_outline(<Id>);

Pass the ID from show_outline. The outline is removed from both memory and the mysql.outline system table immediately.

Example:

CALL dbms_outln.del_outline(32);

If the ID does not exist, the system records a warning rather than an error:

CALL dbms_outln.del_outline(1000);
-- Query OK, 0 rows affected, 2 warnings (0.00 sec)

SHOW WARNINGS;
+---------+------+----------------------------------------------+
| Level   | Code | Message                                      |
+---------+------+----------------------------------------------+
| Warning | 7521 | Statement outline 1000 is not found in table |
| Warning | 7521 | Statement outline 1000 is not found in cache |
+---------+------+----------------------------------------------+