Based on session login and runtime attributes, the SET_CONSUMER_GROUP_MAPPING procedure is used to add, delete, or modify the mapping between sessions and resource groups, enabling fine-grained resource management.
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 mapping attribute to be added or modified. The current version only supports USER(the database username), COLUMN Attribute value and FUNCTION. Tasks such as major compaction, minor compaction, backup and recovery, and data replenishment executed by the OceanBase AI Database backend do not belong to the User category in a macroscopic sense and are likely to consume CPU and I/O resources of other users and tasks during execution. Therefore, you can specify for different background tasks of the OceanBase AI Database FUNCTION The resources of background tasks can be limited to their corresponding resource groups. |
| value | The value of the attribute to match, including absolute mapping and regular expressions. |
| consumer_group | The name of the user group to which the mapping belongs. If it is NULL Then delete the mapping. |
Usage instructions
- If no mapping exists for the specified property and value, a mapping for the specified usage group will be created.
- If a mapping between the specified attribute and value already exists, the usage group for that mapping will be updated to the specified usage group.
- If the
consumer_groupparameter isNULL, any mappings from the specified attribute to value will be deleted.
The following table describes the predefined FUNCTION resource group mapping rules in OceanBase AI 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 target resource group.
- COLUMN: Allows resource group mapping based on specific table column values (e.g.,
t2.c3 = 3 for user1), enabling finer-grained resource isolation. - FUNCTION: Allows you to map resource groups to OceanBase background tasks, such as major compaction, backup, and statistics collection. The following table describes the built-in FUNCTION types:
Attribute name |
Value |
Description |
|---|---|---|
| FUNCTION | COMPACTION_HIGH | 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 Recovery Tasks |
| FUNCTION | HA_MID | migration task |
| FUNCTION | HA_LOW | Tasks such as backup and backup cleanup |
| FUNCTION | DDL | Unique index validation, column deletion, and data supplementation operations |
| FUNCTION | DDL_HIGH | Minor compaction of DDL MemTable |
| FUNCTION | CLOG_HIGH | Clog Commit Task |
| FUNCTION | OPT_STATS | Statistics Collection Task |
| FUNCTION | MVIEW | IO and CPU resource usage for incremental refresh of materialized views and MLOG purge operations |
Examples
1. User mapping
CALL DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(
attribute => 'USER',
value => 'user1',
consumer_group => 'group1'
);
2. Map by table field conditions
CALL DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(
attribute => 'COLUMN',
value => 't2.c3 = 3 for user1',
consumer_group => 'group1'
);
3. Mapping by background task type
CALL DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(
attribute => 'FUNCTION',
value => 'COMPACTION_HIGH',
consumer_group => 'group2'
);