A slow SQL root cause positioning method, system, medium and product

Abstract

A method, system, medium, and product for locating the root cause of slow SQL queries are disclosed, relating to the field of data processing. The method includes: acquiring the SQL statement to be executed in the currently executing thread and calculating the target execution time; when the target execution time exceeds a preset slow query threshold, collecting the current thread's call stack and extracting the target business call point and SQL template string corresponding to the SQL statement, performing structure normalization and hash calculation to generate an SQL fingerprint; concatenating the SQL fingerprint with the target business call point to generate a root cause aggregation identifier; accumulating the target execution time into a cumulative time consumption index; at the end of the current statistical period, sorting the root cause aggregation identifiers in descending order according to the cumulative time consumption index to obtain the root cause aggregation identifier sequence for the current statistical period; and outputting a root cause location list based on the root cause aggregation identifier sequence. Implementing the technical solution provided in this application can improve the efficiency of slow SQL root cause location.

Description

Technical Field

[0001] This application relates to the field of data processing, and in particular to a method, system, medium, and product for locating the root cause of slow SQL queries. Background Technology

[0002] In Java business systems, frameworks such as Mybatis are typically used to encapsulate database interactions. A business interface often needs to serialize and execute multiple SQL statements. When the interface response slows down, because the SQL execution details are hidden by the framework, developers find it difficult to directly know the time consumption of each internal SQL statement, resulting in a delay in identifying performance bottlenecks.

[0003] To address the aforementioned issue of difficulty in identifying SQL execution bottlenecks, existing technologies employ a database slow query log solution. This solution sets a time threshold at the database engine layer. When SQL execution exceeds the threshold, the database automatically captures the original text of the SQL, its execution time, and the number of rows scanned, and records this information in a slow query log file for developers to analyze later.

[0004] However, in real-world microservice or complex business scenarios, the same SQL template is often called by multiple different business interfaces or code locations. When the database slow query log outputs a SQL query that exceeds the time limit, because the record only contains database-side execution metadata and completely lacks application-layer trigger path information, developers can only compare all possible mapping statements that might call the SQL query through a global source code search, and then manually deduce the specific trigger location. This broken link between the database execution result and the application-side code requires developers to invest a lot of time in offline reproduction and code comparison to establish the correspondence between the SQL and the business code, significantly increasing the time spent locating performance bottlenecks and reducing diagnostic efficiency. Summary of the Invention

[0005] This application provides a method, system, medium, and product for locating the root cause of slow SQL queries, which addresses the technical problem of improving the efficiency of slow SQL query root cause location.

[0006] In a first aspect, embodiments of this application provide a method for locating the root cause of slow SQL queries, including: By using the statement execution interception point of the object-relational mapping framework, the SQL statement to be executed in the current execution thread is obtained, and the target execution time is calculated based on the start and end times of the SQL statement's actual submission to the database for execution. When the execution time of the target exceeds the preset slow query threshold, the current thread call stack is collected in real time, and the first stack frame that matches the business package name rule is extracted from the current thread call stack as the target business call point corresponding to the SQL statement; Extract the SQL template string corresponding to the SQL statement, and perform structure normalization and hash calculation on the SQL template string to generate the SQL fingerprint of the SQL statement; The SQL fingerprint is concatenated with the target business call point to generate the root cause aggregation identifier of the SQL statement. The execution time of the target is added to the cumulative time metric corresponding to the root cause aggregation identifier in the current statistical period. The root cause aggregation identifier is used to characterize the source of the performance bottleneck of the SQL statement. At the end of the current statistical period, the root cause aggregation identifiers are sorted in descending order according to the cumulative time consumption index to obtain the root cause aggregation identifier sequence for the current statistical period; Based on the root cause aggregation identifier sequence, a root cause localization list is output, which is used to characterize the actual triggering location of the system performance bottleneck.

[0007] Optionally, the first stack frame matching the business package name rule in the current thread's call stack is extracted as the target business call point corresponding to the SQL statement. This includes: obtaining the complete stack frame sequence of the current thread's call stack; using a preset underlying call whitelist, skipping frame reflection stack frames and proxy execution stack frames in the complete stack frame sequence from top to bottom to obtain the skipped intermediate stack frame sequence; determining the first stack frame in the skipped intermediate stack frame sequence that matches the business package name rule as the initial business call point; if the source code line number debugging information of the initial business call point is missing, extracting the method bytecode descriptor and instruction execution offset corresponding to the initial business call point; jointly encoding the class name, method bytecode descriptor, and instruction execution offset corresponding to the initial business call point as the target business call point, used to maintain the uniqueness of the root cause aggregation identifier in the state of missing source code line numbers; if the source code line number debugging information is not missing, the initial business call point is used as the target business call point.

[0008] Optionally, before obtaining the SQL statement to be executed in the current execution thread, the method further includes: intercepting the external business request that triggers the current execution thread at the network request access layer; parsing the request header parameters of the external business request to identify the traffic access layer source of the external business request; matching the baseline interface latency tolerance corresponding to the traffic access layer source from the dynamic configuration center based on the traffic access layer source; calculating the product of the baseline interface latency tolerance and the preset global slow SQL sensitivity coefficient to obtain the baseline slow query threshold for the external business request; calculating the business priority weight corresponding to the external business request, and dynamically scaling the baseline slow query threshold based on the business priority weight to obtain the slow query threshold for the current execution thread, and binding the slow query threshold to the execution context of the current execution thread, wherein the business priority weight is inversely proportional to the slow query threshold.

[0009] Optionally, based on the root cause aggregation identifier sequence, a root cause location list is output, including: at the end of the current statistical period, a preset number of root cause aggregation identifiers are selected from the root cause aggregation identifier sequence according to the cumulative time consumption index from high to low to form a root cause cluster candidate set; the root cause aggregation identifiers in the root cause cluster candidate set are marked as root cause candidate identifiers; the SQL fingerprint, target business call point, and corresponding cumulative time consumption index, maximum time consumption index, and occurrence frequency index of the root cause candidate identifier are assembled into a root cause record, and the root cause record also includes the percentage of the cumulative time consumption of the root cause candidate identifier in the current statistical period to the total cumulative time consumption of the root cause cluster candidate set; all root cause records are arranged according to the cumulative time consumption index from large to small to generate a root cause location list.

[0010] Optionally, after outputting the root cause localization list, the method further includes: calculating the dispersion of different SQL fingerprints triggered by the same target business call point within the current statistical period, and the frequency density of triggering the same SQL fingerprint; when the frequency density exceeds a preset frequency threshold, and the dispersion is lower than a preset dispersion threshold, and the execution time of a single SQL statement does not reach the slow query threshold, the total loss time generated by the target business call point is accumulated; if the total loss time exceeds a preset logical redundancy threshold, the target business call point is marked as a query redundancy root cause, and the query redundancy root cause is inserted after the TopN sequence of the root cause localization list. The query redundancy root cause is used to characterize the root cause that is not a slow query in a single execution, but is triggered frequently in the loop body, causing the system performance to degrade.

[0011] Optionally, after outputting the root cause localization list, the method further includes: extracting the transaction state object associated with the currently executing thread from the transaction manager of the object-relational mapping framework; if the transaction state object is not empty, obtaining the start timestamp of the current transaction and calculating the transaction offset duration between the actual commit time of the SQL statement and the start timestamp; obtaining the database connection object held by the current transaction and obtaining the count of uncommitted existing change operations in the database connection object; performing normalized weighted calculation on the transaction offset duration and the count of existing change operations to obtain the transaction coupling impact factor of the SQL statement; if the transaction coupling impact factor exceeds the preset weight, adding a transaction risk flag to the root cause aggregation identifier, the added transaction risk flag is used to characterize that the increase in the execution time of the SQL statement is caused by a non-statement-specific root cause induced by long transaction lock waiting or connection pool queuing.

[0012] Optionally, the method further includes: determining whether the target execution time reaches a preset severe threshold for deep diagnosis, where the severe threshold for deep diagnosis is greater than the slow query threshold; if the target execution time reaches the severe threshold for deep diagnosis, extracting the list of dynamic parameter data actually bound to the SQL statement in the current execution thread at the statement execution interception point; traversing the list of dynamic parameter data, filtering parameter fields carrying preset privacy-de-identifying annotations to obtain a filtered list of dynamic parameter data; calculating parameter character length feature values ​​and set element size feature values ​​based on the data types of parameter fields in the filtered list of dynamic parameter data; mapping the parameter character length feature values ​​and set element size feature values ​​to a preset capacity ladder dictionary, concatenating them into a parameter morphology feature string, which is used to characterize the parameter data volume; appending the parameter morphology feature string to the end of the root cause aggregation identifier to generate a derived sub-identifier, used to independently statistically analyze system performance bottlenecks induced by specific parameter data volumes.

[0013] Secondly, embodiments of this application provide a slow SQL root cause localization system, which includes: one or more processors and a memory; the memory is coupled to one or more processors, the memory is used to store computer program code, the computer program code includes computer instructions, and the one or more processors call the computer instructions to cause the slow SQL root cause localization system to perform the method described in the first aspect and any possible implementation thereof.

[0014] Thirdly, embodiments of this application provide a computer program product containing instructions that, when the computer program product is run on a slow SQL root cause localization system, cause the slow SQL root cause localization system to perform the method described in the first aspect and any possible implementation thereof.

[0015] Fourthly, embodiments of this application provide a computer-readable storage medium including instructions that, when executed on a slow SQL root cause localization system, cause the slow SQL root cause localization system to perform the method described in the first aspect and any possible implementation thereof.

[0016] In summary, one or more technical solutions provided in this application have at least the following technical effects or advantages: 1. By calculating the actual execution time of SQL at the framework interception point, when the threshold is exceeded, the call stack is collected and the stack frame matching the business package name is extracted as the business call point. At the same time, an SQL fingerprint is generated. The fingerprint and the call point are concatenated into a root cause aggregation identifier and the time consumption index is accumulated. At the end of the statistical period, the root cause location list is output in descending order of cumulative time consumption. This improves the slow SQL location from the database layer to the business code line level, allowing developers to directly know the trigger location, significantly reducing the investigation path and diagnosis time, and improving the efficiency of slow SQL root cause location.

[0017] 2. Improve the robustness of online environment localization by using the combined encoding class name, bytecode descriptor and offset as the call point when the line number is missing; avoid false alarms or missed alarms caused by fixed thresholds by dynamically calculating the thread-level slow query threshold by identifying the traffic source; and improve the accuracy and actionability of monitoring by assembling SQL fingerprints, call points, time consumption indicators and contribution ratios into a priority-sorted root cause list, enabling the team to prioritize the handling of serious root causes according to the repair return rate.

[0018] 3. By statistically analyzing the dispersion of different SQL fingerprints triggered by the same call point and the frequency density of the same fingerprint, the root causes of query redundancy triggered frequently within loops are identified, making up for the blind spots of single long-duration monitoring; by extracting the offset duration within a transaction and the number of uncommitted changes to calculate the transaction coupling impact factor, the non-self-root causes induced by long transactions are distinguished; by extracting parameter volume characteristics during deep diagnosis to generate derived sub-identifiers, conditional slow SQL induced by parameter size is independently statistically analyzed, enhancing the ability to capture hidden bottlenecks from three dimensions: cumulative redundancy, transaction interference, and parameter sensitivity, and improving the completeness and depth of root cause localization. Attached Figure Description

[0019] Figure 1 This is a flowchart illustrating the slow SQL root cause localization method provided in an embodiment of this application; Figure 2 This is a flowchart illustrating the process of determining the slow query threshold provided in an embodiment of this application; Figure 3 This is a schematic diagram of the structure of a slow SQL root cause localization system provided in an embodiment of this application.

[0020] Explanation of reference numerals in the attached figures: 601, Central Processing Unit; 602, Read-Only Memory; 603, Random Access Memory; 604, Bus; 605, Input / Output Interface; 606, Input Section; 607, Output Section; 608, Storage Section; 609, Communication Section; 610, Driver; 611, Removable Media. Detailed Implementation

[0021] To enable those skilled in the art to better understand the technical solutions in this specification, the technical solutions in the embodiments of this specification will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of this application, and not all embodiments.

[0022] In the description of the embodiments of this application, the words "for example" or "for instance" are used to indicate examples, illustrations, or explanations. Any embodiment or design that is described as "for example" or "for instance" in the embodiments of this application should not be construed as being more preferred or advantageous than other embodiments or design options. Rather, the use of the words "for example" or "for instance" is intended to present the relevant concepts in a specific manner.

[0023] In the description of the embodiments of this application, the term "multiple" means two or more. For example, multiple systems means two or more systems, and multiple screen terminals means two or more screen terminals. Furthermore, the terms "first" and "second" are used for descriptive purposes only and should not be construed as indicating or implying relative importance or implicitly specifying the indicated technical features. Thus, a feature defined with "first" or "second" may explicitly or implicitly include one or more of that feature. The terms "comprising," "including," "having," and variations thereof all mean "including but not limited to," unless otherwise specifically emphasized.

[0024] This application provides a method, system, medium, and product for locating the root cause of slow SQL queries, which addresses the technical problem of improving the efficiency of slow SQL query root cause location.

[0025] Figure 1 This is a flowchart illustrating the slow SQL root cause localization method provided in this application embodiment.

[0026] This application discloses a method for locating the root cause of slow SQL queries, such as... Figure 1 As shown, the steps include the following.

[0027] S101. By using the statement execution interception point of the object-relational mapping framework, obtain the SQL statement to be executed in the current execution thread, and calculate the target execution time based on the start and end times of the SQL statement's actual submission to the database for execution.

[0028] Specifically, a custom interceptor, such as a MyBatis interceptor, is registered with the object-relational mapping framework when the application service starts. This interceptor weaves timing logic into the point where the framework executes the SQL statement. When the business thread enters the interception point, it first obtains the encapsulated SQL statement object to be executed from the current execution thread, and simultaneously records the start timestamp of the SQL statement before it is actually submitted to the database driver using a high-precision clock. Then, it allows the execution of the original SQL logic, and records the end timestamp again after the database returns the result. Subtracting the start timestamp from the end timestamp yields the complete time elapsed from when the SQL statement is initiated by the application side to when a response is received, which is used as the target execution time. This time includes the entire chain time of network transmission, database connection acquisition, SQL parsing optimization, and actual execution.

[0029] Among them, the object-relational mapping framework refers to the technical components used to map relational database tables to in-memory objects, including MyBatis and Hibernate; the statement execution interception point refers to the extended interface reserved by the framework for intercepting the execution process of SQL statements, and the corresponding plugin for intercepting the interface in MyBatis; the current execution thread refers to the business processing thread that triggered this SQL call, which is allocated by the application server from the thread pool; the SQL statement to be executed refers to the structured query string generated by the framework after parsing the Mapper interface method and is about to be submitted to the database for execution; the start time and end time refer to two time points collected by a high-precision timer before and after the SQL execution, with time unit precision reaching the millisecond or microsecond level; the target execution time refers to the difference between the start time and the end time, used to quantify the performance of this SQL call.

[0030] Figure 2 This is a flowchart illustrating the process of determining the slow query threshold provided in an embodiment of this application.

[0031] Based on the above embodiments, as an optional embodiment, see [link to embodiment]. Figure 2 ,exist Figure 1 Before step S101 shown, it may also include Figure 2 The steps S201-S205 are implemented, and will be explained in detail below.

[0032] S201. Intercept external business requests that trigger the currently executing thread at the network request access layer.

[0033] Specifically, in the network request access layer of the application service (such as filters, interceptors, or gateway components), all Hypertext Transfer Protocol (HTTP) requests entering the system are uniformly intercepted and processed. When a request initiated by an external client reaches the access layer, the protocol metadata of the request is first parsed, and the identification fields in the request header (such as the caller's application name, interface version number, tenant information, etc.) are extracted to identify the source of the request. At the same time, the business semantic tags (such as interface path, business type in method annotations) of the target processing method corresponding to the request are obtained. After determining the traffic access layer source of the request through the above information, the request is bound to the currently executing business thread, establishing a mapping relationship from the external request to the internal thread execution context, so that the current thread can trace back to the original request that triggered this business call when executing subsequent database queries.

[0034] Among them, the network request access layer represents the request interception component deployed at the forefront of the application service; the external business request represents the Hypertext Transfer Protocol call initiated by the client to trigger a specific business function, which includes the request method, the Uniform Resource Locator path, the request header and the request body.

[0035] S202. Parse the request header parameters of external business requests to identify the traffic access layer source of external business requests.

[0036] Specifically, a set of request header parameters is extracted from the intercepted external business requests. This set contains metadata fields in key-value pair format. The request header parameters are traversed, and feature fields used to identify the request source are matched according to preset field mapping rules. These feature fields include at least one of the following: caller application name field, client type field, business line identifier field, and tenant number field. The values ​​of the matched feature fields are combined or individually selected to generate a traffic access layer source identifier. This identifier is used to distinguish which upstream system, client type, business line, or tenant initiated the current request, thereby providing a basis for subsequent differentiated configuration of performance monitoring parameters for different sources.

[0037] The request header parameters represent the set of metadata key-value pairs carried in the Hypertext Transfer Protocol request, located after the request start line and before the message body; the traffic access layer source represents the tag information used to uniquely identify the source channel or caller identity of the external business request, including at least one of the caller application name, client type, business line identifier, and tenant number.

[0038] S203. Based on the traffic access layer source, match the baseline interface latency tolerance corresponding to the traffic access layer source from the dynamic configuration center.

[0039] Specifically, the identified traffic access layer source is used as the query key to initiate a remote configuration retrieval request to the dynamic configuration center. The dynamic configuration center pre-stores baseline interface latency tolerance configuration items corresponding to different sources according to the dimension of traffic access layer source. This tolerance represents the upper limit of the acceptable normal response time of the business interface under this source. The configuration center performs exact matching or prefix matching based on the query key and returns the baseline interface latency tolerance value associated with the traffic access layer source. If the matching fails, it falls back to the global default tolerance. This global default tolerance is a pre-set fallback value used to cover unconfigured or newly added traffic sources. The obtained baseline interface latency tolerance will be used as the baseline parameter for subsequent calculation of the slow query threshold.

[0040] Among them, the dynamic configuration center refers to the distributed components used for centralized management and dynamic distribution of configuration parameters, including Apollo, Nacos, and Consul; the baseline interface latency tolerance refers to the upper limit of the expected interface response time preset for a specific traffic access layer source, in milliseconds, used to characterize the sensitivity of the service under that source to response latency.

[0041] S204. Calculate the product of the baseline interface latency tolerance and the preset global slow SQL sensitivity coefficient to obtain the baseline slow query threshold for external business requests.

[0042] Specifically, the baseline interface latency tolerance obtained from the dynamic configuration center is used as the multiplicand, and a preset global slow SQL sensitivity coefficient is used as the multiplier. A multiplication operation is performed, and the product of the two is used as the baseline slow query threshold for the current external business request. The global slow SQL sensitivity coefficient is a floating-point number between 0 and 1, pre-set according to the overall performance requirements of the system, and is used to control the strictness of the slow query threshold relative to the interface latency tolerance. The smaller the coefficient, the lower the generated baseline slow query threshold, and the stricter the requirements for SQL execution time; the larger the coefficient, the higher the baseline slow query threshold, and the greater the tolerance for SQL execution time. The result of the multiplication operation is the baseline slow query threshold, with units consistent with the baseline interface latency tolerance, used for subsequent comparison with the actual SQL execution time to determine whether it is a slow query.

[0043] Among them, the global slow SQL sensitivity coefficient represents a pre-set system-level adjustment factor with a value range between 0 and 1, which is used to uniformly control the scaling ratio of the slow query threshold relative to the interface latency tolerance; the baseline slow query threshold represents the intermediate value obtained by multiplication operation, which is used as the initial reference limit for judging whether SQL execution is slow, and the unit is milliseconds.

[0044] Through the above embodiments, the baseline interface latency tolerance is multiplied by the global slow SQL sensitivity coefficient to obtain the baseline slow query threshold that is linked to the business response expectation. This allows the slow query judgment standard to adapt to the performance requirements of the business interface, avoiding the defect that a fixed threshold cannot be adapted to different business scenarios.

[0045] S205. Calculate the business priority weight corresponding to the external business request, and dynamically scale the baseline slow query threshold based on the business priority weight to obtain the slow query threshold for the current execution thread. Then bind the slow query threshold to the execution context of the current execution thread. The business priority weight is inversely proportional to the slow query threshold.

[0046] Specifically, the business priority weight corresponding to the external business request is obtained. This weight is pre-configured based on the importance of the target interface, service level protocol level, or user level. A higher value indicates a higher business priority. The calculated baseline slow query threshold is used as the scaling value. A scaling factor is calculated based on the business priority weight, which is inversely proportional to the business priority weight: the higher the priority weight, the smaller the scaling factor, resulting in stricter slow query judgment criteria for high-priority businesses; the lower the priority weight, the larger the scaling factor, resulting in relatively lenient judgment criteria for low-priority businesses. The baseline slow query threshold is multiplied by the scaling factor to obtain the final slow query threshold for the current execution thread. This slow query threshold is bound to the execution context of the current execution thread as a thread-local variable, so that all subsequent SQL statements executed by the current thread use this threshold for slow query judgment. The bound value is automatically released after the thread finishes execution.

[0047] Among them, the business priority weight represents a numerical weight pre-assigned according to the importance of the business, which is used to characterize the processing priority of the current request compared with other requests. The value range is positive integer, and the larger the value, the higher the priority. The execution context represents the storage space bound to the current execution thread, which is used to save and pass context variables during thread execution.

[0048] Through the above embodiments, the baseline slow query threshold is dynamically scaled according to the business priority weight, so that high-priority businesses are subject to stricter slow query judgment criteria, while low-priority businesses are subject to relatively lenient criteria. This achieves differentiated adaptation of the slow query threshold in the business priority dimension and avoids the problem that the critical interface response requirements of high-priority businesses cannot be guaranteed under a unified threshold.

[0049] S102. When the execution time of the target exceeds the preset slow query threshold, the current thread call stack is collected in real time, and the first stack frame that matches the business package name rule is extracted from the current thread call stack as the target business call point corresponding to the SQL statement.

[0050] Specifically, the calculated target execution time is compared with the slow query threshold bound to the current execution thread. When the target execution time exceeds the slow query threshold, the call stack collection process is triggered. The current call stack snapshot is obtained through the current execution thread object. This call stack snapshot is returned as a stack frame array. The first element of the array is located at the top of the stack, representing the currently executing method. Subsequent elements represent the upper-level methods in the call chain. The stack frame array is traversed from top to bottom starting from the top of the stack. For each stack frame, its corresponding fully qualified class name is obtained. This class name is matched with the preset business package name rules. The business package name rules are a whitelist of one or more package name prefixes used to distinguish business code from framework code. All stack frames whose class names do not match the business package name rules are skipped. When the first stack frame whose class name matches the business package name rules is encountered, the traversal stops and the stack frame is taken as the target business call point. The class name, method name, file name, and source code line number information are extracted from the stack frame. If the line number information is unavailable, the bytecode descriptor and instruction offset are used as substitute identifiers to form a complete description of the target business call point.

[0051] Among them, the current thread call stack represents a snapshot of the method call chain in the currently executing thread, stored in the form of a stack frame array, with the currently executing method at the top of the stack; the business package name rule represents a pre-configured set of package name prefixes used to identify business code, such as "com.company.business"; the target business call point represents the location of the business code that triggers SQL execution, including at least one of the following identification information: class name, method name, file name, and line number.

[0052] Based on the above embodiments, as an optional embodiment, for Figure 1 The step S102 shown can be implemented through steps S1021-S1026, which will be explained in detail below.

[0053] S1021. Obtain the complete stack frame sequence of the current thread's call stack.

[0054] Specifically, the method to obtain the call stack is called through the currently executing thread object. This method returns a snapshot of the current thread's call stack, organized as an array of stack frames. The first index of the array corresponds to the top element of the stack, representing the currently executing method frame. Subsequent indices represent the upper-level method frames in the call chain, and the last index corresponds to the bottom element of the stack, typically representing the thread's entry method or main method. Each stack frame object encapsulates the metadata of the method call, including the fully qualified class name, method name, source code file name, and the source code line number corresponding to the current execution position. If the runtime environment retains debugging information and the class file contains a line number table, the line number information is available; otherwise, it is unavailable. The obtained complete stack frame sequence will serve as the basic data source for subsequent traversal and filtering.

[0055] S1022. Using a preset underlying call whitelist, skip the frame reflection stack frame and proxy execution stack frame in the complete stack frame sequence from top to bottom to obtain the skipped intermediate stack frame sequence.

[0056] Specifically, after obtaining the complete stack frame sequence of the current thread's call stack, each stack frame is traversed from top to bottom, starting from the top of the stack. The fully qualified class name of each stack frame is extracted and matched against a pre-configured underlying call whitelist. The underlying call whitelist is a pre-configured set of class names or package name prefixes that includes identification patterns for non-business code such as framework inner classes, reflection call classes, proxy generation classes, and infrastructure classes. Examples include Spring framework's AOP proxy classes, MyBatis framework's Mapper proxy classes, and JDK dynamic proxy classes. If the class name of the current stack frame matches any item in the whitelist, the stack frame is skipped, and the traversal continues to the next stack frame. If the class name of the current stack frame does not match any item in the whitelist, the skipping operation stops, and all stack frames from the current position onwards are used as the skipped intermediate stack frame sequence. This intermediate stack frame sequence has filtered out interfering stack frames from the underlying framework and proxy execution, retaining only the valid stack frames from the business code to the framework call boundary.

[0057] Among them, the framework reflection stack frame represents the stack frame generated when a method is called through the reflection mechanism inside the framework, and its class name usually contains reflection-related characteristic strings; the proxy execution stack frame represents the stack frame generated when the proxy class generated by dynamic proxy or bytecode enhancement executes the target method, and its class name usually contains the characteristic suffix of the proxy class generator; the intermediate stack frame sequence represents the stack frame sequence remaining after filtering out whitelist matching stack frames from the complete stack frame sequence.

[0058] Through the above embodiments, the underlying call whitelist is used to filter out the framework reflection stack frames and proxy execution stack frames. The resulting intermediate stack frame sequence eliminates interference from non-business code, providing a clean stack frame data source for subsequent accurate extraction of business call points.

[0059] S1023. Determine the first stack frame in the skipped intermediate stack frame sequence that matches the service package name rule as the initial service call point.

[0060] Specifically, starting from the first stack frame in the skipped intermediate stack frame sequence, each stack frame is traversed from top to bottom. The fully qualified class name of each stack frame is extracted and matched against a preset business package name rule. The business package name rule is a whitelist of one or more package name prefixes used to identify the scope of the business code, such as "com.company.business" or "cn.example.service". If the class name of the current stack frame begins with any prefix in the business package name rule, the stack frame is considered to match the rule, traversal stops, and the stack frame is designated as the initial business call point. If the class name of the current stack frame does not match the rule, traversal continues to the next stack frame until the first matching stack frame is found. This initial business call point represents the upper-level business code location located in the call stack that is closest to SQL execution.

[0061] The initial business call point refers to the first stack frame extracted from the intermediate stack frame sequence that conforms to the business package name rule, and is used to characterize the location of the business code that triggers SQL execution.

[0062] S1024. If the source code line number debugging information of the initial business call point is missing, extract the method bytecode descriptor and instruction execution offset corresponding to the initial business call point.

[0063] Specifically, after obtaining the initial business call point stack frame, an attempt is made to read the source code line number information from that stack frame. If the read line number value is -1 or less than 0, it is determined that the source code line number debugging information is missing. In this case, it is impossible to accurately locate the specific line of the source code using the line number. Instead, the bytecode descriptor corresponding to the method and the instruction offset of the current execution position in the method bytecode are obtained through the method metadata stored in the stack frame. The bytecode descriptor contains the parameter type signature and return value type signature of the method, which is used to uniquely identify the overloaded version of the method; the instruction offset represents the number of bytes offset of the currently executing bytecode instruction relative to the method's start position. The obtained method bytecode descriptor and instruction execution offset are combined as alternative location information for the source code line number.

[0064] Among them, the source code line number debugging information represents the source code line number mapping table stored in the bytecode file during compilation, which is used to map the runtime execution position back to the specific line number of the source code. A line number value of -1 or a negative number indicates that debugging information is missing; the method bytecode descriptor represents the method signature description string defined in the Java Virtual Machine Specification, which includes field descriptors for parameter types and field descriptors for return type. For example, "(Ljava / lang / String;)I" means that it accepts a string parameter and returns an integer; the instruction execution offset represents the index position of the current bytecode instruction in the method bytecode array, which is used to approximate the execution position when the line number cannot be obtained.

[0065] Through the above embodiments, in an environment where source code line number debugging information is missing, the method bytecode descriptor and instruction execution offset are extracted as alternative location information, making the slow SQL root cause location capability independent of compilation configuration, thus improving the applicability and robustness of the solution in production environments (where debugging information is usually turned off).

[0066] S1025. The class name, method bytecode descriptor and instruction execution offset corresponding to the initial business call point are jointly encoded as the target business call point, which is used to maintain the uniqueness of the root cause aggregation identifier in the state of source code line number loss.

[0067] Specifically, when the source code line number debugging information for the initial business call point is missing, the three elements corresponding to the stack frame—the fully qualified class name, the method bytecode descriptor, and the instruction execution offset—are concatenated or hashed in a preset order to generate a combined encoded string as the target business call point. The fully qualified class name provides information about the class to which the method belongs, the method bytecode descriptor uniquely identifies the overloaded version of the method, and the instruction execution offset identifies the specific execution location within the method body. This combination of elements can distinguish SQL calls at different locations within a method even when the source code line number is missing, because within the same overloaded version of the same method, different SQL calls correspond to different instruction offsets. This combined encoded string, as part of the root cause aggregation identifier, is used to distinguish SQL calls at different trigger locations within the same class and the same method during aggregation statistics.

[0068] Through the above embodiments, the class name, method bytecode descriptor, and instruction execution offset are jointly encoded to generate the target business call point. Even in a production environment where the source code line number is lost, the uniqueness of the root cause aggregation identifier can still be maintained. This allows SQL calls at different locations within the same method to be counted and sorted independently, avoiding the problem of different triggering locations being incorrectly merged into the same root cause due to missing line numbers.

[0069] S1026. If the source code line number debugging information is not missing, then the initial business call point will be used as the target business call point.

[0070] Specifically, after obtaining the initial business call point stack frame, an attempt is made to read the source code line number information from the stack frame. If the read line number is a positive integer (usually greater than 0), it is determined that the source code line number debugging information is not missing. At this point, no additional encoding conversion is needed; the class name, method name, file name, and source code line number information contained in the initial business call point are directly combined and output as the target business call point. This target business call point maintains the precision of the source code line number dimension, and can directly locate the specific file line number where the business code that triggered SQL execution is located.

[0071] S103. Extract the SQL template string corresponding to the SQL statement, and perform structure normalization and hash calculation on the SQL template string to generate the SQL fingerprint of the SQL statement.

[0072] Specifically, the SQL statement object to be executed is obtained from the SQL execution interception point context. The SQL template string without specific parameter values ​​is extracted through the interface methods provided by the object. The variable values ​​in the template string have been replaced by placeholders. After obtaining the SQL template string, it is subjected to structural normalization processing, including reducing consecutive whitespace characters in the string to a single space, converting SQL keywords to uppercase or lowercase, and replacing string literals and numeric literals with standard placeholders. After normalization, a hash algorithm is applied to the processed string to calculate its hash value. The hash value is output as the SQL fingerprint. The SQL fingerprint is used to group SQL statements with the same structure but different parameters into the same category in the subsequent aggregation process.

[0073] Among them, the SQL template string represents the form of an SQL statement that does not contain specific parameter values, and the parameter positions are usually identified by question mark placeholders; the SQL fingerprint represents a hash value or short string used to uniquely identify a type of SQL structural feature, and is used for clustering statistics of SQL statements.

[0074] For example, in the order query method of an e-commerce system, the original SQL statement generated by the MyBatis framework is "SELECT * FROM orders WHERE user_id = 123 AND status = 'PAID'". Extracting the SQL template string from the SQL statement object yields "SELECT * FROM orders WHERE user_id = ? AND status =?". This template string is then normalized: whitespace is compressed to obtain "SELECT FROM orders WHERE user_id=? AND status=?"; keywords are unified to uppercase to obtain "SELECT FROM orders WHERE user_id=? AND status=?"; literals are already placeholders and do not require conversion. A hash algorithm is applied to the normalized string to calculate a 64-bit hexadecimal hash value "49F664B430298753", which serves as the SQL fingerprint of the SQL statement.

[0075] S104. Concatenate the SQL fingerprint with the target business call point to generate the root cause aggregation identifier of the SQL statement, and add the target execution time to the cumulative time metric corresponding to the root cause aggregation identifier in the current statistical period. The root cause aggregation identifier is used to characterize the source of the performance bottleneck of the SQL statement.

[0076] Specifically, after obtaining the SQL fingerprint string and the target business call point string, they are concatenated into a new string according to a preset concatenation order, serving as the root cause aggregation identifier. The concatenation order can be SQL fingerprint first, target business call point second, or vice versa, with separators inserted in between for easier parsing. This root cause aggregation identifier is used to uniquely identify a class of SQL execution events with the same SQL structure and the same business trigger location. The system searches for the corresponding statistical entry in the aggregation dictionary maintained in memory for the current statistical period. If an existing statistical entry is found, the target execution time of this SQL execution is added to the cumulative time metric of that entry, the occurrence count is incremented by one, and the maximum time metric is updated to the larger of the current time and the historical maximum time. If no corresponding statistical entry is found, a new statistical entry is created in the aggregation dictionary, with the target execution time of this execution as the initial cumulative time, the occurrence count set to 1, and the maximum time set to this time. The root cause aggregation identifier serves as the key in the aggregation dictionary, merging scattered SQL execution events according to the source of performance bottlenecks, providing a data foundation for subsequently outputting the root cause location list sorted by cumulative execution time.

[0077] Among them, the root cause aggregation identifier represents a unique key value generated by combining the SQL fingerprint and the target business call point, which is used to group execution events with the same SQL structure and the same call location into the same performance bottleneck source; the cumulative time consumption indicator represents the sum of the target execution time of all SQL execution events under the same root cause aggregation identifier.

[0078] S105. At the end of the current statistical period, arrange the root cause aggregation identifiers in descending order according to the cumulative time consumption index to obtain the root cause aggregation identifier sequence for the current statistical period.

[0079] Specifically, at the end of the current statistical period's time window, all root cause aggregation identifiers and their corresponding cumulative time consumption indicators in the aggregation dictionary are obtained. Each key-value pair is used as a sorting element, and the sorting elements are arranged in descending order according to the value of the cumulative time consumption indicator. The root cause aggregation identifier with the larger the cumulative time consumption is, the higher the ranking. After sorting, the sorted root cause aggregation identifier sequence is extracted. The root cause aggregation identifiers in this sequence are arranged in descending order according to their impact on the overall system performance. The root cause aggregation identifier with the largest cumulative time consumption is the source of SQL execution that has the most serious negative impact on system performance in the current statistical period.

[0080] Among them, the root cause aggregation identifier sequence represents an ordered list of root cause aggregation identifiers obtained after sorting, and the position order in the sequence reflects the severity of the performance impact of each root cause.

[0081] Through the above embodiments, at the end of the statistical period, the root cause aggregation identifiers are arranged in descending order of cumulative time consumption, and the root causes with the most serious performance impact are placed at the top of the sequence. This provides a basis for prioritizing the output of the root cause localization list, enabling developers to focus on and deal with the performance bottlenecks that contribute the most.

[0082] S106. Based on the root cause aggregation identifier sequence, output the root cause location list. The root cause location list is used to characterize the actual triggering location of the system performance bottleneck.

[0083] Specifically, the root cause aggregation identifiers in the root cause aggregation identifier sequence are sorted in descending order of cumulative execution time. Each root cause aggregation identifier in the sequence is traversed and parsed to reconstruct two components: an SQL fingerprint and a target business call point. Based on the SQL fingerprint, the corresponding original SQL template string is retrieved from the SQL template cache to display the specific SQL statement structure. Based on the target business call point, the location information of the business code that triggered the SQL execution is obtained, including the class name, method name, file name, and source code line number or bytecode location information. Simultaneously, the cumulative execution time, maximum execution time, and occurrence frequency corresponding to the root cause aggregation identifier are read from the aggregation dictionary. This information is assembled into a root cause record, containing the SQL template, business call point, cumulative execution time, maximum execution time, occurrence frequency, and the proportion of cumulative execution time to the total cumulative execution time of all root causes. All root cause records are arranged in order in the sequence to generate a root cause location list. This list is output via log output, Hypertext Transfer Protocol interface return, or message queue push for consumption by developers or operations systems. Each item in the list directly points to a modifiable code location and its quantified performance impact data.

[0084] The root cause analysis list is a readable list containing information such as SQL templates, business call points, and performance metrics, used to show developers the root cause analysis results of performance bottlenecks.

[0085] Through the above embodiments, the sorted root cause aggregation identifier sequence is converted into a readable root cause location list containing SQL templates, business call points, and performance indicators. This allows developers to directly know the code location that most needs optimization and its quantitative impact without additional analysis, significantly shortening the time path from discovering performance issues to locating and fixing targets.

[0086] Based on the above embodiments, as an optional embodiment, for Figure 1 The step S106 shown can be implemented through steps S1061-S1063, which will be explained in detail below.

[0087] S1061. At the end of the current statistical period, a preset number of root cause aggregation identifiers are selected from the root cause aggregation identifier sequence according to the cumulative time consumption index from high to low and extracted as root cause cluster candidate set. The root cause aggregation identifiers in the root cause cluster candidate set are marked as root cause candidate identifiers.

[0088] Specifically, at the end of the current statistical period's time window, the root cause aggregation identifier sequence, sorted in descending order of cumulative time consumption, is obtained. Based on a preset TopN value N, the first N root cause aggregation identifiers are selected from the beginning of the sequence and extracted to form a subset, which is the root cause cluster candidate set. Each root cause aggregation identifier in the root cause cluster candidate set is marked as a root cause candidate identifier. The remaining root cause aggregation identifiers that do not rank in the top N are not included in the candidate set during this period and do not participate in subsequent detailed output processing. This selection operation controls the amount of data output in each period by limiting the number of outputs, avoiding overload of output information due to an excessive number of root causes, while ensuring that the N root causes with the greatest performance impact receive priority attention.

[0089] Among them, the preset quantity represents the pre-configured TopN value, which is used to control the upper limit of the number of root causes output in each statistical period; the root cause cluster candidate set represents the subset composed of the top N root cause aggregation identifiers extracted from the sequence; the root cause candidate identifier represents a single root cause aggregation identifier in the root cause cluster candidate set, which is marked as a candidate object that needs to be output in detail.

[0090] S1062. Assemble the SQL fingerprint of the root cause candidate identifier, the target business call point, and the corresponding cumulative time consumption indicator, maximum time consumption indicator, and occurrence frequency indicator into a root cause record. The root cause record also includes the percentage of the cumulative time consumption of the root cause candidate identifier in the current statistical period to the total cumulative time consumption of the root cause cluster candidate set.

[0091] Specifically, each root cause candidate identifier in the root cause cluster candidate set is traversed, and data assembly is performed for each identifier. The cumulative time consumption metric, maximum time consumption metric, and occurrence frequency metric are read from the aggregate dictionary entry corresponding to the identifier. Based on the SQL fingerprint portion of the identifier, the corresponding original SQL template string is retrieved from the SQL template cache. Based on the target business call point portion of the identifier, the business code location information that triggers SQL execution is obtained. This information is used as field values, and the percentage of the cumulative time consumption of this identifier relative to the total cumulative time consumption of the root cause cluster candidate set is calculated using the formula: the cumulative time consumption of this identifier divided by the sum of the cumulative time consumption of all identifiers in the root cause cluster candidate set, then multiplied by 100%. The six fields—SQL fingerprint or SQL template string, target business call point, cumulative time consumption, maximum time consumption, occurrence frequency, and percentage—are assembled into a structured root cause record. Each root cause record fully describes the SQL characteristics, triggering location, and quantified impact of the performance bottleneck represented by a root cause candidate identifier.

[0092] S1063. Arrange all root cause records in descending order of cumulative time consumption to generate a root cause localization list.

[0093] Specifically, all root cause records generated within the current statistical period are retrieved, each containing a cumulative time consumption index field. The root cause records are sorted in descending order using the cumulative time consumption index as the sorting key, with the record having the highest cumulative time consumption value at the beginning and the record having the lowest cumulative time consumption value at the end. During the sorting process, if two or more root cause records have the same cumulative time consumption index, a secondary sort can be performed using the maximum time consumption index, the frequency of occurrence index, or the maximum single-occurrence time consumption as a secondary sorting key to determine the order. After sorting, the sorted root cause records are placed into an ordered list, which is the root cause localization list. The order of records in the root cause localization list directly reflects the severity and priority of each performance bottleneck; root causes appearing earlier in the list have a greater negative impact on the overall system performance.

[0094] Based on the above embodiments, as an optional embodiment, for Figure 1 The slow SQL root cause localization method shown may also include steps S301-S303, which are explained in detail below.

[0095] S301. Calculate the dispersion of different SQL fingerprints triggered by the same target business call point within the current statistical period, as well as the frequency density of triggering the same SQL fingerprint.

[0096] Specifically, at the end of the current statistical period, all root cause aggregation identifiers in the aggregation dictionary are obtained. The root cause aggregation identifiers are grouped according to the dimension of the target business call point. All root cause aggregation identifiers with the same target business call point are grouped into the same group. For each group, the number of different SQL fingerprints contained in the group is counted, and this number is used as a dispersion index. The higher the dispersion, the more diverse the types of SQL triggered by the business call point. At the same time, the occurrence frequency of each SQL fingerprint in the group is counted, and the proportion of the occurrence frequency of each SQL fingerprint to the total occurrence frequency of the group is calculated. This proportion is used as a frequency density index. The higher the frequency density, the higher the frequency of the same SQL fingerprint being executed repeatedly by the business call point.

[0097] Among them, dispersion represents the number of different SQL fingerprints triggered by the same business call point, which is used to measure the diversity of SQL statements executed at the call point; frequency density represents the proportion of the occurrence of the same SQL fingerprint at the same business call point to the total occurrence of the call point, which is used to measure the concentration of repeated execution of the same SQL at the call point.

[0098] S302. When the frequency density exceeds the preset frequency threshold, the dispersion is lower than the preset dispersion threshold, and the execution time of a single SQL statement does not reach the slow query threshold, the total time lost by the target business call point is calculated by summing the results.

[0099] Specifically, the frequency density and dispersion of the same target business call point within the current statistical period, as well as the single execution time of each SQL statement under that call point, are obtained. The frequency density is compared with a preset frequency threshold, and the dispersion is compared with a preset dispersion threshold. Simultaneously, it is determined whether the single execution time of each SQL statement has not reached the slow query threshold. When all three conditions are met—that is, the frequency density exceeds the preset frequency threshold, the dispersion is lower than the preset dispersion threshold, and the single execution time has not reached the slow query threshold—the single execution times of all SQL statements under that target business call point are accumulated to obtain the total performance loss time generated by that call point within the current statistical period. This total performance loss time reflects the total performance loss accumulated due to high-frequency repetitive execution, even though the individual executions are not slow.

[0100] Among them, the preset frequency threshold represents a pre-set frequency density threshold, used to determine whether it belongs to a high-frequency repetitive execution scenario; the preset dispersion threshold represents a pre-set dispersion threshold, used to determine whether it belongs to a call scenario with a single or a few SQL structures; and the total consumption time represents the sum of the execution time of all SQL statements under the same target business call point.

[0101] Through the above embodiments, in scenarios with high frequency density, low dispersion, and slow single execution, the total loss time generated by the target business call point is accumulated and calculated, which quantifies and presents the hidden performance problem of high-frequency repeated execution within the loop, and makes up for the blind spot of traditional slow query monitoring which only focuses on single long-time SQL queries.

[0102] S303. If the total loss time exceeds the preset logical redundancy threshold, the target business call point is marked as the root cause of query redundancy, and the root cause of query redundancy is inserted after the TopN sequence of the root cause location list. The root cause of query redundancy is used to characterize the root cause of system performance degradation caused by high frequency triggering in the loop body, even if the single execution is not a slow query.

[0103] Specifically, the total execution time of the target business call point, calculated cumulatively, is compared with a preset logical redundancy threshold. When the total execution time exceeds the logical redundancy threshold, the target business call point is marked as a query redundancy root cause. A query redundancy root cause is a special type of performance bottleneck, characterized by a single SQL statement whose execution time does not reach the slow query threshold, but because it is placed in a loop or high-frequency call path, the same SQL statement is repeatedly executed a large number of times at the same call point, resulting in a cumulative total execution time exceeding the logical redundancy threshold. This query redundancy root cause is inserted after the TopN sequence of the generated root cause location list, that is, after all the regular root cause records selected by sorting by cumulative execution time. During insertion, the query redundancy root cause can be appended as a separate entry to the end of the list, or displayed in a group. Query redundancy root causes serve as a reminder to developers: although a single SQL execution is not slow, there is redundant logic of frequently repeated queries in this business code location, requiring loop-outside optimization or batch query modification.

[0104] Among them, the preset logical redundancy threshold represents the pre-set critical value of total loss time, which is used to determine whether high-frequency repeated queries have reached the level that needs optimization; query redundancy root cause represents a special type of performance bottleneck identifier, which is used to characterize the cumulative performance problem caused by the high-frequency triggering of the same SQL within the loop; TopN sequence represents the top N root cause records in the root cause location list sorted by cumulative time consumption.

[0105] Through the above embodiments, business call points whose total loss time exceeds the logical redundancy threshold are marked as query redundancy root causes and inserted into the root cause location list. This enables developers to identify hidden problems that are not slow in a single execution but cause significant performance loss due to high-frequency repetition. This makes up for the blind spot of traditional slow query monitoring, which only focuses on a single long-time SQL query, and provides more complete root cause information for comprehensive optimization of system performance.

[0106] Based on the above embodiments, as an optional embodiment, for Figure 1 The slow SQL root cause localization method shown may also include steps S401-S405, which are explained in detail below.

[0107] S401. Extract the transaction state object associated with the currently executing thread from the transaction manager of the object-relational mapping framework.

[0108] Specifically, through the transaction management interface provided by the object-relational mapping framework, the transaction state object bound to the currently executing thread is obtained. The transaction manager typically uses thread-local variables to store the transaction context currently being executed by the thread. Calling the transaction manager's "Get Current Transaction" method retrieves the transaction state object associated with the current thread from the thread-local storage. If the current thread is within an active transaction, a non-null transaction state object is returned; otherwise, null is returned. The transaction state object encapsulates the metadata information of the current transaction, including a unique transaction identifier, transaction start timestamp, transaction isolation level, transaction propagation behavior, and transaction connection holder.

[0109] Among them, the transaction manager represents the core component in the framework responsible for managing the lifecycle of database transactions, and is used to control the start, commit, rollback of transactions, as well as the binding and unbinding of transaction contexts; the transaction state object represents a container object that encapsulates the metadata of the current transaction, including information such as transaction identifier, start time, isolation level, propagation behavior, and connection holder.

[0110] S402. If the transaction status object is not empty, obtain the start timestamp of the current transaction and calculate the transaction offset between the start time of the actual commit of the SQL statement and the start timestamp.

[0111] Specifically, it checks if the transaction status object retrieved from the transaction manager is empty. If the transaction status object is not empty, it means that the current execution thread is within the scope of an active transaction. Then, it reads the start timestamp of the current transaction from the transaction status object. It obtains the start timestamp of when the current SQL statement was actually committed to the database for execution. This start timestamp was recorded in the pre-interception stage of the SQL execution. Subtracting the transaction start timestamp from the start timestamp yields the transaction offset duration. The transaction offset duration represents the execution time position of the current SQL statement within the transaction's lifecycle, that is, the time interval elapsed from the start of the transaction to the start of the SQL's execution. This offset duration reflects the SQL's execution time position within the transaction context. The larger the offset duration, the later the SQL is executed within the transaction, and the more likely it is to be affected by lock holdings or connection occupancy accumulated in the earlier stages of the transaction.

[0112] The start timestamp represents the point in time when the transaction manager creates the transaction, measured in nanoseconds or milliseconds; the transaction offset duration represents the difference between the start timestamp and the start timestamp, used to characterize the time position of SQL execution within the transaction's lifecycle.

[0113] Through the above embodiments, the offset between the SQL start time and the transaction start time is calculated in the active state of the transaction, which quantifies the time position of the SQL execution in the transaction life cycle and provides a quantitative basis for subsequent judgment on whether the SQL time consumption is caused by long transaction lock waiting or connection pool queuing.

[0114] S403. Obtain the database connection object held by the current transaction, and obtain the count of uncommitted existing change operations in the database connection object.

[0115] Specifically, the database connection object bound to the current transaction is retrieved from the transaction state object. This database connection object represents the communication channel between the application and the database, managed by a connection pool and allocated from the pool when a transaction begins. The metadata interface provided by the database connection object is used to obtain the count of uncommitted existing change operations on that connection. This count represents the number of database update operations executed through this connection within the current transaction scope, including write operations such as inserts, updates, and deletes. The counter increments with each write operation and resets to zero after the transaction is committed or rolled back. This count reflects the accumulated scale of change operations in the current transaction; a larger count indicates that the transaction has performed more write operations, potentially leading to longer lock wait times or a greater connection load for subsequent SQL queries.

[0116] Among them, the database connection object represents an instance of the communication channel between the application and the database, which is managed by the connection pool; the uncommitted inventory change operation count represents the number of database update operations that have been executed but not yet committed in the current transaction, including the cumulative number of insert, update and delete operations.

[0117] Through the above embodiments, the count of uncommitted existing change operations in the database connection objects held by the current transaction is obtained, which quantifies the scale of the accumulated write operations of the transaction. This provides a data basis for subsequent judgment on whether the SQL time consumption is affected by long transaction lock waiting or connection queuing. The larger the count, the more "heavy" the transaction is, and the more likely the subsequent SQL will be slowed down due to lock contention.

[0118] S404. Normalize and weight the offset duration within the transaction with the count of existing change operations to obtain the transaction coupling impact factor of the SQL statement.

[0119] Specifically, after obtaining the intra-transaction offset duration and the count of existing change operations, the two metrics are normalized to map values ​​with different dimensions to a unified numerical range. Normalization of the intra-transaction offset duration is achieved by dividing by a preset offset duration baseline value, and normalization of the count of existing change operations is achieved by dividing by a preset change count baseline value. The two normalized values ​​are then multiplied by their respective weighting coefficients, with the sum of these coefficients being 1. The relative magnitudes of the offset duration weight and the change count weight are preset based on their contribution to SQL performance in the actual scenario. The two weighted values ​​are then added together to obtain the transaction coupling impact factor. This factor quantifies the severity of the impact of the transaction environment on the execution time of the current SQL statement. A larger factor value indicates that the SQL is more likely to experience performance degradation due to long transaction lock waits or connection pool queuing.

[0120] Among them, the transaction coupling impact factor represents a comprehensive value obtained after weighted calculation, which is used to characterize the degree to which SQL execution time is affected by the transaction environment.

[0121] For example, suppose in an e-commerce system, the transaction offset duration is 1000 milliseconds, the preset offset duration baseline is 5000 milliseconds, and after normalization, it becomes 0.2; the existing change operation count is 5, the preset change count baseline is 20, and after normalization, it becomes 0.25. The offset duration weighting coefficient is set to 0.4, and the change count weighting coefficient is set to 0.6. The weighted calculation is: 0.2 × 0.4 + 0.25 × 0.6 = 0.08 + 0.15 = 0.23, and the transaction coupling impact factor is 0.23. In another scenario, the offset duration is 4000 milliseconds, which is 0.8 after normalization; the change count is 15, which is 0.75 after normalization. The weighted calculation is: 0.8 × 0.4 + 0.75 × 0.6 = 0.32 + 0.45 = 0.77, and the transaction coupling impact factor is 0.77, indicating that this SQL is more severely affected by the transaction environment.

[0122] S405. If the transaction coupling impact factor exceeds the preset weight, a transaction risk flag is added to the root cause aggregation flag. The added transaction risk flag is used to characterize that the increase in the time consumption of the SQL statement is caused by a non-statement root cause induced by long transaction lock waiting or connection pool queuing.

[0123] Specifically, the calculated transaction coupling impact factor is compared with a preset transaction impact weight threshold. When the transaction coupling impact factor is greater than the preset weight, it indicates that the execution time of the current SQL statement is significantly negatively affected by the transaction environment. This increase in time is not due to structural or indexing issues in the SQL statement itself, but rather induced by long transaction lock waits or connection pool queuing. In this case, a transaction risk flag is appended to the end of the root cause aggregation identifier string. The transaction risk flag is a predefined identifier used to classify the current SQL execution event as a transaction-coupled slow query. The root cause aggregation identifier with the appended flag carries this flag in subsequent aggregation statistics and root cause localization outputs, enabling developers to distinguish between pure SQL performance issues and performance issues induced by the transaction environment. If the transaction coupling impact factor does not exceed the preset weight, no flag is appended, and the root cause aggregation identifier remains unchanged.

[0124] Among them, the preset weight represents a pre-set threshold for the impact of transactions, which is used to determine whether the contribution of the transaction environment to the SQL execution time is significant; the transaction risk flag represents a predefined identifier used to mark the transaction coupling attribute of the SQL execution event.

[0125] Through the above embodiments, when the transaction coupling impact factor exceeds the preset weight, a transaction risk flag is added to the root cause aggregation flag, which distinguishes the performance problems induced by the transaction environment from the performance problems of the SQL itself, avoids developers being misled by the interference information introduced by the transaction environment during troubleshooting, and improves the accuracy of root cause location.

[0126] Based on the above embodiments, as an optional embodiment, for Figure 1 The slow SQL root cause localization method shown may also include steps S501-S506, which are explained in detail below.

[0127] S501. Determine whether the execution time of the target reaches the preset severe threshold for deep diagnosis. The severe threshold for deep diagnosis is greater than the slow query threshold.

[0128] Specifically, the system obtains the target execution time of the current SQL statement and a pre-configured critical threshold for deep diagnostics. The critical threshold is a preset value greater than the slow query threshold, used to identify SQL statements with extremely long execution times. It is usually set to several times the slow query threshold. For example, if the slow query threshold is 120 milliseconds, the critical threshold for deep diagnostics can be set to 500 milliseconds or 1000 milliseconds. If the target execution time reaches the critical threshold for deep diagnostics, it indicates that the current SQL statement is a serious performance bottleneck and requires deeper root cause analysis, including collecting parameter morphology information to identify slow SQL statements triggered by specific parameter values ​​or parameter sizes. If the threshold is not reached, the deep diagnostic process is not triggered, and only regular slow query processing is performed.

[0129] Among them, the severe threshold for deep diagnosis represents a pre-configured time-consuming threshold that is greater than the slow query threshold, and is used as a criterion for defining severe performance bottlenecks.

[0130] S502. If the execution time of the target reaches the critical threshold for deep diagnosis, extract the list of dynamic parameter data actually bound to the SQL statement in the current execution thread at the statement execution interception point.

[0131] Specifically, when the execution time of the target is determined to reach the critical threshold for deep diagnosis, the deep diagnosis process is triggered. In the context of the current SQL statement's execution interception point, the actual bound dynamic parameter data list is extracted from the SQL statement object to be executed. The dynamic parameter data list is a collection of actual parameter values ​​corresponding to placeholders in the SQL statement, arranged in the order in which the parameters appear in the SQL statement. The extraction process is implemented by calling the parameter mapping method provided by the SQL statement object. This method returns a list or mapping structure containing all parameter values. Each parameter value may be a basic type, string, date, set, or custom object. The extracted dynamic parameter data list will be used for subsequent parameter morphology analysis to identify slow SQL triggered by specific parameter values ​​or parameter sizes. If the target execution time does not reach the critical threshold for deep diagnosis, this extraction operation is not performed to avoid unnecessary parameter data collection overhead.

[0132] The dynamic parameter data list represents the collection of actual parameter values ​​corresponding to placeholders in the SQL template, arranged in order of parameter position.

[0133] S503. Traverse the dynamic parameter data list, filter parameter fields that carry preset privacy desensitization annotations, and obtain the filtered dynamic parameter data list.

[0134] Specifically, after obtaining the list of dynamic parameter data extracted from the SQL statement object, each parameter item in the list is iterated through. For each parameter item, it is checked whether its corresponding field declaration or parameter mapping definition contains a preset privacy-desensitizing annotation. The privacy-desensitizing annotation is a custom annotation identifier used to mark parameter fields containing sensitive information, such as mobile phone numbers, ID card numbers, bank card numbers, passwords, and other personal privacy data. If the annotation exists on the field or type corresponding to the parameter item, the parameter item is filtered out from the list and not included in subsequent analysis; if the annotation does not exist, the parameter item is retained. After the iteration is completed, a filtered list of dynamic parameter data is obtained. This list only contains non-sensitive parameter data that can be safely used for root cause analysis, and sensitive information has been removed.

[0135] Among them, the preset privacy desensitization annotation represents a predefined custom annotation identifier used to mark parameter fields or attributes containing sensitive information; the filtered dynamic parameter data list represents the set of parameter values ​​remaining after sensitive parameter items have been removed.

[0136] By traversing the dynamic parameter data list and filtering parameter fields with privacy-de-identifying annotations, while retaining parameter features that can be used for performance analysis, sensitive personal information such as mobile phone numbers and ID card numbers is eliminated, thus meeting data security and privacy protection compliance requirements and reducing the risk of sensitive data leakage.

[0137] S504. Based on the data type of the parameter field in the filtered dynamic parameter data list, calculate the parameter character length feature value and the set element size feature value.

[0138] Specifically, each parameter item in the filtered dynamic parameter data list is traversed, and its characteristic value is calculated based on its data type. For string parameters, the string length is obtained and used as the parameter's character length characteristic value. For set or array parameters, the number of elements in the set or array is obtained and used as the set's element size characteristic value. For basic data types such as numeric, boolean, and date parameters, these are ignored or their default values ​​are used. If the list contains multiple parameters, the characteristic value of each parameter can be calculated separately, or the maximum value or sum can be taken as the comprehensive characteristic value. The calculated characteristic values ​​are then mapped to the capacity ladder dictionary to identify slow SQL queries induced by specific parameter sizes.

[0139] Among them, the parameter character length feature value represents the number of characters contained in the string type parameter; the set element size feature value represents the number of elements contained in the set or array type parameter.

[0140] S505. Map the parameter character length feature value and the set element size feature value to the preset capacity ladder dictionary, and concatenate them to transform them into a parameter morphology feature string. The parameter morphology feature string is used to represent the parameter data volume.

[0141] Specifically, the query uses two feature values—parameter character length and set element size—as query keys. A predefined capacity hierarchy dictionary is used for mapping and matching. This dictionary divides continuous range feature values ​​into discrete capacity levels; for example, 0 to 10 is mapped to "S", 11 to 50 to "M", 51 to 200 to "L", and greater than 200 to "XL". The parameter character length feature value is mapped to the string capacity level, and the set element size feature value is mapped to the set capacity level. The two capacity levels are then concatenated in a predefined order, with a separator inserted, to generate a parameter morphology feature string. This string characterizes the size of the parameter data during the SQL execution. For example, "MS" indicates a medium string length and a small set size, while "L-XL" indicates a large string length and a very large set size. This parameter morphology feature string, as an abstract representation at the parameter level, can be used in subsequent aggregation statistics to identify slow SQL queries induced by specific parameter sizes.

[0142] Among them, the capacity ladder dictionary represents a mapping table that divides continuous range feature values ​​into discrete capacity levels; the parameter morphology feature string represents a string composed of multiple capacity levels, used to characterize the combined features of parameter data volume.

[0143] S506. Append the parameter morphology feature string to the end of the root cause aggregation identifier to generate a derived sub-identifier, which is used to independently count the system performance bottlenecks induced by the data volume of specific parameters.

[0144] Specifically, after obtaining the generated parameter morphology feature string, it is appended to the end of the root cause aggregation identifier. This string is then connected to the original root cause aggregation identifier using a delimiter to generate a derived sub-identifier. The original root cause aggregation identifier, composed of an SQL fingerprint and the target business call point, is used to group SQL execution events with the same SQL structure and the same business trigger location into the same performance bottleneck source. After appending the parameter morphology feature string, the derived sub-identifier further subdivides execution events with different parameter volumes under the same root cause, forming finer-grained statistical units. For example, under the same root cause aggregation identifier, execution events with volumes "S," "M," and "L" will be assigned to different derived sub-identifiers, each independently calculating cumulative time, occurrence count, and other metrics. This allows the system to independently identify performance bottlenecks induced by specific parameter data volumes, rather than lumping slow queries of all parameter volumes together in the statistics.

[0145] Among them, the derived sub-identifier represents a new identifier generated by appending a parameter morphological feature string to the end of the root cause aggregation identifier, which is used to distinguish execution events with different parameter volumes under the same root cause in a fine-grained manner.

[0146] Through the above embodiments, the parameter morphology feature string is appended to the end of the root cause aggregation identifier to generate a derived sub-identifier, so that SQL execution events with different parameter sizes under the same business call point can be independently counted and sorted. Developers can use this to accurately identify conditional slow SQL that is only triggered when the parameter size reaches a certain level, providing accurate data basis for targeted parameter optimization or pagination transformation.

[0147] The slow SQL root cause localization system in the embodiments of this invention is described below from the perspective of hardware processing. Please refer to [link / reference needed]. Figure 3 , Figure 3 This is a schematic diagram of the structure of a slow SQL root cause localization system provided in an embodiment of this application.

[0148] It should be noted that, Figure 3 The structure of the slow SQL root cause localization system shown is merely an example and should not impose any limitations on the functionality and scope of application of the embodiments of this application.

[0149] like Figure 3 As shown, the slow SQL root cause localization system includes a central processing unit 601, which can perform various appropriate actions and processes based on a program stored in read-only memory 602 or a program loaded from storage section 608 into random access memory 603, such as performing the methods described in the above embodiments. The random access memory 603 also stores various programs and data required for system operation. The central processing unit 601, read-only memory 602, and random access memory 603 are interconnected via bus 604. Input / output interface 605 is also connected to bus 604.

[0150] The following components are connected to the input / output interface 605: an input section 606 including audio input devices, push-button switches, etc.; an output section 607 including a liquid crystal display (LCD) and audio output devices, indicator lights, etc.; a storage section 608 including a hard disk, etc.; and a communication section 609 including a network interface card such as a LAN (Local Area Network) card, modem, etc. The communication section 609 performs communication processing via a network such as the Internet. A drive 610 is also connected to the input / output interface 605 as needed. A removable medium 611, such as a disk, optical disk, magneto-optical disk, semiconductor memory, etc., is installed on the drive 610 as needed so that computer programs read from it can be installed into the storage section 608 as needed.

[0151] In particular, according to embodiments of the present invention, the processes described above with reference to the flowcharts can be implemented as computer software programs. For example, embodiments of the present invention include a computer program product comprising a computer program carried on a computer-readable medium, the computer program including a computer program for performing the methods shown in the flowcharts. In such embodiments, the computer program can be downloaded and installed from a network via communication section 609, and / or installed from removable medium 611. When the computer program is executed by central processing unit 601, it performs the various functions defined in the present invention. It should be noted that specific examples of computer-readable storage media may include, but are not limited to: electrical connections having one or more wires, portable computer disks, hard disks, random access memory (RAM), read-only memory (ROM), erasable programmable read-only memory (EPROM), flash memory, optical fiber, portable compact disc read-only memory (CD-ROM), optical storage devices, magnetic storage devices, or any suitable combination thereof. In this invention, a computer-readable storage medium can be any tangible medium that contains or stores a program that can be used by or in conjunction with an instruction execution system, apparatus, or device.

[0152] The flowcharts and block diagrams in the accompanying drawings illustrate the architecture, functionality, and operation of possible implementations of systems, methods, and computer program products according to various embodiments of the present invention. Each block in a flowchart or block diagram may represent a module, program segment, or portion of code, which contains one or more executable instructions for implementing a specified logical function. It should also be noted that in some alternative implementations, the functions indicated in the blocks may occur in a different order than those shown in the drawings.

[0153] Specifically, the slow SQL root cause localization system of this embodiment includes a processor and a memory. The memory stores a computer program. When the computer program is executed by the processor, it implements the slow SQL root cause localization method provided in the above embodiment.

[0154] In another aspect, the present invention also provides a computer-readable storage medium, which may be included in the slow SQL root cause localization system described in the above embodiments; or it may exist independently and not assembled into the slow SQL root cause localization system. The storage medium carries one or more computer programs that, when executed by a processor of the slow SQL root cause localization system, cause the slow SQL root cause localization system to implement the slow SQL root cause localization method provided in the above embodiments.

[0155] The above description is merely an exemplary embodiment of this disclosure and should not be construed as limiting the scope of this disclosure. Any equivalent changes and modifications made in accordance with the teachings of this disclosure shall still fall within the scope of this disclosure. This application is intended to cover any variations, uses, or adaptations of this disclosure that follow the general principles of this disclosure and include common knowledge or customary techniques in the art not described in this disclosure. The specification and embodiments are considered exemplary only, and the scope and spirit of this disclosure are defined by the claims.

Claims

1. A method for locating the root cause of slow SQL queries, characterized in that, include: By using the statement execution interception point of the object-relational mapping framework, the SQL statement to be executed in the current execution thread is obtained, and the target execution time is calculated based on the start time and end time of the SQL statement actually submitted to the database for execution. When the execution time of the target exceeds the preset slow query threshold, the current thread call stack is collected in real time, and the first stack frame that matches the business package name rule is extracted from the current thread call stack as the target business call point corresponding to the SQL statement; Extract the SQL template string corresponding to the SQL statement, and perform structural normalization and hash calculation on the SQL template string to generate the SQL fingerprint of the SQL statement; The SQL fingerprint is concatenated with the target business call point to generate the root cause aggregation identifier of the SQL statement, and the execution time of the target is added to the cumulative time consumption index corresponding to the root cause aggregation identifier in the current statistical period. The root cause aggregation identifier is used to characterize the source of the performance bottleneck of the SQL statement. At the end of the current statistical period, the root cause aggregation identifiers are arranged in descending order according to the cumulative time consumption index to obtain the root cause aggregation identifier sequence of the current statistical period; Based on the root cause aggregation identifier sequence, a root cause location list is output, which is used to characterize the actual triggering location of the system performance bottleneck.

2. The method according to claim 1, characterized in that, Extracting the first stack frame matching the business package name rule from the current thread's call stack as the target business call point corresponding to the SQL statement includes: Obtain the complete stack frame sequence of the current thread's call stack; Using a pre-defined underlying call whitelist, the frame reflection stack frame and proxy execution stack frame in the complete stack frame sequence are skipped from top to bottom to obtain the skipped intermediate stack frame sequence. The first stack frame in the skipped intermediate stack frame sequence that matches the service package name rule is determined as the initial service call point; If the source code line number debugging information of the initial business call point is missing, then extract the method bytecode descriptor and instruction execution offset corresponding to the initial business call point; The class name corresponding to the initial business call point, the method bytecode descriptor, and the instruction execution offset are jointly encoded as the target business call point, which is used to maintain the uniqueness of the root cause aggregation identifier in the state of source code line number loss. If the source code line number debugging information is not missing, then the initial business call point will be used as the target business call point.

3. The method according to claim 1, characterized in that, Before obtaining the SQL statement to be executed in the currently executing thread, the method further includes: Intercept the external business request that triggers the currently executing thread at the network request access layer; Parse the request header parameters of the external service request to identify the traffic access layer source of the external service request; Based on the traffic access layer source, the baseline interface latency tolerance corresponding to the traffic access layer source is matched from the dynamic configuration center; Calculate the product of the baseline interface latency tolerance and the preset global slow SQL sensitivity coefficient to obtain the baseline slow query threshold for the external business request; Calculate the business priority weight corresponding to the external business request, and dynamically scale the baseline slow query threshold based on the business priority weight to obtain the slow query threshold for the current execution thread. Then, bind the slow query threshold to the execution context of the current execution thread. The business priority weight is inversely proportional to the slow query threshold.

4. The method according to claim 1, characterized in that, The step of outputting a root cause localization list based on the root cause aggregation identifier sequence includes: At the end of the current statistical period, a preset number of root cause aggregation identifiers are selected from the root cause aggregation identifier sequence according to the cumulative time consumption index from high to low and extracted as a root cause cluster candidate set. The root cause aggregation identifiers in the root cause cluster candidate set are marked as root cause candidate identifiers. The SQL fingerprint of the root cause candidate identifier, the target business call point, and the corresponding cumulative time consumption index, maximum time consumption index, and occurrence frequency index are assembled into a root cause record. The root cause record also includes the percentage of the cumulative time consumption of the root cause candidate identifier in the current statistical period to the total cumulative time consumption of the root cause cluster candidate set. Arrange all the root cause records in descending order of cumulative time consumption to generate a root cause localization list.

5. The method according to claim 1, characterized in that, Following the output of the root cause localization list, the method further includes: The dispersion of different SQL fingerprints triggered by the same target business call point within the current statistical period is statistically analyzed, as well as the frequency density of triggering the same SQL fingerprint. When the frequency density exceeds a preset frequency threshold, the dispersion is lower than a preset dispersion threshold, and the execution time of a single SQL statement does not reach the slow query threshold, the total time lost by the target business call point is calculated cumulatively. If the total loss time exceeds the preset logical redundancy threshold, the target service call point is marked as the root cause of query redundancy, and the root cause of query redundancy is inserted after the TopN sequence of the root cause location list. The root cause of query redundancy is used to characterize the root cause that is not a slow query in a single execution, but is triggered frequently in the loop body, causing the system performance to degrade.

6. The method according to claim 1, characterized in that, Following the output of the root cause localization list, the method further includes: Extract the transaction state object associated with the currently executing thread from the transaction manager of the object-relational mapping framework; If the transaction status object is not empty, then obtain the start timestamp of the current transaction, and calculate the intra-transaction offset between the start time of the actual submission of the SQL statement and the start timestamp. Obtain the database connection object held by the current transaction, and obtain the count of uncommitted existing change operations in the database connection object; The transaction coupling impact factor of the SQL statement is obtained by normalizing and weighting the transaction offset duration and the count of existing change operations. If the transaction coupling impact factor exceeds the preset weight, a transaction risk flag is added to the root cause aggregation flag. The added transaction risk flag is used to characterize that the increase in the time consumption of the SQL statement is caused by a non-statement-specific root cause induced by long transaction lock wait or connection pool queuing.

7. The method according to claim 1, characterized in that, The method further includes: Determine whether the execution time of the target reaches a preset severe threshold for deep diagnosis, wherein the severe threshold for deep diagnosis is greater than the slow query threshold; If the execution time of the target reaches the critical threshold of the deep diagnosis, extract the list of dynamic parameter data actually bound to the SQL statement in the current execution thread at the statement execution interception point; Traverse the dynamic parameter data list, filter parameter fields carrying preset privacy desensitization annotations, and obtain the filtered dynamic parameter data list; Based on the data type of the parameter fields in the filtered dynamic parameter data list, calculate the parameter character length feature value and the set element size feature value; The parameter character length feature value and the set element size feature value are mapped to a preset capacity ladder dictionary and concatenated to form a parameter morphology feature string, which is used to characterize the parameter data volume. The parameter morphological feature string is appended to the tail of the root cause aggregation identifier to generate a derived sub-identifier, which is used to independently count the system performance bottlenecks induced by the data volume of specific parameters.

8. A slow SQL root cause localization system, characterized in that, The slow SQL root cause localization system includes: one or more processors and a memory; the memory is coupled to the one or more processors, the memory is used to store computer program code, the computer program code including computer instructions, and the one or more processors invoke the computer instructions to cause the slow SQL root cause localization system to perform the method as described in any one of claims 1-7.

9. A computer-readable storage medium comprising instructions, characterized in that, When the instruction is run on the slow SQL root cause localization system, the slow SQL root cause localization system performs the method as described in any one of claims 1-7.

10. A computer program product, characterized in that, When the computer program product is run on the Slow SQL Root Cause Locator, the Slow SQL Root Cause Locator performs the method as described in any one of claims 1-7.

Images