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 Automatic Workload Repository (AWR) report.

  • The tabbed data area, with the tabs ASH Analytics, SQL Monitoring, ADDM 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 Automatic Workload Repository 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.

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

Follow these stepts to generate and download an AWR report from the Performance Hub:

  1. Use the time selector controls to specify a time range that includes the period for which you want to generate the AWR report.
  2. Click Reports and then choose Automatic Workload Repository to display the Generate Autonomatic Workload Respository Report dialog.
  3. Choose the Start Snapshot and End Snapshot for the report and then click Download.
  4. Performance Hub generates the report, displays the name of the report file, and, depending on your browser settings, automatically downloads it to your default download location or prompts you to specify a download location.

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.

You can use the ASH Sample Resolution drop-down list to control the data resolution of the chart, from the coarsest setting of Low, which shows the fewest data points and has the largest interval between data points, to the finest setting of Maximum, which shows the most data points and has the smallest interval between data points. Note that the higher the setting you choose, the longer it takes Performance Hub to retrieve data and display the chart.

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 ADDM Tab

This tab provides access to analysis information gathered by the Automatic Database Diagnostic Monitor (ADDM) tool. ADDM analyzes AWR (Automatic Workload Repository) snapshots on a regular basis, locates root causes of any performance problems, provides recommendations for correcting the problems, and identifies non-problem areas of the system. Because AWR is a repository of historical performance data, ADDM can analyze performance issues after the event, often saving time and resources in reproducing a problem.

After selecting the ADDM tab, use the time selector controls to specify a time range that includes the period for which you want to view ADDM info. Then, select which ADDM analysis dataset you want to view from the Findings for ADDM Task list.

Performance Hub retrieves the ADDM analysis information:

  • A table of findings that shows performance symptoms and problems. For findings that are problems, you can click the name of the finding to display details and recommendations regarding it.
  • Additional Findings, which lists findings that may affect the completeness or accuracy of the ADDM analysis and findings that are not intrinsically performance issues but can help you understand how the database is performing.

For more information about ADDM findings and how to interpret them, see ADDM Analysis Results in Oracle Database Performance Tuning Guide.

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.