SQL Explorer - Database Autonomy Service - Alibaba Cloud Documentation Center

Prerequisites

The database instance has been added to DAS and its connection status is Normal Connection.
SQL Explorer and Audit is enabled for the instance. For more information, see Enable SQL Explorer and Audit.

How it works

When enabled, SQL Explorer records all DQL, DML, and DDL operations. SQL Explorer captures this information directly from the database kernel with a negligible impact on CPU usage.

Supported databases and regions

For information about the databases and regions supported by each version of DAS Enterprise Edition, see Product editions.

Usage notes

Analytical and statistical data in SQL Explorer, excluding SQL details, is retained for 30 days.
The retention period for SQL detail data in SQL Explorer is the same as the retention period configured for DAS Enterprise Edition.
Disabling SQL Explorer and Audit does not affect your services, but the action clears all stored data for this feature. Before disabling the feature, export and save the required data to a local machine. For more information, see Disable SQL Explorer and Audit.
When an SQL statement is executed on an ApsaraDB RDS for MySQL instance attached to PolarDB-X 1.0, multiple SQL logs are generated on the ApsaraDB RDS for MySQL instance due to sharding.
Data migration can cause transient disconnections, potentially leading to data loss in SQL Explorer during the migration.
When a database instance is under a very high load, a small number of records may be lost. As a result, statistics for incremental data from SQL Explorer may be inaccurate.
A parameter determines the maximum length of a recorded SQL statement. SQL Explorer does not record any part of a statement that exceeds this limit. Because a prefix is added during data collection, the actual recorded length is slightly less than the configured parameter value.
Note
- For versions 5.6 and 5.7 of ApsaraDB RDS for MySQL and PolarDB for MySQL, the recorded length is controlled by the loose_rds_audit_max_sql_size parameter.
- For version 8.0 of ApsaraDB RDS for MySQL and PolarDB for MySQL, the recorded length is controlled by the loose_rds_audit_log_event_buffer_size parameter.
For ApsaraDB RDS for PostgreSQL instances, if a PgBouncer connection pool is enabled, SQL statements executed through PgBouncer are not recorded by SQL Explorer.

Procedure

Log on to the DAS console.
In the navigation pane on the left, click Intelligent O&M Center > Instance Monitoring .
Find the target instance and click the instance ID to open the instance details page.
In the left-side navigation pane, click Request Analysis > SQL Explorer and Audit, and then click the SQL Explorer tab.
Select a feature based on your needs:

Note
When you select a time range, the end time must be later than the start time, and the interval cannot exceed one day. After you enable DAS Enterprise Edition for the instance, you can query data from any day within the SQL Explorer storage duration.
- Display by Time Range: Select a time range to view the SQL Explorer results. You can view the Execution Duration Distribution, Execution Duration, and Executions of all SQL statements within the selected time range. You can also view the details of all SQL statements in the Full Request Statistics section and export the results.
  
  Note
  You can export up to 1,000 SQL logs. To obtain more SQL logs or cover a longer time range, use the Audit feature.
- Display by Comparison: Select the time points for which you want to compare SQL Explorer results. You can view a comparison of the Execution Duration Distribution, Execution Duration, and Executions for all SQL statements. You can also view detailed comparison results in the Requests by Comparison section.
- Source Statistics: Select a time range to view statistics about SQL sources. You can view the source information for all SQL statements within the selected time range.
- SQL Review: Performs a workload analysis on a database instance for a selected time range and a baseline time range. It also performs an in-depth analysis of the SQL statements running on the instance and provides index optimization suggestions, SQL rewrite suggestions, TOP SQL, new SQL, failed SQL, SQL feature analysis, SQL with execution changes, SQL with performance degradation, and TOP traffic tables. For more information, see SQL Review.
- Related SQL Identification: Select the metrics that you want to view and click Analysis. After 1 to 5 minutes, the system identifies and displays SQL statements with trends that most closely match the selected metrics within the specified time range.
Important
- For SQL Explorer and Audit that uses hot and cold storage, the system moves data older than seven days to cold storage. When you analyze SQL detail data older than seven days, the system creates a task to recalculate and analyze the data. You can view task progress and history on the Task list page.
- Queries on SQL Explorer and Audit data older than seven days are charged on a pay-as-you-go basis. For more information, see billing details.

Results

Execution Duration Distribution: Shows the execution duration distribution of all SQL statements within the selected time range. The execution duration is divided into seven intervals, calculated once per minute:
- [0,1]ms indicates the percentage of SQL executions with a duration from 0 ms to 1 ms (inclusive).
- (1,2]ms indicates the percentage of SQL executions with a duration greater than 1 ms and up to 2 ms.
- (2,3]ms indicates the percentage of SQL executions with a duration greater than 2 ms and up to 3 ms.
- (3,10]ms indicates the percentage of SQL executions with a duration greater than 3 ms and up to 10 ms.
- (10,100]ms indicates the percentage of SQL executions with a duration greater than 10 ms and up to 100 ms.
- (0.1,1]s indicates the percentage of SQL executions with a duration greater than 0.1s and up to 1s.
- >1s indicates the percentage of SQL executions with a duration greater than 1s.
Note
The closer the Execution Duration Distribution of an instance is to blue, the healthier its SQL performance. The closer it is to orange and red, the poorer its SQL performance.
Execution Duration (SQL RT): Displays the execution duration of SQL statements within the selected time range.
Full Request Statistics: Displays the SQL text, duration percentage, average execution duration, and execution trend for each type of SQL statement within the selected time range.

Note
Duration percentage is the total execution duration of a specific SQL type as a percentage of the total execution duration of all SQL types. SQL types with a higher duration percentage consume more resources on the MySQL instance.
SQL ID: Click an SQL ID to view the performance trend and SQL samples for that statement.
SQL Sample: Use an SQL Sample to identify which client application initiated the SQL statement.

Note
SQL samples use UTF-8 encoding.

FAQ

Q: ️SQL Explorerlogout! statement mean in the Full Request Statistics section of ️SQL Explorer?

A: logout! indicates a disconnection. The duration of a logout! event is the time difference between the last interaction and the moment the logout! occurred. This duration can be interpreted as the connection idle time. A status of 1158 in the Status column indicates a network disconnection, which can be caused by the following:

The client connection timed out.
The server disconnected unexpectedly.
The server reset the connection because the interactive_timeout or wait_timeout duration was exceeded.

Q: ️SQL ExplorerSource Statistics section of ️SQL Explorer, why does a percent sign (%) appear as an Access Source?

A: This can occur when you use a stored procedure. You can reproduce this behavior by following the example below:

Note

This example uses an ApsaraDB RDS for MySQL instance, a test account named test_user, and a test database named testdb.

In the ApsaraDB RDS console, create a standard account and authorize it to access a database. For more information, see (Deprecated, redirected to "Step 1") Create a database and an account.
Use the test account to connect to the database instance from the command line. For more information, see (Deprecated, redirected to "Step 2") Connect to an ApsaraDB RDS for MySQL instance by using a client or the CLI.

Switch to the test database and create the following stored procedure.

-- Switch to the test 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 (Deprecated, redirected to "Step 2") Connect to an ApsaraDB RDS for MySQL instance by using a client or the CLI.

Call the stored procedure.

-- Switch to the test 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() |
+--------+-----------+--------+--------+---------+------+-----------+-------------------------------------------------------------------------+

Q: Why is the database name in the log list different from the one in the SQL statement?

A: The database name in the log list is retrieved from the session. The database name in the SQL statement is specified by the user and depends on the user's input or query design, such as in cross-database queries or dynamic SQL. Therefore, the two may be inconsistent.

Q: Does enabling SQL Explorer and Audit affect database performance? If so, how significant is the impact?

A: Yes, but the impact is minimal and almost imperceptible.

The resource consumption is as follows:

CPU and memory: The consumption is extremely low and can be ignored.
Storage space: This is primarily used for storing audit information. However, the SQL Explorer and Audit feature in DAS Enterprise Edition uses storage space on the DAS side and does not occupy the storage space of your database instance.
Network: No impact on network performance.
Disk performance: No impact on disk performance because audit data is stored on the DAS side, not on the disk of the database instance.

Q: Why is a captured SQL statement incomplete? How can I view the full statement?

A: This occurs if the executed SQL statement is too long. In SQL Explorer, search for the corresponding thread ID or transaction ID to view the full statement.

Q: In the audit logs of an ApsaraDB RDS for PostgreSQL instance, why are some SQL statements displayed as -?

A: When the log_statement parameter of an ApsaraDB RDS for PostgreSQL instance is set to all, empty statements sent by the client are also recorded in the audit log. These empty statements appear as - in the SQL statement column of the audit log, which is expected behavior. We recommend that you review your client-side source code to optimize the logic for sending empty statements.

Related APIs

API	Description
GetErrorRequestSample	Asynchronously queries up to 20 samples of failed SQL queries from the SQL Explorer results of a database instance within a specified time range.
GetAsyncErrorRequestStatResult	Asynchronously retrieves the number of failed executions for a specified SQL statement from the SQL Explorer results of a database instance.
GetAsyncErrorRequestListByCode	Asynchronously queries the SQL IDs of statements that generated a specified MySQL error code from the SQL Explorer results of a database instance.
GetAsyncErrorRequestStatByCode	Asynchronously queries the MySQL error codes and the number of SQL queries corresponding to each error code from the SQL Explorer results of a database instance.
GetFullRequestOriginStatByInstanceId	Gathers statistics on full request data from the SQL Explorer results of a database instance, categorized by access source.
GetFullRequestStatResultByInstanceId	Asynchronously gathers statistics on full request data from the SQL Explorer results of a database instance, categorized by SQL ID.
GetFullRequestSampleByInstanceId	Queries up to 20 SQL samples by SQL ID from the SQL Explorer results of a database instance.
GetDasSQLLogHotData	Queries the details of data in hot storage from the last seven days for SQL Explorer and Audit of a database instance.

Best practices

Troubleshoot slow SQL queries