PaperlayerView exact revision on arXiv

Source-linked AI summary

Real-time SQL Plan Management in Oracle

Sunil Chakkappen, Mohamed Ziauddin, Hong Su, Shreya Kunjibettu, Nigel Bayliss

arXiv:2608.27758v1cs.DB

TL;DR

Execution-plan changes can cause severe regressions, while background SPM may detect them too slowly or fail to reproduce the user environment. The paper introduces Real-Time SPM, which verifies new plans during statement execution and compares them with accepted baselines. Experiments report immediate foreground detection and resolution of regressions, while preserving demonstrably better plans.

  • Problem

    Execution-plan changes can cause severe regressions, while background SPM may detect them too slowly or fail to reproduce the user environment.

  • Method

    Real-Time SPM verifies new or unaccepted CBO plans during foreground execution by reproducing accepted plans and comparing their performance.

  • Results

    Foreground verification immediately detects and resolves regressions upon initial query execution while retaining new plans that outperform existing baselines.

  • Takeaways & Limitations

    Real-Time SPM provides faster plan-regression response and reduces reliance on background processing in cloud-native and on-premise environments.

Abstract

from arXiv · show

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.

1 INTRODUCTION

SPM proactively manages execution-plan changes by capturing, evolving, and selecting verified baselines, but background verification can be too slow for cloud environments. Real-Time SPM moves verification into foreground query execution to detect regressions sooner and retain superior plans.

  • SPM foundations: SPM captures plan baselines, evolves them by verifying performance, and selects accepted baselines to avoid regressions.SPM was introduced in Oracle 11g; Automatic SPM later automated baseline verification and evolution as a background task.
  • Auto SPM: Auto SPM identifies plan changes through historical workload data, compares best and worst plans using CPU time and Buffer Gets, and captures better alternatives.It uses Auto STS to find statements with multiple plans and applies a regression threshold before adding non-worst plans as baselines.
  • Motivation: Background verification may not react quickly enough to regressions caused by automatic cloud actions such as patches, indexing, and statistics gathering.Shared and constrained cloud resources can delay detection and remediation, increasing user-facing performance impact.
  • Real-Time SPM: Real-Time SPM verifies unaccepted plans during user execution, reinstates previously accepted plans after regressions, and retains new plans when they perform better.The approach uses runtime performance metrics to accelerate regression resolution and reduce reliance on background processing.
  • SPM evolution: Figure 1 summarizes the progression of SPM functionality across Oracle releases.The paper uses the timeline to situate Real-Time SPM within the evolution from manual and background plan management.

2 AUTO SPM

Auto SPM combines foreground capture with background regression detection and baseline evolution, using accepted plans and historical workload data to verify alternatives. Its background design can limit reproducibility, statement coverage, resources, and support for long-running statements.

  • Foreground actions: Foreground SPM checks whether a statement is managed, loads associated plan metadata, and directly uses the CBO plan when it matches an accepted baseline.A fast in-memory bit-vector check uses the SQL text signature before plan-baseline information is loaded into cache.
  • Foreground actions: For a new or unaccepted CBO plan, SPM reproduces accepted plans with stored optimizer hints and executes the least-cost reproducible plan.If no accepted plan is reproducible, the CBO plan executes and is captured as a non-accepted baseline.
  • Background actions: Background SPM detects regressions and evolves plans through separate modules for performance regression detection and plan baseline evolution.Regression detection imports alternate plans from Auto STS, while evolution test-executes non-accepted plans against an accepted or CBO alternative.
  • Auto STS: Auto STS periodically records workload statements, plans, execution metrics, and context from the shared cursor cache, replacing existing metrics and purging inactive statements.Auto SPM relies on this historical repository to identify repeatable statements and their performance.
  • Limitations: Auto SPM may fail to reproduce actual plan performance because background execution cannot exactly replicate the user environment.Missing environment settings, outline bugs, and VPD predicates can make verification difficult.
  • Limitations: Restricted background resources and short task windows can lengthen verification, leave statements unexamined, and prevent verification of long-running statements.These constraints create wider delays between regression detection and resolution.

3 REAL-TIME SPM

Real-Time SPM moves plan verification into the user session, comparing a new test plan with a relevant historical reference plan. It accepts better plans, avoids worse plans, and addresses the challenges of foreground overhead and historical-performance mismatch.

  • Plan roles: Real-Time SPM defines test plans as new, non-accepted, unverified CBO plans and reference plans as reproducible accepted or historical plans used for comparison.Reference plans are selected from the SPM repository or Auto STS.
  • Plan evolution: Performance metrics collected after test-plan execution determine whether to accept the test plan, accept and retain the reference plan, or take no action.Better test plans are accepted; worse test plans are avoided going forward; similar performance causes no change.
  • Regression mitigation: Real-Time SPM can mitigate a regression after only one execution of a potentially suboptimal test plan.This contrasts with repeated bad executions while background Auto SPM performs verification.
  • Challenges: Foreground processing introduces overhead for finding and comparing reference plans, while historical reference performance may not represent current performance reliably.The paper identifies reliable comparison with historical reference performance as a central challenge.

3.1 Foreground Plan Selection and Verification

Foreground verification executes a newly generated CBO plan as a test plan when a comparable historical plan exists, then compares their performance in the user session. The better-performing plan is accepted, while verified but rejected test plans undergo reverse verification later.

  • Foreground verification: Real-Time SPM performs foreground verification when a CBO-generated plan is identified as a test plan alongside a reference plan from the SPM repository or Auto STS.The CBO plan itself is executed in the user session for comparison.
  • Reference selection: If no reproducible accepted baseline exists, Real-Time SPM selects the least-cost Auto STS plan under current binds and optimizer settings as the reference.The reference provides a benchmark because the CBO plan is the test plan in foreground verification.
  • Plan decision: After execution, test-plan metrics are compared with stored reference metrics, and the better-performing plan is accepted.A verified but unaccepted test plan is marked for reverse verification if regenerated in a later compilation.
  • Architecture: Real-Time SPM comprises test-plan detection, reference-plan detection, test-plan performance verification, and reverse verification.These four modules work together in the overall workflow.

3.2 Test Plan Detection

Test-plan detection determines whether a newly generated CBO plan merits foreground verification. It avoids repeated checks and unnecessary Auto STS lookups while requiring a distinct historical plan for meaningful comparison.

  • Eligibility: A CBO plan qualifies as a test plan only if it has not been foreground verified and a different reproducible historical plan can serve as a reference.Both conditions are required for foreground verification.
  • Eligibility: The first condition prevents repeated verification of the same plan, while the second makes verification meaningful by providing an alternative for comparison.A test plan cannot be evaluated without another plan as a benchmark.
  • Eligibility: Real-Time SPM checks existing baselines and compares the CBO plan with accepted and previously verified plans loaded into cache.This checks whether the plan has already been verified.
  • Efficient detection: A multi-plan bit vector identifies statements with multiple Auto STS plans, avoiding direct Auto STS queries during compilation in the common single-plan case.The optimization reduces lookup overhead when most statements have only one historical plan.
  • Efficient detection: When the multi-plan bit is unset, a second SQL ID–plan hash vector distinguishes the only historical plan from statements requiring reference-plan detection.Foreground verification is skipped when the current plan is the only historical plan.

3.3 Reference Plan Detection

Reference-plan detection searches reproducible accepted baselines first and then Auto STS, selecting a low-cost plan whose historical performance remains credible under current conditions. Verification is skipped when no reproducible reference exists, and cost-sanity checks address historical mismatch.

  • Reference definition: A reference plan is a historical plan with execution metrics that is reproducible in the current setting.Real-Time SPM uses such plans to compare against the test plan.
  • Selection process: Real-Time SPM probes the SPM repository for reproducible accepted plans, retrieves their metrics, and selects the least-cost reproducible plan after CBO recompilation and costing.If accepted candidates with metrics are unavailable, it searches non-accepted Auto STS plans other than the test plan.
  • Reproducibility boundary: Accepted plans may fail to reproduce after schema metadata changes such as dropped indexes or session-context changes such as new VPD predicates.These changes constrain the availability of accepted baselines as reference plans.
  • Selection process: If no Auto STS plan is reproducible, verification is skipped because no reference plan was detected.The search proceeds from the SPM repository to Auto STS.
  • Cost sanity: Cost-sanity checks require current and historical optimizer costs to remain within internally defined margins before a plan is used as a reference.The checks account for large cost changes and use absolute differences when plans have small costs.
  • Outcome: When a reference plan outperforms the test plan, it is accepted and the test plan undergoes reverse verification in later executions.This preserves the better reference choice for subsequent processing.

3.4 Test Plan Performance Verification

Real-Time SPM executes a newly detected test plan in the foreground and compares it with a reference plan using historical performance metrics. It accepts clear improvements, while invalidating similar or worse test-plan cursors so subsequent executions can fall back to accepted plans.

  • Test Plan Performance Verification: The verification module executes the test plan and compares it with average historical reference-plan metrics.It accepts the better plan to preserve performance or avoid regression.
  • Test Plan Execution: Real-Time SPM does not impose a runtime cutoff, limiting a bad plan’s impact to at most one foreground execution without bounding its duration.Metrics are collected during test-plan execution and compared with historical reference-plan averages.
  • Plan Performance Comparison: A test plan is accepted and verified when it outperforms the average reference plan by an internal margin and execution is not interrupted.Acceptance also requires that the reference-plan cost sanity check has not failed.
  • Verification Outcome: Real-Time verification avoids repeated execution of a bad test plan, unlike background Auto SPM, which may allow repeated bad executions before verification.Figure 7 depicts the test-plan verification workflow.
  • Cursor Handling: When the reference plan is similar to or better than the test plan, the test-plan cursor is invalidated so the next execution returns to plan selection.This conservative behavior favors previously accepted plans unless the new plan shows clear improvement.

3.5 Reverse Verification

Reverse verification addresses cases where historical reference-plan performance does not reliably represent current performance. It rechecks unfavorable or uncertain comparisons in later executions and can reverse earlier plan-status decisions.

  • Reverse Verification: Real-Time SPM uses cost sanity checks and reverse plan verification to compare test and reference plans reliably.A worse test plan is marked for reverse verification only when a reference plan is accepted.
  • Status Updates: When a non-accepted reference plan outperforms the test plan, the reference plan is accepted and the test plan is marked for reverse verification.Subsequent compilations choose an accepted plan for execution while rechecking the earlier decision.
  • Status Updates: Reverse verification resolves cases where a reference plan appears better historically or a test execution times out, but the test plan may be better under current binds and environment.It sets optimal statuses for old test plans in subsequent compilations and executions.
  • Plan Selection: The optimizer subsequently chooses the least-cost plan among accepted plans.This follows the status updates produced through verification and reverse verification.

3.6 Illustrative Scenarios

The scenarios show Real-Time SPM handling bind-sensitive plan changes and post-upgrade regressions by testing new plans against historical references and using reverse verification when comparisons are uncertain.

  • Illustrative Scenarios: The examples cover two common situations: bind-sensitive plan choice and a true regression after an upgrade.Both scenarios use test and reference plans to guide subsequent status decisions.
  • Bind-Sensitive Plan Choice: For bind-sensitive SQL, Real-Time SPM tests P2 against P1 and can defer rejection by marking P2 for reverse verification.If P2 recurs, reverse verification rechecks the decision for the later bind context.
  • Upgrade Regression: After an upgrade, Real-Time SPM tests the new P2 against pre-upgrade P1 rather than immediately accepting P2 when cost-sanity checks fail.The conservative decision marks P2 for reverse verification on the next execution.

4 EXPERIMENTAL RESULTS

Experiments across Autonomous Database fleets and workloads show that Real-Time SPM detects and prevents most observed regressions, accepts more baselines faster than Auto SPM, and improves workload performance with modest overhead.

  • High-level Results: 20,015 databases were analyzed over five days, producing 959,911 verification events for 463,395 unique SQL statements.The observations covered diverse workloads without database patches during the measurement period.
  • High-level Results: 189,208 SQL statements had regressions prevented after 921,820 normal verifications and subsequent reverse-verification outcomes.731,232 new plans were similar or better, while 1,380 of 190,588 potential regressions were reversed.
  • Regression Magnitude: Regression factors peaked at 3, indicating that most worse test plans increased buffer gets by between two and three times.The regression factor is test-plan buffer gets divided by reference-plan buffer gets, with CPU also considered.
  • Regression Magnitude: Some statements regressed by more than 20x, with a maximum regression factor above 6.7 million.The distribution shows substantial variation in avoided regression severity.
  • Real-Time SPM vs Auto SPM: Real-Time SPM accepted 217 baselines in about 4 hours 20 minutes, compared with Auto SPM’s 63 baselines in 8 hours.This indicates broader and faster action under the evaluated setup.
  • Overhead: Compile-time comparisons across OFE versions were confounded by optimizer and compilation-time improvements unrelated to Real-Time SPM.The observed decreases in compilation metrics cannot be attributed to Real-Time SPM reducing compilation cost.
  • Overhead: Compilation overhead was modest: buffer gets rose by roughly 2 KB and CPU time from 3.87 to 3.91 minutes versus Auto SPM.These costs were small relative to workload execution, exceeding 1200 MB in buffer gets and 400 minutes in total execution time.
  • Automatic Indexes: Relative to the no-Real-Time-SPM configuration, indexed workload performance improved by about 11% in buffer gets and 3% in CPU time.The comparison preserved the benefits of new indexes while mitigating index-driven regressions.

5 RELATED WORK

Related work spans plan pinning, runtime adaptation, learned optimization, and historical plan recombination. Oracle Real-Time SPM distinguishes itself by combining workload-level conservative management with automatic runtime verification and plan selection.

  • Production systems differ in how they stabilize query plans, with Aurora offering largely manual plan management and Spanner supporting optimizer-plan and version pinning.
  • SQL Server Query Store can unforce degraded plans, but its correction targets forced-plan regressions and relies more on manual intervention or offline analysis.
  • ROME mitigates cardinality-estimation regressions by executing a small set of alternative plans in parallel and stopping when the first completes.
  • PQO and AQE provide parameter-sensitive or intra-query adaptation, whereas Real-Time SPM detects regressions and restores verified plans at the workload level.
  • Plan Stitch recombines efficient subplans using operator-level execution statistics, making it complementary to Real-Time SPM's reversion-based correction.
  • Learned query optimizers generally augment classical optimizers by constraining search or guiding plan discovery rather than replacing them outright.

6 CONCLUSION

Real-Time SPM manages execution-plan changes within the optimizer and runtime loop, reinstating previously superior plans while retaining new plans that outperform existing baselines. Experiments show that foreground verification detects and resolves regressions on the initial query execution, providing faster performance assurance.

  • Real-Time SPM detects plan-change regressions during statement execution and reinstates a previously superior plan through SQL plan baselines.
  • New execution plans that demonstrably outperform existing baselines are verified and retained.
  • Foreground verification immediately detects and resolves regressions upon initial query execution across cloud-native and on-premise environments.
Loading 2608.27758v1…