Automatic recompilation for parameterized queries

Abstract

A computer-implemented method may receive a parameterized query having a set of input parameters, where the parameterized query has a query plan stored in a plan cache, and generate a compilation history associated with the parameterized query based on a compilation history associated with the parameterized query. Determining an estimated compilation plus execution time for compiling and executing the parameterized query having the input parameter set, based on an execution history associated with the query plan, determining an estimated execution time for executing the parameterized query having the input parameter set by using the query plan, and determining a cache gain based at least in part on the estimated compilation plus execution time and the estimated execution time. In response to finding that the cache gain is positive, the method may execute a parameterized query with a set of input parameters by using the query plan. Otherwise, the method may compile and execute a parameterized query with a set of input parameters.

Description

Background Art

[0001] A query plan (also known as a "query execution plan") is a series of steps that a database management system (DBMS) executes to complete a query. When a query is first run in a DBMS, the query can be compiled by a query optimizer to generate a corresponding query plan, which can be stored in a memory called a query plan cache or simply a plan cache. The query plan stored in the plan cache can also be referred to as a cached query plan, or simply a cached plan. Thus, when the same query is run again, the DBMS does not need to regenerate the query plan. Instead, it can reuse the cached query plan stored in the query plan cache, thereby enhancing the efficiency of the DBMS.

[0002] Query optimization refers to the overall process of attempting to select the most efficient query plan among many candidate query plans to execute a query. In this context, a parameterized query can be beneficial, which uses placeholders for parameters whose values are provided at query runtime. It allows the DBMS to cache query plans, thereby avoiding the need for repeated query compilation and enhancing performance. However, query optimization for parameterized queries may not always provide the expected results. Therefore, there is room for improvement in handling parameterized queries. Brief Description of the Drawings

[0003] Figure 1 is an overall block diagram of an example runtime parameterized query management system.

[0004] Figure 2 depicts an example compilation history and an example execution history of a parameterized query.

[0005] Figure 3 is a schematic diagram showing the determination of cache gain based on estimated compilation-plus-execution time and estimated execution time.

[0006] Figure 4 is a flowchart showing an example overall method for implementing runtime parameterized query recompilation.

[0007] Figure 5 is a flowchart showing an example method for updating the compilation history of a parameterized query.

[0008] Figure 6 is a flowchart showing an example method for updating the execution history associated with the query plan of a parameterized query.

[0009] Figure 7It is a flowchart showing an example method for counting beneficial and non-beneficial cases when running multiple executions on a set of query plans using a cache.

[0010] Figure 8 It is a flowchart showing an example method for determining whether a query plan in a cache needs to be replaced.

[0011] Figure 9 It is a schematic diagram showing a use case of runtime parameterized query recompilation where a parameterized query has a single query plan.

[0012] Figure 10 It is a schematic diagram showing another use case of runtime parameterized query recompilation where a parameterized query has multiple query plans.

[0013] Figure 11 It is a block diagram of an example computing system in which the described embodiments may be implemented.

[0014] Figure 12 It is a block diagram of an example cloud computing environment that can be used in conjunction with the techniques described herein. Detailed Description

[0015] Overview of Parameterized Query Optimization

[0016] As described herein, query optimization refers to the overall process of attempting to select the most efficient query plan among many candidate query plans to execute a query. In practice, although the terms "optimal" and "optimization" are used, the actual best query plan may not be selected, but the selected query plan is considered better than other query plans based on the data available to the query optimizer in the query processing engine.

[0017] As described herein, query compilation refers to the process of converting an incoming query into a query plan. Depending on the complexity of the query (e.g., the number of joined tables, etc.) and the query optimization algorithm, the query compilation time may be long (e.g., dozens of seconds or longer). Therefore, to improve operational efficiency, the compiled query plan corresponding to the incoming query (e.g., the determined optimal query plan) can be stored in a plan cache so that if the same query is submitted again in the future, the query plan can be quickly retrieved and reused.

[0018] Query optimization can also be applied to parameterized queries. A parameterized query is a type of Structured Query Language (SQL) query that allows parameters to be placed within the query statement as placeholders. When a parameterized query is executed, each parameter can take on its value (e.g., in a separate statement), which allows the parameterized query to be reused with different values and for different purposes. A parameterized query can have multiple parameterized predicates, allowing multiple parameters to have their assigned values during query execution. As described herein, the set of parameter values for a parameterized query can also be referred to as a parameter set. The parameter values in a parameter set can have various data formats, such as integer, floating point, string, boolean, date, time, etc.

[0019] There are several benefits to using parameterized queries. Generally, parameterized queries are more readable than non-parameterized queries. Parameterized queries can be prepared in advance and then reused for similar applications without having to create different SQL queries for each case, thus avoiding repeated query compilation that can consume significant computational resources. Parameterized queries can also be used for security reasons. For example, parameterized queries can be used to mitigate or avoid so-called SQL injection attacks that can lead to unauthorized access to sensitive data.

[0020] However, query optimization for parameterized queries may not always provide the expected results because the values of the query parameters may not be provided until query execution. Additionally, the performance of the cached plan for a parameterized query can fluctuate based on the input parameters. For example, when a parameterized query is executed with a particular set of input parameters, the cached plan can produce excellent performance due to the optimization of the query plan based on those specific parameters. However, if the input parameters change, the same cached plan may not be as efficient. This is because the cached plan is optimized based on the initial parameters, which may not be optimal for different parameters. Therefore, reusing the same cached plan with different input parameters may result in sub-optimal performance.

[0021] The technical challenge of determining whether to use a cached query plan or generate a new query plan for a parameterized query stems from the unpredictability of the parameter values at query execution. This unpredictability complicates the decision-making process because it requires accurately predicting how these parameter values will affect the performance of the query plan. The dynamic nature of the database further amplifies the complexity of this problem. For example, changes in data distribution over time may affect the optimality of the cached plan, making it more difficult to decide whether to stick with the cached plan or replace it with a new one. This highlights the intricate balance required to manage parameterized queries in an evolving database environment.

[0022] The techniques described herein introduce systems and methods for runtime parameterized query management, which can automatically determine whether to reuse or recompile a query plan during runtime. This feature ensures optimal performance by adapting to changes in parameter values and data distribution, thereby maintaining the efficiency and accuracy of database operations. This innovative approach provides a technical solution to the challenges posed by the unpredictability of parameter values and the dynamics of databases in query execution, thus representing a significant advancement in the field of database management systems.

[0023] Example Runtime Parameterized Query Management System

[0024] Figure 1 FIG. shows an overall block diagram of an example runtime parameterized query management system 100 configured to automatically determine at runtime whether an existing query plan should be reused or a recompile of the query plan should be initiated.

[0025] In Figure 1 In the depicted example, the runtime parameterized query management system 100 includes a parameterized query plan manager 120 and a parameterized query log repository 160. Each received parameterized query 110 (with a corresponding set of input parameters) to be run on a target database 150 can be processed by the parameterized query plan manager 120, and the actions taken for the parameterized query 110 and related data can be stored in the parameterized query log repository 160. As described more fully below, the parameterized query plan manager 120 is configured to determine at runtime whether to reuse or recompile a cached query plan based on data stored in the parameterized query log repository 160.

[0026] In some examples, the received parameterized query 110 may have at least one corresponding query plan stored in a plan cache 130. For example, for a parameterized query 110, a cached query plan may be precompiled based on the same set of input parameters or a different set of parameters (e.g., by a query processing engine 140). In some cases, the parameterized query plan manager 120 may determine to reuse one of the cached query plans to process the parameterized query 110. In this scenario, the query processing engine 140 may retrieve one of the cached query plans from the plan cache 130 and use it to execute the parameterized query 110 (with the set of input parameters) against the target database 150.

[0027] In some examples, the received parameterized query 110 does not have a corresponding cached query plan, or even if the parameterized query 110 has a corresponding cached query plan, the parameterized query plan manager 120 may determine that it is not desirable to use one of the cached query plans (e.g., results in an execution time longer than the estimated compilation plus execution time). In this scenario, the query processing engine 140 may compile and execute the parameterized query 110 (with the set of input parameters) without using one of the cached query plans.

[0028] In some examples, the parameterized query plan manager 120 may initiate an update to the current set of cached query plans after multiple executions of the parameterized query 110. This update may be triggered when it is observed that frequent compilation is required for the parameterized query 110 without using one of the cached query plans, indicating that the previous set of cached query plans may be sub-optimal. This proactive approach can help improve performance by updating the cached query plans to better align with the recurring needs of the parameterized query 110.

[0029] As Figure 1 shown, the parameterized query log repository 160 includes a compilation history 170 and an execution history 180.

[0030] For each unique parameterized query 110 (which may have different sets of input parameters), a corresponding compilation history 170 may be stored in the parameterized query log repository 160. The compilation history 170 may contain the compilation plus execution time for compiling and executing the parameterized query with multiple unique sets of parameters. As described herein, a set of parameters is considered unique if no other set of parameters has the exact same combination of parameter values in the same order. As described herein, the compilation plus execution time refers to the total time for first compiling the query to generate a query plan and then executing the query using the generated query plan. The size of the compilation history 170 may be predefined, which defines the maximum number of unique sets of parameters and the corresponding compilation plus execution times that can be stored in the compilation history 170. In some examples, the parameterized query log repository 160 may store multiple compilation histories 170 associated with multiple different parameterized queries.

[0031] As an example, Figure 2Shows an example compilation history 210 associated with an example parameterized query 200 having the following query statement: SELECT * FROM Employees WHERE FirstName =? AND LastName =?. Here, the question marks are placeholders that can be replaced with different parameter values when the query runs. The compilation history 210 includes compilation plus execution times (e.g., tc1, tc2, tc3, and tc4, collectively represented as T com+exe ). In this example, the size of the compilation history 210 is four, and each parameter set includes two parameter values (e.g., two strings) corresponding to the two parameters ("FirstName" and "LastName") defined in the parameterized query 200.

[0032] Return Figure 1 , each unique parameterized query 110 can have one or more previously compiled query plans stored in the plan cache 130. For each cached query plan, a corresponding execution history 180 can be stored in the parameterized query log repository 160. The execution history 180 can contain the execution times for executing a parameterized query with multiple unique parameter sets by using the cached query plan (and thus, without involving compilation of the parameterized query). The multiple unique parameter sets used to generate the execution history 180 can be different (or the same) from the multiple unique parameter sets used to generate the compilation history 170. The size of the execution history 180 can be predefined, which defines the maximum number of unique parameter sets and corresponding execution times that can be stored in the execution history 180. If the parameterized query 110 has multiple cached query plans, the parameterized query 110 can have multiple execution histories 180, where each is associated with a corresponding cached query plan.

[0033] As an example, Figure 2 shows an example execution history 220 associated with the query plan for the parameterized query 200. The execution history 220 includes execution times (e.g., te1, te2, and te3, collectively represented as T exe ) corresponding to three unique parameter sets 222, 224, and 226, respectively. In this example, the size of the execution history 220 is three, and each parameter set includes two parameter values (e.g., two strings) corresponding to the two parameters ("FirstName" and "LastName") defined in the parameterized query 200. In the depicted example, although the parameter set 222 is the same as the parameter set 212, none of the other parameter sets in the execution history 220 are the same as any of the parameter sets in the compilation history 210.

[0034] As Figure 1 shown, the parameterized query plan manager 120 includes a similarity analyzer 122, a cache gain calculator 124, a query plan updater 126, and a history manager 128. For a parameterized query 110 having an input parameter set received, the parameterized query plan manager 120 is configured to determine whether to reuse one of the cached plans or to compile the parameterized query with the input parameter set from scratch based on the compilation history 170 associated with the parameterized query 110 and the execution history 180 associated with the query plan. The maintenance and / or update of the compilation history 170 and the execution history 180 may be performed by the history manager 128.

[0035] Specifically, the parameterized query plan manager 120 may estimate the compile plus execution time for compiling and executing the parameterized query 110 with the input parameter set based on the compilation history 170 associated with the parameterized query 110. The estimation of the compile plus execution time may be performed by the similarity analyzer 122, which is configured to identify the unique parameter set among the multiple unique parameter sets stored in the compilation history 170 that is most similar to the input parameter set. The basic assumption is that the compile plus execution time for a given parameter set will be similar to the compile plus execution time of the most similar parameter set in the compilation history 170. This is based on the principle that similar parameter sets may result in similar query plans and thus similar compile and execution times.

[0036] The parameterized query plan manager 120 may also estimate the execution time for executing the parameterized query 110 with the input parameter set by using the query plan based on the execution history 180 associated with the query plan. Similarly, the estimation of the execution time may also be performed by the similarity analyzer 122, which is configured to identify the unique parameter set among the multiple unique parameter sets stored in the execution history 180 that is most similar to the input parameter set. The basic assumption is that the execution time for a given parameter set will be similar to the execution time of the most similar parameter set in the execution history 180. This is based on the principle that similar parameter sets may result in similar query execution paths and thus similar execution times.

[0037] The parameterized query plan manager 120 can then determine a cache gain based at least in part on the estimated compile plus execution time and the estimated execution time. The cache gain indicates whether executing the parameterized query 110 by using a cached query plan can save time (and is thus preferred) compared to compiling the parameterized query 110 to generate a new query plan and then executing that new query plan (i.e., without using any cached query plan). A positive cache gain indicates that using the cached query plan may save time (and is thus more efficient) compared to compiling and executing a new query plan. Thus, the parameterized query plan manager 120 can make a decision to reuse the cached query plan to process the parameterized query 110 with the set of input parameters. On the other hand, a non-positive (e.g., zero or negative) cache gain indicates that using the cached query plan is unlikely to save time compared to compiling and executing a new query plan. In such a case, the parameterized query plan manager 120 can make a decision to compile and execute the parameterized query 110 with the set of input parameters. As described below, positive cache gain and non-positive cache gain are used as criteria (to determine whether to use a cached query plan or compile and execute a new query plan) for illustrative purposes, but it should be understood that in some cases, the criteria for cache gain can be defined differently depending on the computing environment. For example, even though the cache gain is non-positive, if the cache gain is within a predefined threshold, the system may consider using the cached query plan to save processing time due to unexpected overhead in compilation. The calculation of the cache gain can be performed, for example, by the cache gain calculator 124.

[0038] If the parameterized query 110 has only one cached query plan, the cache gain can be determined by calculating the difference between the estimated compile plus execution time (obtained based on the compilation history 170 associated with the parameterized query 110) and the estimated execution time (obtained based on the execution history 180 associated with the query plan). Figure 3 An example is shown that shows the estimated compile plus execution time 310 (denoted as T com+exe ) for a parameterized query and the estimated execution time 320 (denoted as T exe ) for executing the parameterized query by using the cached query plan. As shown, the cache gain can be calculated as (T com+exe – T exe ). In the depicted example, the cache gain is positive because T com+exe is greater than T exe . In other examples, the cache gain can be zero or negative.

[0039] Return to Figure 1, if the parameterized query 110 has multiple cached query plans, multiple estimated execution times can be obtained based on multiple execution histories 180 respectively associated with the multiple query plans. In this case, the minimum estimated execution time among the multiple estimated execution times can be determined, and the cache gain can be determined by calculating the difference between the estimated compile plus execution time and this minimum estimated execution time.

[0040] The query plan updater 126 is configured to initiate an update to the cached query plan. The update to the cached query plan can be triggered after the parameterized query 110 has been executed multiple times. The need for an update can be recognized when it is observed that the incoming parameterized query 110 frequently results in a non-positive cache gain, and thus the parameterized query 110 needs to be compiled and executed. This indicates that the current set of cached query plans is sub-optimal or outdated, and it is less likely to save time by reusing it compared to compiling and executing a new query plan. By updating the current set of cached query plans, the query plan updater 126 aims to create a more efficient set of query plans that can be reused more frequently for future incoming parameterized queries 110, thereby potentially improving efficiency and reducing resource usage over time.

[0041] In practice, the systems shown herein (such as the runtime parameterized query management system 100) can vary in complexity, having additional features, more complex components, etc. For example, additional features can exist within the parameterized query plan manager 120. Additional components can be included to implement security, redundancy, load balancing, report design, etc.

[0042] 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.).

[0043] The runtime parameterized query management system 100 and any other system described herein can be implemented in combination with any of the hardware components described herein (such as the computing systems described below (e.g., processing units, memories, etc.)). In any of the examples herein, query plans, compilation histories, execution histories, etc. can be stored in one or more computer-readable storage media or computer-readable storage devices. The techniques described herein can be generic to the details of the hardware or operating system and can be applied in any of a variety of environments to utilize the described features.

[0044] Example general method for implementing runtime parameterized query recompilation

[0045] Figure 4is a flowchart showing an example general method 400 for implementing runtime parameterized query recompilation and can be performed, for example, by a runtime parameterized query management system 100 of Figure 1 Execute.

[0046] At step 410, a parameterized query with a set of input parameters is received (e.g., by the parameterized query plan manager 120). As described above, the parameterized query can be associated with a compilation history (e.g., compilation history 170), which includes the compilation plus execution time for compiling and executing the parameterized query with multiple unique sets of parameters. The parameterized query has at least one query plan stored in the plan cache. The query plan can be associated with an execution history (e.g., execution history 180), which includes the execution time for executing the parameterized query with multiple unique sets of parameters by using the query plan. The multiple unique sets of parameters included in the execution history can be different from the multiple unique sets of parameters included in the compilation history.

[0047] At step 420, an estimated compilation plus execution time for compiling and executing the parameterized query with the input parameter set can be determined based on the compilation history associated with the parameterized query. Determining the estimated compilation plus execution time for compiling and executing the parameterized query with the input parameter set can include determining the one unique set of parameters among the multiple unique sets of parameters stored in the compilation history that is most similar to the input parameter set.

[0048] At step 430, an estimated execution time for executing the parameterized query with the input parameter set by using the query plan can be estimated based on the execution history associated with the query plan. Determining the estimated execution time for executing the parameterized query with the input parameter set by using the query plan can include determining the one unique set of parameters among the multiple unique sets of parameters stored in the execution history that is most similar to the input parameter set.

[0049] At step 440, a cache gain can be determined at least in part based on the estimated compilation plus execution time (obtained in step 420) and the estimated execution time (obtained in step 430). For example, when the parameterized query has a single cached query plan, the cache gain can be determined by calculating the estimated compilation plus execution time minus the estimated execution time. However, if the parameterized query has multiple cached query plans, each query plan has its own associated execution history. Thus, multiple estimated execution times (e.g., in step 430) can be obtained, each based on the execution history associated with a particular query plan. In such a scenario, the minimum estimated execution time is selected from the multiple estimates. The cache gain can then be calculated by subtracting the minimum estimated execution time from the estimated compilation plus execution time.

[0050] At step 450, a conditional check is performed to determine whether the cache gain obtained at step 440 is positive.

[0051] If the cache gain is found to be positive, method 400 proceeds to step 460 to execute a parameterized query with an input parameter set by using a query plan (assuming the parameterized query has a single cached query plan). If the parameterized query has multiple cached query plans, the query plan associated with the smallest estimated execution time is selected for use when executing the parameterized query.

[0052] On the other hand, if the cache gain is found to be non - positive (e.g., zero or negative), method 400 proceeds to step 470 to compile and execute a parameterized query with an input parameter set.

[0053] Method 400 and any other method described herein can be executed by computer - executable instructions stored in one or more computer - readable media (e.g., a storage device or other tangible medium) or stored in one or more computer - readable storage devices (e.g., causing a computing system to execute the method). Such a method can be executed in software, firmware, hardware, or a combination thereof. Such a method can be executed at least in part by a computing system (e.g., one or more computing devices).

[0054] While still implementing the technology, the actions shown can be described from an alternative perspective. For example, from a different angle, "receiving" can also be described as "sending".

[0055] Example methods for measuring similarity between parameter sets

[0056] As described above, the process of estimating the compile - plus - execution time for a parameterized query involves identifying the unique parameter set that is most similar to the input parameter set from the compilation history. Similarly, the estimated execution time for a query is determined by identifying the unique parameter set that most closely matches the input parameter set from the execution history. Methods for measuring the similarity between parameter sets can involve various techniques.

[0057] In some examples, distance - based measurements can be used to determine the unique parameter set among multiple unique parameter sets that is most similar to the input parameter set. Specifically, the distance between the input parameter set and multiple unique parameter sets (in the compilation history or execution history) can be measured. The unique parameter set with the smallest of the measured distances can be identified as the parameter set most similar to the input parameter set.

[0058] Depending on the data type of the parameter values included in the parameter set, different distance measurements can be used. For example, Euclidean and Manhattan distances can be used for continuous numerical data, Hamming distance can be used for categorical data, Levenshtein distance can be used for string data, and so on. For a parameter set with mixed data types, Gower distance can be used.

[0059] In some examples, when calculating the distance, different weights can be assigned to the different parameters in the parameter set based on their importance. In some examples, normalization (such as scaling to the range [0,1] or Z-score normalization) can be used to ensure that all parameters contribute equally to the distance calculation, regardless of their scale.

[0060] In some examples, the process of determining the unique parameter set that is most similar to the input parameter set involves calculating the selectivity value (or simply "selectivity") of the input parameter set and the selectivity values of multiple unique parameter sets (in the compilation history or execution history). Selectivity is a measure of how selective a predicate is. For example, selectivity can indicate how many rows in the total number of rows in a table the predicate matches. In some examples, the selectivity of a parameterized query can be determined based on statistics and / or histograms stored in a database that contain information about the distribution of values in a column or a set of columns. Selectivity can be used as a measure to quantify the degree of similarity between the input parameter set and the unique parameter sets in the compilation or execution history. Specifically, the unique parameter set with the selectivity value closest to the selectivity value of the input parameter set can be identified as the most similar parameter set.

[0061] In addition to or instead of the above methods, other similarity measurements (such as cosine similarity, Jaccard index, Pearson or Spearman correlation coefficients, etc.) can be employed to measure the similarity between parameter sets, and the choice of similarity measurement can be customized according to the specific characteristics of the parameter set. Furthermore, machine learning algorithms for pattern recognition, or even custom heuristic methods customized according to the specific characteristics of the parameter set, can be used to measure the similarity between different parameter sets.

[0062] Example method for updating the compilation history of a parameterized query

[0063] As described herein, when processing a parameterized query, the compilation history associated with the parameterized query can be updated dynamically. Figure 5 is a flowchart depicting an example method 500 for updating the compilation history of a parameterized query and can be, for example, byFigure 1 Implementation of the history manager 128.

[0064] Typically, method 500 can be executed during an initialization phase when the compilation history is not yet fully populated (e.g., the number of unique parameter sets and corresponding compilation plus execution times stored in the compilation history is less than the maximum size of the compilation history). Additionally, method 500 can be executed during runtime when the processing of a parameterized query triggers an update to the compilation history, as further described below with reference to Figure 8 Further described.

[0065] At step 510, a parameterized query with input parameter set P is compiled to generate a cached plan (CP), and then executed.

[0066] At step 520, the compilation plus execution time of step 510 (denoted as T com+exe ) is obtained, and the generated cached plan CP can be stored in the plan cache.

[0067] At step 530, a conditional check can be performed to determine whether the input parameter set P is stored in the compilation history (denoted as H com+exe ) associated with the parameterized query.

[0068] If the input parameter set P already exists in the compilation history, method 500 proceeds to step 540 to update the statistics for P. Specifically, the compilation plus execution time corresponding to the input parameter set P stored in the compilation history will be updated based on the newly obtained compilation plus execution time T com+exe . Such an update can be implemented in different ways. For example, the stored compilation plus execution time corresponding to the input parameter set P can be replaced with the newly obtained compilation plus execution time T com+exe . As another example, a weighted average of the stored and newly obtained values can be used to update the stored compilation plus execution time corresponding to the input parameter set P. This can be expressed as: T new = α · T com+exe + (1 - α) · T stored , where T new is the updated compilation plus execution time, T com+exe is the newly obtained compilation plus execution time, T stored is the stored compilation plus execution time, and α is a predefined weighting factor (0 ≤ α ≤ 1).

[0069] On the other hand, if the input parameter set P does not exist in the compilation history (i.e., P represents another unique parameter set), then method 500 proceeds to step 550. At this stage, it verifies whether the compilation history has reached its capacity. Specifically, it checks whether the count of unique parameter sets stored in the compilation history along with their corresponding compilation plus execution times has reached a predefined limit (denoted as k1), which represents the maximum size of the compilation history.

[0070] If the compilation history has not reached its capacity, then method 500 continues to step 570 to insert the input parameter set P and the obtained compilation plus execution time T com+exe paired into the compilation history. On the other hand, if the compilation history has reached its capacity, then method 500 first proceeds to step 560 to remove a unique parameter set from the compilation history and then continues to step 570 for insertion. In other words, the input parameter set P (with paired T com+exe ) replaces one of the previously stored parameter sets (and the corresponding compilation plus execution time). The selection of which stored parameter set to remove can be based on a predefined eviction policy (e.g., a random eviction policy, a least recently used (LRU) eviction policy, a least frequently used (LFU) eviction policy, a first in first out (FIFO) eviction policy, etc.).

[0071] Example method for updating the execution history of a query plan for a parameterized query

[0072] After using Figure 5 method 500 to update the compilation history, the execution history associated with the newly cached query plan CP can be updated. Figure 6 is a flowchart depicting an example method 600 for updating the execution history associated with a query plan and can be implemented, for example, by Figure 1 the history manager 128.

[0073] At step 610, the execution history associated with the cached query plan (denoted as H exe ) is first cleared or initialized to be empty.

[0074] At step 620, a condition check is performed to determine whether the execution history has reached its capacity. Specifically, it checks whether the count of unique parameter sets stored in the execution history along with their corresponding execution times (by using the cached query plan) has reached a predefined limit (denoted as k2), which represents the maximum size of the execution history.

[0075] If the execution history has reached its capacity, method 600 proceeds to step 630 to run multiple executions using the current set of cached query plans, as further explained below with reference to Figure 7 The number of executions to run (denoted as x) can be a predefined parameter. For example, x can be 2, 3, 4, or greater. In a particular case, x can be set to 1.

[0076] On the other hand, if the execution history has not reached its capacity, method 600 proceeds to step 640 to execute a parameterized query with the set of input parameters P by using the cached query plan. A corresponding execution time (denoted as T exe ) can be obtained.

[0077] Then at step 650, another conditional check is performed to determine whether the set of input parameters P is stored in the execution history (H exe ) associated with the cached query plan.

[0078] If the set of input parameters P already exists in the execution history, method 600 proceeds to step 660 to update the statistics for P. Specifically, the execution time corresponding to the set of input parameters P stored in the execution history can be updated based on the newly obtained execution time T exe . Such an update can be implemented in different ways. For example, the stored execution time corresponding to the set of input parameters P can be replaced by the newly obtained execution time T exe . As another example, the stored execution time corresponding to the set of input parameters P can be updated using a weighted average of the stored and newly obtained values. This can be expressed as: T new = β·T exe + (1 - β)·T stored , where T new is the updated execution time, T exe is the newly obtained execution time, T stored is the stored execution time, and β is a predefined weighting factor (0 ≤ β ≤ 1).

[0079] On the other hand, if the set of input parameters P does not exist in the execution history (i.e., P represents another unique set of parameters), method 600 proceeds to step 670 to insert the set of input parameters P paired with the obtained execution time T exe into the execution history.

[0080] After step 660 or step 670, method 600 can return to step 620 to check whether the execution history has reached its capacity. In other words, method 600 can continue to update the execution history until it reaches its capacity.

[0081] Example method for determining whether a cache query plan needs to be replaced

[0082] After the execution history associated with the cache query plan has been updated and reached its capacity, the current set of cache query plans can be used to execute incoming parameterized queries (with any valid set of input parameters) for multiple executions. For each execution, an evaluation is made to determine whether it is beneficial (e.g., time-saving) to use one of the cache query plans as compared to the alternative of compiling and executing a new query plan using the set of input parameters. Based on such an evaluation, a decision can be made as to whether the current set of cache query plans needs to be replaced.

[0083] Figure 7 is a flowchart of an example method 700 that runs and evaluates multiple executions of a parameterized query (with any valid set of input parameters) using the current set of cache query plans. Figure 8 is a flowchart of an example method 800 that determines whether the cache query plan needs to be updated. Method 700 and method 800 can be implemented, for example, by Figure 1 a query plan updater 126.

[0084] Refer to Figure 7 . Method 700 begins at step 710 by initializing three counters: the count of beneficial cases (represented as beneficialCases), the count of non-beneficial cases (represented as nonBeneficialCases), and the count of executions of the parameterized query using the current set of cache query plans (represented as numExecution) can all be set to 0.

[0085] At step 720, a conditional check is performed to determine whether the count of executions has reached a predefined limit x, where x is a positive integer (e.g., x can be 1, 2, 3, 4, or more). If so, method 700 can proceed to step 730 to determine whether to replace one of the cache query plans with a new query plan, which can be implemented by method 800 further described below. Figure 8 a query plan updater 126.

[0086] Otherwise (i.e., numExecution < x), method 700 proceeds to step 740 to estimate or calculate the cache gain. As described above, the cache gain can be calculated by: (a) estimating the compile plus execution time for compiling and executing a parameterized query with an input parameter set based on the compilation history associated with the parameterized query; (b) estimating the execution time for executing a parameterized query with an input parameter set by using the query plan based on the execution history associated with the query plan; and (c) calculating the difference between the estimated compile plus execution time and the estimated execution time. Additionally, as described above, if for a parameterized query, the cached query plan is the only plan, the cache gain is calculated by subtracting the estimated execution time from the estimated compile plus execution time. However, if a parameterized query has multiple cached query plans, each query plan has its own associated execution history. This results in multiple estimated execution times, each based on the execution history associated with a particular query plan. In such a case, the minimum estimated execution time is selected from the multiple estimates. Then the cache gain is calculated by subtracting the minimum estimated execution time from the estimated compile plus execution time.

[0087] Then, a conditional check can be performed at step 750 as to whether the cache gain is positive.

[0088] If the cache gain is positive, reusing the cached query plan is considered beneficial (e.g., time saving) compared to compiling and executing a new query plan. Thus, method 700 can proceed to step 760 to execute the parameterized query using the cached query plan (or if the parameterized query has multiple cached query plans, using the cached plan associated with the minimum estimated execution time). Then, at step 770, a counter for the beneficial case is incremented.

[0089] On the other hand, if the cache gain is negative (or zero), reusing the cached query plan is considered non - beneficial (e.g., not time saving) compared to compiling and executing a new query plan. Thus, method 700 can branch to step 780 to compile and execute the parameterized query. Then, at step 790, a counter for the non - beneficial case is incremented.

[0090] After step 770 or step 790, method 700 may proceed to step 795 to increment the execution count by one, and then return to step 720 to check whether the execution count has reached a predefined limit x. In other words, method 700 may continue to execute and evaluate parameterized queries using the set of query plans of the current cache until the number of executions has reached the predefined limit x, at which point a decision can be made as to whether the set of query plans of the current cache needs to be updated, as Figure 8 described in example method 800 of

[0091] At step 810, a conditional check is performed to determine whether (a) the ratio of beneficialCases to nonBeneficialCases is less than a predefined threshold, or (b) the count of unique parameter sets in the compilation history is less than the maximum size of the compilation history. If so, method 800 proceeds to step 830 to update the compilation history (including the compilation of parameterized queries and the query plans generated by caching) (e.g., by using the method 500 of Figure 5 . If not, method 800 proceeds to step 820 to run multiple executions using the query plans of the current cache (e.g., by using the method 700 of Figure 7 . In other words, when the compilation history is not full and when reusing the set of query plans of the current cache too frequently results in non-beneficial cases (e.g., not saving time), method 800 will compile and execute the parameterized query, cache the query plans generated (and replace the previously cached query plans, if any), and update the compilation history. Otherwise, if the conditional check at step 810 returns false, method 800 will continue to execute and evaluate parameterized queries using the set of query plans of the current cache.

[0092] The predefined threshold c controls the frequency at which the occurrence of non-beneficial cases is considered too frequent. In one particular case, the threshold c can be 1. In this case, the calculation of the above ratio is equivalent to comparing beneficialCases with nonBeneficialCases. In another particular case where x is set to 1, instead of calculating the ratio beneficialCases / nonBeneficialCases, the occurrence of a single non-beneficial case can trigger the replacement of the cached query plan and the update of the compilation history.

[0093] Example use case of runtime parameterized query recompilation where the parameterized query has a single query plan

[0094] Figure 9Schematically depicts a use case that further illustrates the runtime parameterized query recompilation techniques described herein. In this use case, it is assumed that the parameterized query has a single cached query plan (and thus the parameterized query has only one execution history associated with the query plan).

[0095] Figure 9 Depicts the processing of a parameterized query with a varying set of input parameters (e.g., parameter sets A, B, C, etc.) at runtime. In this example, both the maximum size (k1) of the compilation history and the maximum size (k2) of the execution history are set to 3, the predefined limit (x) for executing and evaluating the parameterized query using the cached query plan is set to 4, and the threshold c is set to 1. In this example, represents the compile - plus - execution time for compiling and executing the parameterized query with parameter set A, and represents the execution time for executing the parameterized query with parameter set A using the cached query plan. The ellipsis represents the processing of some additional queries.

[0096] Initially, the compilation history associated with the parameterized query stores the three compile - plus - execution times 905, 910, and 915 for compiling and executing the parameterized query with three different parameter sets A, B, and C respectively. Then, when processing the parameterized query with input parameter set D, the parameterized query is recompiled and executed again (e.g., Figure 8 the conditional check at step 810 returns false). Then the generated query plan is saved in the plan cache (and replaces the previously cached query plan), and the corresponding compile - plus - execution time 920 is stored in the compilation history. Since the compilation history is full, the previously stored parameter set A and its compile - plus - execution time 905 will be removed from the compilation history (e.g., according to a predefined eviction policy) (e.g., Figure 5 step 560). Thus, the compilation history is updated to include the three compile - plus - execution times 910, 915, and 920 corresponding to parameter sets B, C, and D respectively.

[0097] Then, an execution history associated with the cached query plan is created (e.g., using Figure 6 method 600). In the depicted example, the execution times 925, 930, and 935 for executing the parameterized query with three different parameter sets D, E, and F using the cached query plan (obtained with input parameter set D) are obtained and stored in the execution history.

[0098] Next, the parameterized query with input parameter set D is processed, and the cache gain can be estimated (e.g., Figure 7step 740). In this example, it is found that the input parameter set D is the most similar to the parameter set D stored in the compilation history (e.g., among parameter sets B, C, and D). Therefore, the estimated compilation plus execution time for compiling and executing the parameterized query with the input parameter set D is estimated to be 920( ). The input parameter set D is also found to be the most similar to the parameter D stored in the execution history (e.g., among parameter sets D, E, and F). Therefore, the estimated execution time for executing the parameterized query with the input parameter set D using the cached query plan is 925( ). Since in this example is greater than the estimated cache gain is positive. Therefore, the cached query plan will be used to execute the parameterized query with the input parameter set D, resulting in an execution time of 940, and the count of beneficial cases will be incremented( Figure 7 steps 760, 770).

[0099] Next, the parameterized query with the input parameter set G is processed, and the cache gain can be estimated (e.g., Figure 7 step 740). In this example, it is found that the input parameter set G is the most similar to the parameter set B stored in the compilation history (e.g., among parameter sets B, C, and D). Therefore, the estimated compilation plus execution time for compiling and executing the parameterized query with the input parameter set G is 910( ). Additionally, it is found that the input parameter set G is the most similar to the parameter E stored in the execution history (e.g., among parameter sets D, E, and F). Therefore, the estimated execution time for executing the parameterized query with the input parameter set G using the cached query plan is 930( ). Since in this example is less than the estimated cache gain is negative. Therefore, instead of using the cached query plan, the parameterized query with the input parameter set G will be compiled and executed, resulting in a compilation plus execution time of 945, and the count of non - beneficial cases will be incremented (e.g., Figure 7 steps 780, 790). Note that the cached query plan (obtained using the input parameter set D) remains unchanged.

[0100] Then, the parameterized queries with the input parameter sets H and I are processed. In both cases, it is assumed that the estimated cache gain is negative (similar to the parameterized query with the input parameter set G). Therefore, the parameterized queries with the input parameter sets H and I are compiled and executed, resulting in compilation plus execution times of 950 and 95, after which the count of non - beneficial cases becomes three.

[0101] Currently, the total number of executions of parameterized queries is four, including one beneficial case and three non-beneficial cases. Therefore, the query plan cached (obtained using the input parameter set D) needs to be replaced, and the compilation history (e.g., Figure 8 steps 810 and 830). In the depicted example, a parameterized query with input parameter set A is then received. Thus, after compiling and executing the parameterized query, the newly generated query plan (obtained using input parameter set A) will replace the previously cached query plan (e.g., obtained using input parameter set D). The compilation history will also be updated accordingly. In this example, the compile-plus-execute time 960 for compiling and executing the parameterized query with input parameter set A can be stored in the compilation history, and the existing entry in the compilation history (e.g., the compile-plus-execute time 920 corresponding to parameter set D) can be removed according to a predefined eviction policy.

[0102] For parameterized queries with other input parameter sets, a similar process can continue to determine whether to reuse the cached query plan or to compile and execute the parameterized query with a new query plan, and whether the cached query plan needs to be replaced.

[0103] Example use cases of runtime parameterized query recompilation where the parameterized query has multiple query plans

[0104] Figure 10 Another use case further illustrating the runtime parameterized query recompilation technique described herein is schematically depicted. In this use case, it is assumed that the parameterized query has three different cached query plans, and each cached query plan has an associated execution history.

[0105] Similarly, Figure 10 the handling of parameterized queries with varying input parameter sets (e.g., parameter sets A, B, C, etc.) at runtime is depicted. In this example, both the maximum size (k1) of the compilation history and the maximum size (k2) of the execution history are set to 3, the predefined limit (x) for executing and evaluating parameterized queries using the current set of cached query plans is set to 4, and the threshold c is set to 1. In this example, represents the compile-plus-execute time for compiling and executing the parameterized query with parameter set A, and represents the execution time for executing the parameterized query with parameter set A using the cached query plan P. The ellipsis represents the handling of some additional queries.

[0106] Initially, the compilation history includes compilation plus execution times 1005 and 1025 for compiling and executing parameterized queries with input parameter sets A and C, respectively. Using the query plan of the cache obtained by leveraging parameter set A, the corresponding execution history stores execution times 1010, 1015, and 1020 for executing parameterized queries with three different input parameter sets A, B, and C, respectively. Using another cache's query plan obtained by leveraging parameter set C, the corresponding execution history stores execution times 1030, 1035, and 1040 for executing parameterized queries with three different input parameter sets A, D, and B, respectively.

[0107] Then, a parameterized query with input parameter set B is received. Since the compilation history has not reached its full capacity, the parameterized query is compiled and executed, resulting in a query plan for another cache (obtained by leveraging parameter set B), and the compilation history is updated by adding the compilation plus execution time 1045 corresponding to parameter set B (e.g., Figure 8 steps 810 and 830).

[0108] Next, an execution history associated with the query plan of the new cache (obtained by leveraging parameter set B) is created (e.g., using Figure 6 method 600). In the depicted example, using the query plan of the cache (obtained by leveraging input parameter set B), execution times 1050, 1055, and 1060 for executing parameterized queries with three different parameter sets A, B, and C are obtained and stored in the execution history associated with the query plan of the new cache (obtained by leveraging parameter set B).

[0109] Next, a parameterized query with input parameter set A is received, and the cache gain can be estimated (e.g., Figure 7 step 740). In this example, it is found that input parameter set A is most similar to parameter set A stored in the compilation history (e.g., among parameter sets A, C, and B), so the estimated compilation plus execution time for compiling and executing the parameterized query with input parameter set A is 1005 ( ). Then, for each of the three execution histories, the estimated execution time by using the corresponding cache's query plan can be determined. For example, it is found that input parameter set A is most similar to parameter set A stored in the execution history associated with the query plan of the cache obtained by leveraging parameter set A (e.g., among parameter sets A, B, and C). Therefore, the first estimated execution time for executing the parameterized query with input parameter set A (using the query plan of the cache obtained by leveraging parameter set A) is 1010 ( )。The input parameter set A was also found to be most similar to the parameter set A stored in the execution history associated with the query plan of the cache obtained using the parameter set C (e.g., among the parameter sets A, D, and B). Thus, the second estimated execution time for executing the parameterized query with the input parameter set A (using the query plan of the cache obtained using the parameter set C) is 1030( )。Similarly, it was found that the input parameter set A is most similar to the parameter set A stored in the execution history associated with the query plan of the cache obtained using the parameter set B (e.g., among the parameter sets A, B, and C). Thus, the third estimated execution time for executing the parameterized query with the input parameter set A (using the query plan of the cache obtained using the parameter set B) is 1050( )。

[0110] Among the three estimated execution times 1010( ), 1030( ), and 1050( ), the first estimated execution time 1010( ) is the smallest. As described above, the estimated cache gain can then be calculated as which is positive in the depicted example. Thus, the query plan of the cache obtained using the parameter set A will be used to execute the parameterized query with the input parameter A, and the execution time is 1065. The counter for the beneficial cases will also be incremented.

[0111] Next, a parameterized query with the input parameter set E is received, and the cache gain is similarly estimated. In this example, it was found that the input parameter set E is most similar to the parameter set C stored in the compilation history (e.g., among the parameter sets A, C, and B), so the estimated compilation plus execution time for compiling and executing the parameterized query with the input parameter set E is 1025( )。Then, for each of the three execution histories, the estimated execution time by using the query plan of the corresponding cache can be estimated. For example, it was found that the input parameter set E is most similar to the parameter set C stored in the execution history associated with the query plan of the cache obtained using the parameter set A (e.g., among the parameter sets A, B, and C). Thus, the first estimated execution time for executing the parameterized query with the input parameter set E (using the query plan of the cache obtained using the parameter set A) is 1020( )。It is also found that the input parameter set E is most similar to the parameter set D stored in the execution history associated with the query plan of the cache obtained using the parameter set C (e.g., among the parameter sets A, D, and B). Therefore, the second estimated execution time for executing the parameterized query with the input parameter set E (using the query plan of the cache obtained using the parameter set C) is 1035( )。Similarly, it is found that the input parameter set E is most similar to the parameter set C stored in the execution history associated with the query plan of the cache obtained using the parameter set B (e.g., among the parameter sets A, B, and C). Therefore, the third estimated execution time for executing the parameterized query with the input parameter set E (using the query plan of the cache obtained using the parameter set B) is 1060( )。

[0112] Among the three estimated execution times 1020( ), 1035( ), and 1060( ), the second estimated execution time 1035( ) is the smallest. As described above, the estimated cache gain can then be calculated as which is negative in this example. Therefore, none of the cached query plans can be reused. Instead, the parameterized query with the input parameter E will be compiled and executed, and the compile-plus-execute time is 1070. The counter for non-beneficial cases will be incremented.

[0113] For parameterized queries with other input parameter sets, a similar process can be continued to determine whether to reuse any of the cached query plans or to compile and execute the parameterized query with a new query plan, and whether any of the cached query plans need to be replaced.

[0114] Example Advantages

[0115] Many advantages can be achieved via the techniques described herein.

[0116] First, the runtime parameterized query management system disclosed herein enables the automatic determination at runtime whether to reuse one of the cached plans for a parameterized query or to compile a new query plan. Specifically, a parameterized query can be executed using a cached query plan if and only if using the cached query plan is considered beneficial (e.g., results in a positive cache gain). Otherwise, the cached query plan will not be used to execute the parameterized query. Instead, the parameterized query will be compiled and executed with a new query plan. This innovative approach addresses the challenges posed by the dynamic nature of the database and the unpredictability of parameter values in query execution, and helps to maintain the performance of the system in the face of changing query patterns.

[0117] Second, the runtime parameterized query management system disclosed herein can automatically update the set of cached query plans during runtime. This update is triggered if the current set of cached query plans is frequently found to be non-beneficial for processing parameterized queries, leading to repeated compilations. This automatic update of inefficient query plans in the plan cache increases the probability of reusing the updated cached query plans, thereby potentially increasing efficiency over time and minimizing resource usage. Importantly, the decision to update the cached query plans can be made after multiple executions of the parameterized query. This approach allows for the collection of statistics (such as counts of beneficial and non-beneficial cases), which facilitates more robust decision-making. It can avoid the pitfall of prematurely updating the cached query plans based on a single or a few instances where the cached query plan may not be beneficial for parameterized queries with non-frequently occurring input parameter sets.

[0118] In addition, the techniques described herein provide a novel approach for accurately estimating the cache gain of parameterized queries based on estimates of the compile-plus-execute time for compiling and executing parameterized queries and the execution time for executing parameterized queries using cached query plans. This estimation is based on a similarity measurement between the input parameter set of the parameterized query and the parameter sets stored in the compilation history and one or more execution histories associated with the parameterized query. Importantly, each compilation history or execution history has a finite size sufficient to store a limited number of unique parameter sets (and their corresponding compile-plus-execute or execution times). By using similarity measurement (instead of relying on exact matches), the limited number of parameter sets in the compilation history or execution history can represent a large variety of input parameter sets that the parameterized query may encounter. Thus, the compilation history and execution history can occupy only a small amount of memory or storage space. The smaller size of the compilation history and execution history also makes the measurement and comparison of similarity metrics more efficient over time and requires fewer computational resources compared to saving all possible (or many) variants of the parameter sets. This is particularly important for database systems with highly limited environments (e.g., with limited storage space, CPU resources, etc.).

[0119] Example Computing System

[0120] Figure 11 An example of a suitable computing system 1100 in which the described innovation can be implemented is depicted. Computing system 1100 is not intended to impose any limitation on the scope of use or functionality of the present disclosure, as the innovation can be implemented in a variety of computing systems.

[0121] Reference Figure 11, the computing system 1100 includes one or more processing units 1110, 1115 and memories 1120, 1125. In Figure 11 it, the basic configuration 1130 is included within the dashed lines. The processing units 1110, 1115 can execute computer-executable instructions, such as those for implementing the features described in the examples herein (e.g., method 400, etc.). The processing unit can be a general-purpose central processing unit (CPU), a processor in an application-specific integrated circuit (ASIC), or any other type of processor. In a multiprocessing system, multiple processing units can execute computer-executable instructions to increase processing power. For example, Figure 11 shows the central processing unit 1110 and the graphics processing unit or coprocessing unit 1115. The tangible memories 1120, 1125 can be volatile memories (e.g., registers, caches, RAM) accessible by the processing units 1110, 1115, non-volatile memories (e.g., ROM, EEPROM, flash memory, etc.), or some combination of both. The memories 1120, 1125 can store software 1180 that implements one or more of the innovations described herein in a form suitable for computer-executable instructions to be executed by the processing units 1110, 1115.

[0122] The computing system 1100 can have additional features. For example, the computing system 1100 can include a storage device 1140, one or more input devices 1150, one or more output devices 1160, and one or more communication connections 1170, including communication connections, input devices, and output devices for interacting with a user. An interconnection mechanism (not shown), such as a bus, a controller, or a network, can interconnect the components of the computing system 1100. Generally, an operating system software (not shown) can provide an operating environment for other software executed in the computing system 1100 and coordinate the activities of the components of the computing system 1100.

[0123] The tangible storage device 1140 can be removable or non-removable and includes magnetic disks, tapes, or tape cartridges, CD-ROMs, DVDs, or any other medium that can be used to store information in a non-transitory manner and can be accessed within the computing system 1100. The storage device 1140 can store instructions for software that implements one or more of the innovations described herein.

[0124] The input device 1150 can be an input device, such as a keyboard, a mouse, a pen, or a trackball, a voice input device, a scanning device, a touch device (e.g., a touchpad, a display, etc.), or other devices that provide input to the computing system 1100. The output device 1160 can be a display, a printer, a speaker, a CD writer, or other devices that provide output from the computing system 1100.

[0125] The communication connection 1170 can enable communication with other computing entities 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 a modulated data signal. A modulated data signal is a signal that sets or changes one or more of its characteristics in a manner that encodes information in the signal. By way of example and not limitation, the communication medium can use electrical, optical, RF, or other carriers.

[0126] The innovation can be described in the context of computer-executable instructions, such as those included in program modules, being executed in a computing system on a target real or virtual processor (e.g., ultimately being executed on one or more hardware processors). Generally, program modules or components can include routines, programs, libraries, objects, classes, components, data structures, etc. that perform specific tasks or implement specific abstract data types. In various embodiments, the functionality of program modules can be combined or split among program modules as needed. The computer-executable instructions for program modules can be executed within a local or distributed computing system.

[0127] For ease of presentation, the detailed description uses terms such as "determine" and "use" to describe computer operations in a computing system. These terms are for a high-level description of operations performed by a computer and should not be confused with actions performed by a human. The actual computer operations corresponding to these terms vary according to the implementation.

[0128] Computer-readable medium

[0129] Any computer-readable medium herein can be non-transitory (e.g., volatile memory such as DRAM or SRAM, non-volatile memory such as magnetic storage devices, optical storage devices, etc.) and / or tangible. Any storage action described herein can be implemented by being stored 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 an implementation) can be stored in one or more computer-readable media (e.g., computer-readable storage media or other tangible media). Computer-readable media can be limited to embodiments that do not consist of signals.

[0130] Any method described herein can 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 devices, optical storage devices, etc.). Such instructions can cause a computing device to execute the method. The techniques described herein can be implemented in a variety of programming languages.

[0131] Example cloud computing environment

[0132] Figure 12 Depicts an example cloud computing environment 1200 in which the described techniques (including, for example, system 100 and other systems herein) can be implemented. The cloud computing environment 1200 can include cloud computing services 1210. The cloud computing services 1210 can include various types of cloud computing resources, such as computer servers, data repositories, network resources, etc. The cloud computing services 1210 can be centrally located (e.g., provided by an enterprise or organization's data center) or distributed (e.g., provided by various computing resources located at different locations, such as different data centers and / or located in different cities or countries).

[0133] The cloud computing services 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, the computing devices (e.g., 1220, 1222, and 1224) can be computers (e.g., desktop or laptop computers), mobile devices (e.g., tablet computers or smart phones), or other types of computing devices. For example, the computing devices (e.g., 1220, 1222, and 1224) can utilize the cloud computing services 1210 to perform computing operations (e.g., data processing, data storage, etc.).

[0134] In practice, cloud-based, on-premises, or hybrid scenarios can be supported.

[0135] Example embodiments

[0136] Although, for convenience of presentation, the operations of some of the methods disclosed herein are described in a particular, sequential order, this description method encompasses rearrangements unless the specific language set forth herein requires a particular ordering. For example, the operations described sequentially can, in some cases, be rearranged or performed simultaneously.

[0137] As used in this application and the claims, the singular forms "a", "an", and "the" include plural forms unless the context clearly dictates otherwise. Additionally, the term "comprising" means "including". Further, "and / or" means "and" or "or", as well as "and" and "or".

[0138] In any of the examples described herein, an operation performed at runtime means that the operation can be completed in real time or with negligible processing latency (e.g., the operation can be completed within 1 second, etc.).

[0139] Example embodiment

[0140] Any of the following example embodiments can be implemented.

[0141] Embodiment 1. A computer-implemented method, comprising: receiving a parameterized query having a set of input parameters for the parameterized query, wherein the parameterized query has a query plan stored in a plan cache, wherein the parameterized query is associated with a compilation history that includes a compilation plus execution time for compiling and executing the parameterized query with a first plurality of unique parameter sets, wherein the query plan is associated with an execution history that includes an execution time for executing the parameterized query with a second plurality of unique parameter sets by using the query plan; determining, based on the compilation history associated with the parameterized query, an estimated compilation plus execution time for compiling and executing the parameterized query with the input parameter set; determining, based on the execution history associated with the query plan, an estimated execution time for executing the parameterized query with the input parameter set by using the query plan; determining a cache gain at least in part based on the estimated compilation plus execution time and the estimated execution time; in response to finding that the cache gain is positive, executing the parameterized query with the input parameter set by using the query plan; and in response to finding that the cache gain is not positive, compiling and executing the parameterized query with the input parameter set.

[0142] Embodiment 2. The method according to Embodiment 1, further comprising: incrementing a first counter in response to finding that the cache gain is positive; incrementing a second counter in response to finding that the cache gain is not positive; determining the number of executions of the parameterized query since the last evaluation of whether the query plan in the plan cache needs to be updated; in response to finding that the number of executions of the parameterized query since the last evaluation has reached a predetermined number, determining the ratio of the first counter to the second counter; and in response to finding that the ratio of the first counter to the second counter is lower than a predetermined threshold, updating the query plan in the plan cache with a new query plan generated by compiling the parameterized query with the input parameter set.

[0143] Example 3. The method according to Example 2 further includes updating the compilation history associated with the parameterized query after updating the query plan in the plan cache, wherein updating the compilation history includes: determining a new compilation plus execution time for compiling and executing a parameterized query with an input parameter set; determining whether the input parameter set is one of a first plurality of unique parameter sets stored in the compilation history; in response to finding that the input parameter set is one of the first plurality of unique parameter sets stored in the compilation history, updating the compilation plus execution time in the compilation history for compiling and executing the parameterized query with the input parameter set at least in part based on the new compilation plus execution time; and in response to finding that the input parameter set is not one of the first plurality of unique parameter sets stored in the compilation history, inserting the new compilation plus execution time for compiling and executing the parameterized query with the input parameter set into the compilation history.

[0144] Example 4. The method according to Example 3, wherein inserting the new compilation plus execution time for compiling and executing the parameterized query with the input parameter set into the compilation history includes: determining a count of the first plurality of unique parameter sets stored in the compilation history; and in response to finding that the count of the first plurality of unique parameter sets stored in the compilation history is equal to a predefined size of the compilation history, removing from the compilation history the compilation plus execution time for compiling and executing a parameterized query with one of the first plurality of unique parameter sets.

[0145] Example 5. The method according to any one of Examples 3 to 4 further includes updating the execution history associated with the query plan after updating the compilation history associated with the parameterized query, wherein updating the execution history includes, in an iterative operation: using the updated query plan in the plan cache to execute a parameterized query with a new parameter set for the parameterized query; determining a new execution time for executing the parameterized query with the new parameter set for the parameterized query; determining whether the new parameter set is one of a second plurality of unique parameter sets stored in the execution history; in response to finding that the new parameter set is one of the second plurality of unique parameter sets stored in the execution history, updating the execution time in the execution history for executing the parameterized query with the new parameter set at least in part based on the new execution time; and in response to finding that the new parameter set is not one of the second plurality of unique parameter sets stored in the execution history, inserting the new execution time for executing the parameterized query with the input parameter set into the execution history, wherein the iterative operation continues until the count of the second plurality of unique parameter sets stored in the execution history is equal to a predefined size of the execution history.

[0146] Example 6. The method according to any one of Examples 1 to 5, wherein determining the estimated compile plus execution time for compiling and executing a parameterized query having an input parameter set includes: determining the one unique parameter set among the first plurality of unique parameter sets stored in the compilation history that is most similar to the input parameter set; and wherein determining the estimated execution time for executing a parameterized query having an input parameter set by using a query plan includes: determining the one unique parameter set among the second plurality of unique parameter sets stored in the execution history that is most similar to the input parameter set.

[0147] Example 7. The method according to Example 6, wherein determining the one unique parameter set among the first plurality of unique parameter sets or the second plurality of unique parameter sets that is most similar to the input parameter set includes: measuring the distance between the input parameter set and the first plurality of unique parameter sets or the second plurality of unique parameter sets; and identifying the smallest of the measured distances.

[0148] Example 8. The method according to any one of Examples 6 to 7, wherein determining the one unique parameter set among the first plurality of unique parameter sets or the second plurality of unique parameter sets that is most similar to the input parameter set includes: determining the selectivity value of the input parameter set and the selectivity values of the first plurality of unique parameter sets or the second plurality of unique parameter sets; and identifying the selectivity value of the one unique parameter set among the first plurality of unique parameter sets or the second plurality of unique parameter sets whose selectivity value is closest to the selectivity value of the input parameter set.

[0149] Example 9. The method according to any one of Examples 1 to 8, wherein the query plan is one of a plurality of query plans for a parameterized query stored in a plan cache, wherein the execution history is one of a plurality of execution histories respectively associated with the plurality of query plans, wherein the estimated execution time is one of a plurality of estimated execution times respectively determined for the plurality of query plans, and wherein the cache gain is determined based on comparing the estimated compile plus execution time with the plurality of estimated execution times.

[0150] Example 10. The method according to Example 9, wherein determining the cache gain includes: determining the smallest of the plurality of estimated execution times; and determining the difference between the estimated compile plus execution time and the smallest of the estimated execution times.

[0151] Example 11. A computer system includes: a memory; one or more hardware processors coupled to the memory; and one or more computer-readable storage media storing instructions that, when loaded into the memory, cause the one or more hardware processors to perform operations including: receiving a parameterized query having a set of input parameters for the parameterized query, wherein the parameterized query has a query plan stored in a plan cache, wherein the parameterized query is associated with a compilation history including a compilation plus execution time for compiling and executing the parameterized query with a first plurality of unique parameter sets, and wherein the query plan is associated with an execution history including an execution time for executing the parameterized query with a second plurality of unique parameter sets using the query plan; determining an estimated compilation plus execution time for compiling and executing the parameterized query with the input parameter set based on the compilation history associated with the parameterized query; determining an estimated execution time for executing the parameterized query with the input parameter set using the query plan based on the execution history associated with the query plan; determining a cache gain at least in part based on the estimated compilation plus execution time and the estimated execution time; in response to finding that the cache gain is positive, executing the parameterized query with the input parameter set using the query plan; and in response to finding that the cache gain is not positive, compiling and executing the parameterized query with the input parameter set.

[0152] Example 12. The system of Example 11, wherein the operations further include: incrementing a first counter in response to finding that the cache gain is positive; incrementing a second counter in response to finding that the cache gain is not positive; determining a number of executions of the parameterized query since a last evaluation of whether a query plan in the plan cache needs to be updated; in response to finding that the number of executions of the parameterized query since the last evaluation has reached a predetermined number, determining a ratio of the first counter to the second counter; and in response to finding that the ratio of the first counter to the second counter is below a predetermined threshold, updating the query plan in the plan cache with a new query plan generated by compiling the parameterized query with the input parameter set.

[0153] Example 13. The system according to Example 12, wherein the operation further includes updating the compilation history associated with the parameterized query after updating the query plan in the plan cache, wherein updating the compilation history includes: determining a new compilation plus execution time for compiling and executing the parameterized query with the input parameter set; determining whether the input parameter set is one of the first plurality of unique parameter sets stored in the compilation history; in response to finding that the input parameter set is one of the first plurality of unique parameter sets stored in the compilation history, updating, at least in part based on the new compilation plus execution time for compiling and executing the parameterized query with the input parameter set, the compilation plus execution time in the compilation history for compiling and executing the parameterized query with the input parameter set; and in response to finding that the input parameter set is not one of the first plurality of unique parameter sets stored in the compilation history, inserting the new compilation plus execution time for compiling and executing the parameterized query with the input parameter set into the compilation history.

[0154] Example 14. The system according to Example 13, wherein inserting the new compilation plus execution time for compiling and executing the parameterized query with the input parameter set into the compilation history includes: determining the count of the first plurality of unique parameter sets stored in the compilation history; and in response to finding that the count of the first plurality of unique parameter sets stored in the compilation history is equal to the predefined size of the compilation history, removing from the compilation history the compilation plus execution time for compiling and executing the parameterized query with one of the first plurality of unique parameter sets.

[0155] Example 15. The system according to any one of Examples 13 to 14, wherein the operation further includes updating the execution history associated with the query plan after updating the compilation history associated with the parameterized query, wherein updating the execution history includes, in an iterative operation: executing the parameterized query with the new parameter set for the parameterized query using the updated query plan in the plan cache; determining a new execution time for executing the parameterized query with the new parameter set for the parameterized query; determining whether the new parameter set is one of the second plurality of unique parameter sets stored in the execution history; in response to finding that the new parameter set is one of the second plurality of unique parameter sets stored in the execution history, updating, at least in part based on the new execution time for executing the parameterized query with the new parameter set, the execution time in the execution history for executing the parameterized query with the new parameter set; and in response to finding that the new parameter set is not one of the second plurality of unique parameter sets stored in the execution history, inserting the new execution time for executing the parameterized query with the input parameter set into the execution history, wherein the iterative operation continues until the count of the second plurality of unique parameter sets stored in the execution history is equal to the predefined size of the execution history.

[0156] Example 16. The system according to any one of Examples 11 to 15, wherein determining the estimated compilation plus execution time for compiling and executing a parameterized query with an input parameter set includes: determining the one unique parameter set among the first plurality of unique parameter sets stored in the compilation history that is most similar to the input parameter set; and wherein determining the estimated execution time for executing the parameterized query with the input parameter set by using a query plan includes: determining the one unique parameter set among the second plurality of unique parameter sets stored in the execution history that is most similar to the input parameter set.

[0157] Example 17. The system according to Example 16, wherein determining the one unique parameter set among the first plurality of unique parameter sets or the second plurality of unique parameter sets that is most similar to the input parameter set includes: measuring the distance between the input parameter set and the first plurality of unique parameter sets or the second plurality of unique parameter sets; and identifying the smallest distance among the measured distances.

[0158] Example 18. The system according to any one of Examples 16 to 17, wherein determining the one unique parameter set among the first plurality of unique parameter sets or the second plurality of unique parameter sets that is most similar to the input parameter set includes: determining the selectivity value of the input parameter set and the selectivity values of the first plurality of unique parameter sets or the second plurality of unique parameter sets; and identifying the selectivity value of the one unique parameter set among the first plurality of unique parameter sets or the second plurality of unique parameter sets whose selectivity value is closest to the selectivity value of the input parameter set.

[0159] Example 19. The system according to any one of Examples 11 to 18, wherein the query plan is one of a plurality of query plans for the parameterized query stored in the plan cache, wherein the execution history is one of a plurality of execution histories respectively associated with the plurality of query plans, wherein the estimated execution time is one of a plurality of estimated execution times respectively determined for the plurality of query plans, and wherein determining the cache gain includes: determining the smallest estimated execution time among the plurality of estimated execution times; and determining the difference between the estimated compilation plus execution time and the smallest estimated execution time.

[0160] Example 20. One or more non-transitory computer-readable media encoded with computer-executable instructions that, when executed by one or more processors, cause the one or more processors to perform a method that includes: receiving a parameterized query having a set of input parameters for the parameterized query, where the parameterized query has a query plan stored in a plan cache, where the parameterized query is associated with a compilation history that includes a compile-plus-execute time for compiling and executing the parameterized query with a first plurality of unique parameter sets, where the query plan is associated with an execution history that includes an execution time for executing the parameterized query with a second plurality of unique parameter sets by using the query plan; determining an estimated compile-plus-execute time for compiling and executing the parameterized query with the input parameter set based on the compilation history associated with the parameterized query; determining an estimated execution time for executing the parameterized query with the input parameter set by using the query plan based on the execution history associated with the query plan; determining a cache gain, where determining the cache gain includes calculating a difference between the estimated compile-plus-execute time and the estimated execution time; in response to finding that the cache gain is positive, executing the parameterized query with the input parameter set by using the query plan; and in response to finding that the cache gain is not positive, compiling and executing the parameterized query with the input parameter set.

[0161] Example alternative

[0162] The techniques according to any example can be combined with the techniques described in any one or more other examples. Given that the principles of the disclosed techniques can be applied to many possible embodiments thereof, it should be recognized that the illustrated embodiments are examples of the disclosed techniques and should not be regarded as limiting the scope of the disclosed techniques. Instead, the scope of the disclosed techniques includes what is covered by the scope and spirit of the appended claims.

Claims

1. A computer-implemented method, comprising: Receiving a parameterized query having a set of input parameters for the parameterized query, wherein the parameterized query has a query plan stored in a plan cache, wherein the parameterized query is associated with a compilation history including a compilation plus execution time for compiling and executing the parameterized query with a first plurality of unique parameter sets, and wherein the query plan is associated with an execution history including an execution time for executing the parameterized query with a second plurality of unique parameter sets by using the query plan; Determining an estimated compilation plus execution time for compiling and executing the parameterized query with the input parameter set based on the compilation history associated with the parameterized query; Determining an estimated execution time for executing the parameterized query with the input parameter set by using the query plan based on the execution history associated with the query plan; Determining a cache gain at least in part based on the estimated compilation plus execution time and the estimated execution time; Responsive to finding that the cache gain is positive, executing the parameterized query with the input parameter set by using the query plan; and Responsive to finding that the cache gain is not positive, compiling and executing the parameterized query with the input parameter set.

2. The method according to claim 1, further comprising: Incrementing a first counter responsive to finding that the cache gain is positive; Incrementing a second counter responsive to finding that the cache gain is not positive; Determining the number of executions of the parameterized query since the last evaluation of whether the query plan in the plan cache needs to be updated; Determining a ratio of the first counter to the second counter responsive to finding that the number of executions of the parameterized query since the last evaluation has reached a predetermined number; Responsive to finding that the ratio of the first counter to the second counter is below a predetermined threshold, updating the query plan in the plan cache with a new query plan generated by compiling the parameterized query with the input parameter set.

3. The method according to claim 2 further comprises updating a compilation history associated with a parameterized query after updating a query plan in a plan cache, wherein, Updating the compilation history includes: Determining a new compilation plus execution time for compiling and executing the parameterized query with the input parameter set; Determining whether the input parameter set is one of the first plurality of unique parameter sets stored in the compilation history; Responsive to finding that the input parameter set is one of the first plurality of unique parameter sets stored in the compilation history, updating the compilation plus execution time in the compilation history for compiling and executing the parameterized query with the input parameter set at least in part based on the new compilation plus execution time for compiling and executing the parameterized query with the input parameter set; and Responsive to finding that the input parameter set is not one of the first plurality of unique parameter sets stored in the compilation history, inserting the new compilation plus execution time for compiling and executing the parameterized query with the input parameter set into the compilation history.

4. The method according to claim 3, wherein, Inserting the new compilation plus execution time for compiling and executing the parameterized query with the input parameter set into the compilation history includes: Determining a count of the first plurality of unique parameter sets stored in the compilation history; and In response to finding that the count of the first plurality of unique parameter sets stored in the compilation history is equal to a predefined size of the compilation history, remove from the compilation history the compilation plus execution time for compiling and executing a parameterized query having one of the first plurality of unique parameter sets.

5. The method according to claim 3, further comprising updating an execution history associated with a query plan after updating a compilation history associated with a parameterized query, wherein, Updating the execution history includes, in an iterative operation: Executing a parameterized query having a new parameter set for the parameterized query using the updated query plan in the plan cache; Determining a new execution time for executing the parameterized query having the new parameter set for the parameterized query; Determining whether the new parameter set is one of a second plurality of unique parameter sets stored in the execution history; In response to finding that the new parameter set is one of a second plurality of unique parameter sets stored in the execution history, update, at least in part based on the new execution time for executing the parameterized query having the new parameter set, the execution time in the execution history for executing the parameterized query having the new parameter set; And In response to finding that the new parameter set is not one of a second plurality of unique parameter sets stored in the execution history, insert the new execution time for executing the parameterized query having the input parameter set into the execution history, where the iterative operation continues until the count of the second plurality of unique parameter sets stored in the execution history is equal to a predefined size of the execution history.

6. The method according to claim 1, wherein Determining the estimated compilation plus execution time for compiling and executing a parameterized query having an input parameter set includes: Determining the one unique parameter set among the first plurality of unique parameter sets stored in the compilation history that is most similar to the input parameter set; and where determining the estimated execution time for executing the parameterized query having the input parameter set by using the query plan includes: Determining the one unique parameter set among the second plurality of unique parameter sets stored in the execution history that is most similar to the input parameter set.

7. The method according to claim 6, wherein Determining the one unique parameter set among the first plurality of unique parameter sets or the second plurality of unique parameter sets that is most similar to the input parameter set includes: Measuring the distance between the input parameter set and the first plurality of unique parameter sets or the second plurality of unique parameter sets; and Identifying the smallest distance among the measured distances.

8. The method according to claim 6, wherein Determining the one unique parameter set among the first plurality of unique parameter sets or the second plurality of unique parameter sets that is most similar to the input parameter set includes: Determining the selectivity value of the input parameter set and the selectivity values of the first plurality of unique parameter sets or the second plurality of unique parameter sets; and Identifying the selectivity value of the one unique parameter set among the first plurality of unique parameter sets or the second plurality of unique parameter sets whose selectivity value is closest to the selectivity value of the input parameter set.

9. The method according to claim 1, wherein The query plan is one of a plurality of query plans for the parameterized query stored in the plan cache, where the execution history is one of a plurality of execution histories respectively associated with the plurality of query plans, where the estimated execution time is one of a plurality of estimated execution times respectively determined for the plurality of query plans, and where the cache gain is determined based on comparing the estimated compilation plus execution time with the plurality of estimated execution times.

10. The method according to claim 9, wherein, Determining the cache gain includes: Determine the smallest estimated execution time among the multiple estimated execution times; and Determine the difference between the estimated compile plus execution time and the smallest estimated execution time.

11. A computing system, comprising: A memory; One or more hardware processors, coupled to the memory; And One or more computer-readable storage media storing instructions that, when loaded into the memory, cause the one or more hardware processors to perform operations, the operations including: Receiving a parameterized query having a set of input parameters for the parameterized query, wherein the parameterized query has a query plan stored in a plan cache, wherein the parameterized query is associated with a compilation history including a compile plus execution time for compiling and executing parameterized queries having a first plurality of unique parameter sets, and wherein the query plan is associated with an execution history including an execution time for executing parameterized queries having a second plurality of unique parameter sets by using the query plan; Determine an estimated compile plus execution time for compiling and executing the parameterized query having the input parameter set based on the compilation history associated with the parameterized query; Determine an estimated execution time for executing the parameterized query having the input parameter set by using the query plan based on the execution history associated with the query plan; Determine a cache gain at least in part based on the estimated compile plus execution time and the estimated execution time; In response to finding that the cache gain is positive, execute the parameterized query having the input parameter set by using the query plan; and In response to finding that the cache gain is not positive, compile and execute the parameterized query having the input parameter set.

12. The system according to claim 11, wherein, The operations further include: Increment a first counter in response to finding that the cache gain is positive; Increment a second counter in response to finding that the cache gain is not positive; Determine the number of executions of the parameterized query since the last evaluation of whether the query plan in the plan cache needs to be updated; In response to finding that the number of executions of the parameterized query since the last evaluation has reached a predetermined number, determine the ratio of the first counter to the second counter; In response to finding that the ratio of the first counter to the second counter is below a predetermined threshold, update the query plan in the plan cache with a new query plan generated by compiling the parameterized query having the input parameter set.

13. The system according to claim 12, wherein, The operations further include updating the compilation history associated with the parameterized query after updating the query plan in the plan cache, wherein updating the compilation history includes: Determine a new compile plus execution time for compiling and executing the parameterized query having the input parameter set; Determine whether the input parameter set is one of the first plurality of unique parameter sets stored in the compilation history; In response to finding that the input parameter set is one of the first plurality of unique parameter sets stored in the compilation history, update the compile plus execution time in the compilation history for compiling and executing the parameterized query having the input parameter set at least in part based on the new compile plus execution time for compiling and executing the parameterized query having the input parameter set; and In response to discovering that the input parameter set is not one of the first plurality of unique parameter sets stored in the compilation history, insert a new compilation plus execution time for compiling and executing a parameterized query with the input parameter set into the compilation history.

14. The system according to claim 13, wherein, Inserting a new compilation plus execution time for compiling and executing a parameterized query with the input parameter set into the compilation history includes: Determining a count of the first plurality of unique parameter sets stored in the compilation history; and In response to discovering that the count of the first plurality of unique parameter sets stored in the compilation history is equal to a predefined size of the compilation history, remove from the compilation history a compilation plus execution time for compiling and executing a parameterized query with one of the first plurality of unique parameter sets.

15. The system according to claim 13, wherein The operation further includes updating an execution history associated with a query plan after updating the compilation history associated with the parameterized query, wherein updating the execution history includes, in an iterative operation: Executing a parameterized query with a new parameter set for the parameterized query using the updated query plan in a plan cache; Determining a new execution time for executing the parameterized query with the new parameter set for the parameterized query; Determining whether the new parameter set is one of a second plurality of unique parameter sets stored in the execution history; In response to discovering that the new parameter set is one of the second plurality of unique parameter sets stored in the execution history, updating, at least in part based on the new execution time for executing the parameterized query with the new parameter set, an execution time in the execution history for executing the parameterized query with the new parameter set; and In response to discovering that the new parameter set is not one of the second plurality of unique parameter sets stored in the execution history, insert the new execution time for executing the parameterized query with the input parameter set into the execution history, wherein the iterative operation continues until the count of the second plurality of unique parameter sets stored in the execution history is equal to a predefined size of the execution history.

16. The system according to claim 11, wherein, Determining an estimated compilation plus execution time for compiling and executing a parameterized query with the input parameter set includes: Determining a unique parameter set among the first plurality of unique parameter sets stored in the compilation history that is most similar to the input parameter set; and wherein determining an estimated execution time for executing the parameterized query with the input parameter set by using a query plan includes: Determining a unique parameter set among the second plurality of unique parameter sets stored in the execution history that is most similar to the input parameter set.

17. The system according to claim 16, wherein, Determining a unique parameter set among the first plurality of unique parameter sets or the second plurality of unique parameter sets that is most similar to the input parameter set includes: Measuring a distance between the input parameter set and the first plurality of unique parameter sets or the second plurality of unique parameter sets; and Identifying the smallest distance among the measured distances.

18. The system according to claim 16, wherein Determining a unique parameter set among the first plurality of unique parameter sets or the second plurality of unique parameter sets that is most similar to the input parameter set includes: Determining a selectivity value of the input parameter set and selectivity values of the first plurality of unique parameter sets or the second plurality of unique parameter sets; and Identify a selectivity value of a unique parameter set among a first plurality of unique parameter sets or a second plurality of unique parameter sets that is closest to an input parameter set.

19. The system according to claim 11, wherein, A query plan is one of a plurality of query plans for parameterized queries stored in a plan cache, where an execution history is one of a plurality of execution histories respectively associated with the plurality of query plans, where an estimated execution time is one of a plurality of estimated execution times respectively determined for the plurality of query plans, and where determining a cache gain includes: Determining a smallest estimated execution time among the plurality of estimated execution times; and Determining a difference between the estimated compile plus execution time and the smallest estimated execution time.

20. One or more non-transitory computer-readable media encoded with computer-executable instructions that cause one or more processors to perform a method, the method including: Receiving a parameterized query having an input parameter set for the parameterized query, where the parameterized query has a query plan stored in a plan cache, where the parameterized query is associated with a compilation history that includes a compile plus execution time for compiling and executing a parameterized query having a first plurality of unique parameter sets, and where the query plan is associated with an execution history that includes an execution time for executing a parameterized query having a second plurality of unique parameter sets by using the query plan; Determining an estimated compile plus execution time for compiling and executing a parameterized query having the input parameter set based on the compilation history associated with the parameterized query; Determining an estimated execution time for executing a parameterized query having the input parameter set by using the query plan based on the execution history associated with the query plan; Determining a cache gain, where determining the cache gain includes calculating a difference between the estimated compile plus execution time and the estimated execution time; In response to finding that the cache gain is positive, executing the parameterized query having the input parameter set by using the query plan; and In response to finding that the cache gain is not positive, compiling and executing the parameterized query having the input parameter set.

Images