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.
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 | CLOG_MID | Tasks such as reading local disks for log archiving |
| FUNCTION | CLOG_LOW | Physical standby database and CDC read tasks |
| FUNCTION | OPT_STATS | Statistics collection task |
| FUNCTION | GC_MACRO_BLOCK | Macro block recycling and reuse task |
| FUNCTION | SQL_AUDIT | SQL audit log persistence |
| FUNCTION | IMPORT | Import task |
| FUNCTION | MVIEW | IO, CPU resource consumption of incremental refresh and MLOG purge for materialized views |
| FUNCTION | REPLAY_HIGH | Log replay |
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'
);