A method and system for use and management of database logical plan cache

Abstract

The application discloses a kind of database logic plan cache usage and management method and system, belong to database optimization technical field, according to resource occupation and execution overhead, TOP-SQL and non-TOP-SQL are divided, for non-TOP-SQL, using the logical plan of existing PlanCache in template cache mode;For TOP-SQL, establish and maintain a plan manager for it, cache multiple logical plans, select optimal logical cache plan execution according to actual parameters;The implementation of the method includes: Top-SQL determination, logical plan capture, logical plan selection and reuse, logical plan evolution and management.The application realizes the cache of database logical plan, and implements different cache logical plan reuse and management strategies for different weight SQL statements, optimizes the reuse mechanism of cache logical plan, and reduces system resource loss.

Description

Technical Field

[0001] This invention relates to the field of database optimization technology, specifically to a method and system for using and managing database logical plan caching. Background Technology

[0002] In databases, the execution of SQL statements mainly includes several stages: parsing, optimization, and execution. The optimization stage typically consumes a significant amount of time, generating the execution plan. To improve SQL statement execution efficiency, most databases have implemented logical plan caching. By caching the plan, when the same SQL statement is executed multiple times, the cached plan can be reused directly without further optimization, effectively reducing query response time, database load, and costs. However, existing template-based logical plan reuse mechanisms cannot effectively handle situations where constants in the SQL statement change, leading to resource waste and reduced system performance. Therefore, further solutions and optimizations are needed to address this issue. Summary of the Invention

[0003] The technical objective of this invention is to address the above-mentioned shortcomings by providing a method and system for using and managing database logical plan caching. This method implements database logical plan caching and applies different cached logical plan reuse and management strategies to SQL statements with different weights, thereby optimizing the cached logical plan reuse mechanism and reducing system resource consumption.

[0004] The technical solution adopted by this invention to solve its technical problem is:

[0005] A method for using and managing database logical plan caching is proposed. Based on resource consumption and execution overhead, TOP-SQL and non-TOP-SQL are divided. For non-TOP-SQL, logical plans in the existing PlanCache template caching mode are used. For TOP-SQL, a plan manager is established and maintained to cache multiple logical plans. The optimal logical plan cache plan is selected for execution based on actual parameters. This method realizes the reuse and management of different logical plans, thereby greatly improving the degree of plan reuse and the performance of the plan reuse method.

[0006] The implementation of this method includes:

[0007] Top-SQL determination is used to support users in defining which SQL statements belong to Top-SQL;

[0008] Logical plan capture is used to capture logical plans after SQL is first entered into the database and store them in the PlanCache or Plan Manager;

[0009] Logical plan selection and reuse is used for the selection and reuse calculation of specific cache plans after SQL hits PlanCache or Plan Manager;

[0010] The evolution and management of logical plans are used to manage the addition and deletion of K logical plans in the plan manager.

[0011] By assigning different weights to SQL statements and implementing different management methods based on these weights, effective reuse of cached logical plans can be achieved. This addresses the problem of the current database logical plan caching template being too simplistic and unable to reuse cached logical plans effectively.

[0012] Preferably, the Top-SQL determination engine allows users to assign weights to SQL based on multiple rules. SQLs with weights exceeding user-defined thresholds are marked as Top-SQL. Top-SQL logical plan management uses a plan manager, while non-Top-SQL logical plan management uses a PlanCache mechanism.

[0013] Preferably, the user assigns weights to SQL based on multiple rules, including SQL execution time, SQL execution frequency, and SQL resource overhead.

[0014] Preferably, the process of capturing the logical plan is as follows:

[0015] Step S1.1: Upon receiving a raw SQL statement, it is templated to form a template SQL statement.

[0016] Step S1.2: Determine whether the current template SQL belongs to the TOP-SQL query statement based on the rule engine;

[0017] Step S1.3: If the template SQL is not Top-SQL, use the template SQL as the key to query whether it exists in the Plan Cache. If it does not exist, proceed to step S1.4; otherwise, perform the selection and reuse of the corresponding logical plan. If the template SQL is Top-SQL, use the template SQL as the key to query whether it exists in the Plan Manager. If it does not exist, proceed to step S1.5; otherwise, perform the selection and reuse of the corresponding logical plan.

[0018] Step S1.4: This template SQL is entering the database for the first time. The database optimizer directly selects the optimal logical plan using the cost model, and then caches the logical plan in the Plan Cache using the template SQL as the key.

[0019] Step S1.5: This template SQL is entering the database for the first time. The database optimizer directly selects the optimal logical plan using the cost model. Then, using the template SQL as the key, the logical plan and the corresponding cost evaluation number are cached in the plan manager.

[0020] Preferably, the selection and reuse of the logical plan,

[0021] The template SQL is not a Top-SQL. The template SQL is used as a key to query whether it exists in the PlanCache. If it exists, for the template SQL, the corresponding cache plan in the PlanCache is used and the corresponding parameters are replaced, and then it is output to the executor for execution.

[0022] The template SQL is a Top-SQL. The template SQL is used as a key to query whether it exists in the plan manager. If it exists, for that template SQL, the cost is recalculated for the corresponding cached plans in the plan manager using the current parameters. The cost calculation process is serialized. If the difference between the latest calculated cost and the cached cost is within the user-defined range, the latest cost replaces the cached cost evaluation number, and the hit count of the corresponding cached logical plan is increased by 1. Then, the current cached plan is directly selected for a hit, and the corresponding parameters are replaced, before being output to the executor for execution. If all cached logical plans in the plan manager cannot be hit, the evolution and management of the logical plan are executed.

[0023] Furthermore, the evolution and management implementation process of the logical plan is as follows:

[0024] Step S3.1: In the plan manager, a template SQL can cache a maximum of K logical plans and their corresponding cost evaluation numbers, ensuring that all logical plans of each template SQL are sorted in ascending order. K can be user-defined.

[0025] Step S3.2: Input the template SQL and corresponding parameters into the database optimizer to select the optimal logical plan, and output it to the executor for execution; then periodically store the optimal logical plan and cost evaluation data into the plan manager, and jump to step S3.3;

[0026] Step S3.3: If the number of cached plans corresponding to the current template SQL in the plan manager is less than K, then the optimal logical plan and cost evaluation number in step S3.2 are directly stored; otherwise, the cached logical plan with the lowest hit frequency is deleted, and the optimal logical plan and cost evaluation number in step S3.2 are stored.

[0027] Preferably, the Plan Cache data structure includes:

[0028] Key: SQL string; Value: Logical plan;

[0029] The plan manager's data structure includes:

[0030] Key: SQL string; Value1: Logical plan; Value2: Cost evaluation number (float); Value3: Cache hit count (int).

[0031] This invention also claims protection for a database logical plan caching usage and management system, including a Top-SQL determination module, a logical plan capture module, a logical plan selection and reuse module, and a logical plan evolution and management module.

[0032] The Top-SQL determination module allows users to define which SQL statements belong to Top-SQL.

[0033] The logical plan capture module is used to capture the logical plan after the SQL enters the database for the first time and store it in the PlanCache or Plan Manager;

[0034] The logical plan selection and reuse module is used for the selection and reuse calculation of specific cache plans after SQL hits PlanCache or Plan Manager;

[0035] The logical plan evolution and management module is used to manage the addition and deletion of K logical plans in the plan manager;

[0036] This system implements the aforementioned methods for using and managing database logical plan caching.

[0037] The present invention also claims a device for using and managing a database logical plan cache, comprising: at least one memory and at least one processor;

[0038] The at least one memory is used to store a machine-readable program;

[0039] The at least one processor is used to call the machine-readable program to implement the above-described method for using and managing the database logical plan cache.

[0040] The present invention also claims a computer-readable medium storing computer instructions that, when executed by a processor, implement the above-described method for using and managing a database logical plan cache.

[0041] Compared with the prior art, the database logical plan cache usage and management method and system of the present invention have the following advantages:

[0042] This method for using and managing database logical plan caching not only enables caching of logical plans under the database management system, but also assigns weights to different SQL statements to implement different SQL management and usage methods, thereby further improving the reusability of cached logical plans and reducing system resource consumption. Attached Figure Description

[0043] Figure 1 This is a flowchart illustrating the method for using and managing a database logical plan cache according to an embodiment of the present invention. Detailed Implementation

[0044] The present invention will be further described below with reference to specific embodiments.

[0045] This invention provides a method for using and managing database logical plan caching. It determines the weight of SQL statements and categorizes them into TOP-SQL and non-TOP-SQL based on resource consumption and execution overhead. For non-TOP-SQL, the logical plan using the template caching mode in the existing PlanCache is used. For TOP-SQL, a plan manager is established and maintained to cache multiple logical plans, and the optimal cached logical plan is selected for execution based on actual parameters. This achieves different methods of logical plan reuse and management, thereby significantly improving the degree of plan reuse and enhancing performance.

[0046] The implementation of this method includes:

[0047] Top-SQL determination is used to support users in defining which SQL statements belong to Top-SQL;

[0048] Logical plan capture is used to capture logical plans after SQL is first entered into the database and store them in the PlanCache or Plan Manager;

[0049] Logical plan selection and reuse is used for the selection and reuse calculation of specific cache plans after SQL hits PlanCache or Plan Manager;

[0050] The evolution and management of logical plans are used to manage the addition and deletion of K logical plans in the plan manager.

[0051] The important data structures used in this method are as follows:

[0052] Plan Cache data structure:

[0053] SQL string Logical Plan

[0054] Plan Manager Data Structure:

[0055] SQL string Logical Plan Cost assessment number float Cache hit count (int)

[0056] Combination Figure 1 As shown, the specific implementation process is as follows:

[0057] 1. Top-SQL determination:

[0058] The TopSQL engine allows users to assign weights to SQL queries based on multiple rules, such as SQL execution time, SQL execution frequency, and SQL resource overhead. Query queries with weights exceeding user-defined thresholds are marked as TOP-SQL. Top-SQL logical plan management uses a plan manager, while non-Top-SQL logical plan management uses a PlanCache mechanism.

[0059] 2. Logical plan capture:

[0060] Step S1.1: Upon receiving a raw SQL statement, it is templated to form a template SQL statement.

[0061] Step S1.2: Determine whether the current template SQL belongs to the TOP-SQL query statement based on the rule engine;

[0062] Step S1.3: If the template SQL is not a Top-SQL, use the template SQL as the key to query whether it exists in the Plan Cache. If it does not exist, proceed to step S1.4; otherwise, proceed to step S2.1. If the template SQL is a Top-SQL, use the template SQL as the key to query whether it exists in the Plan Manager. If it does not exist, proceed to step S1.5; otherwise, proceed to step S2.2.

[0063] Step S1.4: This template SQL is entering the database for the first time. The database optimizer directly selects the optimal logical plan using the cost model, and then caches the logical plan in the Plan Cache using the template SQL as the key.

[0064] Step S1.5: This template SQL is entering the database for the first time. The database optimizer directly selects the optimal logical plan using the cost model. Then, using the template SQL as the key, the logical plan and the corresponding cost evaluation number are cached in the plan manager.

[0065] 3. Selection and reuse of logical plans:

[0066] Step S2.1: For this template SQL, use the corresponding cache plan in the Plan Cache and replace the corresponding parameters, then output it to the executor for execution;

[0067] Step S2.2: For this template SQL, recalculate the cost of multiple cached plans in the plan manager by substituting the current parameters. The cost calculation process is serialized. If the difference between the latest calculated cost and the cached cost is within the user-defined range, replace the cached cost evaluation number with the latest cost, and increase the hit count of the corresponding cached logical plan by 1. Then, directly select the current cached plan to hit and replace the corresponding parameters, and output it to the executor for execution. If all cached logical plans in the plan manager cannot be hit, proceed to step S3.2.

[0068] 4. Evolution and Management of Logical Plans:

[0069] Step S3.1: In the plan manager, a template SQL can cache a maximum of K logical plans and their corresponding cost evaluation numbers, ensuring that all logical plans of each template SQL are sorted in ascending order. K can be user-defined.

[0070] Step S3.2: Input the template SQL and corresponding parameters into the database optimizer to select the optimal logical plan, and output it to the executor for execution; then periodically store the optimal logical plan and cost evaluation data into the plan manager, and jump to step S3.3;

[0071] Step S3.3: If the number of cached plans corresponding to the current template SQL in the plan manager is less than K, then the optimal logical plan and cost evaluation number in step S3.2 are directly stored; otherwise, the cached logical plan with the lowest hit frequency is deleted, and the optimal logical plan and cost evaluation number in step S3.2 are stored.

[0072] This invention also provides a database logical plan cache usage and management system, including a Top-SQL determination module, a logical plan capture module, a logical plan selection and reuse module, and a logical plan evolution and management module.

[0073] The Top-SQL determination module allows users to define which SQL statements belong to Top-SQL.

[0074] The logical plan capture module is used to capture the logical plan after the SQL enters the database for the first time and store it in the PlanCache or Plan Manager;

[0075] The logical plan selection and reuse module is used for the selection and reuse calculation of specific cache plans after SQL hits PlanCache or Plan Manager;

[0076] The logical plan evolution and management module is used to manage the addition and deletion of K logical plans in the plan manager;

[0077] This system implements the database logical plan cache usage and management method described in the above embodiments.

[0078] Top-SQL determination:

[0079] The TopSQL engine allows users to assign weights to SQL queries based on multiple rules, such as SQL execution time, SQL execution frequency, and SQL resource overhead. Query queries with weights exceeding user-defined thresholds are marked as TOP-SQL. Top-SQL logical plan management uses a plan manager, while non-Top-SQL logical plan management uses a PlanCache mechanism.

[0080] Logical plan capture:

[0081] Step S1.1: Upon receiving a raw SQL statement, it is templated to form a template SQL statement.

[0082] Step S1.2: Determine whether the current template SQL belongs to the TOP-SQL query statement based on the rule engine;

[0083] Step S1.3: If the template SQL is not a Top-SQL, use the template SQL as the key to query whether it exists in the Plan Cache. If it does not exist, proceed to step S1.4; otherwise, proceed to step S2.1. If the template SQL is a Top-SQL, use the template SQL as the key to query whether it exists in the Plan Manager. If it does not exist, proceed to step S1.5; otherwise, proceed to step S2.2.

[0084] Step S1.4: This template SQL is entering the database for the first time. The database optimizer directly selects the optimal logical plan using the cost model, and then caches the logical plan in the Plan Cache using the template SQL as the key.

[0085] Step S1.5: This template SQL is entering the database for the first time. The database optimizer directly selects the optimal logical plan using the cost model. Then, using the template SQL as the key, the logical plan and the corresponding cost evaluation number are cached in the plan manager.

[0086] Selection and reuse of logical plans:

[0087] Step S2.1: For this template SQL, use the corresponding cache plan in the Plan Cache and replace the corresponding parameters, then output it to the executor for execution;

[0088] Step S2.2: For this template SQL, recalculate the cost of multiple cached plans in the plan manager by substituting the current parameters. The cost calculation process is serialized. If the difference between the latest calculated cost and the cached cost is within the user-defined range, replace the cached cost evaluation number with the latest cost, and increase the hit count of the corresponding cached logical plan by 1. Then, directly select the current cached plan to hit and replace the corresponding parameters, and output it to the executor for execution. If all cached logical plans in the plan manager cannot be hit, proceed to step S3.2.

[0089] Evolution and Management of Logical Plans:

[0090] Step S3.1: In the plan manager, a template SQL can cache a maximum of K logical plans and their corresponding cost evaluation numbers, ensuring that all logical plans of each template SQL are sorted in ascending order. K can be user-defined.

[0091] Step S3.2: Input the template SQL and corresponding parameters into the database optimizer to select the optimal logical plan, and output it to the executor for execution; then periodically store the optimal logical plan and cost evaluation data into the plan manager, and jump to step S3.3;

[0092] Step S3.3: If the number of cached plans corresponding to the current template SQL in the plan manager is less than K, then the optimal logical plan and cost evaluation number in step S3.2 are directly stored; otherwise, the cached logical plan with the lowest hit frequency is deleted, and the optimal logical plan and cost evaluation number in step S3.2 are stored.

[0093] This invention also provides a device for using and managing a database logical plan cache, comprising: at least one memory and at least one processor;

[0094] The at least one memory is used to store a machine-readable program;

[0095] The at least one processor is used to call the machine-readable program to implement the database logical plan cache usage and management method described in the above embodiments.

[0096] This invention also provides a computer-readable medium storing computer instructions, which, when executed by a processor, implement the database logical plan cache usage and management method described in the above embodiments. Specifically, a system or apparatus equipped with a storage medium storing software program code that implements the functions of any of the above embodiments, and enabling the computer (or CPU or MPU) of the system or apparatus to read and execute the program code stored in the storage medium.

[0097] In this case, the program code read from the storage medium can itself implement the function of any of the above embodiments, and therefore the program code and the storage medium storing the program code constitute part of the present invention.

[0098] Examples of storage media used to provide program code include floppy disks, hard disks, magneto-optical disks, optical disks (such as CD-ROM, CD-R, CD-RW, DVD-ROM, DVD-RAM, DVD-RW, DVD+RW), magnetic tapes, non-volatile memory cards, and ROMs. Alternatively, program code can be downloaded from a server computer via a communication network.

[0099] Furthermore, it should be clear that not only can the program code read by the computer be executed, but also the operating system or other components operating on the computer can be instructed based on the program code to perform some or all of the actual operations, thereby realizing the function of any of the embodiments described above.

[0100] Furthermore, it is understood that the program code read from the storage medium is written to the memory set in the expansion board inserted into the computer or to the memory set in the expansion unit connected to the computer. Then, based on the instructions of the program code, the CPU or other components installed on the expansion board or expansion unit execute some and all of the actual operations, thereby realizing the function of any of the embodiments described above.

[0101] The present invention has been shown and described in detail above with reference to the accompanying drawings and preferred embodiments. However, the present invention is not limited to these disclosed embodiments. Based on the above embodiments, those skilled in the art will know that more embodiments of the present invention can be obtained by combining the code review methods in the different embodiments. These embodiments are also within the protection scope of the present invention.

Claims

1. A method for using and managing database logical plan caching, characterized in that, Based on resource consumption and execution overhead, TOP-SQL and non-TOP-SQL are divided. For non-TOP-SQL, the logical plan of the template caching mode in the existing PlanCache is used. For TOP-SQL, a plan manager is created and maintained to cache multiple logical plans. The optimal logical cache plan is selected for execution based on the actual parameters. The implementation of this method includes: Top-SQL determination is used to support users in defining which SQL statements belong to Top-SQL; Logical plan capture is used to capture logical plans after SQL is first entered into the database and store them in the PlanCache or Plan Manager; Logical plan selection and reuse is used for the selection and reuse calculation of specific cache plans after SQL hits PlanCache or Plan Manager; The evolution and management of logical plans, used for adding and deleting K logical plans in the plan manager; The Top-SQL determination engine allows users to assign weights to SQL statements based on multiple rules. SQL statements with weights exceeding user-defined thresholds are marked as Top-SQL statements. Top-SQL logical plan management uses a plan manager, while non-Top-SQL logical plan management uses a PlanCache mechanism. The user assigns weights to SQL statements based on multiple rules, including SQL execution time, SQL execution frequency, and SQL resource overhead.

2. The method for using and managing a database logical plan cache according to claim 1, characterized in that, The process of capturing the logical plan is as follows: Step S1.1: Upon receiving a raw SQL statement, it is templated to form a template SQL statement. Step S1.2: Determine whether the current template SQL belongs to a TOP-SQL query statement based on the rule engine; Step S1.3: If the template SQL is not Top-SQL, use the template SQL as the key to query whether it exists in the PlanCache. If it does not exist, proceed to step S1.4; otherwise, perform the selection and reuse of the corresponding logical plan. If the template SQL is Top-SQL, use the template SQL as the key to query whether it exists in the plan manager. If it does not exist, proceed to step S1.5; otherwise, perform the selection and reuse of the corresponding logical plan. Step S1.4: This template SQL is entering the database for the first time. The database optimizer directly selects the optimal logical plan using the cost model, and then caches the logical plan in the Plan Cache using the template SQL as the key. Step S1.5: This template SQL is entering the database for the first time. The database optimizer directly selects the optimal logical plan using the cost model. Then, using the template SQL as the key, the logical plan and the corresponding cost evaluation number are cached in the plan manager.

3. The method for using and managing a database logical plan cache according to claim 2, characterized in that, The selection and reuse of the logical plan The template SQL is not a Top-SQL. The template SQL is used as the key to query whether it exists in the Plan Cache. If it exists, for the template SQL, the corresponding cache plan in the Plan Cache is used and the corresponding parameters are replaced, and then it is output to the executor for execution. The template SQL is a Top-SQL. It is used as the key to query whether it exists in the plan manager. If it exists, for that template SQL, the cost is recalculated for the corresponding cached plans in the plan manager using the current parameters. The cost calculation process is serialized. If the difference between the latest calculated cost and the cached cost is within the user-defined range, the latest cost replaces the cached cost evaluation number, and the hit count of the corresponding cached logical plan is increased by 1. Then, the current cached plan is directly selected for a hit, and the corresponding parameters are replaced, and the result is output to the executor for execution. If all cached logical plans in the plan manager cannot be hit, the evolution and management of the logical plan are executed.

4. The method for using and managing a database logical plan cache according to claim 3, characterized in that, The evolution and management process of the logical plan is as follows: Step S3.1: In the plan manager, a template SQL can cache a maximum of K logical plans and their corresponding cost evaluation numbers, ensuring that all logical plans of each template SQL are sorted in ascending order. K can be user-defined. Step S3.2: Input the template SQL and corresponding parameters into the database optimizer to select the optimal logical plan, and output it to the executor for execution; then periodically store the optimal logical plan and cost evaluation data into the plan manager, and jump to step S3.3; Step S3.3: If the number of cached plans corresponding to the current template SQL in the plan manager is less than K, then the optimal logical plan and cost evaluation number in step S3.2 are directly stored; otherwise, the cached logical plan with the lowest hit frequency is deleted, and the optimal logical plan and cost evaluation number in step S3.2 are stored.

5. The method for using and managing a database logical plan cache according to claim 4, characterized in that, The Plan Cache data structure includes: Key: SQL string; Value: Logical plan; The plan manager's data structure includes: Key: SQL string; Value1: Logical plan; Value2: Cost evaluation number (float); Value3: Cache hit count (int).

6. A database logical plan cache usage and management system, characterized in that, It includes a Top-SQL determination module, a logical plan capture module, a logical plan selection and reuse module, and a logical plan evolution and management module. The Top-SQL determination module allows users to define which SQL statements belong to Top-SQL. The logical plan capture module is used to capture the logical plan after the SQL enters the database for the first time and store it in the PlanCache or Plan Manager; The logical plan selection and reuse module is used for the selection and reuse calculation of specific cache plans after SQL hits PlanCache or Plan Manager; The logical plan evolution and management module is used to manage the addition and deletion of K logical plans in the plan manager; The system implements the method for using and managing the database logical plan cache as described in any one of claims 1 to 5.

7. A device for using and managing a database logical plan cache, characterized in that, include: At least one memory and at least one processor; The at least one memory is used to store a machine-readable program; The at least one processor is configured to invoke the machine-readable program to implement the method of any one of claims 1 to 5.

8. A computer-readable medium, characterized in that, The computer-readable medium stores computer instructions that, when executed by a processor, implement the method described in any one of claims 1 to 5.

Images