Monitor Autonomous Transaction Processing 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 Transaction Processing.

  1. From the Autonomous Transaction Processing landing pad select an instance.

  2. From the Details page click Performance Hub.

The Performance Hub page is displayed. This page has the following sections:

  • The time selector.

  • The Reports drop-down list, containing the option to create and download an AWR (Automatic Workload Repository) report.

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

The Time Selector

The time selector is a set of controls at the top of the Performance Hub page. You use these controls to set the time range for Performance Hub to monitor.

You can use the Quick Select list to quickly set the time range to Last Hour, Last 8 Hours, Last 24 Hours or Last Week, or you can click the Time Range field and specify a custom time range. Additionally, you can use the Time Zone list to have all times based on UTC (Coordinated Universal Time) time, your local web browser time, or the time zone setting of the database.

The Activity timeline shows active sessions during the selected time range. It displays the average number of active sessions broken down by CPU, User I/O, and Wait. It also shows the Max CPU usage.

The sliding box on the timeline is the time slider. Use the time slider to select a section of the time range.

You can slide the box to the left or the right to shift the time selection, and you can widen or narrow the box to increase or decrease the section's timespan. To slide the entire box, left-click anywhere inside the box and drag the box to the left or the right. 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 width of the time slider.

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

The AWR Report

The Automatic Workload Repository (AWR) collects, processes, and maintains performance statistics for problem detection and self-tuning purposes. This data is both in memory and stored in the database. From the Performance Hub you can generate and download a report of the gathered data.

An AWR report shows data captured between two points in time (or snapshots). AWR reports are divided into multiple sections. The content of the report contains the workload profile of the system for the selected range of snapshots. The HTML report includes links that you can use to navigate quickly between sections.

The statistics collected and processed by AWR include:

  • Object statistics that determine both access and usage statistics of database segments
  • Time model statistics based on time usage for activities, displayed in the V$SYS_TIME_MODEL and V$SESS_TIME_MODEL views
  • Some of the system and session statistics collected in the V$SYSSTAT and V$SESSTAT views
  • SQL statements that are producing the highest load on the system, based on criteria such as elapsed time and CPU time
  • ASH statistics, representing the history of recent sessions activity

For more information about using an AWR report, see Using the AWR Compare Periods Reports.

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.