Runtime Plan Cache for Database Query Transformation
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
The transformation of query plans into runtime plans in databases is costly due to tree traversal, code execution, memory allocation, and state management, leading to inefficiencies in database performance and resource usage.
Innovation Solution
Implementing a runtime plan cache that distinguishes between immutable and mutable state, caching immutable state for reuse and regenerating mutable state for each invocation, while utilizing linearization and metadata to facilitate caching and reduce processing costs.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If query plan transformation to runtime plan is performed for each query, then query execution accuracy is improved, but processing time and resource usage increase substantially
Solution Approach 1:
The patent pre-generates runtime plans during query planning phase and stores them in a cache. When a query is executed, the pre-generated runtime plan is retrieved from cache rather than transforming the query plan at runtime, thus eliminating the transformation overhead while maintaining execution accuracy.
Solution Approach 2:
The patent creates and stores copies of runtime plans in a cache structure. These cached copies are reused for matching queries, avoiding repeated transformation operations. The copy mechanism preserves the exact runtime plan structure needed for accurate query execution while significantly reducing processing time.
2Reliability
If query plan transformation is performed for each query, then query execution correctness is improved, but memory allocation and management overhead increase
Solution Approach 1:
The patent merges the query planning and runtime plan generation processes by storing pre-generated runtime plans in a cache. This consolidation eliminates the need for separate runtime transformation and memory allocation operations, reducing memory management complexity while ensuring execution correctness through cached validated plans.
Solution Approach 2:
Runtime plans and their associated memory structures are pre-allocated and validated during the planning phase. This preliminary memory preparation eliminates the need for dynamic memory allocation during query execution, simplifying memory management while guaranteeing execution correctness.
3Adaptability or versatility
If runtime plan transformation is performed for each query, then query adaptability is improved, but processing costs and resource usage increase
Solution Approach 1:
The patent uses parameter-based caching where runtime plans are cached with specific parameters (query identifiers, optimization hints, data distribution characteristics). When a new query arrives, the system checks if cached plans match the query parameters, allowing efficient reuse of adapted plans while maintaining query-specific adaptability through parameter matching.
Data Source
AI summary
Caching runtime plan data that is determined not to change for different invocations of a query plan. In some embodiments, a computing system accesses information that specifies a query plan generated for a first database query and generates a first runtime plan for the first database query based on the query plan. In some embodiments, the system caches information generated for the first runtime plan that is determined not to change for different invocations of the query plan. For example, transformation code may include separate functions for mutable and immutable state. In some embodiments, the system retrieves and uses the cached information to generate a second runtime plan for a second database query. Disclosed techniques may improve performance of query plan transformations that hit in the runtime plan cache.