Automatic regression management for multi-tenant database

Abstract

To provide a method for detecting performance regression and providing automatic solution, a related system for implementing the method, and software.SOLUTION: A method includes: detecting, in a first tenant, performance regression of executing a query using a current query plan; responsive to detecting the performance regression, evaluating one or more candidate solutions for resolving the performance regression; and identifying, from the one or more candidate solutions, an effective solution that resolves the performance regression. The effective solution is configured to generate an updated query plan, which has better performance than the current query plan for executing the query. The method also includes constructing a knowledge object based on the detected performance regression and the identified effective solution and distributing the knowledge object to a second tenant.SELECTED DRAWING: Figure 5

Description

[Technical Field]

[0001] This application relates to automated fallback management for multi-tenant databases. [Background technology]

[0002] A query plan (also called a "query execution plan") is the sequence of steps a database management system (DBMS) performs to complete a Structured Query Language (SQL) query. When a query is run for the first time in a DBMS, the query can be formulated by a query optimizer to generate a corresponding query plan, which can be stored in a memory called a "query plan cache," or simply a "plan cache." Thus, when the same query is run again, the DBMS does not need to regenerate the query plan. Instead, it can reuse the cached query plan stored in the plan cache, thereby improving the efficiency of the DBMS.

[0003] Even for the same query, the query plan generated by the query optimizer may change over time due to, for example, system upgrades to the database or other reasons. While system upgrades often lead to performance improvements in the query plan, in some situations, performance regressions in the query plan may occur. Managing these performance regressions can be a challenge due to the sheer number of queries and the complexity of the systems involved. It requires a deep understanding of the query processing engine and the ability to predict how changes in parts of the system may affect query performance. Furthermore, manual management of these regressions is not scalable, especially in large systems with many queries running in parallel. Therefore, there remains a need for an automated solution that can detect and handle performance regressions in an efficient and reliable manner. [Prior art documents] [Patent documents]

[0004] [Patent Document 1] U.S. Patent No. 11,748,349 [Patent Document 2] U.S. Patent No. 11,556,538 [Patent Document 3] U.S. Patent No. 11,907,217 [Patent Document 4] U.S. Patent No. 11,803,545 Summary of the Invention [Means for solving the problem]

[0005] A computer-implemented method can detect, at a first tenant, a performance regression executing a query using a current query plan. In response to detecting the performance regression, the method can evaluate one or more candidate solutions for resolving the performance regression and identify, from the one or more candidate solutions, an effective solution that resolves the performance regression. The effective solution is configured to generate an updated query plan, the updated query plan having better performance than the current query plan for executing the query. The method can build a knowledge object based on the detected performance regression and the identified effective solution and distribute the knowledge object to a second tenant. Related systems and software for implementing the method are also disclosed. [Brief explanation of the drawings]

[0006] [Figure 1] 1 is a general block diagram of an exemplary database management system that supports automated performance regression management. [Figure 2] FIG. 1 is a block diagram illustrating the various phases involved in automated performance regression management. [Figure 3]FIG. 2 is a block diagram illustrating exemplary modules of an automatic performance regression manager and operational options for each module. [Figure 4] 1 is a flowchart illustrating an exemplary overall method for implementing automated performance regression management. [Figure 5] 1 is a flowchart illustrating an exemplary overall method for implementing automated performance regression management knowledge sharing among tenants. [Figure 6] FIG. 1 is a schematic diagram illustrating a method for detecting performance regression based on data stored in a query execution history. [Figure 7] FIG. 1 is a schematic diagram illustrating the evaluation of candidate solutions for resolving performance regressions using A / B testing. [Figure 8] FIG. 1 illustrates a subtree of an exemplary query plan. [Figure 9] FIG. 9 illustrates an example knowledge object containing a pattern definition specifying the pattern of the subtree of FIG. 8. [Figure 10] FIG. 1 illustrates an example knowledge object that includes a pattern definition that specifies a query statement. [Figure 11] FIG. 1 is a block diagram of an exemplary computing system in which the described embodiments may be implemented. [Figure 12] FIG. 1 is a block diagram of an exemplary cloud computing environment that can be used with the techniques described herein. DETAILED DESCRIPTION OF THE INVENTION

[0007] Summary of query plan performance regressions The goal of query optimization is to select a query plan (from many candidate query plans) that results in optimal performance. The performance of a query plan can be described by its cost, which can be a cost in time (e.g., the time it takes to execute the query plan) and / or computing resources (e.g., the processing power and / or memory expended to execute the query plan). Cost-based query optimization selects the query plan with the lowest cost among all candidate query plans. In practice, although the terms "optimal" and "optimizing" are used, the actual best plan may not be selected; the selected plan is deemed better than other plans based on the data available to the optimizer.

[0008] The stability of a single query's performance can be highly variable due to its strong reliance on decisions made by the query optimizer. These decisions are often based on uncertainty and, in most cases, produce execution plans that reflect changes in the underlying data and have equivalent or better performance. However, there are cases where performance regressions occur, meaning that modified query plans perform worse than their predecessors.

[0009] One common type of performance regression is a long-running query that takes longer than expected to respond, as measured by the query's execution time (also known as "response time" or "elapsed time"). In some situations, a long execution time can be considered a regression even if the overall CPU consumption by the query plan decreases, because a decrease in CPU consumption can indicate that system resources are being underutilized.

[0010] Another type of performance regression is high CPU time consumption. Using multiple threads and a lot of CPU time can reduce response times, but it can also lead to a single query consuming a large amount of system resources. This can adversely affect the performance of other queries running in tandem, thus reducing overall workload throughput. Furthermore, high peak memory consumption by a single query can also lead to performance regression. Like CPU time consumption, high peak memory consumption can affect the performance of other queries, resulting in lower workload throughput on a customer's system.

[0011] As described herein, the superiority of one query plan over another is determined by its performance, which is evaluated based on predefined criteria. These criteria may involve multiple metrics, such as execution time, CPU time consumption, and / or peak memory consumption. A better query plan is one that optimizes one or more of these metrics, resulting in faster response time, efficient use of system resources, and / or higher workload throughput. In some situations, these metrics can be combined into a composite score to provide a holistic view of the query plan's performance. The specific weight assigned to each metric in the composite score may vary depending on the requirements of the system and the nature of the workload.

[0012] In a DBMS, performance regressions can occur for a variety of reasons. One major cause of performance regressions is changes in the system's coding. For example, when a system undergoes an upgrade, the query plans for various queries may change due to modifications to the code related to logically equivalent plan enumeration rules, physical algorithm enumeration rules, and / or underlying query optimization algorithms such as size estimation and cost calculation. While these changes may typically result in performance improvements, there are instances where they can lead to regressions.

[0013] Another factor that contributes to performance regression is skew in data distribution. For example, when the values ​​in a column of a database table are skewed, the query optimizer's size estimates often fail to predict reasonable result sizes for operations. This inaccurate size estimate can cause the query optimizer to generate a suboptimal query plan for the query, resulting in performance regression.

[0014] Changes in data distribution can also lead to performance regressions. For example, if the record count of a table changes significantly, pre-planned, cached plans on the table can be automatically re-planned to generate a plan that better reflects the recent data distribution. However, if the newly planned plan is slower than the pre-planned one, performance regressions can occur.

[0015] Record sampling for size estimation can also cause performance regressions. In some cases, a histogram can be used to represent the distribution of values ​​in a table column. However, in the absence of a histogram, sampling can be used to estimate filter or join selectivity. This means that whenever size estimation is performed, different values ​​may be used, potentially leading to changes in the resulting plan. These changes can cause performance regressions.

[0016] Furthermore, the process of formulating a query with specific parameter values ​​can lead to performance regressions. In some scenarios, the query optimizer supports what is known as "parameter-aware" query optimization (where the query optimizer knows the values ​​assigned to the query parameters). However, there are limitations regarding constraining the parameter values ​​used in this type of optimization. These parameter values ​​directly affect filter selectivity, leading to query plans that are optimized for marginal values. Nevertheless, for different parameter values, the query plan may be suboptimal. This variability in query plan performance based on parameter values ​​represents another potential source of performance regressions.

[0017] Addressing performance regressions can be a complex task due to the large number of queries and the complex nature of the systems they operate in. Addressing them requires a deep understanding of the query processing engine and the ability to foresee how modifications to certain segments of the DBMS may affect query performance. This is especially true considering various types of performance regressions (e.g., slow response times, high resource consumption, etc.), each of which may be influenced by multiple factors. In cloud-based database management systems, these challenges are further amplified by the distributed nature of the system, the variability of network conditions, and the need for efficient resource allocation among multiple tenants. Manually managing these regressions is technically not feasible, especially in large-scale systems where many queries run in parallel.

[0018] The technology described herein provides an automatic performance regression management (hereinafter "Auto-PRM") framework that is designed to intelligently identify and mitigate performance regressions, thereby optimizing query execution and improving overall system performance in multi-tenant cloud-based database environments.

[0019] Exemplary Computing System Supporting Auto PRM FIG. 1 shows an overall block diagram of an exemplary database management system 100 that supports Auto PRM. The database management system 100 can be configured to support multi-tenancy, which is a feature in many types of cloud computing services. In a multi-tenant environment, one instance of a software application and supporting infrastructure (e.g., virtual machines, memory, etc.) can serve multiple user groups, or tenants. Thus, multiple tenants can share the same application and other computing resources running on the same operating system, on the same hardware, with the same data storage mechanisms. Even though resources are shared, each tenant may appear to have its own instance of the application. Importantly, tenants' underlying data can be kept separate and secure.

[0020] As shown, database management system 100 includes a query processing engine 130 and a protocol layer 120 that acts as an interface between one or more tenants 110 and query processing engine 130. For example, protocol layer 120 may implement a server name indication protocol, which allows tenants 110 to connect to query processing engine 130. Each tenant 110 represents a group of users who can access a tenant-specific database (e.g., data storage 105) managed by database management system 100.

[0021] In some examples, query processing engine 130 may include a cache manager 140, a query parser 150, a query optimizer 160, a query executor 170, and a hint manager 180. Cache manager 140 has access to plan cache 122. Plan cache 122 represents a fast-access memory space configured to store pre-formulated query plans.

[0022] Incoming queries 112 sent from tenants 110 can be processed by hint manager 180 to output queries 114, which may be the same as or different from the incoming query 112. Hint manager 180 maintains a hint registry 128, which may contain one or more pairs of query statements and corresponding statement hints, or simply hints (i.e., each query statement in hint registry 128 has a paired hint). The query statements stored in hint registry 128 may be patterned (e.g., with wildcard expressions) or unpatterned (e.g., literal queries, queries including explicit object names, etc.).

[0023] Some of the incoming queries 112 may include hint commands, such as commands for adding a hint, deleting a hint, enabling a hint, disabling a hint, etc. Based on the received hint commands, the hint manager 180 may update the hint registry 128. For example, in response to receiving a command to add a hint to a query statement, the hint manager 180 may add a query statement and a hint pair to the hint registry 128. Conversely, in response to receiving a command to delete a hint for a query statement, the hint manager 180 may erase the query statement and the corresponding hint pair from the hint registry 128. The hint manager 180 may also enable or disable hints registered in the hint registry 128 based on the received hint commands.

[0024] The hint manager 180 is also configured to search the hint registry 128 to identify whether the incoming query 112 matches one of the query statements stored in the hint registry 128. If a matching query statement is found, a corresponding hint may be appended (or added) to the incoming query 112. In other words, the incoming query 112 is modified by the hint paired with the matching query statement. The modified query (i.e., the incoming query 112 with the appended hint) is propagated downstream from the hint manager 180 and processed, for example, by the cache manager 140, the query parser 150, the query optimizer 160, etc. On the other hand, if a matching query statement is not found, the incoming query 112 is not modified and may be passed directly to the cache manager 140 for query processing.

[0025] The hint manager 180 can use a string comparison to determine whether the incoming query 112 matches any of the query statements. For each patterned query statement stored in the hint registry 128, a wildcard expression included in the patterned query statement can match one or more characters of the incoming query when performing a string comparison.

[0026] The cache manager 140 receives queries 114 (which may be incoming queries 112 with hints added or unmodified incoming queries 112) sent from the hint manager 180. The cache manager 140 may evaluate the received queries 114 to determine whether the queries 114 have a corresponding (formulated) query plan stored in the plan cache 122.

[0027] If cache manager 140 does not find a query plan in plan cache 122 that corresponds to query 114, query 114 may be analyzed by query parser 150, which may examine query 114 for syntactic and / or semantic errors. After verifying that query 114 is a valid transactional SQL statement (e.g., SELECT, INSERT, UPDATE, DELETE, MERGE, etc.), query parser 150 may generate a logical tree (also referred to as a "query tree") according to which query 114 may be executed.

[0028] As described herein, a query tree is a logical representation of a query statement. A query tree includes multiple nodes and edges linking the nodes. Nodes may include leaf nodes and one or more interior nodes. Leaf nodes have no child nodes. In contrast, interior nodes have one or more child nodes. The root of a query tree, or root node, can be considered a specialized interior node. A query tree represents a relational algebra expression. Specifically, tables involved in a query can be represented as leaf nodes. Relational algebra operations can be represented as interior nodes. The root node represents the query as a whole. When a query plan is executed, interior nodes may be executed when their operand tables are available. The interior nodes can then be replaced with a result table produced by the operation represented by the interior node. This process can continue for all interior nodes until the root node is executed and replaced with a result table, which can be returned as the query result.

[0029] The logical tree can be used by query optimizer 160 to generate a corresponding query plan, which determines how query 114 is executed. Query optimizer 160 is configured to select a query plan (among multiple query plans generated based on enumeration of the logical tree) that results in optimal performance. The performance of a query plan can be described by cost, which can be a cost in terms of time (e.g., the time required to execute the query plan) and / or computing resources (e.g., the processing power and / or memory expended to execute the query plan). Cost-based query optimization selects the query plan with the lowest cost among all candidate query plans. In practice, although the terms “optimal” and “optimizing” are used, the actual best query plan may not be selected; the selected query plan is deemed better than other plans based on data available to query optimizer 160.

[0030] The determined optimal query plan may then be sent to query executor 170 for execution. Query executor 170 may communicate with data storage 105 and execute the operators in the query plan determined by query optimizer 160. Data retrieved from data storage 105 may be returned to tenant 110 via protocol layer 120.

[0031] In some examples, during execution of a query plan, query executor 170 may collect runtime statistics 172 (denoted "RT statistics") for selected nodes of the query tree representing the query plan. These collected runtime statistics may be used to more accurately determine data statistics for corresponding nodes of subsequent query trees. As a result, query optimizer 160 may more accurately calculate the costs of various query plans. This process may be iterative, allowing query optimizer 160 to adapt to dynamic changes in the data involved in a query, such as changes in table size and / or data distribution.

[0032] As described herein, query formulation refers to the process of converting query 114 into an optimal query plan (e.g., checking for syntactic and / or semantic errors, generating a logic tree, and determining an optimal query plan), as described above. Depending on the complexity of query 114 (e.g., the number of joined tables, etc.) and the complexity of the query optimization algorithm, query formulation time can be long (e.g., tens of seconds or more). Therefore, to improve operational efficiency, the formulated query plan for query 114 (i.e., the determined most optimal query plan) can be stored in plan cache 122 for rapid retrieval and reuse if the same query is encountered again in the future.

[0033] For example, if cache manager 140 determines that query 114 has a corresponding query plan stored in plan cache 122, then the query plan can be fetched directly from plan cache 122 and forwarded to query executor 170 for execution. Thus, in this scenario, operations by query parser 150 and query optimizer 160 can be bypassed. In other words, query 114 does not need to be re-formulated because its pre-formulated query plan is available in plan cache 122.

[0034] The plan cache 122 is configured to store formulated query plans. For each received query 114, the cache manager 140 checks whether it has a formulated query plan stored in the plan cache 122. If so, the cached query plan can be reused. This can improve efficiency by saving the time of formulating the query 114 (i.e., regenerating the query plan). On the other hand, if the query 114 does not have a formulated query plan stored in the plan cache 122, the query 114 must be formulated. The formulated query plan can then be stored in the plan cache 122 so that when the same query 114 occurs again in the future, fast access to the cached query plan can be achieved.

[0035] If the received query 114 is new (i.e., a first-time query that has not been encountered before), the new query does not have a corresponding query plan in the plan cache 122 and must be formulated for the first time. On the other hand, if the received query 114 is old (i.e., the same query has been encountered at least once before), whether a corresponding formulated query plan is available in the plan cache 122 may depend on the size of the plan cache 122 and the plan eviction policy adopted by the cache manager 140.

[0036] The plan cache 122 has a limited size. Therefore, it may not be able to store all formulated query plans. As the plan cache 122 approaches its full capacity, some query plans may need to be evicted from the plan cache 122 to make room for new query plans according to a predefined plan eviction policy implemented by the cache manager 140. For example, the cache manager 140 may implement a random plan eviction policy that randomly evicts query plans from the plan cache 122. In another example, the cache manager 140 may implement a least recently used (LRU) plan eviction policy that first removes the least recently used query plan from the plan cache 122. In yet another example, a least frequently used (LFU) plan eviction policy that first evicts the least frequently used query plan may be used. Other plan eviction policies may also be used by the cache manager 140.

[0037] As described above, performance regressions of query plans may occur due to, for example, system upgrades and / or data changes in database management system 100. To mitigate potential performance regressions of query plans, query plan optimizer 160 may communicate with an automatic performance regression manager, denoted Auto-PRM 162, which is configured to detect performance regressions and automatically find solutions to resolve these issues, thereby ensuring optimal performance and efficiency of database management system 100.

[0038] As described more fully below, auto PRM 162 can use various techniques to manage performance regressions. In some examples, auto PRM 162 can use execution history 124, which stores a list of serialized query plans, also called abstract query plans (“ASPs”), and their corresponding execution statistics (e.g., execution time, CPU consumption, peak memory usage, etc.). Execution history 124 can be used to implement a feature called “plan stability.” Plan stability operates by capturing selected query plans and storing them in execution history 124. If a performance regression occurs for a query plan, these stored ASPs can be reused to recreate the original query plan, thereby preserving the original performance. Data stored in execution history 124 can be persisted to persistence layer 126. Execution history 124 can be stored in volatile memory, which allows for fast access and manipulation of the data. Persistence layer 126 ensures that ASPs and execution statistics are preserved even when volatile memory is cleared during a system restart. This allows data to be reloaded from the persistence layer 126 back into the in-memory execution history 124 after, for example, a system restart, ensuring continued plan stability.

[0039] In some examples, the database management system 100 may further include an auto-PRM coordinator 190 configured to disseminate knowledge about automated performance regression management among multiple tenants 110. For example, a tenant may gain knowledge about a performance regression exhibiting a particular pattern and discover a corresponding solution for regulating the regression. This knowledge may be encapsulated in an individual knowledge object 192. The auto-PRM coordinator 190 may then share this knowledge object 192 with another tenant, thereby facilitating a collaborative and efficient approach for performance regression management in the multi-tenant database management system 100.

[0040] In practice, the systems illustrated herein, such as database management system 100, may vary in complexity, with additional functionality, more complex components, etc. For example, there may be additional functionality within query processing engine 130. Additional components may be included to implement security, redundancy, load balancing, report design, etc.

[0041] The described computing systems may be networked by wired or wireless network connections, including the Internet. Alternatively, the systems may be connected through intranet connections (e.g., in corporate or government environments, etc.).

[0042] Database management system 100 and any of the other systems described herein may be implemented with any of the hardware components (e.g., processing units, memory, etc.) described herein, such as the computing systems described below. In any of the examples herein, queries, query plans, runtime statistics, hints, knowledge objects, etc. may be stored in one or more computer-readable storage media or devices. The techniques described herein may be comprehensive with respect to operating system or hardware details and may be applied in any of a variety of environments to take advantage of the features described.

[0043] Example components of an Auto PRM 2 illustrates different phases of automated performance regression management. In some examples, each tenant of a database management system can implement its own auto-PRM process 200, with the process having multiple phases with different tasks, such as regression detection 210 (e.g., to detect performance regressions for a query), candidate search 220 (e.g., to identify candidate solutions that can potentially resolve the detected performance regression), candidate evaluation 230 (e.g., to evaluate whether any of the identified candidate solutions are effective for resolving the performance regression), and solution application 240 (e.g., to apply effective solutions for future processing of the query). In some examples, the auto-PRM process 200 can further include knowledge sharing 250 across tenants. For example, based on the detected patterns of performance regression and the identified solutions that resolve the performance regressions, one or more knowledge objects can be constructed, and the knowledge objects can be shared among multiple tenants.

[0044] Figure 3 illustrates example modules of an Auto-PRM 300 (which may be an example embodiment of Auto-PRM 162 of Figure 1) configured to implement the Auto-PRM process 200 of Figure 2. As shown, Auto-PRM 300 includes a Setback Detector 310 configured to detect a performance regression for a query (e.g., Setback Detection 210), a Candidate Finder 320 configured to search for candidate solutions that may resolve the detected performance regression (e.g., Candidate Search 220), a Candidate Evaluator 330 configured to assess whether there is a valid solution among the candidate solutions (e.g., Candidate Evaluation 230), and a Solution Applicator 340 configured to apply the valid solution (if found) for future processing of the same query (e.g., Solution Application 240).

[0045] Each module of auto-PRM 300 can have multiple operating options. For example, regression detector 310 can use different methods to detect performance regression for a query. These methods can use runtime statistics 312 (similar to runtime statistics 172) along with performance data stored in execution history 322 (similar to execution history 124) to identify specific patterns in query statements 314 and / or based on user input 316. Candidate finder 320 can identify candidate solutions by searching ASPs stored in execution history 322, applying predefined hints 324 to query statements 314, using feedback from runtime statistics 312 for query optimization, and / or modifying query optimizer settings 326 (e.g., forcing the query optimizer to perform exhaustive plan enumeration). Candidate evaluator 330 can assess identified candidate solutions using either foreground testing 332 (which may adversely affect user workload performance) or background testing 334 (which may not directly affect user workload performance). After candidate evaluator 330 identifies an effective solution (to resolve the detected performance regression), solution applicator 340 may store it for future processing of the same query. Depending on its characteristics, the effective solution may be stored in plan cache 344 (similar to plan cache 122), execution history 322, or hint registry 342 (similar to hint registry 128). Further details of each module's operational options are described more fully below.

[0046] Exemplary Overall Method for Auto PRM Figure 4 is a flowchart illustrating an overall method 400 for implementing Auto-PRM, which may be performed, for example, by the database management system of Figure 1. Specifically, the Auto-PRM feature may be performed by Auto-PRM 162 of Figure 1 or Auto-PRM 300 of Figure 3.

[0047] At 410, the method can detect a performance regression executing the query using the current query plan. Detecting a performance regression can be performed, for example, by regression detector 310 of FIG.

[0048] At 420, in response to detecting a performance regression, the method can automatically search for one or more candidate solutions for resolving the performance regression. The search for candidate solutions can be performed, for example, by candidate finder 320 of FIG. 3.

[0049] At 430, the method can select an effective solution from the one or more candidate solutions that addresses the performance regression. Selecting can include evaluating the performance of executing the query using one or more alternative query plans generated by the one or more candidate solutions. Such evaluation can be performed, for example, by candidate evaluator 330 of FIG. 3.

[0050] At 440, the method can store the effective solution for future executions of the query. In other words, the effective solution is applied when processing the same query in the future. The effective solution is configured to generate an updated query plan selected from one or more alternative query plans. The updated query plan has better performance than the current query plan for executing the query. Storing the effective solution can be performed, for example, by solution applicator 340 of FIG. 3 .

[0051] FIG. 5 is a flowchart illustrating an overall method 500 for sharing auto-PRM knowledge for multi-tenant database management, which may be implemented, for example, by the auto-PRM coordinator 190 of FIG. 1 in conjunction with the auto-PRM 162 (or the auto-PRM 300 of FIG. 3).

[0052] At 510, the method can detect, at the first tenant, a performance regression executing a query using the current query plan. The performance regression detection can be performed, for example, by regression detector 310 of FIG.

[0053] At 520, in response to detecting a performance regression, the method can evaluate one or more candidate solutions for resolving the performance regression. The candidate solutions can be identified, for example, by candidate finder 320, and the evaluation can be performed, for example, by candidate evaluator 330 of FIG. 3.

[0054] At 530, the method can identify an effective solution from the one or more candidate solutions that resolves the performance regression. The effective solution is configured to generate an updated query plan, where the updated query plan has better performance than the current query plan for executing the query. The effective solution can be identified based on results of evaluating the candidate solutions at 520.

[0055] At 540, the method can construct a knowledge object (eg, knowledge object 192 of FIG. 1) based on the detected performance regressions and the identified effective solutions.

[0056] The method may then distribute the knowledge object to a second tenant (different from the first tenant) at 550. The management of the knowledge object, including the distribution of the knowledge object, may be performed, for example, by the auto PRM coordinator 190 of FIG.

[0057] Methods 400 and 500, as well as any other methods described herein, may be implemented by computer-executable instructions (e.g., that cause a computing system to perform the method) stored on one or more computer-readable media (e.g., storage or other tangible media) or stored on one or more computer-readable storage devices. Such methods may be implemented in software, firmware, hardware, or a combination thereof. Such methods may be performed, at least in part, by a computing system (e.g., one or more computing devices).

[0058] The actions shown may be described from alternative perspectives and still implement the present technology, for example, "receive" may be described as "send" from a different perspective.

[0059] Exemplary Performance Measures As described herein, the performance of executing a query using a query plan can be evaluated based on predefined criteria involving multiple metrics.

[0060] For example, one performance metric may be the execution time of a query. Query plans that result in short query execution times may generally be considered better than those that require longer query execution times. Metrics related to computing resources consumed (e.g., CPU time, peak memory consumption, etc.) may also be used to measure performance. For example, a query plan that uses less CPU time to execute a query (thus making more resources available for other processes) may be considered better than another query plan that uses more CPU time. Similarly, among various query plans for the same query, one that consumes less memory may be considered preferable because it reduces the likelihood of memory-related bottlenecks.

[0061] In some situations, multiple metrics can be combined into a composite score to provide a holistic view of the performance of a query plan. The specific weight assigned to each metric in the composite score can be predefined by the user, depending on the requirements of the system and the nature of the workload. For example, in a memory-constrained system, the weight assigned to peak memory consumption may be relatively high. As another example, in a time-critical application where response speed is paramount, the weight assigned to query execution time may be given more importance. In some cases, it may be appropriate to consider query execution time exclusively, meaning that it is the only metric taken into account when evaluating the performance of a query plan.

[0062] In some scenarios, the performance of various query execution plans can be assessed based on predefined rules that involve the simultaneous evaluation of multiple conditions. For example, consider two query execution plans, A and B. A predefined rule may determine that query plan A performs better than query plan B if it satisfies two conditions: (a) the execution time of query plan A is at least X times faster than that of query plan B, and (b) the CPU consumption for executing query plan A does not exceed Y times the CPU consumption for executing query plan B, where X and Y are user-configurable parameters. This rule can strike a balance between execution speed and resource usage, contributing to the overall health and efficiency of the database system. Depending on the situation, additional rules can be formulated.

[0063] Example execution history In some examples, a history of past query executions may be maintained in an execution history, such as execution history 124 in FIG.

[0064] In some examples, for each query, the execution history may keep performance metrics (e.g., query execution time, CPU time, peak memory usage, etc.) collected during previous executions of the query. Thus, performance regressions may be automatically detected based on a comparison of current performance metrics (e.g., obtained when processing an incoming query) with previous performance metrics stored in the execution history, as described further below.

[0065] In some examples, session variables and other runtime-specific information collected during query execution can also be stored in the execution history along with corresponding performance metrics. This may include the number of allocated threads, available CPU and memory, the type and version of the DBMS used, the size of the dataset being queried, network latency, etc. These factors can affect query performance independent of the query plan used. For example, if a previous execution of a query with a particular query plan used 20% of the available CPU and memory resources and completed in 5 minutes, but a recent execution of the same query with the same query plan took the same amount of time but used 80% of the resources, this may indicate a performance regression. Even if the execution time remained the same, the increased resource usage may suggest inefficiencies that were not present in previous executions. In other words, session-related information can be used along with performance metrics in performance analysis.

[0066] In some examples, query plans previously used to execute queries can also be stored in the execution history, along with corresponding performance metrics collected when executing the queries using those query plans. For example, pre-formulated query plans for queries can be serialized into abstract query plans or ASPs, which can be stored in the execution history. The serialized ASPs can have a data exchange format (e.g., JSON) that can be read / written by a query optimizer (e.g., query optimizer 160 in FIG. 1 ). In some examples, ASPs stored in the execution history can be used to mitigate performance regressions, such as the plan stability feature implemented in SAP HANA, offered by SAP SE in Walldorf, Germany. Plan stability can ensure consistent, optimal performance of select statements by capturing ASPs in data storage, so that the same query plan can be reused when the query is executed again. Exemplary ASP storage and methods for capturing query plans are described in U.S. Pat. Nos. 11,748,349 and 11,556,538, both of which are incorporated herein by reference in their entireties. As explained further below, some of the query plans stored in the execution history may be identified as candidate solutions for resolving the performance regression.

[0067] In some examples, metadata and / or information about query objects used by the query optimizer to generate a query plan may also be saved in the execution history. A query may include one or more query objects (e.g., tables, views, indexes, etc.). The metadata for query objects may include the definitions or logical structure of these query objects. By comparing the current metadata for an object with the metadata in the saved ASP, the system can determine whether the logical structure of the object has changed. For example, if there is a change in object parameters that define the logical structure of an object, such as the number of attributes, the names of the attributes, the data types of the attributes, etc., the pre-stored ASP with the object may be deemed invalid and may not be reusable for plan stability purposes and may not be selected as a candidate solution for resolving a query regression, as described below. Furthermore, information used by the query optimizer may also change over time, which may cause a saved ASP to become invalid and non-reusable (and therefore not be selected as a candidate solution for resolving a query regression). For example, changes in the use of hints, the estimated size of an operator, data distribution, or the presence or absence of an index on a column can all affect query optimization. If these factors change, the query may need to be reformulated, even if the logical structure of the query object remains the same.

[0068] Example Hint Registry In some examples, one or more hints may be added to an incoming query to generate a query plan. Management of hints may be handled by a hint registry, such as hint registry 128 in FIG.

[0069] The hint registry can store hint records, each of which includes a hint target and a hint paired with the hint target. A hint target can be a query statement or one or more database objects. A hint manager (e.g., hint manager 180 in FIG. 1) can search the hint registry to identify whether an incoming query matches any of the hint records. If a match is found, the hint specified in the hint record can be added to the incoming query, modifying the incoming query into a new query. This modified query is then propagated downstream for further processing (e.g., to develop a query plan). If no matching hint record is found, the incoming query is not modified and is passed directly for downstream query processing.

[0070] Query statements contained in a hint record may be patterned or non-patterned. As described herein, a patterned query statement includes a wildcard expression, while a non-patterned query statement does not include a wildcard. A wildcard expression may be a special character or sequence of characters that matches any character or set of characters in a string. This wildcard can take various forms, including matching any literal, matching a literal within a set of constants, matching a literal within a range, or matching a literal starting with a particular string or of a particular data type. String comparison can be used to determine whether a received query matches any of the query statements stored in the hint registry.

[0071] For example, consider an incoming query statement, i.e., SELECT * FROM users WHERE name LIKE 'John%'. Assume that a patterned query statement that matches this incoming query exists in the hint registry, such as SELECT * FROM users WHERE name LIKE $$?$$. Assume also that a hint, such as (NO_USE_HEX_PLAN), is paired with this patterned query statement in the hint record. Thus, when the hint manager identifies that the incoming query matches the patterned query statement in the hint record, the hint can be added to the incoming query for downstream query optimization.

[0072] Exemplary Methods for Detecting Performance Regression In any of the examples described herein, the auto PRM can detect a performance regression for executing a query. For example, the auto PRM can have detection logic (e.g., regression detector 310 of FIG. 3) configured to detect a performance regression using various methods.

[0073] In some examples, Auto PRM can utilize performance data stored in an execution history (e.g., execution history 124 of FIG. 1 or execution history 322 of FIG. 3) in detecting performance regressions. Specifically, Auto PRM can be configured to detect performance regressions by comparing one or more performance metrics of a query execution using the current query plan with corresponding performance metrics stored in the execution history. As described above, the execution history can maintain corresponding performance metrics collected during previous executions of the query. If the performance metrics (e.g., execution time and / or resource consumption) of the current query plan are significantly worse than those of the previous performance metrics, this may indicate a performance regression. This comparison can be performed for each metric individually, or for a composite score based on multiple metrics or based on specific rules, as described above. The specific criteria for determining regressions can be predefined based on the system's requirements and the nature of the workload.

[0074] For example, a performance regression can be detected based on query execution time. In one specific example, a performance regression can be detected when the query execution time for executing a query using the current query plan is M times longer than the average execution time of the N most recent queries stored in the execution history, where M is predefined and greater than 1 (e.g., M may be 1.5, 2, 2.5, 3, 4, 5, etc.), and N is a predefined positive integer (e.g., N may be 1, 2, 3, 4, 5, 6, 7, 8, or more). In other words, a moving window of the most recent N query execution times can be used to detect a performance regression. If the execution time of the current query plan is M times longer than the average execution time in this window, a performance regression is detected. Alternatively, instead of using the average of the N most recent query execution times, the median can be used.

[0075] Figure 6 provides a schematic representation of the time history for the execution of a query. The graph exhibits some variability, particularly noticeable for the query execution times at 604 and 606. However, the query execution times remained within an acceptable range of fluctuations, specifically, less than twice the moving average. As a result, Auto PRM did not detect a performance regression at those times. On the other hand, it can be seen that the query execution time at 608 exceeds an acceptable limit (e.g., five times the moving average of the last eight executions). This significant deviation triggers the detection of a performance regression and indicates the need for potential adjustments to improve the query's performance.

[0076] In some examples, a confirmation phase can be implemented to prevent false positives in performance regression detection. This phase can be based on an x-out-of-y criterion or the like, ensuring that a single significant deviation in a performance metric, which may be outlier (e.g., due to abnormal data distribution), does not trigger regression detection. For example, as shown in FIG. 6, regression detection can be confirmed after finding three consecutive significant deviations in execution time. Only after this confirmation is a performance regression confirmed, which can trigger a candidate solution search (described further below) to find an effective solution to resolve the regression. This process results in the execution time returning to normal at 610, which may be based on reusing the legacy query plan associated with the execution time at 602.

[0077] In some examples, analysis of performance regression cases may reveal that certain query patterns may cause the query optimizer to make non-optimal decisions and thus generate query plans that lead to poor performance. Such patterns, hereafter referred to as anti-patterns, may be predefined for AutoPRM. As described herein, anti-patterns can be defined based on the logical structure of a query plan, which can be serialized into an ASP or other equivalent format that represents the query plan. For example, an ASP can specify a logical tree structure that defines the relationships between multiple query operations. An anti-pattern may include a subtree of the logical tree structure. In some examples, an anti-pattern may further include some runtime statistical information, such as data distribution of table columns, selectivity of filters, correlation between table columns, etc. When an anti-pattern is detected (e.g., by regression detector 310 in FIG. 3 ), it indicates a performance regression and can trigger a candidate search to find an effective solution to mitigate the regression.

[0078] An exemplary anti-pattern is index join with a post-filter, especially in skewed data distributions. This pattern can cause the query optimizer to underestimate the selectivity of the post-filter, leading to a potentially suboptimal index join operation. For example, if the joined values ​​have large duplicates, the actual result size of the index join can be significantly larger than estimated, leading to poor performance.

[0079] Another exemplary anti-pattern is no pre-aggregation with multiple grouping columns, which can have a large size estimation error (overestimation). The result size of a group-by operation with multiple grouping columns is highly dependent on the correlation between the columns. However, if the query optimizer does not consider this correlation and overestimates the result size, it may choose a suboptimal query plan.

[0080] Yet another exemplary anti-pattern is index join with large duplicates. If index join is selected but the searched values ​​in the index have many duplicates, index join may suffer performance degradation. In this case, the optimizer should choose a different join algorithm (e.g., hash join) that does not rely on indexes.

[0081] Another method for detecting performance regressions is based on detecting insufficient plan enumeration. As mentioned above, a query optimizer can use various enumeration rules to enumerate many possible query plans, one of which is selected for query execution or considered the optimal query plan. However, a query optimizer may have a plan enumeration limit that is configured to prevent it from enumerating all possible query plans. For example, consider a scenario in which an application defines its data model using composite views that depend on other composite views. When a single query against one of these views is processed, the query optimizer flattens the views into inline views, resulting in a large plan with many operations and tables. This complexity may be further exacerbated when join operations are pushed down below a union all operation, effectively multiplying a single join by the number of union children. The search space for this composite plan becomes so large that it is difficult for the query optimizer to enumerate all possible alternative plans within its configured plan enumeration limit. If the query optimizer is unable to sufficiently enumerate all possible alternative query plans, it can mark this information in the resulting query plan. Therefore, query plans marked with poor plan enumeration may indicate performance regression.

[0082] In some examples, a user may manually tag or label a query plan (e.g., a query plan stored in a plan cache) as having regressed performance or potentially leading to performance regression. This may be used, for example, when a query is newly introduced by another application or when there is not enough data in the execution history to support the automatic regression detection described above (e.g., shortly after a database system upgrade). After detecting such a regression tag or label, Auto PRM may enter a candidate search phase to identify effective solutions for resolving the performance regression, as described further below.

[0083] Exemplary Method for Searching for Candidate Solutions After detecting a performance regression or a high likelihood of performance regression when executing a query using the current query plan, the auto-PRM may automatically trigger a search to identify candidate solutions that can resolve the performance regression. For example, the auto-PRM may have a search module (e.g., candidate finder 320 of FIG. 3) configured to search for candidate solutions using various methods.

[0084] In some examples, for each query, the execution history (e.g., execution history 124 of FIG. 1 or execution history 322 of FIG. 3) may store one or more legacy query plans that were pre-generated for the query, along with corresponding performance metrics that were pre-obtained when executing the query using those legacy query plans. At least some of the legacy query plans stored in the execution history may be identified as candidate solutions if those legacy query plans have better performance than the current query plan for executing the query.

[0085] For example, each legacy query plan stored in the execution history may have a corresponding query execution time. A candidate solution may be identified if the query execution time corresponding to a selected legacy query plan stored in the execution history is less than the query execution time of executing the query using the current query plan. The identified candidate solution designates the selected legacy query plan as an alternative query plan for executing the query. For example, in the example shown in FIG. 6, after detecting a performance regression, Auto PRM identifies a legacy query plan with a shorter query execution time in 602. The candidate solution may designate the query to be executed using that legacy query plan. In some examples, the search may find multiple legacy query plans stored in the execution history as candidate solutions (e.g., all plans in the execution history that performed better than the current performance may be identified as candidate solutions). While execution time is described above as an example for comparing performance, it should be understood that other performance metrics may be used to search for candidate solutions.

[0086] In some examples, to search for candidate solutions, Auto PRM not only compares performance metrics but also considers the appropriateness of legacy query plans stored in the execution history. This is because some stored legacy query plans may no longer be suitable for executing a query, even though they previously had better performance. For example, a query may involve one or more query objects (e.g., tables, views, etc.). If one of the query objects has a different definition, such as a change in the number of columns, column names and / or data types, etc., the logical structure of the object changes. This change may invalidate a legacy query plan based on the object's previous definition. Therefore, regardless of its previous superior performance, this legacy query plan cannot be selected as a candidate solution for resolving a performance regression. Additional details on evaluating the appropriateness of pre-stored query plans are described in U.S. Pat. No. 11,907,217, which is incorporated herein by reference in its entirety.

[0087] In some examples, performance regressions are detected based on the detection of anti-patterns, as described above. In such cases, at least some of the candidate solutions may include applying predefined hints to the query. In other words, the candidate solutions may instruct the query optimizer to use a pair of query hints (or hints) to generate an alternative query plan. Each anti-pattern may have a corresponding hint (or hints). For example, if a regression originates from the anti-patterns "Index Join with Post Filter" or "Index Join with Large Duplicates," a hint instructing not to use Index Join (e.g., the "NO_HEX_INDEX_JOIN" hint in SAP HANA) may be used in query optimization to avoid generating Index Join with the problematic index. As another example, if a regression originates from the anti-pattern "No Pre-Aggregation with Multiple Grouping Columns," a hint instructing the query optimizer to apply pre-aggregation (group-by) before the join operation may be used. These hints act as instructions to the query optimizer, guiding it to formulate a query that mitigates the performance regression.

[0088] Alternatively, when runtime statistics about the operations involved in query execution (e.g., runtime statistics 312 in FIG. 3 or runtime statistics 172 in FIG. 1) are available, at least some of the candidate solutions can specify formulating queries using the runtime statistics in this manner. In other words, the candidate solutions can instruct the query optimizer to use the runtime statistics to generate alternative query plans. As described above, some anti-patterns relate to the discrepancy between size estimates during query optimization and the actual size during query execution. Therefore, a runtime feedback loop can be used to adjust the query optimizer's decisions based on actual execution results. Generally, runtime statistics collected from executed query plans can more accurately reflect the data statistics of internal nodes in the query tree (representing query operations that generate intermediate results). As a result, cost-based query optimization using runtime statistics can be more accurate (compared to estimates). Additional details on using runtime statistics to improve query optimization are described in U.S. Pat. No. 11,803,545, which is incorporated herein by reference in its entirety.

[0089] Thus, when a performance regression due to an anti-pattern is detected, a candidate solution can specify applying hints, using runtime statistics, or both. Hints can guide the query optimizer down a specific path, but are applied broadly to the entire query and affect all query objects. Runtime statistics, on the other hand, provide a more granular, targeted approach. Runtime statistics allow the optimizer to make informed decisions based on actual data from specific operations or query objects, such as the size of a table column. Thus, in some situations, a candidate solution based on runtime statistics may be deemed to have a higher priority than a candidate solution based on hints.

[0090] As described above, performance regression can be caused by insufficient plan enumeration. Thus, when a query's performance regresses and the query plan is marked as having insufficient plan enumeration, at least one candidate solution may specify formulating the query through exhaustive plan enumeration (e.g., by temporarily ignoring or lifting preconfigured plan enumeration limits) to generate an alternative query plan.

[0091] In some examples, a user may also define candidate solutions for user-labeled or known plan regressions. For example, after a database system upgrade, if a new A query execution engine is found to have regressed compared to a previous B query execution engine, a user may define a candidate solution that applies a hint to a query to instruct the query optimizer to generate an alternative query plan by using the B query execution engine instead of the A query execution engine.

[0092] Exemplary Methods for Evaluating Candidate Solutions After finding candidate solutions for a detected performance regression, the auto-PRM may also trigger an evaluation of the candidate solutions to determine whether any of the candidate solutions are effective in resolving the performance regression. For example, the auto-PRM may evaluate the candidate solutions (e.g., via candidate evaluator 330 of FIG. 3) using either foreground or background tests.

[0093] In foreground testing (also known as "A / B testing"), the selected candidate solution can be directly applied to the user's query input, thus affecting user workload performance. This method does not require additional execution of the candidate solution in the background, as described further below. Instead, the selected candidate solution is used for the execution of a setback query triggered by the user's query input. If the performance regression is resolved by the candidate solution, the candidate solution is determined to be a valid solution and is applied to subsequent executions of the setback query, as described further below.

[0094] As described herein, a valid solution specifies an updated query plan that differs from the current query plan associated with the performance regression. To qualify as a valid solution, the performance of the updated query plan must satisfy one or more predefined performance criteria. For example, one criterion may require that the query execution time executing the query using the updated query plan be less than a predefined fraction (e.g., 50%, 25%, etc.) of the query execution time executing the query using the current query plan. Other criteria, such as CPU usage and / or peak memory consumption, may be specified as needed.

[0095] Foreground testing in Auto PRM can use predefined rules to prioritize the evaluation of various candidate solutions. For example, candidate solutions that use query plans stored in the plan cache or ASPs stored in the execution history can be evaluated first, followed by other candidate solutions that require query reformulation. Auto PRM can also use various strategies to determine the effective solution from the evaluated candidate solutions. One exemplary approach is to evaluate multiple candidate solutions and compare the results to find the one with the best performance. Alternatively, a candidate solution can be identified as effective if it is found to resolve a performance regression, eliminating the need to further evaluate other candidate solutions, even though they may potentially provide better performance. To ensure reliability, the evaluation of each candidate solution can be repeated, and an average performance metric can be used to provide more reliable evaluation results.

[0096] During foreground testing, Auto PRM can be configured to handle exceptional behavior. For example, if the response time of an evaluation exceeds K times that of the previous query plan (where K is a user-defined parameter, e.g., K=3, 5, 8, etc.), the evaluation can be canceled, and Auto PRM can automatically switch back to the previous query plan and run it again. As another example, if an evaluation is stopped due to an exception, the exception can be handled internally by A / B testing, and the previous query plan is rerun to ensure that user query processing is not interrupted. If the system crashes during evaluation, Auto PRM can keep a new query plan along with the crash information to prevent a recurrence of the system failure.

[0097] FIG. 7 schematically illustrates a use case using A / B testing to evaluate candidate solutions for resolving a performance regression. In this example, query 710 has a current query plan 750 (denoted by query tree A shown in Box 1) that was previously formulated by query optimizer 740 and stored in plan cache 730. Current query plan 750 has been labeled or previously detected (e.g., by regression detector 310 of FIG. 3 ) as suffering from a performance regression. A candidate solution is identified (e.g., by candidate finder 320 of FIG. 3 ) to formulate query 710 with specific hints to generate an alternative query plan. Thus, after receiving query 710, rather than using the current query plan 750 in plan cache 730 to execute query 710, A / B test manager 720 (which may be a component of candidate evaluator 330 of FIG. 3 ) may instruct query optimizer 740 to reformulate query 710 with specified hints to generate an alternative query plan 760 (denoted by query tree B). The alternative query plan 760 can be stored in the plan cache 730 along with the current query plan 750 (as shown in box 2) and used to execute the query 710. The execution performance (e.g., query execution time, CPU consumption, etc.) associated with the alternative query plan 760 can be compared to the execution performance associated with the current query plan 750 to determine whether the alternative query plan resolves the performance regression. If so, the candidate solution is determined to be a valid solution, and the alternative query plan 760 is retained in the plan cache 730, while the current query plan 750 is removed from the plan cache 730 (as shown in box 4). If not, no valid solution can be found, and the current query plan 750 is retained in the plan cache 730, but the alternative query plan 760 is removed from the plan cache 730 (as shown in box 3).If there are additional candidate solutions, a similar process may be repeated to evaluate the performance of alternative query plans generated by those additional candidate solutions. After the A / B test is completed, the query plans held in plan cache 730 may be stored in persistent layer 770 (similar to persistent layer 126 of FIG. 1). Thus, after a system restart, which may clear plan cache 730 (e.g., volatile memory), A / B test manager 720 can automatically recover plan cache 730 by retrieving the query plans persisted in persistent layer 770. For example, FIG. 7 shows that after a system restart, current query plan 750 is restored in plan cache 730 (at box 5), assuming that the previous A / B test did not find a valid solution.

[0098] In some examples, the evaluation of candidate solutions can be performed using background testing, which can reduce adverse effects on the evaluation process or even separate the evaluation process from the user's workload. Two different approaches to background testing can be implemented. In a first approach, the evaluation can be performed in a separate thread. For example, detecting performance regressions can be performed in a first thread of the database system, and evaluating candidate solutions can be performed in a second thread of the database system. This approach ensures that the user's workload is not directly adversely affected by the evaluation process, provided that the system has sufficient idle resources, such as CPU and memory. However, if the system is heavily loaded with user tasks, the evaluation needs to be performed more carefully, for example, by limiting the number of threads used for evaluation and / or limiting available memory to a configurable value. In a second approach, the evaluation can be performed in a separate instance, such as an Elastic Compute Node (ECN), which is a cloud-based service that provides resizable computing capacity. This approach can completely separate the user's workload from the evaluation process, but requires that the data distribution of the original system be exactly replicated in the ECN for the evaluation process.

[0099] Example methods for applying solutions to mitigate performance regressions After an effective solution for resolving a query's performance regression is identified, the effective solution may be stored and applied for future executions of the query. For example, depending on the type of effective solution, Auto PRM may have different mechanisms for storing and applying the effective solution for future executions of the query (e.g., by solution applicator 340 of FIG. 3).

[0100] For example, as mentioned above, if a valid solution is found after A / B testing, an updated query plan (corresponding to the valid solution) has already been formulated and stored in the plan cache. In this scenario, the query optimizer does not need to regenerate the query plan. Instead, the updated query plan stored in the plan cache can be used for future executions of the query. In other words, Auto PRM can store the valid solution in the plan cache.

[0101] As another example, if Auto PRM finds (either through foreground or background testing) that a valid solution uses a legacy query plan stored in the execution history to resolve a performance regression, it can label the legacy query plan to be used in future executions of the query. In other words, Auto PRM can store the valid solution in the execution history.

[0102] In yet another example, if a valid solution is found (either through foreground or background testing) while reformulating a query with a hint, the Auto-PRM may register a hint record in a hint registry (e.g., hint registry 128 of FIG. 1). The hint record may include the query (or a patterned query statement representing the query) and the hint. Thus, when the query is received again in the future, the query will be formulated with the hint. In other words, the Auto-PRM may store the valid solution in the hint registry.

[0103] An exemplary method for sharing auto-PRM knowledge In the context of a multitenant database management system, the detection of performance regressions and effective solutions to address the regressions can be viewed as valuable experiences. These experiences, when analyzed, can reveal specific patterns that form a body of knowledge. In some examples, each tenant may have its own local auto-PRM. The auto-PRM knowledge gained from each tenant can be encapsulated in individual knowledge objects, which can be centrally stored and disseminated to multiple tenants, for example, by the auto-PRM coordinator 190 of FIG. 1 . By sharing these knowledge objects, other tenants can proactively prevent similar performance regressions, fostering a collaborative and efficient approach to managing performance regressions in multitenant database management systems.

[0104] As described herein, knowledge objects can be constructed, for example, by a database system administrator or developer, based on detected performance regressions executing queries using the current query plan and identified effective solutions that can resolve the performance regressions. The knowledge objects can be specified in a data exchange format (e.g., JSON, etc.) that can be understood by a query optimizer and exchanged between different tenants. Each knowledge object can include a pattern definition (representing a query or the current query plan) and a solution definition. The pattern definition can specify a pattern of detected performance regressions, and the solution definition can specify a corresponding effective solution. Thus, once constructed, the knowledge objects can be distributed to multiple tenants, each of which can apply effective solutions to prevent similar performance regressions in their respective systems.

[0105] In some examples, a pattern definition in a knowledge object can specify a subtree pattern in the logical tree structure of the current query plan. As described above, the logical tree structure defines the relationships between multiple query operations. Thus, the subtree pattern can capture the relationships of one or more operations included in the current query plan. If a query plan has a matching subtree pattern defined in a knowledge object, the query plan may be considered at high risk of performance regression.

[0106] As an example, FIG. 8 illustrates a query plan subtree 800 associated with a performance regression. The subtree 800 is characterized by an "index join with postfilter" antipattern. Specifically, the subtree 800 has a parent node 802 that represents an index join operation with a postfilter and two joined child nodes 804 and 806. Here, the postfilter refers to a filter that can be applied to the table represented by the child node 806, and the child node's index is used in the index join. However, the postfilter is not applied prior to the join operation. This is because if a filter were applied to the table represented by the child node 806 before the join operation represented by the parent node 802, the table's index would not be usable for the index join. Instead, a hash join would have to be used. Such an antipattern can exhibit performance regression, as discussed above.

[0107] The subtree pattern shown in FIG. 8 can be specified in a knowledge object 900 in JSON format, as shown in FIG. 9. For example, knowledge object 900 has a "subtree pattern" (indicating that the pattern definition is based on a subtree pattern in a query plan), which includes three operators with the parent-child relationships shown in FIG. 8. In some examples, the subtree pattern can further specify a condition associated with a performance regression. For example, FIG. 9 indicates that the performance regression is not only characterized by an anti-pattern, but is also associated with a large estimation error (e.g., the actual result size of an index join is more than 1000 times larger than the estimated size).

[0108] 9, knowledge object 900 further specifies valid solutions for the corresponding subtree pattern. In the illustrated example, the valid solution adds a hint "NO_HEX_INDEX_JOIN" to the query. In other words, if a query plan matches the subtree pattern specified in knowledge object 900, the likely performance regression can be mitigated by formulating the query with the specified hint.

[0109] In some examples, a pattern definition in a knowledge object can specify a query statement that represents a query. In other words, the query itself may represent a knowledge pattern. This may occur, for example, when a tenant employs a distributed application that has known performance regressions for some queries.

[0110] The query statement specified in the knowledge object may be unpatterned (e.g., identical to the query itself) or patterned (e.g., including a wildcard expression that matches one or more characters of the query). As an example, FIG. 10 shows a knowledge object 1000 written in JSON format. The knowledge object 1000 has a "statement" type (indicating that the pattern definition specifies a query statement). Specifically, the knowledge object 1000 defines the patterned query statement SELECT * FROM T WHERE A = $$%$$. Furthermore, the knowledge object 1000 specifies a valid solution, which adds a hint "NO_HEX_INDEX_JOIN" to the query. A tenant that receives the knowledge object 1000 may register the patterned query statement and the corresponding hint in its hint registry. Therefore, when a query matches the pattern statement SELECT * FROM T WHERE A = $$%$$, the NO_HEX_INDEX_JOIN hint is automatically applied to the query when the query optimizer generates the query plan.

[0111] Exemplary Benefits The Auto PRM framework described herein provides significant technical advantages by automating the complex task of managing performance regressions in multi-tenant database management systems.

[0112] The disclosed technology can automatically navigate through different phases of query performance regression management, such as regression detection, candidate solution search, candidate evaluation, and solution application. This automation is particularly beneficial in large-scale, multi-tenant, cloud-based database environments where many queries may run in parallel and where performance regressions can occur due to a variety of factors. Thus, the automatic nature of this framework significantly reduces the need for manual intervention, thereby reducing the likelihood of human error and increasing efficiency.

[0113] The disclosed Auto PRM framework exhibits significant versatility by incorporating multiple methods for each phase of performance regression management. For regression detection, it can use performance metrics from the execution history, identify anti-patterns in query statements, use runtime statistics, and even consider user input. This multifaceted approach enables more comprehensive and accurate detection of performance regressions. Similarly, during the candidate search phase, the framework can leverage ASPs stored in the execution history, feedback from runtime statistics, and statement hints to identify potential solutions. This ensures that a wide range of possible solutions is considered. During the candidate evaluation phase, it can utilize both foreground and background testing, enabling a thorough assessment of the effectiveness of each candidate solution without significantly negatively impacting user workload performance. During the solution application phase, effective solutions can be stored in various locations, such as the plan cache, execution history, or hint registry, to ensure optimal retrieval and application for future query processing.

[0114] Additionally, the Auto PRM framework disclosed herein supports knowledge sharing in multi-tenant database management systems. This knowledge sharing is facilitated through the creation and distribution of knowledge objects that encapsulate valuable experiences and solutions related to performance regressions. These knowledge objects can generically define performance regression patterns, such as subtree patterns or query statements, and specify effective solutions for mitigating those regressions. Once created, these knowledge objects may be disseminated across multiple tenants, enabling them to proactively prevent similar performance regressions and thereby fostering a collaborative and efficient approach to managing performance regressions in multi-tenant database environments.

[0115] Exemplary Computing System 11 illustrates an example of a suitable computing system 1100 in which the described innovations may be implemented. The computing system 1100 is not intended to suggest any limitation regarding the scope of use or functionality of the present disclosure, as the innovations may be implemented in a variety of computing systems.

[0116] Referring to FIG. 11 , a computing system 1100 includes one or more processing units 1110, 1115 and memories 1120, 1125. In FIG. 11 , this basic configuration 1130 is included within a dashed line. The processing units 1110, 1115 can execute computer-executable instructions, such as to implement features described in examples herein (e.g., methods 400 and 500). The processing units may be general-purpose central processing units (CPUs), processors in application-specific integrated circuits (ASICs), or any other type of processor. In a multiprocessing system, multiple processing units can execute computer-executable instructions to increase processing power. For example, FIG. 11 shows a central processing unit 1110 and a graphics processing unit or co-processing unit 1115. The tangible memory 1120, 1125 may be volatile memory (e.g., registers, cache, RAM), non-volatile memory (e.g., ROM, EEPROM, flash memory, etc.), or some combination of the two, accessible by the processing units 1110, 1115. The memory 1120, 1125 may store software 1180 implementing one or more innovations described herein in the form of computer-executable instructions suitable for execution by the processing units 1110, 1115.

[0117] Computing system 1100 may have additional features. For example, computing system 1100 may include storage 1140, one or more input devices 1150, one or more output devices 1160, and one or more communication connections 1170, including input devices, output devices, and communication connections for interacting with a user. An interconnection mechanism (not shown), such as a bus, controller, or network, may interconnect the components of computing system 1100. Typically, operating system software (not shown) provides an operating environment for other software executing within computing system 1100 and may coordinate the activities of the components of computing system 1100.

[0118] Tangible storage 1140 may be removable or non-removable and includes magnetic disks, magnetic tapes or cassettes, CD-ROMs, DVDs, or any other medium that can be used to non-transitory store information and that can be accessed within computing system 1100. Storage 1140 can store instructions for software that implements one or more of the innovations described herein.

[0119] Input device(s) 1150 may be an input device such as a keyboard, mouse, pen, or trackball, a voice input device, a scanning device, a touch device (e.g., a touchpad, a display, etc.), or another device that provides input to computing system 1100. Output device(s) 1160 may be a display, a printer, speakers, a CD writer, or another device that provides output from computing system 1100.

[0120] The communication connection(s) 1170 may enable communication over a communication medium to another computing entity. The communication medium may convey information such as computer-executable instructions, audio or video input or output, or other data in a modulated data signal. A modulated data signal is a signal that has one or more of its characteristics set or changed in such a manner as to encode information in the signal. By way of example, and not limitation, the communication medium may use an electrical, optical, RF, or other carrier.

[0121] The innovations may be described in the context of computer-executable instructions, such as those included in program modules, that are executed in a computing system on a target real or virtual processor (e.g., ultimately executed on one or more hardware processors). Generally, program modules or components may include routines, programs, libraries, objects, classes, components, data structures, etc. that perform particular tasks or implement particular abstract data types. The functionality of the program modules may be combined or divided among program modules as desired in various embodiments. The computer-executable instructions for program modules may be executed in local or distributed computing systems.

[0122] For purposes of presentation, the detailed description uses terms such as "determine" and "use" to describe computer operations in a computing system. These terms are high-level descriptions for computer-performed operations and should not be confused with human-performed acts. The actual computer operations corresponding to these terms will vary depending on the implementation.

[0123] Computer-readable medium Any of the computer-readable media herein may be non-transitory (e.g., volatile memory such as DRAM or SRAM, non-volatile memory such as magnetic storage, optical storage, etc.) and / or tangible. Any of the storage actions described herein can be implemented by storing on one or more computer-readable media (e.g., computer-readable storage media or other tangible media). Anything described as being stored (e.g., data created and used during the implementation) can be stored on one or more computer-readable media (e.g., computer-readable storage media or other tangible media). Computer-readable media may be limited to implementations that do not consist of signals.

[0124] Any of the methods described herein may be implemented by computer-executable instructions in (e.g., stored, encoded, etc.) one or more computer-readable media (e.g., computer-readable storage media or other tangible media) or one or more computer-readable storage devices (e.g., memory, magnetic storage, optical storage, etc.). Such instructions can cause a computing device to perform the method. The techniques described herein can be implemented in a variety of programming languages.

[0125] Exemplary Cloud Computing Environment 12 shows an exemplary cloud computing environment 1200 in which the described techniques may be implemented, environment 1200 including, for example, system 100 and other systems herein. Cloud computing environment 1200 may include cloud computing service 1210. Cloud computing service 1210 may include various types of cloud computing resources, such as computer servers, data storage repositories, networking resources, etc. Cloud computing service 1210 can be centrally located (e.g., provided by a data center of a company or organization) or distributed (e.g., provided by various computing resources located in various locations, such as different data centers, and / or located in various cities or countries).

[0126] Cloud computing service 1210 may be used by various types of computing devices (e.g., client computing devices), such as computing devices 1220, 1222, and 1224. For example, the computing devices (e.g., 1220, 1222, and 1224) may be computers (e.g., desktop or laptop computers), mobile devices (e.g., tablet computers or smartphones), or other types of computing devices. For example, the computing devices (e.g., 1220, 1222, and 1224) may use cloud computing service 1210 to perform computing operations (e.g., data processing, data storage, etc.).

[0127] Indeed, cloud-based, premise-based, or hybrid scenarios may be supported.

[0128] Exemplary Implementations Although some actions of the disclosed methods are described in a particular order for convenient presentation, such description encompasses reordering unless a particular order is required by specific language described herein. For example, actions described as sequential can, in some cases, be reordered or performed in parallel.

[0129] As used in this application and in the claims, the singular forms "a," "an," and "the" include the plural forms unless the context dictates otherwise. Furthermore, the word "comprises" means "comprising." Furthermore, "and / or" means "and" or "or," as well as "and" and "or."

[0130] Illustrative clauses Any of the following example clauses may be implemented:

[0131] Clause 1. A computer-implemented method comprising: detecting a performance regression executing a query using a current query plan; and, in response to detecting the performance regression, automatically searching for one or more candidate solutions for resolving the performance regression; selecting from the one or more candidate solutions an effective solution that resolves the performance regression, wherein selecting comprises evaluating performance of executing the query using one or more alternative query plans generated by the one or more candidate solutions; and storing the effective solution for future executions of the query, wherein the effective solution is configured to generate an updated query plan selected from the one or more alternative query plans, wherein the updated query plan has better performance than the current query plan for executing the query.

[0132] Clause 2. The method of clause 1, wherein detecting a performance regression includes comparing one or more performance metrics of executing the query using the current query plan with corresponding performance metrics stored in the execution history.

[0133] Clause 3. The method of clause 2, wherein the one or more performance metrics include query execution time, and wherein a performance regression is detected if the query execution time to execute the query using the current query plan is M times greater than the average of the execution times of the N most recent queries stored in the execution history, where M is a predefined value greater than 1 and N is a predefined positive integer.

[0134] Clause 4. The method of clause 2, wherein the execution history stores one or more legacy query plans pre-generated for the query, and wherein the searching step includes identifying at least some of the legacy query plans stored in the execution history that have better performance than the current query plan for executing the query.

[0135] Clause 5. The method of clause 4, wherein the one or more performance metrics include a query execution time, and wherein a candidate solution is identified if the query execution time corresponding to the selected legacy query plan stored in the execution history is less than the query execution time of executing the query using the current query plan, and the identified candidate solution designates the selected legacy query plan as an alternative query plan.

[0136] Clause 6. The method of clause 5, wherein the effective solution specifies an updated query plan, wherein the query execution time for executing the query using the updated query plan is less than a predefined fraction of the query execution time for executing the query using the current query plan, and wherein query objects included in the current query plan have identical object definitions as corresponding query objects included in the updated query plan.

[0137] Clause 7. The method of any one of clauses 1 to 6, wherein detecting a performance regression includes identifying a predefined subtree pattern in a logical tree structure of the current query plan, the logical tree structure defining a relationship between multiple query operations.

[0138] Clause 8. The method of clause 7, specifying that at least one of the candidate solutions formulates a query using query hints.

[0139] Clause 9. The method of clause 7, further comprising collecting runtime statistics about operations involved in executing a query using the current query plan, and wherein at least one of the candidate solutions specifies formulating the query using the runtime statistics.

[0140] Clause 10. The method of any one of clauses 1 to 9, wherein detecting a performance regression includes identifying that the current query plan was generated with insufficient plan enumeration, and wherein at least one of the candidate solutions specifies formulating the query through exhaustive plan enumeration.

[0141] Clause 11. A computing system comprising a memory, one or more hardware processors coupled to the memory, and one or more computer-readable storage media storing instructions that, when loaded into the memory, cause the one or more hardware processors to perform operations including: detecting a performance regression executing a query using a current query plan; responsive to detecting the performance regression, automatically searching for one or more candidate solutions for resolving the performance regression; selecting from the one or more candidate solutions an effective solution that resolves the performance regression, wherein selecting includes evaluating performance of executing the query using one or more alternative query plans generated by the one or more candidate solutions; and storing the effective solution for future execution of the query, wherein the effective solution is configured to generate an updated query plan selected from the one or more alternative query plans, the updated query plan having better performance than the current query plan for executing the query.

[0142] Clause 12. The system of clause 11, wherein detecting a performance regression includes comparing one or more performance metrics of executing the query using the current query plan with corresponding performance metrics stored in the execution history.

[0143] Clause 13. The system of clause 12, wherein the one or more performance metrics include query execution time, and wherein a performance regression is detected if the query execution time to execute the query using the current query plan is M times greater than the average of the execution times of the N most recent queries stored in the execution history, where M is predefined and greater than 1, and N is a predefined positive integer.

[0144] Clause 14. The system of clause 12, wherein the execution history stores one or more legacy query plans pre-generated for the query, and wherein searching includes identifying at least some of the legacy query plans stored in the execution history that have better performance than the current query plan for executing the query.

[0145] Clause 15. The system of clause 14, wherein the one or more performance metrics include a query execution time, and wherein a candidate solution is identified if the query execution time corresponding to the selected legacy query plan stored in the execution history is less than the query execution time to execute the query using the current query plan, and the identified candidate solution designates the selected legacy query plan as an alternative query plan.

[0146] Clause 16. The system of clause 15, wherein the effective solution specifies an updated query plan, wherein a query execution time for executing the query using the updated query plan is less than a predefined fraction of the query execution time for executing the query using the current query plan, and wherein query objects included in the current query plan have object definitions identical to corresponding query objects included in the updated query plan.

[0147] Clause 17. The system of any one of clauses 11 to 16, wherein detecting a performance regression includes identifying a predefined subtree pattern in a logical tree structure of the current query plan, the logical tree structure defining a relationship between multiple query operations.

[0148] Clause 18. The system of clause 17, wherein at least one of the candidate solutions specifies formulating a query using a query hint.

[0149] Clause 19. The system of clause 18, wherein the operations further include collecting runtime statistics about operations involved in executing a query using the current query plan, and wherein at least one of the candidate solutions specifies formulating a query using the runtime statistics.

[0150] Clause 20. One or more non-transitory computer-readable media encoding computer-executable instructions, the instructions causing one or more processors to perform a method, the method including: detecting a performance regression executing a query using a current query plan; responsive to detecting the performance regression, automatically searching for one or more candidate solutions for resolving the performance regression; selecting, from the one or more candidate solutions, an effective solution that resolves the performance regression, wherein selecting includes evaluating performance of executing the query using one or more alternative query plans generated by the one or more candidate solutions; and storing the effective solution for future execution of the query, the effective solution configured to generate an updated query plan selected from the one or more alternative query plans, the updated query plan having better performance than the current query plan for executing the query.

[0151] Clause 21. A computer-implemented method for multi-tenant database management, comprising: detecting, at a first tenant, a performance regression executing a query using a current query plan; evaluating, in response to detecting the performance regression, one or more candidate solutions for resolving the performance regression; identifying, from the one or more candidate solutions, an effective solution that resolves the performance regression, the effective solution being configured to generate an updated query plan, the updated query plan having better performance than the current query plan for executing the query; building a knowledge object based on the detected performance regression and the identified effective solution; and distributing the knowledge object to a second tenant.

[0152] Clause 22. The method of clause 21, wherein the knowledge object includes a pattern definition and a solution definition, the pattern definition specifying a pattern of detected performance regression and the solution definition specifying an identified effective solution.

[0153] Clause 23. The method of clause 22, wherein the pattern definition specifies a subtree pattern in a logical tree structure of the current query plan, the logical tree structure defining a relationship between multiple query operations.

[0154] Clause 24. The method of clause 22, wherein the pattern definition specifies a query statement representing the query.

[0155] Clause 25. The method of clause 24, wherein the query statement includes a wildcard expression that matches one or more characters of the query.

[0156] Clause 26. The method of clause 22, wherein the solution definition specifies hints, and the identified valid solutions are configured to generate an updated query plan by formulating the query using the hints.

[0157] Clause 27. The method of any one of clauses 21 to 26, wherein the current query plan is originally stored in a plan cache, and wherein evaluating the candidate solution includes generating an alternative query plan using the candidate solution; comparing performance of the current query plan and the alternative query plan when executing the query; and replacing the current query plan stored in the plan cache with the alternative query plan if the alternative query plan has better performance than the current query plan, or discarding the alternative query plan if the current query plan has better performance than the alternative query plan.

[0158] Clause 28. The method of clause 27, wherein after evaluating the candidate solutions, if the alternative query plan has better performance than the current query plan, storing the alternative query plan in the persistent layer, or if the current query plan has better performance than the alternative query plan, storing the current query plan in the persistent layer.

[0159] Clause 29. The method of any one of clauses 21 to 28, wherein the step of detecting performance regression is performed in a first thread on a first computing node of a first tenant, and the step of evaluating one or more candidate solutions is performed in a second thread on the first computing node or on a second computing node of the first tenant.

[0160] Clause 30. The method of any one of clauses 21 to 29, further comprising storing the valid solution for future execution of the query, wherein the storing comprises saving an updated query plan or registering a hint for a patterned query statement representing the incoming query.

[0161] Clause 31. A computing system for multi-tenant database management, comprising: a memory; one or more hardware processors coupled to the memory; and one or more computer-readable storage media storing instructions that, when loaded into the memory, cause the one or more hardware processors to perform operations including: detecting, at a first tenant, a performance regression executing a query using a current query plan; evaluating, in response to detecting the performance regression, one or more candidate solutions for resolving the performance regression; identifying, from the one or more candidate solutions, an effective solution that resolves the performance regression, wherein the effective solution is configured to generate an updated query plan, the updated query plan having better performance than the current query plan for executing the query; building a knowledge object based on the detected performance regression and the identified effective solution; and distributing the knowledge object to a second tenant.

[0162] Clause 32. The system of clause 31, wherein the knowledge objects include pattern definitions and solution definitions, the pattern definitions specifying patterns of performance regressions detected, and the solution definitions specifying effective solutions identified.

[0163] Clause 33. The system of clause 32, wherein the pattern definition specifies a subtree pattern in a logical tree structure of the current query plan, the logical tree structure defining a relationship between multiple query operations.

[0164] Clause 34. The system of clause 32, wherein the pattern definition specifies a query statement representing the query.

[0165] Clause 35. The system of clause 34, wherein the query statement includes a wildcard expression that matches one or more characters of the query.

[0166] Clause 36. The system of clause 32, wherein the solution definition specifies hints, and the identified valid solutions are configured to generate an updated query plan by formulating the query using the hints.

[0167] Clause 37. The system of any one of clauses 31-36, wherein the current query plan is originally stored in a plan cache, and wherein evaluating the candidate solution includes generating an alternative query plan using the candidate solution; comparing performance of the current query plan and the alternative query plan when executing the query; and replacing the current query plan stored in the plan cache with the alternative query plan if the alternative query plan has better performance than the current query plan, or discarding the alternative query plan if the current query plan has better performance than the alternative query plan.

[0168] Clause 38. The system of clause 37, wherein after evaluating the candidate solutions, if the alternative query plan has better performance than the current query plan, the system stores the alternative query plan in the persistent layer, or if the current query plan has better performance than the alternative query plan, the system stores the current query plan in the persistent layer.

[0169] Clause 39. The system of any one of clauses 31-38, wherein detecting performance regression is performed in a first thread on a first computing node of a first tenant and evaluating one or more candidate solutions is performed in a second thread on the first computing node or on a second computing node of the first tenant.

[0170] Clause 40. One or more non-transitory computer-readable media encoding computer-executable instructions, the instructions causing one or more processors to perform a method for multi-tenant database management, the method including: detecting, at a first tenant, a performance regression executing an incoming query having a query syntax using a current query plan; and evaluating one or more candidate solutions for resolving the performance regression in response to detecting the performance regression; detecting, at the first tenant, a performance regression executing the query using the current query plan; and evaluating one or more candidate solutions for resolving the performance regression in response to detecting the performance regression; identifying, from the one or more candidate solutions, an effective solution that resolves the performance regression, the effective solution being configured to generate an updated query plan, the updated query plan having better performance than the current query plan for executing the query; building a knowledge object based on the detected performance regression and the identified effective solution; and distributing the knowledge object to a second tenant.

[0171] Illustrative Alternatives Techniques from any example can be combined with techniques described in any one or more of the other examples. In view of the many possible embodiments to which the principles of the disclosed technology can be applied, it should be recognized that the illustrated embodiments are examples of the disclosed technology and should not be taken as limitations on the scope of the disclosed technology. Rather, the scope of the disclosed technology includes what is covered by the scope and spirit of the appended claims. [Explanation of symbols]

[0172] 100 Database management system, system 105 Data Storage 110 tenants 120 Protocol Layer 122 Plan Cash 126 Persistence Layer, Persistence Layer 128 Tip Registry 130 Query Processing Engine 140 Cache Manager 150 Query Parser 160 Query Optimizer, Query Plan Optimizer 162 Auto PRM 170 Query Executor 180 Hint Manager 190 Auto PRM Coordinator 300 Auto PRM 310 Reverse Detector 320 Candidate Finder 330 Candidate Evaluator 340 Solution Applicator 342 Hint Registry 344 Plan Cash 720 A / B Test Manager 730 Plan Cash 740 Query Optimizer 770 Persistent Layer 1100 Computing System 1110 Processing unit, central processing unit 1115 Processing Unit, Graphics Processing Unit, Co-Processing Unit 1120 Memory, Tangible Memory 1125 Memory, Tangible Memory 1140 Storage, Tangible Storage 1150 Input Devices 1160 output device 1170 Communication Connection 1220 Computing Devices 1222 Computing Devices 1224 Computing Devices

Claims

1. 1. A computer-implemented method for multi-tenant database management, comprising: detecting a performance regression executing a query using a current query plan at a first tenant; In response to detecting the performance regression, evaluating one or more candidate solutions for resolving the performance regression; identifying an effective solution from the one or more candidate solutions that resolves the performance regression, the effective solution being configured to generate an updated query plan, the updated query plan having better performance than the current query plan for executing the query; constructing a knowledge object based on the detected performance regressions and the identified effective solutions; and distributing the knowledge object to a second tenant.

2. The method of claim 1 , wherein the knowledge object includes a pattern definition and a solution definition, the pattern definition specifying the detected pattern of performance regression, and the solution definition specifying the identified effective solution.

3. The method of claim 2 , wherein the pattern definition specifies a subtree pattern in a logical tree structure of the current query plan, the logical tree structure defining a relationship between multiple query operations.

4. The method of claim 2 , wherein the pattern definition specifies a query statement representing the query.

5. The method of claim 4 , wherein the query statement includes a wildcard expression that matches one or more characters of the query.

6. The method of claim 2 , wherein the solution definition specifies hints, and the identified valid solutions are configured to generate the updated query plan by formulating the query using the hints.

7. The current query plan is originally stored in a plan cache, and the step of evaluating candidate solutions comprises: generating alternative query plans using the candidate solutions; comparing the performance of the current query plan and the alternative query plan when executing the query; replacing the current query plan stored in the plan cache with the alternative query plan if the alternative query plan has better performance than the current query plan, or discarding the alternative query plan if the current query plan has better performance than the alternative query plan.

8. 8. The method of claim 7, wherein after evaluating the candidate solutions, if the alternative query plan has better performance than the current query plan, the alternative query plan is stored in a persistent layer, or if the current query plan has better performance than the alternative query plan, the current query plan is stored in a persistent layer.

9. 2. The method of claim 1, wherein detecting a performance regression is performed in a first thread on a first computing node of the first tenant, and evaluating one or more candidate solutions is performed in a second thread on the first computing node or on a second computing node of the first tenant.

10. 10. The method of claim 1, further comprising storing the effective solution for future execution of the query, wherein the storing comprises saving the updated query plan or registering a hint for a patterned query statement representing an incoming query.

11. 1. A computing system for multi-tenant database management, comprising: Memory and one or more hardware processors coupled to the memory; and one or more computer-readable storage media storing instructions that, when loaded into the memory, cause the one or more hardware processors to perform operations, including: Detecting a performance regression executing a query using a current query plan at a first tenant; In response to detecting the performance regression, evaluating one or more candidate solutions for resolving the performance regression; identifying an effective solution from the one or more candidate solutions that resolves the performance regression, the effective solution being configured to generate an updated query plan, the updated query plan having better performance than the current query plan for executing the query; and constructing a knowledge object based on the detected performance regressions and the identified effective solutions; and distributing the knowledge object to a second tenant.

12. 12. The system of claim 11, wherein the knowledge objects include a pattern definition and a solution definition, the pattern definition specifying the detected pattern of performance regression and the solution definition specifying the identified effective solution.

13. The system of claim 12 , wherein the pattern definition specifies a subtree pattern in a logical tree structure of the current query plan, the logical tree structure defining a relationship between multiple query operations.

14. The system of claim 12 , wherein the pattern definition specifies a query statement representing the query.

15. The system of claim 14 , wherein the query statement includes a wildcard expression that matches one or more characters of the query.

16. 13. The system of claim 12, wherein the solution definition specifies hints, and the identified valid solutions are configured to generate the updated query plan by formulating the query using the hints.

17. The current query plan is originally stored in a plan cache, and evaluating candidate solutions includes: generating alternative query plans using the candidate solutions; comparing the performance of the current query plan and the alternative query plan when executing the query; replacing the current query plan stored in the plan cache with the alternative query plan if the alternative query plan has better performance than the current query plan, or discarding the alternative query plan if the current query plan has better performance than the alternative query plan.

18. 18. The system of claim 17, wherein after evaluating the candidate solutions, if the alternative query plan has better performance than the current query plan, the alternative query plan is stored in a persistent layer, or if the current query plan has better performance than the alternative query plan, the current query plan is stored in a persistent layer.

19. 12. The system of claim 11, wherein detecting a performance regression is performed in a first thread on a first computing node of the first tenant, and evaluating one or more candidate solutions is performed in a second thread of the first computing node or on a second computing node of the first tenant.

20. One or more non-transitory computer-readable media encoding computer-executable instructions that cause one or more processors to perform a method for multi-tenant database management, the method comprising: Detecting a performance regression executing an incoming query having a query syntax using a current query plan at a first tenant; In response to detecting the performance regression, evaluating one or more candidate solutions for resolving the performance regression; detecting a performance regression executing a query using a current query plan at a first tenant; In response to detecting the performance regression, evaluating one or more candidate solutions for resolving the performance regression; identifying an effective solution from the one or more candidate solutions that resolves the performance regression, the effective solution being configured to generate an updated query plan, the updated query plan having better performance than the current query plan for executing the query; constructing a knowledge object based on the detected performance regressions and the identified effective solutions; and distributing the knowledge object to a second tenant.

Images