Database Autonomy Service:Query governance

Limitations

• Query governance uses T+1 offline analysis. If you tag a SQL query as Optimization Not Required, the change in the number of Optimizable SQL queries appears on the following day.

• Predefined SQL tags from DAS cannot be disabled.

• For each database instance, DAS analyzes only the top 200 slow SQL queries by execution count. Therefore, the maximum number of Slow SQL Templates for each instance is 200.

Key concepts

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, choose Request Analysis > Slow Logs > Query Governance.

5. On the Query Governance page, view the analysis results.

   • Overview of query governance results: View statistical data of automatically tagged and classified queries.

     Note

     DAS counts the Failed SQL Executions only for instances with DAS Enterprise Edition enabled.

   • Query governance trend: View the trend of query governance results over the selected time range.

   • Top Rankings: View the Best-performing Instances and Worst-performing Instances lists.

     • Worst-performing Instances: View the number of slow query executions for each database instance.

     • Best-performing Instances: View the change in the number of slow query executions for each database instance. A negative number indicates a decrease in the number of slow query executions, indicating successful optimization. A positive number indicates an increase.

     DAS recommends that you focus on the top-ranking lists for Optimizable SQL.

   • SQL to Be Optimized: Set filter conditions to identify the SQL statements that you want to optimize.

     Note

     You can filter SQL statements by DB name, SQL keyword, rule tag, and database username. These conditions are combined with the logical AND operator.

     • To specify multiple DB names, separate them with commas (,). The logical operator between them is OR.

     • To specify multiple SQL keywords, separate them with spaces. The logical operator between them is AND.

     • To specify multiple database usernames, separate them with commas (,). The logical operator between them is OR.

     • You can select multiple rule tags. The logical operator between the rule tags is OR.

     • In the Actions column of a specific SQL sample, click Suggestions to view detailed optimization suggestions.

     • In the Actions column of a specific SQL sample, click Add Tag to manually tag the SQL statement. For more information about the meanings of tags, see Manually added SQL tags.

       You can also add tags to multiple SQL statements at once.

     • In the Actions column of a specific SQL sample, click Samples to view the details of the slow log sample for the SQL statement.

     • In the Actions column of a specific SQL sample, click Trend to view the slow log analysis details of the SQL statement. For more information about slow log analysis, see Slow Logs.

     You can export and share data for the SQL statements that need optimization. For more information, see Best practices.

   • Failed SQL: Set filter conditions to identify the SQL statements that you want to view.

     Note

     • DAS counts Failed SQL templates only for instances with DAS Enterprise Edition enabled.

     • You can filter SQL statements by DB name and SQL keyword. These conditions are combined with the logical AND operator.

       • To specify multiple DB names, separate them with commas (,). The logical operator between them is OR.

       • To specify multiple SQL keywords, separate them with spaces. The logical operator between them is AND.

     In the Actions column of a specific SQL sample, click Samples to view the details of the SQL sample.

Best practices

• Use tags to determine whether a SQL statement needs to be optimized:

  The core logic of query governance is to divide all slow queries into two categories: Optimization Not Required and Optimizable SQL. You can use the following SQL tags to quickly filter the queries and optimize them based on their priority.

  ID	Tag name	Level	Recommendation	Description
  NEW_SQL	New slow SQL	Critical	✔️	A slow SQL query that has not appeared in the past seven days.
  DAS_IGNORE	DAS ignored	Notice	➖	DAS automatically tags the query as not requiring optimization. Examples include SQL statements that start with show, create, xa, commit, rollback, select sleep, or explain.
  FUZZY_LIKE	Fuzzy LIKE query	Notice	➖	A like clause is used for fuzzy matching, which prevents the use of an index.
  HAS_EXPR	Contains expression	Notice	➖	The query contains an expression. The column involved in the calculation cannot use an index.
  LARGE_IN_LIST	Large IN list	Notice	➖	The IN clause contains more than 200 elements, which is likely generated by a program. Indexes cannot be used on the involved columns.
  SELECT_STAR	SELECT full column	Notice	➖	When you use SELECT, specify only the required fields to avoid performance issues and resource waste caused by returning unnecessary columns.
  INDEX_ADVISOR	Index suggestion	Notice	✔️	A suggestion to create an index to accelerate SQL execution.
  COMPLEX_JOIN	Complex JOIN	Notice	➖	Do not join more than three tables. The fields that you join must have identical data types. For multi-table joins, ensure that the join fields are indexed.
  CROSS_DB	Cross-database query	Notice	➖	When you migrate databases and tables across instances, cross-database queries may become unavailable.
  SUBQUERY	Contains subquery	Notice	➖	The query contains a subquery. Consider rewriting it with a JOIN for better performance.
  DEEP_PAGING	Deep paging	Critical	➖	The query uses a LIMIT clause for deep paging. We recommend rewriting the query with a join.
  WITHOUT_PREDICATE	No predicate	Critical	✔️	The statement contains no predicates. Check whether it involves a full table scan.
  NULL_COMPARE	NULL match error	Critical	➖	Use IS NULL or IS NOT NULL to check for a NULL value, because a direct comparison between a value and NULL always results in NULL.
  COUNT_NOT_STAR	Improper COUNT syntax	Critical	➖	Use COUNT(*) instead of count(column_name) or count(constant). count(*) is the standard syntax for counting rows as defined in SQL92. It is database-independent and is not affected by NULL or non-NULL values. count(column_name) does not count rows where the column has a NULL value.
  LARGE_ROWS_EXAMINED	Excessive rows scanned	Notice	➖	The query scans more than 50,000 rows on average. Excessive scanning consumes more resources, which slows down the query and can impact overall database performance. Note Consider using more selective filter conditions.
  LARGE_ROWS_SENT	Excessive rows returned	Notice	➖	The query returns more than 5,000 rows on average. Consider reducing the number of returned rows.
  NO_ADVICE	No suggestion	Notice	➖	No analysis suggestion is available.
  PERIOD_SQL	Periodic SQL	Notice	➖	Executed at a fixed time every day.

  DAS recommends that you focus on Optimizable SQL. You can tag queries as Optimization Not Required, as described in the table below, to continuously reduce the number of queries in the Optimizable SQL category. The following SQL tags can be added manually.

  ID	Tag name	Level	Description
  USER_IGNORE	No need to optimize	Notice	After you add this tag, DAS excludes the query from the next day's Optimizable SQL statistics.
  DAS_IMPORTANT	Important SQL	Notice	Marks the SQL query as important.
  DAS_NOT_IMPORTANT	Not important SQL	Notice	Marks the SQL query as not important.
  DAS_IN_PLAN	Optimization planned	Notice	Marks the SQL query as scheduled for optimization.

• Export data:

  • You can click Create Export Task to download all data currently displayed in the SQL Details tab.

    Note

    Exported data is available for download for three days.

  • Create download tasks with different filter conditions, such as instances or rules, and assign them to different owners for optimization.

  • Alternatively, select multiple SQL records to export only those items.

• Share data:

  Share filtered slow SQL queries with team members in two ways:

  • You can select multiple SQL records and click Batch Sharing. The system generates a short link. When users who have permissions to access the DAS console click the link, they will see only the SQL records that you selected.

  • Alternatively, click Share (next to Export) to generate a URL that captures your current view, including all filters. Users with DAS console permissions can open the URL to see the same data without reconfiguring the filters.