CS_RESOURCE_MANAGER Package

The CS_RESOURCE_MANAGER package provides an interface to create consumer groups, plans, and update consumer group parameters and plan directives.

Summary of CS_RESOURCE_MANAGER Sub Programs

Sub Program	Description
CLEAR_PENDING_AREA Procedure	This procedure clears pending changes for the resource manager.
CREATE_CONSUMER_GROUP Procedure	This procedure defines resource consumer groups.
CREATE_PENDING_AREA Procedure	This procedure creates a pending area to make changes to resource manager objects.
CREATE_PLAN Procedure	This procedure creates entries which define resource plans.
CREATE_PLAN_DIRECTIVE Procedure	This procedure creates resource plan directives.
DELETE_CONSUMER_GROUP Procedure	This procedure deletes resource consumer groups.
DELETE_PLAN Procedure	This procedure deletes the specified plan as well as all the plan directives to which it refers.
DELETE_PLAN_DIRECTIVE Procedure	This procedure deletes resource plan directives.
LIST_CURRENT_RULES Function	This function returns the current settings for the default plan.
LIST_DEFAULT_RULES Function	This function returns the default values for all consumer groups.
REVERT_TO_DEFAULT_VALUES Procedure	This procedure reverts the default resource manager plan properties to default values.
SET_CONSUMER_GROUP_MAPPING Procedure	This procedure adds, deletes, or modifies entries that map sessions to consumer groups, based on the session's login and runtime attributes.
SET_CONSUMER_GROUP_MAPPING_PRI Procedure	Multiple attributes of a session can be used to map the session to a consumer group. This procedure prioritizes the attribute mappings.
SUBMIT_PENDING_AREA Procedure	This procedure submits pending changes for the resource manager. It clears the pending area after validating and committing the changes (if valid).
UPDATE_PLAN_DIRECTIVE Procedure	Use this procedure to update default predefined plans that come with Autonomous AI Database, that is, `DWCS_PLAN` and `OLTP_PLAN` for a specified consumer group.
UPDATE_PLAN_DIRECTIVE_ADV Procedure	Use this procedure to update user-defined resource manager plan for a specified consumer group.

CLEAR_PENDING_AREA Procedure

This procedure clears pending changes for the resource manager.

Syntax

CS_RESOURCE_MANAGER.CLEAR_PENDING_AREA;

CREATE_CONSUMER_GROUP Procedure

This procedure defines resource consumer groups.

Syntax

CS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(
   consumer_group  IN VARCHAR2,
   comment         IN VARCHAR2 DEFAULT NULL);

Parameters

Parameter	Description
`consumer_group`	Name of the consumer group
`comment`	User comment

Usage Notes

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.

Examples

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

CREATE_PENDING_AREA Procedure

This procedure creates a pending area to make changes to resource manager objects.

The pending area can be thought of as a scratch area for plan schema changes. The administrator creates this pending area, makes changes as necessary, possibly validates these changes, and only when the submit is completed do these changes become active.

Syntax

CS_RESOURCE_MANAGER.CREATE_PENDING_AREA;

Usage Notes

You may, at any time while the pending area is active, view the current plan schema with your changes by selecting from the appropriate user views. See Viewing Database Resource Manager Configuration and Status for more details.

At any time, you may clear the pending area if you want to stop the current changes. You may also call the VALIDATE procedure to confirm whether the changes you have made are valid. You do not have to perform your changes in a given order to maintain a consistent group of entries. These checks are also implicitly done when the pending area is submitted.

Note: Oracle allows “orphan” consumer groups (in other words, consumer groups that have no plan directives that refer to them). This is in anticipation that an administrator may want to create a consumer group that is not currently being used, but will be used in the future.

For resource plans, the following rules must be adhered to, and they are checked whenever the validate or submit procedures are executed:

No plan schema may contain any loops.
All plans and consumer groups referred to by plan directives must exist.
All plans must have plan directives that refer to either plans or consumer groups.
All percentages in any given level must not add up to greater than 100 for the emphasis resource allocation method.
No plan may be deleted that is currently being used as a top plan by an active instance.
There cannot be more than 28 plan directives coming from any given plan (that is, no plan can have more than 28 children).
There cannot be more than 28 consumer groups in any active plan schema.
Plans and consumer groups use the same namespace; therefore, no plan can have the same name as any consumer group.
There must be a plan directive for OTHER_GROUPS somewhere in any active plan schema. This ensures that a session not covered by the currently active plan is allocated resources as specified by the OTHER_GROUPS directive.

If any of the preceding rules are broken when checked by the VALIDATE or SUBMIT procedures, then an informative error message is returned. You may then make changes to fix one or more problems and reissue the validate or submit procedures.

CREATE_PLAN Procedure

This procedure creates entries which define resource plans.

Syntax

CS_RESOURCE_MANAGER.CREATE_PLAN (
   plan                       IN   VARCHAR2,
   comment                    IN   VARCHAR2 DEFAULT NULL);

Parameters

Parameter	Description
`plan`	Name of the resource plan
`comment`	User comment

Example

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

CREATE_PLAN_DIRECTIVE Procedure

This procedure creates resource plan directives.

Syntax

CS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (
   plan                          IN VARCHAR2,
   consumer_group                IN VARCHAR2,
   comment                       IN VARCHAR2 DEFAULT NULL,
   shares                        IN NUMBER   DEFAULT NULL,
   utilization_limit             IN NUMBER   DEFAULT NULL,
   switch_action                 IN VARCHAR2 DEFAULT NULL,
   cpu_time_limit                IN NUMBER   DEFAULT NULL,
   io_megabytes_limit            IN NUMBER   DEFAULT NULL,
   io_reqs_limit                 IN NUMBER   DEFAULT NULL,
   io_logical_limit              IN NUMBER   DEFAULT NULL,
   elapsed_time_limit            IN NUMBER   DEFAULT NULL,
   max_idle_time                 IN NUMBER   DEFAULT NULL,
   max_idle_blocker_time         IN NUMBER   DEFAULT NULL,
   active_session_limit          IN NUMBER   DEFAULT NULL,
   active_session_timeout        IN NUMBER   DEFAULT NULL,
   parallel_degree_limit         IN NUMBER   DEFAULT NULL,
   concurrency_limit             IN NUMBER   DEFAULT NULL,
   session_pga_limit             IN NUMBER   DEFAULT NULL,
   parallel_queue_timeout        IN NUMBER   DEFAULT NULL,
   parallel_queue_timeout_action IN NUMBER   DEFAULT NULL);

Parameters

Parameter	Description
`plan`	Name of the resource plan
`consumer_group`	Name of the consumer group
`comment`	Comment for the plan directive
`shares`	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. If you do not specify shares for a consumer group in your plan, that consumer group's share will be set to 1.
`utilization_limit`	Resource limits that determine the maximum CPU and I/O resources a consumer group can get.
`switch_action`	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.
`cpu_time_limit`	Time on CPU (in seconds) that a session can execute before an action is taken. Default is `NULL`, which means unlimited.
`io_megabytes_limit`	Amount of I/O (in MB) that a session can issue before an action is taken. Default is `NULL`, which means unlimited.
`io_reqs_limit`	Number of I/O requests that a session can issue before an action is taken. Default is `NULL`, which means unlimited.
`io_logical_limit`	Number of logical I/Os that will trigger the action specified by `switch_action`.
`elapsed_time_limit`	Elapsed time (in seconds) that will trigger the action specified by `switch_action`.
`max_idle_time`	Number of seconds that a session can be idle before the session is terminated. Default is `NULL`, which means unlimited.
`max_idle_blocker_time`	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.
`active_session_limit`	Maximum number of sessions that can concurrently have an active call.
`active_session_timeout`	Specified time (in seconds) after which a call in the inactive session queue (waiting for execution) will time out. Default is `NULL`, which means unlimited.
`parallel_degree_limit`	Specifies a limit on the degree of parallelism for any operation. Default is `NULL`, which means unlimited. Use value of 1 for an operation to be serial.
`concurrency_limit`	Sets the concurrency level and hence the Degree of Parallelism (DOP). It can make the DOP 1.
`session_pga_limit`	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.
`parallel_queue_timeout`	Specifies the time (in seconds) that a parallel statement may remain in its consumer group’s parallel statement queue before it is removed and terminated with an error (ORA- 07454).
`parallel_queue_timeout_action`	Specifies the action to be taken when a parallel statement is removed from the queue due to `parallel_queue_timeout`. The values are: `CANCEL`: The parallel statement is terminated with error ORA-7454 `RUN`: The SQL statement runs immediately, and might get downgraded if parallel servers are unavailable.

Usage Notes

You cannot create plan directives on the default predefined plans that come with Autonomous AI Database, that is, DWCS_PLAN and OLTP_PLAN.
All parameters except shares default to NULL.
An error is thrown if parallel_queue_timeout is specified, but parallel_queue_timeout_action is not specified.

Examples

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

DELETE_CONSUMER_GROUP Procedure

This procedure deletes resource consumer groups.

Syntax

CS_RESOURCE_MANAGER.DELETE_CONSUMER_GROUP (
   consumer_group IN VARCHAR2);

Parameters

Parameter	Description
`consumer_group`	Name of the consumer group to be deleted. Note: You cannot delete predefined consumer groups that come with Autonomous AI Database, that is, TPURGENT, TP, HIGH, MEDIUM, and LOW.

Examples

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

DELETE_PLAN Procedure

This procedure deletes the specified plan as well as all the plan directives to which it refers.

Syntax

CS_RESOURCE_MANAGER.DELETE_PLAN (
  plan    IN VARCHAR2,
  cascade IN BOOLEAN DEFAULT FALSE);

Parameters

Parameter	Description
`plan`	Name of the resource plan to delete. Note: You cannot delete predefined plans that come with Autonomous AI Database, that is, `DWCS_PLAN` and `OLTP_PLAN`.
`cascade`	Flag to indicate whether to delete the specified plan and all of its descendants (plan directives, subplans, consumer groups). Mandatory objects and directives are not deleted. Default value is `false`. With cascade ON, if `DELETE_PLAN` encounters any error, then it rolls back the operation, and nothing is deleted.

Example

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

DELETE_PLAN_DIRECTIVE Procedure

This procedure deletes resource plan directives.

Syntax

CS_RESOURCE_MANAGER.DELETE_PLAN_DIRECTIVE (
   plan              IN VARCHAR2,
   consumer_group    IN VARCHAR2);

Parameters

Parameter	Description
`plan`	Name of the resource plan. Note: You cannot delete plan directives on the default predefined plans that come with Autonomous AI Database, that is, `DWCS_PLAN` and `OLTP_PLAN`.
`consumer_group`	Name of the consumer group.

Example

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

LIST_CURRENT_RULES Function

This function returns the current settings for the default plan.

Note: LIST_CURRENT_RULES cannot be used to view the settings for custom plans. If you are using custom plans, you can use the DBRM Views to view your settings.

Syntax

CS_RESOURCE_MANAGER.LIST_CURRENT_RULES
   RETURN TABLE;

Example

SELECT * FROM CS_RESOURCE_MANAGER.LIST_CURRENT_RULES();

CONSUMER_GROUP ELAPSED_TIME_LIMIT IO_MEGABYTES_LIMIT SHARES CONCURRENCY_LIMIT DEGREE_OF_PARALLELISM
-------------- ------------------ ------------------ ------ ----------------- ---------------------
HIGH                                                      4                 3                     3
MEDIUM                                                    2                 2                     9
LOW                                                       1               900                     1

LIST_DEFAULT_RULES Function

This function returns the default values for all consumer groups.

Syntax

CS_RESOURCE_MANAGER.LIST_DEFAULT_RULES
  RETURN TABLE;

Usage Note

By default the parallel degree policy value is MANUAL for the TPURGENT consumer group. The CS_RESOURCE_MANAGER.LIST_DEFAULT_RULES function shows no value for the default value for the DEGREE_OF_PARALLELISM for the TPURGENT consumer group.

Example

SELECT * FROM CS_RESOURCE_MANAGER.LIST_DEFAULT_RULES();
CONSUMER_GROUP ELAPSED_TIME_LIMIT IO_MEGABYTES_LIMIT SHARES CONCURRENCY_LIMIT DEGREE_OF_PARALLELISM
-------------- ------------------ ------------------ ------ ----------------- ---------------------
HIGH                            0                  0      4                 3                     1
MEDIUM                          0                  0      2                 1                     1
LOW                             0                  0      1               300                     1
TP                              0                  0      8               300                     1
TPURGENT                        0                  0     12               300

REVERT_TO_DEFAULT_VALUES Procedure

This procedure reverts the default resource manager plan properties to default values.

Note: You cannot use this procedure with custom plans and it can only be used to revert the changes to the default resource manager plans that come with Autonomous AI Database, that is, DWCS_PLAN and OLTP_PLAN.

Syntax

CS_RESOURCE_MANAGER.REVERT_TO_DEFAULT_VALUES(
   consumer_group         IN VARCHAR2,
   shares                 IN BOOLEAN   DEFAULT FALSE,
   concurrency_limit      IN BOOLEAN   DEFAULT FALSE);

Parameters

Parameter Description

Parameter	Description
`consumer_group`	Specifies the consumer group to revert. Valid values are: `HIGH`, `MEDIUM`, `LOW`, `TP`, or `TPURGENT`.
`shares`	When the value is `TRUE`, revert shares for the service to the default value.
`concurrency_limit`	When the value is `TRUE`, revert the `concurrency_limit` for the service to the default value. When you revert the `concurrency_limit`, both the `concurrency_limit` and the `degree_of_parallelism` values are set to their default values.

consumer_group

Specifies the consumer group to revert.

Valid values are: HIGH, MEDIUM, LOW, TP, or TPURGENT.

shares When the value is TRUE, revert shares for the service to the default value.

concurrency_limit When the value is TRUE, revert the concurrency_limit for the service to the default value. When you revert the concurrency_limit, both the concurrency_limit and the degree_of_parallelism values are set to their default values.

Examples

BEGIN
   CS_RESOURCE_MANAGER.REVERT_TO_DEFAULT_VALUES(
      consumer_group => 'MEDIUM',
      concurrency_limit => TRUE);
END;
/

BEGIN
   CS_RESOURCE_MANAGER.REVERT_TO_DEFAULT_VALUES(
      consumer_group => 'HIGH',
      shares => TRUE);
END;
/

SET_CONSUMER_GROUP_MAPPING Procedure

This procedure adds, deletes, or modifies entries that map sessions to consumer groups, based on the session’s login and runtime attributes.

Syntax

CS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(
   attribute        IN VARCHAR2,
   value            IN VARCHAR2,
   consumer_group   IN VARCHAR2 DEFAULT NULL);

Parameters

Parameter	Description
`attribute`	Mapping attribute to add or modify. It can be one of the Constants listed.
`value`	Attribute value to match. This includes both absolute mapping and regular expressions.
`consumer_group`	Name of the mapped consumer group, or `NULL` to delete a mapping.

Usage Notes

If no mapping exists for the given attribute and value, a mapping to the given consumer group will be created. If a mapping already exists for the given attribute and value, the mapped consumer group will be updated to the one given. If the consumer_group argument is NULL, then any mapping from the given attribute and value will be deleted.
The subprogram supports simple regex expressions for the value parameter. It implements the same semantics as the SQL 'LIKE' operator. Specifically, it uses '%' as a multi character wildcard and '_' as a single character wildcard. The '\ character can be used to escape the wildcards. Note that wildcards can only be used if the attribute is one of the following:
- CLIENT_OS_USER
- CLIENT_PROGRAM
- CLIENT_MACHINE
- MODULE_NAME
- MODULE_NAME_ACTION
- SERVICE_MODULE
- SERVICE_MODULE_ACTION
Consumer group mapping comparisons for CS_RESOURCE_MANAGER.CLIENT_PROGRAM are performed by stripping the @ sign and following characters from V$SESSION.PROGRAM before comparing it to the CLIENT_PROGRAM value supplied.
You can use attributes such as username or application name to determine how sessions are placed in a consumer group as shown in the example above.

Examples

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

SET_CONSUMER_GROUP_MAPPING_PRI Procedure

Multiple attributes of a session can be used to map the session to a consumer group. This procedure prioritizes the attribute mappings.

Syntax

CS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING_PRI(
   explicit               IN NUMBER,
   oracle_user            IN NUMBER,
   service_name           IN NUMBER,
   client_program         IN NUMBER,
   module_name            IN NUMBER,
   module_name_action     IN NUMBER,
   client_id              IN NUMBER DEFAULT 11);

Parameters

Parameter	Description
`explicit`	Priority of the explicit mapping.
`oracle_user`	Priority of the Oracle user name mapping.
`service_name`	Priority of the client service name mapping.
`client_program`	Priority of the client program mapping.
`module_name`	Priority of the application module name mapping.
`module_name_action`	Priority of the application module name and action mapping.
`client_id`	Client identifier.

Usage Notes

This procedure requires that you include the pseudo-attribute explicit as an argument. It must be set to
1. It indicates that explicit consumer group switches have the highest priority. Switching the consumer group using DBMS_SESSION.SWITCH_CURRENT_CONSUMER_GROUP will take precedence over the consumer group mappings.
Each priority value must be a unique integer from 1 to 11. Together, they establish an ordering where 1 is the highest priority and 11 is the lowest.
You can use attributes such as username or application name to determine how sessions are placed in a consumer group. See Step 4: Create Consumer Group Mappings for an example.

SUBMIT_PENDING_AREA Procedure

This procedure submits pending changes for the resource manager. It clears the pending area after validating and committing the changes (if valid).

Note: A call to SUBMIT_PENDING_AREA may fail even if VALIDATE_PENDING_AREA succeeds. This can occur if a plan being deleted is loaded by an instance after the call to VALIDATE_PENDING_AREA, but before the call to SUBMIT_PENDING_AREA.

Syntax

CS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA;

UPDATE_PLAN_DIRECTIVE Procedure

Use this procedure to update default predefined plans that come with Autonomous AI Database, that is, DWCS_PLAN and OLTP_PLAN for a specified consumer group.

Note: You cannot use this procedure update the settings for custom plans. To update a custom user-defined plan attributes, use the UPDATE_PLAN_DIRECTIVE_ADV procedure.

Syntax

CS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE(
   consumer_group         IN VARCHAR2,
   io_megabytes_limit     IN NUMBER   DEFAULT NULL,
   elapsed_time_limit     IN NUMBER   DEFAULT NULL,
   shares                 IN NUMBER   DEFAULT NULL,
   concurrency_limit      IN NUMBER   DEFAULT NULL);

Parameters

Parameter	Description
`consumer_group`	Specifies the consumer group to update. Valid values are: `HIGH`, `MEDIUM`, `LOW`, `TP`, or `TPURGENT`.
`io_megabytes_limit`	Specifies the maximum megabytes of I/O that a SQL operation can issue. Specify a `NULL` value to clear the limit.
`elapsed_time_limit`	Specifies the maximum time in seconds that a SQL operation can run. Specify a `NULL` value to clear the limit.
`shares`	Specifies the shares value. A higher number of shares, relative to other consumer groups, increases the consumer group’s CPU and I/O priority.
`concurrency_limit`	Specifies the maximum number of concurrent SQL statements that can be executed. This parameter is only valid with the `MEDIUM` consumer group.

Usage Notes

When a SQL statement in the specified service runs more than the specified runtime limit (elapsed_time_limit) or does more I/O than the specified amount (io_megabytes_limit), then the SQL statement will be terminated.
When the concurrency_limit parameter is specified, the only valid value for consumer_group is MEDIUM.

Examples

BEGIN
   CS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE(
      consumer_group => 'HIGH',
      shares => 8);
   CS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE(
      consumer_group => 'MEDIUM',
      shares => 2);
   CS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE(
      consumer_group => 'LOW',
      shares => 1);
END;
/

BEGIN
   CS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE(
      consumer_group => 'HIGH',
      io_megabytes_limit => null,
      elapsed_time_limit => null);
END;
/

BEGIN
   CS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE(
     consumer_group => 'MEDIUM',
     concurrency_limit => 2);
END;
/

UPDATE_PLAN_DIRECTIVE_ADV Procedure

Use this procedure to update user-defined resource manager plan for a specified consumer group.

Note: You cannot use this procedure to update default predefined plans that come with Autonomous AI Database, that is, DWCS_PLAN and OLTP_PLAN. To update a predefined default resource plan, use the UPDATE_PLAN_DIRECTIVE procedure.

Syntax

CS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE_ADV (
   plan                          IN VARCHAR2 DEFAULT NULL,
   consumer_group                IN VARCHAR2,
   comment                       IN VARCHAR2 DEFAULT NULL,
   shares                        IN NUMBER   DEFAULT NULL,
   utilization_limit             IN NUMBER   DEFAULT NULL,
   switch_action                 IN VARCHAR2 DEFAULT NULL,
   cpu_time_limit                IN NUMBER   DEFAULT NULL,
   io_megabytes_limit            IN NUMBER   DEFAULT NULL,
   io_reqs_limit                 IN NUMBER   DEFAULT NULL,
   io_logical_limit              IN NUMBER   DEFAULT NULL,
   elapsed_time_limit            IN NUMBER   DEFAULT NULL,
   max_idle_time                 IN NUMBER   DEFAULT NULL,
   max_idle_blocker_time         IN NUMBER   DEFAULT NULL,
   active_session_limit          IN NUMBER   DEFAULT NULL,
   active_session_timeout        IN NUMBER   DEFAULT NULL,
   parallel_degree_limit         IN NUMBER   DEFAULT NULL,
   concurrency_limit             IN NUMBER   DEFAULT NULL,
   session_pga_limit             IN NUMBER   DEFAULT NULL,
   parallel_queue_timeout        IN NUMBER   DEFAULT NULL,
   parallel_queue_timeout_action IN NUMBER   DEFAULT NULL);

Parameters

Parameter	Description
`plan`	Name of the resource plan.
`consumer_group`	Name of the consumer group.
`comment`	Comment for the plan directive.
`shares`	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. If you do not specify shares for a consumer group in your plan, that consumer group's share will be set to 1.
`utilization_limit`	Resource limits that determine the maximum CPU and I/O resources a consumer group can get.
`switch_action`	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.
`cpu_time_limit`	Time on CPU (in seconds) that a session can execute before an action is taken. Default is NULL, which means unlimited.
`io_megabytes_limit`	Amount of I/O (in MB) that a session can issue before an action is taken. Default is `NULL`, which means unlimited.
`io_reqs_limit`	Number of I/O requests that a session can issue before an action is taken. Default is `NULL`, which means unlimited.
`io_logical_limit`	Number of logical I/Os that will trigger the action specified by `switch_action`.
`elapsed_time_limit`	Elapsed time (in seconds) that will trigger the action specified by `switch_action`.
`max_idle_time`	Number of seconds that a session can be idle before the session is terminated. Default is `NULL`, which means unlimited.
`max_idle_blocker_time`	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.
`active_session_limit`	Maximum number of sessions that can concurrently have an active call.
`active_session_timeout`	Specified time (in seconds) after which a call in the inactive session queue (waiting for execution) will time out. Default is `NULL`, which means unlimited.
`parallel_degree_limit`	Specifies a limit on the degree of parallelism for any operation. Default is `NULL`, which means unlimited. Use value of 1 for an operation to be serial.
`concurrency_limit`	Sets the concurrency level and hence the Degree of Parallelism (DOP). It can make the DOP 1.
`session_pga_limit`	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.
`parallel_queue_timeout`	Specifies the time (in seconds) that a parallel statement may remain in its consumer group’s parallel statement queue before it is removed and terminated with an error (ORA- 07454).
`parallel_queue_timeout_action`	Specifies the action to be taken when a parallel statement is removed from the queue due to `parallel_queue_timeout`. The values are: `CANCEL`: The parallel statement is terminated with error ORA-7454 `RUN`: The SQL statement runs immediately, and might get downgraded if parallel servers are unavailable.

Usage Notes

All parameters except shares default to NULL.
An error is thrown if parallel_queue_timeout_action is specified, but parallel_queue_timeout is not specified.

VALIDATE_PENDING_AREA Procedure

This procedure validates pending changes for the resource manager.

Syntax

CS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA;