Cost-Based Optimizer Plan Drift Analysis in Oracle 19c and 23c Engines

Abstract

Hybrid OLTP-reporting workloads commonly experience performance instability caused by cost
based optimizer plan drift, where execution plans shift unpredictably despite no changes to SQL text.
This study analyzes plan drift behavior in Oracle 19c and 23c engines under mixed transactional and
reporting conditions. The results show that drift is primarily triggered by evolving data distributions,
dynamic SQL query shapes, and concurrency-induced resource contention. Oracle 23c demonstrates
improved runtime feedback mechanisms that reduce short-term volatility and promote faster plan
convergence, but drift persists under rapidly fluctuating workloads. Effective mitigation requires
selective plan stabilization, controlled query pattern design, and deliberate statistics lifecycle
management to maintain predictable performance while preserving optimizer flexibility. The findings
provide a structured foundation for diagnosing and minimizing plan drift in modern enterprise
deployments.