All Products
Search
Document Center

Database Autonomy Service:Slow query logs

Last Updated:Dec 21, 2023

Slow SQL queries significantly decrease the database stability. When issues such as high workloads and performance jitters occur on databases, database administrators (DBAs) or developers first check whether slow query logs are generated. Database Autonomy Service (DAS) provides the log analysis feature for slow queries. DAS collects statistics on and analyzes SQL statements whose execution duration exceeds the specified threshold value and provides solutions. You can troubleshoot database performance issues with ease. This enhances system reliability and stability.

Background information

Slow query logs are generated by the database kernel. Relevant parameters and thresholds vary based on the database engine. For more information, see the corresponding official documentation.

Procedure

  1. Log on to the DAS console.

  2. In the left-side navigation pane, click Instance Monitoring.

  3. On the page that appears, find the database instance that you want to manage and click the instance ID. The instance details page appears.

  4. In the left-side navigation pane, choose Request Analysis > Slow Logs.

  5. On the Slow Logs page, specify a time range and view the trends, statistics, and details of the slow query logs within the specified time range.

    Note

    When you specify the time range, the end time must be later than the start time, and the interval between the start time and the end time cannot exceed 24 hours. You can query slow query logs within the previous month.

    • In the Slow Log Trend section, you can click a point in time in the trend chart and view the statistics and details of the slow query logs at the point in time.

      Note

      If the content of an SQL statement is not completely displayed due to length limits of the GUI, you can move the pointer over the SQL statement to view the complete content.

    • You can click Export Slow Log to download slow query logs to your computer.

    • On the Slow Log Statistics tab, you can perform the following operations:

      • Find the SQL template that you want to manage and click Sample in the Actions column. In the Slow Log Sample dialog box, view the slow query log sample of the SQL template.

      • Find the SQL template that you want to manage and click Optimize in the Actions column. In the SQL Diagnostic Optimization dialog box, view the SQL diagnostics results.

        If you accept the SQL optimization suggestions, click Copy in the upper-right corner and paste the optimized SQL statement to the database client or Data Management (DMS) for execution. If you do not accept the SQL optimization suggestions, click Cancel.

        Note

        DAS performs SQL diagnostics based on the complexity of SQL statements, the amount of data in the table, and the database workload. Suggestions may be returned in more than 20 seconds after the SQL diagnostics is performed. After the diagnostics is complete, the SQL diagnostics engine provides diagnostics results, optimization suggestions, and expected optimization benefits. You can determine whether to accept the suggestions based on the diagnostics results.

        You can also click Database Expert Service to purchase the expert service. Database Expert Service provides value-added professional database services, such as emergency assistance, health diagnostics, performance optimization, security assurance, and data migration.

    • On the Slow Log Details tab, find the SQL statement that you want to manage and click Optimize in the Actions column. This way, you can also perform SQL diagnostics.