9 Managing and Monitoring Performance of Autonomous Data Warehouse

This section describes managing and monitoring the performance of Autonomous Data Warehouse.

Monitor the Performance of Autonomous Data Warehouse

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

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 Data Warehouse landing pad select an instance.
  2. From the Details page click Service Console.
  3. Autonomous Data Warehouse 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 Data Warehouse 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. 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.

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

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 Concurrency and Priorities on Autonomous Data Warehouse 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 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 Statements

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

  • Queued Statements

    This chart shows the average number of queued SQL statements in each consumer group. See Managing Concurrency and Priorities on Autonomous Data Warehouse 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 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 Database SQL Tuning Guide.

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

Managing Concurrency and Priorities on Autonomous Data Warehouse

Concurrency and prioritization of user requests in Autonomous Data Warehouse 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_high

  • databasename_medium

  • databasename_low

These services map to the LOW, MEDIUM, and HIGH consumer groups. For example, if you provision an Autonomous Data Warehouse service with the name ADW1, your service names are:

  • adw1_high

  • adw1_medium

  • adw1_low

For example, a user connecting with the adw1_low service uses the consumer group LOW.

The basic characteristics of these consumer groups are:

  • HIGH: Highest resources, lowest concurrency. Queries run in parallel.

  • MEDIUM: Less resources, higher concurrency. Queries run in parallel.

  • LOW: Least resources, highest concurrency. Queries run serially.

Idle Time Limits

Autonomous Data Warehouse 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.

Concurrency

The concurrency level of these consumer groups changes based on the number of OCPUs you subscribe to. The HIGH consumer group’s concurrency is fixed and does not change based on the number of OCPUs. The MEDIUM and LOW consumer groups can run more concurrent SQL statements if you scale up the compute capacity of your service.

Note:

The HIGH consumer group is configured for low concurrency, even a single query in this consumer group can use all resources in your database. If your workload has concurrent queries Oracle recommends using the MEDIUM consumer group. If your concurrency requirements are not met with the MEDIUM consumer group, you can use the LOW consumer group or you can scale up your compute capacity and continue using the MEDIUM consumer group.

For example, for an Autonomous Data Warehouse with 16 OCPUs, the HIGH consumer group will be able to run 3 concurrent SQL statements when the MEDIUM consumer group is not running any statements. The MEDIUM consumer group will be able to run 20 concurrent SQL statements when the HIGH consumer group is not running any statements. The LOW consumer group will be able to run 1600 concurrent SQL statements. The HIGH consumer group can run at least 1 SQL statement when the MEDIUM consumer group is also running statements. When these concurrency levels are reached for a consumer group new SQL statements in that consumer group will be queued until one or more running statements finish.

Predefined Job Classes with Oracle Scheduler

Autonomous Data Warehouse includes predefined job_class values to use with Oracle Scheduler.

The predefined job_class values, LOW, MEDIUM, and HIGH 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.

The DBMS_SCHEDULER.CREATE_JOB procedure supports PLSQL_BLOCK and STORED_PROCEDURE job types for the job_type parameter in Autonomous Data Warehouse.

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 Data Warehouse

Autonomous Data Warehouse 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 HIGH, MEDIUM, LOW are 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 HIGH will be able to use 4 times more CPU/IO resources compared to LOW and 2 times more CPU/IO resources compared to MEDIUM, when needed. The consumer group MEDIUM will be able to use 2 times more CPU/IO resources compared to LOW, 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 Data Warehouse 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 dwcs_manage_set_shares.png follows
Description of the illustration dwcs_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 8, 2, and 1 for consumer groups HIGH, MEDIUM, and LOW respectively. This will allow the consumer group HIGH to use 4 times more CPU/IO resources compared to the consumer group MEDIUM and 8 times CPU/IO resources compared to the consumer group LOW:

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

Manage Runaway SQL Statements on Autonomous Data Warehouse

Specifies how you configure Autonomous Data Warehouse 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 Data Warehouse 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, or LOW.
  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 dwcs_manage_set_resource.png follows
Description of the illustration dwcs_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 Data Warehouse

Describes Autonomous Data Warehouse commands to run when you need to gather optimizer statistics or enable optimizer hints.

Managing Optimizer Statistics

Autonomous Data Warehouse gathers optimizer statistics automatically for tables loaded with direct-path load operations. For example, for loads using the DBMS_CLOUD package the database gathers optimizer statistics automatically.

If you have tables modified using conventional DML operations you can run commands to gather optimizer statistics for those tables. For example, for the SH schema you can gather statistics for all tables in the schema using the following command:

BEGIN
  DBMS_STATS.GATHER_SCHEMA_STATS('SH', options=>'GATHER AUTO');
END;
/

This example gathers statistics for all tables that have stale statistics in the SH schema.

Note:

For more information about direct-path loads see Loading Tables.

For more information on optimizer statistics see Database Concepts.

Managing Optimizer Hints

Autonomous Data Warehouse ignores optimizer hints and PARALLEL hints in SQL statements by default. If your application relies on hints you can enable optimizer hints by setting the parameter OPTIMIZER_IGNORE_HINTS to FALSE at the session or system level using ALTER SESSION or ALTER SYSTEM. For example, the following command enables hints in your session:

ALTER SESSION
   SET OPTIMIZER_IGNORE_HINTS=FALSE;

You can also enable PARALLEL hints in your SQL statements by setting OPTIMIZER_IGNORE_PARALLEL_HINTS to FALSE at the session or system level using ALTER SESSION or ALTER SYSTEM. For example, the following command enables PARALLEL hints in your session:

ALTER SESSION 
   SET OPTIMIZER_IGNORE_PARALLEL_HINTS=FALSE;

Monitor the Performance of Autonomous Data Warehouse with Oracle Management Cloud

Oracle Management Cloud allows you to monitor your Autonomous Data Warehouse 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 Data Warehouse see the following: