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 directly from the database kernel with negligible CPU impact.

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.
SQL detail data in SQL Explorer is retained for the same duration configured for DAS Enterprise Edition.
Disabling SQL Explorer and Audit does not affect your services but clears all stored data for this feature. Before disabling, export 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.
Under very high load, a small number of records may be lost, which can cause inaccurate incremental data statistics in SQL Explorer.
A parameter controls the maximum recorded SQL statement length. SQL Explorer truncates any statement that exceeds this limit. Because a prefix is added during data collection, the actual recorded length is slightly less than the configured 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 with PgBouncer 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 how SQL execution durations are distributed within the selected time range. Durations are 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
Colors closer to blue indicate healthier SQL performance. Colors closer to orange and red indicate poorer 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 ratio of a specific SQL type's total execution duration to the total across all SQL types. A higher percentage indicates greater resource consumption 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. Its duration is the time between the last interaction and when the disconnection occurred, representing the connection idle time. A status of 1158 in the Status column indicates a network disconnection, which can be caused by:

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. To reproduce this behavior, follow 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 Logs section different from the one in the SQL statement?

A: The database name shown in the Logs section is retrieved from the session, whereas the database name in the SQL statement is user-specified. These can differ in scenarios such as cross-database queries or dynamic SQL, where the user's input or query design determines the database name in the statement.

Q: Does enabling SQL Explorer and Audit affect database performance? How significant is the impact?

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

The impact on specific resources is as follows:

CPU and memory: Consumption is extremely low and can be ignored.
Storage space: The feature primarily stores audit information. However, DAS Enterprise Edition stores this data on the DAS side, so it does not consume your database instance's storage.
Network: There is no impact on network performance.
Disk performance: No impact. Audit data is written to DAS storage, not the database instance's disks.

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 appear as - in the SQL statement column, which is expected behavior. Review your client-side 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