Automatic recompilation for parameterized query

Abstract

To provide a system and method for runtime parameterized query management.SOLUTION: A method includes: receiving a parameterized query with an input parameter set; determining an estimated compilation-plus-execution time for compiling and executing the parameterized query with the input parameter set; determining an estimated execution time for executing the parameterized query with the input parameter set by using a query plan stored in a plan cache; determining a cache gain based at least in part on the estimated compilation-plus-execution time and the estimated execution time; and, responsive to finding that the cache gain is positive, executing the parameterized query with the input parameter set by using the query plan, otherwise, compiling and executing the parameterized query with the input parameter set.SELECTED DRAWING: Figure 4

Description

[Background technology]

[0001] A query plan (also called a "query execution plan") is a sequence of steps that a database management system (DBMS) performs to complete a query. When a query is executed in a DBMS for the first time, the query may be compiled by a query optimizer to generate a corresponding query plan, which may be stored in a memory called a query plan cache, or simply, a plan cache. A query plan stored in a plan cache may also be called a cached query plan, or simply, a cached plan. Thus, when the same query is executed again, the DBMS does not need to regenerate the query plan. Instead, the DBMS may reuse the cached query plan stored in the query plan cache, thereby improving 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 for executing a query. In this context, parameterized queries, which use placeholders for parameters with values supplied at query runtime, can be beneficial. This allows the DBMS to cache the query plan, thereby eliminating the need for repeated query compilation and improving performance. However, query optimization for parameterized queries may not always provide the expected results. Therefore, there is room for improvement for handling parameterized queries. [Prior art documents] [Non-patent literature]

[0003] [Non-Patent Document 1] Eskandar, “Exploring Common Distance Measures for Machine Learning and Data Science: A Comparative Analysis,” <URL: https: / / medium.com / @eskandar.sahel / exploring-common-distance-measures-formachine-learning-and-data-science-a-comparative-analysis-ea0216c93ba3> , pp. 18, [Accessed: December 11, 2023] [Non-patent document 2] Anand, “Gower's Distance,” Analytics Vidhya,<URL: https: / / medium.com / analyticsvidhya / gowers-distance-899f9c4bd553> , page 15, June 17, 202 [Brief explanation of the drawings]

[0004] [Figure 1] FIG. 1 is a general block diagram of an exemplary runtime parameterized query management system. [Figure 2] FIG. 1 illustrates an exemplary compilation history and an exemplary execution history for a parameterized query. [Figure 3] FIG. 1 is a schematic diagram illustrating cache gain determination based on estimated compilation+execution time and estimated execution time. [Figure 4] 1 is a flowchart illustrating an exemplary overall method for implementing runtime parameterized query recompilation. [Figure 5] 1 is a flowchart illustrating an example method for updating a compilation history for a parameterized query. [Figure 6] 1 is a flowchart illustrating an example method for updating an execution history associated with a query plan for a parameterized query. [Figure 7]1 is a flowchart illustrating an example method for counting profitable and unprofitable cases when running with a set of cached query plans over a number of runs. [Figure 8] 1 is a flowchart illustrating an example method for determining whether a cached query plan needs to be replaced. [Figure 9] FIG. 1 is a schematic diagram illustrating a use case of runtime parameterized query recompilation when the parameterized query has a single query plan. [Figure 10] FIG. 10 is a schematic diagram illustrating another use case of runtime parameterized query recompilation when the parameterized query has multiple query plans. [Figure 11] FIG. 1 is a block diagram of an exemplary computing system in which described embodiments may be implemented. [Figure 12] FIG. 1 is a block diagram of an exemplary cloud computing environment that may be used with the techniques described herein. DETAILED DESCRIPTION OF THE INVENTION

[0005] Overview of Parameterized Query Optimization As described herein, query optimization refers to the overall process of attempting to select the most efficient query plan among a number of candidate query plans for executing a query. In practice, despite the use of the terms "optimal" and "optimizing," the actual best query plan may not be selected, but the selected query plan is deemed better than other query plans based on data available to a query optimizer in a query processing engine.

[0006] 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 can be long (e.g., tens of seconds or more). Therefore, to improve operational efficiency, the compiled query plan corresponding to the incoming query (e.g., the determined most optimal query plan) can be stored in a plan cache so that it can be quickly retrieved and reused if the same query is submitted again in the future.

[0007] Query optimization may also be applied to parameterized queries. A parameterized query is a type of Structured Query Language (SQL) query that allows parameters, which act as placeholders, to be included in a query statement. Each parameter can take its value when the parameterized query is executed (e.g., in a separate statement), thereby allowing the parameterized query to be reused with different values and for different purposes. A parameterized query may have multiple parameterized predicates, thus allowing multiple parameters to be assigned their values during query execution. As described herein, a set of parameter values for a parameterized query may be referred to as a parameter set. Parameter values in a parameter set may have various data formats, such as integer, float, string, Boolean, date, time, etc.

[0008] There are several benefits to using parameterized queries. Generally, parameterized queries are easier to read than non-parameterized queries. Parameterized queries can be prepared in advance and then reused for similar uses without having to create a separate SQL query for each case, thus avoiding repeated query compilation, which can consume a lot of computing 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, which can result in unauthorized access to sensitive data.

[0009] However, query optimization for parameterized queries may not always provide expected results because values for query parameters may not be provided until query execution time. Furthermore, the performance of cached plans for parameterized queries may vary based on input parameters. For example, when a parameterized query is executed with a certain set of input parameters, a cached plan may produce good 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 was optimized based on initial parameters and may not be optimal for different parameters. As a result, reusing the same cached plan with different input parameters may result in suboptimal performance.

[0010] The technical challenge in determining whether to use a cached query plan for a parameterized query or generate a new query plan arises from the unpredictability of parameter values at the time of query execution. This unpredictability complicates the decision-making process because it requires accurate predictions of how these parameter values will affect the performance of the query plan. The complexity of this problem is further amplified by the dynamic nature of databases. For example, changes in data distribution over time can affect the optimality of a cached plan, making it even more difficult to determine whether to persist with the cached plan or replace it with a new plan. This highlights the complex balance required when managing parameterized queries within a constantly evolving database environment.

[0011] The technology described herein provides a system and method for runtime parameterized query management that 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 distributions, thereby maintaining the efficiency and accuracy of database operations. This innovative approach provides a technical solution to the challenges posed by the dynamic nature of databases and the unpredictability of parameter values in query execution, and thus represents a significant advancement in the field of database management systems.

[0012] Exemplary Runtime Parameterized Query Management System FIG. 1 illustrates a general block diagram of an exemplary runtime parameterized query management system 100 that is configured to automatically determine at runtime whether to reuse an existing query plan or initiate a recompilation of the query plan.

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

[0014] In some examples, a received parameterized query 110 may have at least one corresponding query plan stored in plan cache 130. The cached query plan may have been previously compiled for parameterized query 110 by, for example, query processing engine 140 based on the same or a different set of input parameters. In some circumstances, parameterized query plan manager 120 may decide to reuse one of the cached query plans to process parameterized query 110. In this scenario, query processing engine 140 may retrieve one of the cached query plans from plan cache 130 and use it to execute parameterized query 110 (with the input parameter set) against target database 150.

[0015] In some examples, the received parameterized query 110 may 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 using one of the cached query plans would be undesirable (e.g., would result in an execution time longer than the estimated compilation and execution time). In that scenario, the query processing engine 140 may compile and execute the parameterized query 110 (with the input parameter set) without using one of the cached query plans.

[0016] In some examples, parameterized query plan manager 120 may initiate an update to the current set of cached query plans following multiple executions of parameterized query 110. This update may be triggered when it is observed that frequent compilations are required for parameterized query 110 without using one of the cached query plans, thus indicating that the previous set of cached query plans may be suboptimal. This proactive approach can contribute to improved performance by updating cached query plans to better match the recurring needs of parameterized query 110.

[0017] As shown in FIG. 1, parameterized query log repository 160 includes compilation history 170 and execution history 180.

[0018] For each unique parameterized query 110 (which may have different input parameter sets), a corresponding compilation history 170 may be stored in the parameterized query log repository 160. The compilation history 170 may include the compilation+execution time for compiling and executing the parameterized query with multiple unique parameter sets. As described herein, a parameter set is considered unique if there is no other parameter set with the exact same combination of parameter values in the same order. As described herein, the compilation+execution time refers to the total time for initially 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, which defines the maximum number of unique parameter sets and corresponding compilation+execution times that may be stored in the compilation history 170, may be predefined. In some examples, the parameterized query log repository 160 may store multiple compilation histories 170, each associated with multiple different parameterized queries.

[0019] As an example, Figure 2 shows an example compilation history 210 associated with an example parameterized query 200 with the following query statement: SELECT * FROM Employees WHERE FirstName=? AND LastName=?, where the question marks are placeholders that may be replaced with different parameter values at query runtime. The compilation history 210 includes compilation plus execution time (e.g., T com+exe In this example, the size of 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 parameterized query 200.

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

[0021] As an example, Figure 2 shows an exemplary execution history 220 associated with a query plan for parameterized query 200. Execution history 220 includes execution times (e.g., T exe Execution history 220 includes parameter sets te1, te2, and te3, collectively denoted as te1, te2, and te3. In this example, execution history 220 has a size of three, and each parameter set includes two parameter values (e.g., two strings) corresponding to the two parameters (“FirstName” and “LastName”) defined in parameterized query 200. In the illustrated example, parameter set 222 is identical to parameter set 212, but none of the other parameter sets in execution history 220 are the same as any of the parameter sets in compilation history 210.

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

[0023] In particular, parameterized query plan manager 120 can estimate the compile+execution time for compiling and executing parameterized query 110 with an input parameter set based on compilation history 170 associated with parameterized query 110. The compile+execution time estimation can be performed by similarity analyzer 122, which is configured to identify a unique parameter set among multiple unique parameter sets stored in compilation history 170 that is most similar to the input parameter set. The underlying assumption is that the compile+execution time for a given parameter set will be similar to the compile+execution time for the most similar parameter set in compilation history 170. This is based on the principle that similar parameter sets will likely result in similar query plans and, therefore, similar compilation and execution times.

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

[0025] The parameterized query plan manager 120 can then determine a cache gain based at least in part on the estimated compilation and 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 therefore preferable) compared to compiling the parameterized query 110 to generate a new query plan and then executing the new query plan (i.e., not using any cached query plan). A positive cache gain indicates that using the cached query plan is likely to save time (and is therefore 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 input parameter set. On the other hand, a non-positive (e.g., 0 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 that case, the parameterized query plan manager 120 can make a decision to compile and execute the parameterized query 110 with the input parameter set. As described below, positive and non-positive cache gains are utilized as criteria (for determining 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 may be defined differently according to the computing environment. For example, if the cache gain is non-positive but 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 from compilation. Calculation of the cache gain may be performed, for example, by cache gain calculator 124.

[0026] If a parameterized query 110 has only one cached query plan, the cache gain may be determined by calculating the difference between the estimated compilation+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). An example is shown in FIG. 3, which shows the estimated compilation+execution time 310 (T com+exe ) and the estimated execution time 320 (T exe As shown, the cash gain is (T com+exe -T exe ) In the illustrated example, the cash gain can be calculated as T com+exe T exe In other examples, the cash gain may be 0 or negative.

[0027] 1, if parameterized query 110 has multiple cached query plans, multiple estimated execution times may be obtained based on multiple execution histories 180 associated with the multiple query plans, respectively. A minimum estimated execution time among the multiple estimated execution times may then be determined, and the cache gain may be determined by calculating the difference between the estimated compile+execute time and the minimum estimated execution time.

[0028] The query plan updater 126 is configured to initiate updates to cached query plans. A cached query plan update may be triggered following multiple executions of the parameterized query 110. A need for an update may be identified when it is observed that incoming parameterized queries 110 frequently result in a non-positive cache gain, thus requiring compilation and execution of the parameterized query 110. This indicates that the current set of cached query plans is suboptimal or outdated, and that reusing them is unlikely to save time 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.

[0029] In practice, the systems illustrated herein, such as the runtime parameterized query management system 100, may vary in complexity, with additional functionality, more complex components, etc. For example, there may be additional functionality within the parameterized query plan manager 120. Additional components may be included to implement security, redundancy, load balancing, reporting design, etc.

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

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

[0032] Exemplary Overall Method for Implementing Runtime Parameterized Query Recompilation FIG. 4 is a flowchart illustrating an exemplary overall method 400 for implementing runtime parameterized query recompilation, which may be performed, for example, by the runtime parameterized query management system 100 of FIG.

[0033] In step 410, a parameterized query is received (e.g., by parameterized query plan manager 120) along with an input parameter set. As described above, the parameterized query may be associated with a compilation history (e.g., compilation history 170), where the compilation history includes compilation and execution times for compiling and executing the parameterized query with multiple unique parameter sets. The parameterized query has at least one query plan stored in a plan cache. The query plan may be associated with an execution history (e.g., execution history 180), where the execution history includes execution times for executing the parameterized query with multiple unique parameter sets by using the query plan. The multiple unique parameter sets included in the execution history may be different from the multiple unique parameter sets included in the compilation history.

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

[0035] In step 430, an estimated execution time for executing the parameterized query with the input parameter set by using the query plan may 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 may include determining a unique parameter set among the multiple unique parameter sets stored in the execution history that is most similar to the input parameter set.

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

[0037] In step 450, a condition check is performed to determine if the cash gain obtained in step 440 is positive.

[0038] If the cache gain is found to be positive, method 400 proceeds to step 460 and executes the parameterized query with the input parameter set by using the 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 in executing the parameterized query.

[0039] On the other hand, if the cache gain is found to be non-positive (eg, 0 or negative), the method 400 proceeds to step 470, where the parameterized query is compiled and executed with the input parameter set.

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

[0041] The actions shown may be described in alternative terms while still implementing the technology, for example, "receiving" may also be described as "sending" in a different sense.

[0042] Exemplary Methods for Measuring Similarity Between Parameter Sets As described above, the process of estimating compilation and execution time for a parameterized query involves identifying a unique parameter set from the compilation history that is most similar to the input parameter set. Similarly, the estimated execution time for a query is determined by identifying a unique parameter set from the execution history that closely matches the input parameter set. Methods for measuring the similarity between parameter sets may involve a variety of techniques.

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

[0044] Different distance measures may be used depending on the data type of the parameter values contained in the parameter set. For example, Euclidean distance and Manhattan distance may be used for continuous numeric data, Hamming distance may be used for categorical data, Levenshtein distance may be used for string data, etc. For parameter sets with mixed data types, Gower's distance may be used.

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

[0046] In some examples, the process of determining the unique parameter set that is most similar to an input parameter set involves calculating a selectivity value (or simply “selectivity”) for the input parameter set and selectivity values for multiple unique parameter sets (in the compilation or execution history). Selectivity is a measure of how selective a predicate is; for example, selectivity may indicate how many rows a predicate matches out of the total number of rows in a table. In some examples, the selectivity of a parameterized query may be determined based on statistics and / or histograms stored in a database that contain information about the distribution of values in a column or set of columns. Selectivity may 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. In particular, the unique parameter set with a selectivity value closest to the selectivity value of the input parameter set may be identified as most similar.

[0047] In addition to or instead of the methods described above, other similarity measures (e.g., cosine similarity, Jaccard index, Pearson or Spearman correlation coefficient, etc.) can also be employed to measure the similarity between parameter sets, and the choice of similarity measure can be tailored to the particular characteristics of the parameter sets. Furthermore, machine learning algorithms for pattern recognition, or even custom heuristic methods tailored to the particular characteristics of the parameter sets, can be used to measure the similarity between different parameter sets.

[0048] Exemplary Method for Updating Compilation History for Parameterized Queries As described herein, the compilation history associated with a parameterized query may be dynamically updated when processing the parameterized query. Figure 5 is a flowchart illustrating an example method 500 for updating the compilation history for a parameterized query, which may be implemented, for example, by the history manager 128 of Figure 1.

[0049] Generally, method 500 may be executed during an initialization phase when the compilation history is not completely filled (e.g., the number of unique parameter sets and corresponding compilation+execution times stored in the compilation history is less than the maximum size of the compilation history). Additionally, method 500 may be executed during runtime when processing of a parameterized query triggers an update of the compilation history, as further described below with reference to FIG.

[0050] In step 510, the parameterized query is compiled with an input parameter set P to generate a cached plan (CP), which is then executed.

[0051] In step 520, the compilation and execution time (T com+exe ) is obtained and the generated cached plan CP may be stored in the plan cache.

[0052] In step 530, the input parameter set P is compared with the compilation history (H com+exe A condition check can be performed to determine whether the value is stored in the .

[0053] If the input parameter set P already exists in the compilation history, method 500 proceeds to step 540 and updates statistics for P. In particular, the compilation+execution time corresponding to the input parameter set P stored in the compilation history is updated to the newly obtained compilation+execution time T com+exe Such an update can be implemented in different ways. For example, if the stored compile+execution time corresponding to the input parameter set P is updated based on the newly obtained compile+execution time T com+exe As another example, the stored compilation+execution time corresponding to the input parameter set P may be updated using a weighted average of the stored values and the newly obtained values. new =α T com+exe +(1-α)·T stored where T new is the updated compilation + execution time, T com+exe is the newly obtained compilation + execution time, and T stored is the stored compilation+execution time, and α is a predefined weighting coefficient (0≦α≦1).

[0054] 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), method 500 proceeds to step 550. At this stage, method 500 verifies whether the compilation history has reached its capacity. In particular, method 500 checks whether the count of unique parameter sets, along with their respective compilation+execution times stored in the compilation history, has hit a predefined limit (denoted as k1) representing the maximum size of the compilation history.

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

[0056] Exemplary Method for Updating Execution History of a Query Plan for a Parameterized Query After updating the compilation history using method 500 of Figure 5, the execution history associated with the newly cached query plan CP may be updated. Figure 6 is a flowchart illustrating an example method 600 for updating the execution history associated with a query plan, which may be implemented, for example, by history manager 128 of Figure 1.

[0057] In step 610, the execution history (H) associated with the cached query plan is exe ) is initially cleared or initialized to be empty.

[0058] In step 620, a condition check is performed to determine whether the execution history has reached its capacity. Specifically, the condition check checks whether the count of unique parameter sets, along with their respective execution times (by using cached query plans) stored in the execution history, hits a predefined limit (denoted as k2) representing the maximum size of the execution history.

[0059] If the execution history has already reached its capacity, method 600 proceeds to step 630 and executes using the current set of cached query plans for a number of executions, as further described below with reference to FIG. 7. The number of executions to be executed (denoted as x) may be a predefined parameter. For example, x may be 2, 3, 4, or more. In one special situation, x may be set to 1.

[0060] On the other hand, if the execution history has not reached its capacity, the method 600 proceeds to step 640 and executes the parameterized query with the input parameter set P by using the cached query plan. exe ) can be obtained.

[0061] Then, in step 650, the input parameter set P is compared with the execution history (H) associated with the cached query plan. exe Another condition check is performed to determine whether the file is stored in the

[0062] If the input parameter set P already exists in the execution history, the method 600 proceeds to step 660 and updates the statistics for P. In particular, the execution time corresponding to the input parameter set P stored in the execution history is updated to the newly obtained execution time T exe Such an update can be implemented in different ways. For example, if the stored execution time corresponding to the input parameter set P is updated based on the newly obtained execution time T exeAs another example, the stored execution time corresponding to the input parameter set P may be updated using a weighted average of the stored value and the newly acquired value. new =β T exe +(1-β)·T stored where T new is the updated execution time, T exe is the newly obtained execution time, and T stored is the stored execution time, and β is a predefined weighting factor (0≦β≦1).

[0063] On the other hand, if the input parameter set P does not exist in the execution history (i.e., P represents another unique parameter set), the method 600 proceeds to step 670 and compares the input parameter set P with the obtained execution time T exe Insert it into the execution history so that it is paired with

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

[0065] Exemplary Method for Determining Whether a Cached Query Plan Requires Replacement After the execution history associated with the cached query plans is updated and reaches its capacity, the current set of cached query plans may be used to execute incoming parameterized queries (with any valid input parameter set) across a number of executions. For each execution, an evaluation is performed to determine whether it would be beneficial (e.g., save time) to use one of the cached query plans compared to the alternative of compiling and executing a new query plan using the input parameter set. Based on such evaluation, a determination may be made whether the current set of cached query plans needs to be replaced.

[0066] Figure 7 is a flowchart illustrating an example method 700 for executing and evaluating several executions of a parameterized query (with any valid input parameter set) using a current set of cached query plans. Figure 8 is a flowchart illustrating an example method 800 for determining whether a cached query plan needs to be updated. Methods 700 and 800 may be implemented, for example, by query plan updater 126 of Figure 1.

[0067] Referring to FIG. 7, method 700 begins in step 710 by initializing three counters: a count of beneficial cases (denoted as beneficialCases) and a count of non-beneficial cases (denoted as nonBeneficialCases), which may be set to 0; and a count of executions of the parameterized query using the current set of cached query plans (denoted as numExecution), which may also be set to 0.

[0068] In step 720, a condition check is performed to determine whether the execution count has reached a predefined limit x, where x is a positive integer (e.g., x may be 1, 2, 3, 4, or more). If so, method 700 may proceed to step 730 to determine whether to replace one of the cached query plans with a new query plan, which may be implemented by method 800 of FIG. 8, which is described further below.

[0069] If not (i.e., numExecution < x), method 700 moves to step 740 to estimate or calculate the cache gain. As described above, the cache gain can be calculated by: (a) estimating the compile + execution time for compiling and executing the parameterized query with the input parameter set based on the compile history associated with the parameterized query; (b) estimating the 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; and (c) calculating the difference between the estimated compile + execution time and the estimated execution time. Further, as described above, if the cached query plan is the only plan for the parameterized query, the cache gain is calculated by subtracting the estimated execution time from the estimated compile + execution time. However, if the 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 this minimum estimated execution time from the estimated compile + execution time.

[0070] Next, a conditional check of whether the cache gain is positive can be performed at step 750.

[0071] If the cache gain is positive, reusing the cached query plan is considered beneficial (e.g., saves time) compared to compiling and executing a new query plan. Thus, method 700 proceeds to step 760, where the cached query plan (or, if the parameterized query has multiple cached query plans, the cached plan associated with the smallest estimated execution time) may be used to execute the parameterized query. Then, in step 770, a counter for beneficial cases is incremented.

[0072] On the other hand, if the cache gain is negative (or 0), reusing the cached query plan is deemed not beneficial (e.g., does not save time) 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, in step 790, a counter for the not beneficial cases is incremented.

[0073] After step 770 or step 790, method 700 may proceed to step 795 and increment the count of executions by one, and then return to step 720 to check whether the count of executions has reached the predefined limit x. In other words, method 700 may continue to execute and evaluate parameterized queries using the current set of cached query plans until the number of executions reaches the predefined limit x, when a determination may be made whether the current set of cached query plans needs to be updated, as described in example method 800 of FIG. 8 .

[0074] In step 810, a condition 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 yes, method 800 proceeds to step 830 and updates the compilation history (including compiling the parameterized query and caching the generated query plan), e.g., by using method 500 of FIG. 5 . If no, method 800 proceeds to step 820 and executes using the current cached query plan for a number of runs, e.g., by using method 700 of FIG. 7 . In other words, method 800 will compile and execute the parameterized query and cache the generated query plan (and replace the previously cached query plan, if any) when reusing the current set of cached query plans becomes too frequent and not beneficial (e.g., does not save time), and will update the compilation history when the compilation history is not full. Otherwise, the method 800 will continue to execute and evaluate the parameterized query using the current set of cached query plans if the condition check at step 810 returns false.

[0075] A predefined threshold c controls how often occurrences of unbeneficial cases are considered too frequent. In one special case, threshold c can be 1. In that case, the above ratio calculation is equivalent to comparing beneficialCases to nonBeneficialCases. In another special case, where x is set to 1, rather than calculating the ratio beneficialCases / nonBeneficialCases, the occurrence of a single unbeneficial case can trigger replacement of the cached query plan and updating of the compilation history.

[0076] Example Use Case of Runtime Parameterized Query Recompilation When the Parameterized Query Has a Single Query Plan 9 shows a schematic use case to further illustrate the runtime parameterized query recompilation techniques described herein. In this use case, the parameterized query is assumed to have a single cached query plan (and therefore has only one execution history associated with the query plan).

[0077] Figure 9 illustrates the processing of a parameterized query with input parameter sets that vary at runtime (e.g., parameter sets A, B, C, etc.). In this example, the maximum compilation history size (k1) and the maximum execution history size (k2) are both set to 3, the predefined limit (x) for executing and evaluating parameterized queries using cached query plans is set to 4, and the threshold c is set to 1.

[0078]

number

[0079] denotes the compilation + execution time for compiling and executing a parameterized query with parameter set A,

[0080]

number

[0081] shows the execution time for using the cached query plan to execute the parameterized query with parameter set A. The ellipsis indicates the processing of some additional queries.

[0082] Initially, the compilation history associated with the parameterized query stores three compilation+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 the parameterized query is processed with input parameter set D (e.g., the condition check in step 810 of FIG. 8 returns false), the parameterized query is compiled and executed again. The generated query plan is then saved in the plan cache (replacing the previously cached query plan), and the corresponding compilation+execution time 920 is stored in the compilation history. Because the compilation history is already full, the previously stored parameter set A and its compilation+execution time 905 will be removed from the compilation history (e.g., step 560 of FIG. 5), for example, according to a predefined eviction policy. Thus, the compilation history will be updated to include three compilation+execution times 910, 915, and 920 corresponding to parameter sets B, C, and D, respectively.

[0083] An execution history associated with the cached query plan would then be created, for example, using method 600 of Figure 6. In the illustrated example, using the cached query plan (obtained with input parameter set D), execution times 925, 930, and 935 for executing the parameterized query with three different parameter sets D, E, and F are obtained and stored in the execution history.

[0084] Next, the parameterized query may be processed with input parameter set D, and a cache gain may be estimated (e.g., step 740 of FIG. 7). In this example, input parameter set D is found to be most similar to parameter set D stored in the compilation history (e.g., among parameter sets B, C, and D), and therefore the estimated compilation+execution time to compile and execute the parameterized query with input parameter set D is 920

[0085]

number

[0086] Input parameter set D is also found to be most similar to parameter set D stored in the execution history (e.g., among parameter sets D, E, and F), and therefore the estimated execution time for executing the parameterized query with input parameter set D using the cached query plan is 925

[0087]

number

[0088] In this example,

[0089]

number

[0090] but

[0091]

number

[0092] Since , the estimated cache gain is positive. Therefore, the cached query plan will be used to execute the parameterized query with input parameter set D, resulting in execution time 940, and the count of beneficial cases will be incremented (steps 760, 770 in FIG. 7).

[0093] The parameterized query may then be processed with input parameter set G, and a cache gain may be estimated (e.g., step 740 of FIG. 7). In this example, input parameter set G is found to be most similar to parameter set B stored in the compilation history (e.g., among parameter sets B, C, and D), and therefore the estimated compilation+execution time to compile and execute the parameterized query with input parameter set G is 910

[0094]

number

[0095] Furthermore, input parameter set G is found to be most similar to parameter set E stored in the execution history (e.g., among parameter sets D, E, and F), and therefore the estimated execution time for executing the parameterized query with input parameter set G using the cached query plan is 930

[0096]

number

[0097] In this example,

[0098]

number

[0099] but

[0100]

number

[0101] Since , the estimated cache gain is negative. Therefore, rather than using the cached query plan, the parameterized query will be compiled and executed with input parameter set G, resulting in compilation+execution time 945, and the count of uninformative cases will be incremented (e.g., steps 780, 790 in FIG. 7). Note that the cached query plan (obtained with input parameter set D) remains unchanged.

[0102] The parameterized query is then processed with input parameter sets H and I. In both cases, it is assumed that the estimated cache gain is negative (as with the parameterized query with input parameter set G). As a result, the parameterized query is compiled and executed with input parameter sets H and I, resulting in compilation+execution times 950 and 955, followed by a count of 3 for non-informative cases.

[0103] Currently, the total number of executions of the parameterized query is four, including one useful case and three unuseful cases. Therefore, the cached query plan (obtained with input parameter set D) needs to be replaced, and the compilation history needs to be updated (e.g., steps 810 and 830 of FIG. 8 ). In the illustrated example, a parameterized query is next received with input parameter set A. Therefore, after compiling and executing the parameterized query, the newly generated query plan (obtained with input parameter set A) will replace the previously cached query plan (e.g., obtained with input parameter set D). The compilation history will also be updated accordingly. In this example, compilation+execution time 960 for compiling and executing the parameterized query with input parameter set A may be stored in the compilation history, and the existing entry in the compilation history (e.g., compilation+execution time 920 corresponding to parameter set D) may be removed according to a predefined eviction policy.

[0104] A similar process can continue for parameterized queries with other sets of input parameters to determine whether to reuse the cached query plan, compile and execute the parameterized query with a new query plan, and whether the cached query plan needs to be replaced.

[0105] Example Use Cases of Runtime Parameterized Query Recompilation When a Parameterized Query Has Multiple Query Plans 10 schematically illustrates another use case to further illustrate the runtime parameterized query recompilation techniques described herein. In this use case, a parameterized query is assumed to have three different cached query plans, each with an associated execution history.

[0106] Similarly, Figure 10 illustrates the processing of a parameterized query with input parameter sets that vary at runtime (e.g., parameter sets A, B, C, etc.). In this example, the maximum compilation history size (k1) and the maximum execution history size (k2) are both set to 3, a predefined limit (x) for executing and evaluating parameterized queries using the current set of cached query plans is set to 4, and a threshold c is set to 1. In this example,

[0107]

number

[0108] denotes the compilation + execution time for compiling and executing a parameterized query with parameter set A,

[0109]

number

[0110] denotes the execution time for executing the parameterized query with parameter set A using the cached query plan P. The ellipses indicate the processing of some additional queries.

[0111] Initially, the compilation history includes compilation+execution times 1005 and 1025 for compiling and executing the parameterized query with input parameter sets A and C, respectively. Using a cached query plan obtained with parameter set A, the corresponding execution history stores execution times 1010, 1015, and 1020 for executing the parameterized query with three different input parameter sets A, B, and C, respectively. Using another cached query plan obtained with parameter set C, the corresponding execution history stores execution times 1030, 1035, and 1040 for executing the parameterized query with three different input parameter sets A, D, and B, respectively.

[0112] A parameterized query is then received with input parameter set B. Because the compilation history has not reached its full capacity, the parameterized query is compiled and executed, resulting in another cached query plan (obtained with parameter set B), and the compilation history is updated by adding the compilation+execution time 1045 corresponding to parameter set B (e.g., steps 810 and 830 of FIG. 8 ).

[0113] An execution history associated with the newly cached query plan (obtained with parameter set B) is then created using, for example, method 600 of Figure 6. In the illustrated example, execution times 1050, 1055, and 1060 for executing the parameterized query with three different parameter sets A, B, and C using the cached query plan (obtained with input parameter set B) are obtained and stored in the execution history associated with the newly cached query plan (obtained with parameter set B).

[0114] Next, a parameterized query may be received with input parameter set A, and a cache gain may be estimated (e.g., step 740 of FIG. 7). In this example, input parameter set A is found to be most similar to parameter set A stored in the compilation history (e.g., among parameter sets A, C, and B), and therefore the estimated compilation+execution time to compile and execute the parameterized query with input parameter set A is 1005

[0115]

number

[0116] Then, for each of the three execution histories, an estimated execution time by using the corresponding cached query plan may be determined. For example, input parameter set A is found to be most similar (e.g., among parameter sets A, B, and C) to parameter set A stored in the execution history associated with the cached query plan retrieved with parameter set A. Thus, a first estimated execution time for executing the parameterized query with input parameter set A (using the cached query plan retrieved with parameter set A) is 1010

[0117]

number

[0118] Input parameter set A is also found to be most similar (e.g., among parameter sets A, D, and B) to parameter set A stored in the execution history associated with the cached query plan retrieved with parameter set C. Thus, the second estimated execution time for executing the parameterized query with input parameter set A (using the cached query plan retrieved with parameter set C) is 1030

[0119]

number

[0120] Similarly, input parameter set A is found to be most similar (e.g., among parameter sets A, B, and C) to parameter set A stored in the execution history associated with the cached query plan retrieved with parameter set B. Thus, the third estimated execution time for executing the parameterized query with input parameter set A (using the cached query plan retrieved with parameter set B) is 1050

[0121]

number

[0122] is.

[0123] 3 estimated execution times 1010

[0124]

number

[0125] , 1030

[0126]

number

[0127] , and 1050

[0128]

number

[0129] Among them, the first estimated execution time 1010

[0130]

number

[0131] As explained above, the estimated cash gain is then

[0132]

number

[0133] , which in the illustrated example is positive. Thus, the parameterized query will be executed with input parameter set A using the cached query plan obtained with parameter set A, with execution time 1065. The counter for informative cases will also be incremented.

[0134] Next, a parameterized query is received with input parameter set E, and the cache gain may be similarly estimated. In this example, input parameter set E is found to be most similar to parameter set C stored in the compilation history (e.g., among parameter sets A, C, and B), and therefore the estimated compilation+execution time to compile and execute the parameterized query with input parameter set E is 1025

[0135]

number

[0136] Then, for each of the three execution histories, an estimated execution time using the corresponding cached query plan may be estimated. For example, input parameter set E is found to be most similar (e.g., among parameter sets A, B, and C) to parameter set C stored in the execution history associated with the cached query plan retrieved with parameter set A. Thus, a first estimated execution time for executing the parameterized query with input parameter set E (using the cached query plan retrieved with parameter set A) is 1020

[0137]

number

[0138] Input parameter set E is also found to be most similar (e.g., among parameter sets A, D, and B) to parameter set D stored in the execution history associated with the cached query plan retrieved with parameter set C. Thus, the second estimated execution time for executing the parameterized query with input parameter set E (using the cached query plan retrieved with parameter set C) is 1035

[0139]

number

[0140] Similarly, input parameter set E is found to be most similar (e.g., among parameter sets A, B, and C) to parameter set C stored in the execution history associated with the cached query plan retrieved with parameter set B. Thus, the third estimated execution time for executing the parameterized query with input parameter set E (using the cached query plan retrieved with parameter set B) is 1060

[0141]

number

[0142] is.

[0143] 3 estimated execution times 1020

[0144]

number

[0145] , 1035

[0146]

number

[0147] , and 1060

[0148]

number

[0149] The second estimated execution time is 1035

[0150]

number

[0151] As explained above, the estimated cash gain is then

[0152]

number

[0153] , which in this example is negative. Therefore, none of the cached query plans can be reused. Instead, the parameterized query will be compiled and executed with input parameter set E, with a compilation+execution time of 1070. A counter for uninformative cases will be incremented.

[0154] A similar process can continue for parameterized queries with other sets of input parameters to determine whether to reuse any one 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.

[0155] Exemplary Benefits Several advantages may be achieved through the techniques described herein.

[0156] First, the runtime parameterized query management system disclosed herein enables automatic determination during runtime whether to reuse one of the cached plans or compile a new query plan for the parameterized query. Specifically, the parameterized query may be executed using the cached query plan only if using the cached query plan would be deemed beneficial (e.g., would result 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 challenges posed by the dynamic nature of databases and the unpredictability of parameter values in query execution, helping to maintain system performance despite changing query patterns.

[0157] 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 found to be ineffective for processing parameterized queries, frequently leading to repeated compilations. This automatic update of inefficient query plans in the plan cache improves the probability of reusing updated cached query plans, thereby potentially increasing efficiency and minimizing resource usage over time. Importantly, the decision to update the cached query plan can be made after multiple executions of the parameterized query. This approach enables the collection of statistics, such as a count of effective cases and a count of ineffective cases, facilitating more robust decision making. This avoids the pitfall of prematurely updating a cached query plan based on a single or few instances where the cached query plan may not be effective for a parameterized query with an infrequently occurring input parameter set.

[0158] Furthermore, the techniques described herein provide a novel method for accurately estimating cache gain for a parameterized query based on an estimate of the compilation time plus execution time for compiling and executing the parameterized query and the execution time for executing the parameterized query using a cached query plan. Such an estimate is based on a similarity measure 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 limited size that is sufficient to store a limited number of unique parameter sets (and their corresponding compilation time plus execution time or execution time). By using a similarity measure (as opposed to relying on exact matches), the limited number of parameter sets in the compilation history or execution history can represent a large number of variations of input parameter sets that may be encountered for the parameterized query. As a result, the compilation history and execution history may occupy only a small memory or storage space. The smaller size of the compilation history and execution history also makes measuring and comparing similarity metrics more time-efficient and requires fewer computing resources compared to storing all possible (or large number) variations of parameter sets. This is especially important for database systems that have extremely constrained environments (eg, limited storage space, CPU resources, etc.).

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

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

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

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

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

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

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

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

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

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

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

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

[0171] In practice, cloud-based, on-premise-based, or hybrid scenarios may be supported.

[0172] Exemplary Implementation Although some actions of the disclosed methods are described in a particular order for convenient presentation, such methods of description encompass rearrangement unless a particular order is required by specific language set forth herein. For example, actions described sequentially may in some cases be rearranged or performed simultaneously.

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

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

[0175] Illustrative Embodiments Any of the following exemplary embodiments may be implemented.

[0176] Embodiment 1. A computer-implemented method comprising: receiving a parameterized query along with an input parameter set for the parameterized query, the parameterized query having a query plan stored in a plan cache, the parameterized query being associated with a compilation history comprising compilation+execution times for compiling and executing the parameterized query with a first plurality of unique parameter sets, and the query plan being associated with an execution history comprising execution times for executing the parameterized query with a second plurality of unique parameter sets by using the query plan; and compiling and executing the parameterized query along with the input parameter set based on the compilation history associated with the parameterized query. determining an estimated compilation+execution time for executing the parameterized query with the input parameter set by using the query plan based on an execution history associated with the query plan; determining a cache gain based at least in part on the estimated compilation+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.

[0177] Embodiment 2. The method of 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 a number of executions of the parameterized query since the last evaluation whether the query plan in the plan cache needs to be updated; determining a ratio of the first counter to the second counter in response to finding that the number of executions of the parameterized query since the last evaluation reaches a predetermined number; and updating the query plan in the plan cache with a new query plan generated by compilation of the parameterized query with the input parameter set in response to finding that the ratio of the first counter to the second counter is below a predetermined threshold.

[0178] Embodiment 3. The method of embodiment 2, wherein after updating the query plan in the plan cache, the method further includes updating a compilation history associated with the parameterized query, wherein updating the compilation history includes determining a new compile+execution time for compiling and executing the 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 compile+execution time for compiling and executing the parameterized query with the input parameter set in the compilation history based at least in part on the new compile+execution time 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 compile+execution time for compiling and executing the parameterized query with the input parameter set into the compilation history.

[0179] Embodiment 4. The method of embodiment 3, wherein the step of inserting a new compile+execute time for compiling and executing the parameterized query with the input parameter set into the compilation history includes the steps of: determining a count of a 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 the compile+execute time for compiling and executing the parameterized query with one of the first plurality of unique parameter sets from the compilation history.

[0180] Embodiment 5. The method further includes updating an execution history associated with the query plan after updating the compilation history associated with the parameterized query, wherein updating the execution history includes: executing the parameterized query with a new parameter set for the parameterized query using the updated query plan in the plan cache in an iterative operation; 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; and determining whether the new parameter set is one of the second plurality of unique parameter sets stored in the execution history. 5. The method of any one of embodiments 3-4, further comprising: updating, in the execution history, an execution time for executing the parameterized query with the new parameter set based at least in part on the new execution time for executing the parameterized query with the new parameter set in response to finding that the new parameter set is one of the second plurality of unique parameter sets stored in the execution history; and inserting, in the execution history, the new execution time for executing the parameterized query with the new parameter set 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, wherein the iterative operation continues until a count of the second plurality of unique parameter sets stored in the execution history is equal to a predefined size of the execution history.

[0181] Embodiment 6. The method of any one of embodiments 1 to 5, wherein determining an estimated compilation and execution time for compiling and executing the parameterized query with the input parameter set includes determining a unique parameter set among a first plurality of unique parameter sets stored in the compilation history that is most similar to the input parameter set, and determining an estimated execution time for executing the parameterized query with the input parameter set by using the query plan includes determining a unique parameter set among a second plurality of unique parameter sets stored in the execution history that is most similar to the input parameter set.

[0182] Embodiment 7. The method of embodiment 6, wherein the step of determining 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 the steps of 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 a smallest distance among the measured distances.

[0183] Embodiment 8. The method of any one of embodiments 6 to 7, wherein the step of determining 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 the steps of determining a selectivity value of the input parameter set and a selectivity value of the first plurality of unique parameter sets or the second plurality of unique parameter sets, and identifying a selectivity value of one of the first plurality of unique parameter sets or the second plurality of unique parameter sets that is closest to the selectivity value of the input parameter set.

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

[0185] Embodiment 10. The method of embodiment 9, wherein the step of determining the cache gain includes the steps of determining a minimum estimated execution time among a plurality of estimated execution times, and determining a difference between the estimated compile+execute time and the minimum estimated execution time.

[0186] Embodiment 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 along with an input parameter set for the parameterized query, the parameterized query having a query plan stored in a plan cache, the parameterized query being associated with a compilation history comprising compilation+execution times for compiling and executing the parameterized query with a first plurality of unique parameter sets, and the query plan being associated with an execution history comprising execution times for executing the parameterized query with a second plurality of unique parameter sets by using the query plan; determining an estimated compilation+execution time for compiling and executing the parameterized query with an input parameter set based on a 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 an execution history associated with the query plan; determining a cache gain based at least in part on the estimated compilation+execution time and the estimated execution time; executing the parameterized query with the input parameter set by using the query plan in response to finding that the cache gain is positive; and compiling and executing the parameterized query with the input parameter set in response to finding that the cache gain is not positive.

[0187] Embodiment 12. The system of embodiment 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 the last evaluation whether the query plan in the plan cache needs to be updated; determining a ratio of the first counter to the second counter in response to finding that the number of executions of the parameterized query since the last evaluation reaches a predetermined number; and updating the query plan in the plan cache with a new query plan generated by compilation of the parameterized query with the input parameter set in response to finding that the ratio of the first counter to the second counter is below a predetermined threshold.

[0188] Embodiment 13. The system of embodiment 12, wherein the operations further include updating a 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 compile+execution time for compiling and executing the 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; updating the compile+execution time for compiling and executing the parameterized query with the input parameter set in the compilation history based at least in part on the new compile+execution time for compiling and executing the parameterized query with the input parameter set in response to finding that the input parameter set is one of the first plurality of unique parameter sets stored in the compilation history; and inserting the new compile+execution time for compiling and executing the parameterized query with the input parameter set in the compilation history 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.

[0189] Embodiment 14. The system of embodiment 13, wherein inserting a new compile+execute time for compiling and executing a parameterized query with an input parameter set into the compilation history includes determining a count of a 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, deleting the compile+execute time for compiling and executing a parameterized query with one of the first plurality of unique parameter sets from the compilation history.

[0190] Embodiment 15. The operations further include updating an 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 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 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; and determining whether the new parameter set is one of the second plurality of unique parameter sets stored in the execution history. 15. The system of any one of embodiments 13 to 14, further comprising: updating, in the execution history, an execution time for executing the parameterized query with the new parameter set based at least in part on the new execution time for executing the parameterized query with the new parameter set in response to finding that the new parameter set is one of the second plurality of unique parameter sets stored in the execution history; and inserting, into the execution history, the new execution time for executing the parameterized query with the new parameter set 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, 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.

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

[0192] Embodiment 17. The system of embodiment 16, wherein determining 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 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 a smallest distance among the measured distances.

[0193] Embodiment 18. The system of any one of embodiments 16 to 17, wherein determining 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 a selectivity value of the input parameter set and a selectivity value of the first plurality of unique parameter sets or the second plurality of unique parameter sets, and identifying a selectivity value of one of the first plurality of unique parameter sets or the second plurality of unique parameter sets that is closest to the selectivity value of the input parameter set.

[0194] Embodiment 19. The system of any one of embodiments 11-18, wherein the query plan is one of a plurality of query plans for a parameterized query stored in a plan cache, the execution history is one of a plurality of execution histories respectively associated with the plurality of query plans, the estimated execution time is one of a plurality of estimated execution times respectively determined for the plurality of query plans, and determining the cache gain includes determining a minimum estimated execution time among the plurality of estimated execution times and determining a difference between the estimated compile+execute time and the minimum estimated execution time.

[0195] Embodiment 20. One or more non-transitory computer-readable media having encoded thereon computer-executable instructions that cause one or more processors to perform a method, the method comprising: receiving a parameterized query along with an input parameter set for the parameterized query, the parameterized query having a query plan stored in a plan cache, the parameterized query being associated with a compilation history comprising compilation+execution times for compiling and executing the parameterized query with a first plurality of unique parameter sets, and the query plan being associated with an execution history comprising execution times for executing the parameterized query with a second plurality of unique parameter sets by using the query plan; and computing the parameterized query according to the compilation history associated with the parameterized query based on the compilation history associated with the parameterized query. determining an estimated compilation+execution time for compiling and executing the parameterized query with the input parameter set by using the query plan based on an execution history associated with the query plan; determining an estimated execution time for executing the parameterized query with the input parameter set by using the query plan based on an execution history associated with the query plan; determining a cache gain, wherein determining the cache gain includes calculating a difference between the estimated compilation+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 by using the query plan.

[0196] Exemplary Alternatives Techniques from any example may be combined with techniques described in any one or more of the other examples. In view of the numerous possible embodiments to which the principles of the disclosed technology may be applied, it should be recognized that the illustrated embodiments are examples of the disclosed technology and should not be construed as limitations on the scope of the disclosed technology. Rather, the scope of the disclosed technology includes that encompassed by the scope and spirit of the following claims. [Explanation of symbols]

[0197] 100 Runtime Parameterized Query Management System 110 Parameterized Queries, Incoming Parameterized Queries 120 Parameterized Query Plan Manager 122 Similarity Analyzer 124 Cash Profit Calculator 126 Query Plan Updater 128 History Manager 130 Plan Cash 140 Query Processing Engine 150 target databases 160 Parameterized Query Log Repository 170, 210 compilation history 180, 220 Execution history 200 Parameterized Query 212, 214, 216, 218, 222, 224, 226 parameter sets 310, 905, 910, 915, 920, 945, 950, 955, 960, 1005, 1025, 1045, 1070 Compilation + Execution time 320, 925, 930, 935, 940, 1015, 1020, 1030, 1040, 1050, 1055, 1060, 1065 Execution time 1010 Execution time, first estimated execution time 1035 execution time, second estimated execution time 1100 Computing System 1110 Processing unit, central processing unit 1115 Processing Unit, Graphics Processing Unit or Co-Processing Unit 1120, 1125 memory 1130 Basic configuration 1140 Storage 1150 Input Devices 1160 output device 1170 Communication Connection 1180 Software 1200 Cloud Computing Environment 1210 Cloud Computing Services 1220, 1222, 1224 Computing Devices

Claims

1. 1. A computer-implemented method comprising: receiving a parameterized query along with a set of input parameters for the parameterized query, the parameterized query having a query plan stored in a plan cache, the parameterized query being associated with a compilation history comprising compilation+execution times for compiling and executing the parameterized query with a first plurality of unique parameter sets, and the query plan being associated with an execution history comprising execution times for executing the parameterized query with a second plurality of unique parameter sets using the query plan; determining an estimated compilation and 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 based at least in part on the estimated compilation+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; in response to finding that the cache gain is not positive, compiling and executing the parameterized query with the input parameter set; 11. A computer-implemented method comprising:

2. 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; determining a ratio of the first counter to the second counter in response to finding that the number of executions of the parameterized query since a last evaluation reaches a predetermined number; updating the query plan in the plan cache with a new query plan generated by compilation of the parameterized query with the input parameter set in response to finding that the ratio of the first counter to the second counter is below a predetermined threshold; The method of claim 1 further comprising:

3. The method further includes, after updating the query plan in the plan cache, updating the compilation history associated with the parameterized query, wherein updating the compilation history comprises: determining a new compile+execute 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 compile+execution time for compiling and executing the parameterized query with the input parameter set in the compilation history based at least in part on the new compile+execution time for compiling and executing the parameterized query with the input parameter set; 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 compile+execute time for compiling and executing the parameterized query with the input parameter set into the compilation history; 3. The method of claim 2, comprising:

4. inserting the new compilation+execution time for compiling and executing the parameterized query with the input parameter set into the compilation history; determining a count of the first plurality of unique parameter sets stored in the compilation history; 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 the compilation+execution time for compiling and executing the parameterized query with one of the first plurality of unique parameter sets from the compilation history; 4. The method of claim 3, comprising:

5. The method further includes, after updating the compilation history associated with the parameterized query, updating the execution history associated with the query plan, wherein updating the execution history comprises, in an iterative operation: executing the parameterized query with a new set of parameters 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 set of parameters 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; responsive to finding the new parameter set is one of the second plurality of unique parameter sets stored in the execution history, updating the execution time for executing the parameterized query with the new parameter set in the execution history based at least in part on the new execution time for executing the parameterized query with the new parameter set; responsive 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 new parameter set into the execution history; Including, The method of claim 3 , wherein the iterative operation continues until a count of the second plurality of unique parameter sets stored in the execution history equals a predefined size of the execution history.

6. determining the estimated compilation and execution time for compiling and executing the parameterized query with the input parameter set; 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; Including, determining the estimated execution time for executing the parameterized query with the set of input parameters by using the query plan; 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; 2. The method of claim 1, comprising:

7. 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, measuring a distance between the input parameter set and the first plurality of unique parameter sets or the second plurality of unique parameter sets; identifying a minimum distance among the measured distances; 7. The method of claim 6, comprising:

8. 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, determining a selectivity value for the input parameter set and a selectivity value for the first plurality of unique parameter sets or the second plurality of unique parameter sets; identifying a selectivity value of one of the first plurality of unique parameter sets or the second plurality of unique parameter sets that is closest to the selectivity value of the input parameter set; 7. The method of claim 6, comprising:

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

10. determining the cash gain; determining a minimum estimated execution time among the plurality of estimated execution times; determining the difference between the estimated compilation+execution time and the minimum estimated execution time; 10. The method of claim 9, comprising:

11. 1. A computing system comprising: Memory and one or more hardware processors coupled to the memory; 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; and wherein the operation comprises: receiving a parameterized query along with input parameter sets for the parameterized query, the parameterized query having a query plan stored in a plan cache, the parameterized query associated with a compilation history comprising compilation+execution times for compiling and executing the parameterized query with a first plurality of unique parameter sets, and the query plan associated with an execution history comprising execution times for executing the parameterized query with a second plurality of unique parameter sets using the query plan; determining an estimated compilation and 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 based at least in part on the estimated compilation+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; responsive to finding that the cache gain is not positive, compiling and executing the parameterized query together with the input parameter set; a computing system including:

12. The operation is 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; determining a ratio of the first counter to the second counter in response to finding that the number of executions of the parameterized query since a last evaluation reaches a predetermined number; updating the query plan in the plan cache with a new query plan generated by compilation of the parameterized query with the input parameter set in response to finding that the ratio of the first counter to the second counter is below a predetermined threshold; The system of claim 11 further comprising:

13. 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: determining a new compile+execute 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 compile+execution time for compiling and executing the parameterized query with the input parameter set in the compilation history based at least in part on the new compile+execution time for compiling and executing the parameterized query with the input parameter set; 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 compile+execute time for compiling and executing the parameterized query with the input parameter set into the compilation history; The system of claim 12, comprising:

14. inserting the new compilation+execution time for compiling and executing the parameterized query with the input parameter set into the compilation history; determining a count of the first plurality of unique parameter sets stored in the compilation history; 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 the compilation+execution time for compiling and executing the parameterized query with one of the first plurality of unique parameter sets from the compilation history; The system of claim 13, comprising:

15. The operations further include 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 a new set of parameters 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 set of parameters 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; responsive to finding the new parameter set is one of the second plurality of unique parameter sets stored in the execution history, updating the execution time for executing the parameterized query with the new parameter set in the execution history based at least in part on the new execution time for executing the parameterized query with the new parameter set; responsive 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 new parameter set into the execution history; Including, 14. The system of claim 13, wherein the iterative operation continues until a count of the second plurality of unique parameter sets stored in the execution history equals a predefined size of the execution history.

16. determining the estimated compilation and execution time for compiling and executing the parameterized query with the input parameter set; 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; Including, determining the estimated execution time for executing the parameterized query with the input parameter set by using the query plan; 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; The system of claim 11 , comprising:

17. 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; measuring a distance between the input parameter set and the first plurality of unique parameter sets or the second plurality of unique parameter sets; identifying a minimum distance among the measured distances; 17. The system of claim 16, comprising:

18. 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; determining a selectivity value for the input parameter set and a selectivity value for the first plurality of unique parameter sets or the second plurality of unique parameter sets; identifying a selectivity value of one of the first plurality of unique parameter sets or the second plurality of unique parameter sets that is closest to the selectivity value of the input parameter set; 17. The system of claim 16, comprising:

19. the query plan is one of a plurality of query plans for the parameterized query stored in the plan cache, the execution history is one of a plurality of execution histories respectively associated with the plurality of query plans, the estimated execution time is one of a plurality of estimated execution times respectively determined for the plurality of query plans, and determining the cache gain includes: determining a minimum estimated execution time among the plurality of estimated execution times; determining a difference between the estimated compilation+execution time and the minimum estimated execution time; The system of claim 11 , comprising:

20. One or more non-transitory computer-readable media having encoded thereon computer-executable instructions for causing one or more processors to perform a method, the method comprising: receiving a parameterized query along with a set of input parameters for the parameterized query, the parameterized query having a query plan stored in a plan cache, the parameterized query being associated with a compilation history comprising compilation+execution times for compiling and executing the parameterized query with a first plurality of unique parameter sets, and the query plan being associated with an execution history comprising execution times for executing the parameterized query with a second plurality of unique parameter sets using the query plan; determining an estimated compilation and 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, said determining said cache gain comprising calculating a difference between said estimated compilation+execution time and said 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; in response to finding that the cache gain is not positive, compiling and executing the parameterized query with the input parameter set; [0023] 1. One or more non-transitory computer-readable media,

Images