Monitor the Performance of Autonomous Database

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

Open Service Console to Monitor Activity and Utilization

On the Service Console the Overview and Activity tabs show real-time and historical information about the utilization of an Autonomous Database.

Perform the following prerequisite steps as necessary:

  • Open the Oracle Cloud Infrastructure Console by clicking the navigation icon next to Oracle Cloud.

  • From the Oracle Cloud Infrastructure left navigation menu click Oracle Database and then, depending on your workload click one of: Autonomous Data Warehouse, Autonomous JSON Database, or Autonomous Transaction Processing.

To view the Overview tab that shows general information about utilization, do the following:

  1. From the Autonomous Databases list, under Display Name, select an Autonomous Database.
  2. From the Autonomous Database Details page click Service Console.

    This displays the Autonomous Database Service Console Overview page.

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 Set the ADMIN Password in Autonomous Database if you need to change the password for the ADMIN user.

Console Overview

The Overview page shows real-time and historical information about the Autonomous Database utilization.

The charts shown on this page include:

  • Storage: This chart shows the total and used storage capacity (refreshed every 30 minutes). The chart indicates what percentage of the space is currently in-use. The storage in-use is the storage in the Autonomous Database as follows:
    • Used by all database objects (chart does not include storage for the sample schemas as they do not count against your storage)
    • The files users put in the file system
    • The storage used by the temporary tablespace
    Description of adb_console_overview_storage.png follows
    Description of the illustration adb_console_overview_storage.png

    For an Autonomous JSON Database the chart shows an additional field showing the percentage of storage used that is not storing JSON documents.

    Note:

    If you drop an object, the space continues to be consumed until you empty the recycle bin. See Purging Objects in the Recycle Bin for more information.

    See Use Sample Data Sets in Autonomous Database for information on sample schemas SH and SSB.

  • 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.

      Description of adb_console_overview_number_ocpus_noauto.png follows
      Description of the illustration adb_console_overview_number_ocpus_noauto.png
    • 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.

      Description of adb_console_overview_cpu_util.png follows
      Description of the illustration adb_console_overview_cpu_util.png
  • 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.

    Description of adb_console_overview_running_sql.png follows
    Description of the illustration adb_console_overview_running_sql.png
  • Number of OCPUs allocated

    Description of adb_console_overview_number_ocpus.png follows
    Description of the illustration adb_console_overview_number_ocpus.png

    Notes for display results:

    • 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.

    Description of adb_console_overview_sql_statement_response.png follows
    Description of the illustration adb_console_overview_sql_statement_response.png
  • SQL statements executed per second

    This chart does not show in the Autonomous Data Warehouse Service Console.
    Description of adb_console_overview_sql_statements.png follows
    Description of the illustration adb_console_overview_sql_statements.png

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

Note:

You can change the retention period 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 period 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 Autonomous Database instance utilization.

To access detailed information 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 Manage Concurrency and Priorities on Autonomous Database for detailed information on consumer groups.

  • Running Statements

    This chart shows the average number of running SQL statements in each consumer group.

    See Manage Concurrency and Priorities on Autonomous Database for detailed information on consumer groups.

  • Queued Statements

    This chart shows the average number of queued SQL statements in each consumer group.

    See Manage Concurrency and Priorities on Autonomous Database for detailed information on consumer groups.

To see earlier data click Time period. The default retention period for performance data is thirty (30) days. By default in the Time Period view the charts show information for the last eight (8) days.

In the time period view you can use the calendar to look at a specific time period in the past 30 days. You can also use the time slider to change the period for which performance data is shown.

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.

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.

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.