Oracle中的实时SQL计划管理
Real-time SQL Plan Management in Oracle
浏览论文内容
中文总结 AI 辅助
该研究针对Oracle 11g SPM依赖后台验证导致检测延迟的问题，在Oracle 26ai中提出实时SPM，通过前台验证新执行计划，实现快速采用优计划、及时防倒退。
中文摘要 AI 辅助
一致的查询性能对于关键任务数据库应用至关重要，但SQL执行计划可能因数据库升级、DML变更、新索引等因素而改变。虽然存储大纲等计划稳定性机制通过冻结执行计划防止性能倒退，但也因禁止计划演进而抑制了性能提升。我们在Oracle 11g中引入了SQL计划管理（SQL Plan Management, SPM）以解决这种权衡问题，方法是维护一组已接受的执行计划，仅当新计划明显优于现有基线时才允许计划演进。然而，SPM的现有实现主要依赖后台性能验证流程，这会延迟倒退的检测与修复。在自治云数据库系统中，此问题被进一步放大，因为多个可能导致计划变更引发倒退的自动操作仅受有限的客户控制。及时检测与修复至关重要，但云环境中受限的后台资源可能无法跟上需求。为克服这些限制，我们在Oracle 26ai中引入了实时SPM（Real-Time SPM），这是SPM的一种新颖扩展，可在用户查询执行期间对新执行计划进行前台验证。实时SPM利用运行时会话上下文立即验证计划变更，既能快速采用更优计划，又能及时检测并防止性能倒退。本文介绍了实时SPM的架构与设计，包括可靠比较先前计划性能等技术挑战，并将其与传统后台计划演进进行对比。
英文摘要
Consistent query performance is essential for mission critical database applications, yet SQL execution plans can change due to factors such as database upgrades, DML changes, new indexes, etc. While plan stability mechanisms such as stored outlines prevent regressions by freezing execution plans, they also inhibit performance improvements by disallowing plan evolution. We introduced SQL Plan Management (SPM) in Oracle 11g to address this trade-off by maintaining a set of accepted execution plans and allowing plan evolution only when new plans demonstrably outperform existing baselines. However, prior implementations of SPM primarily rely on background performance verification processes, delaying regression detection and recovery. This issue is amplified in autonomous cloud database systems, where several automatic actions that could cause plan change driven regressions are performed with limited customer control. Timely detection and remediation is paramount, but the constrained background resources on cloud may not keep pace. To overcome these limitations, we introduce Real-Time SPM in Oracle 26ai, a novel extension of SPM that performs foreground verification of new execution plans during user query execution. Real-Time SPM leverages runtime session context to immediately validate plan changes, enabling rapid adoption of superior plans while promptly detecting and preventing regressions. This paper presents the architecture and design of Real-Time SPM - including technical challenges like reliably comparing performance of previous plans - and contrasts it with traditional background plan evolution.
