5 Monitor and Diagnose the Performance of Autonomous Databases
On the Performance Hub page in Oracle Database Management, you can monitor the performance of your Autonomous Database entities and ensure that these entities perform optimally.
This page allows you to view all the performance data available for a specific time period. Once a time period is selected, the database performance is displayed.
To go to the Performance Hub of an Autonomous Database entity, click the Performance tile on the Database Home page of the entity or Performance Hub in the Oracle Database navigation menu.
The Performance Hub consists of the following sections:
- Time Selector field and
Time Range:
The Time Selector field is displayed on the top-right corner and performance data for the time selected in this field is displayed on the Performance Hub. By default, Last 60 mins is selected in this field.
The Time Range on the Performance Hub shows the Active Sessions chart for the time period selected in the Time Selector field. The Active Sessions chart displays the average number of active sessions broken down by Wait, User I/O, and CPU. You can use the Time Range Slider to select a shorter period of time for which data is displayed on the Performance Hub, and this is a subset of the period of time selected in the Time Selector field.
- ASH Analytics tab:
The ASH Analytics tab shows Active Session History (ASH) analytics charts to explore ASH data. It allows you to drill down into database performance across multiple dimensions such as Consumer Group, Wait Class, SQL ID, and User Name. On the ASH Analytics tab, you can perform tasks such as:
- Selecting an Average Active Sessions dimension and viewing the top activity for that dimension for the selected time period. For information on ASH, see Active Session History (ASH) in Oracle Database Concepts.
- Generating an Automatic Workload Repository (AWR) report. For information on AWR, see Automatic Workload Repository (AWR) in Oracle Database Concepts.
- SQL Monitoring tab:
The SQL Monitoring tab is not displayed by default and to view it, click SQL Monitoring on the Performance Hub.
The SQL Monitoring tab enables real-time SQL monitoring and allows you to view long running SQL statements in your Autonomous Database. On this tab, you can drill down into individual SQL statements, view the execution plan and the amount of database time being used by each SQL execution plan line, and identify and analyze resource intensive SQL statements. Note that SQL statements are only monitored if they've been running for at least five seconds or if they're run in parallel.
The table on this tab displays monitored SQL statement executions by dimensions such as Last Active Time, CPU Time, and Database Time. The columns in the table provide information such as Status, Duration, and SQL ID.
The Status column has the following icons:
- A green spinning icon indicates that the SQL statement is executing.
- A red cross icon indicates that the SQL statement did not complete either due to an error or due to the session getting terminated.
- A check mark icon indicates that the SQL statement completed its execution during the specified time period.
- A clock icon indicates that the SQL statement is queued.
To analyze a SQL statement, click the SQL ID to go to the corresponding Monitored SQL Execution Details page. You can use the information available on this page to tune the SQL statement.
If a SQL statement is running or queued and you want to terminate the session, click Kill Session on the SQL Monitoring tab.
- Workload tab:
The Workload tab is not displayed by default and to view it, click Workload on the Performance Hub.
The Workload tab displays charts of the various metrics supported for Autonomous Databases, which enable a quick interpretation of data.