Database Autonomy Service:SQL Review

Supported databases and regions

The SQL Explorer and Audit feature is available only after you enable DAS Enterprise Edition. The supported databases and regions vary by edition. For a list of the databases and regions supported by each edition, see Supported databases and regions for each edition.

Features

The SQL Review feature performs a workload analysis on a database instance by comparing data from a specified time range against a baseline time range. It also provides an in-depth analysis of the SQL statements that run on the instance and displays results including Index Optimization Suggestions, SQL Rewrite Suggestions, Top SQL, Added SQL, Failed SQL, SQL Feature Analysis, SQL with Execution Variation, SQL with Deteriorated Performance, and Top Tables by Traffic.

SQL Review performs global SQL workload analysis, analyzing metrics such as SQL samples, average execution duration, duration ratio, execution count ratio, average scanned rows, total executions, and first seen time. This helps you quickly identify problematic SQL statements in your database instance, such as statements that consume excessive resources, show significant changes in trends, exhibit performance degradation, fail to execute, or are newly added. It then analyzes these statements and provides optimization suggestions.

Diagnostic modules:

• Index Optimization Suggestion: Provides index optimization suggestions for SQL statements within the selected time range, grouped by table. This includes recommended index columns, DDL statements, and expected benefits.

• SQL Rewrite Suggestion: Offers rewrite suggestions for inefficient SQL statements.

• Top SQL: Displays the top 10 SQL statements by resource consumption, average scanned rows, and execution count to help you quickly identify high-impact queries.

• New SQL: Shows new SQL statements that appeared during the diagnostic time range but not in the comparison range, helping you verify if recent changes meet business expectations.

• Failed SQL: Lists SQL statements that failed to execute, including details such as SQL ID, SQL sample, database, error code, and error count.

• SQL Characteristics Analysis: Analyzes SQL statements based on their text and provides statistical breakdowns by SQL type.

• Execute Changed SQL: Categorizes SQL statements based on the rate of change in their average scanned rows and execution counts. It displays the number of SQL templates, duration ratio, total executions, and execution count ratio for each change category.

• SQL with Deteriorated Performance: Based on the Execute Changed SQL results, this module identifies SQL statements where the average scanned rows, total executions, or average duration exceeded a specified rate of change.

• Tables with Top Traffic: Displays information about tables based on their traffic volume, including table name, database name, number of related SQL templates, total executions, SELECT counts, INSERT counts, UPDATE counts, DELETE counts, and average execution duration.

Procedure

1. Log on to the DAS console.

2. In the navigation pane on the left, click Intelligent O&M Center > Instance Monitoring .

3. Find the target instance and click the instance ID to open the instance details page.

4. In the left-side navigation pane, click Request Analysis > SQL Explorer and Audit. On the page that opens, click ️SQL Explorer > SQL Review.

5. On the SQL Review page, perform one of the following operations as needed.

   • Create an SQL Review task

     Click Create SQL Review. In the Create SQL Review panel that appears, configure the following parameters and click OK to start the SQL Review task.

     Note

     Only one SQL Review task can run on a database instance at a time.

     Parameter	Description
     SQL Review Name	The name of the SQL review task.
     Select database	Select the database to review.
     Diagnostics Time Range	The time range for the diagnosis. Note The diagnostic time range must be after DAS Enterprise Edition is enabled and within the SQL Explorer data retention period. To ensure accuracy, a diagnostic time range of more than 24 hours is recommended. The time range must be between 1 hour and 8 days. You cannot run multiple diagnostic tasks for the same time range.
     Comparison time range	The baseline time range for comparison. Note The comparison time range must be after DAS Enterprise Edition is enabled and within the SQL Explorer data retention period. The diagnostic and comparison time ranges must have the same duration and must not overlap. The comparison time range must be earlier than the diagnostic time range.
     Diagnostic content	Select the modules to include in the analysis. Index Optimization Suggestions SQL Rewrite Suggestion New SQL Failed SQL Top SQL SQL Characteristics Analysis Execute Changed SQL Top Tables by Traffic

   • View SQL Review results

     In the upper-right corner of the page, select a time range and click View to view the SQL Review tasks initiated within that range.

     Note

     When you select a time range, the end time must be later than the start time, and the time range cannot exceed 7 days. You can query SQL Review tasks from the past 6 months for the database instance.

     • In the Actions column, click Details to view the detailed results of a task. For more information, see Result details.

     • In the Actions column, click Download to save the task results to your local computer.

     • In the Actions column, click Share to share the task results with other users who can access the DAS console.

     • Click the plus sign (+) next to a Task Name to view the subtasks within the SQL Review task and their statuses.

       In the Actions column of a subtask, click Details to view its specific results.

Result details

• Overview: The overview panel displays the Diagnostics Time and Comparison Time ranges at the top. The right side summarizes the diagnostic results in metric cards, including the number of New Index Suggestions, Index Removal Suggestions, SQL Rewrite Suggestions, values for Expected Performance Improvement, Expected Space Reclaimed, and counts for Failed SQL, Added SQL, and SQL with Deteriorated Performance.

• Index Optimization Suggestion: Click the plus sign (+) next to a table name to view Suggestion Details, DDL Statements, and Expected Return. The top of the page contains tabs for other modules such as Index Optimization Suggestions, SQL Rewrite Suggestions, Top SQL, Added SQL, Failed SQL, and SQL Feature Analysis. The Index Recommendation section provides Expand List/Collapse List toggles, a Show only highly recommended suggestions filter, and an Export DDL button. The main table shows columns for the table name, database name, and actions. When expanded, it reveals the suggestion type (such as Create Index), the DDL statement (such as ALTER TABLE xxx ADD INDEX idx_name (action,status,time)), and the expected performance improvement percentage. The collapsed row provides a link to Compare indexes before and after optimization.

  • Click Details to view detailed information about the index suggestion.

  • Click Run DDL to execute the optimized DDL statement.

  • Click Verify Optimization Improvement to create a task that validates the index suggestion.

    Note

    • This feature uses the instance's Backup Restoration functionality to clone a selected backup set to a new, system-created instance for verification. This process does not affect your original production instance.

    • Billing for the new instance is the same as for instances created using the Restore feature. No extra fees are charged.

    • After verification is complete, you must manually release the new instance created by the system.

    In the Create verification task dialog box, complete the Configure task and Purchase resources steps. In the Configure task step, enter a Task Description, select a backup set, and enter the privileged account and password.

• SQL Rewrite Suggestion: View rewrite suggestions for inefficient SQL statements.

  Click Optimize in the Actions column to open the SQL Diagnostic Optimization page. Here, you can review the optimized SQL, its Execution Plan, and the Result.

  If you accept the suggestion, click Copy in the upper-right corner of the SQL Diagnostic Optimization page and paste the optimized SQL into your database client or Data Management Service (DMS) for execution. If you do not accept the suggestion, click Cancel to end the diagnosis.

  This panel includes columns for SQL ID, SQL sample, Rewritten statement, Average execution duration (ms), duration ratio, average scanned rows, and Actions.

• Top SQL: Displays the 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 that SQL statement.

  • Click Optimize in the Actions column to open the SQL Diagnostic Optimization page, where you can view the optimized SQL, its Execution Plan, and the Result.

    If you accept the suggestion, click Copy in the upper-right corner of the SQL Diagnostic Optimization page and paste the optimized SQL into your database client or DMS for execution. If you do not accept the suggestion, click Cancel to end the diagnosis.

  The table on the Top 10 Resource-consuming SQL tab includes the following columns: SQL ID, SQL sample, Average execution duration (ms), duration ratio, Total executions, Execution count ratio, average scanned rows, Total duration (with a trend area chart), SQL feature, and Actions.

• Added SQL: Shows new SQL statements by comparing the diagnostic time range with the comparison time range.

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

  • Click Optimize in the Actions column to open the SQL Diagnostic Optimization page, where you can view the optimized SQL, its Execution Plan, and the Result.

    If you accept the suggestion, click Copy in the upper-right corner of the SQL Diagnostic Optimization page and paste the optimized SQL into your database client or DMS for execution. If you do not accept the suggestion, click Cancel to end the diagnosis.

  The Added SQL list shows performance metrics for each SQL statement, including SQL sample, Average execution duration (ms), duration ratio, Execution count ratio, average scanned rows, Total executions, and First seen time.

• Failed SQL: Lists SQL statements that failed to execute.

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

  This list includes the SQL ID, SQL sample, DB, Error code, and Error count columns.

• SQL Feature Analysis: View a feature-based analysis of SQL statements. Click a value in the Number of SQL templates column to view the SQL Template Details for that feature.

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

  The SQL Feature Analysis panel shows a horizontal stacked bar chart of the SQL type distribution (Select, Update, Insert, Delete, and Other, with their respective percentages). The table below categorizes SQL by feature (such as No Predicate, Equality Request, Range Request, Sort Request, Aggregate Request, and Unclassified) and lists metrics such as Number of SQL templates, Average execution duration (ms), duration ratio, Execution count ratio, average scanned rows, and Total executions.

• SQL with Execution Variation: Groups SQL statements into different change intervals based on their average scanned rows and execution count.

  Click Details to view the Execution Trend Details. On the Execution Trend Details page:

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

  • Click Optimize in the Actions column to open the SQL Diagnostic Optimization page, where you can view the optimized SQL, its Execution Plan, and the Result.

    If you accept the suggestion, click Copy in the upper-right corner of the SQL Diagnostic Optimization page and paste the optimized SQL into your database client or DMS for execution. If you do not accept the suggestion, click Cancel to end the diagnosis.

  This panel displays the top 100 SQL statements by duration ratio whose average scanned rows or average execution duration increased by more than 20% compared to the baseline period. The results are grouped into five change intervals: (-100, 0], (0, 20], (20, 50], (50, 100], and (100, +∞]. The table shows metrics for each interval, including Number of SQL Templates, Duration Ratio, Total Executions, and Execution Count Ratio.

• SQL with Deteriorated Performance: Lists SQL statements that show performance degradation when compared with the baseline.

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

  • Click Optimize in the Actions column to open the SQL Diagnostic Optimization page, where you can view the optimized SQL, its Execution Plan, and the Result.

    If you accept the suggestion, click Copy in the upper-right corner of the SQL Diagnostic Optimization page and paste the optimized SQL into your database client or DMS for execution. If you do not accept the suggestion, click Cancel to end the diagnosis.

  This page displays the top 100 SQL statements by duration ratio whose average scanned rows or average execution duration increased by more than 20%. You can filter results by using conditions, such as an average scanned rows value that is greater than a specified threshold. The table compares metrics between the two time periods and includes columns for SQL ID, SQL sample, duration ratio, Average execution duration (ms), Total executions, and average scanned rows.

• Top Tables by Traffic: View details about the top 100 tables in the database by traffic.

  • Click a table name to view its Field and Index.

  • Click Related SQL Statements to see detailed information about the SQL statements associated with that table.

  The traffic table shows statistics for each table, including the number of Related SQL templates, Total executions, SELECT count, INSERT count, UPDATE count, DELETE count, and Average execution duration (ms).