Manage Workload Resources with Database Resource Manager in Autonomous Database

To customize resource allocation for different users, apps, or workload types in Autonomous Database, you can create and manage your own Database Resource Manager (DBRM) plans and consumer groups using cs_resource_manager subprograms.

Autonomous Database uses a default resource management plan that controls the resources assigned to each service. However, if you want a customized resource manager plan to control the resources for different users, apps, and workloads in your database, you can use cs_resource_manager subprograms to define and manage your own Database Resource Manager (DBRM) plans, define custom consumer groups, and tailor resource usage policies in Autonomous Database to control workload prioritization and system resource allocation.

Topics:

• About cs_resource_manager Package
  
• Use Case
  
• Prerequisites
  
• Step 1: Create Consumer Groups
  
• Step 2: Create Plan
  
• Step 3: Create Plan Directives
  
• Step 4: Create Consumer Group Mappings
  
• Step 5: Enable Plan
  
• Optional Steps
  
• SQL Parallelism Behavior with Custom Plans
  

About cs_resource_manager Package

The cs_resource_manager subprograms allow you to associate specific resource usage policies and directives with each consumer group and implement, monitor, and revise your policies as your requirements change.

With the cs_resource_manager subprograms, you can:

• Create your own Database Resource Manager (DBRM) plan.
• Create and delete custom consumer groups.
• Set consumer group mappings, that is, rules and priorities for how sessions are assigned to these consumer groups.
• Set consumer group mapping priorities.
• Define plan directives to assign the following resource controls for each custom consumer group:
  • Share of resource allocation for the consumer group. Shares determine how much CPU and IO resource a consumer group gets relative to other consumer groups. For example, a consumer group with a share of 2 will get twice the CPU and IO resources than a consumer group with a share of 1. The default value is 1.
  • Resource limits that determine the maximum CPU and I/O resources a consumer group can get.
  • Time on CPU (in seconds) that a session can run before the action determined by the switch_action parameter is taken. Default is NULL, which means unlimited.
  • Amount of I/O (in MB) that a session can issue before the action determined by the switch_action parameter is taken. Default is NULL, which means unlimited.
  • Number of I/O requests that a session can issue before the action determined by the switch_action parameter is taken. Default is NULL, which means unlimited.
  • Number of logical I/Os that will trigger the action specified by switch_action.
  • Elapsed time (in seconds) that will trigger the action specified by switch_action.
  • Number of seconds that a session can be idle before the session is terminated. Default is NULL, which means unlimited.
  • Maximum amount of time in seconds that a session can be idle before the session is terminated, if the session is holding a lock or resource needed by other sessions.
  • Maximum number of sessions that can concurrently have an active call.
  • Time (in seconds) after which a call in the inactive session queue (waiting for execution) will time out. Default is NULL, which means unlimited.
  • Limit on the Degree of Parallelism (DOP) for any operation. Default is NULL, which means unlimited. Use value of 1 for an operation to be serial
  • Concurrency level for parallel statements. Because the concurrency level indirectly determines DOP, setting both values will create a conflict. It is recommended that you set only the concurrency or the degree of parallelism for a consumer group, not both.
  • Maximum amount of untunable PGA (in MB) that a session in this consumer group can allocate before being terminated. NULL (default) indicates no limit. SQL operations that allocate tunable PGA (operations that can opt to use temp space) are not controlled by this limit.
  • Time (in seconds) that a parallel statement may remain in its consumer group's parallel statement queue. You must pair it with the action to be taken when a parallel statement is removed from the queue due to timeout. The options are either to cancel or run the statement.
  • Action to be taken upon reaching the any of the limits specified in the directives. Valid values are cancel_sql, kill_session, or a consumer group name to switch into.
• Activate your custom DBRM plan and revert to the default, if needed.

The following topics outline the detailed workflow for defining custom consumer groups, plans, and session mappings in an example practical use case. This workflow and the associated code examples can be modified and implemented according to your requirements.

Use Case

Consider an organization that decided to use an Autonomous Database for a mixed workload environment that has both OLTP and Lakehouse applications. Based on the applications' resource requirements and workload characteristics, the database administrator decided it would be optimal to define and use custom DBRM plans rather than the predefined consumer groups and plans that come with Autonomous Database.

They finalized on the following requirements:

1. Create three (3) consumer groups:
   • OLTP_HIGH to handle high priority transaction processing sessions.
   • OLTP_LOW to handle background or low priority transaction processing sessions.
   • LH_BATCH for batch or reporting workloads.
2. Create a plan called OLTP_LH_PLAN to split resources between transaction processing and lakehouse workloads.
3. Create four (4) plan directives for the following scenarios:
   • High-priority transaction processing sessions get 8 CPU and I/O shares and no parallelism.
   • Lower-priority transaction processing sessions get 4 CPU and I/O shares and no parallelism.
   • Lakehouse and batch transactions get 4 CPU and I/O shares and the degree of parallelism is capped to 4.
   • All the other sessions that are not mapped to a consumer group get 1 CPU/IO share and no parallelism.
4. Create the following user to consumer group mappings:
   • APP_USER to OLTP_HIGH
   • LH_USER to LH_BATCH
5. Enable the plan.

Prerequisites

To use the cs_resource_manager subprograms, connect to the Autonomous Database as the ADMIN user. As an ADMIN user, you can also grant privileges on this package to other users, as needed.

Step 1: Create Consumer Groups

You can create custom consumer groups using the cs_resource_manager.create_consumer_group procedure.

A connection (session) can be placed in the new consumer group either by specifying the consumer group in the connect string or by configuring consumer group mapping rules using the set_consumer_group_mapping and set_consumer_group_mapping_pri subprograms.

• Start a pending area to run all the Resource Manager DDL statements.

  BEGIN
    CS_RESOURCE_MANAGER.CREATE_PENDING_AREA;
  END;
  /

• Create three (3) consumer groups:
  • OLTP_HIGH to handle high priority transaction processing sessions.
  • OLTP_LOW to handle background or low priority transaction processing sessions.
  • LH_BATCH for batch or reporting workloads.

  BEGIN
    CS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(
      consumer_group => 'OLTP_HIGH',
      comment        => 'Priority OLTP sessions');
  
    CS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(
      consumer_group => 'OLTP_LOW',
      comment        => 'Background/low-priority OLTP');
  
    CS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(
      consumer_group => 'LH_BATCH',
      comment        => 'Batch / reporting workloads');  
  END;
  /

  Note:

  OTHER_GROUPS is an implicit group that is available by default for any unmapped sessions.

See CREATE_PENDING_AREA Procedure and CREATE_CONSUMER_GROUP Procedure for syntax reference.

Step 2: Create Plan

Create a plan called OLTP_LH_PLAN to split resources between transaction processing and lakehouse workload types.

BEGIN
  CS_RESOURCE_MANAGER.CREATE_PLAN(
    plan    => 'OLTP_LH_PLAN',
    comment => 'Split resources between OLTP and Lakehouse workload types');
END;
/

See CREATE_PLAN Procedure for syntax reference.

Step 3: Create Plan Directives

You can assign and adjust plan directives, such as CPU share, I/O limits, concurrency, and parallelism, for your custom consumer groups. See About cs_resource_manager Package for the complete list of directives.

Create four (4) plan directives for the following scenarios:

• High-priority transaction processing sessions get 8 CPU and I/O shares and no parallelism.
• Lower-priority transaction processing sessions get 4 CPU and I/O shares and no parallelism.
• Lakehouse and batch transactions:
  • Get 4 CPU and I/O shares.
  • Have the degree of parallelism capped to 4.
  • Have the parallel queue timeout set to 60 seconds and the SQL statement will be terminated after the timeout.
• All the other sessions that are not mapped to a consumer group get 1 CPU/IO share and no parallelism.

BEGIN

  -- High-priority OLTP gets 8 CPU/IO shares and no parallelism
  CS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
    plan                  => 'OLTP_LH_PLAN',
    consumer_group        => 'OLTP_HIGH',
    comment               => 'OLTP high priority',
    shares                => 8,
    parallel_degree_limit => 1
  );

  -- Lower-priority OLTP gets 4 CPU/IO shares and no parallelism
  CS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
    plan                  => 'OLTP_LH_PLAN',
    consumer_group        => 'OLTP_LOW',
    comment               => 'OLTP low priority',
    shares                => 2,
    parallel_degree_limit => 1
  );

  -- Lakehouse / batch gets 4 shares and the degree of parallelism is capped to 4.
  -- If a parallel SQL statement waits in the queue for more than 60 seconds, it will be canceled.
  CS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
    plan                          => 'OLTP_LH_PLAN',
    consumer_group                => 'LH_BATCH',
    comment                       => 'Lakehouse/reporting workloads',
    shares                        => 4,
    parallel_degree_limit         => 4,     -- cap DOP within this group (adjust as needed)
    parallel_queue_timeout        => 60,
    parallel_queue_timeout_action => 'CANCEL'
  );

  -- Catch-all for anything unmapped; sessions that are not mapped to a consumer group get 1 CPU/IO share and no parallelism
  CS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
    plan                  => 'OLTP_LH_PLAN',
    consumer_group        => 'OTHER_GROUPS',
    comment               => 'Catch-all for unmapped sessions',
    shares                => 1,
    parallel_degree_limit => 1
  );
END;
/

See CREATE_PLAN_DIRECTIVE Procedure for syntax reference.

Step 4: Create Consumer Group Mappings

You can map sessions to your custom consumer groups, based on the session's login and runtime attributes. Using cs_resource_manager.set_consumer_group_mapping and cs_resource_manager.set_consumer_group_mapping_pri subprograms, you can define rules and priorities for how sessions are assigned to these consumer groups and define consumer group mapping priorities. You can have multiple mappings with different attributes. In this example, APP_USER is mapped to OLTP_HIGH and LH_USER is mapped LH_BATCH. These mappings are evaluated in precedence order set by SET_CONSUMER_GROUP_MAPPING_PRI.

You can determine how sessions are placed in consumer groups through:

• Connect String Assignment: Specify the CONSUMER_GROUP in your database connection string as shown below. This approach takes precedence over the mapping and will override any mappings defined.

  (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.us-ashburn-1.oraclecloud.com))(connect_data=(service_name=my_database_low.adb.oraclecloud.com)(CONSUMER_GROUP=OLTP_LOW))(security=(ssl_server_dn_match=yes)))

• Mapping Rules: Use set_consumer_group_mapping and set_consumer_group_mapping_pri subprograms to assign sessions or applications to consumer groups based on attributes such as username or application name.

Note:

set_consumer_group_mapping and set_consumer_group_mapping_pri cannot be used on predefined consumer groups that come with Autonomous Database, that is, TPURGENT, TP, HIGH, MEDIUM, and LOW.

Sessions can be mapped to consumer groups by any of the attributes listed in DBMS_RESOURCE_MANAGER Constants. These mappings are evaluated in precedence order set by SET_CONSUMER_GROUP_MAPPING_PRI. In this example, let's map the consumer groups by the following users:

• APP_USER to OLTP_HIGH
• LH_USER to LH_BATCH

BEGIN
  -- Map schema APP_USER to OLTP_HIGH
  CS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(
    attribute  => 'ORACLE_USER',
    value      => 'APP_USER',
    consumer_group => 'OLTP_HIGH');

  CS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(
    attribute  => 'ORACLE_USER',
    value      => 'LH_USER',
    consumer_group => 'LH_BATCH');
END;
/

Use validate_pending_area to review your changes, and submit_pending_area to enable the plan directives.

BEGIN
  CS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA;
  CS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA;
END;
/

See SET_CONSUMER_GROUP_MAPPING Procedure, SET_CONSUMER_GROUP_MAPPING_PRI Procedure, VALIDATE_PENDING_AREA Procedure, and SUBMIT_PENDING_AREA Procedure for syntax reference.

Step 5: Enable Plan

Finally, you can enable the customized DBRM plan by running an ALTER statement as shown below.

ALTER SYSTEM SET resource_manager_plan='OLTP_LH_PLAN';

Optional Steps

When no longer needed you can disable the plan and delete the plan directives, plan, and consumer groups using the following statements:

• To revert to the default plan:

  ALTER SYSTEM SET resource_manager_plan= DWCS_PLAN; -- To revert to the default plan for the Autonomous AI Lakehouse workload type.
  ALTER SYSTEM SET resource_manager_plan= OLTP_PLAN; -- To revert to the default plan for other Autonomous AI Database workload types.

• To delete a plan directive:

  CS_RESOURCE_MANAGER.DELETE_PLAN_DIRECTIVE (
     plan            => <plan_name>, 
     consumer_group  => <consumer_group_name>);
  

• To delete a plan:

  CS_RESOURCE_MANAGER.DELETE_PLAN ( 
    plan    ==> <plan_name>);

• To delete a consumer group:

  CS_RESOURCE_MANAGER.DELETE_CONSUMER_GROUP (
     consumer_group ==> <consumer_group_name>);

Note:

You cannot delete predefined consumer groups that come with Autonomous Database, that is, TPURGENT, TP, HIGH, MEDIUM, and LOW.

See DELETE_CONSUMER_GROUP Procedure, DELETE_PLAN Procedure, and DELETE_PLAN_DIRECTIVE Procedure Procedure for syntax reference.

SQL Parallelism Behavior with Custom Plans

If you use a custom DBRM plan and connect to your database using the LOW, TP, or TPURGENT services, those sessions will get manual parallelism and you can limit the degree of parallelism for those sessions using the directives in your plan. If you connect using the HIGH and MEDIUM services, those sessions will get parallelism automatically and you can limit the degree of parallelism for those sessions using the directives in your plan.