Parameter value statistics method, device, computer equipment and readable storage medium

Abstract

The embodiment of the present application provides a parameter value statistics method, device, computer equipment and readable storage medium. The method unifies the collection and storage logic of the two parameter values ​​by collecting parameter information including constant information and binding variable information before the SQL statement is parameterized, and optimizes the efficiency of executing statistics in the database; further, the parameter information is stored in the shared memory of the database, ensuring that the parameter information is synchronized between all sessions, which is convenient for the database administrator to tune the entire instance in a certain session; further, based on the locking mechanism of the database, the logic of concurrent access to the shared memory is controlled to ensure the integrity of data operations of a set of parameters in the SQL statement in a multi-concurrent environment, and also effectively reduce the performance impact caused by frequent operations on the shared memory.

Description

Technical Field

[0001] The present application relates to the field of big data technology, and in particular to a parameter value statistics method, device, computer equipment and readable storage medium. Background Art

[0002] In the SQL (Structured Query Language) statements executed by the database, some parameter information such as constant values ​​and bound variable values ​​are often included. For example, the following SQL statement is executed: select idfrom t1 where id>? or id<10; ? binds the value 5 (LONG type). Among them, 10 is a constant value, and the variable value bound at ? can be set to 5 when executing. However, when you want to view the executed SQL statement inside the database, the query result is often parameterized after the constant and bound variable values ​​are parameterized. Taking the sys_stat_statement component in the database as an example, the bound variable 5 is parameterized as $1, and the constant 10 is parameterized as $2. This is because for a type of SQL with only different parameter values, it is not necessary to record the complete SQL text and parameter values ​​every time it is executed. In the existing implementation of the statistics module, in order to realize the statistical update of cumulative statistics so as to reuse storage resources, queryid is used as the unique identifier of the SQL statement. For example, for two SQL statements: select * from a where id < 1, select * form a where id < 10, they can be recorded as one item when storing related statistical information. Although their parameter value information is different, the queryid is the same.

[0003] This implementation facilitates centralized analysis of the same SQL statement. However, when performing database performance tuning, it is sometimes necessary to know the values ​​of two types of parameters, constants and bind variables, in the executed statement to determine their impact on the execution plan selection. In the current database, if you want to view the parameter values ​​before parameterization, you need to enable the log_statement parameter, and then find the corresponding information printed in the log file; if you want to view the execution time at the same time, you also need to set log_min_duration_statement to control whether the duration of the current SQL statement should be recorded in the log file; moreover, the parameterized SQL statement is usually obtained from the KWR report or sys_stat_statements. Enabling the above parameters has a significant impact on the performance of the database. The entire process is cumbersome and requires constant switching of observation positions. Summary of the invention

[0004] The embodiments of the present application provide a parameter value statistics method, apparatus, computer equipment and readable storage medium to achieve the effect of improving the incremental backup speed.

[0005] In a first aspect, an embodiment of the present application provides a parameter value statistics method, including:

[0006] Receive SQL statements;

[0007] Collecting parameter information of the SQL statement before parameterization, wherein the parameter information includes constant information and binding variable information;

[0008] Storing the parameter information in a shared memory of a database;

[0009] Based on the locking mechanism of the database, the logic of concurrent access to the shared memory is controlled, wherein the concurrent access is used to count SQL parameter values.

[0010] In a possible implementation, collecting parameter information of the SQL statement before parameterization includes:

[0011] For an SQL statement using an extended message protocol, constant information is extracted from a P message, and binding variable information is obtained by parsing in a B message; wherein the extended message protocol includes a P message and a B message;

[0012] For an SQL statement using a simple message protocol, constant information is read from an execution plan cached in a Q message; wherein the simple message protocol includes a Q message, and the execution plan is generated by the Q message based on the SQL statement.

[0013] In a possible implementation manner, storing the parameter information in a shared memory of a database includes:

[0014] Storing the parameter information in a hash table of a shared memory;

[0015] Each constant and each binding variable involved in each SQL statement is stored separately as a record.

[0016] In a possible implementation, the hash table is divided into a preset number of partitions, and each partition corresponds to a partition lock; the concurrent access includes a query operation;

[0017] The database-based locking mechanism controls the logic of concurrent access to the shared memory, including:

[0018] Setting a shared lock for each partition in the hash table, and traversing and querying the hash table;

[0019] A set of parameter values ​​corresponding to the current query identifier queried is simultaneously added with a spin lock, and the set of parameter values ​​is read to obtain a query result; wherein the query identifier is generated by the database according to the logical structure of the parameterized SQL statement;

[0020] After the query is completed, the spin lock of the group and the shared lock of each partition are released in turn.

[0021] In a possible implementation, the concurrent access also includes an insert operation and an update operation;

[0022] The database-based locking mechanism controls the logic of concurrent access to the shared memory, and further includes:

[0023] For one of the SQL statements, if it is found that the first parameter of the parameter information corresponding to the SQL statement does not exist in the hash table, first release the shared lock of the group corresponding to the parameter information in the hash table, then add an exclusive lock to the group, insert the parameter information into the hash table, and release the exclusive lock after the insertion is completed;

[0024] Otherwise, find the entry address of the group of parameters to be updated, add a spin lock for the group, update each parameter one by one according to the entry address, and release the spin lock after the update is completed.

[0025] In a possible implementation, the concurrent access also includes a delete operation;

[0026] The database-based locking mechanism controls the logic of concurrent access to the shared memory, and further includes:

[0027] Setting the partition lock of each partition of the hash table to an exclusive lock, and deleting redundant entries in the hash table;

[0028] Release the exclusive lock after the deletion is completed.

[0029] In a possible implementation, the method further includes:

[0030] If an SQL statement has executed an insert operation, and the parameter information corresponding to the SQL statement has been stored in the hash table, the parameter information in the hash table is updated once after the SQL statement is subsequently executed a preset number of times.

[0031] In a second aspect, an embodiment of the present application provides a parameter value statistics device, including:

[0032] A receiving module, used for receiving SQL statements;

[0033] A collection module, used to collect parameter information of the SQL statement before parameterization, wherein the parameter information includes constant information and binding variable information;

[0034] A storage module, used for storing the parameter information in a shared memory of a database;

[0035] A statistics module is used to control the logic of concurrent access to the shared memory based on a locking mechanism of the database, wherein the concurrent access is used to count SQL parameter values.

[0036] In a third aspect, an embodiment of the present application provides a computer device, including: a memory, a processor;

[0037] The memory stores computer-executable instructions;

[0038] The processor executes the computer-executable instructions stored in the memory, so that the processor executes the above first aspect and / or various possible implementations of the first aspect.

[0039] In a fourth aspect, an embodiment of the present application provides a computer-readable storage medium, in which computer-executable instructions are stored. When the computer-executable instructions are executed by a processor, they are used to implement the first aspect above and / or various possible implementations of the first aspect.

[0040] In a fifth aspect, an embodiment of the present application provides a computer program product, including a computer program, which, when executed by a processor, implements the above first aspect and / or various possible implementation methods of the first aspect.

[0041] A parameter value statistics method, apparatus, computer equipment and readable storage medium provided in an embodiment of the present application unify the collection and storage logic of the two parameter values ​​by collecting parameter information including constant information and binding variable information before parameterization of SQL statements, and optimize the efficiency of executing statistics in the database; further, the parameter information is stored in the shared memory of the database, ensuring that the parameter information remains synchronized between all sessions, making it convenient for database administrators to tune the entire instance in a certain session; further, based on the locking mechanism of the database, the logic of concurrent access to the shared memory is controlled to ensure the integrity of data operations of a set of parameters in the SQL statement in a multi-concurrent environment, and also effectively reduce the performance impact caused by frequent operations on the shared memory. BRIEF DESCRIPTION OF THE DRAWINGS

[0042] The accompanying drawings, which are incorporated in and constitute a part of this specification, illustrate embodiments consistent with the present application and, together with the description, serve to explain the principles of the present application.

[0043] Figure 1 A schematic diagram of an application environment of a parameter value statistics method in an embodiment;

[0044] Figure 2 Schematic diagram of a parameter value statistics method in one embodiment;

[0045] Figure 3 A schematic diagram of a flow chart of a step of collecting parameter information in an embodiment;

[0046] Figure 4 A schematic diagram of a process for determining a partition according to an identifier in an embodiment;

[0047] Figure 5 A schematic diagram of a flow chart of executing query operation steps in an embodiment;

[0048] Figure 6 is a flow chart of frequency control logic in one embodiment;

[0049] Figure 7 A schematic diagram of a flow chart of executing insert operation and update operation steps in one embodiment;

[0050] Figure 8 A schematic diagram of a flow chart of an automatic cleaning step in an embodiment;

[0051] Fig. 9 is a structural block diagram of a parameter value statistics device in one embodiment;

[0052] Fig.10 FIG. 4 is a diagram showing the internal structure of a computer device in one embodiment.

[0053] The above drawings have shown clear embodiments of the present application, which will be described in more detail later. These drawings and text descriptions are not intended to limit the scope of the present application in any way, but to illustrate the concept of the present application to those skilled in the art by referring to specific embodiments. DETAILED DESCRIPTION

[0054] Exemplary embodiments will be described in detail herein, examples of which are shown in the accompanying drawings. When the following description refers to the drawings, the same numbers in different drawings represent the same or similar elements unless otherwise indicated. The implementations described in the following exemplary embodiments do not represent all implementations consistent with the present application. Instead, they are merely examples of devices and methods consistent with some aspects of the present application as detailed in the appended claims.

[0055] The parameter value statistical method provided in the embodiment of the present application can be applied to Figure 1 In the application environment shown, the terminal 102 communicates with the server 104 via the network.

[0056] For example, the parameter value statistics method is applied to the terminal 102. The user inputs an SQL statement through the terminal 102. After receiving the SQL statement, the server 104 collects the parameter information of the SQL statement before parameterization, wherein the parameter information includes constant information and binding variable information. The server 104 also stores the parameter information in the shared memory of the database; and based on the locking mechanism of the database, controls the logic of concurrent access to the shared memory, wherein the concurrent access is used to count the SQL parameter values. In this way, the server 104 feeds back the counted SQL parameter values ​​to the terminal 102 for the user to view and process.

[0057] Among them, the terminal 102 can be, but is not limited to, various personal computers, laptops, smart phones, tablet computers, Internet of Things devices and portable wearable devices. The Internet of Things devices can be smart speakers, smart TVs, smart air conditioners, smart car-mounted devices, etc. Portable wearable devices can be smart watches, smart bracelets, head-mounted devices, etc. The first server 104 and the second server 108 can be implemented by independent servers or server clusters composed of multiple servers. The first terminal 102 and the first server 104, the first server 104 and the second terminal 106, and the first server 104 and the second server 108 can be directly or indirectly connected through wired or wireless communication, such as through a network connection.

[0058] The above parameter value statistics method, by collecting parameter information including constant information and binding variable information before SQL statements are parameterized, unifies the collection and storage logic of the two parameter values, and optimizes the efficiency of executing statistics in the database; further, the parameter information is stored in the shared memory of the database, ensuring that the parameter information remains synchronized between all sessions, making it convenient for database administrators to tune the entire instance in a certain session; further, based on the locking mechanism of the database, the logic of concurrent access to the shared memory is controlled to ensure the integrity of data operations of a set of parameters in the SQL statement in a multi-concurrent environment, thereby ensuring the accuracy of data statistics.

[0059] It should be noted that the network communication between the terminal 102 and the server 104 is applicable to different network standards, for example, it can be applicable to global system of mobile communication (GSM), code division multiple access (CDMA), wideband code division multiple access (WCDMA), time division synchronous code division multiple access (TD-SCDMA), long term evolution (LTE) system and future 5G and other network standards. Optionally, the above communication system can be a system in the scenario of ultra-reliable and low latency communications (URLLC) transmission in a 5G communication system.

[0060] Therefore, optionally, the above-mentioned base station can be a base station (Base Transceiver Station, referred to as BTS) and / or a base station controller in GSM or CDMA, or a base station (NodeB, referred to as NB) and / or a radio network controller (Radio Network Controller, referred to as RNC) in WCDMA, or an evolved base station (EvolutionalNode B, referred to as eNB or eNodeB) in LTE, or a relay station or access point, or a base station (gNB) in the future 5G network, etc., and the present application is not limited here.

[0061] The terminal 102 can be a wireless terminal or a wired terminal. A wireless terminal can be a device that provides voice and / or other service data connectivity to a user, a handheld device with a wireless connection function, or other processing devices connected to a wireless modem. A wireless terminal can communicate with one or more core network devices via a radio access network (Radio Access Network, referred to as RAN). A wireless terminal can be a mobile terminal, such as a mobile phone (or "cellular" phone) and a computer with a mobile terminal, for example, a portable, pocket-sized, handheld, computer-built-in or vehicle-mounted mobile device, which exchanges language and / or data with a radio access network. For another example, a wireless terminal can also be a personal communication service (Personal Communication Service, referred to as PCS) phone, a cordless phone, a Session Initiation Protocol (Session Initiation Protocol, referred to as SIP) phone, a wireless local loop (Wireless Local Loop, referred to as WLL) station, a personal digital assistant (Personal Digital Assistant, referred to as PDA) and other devices. The wireless terminal may also be referred to as a system, a subscriber unit, a subscriber station, a mobile station, a mobile station, a remote station, a remote terminal, an access terminal, a user terminal, a user agent, a user device or user equipment, without limitation. Optionally, the terminal device may also be a smart watch, a tablet computer or other device.

[0062] In one embodiment, a parameter value statistics method is provided. This embodiment takes the method applied to the server 104 as an example for illustration. Figure 2 As shown, the parameter value statistical method includes:

[0063] Step 202: Receive SQL statement.

[0064] Specifically, SQL is a language specifically used to communicate with databases. It is a standardized query language used in database management systems (DBMS) to perform various database operations, including data query, data update, data definition, and data access control. SQL statements are the basic units that make up the SQL language. The types of SQL statements include data query (SELECT), data insertion (INSERT INTO), data update (UPDATE), data deletion (DELETE FROM), etc.

[0065] Step 204: Collect parameter information of the SQL statement before parameterization, where the parameter information includes constant information and binding variable information.

[0066] Specifically, when SQL statements with the same logical structure are executed with different parameters, the database can use the same execution plan after parameterizing the SQL statement, thus avoiding repeated compilation and improving execution efficiency.

[0067] Furthermore, after receiving the SQL statement input by the user, the database performs syntax analysis on it and extracts the logical structure of the SQL, such as the table name, field name, WHERE condition, etc. Then, the parts of the SQL statement that may change, such as constant values, are replaced with placeholders, such as binding variables, to form parameterized SQL. According to the logical structure of the parameterized SQL, a unique query identifier (queryid) is generated.

[0068] Furthermore, the collected parameter information also includes the queryid of the SQL statement, the position in the SQL statement (position), the mark of whether it is a bound variable or a constant (id_bind), the string value of the parameter (value), the data type (data_type), and a spin lock (mutex), which will be activated when the parameter information is accessed to implement a row lock to protect the copying of the parameter string value value.

[0069] Step 206: Store the parameter information in the shared memory of the database.

[0070] Specifically, the shared memory of a database refers to a memory area in a database system that is used to store data that can be shared by multiple database processes or threads. Shared memory is an inter-process communication (IPC) mechanism that allows multiple processes to share and access the same memory area, thereby achieving efficient data communication. At the same time, each process only maintains one set of statistical data, without data redundancy.

[0071] The embodiment of the present application stores parameter information in shared memory, so that a database administrator (DBA) can grasp the overall operation information of the database instance, which is convenient for tuning according to the operation information of the entire instance in a certain session.

[0072] Step 208: Based on the locking mechanism of the database, control the logic of concurrent access to the shared memory, wherein the concurrent access is used to count SQL parameter values.

[0073] Specifically, when tuning database performance, database administrators need to understand the impact of parameter values ​​on performance in order to determine whether the execution plan is reasonable. For example, the SQL statements in the database may have a large number of repeated queries, but with different parameter values. If a certain type of SQL has performance problems, the DBA needs to check the specific parameter values ​​and execution status to locate the problem. By accessing the hash table, you can obtain statistical information about SQL execution, such as the number of executions, execution time, number of cache hits, etc. The DBA will perform further tuning based on this statistical information, such as optimizing indexes, adjusting query structures, or rewriting SQL.

[0074] During performance tuning, the DBA usually needs to perform the following operations on the information in shared memory:

[0075] The query operation obtains SQL parameter information by accessing shared memory, including constant information, bound variable information, and statistical data such as execution time and execution times. Through the parameter information, the differences in SQL execution plans under different parameter values ​​can be compared to locate performance bottlenecks.

[0076] Update operation: During the operation of the database, the parameter information in the shared memory will be updated periodically to ensure that the data is up to date. In addition, the DBA can also manually trigger the update through the tool to ensure that the data obtained during the tuning is accurate.

[0077] Insert operation: When a SQL statement is executed for the first time, the database inserts the parameter information into the shared memory. The DBA may need to perform a preliminary analysis of the newly introduced SQL statement to assess its impact on performance.

[0078] Delete operation: If there is outdated or redundant information in the hash table, the DBA can clean up these records to release shared memory space. For example, reset parameter information or delete historical statistics of certain SQL statements.

[0079] Furthermore, concurrent access means that in the database, multiple different sessions may access shared memory at the same time. The database administrator may query or modify the SQL parameter information in the shared memory during performance tuning. These operations may conflict with the running SQL, resulting in concurrent access to query, update, delete, or insert operations.

[0080] Furthermore, when the capacity of the database in the shared memory reaches the maximum threshold, a background task is started to clean up expired or low-priority entries. These cleanup tasks may also compete with the running SQL for access rights to the shared memory, affecting the overall performance.

[0081] Furthermore, frequent operations on shared memory while maintaining high concurrent access occupy a large amount of resources and have a significant impact on database performance.

[0082] The locking mechanism of the database manages concurrent access by setting locks for resources in shared memory, ensuring data consistency and integrity while maximizing the concurrent performance of the system. The locking mechanism usually involves different lock types, lock granularity, and lock strategies. These include shared locks (S Lock), exclusive locks (X Lock), spin locks, etc. Based on the above locking mechanisms, the logic of reasonable control of concurrent access can effectively reduce the impact on performance.

[0083] The above parameter value statistics method, by collecting parameter information including constant information and binding variable information before SQL statements are parameterized, unifies the collection and storage logic of the two parameter values, and optimizes the efficiency of executing statistics in the database; further, the parameter information is stored in the shared memory of the database, which ensures that the parameter information remains synchronized between all sessions, making it convenient for database administrators to tune the entire instance in a certain session; further, based on the locking mechanism of the database, the logic of concurrent access to the shared memory is controlled to ensure the integrity of data operations of a set of parameters in the SQL statement in a multi-concurrent environment, and also effectively reduce the performance impact caused by frequent operations on the shared memory.

[0084] Optionally, parameter information before SQL statement parameterization is collected, including:

[0085] For SQL statements using the extended message protocol, constant information is extracted from the P message, and binding variable information is obtained by parsing in the B message; wherein the extended message protocol includes the P message and the B message.

[0086] For an SQL statement using a simple message protocol, constant information is read from an execution plan cached in a Q message; wherein the simple message protocol includes a Q message, and the execution plan is generated by the Q message based on the SQL statement.

[0087] Specifically, collecting parameter information before SQL statements are parameterized can be understood as the locations in the SQL statement processing flow where statistical information of two types of parameters, constants and bound variables, should be collected, summarized and reported, and the reported information is processed and stored.

[0088] Furthermore, the SQL execution model of the database in processing the client message request mainly includes the simple message protocol (Q) and the extended message protocol (PBE).

[0089] The Q message (Simple Query) in the Simple Query Protocol (Q) refers to the message used to receive SQL statements and generate execution plans in database communication. Under this protocol, the execution plan will be cached to improve the efficiency of subsequent execution of the same SQL statement. The Simple Query Protocol is mainly for situations where there are no bound variables, but constants can exist in SQL statements.

[0090] The extended message protocol (PBE) includes P messages (Parse Message), B messages (Bind Message) and E messages (Execute Message). P messages are used to parse SQL statements, generate parse trees and query trees, and save this information; B messages are used to bind parameters to a prepared SQL statement. If it is a new statement, an execution plan will be generated and saved; if there is an existing plan, it will be used directly; E messages (ExecuteMessage) are used to execute SQL statements that have bound parameters, such as querying, inserting, updating or deleting data. After the execution is completed, the database will return the results to the client. The extended message protocol is mainly for the case of binding variables, but constants can also exist in SQL statements.

[0091] Both of the above protocols can obtain constant information from the query tree (QueryTree) structure after semantic analysis and query rewriting. For example, the sys_stat_statements plug-in completes the calculation of queryid and parameterization of SQL statements in the hook after semantic analysis. On the other hand, it is not convenient to obtain constant information from the plan tree PlanTree generated after the planning stage.

[0092] See also Figure 3 Based on this, for SQL statements using the simple message protocol, the embodiment of the present application first queries whether there is a cached execution plan in the Q message. If so, the constant information cached in the plan can be directly read; otherwise, it is necessary to parse the SQL statement and rewrite the query in the Q message to obtain the query tree, then traverse the query tree to read the constant node information, report and mark it. After the parsing is successful, the database generates an execution plan based on the SQL statement for execution. Since binding statements cannot be executed in ordinary protocols, there is no binding variable information to be collected in the Q message.

[0093] For SQL statements using the extended message protocol, in the P message, the constant information is extracted from the rewritten query tree and saved in the global cache structure. The parsed bind variable information is read in the B message, and the constant and bind variable information is assembled and reported in the B message. Finally, the E message executes the SQL statement.

[0094] The above parameter value statistics method combines the two types of parameter value information, constants and bound variables, and unifies the collection logic of the two types of information, thereby ensuring the consistency of parameter values ​​of the same SQL statement before and after updating.

[0095] Optionally, parameter information is stored in the shared memory of the database, including:

[0096] The parameter information is stored in a hash table in the shared memory, wherein each constant and each bind variable involved in each SQL statement is stored separately as a record.

[0097] Specifically, the data structure used by the shared memory in the embodiment of the present application selects a hash table mode, and uses the calculated queryid and position as the identifier (key) of the SQL statement to count each parameter information. The database uses a hash function to calculate the above identifier to determine which partition the identifier belongs to. When querying, the entry in the hash table is searched according to the identifier, ensuring the efficiency of query and update.

[0098] Furthermore, an SQL statement may include multiple constants and multiple binding variables. In the embodiment of the present application, each constant and binding variable of each SQL statement is counted separately as a record, rather than merging and counting multiple constants and multiple binding variables of each SQL statement as a record. In this way, a single SQL statement can dynamically store multiple parameter value information, and there is no information interception, which can facilitate the subsequent assembly of SQL statements to be more structured and simplifies the parsing of parameter content.

[0099] The above parameter value statistics method stores the parameter information in the shared memory of the database, ensuring that the parameter information remains synchronized between all sessions, making it convenient for database administrators to tune the entire instance in a certain session; further, queryid and position are used as identifiers of SQL statements, and the entries in the hash table are searched based on the identifiers during query, ensuring the efficiency of query and update.

[0100] Optionally, the hash table is divided into a preset number of partitions, and each partition corresponds to a partition lock.

[0101] Specifically, if the Hash table used to store parameter information in the shared memory has only one LightLock for concurrency control, there will be a serious performance bottleneck when reading and writing the Hash table at a high frequency. By dividing the Hash table into multiple partitions, and each partition corresponds to a partition lock, different transactions can operate on data in different partitions at the same time without blocking each other. It should be understood that LightLock is a lightweight lock mechanism, which is mainly used within the database to lock some short-term data access or resource protection, and is usually used to reduce lock overhead and improve concurrency performance. In the above case, LightLock is used as a partition lock. Exemplarily, the embodiment of the present application divides the Hash table into 128 partitions, and then applies for 128 more LightLocks in the kernel. Different locks protect different partitions to reduce lock conflicts.

[0102] See also Figure 4 , the data set extracts the queryid and position of the SQL statement, uses the queryid and position as the key of the Hash table entry, and uses the Hash function to calculate the above key to obtain the hash code. The main function of the Hashcode is to map the key value to a specific position in the Hash table. Through this position, the position of the entry in the Hash table can be quickly located, and the Hash code can also locate the Lightlock lock corresponding to the entry.

[0103] The above parameter value statistical method divides the hash table into multiple partitions and configures a partition lock for each partition, so that different locks protect different partitions, reducing lock conflicts and further reducing the performance impact caused by frequent operations on shared memory.

[0104] Optionally, the logic for controlling concurrent access to shared memory based on the database locking mechanism includes:

[0105] Set the partition lock of each partition of the shared memory hash table to a shared lock.

[0106] When traversing the query shared memory Hash table, a group of parameter values ​​corresponding to the current query identifier are simultaneously added with a spin lock, and the group of parameter values ​​are read to obtain the query result; wherein, the query identifier is generated by the database according to the logical structure of the parameterized SQL statement.

[0107] After the query is completed, the spin lock of the group and the shared lock of each partition are released in turn.

[0108] Specifically, shared locks allow multiple transactions to read the same data, but do not allow write operations. By setting shared locks on each partition, you can reduce lock contention and waiting time while ensuring that multiple parties can read at the same time, thereby optimizing system performance.

[0109] Furthermore, on the basis of setting a shared lock for each partition, a spin lock is set for all parameters of the group at the same time when reading parameter values. This is because the granularity of shared locks and spin locks is different. The granularity of shared locks is coarser, and the entire partition is locked to control concurrent access at the partition level; while the granularity of spin locks is finer, and specific entries or target records of queries are locked. The combination of the two allows shared locks to protect the entire partition, while spin locks protect specific entries.

[0110] Furthermore, adding a spin lock to the corresponding row during the update operation is to prevent a set of parameters of a SQL from being updated in other sessions after a part of the parameters are queried. Therefore, when traversing the shared memory hash table for querying, a set of parameter values ​​corresponding to the current queryid needs to be spin locked at the same time, and then the set of entries is read, and the read entries are processed into tuples, which are fed back to the client as query results.

[0111] Optionally, in order to process a group of entries corresponding to the current queryid at a time during query, a pointer array is defined in an embodiment of the present application to record a group of entries corresponding to the current queryid that are queried when traversing and querying the shared memory Hash table.

[0112] Optionally, in order to avoid repeated processing of the same queryid, the embodiment of the present application also provides a new local hash table for recording whether all parameter entries corresponding to the queryid have been placed in the tuple during the current query.

[0113] See also Figure 5 , the following combination Figure 5 The query operation of this embodiment is described in detail:

[0114] Step 1: Query all entries in the shared memory hash table and create a local hash table to record whether all parameter entries corresponding to the queryid in this query have been placed in the tuple.

[0115] Step 2: Add all shared locks to the shared memory hash table, traverse the shared memory hash table, and process each entry in turn. When processing an entry, query the local hash table to obtain the entry corresponding to the current queryid and add it to the tuple. If it has been added to the tuple, process the next entry; otherwise, query and record all entries corresponding to the current queryid in the hash table, add spin locks to the above group of entries, put all the above group of entries into the tuple to be returned, and then release all spin locks. Furthermore, mark the current queryid in the local hash table as processed.

[0116] Step 3: After all query operations are completed, all shared locks are released, tuples are cleaned up and returned to the client, and the local hash table is cleaned up and the memory context is destroyed.

[0117] The above parameter value statistics method adds a shared lock to each partition when performing a traversal query operation on the shared memory Hash table, which can reduce lock contention and waiting time while ensuring that multiple parties can read at the same time, thereby optimizing system performance. Furthermore, when reading a group of entries corresponding to the current queryid, a spin lock is added to the group of entries at the same time to avoid other sessions updating the group of entries during the reading process. Furthermore, the defined pointer array can be used to quickly query the group of entries corresponding to the current queryid. Furthermore, the local local Hash table is set up to determine whether the group of entries corresponding to the current queryid has been placed in the tuple to avoid repeated processing of the same queryid.

[0118] Optionally, the logic for controlling concurrent access to the shared memory based on the locking mechanism of the database also includes:

[0119] For a SQL statement, if the first parameter of the parameter information corresponding to the SQL statement is found to be not present in the hash table, the shared lock of the group corresponding to the parameter information in the hash table is first released, and then an exclusive lock is added to the group, the parameter information is inserted into the hash table, and finally the exclusive lock is released.

[0120] Otherwise, find the entry address of the group of parameters to be updated, add spin locks for all the parameters in the group, update each parameter one by one according to the entry address, and release the spin lock first and then the shared lock after the update is completed.

[0121] Specifically, the query operation disclosed in the above embodiment is performed before executing the update operation or the insert operation. Therefore, a shared lock is first added to each partition, and then it is determined whether it is an update operation or an insert operation. If only an update is required, only the spin lock of the corresponding row needs to be added. The spin lock is suitable for scenarios with short lock waiting time and can quickly protect temporary resources. Only when an insertion is required, the shared lock is released first and then the exclusive lock is added. Avoid using exclusive locks when updating. Exclusive locks allow transactions to read and write data, but do not allow other transactions to perform any operations on the data. Therefore, reducing the use of exclusive locks can improve the throughput of the database in high concurrency scenarios, reduce performance bottlenecks caused by lock competition, and further reduce the performance impact caused by frequent operations on shared memory.

[0122] Furthermore, before inserting and updating data, the data to be operated is preprocessed and saved before locking to minimize the locking scope. The preprocessing step includes: converting the current set of parameter values ​​into a string type, calculating the hash code according to the key of the current set of parameters, and calculating and recording the partition lock number corresponding to the current set of parameters.

[0123] Optionally, the parameter value statistics method of the embodiment of the present application provides a frequency control logic, including: if an SQL statement has performed an insert operation, and the parameter information corresponding to the SQL statement has been stored in a hash table, then the parameter information in the hash table is updated once after a preset number of subsequent executions.

[0124] See also Figure 6 , exemplarily, each process uses a hash table in the local memory to record the number of times each SQL has been counted. Before updating or inserting the shared memory hash table, the process first accesses the local memory hash table to control the update frequency. Specifically, the database reports the statistical information entry function to determine whether the characteristic statistics switch is turned on. If it is turned on, the local memory hash table is queried according to the queryid. If the number of executions of the SQL statement corresponding to the queryid does not reach the preset number, the statistics are terminated. If the entry corresponding to the queryid is not found or the preset number is reached, the constant information is extracted from the query tree, the constant information and the bound variable information are assembled, and if there are parameters that need to be counted, the insert operation or update operation is performed. The performance impact caused by frequent operations on the shared memory is further reduced.

[0125] Furthermore, the step of performing an insert operation or an update operation includes: storing a statistical information entry function, cleaning a local memory hash table for recording the number of counted times for each SQL statement, and querying or inserting the counted times into the local memory hash table according to the queryid after cleaning. After preprocessing a group of parameters corresponding to the queryid, the group of parameters is locked according to the method in the above embodiment. The shared memory hash table is automatically cleaned, and the local memory hash table is synchronously cleaned, and finally the group of parameters is inserted or updated into the shared memory hash table, and all locks are released. This avoids the situation where new statistics cannot be inserted after the storage capacity reaches the upper limit.

[0126] Furthermore, the above-mentioned automatic cleaning of the local memory Hash table includes: when cleaning the entries of the shared memory Hash table, deleting the SQL statistics record in the local memory Hash table at the same time. In addition, automatic cleaning is performed before inserting into the local memory Hash table. Exemplarily, when the number of entries exceeds 90% of the maximum number of entries, all entries are discarded. The reason why the shared memory Hash table and the local memory Hash table are not synchronized in this embodiment is that when the current client cleans the shared memory Hash table and the local memory Hash table, it is not allowed to communicate with other clients.

[0127] Furthermore, the embodiment of the present application chooses to add a new data structure locally to record the number of executions, rather than adding a new field in the shared memory to record the number of executions, because if it is recorded in the shared memory, the frequency control logic itself will have a performance impact.

[0128] See also Figure 7 , the following combination Figure 7 The insert operation and update operation of this embodiment are described in detail:

[0129] Step 1: Entry function for storing statistical information; before executing the insert operation and the update operation, the frequency control logic disclosed in the above embodiment is executed based on the local memory hash table.

[0130] Step 2: Preprocess a set of parameters corresponding to queryid in the shared memory hash table. Since each entry stored in the shared memory hash table represents the information of a parameter, assuming that there are N parameters in an SQL statement, then one SQL statement corresponds to N entries in the shared memory hash table, so it is necessary to ensure the consistency of the set of N parameter entries corresponding to each SQL statement during operation.

[0131] Step three, add a shared lock to the above set of parameters. The shared memory Hash table in this embodiment is divided into 128 partitions, and 128 partition locks are applied accordingly. Since each insertion or update of the shared memory Hash table is to process a set of N parameter values ​​of an SQL statement, corresponding to N records of the shared memory Hash table, each operation corresponds to M Hash partitions, and also corresponds to M (1≤M≤128) partition locks. Furthermore, for a set of parameters to be inserted, first set the partition lock corresponding to the group of parameters to be shared locks, and query whether the first parameter of the group already exists in the shared memory Hash table. If not, execute step four; otherwise, execute step five. Furthermore, the data preprocessing part before locking has calculated and recorded the serial numbers of a set of M partition locks required. When locking, lock in ascending order of the lock sequence number; when unlocking, unlock in reverse order of the lock sequence number to avoid deadlock.

[0132] Step 4: If the first parameter does not exist, it is considered that none of the parameters exist, and an insert operation is required to insert a set of parameter value information for the current SQL statement. When inserting, first release this set of shared locks, clean up the excessive entries in the shared memory Hash table, and simultaneously clean up the local memory Hash table, and then set the partition lock corresponding to this set of parameters to an exclusive lock. Then, traverse this set of parameters and insert them into the shared memory Hash table. After the insertion is completed, release the exclusive lock corresponding to the set of partitions.

[0133] Step 5: If the first parameter exists, it is considered that a group of parameters are all in the shared memory, and only the parameter string value column in the parameter information needs to be updated. First, find the address of the entry in the current record of the group of parameters to be updated, and then use the record to add a spin lock to this group of entries. Then, traverse this group of parameters and update the parameter string value column one by one. After the update, release the spin lock and shared lock.

[0134] The above parameter value statistics method pre-processes the data to be operated before performing the insert and update operations, and then locks it, which reduces the locking range. The frequency control logic provided further reduces the performance impact caused by frequent operations on shared memory. The automatic cleaning of the shared memory hash table and the local memory hash table avoids the situation where new statistics cannot be inserted after the storage capacity reaches the upper limit.

[0135] Optionally, the logic for controlling concurrent access to the shared memory based on the locking mechanism of the database also includes:

[0136] Set the partition lock of each partition of the hash table to an exclusive lock and delete the redundant entries in the hash table.

[0137] Specifically, there are two cases for cleaning up entries in the shared memory hash table: the first is automatic cleaning; the second is manual cleaning. For the second case, since all entries are cleaned up, all exclusive locks are added, that is, all partitions are locked.

[0138] See also Figure 8 , the following combination Figure 8 Detailed description of the automatic cleanup process:

[0139] Step 1: Directly traverse the shared memory hash table and calculate the number of entries that need to be cleared. Since the partitions where the deleted entries are located are random, exclusive locks need to be added to all partitions.

[0140] Step 2: Determine whether the number of the above items exceeds the preset ratio. If not, no cleanup is required, the exclusive lock is released, and the main program is returned. If exceeded, execute step 3.

[0141] Step 3: Clear the entries in the shared memory hash table.

[0142] Step 4: traverse the shared memory hash table to determine whether a sufficient number of entries have been cleared. If the number of entries cleared is sufficient, unlock and return to the main program. If the number of entries cleared is insufficient, execute step 5.

[0143] Step 5: For the current queryid, find all the corresponding parameters, delete them one by one, and record the number of cleaned up. At the same time, delete the statistical number records corresponding to the current queryid in the local memory hash table, and jump to step 4.

[0144] The above parameter value statistics method can manually or automatically clean up redundant entries in the shared memory Hash table by adding exclusive locks to all partitions, thereby avoiding the situation where new statistics cannot be inserted after the storage capacity reaches the upper limit.

[0145] It should be understood that, although the steps in the flowcharts involved in the above embodiments are displayed in sequence according to the indication of the arrows, these steps are not necessarily executed in sequence according to the order indicated by the arrows. Unless there is a clear explanation in this article, the execution of these steps is not strictly limited in order, and these steps can be executed in other orders. Moreover, at least a part of the steps in the flowcharts involved in the above embodiments may include multiple steps or multiple stages, and these steps or stages are not necessarily executed at the same time, but can be executed at different times, and the execution order of these steps or stages is not necessarily carried out in sequence, but can be executed in turn or alternately with other steps or at least a part of the steps or stages in other steps.

[0146] The above parameter value statistics method combines the two types of parameter value information, constants and bound variables, and unifies the collection logic of the two types of information, ensuring the consistency of the parameter values ​​of the same SQL statement before and after the update. Storing parameter information in the shared memory of the database ensures that the parameter information is synchronized between all sessions, making it convenient for database administrators to tune the entire instance in a session. Automatically clearing the hash table avoids the situation where new statistics cannot be inserted after the storage capacity reaches the upper limit. Finally, through data preprocessing, setting frequency control logic, and controlling concurrent access logic based on the locking mechanism, the performance impact caused by frequent operations on shared memory is reduced.

[0147] Based on the same inventive concept, the embodiment of the present application also provides a parameter value statistics device for implementing the parameter value statistics method involved above. The implementation scheme for solving the problem provided by the device is similar to the implementation scheme recorded in the above parameter value statistics method, so the specific limitations in one or more device embodiments provided below can refer to the limitations of the parameter value statistics method above, and will not be repeated here.

[0148] See also Fig. 9 In one embodiment, the present application provides a parameter value statistics device 90, including: a receiving module 901, a collection module 902, a storage module 903 and a statistics module 904.

[0149] The receiving module 901 is used to receive SQL statements.

[0150] The collection module 902 is used to collect parameter information of SQL statements before parameterization, and the parameter information includes constant information and binding variable information.

[0151] The storage module 903 is used to store parameter information in the shared memory of the database.

[0152] The statistics module 904 is used to control the logic of concurrent access to the shared memory based on the locking mechanism of the database, wherein the concurrent access is used to count SQL parameter values.

[0153] Optionally, the collection module 902 collects parameter information before the SQL statement is parameterized, including: for SQL statements using an extended message protocol, extracting constant information from a P message, and parsing binding variable information in a B message; wherein the extended message protocol includes a P message and a B message; for SQL statements using a simple message protocol, reading constant information from an execution plan cached in a Q message; wherein the simple message protocol includes a Q message, and the execution plan is generated by the Q message based on the SQL statement.

[0154] Optionally, the storage module 903 stores parameter information in a shared memory of a database, including:

[0155] The parameter information is stored in a hash table in the shared memory, wherein each constant and each bind variable involved in each SQL statement is stored separately as a record.

[0156] Optionally, the hash table is divided into a preset number of partitions, and each partition corresponds to a partition lock. The statistical module 904 controls the logic of concurrent access to the shared memory based on the locking mechanism of the database, including: setting the partition lock of each partition of the shared memory hash table to a shared lock; when traversing and querying the shared memory hash table, adding a spin lock to a group of parameter values ​​corresponding to the current query identifier at the same time, reading this group of parameter values, and obtaining the query result; wherein the query identifier is generated by the database according to the logical structure of the parameterized SQL statement; after the query is completed, the spin lock of the group and the shared lock of each partition are released in turn.

[0157] Optionally, the statistical module 904 controls the logic of concurrent access to the shared memory based on the locking mechanism of the database, and also includes: for an SQL statement, if it is queried that the first parameter of the parameter information corresponding to the SQL statement does not exist in the hash table, first release the shared lock of the group corresponding to the parameter information in the hash table, then add an exclusive lock to the group, insert the parameter information into the hash table, and finally release the exclusive lock; otherwise, find the entry address of the group of parameters to be updated, add a spin lock to the group, update each parameter one by one according to the entry address, and release the spin lock first and then the shared lock after the update is completed.

[0158] Optionally, the statistics module 904 controls the logic of concurrent access to the shared memory based on the locking mechanism of the database, and further includes: setting the partition lock of each partition of the hash table to an exclusive lock, and deleting redundant entries in the hash table.

[0159] The above parameter value statistics device merges the two types of parameter value information, constants and bound variables, unifies the collection logic of the two types of information, and ensures the consistency of the parameter values ​​of the same SQL statement before and after the update. The parameter information is stored in the shared memory of the database to ensure that the parameter information is synchronized between all sessions, which is convenient for database administrators to tune the entire instance in a certain session. The hash table is automatically cleaned up to avoid the situation where new statistics cannot be inserted after the storage capacity reaches the upper limit. Finally, through data preprocessing, setting frequency control logic, and controlling concurrent access logic based on the locking mechanism, the performance impact caused by frequent operations on the shared memory is reduced.

[0160] Each module in the above parameter value statistics device can be implemented in whole or in part by software, hardware, or a combination thereof. Each module can be embedded in or independent of a processor in a computer device in the form of hardware, or can be stored in a memory in a computer device in the form of software, so that the processor can call and execute operations corresponding to each module.

[0161] The parameter value statistics device provided in this embodiment can execute the method provided in the above method embodiment. Its implementation principle and technical effect are similar, and this embodiment will not be described in detail here.

[0162] Fig.10 The schematic diagram of the computer structure provided in this application. Fig.10 As shown, the computer device 100 provided in this embodiment includes: at least one processor 1001 and a memory 1002. Optionally, the device 70 further includes a communication component 1003. The processor 1001, the memory 1002 and the communication component 1003 are connected via a bus.

[0163] In a specific implementation process, at least one processor 1001 executes the computer-executable instructions stored in the memory 1002, so that at least one processor 1001 executes the above method.

[0164] The specific implementation process of the processor 1001 can be found in the above method embodiment, and its implementation principle and technical effect are similar, so this embodiment will not be repeated here.

[0165] In the above embodiments, it should be understood that the processor may be a central processing unit (CPU), or other general-purpose processors, digital signal processors (DSP), application-specific integrated circuits (ASIC), etc. A general-purpose processor may be a microprocessor or any conventional processor. The steps of the method disclosed in the invention may be directly implemented as being executed by a hardware processor, or may be executed by a combination of hardware and software modules in the processor.

[0166] The memory may include a high-speed memory (Random Access Memory, RAM), and may also include a non-volatile memory (Non-volatile Memory, NVM), such as at least one disk memory.

[0167] The bus may be an Industry Standard Architecture (ISA) bus, a Peripheral Component Interconnect (PCI) bus, or an Extended Industry Standard Architecture (EISA) bus, etc. The bus may be divided into an address bus, a data bus, a control bus, etc. For ease of representation, the bus in the drawings of the present application is not limited to only one bus or one type of bus.

[0168] The present application also provides a computer program product, including a computer program, which implements the above method when executed by a processor.

[0169] The present application also provides a computer-readable storage medium, in which computer-executable instructions are stored. When a processor executes the computer-executable instructions, the above method is implemented.

[0170] The above-mentioned readable storage medium can be implemented by any type of volatile or non-volatile storage device or a combination thereof, such as static random access memory (SRAM), electrically erasable programmable read-only memory (EEPROM), erasable programmable read-only memory (EPROM), programmable read-only memory (PROM), read-only memory (ROM), magnetic memory, flash memory, magnetic disk or optical disk. The readable storage medium can be any available medium that can be accessed by a general or special-purpose computer.

[0171] An exemplary readable storage medium is coupled to a processor so that the processor can read information from the readable storage medium and write information to the readable storage medium. Of course, the readable storage medium can also be a component of the processor. The processor and the readable storage medium can be located in an application specific integrated circuit (Application Specific Integrated Circuits, referred to as: ASIC). Of course, the processor and the readable storage medium can also exist in the device as discrete components.

[0172] The division of units is only a logical function division, and there may be other divisions in actual implementation, such as multiple units or components can be combined or integrated into another system, or some features can be ignored or not executed. Another point is that the mutual coupling or direct coupling or communication connection shown or discussed can be an indirect coupling or communication connection through some interface, device or unit, which can be electrical, mechanical or other forms.

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

[0174] In addition, each functional unit in each embodiment of the present invention may be integrated into one processing unit, or each unit may exist physically separately, or two or more units may be integrated into one unit.

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

[0176] Those skilled in the art can understand that all or part of the steps of implementing the above-mentioned method embodiments can be completed by hardware related to program instructions. The aforementioned program can be stored in a computer-readable storage medium. When the program is executed, the steps of the above-mentioned method embodiments are executed; and the aforementioned storage medium includes: ROM, RAM, disk or optical disk and other media that can store program codes.

[0177] Finally, it should be noted that those skilled in the art will readily conceive of other embodiments of the present invention after considering the specification and practicing the invention disclosed herein. The present invention is intended to cover any variations, uses or adaptations of the present invention, which follow the general principles of the present invention and include common knowledge or customary technical means in the art not disclosed by the present invention, are not limited to the precise structure described above and shown in the drawings, and may be modified and changed in various ways without departing from the scope thereof. The scope of the present invention is limited only by the appended claims.

Claims

1. A parameter value statistical method, characterized in that: The method comprises: Receive SQL statements; Collecting parameter information of the SQL statement before parameterization, wherein the parameter information includes constant information and binding variable information; Storing the parameter information in a shared memory of a database; Based on the locking mechanism of the database, the logic of concurrent access to the shared memory is controlled, wherein the concurrent access is used to count SQL parameter values.

2. The method according to claim 1, characterized in that The collecting parameter information of the SQL statement before parameterization includes: For an SQL statement using an extended message protocol, constant information is extracted from a P message, and binding variable information is obtained by parsing in a B message; wherein the extended message protocol includes a P message and a B message; For an SQL statement using a simple message protocol, constant information is read from an execution plan cached in a Q message; wherein the simple message protocol includes a Q message, and the execution plan is generated by the Q message based on the SQL statement.

3. The method according to claim 1, characterized in that The storing of the parameter information in the shared memory of the database includes: Storing the parameter information in a hash table of a shared memory; Each constant and each binding variable involved in each SQL statement is stored separately as a record.

4. The method according to claim 3, characterized in that The hash table is divided into a preset number of partitions, and each partition corresponds to a partition lock; The concurrent access includes query operations; The database-based locking mechanism controls the logic of concurrent access to the shared memory, including: Setting a partition lock for each partition in the hash table as a shared lock, and traversing and querying the hash table; A set of parameter values ​​corresponding to the current query identifier queried is simultaneously added with a spin lock, and the set of parameter values ​​is read to obtain a query result; wherein the query identifier is generated by the database according to the logical structure of the parameterized SQL statement; After the query is completed, the spin lock of the group and the shared lock of each partition are released in turn.

5. The method according to claim 4, characterized in that The concurrent access also includes insert operations and update operations; The database-based locking mechanism controls the logic of concurrent access to the shared memory, and further includes: For one of the SQL statements, if it is found that the first parameter of the parameter information corresponding to the SQL statement does not exist in the hash table, first release the shared lock of the group corresponding to the parameter information in the hash table, then add an exclusive lock to the group, insert the parameter information into the hash table, and release the exclusive lock after the insertion is completed; Otherwise, find the entry address of the group of parameters to be updated, add a spin lock for the group, update each parameter one by one according to the entry address, and release the spin lock after the update is completed.

6. The method according to claim 4 or 5, characterized in that: The concurrent access also includes a delete operation; The database-based locking mechanism controls the logic of concurrent access to the shared memory, and further includes: Setting the partition lock of each partition of the hash table to an exclusive lock, and deleting redundant entries in the hash table; Release the exclusive lock after the deletion is completed.

7. The method according to claim 5, characterized in that The method further comprises: If an SQL statement has executed an insert operation, and the parameter information corresponding to the SQL statement has been stored in the hash table, the parameter information in the hash table is updated once after the SQL statement is subsequently executed a preset number of times.

8. A parameter value statistics device, characterized in that: include: A receiving module, used for receiving SQL statements; A collection module, used to collect parameter information of the SQL statement before parameterization, wherein the parameter information includes constant information and binding variable information; A storage module, used for storing the parameter information in a shared memory of a database; A statistics module is used to control the logic of concurrent access to the shared memory based on a locking mechanism of the database, wherein the concurrent access is used to count SQL parameter values.

9. A computer device, characterized in that: include: Memory, processor; The memory stores computer-executable instructions; The processor executes the computer-executable instructions stored in the memory, so that the processor performs the method according to any one of claims 1 to 7.

10. A computer-readable storage medium, characterized in that: The computer-readable storage medium stores computer-executable instructions, which are used to implement the method according to any one of claims 1 to 7 when executed by a processor.

Images