Autoregressive management of multi-tenant databases

Abstract

A computer-implemented method may detect, in a first tenant, a performance regression of executing a query using a current query plan. In response to detecting the performance regression, the method may evaluate one or more candidate solutions for solving the performance regression, and identify an effective solution from among the one or more candidate solutions that solves the performance regression. The efficient solution is configured to generate an updated query plan having better performance than a current query plan used to execute the query. The method can construct knowledge objects based on the detected performance regression and the identified effective solution, and distribute the knowledge objects to the second tenant. Related systems and software for implementing the method are also disclosed.

Description

Background Technology

[0001] A query plan (also known as a query execution plan) is a sequence of steps executed by a database management system (DBMS) to complete a Structured Query Language (SQL) query. When a query is run for the first time in the DBMS, the query optimizer compiles it to generate a corresponding query plan, which can be stored in a memory called a "query plan cache," or simply a "plan cache." Therefore, 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.

[0002] Even for the same query, the query plan generated by the query optimizer can change over time, for example, due to system upgrades to a new database or other reasons. While system upgrades typically lead to performance improvements in the query plan, performance regressions can occur in certain situations. Managing these performance regressions can be challenging due to the sheer number of queries and the complexity of the systems involved. This requires a deep understanding of the query processing engine and the ability to predict how changes in one part of the system might affect query performance. Furthermore, manual management of these regressions is not scalable, especially in large-scale systems with many concurrently running queries. Therefore, automated solutions capable of detecting and handling performance regressions in an effective and reliable manner remain needed. Attached Figure Description

[0003] Figure 1 This is an overall block diagram of an example database management system that supports automatic performance regression management.

[0004] Figure 2 It is a diagram depicting the different stages involved in automated performance regression management.

[0005] Figure 3 This is a block diagram depicting an example module of the Automatic Performance Regression Manager and the operation options for each module.

[0006] Figure 4 This is a flowchart illustrating an example of an overall approach to implementing automatic performance regression management.

[0007] Figure 5 This is a flowchart illustrating an example of an overall approach to achieving knowledge sharing for automated performance regression management among tenants.

[0008] Figure 6 This is a schematic diagram illustrating a method for detecting performance regressions based on data stored in the query execution history.

[0009] Figure 7This is a schematic diagram illustrating candidate solutions for evaluating performance regression using A / B testing.

[0010] Figure 8 Describe the subtree of the example query plan.

[0011] Figure 9 The description includes the specified Figure 8 An example knowledge object defining the pattern of the subtree pattern.

[0012] Figure 10 Describes an example knowledge object that includes a schema definition for a specified query statement.

[0013] Figure 11 This is a block diagram of an example computing system that can implement the described embodiments.

[0014] Figure 12 This is a block diagram of an example cloud computing environment that can be used in conjunction with the technologies described here. Detailed Implementation

[0015] Overview of Query Plan Performance Regression

[0016] The goal of query optimization is to select the query plan that produces the best performance from many candidate query plans. The performance of a query plan can be described in terms of cost, which can be time (e.g., the time required to execute the query plan) and / or the burden on computational resources (e.g., the processing power and / or memory consumed in executing the query plan). Cost-based query optimization selects the query plan with the lowest cost from all candidate query plans. In practice, although the terms "optimal" and "optimized" are used, the actual best plan may not be selected, but the selected plan is considered better than others based on the data available to the optimizer.

[0017] The stability of individual query performance can be quite volatile due to its heavy reliance on decisions made by the query optimizer. These decisions are often based on uncertainty and, in most cases, reflect changes in the underlying data, generating execution plans that are equivalent to or better in terms of performance. However, instances of performance regression exist, meaning that a modified query plan performs worse than its previous plan.

[0018] A common type of performance regression is a long-running query that takes longer than expected to respond. This is measured by the query's execution time (also known as "response time" or "elapsed time"). In some cases, even if the overall CPU consumption of the query plan has been reduced, a long execution time can still be considered a regression because it can indicate that system resources are not being fully utilized.

[0019] Another type of performance regression is high CPU time consumption. While using multiple threads and a significant amount of CPU time can reduce response time, it can also lead to a single query consuming a large amount of system resources. This can impact the performance of other concurrently running queries, thus reducing overall workload throughput. Furthermore, peak storage consumption by a single query can also cause performance regression. Similar to CPU time consumption, peak storage consumption can affect the performance of other queries and result in lower workload throughput on the client system.

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

[0021] In DBMSs, performance regression can occur for a variety of reasons. A major cause of performance regression is changes in system coding. For example, when a system undergoes an upgrade, the query plans used for various queries may change due to changes in code related to the underlying query optimization algorithms, such as logical equivalence plan enumeration rules, physical algorithm enumeration rules, and / or size estimation and cost calculation. While these changes often lead to performance improvements, there are instances where they cause regression.

[0022] Another factor contributing to performance regression is the skewness of the data distribution. For example, if the values ​​in a column of a database table are skewed, the query optimizer's size estimate often fails to predict the reasonable size of the operation's outcome. This inaccurate size estimate may cause the query optimizer to generate a suboptimal query plan, leading to performance regression.

[0023] Changes in data distribution can also lead to performance regression. For example, if the record count of a table changes significantly, previously compiled and cached plans on the table can be automatically recompiled to generate a plan that better reflects the recent data distribution. However, if the newly compiled plan is slower than the previously compiled plan, performance regression may occur.

[0024] Record sampling used for size estimation can also lead to performance regression. In some cases, histograms can be used to represent the distribution of values ​​in a table's columns. However, without a histogram, sampling can be used to estimate filter selectivity or join selectivity. This means that different values ​​may be used each time size estimation is performed, potentially leading to variations in the resulting plan. These variations can cause performance regression.

[0025] Furthermore, the process of compiling queries with specific parameter values ​​can sometimes lead to performance regression. In some scenarios, the query optimizer supports so-called "parameter-aware" query optimization (where the optimizer knows the values ​​assigned to the query parameters). However, there are limitations when it comes to the binding parameter values ​​used for this type of optimization. These parameter values ​​directly affect filter selectivity, resulting in query plans optimized for boundary values. However, for different parameter values, the query plan may be suboptimal. This variability in the performance of query plans based on parameter values ​​represents another potential source of performance regression.

[0026] Addressing performance regressions can be a complex task due to the sheer volume of queries and the complexity of running systems. This requires a deep understanding of the query processing engine and the ability to anticipate how changes within a segment of the DBMS might impact query performance. This is especially true given the variety of performance regressions (e.g., long response times, high resource consumption, etc.), each potentially influenced by multiple factors. In cloud-based database management systems, these challenges are amplified by the distributed nature of the system, the variability of network conditions, and the need for efficient resource allocation across multiple tenants. Manually managing these regressions is technically impractical, particularly in large-scale systems executing many queries concurrently.

[0027] The technology described in this article provides an automated performance regression management (hereinafter referred to as "Automatic PRM") framework. This Automatic PRM framework is designed to intelligently identify and mitigate performance regressions, thereby optimizing query execution and enhancing overall system performance in multi-tenant cloud-based database environments.

[0028] Example computing system supporting automatic PRM

[0029] Figure 1This diagram shows a general block diagram of a sample database management system 100 that supports automatic PRM. The database management system 100 can be configured to support multi-tenancy, a feature of many types of cloud computing services. In a multi-tenant environment, one instance of a software application and supporting infrastructure (e.g., virtual machines, storage, etc.) can serve multiple user groups or tenants. Therefore, multiple tenants can share the same applications and other computing resources running on the same hardware and operating system with the same data storage mechanism(s). Even with shared resources, each tenant can appear to have its own application instance. Importantly, the underlying data of each tenant can be kept separate and secure.

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

[0031] In some examples, query processing engine 130 may include cache manager 140, query parser 150, query optimizer 160, query executor 170, and hint manager 180. Cache manager 140 can access plan cache 122. Plan cache 122 represents a fast-access storage space configured to store previously compiled query plans.

[0032] An incoming query 112 sent from tenant 110 can be processed by prompt manager 180 to output query 114, which may be the same as or different from the incoming query 112. Prompt manager 180 maintains a prompt registry 128, which may include one or more pairs of query statements and corresponding statement prompts, or simply include prompts (i.e., each query statement in prompt registry 128 has a paired prompt). Query statements stored in prompt registry 128 may be patterned (e.g., with wildcard expressions) or non-patterned (e.g., literal queries, queries including explicit object names, etc.).

[0033] Some of the input queries 112 may include prompt commands, such as commands for adding, removing, enabling, and disabling prompts. Based on the received prompt commands, the prompt manager 180 can update the prompt registry 198. For example, in response to receiving a command to add a prompt to a query statement, the prompt manager 180 can add a pair of query statements and prompts to the prompt registry 128. Conversely, in response to receiving a command to remove one or more prompts from a query statement, the prompt manager 180 can delete a pair of query statements and corresponding prompts (or more) from the prompt registry 128. The prompt manager 180 can also enable or disable prompts registered in the prompt registry 128 based on received prompt commands.

[0034] 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, the corresponding hint can 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 attached hint) is propagated downstream of the hint manager 180, for example, processed by the cache manager 140, query parser 150, query optimizer 160, etc. On the other hand, if no matching query statement is found, the incoming query 112 is not modified and can be directly passed to the cache manager 140 for query processing.

[0035] The prompt manager 180 can use string comparison to determine whether the incoming query 112 matches any query statement. For each patterned query statement stored in the prompt registry 128, when a comparison is performed, the wildcard expression contained in the patterned query statement can match one or more characters of the incoming query.

[0036] The cache manager 140 receives a query 114 (which may be an incoming query 112 with an appended hint or an unmodified incoming query 112) sent from the hint manager 180. The cache manager 140 may evaluate the received query 114 to determine whether the query 114 has a corresponding (compiled) query plan stored in the plan cache 122.

[0037] If the cache manager 140 does not find a query plan corresponding to query 114 in the plan cache 122, query 114 can be analyzed by the query parser 150, which can check whether query 114 contains syntax and / or semantic errors. After verifying that query 114 is a valid transactional SQL statement (e.g., SELECT, INSERT, UPDATE, DELETE, MERGE, etc.), the query parser 150 can generate a logical tree (also known as a "query tree") in which query 114 can be executed.

[0038] As described in this article, a query tree is a logical representation of a query statement. It consists of multiple nodes and edges linking the nodes. Nodes can include leaf nodes and one or more internal nodes. Leaf nodes have no child nodes. Conversely, internal nodes have one or more child nodes. The root or root node of the query tree can be considered a special internal node. A query tree represents a relational algebra expression. Specifically, tables involved in the query can be represented as leaf nodes. Relational algebra operations can be represented as internal nodes. The root node represents the query as a whole. When executing the query plan, internal nodes can be executed while the operand tables of the internal nodes are available. The internal nodes can then be replaced with the result table generated by the operations represented by the internal nodes. This process can continue against all internal nodes until the root node is executed and replaced by the result table, which can then be returned as the query result.

[0039] The query optimizer 160 can use a logic tree to generate a corresponding query plan that determines how query 114 will be executed. The query optimizer 160 is configured to select the query plan that produces the best performance (among multiple query plans generated based on enumeration of the logic tree). The performance of a query plan can be described in terms of cost, which can be time (e.g., the time required to execute the query plan) and / or the burden on computational resources (e.g., the processing power and / or memory consumed in executing the query plan). Cost-based query optimization selects the query plan with the lowest cost from all candidate query plans. In practice, although the terms "optimal" and "optimized" are used, it is not necessary to select an actual best query plan; however, based on the data available to the query optimizer 160, the selected query plan is considered better than other query plans.

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

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

[0042] As described herein, query compilation refers to the process of transforming query 114 into an optimal query plan (e.g., checking for syntax and / or semantic errors, generating a logic tree, and determining the optimal query plan), as mentioned above. Depending on the complexity of query 114 (e.g., the number of join tables, etc.) and the query optimization algorithm, query compilation time can be relatively long (e.g., tens of seconds or longer). Therefore, to improve operational efficiency, the compiled query plan for query 114 (i.e., the determined optimal query plan) can be stored in a plan cache 122, so that the query plan can be quickly retrieved and reused if the same query is encountered again in the future.

[0043] For example, if cache manager 140 determines that query 114 has a corresponding query plan stored in plan cache 122, that query plan can be directly retrieved from plan cache 122 and forwarded to query executor 170 for execution. Therefore, in this scenario, the operations of query parser 150 and query optimizer 160 can be bypassed. In other words, query 114 does not need to be recompiled because its previously compiled query plan is available in plan cache 122.

[0044] The plan cache 122 is configured to store compiled query plans. For each received query 114, the cache manager 140 checks whether it has a compiled query plan stored in the plan cache 122. If so, the cached query plan can be reused. This improves efficiency because it eliminates the time spent compiling query 114 (i.e., regenerating the query plan). On the other hand, if query 114 does not have a compiled query plan stored in the plan cache 122, then query 114 must be compiled. The compiled query plan can then be stored in the plan cache 122, making it possible to quickly access its cached query plan when the same query 114 occurs again in the future.

[0045] If the received query 114 is new (i.e., the first query that has not been encountered before), then there is no corresponding query plan in the plan cache 122, and it must be compiled 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 there is a corresponding compiled query plan 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.

[0046] The plan cache 122 has a finite size. Therefore, it may not be able to store all compiled query plans. When the plan cache 122 approaches its full capacity, some query plans may have to be evicted from the plan cache 122 to make room for new plans, according to a predetermined plan eviction policy implemented by the cache manager 140. For example, the cache manager 140 may implement a random plan eviction policy, which evictions query plans from the plan cache 122 in a random manner. In another example, the cache manager 140 may implement a least recently used (LRU) plan eviction policy, which 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 may be used, which first evictions the least frequently used execution plan. The cache manager 140 may also use other plan eviction policies.

[0047] As mentioned above, query plan performance regressions can occur, for example, due to system upgrades and / or data changes to the database management system 100. To mitigate potential performance regressions in query plans, the query plan optimizer 160 can communicate with an automatic performance regression manager, referred to as Automatic PRM 162, which is configured to detect performance regressions and automatically find solutions to address these issues, thereby ensuring optimal performance and efficiency of the database management system 100.

[0048] As described more fully below, the Auto PRM 162 can use different methods to manage performance regressions. In some examples, the Auto PRM 162 can leverage the execution history 124, which stores a list of serialized query plans (also known as abstract query plans (“ASPs”)) and their corresponding execution statistics (e.g., execution time, CPU consumption, peak memory usage, etc.). The 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 the execution history 124. If a performance regression occurs for a query plan, these stored ASPs can be reused to regenerate the original query plan, thus preserving the original performance. The data stored in the execution history 124 can be persisted to the persistent layer 126. The execution history 124 can be stored in volatile memory, which allows for fast access and manipulation of the data. The persistent layer 126 ensures that the ASPs and execution statistics are preserved even when the volatile memory is cleared during a system restart. This allows, for example, the data to be reloaded from the persistent layer 126 back into the execution history 124 in memory after a system restart, thus ensuring the continuity of plan stability.

[0049] In some examples, the database management system 100 may also include an automated performance regression management coordinator 190, configured to propagate knowledge about automated performance regression management across multiple tenants 110. For example, a tenant can acquire knowledge about performance regressions exhibiting a specific pattern and discover corresponding solutions to correct the regression. This knowledge can be encapsulated in different knowledge objects 192. The automated performance regression management coordinator 190 can then share this knowledge object 192 with another tenant, thereby facilitating a collaborative and effective approach to performance regression management within the multi-tenant database management system 100.

[0050] In practice, the systems illustrated in this paper (such as the database management system 100) can vary in complexity, incorporating additional functionalities, more complex components, etc. For example, additional functionalities can exist within the query processing engine 130. Additional components may be included to implement security, redundancy, load balancing, reporting design, etc.

[0051] The described computing system can be networked via a wired or wireless network connection (including the Internet). Alternatively, the system can be connected via an intranet connection (e.g., in a corporate environment, government environment, etc.).

[0052] The database management system 100 and any other systems described herein can be implemented in conjunction with any of the hardware components described herein, such as the computing systems described below (e.g., processing units, memory, etc.). In any example herein, queries, query plans, runtime statistics, hints, knowledge objects, etc., can be stored in one or more computer-readable storage media or computer-readable storage devices. The techniques described herein are general in terms of operating system or hardware details and can be applied to any kind of environment to take advantage of the described features.

[0053] Sample components for automatic PRM

[0054] Figure 2 The diagram illustrates the different stages of automated performance regression management. In some examples, each tenant of the database management system can implement its own automated PRM process 200, which has multiple stages with different tasks, such as regression detection 210 (e.g., for detecting performance regressions in queries), candidate search 220 (e.g., for identifying candidate solutions that can potentially resolve the detected performance regressions), candidate evaluation 230 (e.g., for evaluating whether any of the identified candidate solutions effectively resolves the performance regressions), and solution application 240 (e.g., applying the effective solution for future processing of queries). In some examples, the automated PRM process 200 may further include cross-tenant knowledge sharing 250. For example, based on the detected patterns of performance regressions and the identified solutions for resolving performance regressions, one or more knowledge objects can be constructed, which can be shared among multiple tenants.

[0055] Figure 3 The drawing is configured to implement Figure 2 Automatic PRM process 200 Automatic PRM 300 (which can be Figure 1 Example modules of the Automatic PRM 162 (example embodiment). As shown, the Automatic PRM 300 includes: a regression detector 310 configured to detect performance regressions of a query (e.g., regression detection 210); a candidate finder 320 configured to search for candidates that can resolve the detected performance regressions (e.g., candidate search 220); a candidate evaluator 330 configured to evaluate whether a valid solution exists among the candidate solutions (e.g., candidate evaluation 230); and a solution applicator 340 configured to apply a valid solution (if found) for future processing of the same query (e.g., solution application 240).

[0056] Each module of the automated PRM 300 can have multiple operational options. For example, the regression detector 310 can use different methods to detect performance regressions in queries. These methods can combine performance data stored in the execution history 322 (similar to execution history 124) with runtime statistics 312 (similar to runtime statistics 172), identify specific patterns in query statement 314, and / or be based on user input 316. The candidate finder 320 can identify candidate solutions by searching the ASP stored in the execution history 322, applying predefined hints 324 to query statement 314, optimizing queries using feedback from runtime statistics 312, and / or modifying query optimizer settings 326 (e.g., forcing the query optimizer to perform a full plan enumeration). The candidate evaluator 330 can evaluate the identified candidate solutions using foreground tests 332 (which may affect user workload performance) or background tests 334 (which do not directly affect user workload performance). After the candidate evaluator 330 identifies a valid solution (to resolve the detected performance regression), the solution applicator 340 can store it for future processing of the same query. Depending on its characteristics, effective solutions can be stored in the plan cache 344 (similar to plan cache 122), execution history 322, or prompt registry 342 (similar to prompt registry 128). Further details regarding the operation options for each module are described below in more comprehensive terms.

[0057] Example general method for automatic PRM

[0058] Figure 4 This is a flowchart illustrating the overall method 400 for implementing automatic PRM, and can be, for example, by... Figure 1 The database management system executes this. Specifically, the automatic PRM feature can be implemented by... Figure 1 Automatic PRM 162 or Figure 3 Automatic PRM 300 execution.

[0059] In section 410, this method can detect performance regressions when executing queries using the current query plan. The detection of performance regressions can, for example, be performed by... Figure 3 The regression detector 310 is used to perform this.

[0060] In step 420, in response to the detection of a performance regression, the method can automatically search for one or more candidate solutions to address the performance regression. The search for candidate solutions can be, for example, by... Figure 3 The candidate finder 320 is used to execute.

[0061] In 430, the method can select an effective solution to address performance regression from one or more candidate solutions. The selection may include evaluating the performance of executing the query using one or more alternative query plans generated from the candidate solutions. Such evaluation can, for example, be performed by… Figure 3 The candidate evaluator 330 is executed.

[0062] In section 430, this method can store valid solutions for future execution of the query. In other words, when the same query is processed in the future, the valid solution will be applied. The valid 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 used to execute the query. Storing valid solutions can, for example, be done through... Figure 3 The solution is applied by the 340 application.

[0063] Figure 5 This is a flowchart illustrating a general approach 500 for sharing automated PRM knowledge for multi-tenant database management, and can be, for example, derived from... Figure 1 The Automatic PRM Coordinator 190 combined with the Automatic PRM 162 (or Figure 3 The automatic PRM 300 will be used to execute this.

[0064] In 510, this method can detect performance regressions in the first tenant when executing queries using the current query plan. The detection of performance regressions can, for example, be performed by... Figure 3 The regression detector 310 is used to perform this.

[0065] In step 520, in response to the detection of performance regression, the method can evaluate one or more candidate solutions for resolving the performance regression. Candidate solutions can be identified, for example, via a candidate finder 320, and can be identified, for example, via... Figure 3 The candidate evaluator 330 is used to perform the evaluation.

[0066] In step 530, the method can identify effective solutions to address performance regressions from one or more candidate solutions. Effective solutions are configured to generate updated query plans that offer better performance than the current query plan used to execute the query. Effective solutions can be identified based on the results of evaluating candidate solutions in step 520.

[0067] In 540, this method can construct knowledge objects based on performance regression of detection and effective solutions for identification (e.g., Figure 1 The knowledge object 192).

[0068] Then, at 550, the method can distribute the knowledge object to a second tenant (different from the first tenant). The management of knowledge objects, including their distribution, can be, for example, handled by... Figure 1 The automatic PRM coordinator 190 is executed.

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

[0070] The actions shown can be described from alternative perspectives while still implementing the techniques. For example, "receiving" can also be described as "sending" from a different perspective.

[0071] Example performance measurement

[0072] As described in this article, the performance of executing queries using a query plan can be evaluated based on predefined criteria involving multiple metrics.

[0073] For example, a performance metric could be the execution time of a query. A query plan that results in a short execution time is generally considered superior to a query plan that requires a longer execution time. Metrics related to the computational resources consumed (e.g., CPU time, peak storage consumption, etc.) can also be used to measure performance. For instance, a query plan that uses less CPU time to execute a query (thus leaving more resources available for other processes) can be considered better than another query plan that uses more CPU time. Similarly, among different query plans for the same query, a query plan that consumes less memory can be considered preferred because it reduces the likelihood of memory-related bottlenecks.

[0074] In some cases, multiple metrics can be combined into a composite score to provide a holistic view of query plan performance. The specific weights assigned to each metric in the composite score can be predefined by the user based on system requirements and the nature of the workload. For example, in systems with limited memory, the weight assigned to peak storage consumption might be higher. As another example, in time-critical applications where response time is critical, the weight assigned to query execution time might be given more importance. In some cases, it may be appropriate to consider query execution time exclusively, meaning that this is the only metric considered when evaluating the performance of a query plan.

[0075] In certain scenarios, the performance of various query execution plans can be evaluated based on predefined rules that involve simultaneously assessing multiple conditions. For example, let's consider two query execution plans, A and B. A predefined rule can determine that query plan A performs better than query plan B if two conditions are met: (a) query plan A executes at least X times faster than 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 strikes a balance between execution speed and resource usage, contributing to the overall health and efficiency of the database system. Additional rules can be developed as needed.

[0076] Example execution history

[0077] In some examples, the history of past query executions can be preserved in the execution history, such as... Figure 1 Execution history 124.

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

[0079] 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 can include the number of threads allocated, available CPU and memory, the type and version of the DBMS used, the size of the queried dataset, network latency, etc. These factors affect query performance regardless of the query plan used. For example, if a query previously executed using a certain query plan used 20% of available CPU and memory resources and completed in 5 minutes, but recently the same query executed using the same query plan took the same amount of time but used 80% of the resources, this could indicate performance regression. Even if the execution time remains constant, increased resource usage may indicate inefficiencies not present in previous executions. In other words, session-related information can be used in conjunction with performance metrics in performance analysis.

[0080] In some examples, query plans previously used to execute queries may also be stored in the execution history along with corresponding performance metrics collected when executing queries using those query plans. For example, previously compiled query plans for a query may be serialized into an abstract query plan (ASP), which may be stored in the execution history. The serialized ASP may have parameters that can be optimized by a query optimizer (e.g., ...). Figure 1The query optimizer (160) reads / writes data in a format such as JSON. 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, provided by SAP SE of Walldorf, Germany. In plan stability, consistent optimal performance of select statements can be ensured by capturing ASPs in the data store, allowing the same query plan to be reused when the query is executed again. Example ASP storage and methods for capturing query plans are described in U.S. Patent Nos. 11,748,349 and 11,556,538, which are incorporated herein by reference in their entirety. As further described below, some query plans stored in the execution history can be identified as candidate solutions for addressing performance regressions.

[0081] In some examples, the metadata and / or information of the query objects used by the query optimizer to generate the query plan may also be stored in the execution history. A query may include one or more query objects (e.g., tables, views, indexes, etc.). The metadata of the query objects may include the definitions or logical structures of these query objects. By comparing the current metadata of an object with the metadata of a stored ASP, the system can determine whether the logical structure of the object has changed. For example, if there are changes in the object parameters that define its logical structure (such as the number of attributes, the names of attributes, the data types of attributes, etc.), the previously stored ASP involving the object may be considered invalid and not reusable for plan stability purposes, and it may not be selected as a candidate solution for resolving query regressions, as described below. Additionally, the information used by the query optimizer may also change over time, which may invalidate and deprecate the stored ASP (and therefore, it may not be selected as a candidate solution for resolving query regressions). For example, changes in the use of hints, the estimated size of operators, data distribution, or the presence or absence of column indexes can all affect query optimization. If these factors change, the query may need to be recompiled, even if the logical structure of the query objects remains the same.

[0082] Example prompts the registry

[0083] In some examples, one or more hints can be added to an incoming query to generate a query plan. Hint management can be handled by a hint registry (such as...). Figure 1 The prompt (Registry 128) is being processed.

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

[0085] The query statement included in the prompt registry can be patterned or non-patterned. As described herein, patterned query statements include wildcard expressions, while non-patterned query statements lack wildcards. A wildcard expression can be a special character or sequence of characters that matches any character or character set in a string. The wildcard can take various forms, including matching any literal, matching literals within a constant set, matching literals within a range, or matching literals that begin with a specific string or have a specific data type. String comparisons can be used to determine whether the received query matches any query statement stored in the prompt registry.

[0086] For example, consider the incoming query: `SELECT * FROM users WHERE name LIKE 'John%'`. Assume a patterned query, such as `SELECT * FROM users WHERE name LIKE $$$$`, exists in the prompt registry that matches this incoming query. Also assume a prompt (such as `(NO_USE_HEX_PLAN)`) matches this patterned query in the prompt record. Therefore, when the prompt manager recognizes a match between the incoming query and a patterned query in the prompt record, it can add the prompt to the incoming query for downstream query optimization.

[0087] Example methods for detecting performance regression

[0088] In any of the examples described herein, automated PRM can detect performance regressions used to execute queries. For example, automated PRM can have detection logic (e.g., Figure 3 The regression detector 310 is configured to detect performance regressions using various methods.

[0089] In some examples, automated PRM can leverage the execution history stored in the detection performance regression (e.g., Figure 1 Execution history 124 or Figure 3The performance data can be stored in the execution history (322). Specifically, automatic PRM can be configured to detect performance regression by comparing one or more performance metrics that execute a query using the current query plan with corresponding performance metrics stored in the execution history. As mentioned above, the execution history can maintain corresponding performance metrics collected during previous executions of the query. This can indicate performance regression if the performance metrics of the current query plan (e.g., execution time and / or resource consumption) are significantly worse than those of previous performance metrics. As mentioned above, this comparison can be performed individually for each metric, based on a composite score of multiple metrics, or based on specific rules. Specific criteria for determining regression can be predefined based on system requirements and the nature of the workload.

[0090] For example, performance regression can be detected based on query execution time. In a specific example, a performance regression can be detected if the query execution time using the current query plan is M times greater than the average of the N most recent query execution times stored in the execution history, where M is predefined and greater than 1 (e.g., M could be 1.5, 2, 2.5, 3, 4, 5, etc.), and N is a predefined positive integer (e.g., N could be 1, 2, 3, 4, 5, 6, 7, 8, or more). In other words, a moving window of the N most recent query execution times can be used to detect performance regression. If the execution time of the current query plan is M times greater than the average execution time within that 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.

[0091] Figure 6 A schematic representation of the time history used to execute the query is provided. The graph shows some variability, particularly noticeable at query execution times at 604 and 606. However, the query execution times remain within an acceptable range of fluctuation, specifically not exceeding twice the moving average. Therefore, the automated PRM did not detect performance regressions at those times. On the other hand, the query execution time at 608 was found to exceed an acceptable limit (e.g., five times the moving average of the last eight executions). This significant deviation triggered the detection of performance regressions, indicating a potential need for adjustments to improve query performance.

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

[0093] In some examples, performance regression analysis can reveal specific query patterns that may cause the query optimizer to make suboptimal decisions, thus generating query plans that result in poor performance. Such patterns (hereinafter also referred to as anti-patterns) can be predefined for automatic PRM. As described herein, anti-patterns can be defined based on the logical structure of the query plan, which can be serialized to ASP or other equivalent formats describing the query plan. For example, ASP can specify a logical tree structure defining the relationships between multiple query operations. Anti-patterns can include subtrees of the logical tree structure. In some examples, anti-patterns may also include runtime statistics such as data distribution of table columns, filter selectivity, correlation between table columns, etc. Once an anti-pattern is detected (e.g., through...), Figure 3 The regression detector 310 can indicate performance regression and trigger candidate search to find effective solutions to mitigate regression.

[0094] An example anti-pattern is index joins with post-filters, especially in skewed data distributions. This pattern can cause the query optimizer to underestimate the selectivity of the post-filter, resulting in potentially suboptimal index join operations. For example, if the join values ​​have large duplicates, the actual size of the index join may become significantly larger than the estimated size, leading to performance degradation.

[0095] Another example of an anti-pattern is the absence of pre-aggregation with multiple grouping columns, which can lead to large estimation errors (overestimation). The size of the result from a grouping operation with multiple grouping columns is highly dependent on the relevance between the columns. However, if the query optimizer does not consider this relevance and overestimates the result size, it may choose a suboptimal query plan.

[0096] Another example of an anti-pattern is index joins with large duplicates. If an index join is chosen but the search value in the index has many copies, the index join can suffer from performance degradation. In this case, the optimizer should choose a different join algorithm that does not depend on the index (e.g., hash join).

[0097] Another approach to detecting performance regression is based on detecting insufficient plan enumeration. As mentioned above, the query optimizer can use various enumeration rules to enumerate many possible query plans, one of which will be selected or considered the best query plan for query execution. However, the query optimizer may have plan enumeration constraints configured to prevent it from enumerating all possible query plans. For example, consider a scenario where an application uses composite views that depend on other complex views to define its data model. When processing a single query on one of these views, the query optimizer flattens the view into an inline view, resulting in a large plan with many operations and tables. This complexity can be further compounded when join operations are pushed down to unionall operations, effectively multiplying a single join by the number of union child nodes. The search space for this complex plan becomes so large that the query optimizer struggles to enumerate all possible alternative plans within its configured plan enumeration constraints. If the query optimizer fails to fully enumerate all possible alternative query plans, this information can be flagged in the resulting query plan. Therefore, query plans flagged for insufficient plan enumeration can indicate performance regression.

[0098] In some examples, users can manually tag or label query plans (e.g., query plans stored in the plan cache) that exhibit regression performance or are likely to cause performance regression. This can be used, for example, when the query is newly introduced by another application, or when there is insufficient data on the execution history (e.g., shortly after a database system upgrade), to support the automated regression detection described above. After detecting such regression labels or annotations, automated PRM can proceed to a candidate search phase to identify effective solutions for resolving performance regressions, as further described below.

[0099] Example methods for searching candidate solutions

[0100] After detecting a performance regression or a high probability of performance regression while executing a query using the current query plan, Automated PRM can automatically trigger a search to identify candidate solutions that can resolve the performance regression. For example, Automated PRM can have a search module configured to search for candidate solutions using various methods (e.g., Figure 3 Candidate Finder 320).

[0101] In some examples, for each query, the execution history (e.g., Figure 1 Execution history 124 or Figure 3The execution history (322) may store one or more legacy query plans previously generated for a query, along with corresponding performance metrics previously obtained when executing the query using those legacy query plans. If at least some of the legacy query plans stored in the execution history have better performance than the current query plan used to execute the query, those legacy query plans can be identified as candidate solutions.

[0102] For example, each of the legacy query plans stored in the execution history can have a corresponding query execution time. 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, a candidate solution can be identified. The identified candidate solution designates the selected legacy query plan as an alternative query plan for executing the query. For example, in Figure 6 In the example depicted, after detecting a performance regression, the automated PRM identifies a legacy query plan with a shorter query execution time in a 602 error. Candidate solutions can specify using the legacy query plan to execute the query. In some examples, the search can find multiple legacy query plans stored in the execution history as candidate solutions (e.g., all plans in the execution history that demonstrate better performance than the current one can be identified as candidate solutions). While the execution time is described above as an example of comparing performance, it should be understood that other performance metrics can be used to search for candidate solutions.

[0103] In some examples, to search for candidate solutions, automated PRM not only compares performance metrics but also considers the validity of legacy query plans stored in the execution history. This is because some stored legacy query plans may no longer be suitable for executing queries, even if 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, the logical structure of the object changes. This change can invalidate a legacy query plan because it is based on the object's previous definition. Therefore, despite its previous superior performance, the legacy query plan cannot be selected as a candidate solution for addressing performance regressions. Additional details on evaluating the validity of previously stored query plans are described in U.S. Patent No. 11,907,217, the entire contents of which are incorporated herein by reference.

[0104] In some examples, performance regressions are detected based on anti-pattern detection, as described above. In this case, at least some of the candidate solutions may include applying predefined hints (or more) to the query. In other words, candidate solutions may instruct the query optimizer to utilize paired query hints (or more hints) to generate alternative query plans. Each anti-pattern may have a corresponding hint (or more hints). For example, if the regression comes from the anti-pattern "index join with post-filter" or "index join with large duplicate indexes," a hint indicating that index joins should not be used (e.g., the hint "NO_HEX_INDEX_JOIN" in SAP HANA) can be used for query optimization to avoid generating index joins with problematic indexes. As another example, if the regression comes from the anti-pattern "missing pre-aggregation with multiple grouping columns," a hint instructing the query optimizer to apply pre-aggregation (group-by) before the join operation can be used. These hints serve as instructions for the query optimizer, guiding it to compile the query in a way that mitigates performance regressions.

[0105] Alternatively, runtime statistics of operations involved in the execution of the query (e.g., Figure 3 Runtime statistics 312 or Figure 1 When runtime statistics (172) are available, at least some of the candidate solutions can specify the use of runtime statistics to compile the query. In other words, candidate solutions can instruct the query optimizer to utilize runtime statistics to generate alternative query plans. As mentioned above, some anti-patterns relate to the difference 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 the actual execution results. Generally, runtime statistics collected from the executed query plan can more accurately reflect the data statistics of internal nodes in the query tree (representing query operations that generate intermediate results). Therefore, 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. Patent No. 11,803,545, the entire contents of which are incorporated herein by reference.

[0106] Therefore, if a performance regression is detected due to an anti-pattern, candidate solutions can specify applying hints or using runtime statistics, or both. While hints can guide the query optimizer towards a certain path, they are applied broadly throughout the query, affecting all query objects. Runtime statistics, on the other hand, provide a more granular and targeted approach. They allow the optimizer to make informed decisions based on actual data from specific operations or query objects, such as the size of table columns. Therefore, in some cases, candidate solutions based on runtime statistics can be considered to have higher priority than those based on hints.

[0107] As mentioned above, performance regression can be caused by insufficient plan enumeration. Therefore, if a query experiences performance regression and the query plan is flagged as having insufficient plan enumeration, at least one candidate solution can specify generating an alternative query plan by compiling the query with full plan enumeration (e.g., by temporarily ignoring or increasing pre-configured plan enumeration limits).

[0108] In some examples, users can also define candidate solutions for user-labeled or known query plans to regress. For instance, after a database system upgrade, if it is found that the new query execution engine A has regressed compared to the previous query execution engine B, the user can define a candidate solution that will be applied to the query to instruct the query optimizer to generate an alternative query plan by using query execution engine B instead of query execution engine A.

[0109] Example methods for evaluating candidate solutions

[0110] After identifying candidate solutions for the performance regression of the detection, automated PRM can trigger the evaluation of these candidate solutions to determine whether any of them effectively resolves the performance regression. For example, automated PRM can evaluate candidate solutions using foreground or background testing (e.g., via...). Figure 3 Candidate evaluator 330).

[0111] In foreground testing (also known as "A / B testing"), the selected candidate solutions can be directly applied to the user's query input, thus impacting the performance of the user's workload. This approach does not require additional background execution of the candidate solutions, as described further below. Instead, it uses the selected candidate solutions to execute a regression query triggered by the user's query input. If the performance regression is resolved by the candidate solution, that candidate solution is determined to be the valid solution and will be applied to the subsequent execution of the regression query, as described further below.

[0112] As described in this article, an effective solution specifies an updated query plan that differs from the current query plan associated with performance regression. To qualify as an effective solution, the performance of the updated query plan must meet one or more predefined performance criteria. For example, a criterion might require that the query execution time using the updated query plan is less than a predetermined fraction (e.g., 50%, 25%, etc.) of the query execution time using the current query plan. Other criteria, such as CPU usage and / or peak storage consumption, can be specified as needed.

[0113] Front-end testing in automated PRM can leverage predefined rules to prioritize the evaluation of different candidate solutions. For example, candidate solutions using 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 recompilation. Automated PRM can also use different strategies to identify effective solutions from the evaluated candidate solutions. One example approach is to evaluate multiple candidate solutions and compare the results to find the solution with the best performance. Alternatively, if a candidate solution is found that resolves the performance regression, it can be identified as an effective solution, eliminating the need to further evaluate other candidate solutions, even if they may potentially offer better performance. To ensure reliability, the evaluation of each candidate solution can be repeated, with an average performance metric used to provide more reliable evaluation results.

[0114] During foreground testing, the automatic PRM can be configured to handle abnormal behavior. For example, if the evaluation response time exceeds K times the response time of the previous query plan (where K is a user-defined parameter, such as K=3, 5, 8, etc.), the evaluation can be canceled, and the automatic PRM can automatically switch back to the previous query plan and run it again. As another example, if the evaluation stops due to an anomaly, the anomaly can be handled internally by the A / B test, and the previous query plan can be rerun to ensure that the user's query processing is not interrupted. In the event of a system crash during evaluation, the automatic PRM can retain the new query plan and crash information to prevent the system failure from recurring.

[0115] Figure 7 This illustration schematically depicts a use case where A / B testing is used to evaluate candidate solutions for resolving performance regression. In this example, query 710 has a current query plan 750 (represented by query tree A shown in Box 1) that was previously compiled by query optimizer 740 and stored in plan cache 730. The current query plan 750 has been flagged or previously detected as suffering from performance regression (e.g., by...). Figure 3 The regression detector 310). Identifying candidate solutions (e.g., through...). Figure 3 The candidate lookup manager 720 (which can be a candidate lookup manager 320) compiles a query 710 with specific hints to generate an alternative query plan. Therefore, upon receiving query 710, instead of using the current query plan 750 in the plan cache 730 to execute query 710, the A / B test manager 720 (which can be a candidate lookup manager 320) performs the A / B test 720 test. Figure 3The candidate evaluator 330 (a component of the query optimizer) can instruct the query optimizer 740 to recompile the query 710 using specified hints to generate an alternative query plan 760 (represented by query tree B). The alternative query plan 760 can be stored in the plan cache 730 (as shown in Box 2) along with the current query plan 750 and used to execute the query 710. The execution performance associated with the alternative query plan 760 (e.g., query execution time, CPU consumption, etc.) can be compared with the execution performance associated with the current query plan 750 to determine if 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). Otherwise, no valid solution is found, so the current query plan 750 is retained in the plan cache 730, while the alternative query plan 760 is removed from the plan cache 730 (as shown in Box 3). If additional candidate solutions exist, a similar process can be repeated to evaluate the performance of alternative query plans generated by these additional candidate solutions. After completing the A / B testing, the query plans retained in the plan cache 730 can be stored in the persistent layer 770 (similar to...). Figure 1 In the persistent layer 720. Therefore, after a system reboot that may clear the plan cache 730 (e.g., volatile memory), the A / B test manager 720 can automatically restore the plan cache 730 by retrieving the query plan maintained in the persistent layer 770. For example, Figure 7 This shows that after the system restart, the current query plan 750 is restored in the plan cache 730 (in box 5), assuming that the previous A / B test did not find a valid solution.

[0116] In some examples, background testing can be used to evaluate candidate solutions, which can mitigate the impact on the evaluation process and the user's workload, or even isolate the evaluation process from the user's workload. There are two different approaches to performing background testing. In the first approach, the evaluation can be performed in a separate thread. For example, performance regression testing can be performed in a first thread of the database system, while candidate solutions can be evaluated in a second thread of the database system. If the system has sufficient idle resources, such as CPU and memory, this approach ensures that the user's workload is not directly affected by the evaluation process. However, if the system is heavily loaded with user tasks, a more careful evaluation is needed, for example, by limiting the number of threads used for evaluation and / or limiting available memory to configurable values. In the second approach, the evaluation can be performed on a separate instance, such as an Elastic Compute Node (ECN), a cloud-based service that provides scalable computing power. This approach can completely isolate the user's workload from the evaluation process, but it will require accurately replicating the original system's data distribution within the ECN for the evaluation process.

[0117] Example methods for applying solutions to mitigate performance regression

[0118] After identifying effective solutions for resolving query performance regressions, these effective solutions can be stored and applied to future executions of the query. For example, depending on the type of effective solution, automated PRM can have different mechanisms for storing and applying effective solutions (e.g., through...). Figure 3 The solution application 340) is used for future query execution.

[0119] For example, if a valid solution is found after A / B testing, as described above, the updated query plan (corresponding to the valid solution) has already been compiled 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, automatic PRM stores valid solutions in the plan cache.

[0120] As another example, if an effective solution (through foreground or background testing) is found to resolve a performance regression using a legacy query plan stored in the execution history, automated PRM can mark that legacy query plan as potentially usable for future query executions. In other words, automated PRM can store effective solutions in the execution history.

[0121] In yet another example, if a valid solution is found (through front-end or back-end testing) to recompile the query with a prompt, the automatic PRM can be applied to the prompt registry (e.g., Figure 1 The prompt registry (128) registers prompt records. A prompt record can include a query (or a patterned query statement representing a query) and a prompt. Therefore, when a query is received again in the future, it will be compiled along with the prompt. In other words, automatic PRM can store valid solutions in the prompt registry.

[0122] Example methods for sharing automated PRM knowledge

[0123] In the context of multi-tenant database management systems, the detection of performance regressions and their corresponding effective solutions can be considered valuable experiences. These experiences, when analyzed, can reveal specific patterns that form knowledge subjects. In some examples, each tenant can have its own local automated PRM. The knowledge of the automated PRM obtained from each tenant can be encapsulated into different knowledge objects, which can be, for example, derived from... Figure 1 The automated PRM coordinator 190 is centrally stored and distributed across multiple tenants. By sharing these knowledge objects, other tenants can proactively prevent similar performance regressions, thus promoting a collaborative and effective approach to managing performance regressions in a multi-tenant database management system.

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

[0125] In some examples, the schema definition in a knowledge object can specify a subtree schema within the logical tree structure of the current query plan. As mentioned above, the logical tree structure defines the relationships between multiple query operations. Therefore, a subtree schema can capture the relationships between one or more operations included in the current query plan. If a query plan has a matching subtree schema defined in a knowledge object, the query plan can be considered to have a high risk of performance regression.

[0126] As an example, Figure 8The diagram shows a subtree 800 representing the query plan associated with the performance regression. Subtree 800 is characterized by the anti-pattern "index join with post-filter." Specifically, subtree 800 has a parent node 802 representing an index join operation with post-filter, and two child nodes 804 and 806 being joined. Here, the post-filter refers to a filter that can be applied to the table represented by child node 806, whose index is used for the index join. However, this post-filter is not applied before the join operation. This is because if the filter is applied to the table represented by child node 806 before the join operation represented by parent node 802, the table's index cannot be used for the index join. Instead, a hash join must be used. As described above, this anti-pattern can indicate a performance regression.

[0127] Figure 8 The subtree pattern described in the document can be specified in Knowledge Object 900 in JSON format, such as... Figure 9 As shown. For example, knowledge object 900 has a "subtree schema" (indicating a schema definition based on a query plan subtree schema), which includes three operators with parent-child relationships, such as... Figure 8 As shown. In some examples, the subtree pattern can further specify conditions associated with performance regression. For example, Figure 9 The results show that performance regression is not only characterized by inverse patterns, but is also associated with large estimation errors (e.g., the actual size of the index join is more than 1000 times larger than the estimated size).

[0128] like Figure 9 As shown, knowledge object 900 also specifies an effective solution for the corresponding subtree pattern. In the depicted example, the effective solution is to add the hint "NO_HEX_INDEX_JOIN" to the query. In other words, if the query plan matches the subtree pattern specified in knowledge object 900, potential performance regressions can be mitigated by compiling the query with the specified hint.

[0129] In some examples, the schema definition within a knowledge object can specify the query statement that represents the query. In other words, the query itself can represent a knowledge schema. This can happen, for example, when a tenant employs a distributed application with known performance regressions for certain queries.

[0130] The query statement specified in the knowledge object can be non-patterned (e.g., the same as the query itself) or patterned (e.g., including wildcard expressions that match one or more characters of the query). As an example, Figure 10This example shows a Knowledge Object 1000 written in JSON format. Knowledge Object 1000 has a "Statement" type (indicator pattern definition specifies the query statement). Specifically, Knowledge Object 1000 defines a patterned query statement: `SELECT * FROM T WHERE A=$$%$$`. Additionally, Knowledge Object 1000 specifies a valid solution that adds the hint "NO_HEX_INDEX_JOIN" to the query. Tenants receiving Knowledge Object 1000 can register the patterned query statement and the corresponding hint in their hint registry. Therefore, when a query matches the patterned statement `SELECT * FROM T WHERE A=$$%$$`, the hint `NO_HEX_INDEX_JOIN` will be automatically applied to the query when the query optimizer generates the query plan.

[0131] Example Advantages

[0132] The automated PRM framework described in this paper provides significant technical advantages in multi-tenant database management systems by automating complex tasks that regress management performance.

[0133] The disclosed technology can automatically navigate different stages 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 can be executed concurrently, and performance regressions may occur due to various factors. Therefore, the framework's automated nature significantly reduces the need for manual intervention, thereby reducing the likelihood of human error and improving efficiency.

[0134] The disclosed automated PRM framework demonstrates significant versatility by combining multiple approaches for each stage of performance regression management. For regression detection, it can leverage performance metrics from execution history, identify anti-patterns in query statements, use runtime statistics, and even consider user input. This multifaceted approach allows for more comprehensive and accurate detection of performance regressions. Similarly, in the candidate search phase, the framework can utilize ASP 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 are considered. In the candidate evaluation phase, both foreground and background testing can be employed, allowing for a thorough evaluation of the effectiveness of each candidate solution without significantly impacting user workload performance. In the solution application phase, effective solutions can be stored in various locations, such as the schedule cache, execution history, or a hint registry, ensuring optimal retrieval and application for future query processing.

[0135] Furthermore, the automated PRM framework disclosed in this paper supports knowledge sharing in multi-tenant database management systems. This knowledge sharing is facilitated by creating and distributing knowledge objects, which encapsulate valuable experience and solutions related to performance regressions. These knowledge objects can define performance regression patterns in a generic way, such as subtree patterns or query statements, and specify effective solutions to mitigate these regressions. Once created, these knowledge objects can be propagated across multiple tenants, enabling them to proactively prevent similar performance regressions, thereby promoting a collaborative and efficient approach to managing performance regressions in multi-tenant database environments.

[0136] Example computing system

[0137] Figure 11 Examples of suitable computing systems 1100 in which the described innovations can be implemented are depicted. The computing system 1100 is not intended to impose any limitation on the scope or functionality of this disclosure, as the innovations can be implemented in different computing systems.

[0138] refer to Figure 11 The computing system 1100 includes one or more processing units 1110, 1115 and memories 1120, 1125. Figure 11 In this document, the basic configuration 1130 is included within the dashed lines. Processing units 1110 and 1115 can execute computer-executable instructions, such as those for implementing the features described in the examples herein (e.g., methods 400 and 500). Processing units can be general-purpose central processing units (CPUs), processors in application-specific integrated circuits (ASICs), or any other type of processor. In a multiprocessor system, multiple processing units can execute computer-executable instructions to increase processing power. For example, Figure 11 A central processing unit 1110 and a graphics processing unit or coprocessor 1115 are shown. Physical memories 1120, 1125 may be volatile memories (e.g., registers, caches, RAM), non-volatile memories (e.g., ROM, EEPROM, flash memory, etc.), or some combination thereof, accessible by the processing units 1110, 1115. Memories 1120, 1125 may be adapted to store, in the form of computer-executable instructions that execute from the processing units 1110, 1115, implementing one or more innovative software 1180 described herein.

[0139] The computing system 1100 may have additional features. For example, the 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 user interaction. Interconnection mechanisms (not shown), such as buses, controllers, or networks, may interconnect the components of the computing system 1100. Typically, operating system software (not shown) provides an operating environment for other software executing in the computing system 1100 and coordinates the activities of the components of the computing system 1100.

[0140] Physical storage 1140 may be removable or non-removable and includes disks, magnetic tapes or cassettes, CD-ROMs, DVDs, or any other medium that can be used to store information in a non-transitory manner and is accessible within computing system 1100. Storage 1140 may store instructions for implementing one or more innovative software described herein.

[0141] Input devices (or more) 1150 may be input devices such as a keyboard, mouse, pen or trackball, voice input device, scanning device, touch device (e.g., touchpad, monitor, etc.), or another device that provides input to computing system 1100. Output devices 1160 may be a monitor, printer, speaker, CD burner, or another device that provides output from computing system 1100.

[0142] Communication connections (or multiple connections) 1170 enable communication with another computing entity via a communication medium. The communication medium can transmit information, such as computer-executable instructions, audio or video input or output, or other data in modulated data signals. A modulated data signal is a signal whose one or more characteristics are set or altered in a manner that encodes information in the signal. By way of example and not limitation, the communication medium can be electrical, optical, RF, or other carriers.

[0143] Innovations can be described in the context of computer-executable instructions, such as those included in a program module, that execute on a computing system targeting a real or virtual processor (e.g., ultimately executing on one or more hardware processors). Typically, a program module or component can include routines, programs, libraries, objects, classes, components, data structures, etc., that perform a specific task or implement a specific abstract data type. In various embodiments, the functionality of a program module can be combined or split among program modules as needed. The computer-executable instructions for a program module can execute within a local or distributed computing system.

[0144] For the sake of presentation, detailed descriptions use terms such as "determine" and "use" to describe computer operations in a computing system. These terms are high-level descriptions of operations performed by a computer and should not be confused with actions performed by humans. The actual computer operations corresponding to these terms vary depending on the implementation.

[0145] Computer-readable media

[0146] Any computer-readable medium described herein may be non-transitory (e.g., volatile memory such as DRAM or SRAM, non-volatile memory such as magnetic memory, optical memory, etc.) and / or tangible. Any storage operation described herein may be implemented by storage in 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 implementation) may be stored in 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.

[0147] Any method described herein may be implemented by computer-executable instructions (e.g., stored thereon, encoded thereon, etc.) in 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 may cause a computing device to perform the method. The techniques described herein may be implemented in various programming languages.

[0148] Example cloud computing environment

[0149] Figure 12 An example cloud computing environment 1200 in which the described technologies can be implemented is depicted, including, for example, system 100 and other systems described 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 repositories, networking resources, etc. Cloud computing service 1210 may be centrally located (e.g., provided by a data center of an enterprise or organization) or distributed (e.g., provided by various computing resources located in different locations (such as different data centers) and / or in different cities or countries).

[0150] Cloud computing service 1210 can be utilized by various types of computing devices (e.g., client computing devices), such as computing devices 1220, 1222, and 1224. For example, computing devices (e.g., 1220, 1222, and 1224) can be computers (e.g., desktop or laptop computers), mobile devices (e.g., tablets or smartphones), or other types of computing devices. For example, computing devices (e.g., 1220, 1222, and 1224) can utilize cloud computing service 1210 to perform computing operations (e.g., data processing, data storage, etc.).

[0151] In practice, it can support cloud-based, local-based, or hybrid scenarios.

[0152] Example Implementation

[0153] Although some of the operations of the disclosed methods are described in a specific order for ease of presentation, this description includes rearrangement unless the specific language used in this paper requires a particular order. For example, operations described sequentially may be rearranged or performed concurrently in some cases.

[0154] As stated in this application and the claims, the singular forms “a,” “an,” and “the” include the plural forms unless the context clearly indicates otherwise. Additionally, the term “comprising” means “including.” Furthermore, “and / or” means “and” or “or,” as well as “and” and “or.”

[0155] Example Terms

[0156] Any of the following example terms can be implemented.

[0157] Clause 1. A computer-implemented method comprising: detecting a performance regression when executing a query using a current query plan; automatically searching for one or more candidate solutions to resolve the performance regression in response to detecting the performance regression; selecting an effective solution to resolve the performance regression from the one or more candidate solutions, wherein the selection includes evaluating the performance of executing the query using one or more alternative query plans generated from the one or more candidate solutions; and storing the effective solution for future query execution, 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 used to execute the query.

[0158] Clause 2. The method as described in Clause 1, wherein detecting performance regression includes comparing one or more performance metrics that execute the query using the current query plan with corresponding performance metrics stored in the execution history.

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

[0160] Clause 4. The method as described in Clause 2, wherein the execution history stores one or more legacy query plans previously generated for the query, wherein the search includes identifying at least some of the legacy query plans stored in the execution history that have better performance than the current query plan used to execute the query.

[0161] Clause 5. The method as described in Clause 4, wherein one or more performance metrics include query execution time, 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, wherein the identified candidate solution designates the selected legacy query plan as an alternative query plan.

[0162] Clause 6. The method as described in Clause 5, wherein the valid solution specifies an updated query plan, wherein the query execution time of executing the query using the updated query plan is less than a predetermined fraction of the query execution time of executing the query using the current query plan, wherein the query objects included in the current query plan have the same object definition as the corresponding query objects included in the updated query plan.

[0163] Clause 7. The method as described in any one of Clauses 1-6, wherein detecting performance regression includes identifying predefined subtree patterns with respect to the logical tree structure of the current query plan, wherein the logical tree structure defines the relationships between multiple query operations.

[0164] Clause 8. The method as described in Clause 7, wherein at least one of the candidate solutions specifies that the query is compiled using query hints.

[0165] Clause 9. The method as described in Clause 7 further includes collecting runtime statistics of operations involved in executing the query using the current query plan, wherein at least one of the candidate solutions specifies that the query is compiled using runtime statistics.

[0166] Clause 10. The method of any one of Clauses 1-9, wherein detecting performance regression includes identifying that the current query plan was generated using an insufficient plan enumeration, wherein at least one of the candidate solutions specifies that the query was compiled by a full plan enumeration.

[0167] 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, which, when loaded into the memory, cause the one or more hardware processors to perform operations including: detecting a performance regression when executing a query using a current query plan; automatically searching for one or more candidate solutions to resolve the performance regression in response to detecting the performance regression; selecting an effective solution to resolve the performance regression from the one or more candidate solutions, wherein the selection includes evaluating the performance of executing the query using one or more alternative query plans generated from the one or more candidate solutions; and storing the effective solution for future query execution, 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 used to execute the query.

[0168] Clause 12. The system as described in Clause 11, wherein detecting performance regression includes comparing one or more performance metrics that execute a query using the current query plan with corresponding performance metrics stored in the execution history.

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

[0170] Clause 14. The system as described in Clause 12, wherein the execution history stores one or more legacy query plans previously generated for a query, wherein the search includes identifying at least some of the legacy query plans stored in the execution history that have better performance than the current query plan used to execute the query.

[0171] Clause 15. The system as described in Clause 14, wherein one or more performance metrics include query execution time, 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, wherein the identified candidate solution designates the selected legacy query plan as an alternative query plan.

[0172] Clause 16. The system as described in Clause 15, wherein the effective solution specifies an updated query plan, wherein the query execution time of executing the query using the updated query plan is less than a predetermined fraction of the query execution time of executing the query using the current query plan, wherein the query objects included in the current query plan have the same object definition as the corresponding query objects included in the updated query plan.

[0173] Clause 17. The system as described in any one of Clauses 11-16, wherein detecting performance regression includes identifying predefined subtree patterns with respect to the logical tree structure of the current query plan, wherein the logical tree structure defines the relationships between multiple query operations.

[0174] Clause 18. The system as described in Clause 17, wherein at least one of the candidate solutions specifies that the query is compiled using query hints.

[0175] Clause 19. The system according to Clause 18, wherein the operation further includes collecting runtime statistics of operations involved in executing the query using the current query plan, wherein at least one of the candidate solutions specifies that the query is compiled using runtime statistics.

[0176] Clause 20. One or more non-transitory computer-readable media having computer-executable instructions encoded thereon, the computer-executable instructions causing one or more processors to perform a method, the method comprising: detecting a performance regression in executing a query using a current query plan; automatically searching for one or more candidate solutions to resolve the performance regression in response to detecting the performance regression; selecting an effective solution to resolve the performance regression from the one or more candidate solutions, wherein the selection includes evaluating the performance of executing the query using one or more alternative query plans generated from the one or more candidate solutions; and storing the effective solution for future query execution, 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 used to execute the query.

[0177] Clause 21. A computer-implemented method for managing a multi-tenant database, the method comprising: detecting a performance regression in a first tenant when executing a query using a current query plan; in response to detecting the performance regression, evaluating one or more candidate solutions for resolving the performance regression; identifying an effective solution for resolving the performance regression from the one or more candidate solutions, wherein the effective solution is configured to generate an updated query plan that has better performance than the current query plan used to execute the query; constructing a knowledge object based on the detected performance regression and the identified effective solution; and distributing the knowledge object to a second tenant.

[0178] Clause 22. The method as described in Clause 21, wherein the knowledge object includes a pattern definition and a solution definition, wherein the pattern definition specifies a pattern of performance regression of the detection, and wherein the solution definition specifies an effective solution for identification.

[0179] Clause 23. The method as described in Clause 22, wherein the schema definition specifies a subtree schema in the logical tree structure of the current query plan, wherein the logical tree structure defines the relationship between multiple query operations.

[0180] Clause 24. The method described in Clause 22, wherein the schema definition specifies the query statement representing the query.

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

[0182] Clause 26. The method according to Clause 22, wherein the solution definition specifies a hint, wherein the identified valid solution is configured to generate an updated query plan by compiling the query using the hint.

[0183] Clause 27. The method of any one of Clauses 21-26, wherein the current query plan is initially stored in a plan cache, wherein evaluating candidate solutions comprises: generating alternative query plans using candidate solutions; comparing the 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.

[0184] Clause 28. The method as described in Clause 27, wherein, after evaluating candidate solutions, if an alternative query plan has better performance than the current query plan, the alternative query plan is stored in the persistence layer, or if the current query plan has better performance than the alternative query plan, the current query plan is stored in the persistence layer.

[0185] Clause 29. The method of any one of Clauses 21-28, wherein performance regression detection is performed in a first thread on a first compute node of the first tenant, and wherein evaluation of one or more candidate solutions is performed in a second thread on the first compute node or on a second compute node of the first tenant.

[0186] Clause 30. The method of any one of Clauses 21-29 further includes storing a valid solution for future query execution, wherein the storage includes saving an updated query plan or registering hints representing patterned query statements of the incoming query.

[0187] 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, which, when loaded into the memory, cause the one or more hardware processors to perform operations, said operations including: detecting performance regression in a first tenant for executing a query using a current query plan; evaluating one or more candidate solutions for resolving the performance regression in response to detecting the performance regression; identifying an effective solution for resolving the performance regression from the one or more candidate solutions, wherein the effective solution is configured to generate an updated query plan that has better performance than the current query plan used to execute the query; constructing knowledge objects based on the detected performance regression and the identified effective solution; and distributing the knowledge objects to a second tenant.

[0188] Clause 32. The system according to Clause 31, wherein the knowledge object includes a pattern definition and a solution definition, wherein the pattern definition specifies a pattern of performance regression of the detection, and wherein the solution definition specifies an effective solution for identification.

[0189] Clause 33. The system according to Clause 32, wherein the schema definition specifies a subtree schema in the logical tree structure of the current query plan, wherein the logical tree structure defines the relationship between multiple query operations.

[0190] Clause 34. The system described in Clause 32, wherein the schema definition specifies the query statement representing the query.

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

[0192] Clause 36. The system according to Clause 32, wherein the solution definition specifies a hint, wherein the identified valid solution is configured to generate an updated query plan by compiling the query using the hint.

[0193] Clause 37. The system according to any one of Clauses 31-36, wherein the current query plan is initially stored in a plan cache, wherein evaluating candidate solutions includes: generating alternative query plans using candidate solutions; comparing the 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.

[0194] Clause 38. The system as described in Clause 37, wherein, after evaluating candidate solutions, if an alternative query plan has better performance than the current query plan, the alternative query plan is stored in the persistence layer, or if the current query plan has better performance than the alternative query plan, the current query plan is stored in the persistence layer.

[0195] Clause 39. The system as described in any one of Clauses 31-38, wherein performance regression testing is performed in a first thread on a first compute node of the first tenant, and wherein evaluation of one or more candidate solutions is performed in a second thread on the first compute node or on a second compute node of the first tenant.

[0196] Clause 40. One or more non-transitory computer-readable media having computer-executable instructions encoded thereon, the computer-executable instructions causing one or more processors to perform a method for multi-tenant database management, the method comprising: detecting, in a first tenant, a performance regression of executing an incoming query having a query syntax using a current query plan; in response to detecting the performance regression, evaluating one or more candidate solutions for resolving the performance regression; detecting, in the first tenant, a performance regression of executing the query using the current query plan; in response to detecting the performance regression, evaluating one or more candidate solutions for resolving the performance regression; identifying, from the one or more candidate solutions, a valid solution for resolving the performance regression, wherein the valid solution is configured to generate an updated query plan having better performance than the current query plan used to execute the query; constructing a knowledge object based on the detected performance regression and the identified valid solution; and distributing the knowledge object to a second tenant.

[0197] Example Alternatives

[0198] The techniques from any example can be combined with the techniques described in any one or more other examples. Given the many possible embodiments to which the principles of the disclosed techniques can be applied, it should be understood that the illustrated embodiments are examples of the disclosed techniques and should not be considered as limiting the scope of the disclosed techniques. Rather, the scope of the disclosed techniques includes the scope and spirit covered by the appended claims.

Claims

1. A computer-implemented method for managing a multi-tenant database, the method comprising: Detect performance regressions for executing queries using the current query plan in the first tenant; In response to the detection of performance regression, evaluate one or more candidate solutions to address the performance regression; Identify effective solutions to address performance regressions from one or more candidate solutions, wherein effective solutions are configured to generate updated query plans that have better performance than the current query plan used to execute the query; Constructing knowledge objects based on performance regression of detection and effective solutions for identification; and Distribute the knowledge objects to the second tenant.

2. The method according to claim 1, wherein, The knowledge objects include pattern definitions and solution definitions, where the pattern definition specifies the pattern of performance regression of the detection, and the solution definition specifies the effective solution for identification.

3. The method according to claim 2, wherein, The schema definition specifies the subtree schema in the logical tree structure of the current query plan, where the logical tree structure defines the relationships between multiple query operations.

4. The method according to claim 2, wherein, The schema definition specifies the query statement that represents the query.

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

6. The method according to claim 2, wherein, The solution definition specifies a hint, where a valid solution is configured to generate an updated query plan by compiling the query using the hint.

7. The method according to claim 1, wherein, The current query plan is initially stored in the plan cache, where candidate solutions are evaluated, including: Generate alternative query plans using candidate solutions; Compare the performance of the current query plan and alternative query plans when executing a query; and If an alternative query plan has better performance than the current query plan, the current query plan stored in the plan cache is replaced with the alternative query plan; otherwise, if the current query plan has better performance than the alternative query plan, the alternative query plan is discarded.

8. The method according to claim 7, wherein, After evaluating candidate solutions, if the alternative query plan performs better than the current query plan, the alternative query plan is stored in the persistence layer; otherwise, if the current query plan performs better than the alternative query plan, the current query plan is stored in the persistence layer.

9. The method according to claim 1, wherein, Performance regression testing is performed in the first thread on the first compute node of the first tenant, while evaluation of one or more candidate solutions is performed in the second thread on the first compute node or on the second compute node of the first tenant.

10. The method of claim 1, further comprising storing a valid solution for future query execution, wherein, Storage includes saving updated query plans or registering hints for representing patterned query statements in incoming queries.

11. A computing system for multi-tenant database management, comprising: Memory; One or more hardware processors, coupled to memory; as well as One or more computer-readable storage media store instructions that, when loaded into memory, cause one or more hardware processors to perform operations, said operations including: Detect performance regressions for executing queries using the current query plan in the first tenant; In response to the detection of performance regression, evaluate one or more candidate solutions to address the performance regression; Identify effective solutions to address performance regressions from one or more candidate solutions, wherein effective solutions are configured to generate updated query plans that have better performance than the current query plan used to execute the query; Constructing knowledge objects based on performance regression of detection and effective solutions for identification; and Distribute the knowledge objects to the second tenant.

12. The system according to claim 11, wherein, The knowledge objects include pattern definitions and solution definitions, where the pattern definition specifies the pattern of performance regression of the detection, and the solution definition specifies the effective solution for identification.

13. The system according to claim 12, wherein, The schema definition specifies the subtree schema in the logical tree structure of the current query plan, where the logical tree structure defines the relationships between multiple query operations.

14. The system according to claim 12, wherein, The schema definition specifies the query statement that represents the query.

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

16. The system according to claim 12, wherein, The solution definition specifies a hint, where a valid solution is configured to generate an updated query plan by compiling the query using the hint.

17. The system according to claim 11, wherein, The current query plan is initially stored in the plan cache, where candidate solutions are evaluated, including: Generate alternative query plans using candidate solutions; Compare the performance of the current query plan and alternative query plans when executing a query; and If an alternative query plan has better performance than the current query plan, the current query plan stored in the plan cache is replaced with the alternative query plan; otherwise, if the current query plan has better performance than the alternative query plan, the alternative query plan is discarded.

18. The system according to claim 17, wherein, After evaluating candidate solutions, if the alternative query plan performs better than the current query plan, the alternative query plan is stored in the persistence layer; otherwise, if the current query plan performs better than the alternative query plan, the current query plan is stored in the persistence layer.

19. The system according to claim 11, wherein, Performance regression testing is performed in the first thread on the first compute node of the first tenant, while evaluation of one or more candidate solutions is performed in the second thread on the first compute node or on the second compute node of the first tenant.

20. One or more non-transitory computer-readable media having computer-executable instructions encoded thereon, the computer-executable instructions causing one or more processors to perform a method for multi-tenant database management, the method comprising: Performance regression analysis was performed on the first tenant to test the execution of an incoming query with the current query syntax using the current query plan. In response to the detection of performance regression, evaluate one or more candidate solutions to address the performance regression; Detect performance regressions for executing queries using the current query plan in the first tenant; In response to the detection of performance regression, evaluate one or more candidate solutions to address the performance regression; Identify effective solutions to address performance regressions from one or more candidate solutions, wherein effective solutions are configured to generate updated query plans that have better performance than the current query plan used to execute the query; Constructing knowledge objects based on performance regression of detection and effective solutions for identification; and Distribute the knowledge objects to the second tenant.

Images