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.
  • DAS Professional Edition is enabled for the database instance. For more information, see Purchase DAS Professional Edition.
  • The SQL Explorer and Audit feature is available only for the following types of databases:
    • ApsaraDB RDS for MySQL High-availability Edition and Enterprise Edition
    • ApsaraDB RDS for SQL Server High-availability Edition and Cluster Edition
    • ApsaraDB RDS for PostgreSQL High-availability Edition
    • PolarDB for MySQL Single Node Edition, Archive Database Edition, and Cluster Edition
    • PolarDB-X 2.0
  • The SQL Explorer and Audit feature is available in different regions for different types of databases.
    Database type Region
    • ApsaraDB RDS for MySQL High-availability Edition and Enterprise Edition
    • ApsaraDB RDS for SQL Server High-availability Edition and Cluster Edition
    • PolarDB for MySQL Single Node Edition, Archive Database Edition, and Cluster 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 (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 (Singapore), Malaysia (Kuala Lumpur), and Indonesia (Jakarta)
    PolarDB-X 2.0 China (Hangzhou), China (Shanghai), China (Beijing), China (Shenzhen), and Singapore (Singapore)

Background information

The SQL Explorer feature records 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.

Precautions

  • 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 less than 90 days when you activate 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 activate DAS Professional 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 DAS Professional Edition is activated for the database instance.

Procedure

  1. Log on to the DAS console.
  2. In the left-side navigation pane, click Instance Monitoring.
  3. On the page that appears, click the ID of the instance for which you want to use SQL Explorer. The instance details page appears.
  4. In the left-side navigation pane, click SQL Explorer and Audit. On the page that appears, click the SQL Explorer tab.
    0
  5. On the SQL Explorer tab, choose the following features based on your requirements.
    Note When you select a time range, the end time must be later than the start time, and the interval between the start time and the end time cannot exceed 24 hours. The queried time range must be within the data storage duration of SQL Explorer.
    • Display by Time Range: Set 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 of 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 You can export up to 10 million rows of data at a time. For more information, see Usage notes.
    • Display by Comparison: Set 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: Set 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: This 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, 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.

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. From the chart, you can view 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. From the chart, you can view 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. From the chart, you can view 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. From the chart, you can view 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. From the chart, you can view 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. From the chart, you can view the percentage of SQL queries whose execution duration falls within this range.
    • > 1s: indicates that the execution duration is greater than 1s. From the chart, you can view 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 instance is when the instance runs SQL queries. The closer the color of the areas is to orange and red, the less healthy the 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 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.

Related API operations

Best practices

Troubleshoot slow queries