Oracle Cloud Infrastructure Documentation

CS_RESOURCE_MANAGER Package

The CS_RESOURCE_MANAGER package provides an interface to list and update consumer group parameters, and to revert parameters to default values.

Parent topic: Autonomous Database Supplied Package Reference

LIST_CURRENT_RULES Function

This function lists the parameter values for each consumer group.

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 specified resource manager's plan properties to default values.

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

consumer_group

Specifies the consumer group to revert.

Depending on the workload, 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.

Usage Note

  • When the workload type is Data Warehouse, the valid values for consumer_group are HIGH, MEDIUM, or LOW.

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

UPDATE_PLAN_DIRECTIVE Procedure

Use this procedure to update the resource plan for a specified consumer group.

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.

Depending on the workload, 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 workload type is Data Warehouse, the valid values for consumer_group are HIGH, MEDIUM, or LOW.

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