The SET_CONSUMER_GROUP_MAPPING procedure adds, deletes, or modifies an entry that maps the session to a resource consumer group based on the login and operating attributes of a session.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only the MySQL mode.
Syntax
DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(
attribute IN VARCHAR2,
value IN VARCHAR2,
consumer_group IN VARCHAR2 DEFAULT NULL);
Parameters
| Parameter | Description |
|---|---|
| attribute | The attribute to be added or modified. OceanBase Database of this version supports only ORACLE_USER (database username), COLUMN, and ORACLE_FUNCTION. OceanBase Database executes certain tasks, such as major and minor compaction, backup and recovery, and data completion in the background. Such tasks are not in the scope of user tasks and may cause the contention of CPU and I/O resources. Therefore, OceanBase Database provides the FUNCTION attribute for you to specify mappings between background tasks and resource groups, so that the tasks can use only resources in the mapped resource groups. |
| value | The attribute value, which contains the absolute mapping and a regular expression. |
| consumer_group | The name of the mapped-to resource consumer group. If you set the value to NULL, the mapping will be deleted. |
Considerations
If no mapping exists between the specified attribute and value, a mapping to the specified resource consumer group will be created. If a mapping exists between the specified attribute and value, the mapped-to resource consumer group will be updated to the specified consumer group. If the consumer_group parameter is set to NULL, any mapping between the specified attribute and value will be deleted.
The following table describes the resource group mapping rules predefined for the ORACLE_FUNCTION attribute in OceanBase Database. You can use the DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING procedure to modify or delete these mapping rules.
| Attribute | Value | Description |
|---|---|---|
| ORACLE_FUNCTION | COMPACTION_HIGH | Corresponds to MINI_MERGE and DDL KV merge tasks. |
| ORACLE_FUNCTION | COMPACTION_MID | Corresponds to MINOR_MERGE tasks. |
| ORACLE_FUNCTION | COMPACTION_LOW | Corresponds to MAJOR_MERGE tasks. |
| ORACLE_FUNCTION | HA_HIGH | Corresponds to copy, rebuild, and restore tasks. |
| ORACLE_FUNCTION | HA_MID | Corresponds to migration tasks. |
| ORACLE_FUNCTION | HA_LOW | Corresponds to backup and backup cleanup tasks. |
| ORACLE_FUNCTION | DDL | Corresponds to unique index verification, column dropping, and data completion tasks. |
| ORACLE_FUNCTION | DDL_HIGH | Corresponds to minor compaction tasks of DDL MemTables. |
Examples
obclient> CALL DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(
attribute => 'COLUMN' ,
value => 't2.c3 = 3 for user1',
consumer_group => 'group1');
Query OK, 0 rows affected