In Oracle Database, you can use Database Resource Manager (DRM) to manage memory, CPU, time and other resources.
Usually your workflow is as follows:
- Create a resource plan
- Create the resource consumer groups
- Create the resource consumer group mappings
- Create the resource-plan directives
You create a plan with the
DBMS_RESOURCE_MANAGER.CREATE_PLAN procedure. Plan name is the only required parameter:
Then you create the resource consumer groups as needed with
consumer_group => 'limit_5_min'
, comment => 'users with 5 minute execution time limit');
After the necessary resource consumer groups are created, you can define resource consumer group mappings with
DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING based on the session attribute so that the new and existing sessions are automatically assigned to appropriate resource consumer groups:
attribute => DBMS_RESOURCE_MANAGER.CLIENT_OS_USER
, value => 'spongebob'
, consumer_group => 'limit_5_min');
This mapping will assign all the session made from the OS user
spongebob to the resource consumer group created earlier. There are a number of other attributes you can use to map the session to consumer groups, for example,
DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING_PRI procedure parameters table for a full list of available attributes.
Now is the most important part. You will create the resource-plan directives that will govern the resource usage by the users. You use the
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE procedure to create the directives. Let's create the directive which limits the session execution time:
plan => 'myplan'
, group_or_subplan => 'limit_5_min'
, switch_time => 300
, switch_group => 'KILL_SESSION');
This directive means that every session created from the OS user
spongebob and executes more than 300 seconds (5 minutes) will be automatically killed by DRM.
You will also need to grant the users privileges to switch to the appropriate resource consumer groups with
DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP procedure, otherwise the mapping will not work:
grantee_name => 'scott'
, consumer_group => 'limit_5_min'
, grant_option => false);