This topic describes how to locate slow queries, identify the top resource-consuming SQL statements within a specified time period, and detect SQL statements with execution anomalies through dialogue.
When to use
Business personnel report that an API has become slower, and you need to determine whether it is caused by slow SQL on the database side.
The CPU or I/O utilization of the cluster has increased, and you need to locate the SQL statements that consume the most resources.
You know the SQL ID of a specific SQL statement and need to view its execution details and optimization suggestions.
Prerequisites
You have configured an OAS connection for the product to which the target cluster belongs. Data for slow SQL, TopSQL, and abnormal SQL all comes from OAS.
Procedure
Type
@in the session to select the target cluster (a tenant is optional; specifying a tenant narrows the analysis scope).Describe your analysis requirements. You can use any of the following methods:
Specify a time range, for example:
Identify the slowest SQL statements in the last hour.Provide the SQL ID directly, for example:
Analyze the SQL statement with SQL ID 8A2B...Query for SQL statements with execution anomalies, for example:
Identify SQL statements with execution anomalies in the last hour.
Press Enter to send the request and wait for the Agent to complete the analysis.
The Agent calls the diagnostic interface to obtain lists of slow SQL, TopSQL, and abnormal SQL statements, the SQL text, and information about the involved databases and tables.
View the analysis results.
The Agent outputs a list of problematic SQL statements, including performance metrics such as execution count, average time consumed, logical reads/physical reads, and number of rows returned, and provides optimization suggestions.
Best practices
When describing the time range, be as precise as possible to the window when the issue occurred (for example, "10:00 to 10:30 today"). A too-wide range will significantly increase query execution time.
Before adopting an index suggestion, evaluate the maintenance cost of the new index based on your business write volume, and execute it within the change window according to your enterprise's change process.
What to do next
To further confirm the execution plan risk of a specific SQL statement, you can ask the Agent to perform a pre-analysis. For more information, see SQL pre-analysis and tuning suggestions.
If you have obtained the execution plan and want to understand the meaning of each operator, see Execution plan interpretation.
To export the diagnostic conclusion as a report, see Export a report.