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 Database details page, click Service Console.
  2. On the Service Console click 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.

Click Load Default Values to load the default values; then click Save changes to apply the populated values.

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:

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

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

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