How do I use the SQL Explorer feature in the SQL Explorer and Audit module? - Database Autonomy Service

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 the SQL Explorer feature 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 SQL Explorer and Audit section of the "Overview" topic.

Overview

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 databases and regions

The SQL Explorer and Audit module is available in the new and previous versions:

New version: Compared with the SQL Explorer and Audit module of the previous version, the new version changes the underlying storage architecture 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 your database instance and the region in which the database instance resides support the new version of the SQL Explorer and Audit module, you can manually migrate the data generated by the module to the new version. For more information, see the How do I migrate the data of SQL Explorer and Audit from the previous version to the new version? section of the "FAQ" topic.
Important
- Database instances in the China (Heyuan) and China (Ulanqab) regions do not support data migration from DAS Enterprise Edition V1 to DAS Enterprise Edition V2.
- Database instances in the China (Qingdao), China (Guangzhou), China (Zhangjiakou), China (Hong Kong), and Singapore regions support data migration from DAS Enterprise Edition V1 to DAS Enterprise Edition V2 only for data that is stored no more than 30 days. To migrate data that is stored for more than 30 days from DAS Enterprise Edition V1 to DAS Enterprise Edition V2, you must modify the data storage duration before the migration. For more information, see the Modify the storage duration of data generated by SQL Explorer and Audit section of the "Overview" topic. If you reduce the storage duration of data generated by SQL Explorer and Audit, DAS immediately clears the data that is stored for a period longer than the specified storage duration. Proceed with caution.

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

Version	Database	Region
New version	ApsaraDB RDS for MySQL PolarDB for MySQL	China (Hangzhou), China (Shanghai), China (Qingdao), China (Beijing), China (Shenzhen), China (Guangzhou), China (Heyuan), China (Zhangjiakou), China (Ulanqab), China (Hong Kong), and Singapore
Previous version	ApsaraDB RDS for MySQL ApsaraDB RDS for SQL Server PolarDB for MySQL Note ApsaraDB RDS for SQL Server 2008 R2 High-availability Edition is not supported.	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	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 Enterprise 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 Enterprise Edition for the database instance, the analytical and statistical data (excluding SQL details) generated by SQL Explorer is stored only for 90 days.
The storage duration of SQL details generated by SQL Explorer is the same as that specified when you enable DAS Enterprise Edition 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 SQL Explorer and Audit 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 the data migration of a database instance. During data migration, data loss is normal for SQL Explorer.
If the load of a database instance is high, data loss may occur. Therefore, the statistics that are collected by SQL Explorer on incremental data may be inaccurate.
The maximum length of an SQL statement in an SQL log is 8,192 bytes. If the actual length of an SQL statement exceeds 8,192 bytes, the excess part is not recorded. For ApsaraDB RDS for MySQL instances and PolarDB for MySQL clusters, the maximum length of an SQL statement in SQL logs is specified by a parameter. Take note of the following items:
- If you specify the maximum length of an SQL statement to a length that is no more than 8,192 bytes, the specified length is used as the upper limit and the excess part of an SQL statement is not recorded. A prefix is added to an SQL statement during data collection and processing. As a result, the maximum length of an SQL statement in an SQL log is slightly less than the specified length.
- If you specify the maximum length of an SQL statement to a length that is greater than 8,192 bytes, the upper limit is 8,192 bytes by default. If the actual length of an SQL statement exceeds the upper limit, the excess part is not recorded. A prefix is added to an SQL statement during data collection and processing. As a result, the maximum length of an SQL statement in an SQL log is slightly less than 8,192 bytes.
Note
- For ApsaraDB RDS for MySQL instances and PolarDB for MySQL clusters that run MySQL 5.6 or 5.7, the maximum length of an SQL statement is specified by the loose_rds_audit_max_sql_size parameter.
- For ApsaraDB RDS for MySQL instances and PolarDB for MySQL clusters that run MySQL 8.0, the maximum length of an SQL statement is specified by the loose_rds_audit_log_event_buffer_size parameter.
If PgBouncer is enabled for an ApsaraDB RDS for PostgreSQL instance, SQL Explorer does not record the SQL statements that are executed by using PgBouncer.

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 time range to query data must be later than the time when DAS Enterprise Edition is enabled and must fall within the data storage duration of SQL Explorer.
- 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 longer 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 of 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 index optimization suggestions, SQL rewrite suggestions, top resource-consuming SQL statements, new SQL statements, failed SQL statements, SQL feature analysis, SQL statements with high execution variation, SQL statements with deteriorated performance, and top tables that generate the most traffic for database instances. 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 module that uses a combination of hot storage and cold storage 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 click Task list to view the task progress and historical tasks.
- If you query data generated by the SQL Explorer and Audit module more than seven days ago, you are charged for the query on a pay-as-you-go basis. For more information, see Billing.

More features

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
The section on the Execution Duration Distribution tab shows the execution durations of SQL statements on a database instance over time. A larger blue area in the chart indicates that the instance is healthier when the SQL statements are executed on the instance. Larger orange and red areas in the chart indicate that the instance is less healthy when the SQL statements are executed on the instance.
Execution Duration: On the Execution Duration tab, you can specify a time range to view the execution durations of SQL queries.
Full Request Statistics: You can view the details of SQL statements 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

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

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 a 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 timeout period specified by the interactive_timeout or wait_timeout parameter.

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

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 accounts and databases.
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

Operation	Description
GetErrorRequestSample	Asynchronously queries information about failed SQL queries in the SQL Explorer data of a database instance. You can query up to 20 failed SQL queries within a specific time range.
GetAsyncErrorRequestStatResult	Asynchronously queries the number of failed executions of SQL templates in the SQL Explorer data of a database instance.
GetAsyncErrorRequestListByCode	Asynchronously queries the IDs of SQL statements that generate a MySQL error code in the SQL Explorer data of a database instance.
GetAsyncErrorRequestStatByCode	Asynchronously queries the MySQL error codes in the SQL Explorer data of a database instance and the number of SQL queries corresponding to each error code.
GetFullRequestOriginStatByInstanceId	Queries the full request statistics in the SQL Explorer data of a database instance by access source.
GetFullRequestStatResultByInstanceId	Asynchronously queries the full request statistics in the SQL Explorer data of a database instance by SQL ID.
GetFullRequestSampleByInstanceId	Queries sample SQL statements in the SQL Explorer data of a database instance by SQL ID. You can query up to 20 sample SQL statements.
GetDasSQLLogHotData	Queries the details of the hot storage data that the SQL Explorer and Audit module generates for a database instance within the previous seven days.

Best practices

Troubleshoot slow queries