AnalyticDB for MySQL:Persist plan and query blocker

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.

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

/*+query_blocker=true*/
PERSIST_PLAN
SELECT t1.c1
FROM t1
    INNER JOIN t2 ON t1.c1 = t2.c1
WHERE t1.c2 < 9999;

Persist plan examples

• Parameterize a statement to generate a pattern and its sign value
  Parameterize the following SELECT 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;

  The following table describes the 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.c1FROM 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;

  The following table describes the execution results.

  Sign	Hint	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.