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.

• LIST_CURRENT_RULES Function
  This function lists the parameter values for each consumer group.
• LIST_DEFAULT_RULES Function
  This function returns the default values for all consumer groups.
• REVERT_TO_DEFAULT_VALUES Procedure
  This procedure reverts the specified resource manager's plan properties to default values.
• UPDATE_PLAN_DIRECTIVE Procedure
  Use this procedure to update the resource plan for a specified consumer group.

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