Method and device for realizing plan binding in openGauss database and electronic equipment

Abstract

The invention relates to a method and a device for realizing plan binding in an openGauss database. The method comprises the following steps: generating a query ID and a plan ID, and respectively identifying different SQL statements and execution plans; capturing and caching execution plan information of the SQL statement to a global cache (planar Cache); the captured plan information is checked through a view vsqlplan; using a function fbindplan to bind the SQL statement and the selected execution plan, and storing the SQL statement and the selected execution plan to a system table psqlbind; when the SQL statement is executed, the bound execution plan is preferentially used, and if the bound plan is invalid or does not exist, a plan is generated according to normal logic. According to the method, mechanisms such as multithreading, global memory management and a Hash algorithm of the openGauss database are fully utilized, and plan caching and ID generation are rapidly realized. Through plan binding, the overhead of repeatedly generating the execution plan is avoided, and the execution efficiency of the SQL statement is remarkably improved. A user can flexibly select an optimal execution plan for binding according to factors such as resource control and execution efficiency.

Description

Technical Field

[0001] This application belongs to the technical field of database operations, and particularly relates to a method, device, and electronic device for implementing plan binding in the openGauss database. Background Art

[0002] In a database management system, the execution efficiency and stability of SQL statements are key factors affecting system performance. To optimize the execution behavior of SQL statements, databases usually provide various mechanisms, and two common techniques are SQLPatch and plan binding.

[0003] 1. SQL Patch SQL Patch is a technique for optimizing or fixing the execution behavior of specific SQL statements. It allows, without modifying the original SQL statement, to guide the database to use a specific execution plan by adding hints to the SQL statement. The OpenGauss database supports the SQL Patch function. Through this mechanism, users can optimize SQL statements to run according to the expected execution plan, thereby improving query performance.

[0004] 2. Plan Binding Plan binding is an advanced function in a database management system. It allows database administrators (DBAs) or developers to forcibly associate specific SQL statements with predefined execution plans. In this way, it is ensured that SQL statements run along the optimized path every time they are executed, thus avoiding performance fluctuations caused by the uncertainty in the SQL semantic parsing and optimization process. Plan binding can not only improve the stability and consistency of queries, but also reduce the cost of SQL semantic parsing and optimization, and enhance the overall efficiency of the system. However, there is currently a lack of a convenient and efficient method for implementing plan binding for the openGauss database. Summary of the Invention

[0005] To address the above problems, this application proposes a new method for implementing plan binding in the openGauss database. The core of this method is to generate a unique query ID based on the query tree of the SQL statement to identify the SQL statement. Based on this query ID, the present invention further supports the following functions: Plan capture: It can capture the optimal execution plan of the SQL statement and associate it with the query ID.

[0006] Plan binding: Bind the captured execution plan to the corresponding SQL statement to ensure that the SQL statement always uses the bound execution plan during subsequent executions.

[0007] Using the binding plan function: When the SQL statement is executed, the bound execution plan can be quickly retrieved and applied through the query ID, thereby improving the query performance and stability.

[0008] Through the above design, the present invention can optimize and solidify the execution plan of the SQL statement without modifying the original SQL statement, reduce the cost of SQL semantic parsing and optimization, and ensure the execution efficiency and stability of the SQL statement at the same time.

[0009] The overall technical solution of the present invention is as follows: The present invention aims to implement the plan binding function in the openGauss database. To achieve this goal, the present invention proposes a systematic method, generating a unique query ID for each SQL statement to identify the statement, and designing a series of modules to support the functions of plan capture, viewing, binding, and using the binding plan.

[0010] 1. SQL statement identification and query ID generation In the openGauss database, each SQL statement is parsed into a query tree before execution, and the query tree is the internal representation of the SQL statement. The present invention generates a unique query ID to identify the SQL statement by analyzing the structure and content of the query tree. The generation algorithm of the query ID ensures that the same SQL statement always generates the same ID in different execution environments, while different SQL statements generate different IDs. The generation process of the query ID is transparent and users do not need to intervene manually.

[0011] 2. Plan capture and caching When the SQL statement is executed in the openGauss database, the optimizer of the database generates one or more execution plans. The present invention provides a mechanism for capturing the current execution plan of the SQL statement and caching it in a dedicated storage structure. This storage structure can quickly retrieve and manage the execution plan according to the query ID. The captured execution plan includes but is not limited to detailed information such as the execution path, operator cost, and resource consumption for subsequent analysis and binding operations.

[0012] 3. Viewing the captured plan To facilitate the analysis and selection of captured execution plans by database administrators (DBAs) or developers, the present invention provides a viewing mechanism. Through a dedicated tool or interface, users can query and view all captured execution plans associated with a specific query ID. The viewing function supports detailed display of information such as performance metrics, resource consumption, and execution paths of each execution plan, helping users evaluate the advantages and disadvantages of different execution plans and thus select the most suitable execution plan for binding.

[0013] 4. Plan Binding After the user selects the optimal execution plan through the viewing mechanism, the present invention provides a mechanism for establishing a binding relationship between the SQL statement and the selected execution plan. The binding process is achieved by associating the query ID with the selected execution plan. Once the binding is completed, the SQL statement will preferentially use the bound execution plan during subsequent executions. The binding relationship can be persistently stored to ensure its effectiveness even after the database is restarted or a system failure occurs.

[0014] 5. Using the Bound Plan During the execution of an SQL statement, the present invention provides a mechanism for detecting whether there is a bound execution plan. If a bound plan associated with the query ID of the current SQL statement is detected, the bound plan is preferentially selected for execution. If no bound plan is detected, an execution plan is generated according to the normal optimization process of the openGauss database. This mechanism ensures that the SQL statement can be executed quickly and stably when there is a bound plan, and can still run normally when there is no bound plan.

[0015] 6. System Compatibility and Performance Optimization The present invention fully considers compatibility with the existing functions of the openGauss database during design. The plan binding function will not have a negative impact on the normal operation of the database, and performance is optimized during implementation. For example, through an efficient caching mechanism and a fast query ID retrieval algorithm, it is ensured that the plan binding function can quickly respond to users' binding and execution requirements without affecting system performance.

[0016] Through the above technical solutions, the present invention can implement an efficient plan binding function in the openGauss database, improve the execution efficiency and stability of SQL statements, and reduce the cost of SQL semantic parsing and optimization.

[0017] Specifically, to achieve the above objectives, the present application provides the following technical solutions: The first aspect of the present application provides a method for implementing plan binding in an openGauss database, the method comprising: Generate a query ID and a plan ID, where the query ID is used to identify different SQL statements, and the plan ID is used to identify different execution plans; Capture and cache the execution plan information of SQL statements; View the captured execution plan information; Bind the SQL statement to the selected execution plan; When executing the SQL statement, preferentially use the bound execution plan.

[0018] Furthermore, in the method of the present application, the generating of the query ID and the plan ID includes: At the end of the semantic processing stage, obtain the query tree of the SQL statement, and apply a hash algorithm to process the key data of the query tree to generate the query ID; At the end of the plan generation stage, obtain the plan tree of the SQL statement, and apply a hash algorithm to process the key data of the plan tree to generate the plan ID.

[0019] Furthermore, in the method of the present application, the capturing and caching of the execution plan information of SQL statements includes: Define a global cache planCache for caching the execution plan information of SQL statements; Set the threshold of planCache, and when the number of cached records exceeds the threshold, delete the earlier records; Define a parameter for controlling the enabling and disabling of the capture plan function; When the capture plan function is enabled, after executing the SQL statement, obtain the query tree, plan tree and execution time-consuming information of the SQL statement, and generate the query ID and plan ID according to the query tree and plan tree respectively; Save the query ID, plan ID, plan tree data and execution time information of the SQL statement into the global cache planCache.

[0020] Furthermore, in the method of the present application, the viewing of the captured execution plan information includes: Define a view v_sqlplan for viewing the captured SQL statements and their execution plan information; When the user queries the view v_sqlplan, output the execution plan information from the global cache planCache.

[0021] Furthermore, in the method of the present application, the binding of the SQL statement to the selected execution plan includes: Define a system table p_sqlbind for saving the relationship of the bound plan; Define a function f_bindplan(query ID, plan ID) to implement the plan binding function; By querying the view v_sqlplan, select the query ID and plan ID information for which plan binding is to be performed, and call the function f_bindplan to execute the plan binding.

[0022] Furthermore, in the method of the present application, when executing an SQL statement, preferentially using the bound execution plan includes: When executing an SQL statement, generate the query ID of the SQL statement, and query the system table p_sqlbind to check whether there is a bound plan that matches the query ID; If there is a matching bound plan, obtain the data of the execution plan tree from p_sqlbind and perform a validity check on it; If the bound plan is valid, directly use the bound execution plan to complete the execution of the SQL statement, skipping the plan generation stage; If the bound plan is invalid or no matching bound plan is found, generate an execution plan according to the normal logic.

[0023] The second aspect of the present application provides a device for implementing plan binding in the openGauss database, and the device includes: An ID generation module for generating a query ID and a plan ID, where the query ID is used to identify different SQL statements, and the plan ID is used to identify different execution plans; A capture cache module for capturing and caching the execution plan information of SQL statements; An information viewing module for viewing the captured execution plan information; A plan binding module for binding an SQL statement to a selected execution plan; A plan execution module for preferentially using the bound execution plan when executing an SQL statement.

[0024] When the device runs, it implements the steps of the foregoing method for implementing plan binding in the openGauss database.

[0025] Furthermore, in the device of the present application, it includes: (1) The ID generation module includes: A query tree processing unit for obtaining the query tree of an SQL statement at the end of the semantic processing stage and applying a hash algorithm to process the key data of the query tree to generate a query ID; A plan tree processing unit, which is used to obtain the plan tree of an SQL statement at the end of the plan generation phase, and apply a hash algorithm to process the key data of the plan tree to generate a plan ID; (2)The capture cache module includes: A global cache planCache, which is used to cache the execution plan information of SQL statements; A threshold control unit, which is used to set the threshold of planCache. When the number of cached records exceeds the threshold, earlier records are deleted; A function control unit, which is used to define a parameter to control the enabling and disabling of the capture plan function; An information capture unit, which is used to obtain the query tree, plan tree, and execution time-consuming information of the SQL statement after executing the SQL statement when the capture plan function is enabled, and generate a query ID and a plan ID respectively according to the query tree and the plan tree; An information saving unit, which is used to save the query ID, plan ID, plan tree data, and execution time information of the SQL statement into the global cache planCache; (3)The information viewing module includes: A view v_sqlplan, which is used to view the captured SQL statement and its execution plan information; An output unit, which is used to output the execution plan information from the global cache planCache when the user queries the view v_sqlplan; (4)The plan binding module includes: A system table p_sqlbind, which is used to save the relationship of the bound plan; A binding function f_bindplan, which is used to implement the plan binding function; A binding operation unit, which is used to select the query ID and plan ID information to be plan-bound by querying the view v_sqlplan, and call the function f_bindplan to execute the plan binding; (5)The plan execution module includes: A query unit, which is used to generate a query ID of the SQL statement when executing the SQL statement, and query the system table p_sqlbind to check whether there is a bound plan that matches the query ID; A verification unit, which is used to verify the validity of the bound plan; An execution unit, which is used to directly use the bound execution plan to complete the execution of the SQL statement and skip the plan generation phase when the bound plan is valid; when the bound plan is invalid or no matching bound plan is found, an execution plan is generated according to the normal logic.

[0026] A third aspect of the present application provides an electronic device, including: a memory and a processor; The memory: is used to store computer programs; The processor: is used to execute the computer program to implement the steps of the method for implementing plan binding in the openGauss database as described above.

[0027] A fourth aspect of the present application provides a computer-readable storage medium, on which a computer program is stored. When the computer program is executed by a processor, the steps of the method for implementing plan binding in the openGauss database as described above are implemented.

[0028] In summary, the method of the present invention has the following technical advantages: (1) Fast development and efficient implementation: The present invention makes full use of the existing mechanisms of the openGauss database. By using the multi-thread mechanism, it can parallelly process the execution plan capture and binding operations of SQL statements, significantly improving the system's concurrent processing ability and response speed; with the help of the global memory management function, an efficient and secure plan cache mechanism is implemented; by using the hash algorithm, rapid hash calculations are performed on the key data of the query tree and plan tree to generate unique query IDs and plan IDs. This integrated approach not only ensures the efficiency and uniqueness of ID generation but also reduces the development workload and potential error risks.

[0029] (2) Improved execution efficiency: Through the plan binding function, the present invention avoids the steps of repeatedly generating execution plans during the execution of SQL statements, not only reducing the CPU and memory overhead of plan generation but also shortening the execution time of SQL statements, especially for complex queries and frequently executed SQL statements, with significant effects.

[0030] (3) Optimal utilization of resources: Users can select the optimal execution plan through the plan binding function according to factors such as resource control and execution efficiency. This flexibility enables the system to better adapt to different application scenarios and load requirements, optimize the resource utilization efficiency, and improve the overall system performance.

[0031] (4) Flexibility and scalability: Users can choose whether to enable the plan binding function according to actual needs, as well as how to select and bind the execution plan, meeting the needs of different users and also facilitating the extension and optimization of the system.

[0032] (5) Stability and reliability: By binding the plan, it is ensured that the SQL statement uses the verified optimal execution plan every time it is executed, thereby reducing the performance problems and stability risks caused by fluctuations in the execution plan.

[0033] Other features and advantages of the present invention will be described in detail in the following specification, or can be understood by implementing the relevant technical solutions of this application. The objectives and other advantages of this application can be achieved through the technical features and means clearly pointed out in the specification, claims, and drawings, and obtained through the implementation process of these technical contents. Brief Description of the Drawings

[0034] In order to more clearly elaborate on the technical solutions of the embodiments of this application, the drawings involved in the description of the embodiments will be briefly introduced below. It should be noted that the drawings only show some embodiments of this application. For those skilled in the art, without creative work, other relevant drawings can be deduced based on these drawings.

[0035] Figure 1 This is the overall implementation flowchart of the method for implementing plan binding in the openGauss database of this application.

[0036] Figure 2 This is the composition structure diagram of the device of this application.

[0037] Figure 3 This is the schematic structural diagram of the electronic device provided by the embodiment of this application. Detailed Implementation Manner

[0038] To make the objectives, technical solutions, and advantages of the embodiments of this application clearer and more understandable, the technical solutions in the embodiments of this application will be clearly and completely described below in conjunction with the drawings in the embodiments of this application. It should be clear that the described embodiments are only some embodiments of this application, rather than all embodiments. Based on the embodiments in this application, all other embodiments obtained by those of ordinary skill in the art without creative work fall within the protection scope of this application.

[0039] In this article, the term "including" and any form of its deformation (such as "including", "having included", etc.) are open-ended expressions and should be understood as "including but not limited to", that is, the listed contents are not an exhaustive list and may also include other unmentioned contents. The term "based on" should be understood as "at least partially based on", that is, the referred basis or condition may not be the only factor and may also involve other relevant factors. The term "an embodiment" should be understood as "at least one embodiment", that is, the described embodiment is not the only possible implementation method, and there may also be other similar embodiments.

[0040] In this application, when the terms "a" and "multiple" are used to modify relevant elements or features, their expressions are illustrative rather than restrictive. Unless otherwise clearly stated in the context, "a" should be understood as "at least one", and "multiple" should be understood as "at least two". Those skilled in the art should reasonably interpret these terms according to the semantic and logical relationships in the context to ensure that they cover the possibility of "one or more".

[0041] Figure 1 The following shows the overall implementation process of the method for implementing plan binding in the openGauss database provided by this application, including the following steps: S1. Generate a query ID and a plan ID, where the query ID is used to identify different SQL statements, and the plan ID is used to identify different execution plans; S2. Capture and cache the execution plan information of the SQL statement; S3. View the captured execution plan information; S4. Bind the SQL statement to the selected execution plan; S5. When executing the SQL statement, preferentially use the bound execution plan.

[0042] To more clearly elaborate on the technical solution of this application, the following will be further illustrated through examples in specific scenarios.

[0043] The present invention realizes the plan binding function by expanding and modifying the semantic processing module and the plan generation module of the openGauss database. The specific implementation steps are as follows: 1. Generate a query ID and a plan ID Query ID generation: Calculate the key data of the query tree of the SQL statement through a hash algorithm to generate a unique query ID. The query ID is used to identify different SQL statements, and the bound plan corresponding to the SQL statement can be obtained through the query ID later. At the end of the semantic processing stage, obtain the generated query tree and apply the hash algorithm provided by openGauss to process the data of the query tree to generate the query ID.

[0044] Plan ID Generation: Calculate the key data of the plan tree of the SQL statement through a hash algorithm to generate a unique plan ID. The plan ID is used to identify different execution plans. By binding different plan IDs to an SQL statement, different execution plans can be bound. At the end of the plan generation phase, obtain the generated plan tree and apply the hash algorithm provided by openGauss to process the data of the plan tree to generate the plan ID.

[0045] 2. Capture the Execution Plan Global Cache planCache: Define a global cache planCache to cache the execution plan information of SQL statements. Set a threshold for planCache to cache at most N plan records of SQL statements. When the number of cached records exceeds the threshold, delete the earlier records.

[0046] Capture Plan Function Control: Define a parameter to control the enabling and disabling of the capture plan function. When the capture plan function is enabled, after executing an SQL statement, the system will obtain the query tree, plan tree, and execution time-consuming information of the SQL statement, and generate a query ID and a plan ID based on the query tree and plan tree respectively; subsequently, save the query ID, plan ID, plan tree data, and execution time information of the SQL statement into the global cache planCache.

[0047] 3. View the Captured Plan View v_sqlplan: Define a view v_sqlplan to view the captured SQL statements and their plan information. When a user queries this view, the system will output the plan information from the global cache planCache, and the user can view the currently captured SQL statements and their corresponding execution plans through this view.

[0048] 4. Bind the Plan System Table p_sqlbind: Define a system table p_sqlbind to save the relationship of the bound plan. This system table mainly includes the following fields: query ID, plan ID, and plan tree data.

[0049] Bind Plan Function f_bindplan: Define a function f_bindplan(query ID, plan ID) to implement the plan binding function. This function obtains the corresponding plan tree data from the global cache planCache based on the input query ID and plan ID, and writes the query ID, plan ID, and plan tree data into the system table p_sqlbind for subsequent obtaining of the bound plan.

[0050] Binding operation: The user selects the query ID and plan ID information to be plan-bound by querying the view v_sqlplan, and then calls the function f_bindplan to execute the plan binding.

[0051] 5. Using the bound plan Using the bound plan parameter: Define a parameter to control whether to use the bound plan. When this parameter is enabled, when executing an SQL statement, the system generates the query ID of the SQL statement during the semantic processing phase, and then queries the system table p_sqlbind to check whether there is a bound plan for this SQL statement.

[0052] Execution logic of the bound plan: If there is a bound plan in the system table p_sqlbind that matches the query ID, directly obtain the bound plan tree information from the system table and execute the SQL statement based on this plan tree, skipping the plan generation phase. If there is no bound plan or the bound plan has expired, generate an execution plan according to the normal logic.

[0053] Invalidation detection mechanism for the bound plan: After obtaining the bound plan, the system will detect the key data in the bound plan and check whether the database objects such as tables and functions on which the plan depends are still valid. If there are invalid dependent objects, it is determined that the bound plan has expired, and an execution plan is regenerated according to the normal logic.

[0054] Through the above implementation manners, the present invention can implement the plan binding function in the openGauss database, improve the execution efficiency and stability of SQL statements, and reduce the cost of SQL semantic parsing and optimization at the same time.

[0055] Furthermore, the specific implementation examples of the method of the present invention are as follows: Step 1. Enable the plan capture function The user needs to first enable the plan capture function by setting relevant parameters. This parameter controls whether to perform plan capture and caching operations on the executed SQL statements.

[0056] Step 2. Capture and cache the execution plan information After the user executes a query SQL statement, the system will automatically capture the execution plan information of this SQL statement and cache it in the global cache planCache. The captured execution plan information includes but is not limited to the following: query ID: A unique identifier generated for the query tree through a hash algorithm.

[0057] plan ID: A unique identifier generated for the plan tree through a hash algorithm.

[0058] Data of the execution plan tree: detailed information describing the execution path of the SQL statement.

[0059] Start time: the timestamp when the SQL statement starts to execute.

[0060] Execution time: the elapsed time for the execution of the SQL statement.

[0061] Step 3. View the captured execution plan information Users can view the execution information of the SQL statements captured and cached in the global cache planCache by querying the view v_sqlplan. The view v_sqlplan will output the following content: query ID, plan ID, data of the execution plan tree, start time, and execution time. Users can select the SQL statements to be bound and their corresponding execution plans based on this information.

[0062] Step 4. Bind the plan Users use the query ID and plan ID information obtained in Step 3 to bind the plan by calling the function f_bindsql(queryID, plan ID). The specific operations of this function are as follows: Obtain the execution plan tree data corresponding to the query ID and plan ID from the global cache planCache.

[0063] Save the query ID, plan ID, and the data of the execution plan tree to the system table p_sqlbind to establish the binding relationship between the SQL statement and the execution plan.

[0064] Example call: SELECT f_bindsql('query_id_value', 'plan_id_value').

[0065] Step 5. Execute the SQL statement using the bound plan When the user executes the same query SQL statement again, the system will process it according to the following logic: Generate the query ID: In the semantic processing stage, the system will generate the query ID based on the query tree of the SQL statement.

[0066] Find the bound plan: The system will query the system table p_sqlbind to check if there is a bound plan that matches the generated query ID.

[0067] Execution plan selection: If a matching binding plan is found, the system retrieves the data of the execution plan tree from p_sqlbind and performs a validity check on it.

[0068] If the binding plan is valid, the system directly uses the bound execution plan to complete the execution of the SQL statement, skipping the normal plan generation phase.

[0069] If the binding plan is invalid (e.g., the dependent tables or functions have changed), the system regenerates the execution plan through the planner according to the normal process.

[0070] If no matching binding plan is found, the system also generates the execution plan through the planner according to the normal process.

[0071] Through the above specific implementation steps, the present invention can implement an efficient plan binding function in the openGauss database, improve the execution efficiency and stability of SQL statements, and at the same time provide users with flexible optimization means.

[0072] Figure 2 Shown is a device for implementing plan binding in the openGauss database proposed by this application, and the device includes: An ID generation module, used to generate a query ID and a plan ID, where the query ID is used to identify different SQL statements, and the plan ID is used to identify different execution plans; A capture cache module, used to capture and cache the execution plan information of SQL statements; An information viewing module, used to view the captured execution plan information; A plan binding module, used to bind the SQL statement to the selected execution plan; A plan execution module, used to preferentially use the bound execution plan when executing the SQL statement.

[0073] When the above device runs, it implements the steps of the method for implementing plan binding in the openGauss database disclosed in this application.

[0074] The flowcharts and block diagrams in the accompanying drawings show possible implementation manners of devices, methods, and computer program products according to various embodiments of this application, including system architectures, functions, and operations. In these figures, each box may represent a module, a program segment, or a part of code, which contains one or more executable instructions for implementing the specified logical function. It should be noted that each box in the block diagram and / or flowchart, as well as combinations of these boxes, can be implemented by a dedicated hardware-based system to implement the specified function or operation, or can be implemented by a combination of dedicated hardware and computer instructions.

[0075] AsFigure 3 As shown in the figure, an embodiment of the present application also discloses an electronic device, including: a processor 310, a communication interface 320, a memory 330 for storing computer programs executable by the processor, and a communication bus 340. Among them, the processor 310, the communication interface 320, and the memory 330 complete communication with each other through the communication bus 340. The processor 310 runs the executable computer program to implement the steps of the method for implementing plan binding in the openGauss database described above.

[0076] It can be understood that in addition to including a memory and a processor, the electronic device may further include an input device (such as a keyboard), an output device (such as a display), and other communication modules. These input devices, output devices, and other communication modules communicate with the processor through an I / O interface (i.e., an input / output interface).

[0077] The operations of the present application can be implemented by writing computer program code using one or more programming languages or combinations thereof. The programming languages include but are not limited to the following types: Object-oriented programming languages, such as Java, Smalltalk, C++, etc.; Conventional procedural programming languages, such as the "C" language or similar programming languages.

[0078] The execution modes of the program code include but are not limited to: Fully executed on the user's computer; Partially executed on the user's computer and partially executed on a remote computer; Executed as an independent software package; Fully executed on a remote computer or server.

[0079] In scenarios involving remote computers, the remote computer can be connected to the user's computer through any type of network, and the network includes but is not limited to a local area network (LAN) or a wide area network (WAN). In addition, the remote computer can also be connected to an external computer through an Internet service provider, for example, by using the Internet for connection.

[0080] Furthermore, the present application also discloses a computer-readable storage medium. When the instructions in the computer-readable storage medium are executed by the processor of the electronic device, the electronic device can execute each step of the method for implementing plan binding in the openGauss database disclosed in the present application.

[0081] In the context of the present application, a computer-readable storage medium refers to a tangible medium that can store computer program code and related data. Specific examples include but are not limited to the following: (1) Portable computer disks: Removable magnetic storage media such as floppy disks.

[0082] (2) Hard disks: Fixed storage devices including mechanical hard disks and solid-state drives.

[0083] (3) Random Access Memory (RAM): Volatile storage media for temporarily storing data and program code.

[0084] (4) Read-Only Memory (ROM): Non-volatile storage media for storing fixed programs and data.

[0085] (5) Erasable Programmable Read-Only Memory (EPROM) or Flash Memory: Non-volatile storage media that support multiple erasures and programming.

[0086] (6) Fiber optic storage devices: Storage media based on fiber optic technology.

[0087] (7) Portable Compact Disc Read-Only Memory (CD-ROM): Read-only media for storing data in the form of optical discs.

[0088] (8) Optical storage devices: Storage media based on optical principles such as DVDs and Blu-ray discs.

[0089] (9) Magnetic storage devices: Storage media based on magnetic principles such as magnetic tapes and disks.

[0090] (10) Any suitable combination of the above: For example, combining multiple storage media to meet different storage requirements.

[0091] These computer-readable storage media can be used to store the program code and related data described in this application to support the operation of the program and the persistent storage of data.

[0092] In particular, according to the embodiments of this application, the processes described in the flowcharts can be implemented as computer software programs. For example, the embodiments of this application relate to a computer program product that includes a computer program carried on a non-transitory computer-readable medium. The computer program contains program code for executing the method for implementing plan binding in the openGauss database disclosed in this application. When the computer program is executed by a processing device, it can implement the above functions defined in the embodiments of this application.

[0093] Although the above description contains several specific implementation details, these details should not be construed as limiting the scope of the present application. The above description is only a preferred embodiment of the present application and an explanation of the applied technical principles. Those skilled in the art should understand that the scope of disclosure involved in the present application is not limited to the technical solutions formed by the specific combination of the above technical features. At the same time, the present application should also cover other technical solutions formed by any combination of the above technical features or their equivalent features without departing from the above disclosed concept.

[0094] Those skilled in the art should also understand that they can modify the technical solutions recorded in the foregoing embodiments, or perform equivalent replacements on some of the technical features, without departing from the spirit and scope of the technical solutions of the embodiments of the present application. These modifications or replacements will not cause the essence of the corresponding technical solutions to deviate from the core spirit and scope of the technical solutions of the embodiments of the present application.

Claims

1. A method for implementing plan binding in the openGauss database, characterized in that, The method includes: Generating a query ID and a plan ID, where the query ID is used to identify different SQL statements, and the plan ID is used to identify different execution plans; Capturing and caching the execution plan information of the SQL statement; Viewing the captured execution plan information; Binding the SQL statement to the selected execution plan; When executing the SQL statement, preferentially use the bound execution plan.

2. The method according to claim 1, characterized in that The generating of the query ID and the plan ID includes: At the end of the semantic processing stage, obtaining the query tree of the SQL statement and applying a hash algorithm to process the key data of the query tree to generate the query ID; At the end of the plan generation stage, obtaining the plan tree of the SQL statement and applying a hash algorithm to process the key data of the plan tree to generate the plan ID.

3. The method according to claim 1, characterized in that The capturing and caching the execution plan information of the SQL statement includes: Defining a global cache planCache for caching the execution plan information of the SQL statement; Setting the threshold of planCache, and deleting the earlier records when the number of cached records exceeds the threshold; Defining a parameter for controlling the enabling and disabling of the plan capture function; When the plan capture function is enabled, after executing the SQL statement, obtaining the query tree, plan tree and execution time-consuming information of the SQL statement, and generating the query ID and plan ID respectively according to the query tree and plan tree; Saving the query ID, plan ID, plan tree data and execution time information of the SQL statement to the global cache planCache.

4. The method according to claim 1, wherein The viewing the captured execution plan information includes: Defining a view v_sqlplan for viewing the captured SQL statement and its execution plan information; When the user queries the view v_sqlplan, outputting the execution plan information from the global cache planCache.

5. The method according to claim 1, characterized in that, The binding the SQL statement to the selected execution plan includes: Defining a system table p_sqlbind for saving the relationship of the bound plan; Defining a function f_bindplan(query ID, plan ID) for implementing the plan binding function; By querying the view v_sqlplan, selecting the query ID and plan ID information to be plan-bound, and calling the function f_bindplan to perform the plan binding.

6. The method according to claim 1, wherein The preferentially using the bound execution plan when executing the SQL statement includes: When executing the SQL statement, generating the query ID of the SQL statement and querying the system table p_sqlbind to check whether there is a bound plan matching the query ID; If there is a matching bound plan, obtaining the data of the execution plan tree from p_sqlbind and performing a validity check on it; If the bound plan is valid, directly using the bound execution plan to complete the execution of the SQL statement and skipping the plan generation stage; If the bound plan is invalid or no matching bound plan is found, then generate the execution plan according to the normal logic.

7. An apparatus for implementing plan binding in the openGauss database, characterized in that, The device includes: An ID generation module, which is used to generate a query ID and a plan ID, where the query ID is used to identify different SQL statements, and the plan ID is used to identify different execution plans; A capture cache module, which is used to capture and cache the execution plan information of SQL statements; An information viewing module, which is used to view the captured execution plan information; A plan binding module, which is used to bind an SQL statement to a selected execution plan; A plan execution module, which is used to preferentially use the bound execution plan when executing an SQL statement.

8. The device according to claim 7, wherein (1) The ID generation module includes: A query tree processing unit, which is used to obtain the query tree of an SQL statement at the end of the semantic processing stage, and apply a hash algorithm to process the key data of the query tree to generate a query ID; A plan tree processing unit, which is used to obtain the plan tree of an SQL statement at the end of the plan generation stage, and apply a hash algorithm to process the key data of the plan tree to generate a plan ID; (2) The capture cache module includes: A global cache planCache, which is used to cache the execution plan information of SQL statements; A threshold control unit, which is used to set the threshold of planCache, and delete the earlier records when the number of cached records exceeds the threshold; A function control unit, which is used to define a parameter to control the opening and closing of the capture plan function; An information capture unit, which is used to obtain the query tree, plan tree and execution time-consuming information of an SQL statement after executing the SQL statement when the capture plan function is turned on, and generate a query ID and a plan ID according to the query tree and the plan tree respectively; An information saving unit, which is used to save the query ID, plan ID, plan tree data and execution time information of an SQL statement into the global cache planCache; (3) The information viewing module includes: A view v_sqlplan, which is used to view the captured SQL statement and its execution plan information; An output unit, which is used to output the execution plan information from the global cache planCache when the user queries the view v_sqlplan; (4) The plan binding module includes: A system table p_sqlbind, which is used to save the relationship of the bound plan; A binding function f_bindplan, which is used to implement the plan binding function; A binding operation unit, which is used to select the query ID and planID information to be plan-bound by querying the view v_sqlplan, and call the function f_bindplan to perform the plan binding; (5) The plan execution module includes: A query unit, which is used to generate the query ID of an SQL statement when executing the SQL statement, and query the system table p_sqlbind to check whether there is a bound plan matching the query ID; A verification unit, which is used to verify the validity of the bound plan; An execution unit, which is used to directly execute an SQL statement by using the bound execution plan when the bound plan is valid, skipping the plan generation phase; when the bound plan is invalid or no matching bound plan is found, an execution plan is generated according to the normal logic.

9. A computer-readable storage medium having a computer program stored thereon, characterized in that, When the computer program is executed by a processor, the steps of the method for implementing plan binding in the openGauss database as described in any one of claims 1-6 are implemented.

10. An electronic device, characterized in that, including: a memory and a processor; Memory: used to store a computer program; Processor: used to execute the computer program to implement the steps of the method for implementing plan binding in the openGauss database as described in any one of claims 1-6.

Images