All Products
Search
Document Center

Database Autonomy Service:Query governance

Last Updated:Nov 20, 2023

Database Autonomy Service (DAS) provides the query governance feature. At 01:00:00 every day, the system automatically analyzes all slow queries that were executed on your database instances during the previous day and adds tags to SQL templates based on severity levels. This helps you categorize and optimize slow queries. DAS also provides suggestions on optimizing SQL templates and allows you to export query governance data.

Prerequisites

  • The database instance that you want to manage is of one of the following types:

    • ApsaraDB RDS for MySQL

    • PolarDB for MySQL

    • ApsaraDB MyBase for MySQL

    • ApsaraDB RDS for PostgreSQL

  • The database instance is deployed in a region in the Chinese mainland, the China (Hong Kong) region, or the Singapore region.

    Note

    From April 1, 2023, the query governance feature is supported for database instances that reside in the China (Hong Kong) and Singapore regions.

Limits

  • The query governance feature is implemented by analyzing offline data the day after the transaction (T+1). For example, if you add the Optimization Not Required tag to an SQL template, the SQL template is removed from the SQL templates that need to be optimized the next day.

  • Tags added by the system cannot be changed or removed.

  • For each database instance, DAS counts and analyzes only the top 200 slow queries that are most frequently executed. This indicates that the maximum number of slow SQL templates of a database instance is 200.

Terms

Term

Description

Optimizable SQL

The SQL templates that cause slow queries and need to be optimized.

SQL templates that do not need to be optimized

The SQL templates that cause slow queries and to which DAS automatically adds the Ignored tag or to which you add the Optimization Not Required tag.

Ignored

The SQL templates that cause slow queries and to which DAS automatically adds the Optimization Not Required tag. These SQL templates include the SQL templates that start with the SHOW, CREATE, XA, COMMIT, ROLLBACK, SELECT SLEEP, or EXPLAIN statement.

Procedure

  1. Log on to the DAS console.
  2. In the left-side navigation pane, click Query Governance.

  3. On the Query Governance page, view the query governance results.

    You can specify the time, region, and database engine, and enter one or more instance IDs to view the query governance results of the specified database instances displayed in the following sections:

    • Overview: This section displays the query governance results after SQL templates are categorized and tags are added to the SQL templates.

      Note

      If you want to view Failed SQL Executions, you must purchase DAS Professional Edition.

    • Trend chart: This section displays the trend of changes in the query governance results in a specific time range.

    • Top Rankings: This section displays the Best-performing Instances and Worst-performing Instances charts.

      • Worst-performing Instances: Database instances are displayed in descending order based on the number of slow queries that are executed on each instance. This helps you identify the instance on which the largest number of slow queries are executed.

      • Best-performing Instances: Database instances are displayed in ascending order based on changes in the number of slow queries that are executed on each instance. A negative value indicates that a smaller number of slow queries are executed compared with the number of slow queries that were executed on the previous day. A positive value indicates that a larger number of slow queries are executed compared with the number of slow queries that were executed on the previous day. This helps you identify the instance on which SQL optimization is most effective.

      We recommend that you pay immediate attention to the instance on which the largest number of slow queries are executed and the instance on which SQL optimization is most effective.

    • SQL to Be Optimized: You can specify filter conditions to filter the SQL statements that you want to manage.

      Note

      You can specify the following filter conditions: database name, SQL keyword, rule tag, and username of a database account. The four filter conditions are evaluated by using the AND operator.

      • Separate multiple database names with commas (,). The database names are evaluated by using the OR operator.

      • Separate multiple SQL keywords with spaces. The SQL keywords are evaluated by using the AND operator.

      • Separate the usernames of multiple database accounts with commas (,). The usernames are evaluated by using the OR operator.

      • You can select multiple rule tags. The selected rules are evaluated by using the OR operator.

      • You can find a sample SQL statement and click Suggestions in the Actions column to view the suggestions on query governance.

      • You can find a sample SQL statement and click Add Tag in the Actions column to add a tag to the SQL statement. For more information about tags, see the tag description table in this topic.

        You can also select multiple sample SQL statements to add tags to these SQL statements at a time.

      • You can find a sample SQL statement and click Sample in the Actions column to view the slow query logs of the SQL statement.

      • You can find a sample SQL statement and click Trend in the Actions column to view the slow query log analysis details of the SQL statement. For more information about how to analyze and manage slow queries, see Slow query log analysis.

      You can export and share the data of SQL templates to be optimized based on your business requirements. For more information, see the Best practices section of this topic.

    • Failed SQL: You can specify filter conditions to filter the SQL statements that you want to view.

      Note
      • If you want to view Failed SQL, you must purchase DAS Professional Edition.

      • You can specify database names and SQL keywords to filter SQL statements. The filter conditions are evaluated by using the AND operator.

        • Separate multiple database names with commas (,). The database names are evaluated by using the OR operator.

        • Separate multiple SQL keywords with spaces. The SQL keywords are evaluated by using the AND operator.

      You can find a sample SQL statement and click Sample in the Actions column to view the details of the SQL statement.

Best practices

  • Use tags to identify the SQL templates that need to be optimized.

    The query governance feature classifies SQL templates that cause slow queries into two categories: SQL templates that do not need to be optimized and SQL templates that need to be optimized. You can use tags to filter SQL templates and optimize SQL templates based on the severity levels that are indicated by the tags. The following table describes the tags that are supported by DAS.

    ID

    Name

    Severity level

    Immediate attention required

    Description

    NEW_SQL

    New Slow SQL

    Critical

    ✔️

    SQL templates that caused slow queries within the previous seven days.

    DAS_IGNORE

    Ignored

    Normal

    SQL templates to which DAS automatically adds the No Need to Optimize tag. These SQL templates include the SQL templates that start with the SHOW, CREATE, XA, COMMIT, ROLLBACK, SELECT SLEEP, or EXPLAIN statement.

    FUZZY_LIKE

    LIKE Query

    Normal

    SQL templates in which the LIKE operator is used to implement fuzzy matching. Indexes cannot be used in queries that contain the LIKE operator.

    HAS_EXPR

    Expressions Contained

    Normal

    SQL templates that contain calculation expressions. Indexes cannot be used on the involved columns.

    LARGE_IN_LIST

    Large List Query

    Normal

    SQL templates in which more than 200 elements are specified for the IN operator. Such SQL templates may be automatically generated by programs. Indexes cannot be used on the involved columns.

    SELECT_STAR

    Full-column Query

    Normal

    SQL templates in which one or more fields are specified in SELECT clauses, but no conditions are specified. If you do not specify conditions, resources are wasted for querying unnecessary data. This reduces query performance.

    INDEX_ADVISOR

    Index Suggestions

    Normal

    ✔️

    SQL templates that are used to query data from tables that are not indexed. We recommend that you create and use indexes to accelerate SQL queries.

    COMPLEX_JOIN

    Complex JOIN Query

    Normal

    SQL templates in which the JOIN operator is used to join more than three tables. You cannot use the JOIN operator to join more than three tables. The data type of the specified fields in the tables that you want to join must be the same. Before you join multiple tables, make sure that you create indexes for the fields that you want to query in the tables.

    CROSS_DB

    Cross-database Query

    Normal

    SQL templates that are used to query data in different databases. After databases or tables are migrated from the original database instance to another database instance, cross-database queries may fail.

    SUBQUERY

    Subqueries Included

    Normal

    SQL templates that contain SQL statements for subqueries. We recommend that you rewrite the SQL templates and use the JOIN operator to make queries more efficient.

    DEEP_PAGING

    Deep Paging

    Critical

    SQL templates in which the LIMIT operator is used to implement the deep paging method. We recommend that you rewrite the SQL templates and use the JOIN operator instead of the LIMIT operator.

    WITHOUT_PREDICATE

    No Predicates

    Critical

    ✔️

    SQL templates that do not contain predicates. Check whether your business requires full table scanning. If your business does not require full table scanning, rewrite the SQL templates.

    NULL_COMPARE

    Null Match Error

    Critical

    SQL templates in which the ISNULL() function is used to check for NULL values. You cannot use the ISNULL() function to find NULL values because NULL is returned if one of the values is NULL.

    COUNT_NOT_STAR

    Invalid COUNT Syntax

    Critical

    SQL templates that contain invalid COUNT syntax. Use COUNT(*) to query the number of data records. Do not use the COUNT (Column name) or COUNT (Constant) syntax. COUNT(*) is the standard syntax that is defined in SQL92 to query the number of rows. The COUNT(*) syntax counts the number of data records that include NULL values and can be executed on all types of databases. The COUNT (Column name) syntax does not count NULL values.

    LARGE_ROWS_EXAMINED

    Excessive Scanned Rows

    Normal

    SQL templates that are used to scan more than 50,000 rows on average. A larger number of rows consume a larger amount of database resources. This causes the SQL queries to execute slowly and affects other SQL queries.

    Note

    We recommend that you use more specific filter conditions in SQL statements.

    LARGE_ROWS_SENT

    Excessive Returned Rows

    Normal

    SQL templates that are used in queries for which more than 5,000 rows of data on average are returned. We recommend that you modify the SQL templates to reduce the number of returned rows.

    NO_ADVICE

    No suggestion

    Normal

    No suggestions are provided.

    PERIOD_SQL

    Periodic

    Normal

    SQL templates that are executed at a specific point in time on a daily basis.

    We recommend that you pay immediate attention to the SQL templates that need to be optimized. Before the system analyzes slow queries every day, you can manually add one of the tags described in the following table to SQL templates. This can reduce the number of SQL templates that need to be optimized.

    ID

    Name

    Severity level

    Description

    USER_IGNORE

    No Need to Optimize

    Normal

    After you add this tag to an SQL template, the SQL template is not counted in the SQL templates that need to be optimized on the next day.

    DAS_IMPORTANT

    High-priority SQL

    Normal

    Add this tag to SQL templates that are of high priority.

    DAS_NOT_IMPORTANT

    Low-priority SQL

    Normal

    Add this tag to SQL templates that are of low priority.

    DAS_IN_PLAN

    Optimize Later

    Normal

    Add this tag to SQL templates that are to be optimized later.

  • Export data.

    • Click Export in the upper-right corner of the SQL to Be Optimized section to export the data of the filtered SQL templates.

      Note

      The exported data is available for download for three days.

    • You can specify different conditions to filter SQL templates. For example, you can filter SQL templates based on databases or rule tags, create download tasks, and then assign different owners to optimize SQL templates based on databases or rule tags.

    • You can also select multiple SQL templates and export the data of the selected SQL templates.

  • Share data.

    You can filter SQL templates and share the filtered SQL templates with the owners of the corresponding SQL templates. You can use one of the following methods to share data:

    • Select SQL templates and click Batch Sharing in the lower part of the section. The system generates an URL. On the page to which the URL directs, the information about the selected SQL templates is displayed. If you are granted the permissions on DAS, you can visit the page to view the information about the selected SQL templates.

    • Specify conditions to filter SQL templates and click Share to the right of Export. The system generates an URL. On the page to which the URL directs, the information about the filtered SQL templates is displayed. If you are granted the permissions on DAS, you can visit the page to view the information about all filtered SQL templates.

Related API operations

Operation

Description

CreateQueryOptimizeTag

Adds one or more tags to an SQL template.

GetQueryOptimizeExecErrorStats

Queries SQL templates that fail to be executed.

GetQueryOptimizeExecErrorSample

Queries the failed SQL statements in an SQL template.

GetQueryOptimizeSolution

Queries the suggestions provided by the query governance feature to optimize an SQL template.

GetQueryOptimizeRuleList

Queries a list of tags added by the query governance feature to slow queries on database instances.

GetQueryOptimizeDataTrend

Queries the trend data of the query governance feature.

GetQueryOptimizeDataTop

Queries the information about the best-performing and worst-performing database instances based on query governance data.

GetQueryOptimizeDataStats

Queries the information about SQL templates based on query governance data.

GetQueryOptimizeTag

Queries the tags of an SQL template.

GetQueryOptimizeShareUrl

Queries a share URL of the query governance feature.