SQL Explorer - Database Autonomy Service - Alibaba Cloud Documentation Center

Database Autonomy Service (DAS) provides the SQL Explorer feature. You can use SQL Explorer to check the health status of SQL statements and troubleshoot performance issues. This topic describes how to use SQL Explorer in the SQL Explorer and Audit module.

Prerequisites

The database instance that you want to manage is connected to DAS and is in the Accessed state.
The SQL Explorer and Audit feature is enabled for the database instance. For more information, see the Enable the SQL Explorer and Audit feature section of the "Overview" topic.

Background information

The SQL Explorer feature records the information about all executed data query language (DQL), DML, and DDL statements. DAS obtains the information from database kernels, which consumes only a small amount of CPU resources.

Supported database types and regions

The SQL Explorer and Audit feature is available in the new and previous versions.

New version: Compared with the SQL Explorer and Audit feature of the previous version, the underlying storage architecture is changed in the new version to use a combination of hot storage and cold storage. This reduces costs and increases efficiency. For more information about the differences between the two versions, see the What are the differences between the new and previous versions of SQL Explorer and Audit? section of the "FAQ" topic.
Previous version: If the database instance and the region in which the database instance resides support the new version of the SQL Explorer and Audit feature, you can manually migrate the data generated by the feature to the new version. For more information, see the How do I migrate data of SQL Explorer and Audit from the previous version to the new version? section of the "FAQ" topic.
Important
You can migrate the data generated by the SQL Explorer and Audit feature from the previous version to the new version only for database instances that reside in the China (Shanghai) region.

The following table describes the databases and regions supported by the two versions.

Version	Database type	Region
New version	ApsaraDB RDS for MySQL High-availability Edition, Enterprise Edition, and Cluster Edition PolarDB for MySQL Single Node Edition, X-Engine Edition, Cluster Edition, and Multi-master Cluster (Database/Table) Edition	China (Shanghai), China (Beijing), China (Shenzhen), and China (Zhangjiakou)
Previous version	ApsaraDB RDS for MySQL High-availability Edition, Enterprise Edition, and Cluster Edition ApsaraDB RDS for SQL Server High-availability Edition and Cluster Edition PolarDB for MySQL Single Node Edition, X-Engine Edition, Cluster Edition, and Multi-master Cluster (Database/Table) Edition	China (Hangzhou), China (Shanghai), China (Qingdao), China (Beijing), China (Shenzhen), China (Zhangjiakou), China (Hohhot), China (Chengdu), China (Guangzhou), China (Heyuan), China (Ulanqab), China (Hong Kong), Singapore, Malaysia (Kuala Lumpur), and Indonesia (Jakarta)
	ApsaraDB RDS for PostgreSQL High-availability Edition	China (Hangzhou), China (Shanghai), China (Beijing), China (Shenzhen), China (Zhangjiakou), Singapore, Malaysia (Kuala Lumpur), and Indonesia (Jakarta)
	PolarDB-X 2.0	China (Hangzhou), China (Shanghai), China (Beijing), China (Shenzhen), and Singapore

Usage notes

After SQL Explorer is enabled for a database instance, the analytical and statistical data (excluding SQL details) generated by SQL Explorer can be stored for up to 90 days.
- If you set the storage duration to no more than 90 days when you enable DAS Professional Edition for the database instance, the analytical and statistical data (excluding SQL details) generated by SQL Explorer is stored for the specified duration.
- If you set the storage duration to more than 90 days when you enable DAS Professional Edition for the database instance, the analytical and statistical data (excluding SQL details) generated by SQL Explorer is stored for 90 days.
The storage duration of SQL details generated by SQL Explorer is the same as that specified when DAS Professional Edition is enabled for the database instance.
After you disable the SQL Explorer and Audit feature, your business is not affected. However, all data generated by SQL Explorer and Audit is cleared. We recommend that you export and save the data to your computer before you disable this feature. For more information, see the Disable the SQL Explorer and Audit feature section of the "Overview" topic.
When an SQL statement is executed on an ApsaraDB RDS for MySQL instance that is attached to PolarDB-X 1.0, multiple SQL logs are generated on the ApsaraDB RDS for MySQL instance due to sharding.
Transient connections may occur during data migration of a database instance. During data migration, data loss is normal for SQL Explorer.
An SQL statement that is recorded in the SQL logs can be up to 8,192 bytes in length. The excess part is no longer recorded, and the SQL statement ends with an ellipsis (...). You can configure parameters to specify the maximum length of an SQL statement that is recorded in an ApsaraDB RDS for MySQL instance or a PolarDB for MySQL instance.
- If you specify the maximum length of an SQL statement to a value that is less than or equal to 8192, the maximum length of the SQL statement is the specified value and the excess part is truncated and replaced with an ellipsis (...). A prefix is added to the SQL statement during data collection and processing. As a result, the maximum length of the SQL statement is slightly less than the specified parameter value.
- If you specify the maximum length of an SQL statement to a value that is greater than 8192, the maximum length of the SQL statement is 8,192 bytes. If the maximum length exceeds 8,192 bytes, the excess part is truncated and replaced with an ellipsis (...). A prefix is added to the SQL statement during data collection and processing. As a result, the maximum length of the SQL statement is slightly less than 8,192 bytes.
Note
- The loose_rds_audit_max_sql_size parameter specifies the maximum length of an SQL statement that is recorded in ApsaraDB RDS for MySQL instances or PolarDB for MySQL instances that run MySQL 5.6 or 5.7.
- The loose_rds_audit_log_event_buffer_size parameter specifies the maximum length of an SQL statement that is recorded in ApsaraDB RDS for MySQL instances or PolarDB for MySQL instances that run MySQL 8.0.

Procedure

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 > SQL Explorer and Audit. On the page that appears, click the SQL Explorer tab.
On the SQL Explorer tab, use the following features based on your business requirements.
Note
When you select a time range, make sure that the end time is later than the start time and that the interval between the start time and the end time does not exceed 24 hours. The queried time range must be within the data storage duration of SQL Explorer and must be later than the date when DAS Professional Edition is enabled for the database instance.
- Display by Time Range: Select the time range of the executed SQL statements whose SQL Explorer results you want to query. You can view the Execution Duration Distribution, Execution Duration, and Executions information about all SQL statements over the time range. You can view the details of all SQL statements over the time range and export the details in the Full Request Statistics section.
  Note
  A maximum of 1,000 SQL logs can be exported. You can use the Search feature to obtain more SQL logs within a larger time range.
- Display by Comparison: Select the date and time range of the executed SQL statements whose SQL Explorer results you want to compare. You can view the Execution Duration Distribution, Execution Duration, and Executions comparison results of all SQL statements over the time range. You can view the details about the comparison results in the Requests by Comparison section.
- Source Statistics: Select the time range of the executed SQL statements whose access sources you want to collect. Then, you can view all request sources over the time range.
- SQL Review: The SQL Review feature performs workload analysis on database instances within the diagnostic time range and the baseline time range, and performs in-depth analysis on running SQL queries in database instances. This feature displays the index optimization suggestions, top SQL, added SQL, failed SQL, SQL feature analysis, SQL with execution variation, SQL with deteriorated performance, and top tables by traffic. For more information, see SQL Review.
- Related SQL Identification: Select the metrics that you want to view and click Analysis. It takes 1 to 5 minutes to identify the SQL statements that best fit the performance of the specified metrics.
Important
- Data that was generated by the SQL Explorer and Audit feature of the new version more than seven days ago is stored in cold storage. When you analyze SQL details that were generated more than seven days ago, DAS creates a task for calculation and analysis. You can view the task progress and historical tasks on the Task list tab.
- If you query data generated by the SQL Explorer and Audit feature more than seven days ago, you are charged for the query on a pay-as-you-go basis. For more information, see Billing rules of the new version.

Description

Execution Duration Distribution: On the Execution Duration Distribution tab, you can view the distribution of execution durations of SQL queries based on the time range that you specify. The statistical data is collected every minute. The execution durations are divided into seven ranges:
- [0,1] ms: indicates that the execution duration ranges from 0 ms to 1 ms. The chart shows the percentage of SQL queries whose execution durations fall within this range.
- (1,2] ms: indicates that the execution duration is greater than 1 ms and less than or equal to 2 ms. The chart shows the percentage of SQL queries whose execution durations fall within this range.
- (2,3] ms: indicates that the execution duration is greater than 2 ms and less than or equal to 3 ms. The chart shows the percentage of SQL queries whose execution durations fall within this range.
- (3,10] ms: indicates that the execution duration is greater than 3 ms and less than or equal to 10 ms. The chart shows the percentage of SQL queries whose execution durations fall within this range.
- (10,100] ms: indicates that the execution duration is greater than 10 ms and less than or equal to 100 ms. The chart shows the percentage of SQL queries whose execution durations fall within this range.
- (0.1,1]s: indicates that the execution duration is greater than 0.1s and less than or equal to 1s. The chart shows the percentage of SQL queries whose execution durations fall within this range.
- > 1s: indicates that the execution duration is greater than 1s. The chart shows the percentage of SQL queries whose execution durations fall within this range.
Note
In the Execution Duration Distribution chart, the closer the color of the areas is to blue, the healthier the database instance is when the database instance runs SQL queries. The closer the color of the areas is to orange and red, the less healthy the database instance is when it runs SQL queries.
Execution Duration: On the Execution Duration tab, you can specify a time range to view the execution durations of SQL queries.
Full Request Statistics: In the Full Request Statistics section, you can view the SQL statement details based on the time range that you specify. The details include the SQL text, execution duration percentage, average execution duration, and execution trend for each SQL statement.
Note
You can calculate the execution duration percentage for the SQL statements that use a specific SQL template based on the following formula: Execution duration percentage = (Execution duration of the SQL statements that use the SQL template × Number of executions of the SQL statements)/(Total execution duration of all SQL statements × Total number of executions) × 100%. Higher execution duration percentages indicate that the database instance uses a larger number of MySQL resources to execute the corresponding SQL statements.
SQL ID: You can click an SQL ID to view the performance trend and sample data of the SQL statements that use the corresponding SQL template.
SQL Sample: On the SQL Sample tab, you can view the client that initiated each sample SQL request.
Note
The UTF-8 character set is used to encode SQL samples.

FAQ

Q: What does the logout! statement in the Full Request Statistics section on the SQL Explorer tab indicate?

A: The logout! statement indicates a disconnection. The execution duration of the logout! statement is the difference between the last interaction time and the time when the disconnection occurs. During the time difference, the connection remains idle. The 1158 code displayed in the Status column indicates network disconnection that may be caused by the following reasons:

The client connection times out.
The server is disconnected.
The connection to the server is reset if the duration of the connection exceeds the value specified by the interactive_timeout or wait_timeout parameter.

Q: Why does a percent sign (%) appear in the Access Source column on the Source Statistics tab of the SQL Explorer tab?

A: When you use a stored procedure, a percent sign (%) may be displayed in the Access Source column on the Source Statistics tab of the SQL Explorer tab. You can perform the following operations to reproduce this situation.

Note

In this example, the database instance is an ApsaraDB RDS for MySQL instance, the test account is test_user, and the test database is testdb.

In the ApsaraDB RDS console, create a database and a standard account and grant permissions on the database to the standard account. For more information, see Create databases and accounts for an ApsaraDB RDS for MySQL instance.
Use the test_user account to connect to the database instance by using the CLI. For more information, see Use a database client or the CLI to connect to an ApsaraDB RDS for MySQL instance.

Switch to the testdb database and create a stored procedure by executing the following statements:

-- Switch to the testdb database.
USE testdb;

-- Create a stored procedure.
DELIMITER $$
DROP PROCEDURE IF EXISTS `das` $$
CREATE DEFINER=`test_user`@`%` PROCEDURE `das`()
BEGIN
SELECT * FROM information_schema.processlist WHERE Id = CONNECTION_ID();
END $$
DELIMITER;

Use a privileged account to connect to the database instance. For more information, see Use a database client or the CLI to connect to an ApsaraDB RDS for MySQL instance.

Call the stored procedure that you created.

-- Switch to the testdb database.
USE testdb;

-- Call the stored procedure.
CALL das();

+--------+-----------+--------+--------+---------+------+-----------+-------------------------------------------------------------------------+
| ID     | USER      | HOST   | DB     | COMMAND | TIME | STATE     | INFO                                                                    |
+--------+-----------+--------+--------+---------+------+-----------+-------------------------------------------------------------------------+
| 487818 | test_user | %:2065 | testdb | Query   |    0 | executing | SELECT * FROM information_schema.processlist WHERE Id = CONNECTION_ID() |
+--------+-----------+--------+--------+---------+------+-----------+-------------------------------------------------------------------------+

Related API operations

Best practice

Troubleshoot slow queries