Query governance

Video tutorial

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 left-side navigation pane, choose Intelligent O&M Center > Query Governance.

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

   You can view query governance results for a specific instance by filtering by time range, region, and engine, or by entering an instance ID.

   • Query Governance Overview: View the result data after the system tags and classifies SQL templates.

     Note

     DAS counts Failed SQL Executions only on instances for which DAS Enterprise Edition is 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: Ranks instances in descending order of their slow query execution counts to help you quickly identify the most problematic instances.

     • Best-performing Instances: Ranks instances in ascending order based on the change in their slow query execution counts. A negative value indicates a decrease in slow query executions (good optimization results), while a positive value indicates an increase. This helps you quickly identify the instances with the best governance results.

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

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

     Note

     These filters are combined with AND logic.

     • Separate multiple database names with commas (,) to combine them with OR logic.

     • Separate multiple SQL keywords with spaces to combine them with AND logic.

     • Separate multiple database usernames with commas (,) to combine them with OR logic.

     • Selecting multiple rule tags combines them with OR logic.

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

     • In the Actions column for a target SQL sample, click Add Tag to manually tag the SQL statement. For more information about the tags, see Manual Tagging.

       You can also select and tag multiple SQL statements at once.

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

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

     You can export and share the data of optimizable SQL statements as needed. For more information, see best practices.

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

     Note

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

     • You can filter by database name and SQL keyword. These two filters are combined by using AND logic.

       • Multiple database names are separated by commas (,) and are combined by using OR logic.

       • Multiple SQL keywords are separated by spaces and are combined by using AND logic.

     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 determine whether to optimize SQL queries:

  The core logic of governance is to classify the statements from the Overall Slow SQL list into two categories: Optimization Not Required and Optimizable SQL. You can use the following SQL tags to quickly filter these statements and manage them by 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 table below to tag SQL statements as Optimization Not Required to continuously reduce the number of Optimizable SQL statements. The following SQL tags support manual tagging by users.

  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.

API reference