Persist plan and Query-Blocker best practices - AnalyticDB

Persist plan and query blocker give you direct control over how AnalyticDB for MySQL executes parameterized SQL statements. Use persist plan to lock in specific optimizer hints for a statement pattern, and use query blocker to intercept and block statements that match the same pattern — acting as an SQL firewall. Both features require V3.1.4 or later.

How it works

AnalyticDB for MySQL identifies statements that share the same structure by parameterizing them: replacing constants in clauses like WHERE and LIMIT with question marks (?). Each unique structure, called a pattern, gets a sign value — a hash identifier for that pattern.

A persist plan attaches optimizer hints to a pattern. Every statement that matches the pattern runs with those hints applied, without modifying the original SQL.

A query blocker works the same way, but instead of tuning execution, it rejects every statement that matches the pattern.

When advanced optimization features are enabled on a cluster, a wide range of statements may be affected. You can use persist plan to control which hints apply to statements that share the same pattern.

Running PERSIST_PLAN or /*+query_blocker=true*/ PERSIST_PLAN does not execute the target SQL statement. It only stores the hint configuration.

When to use each feature

Situation	Feature
The optimizer is choosing a suboptimal execution plan for a group of similar queries	Persist plan
A group of similar queries is causing performance issues and needs to be stopped immediately	Query blocker

Prerequisites

Before you begin, ensure that you have:

An AnalyticDB for MySQL cluster running V3.1.4 or later
Access to a MySQL client (required for PERSIST_PLAN_CHECK — Data Management (DMS) does not return results for this command)

Syntax reference

Operation	Syntax
Generate the pattern and sign value for a statement	`PARAMETERIZE $sql`
Attach hints to a pattern	`/+hints/ PERSIST_PLAN $sql`
Block all statements matching a pattern	`/+query_blocker=true/ PERSIST_PLAN $sql`
Check whether hints are configured for a statement	`PERSIST_PLAN_CHECK $sql`
List all patterns with hints	`SELECT * FROM INFORMATION_SCHEMA.KEPLER_PERSIST_PLAN_SUMMARY`
Remove hints by SQL statement	`DELETE_PLAN $sql`
Remove hints by sign value	`DELETE_PLAN_BY_SIGN $Sign`

Persist plan examples

Generate a pattern and sign value

Run PARAMETERIZE on a statement to see how AnalyticDB for MySQL normalizes it and what sign value it gets:

PARAMETERIZE
SELECT t1.c1
FROM t1
    INNER JOIN t2 ON t1.c1 = t2.c1
WHERE t1.c2 < 9999;

Result:

Sign	Pattern
2506ed2c1f53ea59a1ef996a98a50411	SELECT t1.c1 FROM t1 INNER JOIN t2 ON t1.c1 = t2.c1 WHERE t1.c2 < ?

The sign value uniquely identifies this pattern. Use it to remove a persist plan without re-specifying the full SQL.

Attach hints to a pattern

To apply the nested_loop_join algorithm to all statements that match the pattern:

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

Block statements matching a pattern

To intercept all statements that match a pattern:

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

Remove a persist plan

Two methods are available.

By SQL statement:

DELETE_PLAN
SELECT t1.c1
FROM t1
    INNER JOIN t2 ON t1.c1 = t2.c1
WHERE t1.c2 < 9999;

By sign value:

DELETE_PLAN_BY_SIGN 2506ed2c1f53ea59a1ef996a98a50411;

To get the sign value for a pattern, run PARAMETERIZE $sql or query INFORMATION_SCHEMA.KEPLER_PERSIST_PLAN_SUMMARY.

Verify and monitor persist plans

Check hints on a specific statement

Run PERSIST_PLAN_CHECK to confirm that a persist plan is configured and to see how many times it has been applied:

PERSIST_PLAN_CHECK
SELECT t1.c1
FROM t1
    INNER JOIN t2 ON t1.c1 = t2.c1
WHERE t1.c2 < 9999;

Result:

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 < ?

Field descriptions:

Field	Description
`Sign`	The hash identifier for the pattern
`Hint`	The hint currently configured for the pattern
`hitApplied`	The number of times the pattern has been matched since the persist plan was created or last updated. Resets to 0 after each update.
`Pattern`	The parameterized form of the statement, with constants replaced by `?`

Important

PERSIST_PLAN_CHECK does not return results when run in Data Management (DMS). Run it from a MySQL client to see the output.

List all configured patterns

SELECT * FROM INFORMATION_SCHEMA.KEPLER_PERSIST_PLAN_SUMMARY;

This query returns all patterns that currently have persist plans configured, along with their hints and sign values.