Database Autonomy Service:Performance insight (new version)

Billing

The new version of performance insight is in public preview. You can use the new version for free during the public preview.

Introduction

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, click Performance Insight.

5. On the Performance Insight tab, click Enable Performance Insight.

   If the Performance Schema feature is disabled, the Parameter Settings dialog box appears. You must click Configure to configure the parameters that are related to the Performance Schema feature. For more information, see Enable the Performance Schema feature.

   Warning

   To make the modifications to the parameters take effect, you must restart the database instance. We recommend that you perform this operation during off-peak hours and make sure that your application is configured to automatically reconnect to the database instance.

   When you enable the performance insight feature, the system automatically enables the feature that identifies tables and indexes on which no operations are performed.

   Important

   From April 11, 2023, the performance insight feature supports the feature that identifies tables and indexes on which no operations are performed. This feature is automatically enabled when you enable the performance insight feature. If the performance insight feature was enabled for your database instances before April 11, 2023, you can manually turn on the Identification for Tables and Indexes with No Traffic switch in the Service Settings dialog box.

6. On the Performance Insight tab, click one of the following tabs based on your business requirements.

   

   • Display by Time Range:

     • Select a time range that you want to monitor to view the Resource Usage, Executions, Scanned Rows, and Execution Duration trend charts of all SQL statements within the specified time range.

       Note

       When you select a time range, the end time must be later than the start time. You can query data within up to seven days within the last month.

     • Click Export to save the data as a file to your computer.

     • Click More Metrics to view the trend charts of key performance metrics.

       Note

       You can click Settings to select the metrics that you want to view.

       

     • In the SQL list section, perform the following operations to view the details of SQL statements.

       Note

       You can click Settings, find the Column Settings section in the dialog box that appears, and then select the performance metrics to be displayed in the SQL list.

       • Click the ID of the SQL statement that you want to manage to view the Resource Usage, Executions, Scanned Rows, and Execution Duration trend charts of the SQL statement within a specific time range. You can also view the details of the SQL sample related to the SQL statement.

       • Click Sample in the Actions column corresponding to the SQL statement that you want to manage to view the details of the SQL sample related to the SQL statement.

       • Click Throttling in the Actions column of the SQL statement that you want to manage. In the SQL Throttling dialog box, configure throttling parameters to throttle the SQL statement. For more information, see SQL throttling.

       • Click Optimize in the Actions column corresponding to the SQL statement that you want to manage. In the SQL Diagnostic Optimization dialog box, view the diagnostic results.

         If you accept the SQL optimization suggestions, click Copy 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 load. Suggestions may be returned 20 seconds after the SQL diagnostics is performed. After the diagnostics is complete, the SQL diagnostic engine provides diagnostic results, optimization suggestions, and expected optimization benefits. You can determine whether to accept the suggestions based on the diagnostic 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 solutions, health diagnostics, performance optimization, security assurance, and data migration.

   • Display by Comparison: Select a point in time at which you want to compare the performance insight results to view the comparison results of the resource usage, number of executions, number of scanned rows, and execution duration of all SQL statements at the specified point in time. You can also view the detailed comparison results in the SQL list section.

     Note

     You can click Settings to select the performance metrics to be displayed in the SQL list.

   • Tables/Indexes: You can specify filter conditions to query the statistics on tables and indexes on which no operations are performed in the Tables with No Traffic and Indexes with No Traffic sections.

     You can click Export to save the filtered data to your computer.

Disable the performance insight feature

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, click Performance Insight.

5. On the Performance Insight tab, click Service Settings. In the Service Settings dialog box, turn off Feature Setting and click OK. In the message that appears, click OK to disable the Performance Insight feature.

   After you disable the performance insight feature, the feature that identifies tables and indexes on which no operations are performed is also disabled. You can also disable only the feature that identifies tables and indexes on which no operations are performed. After this feature is disabled, DAS deletes the statistics on tables and indexes on which no operations are performed and the statistics on tables and indexes on which operations are performed.

   Important

   After the performance insight feature is disabled, DAS does not collect data and deletes historical data, including the statistics on tables and indexes on which no operations are performed and the statistics on tables and indexes on which operations are performed.

   

References

You can enable the autonomy features of DAS to implement automatic governance to resolve performance issues.

• Automatic SQL throttling

• Automatic SQL optimization

• Automatic tablespace fragment recycling