SQL execution plan management methods, devices, equipment, and readable storage media

Abstract

This invention provides a method, apparatus, device, and readable storage medium for SQL execution plan management. The method includes: searching a hash table to determine whether the hash table stores an SQL statement to be executed and its execution plan; if not, executing the execution plan of the SQL statement to be executed and storing the SQL statement to be executed and its execution plan in the hash table; if yes, determining and executing the plan for the SQL statement to be executed based on the execution plan stored in the hash table; and persistently storing the data in the hash table. This invention achieves stable database performance while minimizing memory usage, preventing database performance regression. Furthermore, by using a hash table stored in memory, execution plans can be shared among multiple SQL sessions, reducing the system resource consumption of execution plan management.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of database, in particular to a SQL execution plan management method and device, equipment and readable storage medium. BACKGROUND

[0002] In many relational databases and some large data engines, when executing SQL (Structured Query Language) statements, the query optimizer plays a very important role. When a user inputs a declarative query statement, the query optimizer can find a "best" execution plan for it. The subsequent query executor will get the final result according to the "best" execution plan. The user only needs to focus on how to write a query statement that meets his own needs, and does not need to worry about how the statement will be executed and the execution efficiency. At present, most existing database systems use cost-based query optimizers.

[0003] In theory, if the search space of plan enumeration is large and effective, and accurate cardinality estimation results and execution plan cost calculation results are provided, the existing cost-based optimizer can develop a "best" execution plan within a reasonable time. However, in practice, cardinality estimation, cost model and plan enumeration all have more or less errors. When complex queries are involved, these errors will be magnified. The cost-based optimizer cannot generate a "best" execution plan for them, and may even generate an execution plan with very poor performance.

[0004] During the operation of the database, the database system environment may also change, such as database version upgrade, configuration parameter modification, hardware configuration replacement, statistical information update, etc. These changes will also affect the accuracy of cardinality estimation, cost model and plan enumeration. Therefore, during the repeated execution of query statements, the query optimizer cannot guarantee that the execution plan generated each time is the "best" execution plan under the current database state. This is the instability problem of the execution plan. The instability of the execution plan will further lead to the instability of the overall performance of the database. If a very poor execution plan is generated, the performance of the database may even regress. In addition, as the amount of data in the database increases during use, the cached plans will consume a large amount of space resources. SUMMARY

[0005] The main purpose of the present application is to provide a SQL execution plan management method, device, equipment and readable storage medium, which aims to solve the technical problem of how to maintain the stability of the performance of the database on the basis of saving as much memory space as possible and prevent the performance regression of the database.

[0006] In a first aspect, the present application provides a SQL execution plan management method, comprising:

[0007] obtaining a to-be-executed SQL statement and an execution plan of the to-be-executed SQL statement from a database;

[0008] determining whether the to-be-executed SQL statement and the execution plan of the to-be-executed SQL statement are stored in a hash table by searching the hash table;

[0009] if not, executing the execution plan of the to-be-executed SQL statement, and storing the to-be-executed SQL statement and the execution plan of the to-be-executed SQL statement in the hash table;

[0010] if yes, determining a plan of the to-be-executed SQL statement according to the execution plan of the to-be-executed SQL statement stored in the hash table, and executing the determined plan of the to-be-executed SQL statement;

[0011] the hash table is stored in a memory, when a memory capacity used for storing the hash table is greater than a preset capacity or a number of data in the hash table is greater than a preset number, data stored in the hash table is sequentially persisted and deleted from the hash table in an order from low to high according to a frequency of use of the data in the hash table, until the memory capacity used for storing the hash table is not greater than the preset capacity or the number of data in the hash table is not greater than the preset number.

[0012] Optionally, the hash table comprises a first hash table and a second hash table, and the storing of the to-be-executed SQL statement and the execution plan of the to-be-executed SQL statement in the hash table comprises:

[0013] storing the to-be-executed SQL statement in the first hash table by taking the to-be-executed SQL statement as a search key;

[0014] storing the to-be-executed SQL statement and the execution plan of the to-be-executed SQL statement in the second hash table by taking an array formed by the to-be-executed SQL statement and the execution plan of the to-be-executed SQL statement as a search key.

[0015] Optionally, the execution plan is composed of one or more plan units, and the determining of the plan of the to-be-executed SQL statement according to the execution plan of the to-be-executed SQL statement stored in the hash table, and the executing of the determined plan of the to-be-executed SQL statement comprises:

[0016] determining a plan baseline of the to-be-executed SQL statement according to the plan units of the to-be-executed SQL statement stored in the hash table, the plan baseline of the to-be-executed SQL statement being composed of the plan units corresponding to the to-be-executed SQL statement and stored in the hash table and having an accepted state;

[0017] determining whether all the plan units of the to-be-executed SQL statement exist in the plan baseline of the to-be-executed SQL statement;

[0018] if yes, executing the execution plan of the to-be-executed SQL statement;

[0019] if no, storing the to-be-executed SQL statement and the plan unit of the to-be-executed SQL statement which do not exist in the plan baseline of the to-be-executed SQL statement in the hash table, and setting the state of the plan unit as not accepted;

[0020] detecting whether the plan baseline option is enabled;

[0021] if no, executing the execution plan of the to-be-executed SQL statement;

[0022] if yes, determining whether the fixed plan is included in the plan baseline of the to-be-executed SQL statement;

[0023] if yes, selecting the plan with the minimum cost from the fixed plan for execution;

[0024] if no, selecting the plan with the minimum cost from the plan baseline of the to-be-executed SQL statement for execution.

[0025] Optionally, the SQL execution plan management method further comprises:

[0026] based on a preset period, evolving the plan unit with the state of not accepted stored in the hash table, and if the evolution is verified, modifying the state of the plan unit from not accepted to accepted.

[0027] Optionally, the evolving the plan unit with the state of not accepted stored in the hash table, and if the evolution is verified, modifying the state of the plan unit from not accepted to accepted comprises:

[0028] executing the plan unit with the minimum cost in the plan baseline stored in the hash table to obtain a first running time and a first consumed memory;

[0029] sequentially executing the plan unit with the state of not accepted stored in the hash table to obtain a second running time and a second consumed memory;

[0030] if the second running time is greater than the first running time, keeping the state of the plan unit as not accepted;

[0031] if the second running time is not greater than the first running time, and the second consumed memory is less than the first consumed memory, modifying the state of the plan unit from not accepted to accepted.

[0032] Optionally, the SQL execution plan management method further comprises:

[0033] based on a preset period, persistently saving the data stored in the hash table.

[0034] Optionally, the SQL execution plan management method further comprises:

[0035] capturing an invalid execution plan caused by database change, and setting a state of a plan unit corresponding to the invalid execution plan stored in the hash table as unavailable.

[0036] In a second aspect, the present application provides a SQL execution plan management device, which comprises:

[0037] an obtaining module, configured to obtain a to-be-executed SQL statement and an execution plan of the to-be-executed SQL statement from a database;

[0038] a searching module, configured to search the hash table to determine whether the hash table stores the to-be-executed SQL statement and the execution plan of the to-be-executed SQL statement;

[0039] a storing module, configured to, if not, execute the execution plan of the to-be-executed SQL statement, and store the to-be-executed SQL statement and the execution plan of the to-be-executed SQL statement in the hash table;

[0040] an executing module, configured to, if yes, determine a plan of the to-be-executed SQL statement according to the execution plan of the to-be-executed SQL statement stored in the hash table, and execute the determined plan of the to-be-executed SQL statement;

[0041] a saving module, configured to, when a memory capacity for storing the hash table is greater than a preset capacity or a number of data in the hash table is greater than a preset number, save data stored in the hash table in a persistent manner in a sequence from low to high according to a frequency of use of the data in the hash table, and delete the data from the hash table, until the memory capacity for storing the hash table is not greater than the preset capacity or the number of data in the hash table is not greater than the preset number.

[0042] In a third aspect, the present application provides a SQL execution plan management device, which comprises a processor, a memory, and a SQL execution plan management program stored in the memory and executable by the processor, wherein the SQL execution plan management program, when executed by the processor, implements the steps of the SQL execution plan management method.

[0043] In a fourth aspect, the present application provides a readable storage medium, which stores a SQL execution plan management program, wherein the SQL execution plan management program, when executed by a processor, implements the steps of the SQL execution plan management method.

[0044] In this invention, the SQL statement to be executed and its execution plan are obtained from the database. A hash table is searched to determine if the SQL statement to be executed and its execution plan are stored there. If not, the execution plan of the SQL statement to be executed is executed, and the SQL statement to be executed and its execution plan are stored in the hash table. If yes, the execution plan of the SQL statement to be executed is determined based on the execution plan stored in the hash table, and the determined execution plan is executed. The hash table is stored in memory. When the memory capacity used to store the hash table is greater than a preset capacity or the number of data entries in the hash table is greater than a preset number, the data stored in the hash table is persistently saved and deleted from the hash table in order of increasing usage frequency, until the memory capacity used to store the hash table is no greater than the preset capacity or the number of data entries in the hash table is no greater than the preset number. This invention first retrieves the SQL statement to be executed and its execution plan generated by the database optimizer from the database. However, instead of immediately executing the generated execution plan, it searches a hash table stored in memory to determine if the SQL statement and its execution plan are present in the hash table. Specifically, it checks if the SQL statement has been executed before and if its execution plan is stored in the hash table. If not, it indicates that this is the first execution of the SQL statement, and the current execution plan generated by the optimizer is executed. The SQL statement and its execution plan are then saved to the hash table. If the execution plan is present, the execution plan for the SQL statement is determined and executed based on the plan stored in the hash table. As the number of SQL statements and execution plans stored in the hash table increases, and memory usage grows, infrequently used data is persistently saved, such as to an external disk. This invention achieves stable database performance while minimizing memory usage, preventing performance regression. Furthermore, the in-memory hash table allows execution plans to be shared across multiple SQL sessions, reducing the resource consumption of execution plan management. Attached Figure Description

[0045] Figure 1 This is a schematic diagram of the hardware structure of an embodiment of the SQL execution plan management device of the present invention;

[0046] Figure 2 This is a flowchart illustrating an embodiment of the SQL execution plan management method of the present invention;

[0047] Figure 3 for Figure 2 A detailed flowchart of step S30;

[0048] Figure 4 For Figure 2 The detailed flowchart of step S40 is shown in the figure.

[0049] Figure 5 The functional module diagram of an embodiment of the SQL execution plan management device of the present application is shown in the figure.

[0050] The implementation, functional features and advantages of the present application will be further described with reference to the embodiments and the accompanying drawings. DETAILED DESCRIPTION

[0051] It should be understood that the specific embodiments described herein are merely intended to explain the present application and not to limit the present application.

[0052] In a first aspect, an embodiment of the present application provides a SQL execution plan management device.

[0053] Referring to Figure 1 , Figure 1 The hardware structure diagram of an embodiment of the SQL execution plan management device of the present application is shown in the figure. In the embodiment of the present application, the SQL execution plan management device can include a processor 1001 (for example, a central processing unit, CPU), a communication bus 1002, a user interface 1003, a network interface 1004, and a memory 1005. The communication bus 1002 is used to realize the connection and communication between these components; the user interface 1003 can include a display screen and an input unit such as a keyboard; the network interface 1004 can optionally include a standard wired interface and a wireless interface (such as a WI-FI interface); the memory 1005 can be a high-speed random access memory (RAM) and can also be a stable memory (non-volatile memory) such as a disk memory; the memory 1005 can optionally also be a storage device independent of the aforementioned processor 1001. Those skilled in the art can understand that the hardware structure shown in the figure is not a limitation of the present application and can include more or fewer components than those shown in the figure, or combine certain components, or different component arrangements. Figure 1 The hardware structure shown in the figure is not a limitation of the present application and can include more or fewer components than those shown in the figure, or combine certain components, or different component arrangements.

[0054] Continuing to refer to Figure 1 , Figure 1The memory 1005, which serves as a computer storage medium, may include an operating system, a network communication module, a user interface module, and an SQL execution plan management program. The processor 1001 can call the SQL execution plan management program stored in the memory 1005 and execute the SQL execution plan management method provided in this embodiment of the invention.

[0055] Secondly, embodiments of the present invention provide a method for managing SQL execution plans.

[0056] To more clearly demonstrate the SQL execution plan management method provided in the embodiments of this application, we will first introduce the application scenarios of the SQL execution plan management method provided in the embodiments of this application.

[0057] The SQL execution plan management method provided in this application is applied during the database operation process, constantly generating new execution plans and continuously increasing storage space. The new execution plans may lead to a regression in database performance.

[0058] In one embodiment, reference is made to Figure 2 , Figure 2 This is a flowchart illustrating an embodiment of the SQL execution plan management method of the present invention, as shown below. Figure 2 As shown, the SQL execution plan management method includes:

[0059] Step S10: Obtain the SQL statement to be executed and its execution plan from the database.

[0060] In this embodiment, the SQL statement to be executed and the execution plan of the SQL statement to be executed generated by the database's optimizer are obtained from the database. The database includes MySQL, Oracle, SQL Server, DB2, Sybase, and PostgreSQL, etc. The database is usually composed of three main parts: parser, optimizer, and execution engine. The optimizer is the core function of the database. It is responsible for formulating the optimal execution plan for the SQL statement submitted by the user according to various judgment criteria and handing it over to the executor for final execution.

[0061] Step S20: By searching the hash table, determine whether the hash table stores the SQL statement to be executed and its execution plan.

[0062] In this embodiment, after obtaining the SQL statement to be executed and the execution plan of the SQL statement to be executed generated by the optimizer, the generated execution plan is not executed immediately. Instead, the hash table stored in memory is searched to determine whether the SQL statement to be executed and the execution plan of the SQL statement to be executed are stored in the hash table, that is, to determine whether the current SQL statement to be executed has been executed before.

[0063] Step S30: If not, execute the execution plan of the SQL statement to be executed and store the SQL statement to be executed and its execution plan in a hash table.

[0064] In this embodiment, if the hash table does not store the SQL statement to be executed and its execution plan, it means that the SQL statement to be executed is being executed for the first time. In this case, the execution plan of the SQL statement to be executed generated by the optimizer is executed, and the SQL statement to be executed and its execution plan are stored in the hash table for execution plan management and for subsequent execution of other SQL statements.

[0065] Step S40: If yes, then determine the execution plan of the SQL statement to be executed based on the execution plan of the SQL statement to be executed stored in the hash table, and execute the determined execution plan of the SQL statement to be executed.

[0066] In this embodiment, if the hash table contains a SQL statement to be executed and its execution plan, it means that the SQL statement to be executed has been executed before. Therefore, based on the execution plan of the SQL statement to be executed stored in the hash table, the execution plan of the SQL statement to be executed is determined and executed. Since the execution plan of the SQL statement to be executed has been executed before, the execution plan of the SQL statement to be executed stored in the hash table has been verified by execution, thereby ensuring the performance of the execution plan and thus ensuring the performance stability of the database.

[0067] Step S50: The hash table is stored in memory. When the memory capacity used to store the hash table is greater than the preset capacity or the number of data entries in the hash table is greater than the preset number of data entries, the data stored in the hash table is persisted and deleted from the hash table in order of the frequency of use of the data in the hash table from low to high, until the memory capacity used to store the hash table is no greater than the preset capacity or the number of data entries in the hash table is no greater than the preset number of data entries.

[0068] In this embodiment, the hash table is stored in memory for global shared access between SQL sessions. To control memory space and save system resources, when the memory capacity used to store the hash table is greater than the preset capacity or the number of data entries in the hash table is greater than the preset number, the data stored in the hash table is persistently saved in order of increasing usage frequency, such as saving to an external disk, and then deleted from the hash table, until the memory capacity used to store the hash table is no greater than the preset capacity or the number of data entries in the hash table is no greater than the preset number, so that the memory space occupied by the hash table remains within the limit.

[0069] In this embodiment, the SQL statement to be executed and its execution plan generated by the database's optimizer are first obtained from the database. The database includes MySQL, Oracle, SQL Server, DB2, Sybase, and PostgreSQL, etc. However, the generated execution plan is not executed immediately. Instead, a search is performed in a hash table stored in memory to determine if the SQL statement to be executed and its execution plan are present in the hash table. Specifically, it checks if the current SQL statement has been executed before and if its execution plan is stored in the hash table. If not, it indicates that this is the first execution of the SQL statement, and the current execution plan generated by the optimizer is executed. The SQL statement to be executed and its execution plan are then saved to the hash table. If the execution plan is present in the hash table, the execution plan is used accordingly. The execution plan for the SQL statement to be executed is determined and executed. Since the execution plan for the SQL statement to be executed has been executed before, the execution plan of the SQL statement to be executed stored in the hash table has been verified, thus ensuring the execution performance of the SQL statement and ensuring the performance stability of the database. As the number of SQL statements and execution plans stored in the hash table increases and memory usage increases, data with low usage frequency is persisted, such as by saving it to an external disk. Through this invention, the database performance stability is maintained while saving as much memory space as possible, preventing database performance regression. At the same time, by using a hash table stored in memory, the execution plan can be shared among multiple SQL sessions, reducing the consumption of system resources by execution plan management.

[0070] Furthermore, in one embodiment, reference is made to Figure 3 , Figure 3 for Figure 2 A detailed flowchart of step S30 is shown below. Figure 3 As shown, step S30 includes:

[0071] Step S301: Using the SQL statement to be executed as the lookup key, store the SQL statement to be executed in the first hash table;

[0072] Step S302: Construct an array using the SQL statement to be executed and its execution plan, and use the constructed array as the lookup key to store the SQL statement to be executed and its execution plan in the second hash table.

[0073] In this embodiment, a hash table is a data structure that allows direct access based on key-value pairs. That is, records are accessed by mapping key-value pairs to a location in the table, thus speeding up the search. This mapping function is called a hash function, and the array storing the records is called a hash table. Two hash tables can be defined and stored in global shared memory. The first hash table stores SQL statements, and the second hash table stores SQL statements and execution plans. Depending on specific needs, the database OID and SQL statement identifier can be used as the lookup keys for the first hash table, and the database OID, SQL statement identifier, and execution plan identifier can be used as the lookup keys for the second hash table.

[0074] In another embodiment, for PBE (Prepare, Bind, and Execute) statements, there are two hash tables, one in global shared memory and the other in local memory. The former shared hash table stores information about the PBE statement itself (such as pre-parsed query statements), while the latter local hash table stores the prepared statements defined in the local session, but only saves the names of the prepared statements. The actual PBE information is obtained by referencing the entries in the hash table in global shared memory.

[0075] Furthermore, in one embodiment, the execution plan consists of one or more planning units, referring to... Figure 4 , Figure 4 for Figure 2 A detailed flowchart of step S40 is shown below. Figure 4 As shown, step S40 includes:

[0076] Step S401: Determine the plan baseline of the SQL statement to be executed based on the plan units of the SQL statement to be executed stored in the hash table. The plan baseline of the SQL statement to be executed consists of the plan units stored in the hash table that correspond to the SQL statement to be executed and whose status is accepted.

[0077] Step S402: Determine whether all plan units of the SQL statement to be executed exist in the plan baseline of the SQL statement to be executed;

[0078] Step S403: If yes, then execute the execution plan of the SQL statement to be executed;

[0079] Step S404: If not, store the SQL statement to be executed and the plan unit of the SQL statement to be executed that do not exist in the plan baseline of the SQL statement to be executed into a hash table, and set the status of the plan unit to unacceptable;

[0080] Step S405: Check if the plan baseline option is enabled;

[0081] Step S406: If not, execute the execution plan of the SQL statement to be executed;

[0082] Step S407: If yes, then determine whether the plan baseline of the SQL statement to be executed includes a fixed plan;

[0083] Step S408: If yes, then select the plan with the lowest cost from the fixed plans and execute it;

[0084] Step S409: If not, select the least expensive execution plan from the plan baseline of the SQL statement to be executed.

[0085] In this embodiment, the execution plan consists of one or more plan units. The database optimizer generates an execution plan for an SQL statement with multiple options to choose from, such as whether to use an index on the table, which index to choose, whether to use a range scan, the join order of multiple tables, and the execution method of subqueries. The plan baseline is used to guide the execution of the selected execution plan. The plan baseline consists of a set of plan units that have been verified and are in an accepted state. The execution plan baseline for the SQL statement to be executed is determined. It consists of a set of plan units in a hash table that are in the accepted state and correspond to the SQL statement to be executed. If all the plan units of the generated SQL statement to be executed exist in the execution plan baseline, the execution plan of the SQL statement to be executed is executed. Otherwise, the SQL statement to be executed and its plan units that do not exist in the execution plan baseline are stored in the hash table, and the state of the plan units is set to unacceptable for subsequent evolution verification. Then, it is checked whether the execution plan baseline option is enabled. If the execution plan baseline is not enabled, the execution plan of the SQL statement to be executed is executed. If the execution plan baseline is enabled, it is further determined whether the execution plan baseline of the SQL statement to be executed includes a fixed plan. The fixed plan is a plan with higher priority. If a fixed plan exists, the plan with the lowest cost is selected from the fixed plans for execution. If no fixed plan exists, the plan with the lowest cost is selected from the execution plan baseline of the SQL statement to be executed for execution.

[0086] Furthermore, in one embodiment, the SQL execution plan management method further includes:

[0087] Based on a preset period, the plan units stored in the hash table that are in an unacceptable state are evolved. If the verification is successful, the state of the plan unit is changed from unacceptable to acceptable.

[0088] In this embodiment, to prevent database performance regression, the newly generated execution plan by the optimizer is not allowed to be executed directly. It must undergo evolution verification. After passing the verification, the status of the plan unit is changed from unacceptable to accept before it can be used for the execution of subsequent plans.

[0089] Furthermore, in one embodiment, the process of evolving the plan unit stored in the hash table with a state of "unacceptable," and modifying the state of the plan unit from "unacceptable" to "acceptable" if the verification is successful, includes:

[0090] Execute the plan unit with the lowest cost among the plan baselines stored in the hash table to obtain the first running time and the first memory consumption;

[0091] Execute the plan units whose status is unacceptable stored in the hash table in sequence to obtain the second running time and the second memory consumption;

[0092] If the second running time is longer than the first running time, then keep the status of the planning unit as unacceptable;

[0093] If the second running time is not greater than the first running time, and the second memory consumption is less than the first memory consumption, then the status of the planning unit is changed from unacceptable to accepted.

[0094] In this embodiment, during the evolution verification of the plan units stored in the hash table that are in an unacceptable state, the plan unit with the lowest cost among the plan baselines stored in the hash table is executed first to obtain the first running time and the first memory consumption, which serve as the benchmark for comparing the execution plan performance. Then, the plan units stored in the hash table that are in an unacceptable state are executed sequentially to obtain the second running time and the second memory consumption. The two indicators of running time and memory consumption are compared. If the second running time is not greater than the first running time and the second memory consumption is less than the first memory consumption, then the state of the plan unit is changed from unacceptable to acceptable.

[0095] Furthermore, in one embodiment, the SQL execution plan management method further includes:

[0096] Based on a preset period, the stored data in the hash table is persistently saved.

[0097] In this embodiment, the stored data in the hash table is periodically persisted from memory to a disk file. By persisting the data and loading the data from the disk file into memory, the SQL statements and execution plan records of one machine can be imported into another machine for use, making it easier for database system administrators to manage more flexibly.

[0098] Furthermore, in one embodiment, the SQL execution plan management method further includes:

[0099] Capture failed execution plans caused by database changes and set the status of the plan unit corresponding to the failed execution plan stored in the hash table to unavailable.

[0100] In this embodiment, since the execution plan of the SQL statement is dependent on some database objects (such as tables, views and functions), when these database objects are deleted or modified, the related execution plan will become invalid and can no longer be used for the execution of the plan. Therefore, when the execution plan becomes invalid due to database changes, the status of the plan unit corresponding to the invalid execution plan stored in the hash table is set to unavailable.

[0101] Thirdly, embodiments of the present invention also provide an SQL execution plan management device.

[0102] Reference Figure 5 , Figure 5 This is a schematic diagram of the functional modules of an embodiment of the SQL execution plan management device of the present invention.

[0103] In this embodiment, the SQL execution plan management device includes:

[0104] Module 10 is used to obtain the SQL statement to be executed and its execution plan from the database.

[0105] The lookup module 20 is used to determine whether the hash table stores the SQL statement to be executed and the execution plan of the SQL statement by looking up the hash table.

[0106] Storage module 30 is used to execute the execution plan of the SQL statement to be executed if not, and to store the SQL statement to be executed and the execution plan of the SQL statement to be executed in a hash table;

[0107] Execution module 40 is used to determine the execution plan of the SQL statement to be executed based on the execution plan of the SQL statement to be executed stored in the hash table, and execute the determined execution plan of the SQL statement to be executed.

[0108] The storage module 50 is used to store the hash table in memory. When the memory capacity used to store the hash table is greater than the preset capacity or the number of data entries in the hash table is greater than the preset number of data entries, the data stored in the hash table is persistently saved and deleted from the hash table in order of the frequency of use of the data in the hash table from low to high, until the memory capacity used to store the hash table is no greater than the preset capacity or the number of data entries in the hash table is no greater than the preset number of data entries.

[0109] Furthermore, in one embodiment, the storage module 30 is used for:

[0110] Using the SQL statement to be executed as the lookup key, store the SQL statement to be executed in the first hash table;

[0111] An array is constructed using the SQL statement to be executed and its execution plan. The constructed array is used as the lookup key to store the SQL statement to be executed and its execution plan in a second hash table.

[0112] Furthermore, in one embodiment, the execution plan consists of one or more planning units, and the execution module 40 is used for:

[0113] Based on the plan units of the SQL statement to be executed stored in the hash table, the plan baseline of the SQL statement to be executed is determined. The plan baseline of the SQL statement to be executed consists of the plan units stored in the hash table that correspond to the SQL statement to be executed and whose status is accepted.

[0114] Determine whether all plan units of the SQL statement to be executed exist in the plan baseline of the SQL statement to be executed;

[0115] If so, then execute the execution plan of the SQL statement to be executed;

[0116] If not, store the SQL statements to be executed and the plan units of the SQL statements to be executed that do not exist in the plan baseline of the SQL statements to be executed into a hash table, and set the status of the plan units to unacceptable;

[0117] Is the baseline option for the detection plan enabled?

[0118] If not, then execute the execution plan for the SQL statement to be executed;

[0119] If so, determine whether the plan baseline of the SQL statement to be executed includes a fixed plan;

[0120] If so, then select the plan with the lowest cost from the fixed plans and execute it;

[0121] If not, select the least expensive execution plan from the plan baseline of the SQL statements to be executed.

[0122] Furthermore, in one embodiment, the SQL execution plan management device further includes an evolution module, used for:

[0123] Based on a preset period, the plan units stored in the hash table that are in an unacceptable state are evolved. If the verification is successful, the state of the plan unit is changed from unacceptable to acceptable.

[0124] Furthermore, in one embodiment, the evolution module is also used for:

[0125] Execute the plan unit with the lowest cost among the plan baselines stored in the hash table to obtain the first running time and the first memory consumption;

[0126] Execute the plan units whose status is unacceptable stored in the hash table in sequence to obtain the second running time and the second memory consumption;

[0127] If the second running time is longer than the first running time, then keep the status of the planning unit as unacceptable;

[0128] If the second running time is not greater than the first running time, and the second memory consumption is less than the first memory consumption, then the status of the planning unit is changed from unacceptable to accepted.

[0129] Furthermore, in one embodiment, the SQL execution plan management device further includes a persistence module, used for:

[0130] Based on a preset period, the stored data in the hash table is persistently saved.

[0131] Furthermore, in one embodiment, the SQL execution plan management device further includes a setting module, used for:

[0132] Capture failed execution plans caused by database changes and set the status of the plan unit corresponding to the failed execution plan stored in the hash table to unavailable.

[0133] The functions of each module in the SQL execution plan management device correspond to the steps in the SQL execution plan management method embodiment, and their functions and implementation processes will not be described in detail here.

[0134] Fourthly, embodiments of the present invention also provide a readable storage medium.

[0135] The present invention provides a storage medium on which an SQL execution plan management program is stored, wherein when the SQL execution plan management program is executed by a processor, the steps of the SQL execution plan management method described above are implemented.

[0136] The method implemented when the SQL execution plan management program is executed can be referred to in various embodiments of the SQL execution plan management method of the present invention, and will not be repeated here.

[0137] It should be noted that, in this document, the terms "comprising," "including," or any other variations thereof are intended to cover non-exclusive inclusion, such that a process, method, article, or system that comprises a list of elements includes not only those elements but also other elements not expressly listed, or elements inherent to such a process, method, article, or system. Unless otherwise specified, an element defined by the phrase "comprising one..." does not exclude the presence of other identical elements in the process, method, article, or system that includes that element.

[0138] The sequence numbers of the above embodiments of the present invention are for descriptive purposes only and do not represent the superiority or inferiority of the embodiments.

[0139] Through the above description of the embodiments, those skilled in the art can clearly understand that the methods of the above embodiments can be implemented by means of software plus necessary general-purpose hardware platforms. Of course, they can also be implemented by hardware, but in many cases the former is a better implementation method. Based on this understanding, the technical solution of the present invention, in essence, or the part that contributes to the prior art, can be embodied in the form of a software product. This computer software product is stored in a storage medium (such as ROM / RAM, magnetic disk, optical disk) as described above, and includes several instructions to cause a terminal device to execute the methods described in the various embodiments of the present invention.

[0140] The above are merely preferred embodiments of the present invention and do not limit the scope of the patent. Any equivalent structural or procedural transformations made based on the description and drawings of the present invention, or direct or indirect applications in other related technical fields, are similarly included within the scope of patent protection of the present invention.

Claims

1. A method of managing SQL execution plans, characterized by, The SQL execution plan management method comprises: obtaining a to-be-executed SQL statement and an execution plan of the to-be-executed SQL statement from a database; judging whether the to-be-executed SQL statement and the execution plan of the to-be-executed SQL statement are stored in a hash table; if not, executing the execution plan of the to-be-executed SQL statement and storing the to-be-executed SQL statement and the execution plan of the to-be-executed SQL statement in the hash table; if yes, determining a plan of the to-be-executed SQL statement according to the execution plan of the to-be-executed SQL statement stored in the hash table and executing the determined plan of the to-be-executed SQL statement; the hash table is stored in a memory, when a memory capacity for storing the hash table is greater than a preset capacity or a data quantity in the hash table is greater than a preset quantity, data stored in the hash table is sequentially and persistently saved in order of usage frequency of the data in the hash table from low to high and is deleted from the hash table until the memory capacity for storing the hash table is not greater than the preset capacity or the data quantity in the hash table is not greater than the preset quantity; the execution plan is composed of one or more plan units, and the determining of the plan of the to-be-executed SQL statement according to the execution plan of the to-be-executed SQL statement stored in the hash table and the executing of the determined plan of the to-be-executed SQL statement comprise: determining a plan baseline of the to-be-executed SQL statement according to plan units of the to-be-executed SQL statement stored in the hash table, the plan baseline of the to-be-executed SQL statement being composed of the plan units corresponding to the to-be-executed SQL statement and stored in the hash table and having an accepted state; judging whether the plan units of the to-be-executed SQL statement all exist in the plan baseline of the to-be-executed SQL statement; if yes, executing the execution plan of the to-be-executed SQL statement; if no, storing the to-be-executed SQL statement and the plan units of the to-be-executed SQL statement which do not exist in the plan baseline of the to-be-executed SQL statement in the hash table and setting a state of the plan units as not accepted; detecting whether a plan baseline option is enabled; if no, executing the execution plan of the to-be-executed SQL statement; if yes, judging whether a fixed plan is included in the plan baseline of the to-be-executed SQL statement; if yes, selecting a plan with a minimum cost from the fixed plan for execution; if no, selecting a plan with a minimum cost from the plan baseline of the to-be-executed SQL statement for execution; the SQL execution plan management method further comprises: evolving, based on a preset period, a plan unit stored in the hash table and having a state of not accepted, and if the evolution is verified, modifying the state of the plan unit from not accepted to accepted; the evolving of the plan unit stored in the hash table and having the state of not accepted and, if the evolution is verified, the modifying of the state of the plan unit from not accepted to accepted comprise: executing a plan unit with a minimum cost in a plan baseline stored in the hash table to obtain a first running time and a first consumed memory; sequentially executing plan units stored in the hash table and having the state of not accepted to obtain a second running time and a second consumed memory; if the second running time is greater than the first running time, keeping the state of the plan unit as not accepted; If the second running time is not greater than the first running time and the second consumed memory is less than the first consumed memory, the state of the plan unit is changed from not accepting modification to accepting.

2. The SQL execution plan management method of claim 1, wherein, The hash table comprises a first hash table and a second hash table, and the storing of the to-be-executed SQL statement and the execution plan of the to-be-executed SQL statement into the hash table comprises: storing the to-be-executed SQL statement into the first hash table by taking the to-be-executed SQL statement as a lookup key; storing the to-be-executed SQL statement and the execution plan of the to-be-executed SQL statement into the second hash table by taking the to-be-executed SQL statement and the execution plan of the to-be-executed SQL statement as an array, and taking the array as a lookup key.

3. The SQL execution plan management method of claim 1, wherein, The SQL execution plan management method further comprises: persistently saving the stored data in the hash table based on a preset period.

4. The SQL execution plan management method of claim 1, wherein, The SQL execution plan management method further comprises: capturing an invalid execution plan caused by database changes, and setting the state of the plan unit corresponding to the invalid execution plan stored in the hash table to unavailable.

5. An SQL execution plan management apparatus characterized by comprising: The SQL execution plan management apparatus comprises: an acquisition module configured to acquire a to-be-executed SQL statement and an execution plan of the to-be-executed SQL statement from a database; a lookup module configured to determine whether the to-be-executed SQL statement and the execution plan of the to-be-executed SQL statement are stored in a hash table by looking up the hash table; a storage module configured to, if not, execute the execution plan of the to-be-executed SQL statement, and store the to-be-executed SQL statement and the execution plan of the to-be-executed SQL statement into the hash table; an execution module configured to, if yes, determine a plan of the to-be-executed SQL statement according to the execution plan of the to-be-executed SQL statement stored in the hash table, and execute the determined plan of the to-be-executed SQL statement; a saving module configured to, when the memory capacity for storing the hash table is greater than a preset capacity or the number of data in the hash table is greater than a preset number, persistently save the data stored in the hash table in order from low to high according to the usage frequency of the data in the hash table, and delete the data from the hash table, until the memory capacity for storing the hash table is not greater than the preset capacity or the number of data in the hash table is not greater than the preset number; The execution plan is composed of one or more plan units, and the execution module is configured to: determine a plan baseline of the to-be-executed SQL statement according to the plan units of the to-be-executed SQL statement stored in the hash table, wherein the plan baseline of the to-be-executed SQL statement is composed of the plan units corresponding to the to-be-executed SQL statement and stored in the hash table and having an accepted state; determine whether the plan units of the to-be-executed SQL statement all exist in the plan baseline of the to-be-executed SQL statement; if yes, execute the execution plan of the to-be-executed SQL statement; if not, store the to-be-executed SQL statement and the plan units of the to-be-executed SQL statement which do not exist in the plan baseline of the to-be-executed SQL statement into the hash table, and set the state of the plan units to not accepting; determine whether the plan baseline option is enabled; if not, execute the execution plan of the to-be-executed SQL statement; If yes, it is judged whether the fixed plan is included in the plan baseline of the SQL statement to be executed; If yes, the plan with the minimum cost is selected from the fixed plan for execution; If no, the plan with the minimum cost is selected from the plan baseline of the SQL statement to be executed for execution; The SQL execution plan management device further comprises an evolution module configured to: evolve the plan unit with the state of not accepted in the hash table based on a preset period, and if the evolution is verified, modify the state of the plan unit from not accepted to accepted; The evolution module is further configured to: execute the plan unit with the minimum cost in the plan baseline stored in the hash table to obtain a first running time and a first consumed memory; execute the plan unit with the state of not accepted in the hash table in sequence to obtain a second running time and a second consumed memory; if the second running time is greater than the first running time, keep the state of the plan unit as not accepted; if the second running time is not greater than the first running time and the second consumed memory is less than the first consumed memory, modify the state of the plan unit from not accepted to accepted.

6. An SQL execution plan management device characterized by comprising: The SQL execution plan management device comprises a processor, a memory, and a SQL execution plan management program stored in the memory and executable by the processor, wherein when the SQL execution plan management program is executed by the processor, the steps of the SQL execution plan management method in any one of claims 1 to 4 are implemented.

7. A readable storage medium, characterized by, The readable storage medium stores a SQL execution plan management program, wherein when the SQL execution plan management program is executed by the processor, the steps of the SQL execution plan management method in any one of claims 1 to 4 are implemented.

Images