# 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](https://docs.singlestore.com/db/v9.1/query-data/query-tuning/statistics-and-sampling/statistics-and-sampling-concepts.md) 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](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-manipulation-language-dml/reoptimize-mark.md) command.
* **Explicit Reoptimization:** Manually reoptimize a plan by running the [REOPTIMIZE](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-manipulation-language-dml/reoptimize.md) 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 `ON` specifies that plans will be automatically marked at the end of the execution if they have the potential to be improved by FR. FR occurs at the next user execution for marked plans. Setting this variable to `AUTO` specifies that FR occurs in the background.

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](https://docs.singlestore.com/db/v9.1/reference/sql-reference/show-commands/show-feedback-reoptimization-status.md) command or per-query by viewing `OPTIMIZER_NOTES` from the [PLANCACHE](https://docs.singlestore.com/db/v9.1/reference/information-schema-reference/query-performance-workload-management-and-statistics/plancache.md).

## 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](https://docs.singlestore.com/db/v9.1/reference/information-schema-reference/query-performance-workload-management-and-statistics/plancache.md) 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](https://docs.singlestore.com/db/v9.1/reference/sql-reference/show-commands/show-plancache.md) to list plans and obtain the `ID` of the plan to be reoptimized.
  ```sql
  SHOW PLANCACHE;

  ```
* Run [REOPTIMIZE MARK](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-manipulation-language-dml/reoptimize-mark.md) 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.

1. The user runs 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, FR swaps in the new plan if it detects significant improvement in at least one metric, and other metrics do not worsen significantly.

5. Run [SHOW FEEDBACK REOPTIMIZATION STATUS](https://docs.singlestore.com/db/v9.1/reference/sql-reference/show-commands/show-feedback-reoptimization-status.md) or use [EXPLAIN](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-manipulation-language-dml/explain.md) to obtain diagnostic detail.

## Example 2: Manual Marking - FR Flow

Manually trigger FR using the following steps.

1. Run [SHOW PLANCACHE](https://docs.singlestore.com/db/v9.1/reference/sql-reference/show-commands/show-plancache.md) to identify target `plan_id`.

2. Run [REOPTIMIZE MARK \<plan\_id>](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-manipulation-language-dml/reoptimize-mark.md) 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](https://docs.singlestore.com/db/v9.1/reference/sql-reference/show-commands/show-feedback-reoptimization-status.md) or use [EXPLAIN](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-manipulation-language-dml/explain.md) to obtain diagnostic detail.

***

Modified at: May 29, 2026

Source: [/db/v9.1/query-data/query-tuning/feedback-reoptimization/](https://docs.singlestore.com/db/v9.1/query-data/query-tuning/feedback-reoptimization/)

(An index of the documentation is available at /llms.txt)