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.
- From the Autonomous Data Warehouse landing pad select an instance.
- From the Details page click Service Console.
- 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
ADMINusername, the password, and click Sign in. See Change the Administrator Password in Autonomous Data Warehouse if you need to change the password for the
The Overview page shows real-time and historical information about the utilization of the service.
Description of the illustration dwcs_console_overview2.png
The components on this page are:
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.
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.
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:
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.
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.
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.
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.
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 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 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 the illustration dwcs_monitor_tp_slider.png
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.
Description of the illustration dwcs_monitor_sql.png
- 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 the illustration dwcs_monitor_sql_overview.png
Click Plan Statistics tab to see the runtime execution plan of the statement.
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 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.
Users are required to select a service when connecting to the database. The service names are in the format:
These services map to the
HIGH consumer groups. For example, if you provision
an Autonomous Data Warehouse service with the name ADW1, your
service names are:
For example, a user connecting with the
adw1_low service uses the consumer group
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.
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.
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
Follow these steps to set rules from the service console:
- On the Service Console click the Administration tab.
- Click Set Resource Management Rules to open the page to set runaway query rules for consumer groups.
- Select the Consumer group: HIGH, MEDIUM, or LOW.
- Set runaway criteria values:
Query run time (seconds)
Amount of IO (MB)
- 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 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,
SH schema you can gather statistics for all tables in the schema using the
BEGIN DBMS_STATS.GATHER_SCHEMA_STATS('SH', options=>'GATHER AUTO'); END; /
This example gathers statistics for all tables that have stale statistics in the
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
FALSE at the session or system level using
ALTER SESSION or
ALTER SYSTEM. For example, the following command enables hints in your
ALTER SESSION SET OPTIMIZER_IGNORE_HINTS=FALSE;
You can also enable
PARALLEL hints in your SQL statements by setting
FALSE at the session or system
ALTER SESSION or
ALTER SYSTEM. For example, the
following command enables
PARALLEL hints in your
ALTER SESSION SET OPTIMIZER_IGNORE_PARALLEL_HINTS=FALSE;