You can analyze slow query to troubleshoot the performance issues of a database instance. This method is common and effective. SQL queries that consume a large amount of CPU resources or I/O resources, require a long period of time to be executed, or scan a large number of rows may be slow queries. You can use Database Autonomy Service (DAS) to monitor your databases to identify and optimize slow queries, which can help you solve performance issues.

Solutions

To troubleshoot slow queries, use one of the following methods:
  • View and optimize the slow queries of a single database instance
  • Search and manage slow queries on all your database instances
  • Enable automatic SQL optimization

View and optimize the slow queries of a single database instance

  1. You can use the slow query log analysis feature of DAS to view the trends, statistics, and details of slow queries. For more information, see Slow query log analysis.
  2. You can also use DAS to diagnose and optimize slow queries. For more information, see SQL Optimization.

Search and manage slow queries on all your database instances

DAS provides the query governance feature. This feature uses offline data analysis to automatically analyze all slow queries that are executed on your database instances during the previous day and adds tags to SQL templates at 01:00:00 every day. This can help you categorize SQL templates and optimize SQL templates based on the severity levels that tags indicate. DAS also provides suggestions to optimize SQL templates and provides the data export feature. For more information, see Query governance.

Enable automatic SQL optimization

DAS can automatically analyze and optimize your SQL queries and create indexes. This can help you quickly solve slow query issues and ensure the performance of your database. For more information, see Automatic SQL optimization.