9 Managing and Monitoring Performance of Autonomous Transaction Processing

This section describes managing and monitoring the performance of Autonomous Transaction Processing.

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

    CPU Utilization with 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 three times the number of OCPUs. For example, if the database has four (4) OCPUs with auto scaling enabled the percentage in this graph is based on 12 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.

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. See Managing Priorities on Autonomous Transaction Processing for detailed information on consumer groups.

    CPU Utilization with 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 three times the number of OCPUs. For example, if the database has four (4) OCPUs with auto scaling enabled the percentage in this graph is based on 12 CPUs.

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

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

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.

Managing Priorities on Autonomous Transaction Processing

The priority of user requests in Autonomous Transaction Processing is determined by the database service the user is connected with.

Overview

Users are required to select a service when connecting to the database. The service names are in the format:

  • databasename_tpurgent

  • databasename_tp

  • databasename_high

  • databasename_medium

  • databasename_low

These services map to the TPURGENT, TP, HIGH, MEDIUM and LOW consumer groups. For example, if you provision an Autonomous Transaction Processing service with the name ATP1, your service names are:

  • atp1_tpurgent

  • atp1_tp

  • atp1_high

  • atp1_medium

  • atp1_low

For example, a user connecting with the atp1_tp service uses the consumer group TP.

The basic characteristics of these consumer groups are:

  • TPURGENT: The highest priority application connection service for time critical transaction processing operations. This connection service supports manual parallelism.

  • TP: A typical application connection service for transaction processing operations. This connection service does not run with parallelism.

  • HIGH: A high priority application connection service for reporting and batch operations. All operations run in parallel and are subject to queuing.

  • MEDIUM: A typical application connection service for reporting and batch operations. All operations run in parallel and are subject to queuing. Using this service the degree of parallelism is limited to four (4).

  • LOW: A lowest priority application connection service for reporting or batch processing operations. This connection service does not run with parallelism.

Note:

After connecting to the database using one service, do not attempt to manually switch that connection to a different service by simply changing the consumer group of the connection. When you connect using a service, Autonomous Transaction Processing performs more actions to configure the connection than just setting its consumer group.

Idle Time Limits

Autonomous Transaction Processing has predefined idle time limits for sessions so that idle sessions do not hold system resources for a long time.

A session may be terminated if it stays idle for more than five (5) minutes and the resources it consumes are needed by other users. This allows other active sessions to proceed without waiting for the idle session.

Predefined Job Classes with Oracle Scheduler

Autonomous Transaction Processing includes predefined job_class values to use with Oracle Scheduler.

The predefined job_class values, TPURGENT, TP, HIGH, MEDIUM and LOW map to the corresponding consumer groups. These job classes allow you to specify the consumer group a job runs in with DBMS_SCHEDULER.CREATE_JOB.

For example: use the following to create a single regular job to run in HIGH consumer group:

BEGIN
   DBMS_SCHEDULER.CREATE_JOB (
     job_name => 'update_sales',
     job_type => 'STORED_PROCEDURE',
     job_action => 'OPS.SALES_PKG.UPDATE_SALES_SUMMARY',
     start_date => '28-APR-19 07.00.00 PM Australia/Sydney',
     repeat_interval => 'FREQ=DAILY;INTERVAL=2',
     end_date => '20-NOV-19 07.00.00 PM Australia/Sydney',
     auto_drop => FALSE,
     job_class => 'HIGH',
     comments => 'My new job');
END;
/

Note:

To use DBMS_SCHEDULER.CREATE_JOB additional grants for specific roles or privileges might be required. The ADMIN user and users with DWROLE have the required CREATE SESSION and CREATE JOB privileges. If a user does not have DWROLE then grants are required for CREATE SESSION and CREATE JOB privileges.

See Scheduling Jobs with Oracle Scheduler for more information on Oracle Scheduler and DBMS_SCHEDULER.CREATE_JOB.

Manage CPU/IO Shares on Autonomous Transaction Processing

Autonomous Transaction Processing comes with predefined CPU/IO shares assigned to different consumer groups. You can modify these predefined CPU/IO shares if your workload requires different CPU/IO resource allocations.

By default, the CPU/IO shares assigned to the consumer groups TPURGENT, TP, HIGH, MEDIUM, and LOW are 12, 8, 4, 2, and 1, respectively. The shares determine how much CPU/IO resources a consumer group can use with respect to the other consumer groups. With the default settings the consumer group TPURGENT will be able to use 12 times more CPU/IO resources compared to LOW, when needed. The consumer group TP will be able to use 4 times more CPU/IO resources compared to MEDIUM, when needed.

You can set CPU/IO shares from the service console or using the PL/SQL package cs_resource_manager.update_plan_directive.

Follow these steps to set CPU/IO shares from the service console:

  1. From the Autonomous Transaction Processing details page, click Service Console.
  2. On the Service Console click Administration.
  3. Click Set Resource Management Rules.
  4. Select CPU/IO shares to set CPU/IO share values for consumer groups.
  5. Set the CPU/IO share values.
  6. Click Save changes.

For example, the following figure shows CPU/IO share values that you can modify:

Description of atpc_manage_set_shares.png follows
Description of the illustration atpc_manage_set_shares.png

You can also change the default values using the PL/SQL procedure cs_resource_manager.update_plan_directive:

For example, running the following script with the ADMIN user sets CPU/IO shares to 12, 4, 2, 1, and 1 for consumer groups TPURGENT, TP, HIGH, MEDIUM, and LOW respectively. This will allow the consumer group TPURGENT to use 3 times more CPU/IO resources compared to the consumer group TP and 12 times CPU/IO resources compared to the consumer group MEDIUM:

BEGIN
   cs_resource_manager.update_plan_directive(consumer_group => 'TPURGENT', shares => 12);
   cs_resource_manager.update_plan_directive(consumer_group => 'TP', shares => 4);
   cs_resource_manager.update_plan_directive(consumer_group => 'HIGH', shares => 2);
   cs_resource_manager.update_plan_directive(consumer_group => 'MEDIUM', shares => 1);
   cs_resource_manager.update_plan_directive(consumer_group => 'LOW', shares => 1);
END;
/

Manage Runaway SQL Statements on Autonomous Transaction Processing

Specifies how you configure Autonomous Transaction Processing to terminate SQL statements automatically based on their runtime or the amount of IO they are doing.

You can set rules from the service console or using the PL/SQL package cs_resource_manager.

Follow these steps to set rules from the service console:

  1. From the Autonomous Transaction Processing details page, click Service Console.
  2. On the Service Console click the Administration.
  3. Click Set Resource Management Rules.
  4. Select the Run-away criteria tab to set rules for consumer groups.
  5. Select the Consumer group: HIGH, MEDIUM, LOW, TP, or TPURGENT.
  6. Set runaway criteria values:
    • Query run time (seconds)

    • Amount of IO (MB)

  7. Click Save changes.

For example, the following shows values for setting the runtime limit to 120 seconds and the IO limit to 1000MB for the HIGH consumer group:

Description of atpc_manage_set_resource.png follows
Description of the illustration atpc_manage_set_resource.png

When a SQL statement in the specified consumer runs more than the specified runtime limit or does more IO than the specified amount, then the SQL statement will be terminated.

To reset the values and lift the limits, you can use the same screen, delete the values, and save the changes.

You can also use the procedure cs_resource_manager.update_plan_directive to set these rules. For example, to set a runtime limit of 120 seconds and an IO limit of 1000MB for the HIGH consumer group run the following command when connected to the database as the ADMIN user:

BEGIN
     cs_resource_manager.update_plan_directive(consumer_group => 'HIGH', io_megabytes_limit => 1000, elapsed_time_limit => 120);
END;
/	

To reset the values and lift the limits, you can set the values to null:

BEGIN
     cs_resource_manager.update_plan_directive(consumer_group => 'HIGH', io_megabytes_limit => null, elapsed_time_limit => null);
END;
/	

Manage Optimizer Statistics on Autonomous Transaction Processing

Describes Autonomous Transaction Processing commands to run when you need to gather optimizer statistics or enable optimizer hints.

Managing Optimizer Statistics

Autonomous Transaction Processing gathers optimizer statistics automatically so that you do not need to perform this task manually and this helps to ensure your statistics are current. Automatic statistics gathering is enabled in Autonomous Transaction Processing and runs in a standard maintenance window.

Note:

For more information on maintenance window times and automatic optimizer statistics collection, see Database Administrator’s Guide.

For more information on optimizer statistics see SQL Tuning Guide.

Managing Optimizer Hints

Autonomous Transaction Processing honors optimizer hints and PARALLEL hints in SQL statements by default. You can disable optimizer hints by setting the parameter OPTIMIZER_IGNORE_HINTS to TRUE at the session or system level using ALTER SESSION or ALTER SYSTEM. For example, the following command disables hints in your session:

ALTER SESSION
   SET OPTIMIZER_IGNORE_HINTS=TRUE;

You can also disable PARALLEL hints in your SQL statements by setting OPTIMIZER_IGNORE_PARALLEL_HINTS to TRUE at the session or system level using ALTER SESSION or ALTER SYSTEM.

ALTER SESSION 
   SET OPTIMIZER_IGNORE_PARALLEL_HINTS=TRUE;

Monitor the Performance of Autonomous Transaction Processing with Oracle Management Cloud

Oracle Management Cloud allows you to monitor your Autonomous Transaction Processing database availability and performance. You can use Oracle Database Management, part of Oracle Management Cloud, to monitor Autonomous Databases and On-premises Oracle Databases.

For information on using Oracle Management Cloud with Autonomous Transaction Processing see the following: