The SET_CONSUMER_GROUP_MAPPING procedure is used to add, modify, or drop the mapping of session and resource group based on the login and runtime session attributes. This enables finer-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 | Specifies the mapping attribute to be added or modified. Supported values: USER (the database username), COLUMN (the column name), FUNCTION, and DATABASE. Since OceanBase Database's internal tasks such as minor compactions, major compactions, backups, data restore, and data recovery may consume CPU or I/O resources of other users or tasks, you can specify the FUNCTION attribute to map internal tasks to specified resource groups so that resource consumption of the tasks is limited to their resource groups. |
| value | Specifies the value of the attribute that must match the specified attribute. You can enter a literal or a regular expression. |
| consumer_group | Specifies the resource group to which to map the specified attribute value. If this parameter is NULL, the mapping is dropped. |
Considerations
- If no mapping is specified for the attribute and value, a mapping is created for the specified resource group.
- If a mapping is specified for the attribute and value, the resource group of the mapping is updated to the specified one.
- If
consumer_groupisNULL, all the mappings for the specified attribute and value are dropped.
The following table lists the built-in FUNCTION resource group mapping rules in OceanBase Database. You can use the DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING procedure to modify or drop these mapping rules.
Attribute |
Value |
Description |
|---|---|---|
| FUNCTION | COMPACTION_HIGH | Mini Merge and DDL KV Merge tasks |
| FUNCTION | COMPACTION_MID | Minor Merge tasks |
| FUNCTION | COMPACTION_LOW | Major Merge tasks |
| FUNCTION | HA_HIGH | Replication, Rebuild, and data restore tasks |
| FUNCTION | HA_MID | Migration tasks |
| FUNCTION | HA_LOW | Backup and garbage collection of backup files |
| FUNCTION | DDL | Operations such as checking the uniqueness of columns, dropping columns, and completing data. |
| FUNCTION | DDL_HIGH | Dumping of DDL MemTables. |
| FUNCTION | CLOG_HIGH | CLog log submission tasks. |
| FUNCTION | CLOG_MID | Tasks such as reading local disks for log archiving. |
| FUNCTION | CLOG_LOW | Read tasks for physical standby databases and CDC. |
| FUNCTION | OPT_STATS | Statistics collection tasks. |
| FUNCTION | GC_MACRO_BLOCK | Macro block recycling and reuse tasks. |
| FUNCTION | SQL_AUDIT | Persistence of SQL audit logs. |
| FUNCTION | IMPORT | Import tasks. |
| FUNCTION | MVIEW | I/O and CPU resource operations for incremental refreshes of materialized views and the purge of MLogs. |
| 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. Map by table field values
CALL DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(
attribute => 'COLUMN',
value => 't2.c3 = 3 for user1',
consumer_group => 'group1'
);
3. Map by internal task types
CALL DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(
attribute => 'FUNCTION',
value => 'COMPACTION_HIGH',
consumer_group => 'group2'
);
4. Map by database
obclient> CALL DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(
attribute => 'DATABASE',
value => 'db1',
consumer_group => 'group2'
);