In the old days of Oracle, once the Query Optimizer picked a plan, it was committed to it—even if that plan turned out to be a disaster mid-execution. It was like a GPS that told you to turn left into a lake and refused to recalculate when you started sinking.

Adaptive Query Optimization, introduced in Oracle 12c and refined in 19c and 23c, changes that. It allows the database to "change its mind" and adjust its strategy based on real-time data as the query is actually running.

1. Why do we need it?

The Optimizer makes decisions based on statistics. If those statistics are stale, missing, or if the SQL is too complex for the math to be perfect, the Optimizer might estimate that a query will return 10 rows when it actually returns 10 million.

Without Adaptive features, Oracle would try to process those 10 million rows using a method meant for 10 rows (like a Nested Loop), causing the database to hang.

2. Key Feature: Adaptive Plans

An Adaptive Plan allows the Optimizer to delay its final decision on a specific part of the plan until it sees the first few rows of data.

• The Pivot Point: Oracle creates a "Pivot Point" in the execution plan—usually a choice between a Nested Loop (good for small data) and a Hash Join (good for large data).

• The Switch: As the query starts, Oracle monitors the rows coming out of the first table. If the row count crosses a certain threshold, it automatically switches from the Nested Loop to the Hash Join on the fly.

  +1

3. Key Feature: Adaptive Statistics

Sometimes the "map" is just wrong. Oracle has two ways to fix its own statistics automatically:

• Dynamic Statistics: If the Optimizer is unsure about a query (e.g., a complex WHERE clause with multiple predicates), it will pause for a few milliseconds and "sample" the data to get a better estimate before starting the full execution.

• SQL Plan Directives: If a query runs and the "Actual" rows are wildly different from the "Estimated" rows, Oracle creates a "Directive." The next time any query uses those columns, Oracle remembers: "Hey, the stats on these columns are misleading; do a better check next time."

4. Automatic Re-Optimization

If a query finishes and Oracle realizes it chose a terrible plan, it doesn't just forget about it. It stores the "Actual" execution data in the SQL Plan Baseline. The next time you run that exact query, Oracle will use the knowledge from the previous failure to pick a much better plan. It literally learns from its mistakes.

5. Summary: Standard vs. Adaptive

6. How to see it in action

You can see if a plan is adaptive by looking at the execution plan notes. You will often see a section titled "Note" at the bottom:

- this is an adaptive plan

You can also use the display_adaptive format in dbms_xplan:

SELECT * FROM TABLE(dbms_xplan.display_cursor(FORMAT => 'ADAPTIVE'));

In this view, Oracle will show you the "inactive" parts of the plan—the paths it could have taken but chose not to once it saw the real data.

Pro-Tip: Controlling the "Magic"

While Adaptive Optimization is amazing, it can sometimes cause "Plan Instability" (queries changing speed unexpectedly). In Oracle 19c, most of these features are "On" by default, but you can control them using the parameter:

OPTIMIZER_ADAPTIVE_PLANS = TRUE/FALSE