Self-adaptive management method and system for database plan cache

Abstract

The invention relates to a self-adaptive management method and system for a database plan cache, and the method comprises the steps: firstly judging whether the plan cache is hit or not after a database receives an SQL statement, and triggering a hard analysis generation plan and caching the plan when the plan cache is not hit; if the cache is hit, further judging whether a corresponding multi-execution plan exists or not; and when only a single execution plan exists, analyzing the relationship between the number distribution of execution tuples and the number of execution times by adopting an execution efficiency algorithm to dynamically judge whether the data is inclined. And when a plurality of execution plans exist, entering a multi-plan cache management stage, selecting the execution plans according to a matching result of the current binding variable predicate selection rate and the cache plan historical selection rate, and generating and caching a new plan when the matching fails. According to the method, the data skew is accurately identified by executing the efficiency algorithm, intelligent matching and multiplexing of plans are realized by means of a multi-plan cache management mechanism, and the overall performance of the database in a data skew scene is improved.

Description

Technical Field

[0001] This invention relates to the field of database plan management technology, and in particular to an adaptive management method and system for database plan caching. Background Technology

[0002] In database management systems, there are two ways to generate a plan when executing an SQL statement: hard parsing and retrieving it from a plan cache. The introduction of plan caching solves the performance problems caused by hard parsing. The cache hit logic manifests as a one-to-one relationship between the SQL statement and the plan, and a many-to-one relationship between bind variables and the plan. This relationship resolves the performance issues caused by hard parsing in bind variable scenarios, thereby improving SQL parsing performance. However, plan caching also has drawbacks, impacting execution performance. For example, in the query statement `SELECT * FROM T WHERE A = ?`, if column A has an index `A_I`, and the bind variable is 1 on the first execution, with a selectivity of 99% for A=1, the optimizer will choose a sequential scan and cache the plan. When the statement is executed again with a bind variable value of 2, and the selectivity of A=2 is 1%, the optimizer should choose an index scan of A_I based on the cost model, but due to a cache hit, it uses a sequential scan, leading to performance degradation. When the SQL statement and data distribution are complex, this performance impact can be catastrophic. The problem arises because cache hits ignore the data distribution; in real-world business scenarios, data distribution is rarely uniform.

[0003] The existing solution uses multiple hard parses to evaluate whether the data distribution in the table is skewed. The general process is as follows: set the number of evaluations, assuming it is 10. If the number of executions is less than the number of evaluations, then a hard parse is performed. That is, the first 10 times will obtain the plan through hard parse. Compare the difference in the cost of the plan within the number of evaluations. Evaluate whether the data is skewed according to a certain algorithm. If the data is skewed, then the plan will be obtained through hard parse in the future. If the data is not skewed, then the plan in the cache will be reused in the future.

[0004] The above approach can alleviate some of the problems, but it has several drawbacks. First, the number of evaluations is limited; the complexity of the business and data makes it difficult to accurately reflect the actual situation using only the first few plans. Second, if skew is identified, subsequent plans in the cache will not be used, thus negating the value of caching. Finally, using cost differences to judge data skew is inaccurate because cost is a relative value, and the optimizer can change the cost by adjusting parameters, making it difficult to reflect the true execution situation. Summary of the Invention

[0005] In view of this, it is necessary to provide an adaptive management method for database plan caching to address the aforementioned shortcomings of the prior art.

[0006] To address the aforementioned problems, in a first aspect, embodiments of the present invention provide an adaptive management method for database plan caching, comprising: The database receives an SQL statement and determines whether the SQL statement hits the plan cache. If the SQL statement hits the plan cache, then determine whether there are multiple execution plans corresponding to the SQL statement; If it is determined that there are no multiple execution plans, an execution performance algorithm is used to determine whether there is data skew by analyzing the relationship between the distribution of the number of execution tuples and the number of executions. If there is no data skew, the current execution plan is reused; otherwise, a hard parse is triggered to generate a new execution plan. If multiple execution plans are determined to exist, the multi-plan cache management phase is entered. Based on the matching result between the predicate selectivity of the bind variables in the current SQL statement and the predicate selectivity of each execution plan in the cache, an execution plan is selected and the SQL statement is executed.

[0007] Preferably, after determining whether the SQL statement hits the plan cache, the method further includes: If the SQL statement does not hit the plan cache, the process enters the hard parsing stage to generate the corresponding execution plan, saves the execution plan, executes the execution plan, and saves the number of execution tuples for this execution.

[0008] Preferably, determining whether multiple execution plans exist for the SQL statement includes: Check the dedicated cache entry corresponding to the SQL statement in the plan cache; Count the number of execution plans stored under the dedicated cache entry; if the count of execution plans is greater than 1, it is determined that there are multiple execution plans corresponding to the SQL statement; If the number of execution plans counted is 1, then it is determined that there are no multiple execution plans corresponding to the SQL statement.

[0009] Preferably, the execution performance algorithm is used to determine whether the data is skewed by analyzing the relationship between the distribution of the number of executed tuples and the number of executions. Specifically, this includes: The number of execution tuples generated by this SQL statement execution is counted to determine the preset data bucket into which the number of execution tuples falls; wherein, the preset data bucket includes a first data bucket, a second data bucket, and a third data bucket, the first data bucket corresponds to the range of the number of execution tuples in [1, 1000], the second data bucket corresponds to the range of the number of execution tuples in (1000, 1000000], and the third data bucket corresponds to the range of the number of execution tuples in (1000000, +∞), and the execution count of the corresponding data bucket is accumulated; Based on the number of executions for each data bucket, data skew is determined when any of the following conditions are met; if none of the following conditions are met, data skew is determined. Condition 1: The number of executed tuples is distributed only in the first and second data buckets, and the number of executions in the two data buckets is equal; Condition 2: The number of executed tuples is distributed only in the second and third data buckets, and the number of executions in the two data buckets is equal; Condition 3: The number of executed tuples is distributed only in the first data bucket and the third data bucket, and the number of executions in the third data bucket is equal to the number of executions in the first data bucket divided by three and rounded up; Condition 4: The number of executed tuples is distributed across all three data buckets, and the sum of the execution counts of the first and third data buckets is not less than the execution count of the second data bucket. The smaller execution count between the first and third data buckets is not less than the larger execution count minus the execution count of the second data bucket, divided by three and rounded up.

[0010] Preferably, the step of reusing the current execution plan if there is no data skew, and triggering a hard parse to generate a new execution plan otherwise, includes: If it is determined that there is no data skew, the SQL statement is executed using the current cached single execution plan, and the number of execution tuples for this execution is saved; If data skew is determined, a hard parse is triggered to parse and optimize the SQL statement, generate a new execution plan and execute it, and save the new execution plan and the number of execution tuples in this execution.

[0011] Preferably, the step of selecting an execution plan and executing the SQL statement based on the matching result of the predicate selectivity of the bind variables in the current SQL statement and the predicate selectivity of each execution plan in the cache includes: Calculate the predicate selectivity of the bind variables in the current SQL statement; wherein, the predicate selectivity is the proportion of the amount of data filtered by the predicate specified by the bind variables in the current SQL statement to the total amount of data in the data table affected by the predicate; Retrieve the predicate selectivity for each execution plan corresponding to the SQL statement in the cache; Match the predicate selectivity of the bind variables in the current SQL statement with the predicate selectivity of each execution plan in the cache; the condition for a successful match is that the ratio of the current predicate selectivity to a certain historical predicate selectivity is within a preset floating range. If a match is found, the corresponding execution plan is reused; If a match fails, a hard parse is triggered to generate a new execution plan, and the new execution plan and its corresponding predicate selectivity are saved to the plan cache.

[0012] Preferably, when generating any execution plan, the predicate selectivity of the execution plan and the bind variables in the current SQL statement associated with it are simultaneously saved to the plan cache.

[0013] In a second aspect, embodiments of the present invention provide an adaptive management system for database plan caching, comprising: The SQL receiving module is used to receive SQL statements from the database and determine whether the SQL statement hits the plan cache. The multi-plan determination module is used to determine whether there are multiple execution plans corresponding to the SQL statement if the SQL statement hits the plan cache. The execution performance module is used to determine whether there are multiple execution plans if it is determined that there are no multiple execution plans. The algorithm analyzes the relationship between the distribution of the number of execution tuples and the number of executions to determine whether there is data skew. If there is no data skew, the current execution plan is reused; otherwise, a hard parse is triggered to generate a new execution plan. The multi-plan caching module is used to select an execution plan and execute the SQL statement if it is determined that there are multiple execution plans. This is done by matching the predicate selectivity of the bind variables in the current SQL statement with the predicate selectivity of each execution plan in the cache.

[0014] Thirdly, the present invention also provides an electronic device, including a memory and a processor, wherein, The memory is used to store programs; The processor, coupled to the memory, is used to execute the program stored in the memory to implement the steps in the adaptive management method for database plan caching as described in the first aspect embodiment of the present invention.

[0015] Fourthly, the present invention also provides a computer-readable storage medium for storing a computer-readable program or instructions, which, when executed by a processor, can implement the steps in the adaptive management method for database plan caching as described in the first aspect embodiment of the present invention.

[0016] The adaptive management method and system for database plan caching provided by this invention has the following advantages compared with the prior art: 1) This invention continuously tracks the number of SQL execution tuples and the number of executions through an execution performance algorithm, and determines data skew by combining it with business frequency, without the need to preset the number of evaluations. This method solves the problem of difficulty in matching the number of evaluations with business needs, accurately identifies data skew, avoids misjudgments caused by a fixed number of evaluations, and ensures the rationality of the execution plan selection.

[0017] 2) This invention employs a multi-plan cache management mechanism based on predicate selectivity. When generating an execution plan, the corresponding predicate selectivity is saved synchronously. Subsequently, plans are reused by matching current and historical selectivity, with a new plan generated only when a match fails. This approach balances data skew handling and cache reuse, avoids continuous hard parsing, reduces performance overhead, maximizes cache value, and effectively improves SQL execution efficiency.

[0018] 3) This invention uses the number of executed tuples (linearly related to the total CPU and I / O consumption during execution) as the core criterion, and determines skewness through data bucket distribution and execution count analysis, avoiding the relativity of cost and parameter interference. This method solves the problem of inaccurate cost evaluation, accurately reflects the actual SQL execution situation, provides reliable support for skewness determination and plan selection, and ensures the stability of adaptive cache management. Attached Figure Description

[0019] Figure 1 Flowchart of the adaptive management method for database plan caching provided by the present invention; Figure 2 This is a schematic diagram of the overall process of the adaptive management method for database plan caching in this invention; Figure 3 This is a histogram showing the distribution of the number of SQL execution tuples provided by the present invention. Figure 4 The execution performance algorithm flowchart provided by this invention; Figure 5 This is a schematic diagram illustrating the correspondence between SQL, execution plan, and predicate selectivity provided by the present invention. Figure 6 This is a structural block diagram of the electronic device provided by the present invention. Detailed Implementation

[0020] To make the objectives, technical solutions, and advantages of the embodiments of the present invention clearer, the technical solutions of the embodiments of the present invention will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of the present invention, not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of the present invention.

[0021] The naming or numbering of steps in the embodiments of the present invention does not mean that the steps in the method flow must be executed in the time / logical order indicated by the naming or numbering. The execution order of the named or numbered process steps can be changed according to the technical purpose to be achieved, as long as the same or similar technical effect can be achieved.

[0022] Figure 1Flowchart of the adaptive management method for database plan caching provided by the present invention; Figure 2 This is a schematic diagram illustrating the overall process of the adaptive management method for database plan caching according to the present invention. (Refer to...) Figure 1 and Figure 2 The adaptive management method for database plan caching includes the following steps: Step S1: The database receives the SQL statement and determines whether the SQL statement hits the plan cache.

[0023] Specifically, the database receives SQL (Structured Query Language) statements submitted externally. It then queries the plan cache to check if a dedicated cache entry exists for the current SQL statement. If such an entry exists, the SQL statement is considered to have hit the plan cache. The plan cache refers to the memory area in the database system used to store the parsed and optimized execution plans of SQL statements. A dedicated cache entry is a storage unit in the plan cache that corresponds one-to-one with a single SQL statement, associated with all execution plans for that SQL statement.

[0024] Step S2: If the SQL statement hits the plan cache, then determine whether there are multiple execution plans corresponding to the SQL statement.

[0025] Specifically, the system locates the dedicated cache entry for the SQL statement in the plan cache. In the caching structure of this invention, the cache entry corresponding to each SQL statement template no longer stores only a single execution plan, but can be associated with multiple execution plans. The system needs to examine the internal structure of this dedicated cache entry to determine the actual number of execution plans it stores.

[0026] The system counts the number of execution plans actually stored under this cache entry. The number of execution plans refers to the number of execution plans corresponding to the current SQL statement stored under this specific cache entry.

[0027] If the number of execution plans counted is greater than 1, it is determined that there are multiple execution plans corresponding to the SQL, and the process proceeds to step S4; if the number of execution plans counted is equal to 1, it is determined that there are no multiple execution plans, and the process proceeds to step S3.

[0028] If the SQL statement does not hit the plan cache, the hard parsing phase is entered to generate the corresponding execution plan. The execution plan is saved to the dedicated cache entry corresponding to the current SQL, the execution plan is executed, and the number of execution tuples in this execution is saved.

[0029] Step S3: If it is determined that there are no multiple execution plans, the execution performance algorithm is used to determine whether the data is skewed by analyzing the relationship between the distribution of the number of execution tuples and the number of executions. If the data is not skewed, the current execution plan is reused; otherwise, a hard parse is triggered to generate a new execution plan.

[0030] Traditional databases use a fixed number of evaluations or static cost models to determine data skew, which has drawbacks such as difficulty in setting the number of evaluations, cache invalidation after skew, and inaccurate cost assessment. Their fundamental flaw lies in their failure to continuously track the actual execution load characteristics and their neglect of the actual performance differences caused by different bind variable values.

[0031] Therefore, this invention introduces an execution performance algorithm. This algorithm uses the total CPU and I / O consumption during the execution phase to reflect the degree of matching between the plan and bind variables. Its core idea is that, with the plan and data unchanged, the CPU and I / O consumption during the execution phase remains stable; if changes in bind variable values ​​cause changes in execution performance, a new plan needs to be generated. This algorithm incorporates the concept of business frequency into its judgment logic, that is, while considering changes in execution performance, it also takes into account the frequency of occurrence of the SQL execution patterns that cause these changes. By analyzing the relationship between the distribution of the number of executed tuples and the number of executions, it achieves continuous dynamic evaluation of data skew, thereby maximizing the value of the plan cache while ensuring the accuracy of the judgment.

[0032] Specifically, when step S2 determines that there are no multiple execution plans corresponding to the SQL, the process proceeds to step S3, where an execution performance algorithm is used. First, count the number of executed tuples generated by the SQL statement execution to determine the preset data buckets into which the executed tuples fall. These preset data buckets include bucket 1, bucket 2, and bucket 3. The number of executed tuples represents the number of rows in the dataset processed after the SQL execution.

[0033] The execution count of the corresponding data bucket is accumulated, and the histogram of the distribution of the number of executed tuples is updated. The structure of the histogram of the distribution of the number of executed tuples is as follows. Figure 3 As shown, Figure 3 The horizontal axis represents the three data buckets, and the vertical axis represents the number of times each bucket was executed. The number of executed tuples in the first data bucket ranges from [1, 1000], the number of executed tuples in the second data bucket ranges from (1000, 1000000], and the number of executed tuples in the third data bucket ranges from (1000000, +∞).

[0034] Based on the number of executions for each data bucket, by Figure 4 The process matches four conditions sequentially: Condition 1: The number of executed tuples is distributed only in the first data bucket (bucket1) and the second data bucket (bucket2), and the number of executions in the two data buckets is equal; the formula for Condition 1 is: COUNT(bucket1) = COUNT(bucket2) Condition 2: The number of executed tuples is distributed only in the second data bucket (bucket2) and the third data bucket (bucket3), and the number of executions in the two data buckets is equal; the formula for condition 2 is: COUNT(bucket2) = COUNT(bucket3) Condition 3: The number of executed tuples is distributed only in the first data bucket (bucket1) and the third data bucket (bucket3), and the number of executions in the third data bucket (bucket3) is equal to the number of executions in the first data bucket divided by three and rounded up; the formula for condition 3 is: COUNT(bucket3)=CEIL(COUNT(bucket1) / 3) Condition 4: The number of executed tuples is distributed across all three data buckets, and the sum of the execution counts of the first data bucket (bucket1) and the third data bucket (bucket3) is not less than the execution count of the second data bucket (bucket2). The smaller execution count between the first data bucket (bucket1) and the third data bucket (bucket3) is not less than the larger execution count minus the execution count of the second data bucket (bucket2), divided by three and rounded up. The formula for Condition 4 is: COUNT(bucket1)+COUNT(bucket3)≥COUNT(bucket2) MIN(bucket1,bucket3)≥CEIL((MAX(bucket1,bucket3) COUNT(bucket2) / 3) In the formula, COUNT represents the number of executions that fall into the corresponding histogram bucket; MIN(bucket1,bucket3) represents the minimum number of executions for bucket1 and bucket3; MAX(bucket1,bucket3) represents the maximum number of executions for bucket1 and bucket3; and CEIL represents rounding up the value.

[0035] If any one condition is met, the data is determined to be skewed; if none of the conditions are met, the data is determined to be unskewed.

[0036] If the data is not skewed, the current cached single execution plan is reused to execute the SQL, and the number of execution tuples for this execution is saved. If the data is skewed, a hard parse is triggered to parse and optimize the SQL, generate a new execution plan and execute it, while saving the new execution plan and the number of execution tuples for this execution, and then proceeding to the multi-plan cache management stage.

[0037] This invention utilizes an execution performance algorithm to continuously track the number of SQL execution tuples and the number of executions, and determines data skew based on business frequency, eliminating the need for pre-setting the number of evaluations. This method solves the problem of difficulty in matching the number of evaluations to business needs, accurately identifies data skew, avoids misjudgments caused by fixed numbers, and ensures the rationality of execution plan selection.

[0038] This invention uses the number of executed tuples (linearly related to the total CPU and I / O consumption during execution) as its core criterion. It analyzes data bucket distribution and execution counts to determine skewness, avoiding the relativity of cost and parameter interference. This method solves the problem of inaccurate cost assessment, accurately reflects the actual SQL execution situation, provides reliable support for skewness determination and plan selection, and ensures the stability of adaptive cache management.

[0039] Step S4: If it is determined that there are multiple execution plans, then enter the multi-plan cache management stage. Based on the matching result of the predicate selectivity of the bind variables in the current SQL statement and the predicate selectivity of each execution plan in the cache, select the execution plan and execute the SQL statement.

[0040] It's important to note that traditional plan caching management assigns one plan to each SQL statement. As long as the SQL statement hits the cache, the plan is retrieved and executed. However, when the system identifies data skew and generates multiple execution plans, a core problem arises: how to select the most suitable execution plan for the current SQL statement from among these plans. Blindly selecting a plan or continuously performing hard parses will both lead to performance penalties.

[0041] To address this issue, this invention introduces predicate selection rate. By introducing predicate selection rate as the matching criterion during the multi-plan cache management phase, the selection problem when multiple execution plans coexist is solved, ensuring that the system can still maintain efficient operation in complex data distribution environments.

[0042] After step S2 determines that there are multiple execution plans corresponding to the SQL statement, the process proceeds to the multi-plan cache management stage in step S4. This stage includes the plan saving process and the plan matching process. The specific process of saving the plan is as follows: The plan saving process is triggered by two conditions: first, the execution performance algorithm is satisfied; second, the selectivity of the bind variables fails to match. Compared to traditional cache management, this process adds a selectivity calculation and saving step. Specifically, when a new execution plan is generated through hard parsing, the system synchronously calculates the predicate selectivity of the current bind variables, and then saves the execution plan and its corresponding predicate selectivity as a complete unit to the plan cache. This establishes a cache structure where one SQL statement corresponds to multiple plans and selectivity combinations.

[0043] The matching plan process is as follows: The plan matching process is triggered when multiple plans already exist, and its flow is as follows: Calculate the predicate selectivity of bind variables in the current SQL statement. Bind variables are parameter placeholders in an SQL statement used to replace specific query values, allowing SQL statements with the same structure but different parameters to be associated with the same execution plan, thus achieving plan caching reuse. The predicate selectivity is the proportion of data filtered by the predicate specified by the bind variable in the current SQL statement, relative to the total amount of data in the table affected by that predicate.

[0044] Retrieve the predicate selectivity for each execution plan corresponding to the SQL statement in the cache; retrieve the selectivity of the bind variables corresponding to the plans in the cache, denoted as {S1, S2...Si...Sn}; Figure 5 The relationship shown is that a single SQL statement, such as SQL1, corresponds to multiple execution plans, such as Plan1 to PlanN. Each execution plan corresponds to a predicate selectivity, such as Plan1 corresponding to S1, Plan2 corresponding to S2, and PlanN corresponding to SN.

[0045] Set the predicate selectivity S of the bind variables in the current SQL statement. i Match the predicate selectivity S0 corresponding to each execution plan in the cache; where a successful match is achieved when the ratio of the current predicate selectivity to a certain historical predicate selectivity is within a preset floating range; the matching condition is as follows: 0.9*S i ≤S0≤1.1*S i If a match is found, the corresponding execution plan is reused; If a match fails, a hard parse is triggered to generate a new execution plan, and the new execution plan and its corresponding predicate selectivity are saved to the plan cache.

[0046] At the same time, when generating any execution plan, the predicate selectivity of the execution plan and the bind variables in the current SQL statement associated with it is simultaneously saved to the plan cache.

[0047] This invention employs a multi-plan cache management mechanism based on predicate selectivity. When generating an execution plan, the corresponding predicate selectivity is saved simultaneously. The plan is then reused by matching the current and historical selectivity, with a new plan generated only when a match fails. This approach balances data skew handling and cache reuse, avoids continuous hard parsing, reduces performance overhead, maximizes cache value, and effectively improves SQL execution efficiency.

[0048] In some embodiments of this application, an adaptive management system for database schedule caching is provided, the system comprising: The SQL receiving module is used to receive SQL statements from the database and determine whether the SQL statement hits the plan cache. The multi-plan determination module is used to determine whether there are multiple execution plans corresponding to the SQL statement if the SQL statement hits the plan cache. The execution performance module is used to determine whether there are multiple execution plans if it is determined that there are no multiple execution plans. The algorithm analyzes the relationship between the distribution of the number of execution tuples and the number of executions to determine whether there is data skew. If there is no data skew, the current execution plan is reused; otherwise, a hard parse is triggered to generate a new execution plan. The multi-plan caching module is used to select an execution plan and execute the SQL statement if it is determined that there are multiple execution plans. This is done by matching the predicate selectivity of the bind variables in the current SQL statement with the predicate selectivity of each execution plan in the cache.

[0049] The adaptive management system for database plan caching provided by the present invention is used to execute the adaptive management method for database plan caching provided in the foregoing embodiments. The adaptive management method for database plan caching has been described in detail in the foregoing embodiments, and will not be repeated here.

[0050] Figure 6 A structural block diagram of the electronic device provided by the present invention, such as Figure 6 As shown, the present invention also provides an electronic device 600, which can be a mobile terminal, desktop computer, laptop, handheld computer, server, or other computing device. The electronic device 600 includes a processor 601 and a memory 602, wherein the memory 602 stores an adaptive management program 603 for a database plan cache.

[0051] In some embodiments, memory 602 may be an internal storage unit of a computer device, such as a hard disk or memory. In other embodiments, memory 602 may be an external storage device of a computer device, such as a plug-in hard disk, smart media card (SMC), secure digital card (SD), flash card, etc. Further, memory 602 may include both internal and external storage units of the computer device. Memory 602 is used to store application software and various types of data installed on the computer device, such as program code installed on the computer device. Memory 602 can also be used to temporarily store data that has been output or will be output. In one embodiment, when the adaptive management program 603 for the database plan cache is executed by processor 601, the following steps are implemented: The database receives an SQL statement and determines whether the SQL statement hits the plan cache. If the SQL statement hits the plan cache, then determine whether there are multiple execution plans corresponding to the SQL statement; If it is determined that there are no multiple execution plans, an execution performance algorithm is used to determine whether there is data skew by analyzing the relationship between the distribution of the number of execution tuples and the number of executions. If there is no data skew, the current execution plan is reused; otherwise, a hard parse is triggered to generate a new execution plan. If multiple execution plans are determined to exist, the multi-plan cache management phase is entered. Based on the matching result between the predicate selectivity of the bind variables in the current SQL statement and the predicate selectivity of each execution plan in the cache, an execution plan is selected and the SQL statement is executed.

[0052] In some embodiments, processor 601 may be a central processing unit (CPU), microprocessor, or other data processing chip, used to run program code stored in memory 602 or process data, such as executing an adaptive management program for database schedule caching.

[0053] This embodiment also provides a computer-readable storage medium storing an adaptive management program for a database plan cache. When executed by a processor, the adaptive management program for the database plan cache performs the following steps: The database receives an SQL statement and determines whether the SQL statement hits the plan cache. If the SQL statement hits the plan cache, then determine whether there are multiple execution plans corresponding to the SQL statement; If it is determined that there are no multiple execution plans, an execution performance algorithm is used to determine whether there is data skew by analyzing the relationship between the distribution of the number of execution tuples and the number of executions. If there is no data skew, the current execution plan is reused; otherwise, a hard parse is triggered to generate a new execution plan. If multiple execution plans are determined to exist, the multi-plan cache management phase is entered. Based on the matching result between the predicate selectivity of the bind variables in the current SQL statement and the predicate selectivity of each execution plan in the cache, an execution plan is selected and the SQL statement is executed.

[0054] The embodiments described above are merely illustrative of several implementations of the present invention, and while the descriptions are specific and detailed, they should not be construed as limiting the scope of the invention. It should be noted that those skilled in the art can make various modifications and improvements without departing from the concept of the present invention, and these modifications and improvements all fall within the scope of protection of the present invention. Therefore, the scope of protection of the present invention should be determined by the appended claims.

[0055] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of the present invention, and not to limit them; although the present invention has been described in detail with reference to the foregoing embodiments, those skilled in the art should understand that modifications can still be made to the technical solutions described in the foregoing embodiments, or equivalent substitutions can be made to some of the technical features; and these modifications or substitutions do not cause the essence of the corresponding technical solutions to deviate from the spirit and scope of the technical solutions of the embodiments of the present invention.

Claims

1. A method for adaptive management of database plan cache, the method comprising: The method comprises the following steps: The database receives an SQL statement, and determines whether the SQL statement hits a plan cache; If the SQL statement hits the plan cache, it is determined whether there are multiple execution plans corresponding to the SQL statement; If it is determined that there are no multiple execution plans, an execution performance algorithm is used to determine whether data is skewed by analyzing the relationship between the number of execution tuples and the number of executions; if the data is not skewed, the current execution plan is reused, otherwise a new execution plan is generated by triggering hard analysis; If it is determined that there are multiple execution plans, a multi-plan cache management stage is entered, and an execution plan is selected according to the matching result of the predicate selection rate of the bound variable in the current SQL statement and the predicate selection rate corresponding to each execution plan in the cache, and the SQL statement is executed.

2. The adaptive management method of database plan cache according to claim 1, characterized in that, After determining whether the SQL statement hits the plan cache, the method further comprises the following steps: If the SQL statement does not hit the plan cache, a corresponding execution plan is generated in a hard analysis stage, the execution plan is saved, the execution plan is executed, and the number of execution tuples of this execution is saved.

3. The adaptive management of database plan cache method of claim 1, wherein, The determination of whether there are multiple execution plans corresponding to the SQL statement comprises the following steps: Checking the exclusive cache entry corresponding to the SQL statement in the plan cache; Counting the number of stored execution plans under the exclusive cache entry; if the number of counted execution plans is greater than 1, it is determined that there are multiple execution plans corresponding to the SQL statement; If the number of counted execution plans is equal to 1, it is determined that there are no multiple execution plans corresponding to the SQL statement.

4. The adaptive management of database plan cache method of claim 1, wherein, The execution performance algorithm is used to determine whether the data is skewed by analyzing the relationship between the number of execution tuples and the number of executions, which specifically comprises the following steps: Counting the number of execution tuples generated by executing the SQL statement, and determining the preset data bucket into which the number of execution tuples falls; wherein the preset data bucket comprises a first data bucket, a second data bucket and a third data bucket, the first data bucket corresponds to an execution tuple number range of [1, 1000], the second data bucket corresponds to an execution tuple number range of (1000, 1000000], and the third data bucket corresponds to an execution tuple number range of (1000000, +∞), and the number of executions corresponding to the data bucket is accumulated; Based on the number of executions of each data bucket, it is determined that the data is skewed when any of the following conditions is met; if none of the following conditions is met, it is determined that the data is not skewed; Condition 1: the number of execution tuples is only distributed in the first data bucket and the second data bucket, and the number of executions of the two data buckets is equal; Condition 2: the number of execution tuples is only distributed in the second data bucket and the third data bucket, and the number of executions of the two data buckets is equal; Condition 3: the number of execution tuples is only distributed in the first data bucket and the third data bucket, and the number of executions of the third data bucket is equal to the number of executions of the first data bucket divided by three and rounded up; Condition 4: the number of execution tuples is distributed in all three data buckets, and the sum of the number of executions of the first data bucket and the third data bucket is not less than the number of executions of the second data bucket, and the smaller value of the number of executions of the first data bucket and the third data bucket is not less than the larger value minus the number of executions of the second data bucket and divided by three and rounded up.

5. The adaptive management of database plan cache method of claim 1, wherein, If the data is determined to be not skewed, the current execution plan is reused to execute the SQL statement, and the number of execution tuples of this execution is saved. If the data is determined to be not skewed, the current execution plan is reused to execute the SQL statement, and the number of execution tuples of this execution is saved. If the data is determined to be skewed, hard parsing is triggered to parse and optimize the SQL statement, a new execution plan is generated and executed, and the new execution plan and the number of execution tuples of this execution are saved.

6. The adaptive management of database plan cache method of claim 1, wherein, The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps:

7. The adaptive management of database plan cache method of claim 1, wherein, The method comprises the following steps:

8. An adaptive management system of database plan cache for the adaptive management method of database plan cache according to any one of claims 1-7, characterized in that, The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: characterized in that The method comprises the following steps: The method comprises the following steps: The method comprises the following steps:

10. A computer-readable storage medium, characterized in that, The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The method comprises the following steps: The

Images