Monitor Autonomous Data Warehouse with Performance Hub

You can view real-time and historical performance data from the Performance Hub. Performance Hub shows Active Session History (ASH) analytics, SQL monitoring and workload information.

Sign in to your Oracle Cloud Account at cloud.oracle.com.

From the Oracle Cloud Infrastructure left navigation list click Autonomous Data Warehouse.

  1. From the Autonomous Data Warehouse landing pad select an instance.

  2. From the Details page click Performance Hub.

The Performance Hub page is displayed. This page has two sections:

  • The Time Range selector.

  • The tabbed data area, with the tabs ASH Analytics, SQL Monitoring and Workload.

The Time Range Selector

The Time Range selector is on the top of the Performance Hub page. Use the Select Duration field to set the time duration. By default, Last hour is selected. You can choose to view Last 8 hours, Last 24 hours, Last week, or specify a custom time range using the Custom option.

The Time Range field shows active sessions in chart form for the time period selected. The active sessions chart displays the average number of active sessions broken down by CPU, User I/O, and Wait. The active sessions chart also shows the Max CPU usage.

The sliding box on the time range chart is the time slider. Use the time slider to select the exact period of time for which data is displayed in the Performance Hub tables and graphs. This is a subsection of the period of time shown in the Time Range field.

You can slide the box to the left or the right to shift the time period under analysis. To slide the entire box, left-click anywhere inside the box and drag the box to the left or the right. Widen or narrow the box to increase or decrease the length of time under analysis. To widen or narrow the box, left-click and hold the handlebar on either side of the box, then drag to the left or the right to increase or decrease the size of the current time range box.

Click Refresh to refresh the data in Performance Hub according to the time range chosen.

The ASH Analytics Tab

This tab, which is displayed by default, shows Active Session History (ASH) analytics charts to explore Active Session History data. You can drill down into database performance across multiple dimensions such as Consumer Group, Wait Class, SQL ID, and User Name. Select an Average Active Sessions dimension and view the top activity for that dimension for the selected time period.

See Active Session History (ASH) in Oracle Database Concepts for more information on Active Session History.

The SQL Monitoring Tab

The SQL statements are only monitored if they've been running for at least five seconds or if they're run in parallel. The table displays monitored SQL statement executions by dimensions including Last Active Time, CPU Time, and Database Time. The table displays currently running SQL statements and SQL statements that completed, failed, or were terminated. The columns in the table provide information for monitored SQL statements including Status, Duration, and SQL ID.

The Status column has the following icons:

  • A spinning icon indicates that the SQL statement is executing.
  • A green check mark icon indicates that the SQL statement completed its execution during the specified time period.
  • A red cross icon indicates that the SQL statement did not complete, either due to an error, or due to the session being terminated.
  • A clock icon indicates that the SQL statement is queued.

To terminate a running or queued SQL statement, click Kill Session.

Select the link in the SQL ID column to go to the corresponding Real-time SQL Monitoring page. This page provides additional details to help you tune the selected SQL statement.

The Workload Tab

This tab shows four chart areas that show the workload on the database in various ways:

  • CPU Statistics: Charts CPU usage as measured by the statistic you select:

    • CPU Time: Shows how many CPU seconds are being used per second by the database's foreground sessions.

    • CPU Utilization (%): Shows the CPU usage of all the database's consumer groups as a percentage of the number of CPUs the database is allowed to use.

  • Wait Time Statistics: Shows the wait time across the database's foreground sessions, divided by wait classes.

  • Workload Profile: Charts user (client) workload on the database as measured by the statistic you select:

    • User Calls and Transactions: Shows the User Calls, Executions and Transactions statistics in a single, consolidated chart.

    • User Calls: Shows the number of user calls (such as login, parse, fetch, or execute) per second.

    • Executions: Shows the number of executed SQL statements per second, whether initiated directly by a user or recursively.

    • Transactions: Shows the combined number of user commits and user rollbacks per second.

    • Parses: Shows the combined number of hard and soft parses per second.

    • Running Statements: Shows the number of running SQL statements across all the database's consumer groups.

    • Queued Statements: Shows the number of queued parallel SQL statements across all the database's consumer groups.

  • Sessions:

    • Current Logons: Shows the number of current successful logons.

    • Sessions: Shows the number of sessions.