Statistical method for procedural query, storage medium, product and equipment
By using sample statistics and vector graphics generation, the problem of incomplete display of procedural query call relationships was solved, and a clear display of hierarchical call relationships was achieved, improving the efficiency and accuracy of database management.
Patent Information
- Application Number
- CN202510949221.1
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2025-07-09
- Publication Date
- 2025-11-04
AI Technical Summary
In existing technologies, statistical reports for procedural queries cannot accurately display the call relationships and resource consumption between statements, resulting in insufficient basis for performance tuning. Furthermore, information in the reports may be missing or incomplete, affecting database management efficiency.
By extracting patterns and statistically analyzing the results of procedural query statements, a multi-layered bar vector diagram is generated to display the hierarchical call relationship, including the file header, event list, query list, and extracted pattern event records, thus realizing the systematic collection and visualization of data.
It improves the tuning capabilities of procedural query statements, ensures the reliability and accuracy of statistical data, provides complete performance tuning criteria, and enhances the efficiency and accuracy of database management.
Smart Images
Figure CN120892301A_ABST
Abstract
Description
TECHNICAL FIELD
[0001] The present application relates to database technology, in particular to a statistical method of procedural query, a storage medium, a product and an apparatus. BACKGROUND
[0002] Procedural query language generally refers to those programming extensions that allow a series of operations to be performed in a database, which can include data retrieval, modification, and control flow structures (such as conditional judgments and loops). This type of query goes beyond the functions provided by basic SQL, supporting more complex logical processing. A database can analyze procedural query statements through statement statistical reporting, which can list directly executed SQL statements and procedural query statements according to different indicators (such as execution time, CPU time, I / O operations, etc.), and statements are arranged in the same table according to resource consumption.
[0003] However, procedural queries have a clear hierarchical calling relationship, and their resource consumption also has a clear hierarchical containing relationship. In the existing product's statistical report involving procedural queries, the hierarchical containing relationship is ignored, there is no procedural calling relationship between statements, and statements are arranged in the same table according to resource consumption. For example, if statement A is executed, it calls procedural query B, and B calls actual query statement C. The resource consumption of statement A completely contains the resource consumption of statement C, so in the existing way of displaying procedural query statistics by execution time, A will be sorted before C, but in fact, the resource consumption is generated by statement C. Therefore, the existing product's statistical report ignores the calling relationship between procedural query statements, and cannot accurately provide the basis for performance tuning.
[0004] In addition, with the increase of the number of nested procedural query layers, the actual performance loss statement will be delayed in the report. In order to increase the readability of the report, some database statement statistical reports will limit the number of report lines, and statements exceeding the report line limit will not be displayed in the report, thereby causing the information of the actual performance loss statement to be directly lost in the report. In addition, some databases use tree report display schemes in their reports, which display the calling relationship of procedural query statements, but in order to improve the readability of the report, the number of levels and the total number of procedural query statements displayed in the report are often limited, and the hierarchical calling relationship is still incomplete. In some other database statement statistical reports, the current limitation is to only display the direct call of the statement, that is, only to display the directly executed statement A, and all information of the actual final executed statement C is completely lost, which does not have the ability to tune the procedural query statement in similar situations. SUMMARY
[0005] In view of the above problems, a statistical method of procedural query, a storage medium, a product and an equipment are provided to overcome the above problems or at least partially solve the above problems.
[0006] An object of the present application is to provide a statistical method of procedural query to intuitively display a procedural query statement and a query statement called by the procedural query statement.
[0007] A further object of the present application is to improve the reliability of statistical data, thereby improving the accuracy of vector graph generation.
[0008] A further object of the present application is to intuitively and comprehensively display the hierarchical calling relationship of the procedural query statement.
[0009] In particular, the present application provides a statistical method of procedural query, comprising:
[0010] obtaining a procedural query statement in a database;
[0011] sampling the calling result of the procedural query statement and recording statistical data;
[0012] generating a vector graph corresponding to the procedural query statement according to the statistical data, wherein the vector graph is used to display the procedural query statement and a query statement called by the procedural query statement in an execution process.
[0013] Optionally, the step of sampling the calling result of the procedural query statement and recording statistical data comprises:
[0014] sampling the query statement called by the procedural query statement in the execution process, the event and the sampling object multiple times at a preset sampling frequency, and taking the sampled data as the statistical data;
[0015] sequentially writing the statistical data into a statistical file of the database.
[0016] Optionally, the vector graph comprises a plurality of stacked bar graphs in the up-down direction, wherein a bottom bar graph in the plurality of stacked bar graphs is used to display the procedural query statement, and each non-bottom bar graph in the plurality of stacked bar graphs is used to display a query statement called by a query statement in a next layer of bar graph, so as to display the hierarchical calling relationship of the procedural query statement in the execution process.
[0017] Optionally, each bar graph comprises at least one horizontally extended subgraph, and each subgraph is used to display a query statement, and the length of the subgraph is positively correlated with the number of times of sampling of the query statement displayed by the subgraph.
[0018] Optionally, after the step of generating the vector graph corresponding to the procedural query statement according to the statistical data, the statistical method of procedural query further comprises:
[0019] in response to the first trigger instruction for any subgraph, display the query statement corresponding to the triggered subgraph completely; and / or
[0020] in response to the second trigger instruction for any subgraph, display the triggered local data in an enlarged manner, and show the proportion of each event in the sampling process corresponding to the query statement.
[0021] Optionally, the step of generating the vector diagram corresponding to the process query statement according to the statistical data comprises:
[0022] acquire a preset specified target, wherein the specified target comprises at least one of a specified query, a specified event and a specified object;
[0023] compare the statistical data with the specified target, and generate the vector diagram according to the partial data of the statistical data containing the specified target.
[0024] Optionally, the statistical file comprises a file header, an event list, a query list and a sampling event record, wherein the file header is used for storing the basic information of the statistical file, the basic information of the statistical file comprises a preset sampling frequency and a sampling number, the event list is used for recording all events occurring in the sampling process, the query list is used for recording all query statements occurring in the sampling process, and the sampling event record is used for sequentially recording the hierarchical calling relationship of the process query and the sampling object.
[0025] According to another aspect of the present application, there is also provided a machine readable storage medium, which stores a computer program, and the computer program is executed by a processor to implement the steps of the statistical method of the process query.
[0026] According to still another aspect of the present application, there is also provided a computer program product, which comprises a computer program, and the computer program is executed by a processor to implement the steps of the statistical method of the process query.
[0027] According to yet another aspect of the present application, there is also provided a computer device, which comprises a memory, a processor and a computer program stored in the memory and running on the processor, and the processor executes the computer program to implement the steps of the statistical method of the process query.
[0028] The statistical method for process query of the present application, by obtaining process query statements in a database, statistically sampling the calling results of the process query statements, recording statistical data, and generating a vector diagram corresponding to the process query statements according to the statistical data, wherein the vector diagram is used to display the process query statements and the query statements called by the process query statements in the execution process, the hierarchical calling relationship of the process query statements and the called query statements is intuitively displayed. Thus, the statistical method for process query of the present application, by combining the sampling statistical method with the vector diagram generation, converts the hierarchical calling relationship of the process query statements into a graphical structure in a visual manner, provides a data carrier for subsequent query optimization, effectively solves the problem of being unable to accurately provide performance tuning basis due to the lack of information of the statements actually causing performance loss, improves the tuning capability of the process query statements, and thus improves the efficiency and accuracy of database management.
[0029] Further, the statistical method for process query of the present application, by sampling the query statements called by the process query statements in the execution process, the events and the sampling objects multiple times at a preset sampling frequency, taking the sampled data as the statistical data, and sequentially writing the statistical data into a statistical file of the database, the sampling statistical method for the calling results of the process query statements is realized. Thus, the statistical method for process query of the present application, by sampling the query statements, events and objects multiple times at the preset sampling frequency, makes the collection of the statistical data more systematic, comprehensive and traceable, improves the reliability of the statistical data, ensures the accuracy and reliability of the data basis for subsequent vector diagram generation, and thus improves the accuracy of generating the vector diagram based on the statistical data.
[0030] Further, in the statistical method for process query of the present application, the vector diagram includes multiple layers of bar charts stacked in the up-down direction, the bottom layer of bar charts in the multiple layers of bar charts is used to display the process query statements, and each non-bottom layer of bar charts in the multiple layers of bar charts is used to display the query statements called by the query statements in the next layer of bar charts, so as to display the hierarchical calling relationship of the process query statements in the execution process. Thus, the statistical method for process query of the present application, by using the multiple layers of bar charts stacked in the up-down direction to display the hierarchical calling results of the process query statements layer by layer, intuitively and comprehensively displays the hierarchical calling relationship of the process query statements, and thus further improves the tuning capability of the process query statements.
[0031] The above and other objects, advantages and features of the present application will become more apparent from the following detailed description of specific embodiments thereof, taken in conjunction with the accompanying drawings. BRIEF DESCRIPTION OF DRAWINGS
[0032] Some specific embodiments of the present application will be described in detail in the following with reference to the accompanying drawings. The same reference numbers in the drawings identify the same or similar components or parts. Those skilled in the art should understand that the drawings are not necessarily drawn to scale. In the drawings:
[0033] Figure 1 is a schematic diagram of a statistical method of a procedural query according to an embodiment of the present application;
[0034] Figure 2 is a schematic diagram of a hierarchical call relationship of a procedural query statement in the statistical method of the procedural query according to an embodiment of the present application;
[0035] Figure 3 is a schematic diagram of a structure of a vector graph in the statistical method of the procedural query according to an embodiment of the present application;
[0036] Figure 4 is a control flow diagram of the statistical method of the procedural query according to an embodiment of the present application;
[0037] Figure 5 is a schematic diagram of a structure of a computer program product according to an embodiment of the present application;
[0038] Figure 6 is a schematic diagram of a structure of a computer readable storage medium according to an embodiment of the present application; and
[0039] Figure 7 is a schematic diagram of a structure of a computer device according to an embodiment of the present application. DETAILED DESCRIPTION
[0040] Exemplary embodiments of the present application will be described hereinafter with reference to the accompanying drawings. Although exemplary embodiments of the present application are shown in the drawings, it should be understood that the present application can be implemented in various forms and should not be limited by the embodiments set forth herein. Rather, these embodiments are provided so that this disclosure will be thorough and complete, and will fully convey the scope of the present application to those skilled in the art.
[0041] To solve the above problems, an embodiment of the present application proposes a statistical method of a procedural query to improve stability of an execution plan and query performance. Figure 1 is a schematic diagram of a statistical method of a procedural query according to an embodiment of the present application. As shown in Figure 1 , the statistical method of the procedural query of the present embodiment can generally include:
[0042] Step S102, obtaining the procedural query statement in the database. It should be noted that the procedural query statement refers to a programming extension statement allowing a series of operations such as data retrieval, modification, and control flow structure (such as conditional judgment and loop) in the database. In addition, the procedural query has a clear hierarchical calling relationship, for example, executing statement A, calling procedural query B, and B calling actual query statement C.
[0043] Step S104, sampling the calling result of the procedural query statement and recording the statistical data. It should be noted that the sampling statistics refers to obtaining the calling result of the procedural query statement in an intermittent and multiple times to achieve the balance between data acquisition comprehensiveness and system monitoring load. In addition, the statistical data obtained by the sampling statistics are sequentially recorded in the database management system for subsequent acquisition.
[0044] Step S106, generating a vector diagram corresponding to the procedural query statement according to the statistical data, wherein the vector diagram is used to display the procedural query statement and the query statement called in the execution process of the procedural query statement. That is, after sampling the calling result of the procedural query statement, the statistical data is converted into a visual vector diagram, instead of simply being listed in the same table according to resource consumption.
[0045] Therefore, the statistical method of the procedural query of the embodiment of the present application realizes the conversion of the hierarchical calling relationship of the procedural query statement into a graphical structure in a visual manner by combining the sampling statistics and the vector diagram generation, provides a data carrier for subsequent query optimization, effectively solves the problem that the performance tuning basis cannot be accurately provided due to the lack of information of the statement actually causing performance loss, improves the tuning ability of the procedural query statement, and thus improves the efficiency and accuracy of the database management.
[0046] In addition, the procedural query statement in the above step S102 can generally include PL / SQL (Procedural Language / SQL), PL / pgSQL, T-SQL (Transact-SQL), and MySQL Stored Procedures and Functions, etc. Specifically, PL / SQL is a procedural SQL language provided by Oracle database, which allows users to write stored procedures, functions, triggers, etc., and can be directly run on the database server. PL / SQL not only supports SQL statements, but also provides variable declaration, conditional judgment (IF...THEN...ELSE), loop (LOOP, WHILE), exception handling, etc. PL / pgSQL is a procedural query language in PostgreSQL database, similar to PL / SQL, also supports variable definition, conditional branching, loop and other program control structures, used to create stored procedures and triggers. T-SQL is an extended SQL language used by Microsoft SQL Server, which increases the support for transaction processing, and introduces functions such as variables, control flow statements (such as BEGIN...END, IF...ELSE, WHILE), error handling, etc., so that more complex data processing logic can be implemented in SQL Server. MySQL Stored Procedures and Functions also support procedural programming through stored procedures and functions, allowing users to write reusable code blocks to perform specific tasks, such as complex business logic processing or batch data operations.
[0047] In one implementation, for example, a stored procedure is created using PL / SQL, which accepts an employee ID as an input parameter and returns the employee's name and salary:
[0048] CREATE OR REPLACE PROCEDURE GetEmployeeDetails(
[0049] p_emp_id IN NUMBER
[0050] )RETURNS TEXT AS$$
[0051] BEGIN
[0052] SELECT Name INTO o_name FROM Employees
[0053] WHERE ID=p_emp_id;
[0054] retrun o_name;
[0055] END;
[0056] $$;
[0057] /
[0058] In another embodiment, as in the execution of the procedural query statement A, the query statement B is called first, and the actual query statement C is called by the query statement B:
[0059] SELECT B(); -- statement A
[0060] CREATE OR REPLACE PROCEDURE B() -- procedural query B
[0061] RETURNS TEXT AS$$
[0062] BEGIN
[0063] SELECT Name INTO o_name FROM Employees WHERE ID = p_emp_id; -- statement C
[0064] retrun o_name;
[0065] END;
[0066] $$;
[0067] /
[0068] Figure 2 is a schematic diagram of the hierarchical calling relationship of the procedural query statement in the statistical method of the procedural query according to an embodiment of the embodiment of the present application. As shown in the figure, Figure 2 In another embodiment of the present application, the procedural query statement calls multiple groups of query statements in multiple levels in the execution process.
[0069] In this embodiment, the hierarchical calling relationship of the procedural query statement can include: Call my_test($1) first calls Call my_test($1), Update t1 set id>$1, and Delete from t4 where id = $1; and Call my_test($1) calls the actual execution of Update t1 set id>$1 and Delete from t4 where id = $1 again.
[0070] It can be seen that the procedural query has a clear hierarchical calling relationship, and the resource consumption also has a clear hierarchical containing relationship. The resource consumption of the procedural query statement is generated by the finally executed query statement, that is, the resource consumption of the procedural query statement completely contains the resource consumption of the finally executed query statement.
[0071] In some embodiments, before the step S104, the statistical method of the procedural query of the present application can further include the following steps: setting a sampling task, configuring parameters such as sampling frequency, sampling object, total sampling time, and events to be recorded. In addition, the sampling frequency can refer to the number of samplings per second, the sampling object can refer to the database session connection to be sampled, and the events to be recorded can include CPU active events, waiting events, etc.
[0072] Thus, before the step of executing the sampling statistical procedural query statement calling result, the database management system has pre-constructed the sampling task, realized the ordered and accurate acquisition of the procedural query statement calling result, and improved the stability of acquiring statistical data.
[0073] In some embodiments, the step S104 can include the following steps: sampling the query statement called by the procedural query statement in the execution process, the events occurred, and the sampling object multiple times at a preset sampling frequency, and taking the sampled data as statistical data; and sequentially writing the statistical data into a statistical file of the database. That is, after starting the sampling statistics, the sampling records the procedural query calling relationship, the events occurred, etc. according to the sampling frequency setting, and the sampling data is sequentially written into the disk statistical file.
[0074] It should be noted that the preset sampling frequency is the sampling frequency pre-configured before the step S104, which realizes stable and ordered sampling acquisition at a fixed time interval. In addition, the statistical data includes the query statement called by the procedural query statement in the execution process, the events occurred, and the sampling object sampled multiple times, which realizes comprehensive acquisition of the sampling statistical procedural query statement calling result.
[0075] Thus, the statistical method of the procedural query of the embodiment of the present application samples the query statement, the events, and the object multiple times through the preset sampling frequency, so that the collection of the statistical data is more systematic, comprehensive, and traceable, the reliability of the statistical data is improved, the accuracy and reliability of the data basis of the subsequent vector diagram generation are ensured, and thus the accuracy of generating the vector diagram based on the statistical data is improved.
[0076] In some embodiments, the statistical file in step S104 can be constructed in advance and stored in a database. As shown in Table 1, the statistical file can be a storage structure in the form of a table. Specifically, the statistical file can include a file header, event descriptors, query descriptors, and sampled event data. In the statistical file, the file header is used to store basic information of the statistical file, which can include a preset sampling frequency and a sampling number, the event descriptors are used to record all events occurring in the sampling process, the query descriptors are used to record all query statements occurring in the sampling process, and the sampled event data is used to sequentially record a hierarchical calling relationship of a procedural query and a sampling object.
[0077] Table 1
[0078]
[0079] In the present embodiment, the basic information of the statistical file can further include a database version, a total sampling time, a sampling failure number, and a sampling object list. When recording all events occurring in the sampling process, the event descriptors can further synchronously record basic information of the events. The query descriptors can further be used to record a query identifier of all query statements occurring in the sampling process and basic information of the query. The sampled event data can further be used to sequentially record a result of each sampling, including a hierarchical calling relationship of a procedural query (recorded using a group of query identifier list), a sampled event, and a sampling object.
[0080] Thus, the statistical method for a procedural query according to the present embodiment can realize standardized data storage by limiting the specific structure of the statistical file, using the file header, the event descriptors, the query descriptors, and the sampled event data to respectively store different types of statistical data, which is beneficial to guarantee the integrity and easy access of the statistical data, facilitates subsequent data acquisition, processing, analysis, and data sharing between different modules, and provides a solid data management foundation for efficient operation of the entire statistical method.
[0081] Figure 3 is a structural schematic diagram of a vector diagram in the statistical method for a procedural query according to one embodiment of the present embodiment. As shown in Figure 3 , the vector diagram can include a plurality of stacked bar charts in the up-down direction.
[0082] In the embodiment, the bottom bar chart in the multi-layer bar chart is used to display the process query statement, and each non-bottom bar chart in the multi-layer bar chart is used to display the query statement called by the query statement in the next layer bar chart, so as to display the hierarchical calling relationship of the process query statement in the execution process. Thus, the top bar chart in the multi-layer bar chart is used to display the finally executed query statement. Specifically, the number of layers of the multi-layer bar chart is the same as the number of calling layers of the query statements called by the process query statement in the execution process.
[0083] That is, each layer bar chart is vertically stacked from bottom to top, reflecting the calling relationship of the process query, forming a volcano-shaped structure with a wide bottom and a narrow top. Based on the multi-layer bar chart, the query statements called by the process query statement in the execution process are arranged from bottom to top according to the hierarchical calling relationship, so as to completely display the entire calling relationship chain of the process query statement in the execution process.
[0084] Thus, the statistical method of the process query of the embodiment of the application displays the hierarchical calling results of the process query statement layer by layer by using the multi-layer bar chart stacked from top to bottom, realizes intuitive and comprehensive display of the hierarchical calling relationship of the process query statement, and further improves the optimization capability of the process query statement.
[0085] In some implementations, each layer bar chart includes at least one horizontally extended sub-chart, each sub-chart is used to display a query statement, and the length of the sub-chart is positively correlated with the number of times the query statement displayed by the sub-chart is sampled. In addition, the horizontally extended bar sub-chart is used to represent the process query statement, and the more times the bar sub-chart is sampled, the longer the bar sub-chart is.
[0086] Further, after the step S106, the statistical method of the process query of the application can further include the following steps: in response to a first triggering instruction for any sub-chart, displaying the query statement corresponding to the triggered sub-chart completely; and / or in response to a second triggering instruction for any sub-chart, displaying the local data triggered by zooming in and displaying the proportion of each event occurring in the sampling process of the query statement corresponding to the local data.
[0087] Specifically, the first triggering instruction can include a mouse hovering operation, and the second triggering instruction can include a mouse clicking operation. That is, after the mouse is hovered over the sub-chart corresponding to any query statement, the complete query statement is displayed, and after clicking, the local data item is zoomed in and the event proportion occurring in the sampling process of the query statement is displayed.
[0088] Thus, in the statistical method of the process query of the embodiment of the application, the vector diagram can have the following characteristics:
[0089] 1. Hierarchical structure: Horizontally extended bar charts are used to represent procedural queries. The vertical stacking from bottom to top reflects the calling relationship of procedural queries. The top bar chart represents the query statement that is finally executed, and the bottom bar chart represents the query statement that is directly executed.
[0090] 2. Width represents the number of samplings: the more times a bar chart is sampled, the longer it is, which can also be considered as a larger proportion of the time during the sampling period.
[0091] 3. Interactivity: Hovering the mouse over the subgraph corresponding to the query statement displays the complete query statement. Clicking it zooms in on the local data item and shows the proportion of events that occurred during the sampling of the query statement.
[0092] Therefore, the statistical method for procedural queries in this embodiment of the invention uses multi-layered bar charts stacked vertically to display the hierarchical call results of procedural query statements layer by layer, thereby achieving an intuitive and comprehensive display of the hierarchical call relationship of procedural query statements, and further improving the optimization capability of procedural query statements.
[0093] In some implementations, step S106 may include the following step: generating a vector diagram based on all statistical data to fully display the hierarchical call relationship of the procedural query statement. Thus, the statistical method for procedural queries in this embodiment of the invention, by fully displaying every call stage of a procedural query statement from beginning to end, including the top-level query statement, the query statements called at each intermediate level, and the final query statement executed at the bottom level, achieves a comprehensive presentation of the complete call chain without any omissions in call relationships. This ensures the integrity and comprehensiveness of the hierarchical call relationship of the procedural query statement, which helps database administrators and developers fully grasp the entire execution flow of the procedural query and avoid misjudgments of performance issues due to missing information.
[0094] In other implementations, step S106 may further include the following steps: obtaining a preset specified target, wherein the specified target includes at least one of a specified query, a specified event, and a specified object; comparing statistical data with the specified target, and generating a vector graphic based on the portion of the statistical data containing the specified target. In other words, there are some extended functions for generating vector graphics, and these extended functions can be used in combination:
[0095] 1. Specify query process generation: During the generation of visualized vector graphics, a query process can be specified. That is, the query must exist in the procedural query hierarchy call relationship to be perceived by the generation process of visualized vector graphics.
[0096] 2、 specified event generation: in the process of generating the visualized vector diagram, the query event generation can be specified, that is, the sampled event is the specified event, which is perceived by the generation process of the visualized vector diagram.
[0097] 3、 specified object generation: in the process of generating the visualized vector diagram, the sampling object generation can be specified, that is, the sampled object is the specified object, which is perceived by the generation process of the visualized vector diagram.
[0098] Therefore, the statistical method of the process query of the embodiment of the application generates a vector diagram by acquiring a preset specified target and comparing it with statistical data, realizes the focus on the specified query, the specified event and / or the specified object, realizes that the user can flexibly customize the analysis focus according to actual needs, avoids the interference of irrelevant information, improves the pertinence and efficiency of analysis, helps to quickly locate specific performance problems, and meets the individualized analysis needs in different scenarios.
[0099] Compared with the reporting scheme of the existing product, the statistical method of the process query of the embodiment of the application can record the process query statement actually causing performance loss, facilitates correct and specific positioning of the query statement actually causing performance loss, simultaneously realizes dynamic display of resource consumption of the process query statement and reaction of the calling relationship, time proportion and event type between the process query statements, and effectively provides a basis for performance tuning.
[0100] Figure 4 is a control flow chart of the statistical method of the process query according to an embodiment of the embodiment of the application. The following will be combined with Figure 4 The flow steps of the embodiment will be specifically described.
[0101] Step S402, acquire the process query statement in the database.
[0102] Step S404, start the sampling task and configure the sampling parameters. It should be noted that the sampling parameters include the sampling frequency, the sampling object, the total sampling time, the events to be recorded, etc.
[0103] Step S406, execute the process query statement.
[0104] Step S408, sample the query statement called in the execution process of the process query statement, the event occurred and the sampling object at a preset sampling frequency for multiple times, and take the sampled data as the statistical data.
[0105] Step S410, sequentially write the statistical data into the statistical file of the database.
[0106] Step S412, generating a vector diagram corresponding to the process query statement according to the statistical data. It should be noted that the vector diagram is used to display the process query statement and the query statement called in the execution process of the process query statement.
[0107] Step S414, in response to a mouse hovering operation on any subgraph, displaying the query statement corresponding to the triggered subgraph completely.
[0108] Step S416, in response to a mouse clicking operation on any subgraph, displaying the triggered local data in an enlarged manner and showing the proportion of each event of the corresponding query statement in the sampling process. Thus, the current flow ends.
[0109] By using the above method, the hierarchical calling relationship of the process query statement is converted into a graphical structure in a visual manner by combining the sampling statistical data and the vector diagram generation, which provides a data carrier for subsequent query optimization, effectively solves the problem that the basis for performance tuning cannot be accurately provided due to the missing information of the statement actually causing performance loss, improves the tuning capability of the process query statement, and thus improves the efficiency and accuracy of the database management.
[0110] The embodiment also provides a computer readable storage medium, a computer program product and a computer device. Figure 5 is a structural schematic diagram of a computer program product according to an embodiment of the present application, Figure 6 is a structural schematic diagram of a computer readable storage medium according to an embodiment of the present application, Figure 7 is a structural schematic diagram of a computer device according to an embodiment of the present application.
[0111] As shown in Figure 5 , the computer program product 10 includes a computer program 11, which, when executed by the processor 32, implements the steps of the process query statistical method of any of the above embodiments. As shown in Figure 6 , the computer readable storage medium 20 stores the above computer program 11, and the computer program 11, when executed by the processor 32, implements the steps of the process query statistical method of any of the above embodiments. As shown in Figure 7 , the computer device 30 can include a memory 31, a processor 32, and a computer program 11 stored on the memory 31 and running on the processor 32, and the processor 32 implements the steps of the process query statistical method of any of the above embodiments when executing the computer program 11.
[0112] The computer program 11 for performing the operations of the present application can be assembly code, instruction set architecture (ISA) code, machine code, machine dependent instructions, microcode, firmware instructions, state-setting data, configuration data for integrated circuitry, or either source code or object code written in any combination of one or more programming languages, all of which can be transformed by an implementation of an interpreter, compiler, code builder, or assembler, etc. into machine code or object code suitable for execution by the machine 12. The computer program 11 can execute entirely on the user's computer, partly on the user's computer, as a stand-alone software package, partly on the user's computer and partly on a remote computer or entirely on the remote computer or server. In the latter scenario, the remote computer can be connected to the user's computer through any type of network, including a local area network (LAN) or a wide area network (WAN), or the connection can be made to an external computer (for example, through the Internet using an Internet Service Provider). In some embodiments, electronic circuitry including, for example, programmable logic circuitry, field-programmable gate array (FPGA), or programmable logic array (PLA) can execute the computer readable program instructions by utilizing state information of the computer readable program instructions to personalize the electronic circuitry, in order to perform aspects of the present application.
[0113] For the purposes of this description of the embodiments, the computer program product 10 is a product of authorship that includes the computer program 11.
[0114] For the purposes of this description of the embodiments, the computer readable storage medium 20 is a tangible device that can retain and store computer program instructions 11 for use by or in connection with an instruction execution system, apparatus, or device. More specifically, the computer readable storage medium 20 can be, without limitation, a portable computer diskette, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or Flash memory), a static random access memory (SRAM), a portable compact disc read-only memory (CD-ROM), a digital versatile disk (DVD), a memory stick, a floppy disk, and / or a mechanism of information storage such as a
[0115] The computer device 30 can be, for example, a server, a desktop computer, a notebook computer, a tablet computer, or a smart phone. In some examples, the computer device 30 can be a cloud computing node. The computer device 30 can be described in the general context of computer system-executable instructions, such as program modules, being executed by a computer system. Generally, program modules can include routines, programs, objects, components, logic, data structures, and so on that perform particular tasks or implement particular abstract data types. The computer device 30 can be practiced in distributed cloud computing environments where tasks are performed by remote processing devices that are linked through a communications network. In a distributed cloud computing environment, program modules can be located in both local and remote computer system storage media including memory storage devices.
[0116] The computer device 30 can include a processor 32 adapted to execute instructions stored in memory 31, which in operation provide temporary storage of instructions during execution by the processor 32. The processor 32 can be a single core processor, multi-core processor, computing cluster, or any number of other configurations. The memory 31 can include random access memory (RAM), read only memory, flash memory, or any other suitable memory systems.
[0117] The computer device 30 can also include a network adapter / interface and an input / output (I / O) interface. The I / O interface allows for input and output of data with external devices that can be connected to the computer device. The network adapter / interface can provide for communication between the computer device and a network, generally illustrated as a communication network.
[0118] The flow diagrams provided herein are not intended to indicate that the operations of the methods will be executed in any particular order, or that all of the operations of the methods will be included in every case. Additionally, the methods can include additional operations. Additional changes can be made to the above-described methods within the scope of the technology provided by the present embodiments.
[0119] Thus far, those skilled in the art will recognize that while the present application has been shown and described in relation to a few exemplary embodiments thereof, a variety of other changes and modifications can be made in the present application without departing from the spirit and scope of the application. Therefore, the scope of the present application should be understood to cover all such changes and modifications.
Claims
1. A statistical method for procedural queries, comprising: Retrieve procedural query statements from the database; The results of the procedural query statements are statistically analyzed by sampling the data, and the statistical data is recorded. A vector graphic corresponding to the procedural query statement is generated based on the statistical data. The vector graphic is used to display the procedural query statement and the query statements called by the procedural query statement during execution.
2. The statistical method for procedural queries according to claim 1, wherein, The steps of sampling and statistically analyzing the results of the procedural query statements and recording the statistical data include: At a preset sampling frequency, the query statements called during the execution of the procedural query statement, the events that occur, and the sampling objects are sampled multiple times, and the sampled data is used as the statistical data. The statistical data is written sequentially into the statistical file of the database.
3. The statistical method for procedural queries according to claim 2, wherein, The vector diagram includes multiple layers of bar charts stacked vertically. The bottom layer of the multiple layers of bar charts is used to display the procedural query statement, and each non-bottom layer bar chart is used to display the query statement called by the query statement in the next layer bar chart, so as to show the hierarchical calling relationship of the procedural query statement during execution.
4. The statistical method for procedural queries according to claim 3, wherein, Each layer of the bar chart includes at least one horizontally extending subchart, each subchart representing a query statement, and the length of the subchart is positively correlated with the number of times the query statement it represents is sampled.
5. The statistical method for procedural queries according to claim 4, wherein, After the step of generating a vector map corresponding to the procedural query statement based on the statistical data, the statistical method for the procedural query further includes: In response to a first trigger command for any of the subgraphs, the query statement corresponding to the triggered subgraph is fully displayed; and / or In response to a second trigger command for any of the subgraphs, the triggered local data is enlarged and the proportion of each event that occurred during the sampling process is displayed, along with the corresponding query statement.
6. The statistical method for procedural queries according to claim 3, wherein, The step of generating a vector graphic corresponding to the procedural query statement based on the statistical data includes: Obtain a preset specified target, wherein the specified target includes at least one of a specified query, a specified event, and a specified object; The statistical data is compared with the specified target, and the vector diagram is generated based on the partial data of the specified target contained in the statistical data.
7. The statistical method for procedural queries according to claim 2, wherein, The statistical file includes a file header, an event list, a query list, and a sampling event record. The file header stores basic information about the statistical file, including a preset sampling frequency and the number of samples. The event list records all events that occur during the sampling process. The query list records all query statements that occur during the sampling process. The sampling event record sequentially records the hierarchical call relationship of procedural queries and the sampling objects.
8. A computer-readable storage medium having a computer program stored thereon, said computer program, when executed by a processor, implementing the steps of the statistical method for procedural querying as described in any one of claims 1 to 7.
9. A computer program product, comprising a computer program, characterized in that, When the computer program is executed by a processor, it implements the steps of the statistical method for procedural querying as described in any one of claims 1 to 7.
10. A computer device comprising a memory, a processor, and a computer program stored in the memory and running on the processor, wherein the processor, when executing the computer program, implements the steps of the statistical method for procedural querying according to any one of claims 1 to 7.