Analysis of Cost-Based Optimizer Plan Drift 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.