The Adaptive Plan That Chose Hash Join for 7 Rows

After upgrading from Oracle 11g to 12c, the customer lookup API — the most-called query in the application — started intermittently taking 14 seconds instead of its usual 2ms. No pattern: it happened 3% of the time, randomly, across all application servers. The same bind variable values returned in 2ms on one execution and 14 seconds on the next.

The Alert

SELECT
  sql_id, plan_hash_value,
  executions,
  elapsed_time / GREATEST(executions, 1) / 1000 AS avg_ms,
  buffer_gets / GREATEST(executions, 1) AS avg_gets
FROM v$sql
WHERE sql_id = '4f7ab8cd2e1'
ORDER BY plan_hash_value;

Two plan_hash_values for the same sql_id. Plan A: avg 1.8ms, 12 buffer gets. Plan B: avg 14,200ms, 890,000 buffer gets. We ran this three times: the execution counts for both plans kept climbing. Both plans were active simultaneously — Oracle was flip-flopping between them. Plan A used nested loops. Plan B used hash join. For a query returning 7 rows.

First Hypothesis: Bind Variable Peeking

Different bind values producing different plans — classic bind variable peeking. We tested with the same bind value that was slow and fast.

Same value, two different plans. Ran it three times with the exact same customer_id: twice got nested loops (2ms), once got hash join (14s). Bind variable peeking was not the cause — the same literal value got different plans. This pointed to Oracle 12c's adaptive query plan feature.

The Discovery

SELECT
  sql_id, child_number, plan_hash_value,
  is_resolved_adaptive_plan, is_reoptimizable
FROM v$sql
WHERE sql_id = '4f7ab8cd2e1';

-- Check cardinality feedback on the join
SELECT * FROM v$sql_plan
WHERE sql_id = '4f7ab8cd2e1'
  AND operation LIKE '%JOIN%';

is_resolved_adaptive_plan = 'Y' on the slow child cursor. Oracle's adaptive join was choosing hash join based on an inflated cardinality estimate from a previous execution that happened to hit a partition with skewed data. The cardinality feedback persisted in the cursor, causing subsequent executions to use the hash join path even when the actual row count was 7. We verified three times across different child cursors — the inflated estimate came from a single outlier execution.

Incident Timeline

Root Cause

The intermittent nature of this problem made it particularly difficult to diagnose. A 3% failure rate across thousands of daily executions meant the slow execution appeared in logs roughly 200 times per day — enough to cause user complaints but rare enough that random sampling during investigation usually showed the fast plan. We only caught it by querying V$SQL for multiple plan_hash_values on the same sql_id, which is not a standard monitoring check. Most database monitoring tools track average query time, which was barely affected — the 97% of fast executions pulled the average down to 430ms, well within alert thresholds.

Oracle 12c's adaptive query plan feature re-evaluates join methods at runtime based on cardinality feedback. A single execution hitting skewed data inflated the cardinality estimate, causing the adaptive plan to resolve to hash join. That resolution persisted across subsequent executions, even when actual cardinality was 7 rows. The feature is designed to improve plans over time, but for high-frequency OLTP queries with skewed data, it creates plan instability.

The Fix

-- Disable adaptive plans database-wide
ALTER SYSTEM SET optimizer_adaptive_plans = FALSE SCOPE = BOTH;

-- Or per-session for the OLTP connection pool:
ALTER SESSION SET optimizer_adaptive_plans = FALSE;

-- Flush the bad child cursor
EXEC DBMS_SHARED_POOL.PURGE('4f7ab8cd2e1', 'C');

Prevention

Adaptive plans are disabled for the OLTP connection pool. Reporting connections keep them enabled — the feature works well for long-running analytics where a wrong join method costs minutes. SQL Plan Baselines are created for the 50 highest-frequency OLTP queries to prevent plan instability regardless of optimizer features. New optimizer features are tested under production-like concurrency before enabling.

The fundamental tension with adaptive plans is that they optimize for the long-term average at the cost of short-term stability. For a reporting query that runs once a day, adapting the plan based on runtime cardinality is valuable — the feedback loop improves the plan for next execution. For an OLTP query that runs 200 times per second, plan stability is more valuable than plan optimality. A nested loop join that takes 3ms instead of an optimal 1.5ms is acceptable. A hash join that takes 14 seconds is catastrophic, even if it only happens 3% of the time.

We built a monitoring view that joins V$SQL with V$SQL_PLAN_MONITOR to detect plan instability: any sql_id with more than one plan_hash_value where the elapsed_time ratio between the fastest and slowest plan exceeds 100x. This runs every 5 minutes. When it fires, we capture both plans, create a SQL Plan Baseline pinning the fast plan, and investigate the slow plan at leisure. The alert has fired four more times since the initial incident — each time catching an adaptive plan regression before users noticed. Reporting connections keep them enabled — the feature works well for long-running analytics where a wrong join method costs minutes. SQL Plan Baselines are created for the 50 highest-frequency OLTP queries to prevent plan instability regardless of optimizer features. New optimizer features are tested under production-like concurrency before enabling.