Database Autonomy Service:SQL Review

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

Features

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 provides index optimization and SQL rewrite suggestions, and displays the 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 issues 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 specific time range. A suggestion includes the index column that can be optimized, the DDL statements that can be executed for optimization, and the expected return.

• SQL Rewrite Suggestions: provides rewrite suggestions for SQL statements that are inefficiently executed.

• 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 based on a comparison of 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 types.

• 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: displays SQL statements whose average number of scanned rows, total number of executions, or average execution duration exceeds a specific change rate based on SQL statements with execution variation.

• Top Tables by Traffic: displays information about tables in a database by traffic, including the table name, database name, number of 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, find the database instance that you want to manage and click the instance ID. 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 the operations based on your business requirements.

   • Create an SQL review task

     Click Create SQL Review. Then, in the Create SQL Review panel, configure the parameters that are described in the following table. After you complete the settings, click OK.

     Note

     Only one SQL review task can run in a database instance at the same time.

     Parameter	Description
     SQL Review Name	The name of the SQL review task.
     Select database	The database for which you want to initiate the SQL review task in the database instance.
     Diagnostics Time Range	The time range for diagnostics. Note The diagnostic time range must be later than the time when DAS Enterprise Edition is enabled and must fall within the data storage duration of SQL Explorer. 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 eight days and the minimum time range is 1 hour. SQL statements executed at the same time cannot be reviewed multiple times.
     Comparison time range	The baseline time range for comparison. Note The comparison time range must be later than the time when DAS Enterprise Edition is enabled and must fall within the data storage duration of SQL Explorer. 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	The content for diagnostics. Select one or more items for diagnostics based on your business requirements. Valid values: 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, 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 seven days. You can query SQL review tasks initiated for a database instance within six months.

     • Click Details in the Actions column of an SQL review task to view the detailed task results. For more information, see the Result details section of this topic.

     • Click Download in the Actions column of an SQL review task to download the task results to your local computer.

     • Click Share in the Actions column of 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 of a subtask to view the details of the subtask.

Result details

• Overview:

• Index Optimization Suggestions: Click the plus sign (+) before a table name to view the information displayed in the Suggestion Details, DDL Statements, and Expected Return columns.

  • Click Details to view the suggestion details.

  • Click Run DDL to execute the optimized DDL statement.

  • Click Verify Optimization Improvement to create a task to verify the index optimization suggestions.

    Note

    • During the verification, the Backup and Restore feature of the database instance 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 impact on 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 result is verified, you must manually release the new instance created by the system.

    

• SQL Review Suggestions: View the rewrite suggestions provided for SQL statements that are inefficiently executed.

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

  If you 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 Cancel.

  

• 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 of an SQL statement. On the SQL Diagnostic Optimization page, view the optimized SQL statement, execution plan, and diagnostic results.

    If you 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 Cancel.

  

• Added SQL: View new SQL statements based on a comparison of 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 of an SQL statement. On the SQL Diagnostic Optimization page, view the optimized SQL statement, execution plan, and diagnostic results.

    If you 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 Cancel.

  

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

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

  

• 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 with Execution Variation: View SQL statements within different change intervals based on the average number of scanned rows and the 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 of an SQL statement. On the SQL Diagnostic Optimization page, view the optimized SQL statement, execution plan, and diagnostic results.

    If you 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 Cancel.

  

• 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 of an SQL statement. On the SQL Diagnostic Optimization page, view the optimized SQL statement, execution plan, and diagnostic results.

    If you 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 Cancel.

  

• Top Tables by Traffic: View the details of the top 100 tables that generate the most traffic in the database.

  • Click a table name to view the field and index of the table.

  • Click Related SQL Statements to view the related SQL details of the table.