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 navigation pane on the left, click Request Analysis > Slow Logs > Query Governance.

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

   • Query governance overview: View the categorized results of the query analysis.

     Note

     The number of Failed SQL Executions is counted only for instances with DAS Enterprise Edition enabled.

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

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

     • Worst-performing instances: Ranks database instances by the number of slow query executions.

     • Best-performing instances: Shows the change in the number of slow query executions. A negative value indicates a decrease, which suggests effective optimization. A positive value indicates an increase.

     DAS recommends that you focus on the rankings for Optimizable SQL.

   • SQL to Be Optimized: You can set filter conditions to find specific SQL statements that require governance.

     Note

     You can filter by DB Name, SQL Keyword, rule tag, and Database Username. These filters are combined using AND logic.

     • To filter by multiple DB names, separate them with commas (,). This creates an OR condition.

     • To filter by multiple SQL keywords, separate them with spaces. This creates an AND condition.

     • To filter by multiple database usernames, separate them with commas (,). This creates an OR condition.

     • You can select multiple rule tags. This creates an OR condition between the selected tags.

     • In the Actions column for a target SQL sample, click Suggestions to view detailed governance recommendations.

     • In the Actions column, click Add Tag to manually tag an SQL statement. For details about the tags, see user-defined tag.

       You can also select multiple SQL statements to tag them in a batch.

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

     • In the Actions column, click Trend to view the slow query analysis details for the SQL statement. For more information about slow query log analysis, see slow query log.

     You can export and share the SQL to be optimized data as needed. For more information, see Best practices.

   • Failed SQL: You can set filter conditions to find specific SQL statements.

     Note

     • Statistics for Failed SQL are collected only for instances with DAS Enterprise Edition enabled.

     • You can filter by DB Name and SQL Keyword. These filters are combined using AND logic.

       • To filter by multiple DB names, separate them with commas (,). This creates an OR condition.

       • To filter by multiple SQL keywords, separate them with spaces. This creates an AND condition.

     In the Actions column for a target SQL sample, click Samples to view the sample details for the SQL statement.

Best practices

• Use tags to distinguish which SQL statements require optimization.

  The core governance logic is to divide Overall Slow Queries into two categories: slow queries classified as Optimization Not Required and Optimizable SQL. You can use the following SQL tags to quickly filter and manage them based on 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 use the following tags to mark SQL statements as Optimization Not Required to continuously reduce the number of Optimizable SQL. The following table describes the tags that you can add 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.

  • You can create different download tasks by setting different filter conditions, such as different instances or rules, and then assign them to different owners for optimization.

  • You can also select multiple SQL records. In this case, the export task includes only the selected SQL records.

• Share data:

  DAS provides two ways to share filtered slow SQL queries with team members:

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