Database Autonomy Service (DAS) provides the SQL Review feature. This topic describes how to use the SQL Review feature in the DAS console.

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 Review feature is available only for the following types of database instances:
    • ApsaraDB RDS for MySQL High-availability Edition and Enterprise Edition
    • PolarDB for MySQL Single Node Edition, Archive Database Edition, and Cluster Edition
  • The SQL Review feature is available in the following regions: 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).

Overview

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, 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.

The SQL Review feature can perform global workload analysis on SQL statements and display the analysis results, including the SQL samples, average execution duration, execution duration percentage, execution percentage, average number of scanned rows, total number of executions, and first occurrence time. Based on the analysis results, you can identify SQL statements that may result in problems in the database instances, such as SQL statements that consume excessive amounts of resources, show abrupt changes in trends, experience performance deterioration, fail to be executed, or are newly added. Then, SQL Review can help you analyze these SQL statements and provide optimization suggestions.

Functional modules:

  • Index optimization suggestions: displays index optimization suggestions for SQL statements in a table within a specified time range. A suggestion includes the index column to optimize, the DDL statements that can be executed for optimization, and the expected return.
  • SQL rewrite suggestions: provides rewrite suggestions for SQL statements that are executed in an inefficient manner.
  • Top SQL: displays the SQL statements that rank top 10 in terms of resource consumption, average number of scanned rows, or total number of executions. This helps you identify SQL statements that significantly degrade database performance.
  • Added SQL: displays new SQL statements by comparing the SQL statements within the diagnostic time range and those within the baseline time range. This helps you check whether the changes to SQL statements meet your business requirements.
  • Failed SQL: displays the SQL statements that fail to be executed, including the SQL ID, SQL sample, database, error code, and number of errors.
  • SQL feature analysis: analyzes the characteristics of SQL statements and displays the statistics of all SQL statements based on their type.
  • SQL with execution variation: displays the statistics of SQL statements based on the average number of scanned rows and the rate of change in the number of executions. The statistics show the number of SQL templates, execution duration percentage, total number of executions, and execution percentage within different change intervals.
  • SQL with deteriorated performance: filters SQL statements whose average number of scanned rows, total number of executions, or average execution duration exceeds a specified change rate based on SQL with execution variation.
  • Top tables by traffic: displays information about tables in a database by traffic, including the table name, database name, number of relevant SQL templates, total number of SQL executions, number of executed SELECT statements, number of executed INSERT statements, number of executed UPDATE statements, number of executed DELETE statements, and average execution duration.

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 database instance that you want to manage. The instance details page appears.
  4. In the left-side navigation pane, choose Request Analysis > SQL Explorer and Audit. On the page that appears, click the SQL Explorer tab. Then, click the SQL Review tab.
  5. On the SQL Review tab, perform required operations.
    • Create a SQL review task
      Click Create a SQL Review. Then, in the Create a SQL Review panel, configure the parameters described in the following table. After you complete the settings, click OK.
      Note You can initiate a SQL review task only for the same database instance at the same time.
      Parameter Description
      SQL Review Name Specify the name of the SQL review task.
      Select Database Specify the database for which you want to initiate the SQL review task in the database instance.
      Diagnostics Time Range Specify the time range for diagnostics.
      Note
      • The time range for diagnostics must be within the storage duration of SQL Explorer data.
      • To ensure the accuracy of SQL review, we recommend that you select a diagnostic time range of more than 24 hours. The maximum time range is 8 days and the minimum time range is 1 hour.
      • SQL statements executed at the same time cannot be reviewed multiple times.
      Comparison Time Range Specify the baseline time range for comparison.
      Note
      • The baseline time range must be within the storage duration of SQL Explorer data.
      • The length of the diagnostic time range must be the same as that of the baseline time range, and the two time ranges cannot overlap.
      • The baseline time range must be earlier than the diagnostic time range.
      Diagnostic Content Specify the content for diagnostics. Select one or more items for diagnostics based on your business requirements.
      • Index Optimization Suggestions
      • SQL Rewrite Suggestions
      • Added SQL
      • Failed SQL
      • TOP SQL
      • SQL Feature Analysis
      • SQL with Execution Variation
      • Top Tables by Traffic
    • View SQL review results
      In the upper-right corner of the page, select a time range and then click Search to view the SQL review tasks initiated within the specified time range.
      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 7 days. You can query SQL review tasks initiated for a database instance within six months.
      • Click Details in the Actions column corresponding to an SQL review task to view the details of the task results. For more information, see Result details.
      • Click Download in the Actions column corresponding to an SQL review task to download the task results to your local computer.
      • Click Share in the Actions column corresponding to an SQL review task to share the task results with users who have permissions on the DAS console.
      • Click the plus sign (+) before Task Name to view the subtasks contained in an SQL review task and the status of each subtask.

        You can click Details in the Actions column corresponding to a subtask to view the details of the subtask.

Result details

  • Overview:Overview
  • Index Optimization Suggestions: Click the plus sign (+) before a table name to view Suggestion Details, DDL Statements, and Expected Return. Index Optimization Suggestions
    • Click Details to view the suggestion details.
    • Click Run DDL to execute the optimized DDL statement.
    • Click Verify the Optimization Effect to create a task to verify the index optimization suggestions.
      Note
      • During the verification, the Backup and Restore feature is used to clone the selected backup set from the database instance to a new instance created by the system. This allows the optimization effect to be verified without affecting the original instance.
      • The fee billed for an optimization verification task is the same as the fee billed for a new instance created by the Restore feature.
      • After the optimization effect is verified, you must manually release the new instance created by the system.
      Create a Task to Verify the Optimization Effect
  • SQL Review Suggestions: View the rewrite suggestions provided for SQL statements that are executed in an inefficient manner.

    Click Optimize in the Actions column corresponding to an SQL statement. On the SQL Diagnostic Optimization page, view the optimized SQL statement, execution plan, and diagnostic results.

    If you want to accept the SQL optimization suggestions, click Copy in the upper-right corner of the SQL Diagnostic Optimization page and paste the optimized SQL statement to the database client or Data Management Service (DMS) for execution. If you do not accept the SQL optimization suggestions, click OK.

    SQL Rewrite Suggestions
  • Top SQL: View Top 10 Resource-consuming SQL Statements, Top 10 SQL Statements with Maximum Average Number of Scanned Rows, and Top 10 Most Frequently Executed SQL Statements.
    • Click an SQL ID to view the details of the SQL statement.
    • Click Optimize in the Actions column corresponding to an SQL statement. On the SQL Diagnostic Optimization page, view the optimized SQL statement, execution plan, and diagnostic results.

      If you want to accept the SQL optimization suggestions, click Copy in the upper-right corner of the SQL Diagnostic Optimization page and paste the optimized SQL statement to the database client or DMS for execution. If you do not accept the SQL optimization suggestions, click OK.

    TOP SQL
  • Added SQL: View new SQL statements by comparing the SQL statements within the diagnostic time range and those within the baseline time range.
    • Click an SQL ID to view the details of the SQL statement.
    • Click Optimize in the Actions column corresponding to an SQL statement. On the SQL Diagnostic Optimization page, view the optimized SQL statement, execution plan, and diagnostic results.

      If you want to accept the SQL optimization suggestions, click Copy in the upper-right corner of the SQL Diagnostic Optimization page and paste the optimized SQL statement to the database client or DMS for execution. If you do not accept the SQL optimization suggestions, click OK.

    Added SQL
  • Failed SQL: View the SQL statements that fail to be executed.

    Click an SQL ID to view the details of the SQL statement.

    Failed SQL
  • SQL Feature Analysis: View the SQL feature analysis results. Click Number of SQL Templates to view the SQL template details of an SQL feature.

    On the SQL Template Details page, click an SQL ID to view the details of the SQL statement.

    SQL Feature Analysis
  • SQL with Execution Variation: View SQL statements within different change intervals based on the average number of scanned rows and total number of executions.
    Click Details to view the execution trend details. On the Execution Trend Details page, perform the following operations:
    • Click an SQL ID to view the details of the SQL statement.
    • Click Optimize in the Actions column corresponding to an SQL statement. On the SQL Diagnostic Optimization page, view the optimized SQL statement, execution plan, and diagnostic results.

      If you want to accept the SQL optimization suggestions, click Copy in the upper-right corner of the SQL Diagnostic Optimization page and paste the optimized SQL statement to the database client or DMS for execution. If you do not accept the SQL optimization suggestions, click OK.

    SQL with Execution Variation
  • SQL with Deteriorated Performance: View the SQL statements whose performance has deteriorated over time.
    • Click an SQL ID to view the details of the SQL statement.
    • Click Optimize in the Actions column corresponding to an SQL statement. On the SQL Diagnostic Optimization page, view the optimized SQL statement, execution plan, and diagnostic results.

      If you want to accept the SQL optimization suggestions, click Copy in the upper-right corner of the SQL Diagnostic Optimization page and paste the optimized SQL statement to the database client or DMS for execution. If you do not accept the SQL optimization suggestions, click OK.

    SQL with Deteriorated Performance
  • Top Tables by Traffic: View the details of top 100 tables that generate the most traffic in the database.
    • Click a table name to view Field and Index.
    • Click Related SQL Statements to view related SQL details of the table.
    TOP Tables by Traffic