Query governance

Limitations

• Query governance uses T+1 offline analysis. Changes you make today—such as adding the No Need to Optimize tag—take effect the following day.

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

• DAS analyzes only the top 200 most frequently executed slow queries per database instance. Each instance has a maximum of 200 slow SQL templates.

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	Recommended focus	Description
  NEW_SQL	New slow SQL	Warning	✔️	Slow SQL templates that are new compared to the last seven days.
  DAS_IGNORE	Ignored	Notice	➖	DAS automatically tags SQL templates that do not need to be optimized. Examples include SQL statements that start with show, create, xa, commit, rollback, select sleep, or explain.
  FUZZY_LIKE	LIKE query	Notice	➖	Fuzzy matching that uses like cannot use indexes.
  HAS_EXPR	Expressions contained	Notice	➖	The SQL statement contains expressions. Columns used in expressions cannot use indexes.
  LARGE_IN_LIST	Large IN list	Notice	➖	The in clause contains more than 200 elements, which indicates that it may be generated by a program. The associated column cannot use an index.
  SELECT_STAR	SELECT * query	Notice	➖	Specify required fields in the select clause. Do not return unnecessary fields to prevent performance degradation and resource waste.
  INDEX_ADVISOR	Index suggestions	Notice	✔️	Suggestions for creating indexes to accelerate SQL execution.
  COMPLEX_JOIN	Complex JOIN	Notice	➖	Do not join more than three tables. The fields that you want to join must have the same data type. When you associate multiple tables in a query, ensure the associated columns are indexed.
  CROSS_DB	Cross-database query	Notice	➖	When you migrate databases and tables across instances, cross-database queries may become unavailable.
  SUBQUERY	Subqueries included	Notice	➖	The SQL statement contains subqueries. We recommend that you rewrite the subqueries as join operations to improve efficiency.
  DEEP_PAGING	Deep paging	Warning	➖	The SQL statement uses the limit clause for deep paging. We recommend that you rewrite the query to use a join operation.
  WITHOUT_PREDICATE	No predicates	Warning	✔️	The statement contains no predicates. Check whether a full table scan is involved.
  NULL_COMPARE	NULL match error	Warning	➖	Use ISNULL() to check whether a value is NULL. A direct comparison between a NULL value and any other value returns NULL.
  COUNT_NOT_STAR	Invalid COUNT syntax	Warning	➖	Use COUNT(*) instead of count(column_name) or count(constant). count(*) is the standard syntax defined in SQL-92 for counting rows, is database-agnostic, and is not affected by NULL and non-NULL values. count(column_name) does not count rows where this column has a NULL value.
  LARGE_ROWS_EXAMINED	Excessive rows scanned	Notice	➖	The average number of scanned rows exceeds 50,000. Scanning a large number of rows consumes more database resources, which can slow down the query and affect overall database performance. Note We recommend that you add more specific filter conditions to the SQL statement.
  LARGE_ROWS_SENT	Excessive rows returned	Notice	➖	The average number of returned rows exceeds 5,000. Returning a large dataset at once can impact application and network performance.
  NO_ADVICE	No suggestions	Notice	➖	No analysis suggestions are available.
  PERIOD_SQL	Periodic	Notice	➖	The SQL statement is 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	If you add this tag to a SQL template, the template is excluded from the Optimizable SQL category the next day.
  DAS_IMPORTANT	High-priority SQL	Notice	Marks a SQL template as critical, allowing for quick filtering and prioritization.
  DAS_NOT_IMPORTANT	Low-priority SQL	Notice	Marks a SQL template as non-critical for prioritization.
  DAS_IN_PLAN	Optimize Later	Notice	Tag a SQL template that is scheduled for future optimization.

• Export data

  • Click Create Export Task to export all data that is displayed in the SQL Details section.

    Note

    The exported data is stored for 3 days. You can download the data at any time within this period.

  • You can set various filter conditions, such as different instances and rules, to create separate download tasks and assign them to different owners for optimization.

  • You can also select multiple SQL records to export only the selected records.

• Share data

  DAS provides two sharing features that let you quickly share filtered slow queries with the appropriate owners:

  • Select multiple SQL records and click the Batch Sharing button. The system generates a short link. When a user with the required permissions clicks the link, they see only the selected SQL records.

  • Click the Share button next to the Export button. The system generates a short link. After a user with permissions on the DAS console clicks the link, the user is taken to the same view with all your filters applied.