SQL performance detection early warning method and device, equipment and storage medium

Abstract

The invention relates to the field of data, and discloses an SQL (Structured Query Language) performance detection early warning method, device and equipment and a storage medium, and the method is used for detecting GreenPlum SQL abnormity and generating an optimization early warning work order. The method comprises the following steps: constructing and dynamically updating an SQL performance baseline according to a historical load mode; respectively comparing the performance data of the plurality of SQL statements obtained in real time with the SQL performance baseline, identifying to obtain a plurality of abnormal SQL statements, and analyzing the abnormal SQL statements to generate a performance degradation portrait; according to the performance degradation portraits, matching and generating optimization work orders corresponding to the performance bottleneck points of the performance degradation portraits from a preset optimization rule knowledge base; in combination with historical effect data, benefit pre-evaluation and risk analysis implementation are carried out on the multiple optimized work orders, priority ranking is carried out on the multiple optimized work orders according to evaluation and analysis results, and the optimized work orders after ranking are obtained; and generating an early warning message packet according to the sorted optimized work order, and pushing the early warning message packet to a specified terminal.

Description

Technical Field

[0001] This invention relates to the field of data processing technology, and in particular to a method, apparatus, device, and storage medium for SQL performance detection and early warning. Background Technology

[0002] With the advent of the big data era, large-scale parallel processing databases such as GreenPlum have been widely used in core business scenarios such as enterprise-level data warehousing and data analysis. As the core carrier of data interaction, the performance of SQL statements directly determines the response efficiency and operational stability of business systems.

[0003] Traditional SQL performance testing methods often employ static threshold judgment, pre-setting fixed performance metric thresholds (such as execution time, CPU utilization, etc.). An alarm is triggered when the SQL statement's metric exceeds the threshold. However, this approach has significant drawbacks: firstly, static thresholds cannot adapt to dynamic changes in business load. Load patterns vary considerably across different time periods and business scenarios, making fixed thresholds prone to false alarms or missed alarms; secondly, traditional methods can only identify performance anomalies but cannot accurately pinpoint performance bottlenecks, and lack systematic analysis and optimization guidance for abnormal SQL statements.

[0004] Meanwhile, in existing technologies, SQL optimization largely relies on the experience and judgment of operations and maintenance personnel, resulting in low efficiency and poor targeting in generating optimization solutions. Furthermore, when multiple abnormal SQL statements exist, it is impossible to scientifically assess the benefits and risks of each optimization solution, making it difficult to prioritize performance issues with the greatest impact on business operations. This leads to unreasonable allocation of optimization resources and may even cause new system risks due to blind optimization.

[0005] In addition, traditional detection methods lack real-time capability, making it difficult to quickly capture the dynamic degradation trend of SQL performance. They are often only discovered after performance problems have caused business stagnation or data processing delays, making it impossible to achieve proactive early warning and intervention.

[0006] Therefore, existing technologies still need improvement and development. Summary of the Invention

[0007] This invention provides a method, apparatus, device, and storage medium for detecting SQL performance detection and early warning, used to detect GreenPlum SQL anomalies and generate optimization early warning work orders.

[0008] The first aspect of this invention provides a SQL performance detection and early warning method, comprising: constructing and dynamically updating an SQL performance baseline based on historical load patterns, and simultaneously acquiring performance data of multiple SQL statements from a GreenPlum database; comparing the performance data of each SQL statement with the SQL performance baseline to identify multiple abnormal SQL statements, and analyzing each abnormal SQL statement to generate a performance degradation profile corresponding to each abnormal SQL statement, wherein the performance degradation profile includes at least one performance bottleneck; matching and generating optimization work orders corresponding to the performance bottlenecks of each performance degradation profile from a preset optimization rule knowledge base based on the performance degradation profile; performing a benefit pre-evaluation and implementation risk analysis on the multiple optimization work orders based on historical effect data to obtain evaluation and analysis results, and prioritizing the multiple optimization work orders based on the evaluation and analysis results to obtain sorted optimization work orders; generating an early warning message package based on the sorted optimization work orders and pushing it to a preset operation and maintenance terminal or development alarm group.

[0009] Optionally, in a first implementation of the first aspect of the present invention, the step of constructing and dynamically updating the SQL performance baseline based on historical load patterns, and simultaneously acquiring performance data of multiple SQL statements from the GreenPlum database, includes: establishing SQL performance baselines for SQL execution time, CPU consumption time, and number of rows scanned according to different business periods and load types, and periodically updating the baseline parameters of the SQL performance baselines according to the latest load patterns; acquiring performance data of multiple SQL statements from the GreenPlum database in real time, wherein the performance data of the SQL statements includes the complete execution plan, the actual number of rows returned, the data distribution key value, and the system load snapshot at the execution time; generating hash fingerprints for each SQL statement, and associating and storing the hash fingerprints and performance data corresponding to each SQL statement with the SQL performance baseline of the corresponding period in a time-series database.

[0010] Optionally, in the second implementation of the first aspect of the present invention, the step of comparing the performance data of each SQL statement with the SQL performance baseline to identify multiple abnormal SQL statements, and analyzing each of the abnormal SQL statements to generate a performance degradation profile corresponding to each abnormal SQL statement, wherein the performance degradation profile includes at least one performance bottleneck point, including: comparing the performance data of each SQL statement with the SQL performance baseline to identify statements with sudden increases in execution time, abnormal resource consumption, or frequent changes in execution plans as abnormal SQL statements; acquiring and analyzing the execution plans of each of the abnormal SQL statements to obtain the execution plan analysis results corresponding to each abnormal SQL statement, wherein the execution plan analysis results include high-cost operators, unnecessary data redistribution operations, and optimizer cost estimation distortion operators; acquiring the table statistics associated with each abnormal SQL statement, and combining the execution plan analysis results and the table statistics associated with the abnormal SQL statement to generate a performance degradation profile corresponding to each abnormal SQL statement, wherein the performance degradation profile includes at least one performance bottleneck point.

[0011] Optionally, in a third implementation of the first aspect of the present invention, the step of acquiring and analyzing the execution plans of each of the abnormal SQL statements to obtain the execution plan analysis results corresponding to each abnormal SQL statement, wherein the execution plan analysis results include high-cost operators, unnecessary data redistribution operations, and optimizer cost estimation distortion operators, includes: for each of the abnormal SQL statements, acquiring the execution plan of the abnormal SQL statement, identifying and extracting all operator nodes and their attribute information in the tree structure of the execution plan; based on the extracted operator nodes and their attribute information, analyzing and marking high-cost operators whose execution costs exceed a preset threshold, and identifying unnecessary data redistribution operations in the execution plan; comparing the optimizer's estimated values ​​for operator costs in the execution plan with the actual execution statistics, calculating and determining optimizer cost estimation distortion links where the cost estimation deviation rate exceeds a set threshold, and integrating high-cost operators, unnecessary data redistribution operations, and optimizer cost estimation distortion links into the execution plan analysis results.

[0012] Optionally, in the fourth implementation of the first aspect of the present invention, the step of matching and generating optimization work orders corresponding to the performance bottleneck points of each performance degradation profile from a preset optimization rule knowledge base according to the performance degradation profile includes: matching the performance bottleneck points in the performance degradation profile with entries in the preset optimization rule knowledge base to obtain target optimization rules, wherein the preset optimization rule knowledge base includes index optimization rules, SQL rewrite optimization rules, statistical information update optimization rules, and table structure optimization rules; generating optimization scripts including specific operation objects and parameters according to the target optimization rules, wherein the optimization scripts are directly executable DDL or annotated SQL rewrite examples; obtaining implementation instructions and rollback scripts corresponding to the optimization scripts, and generating optimization work orders corresponding to each performance degradation profile according to the optimization scripts, the rollback scripts, and the implementation instructions.

[0013] Optionally, in the fifth implementation of the first aspect of the present invention, the step of combining historical performance data to perform a benefit pre-assessment and implementation risk analysis on multiple optimization work orders, obtaining assessment analysis results, and prioritizing the multiple optimization work orders according to the assessment analysis results to obtain sorted optimization work orders includes: querying a historical optimization case library to obtain actual performance improvement data recorded by historical optimization operations similar to each optimization work order, and assessing the estimated benefits of each optimization work order; analyzing the additional overhead and risks that may be brought about by the implementation of each optimization work order, the additional overhead and risks including index maintenance costs and DDL lock blocking risks; calculating the comprehensive score of each optimization work order based on the estimated benefits, the additional overhead and risks, preset benefit weight coefficients and additional overhead and risk weight coefficients, and sorting the multiple optimization work orders in descending order of comprehensive score to obtain sorted optimization work orders.

[0014] Optionally, in a sixth implementation of the first aspect of the present invention, the step of generating an early warning message package based on the sorted optimized work orders and pushing it to a preset operation and maintenance terminal or development alarm group includes: generating an optimized work order list based on the sorted optimized work orders; integrating the optimized work order list, the estimated benefits of each optimized work order, additional costs and risks, corresponding abnormal SQL statements and performance degradation profiles into a structured early warning message package; and pushing the early warning message package to a preset operation and maintenance terminal or development alarm group through an API interface.

[0015] A second aspect of the present invention provides an SQL performance detection and early warning device, comprising: a construction module, configured to construct and dynamically update an SQL performance baseline based on historical load patterns, and simultaneously acquire performance data of multiple SQL statements from a GreenPlum database; a comparison module, configured to compare the performance data of each SQL statement with the SQL performance baseline, identify multiple abnormal SQL statements, and analyze each abnormal SQL statement to generate a performance degradation profile corresponding to each abnormal SQL statement, wherein the performance degradation profile includes at least one performance bottleneck point; a generation module, configured to match and generate optimization work orders corresponding to the performance bottleneck points of each performance degradation profile from a preset optimization rule knowledge base based on the performance degradation profile; an evaluation and analysis module, configured to perform a benefit pre-evaluation and implementation risk analysis on the multiple optimization work orders based on historical effect data, obtain evaluation and analysis results, and prioritize the multiple optimization work orders according to the evaluation and analysis results to obtain ranked optimization work orders; and a push module, configured to generate an early warning message package based on the ranked optimization work orders and push it to a preset operation and maintenance terminal or development alarm group.

[0016] A third aspect of the present invention provides an SQL performance detection and early warning device, comprising: a memory and at least one processor, wherein the memory stores computer-readable instructions, and the memory and the at least one processor are interconnected via a circuit; the at least one processor invokes the computer-readable instructions in the memory to cause the SQL performance detection and early warning device to perform the various steps of the SQL performance detection and early warning method described above.

[0017] A fourth aspect of the present invention provides a computer-readable storage medium storing computer-readable instructions that, when executed on a computer, cause the computer to perform the steps of the SQL performance detection and early warning method described above.

[0018] The technical solution provided by this invention solves the problem that traditional static thresholds cannot adapt to business fluctuations by constructing and dynamically updating SQL performance baselines based on historical load patterns, making anomaly detection more accurate and more in line with the actual operating environment. Simultaneously, by comparing the performance data of SQL statements collected in real time from the GreenPlum database with the dynamic baseline and generating performance degradation profiles for abnormal SQL statements, performance bottlenecks can be fundamentally located, rather than merely superficial phenomena, improving the depth and accuracy of problem diagnosis. Furthermore, by automatically generating optimization work orders through an optimization rule knowledge base, performance optimization suggestions are standardized and operable, reducing manual analysis costs. At the same time, historical effect data is introduced for benefit pre-assessment and risk analysis, and work orders are prioritized, allowing optimization resources to focus on tasks with the greatest benefit and lowest risk, significantly improving the overall efficiency of optimization implementation. In addition, optimization suggestions are pushed to operation and development terminals through structured early warning message packages, enabling real-time feedback and rapid response to performance issues, forming a complete performance management closed loop. Attached Figure Description

[0019] Figure 1 This is a first flowchart of the SQL performance detection and early warning method provided in an embodiment of the present invention; Figure 2 This is a second flowchart of the SQL performance detection and early warning method provided in this embodiment of the invention; Figure 3 This is a third flowchart of the SQL performance detection and early warning method provided in the embodiments of the present invention; Figure 4 This is a fourth flowchart of the SQL performance detection and early warning method provided in this embodiment of the invention; Figure 5 This is the fifth flowchart of the SQL performance detection and early warning method provided in the embodiments of the present invention; Figure 6 This is the sixth flowchart of the SQL performance detection and early warning method provided in this embodiment of the invention; Figure 7 This is a schematic diagram of the SQL performance detection and early warning device provided in an embodiment of the present invention; Figure 8 This is a schematic diagram of the SQL performance detection and early warning device provided in an embodiment of the present invention. Detailed Implementation

[0020] The terms “first,” “second,” “third,” “fourth,” etc. (if present) in the specification, claims, and accompanying drawings of this invention are used to distinguish similar objects and are not necessarily used to describe a specific order or sequence. It should be understood that such data can be interchanged where appropriate so that the embodiments described herein can be implemented in orders other than those illustrated or described herein. Furthermore, the terms “comprising” or “having,” and any variations thereof, are intended to cover a non-exclusive inclusion; for example, a process, method, system, product, or apparatus that includes a series of steps or units is not necessarily limited to those steps or units explicitly listed, but may include other steps or units not explicitly listed or inherent to such processes, methods, products, or apparatus.

[0021] For ease of understanding, the specific process of the embodiments of the present invention is described below. Please refer to [link / reference]. Figure 1 The first embodiment of the SQL performance detection and early warning method in this invention includes: S101. Build and dynamically update the SQL performance baseline based on historical load patterns, and at the same time, obtain the performance data of multiple SQL statements in the GreenPlum database.

[0022] It is understood that the executing entity of this invention can be an SQL performance detection and early warning device, or it can be a terminal or a server; no specific limitation is made here. This embodiment of the invention will be described using a server as the executing entity as an example.

[0023] In this embodiment, historical SQL execution data is first analyzed in depth to identify performance patterns under different business periods and load types, and SQL performance baselines are automatically calculated based on these patterns. The baseline is not fixed but is updated periodically according to the latest system load conditions to ensure the accuracy of anomaly detection. Simultaneously, key performance indicators during the execution of multiple SQL statements are obtained in real time through Greenplum database views, log interfaces, and other channels. These indicators include execution time, CPU consumption, number of rows scanned, and number of rows returned. A complete performance data snapshot is then formed by combining this data with the load conditions at the time of execution.

[0024] S102. The performance data of each SQL statement is compared with the SQL performance baseline to identify multiple abnormal SQL statements. Each abnormal SQL statement is analyzed to generate a performance degradation profile corresponding to each abnormal SQL statement. The performance degradation profile includes at least one performance bottleneck.

[0025] In this embodiment, the performance data of each SQL statement is compared with a dynamic baseline from multiple dimensions, including execution time deviation, resource consumption fluctuation, and execution plan stability. When a certain indicator exceeds the baseline range or exhibits abnormal changes, it is marked as an abnormal SQL statement. Subsequently, the abnormal SQL statement is analyzed in depth, including parsing the execution plan structure, evaluating operator costs, and checking data distribution to determine the specific source of the performance bottleneck. At the same time, statistical information of the tables involved in the abnormal SQL statement is obtained to determine whether the statistical information is outdated or missing. Combining the execution plan analysis results and table statistical information, the performance bottleneck points (i.e., problem patterns) of the abnormal SQL statement are summarized, such as plan errors caused by inaccurate statistical information, abnormal operator costs caused by data skew, and performance degradation caused by unnecessary redistribution. Finally, a structured performance degradation profile is generated, clearly presenting the root cause of the abnormal SQL statement and providing accurate basis for subsequent optimization.

[0026] S103. Based on the performance degradation profile, match and generate optimization work orders corresponding to the performance bottleneck points of each performance degradation profile from the preset optimization rule knowledge base.

[0027] In this embodiment, for each performance degradation profile, a pre-defined optimization rule base is used for matching based on the bottleneck characteristics within the profile. The rule base covers various optimization methods, including index optimization, SQL rewriting, statistics updates, and table structure adjustments. Each rule includes triggering conditions, applicable scenarios, and corresponding optimization actions. Based on the matching results, executable optimization suggestions are automatically generated, such as DDL statements for creating indexes, rewritten SQL examples, and statistics update commands. Simultaneously, implementation instructions are generated for each optimization suggestion, including expected effects, scope of impact, implementation steps, and corresponding rollback solutions. Integrating optimization suggestions, implementation instructions, and rollback solutions into standardized optimization work orders allows operations personnel to clearly understand the optimization content and operational steps, improving the efficiency and security of optimization implementation.

[0028] S104. Based on historical performance data, conduct a pre-assessment of benefits and an implementation risk analysis on multiple optimized work orders to obtain assessment and analysis results. Then, prioritize the multiple optimized work orders according to the assessment and analysis results to obtain the ranked optimized work orders.

[0029] In this embodiment, to ensure optimal resource allocation, historical optimization case data is used to estimate the benefits of newly generated optimization work orders. By querying the historical optimization case library, historical operation records similar to the current work order are obtained, including performance changes before and after implementation, resource consumption, etc., and the potential benefits of the current work order are estimated based on this data, such as the percentage reduction in execution time and the reduction in CPU consumption. Simultaneously, a risk assessment is performed on each work order, including storage and update overhead from index maintenance, lock blocking risks that may result from DDL operations, and the impact of table structure changes on the application. Based on the estimated benefits and risk assessment results, a preset weighted model is used to calculate the comprehensive score of each work order, and they are sorted from highest to lowest score. Optimization measures with high benefits and low risks are prioritized and recommended, improving the overall efficiency and security of optimization implementation.

[0030] S105. Optimize the work orders according to the sorting to generate early warning message packages and push them to the preset operation and maintenance terminal or development alarm group.

[0031] In this embodiment, the sorted optimization work orders are integrated into a structured early warning message package, including details of abnormal SQL statements, performance degradation profiles, optimization suggestions, estimated benefits, and risk descriptions. The message package uses a standardized format for easy parsing and display on different terminals. Subsequently, the early warning message package is pushed to a preset operation and maintenance management platform, or to the instant messaging groups of monitoring or development personnel via an API interface.

[0032] This embodiment provides an SQL performance detection and early warning method. By constructing and dynamically updating the SQL performance baseline based on historical load patterns, it solves the problem that traditional static thresholds cannot adapt to business fluctuations, making anomaly detection more accurate and more in line with the actual operating environment. Simultaneously, it compares the performance data of SQL statements collected in real time from the GreenPlum database with the dynamic baseline and generates performance degradation profiles for abnormal SQL statements, fundamentally locating performance bottlenecks rather than just superficial phenomena, thus improving the depth and accuracy of problem diagnosis. Furthermore, by automatically generating optimization work orders through an optimization rule knowledge base, it standardizes and makes performance optimization suggestions operable, reducing manual analysis costs. At the same time, it introduces historical effect data for benefit pre-assessment and risk analysis, and prioritizes work orders, allowing optimization resources to focus on tasks with the greatest benefit and lowest risk, significantly improving the overall efficiency of optimization implementation. In addition, by pushing optimization suggestions to operation and development terminals through structured early warning message packages, it achieves real-time feedback and rapid response to performance issues, forming a complete performance management closed loop.

[0033] Please see Figure 2 The second embodiment of the SQL performance detection and early warning method in this invention includes: S201. Establish SQL performance baselines for SQL execution time, CPU consumption time, and number of rows scanned according to different business periods and load types, and update the baseline parameters of the SQL performance baselines regularly based on the latest load patterns.

[0034] In this embodiment, historical SQL execution data is divided into business time periods, such as morning peak, noon peak, evening peak, and nighttime maintenance period, and the load type is classified according to indicators such as CPU utilization, IO load, and concurrency. For each time period and load type, the statistical distribution of indicators such as SQL execution time, CPU consumption, and number of rows scanned is calculated, including the mean, median, and 95th percentile, and a multi-dimensional performance baseline is established based on these statistical values. Simultaneously, an automatic baseline update mechanism is set up to periodically update the baseline parameters according to the latest load pattern to adapt to changes in business volume and data growth, ensuring that the baseline always accurately reflects the actual operating status.

[0035] S202. Real-time acquisition of performance data for multiple SQL statements in the GreenPlum database. The performance data for the SQL statements includes the complete execution plan, the actual number of rows returned, the data distribution key values, and a snapshot of the system load at the time of execution.

[0036] In this embodiment, execution information for each SQL statement is acquired in real time through system tables, views, and log interfaces provided by the GreenPlum database. In addition to traditional metrics such as execution time, CPU consumption, and number of rows scanned, the system focuses on collecting the complete execution plan tree, the actual number of rows returned, data distribution keys, and a load snapshot at the time of execution. The execution plan tree contains key information such as operator type, execution order, and data flow, serving as a crucial basis for analyzing performance bottlenecks. The data distribution keys reflect the distribution of data within the cluster, helping to identify data skew issues. The load snapshot includes the usage of node CPU, I / O, and memory resources, helping to determine the environment in which the SQL statement was executed. The data collection process employs a low-overhead design, using asynchronous collection and batch processing to avoid impacting database performance.

[0037] S203. Generate hash fingerprints for each SQL statement, and associate the hash fingerprints and performance data corresponding to each SQL statement with the SQL performance baseline of the corresponding time period and store them in the time series database.

[0038] In this embodiment, a hash fingerprint based on the syntax structure is generated for each SQL statement. By standardizing the SQL statements, such as removing spaces, comments, and parameterized constants, SQL statements with the same semantics generate the same fingerprint, thereby achieving unified identification and management of SQL statements. Subsequently, the hash fingerprint, performance data, and baseline information for the corresponding time period are correlated and stored in a time-series database. The time-series database has high write performance and efficient time-range query capabilities, supporting the rapid storage and retrieval of large-scale performance data. Through this storage method, not only can the current performance status of SQL statements be viewed in real time, but historical performance data can also be traced back to analyze the trend of SQL performance changes over time, providing long-term data support for anomaly diagnosis and optimization suggestions.

[0039] In this embodiment, by establishing multi-dimensional performance baselines according to business time periods and load types, the baselines can more accurately reflect the SQL performance characteristics under different operating scenarios, avoiding misjudgments under complex loads by a single baseline. Moreover, in the data collection phase, in addition to acquiring traditional indicators such as execution time and CPU consumption, the system also collects complete execution plans, actual returned rows, data distribution key values, and load snapshots, providing a more comprehensive and in-depth data foundation for subsequent analysis. Furthermore, by generating hash fingerprints for each SQL statement and storing them in a time-series database, the system achieves accurate tracking and historical backtracking of SQL performance data, facilitating long-term trend analysis and problem localization.

[0040] Please see Figure 3 A third embodiment of an SQL performance detection and early warning method according to the present invention includes: S301. Compare the performance data of each SQL statement with the SQL performance baseline to identify statements with sudden increases in execution time, abnormal resource consumption, or frequent changes in execution plan as abnormal SQL statements.

[0041] In this embodiment, the performance data of each SQL statement is compared with a dynamic baseline using multiple metrics, including execution time, CPU consumption, number of rows scanned, and number of rows returned. When a metric exceeds the baseline range or exhibits abnormal fluctuations, it is marked as a potentially anomalous SQL statement. Simultaneously, by monitoring changes in the execution plan, instability issues caused by changes in statistical information or optimizer selection are identified. For example, if the execution plan of a SQL statement changes multiple times within a short period, and its performance fluctuates accordingly, the system will classify it as anomalous. Based on the comprehensive multi-dimensional judgment results, the system ultimately determines a list of anomalous SQL statements and labels each anomalous SQL statement with an anomalous type, such as abnormal execution time, abnormal resource consumption, or plan instability, providing target objects for subsequent analysis.

[0042] S302. Obtain and analyze the execution plans of each of the abnormal SQL statements to obtain the execution plan analysis results corresponding to each abnormal SQL statement. The execution plan analysis results include high-cost operators, unnecessary data redistribution operations, and optimizer cost estimation distortion operators.

[0043] In this embodiment, the execution plans of each abnormal SQL statement are acquired and analyzed to obtain the execution plan analysis results corresponding to each abnormal SQL statement. The execution plan analysis results include high-cost operators, unnecessary data redistribution operations, and optimizer cost estimation distortion operators. Specifically, this includes: for each abnormal SQL statement, acquiring the execution plan of the abnormal SQL statement, identifying and extracting all operator nodes and their attribute information in the tree structure of the execution plan; based on the extracted operator nodes and their attribute information, analyzing and marking high-cost operators whose execution costs exceed a preset threshold, and identifying unnecessary data redistribution operations in the execution plan; comparing the optimizer's estimated values ​​for operator costs in the execution plan with the actual execution statistics, calculating and determining optimizer cost estimation distortion links where the cost estimation deviation rate exceeds a set threshold, and integrating high-cost operators, unnecessary data redistribution operations, and optimizer cost estimation distortion links into the execution plan analysis results.

[0044] In this embodiment, the execution plan of the abnormal SQL statement is first parsed, converting it from text or JSON format into a structured tree model. During the parsing process, the system extracts key attributes of each operator node, such as type, name, execution cost, estimated number of rows, actual number of rows, filtering conditions, and data distribution method, and establishes parent-child relationships between operators. By traversing the operator tree, the execution flow of the SQL statement can be clearly understood, including which tables the data is read from, which operators it undergoes, and how the final result is returned.

[0045] In this embodiment, the execution cost of each operator is evaluated based on the extracted operator attribute information. Execution cost typically includes CPU cost, I / O cost, network cost, etc. Operators exceeding a preset threshold are marked as high-cost operators. For example, sorting operators may cause serious performance problems if they process large amounts of data. Simultaneously, data redistribution operations, such as broadcasting, repartitioning, and hash distribution, are analyzed to determine their necessity. If data is already on the target node but redistribution still occurs, or if redistribution leads to severe data skew, it is marked as unnecessary redistribution. These identification results help operations personnel quickly pinpoint the specific location of performance bottlenecks.

[0046] In this embodiment, the cost estimation deviation rate is calculated by comparing the optimizer's estimated operator cost in the execution plan with the actual execution statistics. Operators with a cost estimation deviation rate exceeding a set threshold are considered to have cost estimation distortion. Cost estimation distortion may be caused by inaccurate statistical information, changes in data distribution, or defects in the optimizer model.

[0047] In this embodiment, high-cost operators, unnecessary redistribution operations, and cost estimation distortion are integrated into a structured execution plan analysis result. This result includes information such as the type, location, and degree of impact of each problem, providing detailed evidence for generating a performance degradation profile.

[0048] S303. Obtain the table statistics associated with the abnormal SQL statement, and combine the execution plan analysis results with the table statistics associated with the abnormal SQL statement to generate a performance degradation profile corresponding to each abnormal SQL statement. The performance degradation profile includes at least one performance bottleneck.

[0049] In this embodiment, statistical information of the tables involved in the abnormal SQL is obtained, including the number of rows, distribution, and field selectivity, and the statistical information is checked for expiration or missing information. If the statistical information is inaccurate, the optimizer may generate an incorrect execution plan, leading to performance degradation. Combining the execution plan analysis results with the table statistical information, the problem patterns of abnormal SQL are summarized, such as plan errors caused by inaccurate statistical information, abnormal operator costs caused by data skew, performance degradation caused by unnecessary redistribution, and excessively long execution times caused by high-cost operators. These performance bottlenecks are integrated into a structured performance degradation profile, which includes basic information about the abnormal SQL, the anomaly type, key performance bottlenecks, and the scope of impact, providing precise guidance for the subsequent generation of optimization work orders.

[0050] In this embodiment, by comparing multi-dimensional performance data with a dynamic baseline, various anomaly types such as sudden increases in execution time, abnormal resource consumption, or frequent changes in execution plans can be accurately identified, avoiding the limitations of judging by a single indicator. Moreover, through structured analysis of the execution plan, key bottlenecks such as high-cost operators, unnecessary data redistribution, and distorted optimizer cost estimation can be deeply explored, providing precise guidance for subsequent optimization. In addition, by combining the accuracy judgment of table statistical information, performance problems caused by outdated or missing statistical information can be further located, making the performance degradation profile more comprehensive and accurate.

[0051] Please see Figure 4 The fourth embodiment of an SQL performance detection and early warning method in this invention includes: S401. Match the performance bottlenecks in the performance degradation profile with the entries in the preset optimization rule knowledge base. The preset optimization rule knowledge base includes rules for indexing, SQL rewriting, statistical information updating, and table structure optimization.

[0052] In this embodiment, bottleneck features in the performance degradation profile are matched with a pre-defined optimization rule base. The pre-defined optimization rule base includes index optimization rules, SQL rewrite optimization rules, statistics update optimization rules, and table structure optimization rules. Each rule includes triggering conditions, applicable scenarios, optimization actions, and expected effects. For example, if the performance degradation profile shows that a certain SQL statement involves a full table scan and has highly selective filtering conditions, then an index optimization rule is matched; if the profile shows unnecessary redistribution, then an SQL rewrite rule or a table structure optimization rule is matched. This matching mechanism automatically selects the most suitable optimization strategy for the current abnormal SQL statement, improving the targeting and effectiveness of optimization suggestions.

[0053] S402. Based on the matched table structure optimization rules, generate an optimization script that includes specific operation objects and parameters. The optimization script is a DDL that can be executed directly or an example of SQL rewriting with comments.

[0054] In this embodiment, specific optimization scripts are automatically generated based on the matched optimization rules. For example, for index optimization rules, a DDL statement for creating an index is generated, including information such as the index name, field list, and index type; for SQL rewriting rules, a rewritten SQL example is generated, demonstrating how performance can be improved by adjusting query conditions and changing join methods; for statistics update rules, commands such as ANALYZE or UPDATESTATISTICS are generated.

[0055] S403. Obtain the implementation instructions and rollback scripts corresponding to the optimization scripts, and generate optimization work orders corresponding to each performance degradation profile based on the optimization scripts, rollback scripts, and implementation instructions.

[0056] In this embodiment, an implementation description is generated for each optimization script, including expected results, scope of impact, implementation steps, and precautions. The implementation description helps operations personnel understand the purpose of the optimization operation and the potential changes it may bring. Simultaneously, corresponding rollback solutions are provided, such as deleting indexes or restoring the original SQL statements, to address situations where the optimization operation fails or its effects are unsatisfactory. The optimization scripts, implementation descriptions, and rollback solutions are integrated into a standardized optimization work order. The work order uses a unified format and includes fields such as work order number, abnormal SQL information, optimization measures, estimated benefits, and risk level.

[0057] In this embodiment, by matching the performance degradation profile with the optimization rule knowledge base, optimization strategies targeting specific bottlenecks can be automatically recommended, achieving a seamless connection from problem diagnosis to optimization suggestions. Moreover, based on the matching results, directly executable optimization scripts are generated, such as index creation statements and SQL rewriting examples, which significantly reduces the operational threshold for operations and maintenance personnel. In addition, by attaching the above implementation instructions and rollback scheme, the security and traceability of optimization operations are ensured.

[0058] Please see Figure 5 The fifth embodiment of an SQL performance detection and early warning method in this invention includes: S501. Query the historical optimization case library to obtain the actual performance improvement data recorded by historical optimization operations similar to each optimization work order, and evaluate the estimated benefits of each optimization work order.

[0059] In this embodiment, the historical optimization case library is queried to obtain historical optimization operations similar to the current optimization work order. Similarity matching is performed based on dimensions such as SQL fingerprint, table structure, and optimization type. Performance data before and after the implementation of historical optimization operations, such as execution time, CPU consumption, and IO consumption, are extracted, and the performance improvement ratio is calculated to obtain the actual performance improvement data recorded for historical optimization operations similar to each optimization work order.

[0060] In this embodiment, a revenue prediction model is constructed based on a historical optimization case library. For example, a linear regression model is used, with the table data volume, initial execution time, and optimization type in the historical cases as features and the performance improvement ratio as a label for training. The corresponding features of the current optimization work order are input into the model to obtain the estimated revenue.

[0061] S502. Analyze the additional overhead and risks that may be brought about by the implementation of each optimization work order. The additional overhead and risks include index maintenance costs and DDL lock blocking risks.

[0062] In this embodiment, a risk assessment is performed on each optimization work order to identify potential additional overhead and risks. For example, while creating an index can improve query performance, it increases storage overhead and data update costs; executing DDL statements may cause table locks or blocking, affecting normal business operations; changes to table structure may affect application compatibility. A risk score is generated for each work order based on the risk type and its impact.

[0063] In this embodiment, each risk item is assigned a base score of 1-10, with the base score increasing according to the inherent severity of the risk. Then, an impact coefficient of 1.0-2.0 is set based on four dimensions: business importance, data scale, execution period, and scope of impact. The comprehensive impact coefficient is obtained by multiplying these dimension coefficients, thus quantifying the degree of risk impact. A weighted formula is then used to calculate the final score, which is finally divided into four levels: low, medium, high, and very high, corresponding to different handling strategies.

[0064] S503. Based on the estimated benefits, the additional expenses and risks, the preset benefit weighting coefficient and the additional expense and risk weighting coefficient, calculate the comprehensive score of each optimized work order, and sort the multiple optimized work orders in descending order of comprehensive score to obtain the sorted optimized work orders.

[0065] In this embodiment, based on the estimated benefits and risk assessment results, a preset weighted model is used to calculate the comprehensive score of each work order. The weighted model can be configured according to the enterprise's business needs. For example, for core businesses with high performance requirements, the benefit weight can be increased; for businesses with high stability requirements, the risk weight can be increased. Work orders are sorted from high to low according to their comprehensive scores, and optimization measures with high benefits and low risks are recommended first. The sorting results can help operations and maintenance personnel to rationally arrange the execution order of optimization tasks, ensuring that optimization resources are focused on the most valuable and safest tasks, and improving the overall efficiency and success rate of optimization implementation.

[0066] In this embodiment, by querying the historical optimization case library, the benefits of new work orders can be estimated based on the actual effects of similar optimization operations, making optimization recommendations more credible and quantifiable. Moreover, by assessing the additional overhead and risks during implementation, such as index maintenance costs and DDL lock blocking risks, potential problems can be identified in advance, avoiding negative impacts on business operations. Finally, by ranking optimization work orders through a comprehensive weight model, optimization measures with high benefits and low risks can be recommended first, ensuring that optimization resources are focused on the most valuable tasks.

[0067] Please see Figure 6 The sixth embodiment of an SQL performance detection and early warning method in this invention includes: S601. Generate an optimized work order list based on the sorted optimized work orders.

[0068] In this embodiment, an optimization work order list is generated based on the sorting results. The optimization work orders in the list are arranged from high to low according to the comprehensive score, so that maintenance personnel can quickly understand the importance of the optimization tasks and the processing order.

[0069] S602 integrates the optimization work order list, the estimated benefits of each optimization work order, additional costs and risks, the corresponding abnormal SQL statements and performance degradation profiles into a structured early warning message package.

[0070] In this embodiment, the optimization work order list, along with the corresponding abnormal SQL details, performance degradation profile, estimated benefits, and risk descriptions, are integrated into a structured early warning message package. The message package uses a standardized format, such as JSON or XML.

[0071] S603. Push the warning message package to the preset operation and maintenance terminal or development alarm group through the API interface.

[0072] In this embodiment, the alert message package is pushed to a preset operation and maintenance management platform, monitoring system, or instant messaging group of developers via an API interface. The push method can be configured according to the enterprise's needs, such as HTTP / HTTPS interface calls, message queue push, email notification, DingTalk / WeChat Work robot push, etc.

[0073] In this embodiment, the time, recipient, and status of the message push are recorded for subsequent tracking and auditing. After receiving the alert, operations and development personnel can quickly determine the severity of the problem based on the information in the message packet and take corresponding optimization measures, such as executing optimization scripts, adjusting SQL statements, and updating statistics, to achieve rapid response and closed-loop processing of performance issues.

[0074] In this embodiment, the sorted optimization work orders are integrated into a structured early warning message package, which can comprehensively present the key information of abnormal SQL, performance bottlenecks, optimization suggestions and expected benefits, enabling operation and maintenance and development personnel to quickly understand the essence of the problem; moreover, the message package is pushed to the preset operation and maintenance terminal or development alarm group through the API interface, realizing real-time notification and collaborative processing of performance problems.

[0075] The SQL performance detection and early warning method in the embodiments of the present invention has been described above. The apparatus in the embodiments of the present invention is described below. Please refer to [link / reference]. Figure 7 The implementation methods of the SQL performance detection and early warning device in this embodiment of the invention include: Module 701 is used to build and dynamically update the SQL performance baseline based on historical load patterns, and at the same time, obtain performance data of multiple SQL statements in the GreenPlum database. The comparison module 702 is used to compare the performance data of each SQL statement with the SQL performance baseline, identify multiple abnormal SQL statements, and analyze each of the abnormal SQL statements to generate a performance degradation profile corresponding to each abnormal SQL statement. The performance degradation profile includes at least one performance bottleneck point. The generation module 703 is used to match and generate optimization work orders corresponding to the performance bottleneck points of each performance degradation profile from a preset optimization rule knowledge base based on the performance degradation profile. The evaluation and analysis module 704 is used to combine historical effect data to conduct a benefit pre-evaluation and implementation risk analysis on multiple optimization work orders, obtain evaluation and analysis results, and prioritize the multiple optimization work orders according to the evaluation and analysis results to obtain the ranked optimization work orders. The push module 705 is used to generate an early warning message package based on the sorted and optimized work order, and push it to a preset operation and maintenance terminal or development alarm group.

[0076] In this embodiment, the construction module 701 includes: a construction unit 7011, used to establish SQL performance baselines for SQL execution time, CPU consumption time, and number of rows scanned according to different business periods and load types, and to periodically update the baseline parameters of the SQL performance baselines according to the latest load patterns; an acquisition unit 7012, used to acquire performance data of multiple SQL statements in the GreenPlum database in real time, wherein the performance data of the SQL statements includes the complete execution plan, the actual number of rows returned, the data distribution key value, and the system load snapshot at the time of execution; and a storage unit 7013, used to generate hash fingerprints for each of the SQL statements, and associate the hash fingerprints and performance data corresponding to each SQL statement with the SQL performance baseline of the corresponding period in the time-series database.

[0077] In this embodiment, the comparison module 702 includes: a comparison unit 7021, used to compare the performance data of each SQL statement with the SQL performance baseline, and identify statements with sudden increases in execution time, abnormal resource consumption, or frequent changes in execution plans as abnormal SQL statements; a first analysis unit 7022, used to acquire and analyze the execution plans of each of the abnormal SQL statements, and obtain the execution plan analysis results corresponding to each abnormal SQL statement, wherein the execution plan analysis results include high-cost operators, unnecessary data redistribution operations, and optimizer cost estimation distortion operators; and a first generation unit 7023, used to acquire the table statistics associated with each abnormal SQL statement, and combine the execution plan analysis results and the table statistics associated with the abnormal SQL statements to generate a performance degradation profile corresponding to each abnormal SQL statement, wherein the performance degradation profile includes at least one performance bottleneck point.

[0078] In this embodiment, the generation module 703 includes: a matching unit 7031, used to match the performance bottleneck points in the performance degradation profile with entries in a preset optimization rule knowledge base to obtain target optimization rules, wherein the preset optimization rule knowledge base includes index optimization rules, SQL rewriting optimization rules, statistical information update optimization rules, and table structure optimization rules; a second generation unit 7032, used to generate an optimization script including specific operation objects and parameters according to the target optimization rules, wherein the optimization script is a directly executable DDL or an annotated SQL rewriting example; and a third generation unit 7033, used to obtain the implementation instructions and rollback script corresponding to the optimization script, and generate optimization work orders corresponding to each performance degradation profile according to the optimization script, the rollback script, and the implementation instructions.

[0079] In this embodiment, the evaluation and analysis module 704 includes: an evaluation unit 7041, used to query the historical optimization case library, obtain the actual performance improvement data recorded by historical optimization operations similar to each optimization work order, and evaluate the estimated benefits of each optimization work order; a second analysis unit 7042, used to analyze the additional overhead and risks that may be brought about by the implementation of each optimization work order, the additional overhead and risks including index maintenance costs and DDL lock blocking risks; and a sorting unit 7043, used to calculate the comprehensive score of each optimization work order based on the estimated benefits, the additional overhead and risks, preset benefit weighting coefficients and additional overhead and risk weighting coefficients, and sort the multiple optimization work orders in descending order of comprehensive score to obtain the sorted optimization work orders.

[0080] In this embodiment, the push module 705 includes: 7051, a fourth generation unit, used to generate an optimization work order list based on the sorted optimization work orders; an integration unit 7052, used to integrate the optimization work order list, the estimated benefits of each optimization work order, additional costs and risks, corresponding abnormal SQL statements and performance degradation profiles into a structured early warning message package; and a push unit 7053, used to push the early warning message package to a preset operation and maintenance terminal or development alarm group through an API interface.

[0081] In this embodiment, by constructing and dynamically updating the SQL performance baseline based on historical load patterns, the problem of traditional static thresholds being unable to adapt to business fluctuations is solved, making anomaly detection more accurate and more in line with the actual operating environment. Simultaneously, by comparing the performance data of SQL statements collected in real-time from the GreenPlum database with the dynamic baseline and generating performance degradation profiles for abnormal SQL statements, performance bottlenecks can be fundamentally located, rather than merely superficial phenomena, improving the depth and accuracy of problem diagnosis. Furthermore, by automatically generating optimization work orders through an optimization rule knowledge base, performance optimization suggestions are standardized and operable, reducing manual analysis costs. At the same time, historical effect data is introduced for benefit pre-assessment and risk analysis, and work orders are prioritized, allowing optimization resources to focus on tasks with the greatest benefit and lowest risk, significantly improving the overall efficiency of optimization implementation. In addition, by pushing optimization suggestions to operation and development terminals through structured early warning message packages, real-time feedback and rapid response to performance issues are achieved, forming a complete performance management closed loop.

[0082] Figure 7 The structure of the SQL performance detection and early warning device shown does not constitute a limitation on the SQL performance detection and early warning device, and can implement the steps of the SQL performance detection and early warning methods provided in the above method embodiments.

[0083] above Figure 7 The SQL performance detection and early warning device in this embodiment of the invention will be described in detail from the perspective of modular functional entities. The SQL performance detection and early warning device in this embodiment of the invention will be described in detail from the perspective of hardware processing.

[0084] Figure 8 This is a schematic diagram of the structure of an SQL performance detection and early warning device provided in an embodiment of the present invention. The device 800 can vary significantly due to different configurations or performance characteristics. It may include one or more central processing units (CPUs) 810 (e.g., one or more processors) and a memory 820, and one or more storage media 830 (e.g., one or more mass storage devices) for storing application programs 833 or data 832. The memory 820 and storage media 830 can be temporary or persistent storage. The program stored in the storage media 830 may include one or more modules (not shown), each module including a series of instruction operations on the device 800. Furthermore, the processor 810 may be configured to communicate with the storage media 830 and execute the series of instruction operations in the storage media on the device 800.

[0085] Device 800 may also include one or more power supplies 840, one or more wired or wireless network interfaces 850, one or more input / output interfaces 860, and / or one or more operating systems 831, such as Windows Server, Mac OS X, Unix, Linux, FreeBSD, etc.

[0086] This invention also provides a computer-readable storage medium, which can be a non-volatile computer-readable storage medium or a volatile computer-readable storage medium. The computer-readable storage medium stores instructions that, when executed on a computer, cause the computer to perform the steps of the SQL performance detection and early warning method.

[0087] Those skilled in the art will clearly understand that, for the sake of convenience and brevity, the specific working process of the system, device, or unit described above can be referred to the corresponding process in the foregoing method embodiments, and will not be repeated here.

[0088] If the integrated unit is implemented as a software functional unit and sold or used as an independent product, it can be stored in a computer-readable storage medium. Based on this understanding, the technical solution of the present invention, in essence, or the part that contributes to the prior art, or all or part of the technical solution, can be embodied in the form of a software product. This computer software product is stored in a storage medium and includes several instructions to cause a computer device (which may be a personal computer, server, or network device, etc.) to execute all or part of the steps of the methods described in the various embodiments of the present invention. The aforementioned storage medium includes various media capable of storing program code, such as USB flash drives, portable hard drives, read-only memory (ROM), random access memory (RAM), magnetic disks, or optical disks.

[0089] The above-described embodiments are only used to illustrate the technical solutions of the present invention, and are not intended to limit it. Although the present invention has been described in detail with reference to the foregoing embodiments, those skilled in the art should understand that modifications can still be made to the technical solutions described in the foregoing embodiments, or equivalent substitutions can be made to some of the technical features. Such modifications or substitutions do not cause the essence of the corresponding technical solutions to deviate from the spirit and scope of the technical solutions of the embodiments of the present invention.

Claims

1. A method for SQL performance detection and early warning, characterized in that, The SQL performance detection and early warning method includes: Build and dynamically update the SQL performance baseline based on historical load patterns, and at the same time, obtain the performance data of multiple SQL statements in the GreenPlum database; The performance data of each SQL statement is compared with the SQL performance baseline to identify multiple abnormal SQL statements. Each abnormal SQL statement is then analyzed to generate a performance degradation profile corresponding to each abnormal SQL statement. The performance degradation profile includes at least one performance bottleneck. Based on the performance degradation profile, an optimization work order corresponding to the performance bottleneck point of each performance degradation profile is generated by matching from the preset optimization rule knowledge base. Based on historical performance data, a benefit pre-assessment and implementation risk analysis are conducted on multiple optimized work orders to obtain assessment and analysis results. Then, based on the assessment and analysis results, the multiple optimized work orders are prioritized to obtain the prioritized optimized work orders. Based on the sorting, optimize the work order to generate an early warning message package and push it to the preset operation and maintenance terminal or development alarm group.

2. The SQL performance detection and early warning method according to claim 1, characterized in that, The process involves constructing and dynamically updating the SQL performance baseline based on historical load patterns, while simultaneously acquiring performance data for multiple SQL statements from the GreenPlum database, including: Establish SQL performance baselines for SQL execution time, CPU consumption time, and number of rows scanned according to different business periods and load types, and update the baseline parameters of the SQL performance baselines regularly based on the latest load patterns. Real-time performance data of multiple SQL statements in the GreenPlum database is obtained. The performance data of the SQL statements includes the complete execution plan, the actual number of rows returned, the data distribution key values, and the system load snapshot at the time of execution. A hash fingerprint is generated for each of the SQL statements, and the hash fingerprint, performance data and SQL performance baseline of each SQL statement are associated and stored in the time series database.

3. The SQL performance detection and early warning method according to claim 1, characterized in that, The process involves comparing the performance data of each SQL statement with the SQL performance baseline to identify multiple abnormal SQL statements. Each abnormal SQL statement is then analyzed to generate a performance degradation profile corresponding to it. Each performance degradation profile includes at least one performance bottleneck, including: The performance data of each SQL statement is compared with the SQL performance baseline to identify statements with sudden increases in execution time, abnormal resource consumption, or frequent changes in execution plan as abnormal SQL statements. The execution plans of each of the abnormal SQL statements are obtained and analyzed to obtain the execution plan analysis results corresponding to each abnormal SQL statement. The execution plan analysis results include high-cost operators, unnecessary data redistribution operations, and optimizer cost estimation distortion operators. Obtain the table statistics associated with the abnormal SQL statement, and combine the execution plan analysis results with the table statistics associated with the abnormal SQL statement to generate a performance degradation profile corresponding to each abnormal SQL statement. The performance degradation profile includes at least one performance bottleneck.

4. The SQL performance detection and early warning method according to claim 3, characterized in that, The execution plans of each of the abnormal SQL statements are acquired and analyzed to obtain the execution plan analysis results corresponding to each abnormal SQL statement. The execution plan analysis results include high-cost operators, unnecessary data redistribution operations, and optimizer cost estimation distortion operators, including: For each of the aforementioned abnormal SQL statements, obtain the execution plan of the abnormal SQL statement, identify and extract all operator nodes and their attribute information in the tree structure of the execution plan; Based on the extracted operator nodes and their attribute information, high-cost operators whose execution cost exceeds a preset threshold are analyzed and marked, and unnecessary data redistribution operations in the execution plan are identified. Based on the extracted operator nodes and their attribute information, the cost estimation deviation rate between the optimizer's predicted operator cost and the actual execution statistics is calculated. Operators with cost estimation deviation rates exceeding a set threshold are selected to obtain optimizer cost estimation distortion operators. High-cost operators, unnecessary data redistribution operations, and optimizer cost estimation distortion operators are then integrated into the execution plan analysis results.

5. The SQL performance detection and early warning method according to claim 1, characterized in that, The step of matching and generating optimization work orders corresponding to the performance bottleneck points of each performance degradation profile from a preset optimization rule knowledge base, based on the performance degradation profile, includes: The performance bottlenecks in the performance degradation profile are matched with the entries in the preset optimization rule knowledge base to obtain the target optimization rules. The preset optimization rule knowledge base includes index optimization rules, SQL rewrite optimization rules, statistical information update optimization rules, and table structure optimization rules. Based on the target optimization rules, an optimization script is generated that includes specific operation objects and parameters. The optimization script is a DDL that can be executed directly or a rewritten SQL example with comments. Obtain the implementation instructions and rollback script corresponding to the optimization script, and generate optimization work orders corresponding to each performance degradation profile based on the optimization script, the rollback script and the implementation instructions.

6. The SQL performance detection and early warning method according to claim 1, characterized in that, The process involves combining historical performance data to conduct a preliminary benefit assessment and implementation risk analysis on multiple optimized work orders, obtaining assessment and analysis results. Based on these results, the optimized work orders are then prioritized to obtain the prioritized optimized work orders, including: Query the historical optimization case library to obtain the actual performance improvement data recorded by historical optimization operations similar to each of the optimization work orders, and evaluate the estimated benefits of each optimization work order; The analysis covers the potential additional overhead and risks associated with implementing each of the aforementioned optimization work orders, including index maintenance costs and DDL lock blocking risks. Based on the estimated benefits, the additional costs and risks, the preset benefit weighting coefficients and additional cost and risk weighting coefficients, the comprehensive score of each optimized work order is calculated, and the multiple optimized work orders are sorted in descending order of comprehensive score to obtain the sorted optimized work orders.

7. The SQL performance detection and early warning method according to claim 6, characterized in that, The step of generating an early warning message package based on the sorted and optimized work orders, and pushing it to a preset operation and maintenance terminal or development alarm group, includes: An optimized work order list is generated based on the sorted and optimized work orders. The optimization work order list, the estimated benefits of each optimization work order, additional costs and risks, corresponding abnormal SQL statements and performance degradation profiles are integrated into a structured early warning message package. The warning message package is pushed to the preset operation and maintenance terminal or development alarm group through the API interface.

8. An SQL performance detection and early warning device, characterized in that, include: The build module is used to build and dynamically update the SQL performance baseline based on historical load patterns, while also obtaining performance data for multiple SQL statements from the GreenPlum database. The comparison module is used to compare the performance data of each SQL statement with the SQL performance baseline, identify multiple abnormal SQL statements, and analyze each of the abnormal SQL statements to generate a performance degradation profile corresponding to each abnormal SQL statement. The performance degradation profile includes at least one performance bottleneck point. The generation module is used to match and generate optimization work orders corresponding to the performance bottleneck points of each performance degradation profile from a preset optimization rule knowledge base based on the performance degradation profile. The evaluation and analysis module is used to combine historical performance data to conduct a pre-evaluation of the benefits and an implementation risk analysis of multiple optimization work orders, obtain the evaluation and analysis results, and prioritize the multiple optimization work orders according to the evaluation and analysis results to obtain the ranked optimization work orders. The push module is used to generate early warning message packages based on the sorted and optimized work orders, and push them to preset operation and maintenance terminals or development alarm groups.

9. An SQL performance detection and early warning device, characterized in that, It includes a memory and at least one processor, wherein the memory stores computer-readable instructions; The at least one processor invokes the computer-readable instructions in the memory to perform the steps of the SQL performance detection and early warning method as described in any one of claims 1-7.

10. A computer-readable storage medium storing computer-readable instructions thereon, characterized in that, When the computer-readable instructions are executed by the processor, they implement the steps of the SQL performance detection and early warning method as described in any one of claims 1-7.

Images