Deep Dive: Oracle Optimizer Internals – Dynamic Sampling & Optimizer Feature Enable (OFE)

When tuning Oracle Database performance at an advanced level, two often underutilized yet highly impactful parameters come into play:

• OPTIMIZER_DYNAMIC_SAMPLING
• OPTIMIZER_FEATURES_ENABLE (OFE)

Understanding how these influence the Cost-Based Optimizer (CBO) can significantly improve execution plan stability and query performance.

1. OPTIMIZER_DYNAMIC_SAMPLING – Smarter Cardinality Estimates

Dynamic Sampling allows the optimizer to collect runtime statistics when existing object statistics are missing, stale, or insufficient.

Why it matters: The optimizer’s decisions depend heavily on cardinality estimates. Poor estimates = poor plans.

Levels (0–11):

• 0 → Disabled
• 2 (default) → Basic sampling for unanalyzed tables
• 4–6 → Moderate sampling for complex predicates
• 7–11 → Aggressive sampling (used in DSS / complex queries)

Advanced Insight:

• Level 11 enables adaptive dynamic sampling even when stats exist but are deemed unreliable.
• Particularly useful for:
• Complex joins
• Skewed data distributions
• Volatile tables

Trade-off: Higher levels increase parse time due to sampling overhead. Use selectively (session-level or SQL hint).

Example:

ALTER SESSION SET OPTIMIZER_DYNAMIC_SAMPLING = 6;        

2. OPTIMIZER_FEATURES_ENABLE (OFE) – Version-Control for the Optimizer

OFE acts as a compatibility switch that controls optimizer behavior based on a specific Oracle release.

Why it matters: Each Oracle version introduces optimizer enhancements—but sometimes these changes can regress performance for legacy workloads.

Key Use Cases:

• Stabilizing execution plans after database upgrades
• Reproducing historical optimizer behavior
• Controlled testing of new optimizer features

Example:

ALTER SESSION SET OPTIMIZER_FEATURES_ENABLE = '19.1.0';        

What it affects:

• Join ordering strategies
• Query transformations
• Adaptive plans
• Parallel execution decisions

Dynamic Sampling vs OFE – How They Interact

• OFE defines which optimizer logic is used
• Dynamic Sampling improves data accuracy for that logic

Even with a modern OFE, poor statistics can lead to suboptimal plans—this is where dynamic sampling complements the optimizer.

Pro Tips from the Field

• Use SQL Plan Baselines alongside OFE for upgrade safety
• Enable higher dynamic sampling only for problematic queries (via hints)
• Monitor with:
• DBMS_XPLAN.DISPLAY_CURSOR
• V$SQL_PLAN_STATISTICS_ALL
• Combine with Adaptive Statistics (if enabled) for better runtime decisions

Final Thought

Oracle’s optimizer is incredibly powerful—but not infallible. Mastering parameters like Dynamic Sampling and OFE allows you to guide it with precision rather than guesswork.

Performance tuning isn’t just about fixing slow queries—it's about engineering predictable, scalable execution plans.

Note: This content is shared strictly for informational and educational purposes only. The concepts discussed are based on general Oracle Database behavior and real-world experience, but may not apply universally to every environment.