Database statement execution plan processing method and device and storage medium

Abstract

The invention discloses a database statement execution plan processing method and device and a storage medium, and relates to the field of financial science and technology. The method comprises the following steps: acquiring preset execution plans respectively set by a target database statement under L data magnitudes; when the target database statement is executed, collecting a target data magnitude and target execution information actually generated by the target database statement; detecting whether an execution step in the target execution information is matched with any one preset execution step in the L preset execution plans or not; when it is detected that the execution steps in the target execution information are not matched with the preset execution steps in the L preset execution plans, processing operation on the preset execution plans is determined according to the target data magnitude, the target execution information and the L preset execution plans. According to the method and the device, the technical problems of low processing efficiency and unstable performance caused by manual adjustment and optimization of the query statement execution plan based on manual work in the prior art are solved.

Description

Technical Field

[0001] The present application relates to the field of financial technology, and specifically, to a method, device and storage medium for processing an execution plan of a database statement. Background Art

[0002] In a database, the statistics of a database table are a set of statistics about the characteristics of the database table data distribution, data density, number of unique values, etc. The optimizer estimates the execution costs of different query plans based on the database statistics, and selects the optimal execution plan to ensure that the database statement runs with the best efficiency and the minimum overhead, so that the database status and performance are maintained at a good level. Therefore, the execution plan of the database is crucial to the stability of the database performance. A poor execution plan will lead to a decline in database performance and reduced response efficiency.

[0003] At present, whether the database execution plan meets expectations requires manual judgment by the DBA (Database Administrator), who must process statements with poor execution efficiency, re-collect statistical information, or adjust the execution plan. Manual judgment and processing is time-consuming and involves many manual steps. Non-professional DBAs are unable to make judgments and processes, which can easily lead to problems such as low processing efficiency and unstable performance.

[0004] To address the above-mentioned problems, no effective solution has been proposed yet. Summary of the invention

[0005] The main purpose of the present application is to provide a method, device and storage medium for processing an execution plan of a database statement, so as to at least solve the technical problems in the prior art of manual adjustment and optimization of the query statement execution plan, resulting in low processing efficiency and unstable performance.

[0006] In order to achieve the above-mentioned purpose, according to one aspect of an embodiment of the present application, a method for processing an execution plan of a database statement is provided, comprising: obtaining preset execution plans respectively set for a target database statement at L data levels, wherein L is an integer greater than or equal to 1, and each preset execution plan comprises an identifier of the target database statement, preset execution steps, a single execution logical read, a single execution time, and a single execution efficiency, wherein the single execution logical read is used to characterize the speed at which the database uses a memory cache to access data, and the single execution efficiency is used to characterize the CPU utilization of the database when processing database statements; when the target database statement is executed, collecting the target data level and target execution information actually generated by the target database statement; detecting whether the execution steps in the target execution information match any one of the preset execution steps in the L preset execution plans; and when it is detected that the execution steps in the target execution information do not match the preset execution steps in the L preset execution plans, determining the processing operation for the preset execution plan according to the target data level, the target execution information, and the L preset execution plans.

[0007] Optionally, detecting whether the execution step in the target execution information matches any preset execution step in L preset execution plans includes: when the hash value of the execution step in the target execution information is the same as the hash value of the preset execution step of the i-th preset execution plan in the L preset execution plans, determining that the target execution information matches the i-th preset execution plan, where i is a positive integer less than or equal to L; when the hash value of the execution step in the target execution information is different from the hash values ​​of all the preset execution steps in the L preset execution plans, determining that the target execution information does not match any of the L preset execution plans.

[0008] Optionally, after determining that the target execution information matches the i-th preset execution plan, the execution plan processing method of the database statement also includes: calculating the ratio between a single execution logical read in the target execution information and a single execution logical read in the i-th preset execution plan to obtain a first indicator; calculating the ratio between a single execution time in the target execution information and a single execution time in the i-th preset execution plan to obtain a second indicator; calculating the ratio between a single execution efficiency in the target execution information and a single execution efficiency in the i-th preset execution plan to obtain a third indicator; and generating prompt information based on the first indicator, the second indicator, and the third indicator.

[0009] Optionally, prompt information is generated according to the first indicator, the second indicator, and the third indicator, including: when the first indicator, the second indicator, and the third indicator are all less than or equal to their respective corresponding preset indicator values, first prompt information is generated, wherein the first prompt information is used to characterize that the target execution information meets the preset requirements; when any one of the first indicator, the second indicator, and the third indicator is greater than their respective corresponding preset indicator values, first statistical information of a data table associated with the target database statement is obtained, wherein the first statistical information is used to characterize data features of the data table associated with the target database statement; according to the first statistical information, the target execution information of the target database statement is updated to second execution information; and second prompt information is generated according to the second execution information and the i-th preset execution plan, wherein the second prompt information is used to characterize whether the second execution information meets the preset requirements.

[0010] Optionally, when it is detected that the execution steps in the target execution information do not match the preset execution steps in the L preset execution plans, the processing operation of the preset execution plan is determined according to the target data level, the target execution information and the L preset execution plans, including: when the target data level is different from the L data levels corresponding to the L preset execution plans, determining according to the target execution information whether to perform a plan addition operation based on the L preset execution plans, wherein the plan addition operation is used to add a preset execution plan that includes the target data level and the target execution information; when the target data level is the same as the jth data level among the L data levels, determining according to the target execution information whether to update the jth preset execution plan corresponding to the jth data level, wherein j is an integer greater than or equal to 1.

[0011] Optionally, when the target data level is different from the L data levels, determining whether to perform a plan addition operation on the basis of the L preset execution plans is determined according to the target execution information, including: calculating the ratio of the target data level to the kth data level of the target database statement to obtain a first value, wherein k is an integer greater than or equal to 1, and the difference between the kth data level and the target data level is less than a second preset threshold; calculating the ratio of the single execution efficiency in the target execution information to the single execution efficiency in the preset execution plan corresponding to the kth data level to obtain a second value; when the first value is greater than or equal to the second value, adding the target data level and the target execution information on the basis of the L data levels; when the first value is less than the second value, generating a third prompt information and not performing the addition operation, wherein the third prompt information is used to prompt that there is an abnormality in the execution efficiency of the target execution information.

[0012] Optionally, when the target data level is the same as the jth data level among L data levels, determine whether to perform an update operation on the jth preset execution plan corresponding to the jth data level according to the target execution information, including: when the single execution efficiency in the target execution information is less than the single execution efficiency in the jth preset execution plan, updating the jth preset execution plan to the target execution information; taking the product of the single execution efficiency in the jth preset execution plan and the corresponding preset indicator value as the third numerical value; when the single execution efficiency in the target execution information is greater than the third numerical value, generating a fourth prompt information and not performing the update operation, wherein the fourth prompt information is used to prompt that the execution efficiency of the target execution information is lower than the third preset threshold.

[0013] Optionally, when the single execution efficiency in the target execution information is greater than a third value, after generating the fourth prompt information, the execution plan processing method for the database statement also includes: obtaining second statistical information of a data table associated with the target database statement, wherein the second statistical information is used to characterize data features of the data table associated with the target database statement; updating the target execution information to third execution information according to the second statistical information; and when the execution steps in the third execution information are different from the preset execution steps in the jth preset execution plan, using the preset execution steps in the jth preset execution plan as the execution steps of the target database statement.

[0014] In order to achieve the above-mentioned purpose, according to another aspect of an embodiment of the present application, there is further provided an execution plan processing device for a database statement, comprising: an acquisition unit, which acquires preset execution plans respectively set for a target database statement under L data levels, wherein L is an integer greater than or equal to 1, and each preset execution plan comprises an identifier of the target database statement, preset execution steps, a single execution logical read, a single execution time, and a single execution efficiency, wherein the single execution logical read is used to characterize the speed at which the database uses the memory cache to access data, and the single execution efficiency is used to characterize the CPU utilization of the database when processing database statements; a collection unit, which, when the target database statement is executed, collects the target data level and target execution information actually generated by the target database statement; a detection unit, which detects whether the execution steps in the target execution information match any one of the preset execution steps in the L preset execution plans; and a determination unit, which, when it is detected that the execution steps in the target execution information do not match the preset execution steps in the L preset execution plans, determines the processing operation of the preset execution plan according to the target data level, the target execution information, and the L preset execution plans.

[0015] According to another aspect of an embodiment of the present application, there is also provided an electronic device, comprising one or more processors and a memory, wherein the memory is used to store one or more programs, wherein when the one or more programs are executed by the one or more processors, the one or more processors execute the execution plan processing method for the database statement described in any one of claims 1 to 8.

[0016] According to another aspect of an embodiment of the present application, a computer-readable storage medium is also provided, the computer-readable storage medium including a stored executable program, wherein when the executable program is running, the device where the computer-readable storage medium is located is controlled to execute the execution plan processing method of the above-mentioned database statement.

[0017] According to another aspect of an embodiment of the present application, a computer program product is also provided, including computer instructions, which implement the steps of the above-mentioned method for processing an execution plan of a database statement when the computer instructions are executed by a processor.

[0018] In an embodiment of the present application, first, preset execution plans respectively set for a target database statement at L data levels are obtained, wherein L is an integer greater than or equal to 1, and each preset execution plan includes an identifier of the target database statement, preset execution steps, a single execution logical read, a single execution time, and a single execution efficiency, wherein the single execution logical read is used to characterize the speed at which the database uses the memory cache to access data, and the single execution efficiency is used to characterize the CPU utilization of the database when processing database statements. Then, when the target database statement is executed, the target data level and target execution information actually generated by the target database statement are collected, and then it is detected whether the execution steps in the target execution information match any one of the preset execution steps in the L preset execution plans. Finally, when it is detected that the execution steps in the target execution information do not match the preset execution steps in the L preset execution plans, the processing operation on the preset execution plan is determined according to the target data level, the target execution information, and the L preset execution plans.

[0019] As can be seen from the above content, this application first collects the preset execution plans of database statements at L different data levels, where L is an integer greater than or equal to 1. Each preset execution plan contains the unique identifier of the statement, the preset execution steps, and indicators related to execution efficiency, such as single execution logical read, single execution time, and single execution efficiency. The logical read indicator is used to evaluate the efficiency of the database accessing data through the memory cache, while the execution efficiency intuitively reflects the occupancy of CPU resources when processing statements.

[0020] Secondly, when the target database statement is executed, the actual data level and execution information generated by the statement will be collected in real time, including but not limited to the actual execution steps and efficiency indicators.

[0021] Subsequently, the application will detect whether the actual execution steps match the execution steps in the preset execution plan. If it is detected that the actual execution steps do not match all preset execution plans, the application will automatically determine and perform adjustment or repair operations based on the actual data volume, execution information, and existing preset execution plans. This not only covers the addition of new preset execution plans to adapt to the growth of data volume, but also includes updating the current preset execution plan to optimize execution efficiency.

[0022] It can be seen that through the technical solution of the present application, the purpose of automated monitoring and adaptive adjustment is achieved, thereby achieving the technical effects of optimizing database performance, reducing operation and maintenance costs, and improving system stability and response speed, thereby solving the technical problems in the prior art of manual adjustment and optimization of query statement execution plans, resulting in low processing efficiency and unstable performance. BRIEF DESCRIPTION OF THE DRAWINGS

[0023] The drawings described herein are used to provide a further understanding of the present application and constitute a part of the present application. The illustrative embodiments of the present application and their descriptions are used to explain the present application and do not constitute an improper limitation on the present application. In the drawings:

[0024] Figure 1 A hardware structure block diagram of a computer terminal for implementing an execution plan processing method for database statements is shown;

[0025] Figure 2 is a flowchart of an optional database statement execution plan processing method according to an embodiment of the present application;

[0026] Figure 3 It is a schematic diagram of an execution plan processing device for a database statement provided in an embodiment of the present application;

[0027] Figure 4 It is a structural block diagram of an electronic device according to an embodiment of the present application. DETAILED DESCRIPTION

[0028] In order to enable those skilled in the art to better understand the solution of the present application, the technical solution in the embodiments of the present application will be clearly and completely described below in conjunction with the drawings in the embodiments of the present application. Obviously, the described embodiments are only part of the embodiments of the present application, not all of the embodiments. Based on the embodiments in the present application, all other embodiments obtained by ordinary technicians in this field without creative work should fall within the scope of protection of the present application.

[0029] It should be noted that the terms "first", "second", etc. in the specification and claims of the present application and the above-mentioned drawings are used to distinguish similar objects, and are not necessarily used to describe a specific order or sequence. It should be understood that the data used in this way can be interchangeable where appropriate, so that the embodiments of the present application described herein can be implemented in an order other than those illustrated or described herein. In addition, the terms "including" and "having" and any of their variations are intended to cover non-exclusive inclusions, for example, a process, method, system, product or device comprising a series of steps or units is not necessarily limited to those steps or units clearly listed, but may include other steps or units that are not clearly listed or inherent to these processes, methods, products or devices.

[0030] It should also be noted that the collected information (including but not limited to user device information, user personal information, etc.) and data (including but not limited to data for display, data for analysis, etc.) involved in this application are information and data authorized by the user or fully authorized by all parties, and the collection, storage, use, processing, transmission, provision, disclosure and application of relevant data are in compliance with relevant laws, regulations and standards, necessary confidentiality measures are taken, and public order and good customs are not violated, and corresponding operation entrances are provided for users to choose to authorize or refuse. For example, an interface is set up between this system and relevant users or institutions to provide users with corresponding operation entrances for users to choose to agree or refuse the results of automated decision-making; if the user chooses to refuse, the expert decision-making process will be entered.

[0031] According to an embodiment of the present application, a method embodiment of a method for processing an execution plan of a database statement is provided. It should be noted that the steps shown in the flowchart of the accompanying drawings can be executed in a computer system such as a set of computer executable instructions, and although a logical order is shown in the flowchart, in some cases, the steps shown or described can be executed in an order different from that shown here.

[0032] It should be noted that a database statement execution plan processing system can be used as the execution subject of the database statement execution plan processing method of the embodiment of the present application. It is understandable that the database statement execution plan processing method provided in the embodiment of the present application can also be executed by other systems or devices as the execution subject, and the embodiment of the present application does not specifically limit this.

[0033] The method embodiments provided in the embodiments of the present application can be executed in a mobile terminal, a computer terminal or a similar computing device. Figure 1 The hardware structure block diagram of a computer terminal (or mobile device) for implementing a method for processing an execution plan of a database statement is shown. Figure 1As shown, the computer terminal 10 (or mobile device) may include one or more (102a, 102b, ..., 102n are used to illustrate) processors 102 (the processor 102 may include but is not limited to a processing device such as a microprocessor MCU or a programmable logic device FPGA), a memory 104 for storing data, and a transmission device 106 for communication functions. In addition, it may also include: a display, an input / output interface (I / O interface), a universal serial bus (USB) port (which may be included as one of the ports of the BUS bus), a network interface, a power supply and / or a camera. It can be understood by those skilled in the art that Figure 1 The structure shown is only for illustration and does not limit the structure of the above electronic device. Figure 1 More or fewer components as shown, or with Figure 1 Different configurations are shown.

[0034] It should be noted that the one or more processors 102 and / or other data processing circuits described above may generally be referred to herein as "data processing circuits". The data processing circuit may be embodied in whole or in part as software, hardware, firmware, or any other combination thereof. In addition, the data processing circuit may be a single independent processing module, or may be fully or partially integrated into any of the other components in the computer terminal 10 (or mobile device). As in the execution plan processing method for database statements involved in the embodiments of the present application, the data processing circuit serves as a processor control (e.g., selection of a variable resistor terminal path connected to an interface).

[0035] The memory 104 can be used to store software programs and modules of application software, such as the program instructions / data storage device corresponding to the execution plan processing method of the database statement in the embodiment of the present application. The processor 102 executes various functional applications and data processing by running the software programs and modules stored in the memory 104, that is, the execution plan processing method of the database statement mentioned above is realized. The memory 104 may include a high-speed random access memory, and may also include a non-volatile memory, such as one or more magnetic storage devices, flash memory, or other non-volatile solid-state memory. In some examples, the memory 104 may further include a memory remotely arranged relative to the processor 102, and these remote memories may be connected to the computer terminal 10 via a network. Examples of the above-mentioned network include, but are not limited to, the Internet, an intranet, a local area network, a mobile communication network, and combinations thereof.

[0036] The transmission device 106 is used to receive or send data via a network. The specific example of the above network may include a wireless network provided by a communication provider of the computer terminal 10. In one example, the transmission device 106 includes a network adapter (Network Interface Controller, NIC), which can be connected to other network devices through a base station so as to communicate with the Internet. In one example, the transmission device 106 can be a radio frequency (RF) module, which is used to communicate with the Internet wirelessly.

[0037] The display may be, for example, a touch screen liquid crystal display (LCD) that enables a user to interact with a user interface of the computer terminal 10 (or mobile device).

[0038] Under the above operating environment, this application provides Figure 2 The execution plan processing method of the database statement shown. Figure 2 1 is a flowchart of an optional database statement execution plan processing method according to an embodiment of the present application. Figure 2 As shown, the method comprises the following steps:

[0039] Step S201, obtaining preset execution plans respectively set for target database statements at L data levels.

[0040] In step S201, L is an integer greater than or equal to 1, and each preset execution plan includes an identifier of the target database statement, preset execution steps, a single execution logical read, a single execution time, and a single execution efficiency, wherein the single execution logical read is used to characterize the speed at which the database accesses data using the memory cache, and the single execution efficiency is used to characterize the CPU utilization of the database when processing database statements.

[0041] Optionally, the execution plan processing system of the database statement will first pre-analyze the target database statement, collect and establish a series of preset execution plans for its behavior under L different data levels.

[0042] Optionally, L is a non-negative integer parameter that defines the various data sizes that the system is prepared to adapt to, thereby ensuring the wide applicability and high flexibility of the method.

[0043] Optionally, each preset execution plan is a composite data structure including an identifier of a target database statement, preset execution steps, and three key indicators closely related to execution efficiency: single execution logical read, single execution time, and single execution efficiency.

[0044] Optionally, the identifier is used to uniquely identify a database statement, ensuring that the system can accurately distinguish different SQL statements and provide a basis for subsequent execution plan matching and performance comparison.

[0045] Optionally, the preset execution steps describe in detail the optimal path and operation sequence for the database system to execute SQL statements under a specific data level, which is the key to optimizing execution efficiency.

[0046] Optionally, a single execution logical read quantifies the number of times the database system uses the memory cache to access data when executing database operations, which directly reflects the data access speed and cache efficiency and has an important impact on database performance.

[0047] Optionally, the single execution time records the average time required to execute a database statement, which is an important parameter for evaluating the database response speed and overall performance.

[0048] Optionally, single execution efficiency is a measure based on CPU time, which is used to evaluate the resource consumption of the database when processing a specific statement. It indirectly reflects the CPU utilization and is an important consideration in optimizing the execution plan.

[0049] Optionally, the selection of database execution plan is closely related to the size of table data. Based on the table data size, an optional sample execution plan resource pool can be established. The resource pool definition is: plan = {(sql id, table data level c1, execution plan plan_hash_value_1, single execution logical read buffergets_per_exec_1, single execution time elapsed_time_per_exec_1, single execution efficiency cpu_time_per_exec_1), (sql id, table data level c2, execution plan plan_hash_value_2, single execution logical read buffergets_per_exec_2, single execution time elapsed_time_per_exec_2, single execution efficiency cpu_time_per_exec_2), (sql id, table data level c3, execution plan plan_hash_value_3, single execution logical read buffergets_per_exec_3, single execution time elapsed_time_per_exec_3, single execution efficiency cpu_time_per_exec_3)……}.

[0050] Step S202: when the target database statement is executed, the target data level and target execution information actually generated by the target database statement are collected.

[0051] Optionally, during the execution of the target database statement, the execution plan processing system of the database statement will collect the actual data scale of the data processed by the statement in real time, that is, the target data level. Collecting the target data level is directly related to the efficiency and applicability of the execution plan. In a database environment, changes in data level (such as an increase or decrease in the number of rows in a data table) may significantly affect the optimality of the execution plan, resulting in fluctuations in response time and resource consumption.

[0052] Optionally, the execution plan processing system of the database statement will also collect in real time the target execution information generated by the target database statement during the current execution. The target execution information includes, but is not limited to, a detailed description of the execution steps, and indicators directly related to execution efficiency, such as the number of logical reads per execution, the time taken per execution, and the CPU time occupied per execution. The execution information collected in real time provides the execution plan processing system of the database statement with the actual running effect of the execution plan, and is key data for evaluating whether the execution plan meets the expected performance standards.

[0053] Step S203: Detect whether the execution step in the target execution information matches any preset execution step in the L preset execution plans.

[0054] Optionally, during the execution of the target database statement, the execution plan processing system of the database statement will automatically collect and analyze the execution information of the statement, including specific execution steps and related performance indicators. Subsequently, the system will detect whether the execution steps included in the target execution information are consistent with the steps of any execution plan in the preset execution plan library.

[0055] Optionally, the detection process is performed based on a hash value of the execution step, which is a unique representation of the execution step and can quickly and efficiently perform matching judgments.

[0056] Step S204, when it is detected that the execution steps in the target execution information do not match the preset execution steps in the L preset execution plans, determine the processing operation of the preset execution plan according to the target data level, the target execution information and the L preset execution plans.

[0057] Optionally, the execution plan processing system of the database statement collects and analyzes the execution information in real time during the execution of the target database statement, including the specific description of the execution steps and the performance indicators related thereto. When it is detected that the execution steps in the target execution information do not match the hash value of any preset execution step in the L pre-stored execution plans, this indicates that the current execution path may not meet the preset execution standard or efficiency target.

[0058] Optionally, after identifying the mismatch in execution steps, the execution plan processing system of the database statement will determine the processing operation of the preset execution plan based on the target data level, target execution information and the comprehensive data of L pre-stored execution plans. This may include updating, adding or repairing the execution plan to ensure that the database can maintain efficient and stable execution efficiency in a changing operating environment.

[0059] From the contents of step S201 to step S204, it can be seen that the present application first collects the preset execution plans of database statements under L different data levels, where L is an integer greater than or equal to 1. Each preset execution plan contains the unique identifier of the statement, the preset execution steps, and indicators related to execution efficiency, such as single execution logical read, single execution time, and single execution efficiency. The logical read indicator is used to evaluate the efficiency of the database accessing data through the memory cache, while the execution efficiency directly reflects the occupancy of CPU resources when processing statements.

[0060] Secondly, when the target database statement is executed, the actual data level and execution information generated by the statement will be collected in real time, including but not limited to the actual execution steps and efficiency indicators.

[0061] Subsequently, the application will detect whether the actual execution steps match the execution steps in the preset execution plan. If it is detected that the actual execution steps do not match all preset execution plans, the application will automatically determine and perform adjustment or repair operations based on the actual data volume, execution information, and existing preset execution plans. This not only covers the addition of new preset execution plans to adapt to the growth of data volume, but also includes updating the current preset execution plan to optimize execution efficiency.

[0062] It can be seen that through the technical solution of the present application, the purpose of automated monitoring and adaptive adjustment is achieved, thereby achieving the technical effects of optimizing database performance, reducing operation and maintenance costs, and improving system stability and response speed, thereby solving the technical problems in the prior art of manual adjustment and optimization of query statement execution plans, resulting in low processing efficiency and unstable performance.

[0063] In an optional embodiment, the execution plan processing system of the database statement first determines that the target execution information matches the i-th preset execution plan when the hash value of the execution step in the target execution information is the same as the hash value of the preset execution step of the i-th preset execution plan among L preset execution plans, where i is a positive integer less than or equal to L, and then determines that the target execution information does not match any of the L preset execution plans when the hash value of the execution step in the target execution information is different from the hash values ​​of all the preset execution steps in the L preset execution plans.

[0064] Optionally, when the target database statement is executed, the execution plan processing system of the database statement will automatically generate a hash value of the execution step in the target execution information. A hash value is a variable represented by a fixed-length numerical value that can be uniquely calculated from the description of the execution step, ensuring rapid identification and comparison of the execution step between different execution plans.

[0065] Optionally, the execution plan processing system of the database statement compares the hash value of the execution step in the target execution information with the hash values ​​of L preset execution plans in the preset execution plan library one by one. When the execution step hash value of the target execution information is equal to the execution step hash value of the i-th preset execution plan (where i is a positive integer, and i≤L) in the preset execution plan library, the system will determine that the target execution information completely matches the i-th preset execution plan. The matching process is based on the principle that the same data input will produce the same hash value, ensuring the accurate matching of the execution plan.

[0066] Optionally, when the execution plan processing system of the database statement detects that the execution step hash value in the target execution information is different from the execution step hash values ​​of all L preset execution plans in the preset execution plan library, this means that there is no matching relationship between the target execution step and any preset execution step in the preset execution plan library. At this time, the system will determine that the target execution information does not match any preset execution plan.

[0067] From the above content, it can be seen that the execution plan processing system of the database statement is based on the execution plan matching detection of hash values, which ensures that the database system can adaptively adjust the execution strategy according to the changes in the real-time data level and execution information, thereby improving the automation level and overall performance of database operation and maintenance.

[0068] In an optional embodiment, after determining that the target execution information matches the i-th preset execution plan, the execution plan processing system of the database statement first calculates the ratio between the single execution logical read in the target execution information and the single execution logical read in the i-th preset execution plan to obtain a first indicator, then calculates the ratio between the single execution time in the target execution information and the single execution time in the i-th preset execution plan to obtain a second indicator, and then calculates the ratio between the single execution efficiency in the target execution information and the single execution efficiency in the i-th preset execution plan to obtain a third indicator, and finally generates prompt information based on the first indicator, the second indicator, and the third indicator.

[0069] Optionally, when it is determined that the target execution information matches the i-th preset execution plan, the execution plan processing system of the database statement calculates the ratio between the number of single execution logical reads in the target execution information and the corresponding index in the preset execution plan to obtain a first index. The number of logical reads is a measure of the efficiency of the database system in utilizing the memory cache when executing a query. A smaller number of logical reads means more efficient cache utilization and faster data access speed.

[0070] Optionally, the execution plan processing system of the database statement calculates the ratio between the single execution time in the target execution information and the single execution time in the i-th preset execution plan to obtain a second indicator. Execution time is an important indicator for evaluating database response speed and overall performance. Shorter execution time means faster query response and better overall performance.

[0071] Optionally, the execution plan processing system of the database statement obtains the third indicator by calculating the ratio between the single execution efficiency in the scientific computing target execution information and the single execution efficiency in the preset execution plan. Execution efficiency, especially CPU time utilization, is a key parameter for measuring database resource consumption and processing capacity. Higher execution efficiency means less CPU time consumption and greater concurrent processing capacity.

[0072] Optionally, based on the calculation results of the first indicator, the second indicator, and the third indicator, the execution plan processing system of the database statement will generate corresponding prompt information for feedback of the change in efficiency of the execution plan. These prompt information not only includes the specific value of the efficiency ratio, but also may include the matching status of the target execution information and the preset execution plan, the change trend of the execution efficiency, and possible optimization suggestions.

[0073] Optionally, there is an execution plan example generated based on a SQL statement. When the execution plan plan_hash_value_n generated by the SQL statement is in the resource pool, determine whether the following indicators meet the database performance baseline requirements:

[0074] Single execution logical read (i.e. first index):

[0075] bv=buffergets_per_exec_new / buffergets_per_exec_old;

[0076] Single execution time (the second indicator):

[0077] ev=elapsed_time_per_exec_new / buffergets_per_exec_old;

[0078] Single execution efficiency (the third indicator):

[0079] cv=cpu_time_per_exec_new / plan0_cpu_time_per_exec_old.

[0080] From the above content, it can be seen that the execution plan processing system of the database statement obtains the first indicator, the second indicator, and the third indicator by calculating, and generates prompt information according to the first indicator, the second indicator, and the third indicator. This ensures that the execution plan processing system of the database statement can adaptively adjust the execution strategy according to changes in the real-time data level and execution efficiency, thereby improving the intelligence level and overall performance of database operation and maintenance.

[0081] In an optional embodiment, the execution plan processing system of the database statement first generates a first prompt message when the first indicator, the second indicator and the third indicator are all less than or equal to their respective corresponding preset indicator values, wherein the first prompt message is used to indicate that the target execution information meets the preset requirements, and then obtains first statistical information of a data table associated with the target database statement when any one of the first indicator, the second indicator and the third indicator is greater than their respective corresponding preset indicator values, wherein the first statistical information is used to indicate data features of the data table associated with the target database statement, and then updates the target execution information of the target database statement to the second execution information according to the first statistical information, and finally generates a second prompt message according to the second execution information and the i-th preset execution plan, wherein the second prompt information is used to indicate whether the second execution information meets the preset requirements.

[0082] Optionally, the first prompt information indicates that the target execution information meets preset execution requirements in terms of the number of logical reads, execution time and execution efficiency.

[0083] Optionally, when the execution plan processing system of the database statement detects that any one of the indicators in the efficiency ratio is greater than a preset indicator value, it indicates that there is a mismatch between the efficiency state of the target execution information and the preset execution plan, which may be caused by a change in data characteristics. At this time, the system will automatically obtain the first statistical information of the data table associated with the target database statement. By obtaining the first statistical information, the system can analyze the impact of the change in data characteristics on the efficiency of the execution plan.

[0084] Optionally, the first statistical information, including data distribution, data density, number of unique values, etc., is a key indicator for measuring data characteristics and is crucial for evaluating the applicability of the execution plan.

[0085] Optionally, based on the acquired first statistical information, the database statement execution plan processing system will adjust the target execution information of the target database statement to generate second execution information. This process is intended to ensure that the execution plan matches the current data features and improve execution efficiency.

[0086] Optionally, the execution plan processing system of the database statement recalculates the efficiency index based on the updated second execution information and the i-th preset execution plan, and generates second prompt information. The second prompt information is used to scientifically characterize whether the updated execution information meets the preset execution requirements, including the optimization goals of the number of logical reads, execution time, and execution efficiency.

[0087] Optionally, there is an example of a prompt message generated based on a SQL statement:

[0088] a. If bv, ev, and cv are all <1, it is considered that the efficiency of the execution plan meets expectations and there is no need to update the execution plan resource pool (i.e., the first prompt message is displayed when the first indicator, the second indicator, and the third indicator are all less than or equal to their corresponding preset indicator values).

[0089] b. If one, two or all of bv, ev, and cv are > n (n is greater than 1 and can be defined based on the actual production database situation), it is considered that the current execution plan has not changed, but the execution efficiency has deteriorated, and statistical information (i.e., the first statistical information) is collected for the tables involved according to the order of magnitude standard.

[0090] Set table analysis sampling percentage Estimate_Percent, 10% below 1G, 1-10G 1%, 0.1% above 10G: BEGIN

[0091] SYS.DBMS_STATS.GATHER_TABLE_STATS(

[0092] OwnName=>'owner'

[0093] ,TabName=>'tablename'

[0094] ,Estimate_Percent=>1

[0095] ,degree=>8

[0096] ,Cascade=>TRUE);

[0097] END;

[0098] After collecting statistical information, continue to judge the change rate of bv, ev, and cv. If one, two, or all of bv, ev, and cv are still > n, the program will issue an alarm prompt [execution efficiency of database statement sql_id has deteriorated, efficiency has deteriorated cpu_time_per_exec_n / cpu_time_per_exec_2, current execution plan plan_hash_value_n, historical optimal execution plan plan_hash_value_2] (that is, the second prompt information is generated according to the second execution information and the i-th preset execution plan).

[0099] From the above content, it can be seen that the generation of the first prompt information confirms the efficiency status of the execution plan. When the efficiency ratio exceeds the limit, the execution plan processing system of the database statement obtains the first statistical information, analyzes the changes in data characteristics, and intelligently adjusts the execution information. Finally, the adjustment effect is fed back through the second prompt information, ensuring that the database system can adaptively optimize the execution strategy and maintain efficient and stable operation when facing changes in data characteristics.

[0100] In an optional embodiment, the execution plan processing system of the database statement first determines whether to perform a plan addition operation based on the L preset execution plans according to the target execution information when the target data level is different from the L data levels corresponding to the L preset execution plans, wherein the plan addition operation is used to add a preset execution plan including the target data level and the target execution information, and then when the target data level is the same as the jth data level among the L data levels, it is determined according to the target execution information whether to update the jth preset execution plan corresponding to the jth data level, wherein j is an integer greater than or equal to 1.

[0101] Optionally, when the execution plan processing system of the database statement finds that the target data level is different from the data levels corresponding to the L execution plans in the preset execution plan library, it will determine, based on the target execution information, whether it is necessary to add a new execution plan that matches the target data level and execution information based on the existing L preset execution plans.

[0102] Optionally, when the target data level matches the jth data level among L data levels in the preset execution plan library, the execution plan processing system of the database statement will again conduct an in-depth efficiency analysis of the preset execution plan corresponding to the jth data level based on the target execution information to determine whether the target execution information is better than the current preset execution plan, or whether the execution strategy needs to be adjusted due to changes in data characteristics.

[0103] Optionally, if the execution plan processing system of the database statement determines that the target execution information performs better than the current preset execution plan in terms of execution efficiency, response time, and resource consumption, it will decide to update the jth preset execution plan. Based on real-time performance evaluation and historical data comparison, this ensures continuous optimization of the execution plan and improves the operating efficiency of the database under stable data levels.

[0104] From the above content, it can be seen that when the generated execution plan is not the same as that in the resource pool, the execution plan processing system of the database statement determines whether to execute the relevant processing operations based on the data level corresponding to the current database statement and the corresponding data level in the resource pool, thereby ensuring the dynamic adaptability and continuous optimization of the execution plan.

[0105] In an optional embodiment, the execution plan processing system of the database statement first calculates the ratio of the target data level to the kth data level of the target database statement to obtain a first value, wherein k is an integer greater than or equal to 1, and the difference between the kth data level and the target data level is less than a second preset threshold value, and then calculates the ratio of the single execution efficiency in the target execution information to the single execution efficiency in the preset execution plan corresponding to the kth data level to obtain a second value, and then, when the first value is greater than or equal to the second value, the target data level and target execution information are added based on L data levels, and finally, when the first value is less than the second value, a third prompt information is generated and the new operation is not performed, wherein the third prompt information is used to prompt that there is an abnormality in the execution efficiency of the target execution information.

[0106] Optionally, the execution plan processing system of the database statement monitors the changes in data levels during the execution of the target database statement in real time, and calculates the ratio between the target data level and the kth data level in the execution plan library (k is an integer greater than or equal to 1) to obtain a first value.

[0107] Optionally, the execution plan processing system of the database statement sets a second preset threshold value to detect whether the magnitude of the data magnitude change is within an expected reasonable range. If the difference between the kth data magnitude and the target data magnitude is less than the second preset threshold value, it indicates that the magnitude of the data magnitude change is within an acceptable range for the system.

[0108] Optionally, the second numerical value is based on a comprehensive analysis of scientific expectations of database execution efficiency and actual operating data, and is used to evaluate the impact of changes in data magnitude on execution efficiency.

[0109] Optionally, when the first value is greater than or equal to the second value, it indicates that the change in execution efficiency caused by the change in the target data magnitude is within a reasonable expectation. The system will decide whether to add the target data magnitude and target execution information on the basis of L data magnitudes to adapt to the dynamic change of the data scale. Conversely, if the first value is less than the second value, it indicates that the execution efficiency is abnormal. The system will generate a third prompt message and automatically avoid performing the add operation to prevent the ineffective expansion of the execution plan.

[0110] Optionally, there is an example of judging whether to perform an add operation when the currently generated data magnitude is inconsistent with all data magnitudes in the current resource pool based on an SQL statement:

[0111] If the data magnitude cnew is inconsistent with all table data magnitudes in the current resource pool (that is, the currently generated data magnitude is inconsistent with all data magnitudes in the current resource pool):

[0112] a. If cnew / c1 (i.e., the first value) > cpu_time_per_exec_new / cpu_time_per_exec_1 (i.e., the second value), it means that the change in the execution plan caused by the increase in the table data volume and the increase in execution efficiency are within a reasonable range. Then, the execution plan cpu_time_per_exec_new will be added to the resource pool as the optimal execution plan for the table with the data magnitude cnew: (sqlid, table data magnitude cnew, execution plan plan_hash_value_new, logical reads per execution buffergets_per_exec_new, elapsed time per execution elapsed_time_per_exec_new, cpu time per execution cpu_time_per_exec_new);

[0113] b. If cnew / c1 < cpu_time_per_exec_new / cpu_time_per_exec_1 < n (n > 1, which can be defined according to the actual production database situation), it means that the change in the execution plan caused by the increase in the table data volume and the increase in execution efficiency are abnormal, and warnings and manual intervention are required: [The execution plan of the database statement sql_id has changed, the data volume has increased by cnew / c1 times, the current execution plan is plan_hash_value_new, and the historical optimal execution plan is plan_hash_value_1. Please handle it manually]. (That is, when the first value is less than the second value, a third prompt message is generated and the add operation is not performed).

[0114] As can be seen from the above, the execution plan processing system of the database statement ensures the dynamic adaptability and continuous optimization of the execution plan through efficiency evaluation and threshold detection, improving the intelligence and performance of database operation and maintenance.

[0115] In an optional embodiment, the execution plan processing system of the database statement first updates the j-th preset execution plan to the target execution information when the single execution efficiency in the target execution information is less than the single execution efficiency in the j-th preset execution plan, then takes the product of the single execution efficiency in the j-th preset execution plan and the corresponding preset indicator value as the third value, and then generates a fourth prompt information and does not perform the update operation when the single execution efficiency in the target execution information is greater than the third value, wherein the fourth prompt information is used to prompt that the execution efficiency of the target execution information is lower than the third preset threshold.

[0116] Optionally, the execution plan processing system of the database statement monitors the execution of the target database statement in real time. If it is detected that the single execution efficiency (i.e., CPU time utilization) in the target execution information is lower than the single execution efficiency of the jth preset execution plan (j is an integer greater than or equal to 1) in the preset execution plan library, it indicates that the efficiency performance of the current execution information has not reached the historical optimal level. In this case, the system updates the jth preset execution plan to the target execution information.

[0117] Optionally, the third value is calculated based on the product of the single execution efficiency in the j-th preset execution plan and the corresponding preset indicator value, and can measure the change in the execution plan efficiency.

[0118] Optionally, the execution plan processing system of the database statement detects whether the single execution efficiency in the target execution information exceeds a third value. When the single execution efficiency of the target execution information is greater than the third value, it indicates that the execution efficiency has dropped abnormally and exceeds the reasonable range preset by the system. In this case, the system will generate a fourth prompt information indicating that the execution efficiency of the target execution information is lower than the third preset threshold, and automatically avoid executing the update operation to prevent invalid optimization of the execution plan library.

[0119] Optionally, there is an example based on a SQL statement to determine whether to perform an update operation when the generated data level is consistent with one of the data levels in the current resource pool:

[0120] If the data level cn is consistent with the data level of any table in the current resource pool, for example, the level cn is equal to c2, then the execution plan is considered to have changed, and the single execution efficiency cpu_time_per_exec is further determined (that is, the current generated data level is consistent with the data level of all data in the current resource pool):

[0121] a. If cpu_time_per_exec_n (i.e., the single - execution efficiency in the target execution information) < cpu_time_per_exec_2 (i.e., the single - execution efficiency in the preset execution plan), it is considered that the efficiency of this execution plan is better than the current one. Then, take cpu_time_per_exec_n of this execution plan as the optimal execution plan of this data magnitude table and replace the optimal execution plan in the resource pool: (sql id, table data magnitude c2, execution plan plan_hash_value_n, single - execution logical read buffergets_per_exec_n, single - execution time elapsed_time_per_exec_n, single - execution efficiency cpu_time_per_exec_n);

[0122] b. If cpu_time_per_exec_n > n * cpu_time_per_exec_2 (i.e., the third value), it is considered that the execution efficiency is lower than the current one. The program makes an alarm prompt [The execution plan of database statement sql_id has changed and the efficiency has deteriorated cpu_time_per_exec_n / cpu_time_per_exec_2, the current execution plan plan_hash_value_n, the historical optimal execution plan plan_hash_value_2]. At the same time, collect and statistics information for the involved tables according to the magnitude standard, and set the table analysis sampling percentage Estimate_Percent, 10% for less than 1G, 1% for 1 - 10G, and 0.1% for more than 10G:

[0123]

[0124] As can be seen from the above, the execution - plan processing system of database statements realizes the dynamic monitoring and intelligent adjustment of the execution - plan efficiency through the efficiency evaluation and threshold - detection process.

[0125] In an alternative embodiment, the execution - plan processing system of database statements first obtains the second statistical information of the data tables associated with the target database statement. Among them, the second statistical information is used to characterize the data characteristics of the data tables associated with the target database statement. Then, update the target execution information to the third execution information according to the second statistical information. Then, when the execution steps in the third execution information are different from the preset execution steps in the j - th preset execution plan, take the preset execution steps in the j - th preset execution plan as the execution steps of the target database statement.

[0126] Optionally, the second statistical information covers key data characteristics such as data distribution, data density, and the number of unique values, and is the basis for evaluating the efficiency and applicability of the database execution plan.

[0127] Optionally, based on the acquired second statistical information, the execution plan processing system of the database statement will analyze the impact of changes in data characteristics on the efficiency of the execution plan, thereby updating the target execution information to the third execution information.

[0128] Optionally, after updating the execution information, the execution plan processing system of the database statement will verify whether the execution steps in the third execution information are the same as the preset execution steps of the jth preset execution plan in the preset execution plan library (j is an integer greater than or equal to 1).

[0129] Optionally, there is an example based on a SQL statement, which shows that after the fourth prompt information is generated, after collecting statistical information, the current execution plan is checked, and if the optimal execution plan has not been returned, the execution program restores the statistical information to the optimal execution plan:

[0130] After collecting statistics, check the current execution plan. If it does not return to the optimal execution plan plan_hash_value_2, execute the program to restore the statistics to the optimal execution plan:

[0131] exec DBMS_STATS.RESTORE_TABLE_STATS(ownname=>'owner',tabname=>'tablename',as_of_timestamp=>to_timestamp('time','YYYYMMDDHH24MISS'));

[0132] From the above content, we can see that the execution plan processing system of database statements ensures the best match between execution plans and data features through real-time data feature monitoring and scientific execution strategy adjustment.

[0133] It can be seen from the above content that according to the technical solution of this application, at least the following technical effects can be achieved:

[0134] 1. Effectively manage the database execution plan, and make your own judgment and handle the problem when the execution efficiency of database statements is not optimal.

[0135] 2. Applicable to databases of different types and versions.

[0136] According to another aspect of the embodiment of the present application, a database statement execution plan processing device is also provided. It should be noted that the database statement execution plan processing device of the embodiment of the present application can be used to execute the database statement execution plan processing method provided in the embodiment of the present application. Figure 3 is a schematic diagram of an optional database statement execution plan processing device according to an embodiment of the present application, such as Figure 3As shown, the execution plan processing device for a database statement includes: an acquisition unit 301 , a collection unit 302 , a detection unit 303 , and a determination unit 304 .

[0137] An acquisition unit 301 acquires preset execution plans respectively set for a target database statement at L data levels, wherein L is an integer greater than or equal to 1, and each preset execution plan includes an identifier of the target database statement, preset execution steps, a single execution logical read, a single execution time, and a single execution efficiency, wherein the single execution logical read is used to characterize the speed at which the database uses the memory cache to access data, and the single execution efficiency is used to characterize the CPU utilization of the database when processing database statements; a collection unit 302 collects the target data level and target execution information actually generated by the target database statement when the target database statement is executed; a detection unit 303 detects whether the execution steps in the target execution information match any one of the preset execution steps in the L preset execution plans; a determination unit 304 determines a processing operation for the preset execution plan according to the target data level, the target execution information, and the L preset execution plans when it is detected that the execution steps in the target execution information do not match the preset execution steps in the L preset execution plans.

[0138] Optionally, the detection unit 303 includes: a first determination subunit and a second determination subunit. The first determination subunit is used to determine that the target execution information matches the i-th preset execution plan when the hash value of the execution step in the target execution information is the same as the hash value of the preset execution step of the i-th preset execution plan in the L preset execution plans, where i is a positive integer less than or equal to L; the second determination subunit is used to determine that the target execution information does not match any of the L preset execution plans when the hash value of the execution step in the target execution information is different from the hash value of all the preset execution steps in the L preset execution plans.

[0139] Optionally, the execution plan processing device for database statements further includes: a first calculation unit, a second calculation unit, a third calculation unit, and a generation unit. The first calculation unit is used to calculate the ratio between the single execution logical read in the target execution information and the single execution logical read in the i-th preset execution plan to obtain a first indicator; the second calculation unit is used to calculate the ratio between the single execution time in the target execution information and the single execution time in the i-th preset execution plan to obtain a second indicator; the third calculation unit is used to calculate the ratio between the single execution efficiency in the target execution information and the single execution efficiency in the i-th preset execution plan to obtain a third indicator; the generation unit is used to generate prompt information according to the first indicator, the second indicator, and the third indicator.

[0140] Optionally, the generating unit includes: a first generating subunit, a first acquiring subunit, a first updating subunit, and a second generating subunit. The first generating subunit is used to generate a first prompt message when the first indicator, the second indicator, and the third indicator are all less than or equal to their respective corresponding preset indicator values, wherein the first prompt message is used to indicate that the target execution information meets the preset requirements; the first acquiring subunit is used to acquire the first statistical information of the data table associated with the target database statement when any one of the first indicator, the second indicator, and the third indicator is greater than their respective corresponding preset indicator values, wherein the first statistical information is used to indicate the data characteristics of the data table associated with the target database statement; the first updating subunit is used to update the target execution information of the target database statement to the second execution information according to the first statistical information; the second generating subunit is used to generate a second prompt message according to the second execution information and the i-th preset execution plan, wherein the second prompt message is used to indicate whether the second execution information meets the preset requirements.

[0141] Optionally, the determination unit 304 includes: a first processing subunit and a third determination subunit. The first processing subunit is used to determine whether to perform a plan addition operation on the basis of the L preset execution plans according to the target execution information when the target data level is different from the L data levels corresponding to the L preset execution plans, wherein the plan addition operation is used to add a preset execution plan including the target data level and the target execution information; the third determination subunit is used to determine whether to perform an update operation on the jth preset execution plan corresponding to the jth data level according to the target execution information when the target data level is the same as the jth data level among the L data levels, wherein j is an integer greater than or equal to 1.

[0142] Optionally, the first processing subunit includes: a first calculation module, a second calculation module, a first processing module and a first generation module. The first calculation module is used to calculate the ratio of the target data level to the kth data level of the target database statement to obtain a first value, wherein k is an integer greater than or equal to 1, and the difference between the kth data level and the target data level is less than a second preset threshold; the second calculation module is used to calculate the ratio of the single execution efficiency in the target execution information to the single execution efficiency in the preset execution plan corresponding to the kth data level to obtain a second value; the first processing module is used to add the target data level and target execution information on the basis of L data levels when the first value is greater than or equal to the second value; the first generation module is used to generate a third prompt information and not perform the new operation when the first value is less than the second value, wherein the third prompt information is used to prompt that the execution efficiency of the target execution information is abnormal.

[0143] Optionally, the third determination subunit includes: a second processing module, a third processing module, and a first generation module. The second processing module is used to update the j-th preset execution plan to the target execution information when the single execution efficiency in the target execution information is less than the single execution efficiency in the j-th preset execution plan; the third processing module is used to take the product of the single execution efficiency in the j-th preset execution plan and the corresponding preset indicator value as the third value; the first generation module is used to generate fourth prompt information and not perform the update operation when the single execution efficiency in the target execution information is greater than the third value, wherein the fourth prompt information is used to prompt that the execution efficiency of the target execution information is lower than the third preset threshold.

[0144] Optionally, the first generation module includes: a first acquisition submodule, a first processing submodule, and a second processing submodule. The first acquisition submodule is used to acquire second statistical information of a data table associated with a target database statement, wherein the second statistical information is used to characterize data features of the data table associated with the target database statement; the first processing submodule is used to update the target execution information to third execution information according to the second statistical information; and the second processing submodule is used to use the preset execution steps in the jth preset execution plan as the execution steps of the target database statement when the execution steps in the third execution information are different from the preset execution steps in the jth preset execution plan.

[0145] An embodiment of the present application may provide an electronic device, Figure 4 is a structural block diagram of an electronic device according to an embodiment of the present application. Figure 4 As shown, the electronic device may include: one or more ( Figure 4 Only one is shown) a processor, a memory, a storage controller, and a peripheral interface, wherein the peripheral interface is connected to a radio frequency module, an audio module, and a display.

[0146] Among them, the memory can be used to store software programs and modules, such as program instructions / modules corresponding to the methods and devices in the embodiments of the present application, and the processor executes various functional applications and data processing by running the software programs and modules stored in the memory, that is, realizing the above-mentioned method. The memory may include a high-speed random access memory, and may also include a non-volatile memory, such as one or more magnetic storage devices, flash memory, or other non-volatile solid-state memory. In some instances, the memory may further include a memory remotely arranged relative to the processor, and these remote memories may be connected to the terminal via a network. Examples of the above-mentioned network include, but are not limited to, the Internet, an intranet, a local area network, a mobile communication network, and combinations thereof.

[0147] It can be understood by those skilled in the art that Figure 4The structure shown is for illustration only, and the electronic device may also be a smart phone (such as an Android phone, an iOS phone, etc.), a tablet computer, a PDA, a mobile Internet device (Mobile Internet Devices, MID), a PAD, or other terminal devices. Figure 4 The structure of the electronic device is not limited. Figure 4 More or fewer components (such as network interfaces, display devices, etc.) shown in, or having Figure 4 Different configurations are shown.

[0148] A person of ordinary skill in the art can understand that all or part of the steps in the various methods of the above embodiments can be completed by instructing the hardware related to the terminal device through a program, and the program can be stored in a computer-readable storage medium, and the storage medium may include: a flash drive, a read-only memory (ROM), a random access memory (RAM), a magnetic disk or an optical disk, etc.

[0149] According to another aspect of the present application, a computer-readable storage medium is also provided, wherein a computer program is stored in the computer-readable storage medium, wherein when the computer program is executed, the device where the computer-readable storage medium is located executes the above-mentioned execution plan processing method of the database statement.

[0150] According to another aspect of the present application, a computer program product is also provided, wherein the computer program product includes computer instructions, wherein when the computer instructions are executed, the device where the computer program product is located executes the above-mentioned execution plan processing method of the database statement.

[0151] The above-mentioned embodiments or examples disclosed in the present application are not exhaustive, but are only illustrative of some embodiments or examples, and are not intended to be specific limitations on the scope of protection disclosed in the present application. In the absence of contradiction, each step in a certain embodiment or example in the present application can be implemented as an independent example, and the steps can be combined arbitrarily. For example, the scheme after removing some steps in a certain embodiment or example can also be implemented as an independent example, and the order of the steps in a certain embodiment or example can be arbitrarily exchanged. In addition, the optional methods or optional examples in a certain embodiment or example can be combined arbitrarily; in addition, the various embodiments or examples can be combined arbitrarily, for example, some or all steps of different embodiments or examples can be combined arbitrarily, and a certain embodiment or example can be combined arbitrarily with the optional methods or optional examples of other embodiments or examples.

[0152] The serial numbers of the above-mentioned embodiments of the present application are for description only and do not represent the advantages or disadvantages of the embodiments.

[0153] In the above embodiments of the present application, the description of each embodiment has its own emphasis. For parts that are not described in detail in a certain embodiment, please refer to the relevant description of other embodiments.

[0154] In the several embodiments provided in this application, it should be understood that the disclosed technical content can be implemented in other ways. Among them, the device embodiments described above are only schematic. For example, the division of the units can be a logical function division. There may be other division methods in actual implementation. For example, multiple units or components can be combined or integrated into another system, or some features can be ignored or not executed. Another point is that the mutual coupling or direct coupling or communication connection shown or discussed can be through some interfaces, indirect coupling or communication connection of units or modules, which can be electrical or other forms.

[0155] The units described as separate components may or may not be physically separated, and the components shown as units may or may not be physical units, that is, they may be located in one place or distributed on multiple units. Some or all of the units may be selected according to actual needs to achieve the purpose of the present embodiment.

[0156] In addition, each functional unit in each embodiment of the present application may be integrated into one processing unit, or each unit may exist physically separately, or two or more units may be integrated into one unit. The above-mentioned integrated unit may be implemented in the form of hardware or in the form of software functional units.

[0157] If the integrated unit is implemented in the form of a software functional unit and sold or used as an independent product, it can be stored in a computer-readable storage medium. Based on this understanding, the technical solution of the present application, in essence, or the part that contributes to the prior art or all or part of the technical solution can be embodied in the form of a software product, and the computer software product is stored in a storage medium, including a number of instructions to enable a computer device (which can be a personal computer, a server or a network device, etc.) to perform all or part of the steps of the method described in each embodiment of the present application. The aforementioned storage medium includes: U disk, read-only memory (ROM, Read-Only Memory), random access memory (RAM, Random Access Memory), mobile hard disk, disk or optical disk and other media that can store program codes.

[0158] The above is only a preferred implementation of the present application. It should be pointed out that for ordinary technicians in this technical field, several improvements and modifications can be made without departing from the principles of the present application. These improvements and modifications should also be regarded as the scope of protection of the present application.

Claims

1. A method for processing an execution plan of a database statement, characterized in that: include: Obtain preset execution plans respectively set for the target database statement at L data levels, where L is an integer greater than or equal to 1, and each preset execution plan includes an identifier of the target database statement, preset execution steps, single execution logical read, single execution time, and single execution efficiency, where the single execution logical read is used to characterize the speed at which the database uses the memory cache to access data, and the single execution efficiency is used to characterize the CPU utilization of the database when processing the database statement; When the target database statement is executed, collecting the target data magnitude and target execution information actually generated by the target database statement; Detecting whether the execution step in the target execution information matches any one of the L preset execution steps in the preset execution plans; When it is detected that the execution steps in the target execution information do not match the preset execution steps in the L preset execution plans, the processing operation of the preset execution plan is determined according to the target data level, the target execution information and the L preset execution plans.

2. The method for processing an execution plan of a database statement according to claim 1, characterized in that: Detecting whether the execution step in the target execution information matches any one of the preset execution steps in the L preset execution plans includes: When the hash value of the execution step in the target execution information is the same as the hash value of the preset execution step of the i-th preset execution plan among the L preset execution plans, it is determined that the target execution information matches the i-th preset execution plan, where i is a positive integer less than or equal to L; When the hash value of the execution step in the target execution information is different from the hash values ​​of all the preset execution steps in the L preset execution plans, it is determined that the target execution information does not match any of the L preset execution plans.

3. The method for processing an execution plan of a database statement according to claim 2, characterized in that: After determining that the target execution information matches the i-th preset execution plan, the execution plan processing method of the database statement further includes: Calculate the ratio between the single execution logical read in the target execution information and the single execution logical read in the i-th preset execution plan to obtain a first indicator; Calculate the ratio between the single execution time in the target execution information and the single execution time in the i-th preset execution plan to obtain a second indicator; Calculate the ratio between the single execution efficiency in the target execution information and the single execution efficiency in the i-th preset execution plan to obtain a third indicator; Prompt information is generated according to the first indicator, the second indicator, and the third indicator.

4. The method for processing an execution plan of a database statement according to claim 3, characterized in that: Generating prompt information according to the first indicator, the second indicator, and the third indicator includes: When the first indicator, the second indicator, and the third indicator are all less than or equal to their respective corresponding preset indicator values, generating first prompt information, wherein the first prompt information is used to indicate that the target execution information meets the preset requirements; When any one of the first indicator, the second indicator, and the third indicator is greater than the corresponding preset indicator value, obtaining first statistical information of the data table associated with the target database statement, wherein the first statistical information is used to characterize data features of the data table associated with the target database statement; updating the target execution information of the target database statement to second execution information according to the first statistical information; Second prompt information is generated according to the second execution information and the i-th preset execution plan, wherein the second prompt information is used to indicate whether the second execution information meets the preset requirement.

5. The method for processing an execution plan of a database statement according to claim 1, characterized in that: When it is detected that the execution steps in the target execution information do not match the preset execution steps in the L preset execution plans, determining a processing operation for the preset execution plan according to the target data level, the target execution information and the L preset execution plans, including: In a case where the target data level is different from the L data levels corresponding to the L preset execution plans, determining whether to perform a plan adding operation based on the L preset execution plans according to the target execution information, wherein the plan adding operation is used to add a preset execution plan including the target data level and the target execution information; When the target data level is the same as the jth data level among L data levels, determine whether to update the jth preset execution plan corresponding to the jth data level according to the target execution information, where j is an integer greater than or equal to 1.

6. The method for processing an execution plan of a database statement according to claim 5, characterized in that: In a case where the target data magnitude is different from the L data magnitudes, determining whether to perform a plan adding operation based on the L preset execution plans according to the target execution information includes: Calculating a ratio of the target data level to the kth data level of the target database statement to obtain a first value, wherein k is an integer greater than or equal to 1, and a difference between the kth data level and the target data level is less than a second preset threshold; Calculate the ratio of the single execution efficiency in the target execution information to the single execution efficiency in the preset execution plan corresponding to the k-th data level to obtain a second value; When the first value is greater than or equal to the second value, adding the target data level and the target execution information on the basis of the L data levels; When the first value is smaller than the second value, a third prompt message is generated and the newly added operation is not performed, wherein the third prompt message is used to prompt that there is an abnormality in the execution efficiency of the target execution information.

7. The method for processing an execution plan of a database statement according to claim 5, characterized in that: When the target data level is the same as the jth data level among the L data levels, determining whether to update the jth preset execution plan corresponding to the jth data level according to the target execution information includes: When the single execution efficiency in the target execution information is less than the single execution efficiency in the j-th preset execution plan, updating the j-th preset execution plan to the target execution information; The product of the single execution efficiency in the j-th preset execution plan and the corresponding preset index value is used as the third value; When the single execution efficiency in the target execution information is greater than the third value, fourth prompt information is generated and the update operation is not performed, wherein the fourth prompt information is used to prompt that the execution efficiency of the target execution information is lower than the third preset threshold.

8. The method for processing an execution plan of a database statement according to claim 7, characterized in that: When the single execution efficiency in the target execution information is greater than the third value, after generating the fourth prompt information, the execution plan processing method of the database statement further includes: Acquire second statistical information of a data table associated with the target database statement, wherein the second statistical information is used to characterize data features of the data table associated with the target database statement; updating the target execution information to third execution information according to the second statistical information; When the execution steps in the third execution information are different from the preset execution steps in the j-th preset execution plan, the preset execution steps in the j-th preset execution plan are used as the execution steps of the target database statement.

9. A database statement execution plan processing device, characterized in that: include: An acquisition unit is configured to acquire preset execution plans respectively set for a target database statement at L data levels, wherein L is an integer greater than or equal to 1, and each preset execution plan includes an identifier of the target database statement, preset execution steps, a single execution logical read, a single execution time, and a single execution efficiency, wherein the single execution logical read is used to characterize a speed at which the database uses a memory cache to access data, and the single execution efficiency is used to characterize a CPU utilization rate of the database when processing database statements; A collection unit, which collects the target data level and target execution information actually generated by the target database statement when the target database statement is executed; A detection unit, detecting whether the execution step in the target execution information matches any one of the L preset execution steps in the preset execution plans; A determination unit, when detecting that the execution steps in the target execution information do not match the preset execution steps in the L preset execution plans, determines the processing operation of the preset execution plan according to the target data level, the target execution information and the L preset execution plans.

10. A computer-readable storage medium, characterized in that: The computer-readable storage medium includes a stored executable program, wherein when the executable program is run, the device where the computer-readable storage medium is located is controlled to execute the execution plan processing method for a database statement according to any one of claims 1 to 8.

11. An electronic device, characterized in that: The method comprises one or more processors and a memory, wherein the memory is used to store one or more programs, wherein when the one or more programs are executed by the one or more processors, the one or more processors execute the execution plan processing method of the database statement described in any one of claims 1 to 8.

12. A computer program product comprising computer instructions, characterized in that: When the computer instructions are executed by a processor, the steps of the method for processing an execution plan of a database statement described in any one of claims 1 to 8 are implemented.

Images