Performance analysis and optimization suggestion system and method based on SQL (Structured Query Language) execution plan

Abstract

The invention discloses a performance analysis and optimization suggestion system and method based on an SQL (Structured Query Language) execution plan. A multi-source data acquisition sub-module of the system adopts a lightweight Agent real-time capture, performance view depth integration and full-link SQL (Structured Query Language) interception framework; the heterogeneous execution plan analysis module adapts to execution plan formats of different databases through a multi-engine analyzer, and converts the execution plan formats into a standardized directed acyclic graph and a high-dimensional vector through an intermediate representation generation sub-module; the intelligent diagnosis module is combined with an expert rule base and a model to realize accurate identification of known problems and mining of unknown anomalies and quantify risk levels; the optimization suggestion generation module generates a targeted optimization scheme based on a rule template and deep reinforcement learning, pre-estimates income and evaluates credibility; and the visualization and interaction module provides global performance overview, SQL detail analysis and personalized configuration functions. According to the system, automatic, quantifiable and cross-database unified management of SQL performance analysis and optimization is realized.

Description

Technical Field

[0001] This invention relates to the field of databases, and more specifically to a system and method for performance analysis and optimization suggestions based on SQL execution plans. Background Technology

[0002] With the rapid development of data-intensive industries such as the internet, finance, and e-commerce, data volume is exploding. As the core support of business systems, the performance, stability, and efficiency of databases directly determine business response speed and user experience. SQL (Structured Query Language), as the core interface for database interaction, has become a key bottleneck affecting system performance due to its execution efficiency. An inefficient SQL statement can lead to a sudden increase in database load, response delays, and even business interruptions. Therefore, performance analysis and optimization of SQL execution plans have become crucial for ensuring stable database operation.

[0003] However, the field of SQL performance optimization still faces many technical challenges, including:

[0004] (1) High labor costs and skill barriers. Database performance optimization relies heavily on the experience of senior database administrators (DBAs), who need to master the execution plan parsing rules, optimizer characteristics, and tuning techniques of different databases, such as MySQL, PostgreSQL, and Oracle. Such talents are scarce and have a long training cycle, while ordinary developers often do not know where to start when faced with complex execution plans.

[0005] (2) The reactive operation and maintenance model is lagging behind. Most enterprises rely on slow query logs or user complaints to discover performance problems, which is a reactive model of remediation after the fact. By the time the problem is discovered, it has often already had a substantial impact on the business. The lack of real-time monitoring and proactive early warning capabilities for SQL performance degradation makes it difficult to avoid risks in advance.

[0006] (3) The diagnostic results are highly subjective. When manually analyzing execution plans, the results are highly dependent on personal experience and subjective judgment. For the same SQL performance problem, different DBAs may give significantly different diagnostic conclusions and optimization solutions, leading to disagreements in decision-making within the team.

[0007] In summary, existing technologies for SQL performance analysis and optimization suffer from problems such as excessive reliance on manual intervention, delayed response, and inconsistent standards. Therefore, an automated, intelligent, and quantifiable SQL performance analysis and optimization system is needed. Summary of the Invention

[0008] To address the aforementioned shortcomings of existing technologies, this invention relies on a microservice architecture and, through the collaborative operation of multiple modules, constructs a fully automated and intelligent SQL performance optimization system, encompassing data collection, execution plan parsing, intelligent diagnosis, optimization suggestion generation, and visual interaction.

[0009] The primary objective of this invention is to provide a performance analysis and optimization suggestion system based on SQL execution plans, comprising a multi-source data acquisition submodule, a heterogeneous execution plan parsing module, an intelligent diagnosis module, an optimization suggestion generation module, and a visualization and interaction module;

[0010] The multi-source data acquisition submodule uses a lightweight agent for real-time capture, deep integration of performance views, and full-link SQL interception to collect slow query logs, performance metrics, and full SQL context data from different databases.

[0011] The heterogeneous execution plan parsing module adapts to different database execution plan formats through a multi-engine parser, and then converts them into standardized directed acyclic graphs and high-dimensional vectors through an intermediate representation generation submodule;

[0012] The intelligent diagnostic module integrates an expert rule base and a machine learning model to accurately identify known performance problems, uncover unknown anomalies, and quantify risk levels.

[0013] The optimization suggestion generation module generates targeted optimization solutions based on rule templates and deep reinforcement learning, and estimates the optimization benefits and evaluates the credibility of the solutions through a benefit prediction model and a credibility scoring system.

[0014] The visualization and interaction module provides a global performance overview, SQL detailed analysis, and personalized configuration functions.

[0015] Preferably, the lightweight agent's real-time capture includes listening to slow query logs through the operating system's underlying API and identifying mainstream database log formats; the mainstream databases include MySQL, PostgreSQL, and Oracle.

[0016] Preferably, the deep integration of the performance view includes connecting to the database through JDBC and ODBC standard interfaces, supporting SSL encrypted transmission and the principle of least privilege, providing two collection strategies: periodic and event-triggered, collecting core performance views of each database and server-level resource load indicators, and using incremental collection and compressed transmission mechanisms to reduce network bandwidth usage.

[0017] Preferably, the end-to-end SQL interception includes deploying an interception mechanism through a database connection pool layer or service gateway layer to synchronously collect execution context information at the application, execution, and environment dimensions.

[0018] Preferably, the heterogeneous execution plan parsing module includes a multi-engine parser and an intermediate representation generation submodule.

[0019] The multi-engine parser is designed with dedicated parsing logic for different database execution plan formats, including:

[0020] (1) The MySQL execution plan parsing sub-engine uses the row and column association parsing method to parse the tabular output, extract and map the core fields type, key, rows, filtered, and Extra, and handle the dependency relationship between subqueries and main queries;

[0021] (2) The PostgreSQL execution plan parsing sub-engine uses a recursive traversal method to parse the tree structure, extract the NodeType, Actual Rows, Actual Time, and Buffers metrics, and calculate the prediction deviation rate and cache hit rate.

[0022] (3) The Oracle execution plan parsing sub-engine combines plan tree parsing and view data supplementation to parse the plan tree output by DBMS_XPLAN and associate it with the V$SQL_PLAN view to obtain real-time execution metrics;

[0023] The intermediate representation generation submodule constructs a standardized directed acyclic graph structure containing basic attributes, operational attributes, performance attributes, and relational attributes, and uses a graph neural network to convert the directed acyclic graph into a high-dimensional vector through node feature encoding, topological relation encoding, and global feature aggregation.

[0024] Preferably, the intelligent diagnostic module includes an expert rules submodule, an anomaly detection and classification submodule, and a risk quantification and scoring submodule;

[0025] The expert rule submodule constructs an extensible rule base with a three-element structure of condition, conclusion, and action, covering common performance issues. It supports user-defined rules, online updates, and canary deployments, and regularly eliminates low-value rules based on optimization feedback data. The common performance issues include scan efficiency, index validity, join operations, sorting and aggregation, and locking and concurrency.

[0026] In the anomaly detection and classification submodule, anomaly detection adopts a variational autoencoder, which learns the feature distribution of historical normal execution plans and uses mean squared error and dynamic threshold to determine anomalies; anomaly classification adopts a gradient boosting tree and BERT fusion model, which takes the execution plan intermediate representation vector, SQL fingerprint features and metadata features as input.

[0027] The risk quantification and scoring submodule uses a lightweight Transformer architecture risk prediction regression model to output risk scores and the estimated deterioration rate of execution time. It dynamically integrates expert rules and model results with weights, and divides the risk scores into multiple levels, corresponding to different processing strategies.

[0028] Preferably, the optimization suggestion generation module includes an optimization scheme generation submodule and an intelligent benefit estimation and credibility assessment submodule;

[0029] The optimization scheme generation submodule employs rule templates and deep reinforcement learning methods. The rule templates generate directly executable scripts for high-frequency typical problems, including index optimization templates, SQL syntax optimization templates, and join operation optimization templates. The deep reinforcement learning method addresses complex atypical problems by constructing a reinforcement learning environment that includes states, actions, and rewards, and uses a proximal policy optimization algorithm to train the agent to generate combined optimization schemes.

[0030] The intelligent revenue prediction and credibility assessment submodule adopts a time-series fusion Transformer model. It takes the original SQL features, optimization scheme features, and environmental features as input, and predicts the optimized execution time, CPU utilization reduction ratio, IOPS savings, and long-term performance trend. It generates a credibility score through sample size, model error, and scenario matching degree to guide users to take different actions according to credibility.

[0031] Preferably, the visualization and interaction module includes a global performance overview panel and an SQL details analysis workbench, wherein the global performance overview panel includes a core performance indicator dashboard and an intelligent anomaly warning area;

[0032] The core performance indicator dashboard displays multi-dimensional indicators through visual charts, including line charts, box plots, heatmaps, dashboards, and area charts. The multi-dimensional indicators include slow query trends, SQL execution time distribution, server resource load, connection and lock status, core business SQL health, and data growth trends.

[0033] The intelligent anomaly warning zone displays warning records sorted by risk level, supports filtering by problem type, scope of impact, and occurrence time, and provides a warning source tracing path; the warning records include the core of the problem, key indicators, risk spread prediction, and quick operations.

[0034] The SQL details analysis workbench provides execution plan visualization through hierarchical tree diagrams, hover viewing, drill-down analysis, and pattern comparison; it traces root causes through causal relationship diagrams; and it presents suggestions through benefit-risk ratios.

[0035] The second objective of this invention is to provide a method for performance analysis and optimization suggestions based on SQL execution plans, comprising the following steps:

[0036] S1. Multi-source data collection: Real-time capture of slow query logs by lightweight agents, collection of performance metrics by deep integration with database performance views, and capture of SQL and execution context by full-link SQL interception to achieve multi-dimensional data collection;

[0037] S2. Heterogeneous Execution Plan Parsing: Utilizes a multi-engine parser to adapt to different database execution plan formats, and then converts the parsing results into a standardized directed acyclic graph and high-dimensional vector to achieve a unified representation of execution plans across databases;

[0038] S3. Intelligent Diagnosis: Identify known performance problems based on expert rule base, discover unknown anomalies through anomaly detection model, locate the root cause of anomalies using anomaly classification model, and quantify the risk level by combining risk prediction model;

[0039] S4. Optimization suggestion generation: Based on the diagnostic results, typical problem optimization solutions are generated through rule templates. Deep reinforcement learning is used to explore optimization strategies for complex problems, while the optimization benefits are estimated and the credibility of the solutions is evaluated.

[0040] S5. Visual Interaction: The database cluster performance status is displayed through the global performance overview panel, and the SQL details analysis workbench provides functions such as execution plan analysis, viewing diagnostic results, and verifying optimization solutions, realizing data visualization and user interaction.

[0041] The present invention has the following beneficial effects:

[0042] (1) Breaking down format differences and unifying data collection standards for standardized management. Addressing the issue of differing execution plan formats across mainstream databases such as MySQL, PostgreSQL, and Oracle, the heterogeneous execution plan parsing module adapts to the native formats of different databases through a multi-engine parser, and then converts them into standardized directed acyclic graphs and high-dimensional vectors via an intermediate representation generation submodule. The multi-source data collection submodule adopts a lightweight agent real-time capture, deep integration of performance views, and a full-link SQL interception architecture, compatible with slow query log formats, performance views, and SQL interception scenarios of different databases. This enables the system to achieve unified parsing and semantic understanding of execution plans across databases, eliminating the need to deploy separate analysis tools for different databases and reducing the complexity of cross-database management.

[0043] (2) Reduce reliance on manual intervention and achieve automated and intelligent diagnosis. The intelligent diagnosis module integrates an expert rule base and a machine learning model, eliminating the need for subjective experience from senior database administrators (DBAs). The expert rule base covers over 90% of common performance issues and achieves standardized identification through a three-element structure of condition, conclusion, and action. The anomaly detection model can uncover hidden anomalies not covered by the rules, and the anomaly classification model can accurately locate the root cause. Ordinary developers do not need to master complex execution plan parsing skills to obtain accurate diagnostic results through the system, solving the industry pain points of talent scarcity and long training cycles.

[0044] (3) The optimization suggestion generation module generates solutions based on diagnostic results through a dual-path approach of rule templates and deep reinforcement learning. For high-frequency typical problems, rule templates can automatically generate directly executable scripts. For complex atypical problems, the deep reinforcement learning agent can explore combinations of subqueries, temporary table indexes, and adjustments to the join order. The benefit prediction model accurately estimates the execution time, CPU utilization reduction rate, and long-term performance trend after optimization; the credibility scoring system generates scores based on historical case sample size, model error, and scenario matching degree, allowing users to clearly understand the optimization value and risks, and avoid resource waste or business impact caused by blindly implementing solutions.

[0045] The present invention provides a performance analysis and optimization suggestion system based on SQL execution plans, which demonstrates technical advantages in multiple dimensions such as technology, efficiency, cost, and business support. Attached Figure Description

[0046] Figure 1 This is a diagram illustrating the method steps of an embodiment of the present invention. Detailed Implementation

[0047] The technical solutions of the embodiments of the present invention will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of the present invention, and not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of the present invention.

[0048] Example 1

[0049] This embodiment discloses a performance analysis and optimization suggestion system based on SQL execution plans. It adopts a microservice architecture and, through the collaborative work of multiple modules, achieves full automation and intelligence throughout the entire process, from SQL data collection and execution plan parsing to intelligent diagnosis, optimization suggestion generation, and effect feedback. The system includes the following modules and sub-modules:

[0050] I. Multi-Source Data Acquisition Submodule. Addressing the characteristics of scattered data, diverse formats, and varying real-time requirements in heterogeneous database environments, this module employs a lightweight agent-based real-time capture, deep integration of performance views, and end-to-end SQL interception architecture to achieve comprehensive capture of core database operational data.

[0051] (1) Lightweight Agent Real-time Capture. A non-intrusive Agent deployment scheme is adopted for slow query logs in the database, which are performance data carriers.

[0052] The agent is deployed on the database server in a user-space lightweight process mode. It uses underlying operating system APIs, such as Linux's inotify mechanism and Windows' ReadDirectoryChangesW function, to achieve read-only monitoring of slow query log files without modifying database configuration files or injecting any plugins, thus avoiding interference with the database kernel's operating logic.

[0053] It is compatible with logs from multiple databases and has a built-in parsing engine for slow query log formats of mainstream databases, which can accurately identify and extract them.

[0054] (a) MySQL: The core fields in mysqlslow.log, such as Query_time (execution time), Lock_time (lock wait time), and Rows_sent (number of rows returned), support the differentiation of different SQL types such as SELECT "UPDATE" and "DELETE".

[0055] (b) PostgreSQL: Slow query logs triggered by the log_min_duration_statement parameter, which parse information such as duration (execution time), statement (SQL text), and user (execution user).

[0056] (c) Oracle: Records marked as “long running query” in the alert log, combined with the v$session view to supplement the execution context.

[0057] (d) Other databases: such as SQL Server's "Slow Query Trace Log" and MongoDB's profile log, all provide adapted parsing rules.

[0058] Strict control over resource consumption is implemented, and a lightweight edge computing design is adopted. The Agent process resides in memory ≤50MB, and the parsing thread is activated only when the log file changes through an on-demand wake-up mechanism.

[0059] (2) Deep integration of database performance view. By directly connecting to the built-in performance view of the database through a standardized interface, the periodic or event-triggered collection of multi-dimensional indicators can be realized, which can supplement the database operation details not covered by the slow query log, such as the cumulative impact of high-frequency fast SQL and system-level resource bottlenecks.

[0060] A secure and stable connection mechanism is used: Connections to the database are established using the JDBC / ODBC standard interface, supporting SSL encrypted transmission and the principle of least privilege, granting only the SELECT permission for performance views. The connection pool is implemented using HikariCP, with a configurable maximum number of connections (default ≤ 5) and an automatic timeout release mechanism to reduce the consumption of database connection resources. Two data collection strategies are available:

[0061] (a) Periodic data collection: Supports user-defined data collection granularity, adjustable from 1 to 60 seconds. By default, core metrics are collected every 10 seconds to balance real-time performance and resource consumption.

[0062] (b) Event-triggered collection: When the database key indicators are detected to exceed the threshold, such as CPU utilization ≥80% or lock wait count ≥100 times / second, high-frequency collection is automatically triggered, such as increasing the granularity to 1 second to capture the performance characteristics of abnormal moments.

[0063] It provides comprehensive coverage of database metrics, taking into account the performance view characteristics of different databases.

[0064] (a) MySQL: Focusing on Performance Schema and sys view, collecting events_statements_summary_by_digest (average execution time, number of executions, and error rate of SQL statements), table_io_waits_summary_by_table (number of read / write IO operations and waiting time of tables), events_waits_summary_global_by_event_name (cumulative time of events such as lock waits and IO waits).

[0065] (b) PostgreSQL: Relying on the pre-enabled pg_stat_statements extension to collect queryid (SQL unique identifier), total_time (total execution time), mean_time (average time), and combined with pg_stat_activity to obtain the state status of active sessions, such as "idle in transaction", and wait_event (wait event).

[0066] (c) Oracle: Integrate AWR (hourly snapshot) and ASH (sample every 10 seconds) data, extract the elapsed_time (total time), executions (number of executions) corresponding to SQL_ID, as well as lock contention information such as session_state (session state) and blocking_session (blocking session ID).

[0067] (d) General metrics: Regardless of the database type, server-level resource load is collected, including CPU utilization, memory usage, disk IOPS, network throughput, etc., as the environmental context for SQL performance analysis.

[0068] An incremental acquisition and compressed transmission mechanism is adopted, which only collects indicator data that has changed compared to the previous time, and compresses the data during transmission to reduce network bandwidth usage.

[0069] (3) SQL Interception and Proxy. Deploy an interception mechanism at key nodes in the database access chain to achieve seamless capture of all SQL statements entering the database. Two interception deployment modes can be adopted:

[0070] (a) Database connection pool layer: Connection pools integrated into the application, such as Druid and HikariCP, intercept the SQL execution process through custom filters, and are suitable for Java applications.

[0071] (b) Service Gateway Layer: Deployed on the database gateway, such as Spring Cloud Gateway, MyCat, etc., it captures all SQL statements passing through the gateway through traffic mirroring or proxy forwarding, and is suitable for multi-language mixed architectures.

[0072] In addition to SQL text, complete execution context information is collected synchronously, including:

[0073] (a) Application dimension: client IP, application service name, call stack information, such as Java's StackTrace, as well as user roles and login accounts.

[0074] (b) Execution dimension: SQL type, including SELECT / INSERT / UPDATE / DELETE, transaction ID, execution start / end time, and whether it is in a transaction.

[0075] (c) Environment dimension: database connection port, database instance name, connection establishment time.

[0076] The multi-source data acquisition submodule achieves comprehensive coverage of real-time monitoring of slow query logs, in-depth extraction of performance indicators, and full capture of SQL context through the coordinated use of three acquisition methods, providing data input for subsequent stages.

[0077] II. Heterogeneous Execution Plan Parsing Module.

[0078] In database performance analysis, different database vendors use significantly different formats to describe SQL execution plans. Some, like MySQL, present them in tabular form; others, like PostgreSQL, display them in a tree structure; and still others, like Oracle's DBMS_XPLAN, rely on proprietary view outputs. These format differences make it difficult for traditional performance analysis tools to provide unified diagnostics across different databases.

[0079] This module serves as the system's format converter and semantic normalizer. It accurately interprets the execution plans of each database through a multi-engine parser, and then converts them into a common language that the system can understand through an intermediate representation generation submodule.

[0080] (1) Multi-engine parser. Parsing logic is developed to address the execution plan format characteristics of mainstream databases, ensuring accurate extraction of key semantic information from execution plans with different structures. Its design follows a three-tiered principle: structure adaptation, field mapping, and semantic verification. First, it adapts to the native format of the target database's execution plan, including tables, trees, and views; then, it maps to intermediate fields recognizable by the system; and finally, semantic verification ensures the accuracy of information extraction.

[0081] (a) MySQL Execution Plan Parsing Sub-engine. MySQL generates execution plans using the EXPLAIN or EXPLAIN ANALYZE command, outputting them in tabular form and containing several core fields such as id, select_type, table, and type.

[0082] The parsing sub-engine first performs format adaptation. For table-type output, it uses a row-column association parsing method to first identify the table header fields, and then extract detailed information of each step of the operation by row. It pays special attention to the hierarchical relationship of the "id" field. The same "id" indicates the same level, and the larger the "id" is, the deeper the subquery level is.

[0083] The parsing sub-engine parses the core fields:

[0084] The "type" field maps to an enumeration of "scan types", such as ALL → full table scan, index → ​​index scan, ref → index equality match, eq_ref → primary key / unique index match, etc., which serves as the core basis for judging query efficiency.

[0085] "key" and possible_keys: possible_keys extracts a list of candidate indices, and key determines the index actually used. If key is NULL, it is marked as "unused index".

[0086] "rows" and "filtered": "rows" extracts the estimated number of rows to be scanned, and calculates the "scan percentage" (rows / total rows) by combining it with the actual total number of rows in the table. If it exceeds 10%, it is marked as "high percentage scan". "filtered" converts the filtering percentage into "filtering efficiency" (filtered / 100). If it is less than 30%, it will prompt "inefficient filtering conditions".

[0087] The “Extra” field parses special performance indicators, such as “Using filesort” → additional sorting is required, “Usingtemporary” → temporary table is used, and “Using index condition” → index pushdown. These indicators are directly related to subsequent performance risk diagnosis.

[0088] For special scenarios, for complex queries with select_type set to SUBQUERY (subquery) or DERIVED (derived table), a nested execution plan tree is constructed based on the hierarchical relationship of the id field to ensure that the dependency relationship between the subquery and the main query is fully preserved.

[0089] (b) PostgreSQL Execution Plan Parsing Sub-engine. PostgreSQL's execution plan is generated using the EXPLAIN(ANALYZE, BUFFERS, VERBOSE) command and presented in a nested tree structure. Each node represents an operation, such as scan, join, sort, etc., and contains rich runtime statistics.

[0090] The parsing sub-engine first performs format adaptation, and then uses a recursive traversal method to parse the tree structure. Starting from the root node, usually the Result or Gather node, it traverses the child nodes layer by layer, records the type, attributes and parent-child relationships of each node, and generates a flat list of nodes, preserving the original topology.

[0091] The parsing sub-engine parses the core fields:

[0092] Node Type: Mapped to standardized operation types, such as Seq Scan → full table scan, Index Scan → index scan, Hash Join → hash join, Nested Loop → nested loop join, etc.

[0093] Execution efficiency metrics: Extract Actual Rows (actual number of rows returned) and Actual Time (actual execution time), including start and end subfields to calculate the total time, and compare it with Plan Rows (estimated number of rows) and Plan Time (estimated time) to calculate the estimation deviation rate: |actual - estimated| / estimated. If the deviation rate exceeds 50%, it is marked as "optimizer estimation is inaccurate".

[0094] Cache and IO statistics: Parse the Buffers field to extract Shared Hit (shared cache hit count), SharedRead (physical read count), Local Hit (local cache hit count), and Local Read (local physical read count), and calculate the cache hit rate Hit / (Hit+Read). If it is lower than 70%, a message "insufficient cache utilization" will be displayed.

[0095] Filtering and Join Conditions: Parse Filter (single table filtering conditions) and Join Filter (join filtering conditions), identify function operations in the conditions, such as DATE_TRUNC(time, 'day'), or non-indexed fields, to provide a basis for subsequent index failure diagnosis.

[0096] (c) Oracle Execution Plan Parsing Sub-engine. Oracle's execution plan needs to be generated through the DBMS_XPLAN package, such as DBMS_XPLAN.DISPLAY, and output in the form of a plan tree. It also relies on dynamic performance views such as V$SQL_PLAN to supplement real-time execution information.

[0097] The parsing sub-engine first performs format adaptation. Combining the dual mechanisms of plan tree parsing and view data supplementation, it first parses the plan tree structure output by DBMS_XPLAN, which includes fields such as Id, Operation, Name, and Cost. Then, it obtains real-time execution metrics, such as Elapsed Time and CPU Time, by matching and associating the SQL_ID with the V$SQL_PLAN view, thus achieving the fusion of static plan and dynamic execution data.

[0098] The parsing sub-engine parses the core fields:

[0099] Operation and Options: Combined, they determine the operation type, such as FULL TABLE SCAN, INDEXRANGE SCAN, HASH JOIN, etc.; the Options field supplements operation details, such as the ASC / DESC sorting direction of INDEX RANGE SCAN, etc.

[0100] Cost and Rows: Extract Cost (optimizer estimated cost) and Cardinality (estimated number of rows), and combine them with EXECUTIONS (execution count) from the V$SQL view to calculate the total cost Cost*EXECUTIONS.

[0101] Lock and wait information: By associating the V$SESSION and V$LOCK views, the blocking_session (blocking session ID) and wait_event (wait event name) are extracted to provide a basis for diagnosing lock contention issues.

[0102] (2) Intermediate Representation (IR) Generation Submodule. After processing by the multi-engine parser, the execution plans of different databases have been converted into structured fields, but the logical relationships between fields, such as parent-child operation dependencies and data flow directions, still differ. The core task of the intermediate representation generation submodule is to further abstract this structured information into a standardized directed acyclic graph (DAG), and convert it into a vector form that can be directly processed through graph embedding technology, thereby achieving a semantically equivalent unified representation across databases.

[0103] (a) Standardized DAG Structure Design. The DAG maps the operation flow of the execution plan using a node-edge model: each node represents a specific operation, such as scanning, joining, or sorting; edges represent the direction of data flow, from parent node to child node, indicating that data flows from the child node to the parent node. Node attributes are designed as follows to ensure coverage of all key semantics of the execution plan:

[0104] The basic attributes include: operation: a unified enumeration of operation types, such as TABLE_SCAN, INDEX_SEEK, JOIN, SORT, etc., to eliminate terminology differences between different databases; cost: the optimized operation cost is uniformly converted into a numerical type, such as Oracle's Cost being directly used, and MySQL's rows being converted into a relative cost proportionally; rows_estimated and rows_actual: record the number of rows estimated by the optimizer and the number of rows actually returned, respectively, for evaluating the accuracy of the estimation.

[0105] Operational attributes include: filter_condition: a standardized filter condition expression, such as unifying MySQL's Usingwhere and PostgreSQL's Filter into "id > 100 AND status = 'active'"; table_name and index_name: the table name and index name involved, supporting table_alias mapping for multi-table join scenarios; join_type and join_condition: the join type such as INNER, LEFT, RIGHT, etc., and the join condition such as "a.id = b.a_id", with a special mark join_order (join order) for subsequent optimization.

[0106] Performance attributes include: io_stats: I / O related statistics, such as read_count physical read counts, write_count physical write counts, buffer_hit_rate cache hit rate, etc.; cpu_time: CPU time consumed by the operation; duration: total execution time of the operation, including waiting time, such as lock wait, I / O wait, etc.

[0107] Relationship attributes include: children: a list of child node IDs, such as the left and right table scan nodes of a JOIN node; parent: the parent node ID, marking the upstream dependency of the current operation; data_flow: the data flow rate, the number of rows from the child node to the parent node, used to evaluate data processing efficiency.

[0108] (b) Graph Embedding: Converting the DAG into a vector representation that the model can understand. To enable the subsequent intelligent diagnostic model to understand the global structure and semantics of the execution plan, this submodule uses a graph neural network (GNN) to embed the DAG into a high-dimensional vector, with a default dimension of 1024. The specific process is as follows:

[0109] Node feature encoding: First, the attributes of each node are numerically encoded, such as converting operation enumerations into one-hot vectors and normalizing numerical fields such as cost, to generate node-level feature vectors (256 dimensions).

[0110] Topological relationship encoding: Through the message passing mechanism of GNN, such as the GraphSAGE algorithm, the vector of each node is fused with the features of its neighboring nodes, including parent and child nodes. For example, the vector of a JOIN node not only contains its own join_type and cost, but also incorporates the rows_actual and index_name of the scan nodes such as left / right child nodes, thereby capturing the "association between the connection operation and the upstream and downstream scan operations".

[0111] Global feature aggregation: The vectors of all nodes are weighted and aggregated through the attention mechanism to generate a 1024-dimensional global vector of the entire DAG. The weights are determined by the degree of influence of the node on the overall performance of the execution plan, such as the duration ratio. For example, if the duration ratio of a full table scan node reaches 60%, its weight in the global vector will be higher.

[0112] This graph embedding method ensures that vectors can reflect both the details of individual operations, such as index usage, and the dependencies between operations, such as the impact of connection order on performance, providing the model with semantically rich input.

[0113] By using the heterogeneous execution plan parsing module, the system successfully broke down the format barriers of heterogeneous database execution plans, providing a unified foundation for subsequent performance analysis and optimization throughout the entire process.

[0114] III. Intelligent Diagnostic Module. Through deep collaboration between expert rules and models, this module achieves accurate identification, classification, and risk quantification of SQL execution plan performance issues, including:

[0115] (1) Expert Rules Submodule. Based on the practical experience of senior database administrators (DBAs), an extensible and configurable structured rule base is built to ensure rapid review and identification of frequently occurring typical problems.

[0116] (a) The rule base construction framework adopts a three-element structure of condition, conclusion, and action to achieve standardized definition of performance issues:

[0117] Conditions are composed of multi-dimensional features and support logical operators (AND / OR / NOT) and dynamic threshold configuration. Feature sources include node attributes of the execution plan (IR) such as operation and rows_actual, table metadata such as total number of rows and index distribution, and runtime metrics such as CPU utilization and lock wait counts.

[0118] Conclusion: Includes problem types such as "low efficiency of full table scan", risk levels of low / medium / high, and scope of impact such as "affecting order query interface".

[0119] Action: Triggers subsequent processes, such as pushing optimization suggestion generation modules or recording to the issue log.

[0120] (b) Core rule classification and examples. The rule base is divided into several categories according to the type of performance problem, covering more than 90% of common bottleneck scenarios.

[0121] Scan efficiency rules, targeting issues such as full table scans and inefficient index scans, assess risk based on data volume and scan percentage, including:

[0122] Rule 1: When node operation=TABLE_SCAN AND rows_estimated / total number of rows > 10% AND total number of rows > 1 million, a "high percentage full table scan" warning is triggered. Risk level: High.

[0123] Rule 2: WHEN Node operation = INDEX_SCAN AND buffer_hit_rate < 50% AND read_count > 1000 THEN Trigger "Index Scan Physical Read Too High" warning, risk level: medium.

[0124] Index validity rules identify common scenarios that cause index invalidation, such as function operations and type conversions, including:

[0125] Rule 3: When node operation = INDEX_SCAN AND filter_condition contains function operations (such as SUBSTR, DATE_FORMAT) AND index column = function parameter THEN, an "Index Failure - Function Operation" warning is triggered. Risk level: High.

[0126] Rule 4: When there is a type conversion between node operation=INDEX_SCAN and filter_condition, such as comparing a string column with a number, a "index failure - type mismatch" warning will be triggered. Risk level: Medium.

[0127] Join operation rules address efficiency issues associated with multi-table joins, taking into account join type, table size, and data throughput. These rules include:

[0128] Rule 5: WHEN node operation=JOIN AND join_type=Nested Loop AND outer table rows_actual>10,000 AND inner table has no index THEN triggers the "Low efficiency of large table Nested Loop join" warning, risk level: high.

[0129] Rule 6: WHEN node operation=JOIN AND join_order does not conform to "small table drives large table", outer table row count > inner table row count * 10 THEN triggers an unreasonable join order warning, risk level: medium.

[0130] Sorting and aggregation rules identify issues with excessive resource consumption in sorting / aggregation operations, including:

[0131] Rule 7: When node operation=SORT AND using_filesort=TRUE AND rows_actual>100,000 THEN, a "large result set disk sorting" warning is triggered. Risk level: High.

[0132] Rule 8: When node operation=AGGREGATE AND no GROUP BY index AND rows_actual>500,000 THEN, a "Gathering without index support" warning is triggered. Risk level: Medium.

[0133] Lock and concurrency rules address concurrency issues such as lock waiting and long transaction execution times, including:

[0134] Rule 9: WHEN Transaction duration > 30 seconds AND Number of row locks held > 100 THEN Trigger a "Long Transaction Lock Contention Risk" warning, risk level: high.

[0135] Rule 10: When the number of lock waits exceeds 100 times / second and a blocking session exists, a "high-frequency lock blocking" warning will be triggered. Risk level: High.

[0136] (c) Dynamic management and expansion of rules: To adapt to changes in business and database environment, the rule base supports user customization and online updates: Users can create new rules through a visual interface, such as dragging and dropping condition components and configuring thresholds. The system automatically verifies rule syntax and conflicts, such as avoiding duplicate rules; it supports gray-scale activation of rules, which takes effect on specified database instances and is then fully promoted after small-scale verification; it automatically eliminates low-value rules on a regular basis based on optimization feedback data, such as the proportion of actual problems after a rule is hit, such as a rule that is hit 100 times but only has 5 actual problems.

[0137] (2) Anomaly Detection and Classification Submodule. Expert rules can only cover known problems, while the anomaly detection and classification submodule compensates for the limitations of the rule base by learning models to uncover complex and implicit performance patterns. This submodule includes two main functions: anomaly detection and anomaly classification, realizing a closed loop from anomaly discovery to cause location.

[0138] (a) Anomaly Detection Model: Identifying Execution Plans that Deviate from Normal Patterns. A variational autoencoder (VAE) is used as the core model. By learning the feature distribution of historical normal execution plans, abnormal execution plans that deviate from normal patterns are identified.

[0139] Training data construction: Select normal samples from the system's historical data, which meet the following conditions: execution time is stable within ±20% of the baseline, warnings are triggered irregularly, and resource consumption does not increase suddenly. Extract their 1024-dimensional IR vectors as training data, with a sample size of ≥100,000, covering different database types and SQL scenarios.

[0140] Model Training and Inference: The VAE maps the IR vector to the latent space through the encoder, and then reconstructs the vector through the decoder, learning the probability distribution of normal patterns. For a new input IR vector, the mean squared error (MSE) between it and the reconstructed vector is calculated. When MSE ≤ dynamic threshold, it is judged as normal. The threshold can be based on the 3σ principle, taking the mean MSE of historical normal samples + 3 times the standard deviation. When MSE > dynamic threshold, it is judged as abnormal, and the degree of abnormality is marked. The larger the MSE, the more significant the abnormality.

[0141] Advantages and effects: The model has an accuracy of over 92% in identifying sudden performance degradation, such as a sudden increase in the number of rows scanned due to index fragmentation or abnormal join order due to expired statistical information, with a false negative rate of <5%. It is particularly good at discovering new anomalies not covered by the rules, such as an abnormal increase in the number of nested subqueries.

[0142] (b) Anomaly Classification Model. For detected abnormal execution plans, a fusion model of gradient boosting tree (XGBoost) and BERT is used for classification, and the anomalies are assigned to several common root causes, such as indexing problems, connection efficiency, sorting overhead, etc., to provide direction for subsequent optimization.

[0143] The feature inputs include: a 1024-dimensional IR vector of the execution plan, reflecting the structure and performance characteristics of the execution plan; SQL fingerprint features such as SQL type, number of tables, and number of subquery levels, reflecting the syntax structure of the SQL; and metadata features such as table data volume, number of indexes, and server load, reflecting the execution environment.

[0144] The model architecture includes an XGBoost layer at the bottom layer to process structured features, including IR vectors and metadata, capturing numerical patterns such as the negative correlation between the number of rows scanned and the number of indexes; an upper layer using the BERT model to process SQL text, converting it into word vectors to capture semantic features, such as the impact of the syntactic difference between "NOT IN" and "LEFT JOIN" on performance; and an attention mechanism to fuse the two types of features, enhancing the weight of key features, such as increasing the weight of "Using filesort" in the sorting overhead class by 30%.

[0145] Classification performance: The model achieves an overall accuracy of 89% on a test set containing 8,000 anomaly samples. The accuracy for indexing issues and connection efficiency exceeds 95% for both categories. It also supports outputting "feature importance scores", such as the importance of "JOIN node rows_actual" accounting for 40% in a certain anomaly, which improves interpretability.

[0146] (3) Risk Quantification and Scoring Submodule. The severity of performance issues needs to be comprehensively judged by considering factors such as business impact (e.g., whether it affects core interfaces) and frequency of occurrence (e.g., executing SQL 100 times per second vs. executing SQL once per day). The risk quantification submodule converts diagnostic results into risk scores of 0-100, providing a basis for optimization priority ranking.

[0147] (a) Risk prediction regression model, which uses a lightweight Transformer architecture to predict the potential risk score of the execution plan. Inputs include: IR vector reflecting the structure of the execution plan; SQL metadata execution frequency, average execution time, business importance of related tables (e.g., the order table has a higher weight than the log table); and environmental characteristics such as database load and server resource availability.

[0148] The model outputs a risk score from 0 to 100. The higher the score, the greater the risk of performance degradation, along with an "estimated percentage of performance time deterioration," such as "If not optimized, execution time may increase by 50% within 3 days." Through training on historical optimization data and comparing performance before and after optimization, it can be seen that the model's prediction error for execution time can be controlled within 15%.

[0149] (b) Dynamic Weight Fusion Mechanism. The risk score is generated by dynamically fusion of expert rule results and model results according to weights, avoiding the limitations of a single method. Initial weights: rule results and model results each account for 50%; Real-time adjustment: The weights are dynamically updated based on the accuracy of both in historical data. For example, if the accuracy of the rule reaches 98% in the full table scan type problem, the rule weight in this type of problem will be increased to 80%; Conflict handling: When the rule and model conclusions conflict, such as the rule being judged as normal but the model being judged as abnormal, a manual review process is triggered, and the review results are used as samples to supplement the model training set.

[0150] (c) Risk Level and Application. Issues can be categorized into four levels based on the final risk score, each corresponding to a different handling strategy. Low risk (0-20 points): Only log entries are recorded; no proactive push notifications. Medium risk (21-50 points): Pushed to the DBA's to-do list; optimization is recommended as needed. High risk (51-80 points): Immediate email or WeChat alert is sent, and optimization suggestions are automatically generated. Emergency risk (81-100 points): Emergency response procedures are triggered, such as suspending non-core SQL execution, automatically executing temporary optimization scripts, etc., while simultaneously notifying a senior DBA for intervention.

[0151] By accurately identifying known problems through rules, uncovering unknown patterns through models, and quantifying risks to assist decision-making, the intelligent diagnostic module achieves comprehensive and thorough diagnosis of performance issues, providing accurate basis for generating subsequent optimization suggestions.

[0152] IV. Optimization Suggestion Generation Module: Based on the risk conclusions and execution plan characteristics of the intelligent diagnostic module, this module constructs an optimization system encompassing problem identification, solution generation, effect verification, and decision support. Through rule templates, it generates precise, actionable, and quantifiable optimization solutions, and lowers the user's decision-making threshold through benefit forecasting and visualization.

[0153] (1) Optimization scheme generation submodule. In response to the performance bottlenecks identified by the intelligent diagnostic module, such as full table scan, index failure, and low connection efficiency, this submodule generates optimization schemes covering simple to complex scenarios through a strategy of rule template fallback and intelligent innovation exploration, ensuring the relevance and feasibility of the schemes.

[0154] (a) Standardized recommendations based on rule templates, covering frequently encountered typical problems. For known issues triggered by the expert rule submodule, such as full table scans and excessive sorting overhead, predefined structured optimization templates are used. These templates, combined with execution plan details and table metadata, automatically generate directly executable scripts, ensuring the efficiency and accuracy of the solution. Templates include:

[0155] Index optimization template: When a full table scan or index failure is diagnosed, the system automatically analyzes the filter condition and table data distribution to generate optimal index recommendations: For single-field filtering, such as WHERE status = 'active' AND create_time > '2024-01-01', a combined index of "filter field + high-frequency sorting field" is preferred, such as idx_status_create_time(status, create_time), and low-discrimination fields are excluded based on the proportion of different values ​​of status. Fields with a discrimination of <10% are not recommended as index prefixes; For multi-table joins, such as JOIN condition a.id = b.a_id, the system automatically detects whether the join field is missing an index, generates the CREATE INDEX idx_b_a_id ON b(a_id) script, and marks it based on historical DML performance data of similar tables: "Index only affects read performance and may increase the latency of write operations INSERT / UPDATE by about 5%-10%".

[0156] This SQL syntax optimization template addresses issues such as low subquery efficiency and high sorting overhead by generating syntax adjustment suggestions. These include: Subquery optimization: Converting NOT IN subqueries like `SELECT * FROM a WHERE id NOT IN (SELECT a_id FROM b)` to `LEFT JOIN + IS NULL` (`SELECT a.* FROM a LEFT JOIN b ON a.id = b.a_id WHERE b.a_id IS NULL`), avoiding full table scans in subqueries; Sorting optimization: When "Usingfilesort" disk sorting is detected, it is recommended to include the sorting field in the index coverage. For example, when using `ORDER BY create_time`, expand the existing index to `(status, create_time)`, or adjust database parameters such as increasing MySQL's `sort_buffer_size` from the default 256KB to 1MB, suitable for scenarios with less than 10,000 rows to sort.

[0157] The join operation optimization template generates adjustment suggestions for issues such as unreasonable join order and large table nested loop joins. These suggestions include: Join order adjustment: When the number of rows in the outer table is more than 10 times that of the inner table, it is recommended to switch the join order so that the smaller table drives the larger table, and generate SQL that forces the specified order using STRAIGHT_JOIN, such as SELECT * FROM small_tableSTRAIGHT_JOIN large_table ON ...; Join algorithm switching: For joins between large tables where both tables have more than 1 million rows, it is recommended to change the nested loop to PostgreSQL / Oracle's Hash Join or adjust MySQL's join_buffer_size, and note that "Hash Join requires approximately 50MB of additional memory and is recommended to be executed during off-peak hours".

[0158] (b) Intelligent-driven solution exploration to solve complex and atypical problems. For diagnosed complex problems, such as scenarios not covered by rules, such as nested joins of multiple tables and abnormal subquery levels, deep reinforcement learning is used to explore optimal optimization strategies to overcome the limitations of traditional rules.

[0159] The reinforcement learning environment design includes: State: containing the DAG structure of the execution plan (IR), such as node type, join order, number of rows scanned, table metadata such as number of rows, index distribution, data skew, and database parameters such as work_mem and join_buffer_size; Action: covering three types of optimization operations: 1. Index adjustment: adding / deleting indexes, such as adding a composite index to the JOIN field, adjusting the order of index fields; 2. SQL refactoring: splitting complex subqueries, adjusting the join order, replacing aggregate functions such as changing COUNT(DISTINCT) to the subquery COUNT; 3. Parameter optimization: dynamically adjusting database running parameters, such as max_parallel_workers controlling the number of parallel query threads; Reward: the core indicator is the reduction in estimated execution time after optimization, such as a 70% reward for reducing from 10 seconds to 3 seconds, while penalizing high-risk operations, such as read performance degradation caused by deleting indexes, with a penalty coefficient of 0.3.

[0160] Model Training and Inference: The agent is trained using a near-end policy optimization algorithm, iteratively learning the optimal action sequence through a simulated environment built based on historical execution plan data. For example, for complex SQL statements with "5 nested joins + multi-level nested subqueries", the agent can generate a combined solution of "splitting 3 levels of subqueries into temporary tables + adding indexes to the temporary tables + adjusting the join order to drive the large table with the smaller table". Compared to traditional rule optimization, the average execution time is significantly reduced.

[0161] (2) Intelligent benefit prediction and credibility assessment submodule: quantifies the value of optimization and reduces decision-making risks. The effectiveness of the optimization plan needs to be verified by data quantification. This submodule predicts the performance benefits after optimization through the model and assesses the credibility of the prediction results to help users judge the actual value and execution risks of the plan.

[0162] (a) Profit forecasting model, used to accurately predict the optimization effect. A time-series fusion Transformer (TFT) model can be used to comprehensively predict the key indicators after optimization by integrating multi-dimensional features, so as to ensure the timeliness and adaptability of the prediction.

[0163] Input features include: raw SQL features: SQL fingerprint, such as operation type, number of tables, number of subquery levels, execution frequency such as number of executions per hour; optimization scheme features: scheme type is index adjustment, syntax reconstruction, or parameter optimization, the tables and fields involved, and resource consumption estimates such as the storage space of the new index; environment features: database load CPU / IO utilization, table data growth trend such as the average daily growth over the past 30 days, and business cycle such as e-commerce promotional period / normal period.

[0164] The predicted output includes core metrics: average / 95th percentile of optimized execution time, percentage reduction in CPU utilization, and IOPS savings; long-term trends: performance change curves for the next 7 / 30 days, taking into account the diminishing impact of data growth on optimization effectiveness, such as the performance gain of an index decreasing from 60% to 30% after a 10-fold increase in data volume.

[0165] Model advantages: By dynamically learning data distribution changes through a sliding window (Window Size = 30 days), the model adapts to fluctuating business scenarios. For example, during major e-commerce promotions, the model automatically corrects for the impact of surging data volume on indexing efficiency and controls execution time prediction errors.

[0166] (b) Credibility scoring, assessing the reliability of prediction results. Based on historical optimization cases and model performance, a credibility score of 0-100 is generated to guide user decision-making.

[0167] The scoring criteria include: Sample size: the number of historical optimization cases with the same fingerprint or similar SQL structure. When the sample size is >100, the base score is +30; Model error: the deviation rate between the model's predicted value and the actual value in similar cases. When the deviation rate is <10%, the base score is +40; Scenario matching degree: the similarity between the current business scenario, such as data volume and load, and historical cases. When the similarity is >80%, the base score is +30.

[0168] The scoring application includes: High confidence (80-100 points): Sufficient sample size, small model error, supports one-click execution of optimization scripts, such as adding indexes for high-frequency queries; Medium confidence (50-79 points): Small-scale verification is recommended, such as executing the solution in a test environment and comparing the actual effect with the predicted value; Low confidence (0-49 points): Execution is advised with caution, and manual review is recommended, such as in scenarios with new SQL structures or severe data skew.

[0169] By optimizing the suggestion generation module, the system achieves a seamless connection from problem diagnosis to implementable optimization, which not only ensures the scientific nature of the solution but also lowers the operational threshold for users, truly realizing data-driven SQL performance optimization.

[0170] V. Visualization and Interaction Module. Through a layered interface architecture and scenario-based interaction design, complex SQL performance data, diagnostic results, and optimization solutions are transformed into understandable and actionable information to meet the needs of different user roles, such as DBAs, development engineers, and business operations and maintenance personnel.

[0171] (1) Global Performance Overview Panel. By integrating multi-dimensional core indicators, it helps users quickly grasp the performance status of the entire database cluster and identify risk points that need to be prioritized.

[0172] (a) Core Performance Indicator Dashboard. This dashboard integrates several core metrics and updates them in real-time via dynamic charts. It may include:

[0173] SQL execution efficiency metrics, slow query trend chart: The number of slow queries in the past 1 hour / 24 hours / 7 days is displayed as a line chart, with statistics categorized by Query_time > 1s / >5s / >10s, and peak time points are marked, such as "120 slow queries over 10s occurred at 10:00, a year-on-year increase of 200%"; Average execution time distribution: The median and 95th percentile of the execution time distribution for different SQL types SELECT / INSERT / UPDATE / DELETE are displayed as a box plot, with the historical baseline marked by a red dashed line, and automatically highlighted in red when the deviation from the baseline exceeds 20%.

[0174] Resource load metrics, server resource heatmap: Displays CPU utilization, memory usage, disk IOPS, and network throughput of each database instance using a color gradient from green to yellow to red. It supports filtering by "resource type" or "instance". Clicking on an instance allows you to drill down to details; Connection and lock status: Displays the number of active connections, connection pool utilization, lock wait count by table dimension, and the number of long transactions with a duration of >30 seconds in real time. When the values ​​exceed the threshold, a flashing warning appears.

[0175] Business-related metrics, core business SQL health: For business SQL marked as "core", such as order queries and payment settlements, the success rate is displayed on the dashboard. An execution failure rate of <0.1% is considered normal, as well as the response time compliance rate; Data growth trend: The row count changes and storage space usage of core tables such as user tables and order tables are displayed using an area chart, predicting the growth curve for the next 7 days. For example, if the order table grows an average of 500,000 rows per day, it is expected to exceed 100 million rows in 3 days.

[0176] (b) Intelligent anomaly warning zone, which displays high-risk issues detected in advance, so that users do not have to sift through massive amounts of data to find key information.

[0177] Warning tiers are displayed: warnings are sorted by risk level as urgent, high, and medium. Urgent warnings with a score ≥81 are highlighted with a red banner and flashing animation, while high-risk warnings (51-80 points) are displayed with an orange card. Each warning includes: the core issue (e.g., "Full table scan of order table, executed 5000 times daily, affecting 30% of order placement interfaces"); key metrics (e.g., execution time, currently 12 seconds, baseline 3 seconds); risk propagation prediction (e.g., "If not addressed, may increase to 20 seconds within 24 hours"); and quick actions ("View Details," "Generate Optimization Suggestions," "Add to To-Do List," buttons that directly redirect to the corresponding function page).

[0178] Early warning and source tracing path: Supports filtering by "problem type" (full table scan / index failure / lock contention, etc.), "impact scope" (core business / non-core business), and "occurrence time". Clicking on a specific early warning can expand the associated historical data, such as the optimization solutions and effects of the last 3 similar problems, to assist in rapid decision-making.

[0179] (2) SQL Detail Analysis Workbench. After the user locates a specific SQL from the global panel, the SQL Detail Analysis Workbench provides in-depth analysis tools such as "execution plan parsing → problem diagnosis → optimization simulation". Through a combination of structured and visual methods, users can clearly understand the root cause and optimization path of SQL performance problems.

[0180] (a) Execution plan visualization transforms the abstract execution plan DAG structure into an interactive graph, supporting multi-dimensional decomposition and detailed exploration.

[0181] Hierarchical tree diagram display: Starting from the root node of the final result operation, the child nodes are expanded layer by layer. The size of the node is dynamically adjusted according to the proportion of execution time. The higher the proportion, the larger the node. The color is encoded according to the operation type: full table scan is red, index scan is green, and join operation is blue.

[0182] Node interaction features include: Hover View: When the mouse hovers over a node, a details pop-up window displays the core parameters of the operation (estimated / actual number of rows, CPU time, IO wait time, filtering conditions, etc.), and automatically marks anomalies, such as "estimated number of rows 1000, actual number of rows 100,000, deviation rate 9900% → optimizer statistics expired"; Drill-down Analysis: Double-clicking a node allows drilling down to the sub-processes of the operation, such as drilling down to the left / right table scan details for a JOIN node, supporting "return to previous level" and "full-screen view" operations; Comparison Mode: Supports loading historical execution plans, such as normal plans from 24 hours ago, highlighting performance degradation trajectories with differences, adding nodes in yellow, and marking nodes with increasing time consumption in red.

[0183] (b) Diagnostic conclusions and root cause analysis: The results of the intelligent diagnostic module are transformed into a structured report, which distinguishes between known problems and potential risks and provides traceable analytical evidence.

[0184] The diagnostic conclusion card includes: a rule-based diagnostic area, grouped by categories such as "scan efficiency" and "index validity," displaying the specific rules that trigger the rule, such as "Rule 1: Full table scan rate 15% → High risk level," and highlighting key features matching the rule, such as "rows_estimated / total number of rows = 18% > 10% threshold"; and an intelligent diagnostic area, displaying anomaly detection results such as "MSE = 0.85 > 0.3 threshold → Anomaly," classification conclusions such as "Root cause: Index fragmentation + unreasonable join order, confidence level 94%," and accompanying key features of interest such as "JOIN node buffer_hit_rate = 30% < 70% baseline value."

[0185] Root cause tracing path: The "cause and effect diagram" shows the chain reaction of the problem, such as "expired statistics → optimizer selects the wrong index → ​​full table scan → increased IO load → extended execution time". Each node can be clicked to view the original data, such as the last update time of statistics and the index fragmentation rate detection results.

[0186] (c) Optimization suggestions and simulation verification: The results of the optimization scheme generation module are transformed into interactive operation options, allowing users to verify the effect before execution.

[0187] Optimization suggestion list: sorted by "benefit / risk ratio", prioritizing high benefit and low risk. Each suggestion includes: a solution description, such as "create a composite index idx_status_create_time(status, create_time) to cover filtering and sorting conditions"; expected results: a comparison bar chart showing the execution time before and after optimization, estimated to be reduced from 12s to 2.5s, and the number of rows scanned reduced from 1 million to 50,000; risk warnings, such as "index space usage is approximately 500MB, write operation latency may increase by 3%-5%", and the probability of risk occurrence.

[0188] The simulation execution function allows users to click the "Simulate Optimization" button, which enables the system to execute optimization plans in a sandbox environment. This includes creating virtual indexes, refactoring SQL, generating simulated execution plans and performance data for comparison with real-world data. It also supports "parameter tuning simulation," allowing users to adjust `sort_buffer_size` from 256KB to 1MB, displaying real-time metric changes to help them select the optimal parameter combination.

[0189] This embodiment of the system uses the above modules to jointly complete the performance analysis and optimization suggestions of QL execution plan, which can be applied to data-intensive industries, reduce the cost of database performance optimization, significantly shorten the problem diagnosis time, and provide support for the stability and scalability of business systems.

[0190] Example 2

[0191] This invention provides a method for performance analysis and optimization suggestions based on SQL execution plans. In the system of Embodiment 1, for example... Figure 1 As shown, it includes the following steps:

[0192] S1. Multi-source data collection: Real-time capture of slow query logs by lightweight agents, collection of performance metrics by deep integration with database performance views, and capture of SQL and execution context by full-link SQL interception to achieve multi-dimensional data collection;

[0193] S2. Heterogeneous Execution Plan Parsing: Utilizes a multi-engine parser to adapt to different database execution plan formats, and then converts the parsing results into a standardized directed acyclic graph and high-dimensional vector to achieve a unified representation of execution plans across databases;

[0194] S3. Intelligent Diagnosis: Identify known performance problems based on expert rule base, discover unknown anomalies through anomaly detection model, locate the root cause of anomalies using anomaly classification model, and quantify the risk level by combining risk prediction model;

[0195] S4. Optimization suggestion generation: Based on the diagnostic results, typical problem optimization solutions are generated through rule templates. Deep reinforcement learning is used to explore optimization strategies for complex problems, while the optimization benefits are estimated and the credibility of the solutions is evaluated.

[0196] S5. Visual Interaction: The database cluster performance status is displayed through the global performance overview panel, and the SQL details analysis workbench provides functions such as execution plan analysis, viewing diagnostic results, and verifying optimization solutions, realizing data visualization and user interaction.

[0197] The method in this embodiment automates, intelligentizes, and quantifies SQL performance analysis and optimization.

[0198] The above description is only a preferred embodiment of the present invention. It should be noted that for those skilled in the art, several improvements and modifications can be made without departing from the technical principles of the present invention, and these improvements and modifications should also be considered within the scope of protection of the present invention.

Claims

1. A performance analysis and optimization suggestion system based on SQL execution plans, characterized in that, It includes a multi-source data acquisition submodule, a heterogeneous execution plan parsing module, an intelligent diagnosis module, an optimization suggestion generation module, and a visualization and interaction module; The multi-source data acquisition submodule uses a lightweight agent for real-time capture, deep integration of performance views, and full-link SQL interception to collect slow query logs, performance metrics, and full SQL context data from different databases. The heterogeneous execution plan parsing module adapts to different database execution plan formats through a multi-engine parser, and then converts them into standardized directed acyclic graphs and high-dimensional vectors through an intermediate representation generation submodule; The intelligent diagnostic module integrates an expert rule base and a machine learning model to accurately identify known performance problems, uncover unknown anomalies, and quantify risk levels. The optimization suggestion generation module generates targeted optimization solutions based on rule templates and deep reinforcement learning, and estimates the optimization benefits and evaluates the credibility of the solutions through a benefit prediction model and a credibility scoring system. The visualization and interaction module provides a global performance overview, SQL detailed analysis, and personalized configuration functions.

2. The performance analysis and optimization suggestion system based on SQL execution plan according to claim 1, characterized in that, The lightweight agent's real-time capture includes listening to slow query logs through the operating system's underlying API and identifying mainstream database log formats; the mainstream databases include MySQL, PostgreSQL, and Oracle.

3. The performance analysis and optimization suggestion system based on SQL execution plan according to claim 1, characterized in that, The deep integration of the performance view includes connecting to the database through JDBC and ODBC standard interfaces, supporting SSL encrypted transmission and the principle of least privilege, providing two collection strategies: periodic and event-triggered, collecting core performance views of each database and server-level resource load indicators, and using incremental collection and compressed transmission mechanisms to reduce network bandwidth consumption.

4. The performance analysis and optimization suggestion system based on SQL execution plan according to claim 1, characterized in that, The full-link SQL interception includes deploying an interception mechanism through the database connection pool layer or service gateway layer to synchronously collect execution context information at the application, execution, and environment dimensions.

5. The performance analysis and optimization suggestion system based on SQL execution plan according to claim 1, characterized in that, The heterogeneous execution plan parsing module includes a multi-engine parser and an intermediate representation generation submodule. The multi-engine parser is designed with dedicated parsing logic for different database execution plan formats, including: (1) The MySQL execution plan parsing sub-engine uses the row and column association parsing method to parse the tabular output, extract and map the core fields type, key, rows, filtered, and Extra, and handle the dependency relationship between subqueries and main queries; (2) The PostgreSQL execution plan parsing sub-engine uses a recursive traversal method to parse the tree structure, extract Node Type, Actual Rows, Actual Time, and Buffers metrics, and calculate the prediction deviation rate and cache hit rate. (3) The Oracle execution plan parsing sub-engine, combined with plan tree parsing and view data supplementation, parses the plan tree output by DBMS_XPLAN and associates it with the V$SQL_PLAN view to obtain real-time execution metrics; The intermediate representation generation submodule constructs a standardized directed acyclic graph structure containing basic attributes, operational attributes, performance attributes, and relational attributes, and uses a graph neural network to convert the directed acyclic graph into a high-dimensional vector through node feature encoding, topological relation encoding, and global feature aggregation.

6. The performance analysis and optimization suggestion system based on SQL execution plan according to claim 1, characterized in that, The intelligent diagnosis module includes an expert rules submodule, an anomaly detection and classification submodule, and a risk quantification and scoring submodule. The expert rule submodule constructs an extensible rule base with a three-element structure of condition, conclusion, and action, covering common performance issues. It supports user-defined rules, online updates, and canary deployments, and regularly eliminates low-value rules based on optimization feedback data. The common performance issues include scan efficiency, index validity, join operations, sorting and aggregation, and locking and concurrency. In the anomaly detection and classification submodule, anomaly detection adopts a variational autoencoder, which learns the feature distribution of historical normal execution plans and uses mean square error and dynamic threshold to determine anomalies. Anomaly classification employs a fusion model of gradient boosting tree and BERT, with inputs including intermediate representation vector of execution plan, SQL fingerprint features, and metadata features; The risk quantification and scoring submodule uses a lightweight Transformer architecture risk prediction regression model to output risk scores and the estimated deterioration rate of execution time. It dynamically integrates expert rules and model results with weights, and divides the risk scores into multiple levels, corresponding to different processing strategies.

7. The performance analysis and optimization suggestion system based on SQL execution plan according to claim 1, characterized in that, The optimization suggestion generation module includes an optimization scheme generation submodule and an intelligent revenue prediction and credibility assessment submodule. The optimization scheme generation submodule employs rule templates and deep reinforcement learning methods; the rule templates generate directly executable scripts for high-frequency typical problems, and the rule templates include index optimization templates, SQL syntax optimization templates, and join operation optimization templates; The deep reinforcement learning addresses complex and atypical problems by constructing a reinforcement learning environment that includes states, actions, and rewards, and using a proximal policy optimization algorithm to train the agent to generate combinatorial optimization schemes. The intelligent revenue prediction and credibility assessment submodule adopts a time-series fusion Transformer model. It takes the original SQL features, optimization scheme features, and environmental features as input, and predicts the optimized execution time, CPU utilization reduction ratio, IOPS savings, and long-term performance trend. It generates a credibility score through sample size, model error, and scenario matching degree to guide users to take different actions according to credibility.

8. The performance analysis and optimization suggestion system based on SQL execution plan according to claim 1, characterized in that, The visualization and interaction module includes a global performance overview panel and an SQL details analysis workbench. The global performance overview panel includes a core performance indicator dashboard and an intelligent anomaly warning area. The core performance indicator dashboard displays multi-dimensional indicators through visual charts, including line charts, box plots, heatmaps, dashboards, and area charts. The multi-dimensional indicators include slow query trends, SQL execution time distribution, server resource load, connection and lock status, core business SQL health, and data growth trends. The intelligent anomaly warning zone displays warning records sorted by risk level, supports filtering by problem type, scope of impact, and occurrence time, and provides a warning source tracing path; the warning records include the core of the problem, key indicators, risk spread prediction, and quick operations. The SQL details analysis workbench provides execution plan visualization through hierarchical tree diagrams, hover viewing, drill-down analysis, and pattern comparison; it traces root causes through causal relationship diagrams; and it presents suggestions through benefit-risk ratios.

9. A method for performance analysis and optimization suggestions based on SQL execution plans, characterized in that, Includes the following steps: S1. Multi-source data collection: Real-time capture of slow query logs by lightweight agents, collection of performance metrics by deep integration with database performance views, and capture of SQL and execution context by full-link SQL interception to achieve multi-dimensional data collection; S2. Heterogeneous Execution Plan Parsing: Utilizes a multi-engine parser to adapt to different database execution plan formats, and then converts the parsing results into a standardized directed acyclic graph and high-dimensional vector to achieve a unified representation of execution plans across databases; S3. Intelligent Diagnosis: Identify known performance problems based on expert rule base, discover unknown anomalies through anomaly detection model, locate the root cause of anomalies using anomaly classification model, and quantify the risk level by combining risk prediction model; S4. Optimization suggestion generation: Based on the diagnostic results, typical problem optimization solutions are generated through rule templates. Deep reinforcement learning is used to explore optimization strategies for complex problems, while the optimization benefits are estimated and the credibility of the solutions is evaluated. S5. Visual Interaction: The database cluster performance status is displayed through the global performance overview panel, and the SQL details analysis workbench provides functions such as execution plan analysis, viewing diagnostic results, and verifying optimization solutions, realizing data visualization and user interaction.

Images