Monitor the Performance of Autonomous Database

The Overview and Activity tabs on the Monitor Database card in Database Actions provide information about the performance of an Autonomous Database instance. The Activity tab shows past and current monitored SQL statements and detailed information about each statement.

Use Database Actions to Monitor Activity and Utilization

Database Actions provides the Database Monitor card, with Overview and Monitor tabs to provide real-time and historical information about the utilization of an Autonomous Database instance.

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. Access Database Actions as the ADMIN user.

    See Access Database Actions as ADMIN for more information.

  2. On the Database Actions Launchpad, under Monitoring, click Database Monitor.

Note:

You can bookmark the Launchpad URL and go to that URL directly without logging in to the Oracle Cloud Infrastructure Console. If you logout and use the bookmark, then you need to enter the ADMIN username and password, and click Sign in. See Set the ADMIN Password in Autonomous Database if you need to change the password for the ADMIN user.

Database Monitor Overview

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

The charts shown on this page include:

  • Storage: This chart shows the provisioned, allocated, and used storage. The chart indicates what percentage of the space is currently in-use.

    Description of adb_console_overview_storage.png follows
    Description of the illustration adb_console_overview_storage.png

    Provisioned storage is the amount of storage you select when you provision the instance or when you modify storage by scaling storage.

    Storage allocated is the amount of storage physically allocated to all data tablespaces and temporary tablespaces and includes the free space in these tablespaces. This does not include storage for the sample schemas.

    Storage used is the amount of storage actually used in all data and temporary tablespaces. This does not include storage for the sample schemas. The storage used is the storage in the Autonomous Database as follows:

    • Storage used by all database objects. Note: the chart does not include storage for the sample schemas as they do not count against your storage.
    • Storage for files users put in the file system.
    • Storage used by temporary tablespaces.
    • Used storage excludes the free space in the data and temporary tablespaces.

    By default the chart does not show the used storage. Select Storage used to expand the chart to see used storage (the values are calculated when you open the chart).

    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:

    • OCPU 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
    • OCPU auto scaling enabled: For databases with OCPU 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:

    • OCPU auto scaling disabled: For databases with OCPU 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.
    • OCPU auto scaling enabled: For databases with OCPU 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.

    Click Show details for more information, including the number of OCPUs allocated to the database and to external resources, and the total allocated OCPUs.

    The Show details view includes separate values for database OCPU usage and external resource OCPU usage. External resources include: Cloud SQL, Graph, OML4PY, and others. The Total OCPUs are the total number of OCPUs in use on the Autonomous Database. The external OCPUs value shows how external OCPUs contribute to the total OCPU usage.

  • 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

    Note:

    Database Monitor does not show this chart when the Autonomous Database instance workload type is Data Warehouse.

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

Database Monitor Activity

The Monitor tab shows real-time and historical information about the Autonomous Database performance data, activity, and utilization.

Note:

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

The charts 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 OCPU 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.

Use Database Actions to Monitor Active Session History Analytics and SQL Statements

In Database Actions the Performance Hub card provides information about Active Session History (ASH) analytics and current and past monitored SQL statements.

Perform the following prerequisite step as necessary:

The Performance Hub Page

The Performance Hub page shows performance data for a time period you specify.

To navigate to the Performance Hub page, do either of the following:

  • In the Launchpad page, click Performance Hub.

  • Click Selectorselector iconto display the navigation menu. Under Monitoring, select Performance Hub.

Note:

The Performance Hub page is available in the following user interface languages: French, Japanese, Korean, Traditional Chinese, and Simplified Chinese. If you change the language to German, Spanish, Italian, or Portuguese in Preferences, the Performance Hub page reverts to English.

The Performance Hub page consists of these parts:

  • Time Range Area: Use the controls in time range area at the top of the page to specify the time period for which you want to view performance data.

  • ASH Analytics Tab: Use this tab to explore ASH (Active Session History) information across a variety of different dimensions for the specified time period.

  • SQL Monitoring Tab: Use this tab to view the top 100 SQL statement executions by different dimensions for the specified time period, and to view details of SQL statement executions you select.

Time Range Area

Use these controls in the time range area to specify the time period for which you want to view performance data:

  • Select Duration: Use this drop-down list, located in the top right of the time range area, to set the timeframe displayed in the timeline. You can choose Last hour, Last 8 hours, Last 24 hours, Last Week, or you can choose Custom and define your own timeframe.

  • Timeline: The timeline displays a graph spanning the timeframe selected in the timeframe dropdown, showing Waits, User I/O and CPU usage during the period. At its end is the time slider.

  • Time Slider: The time slider is a box you can drag back and forth along the current timeline. Use it to pick the specific time period within the timeframe for which you want to view performance data. You can also drag the side handles on the time slider to make it wider or narrower to encompass a longer or shorter time period.

ASH Analytics Tab

The ASH Analytics tab consists of the Average Active Sessions chart and two secondary tables below it.

  • Average Active Sessions Chart: This chart shows performance information for the time period defined by the time slider. You can choose to chart different dimensions of information, such as Wait Class, Wait Event, or Service, by selecting the dimension from the drop-down list to the right of the chart title.

    You can download an AWR (Automatic Workload Repository) report for the current time period by right-clicking in the Average Active Sessions chart area and choosing Generate AWR Report.

    For more information about ASH and AWR, see Active Session History (ASH) and Automatic Workload Repository (AWR) in Oracle Database Concepts.

  • Secondary Tables: The two tables below the Average Active Sessions chart show the information dimension chosen in the chart filtered by another dimension you choose. For example, if the Average Active Sessions chart is showing Wait Class, you could show SQL ID and User Session dimension information by Wait Class, one in each of the two secondary tables.

    In the secondary tables, the SQL ID and User Session dimensions provide links to SQL Details and Session Details pages, respectively, for the dimension data listed in the table.

SQL Monitoring Tab

The SQL Monitoring tab shows a table of the top 100 monitored SQL statements that were executing or that completed during the selected time period.

The table displays information about monitored SQL statement executions. If there is a green spinning icon in the Status column, then the monitored statement did not complete during the selected time period. A red cross indicates that the SQL did not complete either due to an error or due to the session getting terminated. If there is a check mark in the Status column, then the statement completed its execution during the selected time period.

SQL statements are monitored only if they have consumed at least 5 seconds of CPU or I/O time.

You can view information such as the status of a statement, its duration, its type (SQL, PL/SQL, or DBOP), its SQL ID, its SQL plan hash, the user who issued it, whether it executed as a serial or parallel statement, the time the database spent performing CPU activity, I/O, or other activity for the statement, the read and write requests and bytes associated with the statement, and the start and end time for the statement.

Click a SQL ID to display the SQL Details page with more information about that SQL statement.