8 Managing and Monitoring Performance of Autonomous Data Warehouse

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

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

Monitoring 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 page click navigation icon in the top corner of the page and then click Autonomous Data Warehouse from the left navigation pane, and select a Compartment.

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.

Console Overview

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


Description of dwcs_console_overview2.png follows
Description of the illustration dwcs_console_overview2.png

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.

  • CPU utilization (%)

    This chart shows the historical CPU utilization of the service. 

  • Running SQL statements

    This chart shows the average number of running SQL statements historically.

  • Average SQL statement response time (s)

    This chart shows the average response time of SQL statements historically.

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:

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


Description of dwcs_monitor_rt.png follows
Description of the illustration dwcs_monitor_rt.png

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.


Description of dwcs_monitor_tp.png follows
Description of the illustration dwcs_monitor_tp.png

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


Description of dwcs_monitor_cal.png follows
Description of the illustration dwcs_monitor_cal.png

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


Description of dwcs_monitor_tp_slider.png follows
Description of the illustration dwcs_monitor_tp_slider.png

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


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

    Description of dwcs_monitor_sql_overview.png follows
    Description of the illustration dwcs_monitor_sql_overview.png

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


    Description of dwcs_monitor_sql_plans.png follows
    Description of the illustration dwcs_monitor_sql_plans.png

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


    Description of dwcs_monitor_sql_para.png follows
    Description of the illustration dwcs_monitor_sql_para.png

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:

  • tenant_name_database_name_low

  • tenant_name_database_name_medium

  • tenant_name_database_name_high

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

  • orcl_dw_low

  • orcl_dw_medium

  • orcl_dw_high

For example, a user connecting with the orcl_dw_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 pre-defined 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 32 concurrent SQL statements independent of the other groups. 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.

Managing Run–Away 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 the rules using the service console or using the PL/SQL package cs_resource_manager.

Follow these steps to set rules using the service console:

  1. On the Service Console click the Administration tab.
  2. Click Set Resource Management Rules to open the page to set run–away query rules for consumer groups.
  3. Select the Consumer Group: High, Medium, or Low.
  4. Set run–away criteria values:
    • Query run time (seconds)

    • Amount of IO (MB)

  5. 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;
/	

Managing Optimizer Statistics and Hints 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;