By using session login attributes and runtime properties, the SET_CONSUMER_GROUP_MAPPING procedure is used to add, delete, or modify mappings between sessions and consumer groups to precisely control resource usage.
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 add or modify. The current version supports only the USER attribute (the username of the database), the COLUMN attribute, and the FUNCTION attribute. OceanBase Database performs some background tasks, such as major compactions, backup and restore, and data completion. These tasks are not user-driven and might consume the CPU and I/O resources of other users and tasks. Therefore, you can specify a FUNCTION mapping for each OceanBase Database background task to limit its resources to its corresponding resource group. |
| value | Specifies the attribute value to match, which can include absolute mappings or regular expressions. |
| consumer_group | The name of the mapped consumer group to be removed. If NULL, this is the mapped consumer group name. |
Considerations
- If a mapping does not exist for the specified attribute and value, a mapping is created with the specified user group.
- If the mapping for the specified attribute and value already exists, its usage group is updated to the specified usage group.
- If the
consumer_groupparameter isNULL, any mapping based on the specified properties and values will be deleted.
The following table describes the predefined FUNCTION resource group mapping rules in OceanBase Database. You can use the DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING procedure to Modify or delete these mapping rules.
- USER: Maps all sessions of the specified user to the destination resource group.
- COLUMN: You can map values of specific table fields, for example,
t2.c3 = 3 for user1, to resource groups to isolate resources at a finer granularity. - FUNCTION: Used to map resource groups for OceanBase internal background tasks such as major compactions, backups, and statistics collection. The following table describes the built-in FUNCTION types.
| Attribute | Value | Description |
|---|---|---|
| FUNCTION | COMPACTION_HIGH | The mini merge and DDL KV merge tasks |
| FUNCTION | COMPACTION_MID | Minor Merge task |
| FUNCTION | COMPACTION_LOW | Major Merge task |
| FUNCTION | HA_HIGH | Replication, Rebuild, and restore tasks |
| FUNCTION | HA_MID | Migration task |
| FUNCTION | HA_LOW | Backup, cleanup and other tasks |
| FUNCTION | DDL | Validating and dropping unique indexes, dropping columns, and patching the data |
| FUNCTION | DDL_HIGH | DDL MemTable dumping |
| FUNCTION | CLOG_HIGH | Clog commit task |
| FUNCTION | OPT_STATS | Statistics collection task |
| FUNCTION | MVIEW | IO, CPU resource consumption of incremental refresh and MLOG purge for materialized views |
Examples
1. Map by user
CALL DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(
attribute => 'USER',
value => 'user1',
consumer_group => 'group1'
);
2. Table field condition mapping
CALL DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(
attribute => 'COLUMN',
value => 't2.c3 = 3 for user1',
consumer_group => 'group1'
);
3. Mapped to background tasks
CALL DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(
attribute => 'FUNCTION',
value => 'COMPACTION_HIGH',
consumer_group => 'group2'
);