Monitor the Performance of Autonomous Transaction Processing

The Overview and Activity tabs in the Service Console provide information about the performance of the service. The Activity tab also shows past and current monitored SQL statements and detailed information about each statement.

Monitor Activity and Utilization

The Overview and Activity tabs show real-time and historical information about the utilization of the service.

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

From the Oracle Cloud Infrastructure left navigation list click Autonomous Transaction Processing.

See Signing in to Your Cloud Account in Getting Started with Oracle Cloud.

The Overview tab shows general information about the utilization of the service.

  1. From the Autonomous Transaction Processing landing pad select an instance.
  2. From the Details page click Service Console.
  3. Autonomous Transaction Processing shows the service console.

Note:

You can bookmark the service console URL and go to that URL directly without logging in to the Oracle Cloud Infrastructure console. If you logout and use the bookmark, then to see the service console you need to enter the ADMIN username, the password, and click Sign in. See Change the Administrator Password in Autonomous Transaction Processing if you need to change the password for the ADMIN user.

Console Overview

The Overview page shows real-time and historical information about the utilization of the service.

The components on this page are:

  • Storage

    This chart shows the total and used storage capacity of the service. It indicates what percentage of the space is currently in-use. The used storage capacity is the total allocated space for all tablespaces.

  • CPU utilization (%)

    This chart shows the historical CPU utilization of the service:

    • Auto Scaling Disabled: This chart shows hourly data. A data point shows the average CPU utilization for that hour. For example, a data point at 10:00 shows the average CPU utilization for 9:00-10:00.

      The utilization percentage is reported with respect to the number of CPUs the database is allowed to use which is two times the number of OCPUs. For example, if the database has four (4) OCPUs, the percentage in this graph is based on 8 CPUs.

    • Auto Scaling Enabled: For databases with auto scaling enabled the utilization percentage is reported with respect to the maximum number of CPUs the database is allowed to use, which is six times the number of OCPUs. For example, if the database has four OCPUs with auto scaling enabled the percentage in this graph is based on 24 CPUs.
  • Running SQL statements

    This chart shows the average number of running SQL statements historically. This chart shows hourly data. A data point shows the running SQL statements for that hour. For example, a data point at 10:00 shows the average number of running SQL statements for 9:00-10:00.

  • Number of OCPUs allocated

    This chart shows the number of OCPUs allocated:

    • Auto Scaling Disabled: For databases with auto scaling disabled, for each hour the chart shows the number of OCPUs allocated to the database if the database is open for at least some part of the hour.
    • Auto Scaling Enabled: For databases with auto scaling enabled, for each hour the chart shows the average number of OCPUs used during that hour if that value is higher than the number of OCPUs provisioned. If the number of OCPUs used is not higher than the number of OCPUs provisioned, then the chart shows the number of OCPUs allocated for that hour.
    • Stopped Database: If the database was stopped for the full hour the chart shows 0 OCPUs allocated for that hour.
  • SQL statement response time (s)

    This chart shows the average response time, in seconds, of SQL statements historically. This chart shows hourly data. A data point shows the average SQL statement response time for that hour. For example, a data point at 10:00 shows the average SQL statement response time, in seconds, for the hour from 9:00-10:00.

  • SQL statements executed per second

    This chart shows the SQL statements executed per second.

The default retention period for performance data is eight days. So, the CPU utilization, running statements, and average SQL response time charts show data for the last eight days by default.

Note:

The retention time can be changed by modifying the Automatic Workload Repository retention setting with the PL/SQL procedure DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS. Be aware that increasing the retention time will result in more storage usage for performance data. See Oracle Database PL/SQL Packages and Types Reference.

Console Activity

The Activity page shows real-time and historical information about the utilization of the service.

To access detailed information about the service performance click the Activity tab in the service console.

Note:

The default view in this tab is real-time. This view shows performance data for the last hour.

The components on this page are:

  • Database Activity

    This chart shows the average number of sessions in the database using CPU or waiting on a wait event. See Oracle Database Reference for more information on wait events.

  • CPU Utilization

    This chart shows the CPU utilization of each consumer group. The utilization percentage is reported with respect to the number of CPUs the database is allowed to use which is two times the number of OCPUs. For example, if the database has four (4) OCPUs, the percentage in this graph is based on 8 CPUs.

    For databases with auto scaling enabled the utilization percentage is reported with respect to the maximum number of CPUs the database is allowed to use, which is six times the number of OCPUs. For example, if the database has four OCPUs with auto scaling enabled the percentage in this graph is based on 24 CPUs.

    See Managing Priorities on Autonomous Transaction Processing for detailed information on consumer groups.

  • Running Statements

    This chart shows the average number of running SQL statements in each consumer group. See Managing Priorities on Autonomous Transaction Processing for detailed information about consumer groups.

  • Queued Statements

    This chart shows the average number of queued SQL statements in each consumer group. See Managing Priorities on Autonomous Transaction Processing for detailed information on consumer groups.

To see earlier data click Time period. The default retention period for performance data is eight days. So, this view shows information for the last eight days by default.

Note:

The retention time can be changed by changing the Automatic Workload Repository retention setting with the PL/SQL procedure DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS. Be aware that increasing the retention time results in more storage usage for performance data. See Oracle Database PL/SQL Packages and Types Reference.

In the time period view you can use the calendar to look at a specific time in the past eight days.

You can also use the time slider to change the period for which performance data is shown.

Monitor SQL Statements

The Monitored SQL tab shows information about current and past monitored SQL statements.

See About Monitoring Database Operations in Oracle Database SQL Tuning Guide.

Click the Monitored SQL tab to see these statements.

  1. To see the detailed SQL Monitor report for a statement, select a statement and click Show Details. The Overview tab in the pop-up shows general information for that statement.

    Click Plan Statistics tab to see the runtime execution plan of the statement.

    Click Parallel tab to see information about the parallel processes, if the statement uses parallelism.

If you want to download an active SQL Monitor report for a statement, select the statement in the Monitored SQL page and click Download report. This will save the active SQL Monitor report to your client. See About Monitoring Database Operations in Oracle Database SQL Tuning Guide.

To cancel a running statement, select that statement in the Monitored SQL list and click Cancel execution.