Oracle Cloud Infrastructure Documentation

Manage Runaway SQL Statements on Autonomous Database

Specifies how you configure Autonomous Database to terminate SQL statements automatically based on their query runtime or their IO usage.

You can set runtime run-away rules for query run time and IO usage in Database Actions or using the PL/SQL package CS_RESOURCE_MANAGER.

Follow these steps to use Database Actions to set runtime usage rules:

  1. Access Database Actions as the ADMIN user.

    See Access Database Actions as ADMIN for more information.

  2. On the Database Actions Launchpad, under Administration, click Set Resource Management Rules.
  3. Select the Run-away criteria tab to set usage rules for a consumer group.
  4. Select the Consumer group.
  5. Set runaway criteria values:

    • Query run time (seconds)

    • Amount of IO (MB)

  6. Click Save Changes.
  7. Click OK.

When a SQL statement in the specified consumer group 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: 

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

See CS_RESOURCE_MANAGER Package for more information.