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.

   Filter by time range, region, and engine, or enter an instance ID to view results for a specific instance.

   • Query Governance Overview: View results 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 by slow query execution count in descending order to identify the most problematic instances.

     • Best-performing Instances: Ranks instances in ascending order by the change in slow query execution count. A negative value indicates fewer slow query executions (good optimization), while a positive value indicates more.

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

   • SQL to Be Optimized: Set filters to identify 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.

     Export and share optimizable SQL statement data as needed. For more information, see best practices.

   • Failed SQL: Set filters to identify the SQL statements 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:

  Governance classifies statements from the Overall Slow SQL list into two categories: Optimization Not Required and Optimizable SQL. Use the following SQL tags to filter and prioritize these statements.

  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.

  Focus on Optimizable SQL. Use the tags below to mark SQL statements as Optimization Not Required and reduce the number of Optimizable SQL statements. The following tags support manual tagging.

  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.

API reference