Feedback Reoptimization

Feedback Reoptimization (FR) is an automatic mechanism that optimizes query plans based on execution statistics rather than on optimizer estimates. FR is designed for situations where optimizer estimates based on automatically gathered statistics are not sufficient to create an optimal plan and execution statistics are required to create a better plan.

FR proactively detects suboptimal plans and triggers reoptimization, while also providing a precise, auditable way for users to explicitly mark a plan for reoptimization. FR ensures safety under concurrency, provides concrete means for user control, and surfaces comprehensive runtime and historical feedback for observability.

FR learns from history and over time is expected to improve query plans. However, FR does not guarantee a better plan in a single FR run. FR works iteratively and learns from both good and bad plans. It accumulates learning on successive query executions, and gradually converges on a plan. Allow FR to iterate until it converges.

In certain situations, FR may not be able to produce improved plans. For example, FR cannot address parameter sensitivity. For such scenarios, consider disabling FR.

FR uses the following concepts.

Feedback Reoptimization (FR): An automatic mechanism that optimizes query plans based on execution statistics rather than on optimizer estimates.
Automatic FR Marking: A sub-feature of FR. The engine automatically detects suboptimal plans and marks such plans for FR.
Explicit FR Marking: Manually mark a plan for FR using the REOPTIMIZE MARK command.
Explicit Reoptimization: Manually reoptimize a plan by running the REOPTIMIZE command.

Enable Feedback Reoptimization and Automatic Marking

Enable FR and automatic FR marking with the following commands.

SQL

SET SESSION enable_auto_profile = ON;
SET SESSION auto_profile_type = SMART;
SET SESSION enable_automatic_feedback_reoptimization = ON;
SET SESSION optimizer_feedback_reoptimization_auto_marking = AUTO;

Setting enable_auto_profile to ON and auto_profile_type to SMART enables the profiling and collection of execution statistics for FR.

Setting enable_automatic_feedback_reoptimization to ON enables feedback reoptimization itself.

Setting optimizer_feedback_reoptimization_auto_marking to AUTO specifies that FR occurs in the background. Setting this variable to ON specifies that FR occurs after each user run (for marked plans).

Disable FR and automatic FR marking with the following commands.

SQL

SET SESSION enable_automatic_feedback_reoptimization = OFF;
SET SESSION optimizer_feedback_reoptimization_auto_marking = OFF;

Feedback Reoptimization Process

FR occurs:

Automatically when automatic FR marking is enabled.
Manually when plans are manually marked for reoptimization.

When automatic FR marking is enabled, after each query execution, FR analyzes runtime statistics and compares estimated versus actual statistics for row counts, CPU usage, memory, and network usage.

FR uses metrics including: amount of misestimation, query execution time, existence of a compiled plan, and performance of previous FR plans to determine if a plan qualifies for FR. Qualified plans are marked for FR.

Alternatively, a user can manually mark a plan for FR.

Plan Replacement and Policy

Once a plan is marked for FR, plan replacement works as follows.

FR generates a new plan, collects metrics for the new plan (CPU time, memory, network usage), and then compares the metrics for the new plan with the metrics for the original plan.
If the new plan improves over the old plan based on an evaluation of CPU time, memory, and network usage, FR adopts the new plan; if not, FR drops the new plan and retains the original plan.

Convergence

FR works iteratively, improving plans with each successive FR attempt. To avoid infinite cycling, total FR attempts per plan are capped. Ongoing or new queries use the currently valid plan instance, and are unaffected by in-process FR.

Automatic Marking Requirements

Automatic marking occurs under the following conditions.

FR automatically marks queries that run using auto-profiling. FR does not automatically mark queries when the explicit PROFILE command is used.
FR requires consistent CPU measurements to validate whether the corrected plan improves performance. Thus FR waits until the second execution of the query, when the query plan is fully compiled, to consider whether to mark the query.
FR has a CPU time threshold (500ms) for automatic marking. Queries that fall below this threshold are not automatically marked.

The following are required for automatic marking to occur.

Engine Variables

The engine variables must be set as follows.

SQL

SET SESSION enable_auto_profile = ON;
SET SESSION auto_profile_type = SMART;
SET SESSION optimizer_feedback_reoptimization_auto_marking = ON; (or AUTO)

Execution

The most recent execution must use a fully-compiled plan.
The most recent execution must use auto-profiling; it must not use an explicit PROFILE SELECT... command.

FR uses heuristics based on the number and size of misestimations and CPU time to determine if a plan qualifies for automatic marking.

Monitor Feedback Reoptimization

Monitor FR systemwide using the SHOW FEEDBACK REOPTIMIZATION STATUS command or per-query by viewing OPTIMIZER_NOTES from the PLANCACHE.

Systemwide Monitoring

The following command displays system-wide FR information.

SQL

SHOW FEEDBACK REOPTIMIZATION STATUS;

The output displays up-to-date atomic counters, including:

The number of feedback-reoptimized queries currently running.
The number of plans marked for FR.
The total number of plans in the plancache.
Configured threshold values.
System-level stats from startup including total automatic and explicit feedback reoptimizations and number of skips of FR.

Per-Query Monitoring

The OPTIMIZER_NOTES in the PLANCACHE view provide information about FR including:

If, when, and how many times a plan was feedback reoptimized.
If a plan was feedback reoptimized, which metrics improved with that reoptimization.
Historical observed improvement and variance per feedback-reoptimization step.
Largest estimation mismatches to support diagnosis and audit.

Run this command to see the OPTIMIZER_NOTES.

SQL

SELECT OPTIMIZER_NOTES FROM information_schema.PLANCACHE WHERE ...;

Manually Mark a Plan for Feedback Reoptimization

Manually mark a plan that has been profiled for reoptimization using the plan's plan_id.

Run the following series of commands to manually mark a plan for FR.

Run SHOW PLANCACHE to list plans and obtain the ID of the plan to be reoptimized.
SQL
```
SHOW PLANCACHE;
```
Run REOPTIMIZE MARK to mark the plan for FR.
SQL
```
REOPTIMIZE MARK <plan_id>;
```

After a plan has been marked, the next execution of the query triggers FR.

Examples

Example 1: Automatic Marking - FR Flow

FR happens automatically without user intervention. For automatic FR, the flow is as follows.

The user runs a complex query.
The system automatically profiles and detects a significant mismatch between the estimates used to create the plan and the actual runtime statistics.
The plan is marked for FR.
On the next execution of the plan, FR runs automatically; the statistics are compared, FR swaps in the new plan if it detects significant improvement in at least one metric, and other metrics do not worsen significantly.
Run SHOW FEEDBACK REOPTIMIZATION STATUS or use EXPLAIN to obtain diagnostic detail.

Example 2: Manual Marking - FR Flow

Manually trigger FR using the following steps.

Run SHOW PLANCACHE to identify target plan_id.
Run REOPTIMIZE MARK <plan_id> to mark the plan for FR.
On the next execution of the plan, FR runs automatically; the statistics are compared, and the FR plan is swapped in if significant improvement is detected in at least one metric, and other metrics do not worsen significantly.
Run SHOW FEEDBACK REOPTIMIZATION STATUS or use EXPLAIN to obtain diagnostic detail.