SQL Pattern comparison
Compare SQL Patterns across two time windows for an AnalyticDB for MySQL cluster to identify new or changed SQL Patterns and narrow down the scope of performance troubleshooting.
Overview
SQL Pattern comparison analyzes the differences in SQL Patterns of the same AnalyticDB for MySQL cluster across two time windows. You can use this feature to:
View SQL Patterns that newly appeared in the target window.
View SQL Patterns that appeared in both windows but had increased execution counts or resource consumption in the target window.
View and rank candidate SQL Patterns across five dimensions: execution count, CPU cost, shuffle data volume, peak memory, and data read volume.
SQL Pattern comparison asynchronously generates a report that can be viewed repeatedly. The report helps narrow down the investigation scope but cannot independently prove that a SQL statement is the root cause of a cluster performance issue. To determine the root cause, you must also consider execution plans, table schemas, data distribution, business changes, and cluster monitoring data.
The feature supports creating reports, viewing report lists, viewing report details, and canceling incomplete reports.
Prerequisites
An AnalyticDB for MySQL Enterprise Edition, Basic Edition, or Data Lakehouse Edition cluster is created.
Your Alibaba Cloud account or RAM user has permissions to access the target cluster and use the SQL Pattern comparison feature.
SQL access data is available within the selected time windows. If no qualifying data exists, the report can still complete normally, but the results for the corresponding dimensions may be empty.
Limits
The feature is currently available only in the China (Shenzhen) and Singapore regions.
Comparison Time 1 and Comparison Time 2 must belong to the same AnalyticDB for MySQL cluster.
The start time of each window must be earlier than the end time. The maximum duration of a single window is 24 hours.
The two windows can be non-contiguous and of different lengths. For better comparability, use two windows of the same length and similar business cycles.
At most one incomplete report can exist for the same cluster at any time. If an existing report is in the Waiting or Generating state, you cannot create a new report.
After a report is created, you cannot modify the cluster, report type, or time windows. To adjust the conditions, create a new report.
Report results are retained for 7 days. Expired reports no longer appear in the available report list and their details cannot be viewed.
Reports are isolated by Alibaba Cloud account. RAM users under the same account share reports created by that account, provided they have cluster access permissions. Reports are not visible across different accounts.
Literals in SQL Patterns are parameterized as ?, but identifiers such as database names, table names, and column names may still be retained. Parameterization is not equivalent to full anonymization. Use and share reports in accordance with your organization's data security policies.
Key concepts
Comparison Time 1 and Comparison Time 2
Comparison Time 1: The baseline window, used to represent the period before a change or the reference period.
Comparison Time 2: The target window, used to represent the period that requires focused analysis.
Place the period with confirmed traffic, latency, or resource fluctuations in Time 2. Prioritize the following windows:
Today and the same period yesterday.
Today and the same period on the same day last week.
Equal-length periods before and after a release.
Equal-length periods before and after a traffic switch.
If the two windows have different lengths, total metrics are naturally affected by window duration. In this case, check the active-minute average, per-minute peak, and execution count together to avoid judging the degree of change based solely on totals.
Report types
Report type | Description | Scenario |
New | SQL Patterns that appeared in Time 2 but not in Time 1. | Investigate SQL statements added after a release, traffic switch, or business change. |
Changed | SQL Patterns that appeared in both windows and whose active-minute average in the current analysis dimension is higher in Time 2 than in Time 1. | Investigate increases in call volume or resource consumption of existing SQL statements. |
New does not mean abnormal. A new report only indicates the appearance relationship of SQL Patterns between the two windows. You still need to determine whether action is required based on business expectations.
The candidate set of a changed report depends on the current analysis dimension. The same SQL Pattern may appear in the execution count dimension but not in the peak memory dimension.
SQL Pattern
A SQL Pattern is a parameterized SQL template. SQL statements with the same structure but different parameter values are grouped into the same pattern for aggregated execution counts and resource consumption. Each pattern has a stable Pattern Hash that can be used to identify the same SQL template.
Analysis dimensions
Each report calculates the following five dimensions. You do not need to select dimensions when creating a report. You can switch dimensions when viewing report details.
Dimension | Description |
Execution count | The number of times the SQL pattern is executed. |
CPU cost | The operator cost of the SQL pattern, which is not equivalent to exact CPU time. |
Shuffle data volume | The amount of shuffle data generated by the SQL pattern. |
Peak memory | The peak memory consumption of the SQL pattern. |
Data read volume | The amount of data scanned by the SQL pattern. |
Degree of change
Changed reports classify the degree of change based on the active-minute average change rate in the current dimension:
Active-minute average change rate of Time 2 relative to Time 1 | Degree of change |
0% < Change rate <= 20% | Slight |
20% < Change rate <= 50% | Moderate |
50% < Change rate <= 100% | High |
Change rate > 100% | Critical |
Time 1 is 0 and Time 2 is greater than 0 | Critical (zero-baseline growth) |
Usage
Create a comparison report
Log on to the AnalyticDB for MySQL console, select a region, and go to the target cluster. In the navigation pane, choose Diagnostics and Optimization > SQL Diagnostics and Optimization. Then click the SQL Pattern Comparison tab.
Select a report type:
To focus on new SQL statements that appeared after a release or traffic switch, select New.
To focus on whether the call volume or resource consumption of existing SQL statements has increased, select Changed.
Set the start and end times for Comparison Time 1 and Comparison Time 2.
After confirming the target cluster, report type, and time windows, click Start Comparison.
Before submission, confirm the following:
Time 1 is the baseline window, and the target period for analysis is in Time 2.
Each window does not exceed 24 hours.
Use two windows of equal length that cover similar business cycles whenever possible.
After the report is created, the system returns a report ID and generates results asynchronously in the background. A successful creation does not mean the report is complete. You can check the status in the report list later. The longer the windows, the higher the query volume, and the more patterns, the longer the generation time. The system does not guarantee a fixed completion time.
If the system indicates that the cluster already has an incomplete report, wait for the existing report to complete, or cancel it if it is no longer needed. Do not repeatedly submit creation requests.
View reports and status
The report list displays reports that are visible to the current Alibaba Cloud account and have not expired.
Status | Description | Available operations |
Waiting | The report has been created and is waiting for the background task to start. | Wait or cancel. |
Generating | The background is generating results for all five dimensions. | Wait or cancel. |
Completed | The report results have been generated. | View details. |
Reports that failed to generate, were canceled, or have expired no longer appear in the available report list.
Cancel a report
Reports in the Waiting or Generating state can be canceled. Before canceling, confirm that the target cluster and report ID are correct, and that the report is no longer needed.
After a report is canceled, the report and its generated results become invalid, no longer appear in the report list, and the operation cannot be undone. Completed reports cannot be canceled and are retained until they expire.
If the result of a cancel request is unknown due to a network issue, refresh the report list to check the status before submitting another cancel request.
View and interpret report results
After a report is completed, go to the details page and select an analysis dimension. The result count represents the number of SQL Patterns under the current dimension and filter conditions, not the total across all five dimensions.
New report details
A new report displays the total, active-minute average, per-minute peak, proportion, and ranking of newly added SQL Patterns in Time 2. It does not display Time 1 metrics or change rates.
New SQL statements are not necessarily risky. We recommend that you first confirm whether they align with expected releases, traffic switches, or business changes, and then decide whether to continue analysis based on cumulative impact and per-minute peak values.
Changed report details
A changed report displays metrics for both Time 1 and Time 2, and provides change rates for the active-minute average, total, and per-minute peak values.
Whether a report includes a specific pattern and how the degree of change is classified are both based on the active-minute average increase in the current dimension. The total change rate and per-minute peak change rate are used for reference and do not affect the degree of change classification.
When the Time 1 metric value is 0 and the Time 2 value is greater than 0, the pattern is classified as zero-baseline growth and falls into the Critical degree of change. To exclude such patterns, set an upper limit for the change rate.
Active-minute average, total, and per-minute peak
The active-minute average is calculated based on the minute buckets in which the pattern actually appeared, not by dividing by the total number of minutes in the window.
The total reflects the cumulative impact within the window. Exercise caution when comparing windows of different lengths.
The per-minute peak represents the maximum minute bucket and is used to identify SQL patterns that have low frequency but high impact within a specific minute. It does not represent the maximum value of a single SQL statement.
For low-frequency or periodic SQL statements, evaluate the active-minute average, total, per-minute peak, and execution count together.
Analysis recommendations
Follow this analysis order:
Select the analysis dimension most relevant to the cluster anomaly.
Prioritize patterns with high cumulative impact while keeping an eye on low-frequency per-minute peaks.
For changed reports, check the active-minute average change rate and identify zero-baseline growth.
Use the pattern text and Pattern Hash to confirm the SQL type and business purpose.
Use execution plans, table schemas, data distribution, and business change records to verify the root cause.
The report only covers new patterns and changed patterns with growth in the current dimension. High-consumption SQL that has existed for a long time without growth between the two windows may not appear in the report. Empty results do not prove that no other performance issues exist in the cluster.
API operations
API operation | Description |
Create a SQL Pattern comparison report. | |
Query the list of reports visible to the current account. | |
Query report details. | |
Cancel a report that can be canceled. |
When calling these operations, note the following:
Time fields use the UTC minute-level format
yyyy-MM-ddTHH:mmZ, for example,2026-08-18T08:00Z.Report types use
NEWorCHANGED.Detail analysis dimensions use
QUERY_COUNT,CPU_COST,SHUFFLE_SIZE,PEAK_MEMORY, orSCAN_SIZE.Detail requests can use
IncludePatternto control whether to return the parameterized SQL Pattern text. When set tofalse, the Pattern Hash and metrics are still returned.The current version does not support the
Langparameter.
For complete request parameters, pagination methods, response parameters, and error codes, see the corresponding OpenAPI reference documentation.
FAQ
What do I do if I am prompted that the cluster already has an incomplete report?
At most one incomplete report can exist for the same cluster at any time. Look for reports in the Waiting or Generating state in the report list. Wait for them to complete, or cancel them if they are no longer needed.
Why can I not view the details immediately after creating a report?
Reports are generated asynchronously in the background. Wait until the list status changes to Completed before viewing the details.
Why does report generation take a long time?
The report needs to read SQL access data from both time windows and perform aggregation across five dimensions. The longer the windows, the higher the query volume, and the more patterns, the longer the generation time.
Why do different analysis dimensions have different result counts?
A changed report filters patterns whose active-minute average in Time 2 is higher than in Time 1 for the current dimension. Different dimensions have different growth sets, so the result counts and details may vary.
Why is the report detail empty?
This may be because no patterns match the definition for the current report type, no patterns have growth in the current dimension, or no SQL access data is available in the selected windows. An empty detail does not mean the creation failed. Check the report status, type, analysis dimension, and filter conditions first.
Why did a report disappear from the list?
The report may have been canceled, failed to generate, or exceeded the 7-day retention period. Such reports no longer appear in the available report list.
Why can I not see reports created by other accounts?
Reports are isolated by Alibaba Cloud account. The current account can only view reports created within its scope. Reports are not visible across different accounts.
Can a report directly prove that a SQL statement is the root cause of a performance issue?
No. The report is used to identify candidate SQL patterns that are new or growing. The root cause must still be confirmed by using execution plans, table schemas, data distribution, cluster monitoring, and business change records.
Feedback
When you need technical support, provide the region, cluster ID, report ID, report type, two comparison time windows, current analysis dimension, operation time, request ID, error code, error message, and screenshots. This information helps shorten the time required for issue identification.