The persist plan feature can persist hints in parameterized SQL statements. The query blocker feature can act as an SQL firewall by intercepting parameterized statements. These features are supported for AnalyticDB for MySQL clusters of version 3.1.4 and later.

Features

If advanced optimization features are enabled on a cluster, a wide range of statements may be affected. You can add hints to a statement by using a persist plan. This way, the hints take effect in statements that share the same pattern. The statements that share the same pattern are known as parameterized statements. You can view and delete persist plans by using system tables.

Note
  • When you add hints to a statement by using a persist plan, the statement is not executed.
  • In a pattern, question marks (?) are used to replace constants, such as a constant in the WHERE or LIMIT m,n clause of the SELECT statement.

Syntax

  • Parameterize a statement to generate a pattern and its sign value: PARAMETERIZE $sql
  • Add persistent hints to a pattern: /*+hints*/ PERSIST_PLAN $sql
  • Remove persistent hints from a pattern: DELETE_PLAN $sql or DELETE_PLAN_BY_SIGN $sign
  • Check whether persistent hints are configured for a statement:
    • PERSIST_PLAN_CHECK $sql
    • SELECT * FROM INFORMATION_SCHEMA.KEPLER_PERSIST_PLAN_SUMMARY
  • Query all patterns that are configured with persistent hints: SELECT * FROM INFORMATION_SCHEMA.KEPLER_PERSIST_PLAN_SUMMARY

Typical scenario: Intercept slow queries that share the same pattern

Use the /*+query_blocker=true*/ persist_plan + SQL syntax to intercept slow queries that share the same pattern.

Examples

  • Parameterize a statement to generate a pattern and its sign value
    PARAMETERIZE
    SELECT
      t1.c1
    FROM
      t1
      INNER JOIN t2 ON t1.c1 = t2.c1
    WHERE
      t1.c2 < 9999;
    Execution results:
    Sign Pattern
    2506ed2c1f53ea59a1ef996a98a50411 SELECT t1.c1 FROM t1 INNER JOIN t2 ON t1.c1 = t2.c1 WHERE t1.c2 < ?
  • Add persistent hints to a pattern
    Execute the nested loop join algorithm on statements that share the same pattern:
    /*+nested_loop_join=true*/
    PERSIST_PLAN
    SELECT t1.c1
    FROM t1
        INNER JOIN t2 ON t1.c1 = t2.c1
    WHERE t1.c2 < 9999;
  • Check whether persistent hints are configured for a statement
    PERSIST_PLAN_CHECK
    SELECT t1.c1
    FROM t1
        INNER JOIN t2 ON t1.c1 = t2.c1
    WHERE t1.c2 < 9999;
    Execution results:
    Sign Hints hitApplied Pattern
    2506ed2c1f53ea59a1ef996a98a50411 nested_loop_join=true 12 SELECT t1.c1 FROM t1 INNER JOIN t2 ON t1.c1 = t2.c1 WHERE t1.c2 < ?
    Note
    • When the PERSIST_PLAN_CHECK $sql statement is executed in Data Management (DMS), the preceding results are not returned after a successful execution. To obtain the preceding results, execute this statement on your MySQL client.
    • In the preceding results, the hitApplied field indicates how many times the pattern is applied after the persist plan is created. If the persist plan is updated, the hitApplied field is counted from scratch.
  • Query all patterns that are configured with persistent hints
    SELECT * FROM INFORMATION_SCHEMA.KEPLER_PERSIST_PLAN_SUMMARY;
  • Remove persistent hints from a pattern
    AnalyticDB for MySQL allows you to remove persistent hints from a pattern by using one of the following methods:
    • Execute the DELETE_PLAN $sql statement:
      DELETE_PLAN
      SELECT t1.c1
      FROM t1
          INNER JOIN t2 ON t1.c1 = t2.c1
      WHERE t1.c2 < 9999;
    • Execute the DELETE_PLAN_BY_SIGN statement based on the sign value:
      DELETE_PLAN_BY_SIGN 2506ed2c1f53ea59a1ef996a98a50411;
      Note You can use one of the following methods to query the sign value of a pattern:
      • Execute the PARAMETERIZE $sql statement.
      • Execute the SELECT * FROM INFORMATION_SCHEMA.KEPLER_PERSIST_PLAN_SUMMARY statement.