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.

• Manual FR Marking: Manually mark a plan for FR using the REOPTIMIZE MARK command.

• Explicit Reoptimization: Manually reoptimize a plan by executing the REOPTIMIZE command.

Configure Feedback Reoptimization

To use FR, enable_auto_profile must be set to ON and auto_profile_type must be set to SMART. Use the command below to verify the values of these variables.

SELECT @@enable_auto_profile, @@auto_profile_type;

+-----------------------+---------------------+
| @@enable_auto_profile | @@auto_profile_type |
+-----------------------+---------------------+
|                     1 | SMART               |
+-----------------------+---------------------+

The following engine variables are used to configure FR.

Enable FR with the following command.

SET SESSION enable_automatic_feedback_reoptimization = ON;

Enable automatic FR marking with the following command.

SET SESSION optimizer_feedback_reoptimization_auto_marking = AUTO;

Disable FR and automatic FR marking with the following commands.

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

Feedback Reoptimization Process

When FR and automatic FR marking are enabled, FR occurs automatically without user intervention.

The FR process begins with a plan being marked for FR. Plans can be automatically marked for FR or manually marked.

When automatic FR marking is enabled, after each query execution, FR analyzes runtime statistics and compares estimated versus actual statistics including 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 plan can be manually marked for FR.

Once 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 following conditions are met, the new plan is adopted, if not, the new plan is dropped and the original plan remains.

  • At least one metric must improve significantly.

  • No metrics must worsen significantly.

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.

Remarks

• Parameter Sensitivity: Automatic FR does not address parameter sensitivity issues. For such scenarios, FR may need to be temporarily disabled.

• Compilation Consistency: FR is only triggered on fully compiled runs to ensure stats are comparable; async compilation may yield misleading stats.

• Short/Low-Latency Queries: Very fast queries are excluded from FR to avoid unnecessary overhead and system churn.

• Concurrency and Failures: Concurrency controls completely isolate plan swaps and drops, ensuring zero disruption to ongoing queries. Cleanup is handled by garbage collection.

Systemwide Monitoring

The following command displays system-wide FR information.

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.

SELECT OPTIMIZER_NOTES FROM information_schema.PLANCACHE WHERE ...;    

Example 1: Automatic Feedback Reoptimization Flow

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

1. The user executes a complex query. 

2. The system automatically profiles and detects a significant mismatch between the estimates used to create the plan and the actual runtime statistics.

3. The plan is marked for FR.

4. 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.

5. Run SHOW FEEDBACK REOPTIMIZATION STATUS or use EXPLAIN to obtain diagnostic detail.

Example 2: Manual Marking Flow

You can manually trigger FR using following these steps:

1. Run SHOW PLANCACHE to identify target plan_id.

2. Run REOPTIMIZE MARK <plan_id> to mark the plan for FR.

3. 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.

4. Run SHOW FEEDBACK REOPTIMIZATION STATUS or use EXPLAIN to obtain diagnostic detail.