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:
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.
Parent topic: Monitor and Manage Performance