Use the SQL optimization feature - Database Autonomy Service

Database Autonomy Service (DAS) provides the SQL optimization feature to automatically deliver diagnostics results, offer optimization suggestions, and analyze expected optimization benefits. You can determine whether to adopt the suggestions based on the diagnostics results. This topic describes how to use the SQL optimization feature.

Prerequisites

The database instance that you want to manage is of one of the following types:
- ApsaraDB RDS for MySQL
- Self-managed MySQL database
- ApsaraDB MyBase for MySQL
- ApsaraDB RDS for PostgreSQL
- PolarDB for MySQL
  Note
  PolarDB for MySQL Cluster Edition that has one node is not supported.
- PolarDB-X 2.0
  Note
  A PolarDB-X 2.0 instance whose major version is 5.4.13 and minor version is in the range of 16415631 to 16504348 is not supported. For more information about how to view the version of a PolarDB-X 2.0 instance, see View and update the version of an instance.
The database instance is connected to DAS. For more information, see Connect a database instance to DAS.
The database instance is in the Normal Access state.

Limits

The SQL optimization feature is not supported for SQL queries on tables that use X-Engine.

Optimize SQL statements on the Slow Logs page

Log on to the DAS console.
In the left-side navigation pane, click Instance Monitoring.
On the page that appears, find the database instance that you want to manage and click the instance ID. The instance details page appears.
In the left-side navigation pane, choose Request Analysis > Slow Logs.
On the Slow Log Analysis tab, perform the following operations:
- In the lower part of the page, click the Slow Log Statistics tab. Then, find the SQL template that you want to optimize and click Optimize in the Actions column.
- In the lower part of the page, click the Slow Log Details tab. Then, find the SQL statement that you want to optimize and click Optimize in the Actions column.
In the SQL Diagnostic Optimization dialog box, view the SQL diagnostics results.
If you accept the SQL optimization suggestions, click Copy in the upper-right corner and paste the optimized SQL statements to the database client or Data Management (DMS) for execution. If you do not accept the suggestions, click Cancel.
Note
DAS performs SQL diagnostics based on the complexity of SQL statements, the amount of data in the table, and the database load. Suggestions may be returned in more than 20 seconds after the SQL diagnostics is performed. After the diagnostics is complete, the SQL diagnostics engine provides diagnostics results, optimization suggestions, and expected optimization benefits. You can determine whether to adopt the suggestions based on the diagnostics results.

Optimize SQL statements on the Instance Sessions page

Important

For self-managed MySQL databases, ApsaraDB for MongoDB instances, and ApsaraDB RDS for PostgreSQL instances, SQL optimization cannot be performed on the Instance Sessions page.

Log on to the DAS console.
In the left-side navigation pane, click Instance Monitoring.
On the page that appears, find the database instance that you want to manage and click the instance ID. The instance details page appears.
In the left-side navigation pane, click Instance Sessions.
On the Instance Sessions tab, select the sessions for which you want to perform SQL optimization in the Instance Sessions section and click Optimize.
In the SQL Diagnostic Optimization dialog box, view the SQL diagnostics results.
If you accept the SQL optimization suggestions, click Copy in the upper-right corner and paste the optimized SQL statements to the database client or Data Management (DMS) for execution. If you do not accept the suggestions, click Cancel.
Note
DAS performs SQL diagnostics based on the complexity of SQL statements, the amount of data in the table, and the database load. Suggestions may be returned in more than 20 seconds after the SQL diagnostics is performed. After the diagnostics is complete, the SQL diagnostics engine provides diagnostics results, optimization suggestions, and expected optimization benefits. You can determine whether to adopt the suggestions based on the diagnostics results.

View the request diagnostics history of SQL statements

Log on to the DAS console.
In the left-side navigation pane, click Instance Monitoring.
On the page that appears, find the database instance that you want to manage and click the instance ID. The instance details page appears.
In the left-side navigation pane, click Request Diagnostic History. On the page that appears, you can view the diagnostics history of SQL statements, such as the diagnostics state, start time, and diagnostics results of each SQL statement.